5 Replies Latest reply: Mar 30, 2013 1:01 PM by Etbin RSS

    Aging Query show Previous Bucket

    user615979
      Hi All,

      Working ERP Version 11.5.10.2


      I need some help, in my query to show previous bucket from as of date.

      please see the below query

      select TRX_NUMBER,salesper_name, customer_name, customer_number,amount_due_original,
      amount_due_remaining,gl_date,amount_remaining, SALESREP_ID , due_date,

      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) < 1 THEN amount_remaining ELSE 0 END ) current_buck,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 1 AND 30 THEN amount_remaining ELSE 0 END ) buck1,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 31 AND 60 THEN amount_remaining ELSE 0 END ) buck2,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 61 AND 90 THEN amount_remaining ELSE 0 END ) buck3,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 91 AND 180 THEN amount_remaining ELSE 0 END ) buck4,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 181 AND 270 THEN amount_remaining ELSE 0 END ) buck6,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) BETWEEN 271 AND 360 THEN amount_remaining ELSE 0 END ) buck5,
      SUM( CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)-due_date,0) >= 361 THEN amount_remaining ELSE 0 END ) buck7
      from (SELECT aps.TRX_NUMBER, rs.NAME salesper_name,
      ac.customer_name, ac.customer_number, aps.amount_due_original,
      aps.amount_due_remaining, aps.gl_date,
      --SUM (  aps.amount_due_remaining
      SUM (decode(aps.invoice_currency_code,'SAR',aps.amount_due_remaining,(aps.amount_due_remaining*aps.exchange_rate))
      + (SELECT nvl(SUM (NVL (arav.AMOUNT_APPLIED, 0)),0)
      FROM ar_receivable_applications_v arav
      WHERE arav.GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate)
      AND arav.TRX_NUMBER= aps.TRX_NUMBER
      AND aps.CLASS = 'INV'
      AND arav.CUSTOMER_ID= aps.CUSTOMER_ID
      )
      + (select nvl(SUM (NVL (acma.AMOUNT_APPLIED_BASE , 0)),0) from ARBV_CREDIT_MEMO_APPLICATIONS acma, ra_customer_trx_partial_v rctpv1
      where acma.APPLIED_PAYMENT_SCHEDULE_ID=aps.PAYMENT_SCHEDULE_ID
      and rctpv1.CUSTOMER_TRX_ID=acma.CUSTOMER_TRX_ID
      and rctpv1.GD_GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate) )
      +(select nvl(SUM(adj.amount),0) -- for manuval credit note
      from ar_app_adj_v adj , ra_customer_trx_all rac
      where adj.customer_trx_id = aps.CUSTOMER_TRX_ID
      and adj.GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate)
      and adj.ps_class in ('CM','DM')
      and aps.customer_trx_id = rac.customer_trx_id
      and rac.trx_number not in('706575','PO19793/INV#25013#1') )
      + (SELECT nvl(SUM (NVL (arav.amount_applied, 0))*-1,0)
      FROM ar_receivable_applications_v arav
      WHERE arav.GL_DATE > TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')
      AND arav.CUSTOMER_ID =aps.CUSTOMER_ID
      AND arav.CASH_RECEIPT_ID =aps.CASH_RECEIPT_ID
      AND arav.RECEIPT_NUMBER=aps.TRX_NUMBER
      AND aps.CLASS = 'PMT'
      AND aps.GL_DATE_CLOSED > TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')
      )) amount_remaining
      ,RS.SALESREP_ID
      ,aps.due_date
      FROM ar_payment_schedules aps, ra_customers ac,
      ar_lookups look,
      ar_lookups look_status,
      hz_cust_accounts cust,
      hz_parties party,
      hz_cust_site_uses site_uses,
      hz_cust_acct_sites acct_site,
      hz_party_sites party_site,
      hz_locations loc,
      ra_salesreps rs
      WHERE aps.gl_date <=
      NVL (TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS'), SYSDATE)
      AND cust.cust_account_id = acct_site.cust_account_id
      AND cust.party_id = party.party_id
      AND acct_site.party_site_id = party_site.party_site_id(+)
      AND loc.location_id(+) = party_site.location_id
      AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
      AND look.lookup_type(+) = 'SITE_USE_CODE'
      AND look.lookup_code(+) = site_uses.site_use_code
      AND look_status.lookup_type(+) = 'CODE_STATUS'
      AND look_status.lookup_code(+) = NVL (cust.status, 'A')
      AND SUBSTRB (look.meaning, 1, 8)= 'Bill To'
      AND site_uses.PRIMARY_SALESREP_ID=rs.SALESREP_ID
      AND rs.NAME = nvl(:p_sale_per,rs.NAME)
      AND ac.CUSTOMER_NUMBER=cust.ACCOUNT_NUMBER
      AND aps.CUSTOMER_ID=ac.CUSTOMER_ID
      AND aps.CUSTOMER_SITE_USE_ID=site_uses.SITE_USE_ID
      GROUP BY rs.NAME,ac.customer_name,
      ac.customer_number,
      aps.amount_due_original,
      aps.amount_due_remaining,
      aps.gl_date,
      aps.trx_number
      ,rs.SALESREP_ID , aps.due_date
      ORDER BY rs.NAME)
      group by TRX_NUMBER, salesper_name,customer_name, customer_number,amount_due_original,
      amount_due_remaining,gl_date,amount_remaining, SALESREP_ID, due_date

      Regards
        • 1. Re: Aging Query show Previous Bucket
          Ramin Hashimzadeh
          What is the bucket? What do you want? ))) show samle data and result which should be on query execute.
          • 2. Re: Aging Query show Previous Bucket
            user615979
            i need to show previous bucket from as of date

            for example

            if i run the report from 31-dec 2012 then previous bucket should calculate the back 30 days

            i think should be like this

            parameter :p_to_date - 30 days(back days) - due_date

            but i am confused how to implement in the query

            just to add one bucket on top of current bucket

            i need some help
            • 3. Re: Aging Query show Previous Bucket
              Ramin Hashimzadeh
              plz, show your sample data and output , for example
              i have data :
              A,B,C
              1,2,3
              4,5,6
              7,8,9
              result must be :
              A,B,C
              7,8,9
              ok?
              • 4. Re: Aging Query show Previous Bucket
                user615979
                for example

                the outstanding means amount_remaining

                the outstanding amount is 11,500 for as of date 31-DEC-2012

                then what was the amount for 01-DEC-2012 30 days back

                Edited by: user615979 on Mar 30, 2013 12:35 AM
                • 5. Re: Aging Query show Previous Bucket
                  Etbin
                  Maybe
                  select TRX_NUMBER,salesper_name,customer_name,customer_number,amount_due_original,
                         amount_due_remaining,gl_date,amount_remaining,SALESREP_ID,due_date,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) < -30
                                  THEN amount_remaining
                                  ELSE 0
                             END) previous_buck,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 0 AND -30
                                  THEN amount_remaining
                                  ELSE 0
                             END) current_buck,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 1 AND 30
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck1,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 31 AND 60
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck2,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 61 AND 90
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck3,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 91 AND 180
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck4,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 181 AND 270
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck6,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) BETWEEN 271 AND 360
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck5,
                         SUM(CASE WHEN ROUND(nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),SYSDATE) - due_date,0) >= 361
                                  THEN amount_remaining
                                  ELSE 0
                             END) buck7
                    from (SELECT aps.TRX_NUMBER,rs.NAME salesper_name,ac.customer_name, ac.customer_number, aps.amount_due_original,
                                 aps.amount_due_remaining, aps.gl_date,
                                 SUM(decode(aps.invoice_currency_code,'SAR',aps.amount_due_remaining,(aps.amount_due_remaining * aps.exchange_rate)) +
                                     (SELECT nvl(SUM(NVL(arav.AMOUNT_APPLIED,0)),0)
                                        FROM ar_receivable_applications_v arav
                                       WHERE arav.GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate)
                                         AND arav.TRX_NUMBER = aps.TRX_NUMBER
                                         AND aps.CLASS = 'INV'
                                         AND arav.CUSTOMER_ID = aps.CUSTOMER_ID
                                     ) + 
                                     (select nvl(SUM(NVL(acma.AMOUNT_APPLIED_BASE,0)),0)
                                        from ARBV_CREDIT_MEMO_APPLICATIONS acma,
                                             ra_customer_trx_partial_v rctpv1
                                       where acma.APPLIED_PAYMENT_SCHEDULE_ID=aps.PAYMENT_SCHEDULE_ID
                                         and rctpv1.CUSTOMER_TRX_ID = acma.CUSTOMER_TRX_ID
                                         and rctpv1.GD_GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate)
                                     ) + 
                                     (select nvl(SUM(adj.amount),0) -- for manuval credit note 
                                        from ar_app_adj_v adj,
                                             ra_customer_trx_all rac
                                       where adj.customer_trx_id = aps.CUSTOMER_TRX_ID 
                                         and adj.GL_DATE > nvl(to_date(:P_TO_DATE,'RRRR/MM/DD HH24:MI:SS'),sysdate) 
                                         and adj.ps_class in ('CM','DM') 
                                         and aps.customer_trx_id = rac.customer_trx_id 
                                         and rac.trx_number not in('706575','PO19793/INV#25013#1')
                                     ) +
                                     (SELECT nvl(SUM(NVL(arav.amount_applied, 0))*-1,0)
                                        FROM ar_receivable_applications_v arav
                                       WHERE arav.GL_DATE > TO_DATE(:p_to_date,'RRRR/MM/DD HH24:MI:SS')
                                         AND arav.CUSTOMER_ID = aps.CUSTOMER_ID
                                         AND arav.CASH_RECEIPT_ID = aps.CASH_RECEIPT_ID
                                         AND arav.RECEIPT_NUMBER = aps.TRX_NUMBER 
                                         AND aps.CLASS = 'PMT'
                                         AND aps.GL_DATE_CLOSED > TO_DATE(:p_to_date,'RRRR/MM/DD HH24:MI:SS')
                                     )
                                    ) amount_remaining,
                                    RS.SALESREP_ID,
                                    aps.due_date
                               FROM ar_payment_schedules aps,
                                    ra_customers ac,
                                    ar_lookups look,
                                    ar_lookups look_status,
                                    hz_cust_accounts cust,
                                    hz_parties party,
                                    hz_cust_site_uses site_uses,
                                    hz_cust_acct_sites acct_site,
                                    hz_party_sites party_site,
                                    hz_locations loc,
                                    ra_salesreps rs
                              WHERE aps.gl_date <= NVL(TO_DATE(:p_to_date,'RRRR/MM/DD HH24:MI:SS'),SYSDATE)
                                AND cust.cust_account_id = acct_site.cust_account_id
                                AND cust.party_id = party.party_id
                                AND acct_site.party_site_id = party_site.party_site_id(+)
                                AND loc.location_id(+) = party_site.location_id
                                AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
                                AND look.lookup_type(+) = 'SITE_USE_CODE'
                                AND look.lookup_code(+) = site_uses.site_use_code
                                AND look_status.lookup_type(+) = 'CODE_STATUS'
                                AND look_status.lookup_code(+) = NVL (cust.status, 'A')
                                AND SUBSTRB (look.meaning, 1, 8)= 'Bill To'
                                AND site_uses.PRIMARY_SALESREP_ID=rs.SALESREP_ID 
                                AND rs.NAME = nvl(:p_sale_per,rs.NAME)
                                AND ac.CUSTOMER_NUMBER=cust.ACCOUNT_NUMBER
                                AND aps.CUSTOMER_ID=ac.CUSTOMER_ID
                                AND aps.CUSTOMER_SITE_USE_ID=site_uses.SITE_USE_ID
                              GROUP BY rs.NAME,ac.customer_name,ac.customer_number,aps.amount_due_original,aps.amount_due_remaining,
                                       aps.gl_date,aps.trx_number,rs.SALESREP_ID,aps.due_date
                              ORDER BY rs.NAME
                         ) 
                   group by TRX_NUMBER,salesper_name,customer_name,customer_number,amount_due_original,
                            amount_due_remaining,gl_date,amount_remaining,SALESREP_ID,due_date
                  Regards

                  Etbin