SQL Query How to find PO Match Invoices in oracle Apps

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_DATE
,      AIA.INVOICE_NUM
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2018' AND '31-MAR-2019'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2018' AND '31-MAR-2019'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE
SQL Query How to find PO Match Invoices in oracle Apps SQL Query How to find PO Match Invoices in oracle Apps Reviewed by oracle e-business Suite R12 on October 08, 2019 Rating: 5

No comments:

Powered by Blogger.