Query to Display PO Details which don't have Invoices

Query to Display PO Details which don't have Invoices, askhareesh blog for Oracle Applications

  SELECT ASP.VENDOR_NAME SUPPLIER_NAME,
         PHA.SEGMENT1 PO_NUM,
         PHA.TYPE_LOOKUP_CODE PO_TYPE,
         PHA.CREATION_DATE PO_DATE,
         PLA.QUANTITY QUANTITY,
         PLA.UNIT_PRICE,
         SUM (PLA.UNIT_PRICE * PLA.QUANTITY) PO_AMOUNT
    FROM PO_DISTRIBUTIONS_ALL PDA,
         PO_HEADERS_ALL PHA,
         PO_LINES_ALL PLA,
         AP_SUPPLIERS ASP,
         RCV_SHIPMENT_LINES RSL
   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 PDA.PO_LINE_ID = PLA.PO_LINE_ID
         AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
         AND PHA.VENDOR_ID = ASP.VENDOR_ID
         AND PHA.PO_HEADER_ID = RSL.PO_HEADER_ID
GROUP BY ASP.VENDOR_NAME,
         PHA.SEGMENT1,
         PHA.CREATION_DATE,
         PHA.TYPE_LOOKUP_CODE,
         PLA.QUANTITY,
         PLA.UNIT_PRICE
ORDER BY PHA.SEGMENT1

*/

No comments:

Post a Comment