Search Results oe_agreements_tl_u2




Overview
Explore in AI Chat

Overview

QP.OE_AGREEMENTS_TL is the translation (TL) table for pricing agreements in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It stores the translatable attribute of an agreement — specifically the agreement name — in each language installed in the database. The object resides in the QP (Advanced Pricing) schema, is owned by the applications database user, and is classified as VALID in the ETRM metadata. Its FND Design Data entry, QP.OE_AGREEMENTS_TL, confirms registration within the Oracle Application Object Library, which governs its concurrent-program access and Multi-Org behavior.

Functionally, the table is the language-dependent child of QP.OE_AGREEMENTS_B, which holds the language-independent agreement header. Every row is keyed by the pair (AGREEMENT_ID, LANGUAGE), so a single pricing agreement exists once per installed language. The metadata assigns a heuristic Data Vault classification of standalone; in modeling terms this suggests the table is best treated as a reference/attribute structure tied directly to the base agreement hub rather than as an independent hub, link, or satellite. Retention of LAST_UPDATE_DATE and related Who columns nonetheless supports satellite-style change tracking when the object is reverse-engineered into a warehouse.

Key Information Stored

The table contains eleven documented columns. The most significant are:

  • AGREEMENT_ID (NUMBER) — Surrogate key referencing the primary key of QP.OE_AGREEMENTS_B, identifying which agreement the translated name belongs to.
  • LANGUAGE (VARCHAR2) — The language of the translated text for this row; combined with AGREEMENT_ID it forms the primary key, OE_AGREEMENTS_TL_PK.
  • SOURCE_LANG (VARCHAR2) — The language the text mirrors. If the name has not yet been translated into LANGUAGE, changes made to the source-language row are reflected here.
  • NAME (VARCHAR2, 240) — The agreement name, the actual translatable content of the table.
  • REVISION (VARCHAR2, 50) — Revision reference for the pricing agreement.
  • AGREEMENT_SOURCE_CODE (VARCHAR2, 30) — Indicates the source system that created the agreement; documented values are 'PAGR' and 'MCTR'.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns for audit and concurrency.

Two unique indexes act as business-key candidates. OE_AGREEMENTS_TL_U1 enforces uniqueness on (AGREEMENT_ID, LANGUAGE), which is the translation identity. The index the user searched for, OE_AGREEMENTS_TL_U2, enforces uniqueness on the broader business key (NAME, AGREEMENT_SOURCE_CODE, REVISION, LANGUAGE) — effectively preventing duplicate agreement names per source system, revision, and language. Both indexes reside in the APPS_TS_TX_IDX tablespace, while the table itself is stored in APPS_TS_TX_DATA with PCT Free 10.

Common Use Cases and Queries

The primary reporting requirement is retrieving the agreement name in a specific language while joining to the base table for the remaining attributes. A typical pattern is:

  • Translate an agreement ID to a display name: SELECT tl.name FROM qp.oe_agreements_tl tl WHERE tl.agreement_id = :id AND tl.language = USERENV('LANG');
  • List all language variants of an agreement to audit translation coverage: SELECT language, source_lang, name FROM qp.oe_agreements_tl WHERE agreement_id = :id ORDER BY language;
  • Reconcile against the base header: SELECT b.agreement_id, tl.name, tl.revision, tl.agreement_source_code FROM qp.oe_agreements_b b, qp.oe_agreements_tl tl WHERE b.agreement_id = tl.agreement_id AND tl.language = 'US';
  • Detect untranslated rows by comparing LANGUAGE to SOURCE_LANG, or verify index U2 coverage when diagnosing duplicate-name errors raised by pricing agreement maintenance forms.

Because the table is a TL object, ad-hoc inserts or updates outside the Oracle Pricing APIs risk violating the source-language mirroring logic and the two unique indexes.

Related Objects

The most significant dependencies are:

  • QP.OE_AGREEMENTS_B — The base (non-translatable) pricing agreement table; join on AGREEMENT_ID and LANGUAGE.
  • QP.OE_AGREEMENTS_TL# — The internal synonym/implementation object referenced by OE_AGREEMENTS_TL per the metadata dependency listing.
  • QP.OE_AGREEMENTS_TL_PK — Primary key constraint on (AGREEMENT_ID, LANGUAGE).
  • QP.OE_AGREEMENTS_TL_U1 — Unique index on (AGREEMENT_ID, LANGUAGE).
  • QP.OE_AGREEMENTS_TL_U2 — Unique index on (NAME, AGREEMENT_SOURCE_CODE, REVISION, LANGUAGE).

Oracle Pricing exposes the agreement header and its translated name through the same maintenance forms and pricing APIs (for example, the Oracle Pricing public APIs used to create and update agreements), which read and write OE_AGREEMENTS_TL indirectly through OE_AGREEMENTS_B. The table does not reference any database object itself, per the ETRM dependency report.