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;
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