Search Results ar_dunning_letter_sets_uk1

Overview

The AR_DUNNING_LETTER_SETS table is a core configuration table within the Oracle E-Business Suite Receivables (AR) module. It serves as the master definition for dunning letter sequences, which are systematic, escalating series of collection notices sent to customers with overdue invoices. The table's primary role is to define the logical set or sequence name, which is then associated with specific dunning letters and their corresponding aging periods through child tables. This configuration is central to the automated collections process, allowing businesses to enforce consistent, policy-driven customer communication based on payment delinquency.

Key Information Stored

The table stores the fundamental identifiers for each dunning sequence. The primary column is DUNNING_LETTER_SET_ID, a unique system-generated identifier serving as the primary key for relationships. The NAME column holds the unique, user-defined name of the dunning letter set (e.g., "STANDARD_SEQUENCE," "HIGH_RISK_CUSTOMERS"), which is enforced by a unique key constraint (AR_DUNNING_LETTER_SETS_UK1). Additional descriptive columns, such as DESCRIPTION, may also be present, though not explicitly listed in the provided metadata, to provide further detail on the set's purpose.

Common Use Cases and Queries

The primary use case is the assignment of a dunning letter set to a customer profile or profile class, thereby determining the collection correspondence sequence for associated customers. Administrators query this table to review or audit configured sequences. A common reporting query involves joining to customer profile tables to see which customers are assigned to which dunning set.

Sample Query: To list all defined dunning letter sets and a count of active customer profiles using each:

SELECT ds.name AS dunning_set_name,
       COUNT(cp.customer_profile_id) AS assigned_profile_count
FROM ar.ar_dunning_letter_sets ds
LEFT JOIN ar.ar_customer_profiles cp ON ds.dunning_letter_set_id = cp.dunning_letter_set_id
WHERE cp.status = 'A'
GROUP BY ds.name
ORDER BY ds.name;

Related Objects

The AR_DUNNING_LETTER_SETS table is a parent to several key Receivables tables, as defined by its foreign key relationships:

• AR_DUNNING_LETTER_SET_LINES: This is the primary child table (DUNNING_LETTER_SET_ID), defining the specific letters, aging periods, and order within the sequence.
• AR_CUSTOMER_PROFILES: Individual customer profiles reference a set via DUNNING_LETTER_SET_ID to define their specific dunning policy.
• AR_CUSTOMER_PROFILE_CLASSES: Profile classes reference a set via DUNNING_LETTER_SET_ID, allowing the dunning policy to be inherited by customers assigned to that class.
• AR_CORRESPONDENCES_ALL: References the set in the REFERENCE1 column, linking generated correspondence records to the originating dunning set.