Oracle ERP Tech Blogs: Supplier Aging Query based on Subledger

Supplier Aging Query based on Subledger

Aging shows outstanding amount of suppliers and this amount is further showing in buckets. Bucket is basically created on business requirement i.e. 1 day - 30 days, 31 days - 60 days and so on.

Supplier Aging will display details of suppliers which have outstanding amount till as on date. As on date is compare with subledger accounting date.

Subledger tables used

xla_ae_headers
xla_ae_lines
xla_transaction_entities
xla_distribution_links

Detailed Query

SELECT org_name,
vendor_name,
vendor_number,
vendor_site_code,
a.ledger_id,
a.invoice_number,
i.terms_date invoice_date,
a.invoice_type,
due_date,
past_due_days,
amt_due_remaining,
i.invoice_amount * NVL (i.exchange_rate, 1) invoice_amount,
CASE WHEN past_due_days < 0 THEN amt_due_remaining ELSE 0 END not_due,
CASE
WHEN past_due_days >= 0 AND past_due_days <= 30
THEN
amt_due_remaining
ELSE
0
END
bucket_0_30,
CASE
WHEN past_due_days > 30 AND past_due_days <= 60
THEN
amt_due_remaining
ELSE
0
END
bucket_31_60,
CASE
WHEN past_due_days > 60 AND past_due_days <= 90
THEN
amt_due_remaining
ELSE
0
END
bucket_61_90,
CASE
WHEN past_due_days > 90 AND past_due_days <= 120
THEN
amt_due_remaining
ELSE
0
END
bucket_91_120,
CASE
WHEN past_due_days > 120 AND past_due_days <= 150
THEN
amt_due_remaining
ELSE
0
END
bucket_121_150,
CASE
WHEN past_due_days > 150 AND past_due_days <= 180
THEN
amt_due_remaining
ELSE
0
END
bucket_151_180,
CASE
WHEN past_due_days > 180 AND past_due_days <= 365
THEN
amt_due_remaining
ELSE
0
END
bucket_181_365,
CASE
WHEN past_due_days > 365 AND past_due_days <= 730
THEN
amt_due_remaining
ELSE
0
END
greater_than_365,
CASE
WHEN past_due_days > 730 AND past_due_days <= 999999
THEN
amt_due_remaining
ELSE
0
END
greater_than_730,
a.name,
a.vendor_type_lookup_code,
i.invoice_amount inv_amount,
i.invoice_currency_code inv_currency
FROM ( SELECT /*+parallel */
hou.name org_name,
ai.invoice_id,
supplier.vendor_name vendor_name,
supplier.segment1 vendor_number,
sites.vendor_site_code,
h.ledger_id,
ent.transaction_number invoice_number,
SUM (
NVL (dist.unrounded_accounted_cr, 0)
- NVL (dist.unrounded_accounted_dr, 0))
invoice_amount,
sch.due_date,
ai.terms_date invoice_date,
ai.invoice_type_lookup_code invoice_type,
NVL (
( TO_DATE (TO_CHAR (:p_end_date, 'MM-DD-YYYY'),
'MM-DD-YYYY')
- TO_DATE (TO_CHAR (due_date, 'MM-DD-YYYY'), 'MM-DD-YYYY')),
0)
past_due_days,
SUM (
NVL (dist.unrounded_accounted_cr, 0)
- NVL (dist.unrounded_accounted_dr, 0))
- NVL (abc.amount_cd, 0)
amt_due_remaining,
hou.name,
supplier.vendor_type_lookup_code
FROM xla_ae_headers h,
xla_ae_lines l,
xla_transaction_entities ent,
xla_distribution_links dist,
poz_suppliers_v supplier,
poz_supplier_sites_v sites,
( SELECT invoice_id, MAX (due_date) due_date
FROM ap_payment_schedules_all sch
GROUP BY invoice_id) sch,
ap_invoices ai,
hr_operating_units hou,
( SELECT SUM (
NVL (dist1.unrounded_accounted_cr, 0)
- NVL (dist1.unrounded_accounted_dr, 0))
amount_cd,
dist1.applied_to_source_id_num_1
FROM xla_ae_headers h1,
xla_ae_lines l1,
xla_transaction_entities ent1,
xla_distribution_links dist1
WHERE h1.ae_header_id = l1.ae_header_id
AND l1.ae_header_id = dist1.ae_header_id
AND l1.ae_line_num = dist1.ae_line_num
AND ent1.entity_id = h1.entity_id
AND h1.application_id = ent1.application_id
AND h1.application_id = l1.application_id
AND h1.application_id = 200
AND h1.balance_type_code = 'A'
AND h1.gl_transfer_status_code = 'Y'
AND l1.accounting_class_code != 'LIABILITY'
AND ent1.entity_code = 'AP_PAYMENTS'
AND TRUNC (l1.accounting_date) <= :p_end_date
GROUP BY dist1.applied_to_source_id_num_1) abc
WHERE abc.applied_to_source_id_num_1(+) = ent.source_id_int_1
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 ent.entity_id = h.entity_id
AND h.application_id = ent.application_id
AND h.application_id = l.application_id
AND ent.source_id_int_1 = sch.invoice_id
AND ai.invoice_id = ent.source_id_int_1
AND ai.org_id = hou.organization_id
AND h.application_id = 200
AND h.balance_type_code = 'A'
AND h.gl_transfer_status_code = 'Y'
AND l.accounting_class_code = 'LIABILITY'
AND l.party_type_code = 'S'
AND l.party_id = supplier.vendor_id
AND sites.vendor_site_id = l.party_site_id
AND sites.prc_bu_id = ent.security_id_int_1
AND ent.entity_code = 'AP_INVOICES'
AND supplier.vendor_id =
NVL (:p_party_name, supplier.vendor_id)
AND sites.vendor_site_code =
NVL (:p_supp_site, sites.vendor_site_code)
AND TRUNC (l.accounting_date) <= :p_end_date
AND hou.organization_id =
NVL (:p_bu_name, hou.organization_id)
AND nvl(supplier.vendor_type_lookup_code,'N' )=
NVL (:p_vendor_type, nvl(supplier.vendor_type_lookup_code,'N'))
GROUP BY hou.name,
supplier.vendor_name,
supplier.segment1,
sites.vendor_site_code,
ent.transaction_number,
sch.due_date,
ai.terms_date,
ai.invoice_type_lookup_code,
due_date,
ent.source_id_int_1,
ai.invoice_id,
h.ledger_id,
supplier.vendor_type_lookup_code,
abc.amount_cd) a,
ap_invoices i
WHERE i.invoice_id = a.invoice_id
AND NVL(amt_due_remaining,0) <> 0
ORDER BY i.terms_date;

Pages

Supplier Aging Query based on Subledger

Supplier Aging Query based on Subledger

Subledger tables used