Sales Collection Analysis Report Query (Aging Style)

SELECT
sal_per_name,
sb0,
sb1,
sb2,
sb3,
sb4,
sb5,
sb6,
(sb0+sb1+sb2+sb3+sb4+sb5+sb6)g_t
from(
select
sal_per_name,
sum(b0) sb0,
sum(b1) sb1,
sum(b2) sb2,
sum(b3) sb3,
sum(b4) sb4,
sum(b5) sb5,
sum(b6) sb6
from
(select
sal_per_name,
case
when days_past_due between 0 and 30 then amt else 0 end b0,
case
when days_past_due between 31 and 60 then amt else 0 end b1,
case
when days_past_due between 61 and 90 then amt else 0 end b2,
case
when days_past_due between 91 and 120 then amt else 0 end b3,
case
when days_past_due between 121 and 180 then amt else 0 end b4,
case
when days_past_due between 181 and 365 then amt else 0 end b5,
case
when days_past_due between 366 and 9999 then amt else 0 end b6
from
(
select
sal_per_name ,
amt,
days_past_due
from
(
select distinct
nvl( jre.resource_name,arpv.attribute2) sal_per_name, arpv.receipt_number,
arpv.amount_applied amt,
case
when ceil(sysdate-ps.due_date)>0 then ceil(sysdate-ps.due_date) else 0  end days_past_due
from
hr_operating_units hou,
ar_cash_receipts_all acra,
ar_payment_schedules_all ps,
ar_receivable_applications_v arpv,
ra_customer_trx_all rct,
ra_cust_trx_line_salesreps_all rcsa,
ra_salesreps_all rsa,
jtf_rs_resource_extns_vl jre
where
acra.cash_receipt_id=arpv.cash_receipt_id
and arpv.trx_number=rct.trx_number
and rct.customer_trx_id=rcsa.customer_trx_id
and rcsa.salesrep_id=rsa.salesrep_id
and rsa.resource_id=jre.resource_id
and acra.status='APP'
 and ps.trx_number = arpv.trx_number
and hou.organization_id=acra.org_id
and hou.organization_id=nvl(:xx_org_id,hou.organization_id)
and trunc(acra.receipt_date) between nvl(:date_from,trunc(acra.receipt_date)) and nvl(:date_to,trunc(acra.receipt_date))
union
select distinct
nvl(jre.resource_name,acra.attribute1) sal_per_name ,acra.receipt_number,
 acra.applied_amount amt,
case
when ceil(to_date(sysdate)-ps.due_date)>0 then ceil(to_date(sysdate)-ps.due_date) else 0  end days_past_due
from
ar_cash_receipts_v acra,ar_payment_schedules_all ps,jtf_rs_resource_extns_vl jre,ra_salesreps_all rsa,ra_cust_trx_line_salesreps_all rcsa,
ra_customer_trx_all rct,ar_receivable_applications_v arpv
where
 acra.receipt_status='UNAPP'
 and acra.org_id = nvl(:xx_org_id,acra.org_id)
 and ps.trx_number = arpv.trx_number
and trunc(acra.receipt_date ) between nvl(:date_from,trunc(acra.receipt_date)) and nvl(:date_to,trunc(acra.receipt_date))
and acra.applied_amount <> 0
and rsa.resource_id=jre.resource_id
and rcsa.salesrep_id=rsa.salesrep_id
and rct.customer_trx_id=rcsa.customer_trx_id
and acra.cash_receipt_id=arpv.cash_receipt_id
and arpv.trx_number=rct.trx_number
))
)
group by sal_per_name)