Results for “ar_payment_schedules_all”

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

Overview

AR_PAYMENT_SCHEDULES_ALL is the central Receivables table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores one row for each payment schedule (installment/line-level receivable) generated from a transaction. Per the ETRM documentation, the table's scope is "All transactions except adjustments and miscellaneous cash receipts," which positions it as the authoritative repository of open receivable balances derived from invoices, credit memos, debit memos, chargebacks, deposits, and guarantees. Every payment term on a customer transaction produces at least one schedule row, and the table records the original and remaining amounts for the line items, tax, freight, and discount components, plus the full lifecycle history of cash applications, adjustments, collections activity, and disputes against that schedule.

From a Data Vault modeling perspective, the mined FK structure classifies this as a hub. The surrogate primary key is PAYMENT_SCHEDULE_ID, and business-key uniqueness is enforced through AR_PAYMENT_SCHEDULES_U1 (PAYMENT_SCHEDULE_ID, ZD_EDITION_NAME), AR_PAYMENT_SCHEDULES_U2 (CASH_RECEIPT_ID, PAYMENT_SCHEDULE_ID, ZD_EDITION_NAME), and AR_PAYMENT_SCHEDULES_U3 (REVERSED_CASH_RECEIPT_ID, PAYMENT_SCHEDULE_ID, ZD_EDITION_NAME). The ZD_EDITION_NAME component reflects the editioning view support introduced in EBS Release 12.2.

Key Information Stored

The table contains 133 documented columns. The most important for functional and reporting purposes include the following:

• PAYMENT_SCHEDULE_ID – primary key/foreign-key hub identifier for the schedule row.
• CUSTOMER_TRX_ID – FK to RA_CUSTOMER_TRX_ALL, linking the schedule to its parent transaction (the UK1 business key is CUSTOMER_TRX_ID + TERMS_SEQUENCE_NUMBER).
• TERMS_SEQUENCE_NUMBER – distinguishes installments within a single transaction when multiple payment terms apply.
• CLASS and CUST_TRX_TYPE_ID – identify the transaction class (INV, CM, DM, CB, DEP, GUAR) and the transaction type.
• CUSTOMER_ID, CUSTOMER_SITE_USE_ID – FK to HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL, establishing the customer and site context.
• TERM_ID – FK to RA_TERMS_B, capturing the payment terms.
• DUE_DATE, GL_DATE, DISCOUNT_DATE – critical dates for aging and discount analysis.
• AMOUNT_DUE_ORIGINAL / AMOUNT_DUE_REMAINING – the original and open balance amounts.
• AMOUNT_APPLIED, AMOUNT_ADJUSTED, AMOUNT_CREDITED, AMOUNT_IN_DISPUTE – cumulative settlement activity.
• STATUS – OPEN, CLOSED, or REVERSED, controlling whether the schedule is considered outstanding.
• INVOICE_CURRENCY_CODE, EXCHANGE_RATE, ACCTD_AMOUNT_DUE_REMAINING – currency and accounting amounts.
• CASH_RECEIPT_ID, REVERSED_CASH_RECEIPT_ID, ASSOCIATED_CASH_RECEIPT_ID – links to AR_CASH_RECEIPTS_ALL.
• TRX_NUMBER and TRX_DATE – denormalized transaction reference values.
• ORG_ID – multi-org operating unit discriminator.
• COLLECTOR_LAST, FOLLOW_UP_DATE_LAST, PROMISE_DATE_LAST – collections-tracking attributes.

Common Use Cases and Queries

The table underpins aging reports, open receivables balances, collections worklists, and reconciliation between subledger and general ledger. A typical aging query joins back to the transaction header and filters by open status:

• Open receivables by customer: SELECT customer_id, SUM(amount_due_remaining) FROM ar_payment_schedules_all WHERE status='OP' AND class='INV' AND org_id=:p_org GROUP BY customer_id;
• Aging buckets: use DUE_DATE against SYSDATE and AMOUNT_DUE_REMAINING to construct 0-30/31-60/61-90/90+ buckets.
• Collections extract: join to HZ_CUST_ACCOUNTS on CUSTOMER_ID and AR_COLLECTORS on COLLECTOR_LAST to build collector worklists.
• Unapplied cash detection: query for CASH_RECEIPT_ID and REVERSED_CASH_RECEIPT_ID patterns where the schedule remains open.
• FX exposure: reconcile AMOUNT_DUE_REMAINING (entered) against ACCTD_AMOUNT_DUE_REMAINING (functional).

Because the class and status columns are indexed in most implementations, always bound queries by these predicates and by ORG_ID to avoid full scans.

Related Objects

The table is a hub with extensive inbound and outbound referential relationships. The most significant include:

• RA_CUSTOMER_TRX_ALL – joined via CUSTOMER_TRX_ID; the parent transaction.
• AR_CASH_RECEIPTS_ALL – joined via CASH_RECEIPT_ID, REVERSED_CASH_RECEIPT_ID, and ASSOCIATED_CASH_RECEIPT_ID.
• AR_RECEIVABLE_APPLICATIONS_ALL – references both PAYMENT_SCHEDULE_ID and APPLIED_PAYMENT_SCHEDULE_ID; tracks cash applications.
• AR_ADJUSTMENTS_ALL – references PAYMENT_SCHEDULE_ID; records invoice adjustments.
• HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL – customer master context.
• RA_TERMS_B – payment terms referenced through TERM_ID.
• RA_CUST_TRX_TYPES_ALL – transaction type metadata via CUST_TRX_TYPE_ID.
• AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL – inbound FKs supporting unconfirmed receipts.
• AR_MC_PAYMENT_SCHEDULES – multiple-reporting-currency shadow table keyed on PAYMENT_SCHEDULE_ID.
• AR_CASH_BASIS_DISTS_ALL and AR_AUTOREC_EXCEPTIONS – downstream processing hooks.

Standard APIs such as AR_RECEIVABLE_APPLICATIONS_API and AR_ADJUSTMENTS_API operate against this table, so custom code should treat AR_PAYMENT_SCHEDULES_ALL as read-mostly and invoke supported APIs rather than performing direct DML.