> 持续流程改进 > 应收账款 > Microsoft Dynamics 365 > 数据模板

您的应收账款数据模板

Microsoft Dynamics 365

← 返回改进指南

应收账款属性(21) 应收账款活动(15) 数据提取指南(2)

您的应收账款数据模板

此模板为您梳理Microsoft Dynamics 365中的开票到收款流程提供完整框架，列出构建高质量流程挖掘事件日志所需的关键数据字段和里程碑。按此结构整理数据，您就能清楚了解付款延迟，并简化催收工作流。

账务分析所需的关键数据字段
用于跟踪的标准流程里程碑
Microsoft Dynamics 365数据提取技术指南

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

下载模板

应收账款属性

建议在事件日志中纳入以下数据字段，以全面分析应收账款表现。

5 必填 8 建议填写 8 选填

	名称	说明
	事件时间戳 EventTimestamp	活动发生的准确日期和时间。
说明此属性记录事件在系统中登记的具体时间点，用于计算活动间隔时长、确定流程周期时间并排列事件顺序。在D365中，通常使用CreatedDateTime或TransDate字段，并结合时间部分。重要性时间戳是计算所有基于时间的关键绩效指标的基础，例如发票周期时间和发送周期时间。获取途径各数据表中常见的CreatedDateTime或TransDate字段示例 2023-10-15T08:30:00Z2023-10-15T14:45:22Z2023-11-01T09:00:00Z
	发票编号 InvoiceNumber	财务发票单据的唯一标识符。
说明发票编号是Microsoft Dynamics 365中跟踪应收款生命周期的主键。它将初始销售订单、已过账发票、后续付款和催收活动关联到同一个案例。在分析中，此属性用作案例标识符，以重建端到端流程。重要性这是应收账款分析的基本单位，可让流程挖掘引擎将分散的事件归入同一个案例。获取途径 CustInvoiceJour.InvoiceId或CustTrans.Invoice 示例 INV-2023-001CIV-88921US-004321DE-99120
	活动 Activity	在发票上执行的具体事件或任务。
说明此属性描述流程中发生的步骤，例如发票已过账、已收到付款或已创建争议。在Microsoft Dynamics 365中，这些步骤通常根据交易类型（TransType）或记录所在的具体数据表（例如用于付款的CustSettlement）推导得出。此属性是呈现流程图的关键。重要性此属性定义流程图中的节点，支持分析流程走向和发现流程变体。获取途径根据TransType枚举值或数据表上下文（CustTrans、CustSettlement等）推导得出示例发票已过账已收到付款已发送催收函利息通知单已过账
	最近数据更新时间 LastDataUpdate	数据提取或刷新的时间戳。
说明此属性表示最近一次从Microsoft Dynamics 365提取数据的时间，有助于了解分析数据的新鲜度以及KPI的有效性。通常由提取工具在查询时生成。重要性此属性可说明数据延迟情况，帮助您判断仪表板中的数据是否为最新。获取途径由ETL/提取脚本生成示例 2023-11-05T00:00:00Z2023-11-05T12:00:00Z
	源系统 SourceSystem	数据来源系统的名称。
说明此属性用于标识数据的记录系统，在此处为Microsoft Dynamics 365。将多个ERP实例或外部采集工具的数据汇总到一个视图时，此属性十分有用。分析人员可据此按数据来源环境筛选或细分流程。重要性在多系统环境中，这有助于确保数据沿袭清晰且可追溯。获取途径在提取时硬编码，或根据连接字符串生成示例 D365 F&O生产环境D365财务Dynamics AX 2012
	业务单元 BusinessUnit	负责该发票的运营单元或部门。
说明此属性按内部组织架构细分数据，例如电子产品部或服务部。在D365中，它通常是与交易关联的财务维度。借助此属性，发票开具周期仪表板可比较不同业务单元。重要性便于在内部进行对标，找出效率最高的部门。获取途径 CustInvoiceJour上的财务维度（DefaultDimension）示例 BU-001北区销售全球服务
	公司代码 CompanyCode	D365中的法人实体标识符。
说明此属性表示D365中的DataAreaId，对应发生交易的法人实体或公司。在多公司部署中，筛选数据离不开此属性；它还能确保不同公司中可能重复的发票编号被分别识别。重要性对于确保多实体环境中的数据唯一性和开展区域分析至关重要。获取途径所有表中的DataAreaId字段示例 USMFDEMFGBSI
	到期日 DueDate	预计收到付款的日期。
说明到期日是付款条款规定的付款截止日期。将其与实际付款日期对比，可判断付款是否准时并计算应收账款周转天数（DSO）。这是客户交易记录中的标准字段。重要性这是计算准时付款率和分析逾期发票的基础。获取途径 CustTrans.DueDate 示例 2023-11-302023-12-15
	发票金额 InvoiceAmount	发票的总金额。
说明此属性表示发票单据的金额，可用于计算应收账款总额，并优先处理高金额催收事项。此字段通常位于发票日记账表中。重要性对于分析财务影响和识别影响较大的瓶颈至关重要。获取途径 CustInvoiceJour.InvoiceAmount 示例 1500.00250.5010000.00
	客户组 CustomerGroup	客户所属的分类或细分群体。
说明此属性将客户划分为批发、零售或公司间等类别，可用于比较不同客户群体，例如判断某些群体的付款周期是否更长、争议率是否更高。在D365中，对应CustGroup字段。重要性便于比较不同市场细分群体的流程绩效。获取途径 CustTable.CustGroup 示例批发零售公司间出口
	客户账户 CustomerAccount	客户的唯一标识符或账户编号。
说明客户账户用于标识开票对象。在D365中，对应AccountNum字段。按此属性分组，可分析不同客户的付款行为、争议频率和结清时间。重要性对于识别高风险客户并按客户细分绩效至关重要。获取途径 CustInvoiceJour.InvoiceAccount或CustTrans.AccountNum 示例 US-001DE-550CUST-9921
	是否自动执行 IsAutomated	标记该活动是否由系统账户执行。
说明此属性用于判断特定步骤由用户执行还是由自动批处理作业执行。系统会将用户属性与已知系统账户列表（例如“工作流”“批处理”）进行比对后计算得出。此属性可用于手动结清率KPI。重要性对于识别自动化机会和衡量当前自动化水平至关重要。获取途径根据User字段逻辑生成示例 truefalse
	用户 User	执行该活动的人员或系统账户的用户ID。
说明此属性根据CreatedBy或ModifiedBy字段，标识执行特定流程步骤的人员。可用于分析资源利用情况、识别培训需求，并区分系统自动执行的任务与用户手动操作。重要性可用于分析资源绩效和计算自动化率。获取途径交易表中的CreatedBy字段示例管理员JSmith工作流代理BSmith
	争议原因 DisputeReason	案件处于争议状态时分配的原因代码。
说明此属性记录发票争议的类别原因，例如“价格错误”或“货物损坏”。它对于争议解决生命周期仪表板至关重要，可帮助识别付款延迟的根本原因。重要性可用于分析争议解决时间KPI的根本原因。获取途径 CustTrans.ReasonRefRecId或相关催收案件表示例价格差异货物损坏缺少采购订单服务质量
	付款条款 PaymentTerms	规定约定付款安排的代码。
说明此属性包含规定付款到期时间的代码（例如Net30、Net60），可用于分析客户是否遵守相应条款，并验证到期日的计算是否正确。重要性此属性为到期日提供背景信息，有助于判断付款条款是否过于宽松或严格。获取途径 CustInvoiceJour.Payment或CustTable.PaymTermId 示例 Net30Net45CODEOM
	币种 Currency	发票交易使用的币种代码。
说明此属性指定发票开具时使用的币种（例如USD、EUR）。如需跨区域开展全球分析，就需要此属性来统一财务金额的计量口径。重要性对于了解多币种系统中的金额分布必不可少。获取途径 CustInvoiceJour.CurrencyCode 示例 USDEURGBPJPY
	是否为贷项通知单 IsCreditMemo	标记该单据是否为贷项通知单。
说明此布尔属性用于标识该交易是否为向客户开具的贷项通知单，可用于计算贷项通知单开具率，并在返工分析中筛除普通发票。重要性此属性直接支持贷项通知单与返工频率仪表板。获取途径根据CustInvoiceJour.InvoiceAmount < 0或TransType生成示例 truefalse
	是否已结清 IsCleared	标记发票是否已全部结清。
说明此布尔属性表示发票是否已通过付款或核销达到余额为零的状态。借助此属性，可快速筛选未结与已结案件，并计算平均发票周期时间。重要性用于区分活跃案件与历史案件。获取途径根据CustTrans.Closed日期是否存在生成示例 truefalse
	现金折扣日期 CashDiscountDate	提前付款折扣的有效截止日期。
说明此属性表示客户可享受提前付款折扣的截止日期。将其与付款日期对比，可计算折扣错失率KPI并分析折扣激励措施的效果。重要性提前付款折扣优化仪表板的关键属性。获取途径 CustTrans.CashDisc或根据CashDiscCode计算示例 2023-11-102023-11-15
	逾期天数 DaysOverdue	付款日期晚于到期日的天数。
说明此指标计算付款日期与到期日之间的差值。正值表示逾期付款，负值表示提前付款。与简单的布尔标记相比，它能更细致地呈现付款绩效。重要性为准时付款率KPI分析提供更深入的信息。获取途径计算方式：PaymentDate - DueDate 示例 50-230
	销售订单ID SalesOrderId	与发票关联的销售订单参考编号。
说明此属性将发票关联到原始销售订单。如果有订单到收款流程的数据，可据此开展跨流程分析，并调查开票问题的上游原因。重要性将开票流程与销售流程关联起来。获取途径 CustInvoiceJour.SalesId 示例 SO-10022SO-55912

必填建议填写选填

应收账款活动

建议在事件日志中记录以下关键流程步骤和里程碑，以准确发现付款结算流程。

8 建议填写 7 选填

	活动	说明
	发票已发送	通过电子邮件、打印或EDI将发票发送给客户。通常可根据“Printed”计数增加或打印管理日志推断此事件。
重要性过账到发送之间的延误会直接缩短客户实际可用的付款期限，导致客户认为付款时间过晚。获取途径可根据CustInvoiceJour.PrintedOriginals从0变为1推断；如果启用了日志记录，也可通过PrintJobHeader表跟踪。采集比较状态字段变更前后的值，或查看PrintJobHeader日志事件类型 inferred
	发票已核销	最终状态变更：发票余额归零，且系统将交易标记为“已关闭”。
重要性流程实例的最终结束节点，用于计算总周期时间。获取途径 CustTrans.Closed字段（日期）。结算总额等于发票金额时，此字段会填入日期。采集根据CustTrans.Closed日期字段推断事件类型 inferred
	发票已过账	发票首次录入财务总账。此活动记录财务应收款在CustInvoiceJour表中的创建情况。
重要性标志着应收账款生命周期正式开始，同时启动付款期限计时。计算应收账款周转天数（DSO）的重要依据。获取途径 CustInvoiceJour表。时间戳取自CreatedDateTime或InvoiceDate字段。采集交易过账至CustInvoiceJour时记录事件类型 explicit
	已创建争议案例	在案例管理模块中创建与发票关联的案例，表示客户提出异议。此事件会阻断标准催收流程。
重要性争议是导致延迟付款的主要原因之一。跟踪争议有助于发现定价或订单履行等上游流程中的质量问题。获取途径 CaseDetailBase表中Category与Collections/AR相关，RefRecId关联发票信息。采集在案例管理中执行交易X时记录事件类型 explicit
	已发送付款提醒	记录向客户发出正式催收函的情况。数据取自催收函日记账。
重要性标志着主动催收开始。分析发送频率有助于优化催收策略并降低行政成本。获取途径 CustCollectionLetterJour表。记录针对特定发票交易过账催收函代码的日期。采集交易过账至CustCollectionLetterJour时记录事件类型 explicit
	已开具贷项通知单	过账贷项通知单以抵减原发票金额。通常由争议解决或退货引起。
重要性频繁开具通常意味着收入流失和上游质量问题。与付款不同，这属于非现金结算。获取途径 CustTrans表中Type为“Credit Note”，或CustInvoiceJour表中的InvoiceAmount为负数。采集交易过账至CustTrans时记录事件类型 explicit
	已收到全额付款	过账付款交易以结清发票剩余余额。这是现金流入事件。
重要性最关键的成功事件。用于计算按时付款率和折扣条款的有效性。获取途径 CustTrans表中Type为“Payment”的记录，通过CustSettlement关联发票并将余额结清为零。采集交易过账至CustTrans并完成结算时记录事件类型 explicit
	已过账部分付款	记录未能全额抵扣发票未结余额的付款。通常会留下需要继续催收的余额。
重要性区分分期付款与全额结算，对准确预测现金流十分重要。获取途径 CustSettlement表将一笔付款（CustTrans）与发票（CustTrans）关联，且SettlementAmount < InvoiceAmount。采集在CustSettlement中执行交易X时记录事件类型 explicit
	争议审核中	表示争议案例已从“已创建”转为“处理中”。此阶段反映处理团队的实际工作时间。
重要性此处的瓶颈会延长现金转换周期。区分等待时间与实际处理时间。获取途径可根据CaseDetailBase.Status字段变更，或CaseLog表中对应状态推进的记录推断。采集比较CaseDetailBase中的状态字段变更前后的值事件类型 inferred
	争议已解决	争议案例关闭，发票可以继续付款或开具贷项通知单。这标志着异常处理子流程结束。
重要性从创建到解决的时间差是衡量行政处理效率的关键指标。获取途径 CaseDetailBase表中的Status变更为Closed/Resolved。采集比较CaseDetailBase中的状态字段变更前后的值事件类型 inferred
	付款已核销	通过核销日记账将发票认定为无法收回并移除余额，代表一笔财务损失。
重要性催收失败流程的最终状态。分析坏账率和信用政策效果时不可或缺。获取途径 CustTrans表中Type为“WriteOff”的记录，或明确标记核销原因代码的GeneralJournalEntry。采集交易以WriteOff类型过账时记录事件类型 explicit
	已收到付款承诺	催收专员记录的具体承诺，表示客户将在指定日期前付款。数据取自催收管理模块。
重要性衡量催收电话的效果。将承诺日期与实际付款日期比较，可以了解客户承诺的可靠性。获取途径 CustPromiseToPay表。通过TransRecId关联客户交易。采集在CustPromiseToPay中创建记录时记录事件类型 explicit
	已生成利息通知单	为逾期付款创建利息通知单，表示向客户账户收取财务罚金。
重要性表明逾期情况严重。分析有助于判断罚金是否能有效加快付款，或是否会损害客户关系。获取途径 CustInterestJour表。此表记录针对特定客户交易生成的利息通知单。采集交易过账至CustInterestJour时记录事件类型 explicit
	已过到期日	计算得出的里程碑：当前日期已超过发票到期日，且发票仍未结清。此时发票被标记为逾期。
重要性这是账龄分析和触发催收工作流的关键节点，也将标准账单处理与催收管理区分开来。获取途径将CustInvoiceJour.DueDate与当前模拟时间或付款时间戳进行比较后计算得出。采集比较DueDate字段与当前时间后推导得出事件类型 calculated
	银行对账单已匹配	将应收账款中记录的付款与导入的银行对账单行进行匹配的对账事件。
重要性确认款项确已到账。此处延误表明现金与银行管理流程效率低下。获取途径 BankReconciliationLine或BankAccountTrans中的匹配状态。需要高级银行对账模块。采集在银行对账中完成交易匹配时记录事件类型 explicit

建议填写选填

数据提取指南

如何从Microsoft Dynamics 365获取数据

步骤

配置Azure资源：创建Azure SQL数据库，作为Bring Your Own Database（BYOD）目标数据库。确保连接字符串包含具有读写权限的凭据。
配置D365数据管理：依次进入“系统管理”>“工作区”>“数据管理”。点击“将实体导出到数据库”磁贴，输入上一步获取的连接字符串，注册外部数据库。
选择所需实体：在数据管理工作区中创建新的导出项目，并添加以下标准实体：CustInvoiceJournalHeaderEntity、CustTransactionEntity、CustSettlementEntity、CustCollectionLetterJournalHeaderEntity、CaseDetailEntity、CaseAssociationEntity、CustInterestJournalEntity、BankAccountTransEntity和CustPromiseToPayEntity。
映射源数据与目标数据：确保所有字段均已正确映射，尤其要包含RecId和CreatedDateTime字段，这些字段对事件排序至关重要。
执行首次导出：手动运行导出作业（作业类型：导出），将历史数据完整加载到Azure SQL数据库。查看执行摘要，确认所有记录均已成功传输。
安排增量更新：配置定期数据作业，按设定周期运行导出项目（例如每4小时或每晚一次），并使用“增量推送”仅更新发生变化的记录。
连接Azure SQL：使用SQL Server Management Studio（SSMS）或类似工具连接BYOD Azure SQL数据库。
运行转换脚本：对下方“查询”部分中的BYOD表运行提供的SQL脚本。该脚本会将分散在不同表中的数据整合为统一的事件日志结构。
验证并提取数据：检查输出数据是否一致（参见“验证步骤”）。将结果集导出为CSV或Parquet文件，以便导入ProcessMind。

配置

导出方式：Bring Your Own Database（BYOD）或Azure Data Lake Storage Gen2（ADLS）。
刷新策略：首次加载时使用完整推送，定期更新时使用增量推送，以尽量减少对性能的影响。
所需实体：CustInvoiceJournalHeaderEntity（发票）、CustSettlementEntity（付款/清账）、CustCollectionLetterJournalHeaderEntity（催款通知）、CaseDetailEntity（争议）、BankAccountTransEntity（银行对账）。
公司筛选：如果分析范围仅限特定法人实体，请按“DataAreaId”列筛选。
日期范围：建议按“InvoiceDate”或“TransDate”采用滚动12个月时间范围。
变更跟踪：必须在D365的SQL表上启用变更跟踪，才能高效执行增量导出。

查询示例 sql

/* SQL Transformation Script for D365 BYOD Tables */
/* Target: Azure SQL Database containing exported entities */

WITH InvoiceBasics AS (
    SELECT 
        InvoiceNumber AS InvoiceId,
        InvoiceDate,
        DueDate,
        InvoiceAccount AS CustomerAccount,
        SalesId,
        DataAreaId AS CompanyCode,
        InvoiceAmount,
        Currency
    FROM dbo.CustInvoiceJournalHeaderEntity
    WHERE InvoiceDate >= DATEADD(month, -12, GETDATE())
)

/* 1. Invoice Posted */
SELECT 
    T1.InvoiceId AS InvoiceNumber,
    'Invoice Posted' AS Activity,
    CAST(T1.InvoiceDate AS DATETIME) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL AS CustomerGroup,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL AS [User],
    NULL AS BusinessUnit,
    T1.CompanyCode,
    1 AS IsAutomated
FROM InvoiceBasics T1

UNION ALL

/* 2. Invoice Dispatched (Inferred from Document Date as Proxy if Print Log unavailable) */
SELECT 
    T1.InvoiceId,
    'Invoice Dispatched' AS Activity,
    DATEADD(minute, 5, CAST(T1.InvoiceDate AS DATETIME)) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    1
FROM InvoiceBasics T1

UNION ALL

/* 3. Due Date Passed (Synthetic Event) */
SELECT 
    T1.InvoiceId,
    'Due Date Passed' AS Activity,
    CAST(T1.DueDate AS DATETIME) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    1
FROM InvoiceBasics T1
WHERE GETDATE() > T1.DueDate

UNION ALL

/* 4. Payment Reminder Sent */
SELECT 
    Inv.InvoiceId,
    'Payment Reminder Sent' AS Activity,
    CL.JournalDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CL.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustCollectionLetterJournalHeaderEntity CL
JOIN dbo.CustCollectionLetterLineEntity CLL ON CL.JournalNumber = CLL.JournalNumber AND CL.DataAreaId = CLL.DataAreaId
JOIN InvoiceBasics Inv ON CLL.InvoiceId = Inv.InvoiceId AND CLL.DataAreaId = Inv.CompanyCode

UNION ALL

/* 5. Promise to Pay Received */
SELECT 
    Inv.InvoiceId,
    'Promise to Pay Received' AS Activity,
    PTP.PromiseDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustPromiseToPayEntity PTP
JOIN InvoiceBasics Inv ON PTP.Invoice = Inv.InvoiceId AND PTP.DataAreaId = Inv.CompanyCode

UNION ALL

/* 6. Dispute Case Opened */
SELECT 
    Inv.InvoiceId,
    'Dispute Case Opened' AS Activity,
    CD.CreatedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId /* Logic assumes linking via Invoice Number or manual mapping might be needed depending on RefTableId */
WHERE CA.RefEntityName = 'CustInvoiceTable'

UNION ALL

/* 7. Dispute Under Review */
SELECT 
    Inv.InvoiceId,
    'Dispute Under Review' AS Activity,
    CD.ModifiedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.ModifiedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId
WHERE CD.Status = 'InProcess'

UNION ALL

/* 8. Dispute Resolved */
SELECT 
    Inv.InvoiceId,
    'Dispute Resolved' AS Activity,
    CD.ClosedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.ModifiedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId
WHERE CD.Status = 'Closed'

UNION ALL

/* 9. Credit Memo Issued */
SELECT 
    T1.InvoiceId,
    'Credit Memo Issued' AS Activity,
    T1.InvoiceDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    0
FROM dbo.CustInvoiceJournalHeaderEntity T1
WHERE T1.InvoiceAmount < 0

UNION ALL

/* 10. Interest Note Generated */
SELECT 
    Inv.InvoiceId,
    'Interest Note Generated' AS Activity,
    IJ.InterestDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    IJ.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.CustInterestJournalEntity IJ
JOIN InvoiceBasics Inv ON IJ.AccountDisplayValue = Inv.CustomerAccount AND IJ.DataAreaId = Inv.CompanyCode
/* Note: Direct link to specific Invoice ID is often via CustInterestTrans, requiring an additional join */

UNION ALL

/* 11. Partial Payment Posted */
SELECT 
    Inv.InvoiceId,
    'Partial Payment Posted' AS Activity,
    Sett.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Sett.SettleAmountCur AS InvoiceAmount,
    Inv.DueDate,
    Sett.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustSettlementEntity Sett
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE ABS(Sett.SettleAmountCur) < ABS(Trans.AmountCur) AND Sett.SettleAmountCur <> 0

UNION ALL

/* 12. Full Payment Received */
SELECT 
    Inv.InvoiceId,
    'Full Payment Received' AS Activity,
    Sett.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Sett.SettleAmountCur AS InvoiceAmount,
    Inv.DueDate,
    Sett.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustSettlementEntity Sett
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE ABS(Sett.SettleAmountCur) >= ABS(Trans.AmountCur)

UNION ALL

/* 13. Bank Statement Matched */
SELECT 
    Inv.InvoiceId,
    'Bank Statement Matched' AS Activity,
    Bank.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Bank.AmountCur AS InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.BankAccountTransEntity Bank
JOIN dbo.CustSettlementEntity Sett ON Bank.Voucher = Sett.OffsetTransVoucher AND Bank.DataAreaId = Sett.DataAreaId
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId

UNION ALL

/* 14. Payment Written Off */
SELECT 
    Inv.InvoiceId,
    'Payment Written Off' AS Activity,
    Trans.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Trans.AmountCur AS InvoiceAmount,
    Inv.DueDate,
    Trans.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustTransactionEntity Trans
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE Trans.TransactionType = 'WriteOff'

UNION ALL

/* 15. Invoice Cleared */
SELECT 
    Inv.InvoiceId,
    'Invoice Cleared' AS Activity,
    Trans.ClosedDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.CustTransactionEntity Trans
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE Trans.ClosedDate IS NOT NULL AND Trans.ClosedDate <> '1900-01-01'

步骤

准备运行环境：确保执行计算机已安装Python 3.x。使用pip安装所需库：pandas、requests和msal（如果使用Microsoft Authentication Library；为简化流程，此脚本直接使用REST）。
注册Azure应用：在Azure Active Directory（Entra ID）中注册应用，以允许其访问Dynamics 365。记下Client ID、Client Secret和Tenant ID。确保应用已获得Dynamics ERP（OData）的API权限。
配置脚本：打开提供的Python脚本。在顶部的config字典中填写您的D365 URL、Client ID、Client Secret、Tenant ID和目标公司代码（dataAreaId）。
设置日期范围：修改脚本中的start_date和end_date变量，以设定提取时间范围。增量加载建议采用最近90至180天的滚动时间窗口。
执行提取：运行脚本。脚本会通过Azure AD进行身份验证、获取访问令牌，并遍历多个OData实体（CustomerInvoices、PaymentJournals、CaseDetails等）来提取数据。
处理数据：脚本会自动转换数据，例如将列名重命名为符合ProcessMind架构的名称、根据系统日期计算“已过付款到期日”活动，并根据交易金额区分部分付款和全额付款。
合并并排序：脚本会将所有活动数据框合并为一个事件日志，并按InvoiceNumber和EventTimestamp排序。
导出：最终数据框将导出为名为AR_Event_Log.csv的CSV文件。请确认该文件已生成在脚本所在目录中。
验证：打开CSV文件，检查日期是否采用ISO格式（YYYY-MM-DD HH:MM:SS），并确认所有必需列均已填充。
上传：使用标准文件上传向导将生成的CSV文件导入ProcessMind。将列InvoiceNumber映射到案例ID，将列活动映射到活动，将列EventTimestamp映射到时间戳。

配置

批次大小：脚本使用服务器控制的默认分页大小（通常为1000条记录），并通过@odata.nextLink属性自动处理分页。
日期筛选：使用OData的$filter参数，将数据提取限制在相关日期范围内（例如InvoiceDate ge 2023-01-01T00:00:00Z），以显著提升性能。
公司筛选：D365存储多个法人实体的数据。脚本按dataAreaId筛选，确保只提取相关业务单元的数据。
身份验证：使用OAuth 2.0（客户端凭据流）。您必须拥有有效的Client Secret并完成应用注册。
实体选择：使用CustomerInvoices、CustomerPaymentJournalLines和CaseDetail等标准实体。如果组织使用自定义实体，则必须更新endpoints字典中的终结点。
计算事件：脚本运行时会计算“已过付款到期日”等事件，因为数据库中不会为这些事件生成专门的交易日志。

查询示例 python

import requests
import pandas as pd
import datetime
from datetime import  timedelta
import json

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
config = {
    class="hl-string">"resource": class="hl-string">"https://[YourOrg].operations.dynamics.com",
    class="hl-string">"tenant_id": class="hl-string">"[Your-Tenant-ID]",
    class="hl-string">"client_id": class="hl-string">"[Your-Client-ID]",
    class="hl-string">"client_secret": class="hl-string">"[Your-Client-Secret]",
    class="hl-string">"company_code": class="hl-string">"USMF",  class=class="hl-string">"hl-comment"># Example Company Code
    class="hl-string">"start_date": class="hl-string">"2023-01-01T00:00:00Z"
}

class=class="hl-string">"hl-comment"># === AUTHENTICATION ===
def get_token(config):
    token_url = fclass="hl-string">"https://login.microsoftonline.com/{config['tenant_id']}/oauth2/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': config[class="hl-string">'client_id'],
        class="hl-string">'client_secret': config[class="hl-string">'client_secret'],
        class="hl-string">'resource': config[class="hl-string">'resource']
    }
    response = requests.post(token_url, data=payload)
    if response.status_code == 200:
        return response.json().get(class="hl-string">'access_token')
    else:
        raise Exception(fclass="hl-string">"Auth Failed: {response.text}")

class=class="hl-string">"hl-comment"># === ODATA FETCHER ===
def get_odata_data(token, entity_url, filter_str):
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}', class="hl-string">'Accept': class="hl-string">'application/json'}
    results = []
    next_link = fclass="hl-string">"{config['resource']}/data/{entity_url}?$filter={filter_str}&$format=json"
    
    while next_link:
        print(fclass="hl-string">"Fetching: {next_link}")
        try:
            res = requests.get(next_link, headers=headers)
            if res.status_code != 200:
                print(fclass="hl-string">"Error fetching data: {res.text}")
                break
            data = res.json()
            results.extend(data.get(class="hl-string">'value', []))
            next_link = data.get(class="hl-string">'@odata.nextLink', None)
        except Exception as e:
            print(fclass="hl-string">"Exception: {e}")
            break
    return pd.DataFrame(results)

class=class="hl-string">"hl-comment"># === MAIN EXTRACTION ===
def main():
    token = get_token(config)
    all_events = []
    company_filter = fclass="hl-string">"dataAreaId eq '{config['company_code']}'"
    date_filter = fclass="hl-string">"InvoiceDate ge {config['start_date']}"

    class=class="hl-string">"hl-comment"># 1. INVOICE BASIC EVENTS (Posted, Dispatched, Credit Memo, Cleared)
    class=class="hl-string">"hl-comment"># Entity: CustomerInvoices (or SalesInvoiceHeaders)
    print(class="hl-string">"Processing Invoices...")
    df_inv = get_odata_data(token, class="hl-string">"CustomerInvoices", fclass="hl-string">"{company_filter} and {date_filter}")
    
    if not df_inv.empty:
        class=class="hl-string">"hl-comment"># Activity: Invoice Posted
        posted = df_inv.copy()
        posted[class="hl-string">'Activity'] = class="hl-string">'Invoice Posted'
        posted[class="hl-string">'EventTimestamp'] = posted[class="hl-string">'InvoiceDate']
        all_events.append(posted)

        class=class="hl-string">"hl-comment"># Activity: Invoice Dispatched (Assumed same day as posted if not tracked separately)
        dispatched = df_inv.copy()
        dispatched[class="hl-string">'Activity'] = class="hl-string">'Invoice Dispatched'
        dispatched[class="hl-string">'EventTimestamp'] = dispatched[class="hl-string">'InvoiceDate']  class=class="hl-string">"hl-comment"># Often inferred
        all_events.append(dispatched)

        class=class="hl-string">"hl-comment"># Activity: Credit Memo Issued
        credit_memos = df_inv[df_inv[class="hl-string">'InvoiceAmount'] < 0].copy()
        credit_memos[class="hl-string">'Activity'] = class="hl-string">'Credit Memo Issued'
        credit_memos[class="hl-string">'EventTimestamp'] = credit_memos[class="hl-string">'InvoiceDate']
        all_events.append(credit_memos)

        class=class="hl-string">"hl-comment"># Activity: Invoice Cleared (Closed)
        class=class="hl-string">"hl-comment"># Note: ClosedDate might need to be cast to datetime
        cleared = df_inv[df_inv[class="hl-string">'ClosedDate'].notnull()].copy()
        if not cleared.empty:
            cleared[class="hl-string">'Activity'] = class="hl-string">'Invoice Cleared'
            cleared[class="hl-string">'EventTimestamp'] = cleared[class="hl-string">'ClosedDate']
            all_events.append(cleared)

        class=class="hl-string">"hl-comment"># Activity: Due Date Passed (Calculated)
        class=class="hl-string">"hl-comment"># Logic: If not closed, and DueDate < Now
        now = datetime.datetime.now()
        overdue = df_inv[(df_inv[class="hl-string">'ClosedDate'].isnull())].copy()
        class=class="hl-string">"hl-comment"># Convert DueDate to datetime objects for comparison
        overdue[class="hl-string">'DueDate_DT'] = pd.to_datetime(overdue[class="hl-string">'DueDate'])
        overdue = overdue[overdue[class="hl-string">'DueDate_DT'] < now]
        if not overdue.empty:
            overdue[class="hl-string">'Activity'] = class="hl-string">'Due Date Passed'
            class=class="hl-string">"hl-comment"># Timestamp is the day it became overdue
            overdue[class="hl-string">'EventTimestamp'] = overdue[class="hl-string">'DueDate'] 
            all_events.append(overdue)

    class=class="hl-string">"hl-comment"># 2. PAYMENTS (Partial, Full, WriteOff)
    class=class="hl-string">"hl-comment"># Entity: CustomerPaymentJournalLines
    print(class="hl-string">"Processing Payments...")
    class=class="hl-string">"hl-comment"># Note: Filter by TransDate usually
    df_pay = get_odata_data(token, class="hl-string">"CustomerPaymentJournalLines", fclass="hl-string">"{company_filter}")
    
    if not df_pay.empty:
        class=class="hl-string">"hl-comment"># Activity: Partial vs Full Payment
        class=class="hl-string">"hl-comment"># Logic requires comparing payment amount to invoice balance. simplified here based on Amount
        class=class="hl-string">"hl-comment"># Assuming we join with invoices later, strictly classifying based on available fields
        class=class="hl-string">"hl-comment"># Here we assume a field class="hl-string">'PaymentStatus' or we treat all as Partial unless marked Settled
        
        class=class="hl-string">"hl-comment"># Full Payment
        full_pay = df_pay.copy()
        full_pay[class="hl-string">'Activity'] = class="hl-string">'Full Payment Received'
        full_pay[class="hl-string">'EventTimestamp'] = full_pay[class="hl-string">'TransDate']
        all_events.append(full_pay)
        
        class=class="hl-string">"hl-comment"># Partial Payment (In a real scenario, this requires logic checking settlement vs invoice total)
        class=class="hl-string">"hl-comment"># Adding a duplicate row for demonstration of structure
        partial_pay = df_pay[df_pay[class="hl-string">'Amount'] < 1000].copy() class=class="hl-string">"hl-comment"># Placeholder logic
        partial_pay[class="hl-string">'Activity'] = class="hl-string">'Partial Payment Posted'
        partial_pay[class="hl-string">'EventTimestamp'] = partial_pay[class="hl-string">'TransDate']
        all_events.append(partial_pay)

        class=class="hl-string">"hl-comment"># Activity: Payment Written Off
        class=class="hl-string">"hl-comment"># Filter by specific Transaction Type or Journal Name
        write_off = df_pay[df_pay[class="hl-string">'JournalName'].str.contains(class="hl-string">'WriteOff', case=False, na=False)].copy()
        write_off[class="hl-string">'Activity'] = class="hl-string">'Payment Written Off'
        write_off[class="hl-string">'EventTimestamp'] = write_off[class="hl-string">'TransDate']
        all_events.append(write_off)

    class=class="hl-string">"hl-comment"># 3. COLLECTIONS (Reminders, Promise to Pay)
    class=class="hl-string">"hl-comment"># Entity: CollectionLetterJournals
    print(class="hl-string">"Processing Collections...")
    df_col = get_odata_data(token, class="hl-string">"CollectionLetterJournals", fclass="hl-string">"{company_filter}")
    if not df_col.empty:
        reminders = df_col.copy()
        reminders[class="hl-string">'Activity'] = class="hl-string">'Payment Reminder Sent'
        reminders[class="hl-string">'EventTimestamp'] = reminders[class="hl-string">'LetterDate']
        all_events.append(reminders)

    class=class="hl-string">"hl-comment"># Entity: CustomerPromiseToPay
    df_promise = get_odata_data(token, class="hl-string">"CustomerPromiseToPay", fclass="hl-string">"{company_filter}")
    if not df_promise.empty:
        promises = df_promise.copy()
        promises[class="hl-string">'Activity'] = class="hl-string">'Promise to Pay Received'
        promises[class="hl-string">'EventTimestamp'] = promises[class="hl-string">'CreatedDateTime']
        all_events.append(promises)

    class=class="hl-string">"hl-comment"># 4. DISPUTES (Opened, Review, Resolved)
    class=class="hl-string">"hl-comment"># Entity: CaseDetails (requires filtering for Collections/Dispute category)
    print(class="hl-string">"Processing Disputes...")
    df_case = get_odata_data(token, class="hl-string">"CaseDetails", fclass="hl-string">"{company_filter} and Category eq 'Collections'")
    if not df_case.empty:
        class=class="hl-string">"hl-comment"># Opened
        opened = df_case.copy()
        opened[class="hl-string">'Activity'] = class="hl-string">'Dispute Case Opened'
        opened[class="hl-string">'EventTimestamp'] = opened[class="hl-string">'CreatedDateTime']
        all_events.append(opened)

        class=class="hl-string">"hl-comment"># Under Review (In Process)
        in_process = df_case[df_case[class="hl-string">'Status'] == class="hl-string">'InProcess'].copy()
        in_process[class="hl-string">'Activity'] = class="hl-string">'Dispute Under Review'
        in_process[class="hl-string">'EventTimestamp'] = in_process[class="hl-string">'ModifiedDateTime']
        all_events.append(in_process)

        class=class="hl-string">"hl-comment"># Resolved (Closed)
        resolved = df_case[df_case[class="hl-string">'Status'] == class="hl-string">'Closed'].copy()
        resolved[class="hl-string">'Activity'] = class="hl-string">'Dispute Resolved'
        resolved[class="hl-string">'EventTimestamp'] = resolved[class="hl-string">'ClosedDateTime']
        all_events.append(resolved)

    class=class="hl-string">"hl-comment"># 5. INTEREST
    class=class="hl-string">"hl-comment"># Entity: InterestNotes
    print(class="hl-string">"Processing Interest Notes...")
    df_interest = get_odata_data(token, class="hl-string">"InterestNotes", fclass="hl-string">"{company_filter}")
    if not df_interest.empty:
        interest = df_interest.copy()
        interest[class="hl-string">'Activity'] = class="hl-string">'Interest Note Generated'
        interest[class="hl-string">'EventTimestamp'] = interest[class="hl-string">'NoteDate']
        all_events.append(interest)

    class=class="hl-string">"hl-comment"># 6. BANK RECONCILIATION
    class=class="hl-string">"hl-comment"># Entity: BankReconciliationLines (Placeholder for matching)
    print(class="hl-string">"Processing Bank Reconciliation...")
    class=class="hl-string">"hl-comment"># This entity availability varies by version
    df_bank = get_odata_data(token, class="hl-string">"BankReconciliationLines", fclass="hl-string">"{company_filter}")
    if not df_bank.empty:
        matched = df_bank[df_bank[class="hl-string">'Reconciled'] == True].copy()
        matched[class="hl-string">'Activity'] = class="hl-string">'Bank Statement Matched'
        matched[class="hl-string">'EventTimestamp'] = matched[class="hl-string">'TransactionDate']
        class=class="hl-string">"hl-comment"># Need to join with Invoice via Payment Reference ideally
        class=class="hl-string">"hl-comment"># For this script, we assume InvoiceNumber is present or mapped
        all_events.append(matched)

    class=class="hl-string">"hl-comment"># === UNIFICATION & EXPORT ===
    if not all_events:
        print(class="hl-string">"No events extracted.")
        return

    final_df = pd.concat(all_events, ignore_index=True)

    class=class="hl-string">"hl-comment"># Map columns to schema
    class=class="hl-string">"hl-comment"># Note: Column names class="hl-string">'InvoiceNumber', class="hl-string">'InvoiceAccount' etc. depend on exact Entity schema
    class=class="hl-string">"hl-comment"># You may need to rename based on your D365 entity fields, e.g., class="hl-string">'InvoiceNum' -> class="hl-string">'InvoiceNumber'
    schema_map = {
        class="hl-string">'InvoiceNumber': class="hl-string">'InvoiceNumber',
        class="hl-string">'InvoiceId': class="hl-string">'InvoiceNumber', class=class="hl-string">"hl-comment"># Alternative common name
        class="hl-string">'InvoiceAccount': class="hl-string">'CustomerAccount',
        class="hl-string">'CustomerAccount': class="hl-string">'CustomerAccount',
        class="hl-string">'InvoiceAmount': class="hl-string">'InvoiceAmount',
        class="hl-string">'Amount': class="hl-string">'InvoiceAmount', class=class="hl-string">"hl-comment"># Fallback for payments
        class="hl-string">'CurrencyCode': class="hl-string">'Currency',
        class="hl-string">'DueDate': class="hl-string">'DueDate',
        class="hl-string">'CreatedBy': class="hl-string">'User',
        class="hl-string">'BusinessUnit': class="hl-string">'BusinessUnit',
        class="hl-string">'DataAreaId': class="hl-string">'CompanyCode'
    }
    
    class=class="hl-string">"hl-comment"># Normalize columns
    for col in final_df.columns:
        if col in schema_map:
            final_df.rename(columns={col: schema_map[col]}, inplace=True)

    class=class="hl-string">"hl-comment"># Add constant fields
    final_df[class="hl-string">'SourceSystem'] = class="hl-string">'Microsoft D365'
    final_df[class="hl-string">'LastDataUpdate'] = datetime.datetime.now().isoformat()
    
    class=class="hl-string">"hl-comment"># Select final columns (fill missing with None)
    required_cols = [
        class="hl-string">'InvoiceNumber', class="hl-string">'Activity', class="hl-string">'EventTimestamp', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'CustomerAccount', class="hl-string">'CustomerGroup', class="hl-string">'InvoiceAmount', class="hl-string">'DueDate', class="hl-string">'User',
        class="hl-string">'BusinessUnit', class="hl-string">'CompanyCode', class="hl-string">'IsAutomated'
    ]
    
    for col in required_cols:
        if col not in final_df.columns:
            final_df[col] = None
            
    output_df = final_df[required_cols]
    
    class=class="hl-string">"hl-comment"># Export
    output_df.to_csv(class="hl-string">"AR_Event_Log.csv", index=False)
    print(class="hl-string">"Extraction complete. Saved to AR_Event_Log.csv")

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

准备好开始了吗？

立即使用此数据模板，着手改善财务运营。我们的团队可以协助您将Microsoft Dynamics 365数据映射到这些要求。

返回改进指南

下载模板

优化应收账款管理，改善现金流

与使用Microsoft Dynamics 365、将DSO缩短15至20天的团队一起改进

开始免费试用

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

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

数据提取指南

D365数据管理框架实体导出

步骤

配置

查询示例 sql

使用Python OData API提取应收账款事件

步骤

配置

查询示例 python

准备好开始了吗？

优化应收账款管理，改善现金流