Search Results correspondence_id




Overview
Explore in AI Chat

Overview

The AR_CORRESPONDENCES_ALL table is a core transactional data store within Oracle E-Business Suite Receivables (AR) modules, specifically for versions 12.1.1 and 12.2.2. It serves as the system of record for all dunning correspondence, which are formal payment reminder letters, sent to customers. Its primary role is to maintain a complete audit trail of dunning activities initiated through the Receivables' collection process. This enables tracking of communication history, supports compliance requirements, and prevents duplicate dunning for the same debt within a specified period. As an "ALL" table, it is partitioned by the operating unit, adhering to the Oracle EBS multi-organization architecture (MOAC), allowing it to store data for all business units within a single installation.

Key Information Stored

The table's structure is designed to link the dunning event to the involved parties, the specific dunning rules applied, and the related transactions. The primary key is the system-generated CORRESPONDENCE_ID. Critical foreign key columns define its relationships: CUSTOMER_ID links to HZ_CUST_ACCOUNTS, identifying the customer account. SITE_USE_ID links to HZ_CUST_SITE_USES_ALL, specifying the customer site (e.g., bill-to) that received the correspondence. REFERENCE1 and REFERENCE2 are pivotal, storing identifiers for the AR_DUNNING_LETTER_SETS (the dunning plan) and AR_DUNNING_LETTERS_B (the specific letter template level) used, respectively. The REMIT_TO_ADDRESS_ID links to HZ_CUST_ACCT_SITES_ALL, recording the remit-to address printed on the letter. Additional columns typically track the correspondence date, status, the requesting user, and the context of the print request.

Common Use Cases and Queries

This table is central to reporting and auditing dunning history. Common use cases include analyzing the effectiveness of collection strategies by correlating dunning levels with subsequent payments, auditing customer communication for dispute resolution, and ensuring compliance with dunning cycle rules. A typical query retrieves a history of letters sent to a specific customer or for a specific invoice.

  • Sample Query: To find all dunning letters sent for a customer account in a given period, joining to related entities:
    SELECT aca.correspondence_id, aca.correspondence_date, hca.account_number, aca.reference1, aca.reference2
    FROM ar_correspondences_all aca, hz_cust_accounts hca
    WHERE aca.customer_id = hca.cust_account_id
    AND hca.account_number = 'CUST123'
    AND aca.correspondence_date BETWEEN :p_start_date AND :p_end_date
    AND aca.org_id = :p_org_id;
  • Data from this table is often sourced for custom collection agent dashboards or regulatory reports detailing all customer contact attempts.

Related Objects

The AR_CORRESPONDENCES_ALL table is a central hub within the Receivables schema, with documented foreign key relationships to several key TCA (Trading Community Architecture) and AR tables. These relationships are essential for accurate joins in reporting and integrations.

  • Primary Key Dependency: The AR_CORR_PAY_SCHED_ALL table has a foreign key (CORRESPONDENCE_ID) referencing this table, linking specific payment schedules (invoices) to the dunning letter sent.
  • Foreign Key Dependencies (Parent Tables):
    • HZ_CUST_ACCOUNTS: Via CUSTOMER_ID. Provides customer account details.
    • HZ_CUST_SITE_USES_ALL: Via SITE_USE_ID. Identifies the specific customer site use.
    • HZ_CUST_ACCT_SITES_ALL: Via REMIT_TO_ADDRESS_ID. Provides the remit-to address details.
    • AR_DUNNING_LETTER_SETS: Via REFERENCE1. Identifies the dunning letter set (plan) used.
    • AR_DUNNING_LETTERS_B: Via REFERENCE2. Identifies the specific dunning letter template and level used.