> 持续流程改进 > 采购到付款发票处理 > Microsoft Dynamics 365 > 数据模板

您的采购到付款 - 发票处理数据模板

Microsoft Dynamics 365

采购到付款 - 发票处理属性(22) 采购到付款 - 发票处理活动(14) 数据提取指南(4)

您的采购到付款 - 发票处理数据模板

此模板清晰说明了如何从Microsoft Dynamics 365提取分析采购到付款 - 发票处理所需的关键数据。模板列出了需要收集的重要属性、需要跟踪的关键活动，并提供实用的数据提取指南。借助此资源，您可以确保收集全面分析和优化流程所需的信息。

深入分析建议收集的属性
发票处理全周期内需要跟踪的关键活动
从Microsoft Dynamics 365提取数据的指南

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

下载模板

采购到付款 - 发票处理属性

为全面分析采购到付款 - 发票处理，建议在事件日志中加入以下数据字段。

5 必填 7 建议填写 10 选填

	名称	说明
	事件时间 EventTime	指示特定活动或事件发生时间的精确时间戳。
说明事件时间即时间戳，记录活动发生的准确日期和时间。它是事件日志的重要组成部分，提供按正确顺序排列活动和计算时长所需的时间信息。在分析中，时间戳是所有时间类指标的依据。它用于计算活动时长、不同步骤之间的周期时间（例如审批时间），以及每张发票的端到端总处理时间，也支持开展趋势分析。重要性此时间戳对于排列事件顺序、计算周期时间和时长，以及识别流程瓶颈至关重要。获取途径此数据来自多个数据表中的日期/时间字段，例如VendInvoiceInfoTable、VendTrans或工作流历史记录表中的createdDateTime或modifiedDateTime字段。示例 2023-04-15T09:00:12Z2023-05-20T14:30:00Z2023-06-01T11:05:45Z
	发票编号 InvoiceNumber	每张供应商发票的唯一标识符，也是跟踪其处理周期的主要案例ID。
说明发票编号是关联单张供应商发票所有活动的唯一键。它支持端到端追踪发票从接收、登记到匹配、审批和最终付款的全过程。在流程挖掘分析中，此属性至关重要。它用于定义案例，从而重建每张发票的流程路径，计算总周期时间、识别流程变体，并分析发票的具体特征和处理结果。重要性这是关联所有相关事件的关键案例ID，因此可以分析每张发票的完整处理周期。获取途径通常位于供应商发票主表中，例如VendInvoiceInfoTable的Num字段。示例 INV-10056773245-AUS-001-98432
	活动名称 ActivityName	发票处理周期中某一时点发生的具体业务事件或任务的名称。
说明活动名称用于描述发票流程中的具体步骤或状态变化，例如“发票已登记”“发票已提交审批”或“已执行付款”。这些数据对于构建流程图和了解事件顺序至关重要。分析此属性可以揭示流程路径、识别常见处理方式，并突出显示偏差或瓶颈。它还可用于计算活动之间的周期时间，例如从审批到过账的时长，以及衡量拒绝或付款冻结等特定事件的发生频率。重要性它定义流程图中的步骤，支持流程可视化，并可分析不同活动之间的转换。获取途径此属性通常根据Dynamics 365中与发票处理相关的多个数据表中的状态字段、交易类型或变更日志条目组合得出。示例发票已提交审批发现匹配差异已执行付款
	上次数据更新 LastDataUpdate	指示上次从源系统刷新此流程数据的时间戳。
说明此属性记录上次从Microsoft Dynamics 365提取并更新数据集的时间。同一次数据载入中的所有记录通常具有相同的值。此信息有助于用户了解所分析数据的新旧程度，判断流程洞察反映的实际情况有多新，并管理数据刷新计划和验证数据管道。重要性此属性告知用户数据的时效性，帮助他们了解分析结果的更新时间以及预计下次更新的时间。获取途径执行数据提取或ETL流程时，系统会生成此时间戳并写入数据集。示例 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	源系统 SourceSystem	提取事件数据的记录系统。
说明此属性用于标识活动数据的来源应用。对于此流程，来源通常为“Microsoft Dynamics 365”。在包含多个系统的环境中，例如使用外部OCR或数据采集解决方案时，此字段有助于区分流程各步骤的发生位置。它能清楚呈现数据来源，并帮助排查数据提取问题。重要性此属性提供有关数据来源的重要背景信息，有助于验证数据并了解流程涉及的系统。获取途径这是一个静态值“Microsoft Dynamics 365”，在数据转换过程中添加，用于标记数据集的来源。示例 Microsoft Dynamics 365
	付款到期日 PaymentDueDate	根据约定的付款条款，发票应付清的日期。
说明付款到期日根据发票日期和供应商付款条款计算，代表合同约定的付款期限。此日期对于衡量财务绩效和管理供应商关系至关重要。将其与实际“付款执行”日期比较，可计算“按时付款率”KPI。分析这一数据有助于发现导致逾期付款的系统性问题，避免损害供应商关系或错失提前付款折扣。重要性这是衡量按时付款绩效的基准，也是评估财务状况和供应商关系的重要KPI。获取途径此日期通常由系统根据发票日期和付款条款计算，并存储在供应商交易表中，例如VendTrans。示例 2023-05-152023-06-302023-07-20
	供应商编号 VendorNumber	提交发票的供应商唯一标识。
说明供应商编号是在主数据中唯一标识供应商的代码。它将发票交易与特定供应商关联起来，便于按供应商分析。此属性对于按供应商细分流程数据至关重要。借助它，您可以了解“哪些供应商的审批时间最长？”或“某些供应商是否更容易出现匹配差异？”等问题。按供应商分析绩效，有助于发现供应商协作和流程改进的机会。重要性支持按供应商筛选和分析根本原因，帮助您发现与特定供应商相关的绩效问题或规律。获取途径通常位于供应商发票表的表头中，例如VendInvoiceInfoTable，并关联到VendTable中的供应商主数据。示例 V-1001V-2050V-8342
	发票状态 InvoiceStatus	提取数据时发票的当前状态。
说明发票状态表示发票在生命周期中的当前状态，例如“待审批”“已批准”“已过账”或“已付款”，反映发票当前的处理进度。此属性主要用于创建运营仪表板，展示当前工作量和积压情况。管理者可以据此了解各流程阶段的发票数量，从而安排资源并监控运营情况。它也是“发票处理吞吐量与状态”仪表板的关键数据。重要性展示所有发票的当前状态，是运营监控、工作量管理和发现当前瓶颈的重要依据。获取途径通常根据主发票表中的状态字段得出，例如VendInvoiceInfoTable中的字段。示例待审批已过账已付款
	发票金额 InvoiceAmount	发票的货币总额。
说明此属性表示供应商发票上注明的应付总额，是每个案例的基本财务数据。发票金额常用于细分和分析。您可以据此判断高金额发票是否需要更严格的审批，或处理时间是否更长。仪表板也会用它按金额分析发票量，并汇总计算分析范围内的总支出。重要性支持按金额分析，帮助您优先改进高金额发票的处理流程，并了解成本驱动因素。获取途径位于发票表头表中，例如VendInvoiceInfoTable的InvoiceAmount或Amount字段。示例 1500.00250.7512500.50
	用户 User	执行该活动的人员的用户ID或姓名。
说明此属性用于识别负责执行特定流程步骤的员工或系统用户。对于自动执行的步骤，用户可能是系统账号或服务账号。按用户分析流程，有助于了解工作量分配、发现培训需求，并比较个人或团队的绩效。分析返工循环时，此属性也很重要，因为工作可能会在不同用户之间来回传递。它还能帮助您了解不同用户完成同一任务的方式有何差异。重要性支持按用户或团队分析绩效和工作量，并帮助您了解自动化程度和返工来源。获取途径数据来自交易或工作流历史记录表中的用户ID字段，例如createdby或modifiedby字段。相关信息也可能存储在工作流跟踪表中。示例 j.doea.smithAX_Admin
	结束时间 EndTime	表示特定活动或事件完成时间的精确时间戳。
说明结束时间记录活动完成的时间。将其与开始时间（EventTime）结合使用，即可精确计算每个步骤的耗时。此属性对于计算每项活动的“处理时间”至关重要，也是绩效分析的重要指标。它能帮助您准确找出流程中最耗时的步骤，以便有针对性地改进。例如，您可以测量手动编码或审批步骤的确切耗时。重要性支持直接计算活动处理时间，是识别流程中最耗时步骤的基础。获取途径与StartTime类似，此数据来自各种日期或时间字段。对于某些活动，可以使用下一项活动的StartTime；其他情况下，则可能使用专门的“完成时间”时间戳。示例 2023-04-15T09:15:20Z2023-05-20T18:00:00Z2023-06-01T11:05:45Z
	采购订单编号 PurchaseOrderNumber	与发票关联的采购订单（PO）标识。
说明采购订单编号将发票与原始采购单据关联起来。对于需要在发票、PO和收货记录之间进行二方或三方匹配的流程，这一属性至关重要。借助此属性，您可以分析PO发票与非PO发票。两者通常遵循不同的流程路径。它对于了解匹配流程的有效性、调查匹配差异，以及衡量有PO支持的发票直通处理率都很重要。重要性用于区分有PO支持的发票和非PO发票。这是造成流程差异的主要因素，也是匹配分析的关键。获取途径位于发票表头表中，例如VendInvoiceInfoTable，通常对应PurchId等字段。示例 PO-001234PO-005678PO-009101
	付款冻结原因 PaymentBlockReason	说明发票为何被设置付款冻结的原因代码。
说明付款冻结会阻止发票付款。此属性记录冻结原因，通常是用户选择的标准代码，例如“费用存在争议”或“等待收货”。此属性是“付款冻结频率与持续时间”仪表板的关键数据。分析最常见的付款冻结原因，有助于发现采购到付款流程中的潜在问题。解决这些根本原因可以显著减少付款延迟并改善供应商关系。重要性说明付款为何被有意延迟，帮助您发现并解决导致付款冻结的上游问题。获取途径通常是供应商交易记录或发票记录中的字段，常与付款冻结状态相关。可在VendTrans等表中查找。示例质量争议等待贷项通知单手动冻结
	付款条款 PaymentTerms	与供应商约定的付款条款，例如“Net 30”或“2% 10, Net 30”。
说明付款条款规定了与供应商约定的付款条件，包括付款期限和可能适用的折扣。此信息通常来自供应商主数据或采购订单。此属性为付款到期日和按时付款率KPI提供重要背景。分析特定付款条款是否与逾期付款相关，有助于发现运营问题。它也有助于财务规划和有效管理营运资金。重要性为付款到期日提供背景信息，并帮助您分析付款时间和折扣获取率对财务的影响。获取途径此信息存储在供应商主数据（VendTable）中，并复制到VendInvoiceInfoTable等交易表。示例 Net 30Net 602% 10, Net 30
	公司代码 CompanyCode	处理发票的组织内法律实体或公司的标识。
说明公司代码表示对发票承担财务责任的特定法律实体。对于拥有多家公司的组织，这是重要的财务细分数据。借助此属性，您可以按不同法律实体筛选或比较流程分析结果。它有助于发现流程绩效、政策或瓶颈是否集中在集团内的特定公司，从而支持有针对性的本地流程改进。重要性支持按法律实体细分流程分析，对于拥有多家公司的大型组织尤为重要。获取途径 Dynamics 365的大多数财务表中都有此标准字段，通常名为DataAreaId。示例 USMFDEMFGBSI
	发票币种 InvoiceCurrency	发票金额所用的币种代码，例如USD、EUR。
说明发票币种表示发票金额所用的币种。对于与不同国家供应商开展业务的跨国组织，这一信息尤为重要。此属性为发票金额提供必要背景，可用于筛选和报告，尤其适用于全球业务。它有助于正确解读财务金额，并支持按币种分析或换算为统一的报告币种。重要性为财务金额提供必要背景，便于准确解读和分析不同地区的发票金额。获取途径位于发票表头表中，与发票金额字段相邻，例如VendInvoiceInfoTable中的CurrencyCode字段。示例 USDEURGBP
	差异类型 MatchingDiscrepancyType	对发票与采购订单匹配过程中发现的不一致进行分类。
说明二方或三方匹配出现差异时，此属性用于说明问题类型，例如“价格不符”“数量不符”或“缺少收货记录”。此数据是“匹配差异解决时间”仪表板的核心内容。分析不同差异类型的发生频率，有助于企业找出匹配失败的根本原因。例如，频繁出现价格不符可能说明主数据过时，而数量不符则可能表明存在运输问题。重要性对匹配失败进行分类，支持分析根本原因，从而降低差异发生率并提高直通处理率。获取途径此信息可能存储在专门的匹配记录表或异常记录表中，也可能需要根据状态消息或工作流备注推导。示例价格不符数量不符缺少收货记录
	总周期时间 TotalCycleTime	处理单张发票的端到端总时长，从收到发票到完成付款。
说明总周期时间衡量发票在整个流程中的耗时。对于每个发票编号，计算最早活动（例如“发票登记”）与最晚活动（例如“付款执行”）之间的时间差。这是衡量整体流程效率的主要KPI，可从整体上反映绩效，并用于“发票端到端周期时间”仪表板。分析此指标的趋势，并按供应商或金额等属性细分，有助于发现整体改进机会。重要性这是衡量整体流程速度和效率的关键KPI，可概括每张发票的处理绩效。获取途径此指标由流程挖掘工具按案例计算，或在数据准备阶段计算。它表示每个CaseId的首个与末个事件时间戳之间的时间差。示例 P15DP22DT5H30MP7D
	拒绝原因 RejectionReason	审批人拒绝发票时提供的原因。
说明发票在审批工作流中被拒绝时，审批人通常会说明原因。此属性记录相关说明，内容可以是预设代码，也可以是自由文本。此属性是“发票拒绝分析”仪表板的关键数据，可帮助您了解拒绝的根本原因，例如“采购订单编号错误”“发票重复”或“价格不符”。分析这些原因有助于优先采取措施，降低拒绝率。重要性直接揭示发票被拒绝的原因，帮助您采取有针对性的措施，减少返工并提高一次通过率。获取途径通常位于工作流处理表的备注或历史记录中。示例数量错误发票重复超出审批限额
	是否按时付款 IsOnTimePayment	用于标记发票是否在到期日当天或之前付清的计算字段。
说明此布尔属性通过比较“付款执行”活动的时间戳与付款到期日得出。如果付款日期早于或等于到期日，则值为true，否则为false。此属性直接支持“按时付款率”KPI和“付款条款执行情况”仪表板。它为每张发票提供明确的二元结果，简化分析。您可以轻松筛选逾期付款，并按供应商、部门或国家调查其根本原因。重要性直接衡量付款条款的执行情况，简化按时付款率KPI的计算及相关分析。获取途径此属性在数据转换层计算，逻辑为：（“付款执行”活动的时间戳）<=（付款到期日）。示例 truefalse
	是否自动执行 IsAutomated	用于标记活动由系统自动执行还是由人工用户执行。
说明此布尔属性（true/false）用于区分系统执行的事件和人工用户任务。例如，发票匹配步骤可能由系统自动完成，而差异处理则由人工完成。分析此属性有助于了解流程的自动化程度、衡量自动化举措的成效、发现仍由人工处理的瓶颈，并找出进一步自动化的机会，以提高效率、减少错误。重要性帮助衡量流程的自动化程度，发现人工处理的瓶颈和提升效率的机会。获取途径通常根据“用户”属性得出。如果用户是已知的系统账号或服务账号，此标记将设为true。示例 truefalse
	部门 Department	分摊发票费用的成本中心或部门。
说明部门用于标识负责承担发票费用的业务单元或成本中心，通常在发票编码活动中确定。按部门分析流程，有助于了解各部门在支出、审批时间和合规方面的差异。您可以据此发现哪些部门的发票异常最多或审批周期最长，为有针对性的沟通和培训提供依据。重要性支持按部门分析成本和流程，帮助您了解组织内各部门在绩效和合规方面的差异。获取途径数据来自与发票行或表头关联的财务维度。需要查询与维度相关的数据结构。示例销售市场营销IT运营

必填建议填写选填

采购到付款 - 发票处理活动

为准确发现和分析发票处理工作流，建议在事件日志中记录以下关键流程步骤和里程碑。

6 建议填写 8 选填

	活动	说明
	发票已与采购订单匹配	表示匹配流程已成功完成，发票信息与采购订单及收货信息相符。当发票匹配状态更新为“通过”时，可推断此事件已发生。
重要性成功匹配是有采购订单支持的发票的一项重要里程碑，可在付款前确认费用有效。跟踪匹配效率和自动化率也离不开这一事件。获取途径根据供应商发票抬头或行项目（例如VendInvoiceInfoTable）中的匹配状态字段更新为“通过”或“匹配成功”来推断。采集发票匹配验证详情中的状态变为“通过”时的时间戳。事件类型 inferred
	发票已批准	表示工作流已最终批准发票，授权其过账和付款。工作流引擎会在审批完成时明确记录此事件。
重要性这是审批流程结束的重要里程碑。从“发票已提交审批”到此事件的时长，是衡量审批效率的重要指标，也有助于发现瓶颈。获取途径当工作流状态变为“已批准”时，工作流历史记录表（例如WorkflowTrackingStatusTable）会明确记录此事件。采集工作流历史记录中“已批准”状态条目的时间戳。事件类型 explicit
	发票已提交审批	此活动标志着发票正式提交至工作流，进入审核和审批环节。Dynamics 365工作流引擎会明确记录此提交事件。
重要性这是衡量整个审批周期的起点。它有助于确定发票在审批流程开始前等待了多久，也是审批相关KPI的触发点。获取途径当发票提交至工作流时，工作流历史记录表（例如WorkflowTrackingStatusTable）会明确记录此事件。采集工作流历史记录中工作流提交记录的创建时间戳。事件类型 explicit
	发票已登记	发票首次录入系统时，会先创建一条占位记录，之后再处理完整信息。当供应商发票登记日记账中创建新记录时，系统会记录此活动。
重要性此活动标志着发票处理周期正式开始。从这一时点开始分析，有助于衡量总处理时长并发现早期延误。获取途径这是从供应商发票登记记录创建操作中捕获的明确事件，通常来自VendInvoiceRegisterJournalTable或类似的待处理发票表。采集供应商发票登记日记账中记录的创建时间戳。事件类型 explicit
	发票已过账	这是正式的会计事件，表示已批准的发票记入总账并形成负债。这是系统中明确记录的事务事件。
重要性过账是付款前的最后一步，也是重要的财务控制点。从“发票已批准”到“发票已过账”的时长，可衡量最后几个会计步骤的效率。获取途径这是从VendTrans等表中已过账供应商交易的创建操作捕获的明确事件，也包括相关的GeneralJournalEntry和LedgerEntry记录。采集供应商交易表（VendTrans）中记录的创建时间戳。事件类型 explicit
	已执行付款	这是最后一项活动，表示付款日记账已过账，付款随之生成，发票也已结清。这是一项明确记录的事务事件，标志着发票处理周期结束。
重要性此活动标志着发票付款流程成功结束，是衡量“按时付款率”和端到端总周期时间的依据。获取途径此事件来自付款日记账的过账操作。结算信息记录在VendSettlement等表中，用于关联付款和发票。采集用于结清供应商交易的付款日记账过账时间戳。事件类型 explicit
	匹配差异已处理	此活动表示已解决先前发现的匹配差异，发票可以继续处理。当匹配状态为“失败”的发票重新匹配成功或被人工覆盖时，可推断此活动已发生。
重要性此活动标志着差异处理子流程结束。从“发现匹配差异”到此事件的时长，是衡量处理效率的重要KPI。获取途径如果同一张发票在“发现匹配差异”事件之后发生“发票已与采购订单匹配”这一成功匹配事件，则可据此推断。采集识别“通过”状态的匹配时间戳，且该时间戳晚于“失败”状态的时间戳。事件类型 inferred
	发现匹配差异	当发票、采购订单或收货信息不一致，导致发票匹配失败时，会发生此活动。当发票匹配状态设为“失败”或“存在差异”时，系统会记录此活动。
重要性确定差异发生的时间和原因，对“匹配差异率”KPI至关重要。此活动标志着差异处理流程的开始，而该流程往往会造成明显延误。获取途径根据供应商发票（例如VendInvoiceInfoTable）中的匹配状态字段更新为“失败”来推断。系统通常也会记录失败原因。采集匹配过程中状态变为“失败”或“存在差异”时的时间戳。事件类型 inferred
	发票已拒绝	表示审批人拒绝了发票，流程因此暂停，通常会退回发票进行更正。工作流引擎会明确记录此拒绝事件。
重要性跟踪拒绝情况有助于量化返工并找出常见失败原因，例如编码错误或违反政策。这直接支持“发票拒收率”KPI。获取途径当工作流状态变为“已拒绝”或“已取消”时，工作流历史记录表（例如WorkflowTrackingStatusTable）会明确记录此事件。采集工作流历史记录中“已拒绝”状态条目的时间戳。事件类型 explicit
	发票已编码	此活动表示已为发票行项目分配总账科目。通常可根据与发票关联的总账科目分配记录的创建或最终确认来推断。
重要性编码是确保财务准确性的关键步骤。衡量发票编码所需时间，有助于发现会计审核流程中的瓶颈，并支持“数据采集至编码平均时间”这一KPI。获取途径根据与待处理供应商发票关联的会计分配表（例如AccountingDistribution）中的记录创建和验证操作推断。采集发票的会计分配保存并验证时的时间戳。事件类型 inferred
	已安排付款	已过账发票加入付款建议或付款日记账、但尚未执行付款时，会发生此活动。这是创建付款日记账行的明确操作。
重要性此步骤标志着流程从应付账款转入资金管理。分析此步骤有助于发现发票过账到启动付款之间的延误。获取途径此事件来自付款日记账（LedgerJournalTrans）中用于结清已过账供应商交易的行项目创建操作，是一项明确记录的事件。采集引用该发票的付款日记账行项目创建时间戳。事件类型 explicit
	已录入发票数据	表示发票抬头和行项目等数据录入已完成，发票可提交匹配或审批。通常可根据发票记录状态从“新建”或“已登记”变为“待处理”来推断此活动。
重要性跟踪此活动有助于衡量数据录入流程的效率，无论录入方式是人工还是自动化（OCR）。此处的延误可能影响整个流程。获取途径根据待处理供应商发票记录（例如VendInvoiceInfoTable）的状态变化推断。当所有必填字段均已填写且发票可进入下一步时，即发生此事件。采集检测待处理发票表中的状态变化，以判断数据录入是否完成。事件类型 inferred
	已解除付款冻结	表示付款暂停已解除，发票可以进入付款安排环节。当付款冻结字段被清除或状态变为未冻结时，系统会记录此活动。
重要性衡量解决付款暂停问题所需的时间。从设置冻结到解除冻结的间隔较长，说明问题处理流程效率不高。获取途径根据已过账供应商交易（VendTrans）中的付款暂停或冻结字段被清除的变化推断。变更日志可提供时间戳。采集供应商交易中的付款冻结字段被清除时的时间戳。事件类型 inferred
	已设置付款冻结	当发票被暂停付款时，会发生此活动。已过账发票记录中的付款冻结或暂停状态字段发生变化时，系统会记录此活动。
重要性付款冻结是导致逾期付款的主要原因。分析冻结的设置时间和原因，对提高按时付款表现和改善供应商关系至关重要。获取途径根据已过账供应商交易（VendTrans）中的付款暂停或冻结字段变化推断。可通过变更日志获取时间戳。采集供应商交易中的付款冻结字段被更新时的时间戳。事件类型 inferred

建议填写选填

数据提取指南

如何从Microsoft Dynamics 365获取数据

步骤

进入数据管理工作空间：登录Microsoft Dynamics 365 Finance。使用搜索栏查找并进入Data management工作空间。
创建新的导出项目：在工作空间中，选择Export磁贴以创建新的数据导出项目。为项目设置易于识别的名称，例如ProcessMind_Invoice_Processing_Export。
定义数据项目：在新项目的常规选项卡中，指定Data format。通常建议使用CSV格式。根据需要设置其他参数。
添加所需数据实体：前往Selected entities部分，点击Add entity。逐一搜索并添加以下核心实体，并为每个实体指定目标数据格式。
- VendInvoiceInfoTableEntity（供应商发票抬头）
- VendInvoiceTransEntity（供应商发票行）
- VendInvoiceRegisterHeaderEntity（发票登记日记账抬头）
- WorkflowTrackingStatusTableEntity（工作流历史记录）
- AccountingDistributionEntity（总账分配）
- VendInvoiceJour（已过账供应商发票）
- VendTrans（供应商交易，用于付款冻结）
- LedgerJournalTransVendPaymEntity（供应商付款日记账行）
- VendSettlement（供应商结算交易）
应用筛选条件：对于每个实体，都必须应用筛选条件以限制数据范围。选择实体并点击筛选。至少按Company（法人实体）和相关日期字段（如CREATEDDATETIME或INVOICEDATE）的特定日期范围进行筛选。初步分析通常可选择最近3至6个月的数据。
配置实体映射：检查每个实体的源到目标映射，确保导出包含最终事件日志所需的所有字段。此阶段通常无需进行复杂的映射更改。
运行导出作业：添加并筛选所有实体后，点击操作窗格中的Export以启动作业。您可以立即运行，也可以安排在非高峰时段运行，以尽量减少系统负载。
下载导出数据：导出作业成功完成后，前往作业历史记录，下载导出的数据包。数据包中会为每个数据实体分别提供文件。
将数据转换为事件日志：导出的文件是各实体的快照，并非整合后的事件日志。您必须使用数据库、Python脚本或Power Query等外部工具处理并合并这些文件。query部分中的概念性查询提供了完整的转换逻辑。
执行转换逻辑：将导出的CSV文件载入您选择的转换环境。运行提供的转换查询，合并所有源文件中的数据，生成单个事件日志表，每项业务活动占一行。
完成并上传：将转换后的数据导出为单个CSV文件。确保列标题符合要求，例如InvoiceNumber、ActivityName和EventTime。此文件即可上传到ProcessMind。

配置

数据实体：配置的核心是选择正确的数据实体，以覆盖发票全生命周期。关键实体包括VendInvoiceInfoTableEntity、VendInvoiceJour和WorkflowTrackingStatusTableEntity。
导出数据格式：选择CSV作为目标数据格式，便于后续转换处理。
按公司筛选：必须按您要分析的特定法人实体（公司）筛选每个数据实体，避免提取无关数据。
日期范围筛选：根据创建日期或过账日期，对所有相关实体（如VendInvoiceInfoTableEntity和VendInvoiceJour）应用一致的日期范围筛选。建议从近期数据开始，例如最近6个月，以控制数据量。
全量推送与增量导出：首次提取时执行全量推送。后续刷新可配置增量导出，仅获取上次运行后发生变化的数据，从而缩短处理时间。
前提条件：执行导出的用户必须在Dynamics 365中拥有适当的安全角色，通常包括Data management administrator或具有所需数据实体访问权限的类似角色。

查询示例 sql

/*
-- This is a conceptual query to be executed in a database or data transformation tool.
-- The tables referenced (e.g., 'VendInvoiceInfoTableEntity') are the CSV files exported from Dynamics 365.
-- Replace '[Your_Source_System_Name]' with the actual source system identifier.
*/

-- 1. Invoice Registered
SELECT
    h.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Registered' AS "ActivityName",
    h.CREATEDDATETIME AS "EventTime",
    h.CREATEDBY AS "User",
    h.INVOICEACCOUNT AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    h.INVOICEAMOUNT AS "InvoiceAmount",
    NULL AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    h.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceRegisterHeaderEntity h

UNION ALL

-- 2. Invoice Data Captured
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Data Captured' AS "ActivityName",
    i.CREATEDDATETIME AS "EventTime",
    i.CREATEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i

UNION ALL

-- 3. Invoice Coded
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Coded' AS "ActivityName",
    MIN(a.CREATEDDATETIME) AS "EventTime",
    MIN(a.CREATEDBY) AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    MIN(a.CREATEDDATETIME) AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
JOIN AccountingDistributionEntity a ON i.INVOICENUMBER = a.SOURCEDOCUMENTLINE_INVOICENUMBER
GROUP BY i.INVOICENUMBER, i.VENDORACCOUNT, i.PURCHASEORDERNUMBER, i.INVOICEAMOUNT

UNION ALL

-- 4. Invoice Matched To Purchase Order
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Matched To Purchase Order' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed'

UNION ALL

-- 5. Matching Discrepancy Found
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Found' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Discrepancy' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Failed'

UNION ALL

-- 6. Matching Discrepancy Handled (Approximation)
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Handled' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed' AND i.MODIFIEDDATETIME > i.CREATEDDATETIME

UNION ALL

-- 7. Invoice Sent For Approval, 8. Invoice Approved, 9. Invoice Rejected
SELECT
    w.CONTEXT_INVOICENUMBER AS "InvoiceNumber",
    CASE w.TRACKINGSTATUS
        WHEN 'Submitted' THEN 'Invoice Sent For Approval'
        WHEN 'Completed' THEN 'Invoice Approved'
        WHEN 'Canceled' THEN 'Invoice Rejected'
        ELSE CONCAT('Workflow: ', w.TRACKINGSTATUS)
    END AS "ActivityName",
    w.CREATEDDATETIME AS "EventTime",
    w.USERID AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    w.TRACKINGSTATUS AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    w.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM WorkflowTrackingStatusTableEntity w
JOIN VendInvoiceInfoTableEntity i ON w.CONTEXT_RECID = i.RECID AND w.CONTEXT_TABLEID = i.TABLEID
WHERE w.TRACKINGTYPE = 'Approval'

UNION ALL

-- 10. Payment Block Set
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Set' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Blocked' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'Yes'

UNION ALL

-- 11. Payment Block Released
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Released' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Open' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'No' AND v.MODIFIEDDATETIME > v.CREATEDDATETIME

UNION ALL

-- 12. Invoice Posted
SELECT
    vj.INVOICENUM AS "InvoiceNumber",
    'Invoice Posted' AS "ActivityName",
    vj.POSTINGDATE AS "EventTime",
    vj.CREATEDBY AS "User",
    vj.INVOICEACCOUNT AS "VendorNumber",
    vj.PURCHID AS "PurchaseOrderNumber",
    vj.INVOICEAMOUNT AS "InvoiceAmount",
    'Posted' AS "InvoiceStatus",
    vj.DUEDATE AS "PaymentDueDate",
    vj.POSTINGDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceJour vj

UNION ALL

-- 13. Payment Scheduled
SELECT
    l.INVOICENUMBER AS "InvoiceNumber",
    'Payment Scheduled' AS "ActivityName",
    l.CREATEDDATETIME AS "EventTime",
    l.CREATEDBY AS "User",
    l.ACCOUNTDISPLAYVALUE AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    l.DEBITAMOUNT + l.CREDITAMOUNT AS "InvoiceAmount",
    'Scheduled for Payment' AS "InvoiceStatus",
    l.DUEDATE AS "PaymentDueDate",
    l.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM LedgerJournalTransVendPaymEntity l

UNION ALL

-- 14. Payment Executed
SELECT
    vs.INVOICENUMBER AS "InvoiceNumber",
    'Payment Executed' AS "ActivityName",
    vs.TRANSDATE AS "EventTime",
    vs.CREATEDBY AS "User",
    vs.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    vs.SETTLEMENTAMOUNT AS "InvoiceAmount",
    'Paid' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    vs.TRANSDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendSettlement vs;

步骤

前提条件：配置BYOD：确保已为您的Microsoft Dynamics 365 Finance and Operations环境配置自带数据库功能。这包括设置Azure SQL Database，并配置D365将数据导出到该数据库。
确定数据实体：在D365中，前往Data management工作空间。此次提取需要导出特定数据实体。确保导出作业包含以下实体，并将目标设为您的BYOD数据库：Vendor invoice journal、Vendor invoice lines、Vendor invoice document register、Workflow tracking status table、Vendor payment journal lines、Vendor settlements和Accounting distributions。
创建并安排导出作业：在Data management工作空间中创建新的导出项目。将上述实体添加到项目中，并将目标数据格式配置为您的BYOD数据库。设置定期导出计划，例如每天或每小时运行一次，以确保BYOD中的数据保持最新。
验证Azure SQL中的数据：使用SQL Server Management Studio（SSMS）或Azure Data Studio等工具连接Azure SQL Database。确认与D365实体对应的表已创建且包含数据。
准备SQL查询：复制提供的SQL查询。此查询可直接对Azure SQL Database中由BYOD导出流程创建的表运行。
连接并执行：在已连接BYOD的SQL客户端中打开新的查询窗口，将复制的SQL查询粘贴到窗口中。
设置查询参数：找到脚本顶部的占位变量，分别将{StartDate}、{EndDate}和{CompanyCode}替换为所需的开始日期、结束日期和法人实体标识符。例如：SET @StartDate = '2023-01-01';、SET @EndDate = '2023-12-31';、SET @CompanyCode = 'USMF';。
运行查询：执行完整的SQL脚本。查询会从多个表中收集并合并数据，为每张发票生成按时间排序的事件日志。
检查结果：输出为单个表，其中包含事件日志及InvoiceNumber、ActivityName、EventTime和其他指定属性等列。请检查结果，确保数据正确且完整。
导出事件日志：将查询结果导出为CSV文件。大多数SQL客户端都内置了将结果保存到文件的功能。请确保文件采用UTF-8编码。
准备上传：上传到流程挖掘工具前，请确认CSV文件的列标题与查询中指定的完全一致（InvoiceNumber、ActivityName等）。无需进一步转换文件结构。

配置

关键数据实体：数据提取依赖于将多个D365数据实体导出到BYOD。关键实体包括：VendInvoiceJour、VendInvoiceInfoTable、WorkflowTrackingStatusTable、VendSettlement、VendInvoiceRegisterHeader、LedgerJournalTrans、AccountingDistribution和VendTrans。
日期范围筛选：提供的查询包含@StartDate和@EndDate参数。建议先使用较短的日期范围，例如3至6个月的数据，以确保查询性能。筛选条件应用于发票过账日期或创建日期。
公司代码筛选：在多公司环境中，@DataAreaId参数至关重要。请始终筛选特定法人实体（公司代码），确保数据上下文正确。
BYOD刷新频率：数据的时效性完全取决于D365导出作业的计划。若要进行近实时分析，需要较高的刷新频率（例如每小时一次），这可能影响D365和Azure的性能与成本。
前提条件：此方法需要有效的Azure订阅、已配置的Azure SQL Database，以及在D365中配置Data management工作空间所需的管理员级访问权限。

查询示例 sql

DECLARE @StartDate DATETIME = '{StartDate}';
DECLARE @EndDate DATETIME = '{EndDate}';
DECLARE @CompanyCode NVARCHAR(4) = '{CompanyCode}';

WITH BaseInvoices AS (
    SELECT
        h.INVOICEID AS InvoiceNumber,
        h.VENDORACCOUNT AS VendorNumber,
        h.PURCHID AS PurchaseOrderNumber,
        h.INVOICEAMOUNT AS InvoiceAmount,
        p.DUEDATE AS PaymentDueDate,
        h.DOCUMENTSTATUS AS InvoiceStatus,
        h.CREATEDBY AS CreatedByUser,
        h.CREATEDDATETIME AS CreatedDateTime,
        h.MODIFIEDBY AS ModifiedByUser,
        h.MODIFIEDDATETIME AS ModifiedDateTime,
        h.BLOCKED AS IsBlocked,
        h.POSTINGDATE AS PostingDate
    FROM VendInvoiceJour h
    LEFT JOIN VendTrans p ON h.INVOICEID = p.INVOICE AND h.DATAAREAID = p.DATAAREAID AND h.VENDORACCOUNT = p.ACCOUNTNUM
    WHERE h.DATAAREAID = @CompanyCode
      AND h.POSTINGDATE BETWEEN @StartDate AND @EndDate
)
-- 1. Invoice Registered
SELECT
    vpr.JOURNALID AS InvoiceNumber,
    'Invoice Registered' AS ActivityName,
    vpr.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    vpr.VENDORACCOUNT AS VendorNumber,
    NULL AS PurchaseOrderNumber,
    vpr.INVOICEAMOUNT AS InvoiceAmount,
    vpr.CREATEDBY AS User,
    'Registered' AS InvoiceStatus,
    vpr.DUEDATE AS PaymentDueDate,
    vpr.CREATEDDATETIME AS EndTime
FROM VendInvoiceRegisterHeader vpr
WHERE vpr.DATAAREAID = @CompanyCode AND vpr.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 2. Invoice Data Captured
SELECT
    viit.NUM AS InvoiceNumber,
    'Invoice Data Captured' AS ActivityName,
    viit.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.CREATEDBY AS User,
    'Pending' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.CREATEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 3. Invoice Coded
SELECT
    vil.INVOICEID AS InvoiceNumber,
    'Invoice Coded' AS ActivityName,
    MAX(ad.CREATEDDATETIME) AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    MAX(ad.CREATEDBY) AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    MAX(ad.CREATEDDATETIME) AS EndTime
FROM AccountingDistribution ad
JOIN VendInvoiceInfoLine vil ON ad.SOURCEDOCUMENTLINE = vil.RECID
JOIN BaseInvoices bij ON vil.INVOICEID = bij.InvoiceNumber
WHERE ad.DATAAREAID = @CompanyCode
GROUP BY vil.INVOICEID, bij.VendorNumber, bij.PurchaseOrderNumber, bij.InvoiceAmount, bij.InvoiceStatus, bij.PaymentDueDate

UNION ALL

-- 4. Invoice Matched To Purchase Order
SELECT
    viit.NUM AS InvoiceNumber,
    'Invoice Matched To Purchase Order' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matched' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 1 -- Enum for Passed
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 5. Matching Discrepancy Found
SELECT
    viit.NUM AS InvoiceNumber,
    'Matching Discrepancy Found' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matching Failed' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 2 -- Enum for Failed
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 6. Matching Discrepancy Handled (Inferred by successful match after a failure)
-- This is an approximation. A true 'handled' event is difficult to capture without change tracking.
-- This query assumes the modification time of the 'Passed' status represents the handling time.
SELECT
    viit.NUM AS InvoiceNumber,
    'Matching Discrepancy Handled' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matched' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 1 -- Enum for Passed
  AND viit.DISCREPANCYSTATE > 0 -- Indicates there was a discrepancy at some point
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 7, 8, 9. Workflow Events (Submitted, Approved, Rejected)
SELECT
    bij.InvoiceNumber,
    CASE wts.TRACKINGTYPE
        WHEN 1 THEN 'Invoice Sent For Approval' -- Submitted
        WHEN 5 THEN 'Invoice Approved' -- Completed/Approved
        WHEN 4 THEN 'Invoice Rejected' -- Rejection
    END AS ActivityName,
    wts.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    wts.USERID AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    wts.CREATEDDATETIME AS EndTime
FROM WorkflowTrackingStatusTable wts
JOIN VendInvoiceInfoTable viit ON wts.CONTEXTRECID = viit.RECID
JOIN BaseInvoices bij ON viit.NUM = bij.InvoiceNumber
WHERE wts.DATAAREAID = @CompanyCode AND wts.TRACKINGTYPE IN (1, 4, 5)

UNION ALL

-- 10. Payment Block Set
SELECT
    bij.InvoiceNumber,
    'Payment Block Set' AS ActivityName,
    bij.ModifiedDateTime AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.ModifiedByUser AS User,
    'Blocked' AS InvoiceStatus,
    bij.PaymentDueDate,
    bij.ModifiedDateTime AS EndTime
FROM BaseInvoices bij
WHERE bij.IsBlocked = 1 -- Enum for Yes

UNION ALL

-- 11. Payment Block Released (Inferred by modification of a previously blocked invoice)
SELECT
    bij.InvoiceNumber,
    'Payment Block Released' AS ActivityName,
    bij.ModifiedDateTime AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.ModifiedByUser AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    bij.ModifiedDateTime AS EndTime
FROM BaseInvoices bij
-- This logic assumes modification date reflects the unblocking action.
-- It requires a more sophisticated change log for true accuracy.
WHERE bij.IsBlocked = 0 AND bij.ModifiedDateTime > bij.CreatedDateTime

UNION ALL

-- 12. Invoice Posted
SELECT
    bij.InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bij.PostingDate AS EventTime, -- Or CreatedDateTime depending on business process
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.CreatedByUser AS User,
    'Posted' AS InvoiceStatus,
    bij.PaymentDueDate,
    bij.PostingDate AS EndTime
FROM BaseInvoices bij

UNION ALL

-- 13. Payment Scheduled
SELECT
    ljt.INVOICE AS InvoiceNumber,
    'Payment Scheduled' AS ActivityName,
    ljt.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    ljt.CREATEDBY AS User,
    'Payment Scheduled' AS InvoiceStatus,
    bij.PaymentDueDate,
    ljt.CREATEDDATETIME AS EndTime
FROM LedgerJournalTrans ljt
JOIN BaseInvoices bij ON ljt.INVOICE = bij.InvoiceNumber
WHERE ljt.DATAAREAID = @CompanyCode AND ljt.ACCOUNTPLUSTYPE = 2 -- Vendor type
  AND ljt.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 14. Payment Executed
SELECT
    vs.INVOICEID AS InvoiceNumber,
    'Payment Executed' AS ActivityName,
    vs.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    vs.VENDORACCOUNT AS VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    vs.CREATEDBY AS User,
    'Paid' AS InvoiceStatus,
    bij.PaymentDueDate,
    vs.CREATEDDATETIME AS EndTime
FROM VendSettlement vs
JOIN BaseInvoices bij ON vs.INVOICEID = bij.InvoiceNumber
WHERE vs.DATAAREAID = @CompanyCode AND vs.CREATEDDATETIME BETWEEN @StartDate AND @EndDate;

步骤

准备环境：确保系统已安装Python 3.6或更高版本。使用pip安装所需的库：pip install pandas requests msal。这些库用于处理数据、向API发送HTTP请求，以及处理Microsoft身份验证平台的身份验证。
在Azure Active Directory中注册应用程序：要通过身份验证访问Dynamics 365 API，您必须在组织的Azure AD环境中注册应用程序。创建新的应用注册，生成客户端密钥，并记下应用程序（客户端）ID、目录（环境）ID和客户端密钥值。
授予API权限：在Azure AD中新建的应用注册中，转到API权限。添加“Dynamics ERP”或“Dynamics 365 Business Central”权限（具体取决于您的许可证），并授予委派权限user_impersonation。管理员必须为这些权限授予管理员同意。
在Dynamics 365中创建应用程序用户：在Dynamics 365环境中，转到“系统管理”>“用户”>“用户”。创建新用户，并将Azure应用注册中的应用程序（客户端）ID填入“应用程序ID”字段。为该用户分配适当的安全角色，例如“系统管理员”，或具有所需所有数据实体读取权限的限制性更强的角色。
配置Python脚本：将提供的Python脚本复制到文件中，例如extract_invoices.py。打开文件，将顶部的占位变量替换为您的Dynamics 365环境URL、环境ID、客户端ID、客户端密钥，以及您要提取数据的公司代码（dataAreaId）。
设置日期范围：调整脚本中的START_DATE和END_DATE变量，定义数据提取的时间范围。建议先使用较短的范围（例如一个月）测试脚本并核对结果。
运行脚本：在命令行或终端中运行以下命令：python extract_invoices.py。脚本将完成身份验证，查询各项活动对应的OData端点，并处理数据。
监控运行情况：脚本会在获取各项活动的数据时向控制台输出进度信息。运行时间可能较长，具体取决于日期范围和数据量。请留意可能表明身份验证或API访问存在问题的错误信息。
检查输出文件：脚本成功运行后，会在同一目录中生成名为d365_invoice_event_log.csv的CSV文件，其中包含汇总后的事件日志。
准备上传：打开CSV文件，确认格式正确。检查是否包含并填写了所有必需的列（InvoiceNumber、ActivityName、EventTime、SourceSystem、LastDataUpdate）。现在即可将该文件上传到ProcessMind。

配置

身份验证：脚本使用OAuth 2.0客户端凭据流。您必须配置Azure AD应用注册，并授予其访问Dynamics 365的API权限。同时，必须在Dynamics 365中创建具有适当安全角色的应用程序用户。
API端点：Dynamics 365 Finance and Operations OData API的基础URL，通常格式为https://[your-d365-environment-name].operations.dynamics.com。
日期范围：脚本包含START_DATE和END_DATE变量，用于按创建日期或修改日期筛选记录，以控制数据量。首次提取建议选择3至6个月的时间范围。
公司筛选条件：必须按特定法人实体或公司筛选数据。在API查询筛选条件中指定dataAreaId即可，这是脚本的必填配置。
关键数据实体：脚本会查询多个标准Dynamics 365 OData实体，包括VendorInvoiceRegisterJournalsV2、VendorInvoiceHeaders、VendorPostedInvoices、VendorPaymentJournalLines和VendSettlements。如果流程经过大量定制，可能需要使用自定义实体。
分页：脚本使用@odata.nextLink属性自动处理API分页响应，确保获取指定时间范围内的所有记录。

查询示例 python

import os
import pandas as pd
import requests
import msal
import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration: Update these values --- #
D365_URL = class="hl-string">"https://[your-d365-environment].operations.dynamics.com"
TENANT_ID = class="hl-string">"[your-tenant-id]"
CLIENT_ID = class="hl-string">"[your-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"
COMPANY_ID = class="hl-string">"[your-company-id]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"

class=class="hl-string">"hl-comment"># --- End Configuration --- #

RESOURCE = fclass="hl-string">"{D365_URL}/"
AUTHORITY = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
API_BASE_URL = fclass="hl-string">"{D365_URL}/data"

LAST_DATA_UPDATE = datetime.datetime.now(datetime.timezone.utc).isoformat()
SOURCE_SYSTEM = class="hl-string">"Microsoft Dynamics 365"

def get_access_token():
    class="hl-string">"""Acquires an access token from Azure AD."""
    app = msal.ConfidentialClientApplication(
        CLIENT_ID,
        authority=AUTHORITY,
        client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=[fclass="hl-string">"{RESOURCE}/.default"])
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        print(class="hl-string">"Error acquiring token:", result.get(class="hl-string">"error_description"))
        return None

def fetch_all_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given D365 OData entity URL."""
    all_data = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Prefer': class="hl-string">'odata.include-annotations="*"'
    }
    
    next_link = entity_url
    while next_link:
        try:
            response = requests.get(next_link, headers=headers)
            response.raise_for_status()
            data = response.json()
            all_data.extend(data[class="hl-string">'value'])
            next_link = data.get(class="hl-string">'@odata.nextLink')
            time.sleep(0.1) class=class="hl-string">"hl-comment"># Small delay to respect API limits
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            print(fclass="hl-string">"Response body: {response.text}")
            next_link = None
    return all_data

def main():
    token = get_access_token()
    if not token:
        return

    all_events = []
    cross_company_filter = fclass="hl-string">"cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}'"

    print(class="hl-string">"Fetching 'Invoice Registered' events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorInvoiceRegisterJournalsV2?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=JournalNumber,CreatedDateTime,InvoiceNumber,VendorAccount,CreatedBy,JournalTotalCredit"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'),
            class="hl-string">'ActivityName': class="hl-string">'Invoice Registered',
            class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'),
            class="hl-string">'User': item.get(class="hl-string">'CreatedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'),
            class="hl-string">'InvoiceAmount': item.get(class="hl-string">'JournalTotalCredit'),
            class="hl-string">'PurchaseOrderNumber': None, class="hl-string">'InvoiceStatus': None, class="hl-string">'PaymentDueDate': None
        })

    print(class="hl-string">"Fetching invoice header events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorInvoiceHeaders?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=InvoiceNumber,CreatedDateTime,ModifiedDateTime,VendorAccount,PurchaseOrderNumber,InvoiceAmount,InvoiceMatchingStatus,LastMatchingValidationDate,InvoiceLastActionDate,Worker,InvoiceLastAction,DueDate,VendorInvoiceHeaderStatus"
    data = fetch_all_data(token, entity_url)
    for item in data:
        class=class="hl-string">"hl-comment"># Invoice Data Captured (approximated by creation of the header)
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Data Captured', class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
            class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
        })
        class=class="hl-string">"hl-comment"># Invoice Coded (inferred by modification after creation)
        if item.get(class="hl-string">'ModifiedDateTime') != item.get(class="hl-string">'CreatedDateTime'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Coded', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        class=class="hl-string">"hl-comment"># Matching activities
        if item.get(class="hl-string">'InvoiceMatchingStatus') == class="hl-string">'Passed' and item.get(class="hl-string">'LastMatchingValidationDate'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Matched To Purchase Order', class="hl-string">'EventTime': item.get(class="hl-string">'LastMatchingValidationDate'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        if item.get(class="hl-string">'InvoiceMatchingStatus') == class="hl-string">'Failed' and item.get(class="hl-string">'LastMatchingValidationDate'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Matching Discrepancy Found', class="hl-string">'EventTime': item.get(class="hl-string">'LastMatchingValidationDate'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
            class=class="hl-string">"hl-comment"># Matching Discrepancy Handled (inferred if invoice is now matched or posted after a discrepancy)
            if item.get(class="hl-string">'VendorInvoiceHeaderStatus') in [class="hl-string">'Matched', class="hl-string">'Posted'] and item.get(class="hl-string">'ModifiedDateTime') > item.get(class="hl-string">'LastMatchingValidationDate'):
                all_events.append({
                    class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Matching Discrepancy Handled', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                    class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                    class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
                })

    print(class="hl-string">"Fetching workflow events...")
    class=class="hl-string">"hl-comment"># This is a simplified workflow extraction. A real scenario might need to query WorkflowTrackingWorkItem and join.
    entity_url = fclass="hl-string">"{API_BASE_URL}/WorkflowTrackingStatusTables?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$filter=contains(Context,'VendInvoice')&$select=ContextRecId,CreatedDateTime,TrackingStatus,User,Context"
    class=class="hl-string">"hl-comment"># Note: Linking ContextRecId back to an InvoiceNumber requires another lookup, which is complex. 
    class=class="hl-string">"hl-comment"># A placeholder is used here. For a real implementation, you must resolve this link.
    class=class="hl-string">"hl-comment"># For this script, we will not generate workflow events to avoid complexity.

    print(class="hl-string">"Fetching posted invoice events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorPostedInvoices?{cross_company_filter} and PostedDateTime ge {START_DATE} and PostedDateTime le {END_DATE}&$select=InvoiceNumber,PostedDateTime,VendorAccount,PurchaseOrderNumber,InvoiceAmount,DueDate,Block,ModifiedDateTime,ModifiedBy"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Posted', class="hl-string">'EventTime': item.get(class="hl-string">'PostedDateTime'), class="hl-string">'User': item.get(class="hl-string">'ModifiedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
            class="hl-string">'InvoiceStatus': class="hl-string">'Posted', class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
        })
        if item.get(class="hl-string">'Block') not in [None, class="hl-string">'No', class="hl-string">'Free']:
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Block Set', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'ModifiedBy'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': class="hl-string">'Posted (Blocked)', class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        class=class="hl-string">"hl-comment"># Payment Block Released is difficult to capture without audit logs. This is an approximation.

    print(class="hl-string">"Fetching payment schedule events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorPaymentJournalLines?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=InvoiceNumber,CreatedDateTime,CreatedBy,JournalBatchNumber,VendorAccountNumber,Amount"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled', class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'), class="hl-string">'User': item.get(class="hl-string">'CreatedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccountNumber'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'Amount'), class="hl-string">'PurchaseOrderNumber': None,
            class="hl-string">'InvoiceStatus': class="hl-string">'Payment Scheduled', class="hl-string">'PaymentDueDate': None
        })

    print(class="hl-string">"Fetching payment execution events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendSettlements?{cross_company_filter} and SettlementDate ge {START_DATE} and SettlementDate le {END_DATE}&$select=InvoiceNumber,SettlementDate,VendorAccountNumber,SettlementAmount"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Executed', class="hl-string">'EventTime': item.get(class="hl-string">'SettlementDate'), class="hl-string">'User': None, class=class="hl-string">"hl-comment"># User not available in this entity
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccountNumber'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'SettlementAmount'), class="hl-string">'PurchaseOrderNumber': None,
            class="hl-string">'InvoiceStatus': class="hl-string">'Paid', class="hl-string">'PaymentDueDate': None
        })

    if not all_events:
        print(class="hl-string">"No events found for the specified criteria.")
        return

    print(class="hl-string">"Processing and saving data...")
    df = pd.DataFrame(all_events)
    df[class="hl-string">'SourceSystem'] = SOURCE_SYSTEM
    df[class="hl-string">'LastDataUpdate'] = LAST_DATA_UPDATE
    df[class="hl-string">'EndTime'] = df[class="hl-string">'EventTime']
    df.dropna(subset=[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)

    output_filename = class="hl-string">'d365_invoice_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"Event log successfully created: {output_filename}")

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

步骤

确认您可以访问Microsoft Power Platform、Power Query、目标Dynamics 365环境，以及包含供应商发票、发票行、分类账分配、采购订单、收货、工作流、付款冻结、过账和付款日记账数据的源实体或表。由于Dynamics 365的实施配置各不相同，请在映射工作簿中记录实际源对象。确认环境专属对象之前，可使用[您的发票登记表]和[您的工作流事件表]等占位符。
在Power Platform中，于用于ProcessMind提取的工作空间或环境中创建数据流。选择适用于Dynamics 365部署方式的连接器，并使用[您的连接字符串或环境引用]和[您的凭据]配置连接。请勿将凭据写入查询文本。
在Power Query中添加提取所需的源实体。至少应映射发票登记、发票抬头、发票行、分类账分配、采购订单或收货匹配结果、工作流历史、付款冻结历史、过账交易、付款建议或付款日记账行，以及付款结算数据源。发布数据流前，请在映射工作簿中记录实际实体名称和列名。
使用[开始日期]和[结束日期]为每个数据源设置约定的提取时间范围。按[您的公司或法人实体筛选条件]、相关单据类型和供应商发票记录进行筛选。保留足够精度的源时间戳，以便对事件排序。EventTime应使用源系统时间戳，而非刷新时间戳。
创建规范化事件查询，每条记录对应一个明确记录或明确推导的业务事件。查询必须将以下14项活动作为ActivityName值输出：Invoice Registered、Invoice Data Captured、Invoice Coded、Invoice Matched To Purchase Order、Matching Discrepancy Found、Matching Discrepancy Handled、Invoice Sent For Approval、Invoice Approved、Invoice Rejected、Payment Block Set、Payment Block Released、Invoice Posted、Payment Scheduled和Payment Executed。ProcessMind会按原样读取事件日志，因此请勿依赖ProcessMind推断缺失的活动。
使用InvoiceNumber将每个事件数据源关联到发票案例。如果数据源使用内部发票键，请先将该键关联到发票抬头或登记记录，再提取业务InvoiceNumber。尽可能保留VendorNumber、PurchaseOrderNumber、InvoiceAmount、User、InvoiceStatus、PaymentDueDate和EndTime。每个源列都应采用有文档记录的环境专属映射。
应用各事件对应的条件。发票登记要求存在供应商发票登记记录。数据采集要求记录从新建或已登记状态转为可处理状态。编码要求创建或最终确定分类账分配。匹配成功要求结果为Passed。匹配失败要求结果为Failed或Discrepancy。差异处理要求之后成功重新匹配，或存在有记录的人工覆盖。工作流提交、批准和拒绝要求存在明确的工作流事件。付款冻结事件要求冻结状态发生变化。过账、付款安排和付款执行要求存在明确的过账、付款日记账或结算记录。
统一数据类型和值。InvoiceNumber和ActivityName设为文本，EventTime和EndTime设为日期时间，InvoiceAmount设为数值类型，PaymentDueDate则根据源数据设为日期或日期时间。SourceSystem设为Microsoft Dynamics 365。每次刷新时，所有行的LastDataUpdate均使用数据流刷新时间戳或源数据提取时间戳，并保持一致。
删除缺少InvoiceNumber或EventTime的行，除非经批准的例外处理流程为其分配了稳定的案例标识符。只有在重复源记录代表同一事件时才去重，并尽可能使用源事件标识符。请勿合并不同的重复批准、匹配尝试、冻结或付款操作。
将规范化输出加载到已配置的目标位置，例如Power Platform数据目标、湖仓、数据仓库，或当前实施支持的文件目标。导出或提供包含平面表的结果，每行对应一个事件，且包含必需列InvoiceNumber、ActivityName、EventTime、SourceSystem和LastDataUpdate，以及可用的推荐属性。
刷新数据流、检查输出并完成验证。确认存在对应源记录的14个ActivityName值均已输出，时间戳排序正确，且案例标识符为InvoiceNumber。将验证后的表导出为ProcessMind支持的格式，例如CSV或已配置连接器的输出，然后上传到ProcessMind或连接数据源，并将InvoiceNumber设为案例标识符、ActivityName设为活动、EventTime设为事件时间戳。

配置

**源数据映射：**将所有环境专属占位符替换为实际Dynamics 365实体、表、视图和列。请勿假设Finance、Supply Chain Management、Dataverse、定制配置或集成中的名称完全相同。
**必需输出列：**每条有效事件记录都必须填充InvoiceNumber、ActivityName、EventTime、SourceSystem和LastDataUpdate。
**推荐输出列：**如果源数据提供了VendorNumber、PurchaseOrderNumber、InvoiceAmount、User、InvoiceStatus、PaymentDueDate和EndTime，应将其纳入输出。
**日期范围：**先提取3至6个月的历史数据。验证查询性能和事件完整性后，再扩大范围。在支持的情况下，使用包含开始时间、不包含结束时间的边界，避免刷新间出现重复事件。
**公司或法人实体筛选条件：**在所有源实体中一致应用[您的公司或法人实体筛选条件]。如果InvoiceNumber并非全局唯一，请确保关联时不会混合不同法人实体的发票。
**单据类型筛选条件：**仅提取供应商发票及流程涉及的单据类型。请根据系统配置设置筛选条件，不要假定Dynamics 365使用某个特定值。
**状态筛选条件：**使用系统配置中对应新建、已登记、可处理、Passed、Failed、Discrepancy、已批准、已拒绝、已冻结和已解除冻结的值。状态标签可能因版本、语言、定制配置或集成而异。
**事件定义：**为每项必需活动提取一条明确记录。只有在指定的源状态变更或源记录能够提供可靠证据时，才可推导活动。ProcessMind不会根据其他行推断事件。
**时间戳选择：**优先使用源事件时间戳。根据事件定义，使用创建、修改、工作流操作、过账、日记账或结算时间戳。请勿将LastDataUpdate用作EventTime。
**刷新元数据：**每次数据流运行时，所有记录的LastDataUpdate都应使用一致的刷新时间戳。如果目标支持增量刷新，请将刷新水位线与EventTime分开保存。
**增量刷新：**使用稳定的源水位线，例如源修改时间戳或事件标识符，并设置回溯时间范围，以捕获延迟到达的工作流、匹配和付款事件。尽可能使用源事件标识符去重。
**性能：**在关联前筛选源实体，只选择必需列，避免不受限制地展开发票行，并将大型数据源分阶段处理。在连接器支持的情况下使用查询折叠。对较大的日期范围进行分区或增量刷新。
**关联键：**只有当InvoiceNumber在所选公司或法人实体范围内唯一时，才将其用作关联键。否则，请使用环境专属复合键，并在最终输出中提取InvoiceNumber。
**前提条件：**所需权限包括访问Dynamics 365数据、工作流历史、过账和付款数据，以及Power Platform数据流和目标位置。所需许可证和连接器是否可用，取决于租户、Dynamics 365部署方式和所选目标位置。
**安全性：**使用托管连接或经批准的凭据，遵循最小权限原则，避免在Power Query表达式或导出文件中嵌入机密。
**币种和金额：**保留源币种；如财务分析需要，可添加环境专属币种列。除非已定义获批的汇率规则，否则不要转换金额。

查询示例 sql

let
    StartDate = #datetime(2025, 1, 1, 0, 0, 0),
    EndDate = #datetime(2025, 7, 1, 0, 0, 0),
    CompanyFilter = "[Your company or legal entity filter]",
    SourceSystemValue = "Microsoft Dynamics 365",
    RefreshTime = DateTimeZone.RemoveZone(DateTimeZone.UtcNow()),

    InvoiceRegister = Table.SelectRows(#"[Your invoice register table]", each [Company] = CompanyFilter and [CreatedDateTime] >= StartDate and [CreatedDateTime] < EndDate),
    InvoiceHeader = Table.SelectRows(#"[Your invoice header table]", each [Company] = CompanyFilter),
    InvoiceLines = Table.SelectRows(#"[Your invoice line table]", each [Company] = CompanyFilter),
    LedgerDistributions = Table.SelectRows(#"[Your ledger distribution table]", each [Company] = CompanyFilter and [CreatedDateTime] >= StartDate and [CreatedDateTime] < EndDate),
    MatchingResults = Table.SelectRows(#"[Your invoice matching result table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),
    WorkflowEvents = Table.SelectRows(#"[Your workflow event table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),
    PaymentHoldHistory = Table.SelectRows(#"[Your payment hold history table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),
    PostingEvents = Table.SelectRows(#"[Your invoice posting table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),
    PaymentScheduleEvents = Table.SelectRows(#"[Your payment proposal or payment journal line table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),
    PaymentExecutionEvents = Table.SelectRows(#"[Your payment settlement table]", each [Company] = CompanyFilter and [EventDateTime] >= StartDate and [EventDateTime] < EndDate),

    HeaderColumns = Table.SelectColumns(InvoiceHeader, {"InvoiceNumber", "VendorNumber", "PurchaseOrderNumber", "InvoiceAmount", "InvoiceStatus", "PaymentDueDate"}),
    HeaderDistinct = Table.Distinct(HeaderColumns, {"InvoiceNumber"}),

    RegisterEvents = Table.RenameColumns(Table.SelectColumns(InvoiceRegister, {"InvoiceNumber", "CreatedDateTime", "User"}), {{"CreatedDateTime", "EventTime"}}),
    RegisterWithActivity = Table.AddColumn(RegisterEvents, "ActivityName", each "Invoice Registered", type text),

    DataCapturedBase = Table.SelectRows(InvoiceHeader, each ([PreviousStatus] = "new" or [PreviousStatus] = "registered") and [InvoiceStatus] = "ready for processing"),
    DataCapturedEvents = Table.RenameColumns(Table.SelectColumns(DataCapturedBase, {"InvoiceNumber", "StatusChangedDateTime", "User"}), {{"StatusChangedDateTime", "EventTime"}}),
    DataCapturedWithActivity = Table.AddColumn(DataCapturedEvents, "ActivityName", each "Invoice Data Captured", type text),

    CodedBase = Table.SelectRows(LedgerDistributions, each [DistributionStatus] = "Finalized" or [DistributionStatus] = "Created"),
    CodedEvents = Table.RenameColumns(Table.SelectColumns(CodedBase, {"InvoiceNumber", "EventDateTime", "User"}), {}),
    CodedWithActivity = Table.AddColumn(CodedEvents, "ActivityName", each "Invoice Coded", type text),

    MatchedBase = Table.SelectRows(MatchingResults, each [MatchingStatus] = "Passed"),
    MatchedEvents = Table.SelectColumns(MatchedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    MatchedWithActivity = Table.AddColumn(MatchedEvents, "ActivityName", each "Invoice Matched To Purchase Order", type text),

    DiscrepancyBase = Table.SelectRows(MatchingResults, each [MatchingStatus] = "Failed" or [MatchingStatus] = "Discrepancy"),
    DiscrepancyEvents = Table.SelectColumns(DiscrepancyBase, {"InvoiceNumber", "EventDateTime", "User"}),
    DiscrepancyWithActivity = Table.AddColumn(DiscrepancyEvents, "ActivityName", each "Matching Discrepancy Found", type text),

    HandledBase = Table.SelectRows(MatchingResults, each ([MatchingStatus] = "Passed" and ([PreviousMatchingStatus] = "Failed" or [PreviousMatchingStatus] = "Discrepancy")) or [ManualOverride] = true),
    HandledEvents = Table.SelectColumns(HandledBase, {"InvoiceNumber", "EventDateTime", "User"}),
    HandledWithActivity = Table.AddColumn(HandledEvents, "ActivityName", each "Matching Discrepancy Handled", type text),

    SubmittedBase = Table.SelectRows(WorkflowEvents, each [WorkflowEventType] = "Submitted"),
    SubmittedEvents = Table.SelectColumns(SubmittedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    SubmittedWithActivity = Table.AddColumn(SubmittedEvents, "ActivityName", each "Invoice Sent For Approval", type text),

    ApprovedBase = Table.SelectRows(WorkflowEvents, each [WorkflowEventType] = "Approved"),
    ApprovedEvents = Table.SelectColumns(ApprovedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    ApprovedWithActivity = Table.AddColumn(ApprovedEvents, "ActivityName", each "Invoice Approved", type text),

    RejectedBase = Table.SelectRows(WorkflowEvents, each [WorkflowEventType] = "Rejected"),
    RejectedEvents = Table.SelectColumns(RejectedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    RejectedWithActivity = Table.AddColumn(RejectedEvents, "ActivityName", each "Invoice Rejected", type text),

    BlockSetBase = Table.SelectRows(PaymentHoldHistory, each [HoldStatus] = "Blocked" and ([PreviousHoldStatus] <> "Blocked" or [PreviousHoldStatus] = null)),
    BlockSetEvents = Table.SelectColumns(BlockSetBase, {"InvoiceNumber", "EventDateTime", "User"}),
    BlockSetWithActivity = Table.AddColumn(BlockSetEvents, "ActivityName", each "Payment Block Set", type text),

    BlockReleasedBase = Table.SelectRows(PaymentHoldHistory, each ([HoldStatus] = "Unblocked" or [HoldStatus] = "") and [PreviousHoldStatus] = "Blocked"),
    BlockReleasedEvents = Table.SelectColumns(BlockReleasedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    BlockReleasedWithActivity = Table.AddColumn(BlockReleasedEvents, "ActivityName", each "Payment Block Released", type text),

    PostedBase = Table.SelectRows(PostingEvents, each [PostingStatus] = "Posted"),
    PostedEvents = Table.SelectColumns(PostedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    PostedWithActivity = Table.AddColumn(PostedEvents, "ActivityName", each "Invoice Posted", type text),

    ScheduledBase = Table.SelectRows(PaymentScheduleEvents, each [PaymentJournalLineStatus] = "Created" or [PaymentJournalLineStatus] = "Included"),
    ScheduledEvents = Table.SelectColumns(ScheduledBase, {"InvoiceNumber", "EventDateTime", "User"}),
    ScheduledWithActivity = Table.AddColumn(ScheduledEvents, "ActivityName", each "Payment Scheduled", type text),

    ExecutedBase = Table.SelectRows(PaymentExecutionEvents, each [SettlementStatus] = "Posted" or [PaymentStatus] = "Executed"),
    ExecutedEvents = Table.SelectColumns(ExecutedBase, {"InvoiceNumber", "EventDateTime", "User"}),
    ExecutedWithActivity = Table.AddColumn(ExecutedEvents, "ActivityName", each "Payment Executed", type text),

    AllEvents = Table.Combine({RegisterWithActivity, DataCapturedWithActivity, CodedWithActivity, MatchedWithActivity, DiscrepancyWithActivity, HandledWithActivity, SubmittedWithActivity, ApprovedWithActivity, RejectedWithActivity, BlockSetWithActivity, BlockReleasedWithActivity, PostedWithActivity, ScheduledWithActivity, ExecutedWithActivity}),
    EventsWithHeader = Table.NestedJoin(AllEvents, {"InvoiceNumber"}, HeaderDistinct, {"InvoiceNumber"}, "Header", JoinKind.LeftOuter),
    ExpandedHeader = Table.ExpandTableColumn(EventsWithHeader, "Header", {"VendorNumber", "PurchaseOrderNumber", "InvoiceAmount", "InvoiceStatus", "PaymentDueDate"}, {"VendorNumber", "PurchaseOrderNumber", "InvoiceAmount", "InvoiceStatus", "PaymentDueDate"}),
    WithSourceSystem = Table.AddColumn(ExpandedHeader, "SourceSystem", each SourceSystemValue, type text),
    WithLastDataUpdate = Table.AddColumn(WithSourceSystem, "LastDataUpdate", each RefreshTime, type datetime),
    WithEndTime = Table.AddColumn(WithLastDataUpdate, "EndTime", each [EventTime], type datetime),
    SelectedColumns = Table.SelectColumns(WithEndTime, {"InvoiceNumber", "ActivityName", "EventTime", "SourceSystem", "LastDataUpdate", "VendorNumber", "PurchaseOrderNumber", "InvoiceAmount", "User", "InvoiceStatus", "PaymentDueDate", "EndTime"}),
    TypedColumns = Table.TransformColumnTypes(SelectedColumns, {{"InvoiceNumber", type text}, {"ActivityName", type text}, {"EventTime", type datetime}, {"SourceSystem", type text}, {"LastDataUpdate", type datetime}, {"VendorNumber", type text}, {"PurchaseOrderNumber", type text}, {"InvoiceAmount", type number}, {"User", type text}, {"InvoiceStatus", type text}, {"PaymentDueDate", type date}, {"EndTime", type datetime}}),
    ValidEvents = Table.SelectRows(TypedColumns, each [InvoiceNumber] <> null and [InvoiceNumber] <> "" and [EventTime] <> null),
    SortedEvents = Table.Sort(ValidEvents, {{"InvoiceNumber", Order.Ascending}, {"EventTime", Order.Ascending}, {"ActivityName", Order.Ascending}})
in
    SortedEvents

准备好开始了吗？

使用此模板快速开启流程挖掘之旅，发现洞察，让发票处理更快、更准确。立即着手优化运营。

返回改进指南

下载模板

立即优化采购到付款 - 发票处理流程

发现低效环节，将发票周期时间缩短30%或以上。

开始免费试用

无需信用卡，几分钟即可开始。

您的采购到付款 - 发票处理数据模板

您的采购到付款 - 发票处理数据模板

采购到付款 - 发票处理属性

采购到付款 - 发票处理活动

数据提取指南

Dynamics 365数据管理框架导出

步骤

配置

查询示例 sql

通过自带数据库（BYOD）直接运行SQL查询

步骤

配置

查询示例 sql

使用Dynamics 365 OData REST API的Python脚本

步骤

配置

查询示例 python

Microsoft Power Platform数据流提取

步骤

配置

查询示例 sql

准备好开始了吗？

立即优化采购到付款 - 发票处理流程