Results for “ar_customer_payment_schedules”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The AR_CUSTOMER_PAYMENT_SCHEDULES view is an Oracle Receivables (AR) reporting object owned by the APPS schema and exposed through the E-Business Suite Technical Reference Manual (ETRM). Its documented purpose is to "facilitate queries on sums across customers' invoices." Rather than forcing report authors and integrators to join the transaction-level payment schedule table to customer, party, site, and profile tables, the view pre-aggregates outstanding and past-due exposure at the customer and site level. This makes it particularly suited to ad-hoc reporting, collection workbench-style inquiries, and downstream extracts where a summarised receivable position per customer is required.

In 12.1.1 and 12.2.2 the view remains valid under the same APPS schema. Users searching for outstanding_balance typically reach this object because the view exposes an OUTSTANDING_BALANCE column expressed in the invoice currency, enabling direct retrieval of amounts still owed by each customer.

Underlying Base Objects

The ETRM documents the following referenced base objects for this view:

• AR_PAYMENT_SCHEDULES (synonym) — the core transaction-level schedule holding AMOUNT_DUE_REMAINING and DUE_DATE; the source of all monetary aggregation.
• AR_LOOKUPS (view) — resolves the YES/NO lookup that drives the SHOW_SITES_FLAG behaviour via LOOKUP_CODE.
• HZ_CUST_ACCOUNTS (synonym) — provides the customer identifier, account number, and account status.
• HZ_PARTIES (synonym) — supplies the party name used as CUSTOMER_NAME.
• HZ_CUST_SITE_USES (synonym) — supplies the site use and location when site-level reporting is enabled.
• HZ_CUSTOMER_PROFILES (synonym) — contributes the collector assignment and drives the distinct-profile division applied to the summed amounts.

The view joins these objects on the customer account, party, and payment schedule relationships, grouping by customer, party name, account number, lookup code, site use, location, invoice currency, and collector. The amounts are divided by the count of distinct customer account profiles, and site attributes are returned only when the lookup code equals 'Y'.

Key Columns

• CUSTOMER_ID — the CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS.
• CUSTOMER_NAME — the party name, truncated to 50 bytes.
• CUSTOMER_NUMBER — the customer account number.
• STATUS — the minimum customer account status within the grouping.
• SITE_USE_ID / LOCATION — populated only when the lookup indicates site-level display.
• INVOICE_CURRENCY_CODE — the currency in which the balances are expressed.
• OUTSTANDING_BALANCE — the summed AMOUNT_DUE_REMAINING divided by distinct customer profiles; the primary column for outstanding-balance inquiries.
• PAST_DUE_BALANCE — the portion of the balance whose due date precedes SYSDATE.
• SHOW_SITES_FLAG — the YES/NO lookup code governing site-level output.
• COLLECTOR_ID — the collector assigned on the customer profile.

Common Use Cases and Queries

A frequent requirement is to report total outstanding and past-due amounts per customer. The view removes the need to write the underlying aggregation manually:

SELECT customer_number, customer_name, invoice_currency_code,
       outstanding_balance, past_due_balance
FROM   ar_customer_payment_schedules
WHERE  outstanding_balance > 0
ORDER BY outstanding_balance DESC;

Collectors can filter by assignment to build a workload list:

SELECT customer_id, customer_name, outstanding_balance
FROM   ar_customer_payment_schedules
WHERE  collector_id = :collector_id;

Analysts comparing currency exposure across the receivables ledger group by currency and status. Because the balances are currency-specific, cross-currency totals should not be summed without conversion. The view is read-only and intended for query consumption rather than transactional processing; for aging detail or individual transactions, the base AR_PAYMENT_SCHEDULES table should be queried directly.