Search Results amw_objective_associations_u1

Overview

AMW.AMW_OBJECTIVE_ASSOCIATIONS is a transactional table within the Oracle E-Business Suite Application Management Workspace (AMW) schema. It stores the association records that relate process objectives to the processes or risks they govern. In the context of Oracle EBS 12.1.1 and 12.2.2, this object functions as the intersection between the AMW_PROCESS_OBJECTIVES_B definition table and the risk or process entities referenced within the AMW module. The table carries a status of VALID and is registered in FND Design Data as AMW.AMW_OBJECTIVE_ASSOCIATIONS, with its data segment residing in the APPS_TS_TX_DATA tablespace and its indexes in APPS_TS_TX_IDX.

The metadata classifies this table heuristically as standalone under a Data Vault lens. As a modeling suggestion, this means the table is not strictly a hub, link, or satellite in the canonical Data Vault sense, though its structure — a surrogate key, foreign keys to parent entities, standard WHO audit columns, and descriptive flexfield attributes — most closely resembles a link table with embedded satellite attributes. Practitioners building a vault model should treat OBJECTIVE_ASSOCIATION_ID as the hub key, PROCESS_OBJECTIVE_ID and the risk/process reference as link components, and the attribute columns plus the effective-dating and approval fields as satellite payload.

Key Information Stored

The table contains 37 documented columns. The most significant are summarized below, distinguishing the surrogate primary key from business-key candidates.

• OBJECTIVE_ASSOCIATION_ID — the surrogate primary key (AMW_OBJECTIVE_ASSOCIATIONS_PK/PK). It is also the sole column of the unique index AMW_OBJECTIVE_ASSOCIATIONS_U1, making it the principal business-key candidate for uniqueness enforcement.
• PROCESS_OBJECTIVE_ID — foreign key referencing AMW_PROCESS_OBJECTIVES_B, identifying the parent objective being associated.
• OBJECT_TYPE — a VARCHAR2(30) discriminator holding RISK or PROCESS, determining whether the associated object is a risk or a process.
• PK1 — holds the value of the referenced primary key, either Risk_Id or Process_Id, depending on OBJECT_TYPE.
• PK2 through PK5 — documented but not currently used, retained for schema extensibility.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant and security-group filtering.
• OBJECT_VERSION_NUMBER — optimistic locking control for concurrent updates.
• EFFECTIVE_DATE_FROM / EFFECTIVE_DATE_TO — date-range validity of the association.
• ASSOCIATION_CREATION_DATE, APPROVAL_DATE, DELETION_DATE, DELETION_APPROVAL_DATE — lifecycle timestamps covering creation, approval, soft deletion, and deletion approval.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield structure and segment columns.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical usage centers on reporting which objectives map to which processes or risks, and on lifecycle analysis of those associations. The two secondary indexes, AMW_OBJECTIVE_ASSOCIATIONS_N1 (PROCESS_OBJECTIVE_ID, OBJECT_TYPE) and AMW_OBJECTIVE_ASSOCIATIONS_N2 (OBJECT_TYPE), support these access paths.

• Retrieve active associations for an objective:

  SELECT a.objective_association_id, a.object_type, a.pk1
  FROM   amw.amw_objective_associations a
  WHERE  a.process_objective_id = :p_objective_id
  AND    a.object_type IN ('RISK','PROCESS')
  AND    TRUNC(SYSDATE) BETWEEN a.effective_date_from
                            AND NVL(a.effective_date_to, SYSDATE)
  AND    a.deletion_date IS NULL;

• List all risks linked across objectives, filtering by security group:

  SELECT * FROM amw.amw_objective_associations
  WHERE  object_type = 'RISK'
  AND    security_group_id = :p_security_group;

• Join to the parent objective to produce an objective-to-risk matrix:

  SELECT b.objective_name, a.object_type, a.pk1
  FROM   amw.amw_process_objectives_b b,
         amw.amw_objective_associations a
  WHERE  b.process_objective_id = a.process_objective_id
  AND    a.object_type = 'RISK';

• Audit lifecycle: count associations by approval and deletion status for governance dashboards.

Related Objects

The following objects are the most significant references or dependencies, based on the documented foreign keys and primary key structure.

• AMW.AMW_PROCESS_OBJECTIVES_B — parent table joined via PROCESS_OBJECTIVE_ID; supplies the objective definition.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for security-group scoping.
• AMW_OBJECTIVE_ASSOCIATIONS_U1 — unique index on OBJECTIVE_ASSOCIATION_ID, enforcing the business key.
• AMW_OBJECTIVE_ASSOCIATIONS_N1 — non-unique index on PROCESS_OBJECTIVE_ID and OBJECT_TYPE.
• AMW_OBJECTIVE_ASSOCIATIONS_N2 — non-unique index on OBJECT_TYPE.
• AMW.AMW_RISKS and AMW.AMW_PROCESSES (or their base equivalents) — logical targets referenced through PK1 when OBJECT_TYPE is RISK or PROCESS.
• FND_FLEX_VALUES / flexfield infrastructure — validates ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 DFF segments.