Search Results amw_ap_tasks

Overview

AMW_AP_TASKS is the association table that links audit procedures to the project tasks that implement them within Oracle Internal Controls Manager (ICM), the AMW product family. The table stores one row for every task associated with a given audit procedure, thereby describing which project tasks carry out or evidence a specific procedure. In Oracle EBS 12.1.1 and 12.2.2 the object resides in the AMW schema and is documented as obsolete; the ETRM metadata notes that it is not implemented in the reference database, so its presence in a given instance depends on whether ICM was licensed and deployed.

From a modeling perspective, the Data Vault classification mined from the foreign-key structure is link. This is a suggestion rather than a documented designation: AMW_AP_TASKS resolves a many-to-many relationship between audit procedures and project tasks, which is the characteristic role of a link table. The table also carries descriptive attributes of its own, so a hybrid link-satellite treatment may be appropriate in a warehouse design. The surrogate primary key is AP_TASK_ID, enforced by the unique index AMW_AP_TASKS_PK.

Key Information Stored

The table contains 30 documented columns. The most significant are the identifiers and the standard EBS audit columns.

• AP_TASK_ID — surrogate primary key, sourced from AMW_AP_TASKS_PK and mirrored by unique index AMW_AP_TASKS_U1.
• AUDIT_PROCEDURE_ID — identifies the audit procedure to which the task association belongs.
• TASK_ID — foreign key to PA_TASKS; the project task that implements the procedure.
• PROJECT_ID — foreign key to PA_PROJECTS_ALL; the project that owns the task.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS; governs row-level access for multi-organization and delegated administration scenarios.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J framework to detect concurrent updates.
• ORIG_SYSTEM_REFERENCE — identifier of the source record when the association was migrated from an external system.
• REQUESTOR_ID — the user associated with the originating request for the association.
• SOURCE_CODE — code indicating the origin of the row (for example, manual entry versus import).
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit trail columns maintained by the application.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF-ready descriptive flexfield segment columns.

The unique index AMW_AP_TASKS_U2 on (AUDIT_PROCEDURE_ID, PROJECT_ID, TASK_ID) is the business-key candidate. It guarantees that a given audit procedure cannot be associated twice with the same project task, which is the principal integrity rule for this object. Note the distinction between the surrogate AP_TASK_ID and this composite natural key.

Common Use Cases and Queries

Typical reporting asks which tasks implement a procedure, which procedures a project supports, and whether any procedure lacks an associated task. A representative query joins the association to both parents:

SELECT t.ap_task_id,
       t.audit_procedure_id,
       t.project_id,
       t.task_id,
       p.name         AS project_name,
       tk.task_name
FROM   amw.ap_ap_tasks   t,
       pa.pa_tasks       tk,
       pa.pa_projects_all p
WHERE  t.task_id    = tk.task_id
AND    t.project_id = p.project_id
AND    t.audit_procedure_id = :procedure_id;

Coverage analysis — finding procedures with no task assigned — is commonly performed with an outer join or NOT EXISTS against AMW_AP_TASKS. Auditors also extract the association set for an entire project to reconcile ICM scope against project structures in PA. Because SECURITY_GROUP_ID is present, any extraction run under a delegated administration model should filter on the caller's accessible security groups.

Related Objects

• PA_TASKS — joined on AMW_AP_TASKS.TASK_ID = PA_TASKS.TASK_ID; supplies task number, name and scheduling attributes.
• PA_PROJECTS_ALL — joined on AMW_AP_TASKS.PROJECT_ID = PA_PROJECTS_ALL.PROJECT_ID; supplies the project that owns the task.
• FND_SECURITY_GROUPS — joined on AMW_AP_TASKS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; controls row visibility.
• AMW_AP_TASKS_PK / AMW_AP_TASKS_U1 / AMW_AP_TASKS_U2 — the supporting constraints and unique indexes.
• AMW audit procedure definitions — the parent object referenced by AUDIT_PROCEDURE_ID, which supplies procedure text and ownership.
• ICM audit and procedure reporting views — AMW-supplied views that expose procedure-to-task mappings to the ICM user interface and to programmatic extraction.

Because the AMW product family is documented as obsolete, implementations migrating off ICM should treat AMW_AP_TASKS as an archival source and map its rows to the replacement GRC or audit repository before decommissioning the AMW schema.