> 持续流程改进 > 资产维护 > 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	维护工单的唯一标识符。
说明工单编号是维护流程的唯一Case标识符，用于跟踪维护任务从初始请求或计划生成，到执行、完成和财务关闭的完整生命周期。在分析中，该属性是连接事件数据与案例级属性的核心键。分析人员可以据此汇总单个作业的所有活动，并计算端到端周期时间。为什么重要它是流程实例的主键，对于重建案例历史至关重要。获取位置 WIE_WORK_ORDERS_B表，列WORK_ORDER_NUMBER 示例 WO-2023-0015WO-2023-0089WO-2023-1102
	最近数据更新时间 LastDataUpdate	最近一次数据提取的时间戳。
说明数据最近一次在流程挖掘工具中提取或刷新的日期和时间，帮助用户了解分析数据的时效性。这是数据治理所需的系统元数据字段。为什么重要确保用户了解数据的新鲜度。获取位置提取时的系统时间。示例 2023-10-30T12:00:00Z
	源系统 SourceSystem	数据产生所在系统的名称。
说明标识提取记录所来自的源软件或数据库。在此场景中，通常为“Oracle Maintenance Cloud”。对于多系统环境中的数据血缘追踪至关重要。为什么重要跟踪多系统环境中的数据血缘。获取位置在提取过程中硬编码。示例 Oracle Maintenance CloudOracle Cloud ERP
	优先级 Priority	分配给工单的紧急程度。
说明表示工单的重要程度，例如Critical、High、Medium或Low，并影响排程和资源分配。对于Work Order Conversion Efficiency仪表板十分重要，可确保关键故障得到正确优先处理。为什么重要确定目标响应时间和资源分配。获取位置 WIE_WORK_ORDERS_B表，列WORK_ORDER_PRIORITY_ID。示例关键高标准
	实际完成日期 ActualCompletionDate	维护工作完成的日期和时间。
说明记录实体工作最终完成的时间戳，用于结束生命周期中的执行阶段。它是Technical Execution Cycle Time的终点，也是Administrative Lag Time的起点。为什么重要标志着技术执行阶段结束。获取位置 WIE_WORK_ORDERS_B表，COMPLETED_DATE或CLOSED_DATE列。示例 2023-10-252023-10-262023-10-27
	实际开始日期 ActualStartDate	实际开始工作的日期和时间。
说明记录技术人员实际开始工作的时间，通常由'Operation Started'交易触发。用于计算Material Fulfillment Delay的终点和Resource Scheduling Fluidity。为什么重要对于计算实际执行时长与计划时长的差异至关重要。获取位置 WIE_WORK_ORDERS_B表，列ACTUAL_START_DATE。示例 2023-10-212023-10-222023-10-23
	工单状态 WorkOrderStatus	工单当前所处的生命周期状态。
说明表示工单状态，例如Unreleased、Released、On Hold、Completed或Closed，帮助筛选仍在处理中的案例和已完成案例。用于仪表板展示当前在制工作（WIP），并识别卡在特定状态的工单。为什么重要提供工单进度的当前快照。获取位置 WIE_WORK_ORDERS_B表，列WORK_ORDER_STATUS_ID。示例已发布暂挂已关闭
	指定技术人员 AssignedTechnician	负责执行任务的人员或资源组。
说明记录负责工作的技术人员用户ID或员工ID，可在工单抬头或操作层级分配。用于Technician Utilization和Labor分析，以识别技能缺口或资源过度使用。为什么重要支持资源绩效分析和产能规划。获取位置 WIE_OPERATION_RESOURCES表或类似分配表。示例 J. SmithTech-005外部承包商
	组织代码 OrganizationCode	维护组织或工厂的代码。
说明代表负责维护工作的具体业务部门、工厂或设施，支持按区域或设施进行基准比较。用于比较不同站点或运营单元之间的绩效差异。为什么重要按实际地点或业务部门进行分段分析。获取位置 WIE_WORK_ORDERS_B表，列ORGANIZATION_ID。示例 M1NY-PLANTLON-DEPOT
	维护类型 WorkOrderType	将工单分类为预防性、纠正性或预测性维护。
说明该属性定义维护工作的性质。常见值包括Preventive、Corrective、Emergency或Condition-Based。它是Proactive Maintenance Ratio KPI的关键属性，可帮助分析人员比较计划内工作量与针对故障的非计划响应量。为什么重要区分计划内维护与被动维修。获取位置 WIE_WORK_ORDERS_B表，列WORK_ORDER_TYPE_ID（关联类型定义）。示例预防性维护纠正性维护紧急维护
	资产编号 AssetNumber	所维护设备的唯一标识符。
说明识别维护工单对应的具体实体资产或机器，并将流程关联到资产层级。这是Asset Rework Frequency KPI的关键属性，可帮助分析人员发现反复发生故障的机器。为什么重要将维护活动关联到具体实体设备。获取位置 WIE_WORK_ORDERS_B表，列ASSET_ID（关联CSI_ITEM_INSTANCES）。示例 PUMP-101HVAC-Unit-5CONVEYOR-BELT-A
	SLA目标日期 SlaTargetDate	工单必须完成的截止日期。
说明根据优先级和创建日期计算，代表面向业务方作出的资产恢复承诺。用于SLA达成率KPI，以确定维护服务是否履行服务义务。为什么重要定义及时性的成功标准。获取位置通常为自定义字段，或根据Priority配置派生。示例 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	标识该工单是否为重复维修的标志。
说明如果在此前30天内，同一资产ID对应的另一工单已关闭，则该布尔标志为true。这是一个计算属性。该属性直接支持资产返工频率KPI，并有助于识别维修质量不佳或存在故障的资产。为什么重要识别质量问题和重复故障。获取位置在数据转换层计算。示例 truefalse
	计划开始日期 ScheduledStartDate	计划开始维护工作的日期。
说明计划开始工作的日期和时间。与Actual Start Date进行对比，可以发现计划执行偏差。用于Scheduling and Slippage Analysis，识别维护计划中的中断。为什么重要衡量计划执行情况和进度偏差的基准。获取位置 WIE_WORK_ORDERS_B表，列PLANNED_START_DATE。示例 2023-10-202023-10-212023-10-22
	请求日期 RequestDate	收到初始维护请求的日期。
说明记录首次识别并登记维护需求的时间戳，是周期时间计算的起点。对于Work Order Conversion Lead Time KPI至关重要，可衡量从发现问题到创建工单之间的延迟。为什么重要标志着端到端流程生命周期的开始。获取位置 WIE_WORK_ORDERS_B表，根据源请求关联派生。示例 2023-10-012023-10-052023-11-12
	资产类别 AssetCategory	将资产按HVAC、Fleet或Production Line等类型分组。
说明将资产归入更广泛的类别，以便按不同设备类型开展维护绩效分析。用于Maintenance Lead Time Overview仪表板，识别造成延迟最多的设备类别。为什么重要支持按设备类型汇总分析维护绩效。获取位置根据分配给Asset ID的Item Category派生。示例重型机械车队车辆设施
	预计人工工时 EstimatedLaborHours	工单计划人工工时。
说明计划人员预计完成工作所需的时间，在计划或发布阶段设定。与实际人工工时进行比较，以确定差异并改进未来的计划标准。为什么重要评估执行效率的基准。获取位置 WIE_OPERATION_RESOURCES表中的计划数量。示例 5.010.01.0

必需建议可选

资产维护活动

这些是可视化维护工单端到端流程所需的关键生命周期阶段和运营更新。

8 建议 7 可选

	活动	说明
	人工工时已记录	记录技术人员在工单上投入的时间。这是一项交易事件，相关成本会计入维护活动。
为什么重要跟踪工作量和成本累积。此类事件多次出现，表示工作仍在持续，可用于计算人工工时总偏差。获取位置根据WIE_RESOURCE_TRANSACTIONS表派生。采集执行交易X时记录事件类型 explicit
	工单已关闭	工单完成最终行政关闭。之后不能再计入成本，工单也会完成会计结算。
为什么重要标志着行政生命周期结束。Completed与Closed之间的时间代表行政处理延迟。获取位置根据WIE_WO_STATUS_HISTORY派生，条件是STATUS_CODE变更为'CLOSED'。采集比较状态字段变更前后的值事件类型 explicit
	工单已发布	工单状态从Draft或Unreleased变更为Released。此操作授权针对该工单消耗物料和资源。
为什么重要表示计划阶段结束，并授权开始执行。此处的延迟表明存在计划瓶颈。获取位置根据WIE_WO_STATUS_HISTORY派生，条件是STATUS_CODE变更为'Released'。采集比较状态字段变更前后的值事件类型 inferred
	工单已完成	整个工单的技术工作已完成，表示实体资产已修复并可以投入运行，但财务处理可能仍在继续。
为什么重要从运营角度看，这是维护干预的实际结束点，用于计算SLA达成情况。获取位置根据WIE_WO_STATUS_HISTORY派生，条件是STATUS_CODE变更为'COMPLETED'。采集比较状态字段变更前后的值事件类型 explicit
	操作已开始	工单内某项具体操作，例如Op 10或Op 20，开始执行的时刻。这是技术工作正式开始的准确时间点。
为什么重要代表'Work Commenced'里程碑，是计算实际作业时间与计划时长差异的基础。获取位置根据WIE_WO_OPERATIONS_B中的状态变更，或该操作首笔资源交易的时间戳派生。采集通过比较字段X与Y派生事件类型 inferred
	物料已发放	从仓库实际扣减并发放给指定工单的库存，代表备件向维护地点的实物流转。
为什么重要这是工作即将开始的直接信号。将其与'Released'事件进行对比，可以发现仓库拣料流程中的延迟。获取位置根据INV_MATERIAL_TXNS表派生，其中TRANSACTION_SOURCE_TYPE_ID关联Work Order。采集执行交易X时记录事件类型 explicit
	维护工单已创建	系统生成Work Order实体，可以由请求转换而来，也可以手动创建。该事件为流程挖掘建立Case 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变更为'CANCELED'。采集比较状态字段变更前后的值事件类型 explicit
	工单已排程	为工单操作分配具体开始日期或具体资源，例如技术人员和工具，并更新系统中的计划日期。
为什么重要用于衡量调度或排程团队的效率。发布与排程之间的间隔表明存在资源争用。获取位置根据WIE_OPERATION_RESOURCES或WIE_WO_OPERATIONS_B中的排程字段更新派生。采集比较状态字段变更前后的值事件类型 inferred
	工单已暂停	工单状态变更为'On Hold'，执行随即停止。通常由备件缺失、无法进入现场或安全问题导致。
为什么重要这是流程低效的重要来源。识别暂停的频率和持续时间，是缩短整体周期时间的关键。获取位置根据WIE_WO_STATUS_HISTORY派生，条件是STATUS_CODE变更为'ON_HOLD'。采集比较状态字段变更前后的值事件类型 explicit
	操作已完成	完成routing中的某个具体步骤，即Operation，表示部分技术工作已经结束。
为什么重要提供细粒度的进度可见性。'Maintenance Task Executed'通常根据最后一个操作完成派生。获取位置根据WIE_WO_OPERATIONS_B中的状态变更，或显示完成状态的交易历史派生。采集比较状态字段变更前后的值事件类型 inferred
	物料已分配	为工单预留或分配所需备件和组件，确保工作开始前库存可用。
为什么重要这是分析供应链准备情况的关键步骤。若该步骤延迟，通常说明存在库存短缺或采购计划响应缓慢。获取位置根据WIE_WO_COMPONENTS_B表中的CREATION_DATE或ALLOCATION_DATE派生。采集执行交易X时记录事件类型 explicit
	维护成本已转移	将累计成本从维护系统转移到Cost Management模块，作为财务结算环节。
为什么重要确认维护活动的财务影响已被确认，是分析'Maintenance Planning Accuracy'的基础。获取位置根据与Work Order交易来源关联的Cost Accounting分配表派生。采集执行交易X时记录事件类型 inferred
	质量检查已记录	录入与维护工单相关的质量结果，用于验证维修是否符合安全和运营标准。
为什么重要确保合规。完工前缺少质量检查，可能表明存在安全风险或流程偏差。获取位置根据QA_RESULTS或其他与Work Order ID关联的Quality Collection Plan表派生。采集执行交易X时记录事件类型 explicit

建议可选

提取指南

如何从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，确保Case ID映射到WorkOrderNumber，Timestamp映射到EventDateTime。

配置

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

a 示例查询 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。
在Catalog中点击右上角的Create按钮，然后选择Data Model。
在Data Model编辑器中找到左侧的Data Sets面板，点击加号（+）图标，然后选择SQL Query。
为数据集命名，例如MaintenanceEventLog，选择适当的Data Source，通常为ApplicationDB_SCM或类似数据源，并将Type保留为Standard SQL。
将下方提供的完整SQL查询粘贴到SQL Query文本区域。
检查具体表名，例如QA_RESULTS或成本表，因为这些名称可能因模块实施方式而异。如环境需要使用不同的架构引用，请调整架构前缀，例如FUSION.WIE_...。
如需将日期范围参数化，请进入Parameters选项卡，将WHERE子句中的硬编码时间戳替换为:p_start_date等绑定变量。
点击View Data按钮，选择样本量，然后点击View，验证输出包含各种活动类型的行。
验证完成后，将Data Model保存到Shared Folder，例如Custom/Maintenance。
如需提取完整日志，请使用此Data Model创建Report，取消选择所有布局选项以生成原始数据导出，并将报告调度为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界面进行核对，确保没有大范围数据被排除。

a 示例查询 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实例的Base URL，例如https://fa-instance.oraclecloud.com，以及具体的REST端点：maintenanceWorkOrders、maintenanceWorkOrders/child/WorkOrderOperation和maintenanceWorkOrders/child/WorkOrderMaterialTransaction。
身份验证配置：使用Basic Authentication凭据（Username和Password）配置脚本；如果安全策略要求，请实现OAuth 2.0令牌处理。
定义范围：定义提取时间范围，例如LastUpdateDate > 2023-01-01，以限制数据量并确保性能。
提取表头：脚本首先查询父级maintenanceWorkOrders端点，获取核心Case ID（WorkOrderNumber）以及创建、发布和关闭的表头级时间戳。
遍历子资源：对于获取的每个工单，脚本必须继续调用子资源（Operations、Material Transactions、Resource Transactions），以捕获Operation Started或Labor Hours Recorded等细粒度事件。
重建历史：为捕获Work Order On Hold等状态变更，脚本会查询Work Order History子资源或比较状态时间戳。
转换数据：由于返回的数据采用嵌套JSON格式，脚本会将其展平为规范化字典列表，每个字典代表一行事件。
映射属性：将具体API字段映射到ProcessMind所需的架构，例如将OrganizationCode映射为Organization Code，将WorkOrderNumber映射为Case ID。
构建DataFrame：将事件字典列表转换为pandas DataFrame，以完成排序、去重和最终格式化。
导出：将最终DataFrame保存为CSV文件，例如oracle_maintenance_event_log.csv，并确保日期格式符合ISO 8601，便于导入。

配置

API Base URL：Oracle Fusion实例的主URL，例如https://pod.oraclecloud.com。
身份验证：Basic Auth（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等子对象。

a 示例查询 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视图对象提取

步骤

配置

a 示例查询 sql

Oracle BI Publisher SQL数据模型

步骤

配置

a 示例查询 sql

通过Fusion REST API运行Python脚本

步骤

配置

a 示例查询 python

准备好开始了吗？

优化资产维护，立即避免计划延误

您的资产维护数据模板

您的资产维护数据模板

资产维护属性

资产维护活动

提取指南

Oracle BICC视图对象提取

步骤

配置

a 示例查询 sql

Oracle BI Publisher SQL数据模型

步骤

配置

a 示例查询 sql

通过Fusion REST API运行Python脚本

步骤

配置

a 示例查询 python

准备好开始了吗？

优化资产维护，立即避免计划延误

我们重视您的隐私