Saturday, 2 July 2011

po without invoice Query

SELECT segment1 po_number, pol.po_line_id, pol.item_id, rct.transaction_type,
       poh.type_lookup_code,rct.transaction_id
  FROM po_headers_all poh,
       po_lines_all pol,
       po_distributions_all pod,
       rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       rcv_transactions rct
 WHERE poh.po_header_id = pol.po_header_id
   AND pol.po_line_id = pod.po_line_id
   AND rsh.shipment_header_id = rsl.shipment_header_id
   AND rsl.po_distribution_id = pod.po_distribution_id
   AND rsl.shipment_line_id = rct.shipment_line_id
   and poh.segment1=:PO_NUM
   and not exists (select rcv_transaction_id from ap_invoice_distributions_all)  

No comments:

Post a Comment