AR Aging Report (Query)

AR Aging Report

select decode(&3,2,'ABC OPERATING UNIT',5,'XYZ OPERATING UNIT') FROM DUAL
/

set head on   
   
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)   
/