AR AGING REPORT

select customer_number, customer_name,
paymt,
Day_030 ,
Day_031_060,
Day_061_090,
Day_091_120,
Day_121_150,
Day_151_180,
Day_181,
no_ofTrx
from
(select pay.org_id,pay.customer_id
--        ,pay.trx_number, pay.trx_date
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) <=30   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id)  end ) ) Day_030
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 31  and 60   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_031_060   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 61  and 90   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_061_090
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 91  and 120  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_091_120   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 121 and 150  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_121_150
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 151 and 180  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_151_180   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) >= 181               then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_181
,count(*) no_ofTrx
,''
from       apps.ar_payment_Schedules_all pay
where  pay.class in ('XX','YY','ZZ') and pay.org_id = &3
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
and exists   ( select 'x' from  apps.gl_code_combinations gl
,apps.ra_cust_trx_line_gl_dist_all distgl
where  (trim (('&2')) ='0' or gl.segment4 in ('&2'))   --gl.segment4 in ('&2')
and gl.CODE_COMBINATION_ID =  distgl.CODE_COMBINATION_ID
and distgl.ACCOUNT_CLASS ='REC'
and distgl.CUSTOMER_TRX_ID  =pay.CUSTOMER_TRX_ID
)           
group by pay.org_id,pay.customer_id
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
)  invag ,
(select  pay.org_id,pay.customer_id, sum(receiptL.AMOUNT_APPLIED) paymt
from  apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where  pay.class ='PMT' and receiptl.status in ('ACC','UNAPP') and pay.org_id =&3
and   pay.PAYMENT_SCHEDULE_ID =     receiptL.PAYMENT_SCHEDULE_ID
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
group by pay.org_id,pay.customer_id
) payment,
apps.ra_customers cust
where cust.customer_id =  invag.customer_id (+)
and   cust.customer_id =  payment.customer_id (+)
and  (invag.org_id is not null or payment.org_id is not null)
/