生产计划数据模板
Oracle Manufacturing Cloud
生产计划数据模板
此模板将指导您收集分析生产计划流程所需的数据，列出应收集的关键属性、应跟踪的主要活动，并说明如何从源系统提取相关信息。借助此资源，您可以简化数据准备工作，快速开始流程挖掘。
- 建议收集的属性
- 需要跟踪的关键活动
- 实用的数据提取指南
刚接触事件日志？了解 如何创建流程挖掘事件日志.
生产计划属性
以下是建议纳入事件日志的数据字段，可用于全面分析生产计划并深入了解运营情况。
5 必填 6 建议填写 9 选填
| 名称 | 说明 | ||
|---|---|---|---|
| 事件时间戳 EventTimestamp | 具体活动或事件发生的准确日期和时间。 | ||
| 说明 此属性记录流程中每项活动的时间戳，为了解事件顺序和衡量事件间隔提供必要的时间信息。 在流程分析中，时间戳对于计算周期时间、等待时间和整体流程时长至关重要。它支持绩效监控、瓶颈识别，以及分析流程时长如何影响计划达成情况等结果。 重要性 此时间戳是所有基于时间的流程挖掘分析的基础，可用于计算时长、发现瓶颈并构建时间顺序准确的流程图。 获取途径 此信息通常与事件或状态数据一同存储在Oracle Manufacturing Cloud的事务或日志数据表中，常见字段包括创建日期或最后更新日期。 示例 2023-10-26T08:00:00Z2023-10-26T14:30:15Z2023-11-05T10:11:45Z | |||
| 活动名称 ActivityName | 生产计划流程中发生的具体事件或任务名称。 | ||
| 说明 此属性描述生产订单生命周期中的具体步骤或里程碑，例如“创建工单”“发放物料”或“开始生产”。每项活动都代表流程中的一个独立节点。 流程挖掘利用此信息构建流程图，展示所有活动的顺序和发生频率。分析活动是了解流程如何推进、发现步骤间瓶颈以及衡量各阶段耗时的基础。 重要性 活动名称是流程挖掘的基本要素，它定义流程图中的步骤，并支持流程顺序的可视化与分析。 获取途径 此信息通常取自Oracle Manufacturing Cloud中的事件日志、事务状态或跟踪工单生命周期的特定事件数据表。 示例 创建工单下达工单发放物料开始生产完成工单 | |||
| 生产订单 ProductionOrderNumber | 生产订单的唯一标识符，也是生产计划流程的主要案例标识符。 | ||
| 说明 生产订单编号用于唯一标识生产特定数量产品的请求。它是核心案例标识符，可关联所有相关的计划、排程、执行和结案活动。 在流程挖掘中，按此编号对事件分组分析，可以完整查看生产生命周期的端到端情况。这有助于跟踪每个订单的进展、衡量交付周期、识别常见路径，并找出偏离标准流程的情况。 重要性 这是关键的案例标识符，可关联所有相关生产事件，从而全面分析每个订单的生产计划和执行流程。 获取途径 此标识符是Oracle Manufacturing Cloud的核心要素，通常位于工单表头和事务数据表中。请查阅WIP_ENTITIES或类似工单数据表。 示例 WO-2024-00123PROD-58974M450001852 | |||
| 数据上次更新时间 LastDataUpdateTimestamp | 此事件的数据最近一次从源系统刷新或提取的时间戳。 | ||
| 说明 此属性记录最近一次从源系统提取数据的日期和时间，是反映所分析数据集新鲜度的元数据字段。 了解此信息有助于判断分析结果的时效性。用户可以据此了解流程数据的更新时间，从而确保决策基于更新时间明确的数据。 重要性 此属性清楚说明数据的新鲜度，让用户了解分析所依据的数据有多新，以及预计何时进行下一次更新。 获取途径 此值通常在数据提取、转换和加载（ETL）过程中生成并写入数据集。 示例 2024-01-15T02:00:00Z2024-01-16T02:00:00Z2024-01-17T02:00:00Z | |||
| 源系统 SourceSystemIdentifier | 提取生产计划数据的系统。 | ||
| 说明 此属性用于标识数据来源的信息系统，例如Oracle Manufacturing Cloud。在汇总多个系统的数据以全面了解流程时，这一属性尤其有用。 分析时，可按数据来源筛选流程数据，比较不同系统中的流程，或筛选特定来源的数据进行针对性分析。 重要性 此属性提供重要的数据来源信息，对于多系统环境中的数据治理、验证和分析至关重要。 获取途径 这通常是在数据提取、转换和加载（ETL）过程中添加的静态值（“Oracle Manufacturing Cloud”）。 示例 Oracle Manufacturing CloudOracle Fusion Cloud SCMOMC_PROD_INSTANCE_1 | |||
| 产品代码 ProductCode | 所生产产品的唯一标识符。 | ||
| 说明 此属性用于指定生产订单对应的产品或物料，是细分和分析生产流程的重要维度。 分析人员可以按产品代码比较不同产品的生产流程，了解哪些产品的交付周期最长，或哪些产品系列存在特定流程偏差。此属性也是生产绩效吞吐量仪表板的重要数据。 重要性 按产品细分流程分析，可发现特定产品相关的瓶颈、延误或低效环节。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息通常存储在工单详情中，并通过WIP_DISCRETE_JOBS等数据表关联到物料主数据表。 示例 FG-1001-AAS-5432-BLUERAW-987-C | |||
| 工单状态 WorkOrderStatus | 事件发生时生产订单的当前状态。 | ||
| 说明 此属性反映生产订单的状态，例如“未下达”“已下达”“暂停”“已完成”或“已取消”，显示订单当前所处的生命周期阶段。 按工单状态分析流程，有助于了解不同状态下订单的分布情况，也有助于发现长时间停留在某一状态的订单，或筛选已完成、正在处理的订单进行分析。 重要性 此属性清楚呈现订单进度和结果，便于分析流程瓶颈、延误和完成率。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息可能位于工单表头数据表（如WIP_DISCRETE_JOBS）的状态列中。 示例 已下达已完成暂停已取消 | |||
| 生产工厂 ProductionPlant | 执行生产订单的制造设施或工厂。 | ||
| 说明 此属性表示负责生产的实际地点或组织单位，是比较不同厂区流程绩效的重要维度。 按生产工厂分析有助于开展基准对比，发现特定地点的最佳实践或系统性问题。资源利用瓶颈和生产绩效吞吐量等仪表板会使用此属性分析各厂区的绩效。 重要性 此属性支持比较不同制造地点的流程效率和绩效，帮助发现区域性瓶颈或最佳实践。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息通常属于工单定义的一部分，字段名称可能是组织或库存组织。 示例 A厂区-上海001号工厂-奥斯汀德国工厂 | |||
| 计划员 Planner | 负责管理生产订单的用户或计划员。 | ||
| 说明 此属性用于标识负责监督生产订单计划和执行的员工或角色，可以是姓名、ID或计划组。 按计划员分析流程，有助于了解工作量分布，并判断特定计划员或团队是否与流程延误或效率相关。延误根因与活动影响仪表板会使用此关键属性，发现与绩效相关的人为因素。 重要性 此属性有助于识别计划员或团队对流程绩效的影响，从而开展有针对性的培训并平衡工作量。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息可能存储在工单表头或相关分配数据表中。 示例 John SmithPlannerGroup_AUSER12345 | |||
| 计划开始日期 PlannedStartDate | 计划开始生产该订单的日期。 | ||
| 说明 此属性记录计划阶段为生产订单设定的目标开始日期，可作为衡量实际绩效的基准。 计算生产订单按时下达率这一KPI时，需要将此日期与实际下达时间戳进行比较。它有助于评估计划和排程流程的成效，并识别提前或延迟启动的情况。 重要性 这是衡量计划达成情况和生产按时启动绩效的重要基准。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息可能位于工单排程数据表或主要工单表头数据表中。 示例 2023-11-01T08:00:00Z2023-11-15T09:00:00Z2023-12-01T00:00:00Z | |||
| 计划结束日期 PlannedEndDate | 计划完成该订单生产的日期。 | ||
| 说明 此属性记录生产订单的目标完工日期，即生产团队力争达到的期限。 计算生产计划达成率这一KPI时，需要将此日期与实际完工时间戳进行比较。它有助于量化生产目标的达成情况，也是生产计划达成率仪表板中的主要指标。 重要性 这是衡量按时完工和整体生产计划达成情况的主要基准。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息可能位于工单排程数据表或主要工单表头数据表中，例如WIP_DISCRETE_JOBS。 示例 2023-11-10T17:00:00Z2023-11-20T17:00:00Z2023-12-10T17:00:00Z | |||
| 事件结束时间 EventEndTime | 某项活动或事件完成的准确日期和时间。 | ||
| 说明 此属性记录有持续时间的活动完成时间戳，与StartTime配合，确定活动的具体时间范围。 同时具备开始和结束时间时，可准确计算每项活动的处理时间。与根据下一事件的开始时间推算持续时间相比，这种方式更精确，因为它能区分等待时间。 重要性 可准确计算活动处理时间，并将其与活动之间的等待时间区分开来。 获取途径 事件日志或交易表中可能记录了操作的开始和结束时间，可从中获取此信息。 示例 2023-10-26T08:05:10Z2023-10-26T15:00:00Z2023-11-05T10:11:55Z | |||
| 完工数量 CompletedQuantity | 实际成功生产并完成的产品数量。 | ||
| 说明 此属性记录生产订单所有工序完成后的最终产量。由于报废、良品率损失或超产等因素，实际产量可能与计划数量不同。 此属性是“计划数量与实际数量偏差”这一KPI中的实际值，也是分析生产效率、良品率以及计划产量与实际产量差异原因的基础。 重要性 此属性衡量生产流程的实际产出，为分析良品率、效率和计划准确性提供重要依据。 获取途径 请查阅Oracle Manufacturing Cloud文档。相关信息通常存储在WIP_DISCRETE_JOBS等工单表中，字段名可能为'QUANTITY_COMPLETED'。 示例 9849501255 | |||
| 总周期时间 TotalCycleTime | 生产订单从创建到完成的总耗时。 | ||
| 说明 此属性是计算得出的指标，表示每个生产订单从首个事件（如“工单已创建”）到最后一个事件（如“工单已完成”）的总耗时。 这是衡量整体流程绩效的主要KPI，也是“端到端生产周期时间”仪表板的核心指标。它概览流程效率，并帮助跟踪一段时间内的改进情况。 重要性 这是衡量每个订单生产流程端到端效率的关键绩效指标。 获取途径 源系统不提供此属性。数据转换时，系统会按案例计算最大时间戳与最小时间戳之差。 示例 10天4小时3周2天15.5天 | |||
| 按时下达状态 ReleaseOnTimeStatus | 计算得出的状态，用于表示订单是否按时、提前或延迟下达。 | ||
| 说明 此属性通过比较“计划开始日期”与“工单已下达”活动的实际时间戳得出，专门反映流程初始下达步骤的达成情况。 此状态直接支持“生产订单按时下达率”KPI及相关仪表板，帮助定位生产执行周期开始阶段的延误。 重要性 帮助专门识别和分析工单下达到生产现场这一关键步骤中的延误。 获取途径 数据转换时，系统会将“PlannedStartDate”属性与下达事件的时间戳进行比较，计算得出此属性。 示例 按时延迟提前 | |||
| 物料可用情况 MaterialAvailabilityStatus | 表示生产订单所需物料是否可用。 | ||
| 说明 此属性显示物料可用状态，通常包括“可用”“短缺”或“部分可用”。系统会将库存水平与订单物料清单进行比对，以确定该状态。 这是“物料短缺影响分析”仪表板及相关KPI的关键属性。分析此属性有助于量化物料短缺对生产开始时间和整体周期时间的影响。 重要性 直接衡量物料短缺对生产计划的影响，而物料短缺是造成严重延误的常见原因。 获取途径 请查阅Oracle Manufacturing Cloud文档。此信息可能不是直接字段，可通过检查工单的组件可用情况得出。 示例 可用短缺部分可用 | |||
| 生产优先级 ProductionPriority | 表示生产订单紧急程度或优先级的代码或数值。 | ||
| 说明 此属性为生产订单分配优先级，例如“高”“中”或“低”。资源受限时，计划人员和生产团队可据此安排工作优先级。 在“延误根因与活动影响”仪表板中，可将优先级作为维度，查看高优先级订单是否处理得更快，或同样受到延误影响。这有助于评估优先级安排是否有效。 重要性 帮助评估优先级安排是否有效，以及高优先级订单是否比低优先级订单处理得更快。 获取途径 请查阅Oracle Manufacturing Cloud文档。此字段可能是工单标题中的标准字段或自定义字段。 示例 高中低19 | |||
| 生产线 ProductionLine | 工厂内分配给工单的具体装配线或生产线。 | ||
| 说明 此属性用于标识负责执行生产订单的具体设备或工作中心组，其粒度比生产工厂更细。 在“资源利用率瓶颈”仪表板中，此属性用于定位特定生产线或工作中心的延误，帮助识别产能限制，并在更细的层面优化资源分配。 重要性 支持在具体生产线或工作中心层面细致分析资源利用率并识别瓶颈。 获取途径 请查阅Oracle Manufacturing Cloud文档。相关信息通常位于工单路线或工序详情中。 示例 LINE_01_PACKAGINGASSEMBLY_CELL_3BCNC_MACHINE_5 | |||
| 计划数量 PlannedQuantity | 订单计划生产的产品数量。 | ||
| 说明 此属性表示生产订单的目标产量，在计划阶段根据需求和产能确定。 计算“计划数量与实际数量偏差”这一KPI时，需要将此值与实际产量进行比较。分析结果有助于评估计划准确性、良品率和报废量。 重要性 此属性提供评估产量准确性、良品率和报废量的基准数量，对于产能和物料计划至关重要。 获取途径 请查阅Oracle Manufacturing Cloud文档。WIP_DISCRETE_JOBS等工单数据表中通常包含此标准字段，字段名可能为“START_QUANTITY”或类似名称。 示例 10050001250 | |||
| 计划达成状态 ScheduleAdherenceStatus | 计算得出的状态，用于表示订单是否按时、提前或延迟完成。 | ||
| 说明 此属性通过比较“计划结束日期”与“工单已完成”活动的实际时间戳得出，清晰呈现计划执行结果。 此状态是“生产计划达成率”仪表板及KPI的基础。用户可据此快速筛选所有延迟订单，并调查延误根因，从而简化分析。 重要性 清晰直观地显示订单是否按时完成，便于识别和分析未能按期完成的订单。 获取途径 数据转换时，系统会将“PlannedEndDate”属性与完成事件的时间戳进行比较，计算得出此属性。 示例 按时延迟提前 | |||
必填 建议填写 选填
生产计划活动
以下是建议记录在事件日志中的关键流程步骤和里程碑，可用于准确发现流程并识别瓶颈。
5 建议填写 8 选填
| 活动 | 说明 | ||
|---|---|---|---|
| 下达工单 | 表示生产订单正式下达到车间，授权开始生产活动。此事件会将订单及其相关文档（如拣货单和工艺路线单）提供给生产人员。 | ||
| 重要性 这是区分计划阶段与执行阶段的关键里程碑。订单下达延迟是生产计划偏差的常见原因，因此必须重点监控。 获取途径 当用户或系统流程将工单状态更改为“已下达”时，系统会记录此明确事件。工单表头通常会记录具体的下达日期时间戳。 采集 取自工单记录中的“下达日期”时间戳。 事件类型 explicit | |||
| 关闭工单 | 这是生产完成后的最后一项行政步骤，包括结清所有成本、计算并过账偏差，从财务和物流角度正式关闭订单。 | ||
| 重要性 从“已完成”到“已关闭”所用的时间，可以揭示财务结算或成本核算流程中的低效环节，也为整个工单生命周期提供明确的终点。 获取途径 运行期末结算流程，或用户手动将工单状态更改为“已关闭”时，系统会记录此明确事件。工单通常会保存关闭日期时间戳。 采集 取自工单状态设为“已关闭”时的时间戳。 事件类型 explicit | |||
| 创建工单 | 此活动表示在Oracle Manufacturing Cloud中创建生产订单，也称为工单。这是生产指定数量产品的正式指令，通常根据主生产计划或销售订单生成。 | ||
| 重要性 作为生产流程的起点，此活动对于衡量整体交付周期，以及分析需求到生产信号的延迟至关重要。它有助于了解生产需求需要多久才能正式转化为行动。 获取途径 这是从工单创建事务中记录的明确事件，对应制造执行数据表中工单记录的创建时间戳，例如WIP_DISCRETE_JOBS或类似数据表。 采集 取自工单实体的创建日期。 事件类型 explicit | |||
| 完成工单 | 此活动表示生产订单的最终成品数量已生产完成并完成申报，标志着该订单的所有生产活动结束。 | ||
| 重要性 作为主要的成功终点，此活动对于计算端到端生产交付周期，以及衡量实际完工情况与计划完工日期的一致性至关重要，也是绩效分析的重要依据。 获取途径 这是由完工事务触发的明确事件，该事务会将成品转入库存。系统会记录工单完工日期，或将状态更改为“已完成”。 采集 取自最后一笔完工事务的时间戳，或工单状态变为“已完成”的日期。 事件类型 explicit | |||
| 开始生产 | 此活动表示生产订单的第一道制造工序开始，也就是车间开始对产品进行实际加工。 | ||
| 重要性 此里程碑对于衡量实际生产周期时间和计划启动日期的达成情况至关重要。它有助于区分生产前延误与制造执行期间的延误。 获取途径 这可以是车间事务中记录的明确事件，例如操作员登记开始第一道工序。也可以根据订单下达后首次人工事务或物料消耗的时间戳推断。 采集 取自工单工序记录的首笔“移动”或“完工”事务。 事件类型 explicit | |||
| 分析生产绩效 | 表示生产订单完成后的绩效分析，将实际结果与计划目标进行比较。分析内容可能包括成本、物料使用情况和计划达成情况。 | ||
| 重要性 此活动是了解持续改进循环成效的关键。衡量绩效分析所需时间，可以看出企业从生产偏差中学习并作出响应的速度。 获取途径 这是一个计算得出的事件，因为它属于业务流程，而非系统事务。可以根据偏差报告的生成日期或绩效备注字段的更新时间推断，但具体方式高度依赖当地流程。 采集 通过检测相关偏差报告的创建情况，或绩效管理系统中的状态更新来推导。 事件类型 calculated | |||
| 发放物料 | 此活动表示将组件物料从库存发放到指定生产订单的事务，说明所需原材料或半成品已拣出并移至生产线。 | ||
| 重要性 跟踪物料发放情况有助于分析仓储和物流运营效率，也能在生产开始前确认物料是否到位，比初始检查更准确地反映物料可用情况。 获取途径 这是库存模块中记录的明确物料事务，并与工单关联。每笔事务都包含时间戳和已发放组件的详细信息。 采集 取自与工单ID关联的物料事务记录，例如WIP组件发放记录。 事件类型 explicit | |||
| 取消工单 | 此活动表示取消此前已创建、且可能已下达的生产订单。这是一条异常路径，会停止该订单后续的所有工作。 | ||
| 重要性 跟踪取消情况对于了解流程异常至关重要。分析取消原因可以揭示需求预测、计划准确性或工程变更方面的问题。 获取途径 用户将工单状态更改为“已取消”时会触发此明确事件，系统会记录状态变更的时间戳。 采集 取自工单状态变为“已取消”时的时间戳。 事件类型 explicit | |||
| 完成工序 | 表示工单工艺路线中的一道工序或一个步骤已完成。一个生产订单通常会有多个“完成工序”事件，分别对应制造流程中的各个步骤。 | ||
| 重要性 分析连续完成工序之间的时间，可以细致了解生产流程，帮助识别瓶颈工序并计算各阶段的在制品处理时间。 获取途径 这是通过车间移动或完工事务记录的明确事件。操作员记录工序的完工数量后，系统会生成带时间戳的事务记录。 采集 取自工单具体工序的完工事务记录。 事件类型 explicit | |||
| 执行质量检验 | 表示在制造工序期间或完成后进行的质量检查，包括测量、目视检查或其他测试，以确保产品符合规格要求。 | ||
| 重要性 此活动有助于了解质量管理流程。分析检验的频率、时长和结果（通过或未通过），可以发现质量相关瓶颈或反复出现的问题。 获取途径 这是在Oracle Quality Management模块中记录的明确事件。检验结果记录在质量收集计划中，并与生产订单或具体工序关联。 采集 取自与工单关联的质量收集结果数据表记录。 事件类型 explicit | |||
| 批准工单 | 此活动表示计划员或经理已审核并批准生产订单。订单下达到车间前可能需要获得批准，尤其是高价值或复杂产品的订单。 | ||
| 重要性 分析从创建到批准所用的时间，有助于发现生产前行政流程中的瓶颈。审批周期过长会显著推迟生产启动。 获取途径 这通常是根据工单状态变化推断出的事件，例如状态从“未批准”变为“已批准”。Oracle通常使用状态字段管理工单等事务对象的生命周期。 采集 根据工单状态从未批准变为已批准或可下达状态推断。 事件类型 inferred | |||
| 检查物料可用性 | 表示系统已检查生产订单所需的所有组件是否可用。此步骤通常在创建订单后自动执行，也可由计划员手动触发。 | ||
| 重要性 跟踪此活动有助于分析物料短缺造成的延误，将计划问题与执行问题区分开来，并了解供应链绩效对生产启动时间的影响。 获取途径 这可能是根据工单状态或组件需求清单的状态变化推断出的事件。例如，状态从“等待组件”变为“组件可用”，或物料状态字段被填入，都可能表示此事件发生。 采集 根据工单关联的物料可用性状态字段变化推断。 事件类型 inferred | |||
| 调整生产计划 | 当已下达生产订单的关键参数发生变化时，例如所需数量或计划完工日期，此活动便会发生。这表示生产承诺的原计划出现了偏差。 | ||
| 重要性 频繁调整表明计划或执行流程不稳定。跟踪这些事件有助于量化计划变动程度并找出根本原因，例如需求波动或生产问题。 获取途径 这是可从工单记录的审计跟踪或变更日志中捕获的明确事件。Oracle Cloud通常会跟踪关键字段的变更，包括变更人和变更时间。 采集 取自工单实体的审计日志或变更历史数据表。 事件类型 explicit | |||
建议填写 选填
数据提取指南
如何从Oracle Manufacturing Cloud获取数据
步骤
- 打开Oracle BI Publisher：使用具有BI Author或Administrator权限的用户登录Oracle Fusion Cloud实例。在导航器菜单中依次选择Tools > Reports and Analytics，然后点击“Browse Catalog”打开BI Publisher目录。
- 创建数据模型：在BI Publisher目录中进入合适的文件夹，例如Shared Folders > Custom。点击“New”下拉菜单，选择“Data Model”。
- 定义数据集：在数据模型编辑器中，点击“Data Sets”旁的“+”图标，然后选择“SQL Query”。
- 配置数据集：在“New Data Set”对话框中输入描述性名称，例如“Production_Planning_Event_Log”。在“Data Source”中选择Oracle Fusion应用数据库，通常名为“ApplicationDB_FSCM”或类似名称。
- 输入SQL查询：复制本文档“query”部分提供的完整SQL查询，并粘贴到“SQL Query”文本框中。
- 定义查询参数：查询使用
:p_start_date和:p_end_date参数筛选日期范围。粘贴查询后，这些参数会显示在参数区域。将每个参数的“Data Type”设为“Date”，并填写易于识别的“Display Label”，例如“Start Date”。您也可以按需设置默认值。 - 查看并验证数据：在数据模型编辑器中点击“Data”选项卡。输入起止日期参数，然后点击“View”运行查询并查看提取的数据样本。确认列和行均符合预期。
- 保存数据模型：确认数据预览无误后，点击“Save”图标。为数据模型命名，例如“ProductionPlanningExtraction.xdm”，并保存到指定的自定义文件夹。
- 创建导出报表：要导出完整数据集，必须将此数据模型关联到报表。在已保存的数据模型中点击“Create Report”，按照向导创建简单的表格报表布局，无需复杂格式。
- 运行并导出报表：保存新建的报表。打开报表，使用所需日期参数运行。报表生成后，使用“Export”功能将结果保存为CSV文件。此CSV文件即为可上传的事件日志。
配置
- 数据源：查询必须在Oracle Fusion应用数据库上运行，该数据库通常名为
ApplicationDB_FSCM。请确保配置的用户对查询中引用的所有制造、库存和人员表具有读取权限。 - 日期范围参数：查询包含两个必填参数
:p_start_date和:p_end_date，用于筛选数据。建议每次提取3至6个月的数据，以保持良好性能并控制文件大小。 - 关键筛选条件：如需进一步缩小数据范围并提升性能，可在
base_data公用表表达式中添加WHERE子句。常见筛选条件包括：IODV.ORGANIZATION_CODE IN ('[Your Plant Code 1]', '[Your Plant Code 2]')，用于选择指定生产工厂。WDJ.WORK_ORDER_TYPE IN ('Standard', '[Your Custom Type]')，用于选择指定生产订单类型。
- 性能注意事项：此查询较为复杂，包含多个
UNION ALL分支。对于大型制造业务，查询可能占用较多资源。建议安排在业务低峰时段提取，以尽量减少对系统性能的影响。 - 前提条件：创建和运行数据模型的用户需要具备Oracle BI Publisher角色，例如
BI Author。底层数据源用户必须明确获得以下表的读取权限：WIP_DISCRETE_JOBS、WIP_OPERATIONS、INV_MATERIAL_TXNS、QA_RESULTS、INV_ORGANIZATION_DEFINITIONS_V、EGP_SYSTEM_ITEMS_B、PER_USERS和PER_PERSON_NAMES_F。
查询示例 sql
WITH base_data AS (
SELECT
WDJ.WORK_ORDER_NUMBER,
WDJ.STATUS_TYPE,
(SELECT STATUS_NAME FROM WIP_WORK_ORDER_STATUSES_VL WHERE STATUS_TYPE = WDJ.STATUS_TYPE) AS WORK_ORDER_STATUS_NAME,
ESI.ITEM_NUMBER AS PRODUCT_CODE,
IODV.ORGANIZATION_CODE AS PRODUCTION_PLANT,
PPNF.DISPLAY_NAME AS PLANNER,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.CREATION_DATE,
WDJ.DATE_RELEASED,
WDJ.ACTUAL_COMPLETION_DATE,
WDJ.DATE_CLOSED,
WDJ.LAST_UPDATE_DATE,
WDJ.WIP_ENTITY_ID
FROM
WIP_DISCRETE_JOBS WDJ
JOIN INV_ORGANIZATION_DEFINITIONS_V IODV ON WDJ.ORGANIZATION_ID = IODV.ORGANIZATION_ID
JOIN EGP_SYSTEM_ITEMS_B ESI ON WDJ.PRIMARY_ITEM_ID = ESI.INVENTORY_ITEM_ID AND WDJ.ORGANIZATION_ID = ESI.ORGANIZATION_ID
LEFT JOIN PER_USERS PU ON WDJ.CREATED_BY = PU.USERNAME
LEFT JOIN PER_PERSON_NAMES_F PPNF ON PU.PERSON_ID = PPNF.PERSON_ID AND PPNF.NAME_TYPE = 'GLOBAL' AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
WHERE
WDJ.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
)
-- 1. Work Order Created
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Created' AS "ActivityName",
BD.CREATION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
UNION ALL
-- 2. Material Availability Checked (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Material Availability Checked' AS "ActivityName",
BD.CREATION_DATE + interval '1' minute AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
UNION ALL
-- 3. Work Order Approved (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Approved' AS "ActivityName",
BD.DATE_RELEASED - interval '1' minute AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
UNION ALL
-- 4. Work Order Released
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Released' AS "ActivityName",
BD.DATE_RELEASED AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
UNION ALL
-- 5. Materials Issued
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Materials Issued' AS "ActivityName",
IMT.TRANSACTION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
INV_MATERIAL_TXNS IMT
JOIN base_data BD ON IMT.TRANSACTION_SOURCE_ID = BD.WIP_ENTITY_ID
WHERE
IMT.TRANSACTION_SOURCE_TYPE_ID = 5 -- WIP Transaction Source Type
AND IMT.TRANSACTION_ACTION_ID IN (1, 27, 33, 34) -- Issue, Backflush, Sub-assy Return
AND IMT.TRANSACTION_TYPE_ID IN (35, 43) -- WIP Issue, Assembly Completion
UNION ALL
-- 6. Production Started
SELECT
OPS.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Started' AS "ActivityName",
OPS.FIRST_OP_START_DATE AS "EventTimestamp",
OPS.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
OPS.PRODUCT_CODE AS "ProductCode",
OPS.PRODUCTION_PLANT AS "ProductionPlant",
OPS.PLANNER AS "Planner",
OPS.SCHEDULED_START_DATE AS "PlannedStartDate",
OPS.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM (
SELECT
BD.WORK_ORDER_NUMBER,
MIN(WO.ACTUAL_START_DATE) AS FIRST_OP_START_DATE,
BD.WORK_ORDER_STATUS_NAME,
BD.PRODUCT_CODE,
BD.PRODUCTION_PLANT,
BD.PLANNER,
BD.SCHEDULED_START_DATE,
BD.SCHEDULED_COMPLETION_DATE
FROM
WIP_OPERATIONS WO
JOIN base_data BD ON WO.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
WO.ACTUAL_START_DATE IS NOT NULL
GROUP BY
BD.WORK_ORDER_NUMBER, BD.WORK_ORDER_STATUS_NAME, BD.PRODUCT_CODE, BD.PRODUCTION_PLANT, BD.PLANNER, BD.SCHEDULED_START_DATE, BD.SCHEDULED_COMPLETION_DATE
) OPS
UNION ALL
-- 7. Operation Completed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Operation Completed' AS "ActivityName",
WO.ACTUAL_COMPLETION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
WIP_OPERATIONS WO
JOIN base_data BD ON WO.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
WO.ACTUAL_COMPLETION_DATE IS NOT NULL
UNION ALL
-- 8. Production Plan Adjusted
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Plan Adjusted' AS "ActivityName",
BD.LAST_UPDATE_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
AND BD.STATUS_TYPE NOT IN (7, 12) -- Not Canceled or Closed
AND BD.LAST_UPDATE_DATE > (BD.DATE_RELEASED + interval '1' hour)
AND BD.LAST_UPDATE_DATE < NVL(BD.ACTUAL_COMPLETION_DATE, SYSDATE)
UNION ALL
-- 9. Quality Inspection Performed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Quality Inspection Performed' AS "ActivityName",
QR.CREATION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
QA_RESULTS QR
JOIN base_data BD ON QR.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
QR.WIP_ENTITY_ID IS NOT NULL
UNION ALL
-- 10. Work Order Completed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Completed' AS "ActivityName",
BD.ACTUAL_COMPLETION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.ACTUAL_COMPLETION_DATE IS NOT NULL
UNION ALL
-- 11. Production Performance Analyzed (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Performance Analyzed' AS "ActivityName",
BD.DATE_CLOSED + interval '1' day AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_CLOSED IS NOT NULL
UNION ALL
-- 12. Work Order Closed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Closed' AS "ActivityName",
BD.DATE_CLOSED AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_CLOSED IS NOT NULL
UNION ALL
-- 13. Work Order Canceled
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Canceled' AS "ActivityName",
BD.LAST_UPDATE_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.STATUS_TYPE = 7步骤
- 打开Oracle Transactional Business Intelligence（OTBI）：登录Oracle Manufacturing Cloud实例。在导航器菜单中依次选择Tools和Reports and Analytics，然后点击“Browse Catalog”打开商业智能目录。
- 创建分析：在OTBI目录的工具栏中点击“New”，然后选择“Analysis”。系统会提示您选择主题区域。
- 选择主题区域：在可用主题区域列表中，选择“Manufacturing - Work Order Performance Real Time”。该主题区域包含工单及其状态的核心信息。
- 定义分析条件：分析编辑器包含“Criteria”和“Results”两个主要选项卡。在“Criteria”选项卡中，您将设置事件日志的逻辑。
- 添加首个活动的列：在左侧“Subject Areas”窗格中，将所需列拖放到“Selected Columns”区域。先设置“Work Order Created”活动。例如，添加“Work Order Details”.“Work Order Number”、活动名称公式、“Work Order Details”.“Creation Date”作为时间戳，以及其他所需属性。
- 创建活动名称自定义公式：点击“Selected Columns”标题旁的“Add a new calculated measure”图标。在公式框中输入活动名称，例如
'Work Order Created'。将列命名为“ActivityName”等。 - 使用UNION ALL合并所有活动：要创建包含所有所需活动的单一事件日志，必须合并多个查询。在“Criteria”选项卡中，找到“Selected Columns”区域右下方的“Combine results based on a set operation”图标（通常显示为两个重叠的圆）。选择“Union All”，并为下一个活动（例如“Work Order Released”）添加新的条件块。
- 为所有活动重复上述步骤：对全部13个所需活动重复步骤5至7。每个活动都需要新建一个条件块，并通过“Union All”合并。请为每个活动选择正确的日期字段作为时间戳，例如“Release Date”“Actual Completion Date”或“Closed Date”。部分活动还需要按“Work Order Status Name”筛选。
- 应用全局筛选条件：设置完所有UNION块后，进入“Filters”区域添加筛选条件，以限制数据范围。务必按“Work Order Creation Date”或“Last Update Date”设置日期范围，例如最近6个月。还应按“Organization Name”筛选，选择正确的生产工厂。
- 检查并设置结果格式：切换到“Results”选项卡预览数据。确认所有列均已包含且名称正确。如有需要，可拖放列以调整顺序。
- 导出事件日志：分析完成且结果无误后，点击页面底部的“Export”链接，并选择“CSV File”格式。
- 准备上传：打开下载的CSV文件，确认列标题符合所需属性，例如
ProductionOrderNumber、ActivityName、EventTimestamp等。确保时间戳格式一致。完成后即可将文件上传到流程挖掘工具。
配置
- 主题区域：“Manufacturing - Work Order Performance Real Time”是此次提取使用的主要主题区域。如需详细的物料或质量数据，您可能需要分别使用“Manufacturing - Material Usage Real Time”或“Quality Management - Inspection Results Real Time”创建分析，再合并结果。
- 日期范围筛选：设置日期范围筛选条件对于控制数据量至关重要。可按“Work Order Details”.“Creation Date”或“Work Order Details”.“Last Update Date”筛选，提取最近3至6个月的数据。
- 组织筛选：请始终按“Organization Details”.“Organization Name”或“Organization Code”筛选，将数据限定在您要分析的生产工厂或业务单元。
- 工单状态筛选：为减少无关数据，您可以从整个分析中排除某些工单状态，例如草稿或模拟订单。可通过“Work Order Status”.“Work Order Status Name”设置全局筛选条件。
- 前提条件：运行分析的用户必须具备相应角色和权限，通常需要“Manufacturing Engineer”或自定义BI角色，才能访问“Manufacturing - Work Order Performance Real Time”主题区域。
查询示例 sql
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Created' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Created By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Material Availability Checked' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" = 'Unreleased' AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Approved' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" - INTERVAL '1' MINUTE AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Released' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Materials Issued' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Started' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Operation Completed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Plan Adjusted' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" > "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" AND "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" IN ('Released', 'On Hold') AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Quality Inspection Performed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Completed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Performance Analyzed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Closed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Canceled' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" = 'Canceled' AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'立即优化Oracle生产计划
几天内将周期时间缩短30%，提升效率。
开始免费试用
无需信用卡，几分钟即可完成设置。