Oracle EBS AR Aging Report Query with Configurable Buckets

-- bucket_1: days past due <= 0 (current)
-- bucket_2: 0 < days <= 30   bucket_3: 30 < days <= 60
-- bucket_4: 60 < days <= 90   bucket_5: 90 < days <= 120
-- bucket_6: 120 < days <= 180   bucket_7: days > 180
WITH open_items AS (
  SELECT ps.customer_id,
         ps.class,
         ps.acctd_amount_due_remaining AS amt,
         TRUNC(TO_DATE('2026-10-22', 'YYYY-MM-DD')) - TRUNC(ps.due_date) AS days_past_due
  FROM   ar_payment_schedules_all ps
  WHERE  ps.org_id = 204
  AND    ps.status = 'OP'
  AND    ps.amount_due_remaining <> 0
  AND    ps.gl_date <= TO_DATE('2026-10-22', 'YYYY-MM-DD')
  AND    ps.customer_id IS NOT NULL
)
SELECT p.party_name,
       ca.account_number,
       SUM(o.amt) AS total_due,
       SUM(CASE WHEN o.days_past_due <= 0 THEN o.amt ELSE 0 END) AS bucket_1,
       SUM(CASE WHEN o.days_past_due >  0 AND o.days_past_due <= 30 THEN o.amt ELSE 0 END) AS bucket_2,
       SUM(CASE WHEN o.days_past_due >  30 AND o.days_past_due <= 60 THEN o.amt ELSE 0 END) AS bucket_3,
       SUM(CASE WHEN o.days_past_due >  60 AND o.days_past_due <= 90 THEN o.amt ELSE 0 END) AS bucket_4,
       SUM(CASE WHEN o.days_past_due >  90 AND o.days_past_due <= 120 THEN o.amt ELSE 0 END) AS bucket_5,
       SUM(CASE WHEN o.days_past_due >  120 AND o.days_past_due <= 180 THEN o.amt ELSE 0 END) AS bucket_6,
       SUM(CASE WHEN o.days_past_due >  180 THEN o.amt ELSE 0 END) AS bucket_7,
       SUM(CASE WHEN o.class = 'PMT' THEN o.amt ELSE 0 END) AS unapplied_receipts
FROM   open_items o
JOIN   hz_cust_accounts ca
       ON ca.cust_account_id = o.customer_id
JOIN   hz_parties p
       ON p.party_id = ca.party_id
WHERE  p.party_name LIKE '%'
GROUP  BY p.party_name, ca.account_number
ORDER  BY total_due DESC;

SELECT p.party_name,
       ca.account_number,
       ps.class,                       -- INV, DM, CM, CB, DEP, GUAR, PMT, BR
       ps.trx_number,
       t.purchase_order,
       ps.trx_date,
       ps.due_date,
       TRUNC(TO_DATE('2026-10-22', 'YYYY-MM-DD')) - TRUNC(ps.due_date) AS days_past_due,
       ps.invoice_currency_code,
       ps.amount_due_original,
       ps.amount_due_remaining,
       ps.exchange_rate,
       ps.acctd_amount_due_remaining
FROM   ar_payment_schedules_all ps
JOIN   hz_cust_accounts ca
       ON ca.cust_account_id = ps.customer_id
JOIN   hz_parties p
       ON p.party_id = ca.party_id
LEFT JOIN ra_customer_trx_all t
       ON t.customer_trx_id = ps.customer_trx_id
WHERE  ps.org_id = 204
AND    ps.status = 'OP'
AND    ps.amount_due_remaining <> 0
AND    ps.gl_date <= TO_DATE('2026-10-22', 'YYYY-MM-DD')
AND    p.party_name LIKE '%'
ORDER  BY p.party_name, ps.due_date;