Search Results eam_pm_last_service_n1

Overview

EAM.EAM_PM_LAST_SERVICE is a transactional table in the Oracle E-Business Suite Enterprise Asset Management (EAM) schema. It stores the most recent service history for the combination of a meter and an asset activity association. Specifically, it retains the meter reading captured at the last preventive maintenance service and the reading recorded at the prior service, together with the work order identifier associated with each service event. This allows EAM to evaluate whether a preventive maintenance schedule — typically defined as an interval of meter usage — is due, without having to scan the full transaction history each time. The table is present and valid in both Oracle EBS 12.1.1 and 12.2.2 and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

From a dimensional modeling perspective, the mined metadata suggests a link classification under a Data Vault heuristic. The table's natural key is the pairing of METER_ID and ACTIVITY_ASSOCIATION_ID, which connects two independent business concepts (a counter and an asset activity definition). This resolves as a relationship/link entity rather than a standalone hub or satellite, with the service readings acting as descriptive link attributes.

Key Information Stored

The table contains eleven documented columns. The most significant are:

• METER_ID — Identifier of the meter (from CSI_COUNTERS_B) against which readings are captured.
• ACTIVITY_ASSOCIATION_ID — Identifier of the asset activity association (from MTL_EAM_ASSET_ACTIVITIES) that defines the maintenance activity for the asset.
• LAST_SERVICE_READING — Meter reading value recorded at the most recent service.
• PREV_SERVICE_READING — The previous last service reading, copied here when a work order is completed, providing a baseline for interval evaluation.
• WIP_ENTITY_ID — Work order identifier associated with the service.
• MIGRATED_FLAG — Flag indicating whether the row was migrated into EAM from a legacy source.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

Dissecting key versus business-key candidates: the documented physical primary key is EAM_PM_LAST_SERVICE_PK on (METER_ID, ACTIVITY_ASSOCIATION_ID), which is the surrogate/unique row identifier. The unique index EAM_PM_LAST_SERVICE_U1 is defined on (ACTIVITY_ASSOCIATION_ID, METER_ID), the same column pair in reversed order — effectively the business-key candidate. A second index, EAM_PM_LAST_SERVICE_N1, is a nonunique index on WIP_ENTITY_ID, supporting lookups by work order. Both indexes reside in APPS_TS_TX_IDX.

Common Use Cases and Queries

The primary use case is preventive maintenance due-date calculation: EAM reads LAST_SERVICE_READING for a given meter/activity pair and compares it against the current meter reading to determine whether the defined interval has been exceeded. A typical query is:

• SELECT METER_ID, ACTIVITY_ASSOCIATION_ID, LAST_SERVICE_READING, PREV_SERVICE_READING, WIP_ENTITY_ID FROM EAM.EAM_PM_LAST_SERVICE WHERE METER_ID = :meter_id;
• Identifying work orders tied to a service reading: SELECT WIP_ENTITY_ID, LAST_SERVICE_READING FROM EAM.EAM_PM_LAST_SERVICE WHERE WIP_ENTITY_ID = :wip_entity_id;
• Reporting on maintenance intervals: joining to CSI_COUNTERS_B on METER_ID to retrieve counter units and to MTL_EAM_ASSET_ACTIVITIES for the asset/activity definition.
• Auditing migrated data via WHERE MIGRATED_FLAG = 'Y'.

Related Objects

The FK structure confirms the following relationships:

• EAM.MTL_EAM_ASSET_ACTIVITIES — referenced via ACTIVITY_ASSOCIATION_ID; supplies the asset/activity definition.
• CSI.CSI_COUNTERS_B — referenced via METER_ID; provides the counter or meter definition and units of measure.
• WIP.WIP_DISCRETE_JOBS — referenced via WIP_ENTITY_ID; provides the discrete work order details.
• APPS.EAM_PM_LAST_SERVICE — the APPS synonym/view layer that exposes the EAM base table to application queries.

Queries should generally be routed through the APPS synonym. No other database objects are documented as referencing this table.