Search Results work_elem_id




Overview
Explore in AI Chat

Overview

AMW_PROCESS_EXT_VL is a view owned by the APPS schema in Oracle E-Business Suite, belonging to the AMW product family — Internal Controls Manager (ICM). It exposes the extensible (descriptive flexfield style) attributes defined for processes, presenting them in a language-translated, denormalized form suitable for reporting, integration, and inquiry screens. Within ICM, processes are the auditable activities that an organization defines, tests, and certifies as part of its internal control framework. Because different organizations require different metadata against each process, ICM uses a fixed set of numbered attribute columns (C_EXT_ATTR1..40, N_EXT_ATTR1..20, D_EXT_ATTR1..10, UOM_EXT_ATTR1..20, and TL_EXT_ATTR1..n) that are configured per implementation.

The "_VL" suffix indicates this is a language/translated view. It combines the base extension record (carrying the keys EXTENSION_ID, WORK_ID, WORK_ELEM_ID and ATTR_GROUP_ID) with translation rows for the language-sensitive attributes (the TL_EXT_ATTR columns and SOURCE_LANG/LANGUAGE), returning one row per process extension per installed language. This makes it the natural source for any report or interface that must display process attributes in the user's current session language.

Underlying Base Objects

The ETRM metadata records no documented base objects for this view in 12.2.2. Based on the view text, the SELECT references at least two underlying objects aliased B (the extension base table, providing EXTENSION_ID, WORK_ID, WORK_ELEM_ID, ATTR_GROUP_ID and the non-translated C_/N_/D_/UOM_ attributes) and a translation table supplying the TL_EXT_ATTR columns, SOURCE_LANG and LANGUAGE. The join is on EXTENSION_ID and LANGUAGE. The AMW extensible-attribute tables follow the internal controls manager naming convention AMW_PROCESS_EXT_B / AMW_PROCESS_EXT_TL, joined to form AMW_PROCESS_EXT_VL, although the exact table names must be confirmed in the target instance via ALL_VIEWS / DBA_VIEWS and ALL_DEPENDENCIES.

WORK_ID is the key column the user searched for. In AMW tables, WORK_ID typically identifies the owning process (work) record, with WORK_ELEM_ID identifying the individual element within that work item to which the extension row is attached. The view therefore sits as a child of the process/work hierarchy.

Key Columns

  • EXTENSION_ID — Primary key of the extension row; the join key between base and translation records.
  • WORK_ID — Identifier of the process/work item the extension belongs to; the principal link back to the process hierarchy and the column most commonly used to filter results to a single process.
  • WORK_ELEM_ID — Identifier of the specific element within the work item to which the attributes apply.
  • ATTR_GROUP_ID — Identifies the attribute group / context that governs which of the numbered attribute columns are meaningful.
  • C_EXT_ATTR1..40 — Character attributes.
  • N_EXT_ATTR1..20 — Numeric attributes.
  • D_EXT_ATTR1..10 — Date attributes.
  • UOM_EXT_ATTR1..20 — Unit-of-measure attributes accompanying numeric values.
  • TL_EXT_ATTR1..n — Translated (language-dependent) character attributes.
  • LANGUAGE / SOURCE_LANG — The language of the returned row and the source language of the definition.

Common Use Cases and Queries

Typical uses include reporting the configured attributes for a process, extracting extension data for an interface or data warehouse, and loading attributes into a custom inquiry form. Because the columns are generic, reports must first determine, via ATTR_GROUP_ID and the attribute-group definition, which numbered columns are populated for a given context.

Retrieve all attributes for a specific process:

  • SELECT extension_id, work_id, work_elem_id, attr_group_id, c_ext_attr1, c_ext_attr2, n_ext_attr1, d_ext_attr1
  • FROM apps.amw_process_ext_vl
  • WHERE work_id = :p_work_id;

List the available languages for a process extension:

  • SELECT extension_id, language, tl_ext_attr1
  • FROM apps.amw_process_ext_vl
  • WHERE work_id = :p_work_id
  • ORDER BY language;

Join to the process master to obtain process names:

  • SELECT p.work_id, p.name, e.c_ext_attr1, e.n_ext_attr1
  • FROM apps.amw_process_ext_vl e, apps.amw_processes_vl p
  • WHERE e.work_id = p.work_id
  • AND e.language = USERENV('LANG');

Confirm underlying objects in a given instance:

  • SELECT referenced_name, referenced_type
  • FROM all_dependencies
  • WHERE name = 'AMW_PROCESS_EXT_VL' AND owner = 'APPS';