Your Asset Maintenance Data Template
Your Asset Maintenance Data Template
- Essential data fields for maintenance logs
- Standard process milestones and work order stages
- Technical extraction logic for Hexagon EAM
Asset Maintenance Attributes
| Name | Description | ||
|---|---|---|---|
| Activity ActivityName | The specific task or status change event that occurred. | ||
| Description This attribute denotes the step in the process being performed, such as 'Work Order Created' or 'Material Issued'. It is derived from the history of status changes or specific transactional events logged against the work order. Why it matters It defines the event log sequence, allowing for the reconstruction of the process flow and bottleneck analysis. Where to get Hexagon EAM table R5EVENTHISTORY, field EVH_STATUS (mapped to descriptions) or audit logs Examples Work Order CreatedWork Order ScheduledWork Order CompletedMaterial Issued | |||
| Last Data Update LastDataUpdate | The timestamp when the record was last extracted or refreshed. | ||
| Description This attribute tracks when the data was last pulled from the source system. It helps analysts understand the freshness of the data and identify potential latency issues in the reporting pipeline. Why it matters It provides confidence in the currency of the analysis and helps in debugging data synchronization issues. Where to get System time at extraction Examples 2023-10-25T12:00:00Z2023-10-26T00:00:00Z | |||
| Maintenance Work Order WorkOrderNumber | The unique identifier for the maintenance work order. | ||
| Description This attribute represents the unique primary key for the maintenance case. It tracks the work order from the initial request through planning, execution, and final financial closure. It is used as the central case identifier to join activity logs, labor hours, and material consumption. Why it matters It identifies the specific instance of the maintenance process, enabling end-to-end tracking of cycle times and process variants. Where to get Hexagon EAM table R5EVENTS, field EVT_CODE Examples WO-100234WO-100235WO-100236104592 | |||
| Source System SourceSystem | The name of the system where the data originated. | ||
| Description This attribute indicates the system of record for the data. In this context, it is hardcoded to the specific EAM system name to distinguish it from potential legacy data or external contractor systems. Why it matters It ensures data lineage is clear, especially when merging data from multiple maintenance instances. Where to get Hardcoded value Examples Hexagon EAMInfor EAMDatastream 7i | |||
| Timestamp EventTimestamp | The date and time when the activity occurred. | ||
| Description This attribute records the exact moment an event or status change was committed to the system. It allows for the calculation of duration between activities and the overall cycle time of the work order. Why it matters Accurate timestamps are essential for analyzing lead times, identifying delays, and calculating SLA adherence. Where to get Hexagon EAM table R5EVENTHISTORY, field EVH_DATE Examples 2023-10-12T08:30:00Z2023-10-12T14:15:00Z2023-10-13T09:00:00Z | |||
| Actual Labor Hours ActualLaborHours | The total number of hours booked against the work order. | ||
| Description This attribute sums the time technicians have logged against the work order. It is used to measure productivity, calculate labor costs, and analyze variance against estimated hours. Why it matters It is the primary data source for the Technician Labor Productivity dashboard and Labor Hour Variance KPI. Where to get Hexagon EAM table R5BOOKEDHOURS, field BOO_HOURS (Summed by Work Order) Examples 2.54.012.00.5 | |||
| Asset AssetCode | The unique identifier of the equipment or facility being maintained. | ||
| Description This attribute links the work order to a specific piece of equipment or location. It enables the analysis of failure frequencies per asset and helps identify 'bad actors' or equipment requiring replacement. Why it matters It is fundamental for calculating asset reliability metrics and understanding where maintenance effort is concentrated. Where to get Hexagon EAM table R5EVENTS, field EVT_OBJECT Examples PUMP-001HVAC-204FLEET-99CONVEYOR-A | |||
| Department MaintenanceDepartment | The department or work center responsible for executing the work. | ||
| Description This attribute identifies the team or maintenance shop assigned to the work order, such as Electrical, Mechanical, or Facilities. It allows for performance benchmarking across different technical teams. Why it matters It supports the Technician Labor Productivity dashboard and helps allocate resources effectively across different trades. Where to get Hexagon EAM table R5EVENTS, field EVT_MRC Examples Electrical ShopMechanical MaintFacilitiesFleet Svc | |||
| Priority PriorityCode | The urgency level assigned to the work order. | ||
| Description This attribute indicates the importance of the work order, ranging from routine to emergency. It drives the scheduling of resources and is crucial for monitoring SLA adherence and detecting priority abuse. Why it matters It is essential for the Priority Escalation and Resource Audit dashboard to ensure critical tasks are prioritized correctly. Where to get Hexagon EAM table R5EVENTS, field EVT_PRIORITY Examples CriticalHighMediumLow | |||
| Target Completion Date TargetCompletionDate | The scheduled deadline for the work order. | ||
| Description This attribute represents the date by which the maintenance work is expected to be finished. Comparing this date against the actual completion date is primary for calculating SLA adherence and on-time performance. Why it matters It is critical for the Contractor SLA Adherence Monitor and for evaluating planning accuracy. Where to get Hexagon EAM table R5EVENTS, field EVT_TARGET Examples 2023-11-012023-11-152023-12-01 | |||
| User UpdatedBy | The user ID of the person who performed the activity. | ||
| Description This attribute captures the login ID of the individual responsible for triggering the status change or update. It is used to analyze team workload, identify training needs, and audit process compliance. Why it matters It enables the analysis of user behavior and resource utilization across the maintenance lifecycle. Where to get Hexagon EAM table R5EVENTHISTORY, field EVH_USER Examples JSMITHADAVISSYSTEMMGR_USR | |||
| Work Order Type WorkOrderType | Categorizes the work order as preventive, corrective, or emergency. | ||
| Description This attribute classifies the nature of the maintenance work. It is used to analyze the ratio of planned preventive maintenance versus reactive emergency repairs, which is a key indicator of asset health and management maturity. Why it matters It supports the Preventive Maintenance Ratio Analysis dashboard and helps identify opportunities to reduce reactive work. Where to get Hexagon EAM table R5EVENTS, field EVT_TYPE Examples PPMCOREMGPRJ | |||
| Asset Criticality AssetCriticality | A score indicating the operational importance of the asset. | ||
| Description This attribute indicates how critical the asset is to business operations. It is often used to validate if high priority work orders are genuinely associated with critical equipment, highlighting potential process abuse. Why it matters It supports the Priority Escalation and Resource Audit dashboard by correlating priority with asset value. Where to get Hexagon EAM table R5OBJECTS, field OBJ_CRITICALITY (requires join on EVT_OBJECT) Examples A - VitalB - CriticalC - SecondaryD - Low | |||
| Date Created DateCreated | The date the work order was originally generated. | ||
| Description This attribute marks the beginning of the work order lifecycle. It is the baseline for calculating the Mean Time to Plan and determining the age of backlog items. Why it matters It is required for the Maintenance Planning Velocity dashboard and Backlog Age KPI. Where to get Hexagon EAM table R5EVENTS, field EVT_CREATED Examples 2023-09-012023-09-15 | |||
| Emergency Escalation EmergencyEscalationFlag | Flag indicating if priority was raised to Emergency after creation. | ||
| Description This boolean attribute identifies work orders that started with a lower priority but were later upgraded to Emergency. Frequent occurrences may indicate poor initial triage or gaming of the system to get faster service. Why it matters It supports the Priority Escalation and Resource Audit dashboard and Emergency Escalation Rate KPI. Where to get Derived from audit trail of Priority field Examples truefalse | |||
| Is Preventive IsPreventive | Flag indicating if the work order is preventive maintenance. | ||
| Description This boolean attribute allows for easy filtering and categorization of work orders. It simplifies the calculation of the Preventive Maintenance Ratio KPI by separating proactive work from reactive repairs. Why it matters It is a fundamental segmentation for the Preventive Maintenance Ratio Analysis dashboard. Where to get Derived from WorkOrderType Examples truefalse | |||
| Is Rework IsRework | Flag indicating if the work order involved repeated technical tasks. | ||
| Description This calculated attribute identifies cases where the process loops back from a quality check or completion status to an active working status. It helps pinpoint quality issues and training gaps. Why it matters It drives the Technical Rework and Quality Control dashboard and the Maintenance Rework Rate KPI. Where to get Calculated based on activity repetitions Examples truefalse | |||
| Material Requisition Date MaterialRequisitionDate | The date when spare parts were requested. | ||
| Description This attribute captures the timestamp when the material request was submitted to the storeroom or purchasing. Analyzing the time from this date to 'Material Issued' reveals supply chain efficiency. Why it matters It is necessary for the Spare Parts Procurement Lead Times dashboard. Where to get Hexagon EAM table R5REQUISITIONS or linked transaction table Examples 2023-10-012023-10-05 | |||
| Quality Control Passed QualityControlPassed | Flag indicating if the QC step was documented. | ||
| Description This boolean attribute checks if a specific quality control activity or checklist item was marked as completed. It ensures compliance with safety regulations and internal standards. Why it matters It drives the Quality Control Compliance Rate KPI and supports the Compliance and Documentation Integrity dashboard. Where to get Derived from Checklist tables or Activity History Examples truefalse | |||
| SLA State SLAState | Indicates if the work order met its service level agreement. | ||
| Description This calculated attribute compares the Actual Completion Date against the Target Completion Date. It categorizes the case as 'Within SLA' or 'Breached' to simplify reporting on contractor and internal performance. Why it matters It is the direct data source for the SLA Adherence Rate KPI. Where to get Calculated: Actual End Date <= Target Date Examples Within SLABreachedOpen | |||
| Total Actual Cost TotalActualCost | The total financial cost recorded for the work order. | ||
| Description This attribute captures the aggregated cost of labor, materials, and services. It is essential for financial settlement analysis and understanding the cost impact of emergency maintenance vs preventive maintenance. Why it matters It supports the Financial Settlement and Closure Speed dashboard and cost-based decision making. Where to get Hexagon EAM table R5EVENTS, field EVT_ACTCOST (or similar summary column) Examples 150.002500.5075.25 | |||
| Work Order Description WorkOrderDescription | A short text summary of the maintenance task. | ||
| Description This attribute contains the subject line or short description of the problem. It provides context for analysts when drilling down into specific problematic cases or identifying patterns in unstructured text. Why it matters It provides necessary context for human review of the process data. Where to get Hexagon EAM table R5EVENTS, field EVT_DESC Examples Repair leaking pump sealMonthly HVAC InspectionReplace conveyor belt | |||
Asset Maintenance Activities
| Activity | Description | ||
|---|---|---|---|
| Labor Hours Recorded | A technician logs hours worked against the work order. This activity may occur multiple times per case. | ||
| Why it matters Foundation for Labor Hour Variance and Technician Labor Productivity dashboards. Reveals actual effort vs plan. Where to get Hexagon EAM r5booklabor table. Each row represents a timesheet entry for the WO. Capture Logged when transaction INSERT executed on r5booklabor Event type explicit | |||
| Material Issued | Spare parts are physically issued from the storeroom and charged to the work order. This represents material consumption. | ||
| Why it matters End point for Material Availability Lead Time. Identify delays where work starts but halts due to missing parts. Where to get Hexagon EAM r5translines table. Transaction type 'I' (Issue) linked to the work order. Capture Logged when transaction INSERT executed on r5translines Event type explicit | |||
| Work Order Approved | The status change indicating that the work order has passed planning and estimation review. It signifies the work is authorized to proceed. | ||
| Why it matters Supports Maintenance Planning Velocity analysis by marking the end of the planning phase. Separation of duties compliance check. Where to get Hexagon EAM r5statuslog table. Look for transition to status 'A' (Approved) or system equivalent. Capture Logged when transaction executed on r5statuslog Event type explicit | |||
| Work Order Closed | The work order is financially locked and archived. No further costs or labor can be booked. | ||
| Why it matters Process End. Crucial for Financial Settlement Processed analysis and locking in final costs. Where to get Hexagon EAM r5statuslog. Transition to 'C' (Closed) or 'L' (Locked/Closed). Capture Logged when transaction executed on r5statuslog Event type explicit | |||
| Work Order Completed | The technical work is finished, and the technician has marked the job as complete. The asset is returned to service. | ||
| Why it matters Key milestone for End to End Lifecycle Duration and Mean Time to Repair. Stops the downtime clock. Where to get Hexagon EAM r5statuslog. Transition to 'C' (Completed) or 'F' (Finished). Capture Logged when transaction executed on r5statuslog Event type explicit | |||
| Work Order Created | The initial creation of the maintenance work order record in the system. This event establishes the case and captures the initial timestamp for the entire maintenance lifecycle. | ||
| Why it matters Marks the start of the process instance and sets the baseline for measuring total resolution time. Critical for calculating backlog aging. Where to get Hexagon EAM r5events table. Logged when a new record is inserted with evt_code. Capture Logged when transaction INSERT executed on r5events Event type explicit | |||
| Work Order Started | Technicians have begun actual physical work on the asset. Often marked by a status change to In Progress or the first timestamp of labor entry. | ||
| Why it matters End point for backlog calculation and start point for wrench time. Vital for SLA Adherence Rate. Where to get Hexagon EAM r5statuslog. Transition to 'In Progress' (often status code 'I' or 'IP'). Capture Logged when transaction executed on r5statuslog Event type explicit | |||
| Checklist Item Verified | A specific task or inspection step within the work order is marked as complete. This represents granular progress within the job. | ||
| Why it matters Essential for Compliance and Documentation Integrity. Proves safety checks were logged systemically. Where to get Hexagon EAM r5checklist or r5activities table. Status change of a specific activity/task line item. Capture Logged when transaction UPDATE executed on r5checklist Event type explicit | |||
| Material Requisition Created | A request for spare parts or materials is linked to the work order. This initiates the supply chain sub-process. | ||
| Why it matters Start point for Spare Parts Procurement Lead Times. High volumes here indicate dependency on inventory availability. Where to get Hexagon EAM r5requisitions table. Linked via req_evt (Work Order Code). Capture Logged when transaction INSERT executed on r5requisitions Event type explicit | |||
| Priority Updated | A change occurred to the priority level of the work order after its initial creation. This captures the timestamp and the new priority value. | ||
| Why it matters Essential for the Priority Escalation and Resource Audit dashboard to identify priority abuse or scope creep. Where to get Hexagon EAM r5audittrail (if audit is enabled) or inferred from snapshots. Often derived from audit log on evt_priority field. Capture Logged when transaction UPDATE executed on evt_priority Event type explicit | |||
| Quality Control Passed | A supervisor or inspector reviews the completed work and approves the quality. This step validates the repair before closure. | ||
| Why it matters Supports Quality Control Compliance Rate KPI. Loops back from here indicate technical rework. Where to get Hexagon EAM r5statuslog (Transition to 'Review' or 'QC') or r5audittrail on specific UDF validation. Capture Compare status field before/after Event type inferred | |||
| Work Order Scheduled | The work order is assigned a scheduled start date or specific resource. This moves the order from the backlog to the active schedule. | ||
| Why it matters Critical for Technician Labor Productivity analysis and understanding resource allocation efficiency. Where to get Hexagon EAM r5events table (evt_schedstart update) or r5statuslog (transition to 'S' - Scheduled). Capture Compare status field before/after or check evt_schedstart update Event type inferred | |||
| Work Order Signed Off | Final administrative approval usually required for compliance or contractor payment. Confirms all data is accurate. | ||
| Why it matters Monitor for Work Order Approval Cycle Analysis. Delays here affect financial settlement speed. Where to get Hexagon EAM r5statuslog (Transition to 'Signed Off') or electronic signature capture log. Capture Compare status field before/after Event type inferred | |||
Extraction Guides
Steps
- Log in to the Hexagon EAM application with a user account that has Advanced Reporting Authoring privileges.
- Navigate to the Reports module, typically found under the Administration or Reports menu, and launch the Advanced Reporting interface (often Cognos or a built-in SQL report builder).
- Initialize a new report creation wizard and select 'SQL' or 'Free-hand SQL' as the data source type. This allows direct database querying rather than using the standard business object model.
- Copy the SQL script provided in the Query section below and paste it into the dataset definition window.
- Replace the placeholder values (such as [Your Organization Code] and date ranges) with specific values relevant to your environment.
- Validate the query syntax within the editor to ensure the database platform (Oracle or SQL Server) accepts the union statements.
- Map the resulting columns to the report layout list, ensuring the WorkOrderNumber, ActivityName, and EventTimestamp are clearly visible.
- Configure the report to export specifically to CSV or Excel format to ensure a tabular structure without merged cells or formatting headers.
- Run the report for a validation period (e.g., the last 100 work orders) to verify data structure.
- Once verified, schedule the report to run on a recurring basis or execute it for the full extraction time window.
- Download the output file and check the date formats. Ensure timestamps include seconds if possible for precise ordering.
- Import the resulting CSV file into ProcessMind, mapping the SourceSystem column as a case attribute and ActivityName as the activity classifier.
Configuration
- Date Range: It is recommended to filter by EVT_DATE (Creation Date) for a rolling window of 6 to 12 months to maintain query performance.
- Organization Filter: Hexagon EAM is often multi-org. Ensure the query includes a filter for your specific Organization Code to avoid data pollution from other business units.
- Database Syntax: The query below uses ANSI standard SQL where possible, but Hexagon EAM usually runs on Oracle or SQL Server. Slight adjustments to date functions (e.g., SYSDATE vs GETDATE) may be required.
- Status Configuration: The mapping of Status Codes (e.g., 'A', 'C', 'R') to Activity Names (e.g., 'Work Order Approved') must match your system's specific status configuration.
- Permissions: The user account running the report requires read access to R5EVENTS, R5EVENTSTATUS, R5BOOKEDHOURS, R5TRANSACTIONS, and R5AUDITVALUES tables.
a Sample Query config
/* 1. Work Order Created */
SELECT
evt_code AS WorkOrderNumber,
'Work Order Created' AS ActivityName,
evt_created AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
evt_type AS WorkOrderType,
evt_priority AS PriorityCode,
evt_mrc AS MaintenanceDepartment,
evt_updatedby AS UpdatedBy,
evt_target AS TargetCompletionDate,
evt_object AS AssetCode,
NULL AS ActualLaborHours
FROM r5events
WHERE evt_type IN ('JOB', 'PPM') -- Filter for relevant WO types
AND evt_created >= '2023-01-01' -- [Set Start Date]
AND evt_org = 'YOUR_ORG' -- [Set Organization]
UNION ALL
/* 2. Priority Updated (Requires Audit Log) */
SELECT
aud_code AS WorkOrderNumber,
'Priority Updated' AS ActivityName,
aud_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
aud_value AS PriorityCode,
NULL AS MaintenanceDepartment,
aud_user AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5auditvalues
WHERE aud_table = 'R5EVENTS'
AND aud_field = 'EVT_PRIORITY'
AND aud_org = 'YOUR_ORG'
UNION ALL
/* 3. Status Changes (Approved, Scheduled, Started, Completed, Signed Off, Closed) */
/* This block maps generic status codes to specific ProcessMind activities */
SELECT
est_event AS WorkOrderNumber,
CASE est_status
WHEN 'A' THEN 'Work Order Approved'
WHEN 'S' THEN 'Work Order Scheduled'
WHEN 'IP' THEN 'Work Order Started'
WHEN 'C' THEN 'Work Order Completed'
WHEN 'QC' THEN 'Quality Control Passed'
WHEN 'SO' THEN 'Work Order Signed Off'
WHEN 'CL' THEN 'Work Order Closed'
ELSE 'Status Change: ' + est_status
END AS ActivityName,
est_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
est_auth AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5eventstatus
WHERE est_status IN ('A', 'S', 'IP', 'C', 'QC', 'SO', 'CL')
AND est_org = 'YOUR_ORG'
UNION ALL
/* 4. Material Requisition Created */
SELECT
req_event AS WorkOrderNumber,
'Material Requisition Created' AS ActivityName,
req_created AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
req_createdby AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5requisitions
WHERE req_event IS NOT NULL
AND req_org = 'YOUR_ORG'
UNION ALL
/* 5. Material Issued */
SELECT
trl_event AS WorkOrderNumber,
'Material Issued' AS ActivityName,
trl_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
trl_user AS UpdatedBy,
NULL AS TargetCompletionDate,
trl_part AS AssetCode, -- Storing Part ID in Asset column for context
NULL AS ActualLaborHours
FROM r5translines
WHERE trl_type = 'I' -- I = Issue
AND trl_event IS NOT NULL
AND trl_org = 'YOUR_ORG'
UNION ALL
/* 6. Labor Hours Recorded */
SELECT
boo_event AS WorkOrderNumber,
'Labor Hours Recorded' AS ActivityName,
boo_entered AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
boo_mrc AS MaintenanceDepartment,
boo_person AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
boo_hours AS ActualLaborHours
FROM r5bookedhours
WHERE boo_event IS NOT NULL
AND boo_org = 'YOUR_ORG'
UNION ALL
/* 7. Checklist Item Verified */
SELECT
ckv_event AS WorkOrderNumber,
'Checklist Item Verified' AS ActivityName,
ckv_entrydate AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
ckv_enteredby AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5checklistvalues
WHERE ckv_completed = '+' -- Logic for completed item depends on config
AND ckv_event IS NOT NULL
AND ckv_org = 'YOUR_ORG'Steps
- Identify the Database Connection: Ensure you have read-access credentials (typically a service account) for the Hexagon EAM Oracle or SQL Server backend database.
- Locate Core Tables: Confirm access to R5EVENTS (Work Order Headers), R5STATUS (Status History), R5BOOKEDHOURS (Labor), R5TRANSACTIONS (Materials), and R5AUDITVALUES (Audit Log).
- Map Status Codes: Hexagon EAM uses configurable status codes (e.g., A, C, O, R). Review your system configuration (Screen: Administration -> Codes) to map your specific 1 or 2-character codes to the activities Approved, Scheduled, Started, Completed, QC Passed, Signed Off, and Closed.
- Configure the Query: Open the SQL script provided below. Replace the placeholder status codes (e.g., 'APPR', 'SCHD', 'COMP') with your actual system codes.
- Set Date Range: Update the WHERE clauses to filter by EVT_DATE or TRN_DATE to select a specific time window, typically the last 12 months, to ensure query performance.
- Run Extraction: Execute the query in your SQL client (SQL Developer, SSMS, or Toad).
- Verify Audit Data: If the 'Priority Updated' activity returns no rows, check if Audit functionality is enabled for the R5EVENTS table in your EAM system settings.
- Validate Checklist Data: Ensure that R5CHECKLISTVALUES is the correct table for your version; older versions may use R5ACTIVITIES for task-level completion.
- Export Data: Save the result set as a CSV or Parquet file. Ensure the Timestamp column is formatted as ISO 8601 (YYYY-MM-DD HH:MM:SS) to avoid parsing errors.
- Import to ProcessMind: Upload the flat file to the ProcessMind interface, mapping WorkOrderNumber to Case ID, ActivityName to Activity, and EventTimestamp to Timestamp.
Configuration
- Time Window Filter: The query currently selects all data. It is highly recommended to add a filter like WHERE EVT_DATE >= SYSDATE - 365 (Oracle) or GetDate() - 365 (SQL Server) in the foundational CTE or each SELECT block.
- Status Code Mapping: You must replace the string literals in the SQL (e.g., 'APPR', 'COMP', 'QC') with the actual codes defined in your R5STATUS set.
- Audit Configuration: The extraction of 'Priority Updated' relies on the R5AUDITVALUES table. If auditing is turned off for the EVT_PRIORITY column, this specific activity will be missing.
- Organization/Tenant: If your EAM instance hosts multiple organizations, add a filter for EVT_ORG or EVT_MRC (Department) to avoid data commingling.
- Language: The query assumes standard English column headers. If your database uses localized column names, adjust accordingly.
a Sample Query sql
/* 1. Work Order Created */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Created' AS ActivityName,
E.EVT_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
E.EVT_CREATEDBY AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
WHERE E.EVT_TYPE IN ('JOB', 'PPM') -- Filter for Standard and Preventive WOs
UNION ALL
/* 2. Priority Updated (From Audit Log) */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Priority Updated' AS ActivityName,
A.AUD_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
A.AUD_VALUE AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
A.AUD_USER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5AUDITVALUES A ON E.EVT_CODE = A.AUD_KEY1
WHERE A.AUD_TABLE = 'R5EVENTS'
AND A.AUD_FIELD = 'EVT_PRIORITY'
UNION ALL
/* 3. Work Order Approved */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Approved' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'APPR' -- Replace with your Approved status code
UNION ALL
/* 4. Material Requisition Created */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Material Requisition Created' AS ActivityName,
R.REQ_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
R.REQ_CREATEDBY AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5REQUISITIONS R ON E.EVT_CODE = R.REQ_EVENT
UNION ALL
/* 5. Work Order Scheduled */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Scheduled' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'SCHD' -- Replace with your Scheduled status code
UNION ALL
/* 6. Work Order Started */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Started' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS IN ('STRT', 'INPRG') -- Replace with your Started/In Progress status codes
UNION ALL
/* 7. Material Issued */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Material Issued' AS ActivityName,
T.TRN_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
T.TRN_USER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5TRANSACTIONS T ON E.EVT_CODE = T.TRN_EVENT
WHERE T.TRN_TYPE = 'ISSUE' -- Indicates material issue from store
UNION ALL
/* 8. Labor Hours Recorded */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Labor Hours Recorded' AS ActivityName,
B.BOO_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
B.BOO_PERSON AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
B.BOO_HOURS AS ActualLaborHours
FROM R5EVENTS E
JOIN R5BOOKEDHOURS B ON E.EVT_CODE = B.BOO_EVENT
UNION ALL
/* 9. Checklist Item Verified */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Checklist Item Verified' AS ActivityName,
C.CKV_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
C.CKV_ENTEREDBY AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5CHECKLISTVALUES C ON E.EVT_CODE = C.CKV_EVENT
WHERE C.CKV_COMPLETED = '+' -- Assuming '+' indicates checked/complete
UNION ALL
/* 10. Work Order Completed */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Completed' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'COMP' -- Replace with your Completed status code
UNION ALL
/* 11. Quality Control Passed */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Quality Control Passed' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'QC' -- Replace with your QC Passed status code
UNION ALL
/* 12. Work Order Signed Off */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Signed Off' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'SIGNOFF' -- Replace with your Sign Off status code
UNION ALL
/* 13. Work Order Closed */
SELECT
E.EVT_CODE AS WorkOrderNumber,
'Work Order Closed' AS ActivityName,
S.STA_DATE AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
E.EVT_TYPE AS WorkOrderType,
E.EVT_PRIORITY AS PriorityCode,
E.EVT_MRC AS MaintenanceDepartment,
S.STA_UPDATEUSER AS UpdatedBy,
E.EVT_TARGET AS TargetCompletionDate,
E.EVT_OBJECT AS AssetCode,
NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'CLOS' -- Replace with your Closed status codeSteps
- Log in to the Hexagon EAM application with a user account that has Advanced Reporting Authoring privileges.
- Navigate to the Reports module, typically found under the Administration or Reports menu, and launch the Advanced Reporting interface (often Cognos or a built-in SQL report builder).
- Initialize a new report creation wizard and select 'SQL' or 'Free-hand SQL' as the data source type. This allows direct database querying rather than using the standard business object model.
- Copy the SQL script provided in the Query section below and paste it into the dataset definition window.
- Replace the placeholder values (such as [Your Organization Code] and date ranges) with specific values relevant to your environment.
- Validate the query syntax within the editor to ensure the database platform (Oracle or SQL Server) accepts the union statements.
- Map the resulting columns to the report layout list, ensuring the WorkOrderNumber, ActivityName, and EventTimestamp are clearly visible.
- Configure the report to export specifically to CSV or Excel format to ensure a tabular structure without merged cells or formatting headers.
- Run the report for a validation period (e.g., the last 100 work orders) to verify data structure.
- Once verified, schedule the report to run on a recurring basis or execute it for the full extraction time window.
- Download the output file and check the date formats. Ensure timestamps include seconds if possible for precise ordering.
- Import the resulting CSV file into ProcessMind, mapping the SourceSystem column as a case attribute and ActivityName as the activity classifier.
Configuration
- Date Range: It is recommended to filter by EVT_DATE (Creation Date) for a rolling window of 6 to 12 months to maintain query performance.
- Organization Filter: Hexagon EAM is often multi-org. Ensure the query includes a filter for your specific Organization Code to avoid data pollution from other business units.
- Database Syntax: The query below uses ANSI standard SQL where possible, but Hexagon EAM usually runs on Oracle or SQL Server. Slight adjustments to date functions (e.g., SYSDATE vs GETDATE) may be required.
- Status Configuration: The mapping of Status Codes (e.g., 'A', 'C', 'R') to Activity Names (e.g., 'Work Order Approved') must match your system's specific status configuration.
- Permissions: The user account running the report requires read access to R5EVENTS, R5EVENTSTATUS, R5BOOKEDHOURS, R5TRANSACTIONS, and R5AUDITVALUES tables.
a Sample Query config
/* 1. Work Order Created */
SELECT
evt_code AS WorkOrderNumber,
'Work Order Created' AS ActivityName,
evt_created AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
evt_type AS WorkOrderType,
evt_priority AS PriorityCode,
evt_mrc AS MaintenanceDepartment,
evt_updatedby AS UpdatedBy,
evt_target AS TargetCompletionDate,
evt_object AS AssetCode,
NULL AS ActualLaborHours
FROM r5events
WHERE evt_type IN ('JOB', 'PPM') -- Filter for relevant WO types
AND evt_created >= '2023-01-01' -- [Set Start Date]
AND evt_org = 'YOUR_ORG' -- [Set Organization]
UNION ALL
/* 2. Priority Updated (Requires Audit Log) */
SELECT
aud_code AS WorkOrderNumber,
'Priority Updated' AS ActivityName,
aud_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
aud_value AS PriorityCode,
NULL AS MaintenanceDepartment,
aud_user AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5auditvalues
WHERE aud_table = 'R5EVENTS'
AND aud_field = 'EVT_PRIORITY'
AND aud_org = 'YOUR_ORG'
UNION ALL
/* 3. Status Changes (Approved, Scheduled, Started, Completed, Signed Off, Closed) */
/* This block maps generic status codes to specific ProcessMind activities */
SELECT
est_event AS WorkOrderNumber,
CASE est_status
WHEN 'A' THEN 'Work Order Approved'
WHEN 'S' THEN 'Work Order Scheduled'
WHEN 'IP' THEN 'Work Order Started'
WHEN 'C' THEN 'Work Order Completed'
WHEN 'QC' THEN 'Quality Control Passed'
WHEN 'SO' THEN 'Work Order Signed Off'
WHEN 'CL' THEN 'Work Order Closed'
ELSE 'Status Change: ' + est_status
END AS ActivityName,
est_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
est_auth AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5eventstatus
WHERE est_status IN ('A', 'S', 'IP', 'C', 'QC', 'SO', 'CL')
AND est_org = 'YOUR_ORG'
UNION ALL
/* 4. Material Requisition Created */
SELECT
req_event AS WorkOrderNumber,
'Material Requisition Created' AS ActivityName,
req_created AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
req_createdby AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5requisitions
WHERE req_event IS NOT NULL
AND req_org = 'YOUR_ORG'
UNION ALL
/* 5. Material Issued */
SELECT
trl_event AS WorkOrderNumber,
'Material Issued' AS ActivityName,
trl_date AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
trl_user AS UpdatedBy,
NULL AS TargetCompletionDate,
trl_part AS AssetCode, -- Storing Part ID in Asset column for context
NULL AS ActualLaborHours
FROM r5translines
WHERE trl_type = 'I' -- I = Issue
AND trl_event IS NOT NULL
AND trl_org = 'YOUR_ORG'
UNION ALL
/* 6. Labor Hours Recorded */
SELECT
boo_event AS WorkOrderNumber,
'Labor Hours Recorded' AS ActivityName,
boo_entered AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
boo_mrc AS MaintenanceDepartment,
boo_person AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
boo_hours AS ActualLaborHours
FROM r5bookedhours
WHERE boo_event IS NOT NULL
AND boo_org = 'YOUR_ORG'
UNION ALL
/* 7. Checklist Item Verified */
SELECT
ckv_event AS WorkOrderNumber,
'Checklist Item Verified' AS ActivityName,
ckv_entrydate AS EventTimestamp,
'Hexagon EAM' AS SourceSystem,
GETDATE() AS LastDataUpdate,
NULL AS WorkOrderType,
NULL AS PriorityCode,
NULL AS MaintenanceDepartment,
ckv_enteredby AS UpdatedBy,
NULL AS TargetCompletionDate,
NULL AS AssetCode,
NULL AS ActualLaborHours
FROM r5checklistvalues
WHERE ckv_completed = '+' -- Logic for completed item depends on config
AND ckv_event IS NOT NULL
AND ckv_org = 'YOUR_ORG'