Search Results amw_control_purposes_nu1

Overview

The AMW_CONTROL_PURPOSES table is a core data object within the Oracle E-Business Suite (EBS) Application Management Workspace (AMW) module, specifically for releases 12.1.1 and 12.2.2. Its primary function is to store and manage the defined purposes or objectives associated with internal controls. This table enables organizations to categorize controls based on their intended effect, such as ensuring operational efficiency, safeguarding assets, or promoting compliance with financial reporting regulations. By linking purposes to specific control revisions, it forms a critical component of the governance, risk, and compliance (GRC) framework within EBS, allowing for structured risk assessment and control monitoring.

Key Information Stored

The table's structure includes standard administrative columns and specific columns for control purpose management. The most critical columns are:

• CONTROL_PURPOSE_ID: The primary key (PK) unique identifier for each control purpose record.
• CONTROL_REV_ID: A foreign key (FK) linking the purpose to a specific control revision in the AMW_CONTROLS_B table.
• PURPOSE_CODE: Stores a code classifying the purpose (e.g., Preventive, Detective, Corrective, or organization-specific values).
• EFFECTIVE_DATE_FROM: Defines the start date from which the purpose association is active for the control.
• OBJECT_VERSION_NUMBER: Used for optimistic locking to manage concurrent data updates.
• Standard Who Columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN): For audit trails.
• ATTRIBUTE1-15, ATTRIBUTE_CATEGORY, SECURITY_GROUP_ID: Support Descriptive Flexfields (DFFs) and multi-org security.

Common Use Cases and Queries

This table is primarily accessed for control analysis, reporting, and integration within the AMW module. Common scenarios include generating reports that list all controls tagged with a specific purpose, such as all detective controls, or validating control design during implementation or audit reviews. A typical query involves joining with the main controls table to extract purpose information.

Sample Query: Retrieving active controls and their associated purpose codes.

SELECT acb.control_name,
       acp.purpose_code,
       acp.effective_date_from
FROM amw.amw_control_purposes acp,
     amw.amw_controls_b acb
WHERE acp.control_rev_id = acb.control_rev_id
  AND acb.status_code = 'ACTIVE'
  AND acp.effective_date_from <= SYSDATE
  AND (acp.effective_date_to IS NULL OR acp.effective_date_to >= SYSDATE);

This data is also critical for underlying AMW APIs and business logic that manage control lifecycles and evaluations.

Related Objects

The AMW_CONTROL_PURPOSES table has defined relationships with other key AMW tables, primarily through foreign key constraints.

• Primary Key: The table's primary key is AMW_CONTROL_PURPOSES_PK on the CONTROL_PURPOSE_ID column.
• Foreign Key (Outbound): The CONTROL_REV_ID column is a foreign key referencing the AMW.AMW_CONTROLS_B table. This establishes that every control purpose must be associated with a valid control revision record.
• Indexes: Two indexes optimize access:
  • AMW_CONTROL_PURPOSES_U1: Unique index on CONTROL_PURPOSE_ID.
  • AMW_CONTROL_PURPOSES_NU1: Non-unique index on CONTROL_REV_ID for efficient joins to the parent controls table.

This table is a child of AMW_CONTROLS_B and may be referenced by various AMW reporting views or APIs that aggregate control and purpose data for user interfaces.