Introduction:
This blog has the script to get the credit memo application details in Oracle Cloud application for Audit purpose.
Cause of the issue:
Auditor wanted to see the credit application details which were applied to the transactions. if it is not properly applied then they will ask business to un-apply.
How do we solve:
Below script will identity the credit application details.
SELECT DISTINCT
customeraccountpeo.account_number,
hp.party_name customer_name,
‘Credit Memo’ AS cm_type,
transactionheader.trx_number AS credit_number,
transactionheader.trx_class cm_type,
to_char(cm.gl_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) gl_cm,
transactionheader.reason_code,
appliedtransactionheader.trx_number,
(
SELECT DISTINCT
account_number
FROM
hz_cust_accounts a,
ra_customer_trx_all b,
ra_batch_sources_all c,
ra_cust_trx_types_all d
WHERE
a.cust_account_id = b.bill_to_customer_id
AND b.trx_number = appliedtransactionheader.trx_number
AND b.org_id = businessunit.bu_id
AND b.org_id = appliedtransactionheader.org_id
AND b.batch_source_seq_id = c.batch_source_seq_id
AND b.cust_trx_type_seq_id = d.cust_trx_type_seq_id
AND d.type NOT IN ( ‘CM’ )
AND b.customer_trx_id = receivableapplication.applied_customer_trx_id
) applied_trx_cust_number,
(
SELECT DISTINCT
e.party_name
FROM
hz_cust_accounts a,
ra_customer_trx_all b,
ra_batch_sources_all c,
ra_cust_trx_types_all d,
hz_parties e
WHERE
a.cust_account_id = b.bill_to_customer_id
AND b.trx_number = appliedtransactionheader.trx_number
AND b.org_id = businessunit.bu_id
AND b.org_id = appliedtransactionheader.org_id
AND b.batch_source_seq_id = c.batch_source_seq_id
AND b.cust_trx_type_seq_id = d.cust_trx_type_seq_id
AND d.type NOT IN ( ‘CM’ )
AND b.customer_trx_id = receivableapplication.applied_customer_trx_id
AND a.party_id = e.party_id
) applied_trx_cust_name
–,AppliedTransactionHeader.TRX_CLASS INV_TYPE
,
decode(appliedtransactionheader.trx_class, ‘INV’, ‘Invoice’, ‘DM’, ‘Debit Memo’,
appliedtransactionheader.trx_class) inv_type,
to_char(invoice.gl_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) gl_date,
invoice.invoice_currency_code,
receivableapplication.amount_applied
–,invoice.EXCHANGE_RATE*invoice.AMOUNT_APPLIED func
,
receivableapplication.acctd_amount_applied_from,
to_char(receivableapplication.gl_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) gl,
to_char(receivableapplication.apply_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) apply_date,
to_char(appliedtransactionheader.trx_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) trx_date,
to_char(transactionheader.trx_date, ‘DD-Mon-YY’, ‘nls_date_language=American’) trx_date_to
FROM
ar_receivable_applications_all receivableapplication,
ra_customer_trx_all transactionheader,
hz_cust_accounts custacct,
hz_parties customerpartypeo,
ra_customer_trx_all appliedtransactionheader,
fun_bu_perf_v businessunit,
hz_cust_accounts customeraccountpeo,
ar_payment_schedules_all invoice,
ar_payment_schedules_all cm,
hz_parties hp,
ra_customer_trx_lines_all rctla
WHERE
( receivableapplication.org_id = businessunit.bu_id
AND receivableapplication.customer_trx_id = transactionheader.customer_trx_id (+)
AND receivableapplication.applied_customer_trx_id = appliedtransactionheader.customer_trx_id (+) )
AND upper(receivableapplication.application_type) = upper(‘CM’)
AND transactionheader.bill_to_customer_id = custacct.cust_account_id (+)
AND transactionheader.org_id = businessunit.bu_id
AND custacct.party_id = customerpartypeo.party_id (+)
AND transactionheader.bill_to_customer_id = customeraccountpeo.cust_account_id (+)
AND invoice.customer_trx_id = appliedtransactionheader.customer_trx_id
AND cm.customer_trx_id = transactionheader.customer_trx_id
AND custacct.party_id = hp.party_id (+)
AND rctla.customer_trx_id (+) = transactionheader.customer_trx_id
AND businessunit.bu_name IN ( :bu_name )
AND receivableapplication.gl_date BETWEEN :from_gl_date AND :to_gl_date
