Thursday, February 22, 2018

SQL to get PO Number, Invoice Number and Receipt Number inline

Here is the SQL query to get the PO number,Invoice number and Receipt number inline.



 SELECT DISTINCT
    PHA.SEGMENT1 PO_NUMBER,
    PR.PO_RELEASE_NUM,  
    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 ,
    PO_RELEASES_ALL PR,
    RCV_TRANSACTIONS RT
 WHERE
    PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
    AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID
    AND AIDA.INVOICE_ID= AIA.INVOICE_ID
    AND PHA.PO_HEADER_ID = RSL.PO_HEADER_ID
    AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
    AND RSL.PO_RELEASE_ID = PR.PO_RELEASE_ID
    AND RSL.PO_HEADER_ID = PR.PO_HEADER_ID
    AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
    AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
    AND RT.TRANSACTION_ID=AIDA.RCV_TRANSACTION_ID
    AND PHA.SEGMENT1=NVL(:PO_NUM,PHA.SEGMENT1)
    AND PR.RELEASE_NUM=NVL(:PO_REL_NUM,PR.RELEASE_NUM)
ORDER BY 3;


No comments:

Post a Comment