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);
