Search Results ap_association_id

Overview

AMW.AMW_AP_ASSOCIATIONS is a transaction data table in the Oracle E-Business Suite Audit Management (AMW) module. It stores the associations between audit procedures and the objects to which they apply. As documented in the ETRM metadata, the table "holds information about audit procedure associations. An audit procedure maybe associated to a control in risk library context, to a control in organization context or to a task in project context." This makes the table a central junction point that connects audit procedures to controls, organizations, and project tasks across three distinct functional contexts.

The object resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and all three of its indexes (AMW_AP_ASSOCIATIONS_U1, N1, and N2) are stored in APPS_TS_TX_IDX. The table is owned by the AMW schema and is registered in FND Design Data as AMW.AMW_AP_ASSOCIATIONS. From a Data Vault modeling perspective, the mined FK structure classifies this object heuristically as standalone. In practice it behaves as a link-style association table: it joins an audit procedure revision (via AUDIT_PROCEDURE_REV_ID) to a polymorphic target object whose identity is carried in the PK1–PK5 generic columns, with the context disambiguated by OBJECT_TYPE.

Key Information Stored

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

• AP_ASSOCIATION_ID — the surrogate primary key of the table, defined by the unique index AMW_AP_ASSOCIATIONS_U1 and enforced by the AMW_AP_ASSOCIATIONS_PK constraint. This is the identifier most frequently referenced by "ap_association_id".
• OBJECT_TYPE — a VARCHAR2(30) column that determines the association context. Documented values include 'CTRL_ORG' (organization context), 'PROJECT' (project context), and 'CTRL' (risk library context).
• AUDIT_PROCEDURE_ID — the audit procedure associated with the target object; indexed non-uniquely together with OBJECT_TYPE by AMW_AP_ASSOCIATIONS_N1.
• AUDIT_PROCEDURE_REV_ID — the specific audit procedure revision; a foreign key to AMW.AMW_AUDIT_PROCEDURES_B.
• PK1–PK5 — a polymorphic business-key set whose meaning is context-dependent. In 'PROJECT' context, PK1 is the project identifier, PK2 the organization identifier, PK3 the control identifier, and PK4 the task identifier. In 'CTRL_ORG' context, PK1 is the organization identifier, PK2 the process identifier, and PK3 the control identifier. In 'CTRL' (risk library) context, PK1 is the control identifier, while PK2, PK3, and PK4 remain unused. PK5 is documented as unused in all contexts.
• DESIGN_EFFECTIVENESS and OP_EFFECTIVENESS — track the design and operating effectiveness assessments of the audit procedure within its context.
• ASSOCIATION_CREATION_DATE, APPROVAL_DATE, DELETION_DATE, and DELETION_APPROVAL_DATE — lifecycle timestamps covering association creation, approval, deletion request, and deletion approval.
• SECURITY_GROUP_ID — a foreign key to FND_SECURITY_GROUPS, providing multi-tenant style row-level security partitioning.
• OBJECT_VERSION_NUMBER — used by Oracle's optimistic locking framework to detect concurrent updates.
• Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and the descriptive flexfield columns ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15.

Common Use Cases and Queries

The most common access pattern is retrieving all audit procedures linked to a given control, task, project, or organization. Because the target side is polymorphic, queries typically filter on OBJECT_TYPE alongside the relevant PK column. For example, to find every audit procedure associated with a specific project task:

• SELECT ap_association_id, audit_procedure_id, audit_procedure_rev_id FROM amw.amw_ap_associations WHERE object_type = 'PROJECT' AND pk1 = :project_id AND pk4 = :task_id;
• To retrieve associations for a control in the risk library: ... WHERE object_type = 'CTRL' AND pk1 = :control_id;
• To retrieve associations for a control in an organization: ... WHERE object_type = 'CTRL_ORG' AND pk1 = :org_id AND pk3 = :control_id;
• To list active associations with approved status: ... WHERE deletion_date IS NULL AND approval_date IS NOT NULL;

Reporting use cases include effectiveness trending (aggregating DESIGN_EFFECTIVENESS and OP_EFFECTIVENESS by OBJECT_TYPE over time), audit trail reporting on additions and deletions via the lifecycle date columns, and bulk migration or interface loading through AMW_AUDIT_PROCEDURE_INTERFACE. Joins to AMW_AUDIT_PROCEDURES_B on AUDIT_PROCEDURE_REV_ID enrich the result set with procedure descriptions and revision details. Because SECURITY_GROUP_ID is populated, queries executed in a multi-organization or secured environment must respect security group filtering, typically by joining to FND_SECURITY_GROUPS or relying on the AMW security profile.

Related Objects

The following objects are the most significant relationships for AMW_AP_ASSOCIATIONS, based on the documented FK structure:

• AMW.AMW_AUDIT_PROCEDURES_B — joined on AMW_AP_ASSOCIATIONS.AUDIT_PROCEDURE_REV_ID = AMW_AUDIT_PROCEDURES_B.primary key of the revisions table. This provides the parent audit procedure revision for each association.
• FND_SECURITY_GROUPS — joined on AMW_AP_ASSOCIATIONS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID. Governs row-level security access.
• AMW.AMW_AUDIT_PROCEDURE_INTERFACE — referenced by the child column AP_ASSOCIATION_ID, which points back to AMW_AP_ASSOCIATIONS. This interface table is used to load or modify associations in bulk.
• AMW.AMW_CONTROLS_B (or the corresponding controls view) — although not an explicit FK in the mined structure, controls are the target object in 'CTRL' and 'CTRL_ORG' contexts via PK1/PK3.
• AMW.AMW_PROJECTS_B / AMW.AMW_TASKS_B — target objects in 'PROJECT' context via PK1 and PK4 respectively.
• AMW.AMW_ORGANIZATIONS — target of PK1 in 'CTRL_ORG' context and PK2 in 'PROJECT' context.

These relationships confirm that AMW_AP_ASSOCIATIONS functions as the integration point between audit procedures and the broader audit management hierarchy, with the polymorphic OBJECT_TYPE and PK1–PK5 columns providing the flexibility to represent three distinct association contexts in a single physical structure.