Results for “amw_control_assertions_u1”

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

Overview

AMW.AMW_CONTROL_ASSERTIONS is a transactional table within the Oracle E-Business Suite governance, risk, and compliance (GRC) data model, owned by the AMW schema. The table stores the relations between controls and assertions, effectively recording which financial statement assertions (such as completeness, existence, valuation, and rights and obligations) are addressed by each control. It is a core reference object for audit, internal controls, and Sarbanes-Oxley style compliance reporting within Oracle EBS 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the mined relationship heuristics classify this object as satellite-leaning. It carries a surrogate primary key, descriptive attributes, standard who-columns, and a foreign key into the control revision table, which is characteristic of a satellite attached to a control-revision parent rather than a true hub or link. The classification is offered as a modeling suggestion only; the object is a native EBS table, not a Data Vault artifact.

Key Information Stored

The table contains 28 documented columns. The most significant are:

• CONTROL_ASSERTION_ID — the surrogate primary key (defined by AMW_CONTROL_ASSERTIONS_PK) and the sole column of the unique index AMW_CONTROL_ASSERTIONS_U1. It uniquely identifies each control-assertion relationship line.
• CONTROL_REV_ID — denotes the control revision identifier, linking the row to a specific control version (foreign key to AMW_CONTROLS_B).
• ASSERTION_CODE — lookup code identifying the assertion associated with the control.
• EFFECTIVE_DATE_FROM and EFFECTIVE_DATE_TO — the validity window over which the control-assertion relationship is in force.
• OBJECT_VERSION_NUMBER — supports optimistic locking during concurrent updates.
• SECURITY_GROUP_ID — supports data segregation in hosted or multi-tenant environments.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — a descriptive flexfield (DFF) structure and its 150-character segments, providing extensible attribute storage.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording creation and maintenance history.

The surrogate key (CONTROL_ASSERTION_ID) is distinct from the unique index that enforces it; no separate business-key candidate is documented, meaning uniqueness is currently guaranteed only by the surrogate identifier.

Common Use Cases and Queries

Typical usage centers on identifying which assertions a control addresses and reporting on control coverage across the assertion set. Reporting queries commonly join to the control revision parent to resolve control identity and attributes.

• Retrieve all assertions for a given control revision: SELECT ca.CONTROL_ASSERTION_ID, ca.ASSERTION_CODE, ca.EFFECTIVE_DATE_FROM, ca.EFFECTIVE_DATE_TO FROM AMW.AMW_CONTROL_ASSERTIONS ca WHERE ca.CONTROL_REV_ID = :control_rev_id;
• Point-in-time assertion coverage: SELECT ca.ASSERTION_CODE FROM AMW.AMW_CONTROL_ASSERTIONS ca WHERE ca.CONTROL_REV_ID = :control_rev_id AND TRUNC(SYSDATE) BETWEEN ca.EFFECTIVE_DATE_FROM AND NVL(ca.EFFECTIVE_DATE_TO, TRUNC(SYSDATE)) + 1;
• Join to the control parent for descriptive reporting: SELECT cb.*, ca.ASSERTION_CODE FROM AMW.AMW_CONTROLS_B cb, AMW.AMW_CONTROL_ASSERTIONS ca WHERE cb.CONTROL_REV_ID = ca.CONTROL_REV_ID;
• DFF extraction for custom compliance reporting by querying ATTRIBUTE_CATEGORY and ATTRIBUTE1..15.
• Audit logging and concurrency checks relying on LAST_UPDATE_DATE and OBJECT_VERSION_NUMBER.

Related Objects

The most significant dependent and related objects are:

• AMW.AMW_CONTROLS_B — the control base/revision parent referenced through CONTROL_REV_ID (AMW_CONTROL_ASSERTIONS.CONTROL_REV_ID → AMW_CONTROLS_B).
• AMW_CONTROL_ASSERTIONS_PK — the primary key constraint on CONTROL_ASSERTION_ID.
• AMW_CONTROL_ASSERTIONS_U1 — the unique index on CONTROL_ASSERTION_ID stored in APPS_TS_TX_IDX; the object the user searched for.
• FND descriptive flexfield definitions — referenced via ATTRIBUTE_CATEGORY (AMW.AMW_CONTROL_ASSERTIONS design data).
• ASSERTION_CODE lookup — the assertion lookup value set that resolves the stored code to a displayable assertion name.

Together these objects support the control-to-assertion mapping used throughout EBS GRC and audit reporting.