Search Results association_creation_date

Overview

AMW.AMW_ASSESS_PROCEDURE_ASSOCS is a transactional table within the Oracle E-Business Suite Advanced Maintenance (ETRM) module. It stores the details of the association between an assessment and a procedure. In the ETRM data model, assessments represent evaluations performed against an asset, work order, or other maintainable object, while procedures define the ordered set of steps carried out during that evaluation. This table acts as the bridge that links a specific assessment record to the procedure applied to it, capturing the context in which the procedure was attached.

The table is owned by the AMW schema and resides in the APPS_TS_TX_DATA tablespace. It is registered in FND Design Data as AMW.AMW_ASSESS_PROCEDURE_ASSOCS and carries a VALID status. Based on the FK/PK structure mined from the metadata, the heuristic Data Vault classification for this object is standalone; in modeling terms this suggests the table behaves primarily as a link-style entity that resolves a relationship between an assessment and a procedure rather than acting as a hub or a descriptive satellite of either parent.

Key Information Stored

The table contains 31 documented columns. The most important of these are summarized below.

• ASSESS_PROCEDUR_ASSOC_ID — The surrogate primary key (AMW_ASSESS_PROCEDURE_ASSOCS_PK). Uniquely identifies each association row.
• ASSESS_PROCEDURE_ID — Foreign key to AMW.AMW_ASSESS_PROCEDURES_B. Identifies the procedure being associated with the assessment.
• PK1 through PK5 — Composite/legacy key columns retained in the physical design; PK2 through PK5 are documented as "Not currently used." These are business-key candidates in the physical schema, with the surrogate ASSESS_PROCEDUR_ASSOC_ID serving as the enforced primary key.
• OBJECT_TYPE — VARCHAR2(30) describing the parent entity type; the documented possible value is 'ASSESSMENT', identifying the context of the association.
• ASSOCIATION_CREATION_DATE — The date on which the association between the assessment and procedure was created. This column is the primary object of the user's search term and is the business-relevant timestamp for association lineage, distinct from the generic CREATION_DATE audit column.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — Standard Who audit columns capturing insert/update tracking for the row.
• OBJECT_VERSION_NUMBER — Used for optimistic locking to prevent lost updates in concurrent sessions.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield (DFF) structure and segment columns enabling customer-specific extension data.

Common Use Cases and Queries

The table is most commonly referenced when determining which procedure was attached to a given assessment, or when reporting on assessment-to-procedure mappings. A typical query joins to the parent procedure table:

• List procedures for an assessment: SELECT pap.assess_procedure_id, pap.procedure_name, apa.association_creation_date FROM amw_assess_procedure_assocs apa, amw_assess_procedures_b pap WHERE apa.assess_procedure_id = pap.assess_procedure_id AND apa.object_type = 'ASSESSMENT';
• Association volume over time: SELECT TRUNC(association_creation_date) d, COUNT(*) FROM amw_assess_procedure_assocs GROUP BY TRUNC(association_creation_date) ORDER BY d;
• Auditing recent links: SELECT * FROM amw_assess_procedure_assocs WHERE last_update_date > SYSDATE - 7;
• Detecting duplicates: flag assessments with more than one active procedure association for remediation.

Reporting use cases include maintenance cost analysis tied to procedure execution, audit trails of procedure assignment changes, and migration/validation scripts during upgrades between 12.1.1 and 12.2.2.

Related Objects

• AMW.AMW_ASSESS_PROCEDURES_B — The primary referenced parent; join on ASSESS_PROCEDURE_ID = AMW_ASSESS_PROCEDURES_B.ASSESS_PROCEDURE_ID via the documented foreign key.
• AMW_ASSESS_PROCEDURES_TL — Translation table supplying procedure names/descriptions for reporting.
• AMW_ASSESSMENTS / assessment base tables — Logical parent representing the assessment identified by OBJECT_TYPE = 'ASSESSMENT'.
• AMW_ASSESS_PROCEDURE_ASSOCS_PK — Primary key constraint/index guaranteeing uniqueness of ASSESS_PROCEDUR_ASSOC_ID.
• EAM/ETRM assessment APIs and concurrent programs — Processes that create associations and populate OBJECT_VERSION_NUMBER.