您的生产计划数据模板
Oracle 制造云
您的生产计划数据模板
本模板为收集用于分析生产计划流程的正确数据提供了全面指南。它概述了要收集的基本属性、要跟踪的关键活动，以及从源系统中提取此信息的实用指南。利用此资源简化您的数据准备工作，并开启您的 Process Mining 旅程。
- 建议收集的属性
- 需要追踪的关键活动
- 数据抽取实用指南
不熟悉事件日志？了解 如何创建流程挖掘事件日志.
生产计划属性
这些是建议包含在事件日志中的数据字段，用于全面的生产计划分析，从而实现对运营的详细洞察。
5 必填 6 推荐 10 可选
| 名称 | 描述 | ||
|---|---|---|---|
| 事件timestamp EventTimestamp | 特定活动或事件发生的精确日期和时间。 | ||
| 描述 此属性记录流程中每个活动的时间戳。它提供了理解事件顺序和测量事件间持续时间所需的按时间顺序排列的上下文。 在流程分析中，此时间戳对于计算周期时间、等待时间及整体流程持续时间至关重要。它能够实现绩效监控、瓶颈检测，并分析流程时效如何影响计划达成率等结果。 为何重要 此时间戳对于所有基于时间的 Process Mining 分析至关重要，包括计算持续时间、发现瓶颈以及构建按时间顺序排列的准确流程图。 获取方式 此信息通常与 Oracle Manufacturing Cloud 内事务或日志表中的事件或状态数据并列，通常作为创建日期或最后更新日期。 示例 2023-10-26T08:00:00Z2023-10-26T14:30:15Z2023-11-05T10:11:45Z | |||
| 活动名称 ActivityName | 生产计划流程中发生的特定事件或任务的名称。 | ||
| 描述 此属性描述生产订单生命周期中的特定步骤或里程碑，例如“工单已创建”、“物料已发放”或“生产已开始”。每个活动代表流程中的一个不同点。 Process Mining 利用此信息构建流程图，显示所有活动的顺序和频率。分析活动是了解流程流向、识别步骤间瓶颈以及测量不同阶段耗时的基础。 为何重要 活动名称是 Process Mining 的核心要求，它定义了流程图中的步骤，并允许对流程进行可视化和分析。 获取方式 这通常源自 Oracle Manufacturing Cloud 内跟踪工单生命周期的事件日志、事务状态或特定事件表。 示例 工单已创建工单已下达物料已发放生产已开始工单已完工 | |||
| 生产订单 ProductionOrderNumber | 生产订单的唯一标识符，作为生产计划流程的主要 Case 标识符。 | ||
| 描述 生产订单号唯一地标识了生产特定数量产品的请求。它作为核心 Case 标识符，串联了所有相关的计划、排程、执行和结案活动。 在 Process Mining 中，通过按此编号分组分析事件，可以获得生产生命周期的完整端到端视图。这有助于跟踪每笔订单的历程、测量交付周期、识别常见路径并精准定位偏离标准流程的情况。 为何重要 这是必不可少的 Case 标识符，它连接了所有相关的生产事件，从而可以对每个订单的整个生产计划和执行流程进行全面分析。 获取方式 此标识符是 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。在将多个系统的数据合并以获得整体流程视图的环境中，它特别有用。 在分析中，它允许根据来源过滤流程数据。这有助于跨不同系统比较流程，或将数据隔离到特定来源以进行针对性分析。 为何重要 它提供了关于 data 来源的关键背景信息，这对于多系统环境下的 data 治理、验证和分析非常重要。 获取方式 这通常是在数据提取、转换和加载 (ETL) 过程中添加的静态值（“Oracle Manufacturing Cloud”）。 示例 Oracle 制造云Oracle Fusion Cloud SCMOMC_PROD_INSTANCE_1 | |||
| Planner Planner | 负责管理生产订单的用户或计划员。 | ||
| 描述 此属性标识负责监督生产订单计划和执行的员工或角色。它可以是人员姓名、ID 或计划组。 按计划员分析流程有助于了解工作负载分布，并识别特定计划员或团队是否与流程延迟或效率相关。它是“延迟根源与活动影响”仪表板的关键属性，用于发现与绩效相关的人为因素。 为何重要 有助于识别单个计划员或团队对流程绩效的影响，从而实现有针对性的培训和工作量平衡。 获取方式 请咨询 Oracle 制造云文档。这可能存储在工单标头或相关的分配表中。 示例 约翰·史密斯PlannerGroup_AUSER12345 | |||
| 产品代码 ProductCode | 正在制造的产品的唯一标识符。 | ||
| 描述 此属性指定作为生产订单主体的物品或物料。它是细分和分析生产流程的关键维度。 利用产品代码，分析人员可以比较不同物品的生产流程。这有助于回答诸如“哪些产品的交付周期最长？”或“是否存在特定于某些产品族的流程偏差？”等问题。它对于“生产绩效吞吐量”仪表板至关重要。 为何重要 支持按产品对流程分析进行细分，揭示特定产品的瓶颈、延迟或低效环节。 获取方式 请咨询 Oracle 制造云文档。通常存储在工单详情中，从 WIP_DISCRETE_JOBS 等表链接到物料主表。 示例 FG-1001-AAS-5432-BLUERAW-987-C | |||
| 工单状态 WorkOrderStatus | 事件发生时生产订单的当前状态。 | ||
| 描述 此属性反映生产订单的状态，例如“未下达”、“已下达”、“暂缓”、“已完成”或“已取消”。状态提供了订单在其生命周期中所处位置的快照。 按工单状态分析流程有助于了解订单在不同状态下的分布。这对于识别在特定状态下停留过久的订单，或将分析过滤为仅关注已完成或进行中的订单也很有价值。 为何重要 它提供了订单进度和结果的清晰视图，支持分析流程瓶颈、延迟和完成率。 获取方式 请咨询 Oracle 制造云文档。通常可以在工单标头表（如 WIP_DISCRETE_JOBS）中找到，与状态列相关联。 示例 已下发已完成暂停已取消 | |||
| 生产工厂 ProductionPlant | 执行生产订单的制造设施或工厂。 | ||
| 描述 此属性指示负责生产的物理位置或组织单元。它是跨不同站点比较流程绩效的关键维度。 按生产工厂分析可以进行基准测试，并识别特定位置的最佳实践或系统性问题。它用于“资源利用瓶颈”和“生产绩效吞吐量”等仪表板，以了解特定站点的绩效。 为何重要 支持对比不同制造地点的流程效率和绩效，突出区域性瓶颈或最佳实践。 获取方式 请咨询 Oracle 制造云文档。此类信息通常是工单定义的一部分，常被称为组织或库存组织。 示例 站点 A - 上海001 号工厂 - 奥斯汀德国工厂 | |||
| 计划开始日期 PlannedStartDate | 订单计划开始生产的日期。 | ||
| 描述 此属性包含在计划阶段定义的生产订单目标开始日期，作为衡量实际绩效的基准。 该日期对于“生产订单准时下达率”KPI 至关重要，在此 KPI 中，它将与实际下达时间戳进行对比。它有助于评估计划和排程流程的有效性，并识别提前或延迟启动的情况。 为何重要 这是衡量生产启动的计划达成率和准时绩效的关键基准。 获取方式 请咨询 Oracle 制造云文档。可能位于工单调度表或主工单标头表中。 示例 2023-11-01T08:00:00Z2023-11-15T09:00:00Z2023-12-01T00:00:00Z | |||
| 计划结束日期 PlannedEndDate | 订单计划完成生产的日期。 | ||
| 描述 此属性包含生产订单的目标完成日期，即生产团队力争达成的截止日期。 该日期对于计算“生产计划达成率”KPI 至关重要，该 KPI 将此计划日期与实际完成时间戳进行对比。它有助于量化生产达成目标的程度，是“生产计划达成”仪表板上的主要指标。 为何重要 作为衡量准时完成率和整体生产计划达成率的主要基准。 获取方式 请咨询 Oracle 制造云文档。可能位于工单调度表或主工单标头表（如 WIP_DISCRETE_JOBS）中。 示例 2023-11-10T17:00:00Z2023-11-20T17:00:00Z2023-12-10T17:00:00Z | |||
| 下达准时状态 ReleaseOnTimeStatus | 一种计算出的状态，用于指示订单是准时、提前还是延迟下达。 | ||
| 描述 此属性通过比较“计划开始日期”与“工单已下达”活动的实际时间戳得出。它专门关注流程初始下达步骤的达成情况。 此状态直接支持“生产订单准时下达率”KPI 及相关仪表板。它有助于隔离发生在生产执行周期最开始阶段的延迟。 为何重要 有助于专门识别和分析将工单下达到生产车间这一关键步骤中的延迟。 获取方式 此属性在数据转换期间通过将“PlannedStartDate”属性与下达事件的时间戳进行对比计算得出。 示例 准时逾期提前 | |||
| 事件结束时间 EventEndTime | 特定活动或事件完成的精确日期和时间。 | ||
| 描述 此属性记录具有持续时间的活动的完成时间戳。它通过定义活动的特定时间窗口来补充开始时间 (StartTime)。 当开始和结束时间都可用时，可以准确计算每个活动的实际处理时间。这比根据下一个事件的开始时间推断持续时间更精确，因为它考虑了等待时间。 为何重要 支持精确计算活动处理时间，并将其与活动之间的等待时间区分开来。 获取方式 此信息可能存在于记录操作开始和结束的事件日志或事务表中。 示例 2023-10-26T08:05:10Z2023-10-26T15:00:00Z2023-11-05T10:11:55Z | |||
| 完成数量 CompletedQuantity | 成功生产并完成的产品实际数量。 | ||
| 描述 此属性捕获所有操作完成后生产订单的最终产出数量。由于报废、良率损失或超产等因素，这可能与计划数量有所不同。 这是“计划与实际数量偏差”KPI 中使用的“实际”值。它是分析生产效率、良率分析以及了解计划与实际产出差异原因的基础。 为何重要 这衡量生产流程的实际产出，从而能够对良率、效率和计划准确性进行关键分析。 获取方式 请咨询 Oracle 制造云文档。通常存储在工单表（如 WIP_DISCRETE_JOBS）中，字段名为“QUANTITY_COMPLETED”。 示例 9849501255 | |||
| 总周期时间 TotalCycleTime | 生产订单从创建到完成的总时长。 | ||
| 描述 此属性是一个计算指标，代表每个生产订单从第一个事件（例如“工单已创建”）到最后一个事件（例如“工单已完成”）所经历的总时间。 这是衡量整体流程绩效的主要 KPI，也是“端到端生产交付周期”仪表板的核心。它提供了效率的宏观视图，并有助于跟踪随时间推移的改进情况。 为何重要 这是一个衡量每个订单生产流程端到端效率的关键绩效指标。 获取方式 源系统中不提供此属性。它是在数据转换期间通过为每个 Case 用最大时间戳减去最小时间戳计算得出的。 示例 10天4小时3 周 2 天15.5 天 | |||
| 活动时长 ActivityDuration | 完成单个活动所需的时间，也称为处理时间。 | ||
| 描述 此指标代表一个活动的执行时间，计算方式为结束时间戳与开始时间戳之差。如果结束时间不可用，可以进行估算，但两者兼具能提供最准确的衡量。 分析活动时长是识别流程中哪些具体步骤最耗时的关键。这有助于将改进工作集中在对整体周期时间影响最大的活动上。 为何重要 精准识别耗时最长的流程步骤，助力实现针对性的优化。 获取方式 这在数据转换期间计算得出。为了保证准确，它需要每个活动的开始 (EventTimestamp) 和结束 (EventEndTime) 时间戳。 示例 5分钟8 小时1.5 天 | |||
| 物料可用性 MaterialAvailabilityStatus | 指示生产订单所需的物料是否可用。 | ||
| 描述 此属性显示物料可用性状态，通常包含“可用”、“短缺”或“部分可用”等值。此状态通过检查库存水平与订单物料清单 (BOM) 对比得出。 它是“物料短缺影响分析”仪表板及相关 KPI 的关键属性。分析这有助于量化物料短缺对生产开始时间和整体交付周期的影响。 为何重要 直接衡量物料短缺对生产计划的影响，这是导致重大延迟的常见原因。 获取方式 请咨询 Oracle 制造云文档。这可能不是一个直接字段，但可以通过检查工单的组件可用性来推导。 示例 可用短缺部分可用 | |||
| 生产优先级 ProductionPriority | 指示生产订单紧急程度或优先级的代码或数值。 | ||
| 描述 此属性为生产订单分配优先级（如“高”、“中”、“低”）。这有助于排程员和生产团队在资源受限时确定工作优先级。 在“延迟根源与活动影响”仪表板中，优先级可用作一个维度，以查看高优先级订单是否确实处理得更快，或者是否同样遭受延迟。这有助于评估优先级系统的有效性。 为何重要 有助于分析优先级系统是否有效，以及高优先级订单的处理速度是否快于低优先级订单。 获取方式 请咨询 Oracle 制造云文档。这可能是工单标头上的标准或自定义字段。 示例 高中低19 | |||
| 生产线 ProductionLine | 工厂内分配给该工单的特定装配线或生产线。 | ||
| 描述 此属性标识负责执行生产订单的特定设备或工作中心组。它提供了比生产工厂更细粒度的细节。 在“资源利用瓶颈”仪表板中，此属性用于定位与特定生产线或工作中心相关的延迟。它有助于识别产能限制并在细节层面优化资源分配。 为何重要 支持在特定生产线或工作中心级别进行细粒度的资源利用率分析和瓶颈检测。 获取方式 请咨询 Oracle 制造云文档。此类信息通常位于工单工艺路线或工序详情中。 示例 LINE_01_PACKAGINGASSEMBLY_CELL_3BCNC_MACHINE_5 | |||
| 计划数量 PlannedQuantity | 订单中计划生产的产品数量。 | ||
| 描述 此属性代表给定生产订单的目标生产量。它是在计划阶段根据需求和产能设定的。 该值是“计划与实际数量偏差”KPI 的关键输入，在此 KPI 中，它将与实际生产数量进行对比。分析这有助于评估计划准确性、良率和报废水平。 为何重要 为评估生产产出准确性、良率和报废情况提供基准数量，这对于产能和物料计划至关重要。 获取方式 请咨询 Oracle 制造云文档。这是工单表（如 WIP_DISCRETE_JOBS）中的标准字段，通常命名为“START_QUANTITY”或类似名称。 示例 10050001250 | |||
| 计划达成状态 ScheduleAdherenceStatus | 一种计算出的状态，用于指示订单是准时、提前还是延迟完成。 | ||
| 描述 此属性通过比较“计划结束日期”与“工单已完成”活动的实际时间戳得出。它为排程绩效提供了一个清晰的分类结果。 此状态是“生产计划达成”仪表板和 KPI 的基础。它允许用户快速过滤所有延迟订单并调查延迟的根源，从而简化分析。 为何重要 提供清晰直观的准时绩效指标，便于识别和分析未能按期完成的订单。 获取方式 此属性在数据转换期间通过将“PlannedEndDate”属性与完成事件的时间戳进行对比计算得出。 示例 准时逾期提前 | |||
必填 推荐 可选
生产计划活动
这些是在事件日志中需要捕获的关键流程步骤和里程碑，用于准确的流程发现和瓶颈识别。
5 推荐 8 可选
| 活动 | 描述 | ||
|---|---|---|---|
| 工单已下达 | 标志着生产订单正式下达到车间，授权开始制造活动。该事件使订单及其文档（如领料单和工艺路线单）可供生产人员使用。 | ||
| 为何重要 这是一个将计划阶段与执行阶段分开的关键里程碑。订单下达延迟是导致生产排程偏差的常见原因，必须对其进行监控。 获取方式 这是当用户或系统流程将工单状态更改为“已下达”时捕获的显式事件。通常会在工单标题上记录一个具体的下达日期时间戳。 捕获 从工单记录上的“Released Date” timestamp 中获取。 事件类型 explicit | |||
| 工单已关闭 | 这是生产完成后的最后一个行政步骤，在此步骤中将确定所有成本，并计算和过账差异。这从财务和物流角度正式结案了订单。 | ||
| 为何重要 “已完成”与“已结案”之间的时间差可以揭示财务结算或成本核算流程中的低效环节。它为整个工单生命周期提供了一个明确的终点。 获取方式 这是当运行期末结案流程或用户手动将工单状态更改为“已结案”时捕获的显式事件。工单上通常会存储结案日期时间戳。 捕获 从工单状态设置为“已关闭”时的 timestamp 中获取。 事件类型 explicit | |||
| 工单已创建 | 此活动标志着在 Oracle Manufacturing Cloud 中创建生产订单（也称为工单）。这是制造特定数量物品的正式指令，通常根据主生产计划或销售订单生成。 | ||
| 为何重要 作为生产流程的起点，此活动对于衡量整体前置时间和分析需求到生产信号的延迟至关重要。它有助于识别从产生生产需求到正式采取行动所需的时间。 获取方式 这是从工单创建事务中捕获的显式事件。它对应于制造执行表（如 WIP_DISCRETE_JOBS 或类似表）中工单记录的创建时间戳。 捕获 从工单实体的创建日期中获取。 事件类型 explicit | |||
| 工单已完工 | 此活动表示已针对生产订单完成最终数量成品制造并进行申报。此事件标志着该订单所有生产活动的结束。 | ||
| 为何重要 作为主要的成功终点，此活动对于计算端到端生产前置时间和衡量针对计划完成日期的计划达成率至关重要。它是绩效分析的基石。 获取方式 这是由将成品移入库存的完成事务触发的显式事件。系统会记录工单完成日期或状态更改为“已完成”。 捕获 从最终完成事务的 timestamp 或工单状态变更为“已完成”的日期中获取。 事件类型 explicit | |||
| 生产已开始 | 此活动标志着生产订单第一次制造操作的开始。这是在车间对产品开始实际作业的时间点。 | ||
| 为何重要 此里程碑对于衡量实际生产周期时间和计划开始日期的达成情况至关重要。它有助于区分生产前延迟与制造执行期间的延迟。 获取方式 这可以是来自车间事务的显式事件，例如操作员签入第一道工序。它也可以根据订单下达后的第一次人工事务或物料消耗的时间戳进行推断。 捕获 从针对工单工序记录的第一个“移动”或“完成”事务中获取。 事件类型 explicit | |||
| 工单已取消 | 此活动代表取消之前已创建且可能已下达的生产订单。这是一个异常路径，会停止该订单的所有后续工作。 | ||
| 为何重要 跟踪取消情况对于理解流程异常至关重要。分析取消原因可以突出显示需求预测、计划准确性或工程变更方面的问题。 获取方式 这是由用户更改工单状态为“已取消”的操作触发的显式事件。系统会记录此次状态更改的时间戳。 捕获 从工单状态变更为“已取消”的 timestamp 中获取。 事件类型 explicit | |||
| 工单已批准 | 此活动表示生产订单已由计划员或经理审查并批准。在订单下达至车间之前可能需要审批，尤其是对于高价值或复杂产品。 | ||
| 为何重要 分析从创建到审批之间的时间，有助于识别生产前行政流程中的瓶颈。漫长的审批周期会严重推迟生产的启动。 获取方式 这通常是基于工单状态更改的推断事件，例如，从“未批准”变为“已批准”。Oracle 通常使用状态字段来管理工单等事务对象的生命周期。 捕获 通过工单状态从“未批准”变更为“已批准”或“可下达”状态来推导。 事件类型 inferred | |||
| 已执行质量检验 | 代表在制造操作过程中或之后执行的质量控制检查。这可能包括测量、目视检查或其他测试，以确保产品符合规格。 | ||
| 为何重要 此活动提供了对质量管理流程的洞察。分析检查的频率和时长及其结果（合格/不合格），有助于识别与质量相关的瓶颈或重复出现的问题。 获取方式 这是在 Oracle Quality Management 模块中捕获的显式事件。检查结果记录在质量收集计划中，这些计划与生产订单或特定工序相关联。 捕获 从链接到工单的质量采集结果表中的记录获取。 事件类型 explicit | |||
| 已检查物料可用性 | 代表系统已检查生产订单所需所有组件可用性的时间点。这通常是订单创建后的自动化步骤，也可以由计划员手动触发。 | ||
| 为何重要 跟踪此活动有助于分析由物料短缺引起的延迟。它将计划问题与执行问题区分开来，是理解供应链绩效对生产开始时间影响的关键。 获取方式 这可能是一个推断出的事件，源自工单或其组件需求列表上的状态更改。例如，“等待组件”状态变为“组件可用”，或者物料状态字段被填充，都标志着这一事件。 捕获 通过与工单关联的物料可用性状态字段的变更来推导。 事件类型 inferred | |||
| 操作完成 | 代表工单生产工艺路线中单个步骤或工序的完成。一个生产订单通常会有多个“工序已完成”事件，对应其制造过程中的每个步骤。 | ||
| 为何重要 分析连续工序完成之间的时间，可以提供生产流的细粒度洞察。它有助于识别瓶颈工序并计算特定阶段的在制品时间。 获取方式 这是通过车间移动或完成事务捕获的显式事件。操作员记录工序完成的数量，从而生成带有时间戳的事务记录。 捕获 从针对工单特定工序的完成事务中获取。 事件类型 explicit | |||
| 物料已发放 | 此活动代表将组件物料从库存发料至特定生产订单的事务。它表示必要的原材料或子组件已被拣选并移至生产线。 | ||
| 为何重要 跟踪发料情况有助于分析仓库和物流运营的效率。它还能在生产开始前确认物料可用性，提供比初始检查更准确的情况。 获取方式 这是在库存模块中记录为物料事务并链接到工单的显式事件。每笔事务都有一个时间戳和所发组件的详细信息。 捕获 从链接到工单 ID 的物料事务记录（例如 WIP 组件发放）中获取。 事件类型 explicit | |||
| 生产绩效已分析 | 代表生产订单完成后的绩效分析，将实际结果与计划目标进行对比。这可能涉及审查成本、物料消耗和计划达成率。 | ||
| 为何重要 此活动是了解持续改进循环有效性的关键。衡量分析绩效所需的时间，可以反映组织从生产差异中学习并做出响应的速度。 获取方式 这是一个计算出的事件，因为它是一个业务流程而非系统事务。它可以从差异报告的生成日期或绩效备注字段的更新中推断出来，但这高度取决于当地的规程。 捕获 通过检测绩效管理系统中相关差异报告的创建或状态更新来推导。 事件类型 calculated | |||
| 生产计划已调整 | 当已下达生产订单的关键参数（如需求数量或计划完成日期）发生更改时，会发生此活动。这表示偏离了最初投入生产的计划。 | ||
| 为何重要 频繁的调整表明计划或执行流程存在不稳定性。跟踪这些事件有助于量化变动程度，并识别根因（如需求波动或生产问题）。 获取方式 这是一个可以从工单记录上的审计追踪或更改日志中捕获的显式事件。Oracle Cloud 通常会跟踪关键字段的更改，包括更改者和更改时间。 捕获 从工单实体的审计日志或变更历史表中获取。 事件类型 explicit | |||
推荐 可选
提取指南
如何从 Oracle 制造云获取您的数据
步骤
- 导航至 Oracle BI Publisher：使用具有 BI Author 或 Administrator 权限的用户登录您的 Oracle Fusion Cloud 实例。通过导航菜单进入“工具” > “报告和分析”。然后，点击“浏览目录”按钮以打开 BI Publisher 目录。
- 创建新数据模型：在 BI Publisher 目录中，导航至合适的文件夹（例如，共享文件夹 > Custom）。点击“新建”下拉菜单并选择“数据模型”。
- 定义新数据集：在数据模型编辑器中，点击“数据集”旁边的“+”图标并选择“SQL 查询”。
- 配置数据集：在“新建数据集”对话框中，输入一个描述性名称，例如“Production_Planning_Event_Log”。对于“数据源”，选择您的 Oracle Fusion 应用程序数据库，通常命名为“ApplicationDB_FSCM”或类似变体。
- 输入 SQL 查询：复制本文档“查询”部分提供的完整 SQL 查询，并将其粘贴到“SQL 查询”文本区域中。
- 定义查询参数：查询使用参数
:p_start_date和:p_end_date来过滤日期范围。粘贴查询后，这些参数将出现在参数部分。对于每个参数，将“数据类型”设置为“日期”，并提供一个用户友好的“显示标签”，如“开始日期”。如果需要，您还可以设置默认值。 - 查看并验证数据：点击数据模型编辑器中的“数据”选项卡。输入开始和结束日期参数的值，然后点击“查看”以执行查询并查看提取数据的样本。验证列和行是否符合预期。
- 保存数据模型：对 data 预览满意后，点击“保存”图标。为您的 data 模型命名（例如“ProductionPlanningExtraction.xdm”），并将其保存在指定的自定义文件夹中。
- 创建导出报告：要导出完整数据集，必须将此 data 模型链接到报告。在保存的 data 模型中点击“创建报告”按钮。按照向导创建一个简单的基于表格的报告布局，无需复杂的格式设置。
- 运行并导出报告：保存新创建的报告。打开报告并使用所需的日期参数运行。报告生成后，使用“导出”功能将输出保存为 CSV 文件。此 CSV 文件即为准备好上传的事件日志。
配置
- 数据源：查询必须针对 Oracle Fusion 应用程序数据库（通常命名为
ApplicationDB_FSCM）运行。请确保配置的用户对查询中引用的所有制造、库存和人员表具有读取权限。 - 日期范围参数：查询包含两个强制参数
:p_start_date和:p_end_date用于过滤 data。建议按 3 到 6 个月的批次提取 data，以保持良好的系统性能并控制文件大小。 - 关键过滤器：为了进一步缩小 data 范围并提高性能，请考虑在
base_data通用表表达式中添加WHERE子句。常用过滤器包括：IODV.ORGANIZATION_CODE IN ('[您的工厂代码 1]', '[您的工厂代码 2]')用于选择特定的生产工厂。WDJ.WORK_ORDER_TYPE IN ('Standard', '[您的自定义类型]')用于选择特定类型的生产工单。
- 性能考量：这是一个具有多个
UNION ALL分支的复杂查询。对于大型制造业务，它可能会耗费大量资源。请安排在非高峰时段进行提取，以尽量减少对系统性能的影响。 - 先决条件：创建并运行 data 模型的用户需要 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等表具有明确的读取权限。
a 查询示例 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步骤
- 导航至 OTBI：登录您的 Oracle 制造云实例。通过导航菜单进入“工具”，选择“报告和分析”。点击“浏览目录”按钮以打开 Business Intelligence 目录。
- 创建新分析：在 OTBI 目录中，点击工具栏中的“新建”并选择“分析”。系统将提示您选择一个主题区域。
- 选择主题区域：从可用主题区域列表中选择“Manufacturing - Work Order Performance Real Time”。该主题区域包含有关工单及其状态的核心信息。
- 定义分析标准：分析编辑器有两个主要选项卡：“标准”和“结果”。在“标准”选项卡中，您将构建事件日志的逻辑。
- 为第一个活动添加列：从左侧的“主题区域”窗格中，将必要的列拖放到“选定列”区域。从“Work Order Created”活动开始。例如，添加“Work Order Details”的“Work Order Number”、活动名称的公式、“Work Order Details”的“Creation Date”（作为 timestamp）以及其他所需属性。
- 为活动名称创建自定义公式：点击“选定列”标题旁边的“添加新计算度量”图标。在公式框中输入活动文本，例如
'Work Order Created'。将该列命名为“ActivityName”。 - 使用 UNION ALL 合并所有活动：要创建包含所有必需活动的单个事件日志，必须合并多个查询。在“标准”选项卡中，找到“选定列”部分右下角的“基于集合操作合并结果”图标（通常看起来像两个重叠的圆圈）。选择“Union All”，并为下一个活动（如“Work Order Released”）添加一个新的标准块。
- 重复所有活动：对所有 13 个必需活动重复步骤 5-7。对于每个活动，您将创建一个结合了“Union All”的新标准块。确保为每个活动的 timestamp 选择正确的日期字段（例如“Release Date”、“Actual Completion Date”、“Closed Date”）。某些活动需要针对“Work Order Status Name”进行过滤。
- 应用全局过滤器：设置好所有 UNION 块后，转到“过滤器”部分。添加过滤器以限制 data 范围。务必为“Work Order Creation Date”或“Last Update Date”添加过滤器，以指定日期范围（例如过去 6 个月）。您还应按“Organization Name”进行过滤，以选择正确的生产工厂。
- 检查并格式化结果：切换到“结果”选项卡以预览 data。确保所有列都已显示且命名正确。如有必要，您可以拖放列来重新排序。
- 导出事件日志：分析完成且结果无误后，点击页面底部的“导出”链接。选择“CSV 文件”作为格式。
- 准备上传：打开下载的 CSV 文件。验证列标题是否与要求的属性匹配：
ProductionOrderNumber、ActivityName、EventTimestamp等。确保 timestamp 格式一致。文件现在可以上传到您的 Process Mining 工具了。
配置
- 主题区域：“Manufacturing - Work Order Performance Real Time”是此次提取使用的主要主题区域。如需详细的物料或质量 data，您可能需要使用“Manufacturing - Material Usage Real Time”或“Quality Management - Inspection Results Real Time”创建单独的分析并关联结果。
- 日期范围过滤器：应用日期范围过滤器对于管理 data 量至关重要。在“Work Order Details”的“Creation Date”或“Last Update Date”上创建过滤器，以提取最近一段时间（如过去 3 到 6 个月）的 data。
- 组织过滤器：始终按“Organization Details”的“Organization Name”或“Organization Code”进行过滤，以将 data 限制在您正在分析的特定生产工厂或业务单元。
- 工单状态过滤器：为了减少干扰，您可能希望在整个分析中排除某些工单状态，例如草稿或模拟订单。这可以通过在“Work Order Status”的“Work Order Status Name”上设置全局过滤器来实现。
- 先决条件：运行分析的用户必须拥有适当的角色和权限，通常是“制造工程师”角色或自定义 BI 角色，才能访问“Manufacturing - Work Order Performance Real Time”主题区域。
a 查询示例 config
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]'