Search Results amw_exceptions_b_pk

Overview

The AMW_EXCEPTIONS_B table is the Exceptions Base Table within the Oracle E-Business Suite Internal Controls Manager (ICM) module, identified by the product short code AMW. ICM was an application designed to help organisations document, monitor, and certify their internal control environment, including the tracking of deviations, policy exceptions, and control weaknesses surfaced during audits or control testing activities. The AMW_EXCEPTIONS_B table serves as the primary transactional store for such exception records, capturing the identity of the affected object, the before-and-after state of its key values, and the nature and timing of the change or exception.

It is worth noting that AMW - Internal Controls Manager is documented as obsolete in the relevant ETRM metadata, and the table is marked as "Not implemented in this database" in the provided documentation excerpt. In Oracle EBS 12.1.1 and 12.2.2 environments where the module was never licensed or installed, this and all associated AMW objects will be absent from the schema entirely. Where it does exist, it is owned by the AMW schema and consists of 26 documented columns.

From a heuristic Data Vault modelling perspective, the foreign key structure — with AMW_EXCEPTIONS_TL depending on this table via EXCEPTION_ID and no other tables referencing it as a parent — suggests a hub-leaning classification. That is, the table behaves somewhat like a central business entity holding the canonical exception identity, around which descriptive and language-dependent satellites cluster.

Key Information Stored

The table's primary surrogate key is EXCEPTION_ID, enforced by the constraint AMW_EXCEPTIONS_B_PK. This identifier is the single most important column and is reused as the join key in the translation table. Beyond the surrogate key, the most significant columns fall into several functional groups.

• OBJECT_TYPE and EXCEPTION_OBJECT_ID identify the controlled object or entity to which the exception relates, allowing exceptions to be classified by business entity type and pointed at the specific record affected.
• The OLD_PK1 through OLD_PK6 columns hold the prior primary key component values of the affected object.
• The parallel NEW_PK1 through NEW_PK6 columns hold the post-change or exception-related key values, enabling before-and-after comparison of up to six key segments.
• TRANSACTION_TYPE classifies the nature of the exception event, while TRANSACTION_DATE and END_DATE bracket its effective period.
• APPROVED_FLAG records whether the exception has been reviewed and approved through the ICM workflow.
• Standard audit and multi-tenancy columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, SECURITY_GROUP_ID, and OBJECT_VERSION_NUMBER.

No additional unique business-key index beyond the primary key is documented in the metadata, so EXCEPTION_ID remains the sole guaranteed unique identifier.

Common Use Cases and Queries

Typical usage centres on exception reporting, control testing audits, and change-tracking analytics. A frequent query pattern joins the base table to its translation table to retrieve descriptive text in the user's language:

SELECT b.exception_id, b.object_type, b.transaction_type,
       b.transaction_date, b.approved_flag, t.description
FROM   amw_exceptions_b b,
       amw_exceptions_tl t
WHERE  b.exception_id = t.exception_id
AND    t.language = USERENV('LANG')
AND    b.transaction_date >= :from_date;

Analysts often isolate unapproved exceptions using APPROVED_FLAG, or reconstruct before-and-after key values by selecting the OLD_PKn and NEW_PKn column pairs. Reporting on OBJECT_TYPE distributions and trending TRANSACTION_DATE volumes supports control-effectiveness dashboards.

Related Objects

• AMW_EXCEPTIONS_TL — the translation (language) child table, joined on AMW_EXCEPTIONS_TL.EXCEPTION_ID = AMW_EXCEPTIONS_B.EXCEPTION_ID. This is the single documented foreign key relationship and the most important related object.
• AMW_EXCEPTIONS_B_PK — the primary key constraint on EXCEPTION_ID, essential for efficient single-row lookups.
• The broader AMW Internal Controls Manager schema (control, process, and audit objects) where present, though these are not documented here and the module is obsolete.

Because no other tables were documented as referencing this object, AMW_EXCEPTIONS_TL is the principal dependent and the natural companion in virtually all retrieval logic.