Tuesday, April 30, 2013

How to fetch Aging Bucket line details for a delinquent Customer account

Following Select statement will fetch Aging bucket line details for a particular Customer to send dunning letter:-

Parameters:
  •   Dunning Plan Name
  •   Customer Account Number

SELECT l.aging_bucket_line_id,
                l.type Aging_Bucket_Line_Type,
                l.days_start,
                l.days_to
  FROM ar_aging_bucket_lines l,
              ar_aging_buckets b,
              iex_dunning_plans_vl d
 WHERE 1 = 1
   AND d.name = :p_dunning_plan_name   
   AND d.aging_bucket_id = b.aging_bucket_id
   AND b.aging_bucket_id = l.aging_bucket_id
   AND (SELECT MAX (TRUNC (SYSDATE) - TRUNC (ar.due_date)) days
                 FROM iex_delinquencies del,
                             ar_payment_schedules ar,
                             hz_cust_accounts hca
             WHERE 1 = 1
             AND hca.cust_account_id = del.cust_account_id
             AND hca.account_number = :p_account_number
            AND del.payment_schedule_id = ar.payment_schedule_id
            AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
            BETWEEN l.days_start AND l.days_to
            AND EXISTS                    (SELECT 1
                     FROM iex_ag_dn_xref x
                     WHERE 1=1
                      AND d.dunning_plan_id = x.dunning_plan_id
                      AND d.aging_bucket_id = x.aging_bucket_id
                      AND x.aging_bucket_line_id = l.aging_bucket_line_id);

No comments:

Post a Comment