Results for “purged_children_flag”

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

Overview

AR_CORRESPONDENCES_ALL is the Receivables (AR) table that stores the dunning letters sent to a customer. Each row represents a single dunning correspondence — a letter, message, or notice generated by the Oracle Receivables dunning process — and records which customer received it, which dunning letter and letter set produced it, when it was generated, and its processing status. The table is central to the collections and credit management functions of Oracle EBS 12.1.1 and 12.2.2, and it is the parent of at least one child table that stores the associated payment schedules.

The table is owned by the AR schema and contains 19 documented columns in the ETRM 12.2.2 physical schema. Its single-row primary key is AR_CORRESPONDENCES_PK, defined on CORRESPONDENCE_ID, and a matching unique index, AR_CORRESPONDENCES_U1, is documented on the same column. The dotted-name suffix "_ALL" indicates that the table is partitioned by operating unit through the ORG_ID column, so queries in a multi-org environment should generally be constrained by ORG_ID or run through the appropriate MOAC security context.

Based on the foreign key structure mined from the ETRM metadata, the table has a satellite-leaning Data Vault classification. This is a modeling suggestion rather than a documented attribute: AR_CORRESPONDENCES_ALL carries descriptive and transactional attributes about a correspondence relationship rather than serving as a pure reference hub, and it depends on multiple customer and dunning configuration hubs.

Key Information Stored

The primary key of the table is CORRESPONDENCE_ID, a system-generated surrogate identifier that uniquely identifies each dunning letter. Beyond the surrogate key, the documented unique index does not expose a separate natural business key, so CORRESPONDENCE_ID is also the practical business-key candidate when tracing a single correspondence. The remaining documented columns fall into four groups:

• Customer and site context: CUSTOMER_ID (foreign key to HZ_CUST_ACCOUNTS), SITE_USE_ID (foreign key to HZ_CUST_SITE_USES_ALL), and REMIT_TO_ADDRESS_ID (foreign key to HZ_CUST_ACCT_SITES_ALL) identify the account, the site use, and the remit-to address associated with the letter.
• Dunning source: CORRESPONDENCE_TYPE, DUNNING_LEVEL, REFERENCE1 (which points to AR_DUNNING_LETTER_SETS), and REFERENCE2 (which points to AR_DUNNING_LETTERS_B) record the type of correspondence and the specific dunning letter set and letter used to generate it.
• Processing and status: CORRESPONDENCE_DATE, PRINTED_FLAG, PRELIMINARY_FLAG, REQUEST_ID, and PURGED_CHILDREN_FLAG capture when the correspondence was created, whether it has been printed, whether it is preliminary, the concurrent request that produced it, and whether its child payment schedules have been purged.
• Standard EBS audit and multi-org columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and ORG_ID provide audit tracking and operating unit partitioning.

Common Use Cases and Queries

Typical use cases include collections reporting, dunning letter audit, and payment schedule reconciliation. A collections analyst might query all dunning letters generated for a customer within a period:

• SELECT CORRESPONDENCE_ID, CORRESPONDENCE_DATE, DUNNING_LEVEL FROM AR_CORRESPONDENCES_ALL WHERE CUSTOMER_ID = :customer_id AND ORG_ID = :org_id ORDER BY CORRESPONDENCE_DATE DESC;
• Finding unprinted letters: SELECT * FROM AR_CORRESPONDENCES_ALL WHERE PRINTED_FLAG = 'N' AND ORG_ID = :org_id;
• Linking to the dunning letter definition: join REFERENCE2 to AR_DUNNING_LETTERS_B and REFERENCE1 to AR_DUNNING_LETTER_SETS;
• Linking to child payment schedules: join CORRESPONDENCE_ID to AR_CORR_PAY_SCHED_ALL.

Because the table is partitioned by operating unit, report queries should filter on ORG_ID or use the MOAC profile option to avoid cross-organization leakage.

Related Objects

The most significant related objects, based on the documented foreign keys, are:

• HZ_CUST_ACCOUNTS — joined on CUSTOMER_ID; identifies the customer account.
• HZ_CUST_SITE_USES_ALL — joined on SITE_USE_ID; identifies the customer site use.
• HZ_CUST_ACCT_SITES_ALL — joined on REMIT_TO_ADDRESS_ID; identifies the remit-to address.
• AR_DUNNING_LETTER_SETS — joined on REFERENCE1; the letter set that generated the correspondence.
• AR_DUNNING_LETTERS_B — joined on REFERENCE2; the specific dunning letter definition.
• AR_CORR_PAY_SCHED_ALL — the child table whose CORRESPONDENCE_ID foreign key references AR_CORRESPONDENCES_ALL; it stores the payment schedules associated with each dunning letter.

These relationships make AR_CORRESPONDENCES_ALL the transactional anchor that ties dunning configuration to customer accounts and to the resulting payment schedule detail.