Results for “amw_risk_associations”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMW_RISK_ASSOCIATIONS is a table within the Oracle E-Business Suite Internal Controls Manager (ICM) module, delivered under the AMW product schema. It records the relationships between a Risk—identified by RISK_ID and its revision through RISK_REV_ID—and the business objects to which that risk has been associated. The OBJECT_TYPE column determines the nature of the associated object and is documented as accepting values of PROCESS or PROCESS_ORG, indicating that a risk may be linked either to an abstract process definition or to a process scoped to a specific organization. This table therefore functions as the associative bridge that lets ICM track where a given risk applies across the process hierarchy.

The module carrying this table is flagged as obsolete in the supplied ETRM documentation, and the table is noted as not implemented in the current database. Consequently, the object is best regarded as a legacy artifact retained for historical reference or upgrade continuity rather than an active, query-driven structure. From a Data Vault modeling perspective, the mined heuristic classification is standalone; this suggests treating AMW_RISK_ASSOCIATIONS as an independent hub-like entity keyed on its own identifier rather than as a dependent satellite of an existing hub. This classification is a modeling suggestion derived from the FK topology and should be validated against actual usage before being adopted in any warehouse design.

Key Information Stored

The table is defined with 42 columns in the documented 12.1.1 physical schema. The most significant columns are:

• RISK_ASSOCIATION_ID — the primary key of AMW_RISK_ASSOCIATIONS_PK and the column referenced by the user's search term. It is also the sole column of unique index AMW_RISK_ASSOCIATIONS_U1, making it both the surrogate primary key and the documented business-key candidate.
• RISK_ID — identifies the risk to which the association belongs; the parent risk context is carried by RISK_REV_ID.
• RISK_REV_ID — foreign key to AMW_RISKS_B, tying each association row to a specific revision of the underlying risk.
• OBJECT_TYPE — the discriminator that determines what kind of object the association targets, documented as PROCESS or PROCESS_ORG.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling which security group owns the row and therefore row-level access.
• EFFECTIVE_DATE_FROM / EFFECTIVE_DATE_TO — the validity window during which the association is considered in force, supporting date-effective reporting.
• RISK_LIKELIHOOD_CODE / RISK_IMPACT_CODE — the assessed likelihood and impact at the point of association, used by risk scoring and heat-map reporting.
• MATERIAL / MATERIAL_VALUE — flags and quantifies whether the association is material for reporting or disclosure purposes.
• ASSOCIATION_CREATION_DATE, APPROVAL_DATE, DELETION_DATE, DELETION_APPROVAL_DATE — the lifecycle timestamps that record when an association was created, approved, deleted, or had its deletion approved.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle descriptive flexfield columns for extensible, customer-defined attributes.
• OBJECT_VERSION_NUMBER — the optimistic locking column used to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the WHO columns audited by Oracle EBS.

Common Use Cases and Queries

In an active ICM implementation, the principal reporting scenario is retrieving every object associated with a given risk, filtered by object type. A representative query pattern joins the association table to the risk base table and the security group:

SELECT ra.risk_association_id,
       ra.risk_id,
       ra.object_type,
       ra.effective_date_from,
       ra.effective_date_to
FROM   amw_risk_associations ra,
       amw_risks_b          rb,
       fnd_security_groups  sg
WHERE  ra.risk_rev_id      = rb.risk_rev_id
AND    ra.security_group_id = sg.security_group_id
AND    ra.risk_id           = :risk_id;

Other practical scenarios include building audit-trail reports from the creation, approval, deletion, and deletion-approval timestamps; producing risk heat maps by aggregating RISK_LIKELIHOOD_CODE and RISK_IMPACT_CODE per association; and identifying associations whose effectiveness window has expired. Because the table is documented as obsolete and not implemented, these patterns are primarily useful for historical data extraction, migration analysis, or understanding legacy ICM structures during an upgrade from 12.1.1 to 12.2.2.

Related Objects

The FK topology documented in the ETRM metadata defines the most significant dependencies:

• AMW_RISKS_B — referenced via RISK_REV_ID; supplies the risk revision against which each association is defined.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; governs row-level security visibility.
• AMW_RISK_ASSOCIATIONS_PK / AMW_RISK_ASSOCIATIONS_U1 — the primary key constraint and its backing unique index on RISK_ASSOCIATION_ID, which enforce row uniqueness.

The PK1 through PK5 columns in the documented schema are likely part of a composite relationship to the associated PROCESS or PROCESS_ORG objects, although the exact referenced tables are not enumerated in the metadata provided. These should be confirmed against the full ICM data model before being used in joins.