1 Reply Latest reply on Sep 14, 2015 4:13 PM by Srini Chavali-Oracle

    Customer aging query

    user615979

      HI,

       

        Working in EBS

        Version ; 12.1.3

       

      i am trying to build customer aging query for those customers whose transactions are not done for  "Inactive Past Due Customer Over 9 Months"

       

      Please i need help to resolve

       

      select * from

      (

      select rac_customer_number

      ,rac_customer_name

      ,customer_id

      ,nvl(sum(amount_due_remaining),0) remaining_amount

      ,nvl(sum(acurrent),0) current_buck

      ,nvl(sum(days30) ,0)  buck1

      ,nvl(sum(days60),0) buck2

      ,nvl(sum(days90),0) buck3

      ,nvl(sum(days180),0) buck4

      ,nvl(sum(days365),0) buck5

      ,nvl(sum(days365_grt),0) buck6

      from

      (

      select rac_customer_number

      ,rac_customer_name

      ,customer_id

      ,amount_due_remaining

      ,case when(to_date(:p_as_of_date,'DD-MON-RRRR')-due_date)< 1 then amount_due_remaining else 0 end  acurrent

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) between 1 and 30 then amount_due_remaining else 0 end days30

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) between 31 and 60 then amount_due_remaining else 0 end days60

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) between 61 and 90 then amount_due_remaining else 0 end days90

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) between 91 and 180 then amount_due_remaining else 0 end days180

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) between 181 and 365 then amount_due_remaining else 0 end days365

      ,case when (to_date(:p_as_of_date,'DD-MON-RRRR')-due_date) >365 then amount_due_remaining else 0 end days365_grt

      from

      (

      select rac_customer_number

      ,rac_customer_name

      ,aps.trx_number

      ,gl_date

      ,amount_due_remaining

      ,due_date

      ,customer_id

      from ar_payment_schedules_v aps

      where gl_date <= nvl(:p_as_of_date,trunc(sysdate))

      )

      group by rac_customer_number,rac_customer_name, customer_id

      )

      where current_buck = 0

      and buck1 = 0

      and buck2 = 0

      and buck3 = 0

      and buck4 = 0

      and buck5 <> 0

      and buck6 = 0

       

      Regards