Query to get Invoice,Receipt numbers with PO number

Query to get Invoice,Receipt numbers with PO number, askhareesh blog for Oracle Applications

  SELECT DISTINCT
         PHA.SEGMENT1 PO_NUMBER,
         AIA.INVOICE_NUM INVOICE_NUMBER,
         RSH.RECEIPT_NUM RECEIPT_NUMBER
    FROM PO_HEADERS_ALL PHA,
         PO_DISTRIBUTIONS_ALL PDA,
         AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
         AP_INVOICES_ALL AIA,
         RCV_SHIPMENT_LINES RSL,
         RCV_SHIPMENT_HEADERS RSH
   WHERE     PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
         AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
         AND AIA.INVOICE_ID = AIDA.INVOICE_ID
         AND RSL.PO_HEADER_ID = PHA.PO_HEADER_ID
         AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
         AND PHA.SEGMENT1 = NVL (:P_PO_NUM, PHA.SEGMENT1)
--AND AIA.INVOICE_NUM=NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)
--AND RSH.RECEIPT_NUM=NVL(:P_RECEIPT_NUM,RSH.RECEIPT_NUM)
ORDER BY 3

*/

16 comments: