Results for “amw_constraints_vl”

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

Overview

AMW_CONSTRAINTS_VL is a seed data and reporting view in the Oracle E-Business Suite Internal Controls Manager (AMW) product. It returns information about constraints, which in the AMW context represent the assertions, objectives, or conditions tied to controls and risks within an organization's internal control framework. The view belongs to the APPS schema and is documented as VALID within the ETRM reference metadata for both Oracle EBS 12.1.1 and 12.2.2.

The suffix "_VL" denotes a "view with translation" pattern widely used throughout EBS. The view joins a base table (AMW_CONSTRAINTS_B, the "_B" or base table) with a translation table (AMW_CONSTRAINTS_TL), filtering the translation by the session language via USERENV('LANG'). This design allows descriptive fields such as the constraint name and description to be presented in the user's currently selected language without duplicating those attributes across every row. For reporting and integration purposes, consumers need not be aware of the underlying language mechanics; querying the view returns one row per constraint with translated text resolved transparently.

Underlying Base Objects

The view is defined over two documented base objects:

• AMW_CONSTRAINTS_B — the base table holding language-independent constraint attributes, aliased "B" in the view definition.
• AMW_CONSTRAINTS_TL — the translation table holding the NAME and DESCRIPTION columns, aliased "T" in the view definition.

The join condition is B.CONSTRAINT_ID = T.CONSTRAINT_ID AND T.LANGUAGE = USERENV('LANG'). Both tables are owned by APPS. The view exposes B.ROWID as ROW_ID, alongside all standard EBS audit, security, and descriptive-flexfield columns from the base table, and the NAME (CONSTRAINT_NAME) and DESCRIPTION (CONSTRAINT_DESCRIPTION) from the translation table. The "_B/_TL/_VL" triplet is the canonical EBS pattern for multilingual seed data, and AMW_CONSTRAINTS_VL conforms to it exactly.

Key Columns

• ROW_ID — the ROWID of the base table row; useful for row-addressable operations.
• CONSTRAINT_ID / CONSTRAINT_REV_ID — primary identifier and revision of the constraint.
• CONSTRAINT_NAME / CONSTRAINT_DESCRIPTION — translated descriptive text resolved to the session language.
• START_DATE / END_DATE — validity window for the constraint version.
• RISK_ID / CONTROL_ID / OBJECTIVE_CODE — foreign references linking the constraint to associated risks, controls, and objectives.
• TYPE_CODE / CLASSIFICATION — categorization attributes.
• APPROVAL_STATUS / ENTERED_BY_ID — approval workflow state and the entering user.
• SECURITY_GROUP_ID / OBJECT_VERSION_NUMBER — multi-tenant (security group) context and optimistic locking version.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — the standard descriptive flexfield columns.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The principal use case is querying constraints in the user's native language for internal-controls reporting, audit extraction, and integration feeds. Because the translation filter uses USERENV('LANG'), the same query returns language-appropriate text depending on session settings, which is essential for global implementations.

A typical query retrieves all currently valid constraints with their translated names:

• SELECT constraint_id, constraint_name, type_code, approval_status
• FROM amw_constraints_vl
• WHERE TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, SYSDATE);

To join constraints to risks and controls for a control-framework report:

• SELECT c.constraint_name, c.risk_id, c.control_id, c.objective_code
• FROM amw_constraints_vl c
• WHERE c.approval_status = 'APPROVED'
• ORDER BY c.constraint_name;

To inspect flexfield-encoded attributes, query ATTRIBUTE_CATEGORY and ATTRIBUTE1...15 directly. Integration developers should note that the view is read-only; inserts and updates must target AMW_CONSTRAINTS_B and AMW_CONSTRAINTS_TL respectively, and translated columns must be maintained separately for each installed language.