您的应付账款发票处理数据模板

SAP ECC

← 返回改进指南

应付账款发票处理属性 (21) 应付账款发票处理活动 (13) 提取指南 (4)

您的应付账款发票处理数据模板

这份完整的数据模板包含有效分析应付账款发票处理所需的关键信息，说明应收集的重要数据属性、应跟踪的核心流程活动，以及如何直接从SAP ECC系统提取数据的实用指南。使用此模板可确保您收集完整流程分析所需的全部信息。

建议收集的属性
需跟踪的关键活动
提取指南

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

应付账款发票处理属性

以下是建议纳入事件日志的数据字段，用于全面分析您的应付账款发票处理流程。

3 必需 9 建议 9 可选

	名称	说明
	事件时间 EventTime	特定活动或事件发生的准确日期和时间。
说明事件时间是与流程中每项活动关联的时间戳。该数据对于按时间顺序排列事件以及开展所有基于时间的分析都至关重要。在SAP中，这类信息通常存储在创建字段（例如BKPF-CPUDT）或变更日志表（CDHDR-UDATE和CDHDR-UTIME）中。此属性是计算周期时间、处理时间和活动间等待时间等关键绩效指标的基础。它支持按时间分析流程绩效，帮助识别瓶颈出现的时间，并用于检查是否符合服务级别协议（SLA）或付款条件。为什么重要此时间戳是计算所有基于持续时间的指标的关键，包括周期时间和瓶颈分析，是绩效分析的基础。获取位置来自SAP表，例如用于记录创建时间的BKPF（CPUDT/CPUTM），或用于记录变更时间的CDHDR（UDATE/UTIME）。示例 2023-10-01T10:00:00Z2023-10-02T14:35:10Z2023-10-15T09:12:00Z
	发票 InvoiceNumber	每个发票凭证的唯一标识符，用作跟踪发票从接收到付款全过程的主要案例标识。
说明发票编号是一个复合键，通常由SAP中的公司代码（BUKRS）、凭证编号（BELNR）和会计年度（GJAHR）组合而成。该唯一组合可确保每张发票在整个系统中都能被明确识别。在流程挖掘中，此属性至关重要。它将创建、过账、审批和付款等所有相关事件与活动关联到同一个完整的流程实例。按发票分析可以完整呈现发票生命周期的端到端过程，这对于计算周期时间、识别变体和了解返工循环不可或缺。为什么重要这是连接所有发票相关活动的核心案例ID，可对每张发票的应付账款流程进行完整的端到端分析。获取位置由SAP表BKPF（会计凭证抬头）中的BUKRS、BELNR和GJAHR字段构成。示例 1000-1900000001-20232000-5100000055-20231000-1900000042-2024
	活动 Activity	发票处理生命周期中发生的特定业务步骤或事件的名称。
说明此属性描述应付账款流程中的单个任务或状态变更，例如“发票已暂存”“发票已转交审批”或“付款已清账”。这些活动是流程图的基本组成部分，通常从SAP中的变更日志、状态字段或工作流日志中提取。分析这些活动的顺序和持续时间是流程挖掘的核心。它有助于可视化流程、识别步骤之间的瓶颈、衡量返工循环的频率，并将实际流程执行情况与标准操作规程进行比较。活动定义的粒度会直接影响可获得洞察的深度。为什么重要活动构成流程图的骨架，可用于可视化流程、识别瓶颈并分析偏差。获取位置根据发票凭证的变更，从事务代码（BKPF-TCODE）、工作流日志（SWW_WI2OBJ）或变更日志（CDHDR/CDPOS表）中提取。示例发票凭证已创建发票已批准发票已通过付款清账已设置付款冻结
	付款冻结原因 PaymentBlockReason	表示发票被禁止付款原因的代码。
说明当发票存在差异或正在审核时，可以设置付款冻结，防止其被纳入付款运行。此属性提供冻结原因，例如数量不匹配或价格差异。该信息对于异常分析至关重要。它有助于对付款延迟和返工的根本原因进行分类，支持“异常与差异分析”仪表板。了解最常见的冻结原因，可以针对性地改进流程并减少异常。为什么重要提供付款延迟的根本原因，支持针对性分析，以减少异常、返工和发票处理时间。获取位置来自SAP表BSEG（会计凭证分段）的ZLSPR字段。示例 RIA
	付款条件 PaymentTerms	定义付款条件的代码，例如到期日和可享受的折扣。
说明付款条件是SAP中的预定义规则，用于确定发票到期日和现金折扣的计算方式。它们会分配给供应商主数据，也可以在单张发票中指定。此属性对于“付款合规与折扣”仪表板及“提前付款折扣获取率”KPI至关重要。通过分析付款条件，企业可以发现获取折扣的机会，了解付款排程的财务影响，并确保合规。为什么重要定义付款到期日和折扣规则，对于财务优化和合规监控至关重要。获取位置来自SAP表BSEG（会计凭证分段）的ZTERM字段。示例 Z0010001NT30
	供应商名称 VendorName	提交发票的供应商名称。
说明此属性包含供应商的正式名称。在SAP中，供应商主数据（表LFA1）通过凭证抬头或行项目中存储的供应商编号（LIFNR）与发票凭证关联。按供应商分析对于“供应商付款绩效”仪表板至关重要。它支持跟踪特定供应商的发票处理时间、付款及时性和异常率。这些洞察有助于管理供应商关系，识别发票质量持续较差的供应商，并协商更有利的付款条件。为什么重要支持按供应商开展绩效分析，帮助管理供应商关系并识别特定供应商相关的问题。获取位置来自SAP表LFA1的NAME1字段，通过BKPF或BSEG中的供应商编号（LIFNR）关联。示例 Global Office Supplies Inc.Tech Solutions LLCReliable Logistics Corp.
	公司代码 CompanyCode	表示SAP组织中某个法人实体或公司的唯一键。
说明公司代码是SAP财务中的基础组织单位，代表独立的会计核算单位。包括发票在内的所有财务交易都会过账到特定公司代码。此属性对于按法人实体细分流程分析至关重要。它支持比较企业不同部分的流程绩效、合规率和效率。对于拥有多个子公司的大型跨国企业，这一点尤为重要。为什么重要支持按法人实体细分流程分析，从而比较不同业务单元的绩效。获取位置来自SAP表BKPF（会计凭证抬头）的BUKRS字段。示例 10002100US01
	发票到期日 InvoiceDueDate	根据付款条件计算出的发票应付款日期。
说明发票到期日表示供应商付款的截止日期，按约定条件付款即可保持合规。在SAP中，该日期通常不是单独存储的字段，而是根据凭证基准日期（BSEG-ZFBDT）和发票关联的付款条件（BSEG-ZTERM）计算得出。此日期对于“付款合规与折扣”仪表板至关重要，可用于计算逾期付款率等KPI，并识别存在逾期风险的发票。结合该日期进行分析，有助于确定工作优先级并改善供应商关系。为什么重要这是监控付款合规、计算逾期KPI和获取提前付款折扣的关键日期。获取位置根据到期日计算基准日期（BSEG-ZFBDT）和付款条件码（BSEG-ZTERM）得出。示例 2023-10-312023-11-152024-01-10
	发票含税总额 InvoiceGrossAmount	发票的含税总金额，包括原始凭证币种中的税费及其他费用。
说明此属性表示发票的总财务价值，是了解待处理发票财务影响和特征的关键数据。在SAP中，该金额可能根据凭证类型存储于不同表中，例如财务凭证的BSEG表。在分析中，发票金额可用于优先处理高价值发票、了解应付账款流程的财务吞吐量并细分分析。例如，可以分析高价值发票是否具有更长的周期时间，或是否遵循不同的审批路径。它也是财务KPI的基础数据。为什么重要为流程提供财务背景，支持基于金额的分析、高价值发票优先级排序和财务KPI计算。获取位置来自SAP行项目表，例如BSEG的WRBTR字段（凭证币种金额）。可能需要将多个行项目汇总到抬头层级。示例 1500.0012550.75980.50
	处理用户 ProcessorUser	执行活动的员工SAP用户名，例如过账或修改发票的用户。
说明此属性标识负责执行特定流程步骤的用户。在SAP中，该用户可能是创建凭证的用户（例如BKPF-USNAM），也可能是执行特定变更的用户（例如CDHDR-USERNAME）。按处理用户分析对于了解团队绩效和工作量分配至关重要。通过跟踪每位用户处理的发票数量和平均活动时间，它有助于构建“应付账款处理人员工作量分布”仪表板。这些分析可以揭示培训需求、识别高绩效人员，并支持公平分配任务。为什么重要支持工作量分析、用户间绩效比较，以及培训机会或资源失衡情况的识别。获取位置来自SAP表CDHDR的USERNAME字段（用于变更），或BKPF的USNAM字段（用于凭证录入）。示例 AJONESSMITHBCWILLIAMS
	审批人 Approver	负责审批发票付款的用户或角色。
说明此属性标识执行“发票已批准”活动的人员。在SAP中，这通常由工作流系统管理，审批人的ID会记录在工作流历史中。按审批人分析是“发票审批瓶颈分析”仪表板的核心。它有助于衡量个人或团队的审批时间，识别持续处理缓慢的审批人，并了解审批层级中的工作量分布。这些数据对于优化审批工作流至关重要，因为审批通常是主要瓶颈。为什么重要识别负责审批的人员，支持按个人或团队分析审批周期时间和瓶颈。获取位置通常位于SAP Business Workflow表（例如SWW_WI2OBJ、SWWLOG）中，通过将工作流项目与发票对象关联，并识别审批步骤的用户获得。示例 DMARTINLCHENFINMAN_ROLE
	采购订单编号 PurchaseOrderNumber	发票所对应采购订单的标识符。
说明采购订单（PO）编号将发票与采购流程关联起来。对于基于PO的发票，该关联是完成采购订单、收货单和发票三方匹配的基础。此属性是“PO/收货匹配绩效”仪表板的关键数据。分析PO是否存在及其匹配情况，有助于区分不同发票类型（PO发票与非PO发票），并衡量匹配流程的效率。该环节的延迟是流程瓶颈的常见来源。为什么重要将发票与采购流程关联，支持分析PO与非PO发票以及三方匹配流程的效率。获取位置来自SAP表BSEG（FI发票）或RSEG（MM发票）的EBELN字段。示例 450001712345000175894500018330
	最后数据更新时间 LastDataUpdate	表示该记录数据最近一次从源系统刷新时间的时间戳。
说明此属性标记最近一次从SAP ECC提取或更新数据的日期和时间。它不属于交易数据本身，而是在数据摄取过程中添加的元数据。它主要用于说明当前查看数据的新鲜度。这对于仪表板和报告至关重要，因为它可以告知用户分析数据的时效性，并帮助管理对近期交易是否已纳入的预期。为什么重要表示数据集的新鲜度，这对于了解流程洞察和仪表板的时效性至关重要。获取位置这是在数据提取、转换和加载（ETL）过程中生成并添加的元数据。示例 2024-05-21T02:00:00Z2024-05-22T02:00:00Z2024-05-23T02:00:00Z
	凭证币种 DocumentCurrency	开具发票所使用的币种代码（例如USD、EUR）。
说明此属性指定发票金额所使用的币种，存储在SAP的凭证抬头层级。凭证币种是开展任何财务分析时正确解读金额的基础。它支持按币种筛选流程分析，也是将金额转换为统一本位币，以便标准化报告和汇总的前提。为什么重要为解读财务金额提供必要背景，并支持按币种分析或转换为标准币种。获取位置来自SAP表BKPF（会计凭证抬头）的WAERS字段。示例 USDEURGBP
	凭证类型 DocumentType	SAP中用于对会计凭证分类并控制其处理方式的代码。
说明凭证类型用于区分不同业务交易，例如供应商发票（KR）、贷项凭证（KG）或MM发票（RE）。该分类会影响编号范围以及可过账的账户类型等方面。在流程挖掘中，按凭证类型筛选可以开展更同质化的分析。例如，标准供应商发票（KR）流程可能与贷项凭证（KG）流程存在显著差异。分别分析可以避免汇总结果失真，并更准确地洞察具体子流程。为什么重要支持按交易类型（例如发票与贷项凭证）细分分析，从而获得更准确、更相关的流程洞察。获取位置来自SAP表BKPF（会计凭证抬头）的BLART字段。示例 KRREKG
	已损失折扣 DiscountLost	如果未享受可用的提前付款折扣，则为真的计算标记。
说明此属性用于识别以下发票：付款条件提供了提前付款折扣，但实际付款发生在折扣期限届满之后。系统通过比较付款日期与根据“付款条件”计算得出的折扣截止日期来确定该属性。这是“提前付款折扣获取率”这一关键财务KPI的重要指标，可帮助发现降低成本的机会。分析折扣损失的原因，例如审批周期过长、付款冻结，有助于以明确的投资回报率为流程改进项目提供依据。为什么重要突出显示错失的财务机会，直接支持“提前付款折扣获取率”KPI，并为流程改进提供明确的经济激励。获取位置计算字段：如果（付款条件提供折扣）且（付款日期＞折扣截止日期），则为TRUE，否则为FALSE。示例 truefalse
	收货单编号 GoodsReceiptNumber	与基于PO的发票相关的收货凭证标识符。
说明对于实物商品相关发票，收货（GR）凭证用于确认供应商已交付货物。该编号将发票与具体交付事件关联起来。此属性用于“PO/收货匹配绩效”仪表板。收货过账与发票过账之间的时间是关键指标。分析这一关联，有助于了解完整的三方匹配流程，并识别实物交付与财务结算之间的延迟。为什么重要完善PO、收货单和发票的三方匹配视图，支持分析货物交付与发票处理之间的延迟。获取位置来自SAP表RSEG（凭证项目，收货发票）的LFBNR字段（参考凭证编号）。示例 500000123450000015675000002100
	是否已冲销 IsReversed	表示发票凭证是否已冲销的布尔标记。
说明此属性标记已取消或冲销的发票，通常表示存在严重错误或流程失败。在SAP中，已冲销凭证会与冲销凭证关联。识别已冲销发票对于了解流程质量和失败率十分重要。它有助于将这些终止性异常从主流程中分离出来，并开展专门的根因分析，了解冲销发生的原因及预防方法。为什么重要标记已取消的发票，这是流程失败的重要指标，有助于分析重大错误的根本原因。获取位置检查该凭证编号（BKPF-BELNR）是否存在于其他凭证的BKPF-STBLG（冲销凭证）字段中。示例 truefalse
	是否自动化 IsAutomated	表示活动由系统或批处理用户执行，还是由人工用户执行的标记。
说明此布尔属性用于区分自动执行的活动（例如由批处理作业、EDI或OCR系统执行）与用户手动执行的活动。通常通过分析活动关联的用户名确定。这是“手动与自动化处理”仪表板的基础数据。它有助于衡量自动化计划的成效、计算人工干预率KPI，并识别进一步减少人工操作的机会。了解人工与系统的接触点，是提升效率的关键。为什么重要帮助衡量流程自动化水平、识别人工瓶颈并跟踪自动化计划的影响。获取位置通过检查用户ID（例如CDHDR-USERNAME）是否属于预定义的系统、批处理或服务账户列表得出。示例 truefalse
	是否逾期 IsOverdue	如果发票在计划到期日之后支付，则为真的计算标记。
说明此布尔属性通过比较“发票已通过付款清账”活动日期与“发票到期日”得出。如果付款日期晚于到期日，则该标记为真。此属性直接支持“逾期付款率”KPI和“付款合规与折扣”仪表板。它支持快速筛选和统计逾期付款，帮助量化问题规模。按供应商、公司代码等维度分析逾期发票特征，可以揭示根本原因。为什么重要直接衡量付款条件合规性，是逾期付款率KPI的基础，有助于改善供应商关系并避免处罚。获取位置计算字段：如果（付款日期＞发票到期日），则为TRUE，否则为FALSE。示例 truefalse
	源系统 SourceSystem	标识提取数据的SAP ECC源系统。
说明此属性指定数据来源的记录系统，例如SAP系统ID（SID）。在拥有多个ERP实例或混合使用多个系统的组织中，这一点尤为重要。在流程挖掘分析中，它有助于区分来自不同系统的数据，因为不同系统可能采用不同配置或存在流程差异。这样既可以开展系统间对比分析，也能确保数据血缘清晰，这对于数据治理和验证至关重要。为什么重要确保数据可追溯，并支持在多个SAP实例或其他源系统环境中开展流程分析。获取位置通常是表示源ECC实例SAP系统ID（SID）的静态值，在数据提取过程中添加。示例 ECC_PROD_EUSAP_US_01E5P

必需建议可选

应付账款发票处理活动

以下是建议采集的关键流程步骤和里程碑，用于准确发现并分析您的发票处理工作流。

6 建议 7 可选

	活动	说明
	发票凭证已创建	表示在SAP中创建发票凭证，无论该凭证是暂存还是已完整过账。通常这是发票的第一个带时间戳的事件，也是流程分析的起点。该事件从BKPF表中的凭证录入日期和时间获取。
为什么重要该活动是发票处理旅程的主要开始事件。分析从这一时点开始的耗时，有助于衡量总端到端周期时间，并识别初始数据录入延迟。获取位置该事件根据会计凭证抬头记录的创建时间戳推断得出。具体而言，录入日期（BKPF-CPUDT）与录入时间（BKPF-CPUTM）的组合构成该时间戳。采集使用凭证抬头表BKPF中的创建时间戳（CPUDT、CPUTM）。事件类型 inferred
	发票已冲销	已通过创建冲销凭证取消已过账的发票凭证。此操作抵销原发票的财务影响，代表流程的一种替代性且通常不理想的结束方式。
为什么重要冲销通常表示存在错误，例如数据录入错误或发票重复。跟踪冲销频率及原因，有助于发现提升数据质量和一次正确处理率的机会。获取位置从凭证抬头表（BKPF）中识别此事件。原凭证的冲销凭证编号（BKPF-STBLG）会被填充。事件时间为冲销凭证的过账日期。采集在BKPF.STBLG中查找冲销凭证，并从冲销凭证的过账日期获取事件时间。事件类型 explicit
	发票已批准	指定审批人已确认发票有效且可以付款。这可以是工作流系统中的明确事件，也可以根据移除付款冻结进行推断。这是付款前的关键里程碑。
为什么重要这是解除发票付款限制的关键里程碑。此步骤前后的延迟反映不同类型的问题，例如审批人无法及时处理或付款运行排程问题。获取位置在工作流系统中，这是一个明确事件。否则，通常根据移除付款冻结进行推断，并通过BSEG-ZLSPR字段的变更日志（CDHDR/CDPOS）记录。采集来自工作流系统的事件，或记录移除付款冻结的变更日志事件。事件类型 explicit
	发票已过账	发票已正式记入总账，形成财务负债。暂存凭证被转换为已过账凭证，或发票从创建时起即作为已过账凭证存在。这是财务会计中的基础步骤。
为什么重要过账是使发票成为正式负债的关键里程碑，也将数据录入阶段与流程中的主动付款管理阶段区分开来。获取位置从凭证抬头表BKPF中识别。已过账凭证的凭证状态（BKPF-BSTAT）为空，且具有有效的过账日期（BKPF-BUDAT）。事件时间为过账日期。采集对于BKPF-BSTAT不为“V”（暂存），或“V”已发生变更的凭证，使用BKPF.BUDAT。事件类型 explicit
	发票已通过付款清账	发票已全额支付，未清项目已与付款凭证完成清账。这标志着该发票应付账款流程成功完成。此事件通过清账凭证信息捕获。
为什么重要这是成功路径上的主要结束事件。到达此活动所需的时间，是衡量整体流程效率的关键指标，也是计算付款及时性和折扣获取情况的基础。获取位置当供应商行项目已填充清账凭证编号（BSEG-AUGBL）和清账日期（BSEG-AUGDT）时，即可识别此事件。清账日期为事件时间。采集当清账凭证（BSEG.AUGBL）已填充时，使用清账日期（BSEG.AUGDT）。事件类型 explicit
	已创建付款建议	发票已作为付款运行（事务代码F110）的一部分纳入付款建议清单。虽然这不是最终付款，但表示系统已有付款意图。系统根据到期日和付款条件选择发票。
为什么重要这是自动付款流程的第一步。付款建议与最终付款执行之间的延迟，可能反映付款运行审批或排程方面的问题。获取位置通过在付款建议表中查找发票凭证识别此事件，例如REGUP（付款程序已处理项目）表。运行日期（REGUP-LAUFD）可作为事件时间。采集在REGUP表中查找发票，并使用付款运行日期（LAUFD）。事件类型 explicit
	到期未付款	当当前日期超过发票净到期日，而发票尚未清账时，将产生此计算事件。它突出显示可能逾期或已经逾期的发票。这不是直接的系统事件。
为什么重要此活动对于监控逾期付款率KPI至关重要。它可以主动标记需要立即处理的发票，避免产生逾期付款费用并损害供应商关系。获取位置这是一个计算事件。通过比较当前日期与所有未清账发票项目的净到期日（BSEG-ZFBDT）得出。事件时间为到期日。采集通过比较当前日期与未清项目的BSEG.ZFBDT得出。事件类型 calculated
	发票已提交审批	发票已正式提交至审批工作流。此活动通常依赖外部工作流系统，或依赖SAP中的特定状态变更。这通常是审批子流程的起点。
为什么重要这标志着审批周期的开始。衡量此事件到“发票已批准”之间的时间，对于计算发票审批周期时间KPI以及识别审批人相关瓶颈至关重要。获取位置如果使用SAP Business Workflow，可从工作流日志（例如SWWLOG）中提取此事件。否则，这通常是一个概念性步骤，可根据因审批原因设置付款冻结来推断。采集从工作流日志中提取，或根据设置特定付款冻结进行推断。事件类型 explicit
	发票已暂存	表示发票已录入SAP，但尚未过账至总账。这是一项明确操作，便于后续完成或审批。抬头表中的凭证状态字段可表明发票是否处于暂存状态。
为什么重要暂存表示流程被有意暂停，通常是为了审核或等待缺失信息。跟踪该状态有助于识别发票正式过账前发生延迟的原因。获取位置在凭证抬头表BKPF中识别，当凭证状态（BKPF-BSTAT）为“V”（暂存）时触发。事件时间为凭证创建时间（BKPF-CPUDT、BKPF-CPUTM）。采集筛选BKPF.BSTAT = “V”的凭证。事件类型 explicit
	已移除付款冻结	此前设置的发票付款冻结已被移除，发票因此符合付款条件。这通常表示差异已解决或审批已完成。该事件从变更凭证表中提取。
为什么重要此活动表示异常已解决或审批已完成。冻结处于启用状态的持续时间代表返工或等待时间，是流程改进的重点领域。获取位置通过跟踪付款冻结码字段（BSEG-ZLSPR）的变更捕获此事件。CDHDR和CDPOS表中的变更日志会记录该字段被清空的时间。采集跟踪CDHDR/CDPOS中的更新记录，筛选BSEG-ZLSPR字段被改为空白的记录。事件类型 explicit
	已设置付款冻结	发票被主动禁止付款，通常是由于存在差异、等待审批或其他问题。这是设置在发票行项目上的明确状态。该事件可从变更凭证表中获取。
为什么重要该活动标志着异常或审批期间的开始。分析付款冻结的频率和持续时间，对于识别和解决流程瓶颈及争议至关重要。获取位置通过跟踪付款冻结键字段（BSEG-ZLSPR）的变更捕获该事件。CDHDR和CDPOS表中的变更日志会记录该字段被填充的时间。采集跟踪CDHDR/CDPOS中字段BSEG-ZLSPR对应条目的创建。事件类型 explicit
	收货单已匹配	表示通过将发票关联至收货单凭证，验证发票中的货物或服务已经接收。这是三方匹配的第三个组成部分。该事件根据发票行项目中的引用信息推断得出。
为什么重要与收货单匹配，证明公司已收到所付款项对应的货物或服务。跟踪该活动有助于分析采购订单、收货单与发票匹配时间这一KPI，并精简验证流程。获取位置根据BSEG表中的供应商行项目推断，检查是否已填充收货单凭证号（BSEG-LFBNR），或追溯关联采购订单的历史记录（表EKBE）。采集检查BSEG.LFBNR中的收货单引用，或通过EKBE中的采购订单历史记录检查。事件类型 inferred
	采购订单已匹配	表示发票行项目已成功关联采购订单。这是三方匹配流程中的关键验证步骤。该事件根据发票行项目明细中存在采购订单号推断得出。
为什么重要采购订单匹配延迟可能成为重要瓶颈。分析该活动有助于衡量匹配流程效率，并识别偏离标准采购订单工作流的发票。获取位置根据BSEG表中的供应商行项目推断。如果采购订单号字段（BSEG-EBELN）已填充，则视为匹配发生在凭证创建时。采集检查发票的BSEG.EBELN是否为非空值。事件类型 inferred

建议可选

提取指南

如何从SAP ECC获取数据

步骤

范围与设计：编码前，确定与应付账款流程相关的SAP公司代码（BUKRS）和单据类型（BLART）。常见的FI发票单据类型包括“KR”（供应商发票）和“RE”（发票总额）。这些条件将用于筛选数据。与业务相关方确认所需的13项活动是否全部适用于该流程。
创建ABAP程序：使用事务代码SE38或SE80创建新的可执行ABAP程序，例如Z_PM_AP_EXTRACTION，并填写清晰的标题。
定义选择屏幕：在程序中定义选择屏幕，允许用户指定提取参数。必须包括发票过账日期（P_BUDAT）的日期范围、公司代码（P_BUKRS）和单据类型（P_BLART），以便灵活支持不同分析。
声明数据结构：定义与最终事件日志格式一致的内表结构，包括CaseID（发票编号）、Activity、EventTime，以及VendorName、CompanyCode等其他必需属性。
选择基础发票数据：编写核心数据选择逻辑。程序首先根据用户在选择屏幕中输入的参数，从BKPF选择发票抬头，从BSEG选择行项目。初始查询中将这些数据与供应商主数据表LFA1连接，以获取VendorName，可提高效率。
生成“现状”事件：遍历选定的基础发票数据。针对每张发票，生成可直接从BKPF和BSEG表确定的事件，包括发票凭证创建、发票暂存、发票过账、采购订单匹配、收货匹配和付款清账。将每个生成的事件作为新行追加到最终事件日志内表。
生成变更日志事件：要记录付款冻结等变更，从变更凭证表CDHDR（抬头）和CDPOS（项目）中选择数据。搜索BSEG表中ZLSPR（付款冻结键）字段的变更，并遍历这些变更凭证，使用CDHDR中的精确时间戳创建“设置付款冻结”和“移除付款冻结”事件。
生成付款运行事件：查询付款建议表REGUH（抬头）和REGUP（项目），识别发票何时被纳入付款建议，并生成“付款建议创建”活动。
生成计算和推断事件：为非系统直接记录的事件实现相应逻辑。对于“未付款但已过到期日”，比较发票到期日（BSEG-NETDT）与清账日期或当前日期。对于“发票审批”，可能需要根据特定“需要审批”的付款冻结被移除来推断。
整合并格式化数据：确保所有时间戳转换为统一格式，例如YYYY-MM-DD HH:MM:SS。将上述步骤生成的所有事件合并到一个最终内表中。
创建输出文件：使用cl_gui_frontend_services=>gui_download方法，或将文件写入应用服务器。输出应为平面文件，例如CSV，并包含符合ProcessMind数据模型要求的表头。文件现已准备好上传。

配置

选择参数：程序应包含选择屏幕，并提供以下必填和选填字段：
- S_BUDAT：过账日期范围（必填），用于定义提取时间窗口。
- S_BUKRS：公司代码（必填），用于限定特定法人实体。
- S_BLART：单据类型（选填），用于筛选“KR”或“RE”等特定发票类型。
日期范围建议：首次提取建议选择最近3至6个月，以控制数据量并提高执行速度。完整历史数据加载应在后台运行。
性能注意事项：从变更日志表（CDHDR/CDPOS）中选择数据可能较慢。应尽可能使用主键和索引字段进行查询。对于超大型数据集，可将程序作为后台作业运行（事务SM36），避免对话进程超时。
前提条件与授权：
- 执行程序的用户需要访问BKPF、BSEG、LFA1、CDHDR、CDPOS、REGUH和REGUP表的权限。
- 运行事务SE38需要相应的执行权限。
- 如需捕获“设置付款冻结”和“移除付款冻结”活动，必须为BSEG表的ZLSPR字段启用变更凭证日志记录。这属于系统配置设置。

a 示例查询 abap

*&---------------------------------------------------------------------*
*& Report Z_PM_AP_EXTRACTION
*&---------------------------------------------------------------------*
*& This program extracts Accounts Payable invoice data for process mining.
*& It creates an event log based on key business activities.
*&---------------------------------------------------------------------*
REPORT z_pm_ap_extraction.

*&---------------------------------------------------------------------*
*& Global Data Declarations
*&---------------------------------------------------------------------*
TABLES: bkpf, bseg, lfa1.

TYPES:
  BEGIN OF ty_event_log,
    invoicenumber       TYPE belnr_d,
    activity            TYPE string,
    eventtime           TYPE string,
    processoruser       TYPE uname,
    vendorname          TYPE name1_gp,
    companycode         TYPE bukrs,
    invoicegrossamount  TYPE wrbtr,
    invoiceduedate      TYPE c, "char20
    paymentterms        TYPE char256,
    purchaseordernumber TYPE ebeln,
    paymentblockreason  TYPE char256,
    approver            TYPE uname,
  END OF ty_event_log.

DATA: gt_event_log TYPE TABLE OF ty_event_log.

TYPES:
  BEGIN OF ty_invoice_data,
    bukrs TYPE bkpf-bukrs,
    belnr TYPE bkpf-belnr,
    gjahr TYPE bkpf-gjahr,
    blart TYPE bkpf-blart,
    budat TYPE bkpf-budat,
    cpudt TYPE bkpf-cpudt,
    cputm TYPE bkpf-cputm,
    usnam TYPE bkpf-usnam,
    bstat TYPE bkpf-bstat,
    stblg TYPE bkpf-stblg,
    xblnr TYPE bkpf-xblnr,
    lifnr TYPE bseg-lifnr,
    buzei TYPE bseg-buzei,
    wrbtr TYPE bseg-wrbtr,
    zterm TYPE bseg-zterm,
    zlspr TYPE bseg-zlspr,
    netdt TYPE bseg-netdt,
    ebeln TYPE bseg-ebeln,
    lfbnr TYPE bseg-lfbnr,
    augbl TYPE bseg-augbl,
    augdt TYPE bseg-augdt,
    name1 TYPE lfa1-name1,
  END OF ty_invoice_data.

DATA: gt_invoice_data TYPE TABLE OF ty_invoice_data.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS: s_budat FOR bkpf-budat OBLIGATORY,  " Posting Date
                    s_bukrs FOR bkpf-bukrs OBLIGATORY,  " Company Code
                    s_blart FOR bkpf-blart.             " Document Type
SELECTION-SCREEN END OF BLOCK b1.

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY DEFAULT 'C:\temp\ap_event_log.csv'.

*&---------------------------------------------------------------------*
*& Main Program Logic
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  " 1. Select base invoice data
  SELECT
    h~bukrs, h~belnr, h~gjahr, h~blart, h~budat, h~cpudt, h~cputm, h~usnam,
    h~bstat, h~stblg, h~xblnr, i~lifnr, i~buzei, i~wrbtr, i~zterm, i~zlspr,
    i~netdt, i~ebeln, i~lfbnr, i~augbl, i~augdt, v~name1
    INTO TABLE @gt_invoice_data
    FROM bkpf AS h
    INNER JOIN bseg AS i ON  h~bukrs = i~bukrs
                         AND h~belnr = i~belnr
                         AND h~gjahr = i~gjahr
    INNER JOIN lfa1 AS v ON i~lifnr = v~lifnr
    WHERE h~budat IN @s_budat
      AND h~bukrs IN @s_bukrs
      AND h~blart IN @s_blart
      AND i~koart = 'K'. " Vendor line items only

  CHECK gt_invoice_data IS NOT INITIAL.

  " 2. Process each invoice to generate events
  LOOP AT gt_invoice_data ASSIGNING FIELD-SYMBOL(<fs_invoice>).
    " Generate events for each invoice document/line item
    PERFORM generate_events USING <fs_invoice>.
  ENDLOOP.

  " 3. Generate events from change documents (payment blocks)
  PERFORM generate_change_log_events.

  " 4. Generate events from payment proposal run
  PERFORM generate_payment_proposal_events.

  " 5. Download the final event log
  PERFORM download_event_log.


*&---------------------------------------------------------------------*
*&      Form  GENERATE_EVENTS
*&---------------------------------------------------------------------*
FORM generate_events USING is_invoice TYPE ty_invoice_data.
  DATA: ls_event_log TYPE ty_event_log,
        lv_timestamp TYPE string.

  " Common attributes for all events of this invoice
  ls_event_log-invoicenumber      = is_invoice-belnr.
  ls_event_log-companycode        = is_invoice-bukrs.
  ls_event_log-vendorname         = is_invoice-name1.
  ls_event_log-invoicegrossamount = is_invoice-wrbtr.
  ls_event_log-paymentterms       = is_invoice-zterm.
  ls_event_log-purchaseordernumber= is_invoice-ebeln.

  " Format Due Date
  WRITE is_invoice-netdt TO ls_event_log-invoiceduedate DD/MM/YYYY.

  " Activity: Invoice Document Created
  CONCATENATE is_invoice-cpudt is_invoice-cputm INTO lv_timestamp.
  ls_event_log-activity     = 'Invoice Document Created'.
  ls_event_log-eventtime    = lv_timestamp.
  ls_event_log-processoruser= is_invoice-usnam.
  APPEND ls_event_log TO gt_event_log.

  " Activity: Invoice Parked
  IF is_invoice-bstat = 'V'.
    ls_event_log-activity     = 'Invoice Parked'.
    ls_event_log-eventtime    = lv_timestamp. " Parked at time of creation
    ls_event_log-processoruser= is_invoice-usnam.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

  " Activity: Invoice Posted
  IF is_invoice-bstat = space.
    CONCATENATE is_invoice-budat '000000' INTO lv_timestamp.
    ls_event_log-activity     = 'Invoice Posted'.
    ls_event_log-eventtime    = lv_timestamp.
    ls_event_log-processoruser= is_invoice-usnam.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

  " Activity: Purchase Order Matched
  IF is_invoice-ebeln IS NOT INITIAL.
    CONCATENATE is_invoice-cpudt is_invoice-cputm INTO lv_timestamp.
    ls_event_log-activity     = 'Purchase Order Matched'.
    ls_event_log-eventtime    = lv_timestamp.
    ls_event_log-processoruser= is_invoice-usnam.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

  " Activity: Goods Receipt Matched
  IF is_invoice-lfbnr IS NOT INITIAL.
    CONCATENATE is_invoice-cpudt is_invoice-cputm INTO lv_timestamp.
    ls_event_log-activity     = 'Goods Receipt Matched'.
    ls_event_log-eventtime    = lv_timestamp.
    ls_event_log-processoruser= is_invoice-usnam.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

  " Activity: Invoice Cleared By Payment
  IF is_invoice-augbl IS NOT INITIAL.
    CONCATENATE is_invoice-augdt '000000' INTO lv_timestamp.
    ls_event_log-activity     = 'Invoice Cleared By Payment'.
    ls_event_log-eventtime    = lv_timestamp.
    " Processor user for clearing is not in this structure, would require another SELECT
    ls_event_log-processoruser = ''.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

  " Activity: Invoice Reversed
  IF is_invoice-stblg IS NOT INITIAL.
    SELECT SINGLE budat INTO @DATA(lv_rev_date) FROM bkpf
      WHERE belnr = @is_invoice-stblg AND gjahr = @is_invoice-gjahr.
    IF sy-subrc = 0.
      CONCATENATE lv_rev_date '000000' INTO lv_timestamp.
      ls_event_log-activity     = 'Invoice Reversed'.
      ls_event_log-eventtime    = lv_timestamp.
      ls_event_log-processoruser= sy-uname.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.
  ENDIF.

  " Activity: Due Date Passed Without Payment
  IF is_invoice-netdt < sy-datum AND is_invoice-augbl IS INITIAL.
    CONCATENATE is_invoice-netdt '000000' INTO lv_timestamp.
    ls_event_log-activity      = 'Due Date Passed Without Payment'.
    ls_event_log-eventtime     = lv_timestamp.
    ls_event_log-processoruser = 'SYSTEM'.
    APPEND ls_event_log TO gt_event_log.
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GENERATE_CHANGE_LOG_EVENTS
*&---------------------------------------------------------------------*
FORM generate_change_log_events.
  TYPES: BEGIN OF ty_cdpos,
           objectid TYPE cdpos-objectid,
           changenr TYPE cdpos-changenr,
           fname    TYPE cdpos-fname,
           value_new TYPE cdpos-value_new,
           value_old TYPE cdpos-value_old,
         END OF ty_cdpos.

  DATA: lt_cdpos TYPE TABLE OF ty_cdpos,
        lt_cdhdr TYPE TABLE OF cdhdr.

  DATA: ls_event_log TYPE ty_event_log.

  DATA(lt_invoice_range) = VALUE range_belnr_d( FOR wa IN gt_invoice_data ( sign = 'I' option = 'EQ' low = wa-belnr ) ).
  CHECK lt_invoice_range IS NOT INITIAL.

  SELECT objectclas, objectid, changenr, username, udate, utime
    FROM cdhdr
    INTO TABLE @lt_cdhdr
    FOR ALL ENTRIES IN @gt_invoice_data
    WHERE objectclas = 'BELEG'
      AND objectid   = @gt_invoice_data-objectid.
  CHECK sy-subrc = 0.

  SELECT objectid, changenr, fname, value_new, value_old
    FROM cdpos
    INTO TABLE @lt_cdpos
    FOR ALL ENTRIES IN @lt_cdhdr
    WHERE changenr = @lt_cdhdr-changenr
      AND objectid = @lt_cdhdr-objectid
      AND tabname  = 'BSEG'
      AND fname    = 'ZLSPR'.
  CHECK sy-subrc = 0.

  LOOP AT lt_cdpos ASSIGNING FIELD-SYMBOL(<fs_cdpos>).
    READ TABLE lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>) WITH KEY changenr = <fs_cdpos>-changenr.
    IF sy-subrc <> 0. CONTINUE. ENDIF.

    " Find corresponding invoice data
    DATA(lv_belnr) = <fs_cdhdr>-objectid+10(10).
    READ TABLE gt_invoice_data ASSIGNING FIELD-SYMBOL(<fs_invoice>) WITH KEY belnr = lv_belnr.
    IF sy-subrc <> 0. CONTINUE. ENDIF.

    " Populate common event data
    ls_event_log-invoicenumber       = <fs_invoice>-belnr.
    ls_event_log-companycode         = <fs_invoice>-bukrs.
    ls_event_log-vendorname          = <fs_invoice>-name1.
    ls_event_log-invoicegrossamount  = <fs_invoice>-wrbtr.
    ls_event_log-purchaseordernumber = <fs_invoice>-ebeln.
    ls_event_log-processoruser       = <fs_cdhdr>-username.

    CONCATENATE <fs_cdhdr>-udate <fs_cdhdr>-utime INTO ls_event_log-eventtime.

    " Activity: Payment Block Set
    IF <fs_cdpos>-value_new IS NOT INITIAL.
      ls_event_log-activity           = 'Payment Block Set'.
      ls_event_log-paymentblockreason = <fs_cdpos>-value_new.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.

    " Activity: Payment Block Removed
    IF <fs_cdpos>-value_new IS INITIAL AND <fs_cdpos>-value_old IS NOT INITIAL.
      ls_event_log-activity           = 'Payment Block Removed'.
      ls_event_log-paymentblockreason = <fs_cdpos>-value_old.
      APPEND ls_event_log TO gt_event_log.

      " INFERRED Activity: Invoice Approved
      " Assumption: Removing a specific block (e.g., 'A' for Approval) means approval.
      IF <fs_cdpos>-value_old = '[Your Approval Block Reason]'. " E.g., 'A'
        ls_event_log-activity           = 'Invoice Approved'.
        ls_event_log-approver           = <fs_cdhdr>-username.
        APPEND ls_event_log TO gt_event_log.
      ENDIF.
    ENDIF.

    " INFERRED Activity: Invoice Routed For Approval
    " Assumption: Setting a specific block (e.g., 'A' for Approval) means routed for approval.
    IF <fs_cdpos>-value_new = '[Your Approval Block Reason]'. " E.g., 'A'
      ls_event_log-activity           = 'Invoice Routed For Approval'.
      ls_event_log-approver           = ''.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.

  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GENERATE_PAYMENT_PROPOSAL_EVENTS
*&---------------------------------------------------------------------*
FORM generate_payment_proposal_events.
  DATA: lt_regup TYPE TABLE OF regup,
        ls_reguh TYPE reguh.
  DATA: ls_event_log TYPE ty_event_log.

  SELECT * FROM regup INTO TABLE @lt_regup
    FOR ALL ENTRIES IN @gt_invoice_data
    WHERE bukrs = @gt_invoice_data-bukrs
      AND belnr = @gt_invoice_data-belnr
      AND gjahr = @gt_invoice_data-gjahr.
  CHECK sy-subrc = 0.

  LOOP AT lt_regup ASSIGNING FIELD-SYMBOL(<fs_regup>).
    READ TABLE gt_invoice_data ASSIGNING FIELD-SYMBOL(<fs_invoice>) WITH KEY belnr = <fs_regup>-belnr.
    IF sy-subrc <> 0. CONTINUE. ENDIF.

    SELECT SINGLE * FROM reguh INTO @ls_reguh
      WHERE rundat = @<fs_regup>-rundat AND runid = @<fs_regup>-runid.
    IF sy-subrc <> 0. CONTINUE. ENDIF.

    ls_event_log-invoicenumber      = <fs_invoice>-belnr.
    ls_event_log-companycode        = <fs_invoice>-bukrs.
    ls_event_log-vendorname         = <fs_invoice>-name1.
    ls_event_log-invoicegrossamount = <fs_invoice>-wrbtr.
    ls_event_log-processoruser      = ls_reguh-usnam.
    ls_event_log-activity           = 'Payment Proposal Created'.

    CONCATENATE ls_reguh-rundat '000000' INTO ls_event_log-eventtime.
    APPEND ls_event_log TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_EVENT_LOG
*&---------------------------------------------------------------------*
FORM download_event_log.
  DATA: lt_final_output TYPE TABLE OF string,
        l_header        TYPE string.

  SORT gt_event_log BY invoicenumber eventtime.

  " Create Header
  l_header = `"InvoiceNumber","Activity","EventTime","ProcessorUser","VendorName","CompanyCode","InvoiceGrossAmount","InvoiceDueDate","PaymentTerms","PurchaseOrderNumber","PaymentBlockReason","Approver"`.
  APPEND l_header TO lt_final_output.

  " Create Data Rows
  LOOP AT gt_event_log ASSIGNING FIELD-SYMBOL(<fs_log>).
    DATA(l_line) = |"{ <fs_log>-invoicenumber }","{
                     <fs_log>-activity }","{
                     <fs_log>-eventtime }","{
                     <fs_log>-processoruser }","{
                     <fs_log>-vendorname }","{
                     <fs_log>-companycode }","{
                     <fs_log>-invoicegrossamount }","{
                     <fs_log>-invoiceduedate }","{
                     <fs_log>-paymentterms }","{
                     <fs_log>-purchaseordernumber }","{
                     <fs_log>-paymentblockreason }","{
                     <fs_log>-approver }"|.
    APPEND l_line TO lt_final_output.
  ENDLOOP.

  " Download file to presentation server
  cl_gui_frontend_services=>gui_download(
    EXPORTING
      filename = p_file
      filetype = 'ASC'
    CHANGING
      data_tab = lt_final_output
    EXCEPTIONS
      OTHERS   = 4 ).

  IF sy-subrc <> 0.
    MESSAGE 'File download failed.' TYPE 'E'.
  ELSE.
    MESSAGE |File '{ p_file }' downloaded successfully.| TYPE 'I'.
  ENDIF.
ENDFORM.

步骤

前提条件与访问权限：确保您拥有对所需SAP表的读取权限，例如BKPF、BSEG、LFA1、CDHDR、CDPOS、REGUH和REGUP。此方法需要直接访问数据库，可能需要获得IT安全团队和SAP Basis团队的批准。
确认系统详情：确认SAP ECC系统使用的具体数据库类型，例如SAP HANA、Oracle或MS SQL Server。所提供的SQL查询使用SAP HANA常见语法，在其他数据库上可能需要进行少量调整，尤其是时间戳生成部分。
自定义查询：在数据库客户端中打开所提供的SQL查询，例如DBeaver、SAP HANA Studio或SQL Server Management Studio。在BaseInvoices CTE顶部修改占位值：
- “{StartDate}”：设置提取周期的起始日期，例如“2023-01-01”。
- “{EndDate}”：设置提取周期的结束日期，例如“2023-06-30”。
- “{CompanyCodes}”：替换为要分析的公司代码列表，以逗号分隔，例如“1000”、“2000”。
- “{DocTypes}”：确认系统中的供应商发票单据类型。“KR”和“RE”是常见默认值。
执行查询：针对SAP数据库运行自定义SQL查询。执行时间取决于日期范围、公司代码数量和系统总体规模。
检查查询输出：查询完成后，检查前几百行输出，确认所有列，例如InvoiceNumber、Activity和EventTime，均已存在并按预期填充。
处理潜在错误：如果遇到语法错误，请根据具体数据库调整日期和时间函数。例如，TO_TIMESTAMP（HANA/Oracle）可能需要改为CAST或CONVERT函数（SQL Server）。
导出数据：从SQL客户端将完整结果集导出为CSV（逗号分隔值）文件，并确保文件编码设置为UTF-8，以避免特殊字符显示异常。
准备上传：CSV文件中的列名必须与查询别名完全一致，例如InvoiceNumber、Activity、EventTime和ProcessorUser，以确保上传时顺利完成映射。
上传到流程挖掘工具：将最终CSV文件上传到流程挖掘应用程序，并将InvoiceNumber映射到“Case ID”、Activity映射到“Activity”、EventTime映射到“Timestamp”，即可开始分析。

配置

数据库连接：需要直接建立到SAP ECC底层数据库的只读连接。您需要准备服务器地址、数据库名称、端口、用户名和密码。
日期范围：建议先选择有限的日期范围，例如3至6个月，以确保性能并在扩大提取范围前验证结果。日期筛选应用于单据创建日期（BKPF.CPUDT）。
公司代码：查询必须按一个或多个CompanyCode（BUKRS）值进行筛选。在大型系统中，不建议一次提取所有公司代码的数据。
单据类型：提取范围按发票单据类型（BLART）筛选。供应商发票常见类型为KR（供应商发票）和RE（发票收货）。请咨询财务团队，确认适用于您组织的类型。
表授权：数据库用户需要以下表的SELECT权限：BKPF、BSEG、LFA1、CDHDR、CDPOS、REGUH、REGUP。
时间戳转换：查询使用TO_TIMESTAMP(CONCAT(date, time), 'YYYYMMDDHH24MISS')。该写法兼容SAP HANA和Oracle。对于SQL Server，您可以使用CAST(CONCAT(date, ' ', time) AS DATETIME2)。具体写法可能需要根据目标数据库进行调整。

a 示例查询 sql

WITH BaseInvoices AS (
  SELECT
    BKPF.MANDT,
    BKPF.BUKRS,
    BKPF.BELNR,
    BKPF.GJAHR,
    CONCAT(CONCAT(CONCAT(BKPF.BUKRS, '-'), BKPF.BELNR), BKPF.GJAHR) AS InvoiceNumber,
    BKPF.CPUDT AS CreationDate,
    BKPF.CPUTM AS CreationTime,
    BKPF.USNAM AS CreatorUser,
    BKPF.BSTAT AS DocStatus,
    BKPF.STBLG AS ReversalDoc,
    BKPF.STGRD AS ReversalDate
  FROM BKPF
  WHERE
    BKPF.BUKRS IN ('{CompanyCodes}') -- E.g., '1000', '2000'
    AND BKPF.BLART IN ('{DocTypes}') -- E.g., 'KR', 'RE'
    AND BKPF.CPUDT BETWEEN '{StartDate}' AND '{EndDate}' -- E.g., '20230101' AND '20231231'
),
InvoiceVendorLines AS (
  SELECT
    BSEG.MANDT, BSEG.BUKRS, BSEG.BELNR, BSEG.GJAHR,
    MAX(BSEG.LIFNR) AS VendorNumber,
    MAX(BSEG.ZFBDT) AS DueDate,
    MAX(BSEG.ZTERM) AS PaymentTerms,
    MAX(BSEG.ZLSPR) AS PaymentBlockReason,
    SUM(CASE WHEN BSEG.SHKZG = 'S' THEN BSEG.WRBTR ELSE -BSEG.WRBTR END) AS InvoiceGrossAmount,
    MAX(BSEG.EBELN) AS PurchaseOrderNumber,
    MAX(BSEG.AUGDT) AS ClearingDate,
    MAX(AUGBL) AS ClearingDoc
  FROM BSEG
  INNER JOIN BaseInvoices ON BSEG.MANDT = BaseInvoices.MANDT
    AND BSEG.BUKRS = BaseInvoices.BUKRS
    AND BSEG.BELNR = BaseInvoices.BELNR
    AND BSEG.GJAHR = BaseInvoices.GJAHR
  WHERE BSEG.KOART = 'K' -- Filter for Vendor line items
  GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.BELNR, BSEG.GJAHR
)
-- 1. Invoice Document Created
SELECT
  bi.InvoiceNumber AS "InvoiceNumber",
  'Invoice Document Created' AS "Activity",
  TO_TIMESTAMP(CONCAT(bi.CreationDate, bi.CreationTime), 'YYYYMMDDHH24MISS') AS "EventTime",
  bi.CreatorUser AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR

UNION ALL

-- 2. Invoice Parked
SELECT
  bi.InvoiceNumber,
  'Invoice Parked' AS "Activity",
  TO_TIMESTAMP(CONCAT(bi.CreationDate, bi.CreationTime), 'YYYYMMDDHH24MISS') AS "EventTime",
  bi.CreatorUser AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE bi.DocStatus = 'V'

UNION ALL

-- 3. Purchase Order Matched & 4. Goods Receipt Matched (Inferred at creation)
SELECT
  bi.InvoiceNumber,
  CASE WHEN ivl.PurchaseOrderNumber IS NOT NULL THEN 'Purchase Order Matched' ELSE 'Goods Receipt Matched' END AS "Activity",
  TO_TIMESTAMP(CONCAT(bi.CreationDate, bi.CreationTime), 'YYYYMMDDHH24MISS') AS "EventTime",
  bi.CreatorUser AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE ivl.PurchaseOrderNumber IS NOT NULL -- Simplified logic: If PO exists, we assume match for both.

UNION ALL

-- 5. Payment Block Set & 6. Invoice Routed For Approval (Proxy) & 8. Payment Block Removed
SELECT
  bi.InvoiceNumber,
  CASE 
    WHEN pos.VALUE_OLD = ' ' AND pos.VALUE_NEW != ' ' AND pos.VALUE_NEW = 'A' THEN 'Invoice Routed For Approval'
    WHEN pos.VALUE_OLD = ' ' AND pos.VALUE_NEW != ' ' THEN 'Payment Block Set'
    ELSE 'Payment Block Removed' 
  END AS "Activity",
  TO_TIMESTAMP(CONCAT(hdr.UDATE, hdr.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  hdr.USERNAME AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  pos.VALUE_NEW AS "PaymentBlockReason",
  NULL AS "Approver"
FROM CDHDR hdr
JOIN CDPOS pos ON hdr.MANDANT = pos.MANDANT AND hdr.OBJECTCLAS = pos.OBJECTCLAS AND hdr.OBJECTID = pos.OBJECTID AND hdr.CHANGENR = pos.CHANGENR
JOIN BaseInvoices bi ON hdr.MANDANT = bi.MANDT AND hdr.OBJECTID = CONCAT(bi.BUKRS, bi.BELNR, bi.GJAHR)
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE hdr.OBJECTCLAS = 'BELEG' AND pos.TABNAME = 'BSEG' AND pos.FNAME = 'ZLSPR' AND pos.VALUE_NEW != pos.VALUE_OLD

UNION ALL

-- 7. Invoice Approved (Proxy by removal of 'A' block)
SELECT
  bi.InvoiceNumber,
  'Invoice Approved' AS "Activity",
  TO_TIMESTAMP(CONCAT(hdr.UDATE, hdr.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  hdr.USERNAME AS "ProcessorUser", -- This user is the approver
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  pos.VALUE_NEW AS "PaymentBlockReason",
  hdr.USERNAME AS "Approver"
FROM CDHDR hdr
JOIN CDPOS pos ON hdr.MANDANT = pos.MANDANT AND hdr.OBJECTCLAS = pos.OBJECTCLAS AND hdr.OBJECTID = pos.OBJECTID AND hdr.CHANGENR = pos.CHANGENR
JOIN BaseInvoices bi ON hdr.MANDANT = bi.MANDT AND hdr.OBJECTID = CONCAT(bi.BUKRS, bi.BELNR, bi.GJAHR)
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE hdr.OBJECTCLAS = 'BELEG' AND pos.TABNAME = 'BSEG' AND pos.FNAME = 'ZLSPR' AND pos.VALUE_OLD = 'A' AND pos.VALUE_NEW = ' '

UNION ALL

-- 9. Invoice Posted
SELECT
  bi.InvoiceNumber,
  'Invoice Posted' AS "Activity",
  CASE -- If posted from parked, use change date. If posted directly, use creation date.
    WHEN chg.EventTime IS NOT NULL THEN chg.EventTime
    ELSE TO_TIMESTAMP(CONCAT(bi.CreationDate, bi.CreationTime), 'YYYYMMDDHH24MISS')
  END AS "EventTime",
  CASE
    WHEN chg.USERNAME IS NOT NULL THEN chg.USERNAME
    ELSE bi.CreatorUser
  END AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
LEFT JOIN (
    SELECT hdr.MANDANT, hdr.OBJECTID, hdr.USERNAME, TO_TIMESTAMP(CONCAT(hdr.UDATE, hdr.UTIME), 'YYYYMMDDHH24MISS') AS EventTime
    FROM CDHDR hdr JOIN CDPOS pos ON hdr.CHANGENR = pos.CHANGENR AND hdr.OBJECTID = pos.OBJECTID
    WHERE hdr.OBJECTCLAS = 'BELEG' AND pos.TABNAME = 'BKPF' AND pos.FNAME = 'BSTAT' AND pos.VALUE_OLD = 'V' AND pos.VALUE_NEW = ' '
) chg ON bi.MANDT = chg.MANDANT AND CONCAT(bi.BUKRS, bi.BELNR, bi.GJAHR) = chg.OBJECTID
WHERE bi.DocStatus != 'V' -- Only include documents that are currently posted

UNION ALL

-- 10. Due Date Passed Without Payment
SELECT
  bi.InvoiceNumber,
  'Due Date Passed Without Payment' AS "Activity",
  TO_TIMESTAMP(ivl.DueDate, 'YYYYMMDD') AS "EventTime",
  'System' AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE ivl.ClearingDoc IS NULL AND ivl.DueDate < CURRENT_DATE

UNION ALL

-- 11. Payment Proposal Created
SELECT DISTINCT
  bi.InvoiceNumber,
  'Payment Proposal Created' AS "Activity",
  TO_TIMESTAMP(CONCAT(reguh.LAUFD, '000000'), 'YYYYMMDDHH24MISS') AS "EventTime", -- Time is not available in REGUH
  reguh.USNAM AS "ProcessorUser",
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
JOIN REGUP ON ivl.MANDT = REGUP.MANDT AND ivl.BUKRS = REGUP.BUKRS AND ivl.BELNR = REGUP.BELNR AND ivl.GJAHR = REGUP.GJAHR
JOIN REGUH ON REGUP.MANDT = REGUH.MANDT AND REGUP.LAUFD = REGUH.LAUFD AND REGUP.LAUFI = REGUH.LAUFI AND REGUP.XVORL = REGUH.XVORL AND REGUP.ZBUKR = REGUH.ZBUKR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR

UNION ALL

-- 12. Invoice Cleared By Payment
SELECT
  bi.InvoiceNumber,
  'Invoice Cleared By Payment' AS "Activity",
  TO_TIMESTAMP(ivl.ClearingDate, 'YYYYMMDD') AS "EventTime",
  NULL AS "ProcessorUser", -- Clearing user is not on the vendor line
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  ivl.PaymentBlockReason AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE ivl.ClearingDoc IS NOT NULL

UNION ALL

-- 13. Invoice Reversed
SELECT
  bi.InvoiceNumber,
  'Invoice Reversed' AS "Activity",
  TO_TIMESTAMP(bi.ReversalDate, 'YYYYMMDD') AS "EventTime",
  NULL AS "ProcessorUser", -- Reversal user not in BKPF, would require another join
  vend.Name1 AS "VendorName",
  bi.BUKRS AS "CompanyCode",
  ivl.InvoiceGrossAmount AS "InvoiceGrossAmount",
  ivl.DueDate AS "InvoiceDueDate",
  ivl.PaymentTerms AS "PaymentTerms",
  ivl.PurchaseOrderNumber AS "PurchaseOrderNumber",
  NULL AS "PaymentBlockReason",
  NULL AS "Approver"
FROM BaseInvoices bi
JOIN InvoiceVendorLines ivl ON bi.MANDT = ivl.MANDT AND bi.BUKRS = ivl.BUKRS AND bi.BELNR = ivl.BELNR AND bi.GJAHR = ivl.GJAHR
LEFT JOIN LFA1 vend ON ivl.MANDT = vend.MANDT AND ivl.VendorNumber = vend.LIFNR
WHERE bi.ReversalDoc IS NOT NULL;

步骤

安装前提组件：确保Python环境已安装必要的库，包括用于数据处理的pandas和用于SAP RFC通信的pyrfc。此外，还需从SAP支持门户下载SAP NetWeaver RFC SDK，并确保其lib目录位于系统路径中。
设置SAP连接：在Python脚本中，根据系统实际信息配置SAP连接字典：应用服务器主机（ashost）、系统编号（sysnr）、客户端（client）、用户（user）和密码（passwd）。确保指定的SAP用户拥有所需权限，主要包括通过RFC_READ_TABLE访问相关表所需的S_RFC和S_TABU_DIS权限。
定义提取参数：在脚本顶部设置数据提取的关键参数，包括date_range（发票录入的起止日期）和company_codes列表，以限定需要分析的业务单元。
从SAP表提取数据：运行Python脚本。脚本将通过RFC协议连接SAP系统，并多次调用RFC_READ_TABLE函数，从应付账款核心表中提取原始数据，包括BKPF（抬头）、BSEG（行项目）、RSEG（发票收货明细）、LFA1（供应商主数据）、CDHDR/CDPOS（付款冻结变更日志）和BSAK（已清账项目）。
内存中连接数据：脚本使用pandas库连接提取的数据框，将发票抬头与行项目关联，补充供应商名称，并将发票与对应的变更凭证记录关联，形成每张发票的完整扁平数据集。
转换事件日志：这是脚本的核心逻辑。脚本遍历连接后的数据，为13项必需业务活动分别创建记录。对于每项活动，例如“发票暂存”和“设置付款冻结”，脚本会设置正确的活动名称，并从SAP数据中相关的日期和时间字段确定精确时间戳。
处理计算事件：脚本计算SAP中未直接记录的事件。例如，对于“未付款但已过到期日”，脚本会将发票到期日（ZFBDT）与当前日期比较，为所有未清账发票生成相应事件。
填充属性：为每条事件记录填充统一的属性集合，包括InvoiceNumber、Activity和EventTime等必需字段，以及VendorName、InvoiceGrossAmount和CompanyCode等推荐属性，为流程分析提供丰富背景信息。
最终整合：脚本将所有单独的事件数据框汇总为一个最终事件日志数据框，并确保所有记录的列名和数据类型一致。
导出CSV：最后，将整合后的事件日志数据框保存为CSV文件。该文件可直接上传到ProcessMind等流程挖掘工具。脚本将在运行目录中生成名为sap_ap_event_log.csv的文件。

配置

SAP连接参数：脚本需要包含连接详情的字典：ashost（主机名或IP地址）、sysnr（系统编号）、client（例如“100”）、user（SAP用户名）和passwd（SAP密码）。
日期范围：以“YYYYMMDD”格式定义start_date和end_date。该筛选条件应用于发票录入日期（BKPF-CPUDT），用于限定提取范围。初始分析建议选择3至6个月，以平衡数据量和洞察价值。
公司代码（BUKRS）：指定要纳入提取范围的SAP公司代码列表。这是关键筛选条件，可确保分析正确的组织实体，例如['1000', '2000']。
单据类型（BLART）：虽然该脚本未将其明确设为参数，但您可以轻松为BKPF的RFC_READ_TABLE调用增加单据类型筛选，例如“KR”（供应商发票）或“RE”（发票收货），进一步缩小数据范围。
性能与分块：脚本会根据筛选条件读取完整表。对于包含数百万张发票的超大型数据集，RFC_READ_TABLE可能超时或占用过多内存。如遇性能问题，可按月遍历日期范围，分块执行提取。
SAP授权：SAP用户必须拥有RFC执行权限，以及显示以下表数据的权限：BKPF、BSEG、RSEG、LFA1、CDHDR、CDPOS、BSAK、BSIK、REGUH、REGUP。通常通过授权对象S_TABU_DIS授予。

a 示例查询 python

import pandas as pd
from pyrfc import Connection
from datetime import datetime

class=class="hl-string">"hl-comment"># --- 1. SETTINGS & PARAMETERS ---

class=class="hl-string">"hl-comment"># SAP Connection Details (replace with your system's details)
sap_conn_params = {
    class="hl-string">"user": class="hl-string">"[SAP_USER]",
    class="hl-string">"passwd": class="hl-string">"[SAP_PASSWORD]",
    class="hl-string">"ashost": class="hl-string">"[SAP_HOST]",
    class="hl-string">"sysnr": class="hl-string">"[SYSTEM_NUMBER]",
    class="hl-string">"client": class="hl-string">"[CLIENT]"
}

class=class="hl-string">"hl-comment"># Extraction Parameters
company_codes = [class="hl-string">'[YOUR_COMPANY_CODE_1]', class="hl-string">'[YOUR_COMPANY_CODE_2]']
date_range = {
    class="hl-string">"start_date": class="hl-string">"20230101", class=class="hl-string">"hl-comment"># YYYYMMDD
    class="hl-string">"end_date": class="hl-string">"20231231"    class=class="hl-string">"hl-comment"># YYYYMMDD
}

class=class="hl-string">"hl-comment"># --- 2. HELPER FUNCTION TO READ SAP TABLE ---
def read_sap_table(conn, table_name, fields=[], options=[], row_count=0):
    class="hl-string">"""Reads an SAP table using RFC_READ_TABLE and returns a pandas DataFrame."""
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                             QUERY_TABLE=table_name,
                             DELIMITER=class="hl-string">'|',
                             FIELDS=fields,
                             OPTIONS=options,
                             ROWCOUNT=row_count)
        
        data = result[class="hl-string">'DATA']
        if not data:
            return pd.DataFrame(columns=[f[class="hl-string">'FIELDNAME'] for f in result[class="hl-string">'FIELDS']])

        df = pd.DataFrame([row[class="hl-string">'WA'].split(class="hl-string">'|') for row in data])
        df.columns = [f[class="hl-string">'FIELDNAME'] for f in result[class="hl-string">'FIELDS']]
        return df
    except Exception as e:
        print(fclass="hl-string">"Error reading table {table_name}: {e}")
        return pd.DataFrame(columns=[f[class="hl-string">'FIELDNAME'] for f in fields])

class=class="hl-string">"hl-comment"># --- 3. DATA EXTRACTION ---
print(class="hl-string">"Starting SAP data extraction...")

with Connection(**sap_conn_params) as conn:
    class=class="hl-string">"hl-comment"># Define filters
    company_code_filter = class="hl-string">" OR ".join([fclass="hl-string">"BUKRS = '{cc}'" for cc in company_codes])
    date_filter = fclass="hl-string">"CPUDT >= '{date_range['start_date']}' AND CPUDT <= '{date_range['end_date']}'"
    bkpf_filter = [fclass="hl-string">"({company_code_filter}) AND ({date_filter})"]

    class=class="hl-string">"hl-comment"># Extract main tables
    df_bkpf = read_sap_table(conn, class="hl-string">'BKPF', fields=[class="hl-string">'MANDT', class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BLART', class="hl-string">'BUDAT', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'TCODE', class="hl-string">'STBLG', class="hl-string">'BSTAT'], options=bkpf_filter)
    df_bkpf[class="hl-string">'AWKEY'] = df_bkpf[class="hl-string">'BELNR'] + df_bkpf[class="hl-string">'GJAHR']
    df_bkpf[class="hl-string">'OBJECTKEY'] = df_bkpf[class="hl-string">'MANDT'] + df_bkpf[class="hl-string">'BUKRS'] + df_bkpf[class="hl-string">'BELNR'] + df_bkpf[class="hl-string">'GJAHR']
    doc_keys = df_bkpf[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS']].drop_duplicates().to_dict(class="hl-string">'records')
    bseg_filter = class="hl-string">" OR ".join([fclass="hl-string">"(BELNR = '{k['BELNR']}' AND GJAHR = '{k['GJAHR']}' AND BUKRS = '{k['BUKRS']}')" for k in doc_keys])
    df_bseg = read_sap_table(conn, class="hl-string">'BSEG', fields=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'BUZEI', class="hl-string">'LIFNR', class="hl-string">'ZFBDT', class="hl-string">'ZTERM', class="hl-string">'ZLSPR', class="hl-string">'WRBTR'])

    class=class="hl-string">"hl-comment"># Extract more tables based on findings
    rseg_filter = [fclass="hl-string">"BELNR IN ({','.join(df_bkpf['BELNR'].unique())}) AND GJAHR IN ({','.join(df_bkpf['GJAHR'].unique())})"]
    df_rseg = read_sap_table(conn, class="hl-string">'RSEG', fields=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'EBELN', class="hl-string">'EBELP', class="hl-string">'LFBNR'])
    
    vendor_numbers = df_bseg[class="hl-string">'LIFNR'].dropna().unique()
    lfa1_filter = [fclass="hl-string">"LIFNR IN ({','.join(vendor_numbers)})"]
    df_lfa1 = read_sap_table(conn, class="hl-string">'LFA1', fields=[class="hl-string">'LIFNR', class="hl-string">'NAME1'])

    class=class="hl-string">"hl-comment"># Change Documents (CDHDR/CDPOS) for Payment Blocks
    cdhdr_filter = [fclass="hl-string">"OBJECTCLAS = 'BELEGV' AND OBJECTID IN ({','.join(df_bkpf['OBJECTKEY'].unique())}) AND TCODE = 'FB02'"]
    df_cdhdr = read_sap_table(conn, class="hl-string">'CDHDR', fields=[class="hl-string">'OBJECTCLAS', class="hl-string">'OBJECTID', class="hl-string">'CHANGENR', class="hl-string">'USERNAME', class="hl-string">'UDATE', class="hl-string">'UTIME'])
    cdpos_filter = [fclass="hl-string">"OBJECTCLAS = 'BELEGV' AND FNAME = 'ZLSPR' AND TABNAME = 'BSEG' AND CHANGENR IN ({','.join(df_cdhdr['CHANGENR'].unique())})"]
    df_cdpos = read_sap_table(conn, class="hl-string">'CDPOS', fields=[class="hl-string">'CHANGENR', class="hl-string">'TABKEY', class="hl-string">'FNAME', class="hl-string">'VALUE_NEW', class="hl-string">'VALUE_OLD'])
    
    class=class="hl-string">"hl-comment"># Cleared Items
    bsak_filter = class="hl-string">" OR ".join([fclass="hl-string">"(BELNR = '{k['BELNR']}' AND GJAHR = '{k['GJAHR']}' AND BUKRS = '{k['BUKRS']}')" for k in doc_keys])
    df_bsak = read_sap_table(conn, class="hl-string">'BSAK', fields=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'AUGDT', class="hl-string">'AUGBL'])

    class=class="hl-string">"hl-comment"># Payment Proposal
    regup_filter = class="hl-string">" OR ".join([fclass="hl-string">"(VBLNR = '{k['BELNR']}' AND GJAHR = '{k['GJAHR']}' AND BUKRS = '{k['BUKRS']}')" for k in doc_keys])
    df_regup = read_sap_table(conn, class="hl-string">'REGUP', fields=[class="hl-string">'LAUFD', class="hl-string">'LAUFI', class="hl-string">'VBLNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
    reguh_filter = [fclass="hl-string">"LAUFD IN ({','.join(df_regup['LAUFD'].unique())}) AND LAUFI IN ({','.join(df_regup['LAUFI'].unique())})"]
    df_reguh = read_sap_table(conn, class="hl-string">'REGUH', fields=[class="hl-string">'LAUFD', class="hl-string">'LAUFI', class="hl-string">'VBLNR'])


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

class=class="hl-string">"hl-comment"># --- 4. DATA TRANSFORMATION & JOINING ---
class=class="hl-string">"hl-comment"># Merge core invoice data
df_inv = pd.merge(df_bkpf, df_bseg.loc[df_bseg[class="hl-string">'LIFNR'].notna()], on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
df_inv = pd.merge(df_inv, df_lfa1, on=class="hl-string">'LIFNR', how=class="hl-string">'left')
df_inv = pd.merge(df_inv, df_rseg, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR'], how=class="hl-string">'left')

class=class="hl-string">"hl-comment"># --- 5. EVENT LOG CREATION ---
all_events = []

class=class="hl-string">"hl-comment"># Invoice Document Created
df_created = df_inv.copy()
df_created[class="hl-string">'Activity'] = class="hl-string">'Invoice Document Created'
df_created[class="hl-string">'EventTime'] = pd.to_datetime(df_created[class="hl-string">'CPUDT'] + df_created[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
all_events.append(df_created)

class=class="hl-string">"hl-comment"># Invoice Parked
df_parked = df_inv[df_inv[class="hl-string">'BSTAT'] == class="hl-string">'V'].copy()
df_parked[class="hl-string">'Activity'] = class="hl-string">'Invoice Parked'
df_parked[class="hl-string">'EventTime'] = pd.to_datetime(df_parked[class="hl-string">'CPUDT'] + df_parked[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
all_events.append(df_parked)

class=class="hl-string">"hl-comment"># Purchase Order Matched
df_po_match = df_inv[df_inv[class="hl-string">'EBELN'].notna()].copy()
df_po_match[class="hl-string">'Activity'] = class="hl-string">'Purchase Order Matched'
df_po_match[class="hl-string">'EventTime'] = pd.to_datetime(df_po_match[class="hl-string">'CPUDT'] + df_po_match[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
all_events.append(df_po_match)

class=class="hl-string">"hl-comment"># Goods Receipt Matched
df_gr_match = df_inv[df_inv[class="hl-string">'LFBNR'].notna()].copy()
df_gr_match[class="hl-string">'Activity'] = class="hl-string">'Goods Receipt Matched'
df_gr_match[class="hl-string">'EventTime'] = pd.to_datetime(df_gr_match[class="hl-string">'CPUDT'] + df_gr_match[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
all_events.append(df_gr_match)

class=class="hl-string">"hl-comment"># Payment Block Set & Removed (from change logs)
if not df_cdhdr.empty and not df_cdpos.empty:
    df_changes = pd.merge(df_cdhdr, df_cdpos, on=class="hl-string">'CHANGENR')
    df_changes[class="hl-string">'BELNR'] = df_changes[class="hl-string">'OBJECTID'].str[7:17]
    df_changes[class="hl-string">'GJAHR'] = df_changes[class="hl-string">'OBJECTID'].str[17:21]
    df_changes[class="hl-string">'BUKRS'] = df_changes[class="hl-string">'OBJECTID'].str[3:7]
    df_changes = pd.merge(df_changes, df_inv[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS']].drop_duplicates(), on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
    
    df_block_set = df_changes[df_changes[class="hl-string">'VALUE_NEW'].str.strip() != class="hl-string">''].copy()
    df_block_set[class="hl-string">'Activity'] = class="hl-string">'Payment Block Set'
    df_block_set[class="hl-string">'EventTime'] = pd.to_datetime(df_block_set[class="hl-string">'UDATE'] + df_block_set[class="hl-string">'UTIME'], format=class="hl-string">'%Y%m%d%H%M%S')
    df_block_set.rename(columns={class="hl-string">'VALUE_NEW': class="hl-string">'PaymentBlockReason', class="hl-string">'USERNAME': class="hl-string">'ProcessorUser'}, inplace=True)
    all_events.append(df_block_set)

    df_block_removed = df_changes[df_changes[class="hl-string">'VALUE_OLD'].str.strip() != class="hl-string">''].copy()
    df_block_removed[class="hl-string">'Activity'] = class="hl-string">'Payment Block Removed'
    df_block_removed[class="hl-string">'EventTime'] = pd.to_datetime(df_block_removed[class="hl-string">'UDATE'] + df_block_removed[class="hl-string">'UTIME'], format=class="hl-string">'%Y%m%d%H%M%S')
    df_block_removed.rename(columns={class="hl-string">'VALUE_OLD': class="hl-string">'PaymentBlockReason', class="hl-string">'USERNAME': class="hl-string">'ProcessorUser'}, inplace=True)
    all_events.append(df_block_removed)

    class=class="hl-string">"hl-comment"># Invoice Routed For Approval & Approved (inferred from blocks)
    class=class="hl-string">"hl-comment"># Assumption: A specific block reason, e.g., class="hl-string">'A', signifies approval workflow.
    df_routed = df_block_set[df_block_set[class="hl-string">'PaymentBlockReason'].str.strip() == class="hl-string">'[A]'].copy()
    df_routed[class="hl-string">'Activity'] = class="hl-string">'Invoice Routed For Approval'
    all_events.append(df_routed)

    df_approved = df_block_removed[df_block_removed[class="hl-string">'PaymentBlockReason'].str.strip() == class="hl-string">'[A]'].copy()
    df_approved[class="hl-string">'Activity'] = class="hl-string">'Invoice Approved'
    df_approved[class="hl-string">'Approver'] = df_approved[class="hl-string">'ProcessorUser']
    all_events.append(df_approved)

class=class="hl-string">"hl-comment"># Invoice Posted
df_posted = df_inv[df_inv[class="hl-string">'BSTAT'] != class="hl-string">'V'].copy()
df_posted[class="hl-string">'Activity'] = class="hl-string">'Invoice Posted'
df_posted[class="hl-string">'EventTime'] = pd.to_datetime(df_posted[class="hl-string">'BUDAT'], format=class="hl-string">'%Y%m%d')
all_events.append(df_posted)

class=class="hl-string">"hl-comment"># Invoice Cleared By Payment
if not df_bsak.empty:
    df_cleared = pd.merge(df_inv, df_bsak[df_bsak[class="hl-string">'AUGDT'] != class="hl-string">'00000000'], on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
    df_cleared[class="hl-string">'Activity'] = class="hl-string">'Invoice Cleared By Payment'
    df_cleared[class="hl-string">'EventTime'] = pd.to_datetime(df_cleared[class="hl-string">'AUGDT'], format=class="hl-string">'%Y%m%d')
    all_events.append(df_cleared)

class=class="hl-string">"hl-comment"># Invoice Reversed
df_reversed = df_inv[df_inv[class="hl-string">'STBLG'].notna() & (df_inv[class="hl-string">'STBLG'] != class="hl-string">'')].copy()
if not df_reversed.empty:
    reversal_docs = pd.merge(df_reversed, df_bkpf[[class="hl-string">'BELNR', class="hl-string">'BUDAT']], left_on=class="hl-string">'STBLG', right_on=class="hl-string">'BELNR', suffixes=(class="hl-string">'', class="hl-string">'_rev'))
    reversal_docs[class="hl-string">'Activity'] = class="hl-string">'Invoice Reversed'
    reversal_docs[class="hl-string">'EventTime'] = pd.to_datetime(reversal_docs[class="hl-string">'BUDAT_rev'], format=class="hl-string">'%Y%m%d')
    all_events.append(reversal_docs)

class=class="hl-string">"hl-comment"># Payment Proposal Created
if not df_regup.empty and not df_reguh.empty:
    df_proposal = pd.merge(df_regup, df_reguh, on=[class="hl-string">'LAUFD', class="hl-string">'LAUFI'])
    df_proposal = pd.merge(df_proposal, df_inv, left_on=[class="hl-string">'VBLNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'], right_on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
    df_proposal[class="hl-string">'Activity'] = class="hl-string">'Payment Proposal Created'
    df_proposal[class="hl-string">'EventTime'] = pd.to_datetime(df_proposal[class="hl-string">'LAUFD'], format=class="hl-string">'%Y%m%d')
    all_events.append(df_proposal)

class=class="hl-string">"hl-comment"># Due Date Passed Without Payment (Calculated)
if not df_bsak.empty:
    cleared_keys = df_bsak[class="hl-string">'BELNR'] + df_bsak[class="hl-string">'GJAHR'] + df_bsak[class="hl-string">'BUKRS']
    df_open = df_inv[~(df_inv[class="hl-string">'BELNR'] + df_inv[class="hl-string">'GJAHR'] + df_inv[class="hl-string">'BUKRS']).isin(cleared_keys)].copy()
    df_open[class="hl-string">'DueDate'] = pd.to_datetime(df_open[class="hl-string">'ZFBDT'], format=class="hl-string">'%Y%m%d', errors=class="hl-string">'coerce')
    df_overdue = df_open[(df_open[class="hl-string">'DueDate'] < datetime.now()) & (df_open[class="hl-string">'DueDate'].notna())].copy()
    if not df_overdue.empty:
        df_overdue[class="hl-string">'Activity'] = class="hl-string">'Due Date Passed Without Payment'
        df_overdue[class="hl-string">'EventTime'] = df_overdue[class="hl-string">'DueDate']
        all_events.append(df_overdue)

class=class="hl-string">"hl-comment"># --- 6. FINAL ASSEMBLY & EXPORT ---
print(class="hl-string">"Assembling final event log...")

df_event_log = pd.concat(all_events, ignore_index=True, sort=False)

class=class="hl-string">"hl-comment"># Rename and select columns
column_mapping = {
    class="hl-string">'BELNR': class="hl-string">'InvoiceNumber',
    class="hl-string">'USNAM': class="hl-string">'ProcessorUser',
    class="hl-string">'NAME1': class="hl-string">'VendorName',
    class="hl-string">'BUKRS': class="hl-string">'CompanyCode',
    class="hl-string">'WRBTR': class="hl-string">'InvoiceGrossAmount',
    class="hl-string">'ZFBDT': class="hl-string">'InvoiceDueDate',
    class="hl-string">'ZTERM': class="hl-string">'PaymentTerms',
    class="hl-string">'EBELN': class="hl-string">'PurchaseOrderNumber',
    class="hl-string">'ZLSPR': class="hl-string">'PaymentBlockReason'
}
df_event_log.rename(columns=column_mapping, inplace=True)

class=class="hl-string">"hl-comment"># Ensure required columns exist
final_cols = [
    class="hl-string">'InvoiceNumber', class="hl-string">'Activity', class="hl-string">'EventTime', class="hl-string">'ProcessorUser', class="hl-string">'VendorName', 
    class="hl-string">'CompanyCode', class="hl-string">'InvoiceGrossAmount', class="hl-string">'InvoiceDueDate', class="hl-string">'PaymentTerms', 
    class="hl-string">'PurchaseOrderNumber', class="hl-string">'PaymentBlockReason', class="hl-string">'Approver'
]

for col in final_cols:
    if col not in df_event_log.columns:
        df_event_log[col] = None

df_final = df_event_log[final_cols].sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime']).reset_index(drop=True)

class=class="hl-string">"hl-comment"># Export to CSV
df_final.to_csv(class="hl-string">'sap_ap_event_log.csv', index=False)
print(fclass="hl-string">"Event log created successfully with {len(df_final)} events.")

步骤

前提条件与设置：安装经过认证的第三方ETL工具，例如Theobald Xtract Universal或带SAP Connector的Talend。确保拥有有效许可证，并与SAP ECC系统建立网络连接。为SAP用户账户配置必要的RFC和表读取权限，例如访问所需表的S_RFC和S_TABU_DIS权限。
创建SAP连接：在ETL工具中创建新的SAP连接，填写应用服务器详情、系统ID、客户端编号和专用SAP用户账户的凭据。测试连接，确认连接成功。
定义基础数据源：将SAP核心表添加为提取作业的数据源：BKPF（会计凭证抬头）、BSEG（会计凭证项目）、LFA1（供应商主数据）、RBKP（MM发票抬头）、RSEG（MM发票行项目）、CDHDR（变更凭证抬头）和CDPOS（变更凭证项目）。
建立表连接：通过连接这些表创建基础视图或数据流。使用BUKRS、BELNR和GJAHR将BKPF与BSEG连接；使用LIFNR将BKPF与LFA1连接；使用MM凭证信息将BSEG与RSEG连接，即将BELNR、GJAHR映射到RSEG.BELNR、RSEG.GJAHR；使用OBJECTCLAS='BELEG'以及OBJECTID=公司代码+凭证编号+会计年度将BKPF与CDHDR连接。
应用全局筛选条件：在源端应用筛选条件，限定数据范围。常见条件包括BKPF.BUKRS（公司代码）、BKPF.GJAHR（会计年度）和BKPF.BLART（单据类型，例如“KR”“RE”）。必须对BKPF.CPUDT（录入日期）应用日期范围筛选，以控制数据量。
构建“发票凭证创建”活动：创建第一条数据流，将Activity设置为“发票凭证创建”。EventTime由BKPF.CPUDT（日期）和BKPF.CPUTM（时间）组合而成。从连接后的表中选择所有必需和推荐属性。
构建后续活动流：为其余12项活动分别创建数据流或转换路径。应用特定逻辑和筛选条件，提取对应事件的记录。例如，对于“发票暂存”，筛选BKPF.BSTAT='V'；对于基于变更的事件，则按具体表、字段和值的变化筛选CDHDR和CDPOS，例如使用FNAME='ZLSPL'筛选付款冻结。
提取变更凭证事件：对于“设置付款冻结”和“移除付款冻结”等活动，使用CDHDR和CDPOS中的数据。EventTime为CDHDR.UDATE和CDHDR.UTIME，ProcessorUser为CDHDR.USERNAME。
计算“已过到期日”事件：创建专用转换步骤计算该活动。逻辑为：IF BSEG.AUGBL IS NULL AND CURRENT_DATE > BSEG.ZFBDT THEN create event。该活动的EventTime应设置为到期日（BSEG.ZFBDT）。
合并所有数据流：在ETL工具中使用“Union”或“Merge”转换，将13条活动数据流合并为一个数据集。合并前，确保所有数据流的列名、顺序和数据类型一致。
最终格式化与导出：检查最终合并的数据集，确保EventTime列采用标准时间戳格式，例如YYYY-MM-DD HH:MI:SS。将最终事件日志以UTF-8编码导出为CSV文件，准备上传到流程挖掘工具。

配置

ETL工具与连接器：需要使用带有经过认证的SAP连接器的授权ETL工具。此方法依赖工具的图形界面进行配置，无需直接编写ABAP或SQL代码。
SAP用户授权：SAP用户需要RFC执行权限，以及以下表的显示权限：BKPF、BSEG、LFA1、T001、T003T、RBKP、RSEG、CDHDR、CDPOS、REGUH、REGUP和LFB1。
日期范围筛选：首次加载强烈建议按录入日期字段（如BKPF.CPUDT）进行筛选。首次分析通常选择6至12个月的数据。后续加载可根据变更日期使用增量机制。
关键源端筛选条件：
- BKPF.BUKRS（公司代码）：选择特定公司代码，限定分析范围。
- BKPF.BLART（单据类型）：筛选相关发票类型，通常为“KR”（供应商发票）和“RE”（MM发票收货）。
性能注意事项：从变更凭证表（CDHDR、CDPOS）提取数据可能消耗较多资源。应用OBJECTCLAS='BELEG'和相关事务代码（TCODE）的具体筛选条件，以降低性能影响。避免全表扫描。

a 示例查询 sql

-- This is a logical representation of the ETL configuration.
-- Each 'SELECT' block represents a separate data stream for an activity,
-- which are then combined using a UNION ALL transformation in the ETL tool.

-- Base Data Source: A join of BKPF, BSEG, LFA1, RBKP, RSEG
-- Let's call this joined view 'INVOICE_DETAILS'

-- 1. Invoice Document Created
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, -- Case ID
    'Invoice Document Created' AS Activity,
    TO_TIMESTAMP(B.CPUDT, B.CPUTM) AS EventTime,
    B.USNAM AS ProcessorUser,
    L.NAME1 AS VendorName,
    B.BUKRS AS CompanyCode,
    B.WAERS AS Currency,
    (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, 
    S1.ZFBDT AS InvoiceDueDate, -- Due date from first line item
    S1.ZTERM AS PaymentTerms,
    S1.EBELN AS PurchaseOrderNumber,
    S1.ZLSPR AS PaymentBlockReason,
    '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' -- First vendor line item
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.CPUDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 2. Invoice Parked
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber,
    'Invoice Parked' AS Activity,
    TO_TIMESTAMP(B.CPUDT, B.CPUTM) AS EventTime,
    B.USNAM AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE B.BSTAT = 'V' AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.CPUDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 3. Purchase Order Matched
SELECT DISTINCT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber,
    'Purchase Order Matched' AS Activity, 
    TO_TIMESTAMP(B.CPUDT, B.CPUTM) AS EventTime, 
    B.USNAM AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE S1.EBELN IS NOT NULL AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.CPUDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 4. Goods Receipt Matched
SELECT DISTINCT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber,
    'Goods Receipt Matched' AS Activity, 
    TO_TIMESTAMP(B.CPUDT, B.CPUTM) AS EventTime, 
    B.USNAM AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN RSEG AS R ON S1.BELNR = R.BELNR AND S1.GJAHR = R.GJAHR
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE R.LFBNR IS NOT NULL AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.CPUDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 5. Payment Block Set
SELECT
    CONCAT(SUBSTRING(H.OBJECTID, 1, 4), SUBSTRING(H.OBJECTID, 5, 10), SUBSTRING(H.OBJECTID, 15, 4)) AS InvoiceNumber,
    'Payment Block Set' AS Activity,
    TO_TIMESTAMP(H.UDATE, H.UTIME) AS EventTime,
    H.USERNAME AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, P.VALUE_NEW AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM CDHDR H
JOIN CDPOS P ON H.CHANGENR = P.CHANGENR
JOIN BKPF B ON SUBSTRING(H.OBJECTID, 5, 10) = B.BELNR AND SUBSTRING(H.OBJECTID, 1, 4) = B.BUKRS AND SUBSTRING(H.OBJECTID, 15, 4) = B.GJAHR
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE H.OBJECTCLAS = 'BELEG' AND P.TABNAME = 'BSEG' AND P.FNAME = 'ZLSPR' AND P.VALUE_NEW <> ''
AND B.BUKRS = '[YourCompanyCode]' AND H.UDATE BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 6. Invoice Routed For Approval (Inferred from specific block)
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Invoice Routed For Approval' AS Activity, TO_TIMESTAMP(H.UDATE, H.UTIME) AS EventTime, H.USERNAME AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, P.VALUE_NEW AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM CDHDR H JOIN CDPOS P ON H.CHANGENR = P.CHANGENR JOIN BKPF B ON SUBSTRING(H.OBJECTID, 5, 10) = B.BELNR AND SUBSTRING(H.OBJECTID, 1, 4) = B.BUKRS AND SUBSTRING(H.OBJECTID, 15, 4) = B.GJAHR JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE H.OBJECTCLAS = 'BELEG' AND P.TABNAME = 'BSEG' AND P.FNAME = 'ZLSPR' AND P.VALUE_NEW = '[ApprovalBlockReasonCode]'
AND B.BUKRS = '[YourCompanyCode]' AND H.UDATE BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 7. Invoice Approved (Inferred from removal of specific block)
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Invoice Approved' AS Activity, TO_TIMESTAMP(H.UDATE, H.UTIME) AS EventTime, H.USERNAME AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, '' AS PaymentBlockReason, H.USERNAME AS Approver
FROM CDHDR H JOIN CDPOS P ON H.CHANGENR = P.CHANGENR JOIN BKPF B ON SUBSTRING(H.OBJECTID, 5, 10) = B.BELNR AND SUBSTRING(H.OBJECTID, 1, 4) = B.BUKRS AND SUBSTRING(H.OBJECTID, 15, 4) = B.GJAHR JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE H.OBJECTCLAS = 'BELEG' AND P.TABNAME = 'BSEG' AND P.FNAME = 'ZLSPR' AND P.VALUE_OLD = '[ApprovalBlockReasonCode]' AND P.VALUE_NEW = ''
AND B.BUKRS = '[YourCompanyCode]' AND H.UDATE BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 8. Payment Block Removed
SELECT
    CONCAT(SUBSTRING(H.OBJECTID, 1, 4), SUBSTRING(H.OBJECTID, 5, 10), SUBSTRING(H.OBJECTID, 15, 4)) AS InvoiceNumber, 'Payment Block Removed' AS Activity, TO_TIMESTAMP(H.UDATE, H.UTIME) AS EventTime, H.USERNAME AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, '' AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM CDHDR H JOIN CDPOS P ON H.CHANGENR = P.CHANGENR JOIN BKPF B ON SUBSTRING(H.OBJECTID, 5, 10) = B.BELNR AND SUBSTRING(H.OBJECTID, 1, 4) = B.BUKRS AND SUBSTRING(H.OBJECTID, 15, 4) = B.GJAHR JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE H.OBJECTCLAS = 'BELEG' AND P.TABNAME = 'BSEG' AND P.FNAME = 'ZLSPR' AND P.VALUE_NEW = '' AND P.VALUE_OLD <> ''
AND B.BUKRS = '[YourCompanyCode]' AND H.UDATE BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 9. Invoice Posted (Change from Parked to Posted)
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Invoice Posted' AS Activity, TO_TIMESTAMP(H.UDATE, H.UTIME) AS EventTime, H.USERNAME AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM CDHDR H JOIN CDPOS P ON H.CHANGENR = P.CHANGENR JOIN BKPF B ON SUBSTRING(H.OBJECTID, 5, 10) = B.BELNR AND SUBSTRING(H.OBJECTID, 1, 4) = B.BUKRS AND SUBSTRING(H.OBJECTID, 15, 4) = B.GJAHR JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE H.OBJECTCLAS = 'BELEG' AND P.TABNAME = 'BKPF' AND P.FNAME = 'BSTAT' AND P.VALUE_OLD = 'V' AND P.VALUE_NEW = ''
AND B.BUKRS = '[YourCompanyCode]' AND H.UDATE BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 10. Due Date Passed Without Payment (Calculated)
SELECT 
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Due Date Passed Without Payment' AS Activity, TO_TIMESTAMP(S1.ZFBDT) AS EventTime, 'SYSTEM' AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K' JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE S1.AUGBL IS NULL AND S1.ZFBDT < CURRENT_DATE
AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.CPUDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 11. Payment Proposal Created
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Payment Proposal Created' AS Activity, TO_TIMESTAMP(H.LAUFD) AS EventTime, H.USNAM AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM REGUH H
JOIN REGUP P ON H.LAUFD = P.LAUFD AND H.LAUFI = P.LAUFI AND H.XVORL = P.XVORL
JOIN BKPF B ON P.BUKRS = B.BUKRS AND P.BELNR = B.BELNR AND P.GJAHR = B.GJAHR
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE B.BUKRS = '[YourCompanyCode]' AND H.LAUFD BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 12. Invoice Cleared By Payment
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Invoice Cleared By Payment' AS Activity, TO_TIMESTAMP(S1.AUGDT) AS EventTime, B.USNAM AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, '' AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE S1.AUGBL IS NOT NULL
AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND S1.AUGDT BETWEEN '[StartDate]' AND '[EndDate]'

UNION ALL

-- 13. Invoice Reversed
SELECT
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS InvoiceNumber, 'Invoice Reversed' AS Activity, TO_TIMESTAMP(B.STODT) AS EventTime, B.USNAM_REV AS ProcessorUser, L.NAME1 AS VendorName, B.BUKRS AS CompanyCode, B.WAERS AS Currency, (SELECT SUM(WRBTR) FROM BSEG S WHERE S.BELNR = B.BELNR AND S.GJAHR = B.GJAHR AND S.BUKRS = B.BUKRS) AS InvoiceGrossAmount, S1.ZFBDT AS InvoiceDueDate, S1.ZTERM AS PaymentTerms, S1.EBELN AS PurchaseOrderNumber, S1.ZLSPR AS PaymentBlockReason, '[Workflow Approver]' AS Approver
FROM BKPF AS B
JOIN BSEG AS S1 ON B.BELNR = S1.BELNR AND B.GJAHR = S1.GJAHR AND B.BUKRS = S1.BUKRS AND S1.KOART = 'K'
JOIN LFA1 AS L ON B.LIFNR = L.LIFNR
WHERE B.STBLG IS NOT NULL
AND B.BLART IN ('KR', 'RE') AND B.BUKRS = '[YourCompanyCode]' AND B.STODT BETWEEN '[StartDate]' AND '[EndDate]'

准备好开始了吗？

使用此模板开启您的流程挖掘之旅。立即借助数据驱动的洞察，开始改进应付账款发票处理。

返回改进指南下载模板

告别滞纳金：立即优化应付账款，节省60%

借助我们的平台消除重复付款、实现处理自动化，并降低60%的成本。

开始免费试用

无需信用卡，几分钟即可完成设置

您的应付账款发票处理数据模板

您的应付账款发票处理数据模板

应付账款发票处理属性

应付账款发票处理活动

提取指南

用于提取应付账款发票数据的自定义ABAP程序

步骤

配置

a 示例查询 abap

直接查询SAP ECC底层数据库

步骤

配置

a 示例查询 sql

通过SAP RFC协议使用Python脚本提取数据

步骤

配置

a 示例查询 python

使用经过认证的SAP连接器的ETL工具，例如Theobald

步骤

配置

a 示例查询 sql

准备好开始了吗？

告别滞纳金：立即优化应付账款，节省60%

您的应付账款发票处理数据模板

您的应付账款发票处理数据模板

应付账款发票处理属性

应付账款发票处理活动

提取指南

用于提取应付账款发票数据的自定义ABAP程序

步骤

配置

a 示例查询 abap

直接查询SAP ECC底层数据库

步骤

配置

a 示例查询 sql

通过SAP RFC协议使用Python脚本提取数据

步骤

配置

a 示例查询 python

使用经过认证的SAP连接器的ETL工具，例如Theobald

步骤

配置

a 示例查询 sql

准备好开始了吗？

告别滞纳金：立即优化应付账款，节省60%

我们重视您的隐私