Search Results assess_procedur_assoc_id

Overview

AMW_ASSESS_PROCEDURE_ASSOCS is a table within the AMW schema, belonging to the Oracle Internal Controls Manager (ICM) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store the details of the association between an assessment and a procedure. In practice, this means the table acts as the persistence layer for the many-to-many linkage that ties an assessment definition to the specific control procedures being evaluated within it. Each row represents one such association record, carrying its own surrogate identity, audit trail columns, descriptive attributes, and references back to the parent assessment procedure entity.

From a data modeling perspective, the mined relationship metadata classifies this object heuristically as a standalone entity, with a primary key constraint named AMW_ASSESS_PROCEDURE_ASSOCS_PK. The table is documented with 31 physical columns in the 12.1.1 ETRM schema. Its only documented foreign key references AMW_ASSESS_PROCEDURES_B through the ASSESS_PROCEDURE_ID column, indicating that the association row is anchored to a base assessment procedure record. Because the primary key is a single synthetic identifier rather than a composite of participating entity keys, the table is best treated as an associative entity that is self-keyed rather than a pure intersection table.

Key Information Stored

The most operationally significant columns in AMW_ASSESS_PROCEDURE_ASSOCS are the following:

• ASSESS_PROCEDUR_ASSOC_ID — the surrogate primary key for the row, defined by AMW_ASSESS_PROCEDURE_ASSOCS_PK. Note the documented spelling omits the "E" in PROCEDURE; queries and joins must use this exact column name.
• ASSESS_PROCEDURE_ID — foreign key to AMW_ASSESS_PROCEDURES_B; identifies the assessment procedure to which the association belongs.
• OBJECT_TYPE — discriminator column identifying the category of the associated object, enabling polymorphic-style associations.
• ASSOCIATION_CREATION_DATE — a dedicated timestamp recording when the association itself was created, distinct from the standard audit columns.
• PK1 through PK5 — a set of generic key columns that typically store the identifying values of the associated entity in a flexible, denormalized manner.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the standard Oracle EBS WHO columns that audit who created and last changed each association row.
• OBJECT_VERSION_NUMBER — supports Oracle's optimistic locking (OAF/BC4J) to prevent concurrent update conflicts.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns, allowing customers to extend the association record with site-specific information without schema changes.

Common Use Cases and Queries

Typical usage centers on reporting and validating which procedures are linked to a given assessment. Analysts commonly join this table to AMW_ASSESS_PROCEDURES_B to resolve procedure names:

SELECT a.ASSESS_PROCEDUR_ASSOC_ID, a.ASSESS_PROCEDURE_ID, a.OBJECT_TYPE, p.PROCEDURE_NAME
FROM   AMW.AMW_ASSESS_PROCEDURE_ASSOCS a,
       AMW.AMW_ASSESS_PROCEDURES_B p
WHERE  a.ASSESS_PROCEDURE_ID = p.ASSESS_PROCEDURE_ID;

Other scenarios include extracting DFF attribute values for a specific association, auditing association creation timelines using ASSOCIATION_CREATION_DATE and CREATION_DATE, detecting duplicate or orphaned associations, and troubleshooting optimistic lock errors via OBJECT_VERSION_NUMBER.

Related Objects

• AMW_ASSESS_PROCEDURES_B — referenced by ASSESS_PROCEDURE_ID; the parent procedure definition.
• AMW_ASSESS_PROCEDURE_ASSOCS_PK — the primary key constraint on ASSESS_PROCEDUR_ASSOC_ID.
• Other AMW assessment tables — procedures, assessments, and results tables that share the ASSESS_PROCEDURE_ID lineage.
• ICM/OA framework pages — the Internal Controls Manager UI reads and writes this table through the AMW schema during assessment configuration.