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

您的资产维修数据模板

Oracle Maintenance Cloud

您的资产维修数据模板

此模板提供了一个结构化框架，协助您整理 Oracle Maintenance Cloud 中的维护记录，以便进行高级 Process Mining。它识别了获得设备保养和资源分配全方位可见性所需的关键数据点和流程里程碑。遵循这些指南，您可以构建强大的 event log，从而揭示效率低下的环节并优化维护策略。

全面分析的推荐属性
需监控的关键流程里程碑
Oracle 系统专用提取指南

不熟悉事件日志？了解如何创建流程挖掘事件日志.

下载模板

资产维护属性

这些推荐的数据字段允许您对维护事件进行分类和过滤，从而深入了解设备性能和资源利用率。

5 必填 8 推荐 9 可选

	名称	描述
	事件timestamp EventDateTime	活动发生的日期和时间。
描述此属性记录事件发生的精确时刻。它用于在 case 内部按时间顺序对活动进行排序，并计算步骤间的时长。准确的 timestamp 对于计算周期时间、识别延迟以及衡量 SLA 达成绩效至关重要。为何重要对于建立流程的时间轴至关重要。获取方式事务表中的 CREATION_DATE 或 LAST_UPDATE_DATE 列。示例 2023-10-15T08:30:00Z2023-10-15T09:15:00Z2023-10-16T14:00:00Z
	活动 ActivityName	工作流中发生的 event 或状态变更名称。
描述此属性记录了维护流程中执行的具体步骤，如“工单已创建”、“工序已启动”或“工单已关闭”。它代表了流程图中的节点。分析师利用此属性来可视化流程流转、识别变体，并检测特定流程步骤之间的瓶颈。为何重要用于定义流程流转和事件序列。获取方式派生自事务历史表或状态变更日志。示例维护工单已创建工单已下达操作完成
	维护工单 WorkOrderNumber	维护工单的唯一标识符。
描述工单编号作为维护流程中唯一的 case 标识符。它追踪维护任务从初始申请或排程生成，到执行、完成及财务关闭的全生命周期。在分析中，此属性是关联 event 数据与 case 级属性的核心键。它允许分析师将与单个作业相关的所有活动分组，并计算端到端周期时间。为何重要它是流程实例的主键，对于重构案例历史至关重要。获取方式 WIE_WORK_ORDERS_B 表，WORK_ORDER_NUMBER 列示例 WO-2023-0015WO-2023-0089WO-2023-1102
	最后数据更新 LastDataUpdate	最近一次数据提取的 timestamp。
描述在 Process Mining 工具中最后一次提取或刷新数据的日期和时间。它能帮助用户了解分析的时效性。这是数据治理所需的系统元数据字段。为何重要确保用户了解数据的时效性。获取方式提取时的系统时间。示例 2023-10-30T12:00:00Z
	源系统 SourceSystem	数据来源系统的名称。
描述指示提取记录的源软件或数据库。在此背景下，通常为“Oracle Maintenance Cloud”。在多系统环境中对于追踪数据谱系至关重要。为何重要在多系统环境中追踪数据血缘。获取方式在提取期间硬编码。示例 Oracle Maintenance CloudOracle Cloud ERP
	优先级 Priority	分配给工单的紧急程度等级。
描述指示工单的重要性，如：紧急、高、中或低。这直接驱动调度和资源分配。对于“工单转换效率”仪表板至关重要，可确保关键故障得到优先处理。为何重要决定目标响应时间和资源分配。获取方式 WIE_WORK_ORDERS_B 表，WORK_ORDER_PRIORITY_ID 列。示例严重高标准
	实际完成日期 ActualCompletionDate	维护工作完成的日期和时间。
描述记录实际工作的最终完成。此 timestamp 用于关闭生命周期的执行阶段。它是“技术执行周期时间”的终点，也是“行政滞后时间”的起点。为何重要标志着技术执行阶段的结束。获取方式 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。这可以在磁头级或工序级指派。用于“技术员利用率”和“人工分析”，以识别技能差距或资源过度利用的情况。为何重要支持资源绩效分析和产能规划。获取方式 WIE_OPERATION_RESOURCES 表或类似的分配表。示例 J. Smith技术人员-005外部承包商
	机构代码 OrganizationCode	维护组织或工厂的代码。
描述代表负责维护工作的特定业务单元、工厂或设施。它支持基于地区或设施的基准分析。用于映射不同站点或运营单元之间的绩效差异。为何重要按物理位置或业务单元进行细分分析。获取方式 WIE_WORK_ORDERS_B 表，ORGANIZATION_ID 列。示例 M1纽约工厂LON-仓库
	维护类型 WorkOrderType	将工单分类为预防性、纠正性或预测性维护。
描述此属性定义了维护工作的性质。常见值包括：预防性、纠正性、紧急或基于状态的维护。它对于“主动维护比例 KPI”至关重要，使分析师能够对比计划工作量与非计划故障响应量。为何重要区分计划性维护与事后维修。获取方式 WIE_WORK_ORDERS_B 表，WORK_ORDER_TYPE_ID 列（关联至类型定义）。示例预防性纠正性紧急
	资产编号 AssetNumber	被维护设备的唯一标识符。
描述识别维护工单指向的具体物理资产或机器。它将流程连接到资产层级。这对于“资产返工频率”KPI 至关重要，使分析师能够发现反复故障的机器。为何重要将维护活动链接到特定的物理设备。获取方式 WIE_WORK_ORDERS_B 表，ASSET_ID 列（关联至 CSI_ITEM_INSTANCES）。示例泵-101HVAC-单元-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	指示该工单是否为重复维修的标志。
描述一个布尔标志，如果在之前的 30 天窗口内为同一个资产 ID 关闭了另一个工单，则计算结果为 true。这是一个计算属性。它直接支持“资产返工频率”KPI，有助于识别维修质量差或发生故障的资产。为何重要识别质量问题和重复发生的故障。获取方式在数据转换层计算。示例 truefalse
	申请日期 RequestDate	收到初始维护请求的日期。
描述指示维护需求首次被识别并记录的 timestamp。这构成了提前期计算的起点。对于“工单转化提前期 KPI”至关重要，用于衡量识别问题到创建订单之间的延迟。为何重要标志着端到端流程生命周期的开始。获取方式 WIE_WORK_ORDERS_B 表，源自原始请求链接。示例 2023-10-012023-10-052023-11-12
	计划开始日期 ScheduledStartDate	维护工作的计划开始日期。
描述工作计划开始的日期和时间。通过与“实际开始日期”对比可揭示排程达成率。用于“排程与滑坡分析”，以检测维护计划中的干扰因素。为何重要用于衡量进度符合度和偏差的基准。获取方式 WIE_WORK_ORDERS_B 表，PLANNED_START_DATE 列。示例 2023-10-202023-10-212023-10-22
	资产类别 AssetCategory	将资产分为暖通空调（HVAC）、车队或生产线等类型。
描述将资产分为更大的类别。这有助于从宏观层面分析不同类型设备的维护绩效。用于“维护提前期概览”仪表板，以识别哪些设备类别导致的延迟最多。为何重要支持按设备类型对维护绩效进行聚合分析。获取方式派生自指派给资产 ID 的项目类别。示例重型机械车队车辆设施
	预计人工工时 EstimatedLaborHours	工单的计划人工工时。
描述计划员预估的工作时长。这在计划或下达阶段设定。通过与“实际人工工时”对比来确定偏差，并改进未来的规划标准。为何重要评估执行效率的基准。获取方式 WIE_OPERATION_RESOURCES 表，计划数量。示例 5.010.01.0

必填推荐可选

资产维护活动

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

8 推荐 7 可选

	活动	描述
	人工工时已记录	记录技术人员在工单上花费的时间。这是一个事务性的 event，用于将成本计入维护活动。
为何重要追踪投入和成本累积。此类别的多个 event 表明工作正在进行，用于计算总人工差异。获取方式派生自 WIE_RESOURCE_TRANSACTIONS 表。捕获在执行事务码 X 时记录事件类型 explicit
	工单已下达	工单状态从“草稿”或“未下达”变更为“已下达”。此操作授权针对该订单消耗物料和资源。
为何重要指示规划阶段的结束和执行阶段的授权开始。此处的延迟表明存在规划瓶颈。获取方式派生自 WIE_WO_STATUS_HISTORY，其中 STATUS_CODE 变更为 'RELEASED'。捕获比较状态字段的前后变化事件类型 inferred
	工单已关闭	工单的最终行政关闭。此后不再产生费用，订单已定稿进入财务核算。
为何重要标志着行政生命周期的结束。“已完成”与“已关闭”之间的时间代表行政滞后时间。获取方式派生自 WIE_WO_STATUS_HISTORY，其中 STATUS_CODE 变更为 'CLOSED'。捕获比较状态字段的前后变化事件类型 explicit
	工单已完成	整个工单的技术完工。这标志着物理资产已修复并可投入使用，尽管财务处理可能仍在继续。
为何重要从运营角度看，维护干预的有效结束点。用于计算 SLA 达成情况。获取方式派生自 WIE_WO_STATUS_HISTORY，其中 STATUS_CODE 变更为 'COMPLETED'。捕获比较状态字段的前后变化事件类型 explicit
	工序已启动	工单中特定工序（如 Op 10, Op 20）的开始。这是任务技术工作启动的精确时刻。
为何重要代表“工作已开始”里程碑。对于计算实际维修工时与计划时长的对比至关重要。获取方式派生自 WIE_WO_OPERATIONS_B 状态变更或该工序的第一个资源事务时间戳。捕获通过比较字段 X 和 Y 派生事件类型 inferred
	物料已发放	针对特定工单从仓库实际扣除的库存。这代表备件向维护现场的物理移动。
为何重要工作准备开始的实质信号。将其与“已释放”事件进行比较，可以发现仓库拣货环节的延迟。获取方式派生自 INV_MATERIAL_TXNS 表，其中 TRANSACTION_SOURCE_TYPE_ID 链接到工单。捕获在执行事务码 X 时记录事件类型 explicit
	维护工单已创建	系统生成工单实体（无论是从申请转化还是手动创建）。此 event 为 Process Mining 建立 Case ID，并为规划和执行设定基准。
为何重要流程实例的主要锚点 event。它将受理阶段与规划执行阶段区分开来。获取方式派生自 WIE_WORK_ORDERS_B 表中的 CREATION_DATE 时间戳。捕获在创建工单磁头记录时记录事件类型 explicit
	维护请求已创建	通过 self-service 门户或服务台界面初次提交的维护请求。此 event 记录了在正式生成工单前的故障或服务需求初次迹象，通常存在于 Work Requests 表中。
为何重要标志着维护需求周期的真实起点。这对于计算从故障识别到生成可执行工单之间的提前期至关重要。获取方式派生自 MNT_WORK_REQUESTS 表中的 CREATION_DATE 字段。捕获在维护请求表中插入记录时记录事件类型 explicit
	工单已取消	工单在成功完成前终止。这可能由于工作重复、不再需要或已被合并而发生。
为何重要识别高取消率可以揭示请求受理流程或重复检测逻辑中的问题。获取方式派生自 WIE_WO_STATUS_HISTORY，其中 STATUS_CODE 变更为 'CANCELED'。捕获比较状态字段的前后变化事件类型 explicit
	工单已排程	为工单工序分配特定的开始日期或特定资源（技术人员/工具）。这会更新系统中的计划排程日期。
为何重要衡量派单或排程团队的效率。“已下达”与“已排程”之间的间隙表明存在资源竞争。获取方式派生自 WIE_OPERATION_RESOURCES 或 WIE_WO_OPERATIONS_B 调度列的更新。捕获比较状态字段的前后变化事件类型 inferred
	工单挂起	工单状态变更为“挂起”，停止执行。这通常由于备件缺失、无法进入现场或安全问题引起。
为何重要流程效率低下的主要原因。识别挂起的频率和持续时间是缩短总提前期的关键。获取方式派生自 WIE_WO_STATUS_HISTORY，其中 STATUS_CODE 变更为 'ON_HOLD'。捕获比较状态字段的前后变化事件类型 explicit
	操作完成	工艺路线中特定步骤（工序）的完成。标志着特定部分的技术工作已结束。
为何重要提供详尽的进度可见性。“维护任务已执行”通常源于最后一项工序的完成。获取方式派生自 WIE_WO_OPERATIONS_B 状态变更或显示完成的事务历史记录。捕获比较状态字段的前后变化事件类型 inferred
	物料已分配	为工单预留或分配所需的备件及组件。这确保了在开工前库存充足。
为何重要对于分析供应链就绪情况至关重要。如果此步骤延迟，则表明存在库存短缺或采购规划缓慢。获取方式根据 CREATION_DATE 或 ALLOCATION_DATE 派生自 WIE_WO_COMPONENTS_B 表。捕获在执行事务码 X 时记录事件类型 explicit
	维护成本已转移	将维护系统中产生的累计成本转移到成本管理模块。这充当了财务结算的角色。
为何重要确认维护的财务影响已被确认。这对于“维护规划准确性”分析至关重要。获取方式派生自链接到工单事务源的成本核算分配表。捕获在执行事务码 X 时记录事件类型 inferred
	质量检验已记录	录入与维护工单相关的质量结果。这验证了维修是否符合安全和运营标准。
为何重要确保合规性。完成前遗漏质量检查可能预示着安全风险或流程偏离。获取方式派生自链接到工单 ID 的 QA_RESULTS 或类似的质量收集计划表。捕获在执行事务码 X 时记录事件类型 explicit

推荐可选

提取指南

如何从 Oracle 维护云获取数据

步骤

登录 Oracle Cloud 控制台，导航至业务智能云连接器 (BICC) 控制台。
创建新的“产品服务配置（Offering Configuration）”或编辑现有配置。找到“Maintenance Management（维护管理）”或“Supply Chain Management（供应链管理）”产品服务。
选择需要提取的公共视图对象 (PVO)。必须选择：'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderExtractPVO'（磁头信息）、'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderStatusHistoryExtractPVO'（状态变更）、'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkRequestExtractPVO'（请求）、'FscmTopModelAM.ScmExtractAM.InvBiccExtractAM.MaterialTransactionExtractPVO'（物料事务）以及 'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderResourceTransactionExtractPVO'（人工事务）。
配置提取存储。将 BICC 作业指向有效的 Oracle 通用内容管理 (UCM) 文件夹或外部对象存储桶（例如 OCI Object Storage）。
设置调度程序运行这些提取任务。首次加载请选择“全量提取 (Full Extract)”。后续运行请根据 'LastUpdateDate' 配置“增量提取 (Incremental Extract)”，以减少数据量。
从存储位置下载生成的 CSV 文件，并将其加载到您的暂存数据库或数据湖中（如 Snowflake、BigQuery 或 SQL Server）。为清晰起见，请确保暂存环境中的表名与 PVO 名称一致。
对暂存表执行下方“查询”部分提供的 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 查询示例 config

/* 
  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 管理员或数据模型开发权限的用户登录 Oracle Fusion Cloud 环境。
导航至“工具” > “报告和分析”，然后点击“浏览目录”以打开 Oracle BI Publisher 界面（通常为 /xmlpserver）。
在目录中，点击右上角的“创建”按钮并选择“数据模型”。
在数据模型编辑器中，找到左侧的“数据集”面板，点击加号 (+) 图标，然后选择“SQL 查询”。
为数据集命名（如 MaintenanceEventLog），选择合适的数据源（通常为 ApplicationDB_SCM 或类似名称），并将类型保持为“标准 SQL”。
将下方提供的完整 SQL 查询粘贴到 SQL 查询文本区域中。
检查特定的表名（如 QA_RESULTS 或成本表），因为这些可能因具体的模块实现而异；如果您的环境需要特定的模式引用（如 FUSION.WIE_...），请调整模式前缀。
如果您希望对日期范围进行参数化，请导航至“参数”选项卡，将 WHERE 子句中的硬编码时间戳替换为绑定变量，如 :p_start_date。
点击“查看数据”按钮，选择样本大小，然后点击“查看”以验证输出是否包含各种活动类型的行。
验证无误后，将数据模型保存到共享文件夹（如 Custom/Maintenance）。
若要提取完整日志，请使用此数据模型“创建报告”，取消选择所有布局选项以生成原始数据导出，并调度报告以 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 子句。
验证：务必对比“管理维护工单”界面的行数进行验证，确保没有排除主要的数据子集。

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'

步骤

前提条件：确保您配置了一个具有“维护经理（Maintenance Manager）”角色且已启用 REST API 访问权限的 Oracle Cloud 用户。
环境配置：安装 Python（3.8 或更高版本）以及所需的 pandas 和 requests 库，命令为 pip install 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 端点。这提供了核心的 Case ID (WorkOrderNumber) 以及创建、释放和关闭的磁头级时间戳。
子资源迭代：对于检索到的每个工单，脚本必须随后调用子资源 API（工序、物料事务、资源事务），以捕获诸如“工序已开始”或“人工工时已记录”等细粒度事件。
历史重构：若要捕获“工单挂起”等状态变更，脚本需查询工单历史子资源或比较状态时间戳。
数据转换：由于数据以嵌套 JSON 格式返回，脚本会将这些结构扁平化为标准化的字典列表，其中每个字典代表一个事件行。
属性映射：将特定的 API 字段映射到所需的 ProcessMind 模式（例如，将 OrganizationCode 映射到 Organization Code，将 WorkOrderNumber 映射到 Case ID）。
DataFrame 构建：将事件字典列表转换为 pandas DataFrame，以便进行排序、去重和最终格式化。
导出：将最终的 DataFrame 保存为 CSV 文件（如 oracle_maintenance_event_log.csv），确保日期格式符合 ISO 8601 标准以便摄取。

配置

API 基础 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'）。
并发：在处理数千个工单时，请实施频率限制（sleep 时间），以避免触发 API 429 过多请求错误。
子资源：确保用户具有查看工单成本（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 BICC 视图对象提取

步骤

配置

a 查询示例 config

Oracle BI Publisher SQL 数据模型

步骤

配置

a 查询示例 sql

通过 Fusion REST API 的 Python 脚本

步骤

配置

a 查询示例 python

准备好开始了吗？

立即优化资产维护，杜绝进度滞后

您的资产维修数据模板

您的资产维修数据模板

资产维护属性

资产维护活动

提取指南

Oracle BICC 视图对象提取

步骤

配置

a 查询示例 config

Oracle BI Publisher SQL 数据模型

步骤

配置

a 查询示例 sql

通过 Fusion REST API 的 Python 脚本

步骤

配置

a 查询示例 python

我们重视您的隐私

准备好开始了吗？

立即优化资产维护，杜绝进度滞后