Search Results exception_object_id

Overview

AMW.AMW_EXCEPTIONS_B is a transactional table within the Oracle E-Business Suite Application Management Workspace (AMW) schema. Its documented purpose is to "store the organization and process variation exceptions," meaning it captures records where a specific organizational unit, process, or configuration element deviates from an expected or approved state. In Oracle EBS 12.1.1 and 12.2.2, the _B suffix denotes the base (language-independent) table of a translatable entity, paired with a _TL table that holds the translated descriptive attributes. The table resides in the APPS_TS_TX_DATA tablespace, consistent with transaction-level data rather than reference or seed data.

The heuristic Data Vault classification mined from its foreign key structure is hub-leaning. This suggests that, in a dimensional or Data Vault modeling exercise, AMW_EXCEPTIONS_B behaves primarily like a hub entity — a central list of distinct exception identities — rather than a pure link (association) or satellite (descriptive history) table. Modelers incorporating AMW data into a warehouse should therefore consider treating EXCEPTION_ID as a hub business key.

Key Information Stored

The table contains 26 documented columns. The surrogate primary key is EXCEPTION_ID, enforced by the constraint AMW_EXCEPTIONS_B_PK. No unique business-key index is documented beyond this primary key, so EXCEPTION_ID is the authoritative identifier.

The most significant columns are:

• EXCEPTION_ID — the numeric primary key identifying each exception record.
• OBJECT_TYPE — a VARCHAR2(30) classifying the entity type involved in the exception (for example, the kind of organization or process object).
• OLD_PK1 through OLD_PK6 — up to six VARCHAR2(100) columns holding the primary key of the entity that was deleted or replaced.
• NEW_PK1 through NEW_PK6 — up to six VARCHAR2(100) columns holding the primary key of the entity that was added or substituted.
• TRANSACTION_TYPE — VARCHAR2(30) describing the nature of the change that generated the exception.
• TRANSACTION_DATE and END_DATE — the effective and termination dates of the exception.
• EXCEPTION_OBJECT_ID — the requested column; a numeric field storing the exception object identifier associated with the row.
• APPROVED_FLAG — records whether the exception has been approved.
• OBJECT_VERSION_NUMBER — supports optimistic locking during concurrent updates.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — plus SECURITY_GROUP_ID for hosted environments.

Common Use Cases and Queries

Typical scenarios include auditing organizational or process variation exceptions, tracing the before-and-after state of replaced entities, and reporting on approval status. A common lookup by the requested identifier follows:

SELECT EXCEPTION_ID, OBJECT_TYPE, TRANSACTION_TYPE, TRANSACTION_DATE, APPROVED_FLAG FROM AMW.AMW_EXCEPTIONS_B WHERE EXCEPTION_OBJECT_ID = :p_object_id;

To reconstruct the change captured by an exception, join the OLD_PK and NEW_PK column sets against the referenced entity for the given OBJECT_TYPE. To filter approved exceptions within a date range, constrain APPROVED_FLAG = 'Y' and TRANSACTION_DATE BETWEEN :p_from AND :p_to. Because the table is translatable, queries that require the translated description should join to AMW_EXCEPTIONS_TL on EXCEPTION_ID, optionally restricting LANGUAGE to the session language.

Related Objects

• AMW_EXCEPTIONS_TL — the translation (language) table; joins via AMW_EXCEPTIONS_TL.EXCEPTION_ID → AMW_EXCEPTIONS_B.EXCEPTION_ID. This is the documented foreign-key relationship referencing this table.
• AMW_EXCEPTIONS_B_PK — the primary key constraint on EXCEPTION_ID defining row uniqueness.
• Downstream AMW exception-processing views and concurrent programs that read the base table to present exception details, matching on EXCEPTION_ID and OBJECT_TYPE.
• Referenced business entities identified by OBJECT_TYPE, resolved through the OLD_PK and NEW_PK column sets rather than declared foreign keys.