This AP Credit Memo Matching query reviews invoice batches and can be run to identify invoices and resolve batch variances.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves the details of invoice debit and credit memo distributions.
HAOU.NAME ORGANIZATION_NAME,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
AIA.INVOICE_NUM MEMO_NUMBER,
AIA.INVOICE_DATE MEMO_DATE,
AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
AIDA.DISTRIBUTION_LINE_NUMBER,
DECODE (
AIDA.PARENT_INVOICE_ID,
NULL,
'Not Matched',
AIA2.INVOICE_NUM
) INV_NUM_APPLIED,
AIA.INVOICE_CURRENCY_CODE,
NVL (AIDA.AMOUNT, 0) DISTRIBUTION_AMOUNT,
AIDA.ACCOUNTING_DATE,
AIA.EXCHANGE_DATE,
AIA.EXCHANGE_RATE,
AIA.EXCHANGE_RATE_TYPE,
NVL (
NVL (
AIDA.BASE_AMOUNT,
AIDA.AMOUNT
),
0
) DIST_FUNC_AMOUNT,
AIDA.FINAL_MATCH_FLAG,
AIDA.DIST_MATCH_TYPE,
AIDA.LINE_TYPE_LOOKUP_CODE,
AIDA.PA_ADDITION_FLAG,
AIA.TERMS_ID,
PSV.VENDOR_ID SUPPLIER_ID,
AIA.INVOICE_ID,
AIA.ORG_ID,
GL.NAME SET_OF_BOOKS_NAME,
GL.LEDGER_ID
FROM
POZ_SUPPLIERS_V PSV,
AP_INVOICES_ALL AIA,
AP_INVOICES_ALL AIA2,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_LEDGERS GL
WHERE
1 = 1
AND AIA.INVOICE_TYPE_LOOKUP_CODE IN ('DEBIT', 'CREDIT')
AND AIDA.INVOICE_ID(+) = AIA.INVOICE_ID
AND AIA.VENDOR_ID = PSV.VENDOR_ID
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
AND AIDA.PARENT_INVOICE_ID = AIA2.INVOICE_ID(+)
AND AIDA.SET_OF_BOOKS_ID=GL.LEDGER_ID(+)