Oracle Order Management and Purchase Order Scripts

-->: =============================================================================

-->: 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

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 mtl_txn_source_types

 WHERE mmt.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