Search Results amw_ctrl_ext_tl




Overview
Explore in AI Chat

Overview

AMW_CTRL_EXT_TL is a translation (TL) table within the AMW schema — the Oracle Internal Controls Manager (ICM) module of Oracle E-Business Suite. In EBS 12.1.1 and 12.2.2, ICM stores the definition of internal controls, their organization mappings, testing plans, and results. The extensible-attribute framework allows customers to capture organization-specific information against a control that Oracle does not ship as seeded columns. AMW_CTRL_EXT_TL holds the language-specific (translated) values of those extensible attributes, providing one row per control extension per installed language.

The table is a key member of the extensibility layer that surrounds the base control definition. Where the base extension table carries the attribute values in the source language, AMW_CTRL_EXT_TL carries the translated attribute values (TL_EXT_ATTR1 through TL_EXT_ATTR40) plus the LANGUAGE and SOURCE_LANG columns that drive the multi-language lookup behavior. This design follows the standard EBS _TL pattern: a base table and a _TL table linked by EXTENSION_ID.

From a Data Vault modeling perspective, ETRM classifies this object as standalone (no outbound foreign keys other than the CONTROL_ID reference to CST_REVENUE_COGS_CONTROL). A reasonable heuristic suggestion is to treat AMW_CTRL_EXT_TL as a satellite attached to the control hub (CONTROL_ID) and extension hub (EXTENSION_ID), because it holds descriptive, language-dependent attributes rather than transactional relationships.

Key Information Stored

The 53 documented columns fall into three functional groups:

  • Primary key / surrogate: EXTENSION_ID — the surrogate key identifying the extension record. In EBS _TL design, EXTENSION_ID plus LANGUAGE typically forms the unique key of the row.
  • Work and grouping context: WORK_ID, WORK_ELEM_ID, ATTR_GROUP_ID, and WORK_TYPE_ID identify the work item, element, grouping, and type to which the control extension belongs.
  • Language control: LANGUAGE stores the language of the row; SOURCE_LANG records the language in which the value was originally entered, enabling the standard EBS translation and fallback mechanism.
  • Translated attribute payload: TL_EXT_ATTR1 through TL_EXT_ATTR40 hold the translated values of the extensible attributes. These are the business data users see when a non-base language is selected.
  • Foreign key: CONTROL_ID links the extension to the control definition; this is the primary business-key candidate documented in the ETRM relationship data, referencing CST_REVENUE_COGS_CONTROL.
  • Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide the standard EBS who/when audit trail.

Common Use Cases and Queries

AMW_CTRL_EXT_TL is typically queried in three scenarios: (1) reporting translated control attributes for a given language, (2) joining the base and translated extension tables to reconcile source-language and translated values, and (3) building ICM control-detail extracts for audit, SOX compliance, or risk dashboards.

A representative query retrieves the translated attribute values for a control in a target language:

  • SELECT c.control_id, t.language, t.tl_ext_attr1, t.tl_ext_attr2, t.tl_ext_attr3
  • FROM amw.amw_ctrl_ext_tl t
  • JOIN amw.amw_ctrl_ext b ON b.extension_id = t.extension_id
  • WHERE t.control_id = :control_id AND t.language = USERENV('LANG');

Because the table stores up to forty translated attributes, reporting tools generally pivot via ATTR_GROUP_ID to display only the attributes relevant to a given attribute group. Audit extracts commonly compare SOURCE_LANG values against the base extension table to confirm translation completeness across installed languages.

Related Objects

  • CST_REVENUE_COGS_CONTROL — referenced via CONTROL_ID; the control definition that the extension decorates.
  • AMW_CTRL_EXT (base table) — the source-language counterpart sharing EXTENSION_ID, joined to reconcile translated and untranslated values.
  • AMW_CTRL_EXT_B / base extension hierarchy — parent records that define WORK_ID, WORK_ELEM_ID, ATTR_GROUP_ID, and WORK_TYPE_ID context.
  • FND_LANGUAGES — reference table for the LANGUAGE and SOURCE_LANG codes.
  • AMW_CTRL_ATTR_GROUPS — defines the attribute groups whose members populate TL_EXT_ATTR1..40.
  • AMW_WORK_ITEMS / AMW_WORK_ELEMENTS — provide the work context used by WORK_ID and WORK_ELEM_ID.
  • FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to user names for audit reporting.