Wednesday, April 16, 2014

The following Query gives you the PO Details in R12 which have receipts and which don't have Invoices.

SELECT asp.vendor_name
      ,pha.segment1
      ,pha.creation_date po_date
      ,pha.type_lookup_code
      ,sum(pla.unit_price* pla.quantity) po_amount
  FROM po_distributions_all pda
      ,po_headers_all pha
      ,rcv_shipment_lines rsl
      ,ap_suppliers asp
      ,po_lines_all pla
 WHERE 1=1
   AND pda.po_header_id=pha.po_header_id
   AND pda.po_distribution_id not in
      (SELECT po_distribution_id FROM po_distributions_all pda
       where po_distribution_id in (select distinct  po_distribution_id from ap_invoice_distributions_all))
   AND rsl.po_header_id=pha.po_header_id  
   AND asp.vendor_id=pha.vendor_id
   AND pha.po_header_id=pla.po_header_id
   AND pla.po_line_id=pda.po_line_id
GROUP BY asp.vendor_name,pha.segment1,pha.creation_date,pha.type_lookup_code

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect