PO 收料SQL

 

select 
      /*+ INDEX(

      rsh rcv_shipment_headers,

      rsl rcv_shipment_lines,

      ph po_headers_all,

      pl po_lines_all,

      msi mtl_system_items_b,

      pv po_vendors

      )
      */
       rsh.receipt_num,
       
       rsh.last_update_date receipt_date,
       
       rsl.quantity_received,
       
       ph.segment1 po_number,
       
       pl.item_description,
       
       rsl.vendor_lot_num,
       
       pv.vendor_name

  from rcv_shipment_headers rsh,
       
       rcv_shipment_lines rsl,
       
       po_headers_all ph,
       
       po_lines_all pl,
       
       mtl_system_items_b msi,
       
       po_vendors pv

 where rsh.receipt_source_code = 'VENDOR'
      
   and rsh.creation_date >= add_months(trunc(sysdate, 'DD'), -12)
      
   and rsh.ship_to_org_id = 127
      
   and rsh.shipment_header_id = rsl.shipment_header_id
      
   and rsl.po_header_id = ph.po_header_id
      
   and ph.po_header_id = pl.po_header_id
      
   and pl.item_id = msi.inventory_item_id
      
   and msi.organization_id = 127
      
   and ph.org_id = 125
      
   and ph.vendor_id = pv.vendor_id
      
   and pv.enabled_flag = 'Y'

 order by rsh.creation_date desc