Oracle ERP Tech Blogs: Customer Aging on Subledger

Customer Aging

In this post, we will discuss on customer aging logic which also called as AR aging. Aging will display details of customer outstanding amount. I shared query in below section of this post.

Tables used in query development

XLA_TRANSACTION_ENTITIES

XLA_AE_HEADERS

XLA_AE_LINES

GL_CODE_COMBINATIONS

XLA_DISTRIBUTION_LINKS

HZ_CUST_ACCOUNTS

HZ_PARTIES

HZ_PARTY_SITES

HZ_LOCATIONS

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

RA_CUSTOMER_TRX_ALL

RA_CUST_TRX_TYPES_ALL

HR_OPERATING_UNITS

AR_CASH_RECEIPTS_ALL

Detailed Logic

SELECT

*
FROM
(
SELECT 1 rn,
a.current_buck,
buk1_30,
buk31_60,
buk61_90,
buk91_120,
buk121_150,
buk151_180,
buk181_210,
buk211_240,
buk241_270,
buk271_300,
buk301_330,
buk331_365,
buk_365,
buk_731,
cust_number,
invoice_number,
invoice_date,
customer,
site_name,
bill_to_site_use_id,
source_id_int_1,
cust_account_id,
past_due_days,
bu_name,
city,
attribute2,
party_id,
due_date,
customer_class_code,
sum_total_amt_rem,
account_desc,
0 unapplied_amt,
NVL (a.party_id,null) customer_name,
null DOC_SEQUENCE_VALUE
FROM (
SELECT SUM (CASE WHEN past_due_days <= 0
THEN
NVL (xx.total_amt_rem, 0)
END)
current_buck,
SUM (CASE WHEN past_due_days >= 1
AND past_due_days <= 30
THEN NVL (xx.total_amt_rem, 0)
END
)
buk1_30,
SUM (CASE WHEN past_due_days >= 31
AND past_due_days <= 60
THEN NVL (xx.total_amt_rem, 0)
END
)
buk31_60,
SUM (CASE WHEN past_due_days >= 61
AND past_due_days <= 90
THEN NVL (xx.total_amt_rem, 0)
END
)
buk61_90,
SUM (CASE WHEN past_due_days >= 91
AND past_due_days <= 120
THEN NVL (xx.total_amt_rem, 0)
END
)
buk91_120,
SUM (CASE WHEN past_due_days >= 121
AND past_due_days <= 150
THEN NVL (xx.total_amt_rem, 0)
END
)
buk121_150,
SUM (CASE WHEN past_due_days >= 151
AND past_due_days <= 180
THEN NVL (xx.total_amt_rem, 0)
END
)
buk151_180,
SUM (CASE WHEN past_due_days >= 181
AND past_due_days <= 210
THEN NVL (xx.total_amt_rem, 0)
END
)
buk181_210,
SUM (CASE WHEN past_due_days >= 211
AND past_due_days <= 240
THEN NVL (xx.total_amt_rem, 0)
END
)
buk211_240,
SUM (CASE WHEN past_due_days >= 241
AND past_due_days <= 270
THEN NVL (xx.total_amt_rem, 0)
END
)
buk241_270,
SUM (CASE WHEN past_due_days >= 271
AND past_due_days <= 300
THEN NVL (xx.total_amt_rem, 0)
END
)
buk271_300,
SUM (CASE WHEN past_due_days >= 301
AND past_due_days <= 330
THEN NVL (xx.total_amt_rem, 0)
END
)
buk301_330,
SUM (CASE WHEN past_due_days >= 331
AND past_due_days <= 365
THEN NVL (xx.total_amt_rem, 0)
END
)
buk331_365,
SUM (CASE WHEN past_due_days >= 366
AND past_due_days <= 730
THEN NVL (xx.total_amt_rem, 0)
END
)
buk_365,
SUM (CASE WHEN past_due_days >= 731
THEN NVL (xx.total_amt_rem, 0)
END
)
buk_731,
xx.cust_number,
xx.invoice_number,
xx.invoice_date,
xx.customer_name customer,
xx.site_name,
xx.bill_to_site_use_id,
source_id_int_1,
xx.cust_account_id,
past_due_days,
bu_name,
city,
attribute2,
party_id,
due_date,
xx.customer_class_code,
SUM (xx.total_amt_rem) sum_total_amt_rem,
xx.ACCOUNT_NAME account_desc
FROM (
SELECT hz_account.account_number cust_number,
'Customer Wise' report_type,
rct.trx_number invoice_number,
sch.due_date,
ent.source_id_int_1,
rct.trx_date invoice_date,
SUM (
NVL (dist.unrounded_accounted_dr, 0)
- NVL (dist.unrounded_accounted_cr, 0))
+ CASE
WHEN typ.TYPE = 'CM'
THEN
NVL ( (inv_cm.amt), 0)
ELSE
0
END
+ NVL (applied_amt, 0)
- NVL (adjst.amount, 0)
- NVL (cm.amt, 0)
+ NVL (refund.amount_applied,0)
total_amt_rem,
party.party_name customer_name,
party.party_id,
site.party_site_name site_name,
rct.bill_to_site_use_id,
NVL (rct.bill_to_customer_id,
hz_account.cust_account_id)
cust_account_id,
NVL (
(TO_DATE (TO_CHAR (:p_date_from, 'MM-DD-YYYY'),
'MM-DD-YYYY')
- TO_DATE (TO_CHAR (due_date, 'MM-DD-YYYY'),
'MM-DD-YYYY')),
0)
past_due_days,
hou.name bu_name,
hl.city,
CASE
WHEN rct.attribute2 IS NULL
THEN
rct.ct_reference
ELSE
rct.attribute2
END
attribute2,
ent.security_id_int_1 organization_id,
customer_class_code,
hz_account.ACCOUNT_NAME
FROM xla_transaction_entities ent,
xla_ae_headers h,
xla_ae_lines l,
gl_code_combinations gcc,
xla_distribution_links dist,
hz_cust_accounts hz_account,
hz_parties party,
hz_party_sites site,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
ra_customer_trx_all rct,
ra_cust_trx_types_all typ,
hr_operating_units hou,
( SELECT /*+ NO_MERGE */ aps.customer_trx_id,
MAX (aps.due_date) due_date
FROM ar_payment_schedules_all aps
GROUP BY aps.customer_trx_id) sch,
( SELECT /*+ NO_MERGE */ SUM (
NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0))
applied_amt,
rcta.customer_trx_id,
rcta.org_id
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ar_distributions_all ard,
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
ra_customer_trx_all rcta
WHERE xah.ae_header_id = xal.ae_header_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 =
ard.line_id
AND ard.source_id =
ara.receivable_application_id
AND ara.cash_receipt_id =
acr.cash_receipt_id
AND ara.applied_customer_trx_id =
rcta.customer_trx_id
AND xdl.source_distribution_type =
'AR_DISTRIBUTIONS_ALL'
AND accounting_class_code in
(
'CASH',
'CONFIRMATION',
'UNID',
'REMITTANCE',
'RECEIVABLE',
'UNAPPLIED CASH',
'EXCHANGE_GAIN_LOSS',
'REFUND'
)
AND ROUNDING_CLASS_CODE in ('RECEIVABLE')
AND ACCOUNTING_LINE_CODE not in ('RCT_REMIT')
AND xah.event_type_code IN
('RECP_UPDATE',
'RECP_CREATE',
'RECP_REVERSE',
'RECP_RATE_ADJUST')
AND (xal.accounting_date) <=
:p_date_from
GROUP BY rcta.customer_trx_id, rcta.org_id) rcpt,
( SELECT /*+ NO_MERGE */ SUM (NVL (ara.ACCTD_AMOUNT_APPLIED_FROM, 0)) amt,
rcta.customer_trx_id,
rcta.org_id
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ar_distributions_all ard,
ar_receivable_applications_all ara,
ra_customer_trx_all rcta
WHERE 1=1
AND xah.ae_header_id = xal.ae_header_id
AND xah.ae_header_id = xdl.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_id_num_1 =
ard.line_id
AND ard.source_id =
ara.receivable_application_id
AND ara.applied_customer_trx_id =
rcta.customer_trx_id
AND xal.override_reason IS NULL
AND xal.overridden_code_combination_id
IS NULL
AND xdl.source_distribution_type =
'AR_DISTRIBUTIONS_ALL'
AND accounting_class_code = 'RECEIVABLE'
AND je_category_name = 'Credit Memos'
AND xal.accounting_date <= :p_date_from
AND xdl.unrounded_accounted_dr <> 0
GROUP BY rcta.customer_trx_id, rcta.org_id) cm,
( SELECT /*+ NO_MERGE */ SUM (
NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0))
amount,
adj.customer_trx_id,
adj.org_id
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
ar_distributions_all ard,
ar_adjustments_all adj
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.event_id = xet.event_id
AND xet.event_id = xdl.event_id
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 =
ard.line_id
AND ard.source_id = adj.adjustment_id
AND xah.application_id = 222
AND xdl.source_distribution_type IN
('AR_DISTRIBUTIONS_ALL',
'MFAR_DISTRIBUTIONS_ALL')
AND ard.source_table = 'ADJ'
AND xal.accounting_class_code = 'ADJ'
AND xal.accounting_date <= :p_date_from
GROUP BY adj.customer_trx_id, adj.org_id) adjst,
( SELECT /*+ NO_MERGE */ SUM (NVL (ara.ACCTD_AMOUNT_APPLIED_FROM, 0)) amt,
xte1.source_id_int_1,
xte1.security_id_int_1
FROM xla_transaction_entities xte1,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ar_distributions_all ard,
ar_receivable_applications_all ara,
ra_customer_trx_all rcta
WHERE xte1.entity_id = xah.entity_id
AND xte1.application_id = 222
AND xte1.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.override_reason IS NULL
AND xal.overridden_code_combination_id
IS NULL
AND xdl.source_distribution_id_num_1 =
ard.line_id
AND ard.source_id =
ara.receivable_application_id
AND ara.applied_customer_trx_id = rcta.customer_trx_id
AND xte1.source_id_int_1 = ara.customer_trx_id
AND xte1.security_id_int_1 = rcta.org_id
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND accounting_class_code = 'RECEIVABLE'
AND je_category_name = 'Credit Memos'
AND TRIM (xal.description) LIKE
'%Credit Memo Application'
AND xal.accounting_date <= :p_date_from
GROUP BY xte1.source_id_int_1,
xte1.security_id_int_1) inv_cm,
(SELECT DISTINCT
SUM(NVL(amount_applied,0)) amount_applied,
cash_receipt_id,
set_of_books_id,
applied_customer_id,
trx_number,
application_ref_id
FROM (
SELECT
SUM(NVL(acctd_amount_applied_from,0)

) amount_applied,
acr.customer_trx_id cash_receipt_id,
araa.set_of_books_id,
acr.bill_to_customer_id applied_customer_id,
acr.trx_number trx_number,
araa.event_id,
araa.receivable_application_id,
araa.application_ref_id
FROM ar_receivable_applications_all araa
,ra_customer_trx_all acr
WHERE 1=1
AND araa.STATUS = 'ACTIVITY'
AND araa.application_type IN ('CM')
AND araa.APPLIED_PAYMENT_SCHEDULE_ID = -8
AND araa.APPLICATION_REF_TYPE = 'AP_REFUND_REQUEST'
AND araa.display = 'Y'
AND araa.customer_trx_id = acr.customer_trx_id
AND araa.gl_date <= :p_date_from
AND araa.set_of_books_id = acr.set_of_books_id
GROUP BY acr.customer_trx_id,araa.set_of_books_id,
acr.bill_to_customer_id,
acr.trx_number,
araa.event_id,
araa.receivable_application_id,
araa.application_ref_id
)
GROUP BY cash_receipt_id, set_of_books_id,applied_customer_id, trx_number,application_ref_id-- , event_id, receivable_application_id
) refund
WHERE ent.entity_id = h.entity_id
AND h.ae_header_id = l.ae_header_id
AND l.ae_header_id = dist.ae_header_id
AND l.ae_line_num = dist.ae_line_num
AND h.application_id = l.application_id
AND hz_account.cust_account_id = l.party_id
AND party.party_id = hz_account.party_id
AND rct.BILL_TO_CUSTOMER_ID=hz_account.cust_account_id
AND hl.location_id = site.location_id
AND site.party_id = site.party_id
AND site.party_site_id(+) = hcas.party_site_id
AND hcsu.cust_acct_site_id =
hcas.cust_acct_site_id(+)
AND hou.organization_id = ent.security_id_int_1
AND rct.customer_trx_id = ent.source_id_int_1
AND ent.source_id_int_1 = refund.cash_receipt_id(+)
AND rct.org_id = ent.security_id_int_1
AND hcsu.site_use_id = rct.bill_to_site_use_id
AND sch.customer_trx_id = ent.source_id_int_1
AND typ.cust_trx_type_seq_id =
rct.cust_trx_type_seq_id
AND ent.source_id_int_1 = rcpt.customer_trx_id(+)
AND ent.security_id_int_1 = rcpt.org_id(+)
AND ent.source_id_int_1 = cm.customer_trx_id(+)
AND ent.security_id_int_1 = cm.org_id(+)
AND ent.source_id_int_1 = adjst.customer_trx_id(+)
AND ent.security_id_int_1 = adjst.org_id(+)
AND ent.source_id_int_1 = inv_cm.source_id_int_1(+)
AND ent.security_id_int_1 =
inv_cm.security_id_int_1(+)
AND hcsu.site_use_code = 'BILL_TO'
AND h.balance_type_code = 'A'
AND l.party_type_code = 'C'
AND h.event_type_code <> 'ADJ_CREATE'
AND l.accounting_class_code = 'RECEIVABLE'
and dist.SOURCE_DISTRIBUTION_TYPE='RA_CUST_TRX_LINE_GL_DIST_ALL'
AND h.application_id = 222
AND ((party.party_id IN (:p_customer_name)) OR ('All' IN (:p_customer_name||'All')))
AND l.code_combination_id = gcc.code_combination_id
AND l.accounting_date <= :p_date_from
GROUP BY rct.trx_number,
rct.trx_date,
party.party_id,
party.party_name,
due_date,
rct.bill_to_customer_id,
typ.TYPE,
rcpt.applied_amt,
cm.amt,
adjst.amount,
inv_cm.amt,
ent.source_id_int_1,
ent.security_id_int_1,
gcc.chart_of_accounts_id,
hz_account.customer_class_code,
hz_account.account_number,
hl.city,
rct.ct_reference,
site.party_site_name,
hou.name,
rct.attribute2,
rct.bill_to_site_use_id,
hz_account.cust_account_id,
hz_account.ACCOUNT_NAME,
NVL (refund.amount_applied,0)
) xx
WHERE 1=1
AND ((organization_id IN (:p_bu_id)) OR ('All' IN (:p_bu_id||'All')))
AND ((xx.party_id IN (:p_customer_name)) or ('All' IN (:p_customer_name||'All')))
GROUP BY xx.cust_number,
xx.invoice_number,
xx.invoice_date,
xx.customer_name,
xx.party_id,
xx.site_name,
bu_name,
city,
attribute2,
due_date,
past_due_days,
xx.bill_to_site_use_id,
xx.cust_account_id,
source_id_int_1,
xx.customer_class_code,
xx.account_name
having SUM (xx.total_amt_rem) <> 0
) a
UNION ALL
SELECT 2 rn,
0 current_buck,
0 buk1_30,
0 buk31_60,
0 buk61_90,
0 buk91_120,
0 buk121_150,
0 buk151_180,
0 buk181_210,
0 buk211_240,
0 buk241_270,
0 buk271_300,
0 buk301_330,
0 buk331_365,
0 buk_365,
0 buk_731,
account_number cust_number,
receipt_number invoice_number,
receipt_date invoice_date,
party_name customer,
site_name,
-999 bill_to_site_use_id,
-999 security_id_int_1,
cust_account_id,
0 past_due_days,
bu_name,
null city,
null attribute2,
null party_id,
null due_date,
customer_class_code,
0 sum_total_amt_rem,
null account_desc,
ABS(sum(NVL(unapplied_amt,0))) unapplied_amt ,
party_id customer_name,
DOC_SEQUENCE_VALUE
FROM ( SELECT -1*(SUM((NVL (xel.accounted_dr, 0)) -
(NVL (xel.accounted_cr, 0))))
unapplied_amt,
acct.party_id,
acct.account_number,
acr.receipt_number,
acr.receipt_date,
hp.party_name,
null site_name,
hou.name bu_name,
acr.DOC_SEQUENCE_VALUE,
customer_class_code,
acct.cust_account_id
FROM xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xel,
gl_code_combinations gcc,
ar_cash_receipts_all acr,
hz_cust_accounts acct,
hz_parties hp,
hr_operating_units hou
WHERE 1=1
AND hp.party_id=acct.party_id
AND hou.organization_id = xte.security_id_int_1
AND xah.ae_header_id = xel.ae_header_id
AND xte.entity_id = xah.entity_id
AND xte.security_id_int_1 = acr.org_id
AND xte.application_id = 222
AND xah.application_id = xte.application_id
AND acr.cash_receipt_id = xte.source_id_int_1
AND gcc.code_combination_id = xel.code_combination_id
AND acr.pay_from_customer = acct.cust_account_id
AND event_type_code IN
('RECP_CREATE',
'RECP_REVERSE',
'RECP_UPDATE',
'RECP_RATE_ADJUST')
AND acr.STATUS NOT IN ('REV','CC_CHARGEBACK_REV')
AND accounting_class_code IN
('CASH',
'CONFIRMATION',
'UNID',
'REMITTANCE',
'RECEIVABLE',
'UNAPPLIED CASH',
'EXCHANGE_GAIN_LOSS',
'REFUND'
)
AND xel.accounting_date <= :p_date_from
AND ((xte.security_id_int_1 IN (:p_bu_id)) OR ('All' IN (:p_bu_id||'All')))
AND ((acct.party_id IN (:p_customer_name)) or ('All' IN (:p_customer_name||'All')))
AND ((acct.customer_class_code IN (:p_customer_type)) OR ('All' IN (:p_customer_type||'All')))
GROUP BY acct.party_id,
xel.party_site_id,
acct.account_number,
acr.receipt_number,
acr.receipt_date,
hp.party_name,
customer_class_code,
hou.name,
acr.DOC_SEQUENCE_VALUE,
acct.cust_account_id
) b
WHERE 1=1
group by account_number ,
receipt_number ,
receipt_date ,
cust_account_id,
party_name ,
site_name,
bu_name,
party_id ,
customer_class_code,
DOC_SEQUENCE_VALUE
having sum(NVL(unapplied_amt,0))<>0
) aa
ORDER BY rn,
aa.customer,
aa.cust_number,
aa.site_name,
aa.invoice_date;

Pages

Customer Aging on Subledger

Customer Aging

Tables used in query development

Detailed Logic