Introduction
Business requested to provide the date for AR Adjustments for a particular period
Cause of the issue:
Business want to tally the data with uploaded Adjustments.
How do we solve:
We have created below query to retrieve data
Query:
SELECT
rct.org_id,
(
SELECT
a.name
FROM
hr_operating_units a
WHERE
a.organization_id = rct.org_id
) ou_name,
hca.account_number customer_name,
hp.party_name customer_account_name,
rct.trx_number invoice_number,
rct.trx_date trx_date,
psa.amount_due_original amount_due_original,
aa.amount adj_amount,
aa.reason_code,
aa.gl_date,
decode(rt.name, ‘MISC ADJUSTMENT’, ‘MISC ADJUSTMENTS’, rt.name) activity_name,
aa.type adj_type,
decode(gl.segment1, NULL, NULL, gl.segment1
|| ‘.’
|| gl.segment2
|| ‘.’
|| gl.segment3
|| ‘.’
|| gl.segment4
|| ‘.’
|| gl.segment5
|| ‘.’
|| gl.segment6
|| ‘.’
|| gl.segment7
|| ‘.’
|| gl.segment8
|| ‘.’
|| gl.segment9) gl_account,
(
SELECT
lkp1.meaning
FROM
ar_lookups lkp1
WHERE
lkp1.lookup_type = ‘ADJUST_REASON’
AND lkp1.lookup_code = aa.reason_code
) adj_reason,
aa.comments comments,
aa.adjustment_id,
aa.adjustment_number
FROM
ar_adjustments_all aa,
ar_receivables_trx_all rt,
ra_customer_trx_all rct,
gl_code_combinations gl,
hz_cust_accounts hca,
hz_parties hp,
ar_payment_schedules_all psa
WHERE
rt.receivables_trx_id = aa.receivables_trx_id
AND aa.code_combination_id = gl.code_combination_id (+)
AND rct.customer_trx_id = aa.customer_trx_id
AND rct.customer_trx_id = psa.customer_trx_id
AND hca.cust_account_id = rct.bill_to_customer_id
AND hca.party_id = hp.party_id
AND trunc(rct.trx_date) >= ‘2022-02-01’
AND trunc(rct.trx_date) <= ‘2023-05-31’
ORDER BY
org_id,
invoice_number
