SQL Scripts

1. The following sql confirms that the Create Internal Orders concurrent process has inserted the requisition into the Order Management Interface tables.

SELECT COUNT (*)

  FROM oe_lines_iface_all

 WHERE orig_sys_line_ref IN

          (SELECT requisition_line_id

             FROM po_requisition_lines_all

            WHERE requisition_header_id IN

                     (SELECT requisition_header_id

                        FROM po_requisition_headers_all

                       WHERE segment1 = '&Requisition_Number'));

2. The following sql can be used to confirm the Location defined in Oracle Purchasing is tied to a Customer in Order Management. The script shows that last ten locations created in order of creation date.

SELECT RTRIM (hl.location_code) location_code,

       hl.location_id,

       ood.organization_code,

       pla.organization_id,

       hl.ship_to_site_flag,

       hl.receiving_site_flag,

       pla.customer_id,

       RTRIM (rc.customer_name) customer_name

  FROM hr_locations_all hl,

       org_organization_definitions ood,

       po_location_associations_all pla,

       ra_customers rc

 WHERE     pla.location_id = hl.location_id

       AND rc.customer_id(+) = pla.customer_id

       AND ood.organization_id(+) = pla.organization_id

       AND ROWNUM <> 11

        order by hl.creation_date desc;

3. The following sql can be used to review the requisition, sales order, and receipt number. It is offered to assist in showing the joins between the various tables. 

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 --instead of hardcoding to 10

       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

       AND ooh.org_id = posp.org_id

       AND ool.header_id = ooh.header_id

       AND ool.shipped_quantity > 0

       AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%'

       AND ool.source_document_line_id IS NOT NULL