Results for “amw_control_objectives”

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

Overview

AMW_CONTROL_OBJECTIVES is a table within the Oracle E-Business Suite Internal Controls Manager (ICM) module, denoted by the AMW product prefix. It stores the relationships between controls and objectives, functioning as an associative (junction) entity that links individual control revisions to the organizational or compliance objectives they are designed to satisfy. This mapping is fundamental to the ICM data model, which supports regulatory compliance frameworks such as Sarbanes-Oxley by enabling organizations to trace how specific controls mitigate or address defined objectives.

The ETRM metadata notes that this table is documented as part of the AMW - Internal Controls Manager product, which is flagged as obsolete in later releases. In EBS 12.1.1 and 12.2.2 environments, ICM functionality was progressively superseded by Oracle Advanced Controls and, subsequently, Oracle Risk Management Cloud. The table remains present in the physical schema of installations where ICM was deployed, but it may be empty or vestigial in many databases. The metadata explicitly records that the object is "Not implemented in this database" for the source ETRM reference, indicating that availability is instance-dependent.

Based on the foreign key structure — a single primary key (CONTROL_OBJECTIVE_ID) with references outward to AMW_CONTROLS_B and FND_SECURITY_GROUPS — the heuristic Data Vault classification is satellite-leaning. This suggests the table behaves as a descriptive relationship store attached to core control and objective hubs rather than as a standalone hub or pure link entity.

Key Information Stored

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

• CONTROL_OBJECTIVE_ID — The surrogate primary key, defined by the unique index AMW_CONTROL_OBJECTIVES_U1 and the constraint AMW_CONTROL_OBJECTIVES_PK. It uniquely identifies each control-to-objective relationship record.
• CONTROL_REV_ID — Foreign key referencing AMW_CONTROLS_B, identifying the specific control revision associated with the objective.
• OBJECTIVE_CODE — A business-key attribute holding the code that identifies the objective being linked to the control.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant or organizational data segregation.
• EFFECTIVE_DATE_FROM / EFFECTIVE_DATE_TO — Date range columns enabling effective-dated tracking of the control-to-objective association.
• OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrent update control within the Oracle Applications framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO columns providing audit trail information.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield (DFF) columns allowing extensible, client-specific attributes to be captured.

Distinguishing the surrogate primary key (CONTROL_OBJECTIVE_ID) from business-key candidates is important: the unique index AMW_CONTROL_OBJECTIVES_U1 enforces uniqueness on CONTROL_OBJECTIVE_ID, while the true business meaning of a row is the combination of CONTROL_REV_ID and OBJECTIVE_CODE.

Common Use Cases and Queries

Typical reporting scenarios involve identifying which controls map to which objectives, auditing coverage of objectives by controls, and reviewing effective-dated relationships. A common query pattern joins the relationship table to the controls base table:

• Listing all controls associated with a given objective code, filtered by effective dates.
• Reporting on objectives that lack any linked control (gap analysis).
• Extracting control-to-objective mappings for compliance documentation or certification cycles.
• Joining to FND_SECURITY_GROUPS to present data within the correct organizational context.

A representative SQL pattern would be: SELECT co.CONTROL_OBJECTIVE_ID, co.OBJECTIVE_CODE, cb.CONTROL_ID FROM AMW_CONTROL_OBJECTIVES co JOIN AMW_CONTROLS_B cb ON co.CONTROL_REV_ID = cb.CONTROL_REV_ID WHERE co.EFFECTIVE_DATE_TO IS NULL OR co.EFFECTIVE_DATE_TO > SYSDATE; Because the object is obsolete in many instances, consultants should first confirm the table exists and contains rows before building dependent reports.

Related Objects

The most significant related objects, derived from the documented foreign key relationships and the broader ICM schema, include:

• AMW_CONTROLS_B — Joined via CONTROL_REV_ID; the base table holding control definitions.
• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID; enforces data security and organizational partitioning.
• AMW_OBJECTIVES (or the corresponding objective base table) — the target of the relationship, referenced by OBJECTIVE_CODE.
• Standard Oracle WHO audit columns derived from FND_* tables, plus the descriptive flexfield structures associated with the ATTRIBUTE columns.

Because AMW is obsolete, downstream reporting objects, concurrent programs, and pages that historically referenced AMW_CONTROL_OBJECTIVES may no longer be supported in current EBS releases, and organizations are encouraged to migrate to supported risk and control management solutions.