AP Credit Memo Matching - draft

Description

Categories: BI Publisher
Columns: C Vendor Name, C Vendor Number, C Memo Num, C Memo Date, C Distribution Line Num, C Inv Num Applied, C Invoice Date, C Inv Currency Code, C Distribution Amount, C Gl Date ...

Application: Payables
Source: Credit Memo Matching Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCMINV_XML
DB package: AP_APXCMINV_XMLP_PKG

Copy

SELECT
po1.vendor_name	C_VENDOR_NAME,
segment1		C_VENDOR_NUMBER,
i.invoice_num	C_MEMO_NUM,
i.invoice_date	C_MEMO_DATE,
id.distribution_line_number	C_DISTRIBUTION_LINE_NUM,
decode(id.parent_invoice_id, NULL, 'not matched',i2.invoice_num)	C_INV_NUM_APPLIED,
i2.invoice_date	C_INVOICE_DATE,
i.invoice_currency_code C_INV_CURRENCY_CODE,
nvl(id.amount,0)		C_DISTRIBUTION_AMOUNT,
id.accounting_date	C_GL_DATE,
id.exchange_date	C_EXCHANGE_DATE,
id.exchange_rate	C_EXCHANGE_RATE,
id.exchange_rate_type C_EXCHANGE_RATE_TYPE,
nvl(nvl(id.base_amount,id.amount),0)	C_DIST_FUNC_AMOUNT1,
id.base_amount	C_DIST_FUNC_AMOUNT2 
--	,&C_INV_CURRENCY_CODE C_INV_CURRENCY_CODE2
FROM
po_vendors   po1,
ap_invoices i,
ap_invoices i2,
ap_invoice_distributions id
WHERE
i.invoice_type_lookup_code IN ('DEBIT', 'CREDIT')
and id.invoice_id(+) = i.invoice_id
and i.vendor_id = po1.vendor_id
and id.parent_invoice_id = i2.invoice_id(+)
and i.set_of_books_id = :P_SET_OF_BOOKS_ID
&C_VENDOR
&C_DATE
ORDER BY 1,2,3,4,5,6,7