Results for “correspondence_id”

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

Overview

AR_CORR_PAY_SCHED_ALL is an Oracle Receivables (AR) intersection table that records the invoices referenced within a dunning letter. In the collections and correspondence workflow, dunning letters summarize a customer's outstanding balances across multiple transactions. This table provides the many-to-many resolution between a correspondence record and the payment schedules (transaction balances) it covers. Each row links one correspondence to one payment schedule, allowing the dunning process to capture exactly which invoices, debit memos, chargebacks, or other balance-bearing transactions were included in a given letter.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is a link. This is a modeling suggestion: the table does not function as a transactional hub of its own identity, but rather as a relationship artifact connecting two independent entities — the correspondence and the payment schedule. It carries descriptive measures (amount due, accrued, unaccrued) alongside the two foreign keys, so in strict Data Vault terms it behaves like a link with attached descriptive attributes. The table is owned by the AR schema and is documented as part of the Receivables product.

Key Information Stored

The table contains thirteen documented columns. The surrogate primary key is CORRESPONDENCE_PAY_SCHED_ID, enforced by the index AR_CORRESPONDENCE_PAY_SCHED_PK. A second unique index, AR_CORRESPONDENCE_PAY_SCHED_UK (and the business-key candidate AR_CORRESPONDENCE_PAY_SCHED_U1), establishes uniqueness across the combination of CORRESPONDENCE_ID and PAYMENT_SCHEDULE_ID, preventing the same payment schedule from being associated with the same correspondence more than once. The most important columns include:

• CORRESPONDENCE_PAY_SCHED_ID — surrogate primary key for each association row.
• CORRESPONDENCE_ID — foreign key to AR_CORRESPONDENCES_ALL; identifies the dunning letter.
• PAYMENT_SCHEDULE_ID — foreign key to AR_PAYMENT_SCHEDULES_ALL; identifies the referenced transaction balance.
• AMOUNT_DUE_REMAINING — the outstanding balance on the referenced transaction at the time of dunning.
• AMOUNT_ACCRUE — the accrued portion of the balance.
• AMOUNT_UNACCRUE — the unaccrued portion of the balance.
• ORG_ID — the operating unit identifier, for multi-org security and reporting.
• STAGED_DUNNING_LEVEL — the dunning level applied to this line during staged dunning.
• Standard WHO audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The principal use case is generating and printing dunning letters that itemize the transactions a customer must pay. Collections analysts also use the table to audit which invoices were dunned, at what level, and for what remaining amount. A common reporting query joins the table to its parents to produce a dunning detail report:

• Join to AR_CORRESPONDENCES_ALL on CORRESPONDENCE_ID to retrieve letter metadata (recipient, date, template).
• Join to AR_PAYMENT_SCHEDULES_ALL on PAYMENT_SCHEDULE_ID to obtain invoice numbers, due dates, and class.
• Filter by ORG_ID to enforce operating-unit security.
• Aggregate AMOUNT_DUE_REMAINING by CORRESPONDENCE_ID to verify the letter total against the sum of referenced invoices.
• Group by STAGED_DUNNING_LEVEL to analyze the escalation of dunning across the collections cycle.

Typical SQL patterns select from AR_CORR_PAY_SCHED_ALL c, joining AR_PAYMENT_SCHEDULES_ALL p on c.PAYMENT_SCHEDULE_ID = p.PAYMENT_SCHEDULE_ID and AR_CORRESPONDENCES_ALL k on c.CORRESPONDENCE_ID = k.CORRESPONDENCE_ID. Reports frequently filter on the correspondence's creation date range and the operating unit.

Related Objects

The following objects are most significant in relation to AR_CORR_PAY_SCHED_ALL, based on the documented foreign-key relationships:

• AR_CORRESPONDENCES_ALL — parent correspondence/dunning letter table; joined via CORRESPONDENCE_ID.
• AR_PAYMENT_SCHEDULES_ALL — parent transaction balance table; joined via PAYMENT_SCHEDULE_ID.
• AR_PAYMENT_SCHEDULES_ALL is itself the central AR transaction-balance table, linking onward to RA_CUSTOMER_TRX_ALL for invoice headers.
• The Data Vault classification as a link implies downstream modeling objects (links/hubs) generated in an analytics layer that resolve correspondence-to-transaction relationships.
• Dunning and correspondence concurrent programs in Oracle Receivables read and write this table when producing staged dunning letters.

Because the table is an intersection with only two documented foreign keys, its principal dependencies are confined to the correspondence and payment-schedule parents, with the audit columns supplied by the standard EBS WHO framework.