Search Results rev_num




Overview
Explore in AI Chat

Overview

AMW_CONTROLS_ALL_VL is a translated (VL) view owned by the APPS schema within the Oracle E-Business Suite Internal Controls Manager (AMW) module. It presents the complete definition of a Control entity by combining the language-independent attributes stored in the base table AMW_CONTROLS_B with the language-specific, translated descriptive text held in AMW_CONTROLS_TL. The view therefore exposes a single, fully resolved row per control (and control revision) in the session's current language, hiding the underlying translation mechanism from the consumer.

Because Internal Controls Manager supports multiple installed languages, the "TL" table stores translatable columns such as the control name and description per language, while the "_B" table stores the structural, non-translated attributes. The "_VL" view is the canonical reporting and integration surface that fuses the two, and is the object that forms and OA Framework pages, concurrent programs, and interfaces reference when a complete, readable control record is required. This makes the view central to control documentation, audit reporting, and downstream data extraction.

Underlying Base Objects

The view is defined over two base tables in the AMW schema: AMW_CONTROLS_B (non-translated data) and AMW_CONTROLS_TL (translated, language-specific values). A join correlates the language-independent key columns of the "_B" table with the corresponding translated rows of the "_TL" table for the current language, producing one consolidated row per control revision.

The view text aliases the base tables as B and T. It carries forward all structural columns from B—including CONTROL_ID, CONTROL_REV_ID, the audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), and the descriptive translation columns from T (NAME, DESCRIPTION, PHYSICAL_EVIDENCE, VERIFICATION_SOURCE_NAME, and related text fields). The documentation does not enumerate separately maintained base objects beyond these, and the ETRM metadata records no additional referenced objects beyond the two tables implied by the view name and text.

Key Columns

Common Use Cases and Queries

Typical usage retrieves all current, approved controls for reporting or joins the view to control-test, risk, and organization tables for audit dashboards and extracts. The VL view is generally preferred over querying the base tables directly because it already resolves the translated text.

A simple listing of active controls:

  • SELECT control_id, control_rev_id, name, description, control_type, classification FROM apps.amw_controls_all_vl WHERE latest_revision_flag = 'Y' ORDER BY name;

Filtering by lifecycle and category:

  • SELECT control_id, name, approval_status, approval_date FROM apps.amw_controls_all_vl WHERE curr_approved_flag = 'Y' AND category = :p_category;

Joining to a downstream control-test table by key and revision:

  • SELECT v.name, v.description, t.test_result FROM apps.amw_controls_all_vl v, apps.amw_control_tests t WHERE v.control_id = t.control_id AND v.control_rev_id = t.control_rev_id;

When calling from a multi-language environment, the returned NAME and DESCRIPTION reflect the current session language, so integrations should set NLS appropriately before querying the view.