Monday, 24 September 2018

Script to get Aging Bucket Due for Customer

CREATE OR REPLACE FUNCTION APPS.Aging_Bucket_Due
      (P_CUST_ACCOUNT_ID IN NUMBER,
       P_AGING_BUCKET       IN NUMBER)
  RETURN NUMBER
    IS
  l_bucket_balance NUMBER(10,2);
  BEGIN

        select nvl(sum(arp.acctd_amount_due_remaining),0)
          into l_bucket_balance
          from ar_payment_schedules_all           arp,
               ar_aging_buckets                   ab,
               ar_aging_bucket_lines              abl,
               iex_delinquencies_all              iexd
         where iexd.cust_account_id = P_CUST_ACCOUNT_ID
           and iexd.status         = 'DELINQUENT'
           and abl.aging_bucket_id      = ab.aging_bucket_id
           and Ab.bucket_name      = FND_PROFILE.VALUE('IEX_COLLECTIONS_BUCKET_NAME')
           and iexd.payment_schedule_id              = arp.payment_schedule_id
           and (trunc(sysdate) - trunc(arp.due_date)) between abl.days_start and abl.days_to
           and arp.status                            ='OP'
           and abl.Bucket_Sequence_num = P_AGING_BUCKET;

     RETURN l_bucket_balance;

END Aging_Bucket_Due;

2 comments: