您的资产维护数据模板

Hexagon EAM

← 返回改进指南

资产维护属性(22) 资产维护活动(13) 数据提取指南(3)

您的资产维护数据模板

此模板全面介绍如何将Hexagon EAM中的维护工作流映射为适用于流程挖掘的数据格式。您将了解发现低效环节所需的数据点、需要监控的生命周期阶段，以及数据提取方面的技术建议。借助这些结构，您可以全面了解资产管理绩效。

维护日志必备数据字段
标准流程里程碑和工单阶段
Hexagon EAM数据提取技术逻辑

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

资产维护属性

这些建议的数据字段可为工单提供必要背景信息，并帮助您按资产类型或地点细分维护绩效。

5 必填 7 建议填写 10 选填

	名称	说明
	数据上次更新时间 LastDataUpdate	记录上次提取或刷新的时间戳。
说明此属性记录上次从源系统提取数据的时间，帮助分析人员了解数据的新鲜度，并发现报告管道中可能存在的延迟问题。重要性此属性有助于判断分析所用数据是否为最新数据，也有助于排查数据同步问题。获取途径提取时的系统时间示例 2023-10-25T12:00:00Z2023-10-26T00:00:00Z
	时间戳 EventTimestamp	活动发生的日期和时间。
说明此属性记录事件或状态变更写入系统的准确时间，用于计算活动间隔时长和工单整体周期时间。重要性准确的时间戳对于分析周期时间、发现延误和计算服务水平协议达成情况至关重要。获取途径 Hexagon EAM表R5EVENTHISTORY，字段EVH_DATE 示例 2023-10-12T08:30:00Z2023-10-12T14:15:00Z2023-10-13T09:00:00Z
	活动 ActivityName	发生的具体任务或状态变更事件。
说明此属性表示流程中正在执行的步骤，例如“工单已创建”或“物料已发放”。该属性根据工单状态变更历史或记录的具体事务事件生成。重要性此属性定义事件日志中的顺序，用于还原流程并进行瓶颈分析。获取途径 Hexagon EAM表R5EVENTHISTORY，字段EVH_STATUS（映射为描述），或审计日志示例工单已创建工单已排期工单已完成物料已发放
	源系统 SourceSystem	数据来源系统的名称。
说明此属性标明数据所属的记录系统。在此场景中，该值固定为具体的EAM系统名称，以便与可能存在的旧系统数据或外部承包商系统数据区分。重要性合并多个维护实例的数据时，此属性有助于清晰追溯数据来源。获取途径固定值示例 Hexagon EAMInfor EAMDatastream 7i
	维护工单 WorkOrderNumber	维护工单的唯一标识符。
说明此属性是维护案例的唯一主键，用于跟踪工单从初始申请、计划和执行到最终财务结算的全过程。它作为核心案例标识符，用于关联活动日志、工时和物料消耗记录。重要性此属性用于识别维护流程的具体实例，以便端到端跟踪周期时间和流程变体。获取途径 Hexagon EAM表R5EVENTS，字段EVT_CODE 示例 WO-100234WO-100235WO-100236104592
	优先级 PriorityCode	分配给工单的紧急程度。
说明此属性表示工单的重要程度，从常规到紧急不等。它影响资源排期，对于监控服务水平协议达成情况和发现优先级滥用至关重要。重要性优先级升级与资源审计仪表板需要确保严重任务得到正确的优先处理。获取途径 Hexagon EAM表R5EVENTS，字段EVT_PRIORITY 示例严重高中低
	实际工时 ActualLaborHours	记入工单的总工时。
说明此属性汇总技术人员记录在工单上的工时，可用于衡量生产率、计算人工成本，并分析实际工时与预估工时的差异。重要性此属性是技术人员劳动生产率仪表板和工时差异KPI的主要数据来源。获取途径 Hexagon EAM表R5BOOKEDHOURS，字段BOO_HOURS（按工单汇总）示例 2.54.012.00.5
	工单类型 WorkOrderType	将工单归类为预防性、纠正性或紧急维护。
说明此属性用于分类维护工作的性质。借助该属性，可以分析计划性预防维护与被动应急维修的比例，这是衡量资产健康状况和管理成熟度的重要指标。重要性此属性支持预防性维护比例分析仪表板，并帮助发现减少被动维护工作的机会。获取途径 Hexagon EAM表R5EVENTS，字段EVT_TYPE 示例 PPMCOREMGPRJ
	用户 UpdatedBy	执行该活动人员的用户ID。
说明此属性记录负责触发状态变更或更新人员的登录ID。您可以据此分析团队工作量、识别培训需求，并审计流程合规情况。重要性此属性可用于分析整个维护周期中的用户行为和资源利用情况。获取途径 Hexagon EAM表R5EVENTHISTORY，字段EVH_USER 示例 JSMITHADAVISSYSTEMMGR_USR
	目标完成日期 TargetCompletionDate	工单计划完成期限。
说明此属性表示预计完成维护工作的日期。将其与实际完成日期进行比较，是计算SLA达成情况和按时完成率的基础。重要性此属性对于承包商SLA达成情况监控和评估计划准确性至关重要。获取途径 Hexagon EAM表R5EVENTS，字段EVT_TARGET 示例 2023-11-012023-11-152023-12-01
	资产 AssetCode	正在维护的设备或设施的唯一标识符。
说明此属性将工单关联到特定设备或位置。您可以据此分析各项资产的故障频率，识别问题设备或需要更换的设备。重要性此属性是计算资产可靠性指标、了解维护工作重点分布的基础。获取途径 Hexagon EAM表R5EVENTS，字段EVT_OBJECT 示例 PUMP-001HVAC-204FLEET-99CONVEYOR-A
	部门 MaintenanceDepartment	负责执行工作的部门或工作中心。
说明此属性用于标识负责工单的团队或维修车间，例如电气、机械或设施团队。借助此属性，您可以对不同技术团队的绩效进行对标。重要性此属性支持技术人员劳动生产率仪表板，帮助您在不同工种间有效分配资源。获取途径 Hexagon EAM表R5EVENTS，字段EVT_MRC 示例电气车间机械维修设施车队服务
	SLA状态 SLAState	表示工单是否达到服务级别协议要求。
说明此计算属性将实际完成日期与目标完成日期进行比较，并将案例归类为“符合SLA”或“已超期”，便于报告承包商和内部团队的绩效。重要性此属性是SLA达成率KPI的直接数据来源。获取途径计算方式：实际结束日期<=目标日期示例符合SLA已超期未关闭
	创建日期 DateCreated	工单最初生成的日期。
说明此属性标记工单生命周期的起点，是计算平均计划时间和确定积压事项时长的基准。重要性此属性是维护计划效率仪表板和积压时长KPI所必需的数据。获取途径 Hexagon EAM表R5EVENTS，字段EVT_CREATED 示例 2023-09-012023-09-15
	实际总成本 TotalActualCost	工单记录的总财务成本。
说明此属性汇总人工、材料和服务成本，是分析财务结算以及了解紧急维护与预防性维护成本影响的关键数据。重要性此属性支持财务结算与关闭速度仪表板，以及基于成本的决策。获取途径 Hexagon EAM表R5EVENTS，字段EVT_ACTCOST（或类似汇总列）示例 150.002500.5075.25
	工单说明 WorkOrderDescription	对维护任务的简短文字说明。
说明此属性包含问题主题或简短描述。分析人员逐级下钻查看具体问题案例或识别非结构化文本中的模式时，可借此了解背景。重要性此属性为人工审核流程数据提供必要背景。获取途径 Hexagon EAM表R5EVENTS，字段EVT_DESC 示例修理漏水的泵密封件每月暖通空调检查更换输送带
	是否返工 IsRework	用于标记工单是否涉及重复的技术作业。
说明此计算属性用于识别流程从质量检查或完成状态退回到进行中状态的情况，帮助您发现质量问题和培训缺口。重要性此属性用于生成技术返工与质量控制仪表板，并计算维护返工率KPI。获取途径根据活动重复情况计算示例 truefalse
	是否预防性维护 IsPreventive	用于标记工单是否属于预防性维护。
说明此布尔属性便于筛选和分类工单。通过区分主动维护与故障维修，您可以更轻松地计算预防性维护占比KPI。重要性此属性是预防性维护占比分析仪表板的基础分类条件。获取途径根据WorkOrderType派生示例 truefalse
	材料申请日期 MaterialRequisitionDate	申请备件的日期。
说明此属性记录向仓库或采购部门提交材料申请的时间。分析从该日期到“材料发放”的间隔，可以了解供应链效率。重要性此属性是备件采购周期仪表板所必需的数据。获取途径 Hexagon EAM表R5REQUISITIONS或关联交易表示例 2023-10-012023-10-05
	紧急升级 EmergencyEscalationFlag	用于标记工单创建后优先级是否提升为紧急。
说明此布尔属性用于识别创建时优先级较低、之后升级为紧急的工单。此类情况频繁出现，可能意味着初始分级不准确，或有人利用系统获取更快服务。重要性此属性支持优先级升级与资源审计仪表板，并用于计算紧急升级率KPI。获取途径根据优先级字段的审计记录派生示例 truefalse
	质量控制已通过 QualityControlPassed	用于标记是否记录了质量控制步骤。
说明此布尔属性用于检查特定质量控制活动或检查清单项目是否已标记为完成，确保符合安全法规和内部标准。重要性此属性用于计算质量控制合规率KPI，并支持合规与文档完整性仪表板。获取途径根据检查清单表或活动历史记录派生示例 truefalse
	资产重要性 AssetCriticality	用于表示资产运营重要性的评分。
说明此属性表示资产对业务运营的重要程度。您可以用它核实高优先级工单是否确实涉及关键设备，从而发现潜在的流程滥用情况。重要性此属性通过关联优先级与资产价值，支持优先级升级与资源审计仪表板。获取途径 Hexagon EAM表R5OBJECTS，字段OBJ_CRITICALITY（需按EVT_OBJECT关联）示例 A - 关键B - 严重C - 次要D - 低

必填建议填写选填

资产维护活动

记录这些关键流程步骤和状态变更，以准确呈现维护工单和申请的端到端生命周期。

7 建议填写 6 选填

	活动	说明
	工单已关闭	工单已完成财务锁定并归档，无法再登记成本或工时。
重要性流程结束。对于分析财务结算完成情况和锁定最终成本至关重要。获取途径 Hexagon EAM的r5statuslog表。状态变为“C”（已关闭）或“L”（已锁定/已关闭）。采集在r5statuslog上执行事务时记录事件类型 explicit
	工单已创建	在系统中首次创建维护工单记录。此事件建立案例，并记录整个维护周期的起始时间戳。
重要性标记流程实例的起点，并作为衡量总解决时间的基准。对于计算积压时长至关重要。获取途径 Hexagon EAM的r5events表。插入包含evt_code的新记录时记录。采集在r5events上执行INSERT事务时记录事件类型 explicit
	工单已完成	技术工作已结束，技术人员已将作业标记为完成，资产恢复运行。
重要性这是端到端周期时长和平均维修时间分析的关键里程碑，也标志着停机计时结束。获取途径 Hexagon EAM的r5statuslog表。状态变为“C”（已完成）或“F”（已结束）。采集在r5statuslog上执行事务时记录事件类型 explicit
	工单已开始	技术人员已开始对资产进行实际作业。通常以状态变为“进行中”或首次记录工时的时间戳为标志。
重要性这是计算积压时长的终点和计算实际维修时间的起点，对服务水平协议达成率至关重要。获取途径 Hexagon EAM的r5statuslog表。状态变为“进行中”（通常状态代码为“I”或“IP”）。采集在r5statuslog上执行事务时记录事件类型 explicit
	工单已批准	状态变更表示工单已通过计划和估算审核，获准开始执行。
重要性用于分析维护计划效率，并标记计划阶段的结束。也可用于职责分离一致性检查。获取途径 Hexagon EAM的r5statuslog表。查找状态变为“A”（已批准）的记录，或系统中的对应状态。采集在r5statuslog上执行事务时记录事件类型 explicit
	已记录工时	技术人员将工作时长记入工单。每个案例可能多次发生此活动。
重要性用于生成工时差异和技术人员劳动生产率仪表板的基础数据，可揭示实际投入与计划的差异。获取途径 Hexagon EAM的r5booklabor表。每行代表一条工单工时记录。采集在r5booklabor上执行INSERT事务时记录事件类型 explicit
	物料已发放	备件从库房实际发出并计入工单，代表物料已被消耗。
重要性这是计算物料可用周期时间的终点。可据此发现工单已开始但因缺少零件而停滞的延误。获取途径 Hexagon EAM的r5translines表。类型为“I”（发料）且关联工单的事务。采集在r5translines上执行INSERT事务时记录事件类型 explicit
	优先级已更新	工单创建后，其优先级发生变更。此事件记录变更时间戳和新的优先级值。
重要性对于“优先级升级与资源审计”仪表板至关重要，可用于发现优先级滥用或范围蔓延。获取途径 Hexagon EAM的r5audittrail表（启用审计时），或根据快照推断。通常根据evt_priority字段的审计日志生成。采集在evt_priority上执行UPDATE事务时记录事件类型 explicit
	工单已排期	工单已分配计划开始日期或具体资源，从积压队列转入活动排期。
重要性对于分析技术人员劳动生产率和了解资源分配效率至关重要。获取途径 Hexagon EAM的r5events表（evt_schedstart更新），或r5statuslog表（状态变为“S”，即已排期）。采集比较状态字段变更前后的值，或检查evt_schedstart更新事件类型 inferred
	工单已签核	通常需要最终行政审批，以满足合规要求或支付承包商费用，并确认所有数据准确无误。
重要性用于监控工单审批周期分析。此处延误会影响财务结算速度。获取途径 Hexagon EAM的r5statuslog表（状态变为“已签核”），或电子签名记录日志。采集比较状态字段变更前后的值事件类型 inferred
	检查清单项目已核验	工单中的具体任务或检查步骤已标记为完成，表示作业的细分进度。
重要性对于合规和文档完整性至关重要，可证明安全检查已在系统中记录。获取途径 Hexagon EAM的r5checklist或r5activities表。具体活动或任务行项目的状态变更。采集在r5checklist上执行UPDATE事务时记录事件类型 explicit
	物料申请已创建	工单关联了备件或物料申请，由此启动供应链子流程。
重要性用于计算备件采购周期时间的起点。此处数量较多，说明流程依赖库存供应情况。获取途径 Hexagon EAM的r5requisitions表。通过req_evt（工单代码）关联。采集在r5requisitions上执行INSERT事务时记录事件类型 explicit
	质量控制已通过	主管或检查员审核已完成的工作并批准质量，确认维修符合要求后再关闭工单。
重要性用于计算质量控制合规率KPI。此处出现回流，说明发生了技术返工。获取途径 Hexagon EAM的r5statuslog表（状态变为“审核”或“QC”），或针对特定UDF验证的r5audittrail记录。采集比较状态字段变更前后的值事件类型 inferred

建议填写选填

数据提取指南

了解如何从Hexagon EAM环境中提取所需表和字段，构建高质量事件日志。

步骤

使用具有高级报表编写权限的用户账户登录Hexagon EAM应用程序。
进入“报表”模块，通常位于“管理”或“报表”菜单下，然后启动高级报表界面（通常为Cognos或内置SQL报表生成器）。
启动新建报表向导，并选择“SQL”或“自由编写SQL”作为数据源类型。这样即可直接查询数据库，而非使用标准业务对象模型。
复制下方“查询”部分提供的SQL脚本，并粘贴到数据集定义窗口。
将占位值（例如[您的组织代码]和日期范围）替换为适用于您环境的具体值。
在编辑器中验证查询语法，确保数据库平台（Oracle或SQL Server）能够接受UNION语句。
将查询结果列映射到报表布局列表，并确保清晰显示WorkOrderNumber、ActivityName和EventTimestamp。
将报表导出格式设为CSV或Excel，确保数据采用表格结构，且不含合并单元格或格式标题。
选择一个验证时间段运行报表（例如最近100个工单），检查数据结构。
验证无误后，设置定期运行报表，或针对完整的数据提取时间范围运行报表。
下载输出文件并检查日期格式。为确保排序准确，时间戳应尽可能包含秒。
将生成的CSV文件导入ProcessMind，并将SourceSystem列映射为案例属性，将ActivityName映射为活动分类字段。

配置

日期范围：建议按EVT_DATE（创建日期）筛选最近6至12个月的数据，以保持查询性能。
组织筛选：Hexagon EAM通常支持多组织。请确保查询按您的组织代码筛选，避免混入其他业务单元的数据。
数据库语法：下方查询尽可能采用ANSI标准SQL，但Hexagon EAM通常运行在Oracle或SQL Server上。可能需要微调日期函数（例如SYSDATE或GETDATE）。
状态配置：状态代码（例如“A”“C”“R”）与活动名称（例如“工单已批准”）之间的映射必须符合您系统中的具体状态配置。
权限：运行报表的用户账户需要具备对R5EVENTS、R5EVENTSTATUS、R5BOOKEDHOURS、R5TRANSACTIONS和R5AUDITVALUES表的读取权限。

查询示例 sql

/* 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'

步骤

确认数据库连接：确保您拥有Hexagon EAM Oracle或SQL Server后端数据库的只读凭据（通常为服务账户）。
查找核心表：确认您可以访问R5EVENTS（工单标题）、R5STATUS（状态历史）、R5BOOKEDHOURS（工时）、R5TRANSACTIONS（物料）和R5AUDITVALUES（审计日志）。
映射状态代码：Hexagon EAM使用可配置的状态代码（例如A、C、O、R）。查看系统配置（界面：管理 -> 代码），将您系统中的1或2个字符代码映射到“已批准”“已计划”“已开始”“已完成”“质量检查已通过”“已签字”和“已关闭”等活动。
配置查询：打开下方提供的SQL脚本，将占位状态代码（例如“APPR”“SCHD”“COMP”）替换为您系统中的实际代码。
设置日期范围：更新WHERE子句，按EVT_DATE或TRN_DATE筛选特定时间段，通常为最近12个月，以确保查询性能。
执行提取：在SQL客户端（SQL Developer、SSMS或Toad）中运行查询。
验证审计数据：如果“优先级已更新”活动没有返回任何行，请检查EAM系统设置中是否已为R5EVENTS表启用审计功能。
验证检查清单数据：确认R5CHECKLISTVALUES是您当前版本使用的表；旧版本可能使用R5ACTIVITIES记录任务级完成情况。
导出数据：将结果集保存为CSV或Parquet文件。请将Timestamp列设为ISO 8601格式（YYYY-MM-DD HH:MM:SS），以避免解析错误。
导入ProcessMind：将平面文件上传到ProcessMind界面，并在导入向导中将WorkOrderNumber映射为案例ID，将ActivityName映射为活动，将EventTimestamp映射为时间戳。

配置

时间范围筛选：当前查询会选取所有数据。强烈建议在基础CTE或每个SELECT区块中添加筛选条件，例如WHERE EVT_DATE >= SYSDATE - 365（Oracle）或GetDate() - 365（SQL Server）。
状态代码映射：您必须将SQL中的字符串字面值（例如“APPR”“COMP”“QC”）替换为R5STATUS中定义的实际代码。
审计配置：“优先级已更新”活动的数据提取依赖R5AUDITVALUES表。如果EVT_PRIORITY列未启用审计，则不会包含此活动。
组织/环境：如果您的EAM实例托管多个组织，请按EVT_ORG或EVT_MRC（部门）筛选，避免数据混杂。
语言：此查询假设列标题为标准英文。如果数据库使用本地化列名，请相应调整。

查询示例 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 code

步骤

确认Hexagon EAM Databridge Outbound已安装、获得许可并获准提取所需的维护工单数据。确认出站事务配置、目标位置、计划运行能力，以及对源表或视图的访问权限。由于菜单名称和事务标识符因EAM版本而异，请使用[根据您的系统配置]查找适用的管理菜单路径。
确定Hexagon EAM中用于工单标题、工单状态历史、优先级历史、物料申请、物料发放、工时事务、检查清单或检查结果、计划分配、质量审批、签字记录和关闭记录的权威表或视图。将查询中的每个[您的……表名]占位符替换为您环境中的对应对象。请勿假设当前工单标题包含历史变更记录。
配置Databridge Outbound，在相关工单变更提交时发布XML或CSV事务。包含工单编号、事件类型、事件时间戳、源用户、优先级、工单类型、部门、目标完成日期、资产、实际工时，以及刷新或提取时间戳。如果状态、工时、物料、检查清单、计划、质量、签字或关闭事件的源事务结构不同，请分别配置出站映射。
配置出站负载，将每个业务事件记录为单独的事件记录。不要只发送工单的最新状态。对于重复的工时记录、物料发放、检查清单项目、优先级变更或其他重复事件，每次发生都应输出一条记录。将Databridge事件类型映射到13个必需的ActivityName值之一。
将Databridge生成的XML或CSV输出写入受控的暂存位置，然后加载到查询引用的数据库对象中，或将查询源对象替换为暂存表。保留原始事件标识符、工单编号、事件时间戳、源用户和LastDataUpdate。如果Databridge发送XML，请先将重复事件节点解析为行，再进行活动映射。如果发送CSV，请验证分隔符、引号、编码和时间戳格式。
将查询中的占位符替换为连接专用对象、日期参数和公司筛选条件。查询会为每项活动使用规范化事件源，并通过UNION ALL合并结果。确保各区块使用的源列数据类型兼容。如果源数据未提供建议属性，请为该属性返回NULL，不要编造数值。
首次运行查询时，选择约三至六个月的时间范围。确认结果包含必需列WorkOrderNumber、ActivityName、EventTimestamp、SourceSystem和LastDataUpdate，以及可用的建议属性。按WorkOrderNumber、EventTimestamp和ActivityName对输出排序，同时保留代表不同源事件的重复行。
将提取行数与Databridge交付数量、源事务数量以及Hexagon EAM中的工单样本进行核对。调查缺失的状态历史、重复交付、无效时间戳和超出所选日期范围的事件。仅当同一源事件被重复交付时，才应用稳定的去重规则。
将最终结果导出为UTF-8 CSV或其他ProcessMind支持的事件日志格式。每行保留一个事件，统一时间戳及其时区处理方式，并将WorkOrderNumber映射为案例标识符、ActivityName映射为活动、EventTimestamp映射为事件时间戳。将文件上传到ProcessMind，并确认事件模型中包含全部13个活动名称。

配置

将Hexagon EAM Databridge Outbound配置为按事件级别交付XML或CSV。具体事务名称、管理界面和菜单路径取决于已安装的Hexagon EAM版本，因此请使用[根据您的系统配置]，不要假设适用于特定版本的路径。
将WorkOrderNumber配置为ProcessMind案例标识符，将ActivityName配置为活动字段，将EventTimestamp配置为事件时间戳。SourceSystem应填写Hexagon EAM，LastDataUpdate应记录提取或刷新时间。
为全部13项活动配置出站映射：工单已创建、优先级已更新、工单已批准、物料申请已创建、工单已计划、工单已开始、物料已发放、工时已记录、检查清单项目已核验、工单已完成、质量检查已通过、工单已签字、工单已关闭。
保留历史事件。仅凭当前状态字段，无法还原每次状态变更、优先级更新、工时记录、物料发放、检查清单核验、审批或签字。
首次提取使用三至六个月的日期范围。增量加载时，使用Databridge交付记录或LastDataUpdate水位标记，并设置较短的重叠时间段，以防遗漏延迟到达的事务。
仅在分析需要时，按公司、站点、组织、工单类型、部门和状态筛选。组织专用限制请使用[您的公司筛选条件]或[根据您的系统配置]。
如果分析涵盖完整生命周期，请包含已关闭工单。仅筛选未关闭工单会遗漏完成、质量检查、签字和关闭活动。
明确配置时区处理方式。上传到ProcessMind之前，将所有源时间戳转换为约定的同一时区，并在可用时保留原始时区或偏移量。
对于大型数据集，请在源端筛选、缩短提取时间范围、为事件时间戳和工单编号建立索引或分区，并在转换前暂存Databridge文件。避免反复解析完整的历史XML负载。
必要前提包括：可使用Databridge Outbound、获准读取相关EAM数据、可访问出站目标位置或暂存数据库，以及可访问工单、状态、物料、工时、检查清单、计划、审批、质量、签字和关闭数据。
如果Databridge原生事务不提供某项必需业务事件，请配置源视图或暂存转换，明确输出一条事件记录。ProcessMind不会根据其他行推断缺失活动。

查询示例 sql

WITH
created_events AS (
    SELECT
        CAST(c.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Created' AS VARCHAR(100)) AS ActivityName,
        CAST(c.[CreatedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(c.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(c.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(c.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(c.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(c.[CreatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(c.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(c.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(c.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order creation table or view] c
    WHERE c.[CreatedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND c.[CreatedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
priority_events AS (
    SELECT
        CAST(p.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Priority Updated' AS VARCHAR(100)) AS ActivityName,
        CAST(p.[PriorityChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(p.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(p.[NewPriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(p.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your priority history table or view] p
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = p.[WorkOrderNumber]
    WHERE p.[PriorityChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND p.[PriorityChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
approved_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Approved' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your approved status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
requisition_events AS (
    SELECT
        CAST(r.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Material Requisition Created' AS VARCHAR(100)) AS ActivityName,
        CAST(r.[RequisitionCreatedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(r.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(r.[CreatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your material requisition table or view] r
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = r.[WorkOrderNumber]
    WHERE r.[RequisitionCreatedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND r.[RequisitionCreatedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
scheduled_events AS (
    SELECT
        CAST(a.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Scheduled' AS VARCHAR(100)) AS ActivityName,
        CAST(a.[ScheduledTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(a.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(a.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(a.[ScheduledStartDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order scheduling table or view] a
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = a.[WorkOrderNumber]
    WHERE a.[ScheduledTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND a.[ScheduledTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
started_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Started' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] IN ('[Your in progress status value]', '[Your started status value]')
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
issued_events AS (
    SELECT
        CAST(i.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Material Issued' AS VARCHAR(100)) AS ActivityName,
        CAST(i.[IssueTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(i.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(i.[IssuedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your material issue table or view] i
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = i.[WorkOrderNumber]
    WHERE i.[IssueTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND i.[IssueTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
labor_events AS (
    SELECT
        CAST(l.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Labor Hours Recorded' AS VARCHAR(100)) AS ActivityName,
        CAST(l.[LaborEntryTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(l.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(l.[RecordedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(l.[LaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your labor transaction table or view] l
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = l.[WorkOrderNumber]
    WHERE l.[LaborEntryTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND l.[LaborEntryTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
checklist_events AS (
    SELECT
        CAST(k.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Checklist Item Verified' AS VARCHAR(100)) AS ActivityName,
        CAST(k.[VerifiedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(k.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(k.[VerifiedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your checklist verification table or view] k
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = k.[WorkOrderNumber]
    WHERE k.[VerifiedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND k.[VerifiedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
completed_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Completed' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your completed status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
quality_events AS (
    SELECT
        CAST(q.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Quality Control Passed' AS VARCHAR(100)) AS ActivityName,
        CAST(q.[QualityApprovalTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(q.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(q.[ApprovedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your quality approval table or view] q
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = q.[WorkOrderNumber]
    WHERE q.[QualityResult] = '[Your quality passed value]'
      AND q.[QualityApprovalTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND q.[QualityApprovalTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
signoff_events AS (
    SELECT
        CAST(f.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Signed Off' AS VARCHAR(100)) AS ActivityName,
        CAST(f.[SignOffTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(f.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(f.[SignedOffBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order sign-off table or view] f
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = f.[WorkOrderNumber]
    WHERE f.[SignOffTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND f.[SignOffTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
closed_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Closed' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your closed status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
)
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM created_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM priority_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM approved_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM requisition_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM scheduled_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM started_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM issued_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM labor_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM checklist_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM completed_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM quality_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM signoff_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM closed_events
ORDER BY WorkOrderNumber, EventTimestamp, ActivityName;

准备好开始了吗？

按照此模板，将维护数据转化为可执行的洞察。在准备数据期间，如有技术问题，我们的团队可为您提供帮助。

返回改进指南

下载模板

立即缩短资产维护延误，提升绩效

将周期时间缩短30%，消除隐性瓶颈。

开始免费试用

无需信用卡，几分钟即可完成设置。

您的资产维护数据模板

您的资产维护数据模板

资产维护属性

资产维护活动

数据提取指南

Hexagon EAM高级报表模块导出

步骤

配置

查询示例 sql

直接从R5EVENTS和R5STATUS提取SQL数据

步骤

配置

查询示例 sql

Hexagon EAM Databridge出站事务

步骤

配置

查询示例 sql

准备好开始了吗？

立即缩短资产维护延误，提升绩效