Search Results ar_aging_extract

Overview

AR_AGING_EXTRACT is a temporary staging table owned by the AR (Receivables) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is populated and consumed by the MFAR (Multi-Fund Accounts Receivable) aging report process, which produces aged receivable balances across configurable time buckets. The table is not a permanent transactional store; rows are typically inserted, reported against, and purged within the lifecycle of a single aging report run. Because the MFAR aging process must assemble customer, site, payment schedule, and credit memo detail into a single flat structure before applying bucket logic, AR_AGING_EXTRACT serves as the intermediate work area that flattens this multi-table data into report-ready rows.

The ETRM metadata heuristically classifies this table as standalone under a Data Vault modeling lens, meaning it exhibits neither a clean hub nor a link nor a satellite role. This is consistent with a transient extract: it carries denormalized attributes (buckets, sort fields, contact data) alongside references to customer, site, and payment schedule identifiers, but it is not intended as a durable integration anchor. Modelers should treat the standalone classification as a suggestion reflecting the table's extract-stage nature rather than a normalized entity.

Key Information Stored

The table exposes 37 documented columns in the 12.2.2 ETRM schema. The most significant include:

• PAYMENT_SCHEDULE_ID – Identifies the payment schedule line (installment) that drives the aging calculation; the primary grain of the extract.
• CUSTOMER_ID, CUSTOMER_NUMBER, SHORT_CUSTOMER_NAME – Party identifiers and display names for grouping and sorting.
• CUST_ACCT_SITE_ID – Customer account site reference; the sole documented foreign key, pointing to HZ_CUST_ACCT_SITES_ALL.
• TRX_NUMBER – Transaction (invoice or credit memo) number presented on the aging report.
• CLASS, INVOICE_TYPE, CATEGORY – Transaction classification attributes used for filtering and reporting.
• DUE_DATE, GL_DATE, DAYS_PAST_DUE – Timing attributes that determine bucket assignment.
• AMT_DUE_REMAINING, AMOUNT_APPLIED, AMOUNT_ADJUSTED, AMOUNT_CREDITED – Money amounts reconciled from the underlying transactions.
• BUCKET_0 through BUCKET_6 – The seven aging buckets into which remaining balances are distributed based on days past due.
• EXCHANGE_RATE, BAL_SEGMENT_VALUE – Currency conversion rate and balancing segment value.
• PARENT_REQUEST_ID, WORKER_ID – Concurrency request and worker identifiers supporting parallel execution.

No surrogate primary key is documented; the nearest business-key candidates are the combination of PAYMENT_SCHEDULE_ID with TRX_NUMBER and CUSTOMER_ID. The presence of PARENT_REQUEST_ID reinforces that rows belong to a specific report submission and should be scoped accordingly.

Common Use Cases and Queries

Typical uses center on retrieving, reconciling, or re-using MFAR aging output after the concurrent request completes. A basic query filters by the parent request:

• SELECT customer_number, trx_number, days_past_due, bucket_0, bucket_1, bucket_2, bucket_3, bucket_4, bucket_5, bucket_6 FROM ar.ar_aging_extract WHERE parent_request_id = :request_id;
• Aggregating aging exposure by customer: SELECT customer_id, SUM(amt_due_remaining), SUM(bucket_3) FROM ar.ar_aging_extract GROUP BY customer_id;
• Joining back to HZ_CUST_ACCT_SITES_ALL on cust_acct_site_id to retrieve site-level attributes not stored in the extract.
• Reconciling extract totals against AR_PAYMENT_SCHEDULES to validate the aging run.

Because the table is temporary, queries must be executed within the same session or before the process clears rows for the given PARENT_REQUEST_ID.

Related Objects

• HZ_CUST_ACCT_SITES_ALL – Joined via AR_AGING_EXTRACT.CUST_ACCT_SITE_ID to retrieve customer account site details.
• AR_PAYMENT_SCHEDULES_ALL – Source of installment balances keyed by PAYMENT_SCHEDULE_ID.
• HZ_CUST_ACCOUNTS – Party-level master data for CUSTOMER_ID.
• RA_CUSTOMER_TRX_ALL – Transaction header providing TRX_NUMBER and GL_DATE context.
• AR_CASH_RECEIPTS_ALL / AR_RECEIVABLE_APPLICATIONS_ALL – Furnish AMOUNT_APPLIED and AMOUNT_CREDITED values.
• FND_CONCURRENT_REQUESTS – Correlates PARENT_REQUEST_ID with the MFAR aging concurrent program submission.