您的应收账款数据模板

SAP S/4HANA

← 返回改进指南

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

您的应收账款数据模板

此模板为您梳理SAP S/4HANA中的催收和付款流程提供了完整框架，其中包括建议收集的关键属性、需要跟踪的流程活动，以及从财务表中提取数据的技术说明。按照此框架操作，您可以清晰了解现金流周期，并找出需要人工介入或发生付款延迟的环节。

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

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

下载模板

应收账款属性

为全面分析应收账款流程，建议在事件日志中纳入这些数据字段和维度属性。

5 必填 10 建议填写 5 选填

	名称	说明
	事件时间 EventTime	活动发生时的时间戳。
说明此属性记录活动发生的准确日期和时间。对于事务数据，通常由抬头表中的录入日期（CPUDT）和录入时间（CPUTM）组合而成，可用于计算周期时间和处理时长。重要性准确的时间戳对于计算应收账款周转天数和识别瓶颈至关重要。获取途径 SAP表BKPF-CPUDT和BKPF-CPUTM 示例 2023-10-12T08:30:00Z2023-10-15T14:45:12Z2023-11-01T09:15:00Z
	发票编号 InvoiceNumber	发票或会计凭证的唯一标识符。
说明此属性是应收账款流程的唯一案例ID。根据数据提取逻辑，它通常对应SAP FI模块中的会计凭证编号（BELNR）或SD模块中的开票凭证（VBELN）。它将付款、争议和清账等后续活动关联到原始财务义务。重要性这是重建每笔交易端到端流程顺序所需的核心键值。获取途径 SAP表BKPF-BELNR或VBRK-VBELN 示例 1400000234900004321014000002351800000099
	活动 Activity	流程中执行的具体事件或操作。
说明此属性描述应收账款流程中的步骤，例如创建发票、部分付款过账或清账。通常根据事务代码（TCODE）或变更日志表中的凭证状态变更来确定。重要性它对于梳理流程顺序和识别事件先后关系至关重要。获取途径取自SAP TSTCT（事务代码）或CDHDR/CDPOS（变更凭证）示例创建应收账款发票过账部分付款清账发票创建争议案例
	数据最后更新时间 LastDataUpdate	数据最近一次提取或刷新的时间戳。
说明此属性表示数据载入流程挖掘工具的时间，帮助用户了解仪表板（如期末对账状态）中数据的时效性。重要性帮助分析人员判断查看的是实时数据还是历史数据。获取途径 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）定义客户的付款条件，包括到期日和现金折扣，是提前付款折扣优化仪表板的主要数据来源。重要性用于计算到期日和分析折扣适用条件的基准。获取途径 SAP表BSEG-ZTERM或KNB1-ZTERM 示例 NT300001Z015
	公司代码 CompanyCode	发票所属的财务实体或业务单元。
说明公司代码（BUKRS）是SAP系统中外部会计的核心组织单元。它可用于按不同业务单元划分分析，例如在客户付款行为分析仪表板中进行细分。重要性用于筛选和比较不同法人实体的绩效。获取途径 SAP表BKPF-BUKRS或T001-BUKRS 示例 1000US01DE012000
	凭证类型 DocumentType	用于对会计凭证分类，例如发票、付款或贷项凭证。
说明凭证类型（BLART）用于对交易分类。例如，DR表示客户发票，DZ表示客户付款，DG表示贷项凭证。分析贷项凭证与更正时，可据此筛选特定案例类型。重要性将数据划分为发票、付款和调整项。获取途径 SAP表BKPF-BLART 示例 DRDZDGRV
	到期日 DueDate	发票应付清的计算日期。
说明此属性表示净到期日。在SAP中，通常以基准日期（ZFBDT）加上付款条件（ZTERM）中规定的天数计算得出，是催款合规与执行仪表板的基准。重要性用于判断付款是否逾期的参考日期。获取途径根据BSEG-ZFBDT和BSEG-ZTERM计算得出示例 2023-11-302023-12-15
	客户编号 CustomerNumber	客户账户的唯一标识符。
说明此属性表示与发票关联的客户编号（KUNNR），可用于详细分析付款行为，也是客户付款行为分析仪表板的重要数据。重要性可下钻查看特定客户账户的绩效。获取途径 SAP表BSEG-KUNNR或KNA1-KUNNR 示例 CUST100230001004500WALMART_US
	应收账款周转天数 DaysSalesOutstanding	从发票过账到清账所需的时间。
说明对于已清账项目，此指标通过清账日期减去过账日期计算得出。它是应收账款周转天数概览仪表板的核心指标，也是衡量现金流效率的重要指标。重要性应收账款流程的核心管理层KPI。获取途径计算方式：ClearingDate - PostingDate 示例 30天45天15天
	是否自动执行 IsAutomated	用于标记活动是否由系统用户执行。
说明此布尔属性根据用户属性计算得出。如果用户ID与已知系统账户或批处理账户（如“BNK_BSM”）匹配，则设为true。该属性直接用于付款匹配自动化率仪表板。重要性衡量数字化转型和自动化成效的关键指标。获取途径根据用户属性计算得出示例 truefalse
	本币金额 AmountInLocalCurrency	发票或付款按公司代码货币计量的金额。
说明此属性（DMBTR）记录交易金额，可用于计算未清发票、贷项凭证和付款的总额，并为贷项凭证与更正分析仪表板提供金额依据。重要性用于分析财务影响，并优先处理金额较大的催收事项。获取途径 SAP表BSEG-DMBTR或ACDOCA-DMBTR 示例 1500.00250.5010000.00
	清账日期 ClearingDate	发票完成清账或结算的日期。
说明此属性（AUGDT）记录未清项目完成清账的时间，通常由付款完成。它是应收账款核心流程周期的结束时间，也是计算应收账款周转天数的主要变量。重要性标记催收周期的完成。获取途径 SAP表BSEG-AUGDT或BSAD-AUGDT 示例 2023-11-012023-11-10
	用户名 User	执行活动的用户ID。
说明此属性（USNAM）用于标识负责该事件的用户或系统代理。通过区分人工用户和系统批处理用户，可据此计算自动付款匹配率。重要性对于衡量自动化率和用户生产力至关重要。获取途径 SAP表BKPF-USNAM或CDHDR-USERNAME 示例 JSMITHBATCH_USERSAP_WFALEX_D
	争议案例ID DisputeCaseId	与发票关联的争议案例标识符。
说明如果存在争议案例，此属性将记录其ID（UDM_CASE），并将财务凭证关联到争议管理模块。争议解决周期效率仪表板需要此属性。重要性将应收账款凭证与争议解决工作流关联起来。获取途径 SAP表FDM_DCPROC或UDM_CASE_ATTR 示例 0000056789DISP-2023-001
	催款级别 DunningLevel	发票当前的催款级别。
说明此属性（MAHNS）表示客户已收到的付款提醒次数，可跟踪催收措施的升级情况，并在催款合规与执行仪表板中展示。重要性监控逾期付款的严重程度以及催款政策的执行情况。获取途径 SAP表MHNK-MAHNS或KNB5（客户主数据中的催款信息）示例 1230
	国家/地区 Country	客户所在的国家/地区。
说明此属性（LAND1）取自客户主数据，可按地理区域划分KPI，例如按地区或国家/地区分析贷项凭证率。重要性用于区域合规和绩效分析。获取途径 SAP表KNA1-LAND1 示例 USDEFRJP
	地区 Region	客户所在的州、省或地区。
说明此属性（REGIO）比国家/地区提供更细致的地理信息，可用于贷项凭证与更正分析，识别区域处理问题。重要性用于开展更细致的地理分析。获取途径 SAP表KNA1-REGIO 示例 CANYTXBY
	是否为贷项凭证 IsCreditMemo	用于标记该凭证是否为贷项凭证。
说明此布尔属性根据凭证类型推导得出，例如类型为“DG”时设为true。它可快速筛选更正交易，为更正性贷项凭证率KPI提供支持。重要性用于识别返工和开票错误。获取途径根据凭证类型推导得出示例 truefalse

必填建议填写选填

应收账款活动

为准确发现流程并识别瓶颈，建议在事件日志中记录这些关键流程步骤和交易节点。

6 建议填写 7 选填

	活动	说明
	创建应收账款发票	在财务系统中创建客户发票凭证。此活动根据凭证抬头的创建时间戳记录。
重要性标志着应收账款流程和账龄计时的开始，是计算应收账款周转天数（DSO）的必要数据。获取途径 BKPF表（会计凭证抬头），字段CPUDT（录入日期）和CPUTM（录入时间）。按凭证类型（BLART）筛选客户发票相关类型，例如DR、RV。采集交易提交至BKPF时记录事件类型 explicit
	发送付款提醒	为逾期发票生成催款通知或提醒函，用于跟踪组织采取的催收措施。
重要性这是催款合规率KPI的关键数据，可用于核实催收策略是否按政策执行。获取途径 MHND表（催款数据），字段LAUFD（催款运行日期）。通过公司代码和客户进行关联。采集执行催款运行（F150）时记录事件类型 explicit
	发送发票输出	生成输出消息（电子邮件、打印件或EDI）并向客户发送发票。此活动用于确认客户已正式收到付款请求。
重要性从创建到发送的延迟会影响客户实际可用的付款期限。监控此环节可确保发票不会滞留在内部处理中。获取途径 NAST表（消息状态），使用与发票凭证编号匹配的对象键（OBJKY）。时间戳取自DATVR和UHRVR。采集生成输出时记录在NAST表中事件类型 explicit
	收到全额付款	收到足以全额支付未结发票的款项，通常意味着客户的付款义务已履行。
重要性用于确定实际付款日期与到期日的差异，是分析付款条款偏差和客户行为的关键数据。获取途径 BSEG表，根据清账凭证（AUGBL）推导；清账日期（AUGDT）已填写且余额为零。采集清账凭证过账时记录事件类型 explicit
	清账发票	会计处理中的技术步骤，将未清项目与付款或贷项匹配，并将状态更新为已清账。
重要性这是系统中该流程实例的最终结束点，也是计算最终应收账款周转天数所需的数据。获取途径 BSEG或ACDOCA表。AUGDT（清账日期）字段已填写。使用USNAM判断清账是自动完成还是人工完成。采集 AUGDT字段填写时记录事件类型 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字段，具体取决于配置。采集案例状态设为已关闭或作废时记录事件类型 explicit
	记录付款承诺	催收专员记录客户的正式承诺，注明客户将在指定日期前支付指定金额。
重要性反映催收专员的有效工作，可用于预测短期现金流并衡量催收专员处理量。获取途径 UDM_P2P表（付款承诺），字段CREATED_AT。通过案例GUID或凭证键关联发票。采集记录在FSCM催收管理中事件类型 explicit
	过账贷项凭证	开具与发票关联的贷项凭证，以减少应付金额，通常用于纠正错误或处理退货。
重要性这是更正性贷项凭证率KPI的关键数据。发生频率较高，可能表明上游销售或开票环节存在质量问题。获取途径 BSEG表，凭证类型为DG或KG。通过指向原发票的“发票参考”字段（REBZG）识别。采集贷项凭证引用发票时记录事件类型 explicit

建议填写选填

数据提取指南

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

步骤

确定数据源：此提取方式使用SAP S/4HANA标准核心数据服务（CDS）视图。主要视图为I_JournalEntryItem，其中包含所有财务交易的明细行项目，包括发票、付款和清账事件。
准备SQL环境：确保您可以访问SAP HANA数据库中的SQL。您可以通过SAP HANA Studio、DBACOCKPIT事务，或配置了连接到S/4HANA实例的ODBC/JDBC连接的外部ETL工具来实现。
确定范围和筛选条件：确定需要提取的公司代码和会计年度。以下查询包含相应占位符，以优化性能并确保数据相关性。
提取财务活动：查询的第一部分针对I_JournalEntryItem视图，将Account Type筛选为“D”（客户），以提取应收账款数据。根据Financial Account Type和Document Type区分发票、贷项通知单和付款。
提取清账和结算信息：查询通过自连接或单独查询I_JournalEntryItem，识别发票的清账时间，并根据清账时间生成“清账发票”和“收到全额付款”活动。
整合运营事件：若要记录“发送付款提醒”或“创建争议案例”等非财务活动，查询会联接I_DunningHistory、I_DisputeCase和I_OutputRequestItem等专用视图。请注意，争议管理和输出管理是否可用取决于您的S/4HANA配置。
处理银行对账：“银行对账单核对”活动取自I_BankStatementLineItem，用于关联外部银行对账单与内部会计凭证。
统一输出列格式：查询会将所有时间戳转换为标准格式，并确保InvoiceNumber、Activity、EventTime等必需列均有值。货币金额会转换为统一的浮点数或小数格式。
执行并验证：先对少量数据运行查询，例如一个公司代码、一个月的数据，以验证逻辑。检查行数是否符合预期的交易量。
导出数据：将结果集保存为CSV或Parquet文件。编码请设为UTF-8，以正确处理用户名或文本说明中的特殊字符。
导入ProcessMind：将提取的文件上传到ProcessMind。配置导入时，将“InvoiceNumber”列映射到案例ID字段，将“EventTime”映射到时间戳字段。

配置

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

查询示例 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项活动显示为明确的事件行。

配置

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

查询示例 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），且防火墙允许此类流量。
配置脚本：打开“查询”部分提供的Python脚本。根据您的环境更新sap_config字典中的应用服务器主机（ASHOST）、系统编号（SYSNR）、客户端（CLIENT）、用户名和密码。
定义筛选条件：修改脚本中的TARGET_BUKRS（公司代码）和TARGET_GJAHR（会计年度）变量，以限定数据范围。读取BSEG等大型表时，这一步对于避免超时至关重要。
运行脚本：通过命令行运行脚本（例如，python extract_ar.py）。脚本会验证连接，并开始依次下载各个数据块。
转换数据：脚本使用Pandas将SAP原始表（BKPF、BSEG、NAST、MHND、UDM表、FEBEP）合并为统一的事件日志，并处理日期转换及相关逻辑，根据清账情况区分“部分付款过账”和“收到全额付款”等活动。
输出CSV：完成后，脚本会在当前目录生成名为AR_Event_Log.csv的文件。
验证数据：打开CSV文件，检查EventTime和活动列是否已正确填充。确认特定发票号对应多行记录，反映其生命周期（创建→输出→付款）。
导入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对象。

查询示例 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表中提取各类活动。
定义查询：复制本文档“查询”部分提供的代码，并粘贴到Xtract Universal Designer中的SQL编辑器。
配置参数：在提取任务的“参数”选项卡中，定义名为YEAR_START的参数（例如，'20230101'），用于筛选数据并优化性能。如需让脚本动态运行，请更新SQL脚本中的WHERE子句以引用此参数；也可以手动替换脚本中的日期占位符。
设置输出目标：配置所需的目标位置（例如，本地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设为时间戳。

配置

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

查询示例 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应收账款流程，改善现金流

借助我们的工具，加入将应收账款周转天数缩短15至20天的企业行列

立即开始免费试用

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

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

数据提取指南

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

步骤

配置

查询示例 sql

直接查询ACDOCA表的SAP HANA SQL

步骤

配置

查询示例 sql

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

步骤

配置

查询示例 python

Theobald Xtract Universal SAP连接器使用指南

步骤

配置

查询示例 sql

准备好开始了吗？

优化SAP S/4HANA应收账款流程，改善现金流