Tuesday, October 20, 2015

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect