Total Pageviews

December 7, 2017

12/07/2017 11:57:00 PM
Oracle Order Management Queries










Following sql query retrieve the Internal Requisition details which were
transferred to OM , but Internal Sales Orders were NOT created.



SELECT poh.org_id,
       poh.segment1 Requisition_Number,
       poh.requisition_header_id,
       poh.creation_date,
       poh.created_by,
       poh.last_update_date,
       poh.last_updated_by
  FROM po_requisition_headers_all poh 
  where poh.authorization_status = 'APPROVED'  
 AND poh.transferred_to_oe_flag = 'Y'  
 AND poh.type_lookup_code = 'INTERNAL' 
  and NOT EXISTS (
SELECT 1
  FROM oe_order_headers_all h
 WHERE h.order_source_id = 10

       AND h.source_document_id = poh.requisition_header_id)  AND TRUNC(poh.creation_date) BETWEEN TO_DATE ('&from_date', 'DD-MM-RR')          AND TO_DATE ('&To_Date','DD-MM-RR');

Following sql query retrieve the Internal Requisition details whose datawas transferred into OM interface tables, but still NOT created Internal Sales Order.



SELECT poh.org_id,
       poh.segment1 Requisition_Number,
       poh.requisition_header_id,
       poh.creation_date,
       poh.created_by,
       poh.last_update_date,
       poh.last_updated_by
  FROM po_requisition_headers_all poh
 WHERE     poh.authorization_status = 'APPROVED'
       AND poh.transferred_to_oe_flag = 'Y'
       AND poh.type_lookup_code = 'INTERNAL'
       AND NOT EXISTS
                  (SELECT 1
                     FROM oe_order_headers_all h
                    WHERE h.order_source_id = 10
                          AND h.source_document_id =
                                 poh.requisition_header_id)
       AND TRUNC (poh.creation_date) BETWEEN TO_DATE ('&from_date',
                                                      'DD-MM-RR')
                                         AND TO_DATE ('&To_Date', 'DD-MM-RR')
       AND EXISTS
              (SELECT 1
                 FROM oe_headers_iface_all ih
                WHERE ih.order_source_id = 10
                      AND ih.orig_sys_document_ref = poh.segment1)
 
Related Posts Plugin for WordPress, Blogger...