AP Credit Memo Matching - draft

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

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