Total Pageviews

July 23, 2017

7/23/2017 09:54:00 PM

Oracle Order Management :Technical Overview



-Order header info       

SELECT   *
  FROM   oe_order_headers_all ooh
 WHERE   order_number = '&oracle order number'

--Operating unit info 
  
SELECT   *
  FROM   hr_operating_units
 WHERE   organization_id = oe_order_headers_all.org_id

--Order type info   

SELECT   *
  FROM   oe_transaction_types_tl
 WHERE   transaction_type_id = oe_order_headers_all.order_type_id

--Price list info 
   
SELECT   *
  FROM   qp_list_headers_tl
 WHERE   list_header_id = oe_order_headers_all.price_list_id

   /
SELECT   *
  FROM   qp_list_lines
 WHERE   list_header_id = oe_order_headers_all.price_list_id


  Oracle Base Tables involved in Order to Cash life cycle.
  
--To find customer info   

SELECT   *
  FROM   hz_cust_accounts hca
 WHERE   cust_account_id = oe_order_headers_all.sold_to_org_id


  SELECT   *
  FROM   hz_parties
 WHERE   party_id = hz_cust_accounts.party_id


--To find Ship to location 

 SELECT   *
  FROM   hz_cust_site_uses_all
 WHERE   site_use_id = oe_order_headers_all.ship_to_org_id


SELECT   *
  FROM   hz_cust_acct_sites_all
 WHERE   cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id


SELECT   *
  FROM   hz_party_sites
 WHERE   party_site_id = hz_cust_acct_sites_all.party_site_id 


  We can find the actual address in hz_locations table 

SELECT   *
  FROM   hz_locations
 WHERE   location_id = hz_party_sites.location_id

--To find Bill to location 
  
SELECT   *
  FROM   hz_cust_site_uses_all
 WHERE   site_use_id = oe_order_headers_all.invoice_to_org_id

SELECT   *
  FROM   hz_cust_acct_sites_all
 WHERE   cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id

SELECT   *
  FROM   hz_party_sites
 WHERE   party_site_id = hz_cust_acct_sites_all.party_site_id


  We can find the actual address in hz_locations table 

SELECT   *
  FROM   hz_locations
 WHERE   location_id = hz_party_sites.location_id

--Sales rep id    

SELECT   name
  FROM   apps.jtf_rs_salesreps salerep
 WHERE   salesrep_id = oe_order_headers_all.salesrep_id AND ROWNUM = 1

  
--Payment terms   

SELECT   name
  FROM   apps.ra_terms_tl
 WHERE   term_id = oe_order_headers_all.payment_term_id AND language = 'US'

--Order source
    
SELECT   name
  FROM   apps.oe_order_sources
 WHERE   order_source_id = oe_order_headers_all.order_source_id
         AND enabled_flag = 'Y'


--Order Source Reference   

SELECT   orig_sys_document_ref
  FROM   oe_order_headers_all ooh
 WHERE   order_number = '&oracle order number  

--FOB Point Code    

SELECT   lookup_code
  FROM   ar_lookups
 WHERE       lookup_type = 'FOB'
         AND enabled_flag = 'Y'
         AND UPPER (meaning) = UPPER (oe_order_headers_all.fob_point_code)

--For Freight terms    


SELECT   lookup_code
  FROM   apps.oe_lookups
 WHERE   UPPER (lookup_type) = 'FREIGHT_TERMS' AND enabled_flag = 'Y'
         AND UPPER (lookup_code) =
               UPPER (oe_order_headers_all.freight_terms_code)

-- For sales channel code validation   

SELECT   lookup_code
  FROM   apps.oe_lookups
 WHERE   lookup_type = 'SALES_CHANNEL' AND enabled_flag = 'Y'
         AND UPPER (lookup_code) =
               UPPER (oe_order_headers_all.sales_channel_code)


  
--Ship method   

  SELECT   ship_method_code
  FROM   wsh.wsh_carrier_services
 WHERE   ship_method_code = oe_order_headers_all.shipping_method_code


--Warehouse Info   
SELECT   *
  FROM   org_organization_definitions
 WHERE   organization_id = oe_order_headers_all.ship_from_org_id

   
--Sales order Lines Details 
SELECT   *
  FROM   apps.oe_order_lines_all
 WHERE   header_id = oe_order_headers_all.header_id  


SELECT   ota.price_list_id, qhb.currency_code
  FROM   ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb
 WHERE       ota.transaction_type_id = oe_order_headers_all.order_type_id
         AND ota.price_list_id = qhb.list_header_id(+)
         AND NVL (qhb.list_type_code, 'PRL') = 'PRL'
         AND qhb.currency_code = oe_order_headers_all.transactional_curr_code

--Item info 
SELECT   *
  FROM   apps.mtl_system_items_b
 WHERE   segment1 LIKE oe_order_lines_all.ordered_item
         AND organization_id = oe_order_lines_all.ship_from_org_id 

--UOM s

SELECT   uom_code
  FROM   inv.mtl_units_of_measure_tl
 WHERE       UPPER (uom_code) = UPPER (oe_order_lines_all.order_quantity_uom)
         AND language = 'US'
         AND NVL (disable_date, (SYSDATE + 1)) > SYSDATE

  
--Item type code validation 

SELECT   lookup_code
  FROM   apps.oe_lookups
 WHERE       UPPER (lookup_type) = 'ITEM_TYPE'
         AND enabled_flag = 'Y'
         AND UPPER (lookup_code) = oe_order_lines_all.item_type_code

--On hand quantities  

SELECT   *
  FROM   apps.mtl_onhand_quantities
 WHERE   inventory_item_id = oe_order_lines_all.inventory_item_id
         AND organization_id = oe_order_lines_all.ship_from_org_id


----Shipping 
SELECT   *
  FROM   wsh_delivery_details
 WHERE   source_header_id = oe_order_headers_all.header_id

SELECT   *
  FROM   wsh_delivery_assignments
 WHERE   delivery_detail_id = wsh_delivery_details.delivery_detail_id 


SELECT   *
  FROM   wsh_new_deliveries
 WHERE   delivery_id = wsh_delivery_assignments.delivery_id


SELECT   *
  FROM   wsh_delivery_legs
 WHERE   delivery_id = wsh_new_deliveries.delivery_id

SELECT   *
  FROM   wsh_trip_stops wts
 WHERE   stop_id = wsh_delivery_legs.pick_up_stop_id

SELECT   *
  FROM   wsh_trips wt
 WHERE   trip_id = wsh_trip_stops.trip_id

SELECT   *
  FROM   org_organization_definitions
 WHERE   organization_id = wsh_new_deliveries.organization_id


  Oracle Base Tables involved in Order to Cash life cycle.
 
--Material transacions 
SELECT   *
  FROM   mtl_material_transactions
 WHERE   inventory_item_id = oe_order_lines_all.inventory_item_id
         AND organization_id = oe_order_lines_all.ship_from_org_id

SELECT   *
  FROM   mtl_transaction_types
 WHERE   transaction_type_id = mtl_material_transactions.transaction_type_id
SELECT   *
  FROM   mtl_transaction_types
 WHERE   transaction_type_id = mtl_material_transactions.transaction_type_id


select * from apps.mtl_txn_source_types  where transaction_source_type_id=        mtl_material_transactions.transaction_source_type_id                                    


 
  Creating an AR Invoice   
Now to generate the Invoice corresponding to the sales order created in the above steps, we need to go to AR Responsibility   
--AR Auto invoice  Interface Tables 

SELECT   * FROM ra_interface_salescredits_all

select * from ra_interface_distributions_all

select * from ra_interface_errors_all       
Run the Autoinvoice Import Program
   
   
--AR Autoinvoice Base Tables 
SELECT   *
  FROM   ra_customer_trx_all
 WHERE   trx_number = ‘&TRX_NUM’ AND org_id = operating_unit

SELECT   *
  FROM   ra_customer_trx_lines_all
 WHERE   customer_trx_id = ra_customer_trx_all.customer_trx_id
         AND org_id = operating_unit

SELECT   *
  FROM   ra_cust_trx_line_gl_dist_all
 WHERE   customer_trx_id = ra_customer_trx_all.customer_trx_id
         AND org_id = operating_unit    

----Receipt Tables 

SELECT   *
  FROM   ar_cash_receipts_all
 WHERE   receipt_number = '&Receipt_Num'
--Receipt distribution table
SELECT   *
  FROM   ar_cash_receipt_history_all
 WHERE   cash_receipt_id = ar_cash_receipts_all.cash_receipt_id
         AND org_id = operating_unit

SELECT   *
  FROM   ar_payment_schedules_all
 WHERE   cash_receipt_id = ar_cash_receipts_all.cash_receipt_id
         AND org_id = operating_unit
SELECT   *
  FROM   ar_receivable_applications_all
 WHERE   cash_receipt_id = ar_cash_receipts_all.cash_receipt_id
         AND org_id = operating_unit



  
 
Related Posts Plugin for WordPress, Blogger...