# Infor EAM Work Order Backlog and PM Schedule SQL Generator

> Source: https://netray.co/tools/infor-eam-work-order-backlog-and-pm-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Read-only SQL for Infor EAM and HxGN EAM: backlog by department, oldest open work orders, estimated vs booked hours, PM schedule for the year and PM on-time results.

## Category

maintenance

## Erp

Infor EAM

## Audience

- EAM administrator
- Report developer
- Maintenance planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Database you run (both dialects are generated)

**Key:** dialect

**Type:** select

**Options:**

- Oracle
- SQL Server

**Default:** Oracle

#### Organization (EVT_ORG), * for all

**Key:** org

**Type:** text

**Default:** *

#### Department (EVT_MRC), * for all

**Key:** mrc

**Type:** text

**Default:** *

#### From date

**Key:** from_date

**Type:** date

**Default:** 2025-01-01

#### To date

**Key:** to_date

**Type:** date

**Default:** 2025-12-31

#### Closed system statuses (EVT_RSTATUS), quoted list

**Key:** closed

**Type:** text

**Default:** 'C', 'CANC'

**Help:** Example values. Replace with the closed system codes returned by query 1.

#### PM on-time grace (days after target)

**Key:** grace

**Type:** number

**Default:** 7

#### Row limit for detail queries

**Key:** limit

**Type:** number

**Default:** 200

**Blocks:**

#### 0. Header

**Body:** -- Infor EAM / HxGN EAM read-only reporting queries
-- Generated for: {{dialect}} | org: {{org}} | department: {{mrc}} | {{from_date}} to {{to_date}}
-- Run on a reporting replica. Each query is given for Oracle first, then SQL Server.

#### 1. Decode work order statuses and event types

**Body:** -- Status mapping: user code -> system code (both dialects)
SELECT UCO_CODE, UCO_DESC, UCO_RCODE
FROM R5UCODES
WHERE UCO_RENTITY = 'EVST'
ORDER BY UCO_RCODE, UCO_CODE;

-- Which system event types exist, so totals only count maintenance work
SELECT EVT_RTYPE, COUNT(*) AS events
FROM R5EVENTS
GROUP BY EVT_RTYPE
ORDER BY events DESC;

#### 2. Open backlog by department (Oracle)

**Body:** SELECT e.EVT_ORG,
       e.EVT_MRC,
       m.MRC_DESC,
       COUNT(*) AS open_work_orders,
       ROUND(AVG(SYSDATE - e.EVT_REPORTED), 1) AS avg_age_days,
       ROUND(MAX(SYSDATE - e.EVT_REPORTED)) AS oldest_age_days
FROM R5EVENTS e
LEFT JOIN R5MRCS m ON m.MRC_CODE = e.EVT_MRC
WHERE e.EVT_RSTATUS NOT IN ({{closed}})
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
GROUP BY e.EVT_ORG, e.EVT_MRC, m.MRC_DESC
ORDER BY open_work_orders DESC;

-- Oldest open work orders with equipment
SELECT e.EVT_CODE, e.EVT_DESC, e.EVT_STATUS, e.EVT_MRC,
       e.EVT_OBJECT, o.OBJ_DESC, e.EVT_PRIORITY, e.EVT_REPORTED,
       ROUND(SYSDATE - e.EVT_REPORTED) AS age_days
FROM R5EVENTS e
LEFT JOIN R5OBJECTS o ON o.OBJ_CODE = e.EVT_OBJECT AND o.OBJ_ORG = e.EVT_OBJECT_ORG
WHERE e.EVT_RSTATUS NOT IN ({{closed}})
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY e.EVT_REPORTED
FETCH FIRST {{limit}} ROWS ONLY;

#### 2. Open backlog by department (SQL Server)

**Body:** SELECT e.EVT_ORG,
       e.EVT_MRC,
       m.MRC_DESC,
       COUNT(*) AS open_work_orders,
       ROUND(AVG(CAST(DATEDIFF(day, e.EVT_REPORTED, GETDATE()) AS float)), 1) AS avg_age_days,
       MAX(DATEDIFF(day, e.EVT_REPORTED, GETDATE())) AS oldest_age_days
FROM R5EVENTS e
LEFT JOIN R5MRCS m ON m.MRC_CODE = e.EVT_MRC
WHERE e.EVT_RSTATUS NOT IN ({{closed}})
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
GROUP BY e.EVT_ORG, e.EVT_MRC, m.MRC_DESC
ORDER BY open_work_orders DESC;

-- Oldest open work orders with equipment
SELECT TOP ({{limit}})
       e.EVT_CODE, e.EVT_DESC, e.EVT_STATUS, e.EVT_MRC,
       e.EVT_OBJECT, o.OBJ_DESC, e.EVT_PRIORITY, e.EVT_REPORTED,
       DATEDIFF(day, e.EVT_REPORTED, GETDATE()) AS age_days
FROM R5EVENTS e
LEFT JOIN R5OBJECTS o ON o.OBJ_CODE = e.EVT_OBJECT AND o.OBJ_ORG = e.EVT_OBJECT_ORG
WHERE e.EVT_RSTATUS NOT IN ({{closed}})
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY e.EVT_REPORTED;

#### 3. Estimated vs booked hours per work order (Oracle, then SQL Server)

**Body:** -- Oracle
SELECT e.EVT_CODE, e.EVT_DESC, e.EVT_MRC, e.EVT_RSTATUS,
       COALESCE(a.est_hours, 0) AS est_hours,
       COALESCE(b.booked_hours, 0) AS booked_hours,
       COALESCE(b.booked_hours, 0) - COALESCE(a.est_hours, 0) AS variance_hours
FROM R5EVENTS e
LEFT JOIN (SELECT ACT_EVENT, SUM(ACT_EST) AS est_hours
           FROM R5ACTIVITIES GROUP BY ACT_EVENT) a ON a.ACT_EVENT = e.EVT_CODE
LEFT JOIN (SELECT BOO_EVENT, SUM(BOO_HOURS) AS booked_hours
           FROM R5BOOKEDHOURS GROUP BY BOO_EVENT) b ON b.BOO_EVENT = e.EVT_CODE
WHERE e.EVT_REPORTED >= DATE '{{from_date}}'
  AND e.EVT_REPORTED < DATE '{{to_date}}' + 1
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY ABS(COALESCE(b.booked_hours, 0) - COALESCE(a.est_hours, 0)) DESC
FETCH FIRST {{limit}} ROWS ONLY;

-- SQL Server
SELECT TOP ({{limit}})
       e.EVT_CODE, e.EVT_DESC, e.EVT_MRC, e.EVT_RSTATUS,
       COALESCE(a.est_hours, 0) AS est_hours,
       COALESCE(b.booked_hours, 0) AS booked_hours,
       COALESCE(b.booked_hours, 0) - COALESCE(a.est_hours, 0) AS variance_hours
FROM R5EVENTS e
LEFT JOIN (SELECT ACT_EVENT, SUM(ACT_EST) AS est_hours
           FROM R5ACTIVITIES GROUP BY ACT_EVENT) a ON a.ACT_EVENT = e.EVT_CODE
LEFT JOIN (SELECT BOO_EVENT, SUM(BOO_HOURS) AS booked_hours
           FROM R5BOOKEDHOURS GROUP BY BOO_EVENT) b ON b.BOO_EVENT = e.EVT_CODE
WHERE e.EVT_REPORTED >= '{{from_date}}'
  AND e.EVT_REPORTED < DATEADD(day, 1, CAST('{{to_date}}' AS date))
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY ABS(COALESCE(b.booked_hours, 0) - COALESCE(a.est_hours, 0)) DESC;

#### 4. PM schedule due in the period (Oracle, then SQL Server)

**Body:** -- Oracle
SELECT p.PPO_PPM, pm.PPM_DESC, p.PPO_OBJECT, o.OBJ_DESC, o.OBJ_MRC,
       p.PPO_DUE, p.PPO_FREQ, p.PPO_PERIODUOM
FROM R5PPMOBJECTS p
JOIN R5PPMS pm ON pm.PPM_CODE = p.PPO_PPM AND pm.PPM_REVISION = p.PPO_REVISION
LEFT JOIN R5OBJECTS o ON o.OBJ_CODE = p.PPO_OBJECT AND o.OBJ_ORG = p.PPO_OBJECT_ORG
WHERE p.PPO_DUE >= DATE '{{from_date}}'
  AND p.PPO_DUE < DATE '{{to_date}}' + 1
  AND ('{{org}}' = '*' OR p.PPO_OBJECT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR o.OBJ_MRC = '{{mrc}}')
ORDER BY p.PPO_DUE, p.PPO_OBJECT;

-- SQL Server
SELECT p.PPO_PPM, pm.PPM_DESC, p.PPO_OBJECT, o.OBJ_DESC, o.OBJ_MRC,
       p.PPO_DUE, p.PPO_FREQ, p.PPO_PERIODUOM
FROM R5PPMOBJECTS p
JOIN R5PPMS pm ON pm.PPM_CODE = p.PPO_PPM AND pm.PPM_REVISION = p.PPO_REVISION
LEFT JOIN R5OBJECTS o ON o.OBJ_CODE = p.PPO_OBJECT AND o.OBJ_ORG = p.PPO_OBJECT_ORG
WHERE p.PPO_DUE >= '{{from_date}}'
  AND p.PPO_DUE < DATEADD(day, 1, CAST('{{to_date}}' AS date))
  AND ('{{org}}' = '*' OR p.PPO_OBJECT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR o.OBJ_MRC = '{{mrc}}')
ORDER BY p.PPO_DUE, p.PPO_OBJECT;

#### 5. PM work orders with a target date in the period: on time, late, not done

**Body:** -- Oracle
SELECT e.EVT_PPM, e.EVT_CODE, e.EVT_OBJECT, e.EVT_MRC, e.EVT_STATUS,
       e.EVT_TARGET, e.EVT_COMPLETED,
       CASE
         WHEN e.EVT_COMPLETED IS NULL THEN 'Not completed'
         WHEN e.EVT_COMPLETED < e.EVT_TARGET + {{grace}} + 1 THEN 'On time'
         ELSE 'Late'
       END AS pm_result
FROM R5EVENTS e
WHERE e.EVT_PPM IS NOT NULL
  AND e.EVT_TARGET >= DATE '{{from_date}}'
  AND e.EVT_TARGET < DATE '{{to_date}}' + 1
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY e.EVT_TARGET;

-- SQL Server
SELECT e.EVT_PPM, e.EVT_CODE, e.EVT_OBJECT, e.EVT_MRC, e.EVT_STATUS,
       e.EVT_TARGET, e.EVT_COMPLETED,
       CASE
         WHEN e.EVT_COMPLETED IS NULL THEN 'Not completed'
         WHEN e.EVT_COMPLETED < DATEADD(day, {{grace}} + 1, CAST(e.EVT_TARGET AS date)) THEN 'On time'
         ELSE 'Late'
       END AS pm_result
FROM R5EVENTS e
WHERE e.EVT_PPM IS NOT NULL
  AND e.EVT_TARGET >= '{{from_date}}'
  AND e.EVT_TARGET < DATEADD(day, 1, CAST('{{to_date}}' AS date))
  AND ('{{org}}' = '*' OR e.EVT_ORG = '{{org}}')
  AND ('{{mrc}}' = '*' OR e.EVT_MRC = '{{mrc}}')
ORDER BY e.EVT_TARGET;

**Notes:** Table and column names follow the R5 schema shared by Infor EAM 11.x and HxGN EAM 12; confirm them in your own data dictionary. Verify on your system: the closed EVT_RSTATUS codes (query 1), which EVT_RTYPE values you want in totals, that ACT_EST is in hours for your setup, and whether archiving has moved older work orders out of R5EVENTS. Oracle date arithmetic adds days to DATE values; if EVT_TARGET carries a time component, the grace window is measured from that time.

## Content

**Intro:** This generator writes read-only SQL for Infor EAM 11.x and HxGN EAM 12 on Oracle and SQL Server: open backlog by department, the oldest open work orders, estimated versus booked hours, the PM schedule due in a period, and PM work order on-time results. Fill in organization, department, dates and your closed status codes.

**Sections:**

#### How to use it

**Body:** Run query 1 first. It lists your work order statuses from R5UCODES with the system code each maps to, and the event types present in R5EVENTS. Put the system codes that mean closed (completed and cancelled) into the closed statuses field as a quoted, comma separated list, then run the rest. Use * for organization and department to see everything. Every query is generated for both Oracle (SYSDATE, DATE literals, FETCH FIRST) and SQL Server (GETDATE, DATEADD, TOP), so copy the half that matches your database.

**Bullets:**

- Run against a reporting replica or standby, not the live EAM database.
- Save the queries as database views if you will reuse them in BIRT or a BI tool.

#### Where the data lives in EAM

**Body:** Work order headers are in R5EVENTS: EVT_REPORTED is the reported date, EVT_TARGET the target (scheduled) date, EVT_COMPLETED the completion date and EVT_PPM the PM code for work orders generated from a PM schedule. Planned hours come from R5ACTIVITIES.ACT_EST per activity, and actual hours from R5BOOKEDHOURS.BOO_HOURS. Departments are in R5MRCS keyed by MRC_CODE. The PM schedule itself is R5PPMOBJECTS, one row per PM and equipment, where PPO_DUE is the next due date and PPO_FREQ with PPO_PERIODUOM the interval. In the application the same data appears on the Work Orders screen, the Equipment PM Schedules screen and Book Labor.

#### Worked example and reading the results

**Body:** Suppose query 2 returns MECH with 412 open work orders, average age 38 days and oldest 410 days. The oldest list usually exposes work orders that were done but never closed, which inflate backlog; close or cancel those before discussing staffing. Query 3 ranks work orders by absolute variance between booked and estimated hours. Large positive variances on PMs point to estimates that need updating in the task plan; zero booked hours on completed work points to labor not being booked. Query 5 classifies each PM work order with a target in the year as On time, Late or Not completed using the grace days you set, which feeds the PM compliance calculator directly.

#### Gotchas

**Body:** PPO_DUE only holds the next due date, so query 4 shows what is due next inside the window, not every occurrence of a monthly PM across the year. For a full yearly forecast, expand each line by its frequency in a spreadsheet, or use the PM forecast features in EAM. Always join R5PPMS on both PPM_CODE and PPM_REVISION. Do not join R5ACTIVITIES and R5BOOKEDHOURS directly to R5EVENTS in the same select; the subqueries here aggregate first to avoid double counting. In multi-organization installations add the _ORG columns to every equipment and part join.

**Cta:** If your planners would rather ask these backlog and PM questions in plain English, we can put a read-only AI layer over your EAM database on your own servers.

**Faq:**

#### How do I report the yearly PM schedule in Infor EAM?

**Answer:** Read R5PPMOBJECTS joined to R5PPMS and R5OBJECTS for the next due date (PPO_DUE) and frequency (PPO_FREQ, PPO_PERIODUOM) of each PM on each asset, then expand by frequency for the year. For PMs already generated as work orders, read R5EVENTS where EVT_PPM is not null and EVT_TARGET falls in the year.

#### How do I track open work orders by status in SQL?

**Answer:** Filter R5EVENTS on EVT_RSTATUS, the system status, rather than EVT_STATUS, the user status, so new user statuses do not break the report. Display EVT_STATUS or its R5UCODES description for readers. Group by EVT_MRC for department totals.

#### Why do my booked hours look doubled?

**Answer:** Joining R5EVENTS to both R5ACTIVITIES and R5BOOKEDHOURS in one select multiplies each booking by the number of activities. Sum each table in its own subquery by work order, as query 3 does, then join the sums to R5EVENTS.

#### Should end users run SQL against EAM?

**Answer:** Usually not. Dataspies, Grid Designer and the reporting tools inside EAM respect user security and organizations. SQL like this suits administrators building views, BI extracts or one-off analysis on a reporting copy.

## Search terms

infor eam yearly pm schedule report, infor work order report, infor work order tracking, infor eam sql query, infor eam backlog report, r5events query, infor eam user guide, hxgn eam sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json