> 持续流程改进 > 采购到付款－发票处理 > 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	根据约定的付款条款，发票应完成付款的日期。
说明 Payment Due Date根据发票日期和供应商付款条款计算，代表合同约定的付款截止日期。该日期对于衡量财务绩效和管理供应商关系至关重要。将其与实际“Payment Executed”日期进行比较，可计算“On-Time Payment Rate”KPI。分析这些数据有助于识别导致延迟付款的系统性问题，避免损害供应商关系或错失提前付款折扣。为什么重要这是衡量按时付款绩效的基准，也是评估财务健康状况和供应商关系的关键KPI。获取位置该日期通常由系统根据发票日期和付款条款计算，并存储在供应商交易表中，例如VendTrans。示例 2023-05-152023-06-302023-07-20
	供应商编号 VendorNumber	提交发票的供应商或供货商的唯一标识。
说明供应商编号是主数据中用于唯一标识供应商的代码。它将发票交易关联到特定供应商，支持以供应商为中心的分析。此属性对于按供应商细分流程数据至关重要。它可以帮助回答“哪些供应商的审批时间最长？”或“某些供应商是否更容易出现匹配差异？”等问题。按供应商分析绩效，有助于发现供应商协作和流程改进机会。为什么重要支持按供应商筛选和开展根因分析，帮助识别与特定供应商相关的绩效问题或模式。获取位置通常位于供应商发票表的抬头中，例如VendInvoiceInfoTable，并关联VendTable中的主要供应商主数据。示例 V-1001V-2050V-8342
	发票状态 InvoiceStatus	提取数据时发票所处的当前状态。
说明 Invoice Status表示发票在其生命周期中的当前状态，例如“Pending Approval”“Approved”“Posted”或“Paid”，用于概览发票处理进度。该属性主要用于创建运营仪表板，展示当前工作量和积压情况。它帮助管理人员了解流程各阶段的发票数量，从而支持资源分配和运营监控，也是“Invoice Processing Throughput & Status”仪表板的关键数据。为什么重要提供所有发票当前状态的视图，对于运营监控、工作量管理和识别当前瓶颈至关重要。获取位置通常从主发票表中的状态字段获取，例如VendInvoiceInfoTable。示例待审批已过账已付款
	发票金额 InvoiceAmount	发票的货币总金额。
说明此属性表示供应商发票中列明的应付总额，是每个案例的基础财务数据。发票金额广泛用于细分和分析。它可以揭示高价值发票是否遵循更严格的审批流程，或处理时间是否更长。仪表板还可使用该属性按金额分析发票量，并汇总计算所分析范围内的总支出。为什么重要支持按财务价值开展分析，帮助您优先改进高价值发票流程，并了解成本驱动因素。获取位置位于发票抬头表中，例如VendInvoiceInfoTable的InvoiceAmount或Amount字段。示例 1500.00250.7512500.50
	用户 User	执行该活动人员的用户ID或姓名。
说明此属性用于标识负责执行特定流程步骤的员工或系统用户。对于自动化步骤，该值可能是系统账户或服务账户。按用户分析流程，有助于了解工作量分配、识别培训需求，以及比较个人或团队之间的绩效。它对于分析返工循环至关重要，例如工作在不同用户之间来回传递的情况，也有助于了解不同用户完成同一任务时产生差异的原因。为什么重要支持按用户或团队分析绩效和工作量，并帮助识别自动化程度和返工来源。获取位置数据来源于交易表或工作流历史表中的用户ID字段，例如createdby或modifiedby字段。相关信息也可能位于工作流跟踪表中。示例 j.doea.smithAX_Admin
	结束时间 EndTime	表示特定活动或事件完成时间的精确时间戳。
说明 End Time记录活动结束的时间。结合Start Time（EventTime）后，可精确计算每个步骤的完成时长。该属性对于计算每项活动的“ProcessingTime”至关重要，也是绩效分析的关键指标。它帮助您准确定位流程中耗时最长的步骤，从而开展有针对性的改进。例如，可用于测量手动编码或审批步骤的确切时长。为什么重要支持直接计算活动处理时长，是识别流程中最耗时步骤的基础。获取位置与StartTime类似，该字段来源于各种日期和时间字段。对于某些活动，可能取下一项活动的StartTime；在其他情况下，可能取特定的“completed on”时间戳。示例 2023-04-15T09:15:20Z2023-05-20T18:00:00Z2023-06-01T11:05:45Z
	采购订单编号 PurchaseOrderNumber	与发票相关的采购订单（PO）标识。
说明采购订单编号将发票与原始采购单据关联起来。对于发票、采购订单和收货信息之间的二方或三方匹配流程，这一属性至关重要。该属性支持分析采购订单发票与非采购订单发票，因为两者通常遵循不同的流程路径。它是了解匹配流程有效性、调查匹配差异，以及衡量采购订单发票直通处理率的关键。为什么重要它可以区分采购订单发票和非采购订单发票。两者是流程差异的主要来源，对匹配分析至关重要。获取位置位于发票抬头表中，例如VendInvoiceInfoTable的PurchId字段。示例 PO-001234PO-005678PO-009101
	付款冻结原因 PaymentBlockReason	说明发票为何被设置付款冻结的原因代码。
说明付款冻结会阻止发票付款。该属性记录冻结原因，通常是用户选择的标准化代码，例如“Disputed charges”或“Waiting for goods receipt”。该属性对于“Payment Block Frequency & Duration”仪表板至关重要。分析付款冻结最常见的原因，有助于识别P2P流程中的潜在问题。解决这些根因可以显著减少付款延迟并改善供应商关系。为什么重要说明付款被有意延迟的原因，帮助识别并解决导致付款冻结的上游问题。获取位置通常是供应商交易记录或发票记录中的字段，与付款冻结状态相关。可在VendTrans等表中查找。示例质量争议等待贷项通知单手动冻结
	付款条件 PaymentTerms	与供应商约定的付款条款，例如“Net 30”或“2% 10, Net 30”。
说明 Payment Terms定义向供应商付款时约定的条件，包括付款期限和可能享有的折扣。该信息通常来源于供应商主数据或采购订单。该属性为Payment Due Date和On-Time Payment Rate KPI提供重要背景。分析特定付款条款是否与延迟付款相关，可以发现运营方面的挑战，也有助于开展财务规划和有效管理营运资金。为什么重要为付款到期日提供背景信息，并帮助分析付款时点和折扣获取率带来的财务影响。获取位置该信息存储在供应商主数据表（VendTable）中，并复制到VendInvoiceInfoTable等交易表。示例净额30天净额60天2%10天内付款，净额30天
	公司代码 CompanyCode	组织内负责处理发票的法人实体或公司的标识符。
说明 Company Code表示对发票承担财务责任的具体法人实体。在多公司组织中，这是进行财务分段的关键数据。该属性支持按不同法人实体筛选或比较流程分析结果。它可以揭示流程绩效、政策或瓶颈是否集中在集团内的特定公司，从而支持本地化流程改进。为什么重要支持按法人实体细分流程分析，对于大型多公司组织至关重要。获取位置这是Dynamics 365大多数财务表中的标准字段，通常称为DataAreaId。示例 USMFDEMFGBSI
	发票币种 InvoiceCurrency	发票金额使用的币种代码，例如USD、EUR。
说明发票币种用于指定发票金额所采用的币种。对于与不同国家供应商合作的跨国组织，这一属性非常重要。此属性为发票金额提供必要背景，可用于筛选和报表，尤其适用于全球化运营。它确保财务金额得到正确解读，并支持按币种分析或转换为标准报表币种。为什么重要为财务金额提供必要背景，支持准确解读和分析不同地区的发票金额。获取位置位于发票抬头表中，与发票金额相邻，例如VendInvoiceInfoTable的CurrencyCode字段。示例 USDEURGBP
	差异类型 MatchingDiscrepancyType	对发票与采购订单匹配过程中发现的不一致类型进行分类。
说明在二方或三方匹配过程中出现差异时，该属性说明问题性质，例如“Price Mismatch”“Quantity Mismatch”或“Missing Goods Receipt”。该数据是“Matching Discrepancy Resolution Time”仪表板的核心。分析不同差异类型的发生频率，可以识别匹配失败的根因。例如，频繁的价格不一致可能表明主数据过时，而数量不一致可能说明运输环节存在问题。为什么重要对匹配失败进行分类，支持根因分析，降低差异率并改善直通式处理。获取位置可能存储在专用的匹配日志或例外日志表中，也可能需要根据状态消息或工作流备注推导。示例价格不匹配数量不匹配缺少收货确认
	总周期时间 TotalCycleTime	处理单张发票的端到端总时长，从接收到付款完成。
说明 Total Cycle Time衡量发票在整个流程中耗费的时间。对于指定发票编号，它等于第一项活动（例如“Invoice Registered”）与最后一项活动（例如“Payment Executed”）之间的时间差。这是衡量整体流程效率的主要KPI，也是“Invoice End-to-End Cycle Time”仪表板使用的指标。分析该指标的趋势，并按供应商或金额等属性进行细分，有助于发现整体改进机会。为什么重要这是衡量整体流程速度和效率的核心高层KPI，概括每张发票的处理绩效。获取位置该指标在流程挖掘工具的案例层级或数据准备阶段计算，等于每个CaseId的首个和最后一个事件时间戳之差。示例 P15DP22DT5H30MP7D
	拒绝原因 RejectionReason	审批人拒绝发票时提供的原因。
说明发票在审批工作流中被拒绝时，审批人通常需要提供原因。该属性记录这一说明，可以是预定义代码，也可以是自由文本。这是“Invoice Rejection Analysis”仪表板的关键属性，可提供了解拒绝根因所需的定性信息，例如“Incorrect PO Number”“Duplicate Invoice”或“Price Mismatch”。分析这些原因有助于确定降低拒绝率的优先行动。为什么重要直接说明发票被拒绝的原因，支持采取有针对性的措施，减少返工并提高一次通过率。获取位置通常位于工作流处理表的备注或历史日志中。示例数量不正确重复发票超出审批限额
	是否按时付款 IsOnTimePayment	用于标识发票是否在到期日当天或之前完成付款的计算标记。
说明该布尔属性通过比较“Payment Executed”活动的时间戳与“Payment Due Date”推导得出。如果付款日期小于或等于到期日，则值为true，否则为false。该属性直接支持“On-Time Payment Rate”KPI和“Payment Terms Compliance”仪表板。它为每张发票提供清晰的二元结果，简化分析，便于筛选延迟付款，并按供应商、部门或国家调查其根因。为什么重要直接衡量付款条款的合规情况，简化On-Time Payment Rate KPI及相关分析的计算。获取位置该属性在数据转换层计算，逻辑为：（“Payment Executed”活动的时间戳）<=（Payment Due Date）。示例 truefalse
	是否自动化 IsAutomated	用于标识活动由系统自动执行还是由人工用户完成。
说明该布尔属性（true/false）用于区分系统执行的事件和人工用户任务。例如，发票匹配步骤可能由系统自动完成，而差异处理通常需要人工操作。分析该属性有助于了解流程自动化水平，衡量自动化项目成效，识别仍然存在的人工瓶颈，并发现进一步自动化的机会，从而提升效率、减少错误。为什么重要帮助衡量流程自动化水平，识别人工瓶颈和提升效率的机会。获取位置通常根据“User”属性推导。如果用户是已知的系统账户或服务账户，则该标记设为true。示例 truefalse
	部门 Department	发票成本所分配到的成本中心或部门。
说明 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
	发票数据已采集	表示发票数据录入已完成，包括抬头和行项目明细，之后即可提交匹配或审批。通常可根据发票记录状态从“新建”或“已登记”变为“准备处理”来推断。
为什么重要跟踪此活动有助于衡量数据录入流程的效率，无论录入方式是手工操作还是自动化OCR。此处的延误可能会影响整个流程。获取位置根据待处理供应商发票记录的状态变化推断，例如VendInvoiceInfoTable。当所有必填字段完成填充且发票准备进入下一步时，即发生此事件。采集检测待处理发票表中的状态变化，以判断数据录入是否完成。事件类型 inferred
	已安排付款	当已过账发票被纳入付款提案或付款日志，但付款尚未执行时，会发生此活动。这是创建付款日志行的明确操作。
为什么重要此活动标志着流程从应付账款转入资金管理。分析此步骤，有助于发现发票过账到启动付款之间的延误。获取位置这是从付款日志LedgerJournalTrans中创建行项目的操作捕获的明确事件，该行项目用于结算已过账的供应商交易。采集引用该发票的付款日志行创建时间戳。事件类型 explicit
	已解除付款冻结	表示已移除付款挂起，发票可以进入付款计划。系统会在付款冻结字段被清除或变为未冻结状态时捕获此活动。
为什么重要衡量解决付款挂起原因所需的时间。从设置冻结到解除冻结之间耗时较长，说明问题解决流程效率不高。获取位置根据已过账供应商交易记录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作为目标数据格式，便于后续转换处理。
按公司筛选：必须按正在分析的特定法人实体（Company）筛选每个数据实体，避免提取无关数据。
日期范围筛选：根据创建日期或过账日期，在所有相关实体（如VendInvoiceInfoTableEntity和VendInvoiceJour）中应用一致的日期范围筛选。建议先选择最近6个月，以控制数据量。
全量推送与增量推送：首次提取时执行全量推送。后续刷新时，可配置增量导出，仅提取上次运行后发生变化的数据，从而缩短处理时间。
前提条件：执行导出的用户必须在Dynamics 365中拥有适当的安全角色，通常包括Data management administrator，或具备访问所需数据实体权限的类似角色。

a 示例查询 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环境配置Bring Your Own Database功能。这包括设置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等）。无需进一步调整文件结构。

配置

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

a 示例查询 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租户中注册应用。创建新的App Registration，生成客户端密钥，并记录Application（client）ID、Directory（tenant）ID和客户端密钥值。
授予API权限：在Azure AD中新建的App Registration中进入API permissions。添加Dynamics ERP或Dynamics 365 Business Central权限（取决于您的许可证），并授予user_impersonation委托权限。管理员必须为这些权限授予管理员同意。
在Dynamics 365中创建应用程序用户：在Dynamics 365环境中进入System administration > Users > Users。创建新用户，将Azure App Registration中的Application（client）ID填入Application ID字段。为该用户分配适当的安全角色，例如System administrator，或具备所需数据实体读取权限的受限角色。
配置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 App Registration，并授予其访问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分页响应，确保获取指定期间内的全部记录。

a 示例查询 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实施方式各不相同，请在映射工作簿中记录实际源对象。在确认环境特定对象前，可使用[Your invoice register table]和[Your workflow event table]等占位符。
在用于ProcessMind提取的工作区或环境中创建Power Platform数据流。选择适合Dynamics 365部署方式的连接器，并使用[Your connection string or environment reference]和[Your credentials]配置连接。请勿将凭据写入查询文本。
在Power Query中添加提取所需的源实体。至少映射发票登记、发票抬头、发票行、总账分配、采购订单或收货匹配结果、工作流历史、付款冻结历史、过账交易、付款建议或付款日志行，以及付款结算来源。在发布数据流前，将实际实体名称和列记录到映射工作簿中。
使用[Start date]和[End date]对每个源应用约定的提取窗口。按[Your company or legal entity filter]、相关单据类型和供应商发票记录进行筛选。保留足够精度的源时间戳，以便对事件排序。EventTime应使用源系统时间戳，而不是刷新时间戳。
创建标准化事件查询，每行对应一项明确记录或明确推导的业务事件。查询必须将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作为ActivityName值输出。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数据目标、lakehouse、数据仓库或实施方案支持的文件目标。将结果导出或公开为平面表，每行一个事件，包含必需列InvoiceNumber、ActivityName、EventTime、SourceSystem和LastDataUpdate，并在可用时包含建议属性。
刷新数据流，检查输出并完成验证。确认存在对应源记录时，14个ActivityName值均已出现；确认时间戳排序正确，案例标识符为InvoiceNumber。将验证后的表导出为ProcessMind支持的格式，例如CSV或已配置的连接器输出，并将其上传到ProcessMind或连接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个月的历史数据开始。验证查询性能和事件完整性后，再扩大范围。在支持的情况下，使用包含开始日期、不包含结束日期的边界，避免刷新之间产生重复事件。
**公司或法人实体筛选：**在所有源实体中一致应用[Your company or legal entity filter]。当InvoiceNumber不是全局唯一时，确保连接不会合并不同法人实体的发票。
**单据类型筛选：**将提取范围限制为供应商发票及流程使用的单据类型。应根据系统配置此筛选，不要假设Dynamics 365使用某个固定值。
**状态筛选：**使用系统配置的新建、已登记、可处理、Passed、Failed、Discrepancy、已批准、已拒绝、已冻结和已解除冻结值。状态标签可能因版本、语言、自定义项或集成而不同。
**事件语义：**为每项必需活动提取明确的一行。只有在指定的源状态转换或源记录能够提供可靠证据时，才允许推导活动。ProcessMind不会根据其他行推断事件。
**时间戳选择：**优先使用源事件时间戳。根据事件定义，使用创建、修改、工作流操作、过账、日志或结算时间戳。不要将LastDataUpdate用作EventTime。
**刷新元数据：**将LastDataUpdate填充为数据流运行时统一的刷新时间戳。如果目标支持增量刷新，请将刷新水位线与EventTime分开保留。
**增量刷新：**使用稳定的源水位线，例如源修改时间戳或事件标识符，并加入回溯时间段，以捕获延迟到达的工作流、匹配和付款事件。在可用时使用源事件标识符去重。
**性能：**在连接前筛选源实体，仅选择所需列，避免不受限制地展开发票行，并单独暂存大型源。连接器支持时使用查询折叠。对较大的日期范围进行分区或增量刷新。
**连接键：**仅当InvoiceNumber在所选公司或法人实体内唯一时才使用它。否则使用环境特定的复合键，并将InvoiceNumber映射到最终输出。
**前提条件：**所需权限包括访问Dynamics 365数据、工作流历史、过账和付款数据，以及Power Platform数据流和目标。所需许可证和连接器可用性取决于租户、Dynamics 365部署方式和所选目标。
**安全性：**使用托管连接或经批准的凭据，遵循最小权限原则，避免在Power Query表达式或导出文件中嵌入机密信息。
**币种和金额：**保留源系统币种；如果财务分析需要，请添加环境特定的币种列。除非已定义并批准汇率规则，否则不要转换金额。

a 示例查询 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数据管理框架导出

步骤

配置

a 示例查询 sql

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

步骤

配置

a 示例查询 sql

使用Dynamics 365 OData REST API的Python脚本

步骤

配置

a 示例查询 python

Microsoft Power Platform数据流提取

步骤

配置

a 示例查询 sql

准备开始了吗？

立即开始优化您的采购到付款发票处理流程

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

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

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

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

数据提取指南

Dynamics 365数据管理框架导出

步骤

配置

a 示例查询 sql

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

步骤

配置

a 示例查询 sql

使用Dynamics 365 OData REST API的Python脚本

步骤

配置

a 示例查询 python

Microsoft Power Platform数据流提取

步骤

配置

a 示例查询 sql

准备开始了吗？

立即开始优化您的采购到付款发票处理流程

我们重视您的隐私