Query to retrive Internal Requisition and Internal Sales Order Details


SELECT prh.segment1 ir_number,
       prh.creation_date ir_date,
       ppf.full_name requestor,
       hl.location_code,
       ood.organization_code destination_org_code,
       ood.organization_name destination_org_name,
       ood1.organization_code source_org_code,
       ood1.organization_name source_org_name,
       prh.authorization_status ir_status,
       (select action_date from po_action_history
        where object_type_code=’REQUISITION’ and action_code=’APPROVE’
        and employee_id=prh.preparer_id and object_id=prh.requisition_header_id ) ir_approved_date,
       msi.segment1 item_code,
       msi.description item_description,
       prl.quantity,
       prl.unit_meas_lookup_code,
       ooh.order_number internal_so_number,
       ooh.ordered_date sale_order_date,
       (select distinct request_number
        from   wsh_delivery_details wdd,
               mtl_txn_request_lines mtrl,
               mtl_txn_request_headers mtrh
        where  wdd.source_header_id=ooh.header_id
        and    wdd.move_order_line_id=mtrl.line_id
        and    mtrl.header_id=mtrh.header_id)move_order_ref,
        (select mtrh.creation_date
        from   wsh_delivery_details wdd,
               mtl_txn_request_lines mtrl,
               mtl_txn_request_headers mtrh
        where  wdd.source_header_id=ooh.header_id
        and    wdd.move_order_line_id=mtrl.line_id
        and    mtrl.header_id=mtrh.header_id
        and    rownum=1)transact_move_order_date,
        (select global_attribute16
         from   wsh_new_deliveries wnd,
                wsh_delivery_assignments wda,
                wsh_delivery_details wdd
         where  wdd.source_header_id=ooh.header_id
         and    wdd.delivery_detail_id=wda.delivery_detail_id
         and    wda.delivery_id=wnd.delivery_id
         and    rownum=1)actual_dispatch_date,
         (select rsh.receipt_num
         from   rcv_transactions rcv,
                rcv_shipment_headers rsh
         where  rcv.requisition_line_id        =  prl.requisition_line_id
         and    rcv.shipment_header_id         =   rsh.shipment_header_id
         and    rownum=1)destination_org_mrn_number,
         (select rsh.creation_date
         from   rcv_transactions rcv,
                rcv_shipment_headers rsh
         where  rcv.requisition_line_id        =  prl.requisition_line_id
         and    rcv.shipment_header_id         =   rsh.shipment_header_id
         and    rownum=1)destination_org_mrn_date       
—       mtrl.reference move_order_ref
FROM   po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       per_all_people_f ppf,
       hr_locations hl,
       org_organization_definitions ood,
       org_organization_definitions ood1,
       mtl_system_items_b msi,
       oe_order_headers_all ooh
—       oe_order_lines_all oola
WHERE 1=1
AND   prh.segment1                     =   ‘10172000065’–111597–1043
AND   prh.requisition_header_id        =   prl.requisition_header_id
AND   prl.to_person_id                 =   ppf.person_id
AND   ppf.effective_end_date           >   sysdate
AND   prl.deliver_to_location_id       =   hl.location_id(+)
AND   prl.destination_organization_id  =   ood.organization_id
AND   prl.source_organization_id       =   ood1.organization_id
AND   prl.item_id                      =   msi.inventory_item_id
AND   prl.source_organization_id       =   msi.organization_id
AND   prh.requisition_header_id        =   ooh.source_document_id
–AND   oola.header_id = ooha.header_id(+)

  • September 20, 2018 | 21 views
  • Comments