Overview:-
In this post , We will discuss about AP aging report query in oracle apps. The AP aging report helps us to identify the creditor aging which is due in terms of the Due Date. In other words AP aging helps to divide our current outstanding of our suppliers in terms of the Days or Buckets.
Query:-
SELECT
org_name,
vendor_name,
vendor_number,
vendor_site_details,
invoice_number,
invoice_date,
gl_date,
invoice_type,
due_date,
past_due_days,
amount_remaining,
CASE
WHEN past_due_days >=-999
AND past_due_days < 0 THEN amount_remaining
ELSE 0
END
current_bucket,
CASE
WHEN past_due_days >= 0
AND past_due_days <= 30 THEN amount_remaining
ELSE 0
END
“0 to 30”,
CASE
WHEN past_due_days > 30
AND past_due_days <= 60 THEN amount_remaining
ELSE 0
END
“31 to 60”,
CASE
WHEN past_due_days > 60
AND past_due_days <= 90 THEN amount_remaining
ELSE 0
END
“61 to 90”,
CASE
WHEN past_due_days > 90
AND past_due_days <= 120 THEN amount_remaining
ELSE 0
END
“61 to 120″,
CASE
WHEN past_due_days > 120
AND past_due_days <= 999999 THEN amount_remaining
ELSE 0
END
” More than 120 Days”
FROM
(
SELECT
hou.name org_name,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pvs.vendor_site_code
|| ‘ ‘
|| pvs.city
|| ‘ ‘
|| state vendor_site_details,
i.invoice_num invoice_number,
i.payment_status_flag,
i.invoice_type_lookup_code invoice_type,
i.invoice_date invoice_date,
i.gl_date gl_date,
ps.due_date due_date,
ps.amount_remaining,
( ceil(SYSDATE – ps.due_date) ) past_due_days, — DAYS_DUE,
DECODE(i.invoice_currency_code,’USD’,DECODE(0,0,round( ( (nvl(ps.amount_remaining,0) / (nvl(i.payment_cross_rate,1) ) ) * nvl(i.exchange_rate
,1) ),2),round( ( (nvl(ps.amount_remaining,0) / (nvl(i.payment_cross_rate,1) ) ) * nvl(i.exchange_rate,1) ) / 0) * 0),DECODE(i.exchange_rate,NULL
,0,DECODE(0,0,round( ( (nvl(ps.amount_remaining,0) / (nvl(ps.payment_cross_rate,1) ) ) * nvl(i.exchange_rate,1) ),2),round( ( (nvl(ps.amount_remaining
,0) / (nvl(i.payment_cross_rate,1) ) ) * nvl(i.exchange_rate,1) ) / 0) * 0) ) ) amt_due_remaining
FROM
ap_payment_schedules_all ps,
ap_invoices_all i,
ap_suppliers pv,
ap_supplier_sites_all pvs,
hr_operating_units hou
WHERE
i.invoice_id = ps.invoice_id
AND i.vendor_id = pv.vendor_id
AND i.vendor_site_id = pvs.vendor_site_id
AND i.org_id = hou.organization_id
AND i.cancelled_date IS NULL
AND ( nvl(ps.amount_remaining,0) * nvl(i.exchange_rate,1) ) != 0
AND i.payment_status_flag IN (
‘N’,
‘P’
)
AND i.org_id =:p_org_id
)
ORDER BY
vendor_number,
invoice_number
