您的应收账款数据模板

SAP S/4HANA

← 返回改进指南

应收账款属性 (20) 应收账款活动 (13) 提取指南 (4)

您的应收账款数据模板

此模板为您在SAP S/4HANA中梳理收款和付款流程提供完整框架。其中包括建议采集的关键属性、需要跟踪的一系列流程活动，以及从财务表提取数据的技术指南。遵循这一结构，您可以清晰了解现金流周期，并识别需要人工干预或存在付款延迟的环节。

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

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

下载模板

应收账款属性

这些是应纳入事件日志的推荐数据字段和维度属性，用于全面分析您的应收账款流程。

5 必需 10 建议 5 可选

	名称	说明
	事件时间 EventTime	活动发生时的时间戳。
说明此属性记录活动发生的准确日期和时间。对于事务数据，它通常由抬头表中的录入日期（CPUDT）和录入时间（CPUTM）组合而成，可用于计算周期时间和处理时长。为什么重要准确的时间戳对于计算DSO和识别瓶颈至关重要。获取位置 SAP表BKPF-CPUDT和BKPF-CPUTM 示例 2023-10-12T08:30:00Z2023-10-15T14:45:12Z2023-11-01T09:15:00Z
	发票编号 InvoiceNumber	发票或会计凭证的唯一标识符。
说明此属性是应收账款流程的唯一Case ID。根据提取逻辑，它通常对应SAP FI模块中的会计凭证编号（BELNR）或SD中的开票凭证（VBELN）。该属性将付款、争议和清账等后续活动关联到最初的财务义务。为什么重要这是重建每笔交易端到端流程顺序流所需的核心键。获取位置 SAP表BKPF-BELNR或VBRK-VBELN 示例 1400000234900004321014000002351800000099
	活动 Activity	流程中执行的具体事件或操作。
说明此属性描述AR流程中执行的步骤，例如创建发票、过账部分付款或清账发票。它通常源自事务代码（TCODE）或变更日志表中的凭证状态变更。为什么重要它对于映射流程顺序流和识别事件顺序至关重要。获取位置源自SAP TSTCT（事务代码）或CDHDR/CDPOS（变更凭证）示例创建应收账款发票过账部分付款清账发票创建争议案件
	最后数据更新时间 LastDataUpdate	数据最后一次提取或刷新的时间戳。
说明此属性表示数据加载到流程挖掘工具的时间，帮助用户了解仪表板中所呈现数据的新鲜度，例如Period End Reconciliation Status。为什么重要确保分析人员了解当前查看的是实时数据还是历史数据。获取位置 ETL元数据示例 2023-11-05T00:00:00Z2023-11-05T06:00:00Z
	源系统 SourceSystem	数据产生的系统。
说明此属性标识创建记录的SAP S/4HANA具体实例或外部系统。在包含多个ERP实例的系统环境中，它有助于追溯数据来源。为什么重要支持在多系统环境中分别开展分析。获取位置系统ID（SY-SYSID）或在提取过程中硬编码示例 SAP_S4H_PRODSAP_S4H_01LEGACY_ERP
	付款条件 PaymentTerms	表示双方约定付款条件的键。
说明此属性（ZTERM）定义客户付款所依据的条件，包括到期日和现金折扣，是Early Payment Discount Optimization仪表板的主要数据来源。为什么重要用于计算到期日和分析折扣资格的基准。获取位置 SAP表BSEG-ZTERM或KNB1-ZTERM 示例 NT300001Z015
	公司代码 CompanyCode	发票所属的财务实体或业务单元。
说明公司代码（BUKRS）是SAP系统外部会计的核心组织单位，可用于按不同业务单元划分分析范围，例如Customer Payment Behavior Profiling仪表板。为什么重要对于筛选和比较不同法人实体的绩效至关重要。获取位置 SAP表BKPF-BUKRS或T001-BUKRS 示例 1000US01DE012000
	凭证类型 DocumentType	用于对会计凭证进行分类，例如发票、付款和贷项凭证。
说明凭证类型（BLART）用于对交易分类。例如，DR表示客户发票，DZ表示客户付款，DG表示贷项凭证。这对于筛选特定Case类型、开展Credit Memo and Correction Analysis至关重要。为什么重要将数据划分为发票、付款和调整。获取位置 SAP表BKPF-BLART 示例 DRDZDGRV
	到期日 DueDate	发票必须完成付款的计算日期。
说明此属性表示净到期日。在SAP中，通常根据基准日期（ZFBDT）加上付款条件（ZTERM）规定的天数计算得出，是Dunning Compliance and Execution仪表板的基准。为什么重要用于判断付款是否逾期的参考点。获取位置源自BSEG-ZFBDT和BSEG-ZTERM 示例 2023-11-302023-12-15
	客户编号 CustomerNumber	客户账户的唯一标识符。
说明此属性表示与发票关联的客户编号（KUNNR），支持详细分析付款行为，也是Customer Payment Behavior Profiling仪表板的关键数据。为什么重要支持深入分析特定客户账户的绩效。获取位置 SAP表BSEG-KUNNR或KNA1-KUNNR 示例 CUST100230001004500WALMART_US
	是否自动执行 IsAutomated	表示活动是否由系统用户执行的标志。
说明此布尔属性根据User属性计算。如果用户ID匹配已知系统账户或批处理账户（例如“BNK_BSM”），则该标志设为true。它直接驱动Payment Matching Automation Rate仪表板。为什么重要衡量数字化转型和自动化成效的关键指标。获取位置源自User属性示例 truefalse
	未清销售天数 DaysSalesOutstanding	从发票过账到清账的时长。
说明对于已清账项目，该指标通过清账日期减去过账日期计算得出，是Days Sales Outstanding Overview仪表板的核心数值，也是衡量现金流效率的重要指标。为什么重要 AR流程面向管理层的核心KPI。获取位置计算方式：ClearingDate-PostingDate 示例 30天45天15天
	本位币金额 AmountInLocalCurrency	发票或付款按公司代码货币计价的金额。
说明此属性（DMBTR）包含交易的财务金额，用于计算未清发票、贷项凭证和付款的总价值，并通过量化金额支持Credit Memo and Correction Analysis仪表板。为什么重要用于财务影响分析，并确定高价值催收事项的优先级。获取位置 SAP表BSEG-DMBTR或ACDOCA-DMBTR 示例 1500.00250.5010000.00
	清账日期 ClearingDate	发票完成清账或结算的日期。
说明此属性（AUGDT）记录未清项目完成清账的时间，通常由付款触发。它是AR核心流程周期的结束时间戳，也是计算未清销售天数的主要变量。为什么重要定义催收周期的完成时间。获取位置 SAP表BSEG-AUGDT或BSAD-AUGDT 示例 2023-11-012023-11-10
	用户名 User	执行活动的用户ID。
说明此属性（USNAM）标识负责该事件的用户或系统代理。通过区分人工用户和系统批处理用户，可据此计算Automated Payment Matching Rate。为什么重要对于确定自动化率和用户生产力至关重要。获取位置 SAP表BKPF-USNAM或CDHDR-USERNAME 示例 JSMITHBATCH_USERSAP_WFALEX_D
	争议Case ID DisputeCaseId	与发票关联的任何争议Case的标识符。
说明如果存在争议Case，此属性保存其ID（UDM_CASE），并将财务凭证关联到Dispute Management模块，是Dispute Resolution Cycle Efficiency仪表板所需的数据。为什么重要将AR凭证连接到争议解决工作流。获取位置 SAP表FDM_DCPROC或UDM_CASE_ATTR 示例 0000056789DISP-2023-001
	催款级别 DunningLevel	发票当前的催款级别。
说明此属性（MAHNS）表示客户被提醒付款的次数，用于跟踪催收措施的升级情况，并在Dunning Compliance and Execution仪表板中展示。为什么重要监控付款逾期的严重程度以及对催款政策的遵循情况。获取位置 SAP表MHNK-MAHNS或KNB5（客户主数据催款信息）示例 1230
	国家 Country	客户所在国家。
说明此属性（LAND1）来自客户主数据，可按地区或国家划分KPI，例如分析各地区或国家的贷项凭证率。为什么重要用于区域合规和绩效分析。获取位置 SAP表KNA1-LAND1 示例 USDEFRJP
	地区 Region	客户所在的州、省或地区。
说明此属性（REGIO）比国家提供更细粒度的地理视图，可用于Credit Memo and Correction Analysis，以识别区域处理问题。为什么重要用于细粒度的地理分析。获取位置 SAP表KNA1-REGIO 示例 CANYTXBY
	是否为贷项凭证 IsCreditMemo	表示凭证是否为贷项凭证的标志。
说明此布尔属性源自凭证类型，例如类型等于“DG”时为true。它支持快速筛选更正交易，用于计算Corrective Credit Memo Rate KPI。为什么重要识别返工和开票错误。获取位置源自DocumentType 示例 truefalse

必需建议可选

应收账款活动

这些是应采集到事件日志中的关键流程步骤和交易里程碑，用于准确发现流程并识别瓶颈。

6 建议 7 可选

	活动	说明
	创建应收账款发票	在财务系统中首次创建客户发票凭证。该事件取自凭证抬头的创建时间戳。
为什么重要标志着应收账款流程和账龄计时的开始，是计算应收账款周转天数（DSO）的基础。获取位置 BKPF表（会计凭证抬头），字段CPUDT（录入日期）和CPUTM（录入时间）。按与客户发票相关的凭证类型（BLART）筛选，例如DR、RV。采集交易提交至BKPF时记录事件类型 explicit
	发送付款提醒	针对逾期发票生成催收通知或提醒函，用于跟踪组织开展的催收工作。
为什么重要这是催收合规率KPI的关键依据，用于验证催收策略是否按政策执行。获取位置 MHND表（催收数据），字段LAUFD（催收运行日期）。通过公司代码和客户进行关联。采集执行催收运行（F150）时记录事件类型 explicit
	收到全额付款	收到足以完全覆盖未付发票金额的款项，通常意味着客户付款义务已履行完毕。
为什么重要用于确定实际付款日期与到期日之间的差异，是分析付款条件偏差和客户行为的关键。获取位置 BSEG表。根据清账凭证（AUGBL）推导，清账日期（AUGDT）已填写且余额变为零。采集清账凭证过账时记录事件类型 explicit
	清账发票	技术性会计步骤，将未清项目与付款或贷项进行匹配，并将状态变更为Cleared。
为什么重要系统中该流程实例的最终结束点，是计算最终DSO指标所必需的记录。获取位置 BSEG或ACDOCA表。字段AUGDT（清账日期）已填写。使用USNAM判断操作是自动完成还是人工完成。采集 AUGDT字段填写时记录事件类型 explicit
	生成发票输出	生成输出消息（电子邮件、打印或EDI）并将发票发送给客户的明确操作。这表明客户已正式收到开票请求。
为什么重要创建与发送之间的延迟会影响客户实际可用的付款期限。监控该环节可以确保发票不会卡在内部处理流程中。获取位置 NAST表（消息状态），使用与发票凭证编号匹配的对象键（OBJKY）。时间戳取自DATVR和UHRVR。采集生成输出时记录在NAST表中事件类型 explicit
	过账部分付款	收到的付款不足以覆盖发票全额，在账户中留下剩余项目。
为什么重要影响短款解决周期。频繁的部分付款会增加流程复杂度和人工对账工作量。获取位置 BSEG表。当清账凭证（AUGBL）创建新的剩余项目，且参考凭证匹配原始发票并存在未结余额时推断得出。采集根据带有剩余余额的清账凭证创建记录推导事件类型 inferred
	冲销发票	由于存在根本性错误，系统中原始发票被取消或冲销。
为什么重要表示流程实例失败。将其排除或单独分析，对确保数据质量至关重要。获取位置 BKPF表，原始发票记录中的STBLG（冲销凭证编号）字段已填写。采集 STBLG字段填写时记录事件类型 explicit
	创建争议案件	针对发票创建争议案件，通常通过SAP FSCM争议管理完成，表示客户对账单提出异议。
为什么重要标准催收计时在此暂停，同时开始计算争议解决周期。此类案件数量过多会影响现金流的可预测性。获取位置 UDM_CASE_ATTR表（案件属性）或UDM_DISPUTE表（争议案件）。通过CASE_GUID关联至财务凭证键。采集记录在FSCM争议管理表中事件类型 explicit
	更新争议状态	现有争议案件的状态发生变化，例如从“新建”变为“处理中”或“审核中”。
为什么重要细化争议解决工作流，帮助识别审批或调查阶段的瓶颈。获取位置争议案件的变更凭证（S/4HANA案件管理日志）。分析UDM_CASE_ATTR中的变更记录。采集比较案件历史中的状态字段变更事件类型 inferred
	核对银行对账单	银行对账单行项目成功匹配至分类账中的付款凭证，完成现金状况确认。
为什么重要用于跟踪银行对账单核对周期。该环节的延迟会影响财务结账速度。获取位置 FEBEP表（电子银行对账单行项目）。将付款凭证与银行对账单交易进行匹配。采集记录在EBS表（FEBEP/FEBKO）中事件类型 explicit
	解决争议案件	争议案件关闭，结果可能是贷项、核销或重新确认欠款，标志着例外处理结束。
为什么重要用于计算平均争议解决时间KPI的结束时间戳。提高此环节效率可以直接释放被占用的营运资本。获取位置 UDM_CASE_ATTR表，字段ESCALATION_DATE或CLOSED_DATE，具体取决于配置。采集案件状态设为Closed或Void时记录事件类型 explicit
	记录付款承诺	催收人员记录客户的正式承诺，即在指定日期前支付指定金额。
为什么重要表示催收人员工作取得成效，可用于预测短期现金流并衡量催收人员处理效率。获取位置 UDM_P2P表（付款承诺），字段CREATED_AT。通过案件GUID或凭证键关联至发票。采集记录在FSCM催收管理中事件类型 explicit
	过账贷项凭证	开具与发票关联的贷项凭证，减少应付金额，通常用于纠正错误或处理退货。
为什么重要纠正性贷项凭证率KPI的关键指标。频繁出现通常表明销售或开票上游存在质量问题。获取位置 BSEG表，凭证类型为“DG”或“KG”。通过“发票参考”（REBZG）字段识别，该字段指向原始发票。采集贷项凭证引用发票时记录事件类型 explicit

建议可选

提取指南

如何从SAP S/4HANA获取数据

步骤

确定数据源：本次提取使用SAP S/4HANA标准Core Data Services（CDS）视图。主要视图为I_JournalEntryItem，其中包含所有财务交易的明细行项目，包括发票、付款和清账事件。
准备SQL环境：确保您可以访问SAP HANA数据库中的SQL。您可以通过SAP HANA Studio、DBACOCKPIT事务，或配置了ODBC/JDBC连接并连接到S/4HANA实例的外部ETL工具实现访问。
定义范围和筛选条件：确定需要提取的公司代码和会计年度。以下查询为这些值预留了占位符，以提升性能和相关性。
提取财务活动：查询的第一部分针对I_JournalEntryItem视图，筛选Account Type为“D”（Customer），以提取应收账款数据。查询根据Financial Account Type和Document Type区分发票、贷项通知单和付款。
提取清账和结算信息：查询通过对I_JournalEntryItem执行自连接或单独查询，识别发票完成清账的时间。根据清账时间生成“Clear Invoice”和“Receive Full Payment”活动。
整合运营事件：如需捕获“Send Payment Reminder”或“Create Dispute Case”等非财务活动，查询会连接I_DunningHistory、I_DisputeCase和I_OutputRequestItem等专用视图。请注意，争议管理和输出管理是否可用取决于您的S/4HANA具体配置。
处理银行对账：“Reconcile Bank Statement”活动源自I_BankStatementLineItem，用于将外部银行对账单与内部会计凭证关联起来。
统一输出列：将所有时间戳转换为标准格式，并确保所需列（InvoiceNumber、Activity、EventTime）均已填充。货币金额转换为统一的浮点或十进制格式。
执行并验证：先针对小范围数据运行查询，例如一个公司代码、一个月的数据，以验证逻辑。检查行数是否与预期交易量一致。
导出数据：将结果集保存为CSV或Parquet文件。确保编码设置为UTF-8，以正确处理User Names或Text descriptions中的特殊字符。
导入ProcessMind：将提取的文件上传到ProcessMind。在导入配置中，将“InvoiceNumber”列映射到Case ID字段，将“EventTime”映射到Timestamp字段。

配置

源视图：使用标准S/4HANA CDS视图（I_JournalEntryItem、I_DunningHistory、I_DisputeCase、I_OutputRequestItem）。
日期范围：建议提取最近12个月的数据，以覆盖未结发票的完整生命周期。
筛选：按公司代码（CompanyCode）和会计年度（FiscalYear）筛选，限制数据量。
账户类型：严格筛选AccountType = 'D'，确保仅提取Customer/AR数据。
性能：CDS视图属于虚拟数据模型。对于超大数据集，可考虑将结果实体化，或在业务低峰期执行提取。
定制：该逻辑假设使用标准SAP凭证类型，例如发票RV、付款DZ。如果您的组织使用自定义凭证类型，请调整查询中的CASE语句。

a 示例查询 sql

/* ACCOUNTS RECEIVABLE EXTRACTION - SAP S/4HANA CDS VIEWS */
/* METHOD: SQL Union of multiple CDS Views */

/* 1. Create AR Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create AR Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    CASE 
        WHEN JE.ClearingDate IS NOT NULL THEN DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) 
        ELSE NULL 
    END AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D' -- Customer
  AND JE.AccountingDocumentType IN ('RV', 'DR') -- Invoice Types
  AND JE.Ledger = '0L' -- Leading Ledger
  AND JE.CompanyCode IN ('[Your Company Code]')
  AND JE.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 2. Issue Invoice Output (Requires S/4 Output Management) */
SELECT 
    CAST(B.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Issue Invoice Output' AS Activity,
    O.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.CompanyCode,
    B.SoldToParty AS CustomerNumber,
    CAST(B.TotalNetAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    O.CreatedByUser AS "User",
    B.BillingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BillingDocument AS B
JOIN I_OutputRequestItem AS O ON O.BillingDocument = B.BillingDocument
WHERE B.CompanyCode IN ('[Your Company Code]')
  AND B.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 3. Send Payment Reminder */
SELECT 
    CAST(D.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Send Payment Reminder' AS Activity,
    D.DunningDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    D.CompanyCode,
    D.Customer AS CustomerNumber,
    CAST(D.AmountInLocalCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    'System' AS "User",
    'Dunning' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DunningHistory AS D
WHERE D.CompanyCode IN ('[Your Company Code]')
  AND D.DunningDate >= '[Your Start Date]'

UNION ALL

/* 4. Create Dispute Case (Requires FSCM) */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create Dispute Case' AS Activity,
    DC.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    CAST(DC.DisputedAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    DC.CreatedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
/* Note: Join logic simplified. Ideally link via Case ID reference in JE if configured */
WHERE DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 5. Update Dispute Status */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Update Dispute Status' AS Activity,
    DC.LastChangeDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.LastChangeDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 6. Resolve Dispute Case */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Resolve Dispute Case' AS Activity,
    DC.ClosingDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.ClosingDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 7. Record Promise To Pay */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Record Promise To Pay' AS Activity,
    PP.PromiseToPayDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    PP.CompanyCode,
    PP.Customer AS CustomerNumber,
    CAST(PP.PromiseAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    PP.CreatedByUser AS "User",
    'Promise' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_PromiseToPay AS PP
JOIN I_JournalEntryItem AS JE ON PP.CompanyCode = JE.CompanyCode AND PP.Customer = JE.Customer
WHERE PP.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 8. Post Partial Payment (Logic: Payment Doc exists but invoice not fully cleared immediately) */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Partial Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP') -- Payment Types
  AND JE_INV.ClearingDate IS NULL -- Invoice still open
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 9. Post Credit Memo */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Credit Memo' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.AccountingDocumentType IN ('DG') -- Credit Memo
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 10. Receive Full Payment */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Receive Full Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP')
  AND JE_INV.ClearingDate IS NOT NULL -- Fully cleared
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 11. Clear Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Clear Invoice' AS Activity,
    CAST(JE.ClearingDate AS TIMESTAMP) AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    'Clearing' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.ClearingDate IS NOT NULL
  AND JE.AccountingDocumentType IN ('RV', 'DR')
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 12. Reconcile Bank Statement */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reconcile Bank Statement' AS Activity,
    BS.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(BS.AmountInTransactionCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    BS.CreatedByUser AS "User",
    'BankStmt' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BankStatementLineItem AS BS
JOIN I_JournalEntryItem AS JE ON BS.BankStatementShortID = JE.Reference1IDByBusinessPartner -- Check mapping in your system
WHERE JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 13. Reverse Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reverse Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.IsReversal = 'X' 
  OR JE.IsReversed = 'X'
  AND JE.CompanyCode IN ('[Your Company Code]')

步骤

确认可以通过已批准的SQL客户端、数据库视图或提取工具直接读取SAP HANA。获取ACDOCA和BKPF的授权，并确认相关SAP S/4HANA版本及字段可用性。由于输出管理、催收、争议管理、付款承诺和银行对账通常不会完整记录在ACDOCA和BKPF中，请在实施前确定这些活动对应的已批准源表或视图。
配置连接字符串、凭据、提取开始日期、提取结束日期、公司代码筛选条件及任何凭证类型筛选条件。初始使用三到六个月的期间，完成核对和性能测试后再延长范围。
对已批准的SAP HANA架构执行下方查询。查询从ACDOCA和BKPF提取会计凭证事件，并为需要输出管理、催收、争议管理、付款承诺、银行对账或其他应用数据的活动使用可配置源对象。执行前，将每个方括号中的源对象和字段占位符替换为系统中已批准的对象。查询不会臆造SAP对象名称。
确保每个源分支都返回统一的事件日志结构。每项活动都作为明确的独立行输出。ProcessMind不会根据其他行推断输出、催收、争议、付款、清账、冲销或对账事件。
验证连接和标识符。根据适用情况使用会计凭证编号、公司代码、会计年度和客户编号。将发票编号保留为案例标识符。对于付款、贷项凭证、清账及相关凭证，根据已批准的关联逻辑，将InvoiceNumber填充为发票或会计凭证标识符。
如有需要，在提取后执行数据转换。将时间戳统一到一致的时区，严格按指定格式标准化活动标签，将金额转换为统一的小数类型；源数据未提供属性时保留NULL。仅在发票过账时间和清账日期均可用时计算DaysSalesOutstanding。
将结果与SAP凭证数量、未清项目报告、清账信息、输出日志、催收记录、争议记录、付款承诺记录、付款凭证、贷项凭证和银行对账记录进行核对。调查重复行、缺失活动、币种不一致以及超出请求日期范围的凭证。
将最终数据集导出为UTF-8 CSV或其他ProcessMind支持的表格格式。包含InvoiceNumber、Activity、EventTime、SourceSystem、LastDataUpdate、CompanyCode、CustomerNumber、AmountInLocalCurrency、User、DocumentType、PaymentTerms、DueDate、ClearingDate、IsAutomated和DaysSalesOutstanding列。上传文件或配置已批准的定期提取，然后确认ProcessMind将全部13项活动显示为明确的事件行。

配置

提取方式：直接对ACDOCA和BKPF执行SAP HANA SQL，并使用经批准的、针对具体系统的源对象补充这些表中未完整存储的活动。
日期范围：从三至六个月开始。根据业务问题设置适当的过账日期或创建时间戳范围，并保留足够历史数据，以覆盖在期间开始附近创建的发票所关联的清账、争议、催款和付款事件。
公司代码：筛选所需的CompanyCode值。如果多个法人共用同一SAP系统，请使用针对公司的筛选占位符。
凭证类型：限制为目标系统中配置的相关发票、付款、贷项通知单、冲销和清账凭证类型。不要假设凭证类型代码具有通用性。
案例标识符：统一使用InvoiceNumber。定义并记录发票凭证、付款凭证、贷项通知单、清账凭证和争议记录之间的关联关系。
时间戳处理：使用统一的时区和精度。如果源数据仅提供日期，请按约定的业务默认时间表示事件，并记录这一限制。
LastDataUpdate：填入由查询或编排层生成的提取或刷新时间戳，而不是会计过账日期。
输出和催款源：为发票输出生成和付款提醒配置经批准的源对象。仅凭ACDOCA和BKPF无法可靠证明发票已发送或催款通知已生成。
争议和付款承诺源：为争议创建、状态变更、解决和付款承诺配置经批准的SAP FSCM或系统专用源对象。确认争议记录与InvoiceNumber之间的关联关系。
银行对账源：为Reconcile Bank Statement配置经批准的银行对账单或现金管理源。仅有分类账过账不能证明银行对账单已成功匹配。
性能：按日期和CompanyCode筛选，仅选择所需列，避免不受限制的全表扫描，并在经批准的报表窗口执行。条件允许时使用分区裁剪以及经批准的计算视图或提取层。
授权：确认对ACDOCA、BKPF及所有已配置补充源具有数据库或分析读取授权。确认隐私、职责分离和生产访问政策允许执行提取。
前提条件：确认相关SAP组件、输出管理、催款、争议管理、付款处理、银行对账处理和数据保留设置已启用，前提是这些活动为必需项。执行前，验证所有方括号占位符是否与目标系统一致。

a 示例查询 sql

WITH
params AS (
    SELECT
        CAST('[Start date and time]' AS TIMESTAMP) AS start_ts,
        CAST('[End date and time]' AS TIMESTAMP) AS end_ts,
        CAST('[Last data update timestamp]' AS TIMESTAMP) AS last_data_update
    FROM DUMMY
),
base_acdoca AS (
    SELECT
        a.RBUKRS AS CompanyCode,
        a.BELNR AS AccountingDocumentNumber,
        a.GJAHR AS FiscalYear,
        a.BUZEI AS LineItem,
        a.KUNNR AS CustomerNumber,
        a.HSL AS AmountInLocalCurrency,
        a.BLART AS DocumentType,
        a.ZTERM AS PaymentTerms,
        a.BUDAT AS PostingDate,
        a.AUGDT AS ClearingDate,
        a.AUGBL AS ClearingDocumentNumber,
        a.USNAM AS UserName,
        a.BELNR AS InvoiceNumber,
        CAST(a.BUDAT AS TIMESTAMP) AS PostingTimestamp
    FROM ACDOCA a
    CROSS JOIN params p
    WHERE CAST(a.BUDAT AS TIMESTAMP) >= p.start_ts
      AND CAST(a.BUDAT AS TIMESTAMP) < p.end_ts
      AND a.RBUKRS = '[Company Code filter]'
),
base_bkpf AS (
    SELECT
        b.BUKRS AS CompanyCode,
        b.BELNR AS AccountingDocumentNumber,
        b.GJAHR AS FiscalYear,
        b.CPUDT AS CreationDate,
        b.CPUTM AS CreationTime,
        b.USNAM AS UserName,
        b.BLART AS DocumentType
    FROM BKPF b
),
invoice_events AS (
    SELECT
        x.InvoiceNumber,
        'Create AR Invoice' AS Activity,
        CAST(TO_TIMESTAMP(TO_VARCHAR(h.CreationDate) || TO_VARCHAR(h.CreationTime), 'YYYYMMDDHH24MISS') AS TIMESTAMP) AS EventTime,
        'SAP S/4HANA' AS SourceSystem,
        p.last_data_update AS LastDataUpdate,
        x.CompanyCode,
        x.CustomerNumber,
        x.AmountInLocalCurrency,
        COALESCE(h.UserName, x.UserName) AS UserName,
        x.DocumentType,
        x.PaymentTerms,
        CAST(NULL AS DATE) AS DueDate,
        x.ClearingDate,
        CAST(NULL AS NVARCHAR(1)) AS IsAutomated,
        CAST(NULL AS DECIMAL(18,2)) AS DaysSalesOutstanding
    FROM base_acdoca x
    INNER JOIN base_bkpf h ON h.CompanyCode = x.CompanyCode AND h.AccountingDocumentNumber = x.AccountingDocumentNumber AND h.FiscalYear = x.FiscalYear
    CROSS JOIN params p
    WHERE x.CustomerNumber IS NOT NULL
),
output_events AS (
    SELECT InvoiceNumber, 'Issue Invoice Output' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved invoice output source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dunning_events AS (
    SELECT InvoiceNumber, 'Send Payment Reminder' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dunning source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_create_events AS (
    SELECT InvoiceNumber, 'Create Dispute Case' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute case source object]
    WHERE ActivityCode = '[Dispute creation activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_update_events AS (
    SELECT InvoiceNumber, 'Update Dispute Status' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute status history source object]
    WHERE ActivityCode = '[Dispute status update activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_resolve_events AS (
    SELECT InvoiceNumber, 'Resolve Dispute Case' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute case source object]
    WHERE ActivityCode = '[Dispute resolution activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
promise_events AS (
    SELECT InvoiceNumber, 'Record Promise To Pay' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved promise to pay source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
partial_payment_events AS (
    SELECT InvoiceNumber, 'Post Partial Payment' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved payment relationship source object]
    WHERE PaymentClassification = '[Partial payment classification]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
credit_memo_events AS (
    SELECT InvoiceNumber, 'Post Credit Memo' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved credit memo relationship source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
full_payment_events AS (
    SELECT InvoiceNumber, 'Receive Full Payment' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved payment relationship source object]
    WHERE PaymentClassification = '[Full payment classification]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
clear_events AS (
    SELECT InvoiceNumber, 'Clear Invoice' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved clearing relationship source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
reconcile_events AS (
    SELECT InvoiceNumber, 'Reconcile Bank Statement' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved bank statement reconciliation source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
reverse_events AS (
    SELECT
        x.InvoiceNumber,
        'Reverse Invoice' AS Activity,
        CAST(TO_TIMESTAMP(TO_VARCHAR(h.CreationDate) || TO_VARCHAR(h.CreationTime), 'YYYYMMDDHH24MISS') AS TIMESTAMP) AS EventTime,
        'SAP S/4HANA' AS SourceSystem,
        p.last_data_update AS LastDataUpdate,
        x.CompanyCode,
        x.CustomerNumber,
        x.AmountInLocalCurrency,
        COALESCE(h.UserName, x.UserName) AS UserName,
        x.DocumentType,
        x.PaymentTerms,
        CAST(NULL AS DATE) AS DueDate,
        x.ClearingDate,
        CAST(NULL AS NVARCHAR(1)) AS IsAutomated,
        CAST(NULL AS DECIMAL(18,2)) AS DaysSalesOutstanding
    FROM base_acdoca x
    INNER JOIN base_bkpf h ON h.CompanyCode = x.CompanyCode AND h.AccountingDocumentNumber = x.AccountingDocumentNumber AND h.FiscalYear = x.FiscalYear
    CROSS JOIN params p
    WHERE x.DocumentType = '[Reversal document type]'
)
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM invoice_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM output_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dunning_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_create_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_update_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_resolve_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM promise_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM partial_payment_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM credit_memo_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM full_payment_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM clear_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM reconcile_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM reverse_events
ORDER BY InvoiceNumber, EventTime, Activity;

步骤

前置条件设置：确保提取计算机已安装Python 3.x。安装SAP NetWeaver RFC SDK（PyRFC所需），并使用pip安装Python库pyrfc和pandas。
SAP用户配置：确认SAP用户账户拥有S_RFC（远程函数调用）和S_TABU_DIS（表读取）授权，并可访问函数模块RFC_READ_TABLE。
网络配置：确保运行脚本的计算机可以访问SAP应用服务器端口（通常为33xx），并且防火墙允许此类流量。
脚本配置：打开Query部分提供的Python脚本，使用您的应用服务器主机（ASHOST）、系统编号（SYSNR）、客户端（CLIENT）、用户和密码更新sap_config字典。
筛选条件定义：修改脚本中的TARGET_BUKRS（公司代码）和TARGET_GJAHR（会计年度）变量，限制数据范围。读取BSEG等大型表时，这对避免超时至关重要。
执行：通过命令行运行脚本（例如python extract_ar.py）。脚本会验证连接，并开始依次下载表数据块。
数据转换：脚本使用Pandas将原始SAP表（BKPF、BSEG、NAST、MHND、UDM表、FEBEP）合并为统一事件日志，并处理日期转换，根据清账逻辑推导“部分付款已过账”和“已收到全额付款”等活动。
CSV输出：完成后，脚本会在当前目录生成名为AR_Event_Log.csv的文件。
验证：打开CSV文件，确认EventTime和活动列已正确填充。检查特定Invoice Number是否包含与其生命周期（创建→输出→付款）对应的多行记录。
导入ProcessMind：将AR_Event_Log.csv文件上传到ProcessMind。将InvoiceNumber映射为案例ID，将活动映射为活动，将EventTime映射为时间戳。

配置

RFC_READ_TABLE限制：标准SAP函数模块存在行宽限制（通常为512字节）。脚本仅选择必要列，以确保不超过该限制。
会计年度筛选：查询财务表（BKPF、BSEG）时始终按GJAHR（会计年度）筛选，以保持性能。
公司代码：对于多实体SAP实例，按BUKRS限制范围是强制要求。
分块：对于超大数据集，脚本可能需要在read_table函数中实现ROWCOUNT和SKIP选项，以分页读取结果（此处为简洁起见省略，但生产环境建议实现）。
FSCM可用性：脚本尝试读取用于争议管理的UDM_表。如果您的S/4HANA系统未启用这些模块，脚本相关部分将返回空数据框，或应将其注释掉。
日期处理：SAP日期以YYYYMMDD字符串存储。脚本会自动将其转换为Python datetime对象。

a 示例查询 python

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
sap_config = {
    class="hl-string">'user': class="hl-string">'YOUR_USER',
    class="hl-string">'passwd': class="hl-string">'YOUR_PASSWORD',
    class="hl-string">'ashost': class="hl-string">'192.168.0.1',  class=class="hl-string">"hl-comment"># SAP App Server IP
    class="hl-string">'sysnr': class="hl-string">'00',            class=class="hl-string">"hl-comment"># Instance Number
    class="hl-string">'client': class="hl-string">'100',          class=class="hl-string">"hl-comment"># Client
    class="hl-string">'lang': class="hl-string">'EN'
}

TARGET_BUKRS = class="hl-string">'1000'  class=class="hl-string">"hl-comment"># Company Code
TARGET_GJAHR = class="hl-string">'2023'  class=class="hl-string">"hl-comment"># Fiscal Year

class=class="hl-string">"hl-comment"># === HELPER FUNCTIONS ===
def read_table(conn, table_name, fields, options=[]):
    class="hl-string">"""Reads an SAP table via RFC_READ_TABLE."""
    print(fclass="hl-string">"Reading {table_name}...")
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                           QUERY_TABLE=table_name,
                           DELIMITER=class="hl-string">';',
                           FIELDS=[{class="hl-string">'FIELDNAME': f} for f in fields],
                           OPTIONS=[{class="hl-string">'TEXT': o} for o in options])
        data_str = result[class="hl-string">'DATA']
        headers = [f[class="hl-string">'FIELDNAME'] for f in result[class="hl-string">'FIELDS']]
        
        class=class="hl-string">"hl-comment"># Parse delimiter separated values
        rows = [row[class="hl-string">'WA'].split(class="hl-string">';') for row in data_str]
        df = pd.DataFrame(rows, columns=headers)
        return df
    except Exception as e:
        print(fclass="hl-string">"Error reading {table_name}: {e}")
        return pd.DataFrame(columns=fields)

def sap_date_to_datetime(date_str, time_str=class="hl-string">'000000'):
    class="hl-string">"""Converts SAP YYYYMMDD and HHMMSS strings to datetime."""
    try:
        return pd.to_datetime(date_str + time_str, format=class="hl-string">'%Y%m%d%H%M%S')
    except:
        return pd.NaT

class=class="hl-string">"hl-comment"># === MAIN EXTRACTION ===
def main():
    conn = Connection(**sap_config)
    
    class=class="hl-string">"hl-comment"># 1. Fetch Header Data (BKPF) - Invoices and Payments
    class=class="hl-string">"hl-comment"># Document Types for Invoices: DR (Customer Invoice), RV (Billing Doc)
    fields_bkpf = [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BLART', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'WAERS', class="hl-string">'STBLG']
    options_bkpf = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND GJAHR = '{TARGET_GJAHR}'"]
    df_bkpf = read_table(conn, class="hl-string">'BKPF', fields_bkpf, options_bkpf)

    class=class="hl-string">"hl-comment"># 2. Fetch Segment Data (BSEG) - Customer Line Items (KOART = D)
    fields_bseg = [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUZEI', class="hl-string">'KOART', class="hl-string">'KUNNR', class="hl-string">'WRBTR', class="hl-string">'AUGBL', class="hl-string">'AUGDT', class="hl-string">'ZLSPR', class="hl-string">'ZTERM', class="hl-string">'SKFBT']
    class=class="hl-string">"hl-comment"># Note: Querying BSEG directly can be slow. Ensure filters are strict.
    options_bseg = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND GJAHR = '{TARGET_GJAHR}' AND KOART = 'D'"]
    df_bseg = read_table(conn, class="hl-string">'BSEG', fields_bseg, options_bseg)

    class=class="hl-string">"hl-comment"># Merge Header and Item to form base document list
    df_docs = pd.merge(df_bseg, df_bkpf, on=[class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR'])
    
    class=class="hl-string">"hl-comment"># Filter strictly for AR Invoices (Simplification: Doc Types DR, RV)
    df_invoices = df_docs[df_docs[class="hl-string">'BLART'].isin([class="hl-string">'DR', class="hl-string">'RV', class="hl-string">'DG'])].copy()

    class=class="hl-string">"hl-comment"># === ACTIVITY GENERATION ===
    events = []

    class=class="hl-string">"hl-comment"># Activity 1: Create AR Invoice
    for _, row in df_invoices.iterrows():
        timestamp = sap_date_to_datetime(row[class="hl-string">'CPUDT'], row[class="hl-string">'CPUTM'])
        events.append({
            class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
            class="hl-string">'Activity': class="hl-string">'Create AR Invoice',
            class="hl-string">'EventTime': timestamp,
            class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
            class="hl-string">'CompanyCode': row[class="hl-string">'BUKRS'],
            class="hl-string">'CustomerNumber': row[class="hl-string">'KUNNR'],
            class="hl-string">'AmountInLocalCurrency': row[class="hl-string">'WRBTR'],
            class="hl-string">'User': row[class="hl-string">'USNAM'],
            class="hl-string">'DocumentType': row[class="hl-string">'BLART']
        })

    class=class="hl-string">"hl-comment"># Activity 2: Issue Invoice Output (NAST)
    class=class="hl-string">"hl-comment"># Get unique Invoice numbers to filter NAST
    invoice_nums = df_invoices[class="hl-string">'BELNR'].unique().tolist()
    if invoice_nums:
        class=class="hl-string">"hl-comment"># NAST keys: KAPPL=class="hl-string">'V3' (Billing), OBJKY = Invoice Number
        class=class="hl-string">"hl-comment"># Note: In production, batch this query if list is long
        fields_nast = [class="hl-string">'OBJKY', class="hl-string">'KSCHL', class="hl-string">'DATVR', class="hl-string">'UHRVR', class="hl-string">'USNAM']
        options_nast = [class="hl-string">"KAPPL = 'V3'"] class=class="hl-string">"hl-comment"># Add OBJKY IN (...) logic for optimization
        df_nast = read_table(conn, class="hl-string">'NAST', fields_nast, options_nast)
        
        for _, row in df_nast.iterrows():
            if row[class="hl-string">'OBJKY'] in invoice_nums:
                events.append({
                    class="hl-string">'InvoiceNumber': row[class="hl-string">'OBJKY'],
                    class="hl-string">'Activity': class="hl-string">'Issue Invoice Output',
                    class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'DATVR'], row[class="hl-string">'UHRVR']),
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                    class="hl-string">'User': row[class="hl-string">'USNAM']
                })

    class=class="hl-string">"hl-comment"># Activity 3: Send Payment Reminder (MHND/MHNK)
    fields_mhnd = [class="hl-string">'LAUFD', class="hl-string">'LAUFI', class="hl-string">'KUNNR', class="hl-string">'BUKRS', class="hl-string">'KOART']
    class=class="hl-string">"hl-comment"># MHND links dunning run to customer, difficult to link exact invoice item in RFC without join
    class=class="hl-string">"hl-comment"># We will approximate by reading MHND (Dunning Data) where KOART=D
    class=class="hl-string">"hl-comment"># To get Invoice link, we need to know if item is in dunning. 
    class=class="hl-string">"hl-comment"># Simplified: Assuming we can fetch MHND for our customers.
    options_mhnd = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND KOART = 'D'"]
    df_mhnd = read_table(conn, class="hl-string">'MHND', fields_mhnd + [class="hl-string">'BELNR'], options_mhnd)
    
    for _, row in df_mhnd.iterrows():
        if row[class="hl-string">'BELNR'] in invoice_nums:
            events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Send Payment Reminder',
                class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'LAUFD']), class=class="hl-string">"hl-comment"># Dunning date
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'User': class="hl-string">'SYSTEM'
            })

    class=class="hl-string">"hl-comment"># Activity 4, 5, 6: Dispute Management (UDM_CASE_ATTR)
    class=class="hl-string">"hl-comment"># Requires FSCM. Linking via UDM_RELATION or knowing CASE_GUID.
    class=class="hl-string">"hl-comment"># We assume class="hl-string">'FIN_DISPUTE' case type.
    fields_udm = [class="hl-string">'CASE_GUID', class="hl-string">'CASE_ID', class="hl-string">'FIN_KUNNR', class="hl-string">'CREATE_TIME', class="hl-string">'CLOSE_TIME', class="hl-string">'ESCAL_REASON', class="hl-string">'PROCESSOR']
    df_udm = read_table(conn, class="hl-string">'UDM_CASE_ATTR', fields_udm, options=[])
    
    class=class="hl-string">"hl-comment"># UDM_RELATION links Case to Object (Invoice)
    fields_rel = [class="hl-string">'CASE_GUID', class="hl-string">'OBJ_KEY']
    df_rel = read_table(conn, class="hl-string">'UDM_RELATION', fields_rel, options=[])
    
    class=class="hl-string">"hl-comment"># Merge Dispute info
    df_disputes = pd.merge(df_udm, df_rel, on=class="hl-string">'CASE_GUID')
    
    for _, row in df_disputes.iterrows():
        inv_num = row[class="hl-string">'OBJ_KEY'] class=class="hl-string">"hl-comment"># Needs parsing usually (RefKey), assuming raw match for simplicity
        if inv_num in invoice_nums:
            class=class="hl-string">"hl-comment"># 4. Create Dispute Case
            events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'Activity': class="hl-string">'Create Dispute Case',
                class="hl-string">'EventTime': pd.to_datetime(row[class="hl-string">'CREATE_TIME']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'User': row[class="hl-string">'PROCESSOR']
            })
            class=class="hl-string">"hl-comment"># 5. Update Dispute Status (Approximation via Change Time or attribute check)
            class=class="hl-string">"hl-comment"># Omitted for brevity: requires CDHDR/CDPOS lookup
            
            class=class="hl-string">"hl-comment"># 6. Resolve Dispute Case
            if pd.notna(row[class="hl-string">'CLOSE_TIME']) and row[class="hl-string">'CLOSE_TIME'] != class="hl-string">'0.0000000E+00':
                 events.append({
                    class="hl-string">'InvoiceNumber': inv_num,
                    class="hl-string">'Activity': class="hl-string">'Resolve Dispute Case',
                    class="hl-string">'EventTime': pd.to_datetime(row[class="hl-string">'CLOSE_TIME']),
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                })

    class=class="hl-string">"hl-comment"># Activities 8-11: Payments and Clearing
    class=class="hl-string">"hl-comment"># We look at the class="hl-string">'AUGBL' (Clearing Doc) in the Invoice BSEG row.
    for _, row in df_invoices.iterrows():
        if row[class="hl-string">'AUGBL'] and row[class="hl-string">'AUGBL'] != class="hl-string">'':
            clearing_doc = row[class="hl-string">'AUGBL']
            clearing_date = row[class="hl-string">'AUGDT']
            
            class=class="hl-string">"hl-comment"># 11. Clear Invoice
            events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Clear Invoice',
                class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'ClearingDate': sap_date_to_datetime(clearing_date)
            })
            
            class=class="hl-string">"hl-comment"># Analyze Clearing Document to determine if it was Payment or Credit Memo
            class=class="hl-string">"hl-comment"># Fetch the header of the clearing document
            class=class="hl-string">"hl-comment"># In a real run, batch these fetches. Here we simulate availability.
            class=class="hl-string">"hl-comment"># We check if we already fetched it in df_bkpf (if within same year)
            clear_header = df_bkpf[(df_bkpf[class="hl-string">'BELNR'] == clearing_doc) & (df_bkpf[class="hl-string">'BUKRS'] == row[class="hl-string">'BUKRS'])]
            
            if not clear_header.empty:
                doc_type = clear_header.iloc[0][class="hl-string">'BLART']
                class=class="hl-string">"hl-comment"># 8. Post Partial Payment / 10. Receive Full Payment
                if doc_type in [class="hl-string">'DZ', class="hl-string">'ZP']: class=class="hl-string">"hl-comment"># Payment Types
                    class=class="hl-string">"hl-comment"># Logic: If residual items exist, it's Partial. 
                    class=class="hl-string">"hl-comment"># Check if clearing doc created new open items. Complex in basic script.
                    class=class="hl-string">"hl-comment"># Simplified: Assume Full Payment for standard clearing.
                    events.append({
                        class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                        class="hl-string">'Activity': class="hl-string">'Receive Full Payment',
                        class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                        class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                        class="hl-string">'DocumentType': doc_type
                    })
                class=class="hl-string">"hl-comment"># 9. Post Credit Memo
                elif doc_type == class="hl-string">'DG':
                     events.append({
                        class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                        class="hl-string">'Activity': class="hl-string">'Post Credit Memo',
                        class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                        class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                    })

    class=class="hl-string">"hl-comment"># Activity 12: Reconcile Bank Statement (FEBEP)
    class=class="hl-string">"hl-comment"># Check for Electronic Bank Statement items matching the Payment Doc
    class=class="hl-string">"hl-comment"># Requires knowing the Payment Doc number (AUGBL from invoice)
    fields_febep = [class="hl-string">'KUKEY', class="hl-string">'ESNUM', class="hl-string">'NBBLN']
    options_febep = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}'"] 
    df_febep = read_table(conn, class="hl-string">'FEBEP', fields_febep, options_febep)
    
    for _, row in df_invoices.iterrows():
        payment_doc = row[class="hl-string">'AUGBL']
        if payment_doc:
            match = df_febep[df_febep[class="hl-string">'NBBLN'] == payment_doc]
            if not match.empty:
                events.append({
                    class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                    class="hl-string">'Activity': class="hl-string">'Reconcile Bank Statement',
                    class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'AUGDT']), class=class="hl-string">"hl-comment"># Proxy time
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                })

    class=class="hl-string">"hl-comment"># Activity 13: Reverse Invoice
    class=class="hl-string">"hl-comment"># Check if STBLG (Reversal Doc) is filled in header
    for _, row in df_invoices.iterrows():
        if row[class="hl-string">'STBLG'] and row[class="hl-string">'STBLG'] != class="hl-string">'':
             events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Reverse Invoice',
                class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'CPUDT']), class=class="hl-string">"hl-comment"># Using updated time
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
            })

    class=class="hl-string">"hl-comment"># 7. Record Promise To Pay (UDM_P2P_ATTR)
    class=class="hl-string">"hl-comment"># Similar to Dispute, requires P2P table fetch
    class=class="hl-string">"hl-comment"># Implementation logic similar to UDM_CASE_ATTR

    class=class="hl-string">"hl-comment"># === EXPORT ===
    df_events = pd.DataFrame(events)
    df_events[class="hl-string">'LastDataUpdate'] = datetime.now()
    
    print(fclass="hl-string">"Extracted {len(df_events)} events.")
    df_events.to_csv(class="hl-string">'AR_Event_Log.csv', index=False)

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

步骤

验证前置条件：确保已安装Theobald Xtract Universal Designer，并已配置与SAP S/4HANA源系统的有效连接。确认连接使用的SAP用户账户对BKPF、BSEG、BSAD、NAST、MHND、SCMG_T_CASE_ATTR、UDM_P2P_ATTR和FEBEP表拥有读取授权（S_TABU_DIS或S_TABU_NAM）。
创建新提取：打开Xtract Universal Designer。点击新建创建新的提取，命名为AR_Process_Mining_EventLog，然后选择SAP源系统。
选择组件类型：选择自定义SQL组件类型。这样可以执行UNION ALL查询，在一次提取中从不同SAP表获取各种活动。
定义查询：复制本文档Query部分提供的代码，并粘贴到Xtract Universal Designer中的SQL编辑器窗口。
配置参数：在提取的“Parameters”选项卡中定义名为YEAR_START的参数（例如“20230101”），用于筛选数据并优化性能。如果希望脚本动态运行，请更新SQL脚本中的WHERE子句以引用此参数；也可以手动替换脚本中的占位日期。
设置输出目标：配置所需目标（例如Local CSV、Azure Blob、AWS S3或SQL Server）。将SQL中定义的输出列（InvoiceNumber、活动等）映射到目标字段。如果目标要求特定类型，请确保日期字段采用YYYY-MM-DD格式，时间戳采用YYYY-MM-DD HH:MM:SS格式。
处理大量数据：首次加载建议分块运行提取（例如按会计年度）。如需每日增量，请修改WHERE子句，根据系统日期筛选CPUDT（录入日期）或AEDAT（变更日期），例如BKPF.CPUDT >= sy-datum - 1。
预览与验证：点击Xtract Universal中的运行或预览按钮，获取前100行。确认活动列包含“创建应收账款发票”和“清算发票”等值，并检查InvoiceNumber是否持续正确填充。
运行提取：执行完整提取，并监控Xtract Universal日志，留意超时或内存错误。
后处理：将生成的文件导入ProcessMind。在导入映射中，将InvoiceNumber设为案例ID，将活动设为活动名称，将EventTime设为时间戳。

配置

提取类型：Custom SQL（Legacy）或Table Join。建议使用Custom SQL，直接在源系统中构造事件日志。
数据包大小：将数据包大小设置为20,000至50,000行。SAP S/4HANA可以处理更大的批量，但网络稳定性通常是瓶颈。
超时：提取大量历史数据时，将标准超时时间提高到600秒或更长。
Where子句筛选：
- 按BUKRS（公司代码）筛选，将数据限制在相关实体内。
- 按GJAHR（会计年度）筛选，限制历史范围。
列类型转换：在目标设置中确保AmountInLocalCurrency按Decimal/Double处理，以保留小数金额。
增量逻辑：如需增量加载，在Custom SQL逻辑中使用SAP服务器变量@sy-datum（如系统支持），或由调用工作流工具传入动态参数。

a 示例查询 sql

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Create AR Invoice' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    BSEG.ZTERM AS PaymentTerms,
    BSEG.ZFBDT AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    0 AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART IN ('DR', 'RV', 'RE') 
  AND BSEG.KOART = 'D' 
  AND BKPF.GJAHR >= '2023' -- [Configure based on your system]

UNION ALL

SELECT 
    LEFT(NAST.OBJKY, 10) AS InvoiceNumber,
    'Issue Invoice Output' AS Activity,
    CONCAT(NAST.DATVR, NAST.UHRVR) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NAST.KAPPL AS CompanyCode,
    NAST.PARNR AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    NAST.USNAM AS "User",
    NAST.KSCHL AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM NAST
WHERE NAST.KAPPL = 'V3' 
  AND NAST.OBJKY IS NOT NULL
  AND NAST.DATVR >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    MHND.BELNR AS InvoiceNumber,
    'Send Payment Reminder' AS Activity,
    CONCAT(MHND.LAUFD, '120000') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    MHND.BUKRS AS CompanyCode,
    MHND.KUNNR AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    NULL AS "User",
    MHND.MANSP AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM MHND
WHERE MHND.KOART = 'D'
  AND MHND.LAUFD >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Create Dispute Case' AS Activity,
    CONCAT(SUBSTRING(SCMG.CREATE_TIME, 1, 8), SUBSTRING(SCMG.CREATE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CREATE_USER AS "User",
    SCMG.CASE_TYPE AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01'

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Update Dispute Status' AS Activity,
    CONCAT(SUBSTRING(SCMG.CHANGE_TIME, 1, 8), SUBSTRING(SCMG.CHANGE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CHANGE_USER AS "User",
    SCMG.STAT_ORDERNO AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01' AND SCMG.CHANGE_TIME > SCMG.CREATE_TIME

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Resolve Dispute Case' AS Activity,
    CONCAT(SUBSTRING(SCMG.CHANGE_TIME, 1, 8), SUBSTRING(SCMG.CHANGE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CHANGE_USER AS "User",
    SCMG.STAT_ORDERNO AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01' AND SCMG.STAT_ORDERNO >= '30' -- [Adjust status code for Closed]

UNION ALL

SELECT 
    P2P.INVOICE_REF AS InvoiceNumber,
    'Record Promise To Pay' AS Activity,
    CONCAT(SUBSTRING(P2P.PROMISE_TIME, 1, 8), SUBSTRING(P2P.PROMISE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    P2P.CUSTOMER AS CustomerNumber,
    P2P.AMOUNT_PROMISED AS AmountInLocalCurrency,
    P2P.USER_CREATED AS "User",
    P2P.STATE AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM UDM_P2P_ATTR AS P2P

UNION ALL

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Post Partial Payment' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART = 'DZ' AND BSEG.XREF3 IS NOT NULL -- [Assumption: XREF3 implies partial link, or customize logic]

UNION ALL

SELECT 
    BSEG.REBZG AS InvoiceNumber, -- Reference to original invoice
    'Post Credit Memo' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART = 'DG' AND BSEG.REBZG IS NOT NULL

UNION ALL

SELECT 
    BSAD.BELNR AS InvoiceNumber,
    'Receive Full Payment' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSAD.KUNNR AS CustomerNumber,
    BSAD.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    BSAD.AUGDT AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSAD ON BKPF.BUKRS = BSAD.BUKRS AND BKPF.BELNR = BSAD.AUGBL AND BKPF.GJAHR = BSAD.AUGGJ
WHERE BKPF.BLART = 'DZ' -- Customer Payment

UNION ALL

SELECT 
    BSAD.BELNR AS InvoiceNumber,
    'Clear Invoice' AS Activity,
    CONCAT(BSAD.AUGDT, '235959') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSAD.BUKRS AS CompanyCode,
    BSAD.KUNNR AS CustomerNumber,
    BSAD.DMBTR AS AmountInLocalCurrency,
    NULL AS "User",
    BSAD.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    BSAD.AUGDT AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSAD
WHERE BSAD.AUGDT >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    BSEG.BELNR AS InvoiceNumber,
    'Reconcile Bank Statement' AS Activity,
    CONCAT(FEB.BUDAT, '120000') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    FEB.BUKRS AS CompanyCode,
    NULL AS CustomerNumber,
    FEB.KWERT AS AmountInLocalCurrency,
    NULL AS "User",
    'EBS' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM FEBEP AS FEB
INNER JOIN BSEG ON FEB.BELNR = BSEG.BELNR AND FEB.BUKRS = BSEG.BUKRS
WHERE FEB.BUDAT >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Reverse Invoice' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
WHERE BKPF.STBLG IS NOT NULL AND BKPF.STBLG <> ''

准备好开始了吗？

将财务数据转化为可执行的洞察，立即开始缩短应收账款周转天数。我们的团队将全程为您的流程挖掘之旅提供支持。

返回改进指南下载模板

优化SAP S/4HANA应收账款，提升现金流

加入使用我们工具将DSO缩短15至20天的企业行列

开始免费试用

无需信用卡，5分钟完成设置。

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

提取指南

SAP S/4HANA标准CDS视图数据提取

步骤

配置

a 示例查询 sql

直接对ACDOCA表执行SAP HANA SQL查询

步骤

配置

a 示例查询 sql

使用PyRFC连接器的Python提取脚本

步骤

配置

a 示例查询 python

Theobald Xtract Universal SAP连接器使用指南

步骤

配置

a 示例查询 sql

准备好开始了吗？

优化SAP S/4HANA应收账款，提升现金流

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

提取指南

SAP S/4HANA标准CDS视图数据提取

步骤

配置

a 示例查询 sql

直接对ACDOCA表执行SAP HANA SQL查询

步骤

配置

a 示例查询 sql

使用PyRFC连接器的Python提取脚本

步骤

配置

a 示例查询 python

Theobald Xtract Universal SAP连接器使用指南

步骤

配置

a 示例查询 sql

准备好开始了吗？

优化SAP S/4HANA应收账款，提升现金流

我们重视您的隐私