[sourcecode language='sql' padlinenumbers='true']
Select supplier_number, vendor_name,
sum(amount_remaining) amount_remaining,
sum(b0) "<=30",
sum(b1) "31-60",
sum(b2) "61-90",
sum(b3) "91-180",
sum(b4) "181-270",
sum(b5) "271-360",
sum(b6) ">360"
FROM(
SELECT i.invoice_date,
round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3) amount_remaining,
i.vendor_id, supp.vendor_name, supp.segment1 supplier_number,
CASE
when trunc(trunc(sysdate))-i.invoice_date <= 30 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b0
,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 31 AND 60 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b1,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 61 AND 90 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b2,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 91 AND 180 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b3 ,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 181 AND 270 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b4
,
CASE
when trunc(sysdate)-i.invoice_date BETWEEN 271 AND 360 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b5 ,
CASE
when trunc(sysdate)-i.invoice_date > 360 then
NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0)
END b6
FROM ap_payment_schedules ps, ap_invoices i, ap_suppliers supp
WHERE i.invoice_id = ps.invoice_id
AND ps.org_id = :P_ORG_ID -- Security takes care of this part, only for other reporting reqs
AND i.vendor_id = supp.vendor_id
AND i.cancelled_date IS NULL
AND ps.amount_remaining <> 0
)
GROUP BY supplier_number, vendor_name
ORDER BY vendor_name
[/sourcecode]
And as usual, the main view is a protected repository, hence you have to initialize the security part in order to fetch data
[sourcecode language='sql' ]
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;
begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50854, 200);
end;
begin
MO_GLOBAL.INIT('SQLAP'); --Payables
--MO_GLOBAL.INIT('PO');
end;
[/sourcecode]