> 持续流程改进 > 采购到付款采购订单 > Oracle Fusion Financials > 数据模板

数据模板：采购到付款 - 采购订单

Oracle Fusion Financials

采购到付款 - 采购订单属性 (22) 采购到付款 - 采购订单活动 (16) 提取指南 (4)

您的采购到付款-采购订单数据模板

此模板 (template) 为收集分析采购到付款 (Purchase to Pay) - 采购订单流程 (Purchase Order process) 所需的数据 (data) 提供了清晰的路线图。它概述了基本数据属性 (data attributes)、要追踪的关键活动 (activities)，以及提取这些信息的实用指导。请使用此模板确保您捕获所有关键细节，以进行有效的流程挖掘 (Process Mining)。

全面采集数据的推荐属性
需要跟踪和分析的关键流程活动
Oracle Fusion Financials 分步抽取指南

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

下载模板

采购到付款 - 采购订单属性

以下为建议在事件日志中包含的数据字段，用于对“采购到付款（P2P）—采购订单”流程进行全面分析。

3 必填 7 推荐 12 可选

	名称	描述
	开始时间 EventTime	指示特定活动或事件发生时间的时间戳。
描述此属性记录流程中每个活动发生的精确日期和时间，是按时间顺序排列事件及开展所有基于时间分析的基础。在流程挖掘中，开始时间用于构建事件日志（Event Log）、计算活动间的周期时长、衡量等待时间，并分析不同时间段的流程表现。它是与周期时长和绩效相关仪表板的核心字段。为何重要此时间戳 (timestamp) 对于正确地排序事件 (events) 和计算所有基于持续时间的指标 (metrics)，例如周期时间 (cycle times) 和瓶颈 (bottlenecks)，至关重要。获取方式时间戳 (Timestamp) 字段，例如来自PO_HEADERS_ALL、PO_ACTION_HISTORY和RCV_SHIPMENT_LINES等表中的CREATION_DATE和LAST_UPDATE_DATE。示例 2023-04-15T10:05:00Z2023-04-16T14:30:00Z2023-05-01T09:00:00Z
	活动 ActivityName	发生在采购订单生命周期中的具体业务事件或步骤名称。
描述此属性描述流程中的具体任务或状态变化，如“创建采购订单”或“收货”。这些活动构成流程中的一系列事件。分析这些活动的先后顺序与时间点是流程挖掘的核心。它有助于可视化流程图、定位瓶颈、发现偏离标准流程的行为，并衡量特定步骤的持续时间。为何重要活动是流程图的基本组成部分。跟踪活动有助于可视化并分析流程流转、瓶颈和偏差。获取方式来自 PO_HEADERS_ALL、PO_ACTION_HISTORY 等表的状态变更，或来自用于收货的 RCV_TRANSACTIONS 等交易表。示例采购订单已创建采购订单已批准已收货
	采购订单 PurchaseOrder	采购订单单据的唯一标识，用作跟踪采购生命周期的主实例ID。
描述采购订单编号是贯穿从创建到最终关闭、连接所有相关活动的核心标识，便于对单个采购案例进行端到端分析。在流程挖掘中，每个唯一的采购订单编号代表一个流程实例。按该标识汇总分析数据，有助于理解单个订单的流程差异、周期时长和合规情况。为何重要这是将所有相关事件 (events) 连接起来的核心案例ID (Case ID)，能够重构和分析整个采购订单生命周期 (Purchase Order lifecycle)。获取方式 Oracle Fusion Cloud SCM采购模块，表PO_HEADERS_ALL，字段SEGMENT1。示例 100234510023461002347
	PO总金额 PurchaseOrderTotalAmount	采购订单的总金额。
描述此属性表示采购订单中所有行项目按指定币种计价的总金额，是衡量流程中交易规模的核心财务指标。分析PO总金额有助于确定改进优先级。例如，高金额订单可能需要更严格的审批路径。它也支持财务影响评估，如量化频繁被修改或延迟的PO所对应的金额。为何重要为流程提供财务背景，可按金额维度开展分析，例如聚焦高金额订单，或评估延误对财务的影响。获取方式针对指定的 PO 头，通过汇总 PO_LINES_ALL 的金额计算；如有头级总额，则直接取用。示例 5250.00120000.50750.99
	供应商名称 VendorName	提供所购货物或服务的供应商名称。
描述此属性用于标识采购订单（PO）的外部供应商，是与PO抬头关联的关键主数据。供应商分析是P2P流程挖掘的核心环节。通过按供应商筛选或细分，可评估“供应商交付表现”，对比准时交付率，并查看“退货率”，从而识别表现好或欠佳的供应商。这些数据对供应商关系管理和战略寻源至关重要。为何重要供应商绩效分析的关键，可用于对比各供应商的交付时效、退货率与整体可靠性。获取方式从PO_HEADERS_ALL.VENDOR_ID关联到POZ_SUPPLIERS.VENDOR_NAME。示例全球办公用品Tech Solutions Inc.Advanced Logistics Co.
	审批人姓名 ApproverName	对采购订单执行批准或拒绝操作的用户姓名。
描述此属性用于标识工作流中负责某个审批环节的个人。该信息通常存放在与采购订单关联的操作历史或工作流日志表中。按审批人维度分析数据，是“PO审批周期分析”和“审批资源工作量”仪表板的核心。这有助于识别成为瓶颈的审批人或审批组，客观评估工作量，并挖掘可委派或流程再设计的机会。为何重要标识审批链中的人员，可按审批人分析审批瓶颈、工作负载与审批周期。获取方式执行该操作的用户，来源于PO_ACTION_HISTORY.ACTION_PERFORMED_BY，并关联用户表以获取全名。示例 susan.managerdavid.directoremily.finance
	客户要求交货日期 RequestedDeliveryDate	申请方期望货物或服务交付的日期。
描述该日期填在采购订单行上，用于向供应商传达期望的交付时间，是衡量准时交付表现的基准。此属性对计算“准时交付率”KPI至关重要。将实际收货日期与要求交期进行对比，企业即可量化并跟踪供应商可靠性，并识别供应链中的系统性延误。为何重要作为衡量准时交付表现的基线，是评估供应商可靠性与供应链效率的关键KPI。获取方式位于行位置层级：表PO_LINE_LOCATIONS_ALL，字段NEED_BY_DATE。示例 2023-05-202023-06-152023-07-01
	用户 UserName	执行该活动的人员的用户 ID 或姓名。
描述此属性用于标识对某个事件负责的员工或系统用户，例如创建申请、批准PO或进行收货过账。该信息通常来自“Created By”或“Last Updated By”等字段。按用户维度分析流程，有助于了解工作量分布、个人绩效，并识别培训需求。这也是“审批资源工作量”仪表板以及用户操作合规性调查的基础。为何重要将用户操作归属到具体个人，便于进行工作量分析、绩效评估，并识别培训机会。获取方式根据PO_HEADERS_ALL、PO_ACTION_HISTORY等表中的CREATED_BY、LAST_UPDATED_BY等字段的ID，与用户表进行关联。示例 john.doejane.smithsystem.batch
	结束时间 EndTime	某项活动完成时的时间。对于原子事件，通常与开始时间相同。
描述对于有持续时长的活动，该时间标记完成时刻；对于瞬时事件，通常与 Start Time 相同。它是计算单个活动处理时长的关键。区分 End Time 可更精确地分析活动执行时长，并将其与活动间的等待时间区分开来，从而分辨有效工作时间与空闲时间，支持资源负载与效率分析。为何重要支持精确计算活动处理时间，这是分析资源效率、识别耗时任务的关键。获取方式对于原子事件，其值可与开始时间相同，或由后续事件的时间戳推导。部分活动可能存在单独的完成时间戳。示例 2023-04-15T10:05:00Z2023-04-16T14:45:00Z2023-05-01T09:15:00Z
	部门 DepartmentName	发起或负责该采购订单的部门名称。
描述此属性用于标识与采购相关的组织单元，如“财务”“IT”“制造”，常用于成本归集与组织维度报表。在流程挖掘中，按部门切分流程至关重要：便于对比绩效、定位部门特有的瓶颈，并理解组织内执行差异。它直接支撑“PO审批周期分析”“采购订单变更趋势”等仪表板。为何重要支持按业务单元筛选并比较流程绩效，发现各部门特有的问题或最佳实践。获取方式源自 PO_DISTRIBUTIONS_ALL 等表中的成本中心信息，并关联至部门主数据。示例 IT运维市场营销研发
	PO状态 PurchaseOrderStatus	采购订单（PO）单据的当前状态。
描述此属性表示采购订单在其生命周期中的当前状态，如“Open”“Approved”“Finally Closed”或“Canceled”，用于快速了解PO的推进情况。尽管流程挖掘关注活动顺序，但当前状态非常适合用于筛选流程实例。例如，只分析处于 Open 状态的PO以把握当前在途订单，或仅分析已关闭的PO以复盘已完成的流程实例。该属性也是“采购订单流程与状态”仪表板的关键输入。为何重要提供采购订单当前状态快照，可按在途、已完成或已取消进行筛选分析。获取方式 Oracle Fusion Cloud SCM，表PO_HEADERS_ALL，字段AUTHORIZATION_STATUS或DOCUMENT_STATUS。示例 OPENAPPROVEDFINALLY_CLOSEDCANCELED
	PO类型 PurchaseOrderType	采购订单类型，例如“Standard”“Blanket”或“Contract”。
描述此属性依据采购目的对采购订单进行归类。不同类型的PO往往遵循不同的流程规则与生命周期。 “Standard” PO为一次性采购，“Blanket” PO为与供应商的长期协议。按PO类型分析流程能更准确地评估流程绩效；将标准PO与长期协议的周期时长直接对比会产生误导。该方式支持同类对比。为何重要区分不同的采购场景，便于对同类订单进行更准确的同口径流程绩效对比。获取方式 Oracle Fusion Cloud SCM，表PO_HEADERS_ALL，字段TYPE_LOOKUP_CODE。示例 STANDARDBLANKETCONTRACT
	业务单元 BusinessUnitName	组织内进行采购的具体业务单元。
描述业务实体（Business Unit）代表企业内独立的经营主体，通常拥有各自的总账与财务报表。在 Oracle Fusion 中，它是数据隔离的主要机制。按业务实体分析流程绩效对大型跨国企业尤为关键，可横向比较不同组织单元的采购效率、合规水平与成本，既能沉淀最佳实践，也能定位改进空间。为何重要对大型组织而言，用于在不同运营板块间比较流程效率与合规性，至关重要。获取方式业务实体信息通常位于PO抬头字段 PO_HEADERS_ALL.PRC_BU_ID，并关联到视图 FUN_ALL_BUSINESS_UNITS_V。示例美国业务部门EMEA VisionAPAC Services
	最后数据更新 LastDataUpdate	从源系统最后一次抽取或刷新数据的时间。
描述此属性反映所分析数据的时效性，记录最近一次从 Oracle Fusion Financials 抽取数据的日期和时间。这有助于用户判断分析与仪表板的及时性，明确洞察是否已覆盖最新交易，从而合理设定预期。为何重要清晰展示数据时效，帮助用户了解当前分析的及时性。获取方式这是一个在数据 (data) 提取、转换和加载 (ETL) 过程中生成并添加的时间戳 (timestamp)。示例 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	审批周期时间 ApprovalCycleTime	从采购订单创建到最终批准的时长。
描述这是一个计算型持续时间指标，用于衡量单个案例中“采购订单已创建” (Purchase Order Created) 活动与“采购订单已审批” (Purchase Order Approved) 活动之间所经过的时间。此属性直接为“平均采购订单审批周期” (Average PO Approval Cycle Time) KPI 提供数值。分析其分布有助于了解审批工作流的效率，识别异常值，并衡量旨在减少审批延迟的流程改进举措的影响。为何重要量化审批瓶颈，直接衡量“平均PO审批周期”KPI，并突出显示工作流中的延误。获取方式计算字段。'Purchase Order Approved' 与 'Purchase Order Created' 两个活动的时间戳之差。示例 P2DPT8H30MP5DT12H
	审批是否合规 IsApprovalCompliant	用于标识采购订单在发送给供应商前是否已批准。
描述这是一个计算型布尔属性，用于检查关键内部控制的遵循情况：采购订单 (Purchase Order) 必须在发送给供应商之前获得批准。如果“采购订单已审批” (Purchase Order Approved) 活动发生在“采购订单已发送给供应商” (Purchase Order Sent to Vendor) 活动之前，则该属性为True。此属性对于“采购订单流程合规审计” (PO Process Compliance Audit) 仪表板和“采购订单审批合规率” (PO Approval Compliance Rate) KPI至关重要。它提供了一种直接的方法来识别和量化合规性违规，有助于执行采购政策并降低与未经授权支出相关的风险。为何重要直接衡量『采购订单审批合规率』KPI，突出在审批前即发送给供应商等关键内控违规。获取方式计算字段。若 'Purchase Order Approved' 的时间戳小于或等于 'Purchase Order Sent to Vendor' 的时间戳，则设为 'true'。示例 truefalse
	收货地点 DeliveryLocation	货物的实际交付地点或地址。
描述此属性为采购订单所列物品的送货地址，是关键物流信息。在流程挖掘中，按送达地点分析可支撑“收货处理效率”仪表板，帮助识别某些仓库或站点收货更慢，从而定位特定地点的资源或流程问题。为何重要支持按地理位置进行绩效分析，识别收货流程中的区域或站点级瓶颈。获取方式从PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID关联到HR_LOCATIONS_ALL视图。示例主仓库-A卸货口3号楼-前台SF Office - 10th Floor
	是否延迟交付 IsLateDelivery	用于标识最终收货是否晚于要求的交货日期。
描述这是一个计算得到的布尔属性：当某一PO的“Goods Received”活动的时间戳晚于其“Requested Delivery Date”属性时，其值为真（true）。该标记是“准时交付率”KPI的基础。借助它可便捷地对逾期与准时订单进行分组分析，进一步排查延误是否与特定供应商、地点或品类相关。为何重要直接支撑『准时交付率』KPI，便于清晰分析供应商表现与交付可靠性。获取方式计算字段。若 'Goods Received' 活动的时间戳晚于 'RequestedDeliveryDate' 属性，则设为 'true'。示例 truefalse
	是否返工 IsRework	用于标识采购订单在初次创建后是否被修改。
描述这是一个计算型布尔属性，如果采购订单 (Purchase Order) 案例中包含“采购订单已更改” (Purchase Order Changed) 活动，则其值设为True。它有助于快速识别需要更正或修改的订单。此标记简化了“采购订单修改率” (PO Modification Rate) KPI 的计算，并支持对返工订单进行轻松筛选和分析。了解返工订单的特征，例如涉及的供应商或部门，有助于查明数据不准确或需求变更的根本原因。为何重要直接支撑『采购订单变更率』KPI，通过标记所有发生过变更的订单，简化对流程不稳定性的分析。获取方式计算字段。若某个流程实例的事件日志包含活动 'Purchase Order Changed'，则设为 'true'，否则为 'false'。示例 truefalse
	源系统 SourceSystem	此数据的来源系统。
描述此属性用于标识数据来源，尤其适用于多系统集成环境。对于本流程，通常为“Oracle Fusion Financials”。虽然在某一数据集中该值通常不变，但它对数据治理、故障排查与数据血缘至关重要。在多来源合并分析中，可按来源系统进行筛选或分组。为何重要标识数据来源，这对数据治理、业务上下文以及与其他系统的集成至关重要。获取方式这通常是一个在数据 (data) 提取、转换和加载 (ETL) 过程中定义并添加的常量值。示例 Oracle Fusion FinancialsOracle Cloud SCMOracle Fusion P2P
	采购申请 PurchaseRequisitionNumber	作为该采购订单依据的前置采购申请编号。
描述采购申请是用于申请采购货物或服务的内部单据。此属性将采购订单关联回其最初的申请。纳入申请编号后，分析范围可从最初的申请开始，而非只看采购订单（PO）。这有助于分析从申请到下单的周期时长，并了解申请明细如何影响后续的采购订单流程。为何重要将PO与初始请购单关联，可构建从请购到付款的端到端流程视图。获取方式通过PO_DISTRIBUTIONS_ALL表关联（包含REQ_DISTRIBUTION_ID），可回溯到POR_REQUISITION_LINES_ALL表。示例 PR-2023-05-001PR-2023-05-002PR-2023-05-003
	采购类别 PurchaseCategory	对采购的商品或服务进行分类，例如“IT硬件”或“办公用品”。
描述此属性将采购订单中的品项按采购层级进行分类。该分类用于支出分析与供应商管理。在流程挖掘中，按采购类别对流程分段可揭示不同的行为模式或绩效水平。例如，资本性支出的审批流程可能比日常物料更长。它也直接支撑“退货率与原因”仪表板，可分析哪些类别最常被退回。为何重要支持按支出类型进行流程分析，可揭示不同物料类别的流程路径、瓶颈与退货率差异。获取方式从PO_LINES_ALL.CATEGORY_ID关联到EGP_CATEGORIES_VL视图。示例 IT.Hardware.LaptopsOffice.Supplies.StationeryProfessional.Services.Consulting

必填推荐可选

采购到付款 - 采购订单活动

以下是在事件日志中应采集的关键流程步骤与里程碑，用于准确发现“采购到付款（P2P）—采购订单”流程。

6 推荐 10 可选

	活动	描述
	已收货	实物已收货、清点，并登记到对应的采购订单。这是一项会更新库存和采购订单状态的交易事件。
为何重要这是一个衡量供应商准时交付绩效和总体交货期的关键里程碑。它还充当质量检查和发票匹配等后续活动的触发器。获取方式这是一个记录在RCV_TRANSACTIONS表中的显式事件 (explicit event)。具体交易可通过TRANSACTION_TYPE为“RECEIVE” (收货) 来识别。捕获使用RCV_TRANSACTIONS中TRANSACTION_TYPE为“RECEIVE” (收货) 的TRANSACTION_DATE。事件类型 explicit
	采购订单已最终关闭	该采购订单被视为完成，表示已全部收货和/或完成开票，不再有后续活动。这是一项将采购订单设为终止状态的操作。
为何重要此活动标志着采购订单生命周期成功结束。它是主要的正向终态，跟踪该状态对于衡量整体流程吞吐量与完成率至关重要。获取方式该事件记录在 PO_ACTION_HISTORY 表中，ACTION_CODE 为“FINALLY CLOSE”。同时 PO_HEADERS_ALL 中的PO状态会更新为“Finally Closed”。捕获在 PO_ACTION_HISTORY 中筛选 ACTION_CODE='FINALLY CLOSE'。事件类型 explicit
	采购订单已创建	这是采购订单生命周期 (purchase order lifecycle) 的正式开始，此时采购订单 (PO) 文档以草稿或未完成状态生成。系统通过记录新采购订单头记录 (PO header record) 的创建时间戳 (creation timestamp) 来捕获此事件。
为何重要作为 PO 流程实例的主要起始事件，该活动是所有周期时间计算的基础。它为衡量后续步骤（如审批与供应商沟通）的效率提供基线。获取方式这是一个基于PO_HEADERS_ALL表中指定采购订单ID (PO_HEADER_ID) 的CREATION_DATE字段的显式事件 (explicit event)。捕获使用PO_HEADERS_ALL表中的创建时间戳 (creation timestamp)。事件类型 explicit
	采购订单已发送至供应商	已批准的采购订单将通过邮件或EDI等方式正式发送给供应商。此事件通常可由状态变更或PO通信记录上的时间戳推断得到。
为何重要这标志着供应商交货期的开始。它是衡量供应商绩效的关键节点，涵盖从确认到最终交付的整个过程。获取方式可通过PO单据状态变为“Open”且通信日期被写入来推断。对应字段通常为 PO_HEADERS_ALL.communicated_date 或相关状态。捕获根据 PO 传送状态更新为 'Communicated' 时的时间戳进行推断。事件类型 inferred
	采购订单已取消	该采购订单已被永久取消，不再发生后续交易。这是一项将单据置为终止状态的操作。
为何重要此活动代表流程的负向终态。分析取消情形可揭示如重复下单、预算调整或项目需求变更等问题。获取方式该操作会以ACTION_CODE为“CANCEL”的形式记录在PO_ACTION_HISTORY表中，并相应更新PO_HEADERS_ALL中的采购订单状态。捕获在 PO_ACTION_HISTORY 中筛选 ACTION_CODE='CANCEL'。事件类型 explicit
	采购订单已批准	该采购订单已获得全部必要批准，可发出给供应商。这一关键里程碑事件会在单据的操作历史中被记录。
为何重要这是一个关键里程碑，它控制着采购订单 (PO) 是否可以发送给供应商。它对于衡量审批周期和确保支出政策的合规性至关重要。获取方式该事件记录在 PO_ACTION_HISTORY 表中，通常的 ACTION_CODE 为“APPROVE”，或当 PO_HEADERS_ALL 中的单据状态变为已批准时。捕获在 PO_ACTION_HISTORY 中筛选最终一次 'APPROVE' 操作。事件类型 explicit
	已创建收货单	系统中发起收货单，为实物到货做准备。此活动标志着内部收货流程的开始。
为何重要这标志着从采购到物流 (logistics) 的过渡。分析从此时点到最终收货过账的时间，有助于识别仓库或收货部门的效率低下问题。获取方式这是一个显式事件 (explicit event)，通过RCV_SHIPMENT_HEADERS表中与采购订单关联的新记录的创建时间戳 (creation timestamp) 捕获。捕获使用RCV_SHIPMENT_HEADERS中相应记录的创建日期。事件类型 explicit
	已执行质量检验	需质检的物料已完成检验，并被判定为通过或拒收。该活动发生在初次收货之后，并作为独立的交易记录。
为何重要此活动对质量管理至关重要。分析检验耗时有助于精简质控流程，缩短货物可用前的等待时间。获取方式此活动记录在RCV_TRANSACTIONS表中。它通过在收货交易后发生的TRANSACTION_TYPE为“ACCEPT” (接受) 或“REJECT” (拒绝) 的交易来识别。捕获使用RCV_TRANSACTIONS中TRANSACTION_TYPE为“ACCEPT” (接受) 或“REJECT” (拒绝) 的TRANSACTION_DATE。事件类型 explicit
	服务交付已确认	对于服务类采购订单，该活动标记服务已按约定完成。此确认通常由人工记录或通过服务录入单完成。
为何重要这相当于服务的收货，是发票支付前的关键步骤。服务确认的延迟可能导致付款逾期和供应商关系紧张。获取方式这通常作为采购订单 (PO) 上服务行的收货记录。它可能涉及特定字段或复杂收货，以追踪服务的进度或完成情况。捕获识别与服务类 PO 行项目关联的收货交易（RCV_TRANSACTIONS）。事件类型 explicit
	退回供应商	已收货的物料被退回给供应商，通常因缺陷、损坏或错发。该操作会在收货模块中以特定的退货事务记录。
为何重要追踪退货对于评估供应商质量和订单准确性至关重要。供应商较高的退货率可能表明存在需要解决的系统性问题。获取方式这是一个记录在RCV_TRANSACTIONS表中，且TRANSACTION_TYPE为“RETURN TO VENDOR” (退货给供应商) 的显式事件 (explicit event)。捕获使用RCV_TRANSACTIONS中TRANSACTION_TYPE为“RETURN TO VENDOR” (退货给供应商) 的TRANSACTION_DATE。事件类型 explicit
	采购申请已创建	此活动标志着采购申请的创建，它是在采购订单之前提出的正式货物或服务请求。在Oracle Fusion的申请头表新增记录时予以采集。
为何重要分析此活动有助于理解需求发起阶段。跟踪从请购到创建采购订单的时间，可揭示将内部需求转化为可执行采购订单的潜在延迟。获取方式这是一个在保存新采购申请时记录的显式事件 (explicit event)。可以通过追踪POR_REQUISITION_HEADERS_ALL表中的创建时间戳 (creation timestamp) 来找到它。捕获该事件基于 POR_REQUISITION_HEADERS_ALL 表中记录的创建日期。事件类型 explicit
	采购申请已批准	该采购申请已由指定审批方批准，授权采购部门创建采购订单。此事件会在系统的申请单操作历史中被记录。
为何重要此里程碑标志着请求的内部审批流程的结束。此处的延迟会直接影响整个采购时间，因此监控其持续时间至关重要。获取方式该事件记录在请购单的操作历史中，通常通过工作流表或请购单上的特定审批状态字段进行跟踪。捕获在该请购单的工作流历史中记录为审批动作。事件类型 explicit
	采购订单已变更	采购订单在首次批准后被修改，例如数量、价格或交期发生变化。Oracle Fusion会通过创建该单据的新修订版进行跟踪。
为何重要 PO变更代表返工，可能暴露初始下单不准确或业务需求变化。分析变更的频次与类型，有助于发现提升流程效率的机会。获取方式当创建新的单据修订版时会显式记录该事件，可通过 PO_HEADERS_ALL 表中 REVISION_NUM 字段递增来识别。捕获识别同一 PO_HEADER_ID 的 REVISION_NUM 每次递增的记录。事件类型 explicit
	采购订单已拒绝	审批人已驳回该采购订单，并退回给创建者修改。此事件会作为显式事件记录在操作历史中，表明标准流程被打断。
为何重要被拒会带来返工与延误。跟踪此活动有助于找出常见拒绝原因、培训需求或审批要求不清等问题。获取方式该操作会以ACTION_CODE为“REJECT”的形式记录在PO_ACTION_HISTORY表中，并关联到对应的PO_HEADER_ID。捕获在 PO_ACTION_HISTORY 中筛选 ACTION_CODE='REJECT'。事件类型 explicit
	采购订单已提交	已创建的采购订单会提交到审批工作流。Oracle Fusion会记录此操作，并保存提交人和时间。
为何重要此活动标志着审批周期的开始。分析从提交到批准的时间，是找出内部签核流程瓶颈的关键。获取方式该操作会以ACTION_CODE为“SUBMIT”的形式记录在PO_ACTION_HISTORY表中，并关联到对应的PO_HEADER_ID。捕获在 PO_ACTION_HISTORY 中筛选 ACTION_CODE='SUBMIT'。事件类型 explicit
	采购订单已确认收悉	供应商已确认收到并接受采购订单条款。此事件通常由采购人员依据与供应商的沟通手动记录，或通过电子回执采集。
为何重要供应商确认 (acknowledgement) 提供了订单已被接收和处理的确定性。追踪此信息有助于管理供应商沟通，并主动识别潜在的履约问题。获取方式这通常是通过采购订单头 (PO header) 或行上的确认状态字段 (acknowledgement status fields) 的变化来推断的，例如PO_HEADERS_ALL.acceptance_status变为“Accepted” (已接受)。捕获根据 PO 确认状态字段的更新进行推断。事件类型 inferred

推荐可选

提取指南

如何从 Oracle Fusion Financials 获取您的数据

步骤

**进入OTBI：**登录Oracle Fusion Applications，从主导航菜单进入'Reports and Analytics'工作区。点击'Browse Catalog'打开Business Intelligence目录。
**新建Analysis：**点击工具栏中的'Create'按钮并选择'Analysis'。系统会提示您选择主题域。
**选择主题域：**在可用主题域列表中选择'Procurement - Purchasing Real Time'。该主题域包含请购、采购订单与收货所需的数据。
**构建第一个条件块（Purchase Order Created）：**在'Criteria'标签页，将所需列拖入'Selected Columns'区域。针对首个活动'Purchase Order Created'，包含：
- "PO Headers All"."PO Number" 作为 PurchaseOrder
- 自定义文本公式 'Purchase Order Created' 作为 ActivityName
- "PO Headers All"."Creation Date" 作为 EventTime 和 EndTime
- "PO Headers All"."Created By" 作为 UserName
- "Requisitioning BU"."Department Name" 作为 DepartmentName
- "Supplier"."Supplier" 作为 VendorName
- "Base Measures"."Ordered Amount" 作为 PurchaseOrderTotalAmount
- "PO Schedules All"."Requested Delivery Date" 作为 RequestedDeliveryDate
- NULL 作为 ApproverName
**为所有活动创建Union合并：**要将每个活动以独立行记录，需要使用'Union All'将多个分析合并。其余15个活动分别建立类似的条件块。点击'Selected Columns'底部的'Combine results based on another analysis'图标，选择'Union All'。
**配置每个活动块：**根据需求中的16个活动逐一创建条件块。为每个块将 ActivityName 设为固定值（如'Purchase Order Approved'），并将相应的日期列映射到 EventTime。其余属性列保持一致；如某事件不适用，则填入 NULL（例如，事件'Purchase Order Created'的 ApproverName 为 NULL）。
映射关键日期与用户字段：
- **审批/驳回：**使用'Document History'文件夹获取操作日期和执行用户，用于 EventTime 与 ApproverName。
- **请购：**使用'Requisition Headers'文件夹获取请购创建与审批日期。
- **收货：**使用'Receipts'与'Receipt Line Transactions'文件夹处理'Goods Received'、'Goods Returned to Vendor'等事件。针对特定收货活动按'Transaction Type'进行筛选。
- **变更：**筛选 "PO Headers All"."Revision Number" > 0，并使用该修订的创建日期作为'Purchase Order Changed'活动的时间。
**添加筛选器：**切换到合并分析的'Filters'部分，添加全局筛选以限定数据范围。常见且有效的做法是在'PO Headers All'.'Creation Date'上提示用户输入时间区间（如最近6个月）。
**保存并运行分析：**将分析保存到共享文件夹。点击'Results'标签页预览数据。输出应为长表格，每个事件一行。
**导出事件日志：**核验结果后，点击结果页的'Export'链接，选择'CSV File'格式。系统将下载事件日志文件，可直接用于上传。

配置

主题区域：优先使用“Procurement - Purchasing Real Time”。该主题区域包含与采购订单、请购、供应商、收货和单据历史相关的维度和事实数据。
报表结构：需要使用“Union All”分析。联合中的每个组件查询代表一项业务活动（如“采购订单已创建”“采购订单已审批”），以确保每个活动在最终事件日志中对应独立的一行。
日期范围过滤：务必在关键日期字段上添加过滤条件，例如 "PO Headers All"."Creation Date"，以控制查询范围。常见的分析周期为3–6个月。初始测试可选择更短周期，或按单个采购订单号过滤。
业务单元过滤：如果组织包含多个业务单元，可在 "Business Unit"."Business Unit Name" 上添加过滤条件，仅抽取指定业务单元的数据。
前提条件：执行报表的用户需具备运行OTBI分析的权限（如BI Author角色），并可访问“Procurement - Purchasing Real Time”主题区域。数据可见性将受其在Oracle Fusion中的数据安全配置限制。

a 查询示例 config

/* This is a conceptual representation of the logic to be built in the OTBI Analysis using the 'Union All' feature. 
Each SELECT statement corresponds to a separate criteria block in the OTBI analysis. */

-- 1. Purchase Requisition Created
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Created' AS ActivityName,
  "Requisition Headers"."Creation Date" AS EventTime,
  "Requisition Headers"."Creation Date" AS EndTime,
  "Requisition Headers"."Created By" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Requisition Headers"."Creation Date" IS NOT NULL

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'REQUISITION' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 3. Purchase Order Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Created' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime,
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Creation Date" IS NOT NULL

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Submitted' AS ActivityName,
  "PO Headers All"."Submitted Date" AS EventTime,
  "PO Headers All"."Submitted Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- Submitter is often the creator
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Submitted Date" IS NOT NULL

UNION ALL

-- 5. Purchase Order Approved
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Rejected' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'REJECT'

UNION ALL

-- 7. Purchase Order Sent to Vendor (Communicated)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Sent to Vendor' AS ActivityName,
  "PO Headers All"."PO Communication Date" AS EventTime,
  "PO Headers All"."PO Communication Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- User who took action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."PO Communication Date" IS NOT NULL AND "PO Headers All"."Document Status" <> 'INCOMPLETE'

UNION ALL

-- 8. Purchase Order Acknowledged
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Acknowledged' AS ActivityName,
  "PO Headers All"."Acknowledged Date" AS EventTime,
  "PO Headers All"."Acknowledged Date" AS EndTime,
  NULL AS UserName, -- Often an automated or system action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Acknowledged Date" IS NOT NULL

UNION ALL

-- 9. Purchase Order Changed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Changed' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime, -- The creation date of the new revision
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Revision Number" > 0

UNION ALL

-- 10. Goods Receipt Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Receipt Created' AS ActivityName,
  "Receipt"."Creation Date" AS EventTime,
  "Receipt"."Creation Date" AS EndTime,
  "Receipt"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt"."Creation Date" IS NOT NULL

UNION ALL

-- 11. Goods Received
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Received' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE'

UNION ALL

-- 12. Quality Inspection Performed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Quality Inspection Performed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" IN ('ACCEPT', 'REJECT')

UNION ALL

-- 13. Goods Returned to Vendor
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Returned to Vendor' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RETURN TO VENDOR'

UNION ALL

-- 14. Services Delivered Confirmed (Modeled as a receipt against a Service line)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Services Delivered Confirmed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE' AND "PO Lines All"."Line Type" = 'Fixed Price Services'

UNION ALL

-- 15. Purchase Order Canceled
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Canceled' AS ActivityName,
  "PO Headers All"."Cancel Date" AS EventTime,
  "PO Headers All"."Cancel Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who cancelled
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Cancel Flag" = 'Y' AND "PO Headers All"."Cancel Date" IS NOT NULL

UNION ALL

-- 16. Purchase Order Finally Closed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Finally Closed' AS ActivityName,
  "PO Headers All"."Closed Date" AS EventTime,
  "PO Headers All"."Closed Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who closed
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Closed Code" = 'FINALLY CLOSED' AND "PO Headers All"."Closed Date" IS NOT NULL

步骤

**进入OTBI：**登录Oracle Fusion Applications，从主导航菜单进入'Reports and Analytics'工作区。点击'Browse Catalog'打开Business Intelligence目录。
**新建Analysis：**点击工具栏中的'Create'按钮并选择'Analysis'。系统会提示您选择主题域。
**选择主题域：**在可用主题域列表中选择'Procurement - Purchasing Real Time'。该主题域包含请购、采购订单与收货所需的数据。
**构建第一个条件块（Purchase Order Created）：**在'Criteria'标签页，将所需列拖入'Selected Columns'区域。针对首个活动'Purchase Order Created'，包含：
- "PO Headers All"."PO Number" 作为 PurchaseOrder
- 自定义文本公式 'Purchase Order Created' 作为 ActivityName
- "PO Headers All"."Creation Date" 作为 EventTime 和 EndTime
- "PO Headers All"."Created By" 作为 UserName
- "Requisitioning BU"."Department Name" 作为 DepartmentName
- "Supplier"."Supplier" 作为 VendorName
- "Base Measures"."Ordered Amount" 作为 PurchaseOrderTotalAmount
- "PO Schedules All"."Requested Delivery Date" 作为 RequestedDeliveryDate
- NULL 作为 ApproverName
**为所有活动创建Union合并：**要将每个活动以独立行记录，需要使用'Union All'将多个分析合并。其余15个活动分别建立类似的条件块。点击'Selected Columns'底部的'Combine results based on another analysis'图标，选择'Union All'。
**配置每个活动块：**根据需求中的16个活动逐一创建条件块。为每个块将 ActivityName 设为固定值（如'Purchase Order Approved'），并将相应的日期列映射到 EventTime。其余属性列保持一致；如某事件不适用，则填入 NULL（例如，事件'Purchase Order Created'的 ApproverName 为 NULL）。
映射关键日期与用户字段：
- **审批/驳回：**使用'Document History'文件夹获取操作日期和执行用户，用于 EventTime 与 ApproverName。
- **请购：**使用'Requisition Headers'文件夹获取请购创建与审批日期。
- **收货：**使用'Receipts'与'Receipt Line Transactions'文件夹处理'Goods Received'、'Goods Returned to Vendor'等事件。针对特定收货活动按'Transaction Type'进行筛选。
- **变更：**筛选 "PO Headers All"."Revision Number" > 0，并使用该修订的创建日期作为'Purchase Order Changed'活动的时间。
**添加筛选器：**切换到合并分析的'Filters'部分，添加全局筛选以限定数据范围。常见且有效的做法是在'PO Headers All'.'Creation Date'上提示用户输入时间区间（如最近6个月）。
**保存并运行分析：**将分析保存到共享文件夹。点击'Results'标签页预览数据。输出应为长表格，每个事件一行。
**导出事件日志：**核验结果后，点击结果页的'Export'链接，选择'CSV File'格式。系统将下载事件日志文件，可直接用于上传。

配置

主题区域：优先使用“Procurement - Purchasing Real Time”。该主题区域包含与采购订单、请购、供应商、收货和单据历史相关的维度和事实数据。
报表结构：需要使用“Union All”分析。联合中的每个组件查询代表一项业务活动（如“采购订单已创建”“采购订单已审批”），以确保每个活动在最终事件日志中对应独立的一行。
日期范围过滤：务必在关键日期字段上添加过滤条件，例如 "PO Headers All"."Creation Date"，以控制查询范围。常见的分析周期为3–6个月。初始测试可选择更短周期，或按单个采购订单号过滤。
业务单元过滤：如果组织包含多个业务单元，可在 "Business Unit"."Business Unit Name" 上添加过滤条件，仅抽取指定业务单元的数据。
前提条件：执行报表的用户需具备运行OTBI分析的权限（如BI Author角色），并可访问“Procurement - Purchasing Real Time”主题区域。数据可见性将受其在Oracle Fusion中的数据安全配置限制。

a 查询示例 config

/* This is a conceptual representation of the logic to be built in the OTBI Analysis using the 'Union All' feature. 
Each SELECT statement corresponds to a separate criteria block in the OTBI analysis. */

-- 1. Purchase Requisition Created
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Created' AS ActivityName,
  "Requisition Headers"."Creation Date" AS EventTime,
  "Requisition Headers"."Creation Date" AS EndTime,
  "Requisition Headers"."Created By" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Requisition Headers"."Creation Date" IS NOT NULL

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'REQUISITION' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 3. Purchase Order Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Created' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime,
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Creation Date" IS NOT NULL

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Submitted' AS ActivityName,
  "PO Headers All"."Submitted Date" AS EventTime,
  "PO Headers All"."Submitted Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- Submitter is often the creator
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Submitted Date" IS NOT NULL

UNION ALL

-- 5. Purchase Order Approved
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Rejected' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'REJECT'

UNION ALL

-- 7. Purchase Order Sent to Vendor (Communicated)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Sent to Vendor' AS ActivityName,
  "PO Headers All"."PO Communication Date" AS EventTime,
  "PO Headers All"."PO Communication Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- User who took action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."PO Communication Date" IS NOT NULL AND "PO Headers All"."Document Status" <> 'INCOMPLETE'

UNION ALL

-- 8. Purchase Order Acknowledged
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Acknowledged' AS ActivityName,
  "PO Headers All"."Acknowledged Date" AS EventTime,
  "PO Headers All"."Acknowledged Date" AS EndTime,
  NULL AS UserName, -- Often an automated or system action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Acknowledged Date" IS NOT NULL

UNION ALL

-- 9. Purchase Order Changed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Changed' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime, -- The creation date of the new revision
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Revision Number" > 0

UNION ALL

-- 10. Goods Receipt Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Receipt Created' AS ActivityName,
  "Receipt"."Creation Date" AS EventTime,
  "Receipt"."Creation Date" AS EndTime,
  "Receipt"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt"."Creation Date" IS NOT NULL

UNION ALL

-- 11. Goods Received
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Received' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE'

UNION ALL

-- 12. Quality Inspection Performed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Quality Inspection Performed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" IN ('ACCEPT', 'REJECT')

UNION ALL

-- 13. Goods Returned to Vendor
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Returned to Vendor' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RETURN TO VENDOR'

UNION ALL

-- 14. Services Delivered Confirmed (Modeled as a receipt against a Service line)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Services Delivered Confirmed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE' AND "PO Lines All"."Line Type" = 'Fixed Price Services'

UNION ALL

-- 15. Purchase Order Canceled
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Canceled' AS ActivityName,
  "PO Headers All"."Cancel Date" AS EventTime,
  "PO Headers All"."Cancel Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who cancelled
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Cancel Flag" = 'Y' AND "PO Headers All"."Cancel Date" IS NOT NULL

UNION ALL

-- 16. Purchase Order Finally Closed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Finally Closed' AS ActivityName,
  "PO Headers All"."Closed Date" AS EventTime,
  "PO Headers All"."Closed Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who closed
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Closed Code" = 'FINALLY CLOSED' AND "PO Headers All"."Closed Date" IS NOT NULL

步骤

进入 Oracle BI Publisher：登录你的 Oracle Fusion 环境。通过 Navigator 菜单进入 Tools > Reports and Analytics。点击 'Browse Catalog' 打开 BI Publisher 界面。
创建新的 Data Model：在 BI Publisher 的目录中，定位到合适的文件夹（如 Shared Folders > Custom）。点击 'New' 下拉菜单，选择 'Data Model'。
定义 SQL Query 数据集：在 Data Model 编辑器中，点击“+”图标新建数据集并选择 'SQL Query'。
输入 SQL Query：在 'New Data Set' 对话框中，填写名称（如 'ProcessMind_P2P_PO_Extraction'）。在 'Data Source' 选择你的 Oracle Fusion 应用数据库（如 'FSCM'）。将本文档 'query' 部分的完整 SQL 复制并粘贴到 SQL Query 文本区。
定义参数：查询使用 :P_START_DATE 和 :P_END_DATE 作为筛选的绑定变量。BI Publisher 会自动识别它们。你可以在数据模型的 'Parameters' 区域中配置默认值或创建值列表，例如将 Data Type 设为 'Date'。
预览并保存数据：打开 'Data' 选项卡。你可以输入日期并点击 'View'，查看提取数据的样例。确认无误后，将数据模型保存到你的自定义文件夹。
创建报表：点击右上角 'Create Report' 按钮，进入报表创建向导。
配置报表布局：在向导中选择 'Use Data Model' 选项，选择 'Table' 布局。将数据源中的所有列拖放到表格中。无需复杂格式设置，本报表用于原始数据导出。点击 'Finish' 并以清晰的名称保存报表。
运行报表：打开新创建的报表。系统会提示输入起止日期参数。填入所需时间范围并点击 'Apply'。
导出事件日志：报表运行完成后，点击右上角 'Actions' 菜单（齿轮图标），选择 'Export'，再选择 'CSV'。文件将下载至你的电脑。
准备上传：打开 CSV 文件，核对列名是否与 ProcessMind 要求的属性一致：PurchaseOrder、ActivityName、EventTime 等。确保日期格式统一。现在即可上传到 ProcessMind 应用。

配置

数据源：在BI Publisher数据模型中，将Oracle Fusion主应用数据库（通常名为“FSCM”等）设为数据源。
日期范围参数：查询包含两个必填参数 :P_START_DATE 和 :P_END_DATE。建议按3–6个月分批抽取数据，以保障系统性能。这两个参数用于按事件发生日期过滤。
数据筛选：可在每个子查询的WHERE子句中添加额外的过滤条件以缩小范围。常见条件包括PRC_BU_ID（采购业务单元）或POH.TYPE_LOOKUP_CODE（PO类型，例如“STANDARD”）。
用户权限：运行报表的用户必须拥有对查询涉及的所有表的明确读取权限，涵盖采购（PO_）、收货（RCV_）、请购（POR_）、供应商（POZ_）和用户（PER_）模块。通常需要BIAdministrator或自定义的BI角色。
性能：在数据量很大时，报表运行可能耗时较长。建议安排在业务低峰时段执行，降低对系统性能的影响；若性能问题反复出现，可考虑为关键列建立索引。

a 查询示例 sql

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Created' AS "ActivityName",
    PRH.CREATION_DATE AS "EventTime",
    PRH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    PRL.NEED_BY_DATE AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    POR_REQUISITION_HEADERS_ALL PRH
JOIN
    POR_REQUISITION_LINES_ALL PRL ON PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
LEFT JOIN
    PER_USERS PU ON PRH.CREATED_BY = PU.USERNAME
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PRH.DELIVER_TO_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    PRH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Approved' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    PU_PERFORMER.USERNAME AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    POR_REQUISITION_HEADERS_ALL PRH ON PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID
JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'REQUISITION'
    AND PAH.ACTION_CODE = 'APPROVE'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Created' AS "ActivityName",
    POH.CREATION_DATE AS "EventTime",
    POH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    (SELECT SUM(POL.AMOUNT) FROM PO_LINES_ALL POL WHERE POL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "PurchaseOrderTotalAmount",
    (SELECT MIN(PLL.NEED_BY_DATE) FROM PO_LINE_LOCATIONS_ALL PLL WHERE PLL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PO_DISTRIBUTIONS_ALL PDA ON POH.PO_HEADER_ID = PDA.PO_HEADER_ID
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PDA.PJC_EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    POH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')
    AND ROWNUM = 1

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE PAH.ACTION_CODE
        WHEN 'SUBMIT' THEN 'Purchase Order Submitted'
        WHEN 'APPROVE' THEN 'Purchase Order Approved'
        WHEN 'REJECT' THEN 'Purchase Order Rejected'
        WHEN 'ACCEPT' THEN 'Purchase Order Acknowledged'
        WHEN 'CANCEL' THEN 'Purchase Order Canceled'
        WHEN 'FINALLY CLOSE' THEN 'Purchase Order Finally Closed'
    END AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    CASE WHEN PAH.ACTION_CODE IN ('APPROVE', 'REJECT') THEN PU_PERFORMER.USERNAME ELSE NULL END AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    PO_HEADERS_ALL POH ON PAH.OBJECT_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'PO'
    AND PAH.ACTION_CODE IN ('SUBMIT', 'APPROVE', 'REJECT', 'ACCEPT', 'CANCEL', 'FINALLY CLOSE')
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Sent to Vendor' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PER_USERS PU ON POH.SOLDTO_CONTACT_ID = PU.PERSON_ID
JOIN
    (SELECT OBJECT_ID, MIN(ACTION_DATE) AS ACTION_DATE FROM PO_ACTION_HISTORY WHERE OBJECT_TYPE_CODE = 'PO' AND ACTION_CODE = 'APPROVE' GROUP BY OBJECT_ID) PAH ON POH.PO_HEADER_ID = PAH.OBJECT_ID
WHERE
    POH.DOCUMENT_STATUS = 'OPEN'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Changed' AS "ActivityName",
    POH.LAST_UPDATE_DATE AS "EventTime",
    POH.LAST_UPDATE_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.LAST_UPDATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    POH.REVISION_NUM > 0
    AND POH.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Goods Receipt Created' AS "ActivityName",
    RSH.CREATION_DATE AS "EventTime",
    RSH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_SHIPMENT_HEADERS RSH
JOIN
    PO_HEADERS_ALL POH ON RSH.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RSH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON RSH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RSH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE RT.TRANSACTION_TYPE
        WHEN 'RECEIVE' THEN 'Goods Received'
        WHEN 'ACCEPT' THEN 'Quality Inspection Performed'
        WHEN 'RETURN TO VENDOR' THEN 'Goods Returned to Vendor'
        WHEN 'DELIVER' THEN 'Services Delivered Confirmed' -- Using DELIVER for services as a proxy
    END AS "ActivityName",
    RT.TRANSACTION_DATE AS "EventTime",
    RT.TRANSACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_TRANSACTIONS RT
JOIN
    PO_HEADERS_ALL POH ON RT.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RT.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RT.TRANSACTION_TYPE IN ('RECEIVE', 'ACCEPT', 'RETURN TO VENDOR', 'DELIVER')
    AND RT.TRANSACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

步骤

准备Python环境：确保已安装Python 3.6或更高版本。在命令行或终端运行pip install requests安装所需的requests库。该库用于向Oracle Fusion REST API发起HTTP请求。
在Oracle Fusion中配置安全性：在Oracle Identity Cloud Service (IDCS)中创建专用的集成用户，并分配具备读取所需业务对象权限的角色。关键角色包括Procurement Manager、Requisition Self Service User和Warehouse Operator，以分别访问采购订单、请购与收货API。记录用户名、密码以及Oracle Fusion实例URL（如https://[instance].fa.em2.oraclecloud.com）。
设置Python脚本：将提供的Python脚本复制为extract_p2p_events.py。打开文件，在顶部配置区填写您的Oracle Fusion URL、用户名与密码，并设置数据抽取周期的START_DATE与END_DATE。
理解脚本逻辑：脚本先进行认证以获取会话令牌，然后调用purchaseOrders端点，按指定创建日期范围过滤。该调用会自动处理分页，获取期间内创建的所有采购订单。
遍历并丰富数据：脚本遍历每个获取到的采购订单。对每个PO生成'Purchase Order Created'事件，随后针对该PO发起定向API调用以拉取其他事件所需的数据。
获取审批历史：对每个PO查询approvals子资源（.../child/approvals），提取工作流事件，如'Submitted'、'Approved'与'Rejected'，使用响应中的ActionCode与ActionDate。
获取请购数据：检查PO行以找到关联的RequisitionNumber，再调用purchaseRequisitions端点获取该请购的创建与审批事件，并将其关联到采购订单的案例ID。
获取收货数据：调用receivingReceipts端点并以当前采购订单的POHeaderId进行过滤。基于交易生成'Goods Receipt Created'、'Goods Received'、'Quality Inspection Performed'、'Goods Returned to Vendor'等事件。
汇总并转换事件：将来自各端点的数据转换为标准的扁平结构。每个动作对应列表中的一行，即事件日志。将API响应字段映射到所需属性名（PurchaseOrder、ActivityName、EventTime等）。
执行脚本：在终端运行python extract_p2p_events.py。脚本会在控制台输出进度，完成后在同目录生成p2p_event_log.csv。
上传前检查：打开生成的CSV文件，核对列是否符合ProcessMind要求：PurchaseOrder、ActivityName、EventTime及推荐的其他属性。确保日期格式一致（建议ISO 8601）。文件即可上传。

配置

前提条件：需要一个Oracle Fusion集成用户，具备读取采购订单、请购单和收货相关对象的权限。运行脚本的机器需安装Python 3.6+和requests库。
API 端点：脚本主要使用以下REST API资源（版本 11.13.18.05）：
- /fscmRestApi/resources/11.13.18.05/purchaseOrders
- /fscmRestApi/resources/11.13.18.05/purchaseOrders/{id}/child/approvals
- /fscmRestApi/resources/11.13.18.05/purchaseRequisitions
- /fscmRestApi/resources/11.13.18.05/receivingReceipts
认证：脚本使用集成用户凭据进行Basic Authentication连接Oracle Fusion实例。会话管理由脚本内部处理。
日期范围：脚本中的START_DATE和END_DATE变量用于控制抽取窗口。强烈建议先从较小范围开始，例如1–3个月，以控制抽取时长和API负载。该过滤应用于采购订单的创建日期。
过滤：脚本通过q查询参数过滤记录，主要按采购订单的CreationDate过滤。对于大型环境，可在ProcurementBU（采购业务单元）等字段上增加条件以优化性能。
分页：脚本可自动处理API分页，使用limit和offset参数，并检查响应中的hasMore标识，确保取回该查询的全部记录。
性能：脚本会针对每个采购订单发起多次API调用。若选定日期范围内的PO数量以千计，抽取可能耗时较长。建议在业务低峰时段执行。

a 查询示例 python

import requests
import json
import csv
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
FUSION_URL = class="hl-string">"https://[your_instance_url].fa.em2.oraclecloud.com"
USERNAME = os.environ.get(class="hl-string">"FUSION_USERNAME", class="hl-string">"your_username")
PASSWORD = os.environ.get(class="hl-string">"FUSION_PASSWORD", class="hl-string">"your_password")

class=class="hl-string">"hl-comment"># Date range for the extraction (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-03-31T23:59:59Z"

class=class="hl-string">"hl-comment"># API settings
PAGE_SIZE = 500
OUTPUT_FILE = class="hl-string">"p2p_event_log.csv"

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def get_api_data(endpoint, params=None):
    class="hl-string">"""Generic function to handle pagination and data retrieval from Fusion REST API."""
    all_items = []
    offset = 0
    while True:
        if params is None:
            params = {}
        params[class="hl-string">'limit'] = PAGE_SIZE
        params[class="hl-string">'offset'] = offset

        try:
            response = requests.get(
                FUSION_URL + endpoint,
                auth=(USERNAME, PASSWORD),
                params=params,
                headers={class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'}
            )
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_items.extend(items)

            if not data.get(class="hl-string">'hasMore', False):
                break
            offset += PAGE_SIZE
        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error fetching {endpoint}: {e}")
            print(fclass="hl-string">"Response content: {e.response.text}")
            break
        except Exception as e:
            print(fclass="hl-string">"An error occurred fetching {endpoint}: {e}")
            break
    return all_items

def add_event(event_log, po_data, activity, timestamp, user, approver=None):
    class="hl-string">"""Helper to format and add an event to the log."""
    if not timestamp:
        return class=class="hl-string">"hl-comment"># Skip events without a timestamp
    
    event = {
        class="hl-string">'PurchaseOrder': po_data.get(class="hl-string">'OrderNumber'),
        class="hl-string">'ActivityName': activity,
        class="hl-string">'EventTime': timestamp,
        class="hl-string">'EndTime': timestamp,
        class="hl-string">'UserName': user,
        class="hl-string">'DepartmentName': po_data.get(class="hl-string">'BillToBU'),
        class="hl-string">'VendorName': po_data.get(class="hl-string">'Supplier'),
        class="hl-string">'PurchaseOrderTotalAmount': po_data.get(class="hl-string">'Total'),
        class="hl-string">'RequestedDeliveryDate': None, class=class="hl-string">"hl-comment"># Often on the line level
        class="hl-string">'ApproverName': approver
    }
    event_log.append(event)

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    event_log = []
    print(class="hl-string">"Starting P2P event log extraction...")

    class=class="hl-string">"hl-comment"># 1. Get Purchase Orders within the date range
    print(fclass="hl-string">"Fetching Purchase Orders created between {START_DATE} and {END_DATE}...")
    po_query = fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'"
    purchase_orders = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders", {class="hl-string">'q': po_query, class="hl-string">'fields': class="hl-string">'OrderNumber,CreationDate,POHeaderId,CreatedBy,Supplier,Total,POStatus,LastUpdateDate,BillToBU,VersionNumber,ChangeOrderDescription,lines'})
    print(fclass="hl-string">"Found {len(purchase_orders)} Purchase Orders.")

    unique_requisition_nums = set()
    for po in purchase_orders:
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                if line.get(class="hl-string">'RequisitionNumber'):
                    unique_requisition_nums.add(line[class="hl-string">'RequisitionNumber'])

    class=class="hl-string">"hl-comment"># 2. Fetch Requisition data in a batch for efficiency
    print(fclass="hl-string">"Fetching data for {len(unique_requisition_nums)} related requisitions...")
    requisitions_data = {}
    if unique_requisition_nums:
        class=class="hl-string">"hl-comment"># API has a limit on URL length, so we may need to batch this for very large sets
        req_query = class="hl-string">" or ".join([fclass="hl-string">"RequisitionNumber='{req_num}'" for req_num in unique_requisition_nums])
        reqs = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseRequisitions", {class="hl-string">'q': req_query})
        for req in reqs:
            requisitions_data[req[class="hl-string">'RequisitionNumber']] = req

    class=class="hl-string">"hl-comment"># 3. Process each Purchase Order to create events
    for i, po in enumerate(purchase_orders):
        print(fclass="hl-string">"Processing PO {i+1}/{len(purchase_orders)}: {po.get('OrderNumber')}")

        class=class="hl-string">"hl-comment"># PO Events
        add_event(event_log, po, class="hl-string">'Purchase Order Created', po.get(class="hl-string">'CreationDate'), po.get(class="hl-string">'CreatedBy'))
        if po.get(class="hl-string">'VersionNumber', 0) > 1:
            add_event(event_log, po, class="hl-string">'Purchase Order Changed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CANCELED':
            add_event(event_log, po, class="hl-string">'Purchase Order Canceled', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED_FOR_RECEIVING' or po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED':
            add_event(event_log, po, class="hl-string">'Purchase Order Finally Closed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))

        class=class="hl-string">"hl-comment"># Requisition Events (linked to this PO)
        po_req_nums = set()
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                 if line.get(class="hl-string">'RequisitionNumber'):
                    po_req_nums.add(line[class="hl-string">'RequisitionNumber'])
        
        for req_num in po_req_nums:
            req = requisitions_data.get(req_num)
            if req:
                add_event(event_log, po, class="hl-string">'Purchase Requisition Created', req.get(class="hl-string">'CreationDate'), req.get(class="hl-string">'EnteredBy'))
                if req.get(class="hl-string">'RequisitionStatus') == class="hl-string">'APPROVED':
                     class=class="hl-string">"hl-comment"># Note: This timestamp is the final approval, not an event stream
                     add_event(event_log, po, class="hl-string">'Purchase Requisition Approved', req.get(class="hl-string">'LastUpdateDate'), req.get(class="hl-string">'LastUpdatedBy'))

        class=class="hl-string">"hl-comment"># Approval History Events
        approvals = get_api_data(fclass="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders/{po['POHeaderId']}/child/approvals")
        is_first_approval = True
        for action in approvals:
            activity = None
            user = action.get(class="hl-string">'PerformedBy')
            approver = user if action.get(class="hl-string">'ActionCode') in [class="hl-string">'APPROVE', class="hl-string">'REJECT'] else None
            if action.get(class="hl-string">'ActionCode') == class="hl-string">'SUBMIT':
                activity = class="hl-string">'Purchase Order Submitted'
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'APPROVE':
                activity = class="hl-string">'Purchase Order Approved'
                if is_first_approval:
                    add_event(event_log, po, class="hl-string">'Purchase Order Sent to Vendor', action.get(class="hl-string">'ActionDate'), user, approver)
                    is_first_approval = False
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'REJECT':
                activity = class="hl-string">'Purchase Order Rejected'
            
            if activity:
                add_event(event_log, po, activity, action.get(class="hl-string">'ActionDate'), user, approver)

        class=class="hl-string">"hl-comment"># Receiving Events
        receipts_query = fclass="hl-string">"POHeaderId={po['POHeaderId']}"
        receipts = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/receivingReceipts", {class="hl-string">'q': receipts_query})
        for receipt in receipts:
            add_event(event_log, po, class="hl-string">'Goods Receipt Created', receipt.get(class="hl-string">'CreationDate'), receipt.get(class="hl-string">'CreatedBy'))
            class=class="hl-string">"hl-comment"># Lines contain the detailed transactions
            if receipt.get(class="hl-string">'lines'):
                for rcv_line in receipt[class="hl-string">'lines']:
                    tx_type = rcv_line.get(class="hl-string">'TransactionType')
                    tx_time = rcv_line.get(class="hl-string">'TransactionDate')
                    tx_user = receipt.get(class="hl-string">'CreatedBy') class=class="hl-string">"hl-comment"># User is on header
                    activity = None
                    if tx_type == class="hl-string">'RECEIVE':
                        activity = class="hl-string">'Goods Received'
                    elif tx_type == class="hl-string">'INSPECT':
                        activity = class="hl-string">'Quality Inspection Performed'
                    elif tx_type == class="hl-string">'RETURN TO VENDOR':
                        activity = class="hl-string">'Goods Returned to Vendor'
                    elif tx_type == class="hl-string">'MATCH' and po.get(class="hl-string">'OrderTypeCode') == class="hl-string">'SERVICES': class=class="hl-string">"hl-comment"># Approximation for services
                        activity = class="hl-string">'Services Delivered Confirmed'
                    
                    if activity:
                        add_event(event_log, po, activity, tx_time, tx_user)

    class=class="hl-string">"hl-comment"># 4. Write to CSV
    if not event_log:
        print(class="hl-string">"No events generated. Exiting.")
        return

    print(fclass="hl-string">"Writing {len(event_log)} events to {OUTPUT_FILE}...")
    class=class="hl-string">"hl-comment"># Sort events by case ID and timestamp
    event_log.sort(key=lambda x: (x[class="hl-string">'PurchaseOrder'], x[class="hl-string">'EventTime']))

    with open(OUTPUT_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=event_log[0].keys())
        writer.writeheader()
        writer.writerows(event_log)

    print(class="hl-string">"Extraction complete.")

if __name__ == class="hl-string">"__main__":
    main()

数据模板：采购到付款 - 采购订单

您的采购到付款-采购订单数据模板

采购到付款 - 采购订单属性

采购到付款 - 采购订单活动

提取指南

用于导出采购订单数据的Oracle OTBI报表

步骤

配置

a 查询示例 config

Oracle BI Cloud Connector（BICC）批量数据导出

步骤

配置

a 查询示例 config

用于抽取PO数据的Oracle BI Publisher SQL查询

步骤

配置

a 查询示例 sql

通过 Oracle Fusion REST APIs 的 Python 脚本抽取

步骤

配置

a 查询示例 python

准备好开始了吗？

立即优化您的采购到付款（P2P）-采购订单流程