# Oracle EBS AR Aging Report Query with Configurable Buckets

> Source: https://netray.co/tools/oracle-ebs-ar-aging-buckets-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates a 7-bucket receivables aging query by customer account and an open-item detail query from AR_PAYMENT_SCHEDULES_ALL, with your bucket boundaries and as-of date.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle Apps technical consultant
- AR analyst
- Controller

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Operating unit ORG_ID

**Key:** org_id

**Type:** number

**Default:** 204

#### As-of date

**Key:** as_of

**Type:** date

**Default:** 2026-10-22

#### Bucket 1 upper bound (current)

**Key:** b1

**Type:** number

**Default:** 0

#### Bucket 2 upper bound

**Key:** b2

**Type:** number

**Default:** 30

#### Bucket 3 upper bound

**Key:** b3

**Type:** number

**Default:** 60

#### Bucket 4 upper bound

**Key:** b4

**Type:** number

**Default:** 90

#### Bucket 5 upper bound

**Key:** b5

**Type:** number

**Default:** 120

#### Bucket 6 upper bound

**Key:** b6

**Type:** number

**Default:** 180

#### Customer name LIKE

**Key:** cust

**Type:** text

**Default:** %

**Blocks:**

#### Query 1: aging by customer account (functional currency)

**Body:** -- bucket_1: days past due <= {{b1}} (current)
-- bucket_2: {{b1}} < days <= {{b2}}   bucket_3: {{b2}} < days <= {{b3}}
-- bucket_4: {{b3}} < days <= {{b4}}   bucket_5: {{b4}} < days <= {{b5}}
-- bucket_6: {{b5}} < days <= {{b6}}   bucket_7: days > {{b6}}
WITH open_items AS (
  SELECT ps.customer_id,
         ps.class,
         ps.acctd_amount_due_remaining AS amt,
         TRUNC(TO_DATE('{{as_of}}', 'YYYY-MM-DD')) - TRUNC(ps.due_date) AS days_past_due
  FROM   ar_payment_schedules_all ps
  WHERE  ps.org_id = {{org_id}}
  AND    ps.status = 'OP'
  AND    ps.amount_due_remaining <> 0
  AND    ps.gl_date <= TO_DATE('{{as_of}}', '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 <= {{b1}} THEN o.amt ELSE 0 END) AS bucket_1,
       SUM(CASE WHEN o.days_past_due >  {{b1}} AND o.days_past_due <= {{b2}} THEN o.amt ELSE 0 END) AS bucket_2,
       SUM(CASE WHEN o.days_past_due >  {{b2}} AND o.days_past_due <= {{b3}} THEN o.amt ELSE 0 END) AS bucket_3,
       SUM(CASE WHEN o.days_past_due >  {{b3}} AND o.days_past_due <= {{b4}} THEN o.amt ELSE 0 END) AS bucket_4,
       SUM(CASE WHEN o.days_past_due >  {{b4}} AND o.days_past_due <= {{b5}} THEN o.amt ELSE 0 END) AS bucket_5,
       SUM(CASE WHEN o.days_past_due >  {{b5}} AND o.days_past_due <= {{b6}} THEN o.amt ELSE 0 END) AS bucket_6,
       SUM(CASE WHEN o.days_past_due >  {{b6}} 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 '{{cust}}'
GROUP  BY p.party_name, ca.account_number
ORDER  BY total_due DESC;

#### Query 2: open-item detail in entered currency

**Body:** 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('{{as_of}}', '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 = {{org_id}}
AND    ps.status = 'OP'
AND    ps.amount_due_remaining <> 0
AND    ps.gl_date <= TO_DATE('{{as_of}}', 'YYYY-MM-DD')
AND    p.party_name LIKE '{{cust}}'
ORDER  BY p.party_name, ps.due_date;

**Notes:** This is an open-item aging from current balances. For an as-of date in the past it overstates or understates items that were open then but have since been applied or adjusted: a true historical aging must add back AR_RECEIVABLE_APPLICATIONS_ALL and AR_ADJUSTMENTS_ALL activity dated after the as-of date. Reconcile against the standard Aging - 7 Buckets - By Account Report for the same date.

## Content

**Intro:** This generator writes a receivables aging query for Oracle E-Business Suite R12 with seven buckets you define, by customer account in functional currency, plus an open-item detail query in entered currency. It reads AR_PAYMENT_SCHEDULES_ALL, the table the standard aging reports start from.

**Sections:**

#### How to use it

**Body:** Enter the operating unit ORG_ID, the as-of date and six bucket boundaries. The defaults 0, 30, 60, 90, 120 and 180 give current, 1-30, 31-60, 61-90, 91-120, 121-180 and over 180 days past due, the same layout as the standard 7-bucket report. Restrict to a customer with a LIKE pattern or leave %. Run Query 1 for the summary a controller reviews, and Query 2 when a collector needs the transactions behind a bucket. Both run in SQL Developer as APPS; no multi-org context is needed because the _ALL table is filtered by ORG_ID directly.

**Bullets:**

- Days past due = as-of date minus due date, both truncated
- Negative days (not yet due) fall in bucket 1
- Functional amounts use ACCTD_AMOUNT_DUE_REMAINING

#### Where the numbers come from

**Body:** Every invoice, debit memo, credit memo, chargeback, deposit and receipt has one or more payment schedule rows in AR_PAYMENT_SCHEDULES_ALL, one per installment. STATUS is OP while AMOUNT_DUE_REMAINING is not zero and CL once fully applied. CLASS tells you the document type: INV, DM, CM, CB, DEP, GUAR, PMT and BR. AMOUNT_DUE_REMAINING is in the transaction currency; ACCTD_AMOUNT_DUE_REMAINING is the stored functional amount, which avoids recalculating with EXCHANGE_RATE and handles rounding the way Receivables did. Customer comes from CUSTOMER_ID, which is HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID, with the name on HZ_PARTIES.

#### Worked example and reconciliation

**Body:** An invoice due 15 August 2026 aged as of 22 October 2026 is 68 days past due and lands in bucket 4 (61-90). An open credit memo shows as a negative amount in the bucket of its due date, and an unapplied receipt shows as a negative PMT row in the bucket of its due date and in total_due, and is also broken out in the unapplied_receipts column so you can see how much cash is waiting to be applied. To reconcile, run the Aging - 7 Buckets - By Account Report for the same operating unit and date and compare totals by customer. Differences usually come from as-of dates in the past, on-account credits treated differently in the report options, or receipts in other operating units.

#### Limits and common mistakes

**Body:** The query is exact for today and an approximation for a past date, because applications and adjustments posted after that date have already reduced AMOUNT_DUE_REMAINING. For month-end aging, run it on the first day of the next month before cash application, or build the rollback with AR_RECEIVABLE_APPLICATIONS_ALL and AR_ADJUSTMENTS_ALL. Do not join to RA_CUSTOMER_TRX_LINES_ALL in an aging: lines multiply rows and inflate totals. Bill-to site level aging needs CUSTOMER_SITE_USE_ID joined to HZ_CUST_SITE_USES_ALL. Disputed amounts sit in AMOUNT_IN_DISPUTE if you want a separate column.

**Cta:** If you want collectors to ask which customers moved into the 61-90 bucket this week and get the answer from your own EBS instance on your servers, we can set that up.

**Faq:**

#### What is the AR aging query in Oracle Apps R12?

**Answer:** Select open rows from AR_PAYMENT_SCHEDULES_ALL (status OP, amount_due_remaining not zero) for the operating unit, compute as-of date minus due date, and sum the remaining amount into buckets with CASE expressions, grouped by customer account from HZ_CUST_ACCOUNTS and HZ_PARTIES. Query 1 above does this with seven configurable buckets.

#### Which standard report should my query match?

**Answer:** Aging - 7 Buckets - By Account Report in Receivables, run for the same operating unit, as-of date and bucket set. Use the same treatment of on-account credits and receipts as your report parameters. Differences on today's date usually point to filters; differences on past dates point to later applications.

#### What are the main AR tables in Oracle EBS?

**Answer:** RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL for transactions, RA_CUST_TRX_LINE_GL_DIST_ALL for accounting distributions, AR_PAYMENT_SCHEDULES_ALL for balances due, AR_CASH_RECEIPTS_ALL for receipts, AR_RECEIVABLE_APPLICATIONS_ALL for applications, AR_ADJUSTMENTS_ALL for adjustments, and HZ_CUST_ACCOUNTS with HZ_PARTIES for customers.

#### Why do unapplied receipts show as negative amounts?

**Answer:** A receipt has its own payment schedule row with class PMT, and its unapplied amount is stored as a negative AMOUNT_DUE_REMAINING. It reduces the customer's total due. Query 1 shows it separately so you can tell real overdue balances apart from cash that is simply not applied yet.

## Search terms

ar aging report query in oracle apps r12, aging 7 buckets report in oracle apps query, ar_payment_schedules_all aging query, account receivables reports in oracle ebs, ar tables in oracle ebs, oracle receivables aging sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json