SQL QUERY HOW TO FIND MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH


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 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 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 MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH SQL QUERY HOW TO FIND MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH Reviewed by oracle e-business Suite R12 on October 08, 2019 Rating: 5

No comments:

Powered by Blogger.