SELECT org_name,
vendor_name,
vendor_number,
vendor_site_details,
invoice_number,
invoice_date,
gl_Date,
invoice_type,
due_date,
past_due_days,
amt_due_remaining,
CASE
WHEN past_due_days >= -999 AND past_due_days < 0 THEN
amt_due_remaining
ELSE
0
END CURRENT_BUCKET,
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 <= 999999 THEN
amt_due_remaining
ELSE
0
END GREATER_THAN_120
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,
(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,
ap_lookup_codes alc1,
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 ps.amount_remaining = 0
AND (NVL(ps.amount_remaining, 0) * NVL(i.exchange_rate, 1)) != 0
AND i.payment_status_flag IN ('N', 'P')
AND alc1.lookup_type(+) = 'INVOICE TYPE'
AND alc1.lookup_code(+) = i.invoice_type_lookup_code
--and i.INVOICE_NUM ='358908411'
AND ap_invoices_pkg.get_approval_status(i.invoice_id,
i.invoice_amount,
ps.payment_status_flag,
invoice_type_lookup_code) in
('APPROVED', 'NEEDS REAPPROVAL'))
-- AND i.org_id = fnd_profile.VALUE ('ORG_ID'))
ORDER BY 2, 6;
