Results for “eam_job_completion_txns”

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

Overview

EAM_JOB_COMPLETION_TXNS is the work order completion transaction history table within Oracle Enterprise Asset Management (EAM). The object resides in the EAM schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to record the history of work order completion transactions, capturing the financial, organizational, and asset-level details generated when a maintenance work order is completed and the associated asset activity is posted.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is link. This classification suggests the table functions as a transactional intersection record that associates multiple business entities — the work order, the asset, the accounting period, and the transaction type — rather than acting as a standalone hub of a single business key. Analysts designing a Data Vault or dimensional layer over EAM should treat EAM_JOB_COMPLETION_TXNS accordingly, resolving its foreign keys into the corresponding hub and satellite structures.

Key Information Stored

The table contains 44 documented columns in the ETRM 12.2.2 schema. The surrogate primary key is TRANSACTION_ID, enforced by the EAM_JOB_COMPLETION_TXNS_PK constraint and mirrored by the unique index EAM_JOB_COMPLETION_TXNS_U1, which serves as the business-key candidate. The most significant columns fall into several functional groups:

• Transaction identity and timing: TRANSACTION_ID (surrogate PK), TRANSACTION_DATE, TRANSACTION_TYPE (FK to MTL_TRANSACTION_TYPES), RECONCILIATION_CODE, and REFERENCE.
• Work order context: WIP_ENTITY_ID identifies the discrete job or work order being completed, while PARENT_WIP_ENTITY_ID links to a parent work order where a hierarchical relationship exists.
• Organization and accounting: ORGANIZATION_ID, ACCT_PERIOD_ID (FK to ORG_ACCT_PERIODS), COMPLETION_SUBINVENTORY, and COMPLETION_LOCATOR_ID define where and when the completed asset is received into inventory.
• Asset detail: ASSET_GROUP_ID, ASSET_NUMBER (FK to MTL_SERIAL_NUMBERS), ASSET_ACTIVITY_ID (FK to MTL_SYSTEM_ITEMS_B), and LOT_NUMBER.
• Activity duration: ACTUAL_START_DATE, ACTUAL_END_DATE, and ACTUAL_DURATION.
• Quality and audit: QA_COLLECTION_ID, plus the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and the concurrent program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE), along with 15 descriptive ATTRIBUTE columns.

Common Use Cases and Queries

Typical use cases include work order completion reporting, asset activity cost analysis, maintenance history trending, and reconciliation of completed jobs against accounting periods. A representative query retrieves completion transactions for a specific asset:

• SELECT transaction_id, transaction_date, wip_entity_id, asset_number, actual_duration FROM eam_job_completion_txns WHERE asset_number = :p_asset AND organization_id = :p_org ORDER BY transaction_date DESC;
• Period-based throughput reporting joins ACCT_PERIOD_ID to ORG_ACCT_PERIODS to aggregate completions by accounting period name.
• Transaction type analysis joins TRANSACTION_TYPE to MTL_TRANSACTION_TYPES to classify completions (for example, asset receipt versus issue).
• Work order drill-down joins WIP_ENTITY_ID to the WIP entities table to obtain job names and statuses.

Related Objects

The documented foreign keys establish the primary relationships. MTL_SERIAL_NUMBERS is referenced via ASSET_NUMBER and ASSET_GROUP_ID. MTL_SYSTEM_ITEMS_B is referenced via ASSET_ACTIVITY_ID and ORGANIZATION_ID. ORG_ACCT_PERIODS is referenced via ACCT_PERIOD_ID and ORGANIZATION_ID, and MTL_TRANSACTION_TYPES via TRANSACTION_TYPE. Additional practical joins include the WIP entity tables (WIP_ENTITIES, WIP_DISCRETE_JOBS) on WIP_ENTITY_ID and PARENT_WIP_ENTITY_ID, ORG_ORGANIZATION_DEFINITIONS on ORGANIZATION_ID, and inventory tables (MTL_ITEM_LOCATIONS) on COMPLETION_LOCATOR_ID. These objects collectively provide the description, validation, and organizational context required to interpret the completion transaction records.