Useful queries/code/scripts in Oracle Apps Order Management

Useful queries/code/scripts in Oracle Apps Order Management

Following are some of the most frequently used queries in oracle apps order management:

Order header info

Select * from oe_order_headers_all ooh

where order_number= :p_order_number

Operating unit info

select * from hr_operating_units

where organization_id=oe_order_headers_all.org_id

Order type info

select * from apps.oe_transaction_types_tl

where transaction_type_id=oe_order_headers_all.order_type_id

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

Find 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

Find 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

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

actual address

select * from hz_locations 

where location_id=hz_party_sites.location_id

Sales rep id

select name from apps.ra_salesreps_all 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)

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'

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

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

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

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

Material transactions

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 OM, WSH, AR 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