Search Results ar_corr_pay_sched_all




Overview
Explore in AI Chat

Overview

The AR_CORR_PAY_SCHED_ALL table is a critical intersection table within the Oracle E-Business Suite Receivables (AR) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to establish and maintain the relationship between dunning correspondence (collection letters) and the specific invoices or payment schedules referenced within those letters. This table acts as a junction, enabling the system to track precisely which transactional items were included in a generated dunning letter. This linkage is essential for audit trails, preventing duplicate dunning for the same item in subsequent correspondence runs, and for customer service inquiries regarding collection activities.

Key Information Stored

The table's structure is designed to efficiently map correspondence to payment schedules. Its most critical columns include the surrogate primary key, CORRESPONDENCE_PAY_SCHED_ID, which uniquely identifies each record in this intersection table. The two foreign key columns form the core relationship: CORRESPONDENCE_ID links to the AR_CORRESPONDENCES_ALL table, identifying the specific dunning letter instance, and PAYMENT_SCHEDULE_ID links to the AR_PAYMENT_SCHEDULES_ALL table, identifying the individual invoice or charge that was included in that correspondence. The table's unique key constraint (AR_CORRESPONDENCE_PAY_SCHED_UK) on the combination of these two foreign keys ensures that the same invoice cannot be logged more than once for the same letter.

Common Use Cases and Queries

A primary use case is auditing and reporting on dunning history. Analysts can query this table to list all invoices dunned for a specific customer or within a date range. Another key operational use is within the dunning engine logic itself, which likely queries this table to exclude already-dunned items when building a new letter batch, based on system setup rules. A common reporting query would join to customer and invoice tables to produce a detailed dunning report.

  • Sample Query: To find all invoices included in a specific correspondence: SELECT ps.customer_trx_id, ps.due_date FROM ar_corr_pay_sched_all cps, ar_payment_schedules_all ps WHERE cps.payment_schedule_id = ps.payment_schedule_id AND cps.correspondence_id = :corr_id;
  • Sample Query: To find all dunning letters sent for a specific invoice: SELECT corr.correspondence_id, corr.creation_date FROM ar_corr_pay_sched_all cps, ar_correspondences_all corr WHERE cps.correspondence_id = corr.correspondence_id AND cps.payment_schedule_id = :payment_schedule_id;

Related Objects

The AR_CORR_PAY_SCHED_ALL table sits at the intersection of two major Receivables entities, as defined by its documented foreign key relationships.

  • AR_CORRESPONDENCES_ALL: This is the master table for dunning correspondence. The relationship is defined by AR_CORR_PAY_SCHED_ALL.CORRESPONDENCE_ID → AR_CORRESPONDENCES_ALL. Each record in the intersection table must reference a valid, existing correspondence header.
  • AR_PAYMENT_SCHEDULES_ALL: This is the core table storing invoice and receipt payment schedules. The relationship is defined by AR_CORR_PAY_SCHED_ALL.PAYMENT_SCHEDULE_ID → AR_PAYMENT_SCHEDULES_ALL. Each record links to a specific transactional line item that was subject to dunning.

These relationships are enforced by the database and are fundamental to the integrity of the dunning history data. The table's primary key, AR_CORRESPONDENCE_PAY_SCHED_PK, is on the CORRESPONDENCE_PAY_SCHED_ID column.