EAM PM Schedule Upload 11i – Oracle EBS SQL Report

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

Overview

EAM Preventive Maintenance Schedule Upload ========================================= Create or update Oracle EAM Preventive Maintenance schedule definitions, including:

• PM Schedule header (asset or asset group, activity, schedule name, set name, type, rescheduling point, etc.)
• Activity group (for Rule Based schedules: list of activities with interval multiples)
• Scheduling rules: Day Interval, Runtime / Meter, and List Date rules
• Optional Last Service info (per meter)

Row Model

Each spreadsheet row can contain PM header values plus any populated child-object columns. The upload infers the object levels from populated column groups:

• Header columns identify and define the PM schedule.
• Act Activity columns add PM activity-group entries. If no Act Activity is entered for a new Rule Based schedule, the header Activity is used as the first activity entry.
• Rule columns add Day Interval, Runtime Meter, or List Date scheduling rules.
• LSI columns add optional last-service readings.

The upload saves schedules and rules as the Preventive Maintenance form does: 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 read-only.

Rows belonging to the same PM must repeat the same Organization Code + Schedule Name + Asset + Activity and remain contiguous. The header is processed once per PM; repeated rows are used for additional activities, rules, and last-service readings.

Upload Modes

Create - empty spreadsheet for new PM schedules. Create, Update - downloads existing PM schedules for review/edit and allows adding new ones.

IMPORTANT on Update

In Create, Update mode, use Update Activities / Update Rules / Update Last Service to include those child details in the downloaded template. Update Activities defaults to Yes; the other child sections are optional.

PM Set Name

• Reference an existing Set Name, or
• Enter a new Set Name together with Set Name Description / End Date / Local Flag; the upload will auto-create it via EAM_SetName_PUB.

After Upload

Schedule and run the “XMLSG - Create Preventive Maintenance Work Order” concurrent program - uploading a PM schedule alone does not generate work orders.

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_schedulings, mtl_eam_asset_activities, mtl_serial_numbers, mtl_system_items_b_kfv, mtl_parameters, eam_pm_set_names, mfg_lookups, org_access_view, eam_pm_scheduling_rules, eam_meters, eam_pm_last_service, wip_entities

Report Categories

Enginatics, Upload

Related Reports

EAM PM Schedule Upload, EAM Work Orders 11i, EAM Work Orders, INV Items, INV Item Upload, INV Item Upload 11i, INV Organization Parameters, EAM Assets

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_11i.xml
SQL Source	EAM PM Schedule Upload 11i.sql
Full Report on Enginatics	www.enginatics.com/reports/eam-pm-schedule-upload-11i/

Case Study & Technical Analysis: EAM PM Schedule Upload 11i

Executive Summary

The EAM PM Schedule Upload 11i creates and maintains Preventive Maintenance (PM) schedule definitions in Oracle Enterprise Asset Management. Each spreadsheet can carry the PM header, its activity group, its scheduling rules and optional last-service readings for an asset or asset group. Maintenance planners and EAM implementation teams use it to set up or revise recurring maintenance programs for many assets at once, following the same rules as the Preventive Maintenance form. The report runs in Excel via Blitz Report. Uploading a schedule does not create work orders; the “XMLSG - Create Preventive Maintenance Work Order” concurrent program must be run afterwards.

Business Challenge

• Large maintenance programs: Defining day-interval, runtime-meter and list-date schedules one at a time through the form is slow across a fleet of assets.
• Rule consistency: Each PM schedule combines a header, activities and rules, and inconsistent setup produces missed or duplicated services.
• Meter-based scheduling: Runtime and meter rules depend on meter readings and last-service values that must be recorded accurately before scheduling works.
• Set name governance: Shared PM set names must be reused or created consistently across organizations.

The Solution

• Hierarchical row model: Header, activity, rule and last-service columns share one spreadsheet, and the upload infers the object level from which column groups are populated.
• Form-equivalent processing: A changed rule is updated in place, a Delete Rule entry removes a rule, and a rule row without a rule identifier is added.
• Set name handling: An existing set name can be referenced, or a new one created with its description, end date and local flag.
• Selective child download: Update Activities, Update Rules and Update Last Service control which child details appear in the downloaded template.

Technical Architecture (High Level)

Primary Tables

• EAM_PM_SCHEDULINGS: the PM schedule header, including type, effective dates, rescheduling point and scheduling method.
• EAM_PM_SCHEDULING_RULES: the day interval, runtime meter and list date rules attached to a schedule.
• EAM_PM_LAST_SERVICE: last-service meter readings and work order links for each activity association.
• EAM_PM_SET_NAMES: the named sets that group PM schedules.
• MTL_EAM_ASSET_ACTIVITIES: the activity associations that link a maintenance object to a PM activity.
• MTL_SERIAL_NUMBERS, MTL_SYSTEM_ITEMS_B_KFV: the asset numbers and the asset or asset group items.
• EAM_METERS: the meter definitions referenced by rules and last-service readings.
• WIP_ENTITIES: the work order entity referenced by last-service rows.
• MFG_LOOKUPS: the work order status meanings.
• MTL_PARAMETERS, ORG_ACCESS_VIEW: the EAM-enabled organizations and the responsibility security.

Logical Relationships

1. EAM_PM_SCHEDULINGS is joined to MTL_EAM_ASSET_ACTIVITIES on activity_association_id, which gives the maintenance object and the activity.
2. The maintenance object is resolved to an asset through MTL_SERIAL_NUMBERS for asset-type objects, or to an asset group item through MTL_SYSTEM_ITEMS_B_KFV for group-type objects.
3. MTL_PARAMETERS is joined on organization and restricted to EAM-enabled organizations in ORG_ACCESS_VIEW.
4. EAM_PM_SET_NAMES is joined on set_name_id, and MFG_LOOKUPS (WIP_JOB_STATUS) supplies the work order status.
5. Rules from EAM_PM_SCHEDULING_RULES and EAM_METERS are unioned to the header, as are last-service rows from EAM_PM_LAST_SERVICE joined to WIP_ENTITIES and EAM_METERS, each with a row sort that orders the output.

Parameters & Filtering

• Upload Mode: Create returns an empty spreadsheet for new schedules, and Create, Update downloads existing schedules for editing.
• Organization: Restricts the schedules to an EAM-enabled organization that the responsibility can access.
• Update Activities: Includes the activity group rows in the template; the SQL applies it through a bind comparison, and it defaults to Yes in the upload.
• Update Rules: When Y, the day interval, runtime meter and list date rules of each schedule are included.
• Update Last Service: When Y, the last-service readings per meter are included.

PM templates (the tmpl_flag condition) are excluded from the download.

Performance & Optimization

• The base query filters out template schedules and restricts organizations to EAM-enabled ones that the responsibility can access, before any child rows are added.
• The header is produced once per schedule, and the activity, rule and last-service sections are each added by a separate UNION ALL branch that is switched off by its bind flag.
• Asset and asset group item descriptions are resolved with left joins to MTL_SYSTEM_ITEMS_B_KFV, so schedules without an asset still appear.

FAQ

Q: Why is no work order created after a PM schedule upload? A: The upload saves the schedule only. The description states that the “XMLSG - Create Preventive Maintenance Work Order” concurrent program must be scheduled and run afterwards.

Q: How do I delete a scheduling rule through the upload? A: Enter Yes in the Delete Rule column on the rule row, using the rule identifier from the downloaded template.

Q: Can a new PM set name be created during the upload? A: Yes. Enter a new Set Name with Set Name Description, End Date and Local Flag, and the upload creates it through the EAM set name API.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH