Oracle Order Management :Technical Overview

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