Results for “ar_dunning_letters_tl”

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

Overview

AR_DUNNING_LETTERS_TL is the translation (multi-lingual support, MLS) table for standard dunning letters in Oracle Receivables. Dunning letters are the correspondence sent to customers to demand payment on past-due invoices, and the base table AR_DUNNING_LETTERS holds the language-independent definition of each letter and its associated dunning configuration. The _TL table stores the language-dependent descriptive attributes, principally the letter name and description, so the same dunning letter can be presented to customers and collectors in multiple installed languages.

The object resides in the AR schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. ETRM classifies it heuristically as standalone within a Data Vault modeling convention. In practice it functions as a satellite attached to the dunning letter hub: the surrogate key DUNNING_LETTER_ID behaves as the hub/business key, and LANGUAGE plus the descriptive columns behave as satellite payload. Modeling it as a standalone satellite keyed on (DUNNING_LETTER_ID, LANGUAGE) is the recommended interpretation.

Key Information Stored

The documented physical schema in ETRM 12.2.2 exposes 11 columns. The principal ones are:

• DUNNING_LETTER_ID — surrogate foreign key to the base AR_DUNNING_LETTERS table, identifying which dunning letter this translation belongs to.
• LANGUAGE — the NLS language code of the translated row (for example US, FR, DE).
• LETTER_NAME — the language-specific name of the dunning letter shown to users.
• DESCRIPTION — the translated description of the letter.
• SOURCE_LANG — the language in which the row was originally authored, used by the MLS/translation framework.
• ZD_EDITION_NAME — the editioning column introduced with EBS 12.2 Online Patching, used to segregate rows across AD editions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns tracking row creation and last modification.

The surrogate primary key is AR_DUNNING_LETTERS_TL_PK over (DUNNING_LETTER_ID, LANGUAGE). Two unique indexes are documented as business-key candidates: AR_DUNNING_LETTERS_TL_U1 on (DUNNING_LETTER_ID, LANGUAGE, ZD_EDITION_NAME) and AR_DUNNING_LETTERS_TL_U2 on (LETTER_NAME, LANGUAGE, ZD_EDITION_NAME). The U2 index confirms that a given letter name must be unique per language within an edition.

Common Use Cases and Queries

Typical scenarios include reporting dunning letters in a specific language, validating that translations exist for all installed languages, and feeding letter layouts into correspondence printing. A simple join to the base table retrieves language-specific names:

• SELECT b.dunning_letter_id, t.letter_name, t.description, t.language FROM ar_dunning_letters b, ar_dunning_letters_tl t WHERE b.dunning_letter_id = t.dunning_letter_id AND t.language = USERENV('LANG');
• Identify untranslated letters: SELECT b.dunning_letter_id FROM ar_dunning_letters b WHERE NOT EXISTS (SELECT 1 FROM ar_dunning_letters_tl t WHERE t.dunning_letter_id = b.dunning_letter_id AND t.language = 'FR');
• Detect duplicate business keys: SELECT letter_name, language, COUNT(*) FROM ar_dunning_letters_tl GROUP BY letter_name, language HAVING COUNT(*) > 1;

Because of editioning, all production queries should filter or be aware of ZD_EDITION_NAME in 12.2.x environments.

Related Objects

• AR_DUNNING_LETTERS — base table joined on DUNNING_LETTER_ID; holds the language-independent letter definition and configuration.
• AR_DUNNING_LETTERS_TL_PK / _U1 / _U2 — primary and unique indexes enforcing identity and business-key uniqueness.
• AR_DUNNING_PROMPT_TL — companion MLS table for dunning prompt text, following the same (ID, LANGUAGE) translation pattern.
• FND_LANGUAGES — reference table validating LANGUAGE codes.
• AR_DUNNING_LETTER_API or AR_DUNNING_LETTERS_PKG — programmatic interfaces that maintain dunning letter and translation rows.
• FND_TERRITORIES / FND_NEW_MESSAGES — supporting lookups used by the MLS framework.