Results for “mtl_eam_asset_activities_pk”

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

Overview

MTL_EAM_ASSET_ACTIVITIES is an Inventory (INV) module table within Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the association between maintenance activities and the assets, rebuildable items, or other maintenance objects they belong to. It is a core EAM (Enterprise Asset Management) object, recording which preventive maintenance or activity definitions apply to a specific item, serial number, and maintenance object type within an organization. Each row represents one activity association, carrying scheduling, service-history, priority, and classification data that drives EAM work order generation and PM scheduling.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this table behaves as a hub, since the primary key ACTIVITY_ASSOCIATION_ID is referenced as a foreign key by several downstream EAM tables (forecasted work orders, PM activities, PM schedulings, and suppression relations). This classification is a heuristic and should be treated as a modeling suggestion rather than an authoritative designation.

Key Information Stored

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

• ACTIVITY_ASSOCIATION_ID — Surrogate primary key (MTL_EAM_ASSET_ACTIVITIES_PK) and the value propagated to dependent EAM tables via the ACTIVITY_ASSOCIATION_ID foreign key column. Also enforced by unique index U1.
• ORGANIZATION_ID — Owning inventory organization; foreign key to MTL_PARAMETERS.
• ASSET_ACTIVITY_ID — The activity definition; foreign key to MTL_SYSTEM_ITEMS_B. Part of unique index U2.
• INVENTORY_ITEM_ID and SERIAL_NUMBER — Item and serial identifying the maintained asset; foreign keys to MTL_SYSTEM_ITEMS_B and MTL_SERIAL_NUMBERS respectively.
• MAINTENANCE_OBJECT_TYPE and MAINTENANCE_OBJECT_ID — Identify the object being maintained; complete business-key candidate U2 (ASSET_ACTIVITY_ID, MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_ID).
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date range for the association.
• PRIORITY_CODE, CLASS_CODE, SHUTDOWN_TYPE_CODE — Scheduling and operational classification of the activity.
• ACTIVITY_CAUSE_CODE, ACTIVITY_TYPE_CODE, ACTIVITY_SOURCE_CODE — Descriptive attributes describing the activity's cause, type, and origin.
• LAST_SERVICE_START_DATE and related history columns — LAST_SERVICE_END_DATE, PREV_SERVICE_START_DATE, PREV_SERVICE_END_DATE, plus LAST_/PREV_SCHEDULED_* and LAST_/PREV_PM_SUGGESTED_* capture service, scheduling, and suggested PM date history.
• TMPL_FLAG and SOURCE_TMPL_ID — Indicate template origin and the source template association.
• WIP_ENTITY_ID — Links the association to a work-in-process entity when applicable.
• OWNING_DEPARTMENT_ID — Organizational ownership of the activity.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Standard EBS descriptive flexfield columns.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include listing all PM activities associated with a given asset, reporting service schedules per organization, and tracing which items are covered by a specific activity definition. A common query joins the association to the item master:

• Asset activity listing: SELECT a.ACTIVITY_ASSOCIATION_ID, a.INVENTORY_ITEM_ID, a.SERIAL_NUMBER, a.PRIORITY_CODE FROM MTL_EAM_ASSET_ACTIVITIES a WHERE a.ORGANIZATION_ID = :org_id AND a.END_DATE_ACTIVE IS NULL.
• Join to item master: join MTL_SYSTEM_ITEMS_B b ON a.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID AND a.ORGANIZATION_ID = b.ORGANIZATION_ID to resolve item descriptions.
• PM scheduling report: filter on LAST_PM_SUGGESTED_START_DATE and LAST_SCHEDULED_START_DATE to surface upcoming or overdue maintenance.
• Serialized asset history: join to MTL_SERIAL_NUMBERS on SERIAL_NUMBER and INVENTORY_ITEM_ID to report service history for a specific unit.
• Template identification: filter TMPL_FLAG and SOURCE_TMPL_ID to distinguish template-driven associations from directly created ones.

Related Objects

The following objects depend on or reference MTL_EAM_ASSET_ACTIVITIES through documented foreign keys:

• MTL_PARAMETERS — joined on ORGANIZATION_ID (parent for the organization foreign key).
• MTL_SYSTEM_ITEMS_B — joined on ASSET_ACTIVITY_ID and separately on INVENTORY_ITEM_ID, both with ORGANIZATION_ID.
• MTL_SERIAL_NUMBERS — joined on SERIAL_NUMBER and INVENTORY_ITEM_ID.
• EAM_FORECASTED_WORK_ORDERS — references ACTIVITY_ASSOCIATION_ID.
• EAM_PM_ACTIVITIES — references ACTIVITY_ASSOCIATION_ID.
• EAM_PM_LAST_SERVICE — references ACTIVITY_ASSOCIATION_ID.
• EAM_PM_SCHEDULINGS — references ACTIVITY_ASSOCIATION_ID.
• EAM_SUPPRESSION_RELATIONS — references ACTIVITY_ASSOCIATION_ID as both PARENT_ASSOCIATION_ID and CHILD_ASSOCIATION_ID.