Oracle Cloud Fusion R13 Account Payable (AP) Credit Memo Matching - draft SQL
=========================================================================
SELECT
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
POZ_SUPPLIERS 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
No comments:
Post a Comment