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