Important SQL Queries For Order To Cash [O2C] Cycle


Following  SQL queries can be very handy if you are performing any transactions related to Order to cash cycle in Oracle applications. 

For Getting the Order header info:
Select * from oe_order_headers_all ooh
where order_number= :p_order_number

For Getting the Operating unit info
select * from hr_operating_units
where organization_id=oe_order_headers_all.org_id

For Getting the Order type info
select * from apps.oe_transaction_types_tl
where transaction_type_id=oe_order_headers_all.order_type_id

For Getting the Price list info
select * from apps.qp_list_headers_tl
where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines
where list_header_id=oe_order_headers_all.price_list_id


For Getting the customer info
select * from hz_cust_accounts hca
where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties
where party_id=hz_cust_accounts.party_id

For Getting the Ship to location info
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.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

For Getting the 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

For Getting the Actual address
select * from hz_locations 
where location_id=hz_party_sites.location_id

For Getting the Sales rep id
select name from apps.ra_salesreps_all salerep  where
salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

For Getting the Payment terms details
select name from apps.ra_terms_tl
where term_id =oe_order_headers_all.payment_term_id
and language = 'US'

For Getting the Order source details
select name from apps.oe_order_sources
where order_source_id= oe_order_headers_all.order_source_id
and enabled_flag= 'Y'

For Getting the Order Source Reference details
select orig_sys_document_ref from oe_order_headers_all ooh
where order_number='&oracle order number'

For Getting the 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 Getting the Freight terms details
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 performing the sales channel code validation
select lookup_code from apps.oe_lookups
where lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'
upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

For Getting the Ship method
select ship_method_code from wsh.wsh_carrier_services
where ship_method_code = oe_order_headers_all.shipping_method_code

For Getting the Warehouse Info
select * from org_organization_definitions
where organization_id = oe_order_headers_all.ship_from_org_id

For Getting the Sales order Lines Details
select * from apps.oe_order_lines_all
where header_id=oe_order_headers_all.header_id

For Getting the Transactional currency code
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

For Getting the 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

For Getting the UOM details
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

For performing the 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

For Getting the On hand quantities details
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

For Getting the Shipping details
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

For Getting the Material transactions details
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 = mmt.transaction_type_id

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


Join between OMWSHAR Tables SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'



Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:

SELECT interface_source_code,
           interface_source_line_id,
           quantity,
           destination_type_code,
           transaction_id,
           process_flag,
           request_id,
           TRUNC (creation_date)
  FROM po_requisitions_interface_all
 WHERE interface_source_code = 'ORDER ENTRY'
   AND interface_source_line_id IN (SELECT drop_ship_source_id
                                      FROM oe_drop_ship_sources
                                     WHERE header_id = &order_hdr_id
                                       AND line_id = &order_line_id);

The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)

SELECT porh.segment1,
           porl.line_num,
           pord.distribution_num,
           ooh.order_number
           sales_order,
           ool.line_number so_line_num,
           rsh.receipt_num,
           rcv.transaction_type
  FROM oe_order_headers_all ooh,
          po_requisition_headers_all porh,
          po_requisition_lines_all porl,
          po_req_distributions_all pord,
          oe_order_lines_all ool,
          po_system_parameters_all posp,
          rcv_shipment_headers rsh,
          rcv_transactions rcv
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND porl.requisition_line_id = rcv.requisition_line_id
   AND pord.distribution_id = rcv.req_distribution_id
   AND rcv.shipment_header_id = rsh.shipment_header_id


You can Also Read:

COMMENTS

BLOGGER: 8
Loading...
Name

Advance Table In OAF,1,AOL Concepts,18,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,57,Interview Q/A,57,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,49,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle Alerts,1,Oracle AP,11,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Apps,2,Oracle Apps Interviews,29,Oracle Apps Tips,8,Oracle Apps Tutorials,97,Oracle AR,18,Oracle Assets,2,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle System Administrator,4,Oracle Workflow,3,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,51,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,3,
ltr
item
Oracle Apps Guy: Important SQL Queries For Order To Cash [O2C] Cycle
Important SQL Queries For Order To Cash [O2C] Cycle
Oracle Apps Guy
http://www.oracleappsguy.com/2012/12/important-sql-queries-for-order-to-cash.html
http://www.oracleappsguy.com/
http://www.oracleappsguy.com/
http://www.oracleappsguy.com/2012/12/important-sql-queries-for-order-to-cash.html
true
4186973960539901882
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy