Search Results amw_objective_associations




Overview
Explore in AI Chat

Overview

AMW_OBJECTIVE_ASSOCIATIONS is a table within the AMW schema, belonging to the Oracle E-Business Suite Internal Controls Manager (ICM) product family. It stores the association records that link process objectives to processes and risks managed within the ICM framework. In practical terms, this table captures the many-to-many mapping between a Process Objective (defined in AMW_PROCESS_OBJECTIVES_B) and the process or risk artifacts to which that objective applies, together with approval, effective-dating, and soft-deletion metadata that governs the lifecycle of each association.

The table is documented in ETRM 12.1.1 with 37 columns and is present in 12.2.2 with the same owner and structure. Its heuristic Data Vault classification is standalone, meaning it does not behave as a classic hub, link, or satellite when mined solely by foreign-key structure. From a modeling perspective, the association semantics (objective-to-process/risk) suggest it functions as a link-style table in a dimensional sense, but the ETRM metadata does not formally classify it as such. It should be treated as an independent transactional entity keyed by its own surrogate identifier.

Key Information Stored

The primary key is AMW_OBJECTIVE_ASSOCIATIONS_PK, defined on OBJECTIVE_ASSOCIATION_ID. A unique index, AMW_OBJECTIVE_ASSOCIATIONS_U1, also exists on OBJECTIVE_ASSOCIATION_ID, confirming it as the sole business-key candidate in the documented metadata.

The PK1 through PK5 columns appear in the documented schema but carry no described business meaning beyond legacy or denormalized key storage.

Common Use Cases and Queries

Typical reporting scenarios include determining which objectives apply to a given process or risk, auditing approved versus pending associations, and reviewing effective-dating for compliance reporting. A common pattern joins the association table back to its parent objective:

  • List active associations for a security group: SELECT ao.objective_association_id, po.name FROM amw_objective_associations ao, amw_process_objectives_b po WHERE ao.process_objective_id = po.process_objective_id AND ao.security_group_id = :p_security_group_id AND ao.deletion_date IS NULL.
  • Audit approvals: filter on approval_date IS NOT NULL and deletion_approval_date to reconstruct the approval lifecycle.
  • Effective-date analysis: constrain on effective_date_from and effective_date_to relative to a reporting date.
  • Soft-deletion reconciliation: separate live associations (deletion_date IS NULL) from archived ones for historical trend reporting.

Related Objects

The ETRM metadata documents two foreign-key relationships from this table, which form the primary join paths:

  • AMW_PROCESS_OBJECTIVES_B — Joined via PROCESS_OBJECTIVE_ID; supplies the objective definition associated to processes or risks.
  • FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID; governs access control and data partitioning.

Beyond documented FKs, dependent reporting typically reaches AMW process and risk tables through the OBJECT_TYPE discriminator, and the association records are consumed by ICM workflow and approval components that read APPROVAL_DATE and DELETION_APPROVAL_DATE. Developers extending ICM functionality commonly reference AMW_OBJECTIVE_ASSOCIATIONS through the standard AMW API layer rather than direct DML, given the WHO columns and optimistic locking enforced via OBJECT_VERSION_NUMBER.