Results for “control_purpose_id”

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

Overview

AMW_CONTROL_PURPOSES is a transactional table within the Oracle E-Business Suite Internal Controls Manager (AMW) module. It stores the purpose definitions that describe why a specific control exists within an organization's internal control framework. In the context of regulatory compliance initiatives such as Sarbanes-Oxley, controls are documented not merely as procedural steps but as activities tied to explicit purposes—for example, ensuring financial statement accuracy, safeguarding assets, or preventing fraud. This table captures those purpose assignments and links each one back to a specific control revision.

The table resides in the AMW schema and is classified as VALID in both Oracle EBS 12.1.1 and 12.2.2. Based on a heuristic mined from its foreign key structure, the object leans toward a satellite classification in a Data Vault model. This reflects its role as a descriptive, attribute-bearing table that hangs off the AMW_CONTROLS_B hub through the CONTROL_REV_ID reference, rather than acting as an independent hub or an associative link. The physical schema documented for 12.1.1 contains 28 columns.

Key Information Stored

The table's structural spine is its surrogate primary key, CONTROL_PURPOSE_ID, enforced by the AMW_CONTROL_PURPOSES_PK constraint and mirrored by the unique index AMW_CONTROL_PURPOSES_U1. Surrounding that key are the standard Oracle EBS who-columns—LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN—which support auditability and multi-user change tracking.

• CONTROL_REV_ID — Foreign key to AMW_CONTROLS_B, tying each purpose record to a specific revision of a control definition.
• PURPOSE_CODE — The business-meaningful code identifying the purpose category or type associated with the control.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enabling row-level security partitioning across operating units or organizations.
• EFFECTIVE_DATE_FROM / EFFECTIVE_DATE_TO — Date-range columns supporting date-tracked or historically effective purpose assignments.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the AMW forms and APIs for concurrent update control.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The flexible descriptive flexfield (DFF) columns, providing extensible storage for customer-specific purpose attributes.

The distinction between the surrogate key and any business key is important: CONTROL_PURPOSE_ID is a system-generated identifier, while CONTROL_REV_ID plus PURPOSE_CODE typically constitute the natural business combination used in application logic.

Common Use Cases and Queries

Typical reporting scenarios include audit readiness assessments, control-mapping reports, and segregation-of-duties analysis, where analysts must answer which controls serve a given purpose or which purposes have been assigned to a control revision. A representative query joining the purpose table to its parent control table follows:

• SELECT cp.control_purpose_id, cp.purpose_code, cp.control_rev_id FROM amw.amw_control_purposes cp WHERE cp.control_rev_id = :control_rev_id;
• Joining to AMW_CONTROLS_B to retrieve control names alongside their purposes for audit workpapers.
• Filtering on EFFECTIVE_DATE_FROM and EFFECTIVE_DATE_TO to reproduce point-in-time purpose assignments for a given reporting period.
• Restricting results by SECURITY_GROUP_ID to honor organizational data access rules.

Related Objects

The most significant related objects derive from the documented foreign key and primary key relationships:

• AMW_CONTROLS_B — Parent table referenced via CONTROL_REV_ID; supplies the control revision context.
• AMW_CONTROL_PURPOSES_PK / AMW_CONTROL_PURPOSES_U1 — Primary key constraint and unique index on CONTROL_PURPOSE_ID.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for row-level security.
• Child tables within the AMW module that reference CONTROL_PURPOSE_ID for purpose-detail breakdowns, where such relationships exist.
• AMW forms and concurrent programs that maintain control purpose definitions and depend on OBJECT_VERSION_NUMBER for concurrency.