Search Results control_assertion_id

Overview

The AMW.AMW_CONTROL_ASSERTIONS table is a core data object within the Oracle E-Business Suite (EBS) Application Management Workspace (AMW) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to store and manage the many-to-many relationships between internal controls and control assertions. In the context of governance, risk, and compliance (GRC) processes, a control is a specific procedure or policy, while an assertion defines the objective or claim that the control is designed to validate (e.g., completeness, accuracy, authorization). This table acts as the junction table that formally links a specific revision of a control (CONTROL_REV_ID) to a standardized assertion code (ASSERTION_CODE), enabling structured risk assessment and audit workflows within the AMW framework.

Key Information Stored

The table's structure is designed to track the relationship's metadata, audit trail, and extensibility. The primary identifier is the CONTROL_ASSERTION_ID, a unique system-generated number for each relationship record. The two critical foreign key columns are CONTROL_REV_ID, which references a specific version of a control in AMW_CONTROLS_B, and ASSERTION_CODE, a lookup code defining the type of assertion. Standard Oracle WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide a full audit trail. The table also includes a set of 15 descriptive flexfield segments (ATTRIBUTE1-15) for custom extensions, an OBJECT_VERSION_NUMBER for optimistic locking in the application's middle tier, and a SECURITY_GROUP_ID for Virtual Private Database (VPD) implementations.

Common Use Cases and Queries

This table is central to queries that analyze control coverage against financial or operational assertions, a fundamental task for SOX compliance and internal audits. Common reporting use cases include listing all assertions mapped to a specific control, identifying controls that address a particular assertion type, and generating gap analyses. A typical query to retrieve all assertion relationships for a given control revision would be:

• SELECT aca.assertion_code, aca.control_assertion_id, aca.creation_date FROM amw_control_assertions aca WHERE aca.control_rev_id = :p_control_rev_id ORDER BY aca.assertion_code;

For integration or data migration scripts, inserts into this table must populate the CONTROL_REV_ID and ASSERTION_CODE correctly while letting the application generate the CONTROL_ASSERTION_ID sequence value. Updates typically involve modifying the flexfield attributes or the EFFECTIVE_DATE_FROM column.

Related Objects

The AMW_CONTROL_ASSERTIONS table has defined relationships with other key AMW tables, primarily through foreign key constraints. The documented relationship is:

• Foreign Key (CONTROL_REV_ID): References TABLE: AMW.AMW_CONTROLS_B. This links each record in AMW_CONTROL_ASSERTIONS to a specific control revision, which is the master definition of the control itself.

The table's primary key, CONTROL_ASSERTION_ID, is enforced by the unique index AMW_CONTROL_ASSERTIONS_U1. While not explicitly listed in the provided metadata, the ASSERTION_CODE column typically references a standard Oracle Application Object Library (FND) lookup type (such as 'AMW_ASSERTION'), allowing validation against a defined set of permissible assertion values maintained within the EBS lookup tables.