Results for “amw_controls_b”

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

Overview

AMW_CONTROLS_B is the base (non-translatable) table of the Internal Controls Manager (AMW) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the language-independent attributes of organizational controls — the assertions, objectives, purposes, classification, approval state, and workflow context that define each control and its revision. The AMW module itself is documented as obsolete, meaning the object is retained primarily for historical reference and for supporting dependent objects inherited from earlier releases rather than for active functional configuration. Its presence in a given instance is therefore implementation-dependent; the ETRM metadata explicitly notes it is not implemented in every database.

From a Data Vault modeling perspective, the mined metadata suggests AMW_CONTROLS_B behaves as a hub-leaning entity. Its primary key, AMW_CONTROLS_B_PK, is defined on CONTROL_REV_ID, a surrogate identifier for a control revision rather than a natural business key. A secondary unique index, AMW_CONTROLS_B_U2, spans CONTROL_REV_ID and CONTROL_ID, offering a composite business-key candidate. The table also references external parents such as FND_SECURITY_GROUPS and CST_REVENUE_COGS_CONTROL, and is referenced by numerous child tables that carry the revision identifier. This pattern — a central keyed entity with multiple dependent satellites and link-style children — is characteristic of a hub surrounded by satellites.

Key Information Stored

The table contains 49 documented columns. The most functionally significant are:

• CONTROL_REV_ID — surrogate primary key (AMW_CONTROLS_B_PK); the revision identifier that anchors the control record.
• CONTROL_ID — the underlying control number; combined with CONTROL_REV_ID in unique index AMW_CONTROLS_B_U2.
• CONTROL_TYPE — classifies the control (for example, manual versus automated behavior).
• CATEGORY — the control category used for reporting and grouping.
• CLASSIFICATION and CONTROL_LOCATION — describe the nature and placement of the control within the organization.
• AUTOMATION_TYPE and APPLICATION_ID — indicate whether the control is automated and, if so, which application supports it.
• PREVENTIVE_CONTROL, DETECTIVE_CONTROL, DISCLOSURE_CONTROL, KEY_MITIGATING — boolean flags expressing the control's role in risk mitigation and disclosure.
• APPROVAL_STATUS, APPROVAL_DATE, CURR_APPROVED_FLAG, LATEST_REVISION_FLAG — capture workflow approval state and revision currency.
• REV_NUM, END_DATE, OBJECT_VERSION_NUMBER — support revision tracking and optimistic locking.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level access.
• SOURCE and ORIG_SYSTEM_REFERENCE — identify the originating system or interface.
• The ATTRIBUTE1–15 and ATTRIBUTE_CATEGORY descriptive flexfield columns — hold installation-specific control attributes.

Common Use Cases and Queries

Typical usage centers on reporting the current approved revision of a control and joining to its translatable text. The following patterns are representative:

• Retrieve the latest revision: SELECT CONTROL_ID, CONTROL_REV_ID, APPROVAL_STATUS FROM AMW_CONTROLS_B WHERE LATEST_REVISION_FLAG = 'Y'.
• Filter to currently approved controls: SELECT CONTROL_REV_ID, APPROVAL_DATE FROM AMW_CONTROLS_B WHERE CURR_APPROVED_FLAG = 'Y'.
• Join to the translation table for display: SELECT b.CONTROL_ID, t.CONTROL_NAME FROM AMW_CONTROLS_B b, AMW_CONTROLS_TL t WHERE b.CONTROL_REV_ID = t.CONTROL_REV_ID.
• Analyze preventive versus detective coverage by category: SELECT CATEGORY, PREVENTIVE_CONTROL, COUNT(*) FROM AMW_CONTROLS_B GROUP BY CATEGORY, PREVENTIVE_CONTROL.
• Reconcile sourcing: SELECT ORIG_SYSTEM_REFERENCE, COUNT(*) FROM AMW_CONTROLS_B WHERE SOURCE IS NOT NULL GROUP BY ORIG_SYSTEM_REFERENCE.

Because the module is obsolete, queries are most often run during audit, migration assessment, or custom reporting rather than in live functional configuration.

Related Objects

The following objects depend on or reference AMW_CONTROLS_B through CONTROL_REV_ID, forming the surrounding control model:

• AMW_CONTROLS_TL — holds the translatable control text; joined on CONTROL_REV_ID.
• AMW_CONTROL_ASSERTIONS — links controls to assertions on CONTROL_REV_ID.
• AMW_CONTROL_OBJECTIVES — associates controls with objectives.
• AMW_CONTROL_PURPOSES — associates controls with purposes.
• AMW_CONTROL_ASSOCIATIONS — connection records keyed by CONTROL_REV_ID.
• AMW_FIN_ITEM_ACC_CTRL — financial item to accountable control mapping.
• RCI_ORG_CERT_CTRLS_F — organization certification controls foundation.
• FND_SECURITY_GROUPS — parent of SECURITY_GROUP_ID for access control.
• CST_REVENUE_COGS_CONTROL — referenced from CONTROL_ID.