EAM PM Schedule Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Create and update preventive maintenance schedules of assets: the schedule header, its activities, its day interval, runtime meter and list date rules, and optional last service readings per meter.

A schedule spans several rows, which repeat the Organization Code, Schedule Name, Asset Number and Activity and stay together. The header is processed once per schedule; further rows add activities (Act columns), rules (Rule columns) and last service readings (Lsi columns). If no Act Activity is entered for a new Rule Based schedule, the header Activity is its first activity. Whether a schedule has the rules its type needs is checked for the whole schedule on upload.

Schedules are saved as the Preventive Maintenance form saves them: a changed rule is updated in place, Delete Rule removes it, and a rule row without a rule id is added. Rule Last Service Reading is derived from the last service information on save, and Act Last Svc Start and Act Last Svc End show the dates that work order completion records; all three are read-only.

Every schedule needs an Asset Number. Asset group schedule templates are not downloaded and cannot be uploaded.

In Create, Update mode, Update Activities, Update Rules and Update Last Service include those rows in the download. Update Activities defaults to Yes.

Set Name takes an existing set name, or a new one together with Set Name Description, End Date and Local Flag, which the upload creates.

Uploading a schedule does not create work orders: run the concurrent program Generate Preventive Maintenance Work Orders afterwards.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Organization, Update Activities, Update Rules, Update Last Service

Oracle EBS Tables Used

eam_pm_activities, eam_pm_schedulings, csi_item_instances, mtl_eam_asset_activities, mtl_system_items_b_kfv, mtl_parameters, eam_pm_set_names, eam_wo_statuses_v, xxen_report_org_access, eam_pm_scheduling_rules, csi_counters_vl, eam_pm_last_service, wip_entities

Report Categories

Enginatics, R12 only, Upload

EAM PM Schedule Upload 11i, EAM Work Orders, EAM Work Orders 11i, INV Items, EAM Asset Activity Association Upload, INV Item Upload, INV Item Upload 11i, EAM Asset Number Upload

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import EAM_PM_Schedule_Upload.xml
SQL Source EAM PM Schedule Upload.sql
Full Report on Enginatics www.enginatics.com/reports/eam-pm-schedule-upload/

Case Study & Technical Analysis: EAM PM Schedule Upload

Executive Summary

This upload creates and updates Oracle Enterprise Asset Management preventive maintenance schedules. A schedule covers the header for an asset or asset group, its activity group, its scheduling rules and optional last service readings. Maintenance planners use it to set up or revise the service plan for a plant’s equipment in one workbook, rather than through the Preventive Maintenance form asset by asset. It is used by EAM administrators and planners who maintain large fleets of meter-based and calendar-based services. The template runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. EAM_PM_SCHEDULINGS is the header source. Its activity association id falls back to the first activity of the schedule when none is set.
  2. The header joins to CSI_ITEM_INSTANCES for asset maintenance objects, and to MTL_SYSTEM_ITEMS_B_KFV for asset groups.
  3. The activity joins through MTL_EAM_ASSET_ACTIVITIES to the activity item, on activity association and organization.
  4. Rule rows join EAM_PM_SCHEDULING_RULES to the header on schedule id, and the meter joins to CSI_COUNTERS_VL as an outer join.
  5. Last service rows join EAM_PM_LAST_SERVICE on the activity association, and the job joins to WIP_ENTITIES as an outer join.
  6. The three row sets are combined with UNION ALL, and each set is included only when its update flag is Y.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why do my work orders not appear after I upload a schedule? A: Uploading a schedule does not generate work orders. Run the Create Preventive Maintenance Work Order concurrent program after the upload.

Q: How do I remove a scheduling rule? A: Set Delete Rule on the rule row. A changed rule is updated in place, and a rule row without a rule id is added.

© Enginatics GmbH