> 持续流程改进 > 资产维护 > Oracle Maintenance Cloud > 数据模板

您的资产维护数据模板

Oracle Maintenance Cloud

您的资产维护数据模板

此模板提供结构化框架，帮助您整理Oracle Maintenance Cloud中的维护记录，以便开展深入的流程挖掘。模板列出了全面了解设备维护和资源分配所需的数据点及流程里程碑。按照这些指引，您可以构建完整的事件日志，发现低效环节并改进维护策略。

全面分析所需的属性
需要监控的关键流程里程碑
Oracle数据提取指南

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

下载模板

资产维护属性

这些推荐的数据字段可用于分类和筛选维护事件，帮助您深入了解设备性能和资源利用情况。

5 必填 8 建议填写 9 选填

	名称	说明
	事件时间戳 EventDateTime	活动发生的日期和时间。
说明此属性记录事件发生的准确时刻，用于按时间顺序排列案例中的活动，并计算步骤之间的时长。准确的时间戳对于计算周期时间、发现延迟以及衡量SLA达成情况至关重要。重要性用于确定流程时间线。获取途径交易表中的CREATION_DATE或LAST_UPDATE_DATE列。示例 2023-10-15T08:30:00Z2023-10-15T09:15:00Z2023-10-16T14:00:00Z
	活动 ActivityName	工作流中发生的事件或状态变更名称。
说明此属性记录维修流程中执行的具体步骤，例如“创建工单”“开始操作”或“关闭工单”，对应流程图中的节点。分析人员可借此查看流程走向、识别流程变体，并发现具体步骤之间的瓶颈。重要性用于定义流程走向和事件顺序。获取途径根据交易历史表或状态变更日志推导。示例创建维修工单发布工单完成操作
	维修工单 WorkOrderNumber	维修工单的唯一标识符。
说明工单编号是维修流程中唯一的案例标识符，可跟踪维修任务从初始申请或排程生成，到执行、完成和财务结算的整个生命周期。在分析中，此属性是将事件数据与案例级属性关联起来的核心键。分析人员可据此汇总单项工作的所有活动，并计算端到端周期时间。重要性这是流程实例的主键，对于还原案例历史记录至关重要。获取途径 WIE_WORK_ORDERS_B表，列WORK_ORDER_NUMBER 示例工单-2023-0015工单-2023-0089工单-2023-1102
	最近数据更新时间 LastDataUpdate	最近一次数据提取的时间戳。
说明数据最近一次在流程挖掘工具中提取或刷新的日期和时间，帮助用户了解分析所用数据的时效性。这是数据治理所需的系统元数据字段。重要性让用户了解数据的时效性。获取途径提取时的系统时间。示例 2023-10-30T12:00:00Z
	源系统 SourceSystem	数据来源系统的名称。
说明指明提取记录的源软件或数据库。在此场景中，通常为“Oracle Maintenance Cloud”。在多系统环境中，这一信息对于追踪数据沿袭至关重要。重要性在多系统环境中追踪数据沿袭。获取途径提取时硬编码。示例 Oracle Maintenance CloudOracle Cloud ERP
	优先级 Priority	分配给工单的紧急程度。
说明表示工单的重要程度，例如严重、高、中或低，并影响排程和资源分配。这是工单转化效率仪表板的重要依据，可确保严重故障得到优先处理。重要性决定目标响应时间和资源分配。获取途径 WIE_WORK_ORDERS_B表中的WORK_ORDER_PRIORITY_ID列。示例严重高标准
	实际完成日期 ActualCompletionDate	维护工作完成的日期和时间。
说明记录实际工作的最终完成时间。此时间戳用于结束生命周期中的执行阶段。这是技术执行周期时间的终点，也是行政延迟时间的起点。重要性标记技术执行阶段的结束。获取途径 WIE_WORK_ORDERS_B表，COMPLETED_DATE或CLOSED_DATE列。示例 2023-10-252023-10-262023-10-27
	实际开始日期 ActualStartDate	实际开始工作的日期和时间。
说明记录技术人员实际开始工作的时间，通常由“操作已开始”事务触发。用于计算物料供应延迟的终点和资源排程灵活度。重要性这是计算实际执行时长与计划时长差异的关键数据。获取途径 WIE_WORK_ORDERS_B表，ACTUAL_START_DATE列。示例 2023-10-212023-10-222023-10-23
	工单状态 WorkOrderStatus	工单当前所处的生命周期阶段。
说明显示工单状态，例如未发布、已发布、暂停、已完成或已关闭，帮助筛选仍在处理中的案例和已完成的案例。可用于仪表板，查看当前在制工作（WIP），并识别停滞在特定状态的工单。重要性显示工单进度的当前状态。获取途径 WIE_WORK_ORDERS_B表中的WORK_ORDER_STATUS_ID列。示例已发布暂停已关闭
	指定技术人员 AssignedTechnician	负责执行任务的人员或资源组。
说明记录负责这项工作的技术人员用户ID或员工ID，可在工单表头或操作级别分配。可用于技术人员利用率和人工分析，识别技能缺口或资源过度使用。重要性支持资源绩效分析和产能规划。获取途径 WIE_OPERATION_RESOURCES表或类似的分配表。示例 J. SmithTech-005外部承包商
	组织代码 OrganizationCode	维修组织或工厂的代码。
说明表示负责维修工作的具体业务单元、工厂或设施，可用于按地区或设施进行对标。可用于比较不同站点或运营单元之间的绩效差异。重要性按实际地点或业务单元细分分析。获取途径 WIE_WORK_ORDERS_B表中的ORGANIZATION_ID列。示例 M1纽约工厂伦敦仓库
	维修类型 WorkOrderType	将工单归类为预防性、纠正性或预测性维修。
说明此属性定义维修工作的性质。常见值包括预防性、纠正性、紧急或基于状态的维修。该属性是主动维护占比KPI的重要依据，分析人员可据此比较计划内工作量与故障后的非计划维修量。重要性区分计划内维护与故障后的维修。获取途径 WIE_WORK_ORDERS_B表中的WORK_ORDER_TYPE_ID列（关联类型定义表）。示例预防性纠正性紧急
	资产编号 AssetNumber	受维护设备的唯一标识符。
说明标识维修工单所涉及的具体实体资产或机器，并将流程与资产层级关联起来。这是资产返修频率KPI的重要依据，可帮助分析人员发现反复发生故障的设备。重要性将维修活动关联到具体的实体设备。获取途径 WIE_WORK_ORDERS_B表中的ASSET_ID列（关联CSI_ITEM_INSTANCES）。示例泵-101暖通空调设备-5输送带-A
	SLA目标日期 SlaTargetDate	工单必须完成的截止日期。
说明根据优先级和创建日期计算，代表对业务方作出的资产修复承诺。用于SLA达成率KPI，以判断维护服务是否履行了服务义务。重要性定义按时完成的成功标准。获取途径通常为自定义字段，或根据优先级配置计算得出。示例 2023-11-012023-11-052023-11-10
	实际人工工时 ActualLaborHours	技术人员处理此工单所花费的总工时。
说明工单所有人工事务中记录的时长总和，用于衡量实际投入。用于“预估与实际人工工时差异”KPI，以评估计划准确性。重要性用于成本核算和人员效率分析的关键指标。获取途径汇总自WIE_WORK_ORDER_TRANSACTIONS（资源事务）。示例 4.512.00.75
	工单说明 WorkOrderDescription	简要描述问题或任务的文本。
说明由请求人或计划人员填写的自由文本字段，用于描述问题或所需工作，可补充结构化数据未能体现的背景信息。可用于文本分析，或人工审核“其他”工作类型。重要性为维护任务提供易于理解的背景信息。获取途径 WIE_WORK_ORDERS_B表，WORK_ORDER_DESCRIPTION列。示例更换输送带电机月度安全检查修复A泵漏油
	总成本 TotalCost	工单产生的总成本。
说明工单相关的物料、人工和间接费用总和，通常在“维护成本已转移”活动中最终确定。用于维护计划准确性分析，对比预算与实际支出。重要性维护活动的主要财务指标。获取途径与工单ID关联的成本核算模块表。示例 1500.00250.505000.00
	是否返工 IsRework	标记此工单是否为重复维修。
说明布尔标记：如果同一资产ID在此前30天内有另一张工单已关闭，则值为true。这是一个计算属性。该属性直接用于资产返工频率KPI，有助于发现维修质量问题或资产故障反复发生的情况。重要性用于识别质量问题和反复发生的故障。获取途径在数据转换层计算。示例 truefalse
	计划开始日期 ScheduledStartDate	计划开始维护工作的日期。
说明计划开始工作的日期和时间。将其与实际开始日期对比，可了解计划执行情况。用于排程与延误分析，以发现维护计划受到的影响。重要性衡量计划执行情况和延误的基准。获取途径 WIE_WORK_ORDERS_B表，PLANNED_START_DATE列。示例 2023-10-202023-10-212023-10-22
	请求日期 RequestDate	收到初始维护请求的日期。
说明记录首次发现并登记维护需求的时间戳，用于计算前置时间。这是工单转换前置时间KPI的关键数据，可用于衡量从发现问题到创建工单的延迟。重要性标记端到端流程生命周期的起点。获取途径 WIE_WORK_ORDERS_B表，根据源请求关联信息生成。示例 2023-10-012023-10-052023-11-12
	资产类别 AssetCategory	按暖通空调、车队或生产线等类型对资产分组。
说明将资产划分为更广泛的类别，以便从整体上分析不同设备类型的维护绩效。可用于维修周期时间概览仪表板，找出导致延迟最多的设备类别。重要性支持按设备类型汇总分析维护绩效。获取途径根据分配给资产ID的物料类别推导。示例重型机械车队车辆设施
	预估人工工时 EstimatedLaborHours	工单计划人工工时。
说明计划人员预估完成工作所需的时间，在计划或发布阶段设定。与实际人工工时对比，可计算差异并改进未来的计划标准。重要性评估执行效率的基准。获取途径 WIE_OPERATION_RESOURCES表中的计划数量。示例 5.010.01.0

必填建议填写选填

资产维护活动

这些活动涵盖维护工单端到端流程中的关键生命周期阶段和运营更新。

8 建议填写 7 选填

	活动	说明
	关闭工单	工单的最终行政结案。此后不再产生新成本，工单也完成了会计结算。
重要性标志着行政处理周期结束。从完成到关闭之间的时间即为行政处理延迟。获取途径根据WIE_WO_STATUS_HISTORY推导，条件为STATUS_CODE变为'CLOSED'。采集比较状态字段变更前后的值事件类型 explicit
	创建维修工单	系统生成Work Order实体，可能由维修申请转化而来，也可能由人工创建。此事件为流程挖掘确定案例ID，并作为计划和执行阶段的起点。
重要性这是流程实例的主要锚点事件，用于区分受理阶段与计划、执行阶段。获取途径根据WIE_WORK_ORDERS_B表中的CREATION_DATE时间戳推导。采集创建WO表头记录时记录事件类型 explicit
	创建维修申请	通过自助服务门户或服务台界面首次提交维修申请。该事件记录故障或服务需求的最初迹象，此时尚未生成正式工单，通常可在Work Requests表中找到。
重要性标志着维修需求周期的真正起点。计算从发现故障到生成可执行工单的周期时间时，这一事件必不可少。获取途径根据MNT_WORK_REQUESTS表中的CREATION_DATE字段推导。采集向Work Requests表插入记录时记录事件类型 explicit
	发布工单	工单状态从草稿或未发布变为已发布。此操作授权为该工单领用物料和资源。
重要性标志着计划阶段结束，执行工作获准开始。此处的延迟可能表明计划环节存在瓶颈。获取途径根据WIE_WO_STATUS_HISTORY推导，条件为STATUS_CODE变为'RELEASED'。采集比较状态字段变更前后的值事件类型 inferred
	发放物料	从仓库实际扣减并发放给指定工单的库存，代表备件已实物运送至维修地点。
重要性这是工作即将开始的明确信号。将此事件与“已发布”事件进行比较，可以发现仓库拣货环节的延迟。获取途径根据INV_MATERIAL_TXNS表推导，其中TRANSACTION_SOURCE_TYPE_ID关联到工单。采集执行交易X时记录事件类型 explicit
	完成工单	整个工单的技术工作已完成。这表示实体资产已修复并可投入使用，但财务处理可能仍在继续。
重要性从运营角度看，这标志着维修作业实际结束，可用于计算SLA达成情况。获取途径根据WIE_WO_STATUS_HISTORY推导，条件为STATUS_CODE变为'COMPLETED'。采集比较状态字段变更前后的值事件类型 explicit
	开始操作	开始工单中的某项操作（例如操作10、操作20），即技术工作实际开始的时刻。
重要性代表“开始工作”这一里程碑。计算实际维修工时与计划时长时，这一事件必不可少。获取途径根据WIE_WO_OPERATIONS_B的状态变更，或该操作的首个资源交易时间戳推导。采集通过比较字段X和Y推导事件类型 inferred
	记录人工工时	记录技术人员在工单上投入的时间。这是一项交易事件，相关成本会计入维修活动。
重要性跟踪工作量和成本累计情况。此类事件多次出现，表示工作仍在进行，可用于计算人工工时总偏差。获取途径根据WIE_RESOURCE_TRANSACTIONS表推导。采集执行交易X时记录事件类型 explicit
	分配物料	为工单预留或分配所需的备件和组件，确保开工前库存充足。
重要性这对分析供应链准备情况至关重要。此步骤延迟可能意味着库存短缺或采购计划缓慢。获取途径根据WIE_WO_COMPONENTS_B表中的CREATION_DATE或ALLOCATION_DATE推导。采集执行交易X时记录事件类型 explicit
	取消工单	工单未成功完成便终止。可能是因为工作重复、无需执行或已合并处理。
重要性取消率偏高可能反映申请受理流程或重复项识别逻辑存在问题。获取途径根据WIE_WO_STATUS_HISTORY推导，条件为STATUS_CODE变为'CANCELED'。采集比较状态字段变更前后的值事件类型 explicit
	安排工单	为工单操作指定具体开始日期或资源（技术人员、工具），并更新系统中的计划日期。
重要性用于衡量派工或排程团队的效率。发布与排程之间的间隔可能表明资源竞争。获取途径根据WIE_OPERATION_RESOURCES或WIE_WO_OPERATIONS_B中的排程字段更新推导。采集比较状态字段变更前后的值事件类型 inferred
	完成操作	完成路线中的某个步骤（操作），表示一部分技术工作已经结束。
重要性提供更细致的进度视图。“执行维修任务”通常根据最后一项操作的完成情况推导。获取途径根据WIE_WO_OPERATIONS_B的状态变更或显示操作已完成的交易历史推导。采集比较状态字段变更前后的值事件类型 inferred
	工单暂停	工单状态变为“暂停”，执行工作随之停止。通常是因为缺少备件、无法进入现场或存在安全问题。
重要性这是流程低效的重要来源。了解暂停的频率和时长，是缩短整体周期时间的关键。获取途径根据WIE_WO_STATUS_HISTORY推导，条件为STATUS_CODE变为'ON_HOLD'。采集比较状态字段变更前后的值事件类型 explicit
	记录质量检查	录入与维修工单相关的质量检查结果，以验证维修是否符合安全和运营标准。
重要性确保符合要求。完工前缺少质量检查，可能意味着存在安全风险或流程偏差。获取途径根据QA_RESULTS或类似的质量采集计划表推导，这些表与工单ID相关联。采集执行交易X时记录事件类型 explicit
	转移维修成本	将累计成本从维修系统转入成本管理模块，完成财务结算。
重要性确认维修产生的财务影响已入账，是分析“维修计划准确性”的重要依据。获取途径根据与工单交易来源关联的成本会计分配表推导。采集执行交易X时记录事件类型 inferred

建议填写选填

数据提取指南

如何从Oracle Maintenance Cloud获取数据

步骤

准备工作：登录Oracle Cloud Console，进入Business Intelligence Cloud Connector（BICC）控制台。
创建新的Offering Configuration，或编辑现有配置。找到“Maintenance Management”或“Supply Chain Management”对应的Offering。
选择所需的Public View Objects（PVO）进行提取。必须选择：'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderExtractPVO'（工单表头）、'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderStatusHistoryExtractPVO'（状态变更）、'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkRequestExtractPVO'（请求）、'FscmTopModelAM.ScmExtractAM.InvBiccExtractAM.MaterialTransactionExtractPVO'（物料发放）和'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderResourceTransactionExtractPVO'（人工）。
配置提取文件的存储位置。将BICC作业指向有效的Oracle Universal Content Management（UCM）文件夹或外部Object Storage存储桶（例如OCI Object Storage）。
设置提取任务的计划。首次加载时选择“Full Extract”。后续运行时，根据“LastUpdateDate”配置“Incremental Extract”，以减少数据量。
从存储位置下载生成的CSV文件，并加载到暂存数据库或数据湖（例如Snowflake、BigQuery或SQL Server）。为便于识别，请确保暂存环境中的表名与PVO名称一致。
对暂存表运行下方Query部分提供的SQL转换脚本，将不同PVO中的数据整合为统一的事件日志结构。
检查输出是否包含所有必需列：WorkOrderNumber、ActivityName、EventDateTime和SourceSystem。
如果BICC提取的数据采用UTC（通常如此），而分析需要使用工厂当地时间，请进行必要的时区转换。
将最终结果导出为CSV或Parquet文件。
将文件上传到ProcessMind，并将案例ID映射到WorkOrderNumber，将时间戳映射到EventDateTime。

配置

提取模式：首次运行使用“Full”，之后切换为“Incremental”，仅提取自上次运行以来发生变更的记录。
数据截断日期：在BICC配置中设置“Pruning Date”，限制历史数据范围（例如最近12至24个月），以提升性能。
列选择：在BICC控制台中，可取消选择PVO中不需要的列以减小文件体积。请保留主键、外键、状态代码和日期时间戳。
获取大小：通常使用默认获取大小即可。对于工单数量达到数百万的高数据量环境，如果目标存储支持，可考虑增加批次大小。
筛选器：如果只分析特定组织，可在BICC的PVO配置中按“OrganizationCode”筛选，从源头过滤数据。

查询示例 sql

/* 
  SQL Transformation Script for Oracle Maintenance Cloud via BICC
  Assumes BICC CSVs are loaded into staging tables:
  - STG_WORK_ORDER (Source: WorkOrderExtractPVO)
  - STG_WO_STATUS_HISTORY (Source: WorkOrderStatusHistoryExtractPVO)
  - STG_WORK_REQUEST (Source: WorkRequestExtractPVO)
  - STG_WO_MATERIAL (Source: WorkOrderMaterialExtractPVO)
  - STG_MAT_TRX (Source: MaterialTransactionExtractPVO)
  - STG_WO_OP_TRX (Source: WorkOrderOperationTransactionExtractPVO)
  - STG_WO_RES_TRX (Source: WorkOrderResourceTransactionExtractPVO)
  - STG_QA_RESULTS (Source: QAResultsExtractPVO)
  - STG_COST_TRX (Source: CostTransactionExtractPVO)
*/

SELECT
    CAST(WR.WorkRequestNumber AS VARCHAR(255)) AS WorkOrderNumber, -- Using Request Number temporarily or link to WO if exists
    'Maintenance Request Created' AS ActivityName,
    WR.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS WorkOrderType,
    WR.StatusCode AS WorkOrderStatus,
    WR.AssetNumber AS AssetNumber,
    WR.OrganizationCode AS OrganizationCode,
    NULL AS Priority,
    NULL AS AssignedTechnician,
    NULL AS ActualStartDate,
    NULL AS ActualCompletionDate
FROM STG_WORK_REQUEST WR

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Maintenance Work Order Created' AS ActivityName,
    WO.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WORK_ORDER WO

UNION ALL

-- Captures Released, On Hold, Completed, Closed, Canceled based on status history
SELECT
    WO.WorkOrderNumber,
    CASE 
        WHEN WSH.StatusCode = 'RELEASED' THEN 'Work Order Released'
        WHEN WSH.StatusCode = 'ON_HOLD' THEN 'Work Order On Hold'
        WHEN WSH.StatusCode = 'COMPLETED' THEN 'Work Order Completed'
        WHEN WSH.StatusCode = 'CLOSED' THEN 'Work Order Closed'
        WHEN WSH.StatusCode = 'CANCELED' THEN 'Work Order Canceled'
        ELSE CONCAT('Status Change: ', WSH.StatusCode)
    END AS ActivityName,
    WSH.StatusChangeDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WSH.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_STATUS_HISTORY WSH
JOIN STG_WORK_ORDER WO ON WSH.WorkOrderId = WO.WorkOrderId
WHERE WSH.StatusCode IN ('RELEASED', 'ON_HOLD', 'COMPLETED', 'CLOSED', 'CANCELED')

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Materials Allocated' AS ActivityName,
    WOM.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_MATERIAL WOM
JOIN STG_WORK_ORDER WO ON WOM.WorkOrderId = WO.WorkOrderId

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Materials Issued' AS ActivityName,
    MT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_MAT_TRX MT
JOIN STG_WORK_ORDER WO ON MT.WorkOrderNumber = WO.WorkOrderNumber -- Note: Linking via WO Number string if ID not present in Inv Extract
WHERE MT.TransactionTypeId IN (35, 43) -- Standard Type IDs for WIP Issue/Return, adjust based on specific setup

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Work Order Scheduled' AS ActivityName,
    WO.ScheduledStartDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WORK_ORDER WO
WHERE WO.ScheduledStartDate IS NOT NULL
AND WO.ScheduledStartDate <> WO.CreationDate -- Attempt to isolate explicit scheduling action

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Operation Started' AS ActivityName,
    WOT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_OP_TRX WOT
JOIN STG_WORK_ORDER WO ON WOT.WorkOrderId = WO.WorkOrderId
WHERE WOT.TransactionType = 'OP_START' -- Adjust literal based on lookup code

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Operation Completed' AS ActivityName,
    WOT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_OP_TRX WOT
JOIN STG_WORK_ORDER WO ON WOT.WorkOrderId = WO.WorkOrderId
WHERE WOT.TransactionType = 'OP_COMPLETE' -- Adjust literal based on lookup code

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    WRT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    WRT.PersonName AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_RES_TRX WRT
JOIN STG_WORK_ORDER WO ON WRT.WorkOrderId = WO.WorkOrderId

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Quality Inspection Recorded' AS ActivityName,
    QR.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_QA_RESULTS QR
JOIN STG_WORK_ORDER WO ON QR.SourceReferenceId = WO.WorkOrderId -- Mapping depends on Quality Plan setup

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Maintenance Costs Transferred' AS ActivityName,
    CT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_COST_TRX CT
JOIN STG_WORK_ORDER WO ON CT.SourceRefId = WO.WorkOrderId
WHERE CT.TransactionSource = 'MAINTENANCE'

步骤

使用具有BI Administrator或Data Model开发权限的用户登录Oracle Fusion Cloud环境。
进入Tools > Reports and Analytics，点击Browse Catalog打开Oracle BI Publisher界面（通常为/xmlpserver）。
在目录中点击右上角的Create按钮，然后选择Data Model。
在Data Model编辑器左侧找到Data Sets面板，点击加号（+），然后选择SQL Query。
为数据集命名（例如MaintenanceEventLog），选择合适的Data Source（通常为ApplicationDB_SCM或类似选项），并将Type保留为Standard SQL。
将下方提供的完整SQL查询粘贴到SQL Query文本框中。
检查具体表名（例如QA_RESULTS或Cost表），因为这些名称可能因模块实施方式而异；如果环境需要使用不同的架构引用，请调整架构前缀（例如FUSION.WIE_...）。
如需设置日期范围参数，请进入Parameters选项卡，将WHERE子句中的硬编码时间戳替换为:p_start_date等绑定变量。
点击View Data按钮，选择样本数量并点击View，确认输出包含各种活动类型的记录。
确认无误后，将Data Model保存到Shared Folder（例如Custom/Maintenance）。
如需提取完整日志，请基于此Data Model创建报告，取消选择所有布局选项以导出原始数据，并将报告计划设置为CSV或XML格式。
下载生成的文件并导入ProcessMind，确保EventDateTime列按照正确的时区偏移解析。

配置

数据源：确保SQL查询针对SCM或Maintenance Cloud运营数据库运行，而非归档数据库。
日期范围：当前查询会提取所有历史数据。用于生产环境时，请添加WHERE子句，按CREATION_DATE或TRANSACTION_DATE筛选（例如最近12个月），以提升性能。
组织筛选：如果环境中有多个维护组织，请按ORGANIZATION_CODE或ORGANIZATION_ID筛选，以便按工厂区分日志。
系统时区：Oracle Cloud以UTC存储日期。请确保ProcessMind项目将这些时间戳识别为UTC，或调整SQL将其转换为当地时区。
状态代码：查询假定使用标准Oracle查找代码（例如ORA_WO_RELEASED）。如果您创建了自定义状态代码，请更新状态历史部分中的CASE语句或WHERE子句。
验证：请务必将行数与Manage Maintenance Work Orders界面中的数据进行核对，确保没有遗漏重要数据子集。

查询示例 sql

/* 1. Maintenance Request Created */
SELECT
    CAST(req.WORK_REQUEST_NUMBER AS VARCHAR(50)) AS WorkOrderNumber,
    'Maintenance Request Created' AS ActivityName,
    req.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    'Corrective' AS WorkOrderType,
    'NEW' AS WorkOrderStatus,
    req.ASSET_NUMBER AS AssetNumber,
    req.ORGANIZATION_CODE AS OrganizationCode,
    NULL AS Priority,
    NULL AS AssignedTechnician,
    NULL AS ActualStartDate,
    NULL AS ActualCompletionDate
FROM WIE_WORK_REQUESTS_B req

UNION ALL

/* 2. Maintenance Work Order Created */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Maintenance Work Order Created' AS ActivityName,
    wo.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'UNRELEASED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WORK_ORDERS_B wo

UNION ALL

/* 3. Work Order Released */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Released' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'RELEASED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_RELEASED'

UNION ALL

/* 4. Materials Allocated (Requirement Definition) */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Materials Allocated' AS ActivityName,
    comps.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_OPERATION_COMPONENTS comps
JOIN WIE_WORK_ORDERS_B wo ON comps.WORK_ORDER_ID = wo.WORK_ORDER_ID

UNION ALL

/* 5. Materials Issued */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Materials Issued' AS ActivityName,
    mat_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_MATERIAL_TRANSACTIONS mat_txn
JOIN WIE_WORK_ORDERS_B wo ON mat_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE mat_txn.TRANSACTION_TYPE_CODE = 'MATERIAL_ISSUE'

UNION ALL

/* 6. Work Order Scheduled */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Scheduled' AS ActivityName,
    ops.LAST_UPDATE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATIONS_B ops
JOIN WIE_WORK_ORDERS_B wo ON ops.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE ops.PLANNED_START_DATE IS NOT NULL
AND ops.CREATION_DATE <> ops.LAST_UPDATE_DATE

UNION ALL

/* 7. Operation Started */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Operation Started' AS ActivityName,
    op_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS op_txn
JOIN WIE_WORK_ORDERS_B wo ON op_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE op_txn.TRANSACTION_TYPE_CODE IN ('ORA_WIE_OP_START', 'OP_START')

UNION ALL

/* 8. Labor Hours Recorded */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    res_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    res_txn.RESOURCE_CODE AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS res_txn
JOIN WIE_WORK_ORDERS_B wo ON res_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE res_txn.TRANSACTION_TYPE_CODE = 'RESOURCE_TRANSACTION'

UNION ALL

/* 9. Work Order On Hold */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order On Hold' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'ON_HOLD' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_ON_HOLD'

UNION ALL

/* 10. Quality Inspection Recorded */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Quality Inspection Recorded' AS ActivityName,
    qa.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    qa.CREATED_BY AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM QA_RESULTS qa
JOIN WIE_WORK_ORDERS_B wo ON qa.WORK_ORDER_ID = wo.WORK_ORDER_ID

UNION ALL

/* 11. Operation Completed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Operation Completed' AS ActivityName,
    op_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS op_txn
JOIN WIE_WORK_ORDERS_B wo ON op_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE op_txn.TRANSACTION_TYPE_CODE IN ('ORA_WIE_OP_COMPLETE', 'OP_COMPLETE')

UNION ALL

/* 12. Work Order Completed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Completed' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'COMPLETED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_COMPLETED'

UNION ALL

/* 13. Work Order Closed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Closed' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'CLOSED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_CLOSED'

UNION ALL

/* 14. Work Order Canceled */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Canceled' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'CANCELED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_CANCELED'

UNION ALL

/* 15. Maintenance Costs Transferred */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Maintenance Costs Transferred' AS ActivityName,
    cst.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM CST_TRANSACTIONS cst
JOIN WIE_WORK_ORDERS_B wo ON cst.SOURCE_HEADER_ID = wo.WORK_ORDER_ID
WHERE cst.TRANSACTION_SOURCE_CODE = 'MAINTENANCE'

步骤

准备工作：确保您已配置Oracle Cloud用户，并为其分配Maintenance Manager角色且启用REST API访问权限。
环境设置：安装Python（3.8或更高版本），并使用pip install pandas requests安装pandas和requests库。
确定端点：找到Oracle Fusion实例的基础URL（例如https://fa-instance.oraclecloud.com）及以下REST端点：maintenanceWorkOrders、maintenanceWorkOrders/child/WorkOrderOperation和maintenanceWorkOrders/child/WorkOrderMaterialTransaction。
配置身份验证：为脚本配置基本身份验证凭据（用户名和密码）；如果安全策略有要求，也可实现OAuth 2.0令牌处理。
定义范围：设置数据提取的时间范围（例如LastUpdateDate > 2023-01-01），以控制数据量并确保运行效率。
提取工单表头：脚本首先查询maintenanceWorkOrders父端点，获取核心案例ID（WorkOrderNumber）以及创建、发布和关闭时间戳等表头信息。
遍历子资源：对于检索到的每张工单，脚本都需继续调用子资源API（作业、物料交易和资源交易），以获取“作业开始”或“记录人工工时”等详细事件。
重建历史记录：如需捕获“工单暂停”等状态变更，脚本应查询工单历史子资源，或比较状态时间戳。
转换数据：API返回的数据为嵌套JSON格式，脚本需将其展开为标准化字典列表，每个字典对应一行事件数据。
映射属性：将API字段映射到ProcessMind所需的架构中（例如，将OrganizationCode映射到组织代码，将WorkOrderNumber映射到案例ID）。
构建DataFrame：将事件字典列表转换为pandas DataFrame，以便排序、去重和最终格式化。
导出：将最终DataFrame保存为CSV文件（例如oracle_maintenance_event_log.csv），并确保日期格式符合ISO 8601标准，以便导入。

配置

API基础URL：Oracle Fusion实例的主URL（例如https://pod.oraclecloud.com）。
身份验证：脚本通常使用基本身份验证（Base64编码），生产环境自动化则建议使用OAuth。
批次大小：Oracle REST API默认每次返回25条记录。使用limit参数（例如limit=499）可提高每次调用的吞吐量。
日期筛选：使用q参数按LastUpdateDate筛选，以便增量提取（例如LastUpdateDate > '2023-01-01'）。
并发控制：处理数千张工单时，请设置速率限制（等待时间），避免触发API 429 Too Many Requests错误。
子资源：确保用户有权限查看Work Order Costs和Resource Transactions等子对象。

查询示例 python

import requests
import pandas as pd
import base64
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># Configuration
BASE_URL = class="hl-string">"https://your-instance.fa.us2.oraclecloud.com"
USER = class="hl-string">"your_username"
PASSWORD = class="hl-string">"your_password"
OUTPUT_FILE = class="hl-string">"oracle_maintenance_log.csv"

class=class="hl-string">"hl-comment"># API Endpoints
WO_ENDPOINT = fclass="hl-string">"{BASE_URL}/fscmRestApi/resources/11.13.18.05/maintenanceWorkOrders"

class=class="hl-string">"hl-comment"># List to hold all extracted events
event_log = []

def get_headers():
    auth_str = fclass="hl-string">"{USER}:{PASSWORD}"
    b64_auth = base64.b64encode(auth_str.encode()).decode()
    return {
        class="hl-string">"Authorization": fclass="hl-string">"Basic {b64_auth}",
        class="hl-string">"Content-Type": class="hl-string">"application/vnd.oracle.adf.resourceitem+json"
    }

def fetch_all_pages(url, params=None):
    results = []
    while url:
        try:
            response = requests.get(url, headers=get_headers(), params=params)
            response.raise_for_status()
            data = response.json()
            if class="hl-string">'items' in data:
                results.extend(data[class="hl-string">'items'])
            
            class=class="hl-string">"hl-comment"># Check for next page
            url = None
            if class="hl-string">'links' in data:
                for link in data[class="hl-string">'links']:
                    if link[class="hl-string">'rel'] == class="hl-string">'next':
                        url = link[class="hl-string">'href']
                        params = None class=class="hl-string">"hl-comment"># Params are usually part of the next link
                        break
        except Exception as e:
            print(fclass="hl-string">"Error fetching data: {e}")
            break
    return results

class=class="hl-string">"hl-comment"># 1. Fetch Maintenance Work Orders (Header Level)
print(class="hl-string">"Fetching Work Orders...")
class=class="hl-string">"hl-comment"># Example filter: Last 90 days. Adjust date as needed.
params = {
    class="hl-string">"q": class="hl-string">"LastUpdateDate > '2023-10-01'",
    class="hl-string">"limit": 499,
    class="hl-string">"fields": class="hl-string">"WorkOrderNumber,WorkOrderId,WorkOrderDefinitionName,WorkOrderStatus,WorkOrderType,AssetNumber,OrganizationCode,CreationDate,ReleasedDate,ScheduledStartDate,ActualStartDate,ActualCompletionDate,ClosedDate,CanceledDate,WorkRequestId,WorkRequestDate"
}

work_orders = fetch_all_pages(WO_ENDPOINT, params)

for wo in work_orders:
    wo_num = wo.get(class="hl-string">'WorkOrderNumber')
    wo_id = wo.get(class="hl-string">'WorkOrderId')
    asset_num = wo.get(class="hl-string">'AssetNumber')
    org_code = wo.get(class="hl-string">'OrganizationCode')
    wo_type = wo.get(class="hl-string">'WorkOrderType')
    
    common_attributes = {
        class="hl-string">"WorkOrderNumber": wo_num,
        class="hl-string">"AssetNumber": asset_num,
        class="hl-string">"OrganizationCode": org_code,
        class="hl-string">"WorkOrderType": wo_type,
        class="hl-string">"WorkOrderStatus": wo.get(class="hl-string">'WorkOrderStatus'),
        class="hl-string">"SourceSystem": class="hl-string">"Oracle Maintenance Cloud",
        class="hl-string">"LastDataUpdate": datetime.now().isoformat()
    }

    class=class="hl-string">"hl-comment"># --- Header Level Activities ---

    class=class="hl-string">"hl-comment"># 1. Maintenance Request Created
    if wo.get(class="hl-string">'WorkRequestDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Request Created", class="hl-string">"EventDateTime": wo[class="hl-string">'WorkRequestDate']})
    elif wo.get(class="hl-string">'CreationDate'):
        class=class="hl-string">"hl-comment"># Fallback if no specific request date linked
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Request Created", class="hl-string">"EventDateTime": wo[class="hl-string">'CreationDate']})

    class=class="hl-string">"hl-comment"># 2. Maintenance Work Order Created
    if wo.get(class="hl-string">'CreationDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Work Order Created", class="hl-string">"EventDateTime": wo[class="hl-string">'CreationDate']})

    class=class="hl-string">"hl-comment"># 3. Work Order Released
    if wo.get(class="hl-string">'ReleasedDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Released", class="hl-string">"EventDateTime": wo[class="hl-string">'ReleasedDate']})

    class=class="hl-string">"hl-comment"># 4. Work Order Scheduled
    if wo.get(class="hl-string">'ScheduledStartDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Scheduled", class="hl-string">"EventDateTime": wo[class="hl-string">'ScheduledStartDate']})

    class=class="hl-string">"hl-comment"># 5. Work Order Completed (Header)
    if wo.get(class="hl-string">'ActualCompletionDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Completed", class="hl-string">"EventDateTime": wo[class="hl-string">'ActualCompletionDate']})

    class=class="hl-string">"hl-comment"># 6. Work Order Closed
    if wo.get(class="hl-string">'ClosedDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Closed", class="hl-string">"EventDateTime": wo[class="hl-string">'ClosedDate']})

    class=class="hl-string">"hl-comment"># 7. Work Order Canceled
    if wo.get(class="hl-string">'CanceledDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Canceled", class="hl-string">"EventDateTime": wo[class="hl-string">'CanceledDate']})

    class=class="hl-string">"hl-comment"># --- Child Resources Extraction ---
    
    class=class="hl-string">"hl-comment"># Operations (Started, Completed)
    ops_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderOperation"
    operations = fetch_all_pages(ops_url)
    for op in operations:
        if op.get(class="hl-string">'ActualStartDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Operation Started", class="hl-string">"EventDateTime": op[class="hl-string">'ActualStartDate'], class="hl-string">"AssignedTechnician": op.get(class="hl-string">'WorkCenterName')})
        if op.get(class="hl-string">'ActualCompletionDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Operation Completed", class="hl-string">"EventDateTime": op[class="hl-string">'ActualCompletionDate'], class="hl-string">"AssignedTechnician": op.get(class="hl-string">'WorkCenterName')})

    class=class="hl-string">"hl-comment"># Material Transactions (Issued, Allocated)
    class=class="hl-string">"hl-comment"># Note: Allocated is derived from class="hl-string">'RequiredDate' in WorkOrderComponent child, Issued is from WorkOrderMaterialTransaction
    
    class=class="hl-string">"hl-comment"># 8. Materials Allocated (Using Component Requirements)
    comp_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderComponent"
    components = fetch_all_pages(comp_url)
    for comp in components:
        if comp.get(class="hl-string">'RequiredDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Materials Allocated", class="hl-string">"EventDateTime": comp[class="hl-string">'RequiredDate']})

    class=class="hl-string">"hl-comment"># 9. Materials Issued (Using Transactions)
    mat_trans_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderMaterialTransaction"
    mat_trans = fetch_all_pages(mat_trans_url)
    for trans in mat_trans:
        if trans.get(class="hl-string">'TransactionDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Materials Issued", class="hl-string">"EventDateTime": trans[class="hl-string">'TransactionDate']})

    class=class="hl-string">"hl-comment"># 10. Labor Hours Recorded
    res_trans_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderResourceTransaction"
    res_trans = fetch_all_pages(res_trans_url)
    for res in res_trans:
        if res.get(class="hl-string">'TransactionDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Labor Hours Recorded", class="hl-string">"EventDateTime": res[class="hl-string">'TransactionDate'], class="hl-string">"AssignedTechnician": res.get(class="hl-string">'ResourceName')})

    class=class="hl-string">"hl-comment"># 11. Work Order On Hold (Check History)
    class=class="hl-string">"hl-comment"># Note: Accessing history child resource. If unavailable, requires separate Audit Trail query.
    hist_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderHistory"
    history = fetch_all_pages(hist_url)
    for h in history:
        if h.get(class="hl-string">'Status') == class="hl-string">'ON_HOLD' and h.get(class="hl-string">'StatusChangeDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order On Hold", class="hl-string">"EventDateTime": h[class="hl-string">'StatusChangeDate']})

    class=class="hl-string">"hl-comment"># 12. Quality Inspection Recorded
    qual_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderQualityCollection"
    quality = fetch_all_pages(qual_url)
    for q in quality:
        if q.get(class="hl-string">'CollectionDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Quality Inspection Recorded", class="hl-string">"EventDateTime": q[class="hl-string">'CollectionDate']})

    class=class="hl-string">"hl-comment"># 13. Maintenance Costs Transferred
    class=class="hl-string">"hl-comment"># Often found in WorkOrderCost child or inferred from specific Cost interactions
    cost_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderCost"
    costs = fetch_all_pages(cost_url)
    for c in costs:
        class=class="hl-string">"hl-comment"># Assuming transfer date exists or using creation of cost record
        date_field = c.get(class="hl-string">'TransferDate') or c.get(class="hl-string">'CreationDate')
        if date_field:
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Costs Transferred", class="hl-string">"EventDateTime": date_field})

class=class="hl-string">"hl-comment"># Convert to DataFrame and Export
df = pd.DataFrame(event_log)

class=class="hl-string">"hl-comment"># Ensure Date format
df[class="hl-string">'EventDateTime'] = pd.to_datetime(df[class="hl-string">'EventDateTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

class=class="hl-string">"hl-comment"># Final Column Selection
columns = [class="hl-string">"WorkOrderNumber", class="hl-string">"ActivityName", class="hl-string">"EventDateTime", class="hl-string">"SourceSystem", class="hl-string">"LastDataUpdate", 
           class="hl-string">"WorkOrderType", class="hl-string">"WorkOrderStatus", class="hl-string">"AssetNumber", class="hl-string">"OrganizationCode", class="hl-string">"AssignedTechnician"]

class=class="hl-string">"hl-comment"># Handle missing columns gracefully
for col in columns:
    if col not in df.columns:
        df[col] = None

df = df[columns]
df.to_csv(OUTPUT_FILE, index=False)
print(fclass="hl-string">"Extraction complete. Saved {len(df)} rows to {OUTPUT_FILE}")

准备好开始了吗？

立即将此模板应用于您的Oracle环境，将维护数据转化为可执行的洞察。开始流程挖掘时，我们的团队可为您提供支持。

返回改进指南

下载模板

优化资产维护，及时避免计划延误

立即缩短30%的周期时间，并合理调配资源

开始免费试用

无需信用卡。免费试用14天。

您的资产维护数据模板

您的资产维护数据模板

资产维护属性

资产维护活动

数据提取指南

Oracle BICC视图对象提取

步骤

配置

查询示例 sql

Oracle BI Publisher SQL数据模型

步骤

配置

查询示例 sql

通过Fusion REST API使用Python脚本

步骤

配置

查询示例 python

准备好开始了吗？

优化资产维护，及时避免计划延误