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

您的应付账款发票处理数据模板

Microsoft Dynamics 365

← 返回改进指南

应付账款发票处理属性(21) 应付账款发票处理活动(14) 数据提取指南(4)

您的应付账款发票处理数据模板

此模板将引导您收集分析和优化应付账款发票处理流程所需的关键数据，其中列出了重要属性和核心活动，并提供了实用的数据提取指南。使用此模板有助于您构建完整的事件日志，深入了解流程表现。

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

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

下载模板

应付账款发票处理属性

以下是建议纳入事件日志的数据字段，可用于全面分析应付账款发票处理流程并深入了解流程情况。

3 必填 6 建议填写 12 选填

	名称	说明
	发票 Invoice	每份供应商发票单据的唯一标识符。
说明发票是主要的案例标识符，可关联从收到发票到最终付款的所有活动。借助它，您可以端到端分析每张发票在应付账款流程中的处理过程。分析时，首先按此标识符对事件分组，以还原每张发票的流程。它还可用于计算案例级KPI，例如总周期时间，并识别特定发票处理过程中的变体和瓶颈。重要性这是追踪发票完整生命周期的关键，也是应付账款流程挖掘分析的基础。获取途径通常取自Dynamics 365 Finance的“供应商发票”页面或相关数据实体中的供应商发票编号。示例 INV-00125475000921DE-8832-2023
	开始时间 EventTime	表示活动或事件发生时间的时间戳。
说明此属性记录每项活动的日期和时间，是按时间顺序排列事件和计算时长的必要数据，也是事件日志的时间基础。分析时，开始时间用于计算所有时间相关指标，包括活动间的周期时间、等待时间和案例总时长。它对于识别瓶颈和衡量流程绩效是否达到SLA要求至关重要。重要性此时间戳对于正确排列事件顺序以及计算周期时间、瓶颈等绩效指标至关重要。获取途径数据取自Dynamics 365交易记录的创建或修改日期字段、工作流历史日志或过账日期字段。示例 2023-04-15T09:00:12Z2023-04-18T14:35:00Z2023-05-02T11:21:45Z
	活动 ActivityName	已执行的业务流程步骤名称。
说明此属性记录发票在某个时间点发生的具体操作或事件，例如“发票已登记”或“发票已批准”。这些活动构成发现的流程图中的节点。分析活动的顺序和频率是流程挖掘的基础。这有助于呈现流程路径，识别常见和少见的路径（变体），并找出导致延迟或返工的活动。重要性活动定义了流程中发生的“事项”，可据此构建流程图并分析流程路径和变体。获取途径此数据通常来自Dynamics 365应付账款模块中的状态变更、工作流历史日志或单据过账记录。示例发票已登记发票已批准差异已解决已完成付款
	付款条件 PaymentTerms	与供应商约定的发票付款条件。
说明此属性定义供应商发票的付款条件，例如“Net 30”（30天内付款）或“2/10 Net 30”（10天内付款可享受2%的折扣，否则须在30天内付款）。按付款条件分析，有助于了解不同协议对付款时效和现金流的影响。这也是识别提前付款折扣机会的基础，还可用于细分合规分析，判断某些付款条件是否更难满足。重要性明确付款期限和折扣机会，直接影响现金流管理和成本节约。获取途径可在供应商主数据中找到，也可能在采购订单或发票抬头中指定。数据存储在“PaymTermId”等字段中。示例 Net 30Net 602/10 Net 30
	供应商 VendorName	提交发票的供应商名称。
说明此属性标识与发票关联的供应商。供应商数据通常包括名称、ID和类别等信息。按供应商分析应付账款流程，可以发现哪些供应商经常提交有问题的发票（例如差异频发），哪些供应商有特殊付款条件，以及哪些供应商的发票处理周期较长。利用这些洞察，可以改善供应商关系与协作。重要性按供应商细分流程，识别差异频发或付款延迟等特定问题。获取途径通过供应商发票抬头关联，通常依据供应商账号从“VendTable”数据实体获取。示例 Contoso有限公司Fabrikam公司Northwind贸易公司
	发票到期日 InvoiceDueDate	根据付款条件计算出的发票付款截止日期。
说明此日期表示发票付款期限，按时付款可避免罚款并遵守供应商协议。通常根据发票日期和指定的付款条件计算。此属性是“付款条件合规性”分析的必要数据。将“付款执行”日期与“发票到期日”进行比较，分析即可自动标记逾期付款、计算合规率，并帮助您安排付款优先级，维护良好的供应商关系。重要性对于衡量按时付款表现、维护供应商关系和避免逾期付款费用至关重要。获取途径根据发票日期和付款条件计算得出。数据存储在供应商交易表（如“VendTrans”）的“DueDate”等字段中。示例 2023-05-152023-06-302023-07-01
	发票金额 InvoiceAmount	发票的货币总额。
说明表示供应商发票的应付总额，是每个案例的重要财务指标。此属性支持应付账款流程的财务分析。您可以据此优先处理高金额发票，按金额区间分析审批时间，并了解付款延迟或提前付款折扣带来的财务影响。重要性提供财务背景信息，便于按金额分析流程表现，例如识别高金额发票是否采用不同的处理方式。获取途径位于供应商发票抬头中，通常对应“VendInvoiceInfoTable”中的“InvoiceAmount”等字段。示例 1500.75250.0012345.50
	用户 UserName	执行该活动的用户。
说明标识负责完成流程步骤的具体用户，例如登记或审批发票的用户。此属性通常关联Dynamics 365中的用户系统ID。按用户分析绩效有助于发现培训需求、表现突出的员工以及工作量分布情况。对于关注审批时间和资源效率的仪表板，这项数据必不可少，可帮助管理者识别造成瓶颈的用户或团队。重要性将工作关联到具体人员，便于分析工作量和绩效，并发现培训机会。获取途径通常取自交易记录中的“创建者”或“修改者”字段，也可从Dynamics 365工作流历史日志中获取。示例 j.doea.smithr.williams
	采购订单编号 PurchaseOrderNumber	与发票关联的采购订单唯一标识符。
说明此属性将发票关联到对应的采购订单（PO）。发票可能关联采购订单，也可能不关联采购订单。分析此属性有助于区分基于采购订单和非采购订单的发票处理流程，因为两者通常遵循不同的工作流。它对于衡量采购订单匹配效率，以及识别发票详情与采购订单不一致时出现的问题至关重要。重要性区分遵循不同流程的采购订单发票和非采购订单发票，是分析匹配效率的必要数据。获取途径可在供应商发票抬头或行项目中找到，通常对应“VendInvoiceInfoTable”中的“PurchId”字段。示例 PO-000432PO-000511
	公司代码 CompanyCode	处理发票的法律实体标识符。
说明此属性标识组织内负责处理发票的具体公司或法律实体。在多公司架构中，这是重要的组织维度。按公司代码分析应付账款流程，可以比较不同业务单元或法律实体的绩效，帮助发现效率较低、返工率较高或流程变体不同的实体，从而找到标准化和共享最佳实践的机会。重要性支持比较组织内不同法律实体或业务单元的绩效和流程。获取途径 Dynamics 365几乎所有交易表中的标准字段，通常名为“DataAreaId”。示例 USMFDEMFGBSI
	最近数据更新时间 LastDataUpdate	源系统最近一次刷新数据的时间戳。
说明此属性表示事件日志最近一次从Microsoft Dynamics 365更新数据的时间，可帮助您了解分析数据的新旧程度。对于仪表板和持续监控，此时间戳有助于用户确认当前查看的是否为最新流程数据。它能让用户了解数据的时效性，也有助于监控数据管道的运行状况。重要性显示数据的时效性，帮助用户依据分析结果及时、准确地制定业务决策。获取途径这是数据提取和加载（ETL）工具在数据导入时生成并写入的元数据字段。示例 2023-06-01T02:00:00Z2023-06-02T02:00:00Z
	发票状态 InvoiceStatus	发票当前的处理状态。
说明此属性反映发票在流程中的最近状态，例如“处理中”“已批准”“已付款”或“已取消”，呈现发票生命周期中的当前进度。流程挖掘会根据活动还原流程路径，而最终状态可用于验证数据，也可用于创建业务仪表板，汇总所有未结发票的当前状态。例如，您可以筛选出当前“已批准”但尚未“已付款”的发票。重要性概览发票当前状态，便于筛选数据并创建基于状态的仪表板。获取途径通常根据应付账款模块中的单据状态或工作流状态字段得出。示例处理中已批准已付款已取消
	审批人 ApproverName	在特定步骤批准或拒绝发票的用户。
说明此属性标识工作流中负责审批决策的人员。多级审批的发票在不同步骤可能由不同人员审批。此属性是“发票审批周期时间分析”仪表板的必要数据。按审批人细分分析，可以发现因工作量或其他因素造成流程瓶颈的人员，并据此采取有针对性的措施，缩短审批周期。重要性支持深入分析审批流程，帮助识别个人或团队层面的瓶颈。获取途径从记录每个审批步骤完成者的工作流历史日志（“WorkflowTrackingStatusTable”）中提取。示例 David ChenMaria GarciaAP_Manager_Group
	差异原因 DiscrepancyReason	发票差异的原因代码或说明。
说明发票被挂起或发现差异时，此属性记录具体原因，例如“价格不符”“数量差异”或“缺少收货记录”。此属性是“差异解决与返工”分析的关键数据。按原因对返工分类，有助于找出流程效率低下的根本原因。例如，如果“价格不符”最常见，说明采购方与供应商之间的数据需要更好地对齐。重要性揭示返工的根本原因，便于有针对性地改进流程，减少异常和人工干预。获取途径数据可能存储在应付账款模块的发票挂起表、工作流备注或专用差异记录字段中。示例价格不符数量差异采购订单无效
	折扣截止日 EarlyPaymentDiscountDate	符合提前付款折扣条件的发票付款截止日期。
说明此属性指定获得供应商折扣的最晚付款日期。该日期根据发票日期和付款条件中的折扣期限计算得出（例如“2/10 Net 30”中的“10”）。此日期是“提前付款折扣状态”分析的主要依据。将“付款安排”或“付款执行”日期与此期限进行比较，系统即可判断是否成功获得折扣，并提供清晰的财务优化指标。重要性明确获得成本节约的目标日期，是安排付款优先级和提升效率的重要依据。获取途径系统根据付款条件和发票日期计算得出。数据存储在“VendTrans”中的“CashDiscDate”等字段中。示例 2023-04-252023-05-102023-06-15
	提前付款折扣金额 EarlyPaymentDiscountAmount	提前支付发票时可能获得的折扣金额。
说明此属性显示供应商根据付款条件提供的提前付款折扣金额。这项财务数据是“提前付款折扣状态”仪表板的关键内容。它可以量化已获得和错失的折扣金额，为提高发票审批和付款安排效率提供明确的财务依据。重要性量化流程效率带来的财务机会，直接体现流程绩效与成本节约之间的关系。获取途径根据发票金额和付款条件计算得出。此值可在“VendTrans”等表或相关现金折扣字段中获取。示例 30.015.00246.91
	收货单编号 GoodsReceiptNumber	与发票相关的收货单据标识符。
说明此属性将发票关联到货物或服务的收货记录，这是进行三方匹配（采购订单、收货记录和发票）的必要数据。它对于分析流程的匹配效率至关重要。若“收货记录匹配”环节出现延迟或失败，可通过此标识符追溯到原始收货单据，进而发现采购或收货环节中影响应付账款的问题。重要性便于分析三方匹配效率，并帮助定位货物接收流程中的问题。获取途径通过采购订单行项目关联，通常可在装箱单日记账（“VendPackingSlipJour”）或相关表中找到。示例 GRN-00981GRN-01024
	是否自动执行 IsAutomated	标记活动是否由系统自动执行。
说明此布尔属性用于区分人工用户执行的活动和系统自动执行的活动，例如自动过账或匹配发票。分析此属性有助于衡量应付账款流程的自动化程度，计算直通式处理（STP）率，并找出适合未来自动化的人工活动，从而降低成本和处理时间。重要性帮助衡量直通式处理率，并发现提升自动化程度和效率的机会。获取途径通过检查活动关联的用户是否为系统用户或批处理用户（例如“Admin”“BatchUser”）得出。示例 truefalse
	是否返工 IsRework	用于标识发票是否经历过返工的计算标记。
说明如果发票流程包含返工活动，例如“差异已解决”，或首次失败后再次执行“发票数据已验证”，此布尔标记就会设为true。该值根据整个案例计算得出。此属性直接支持“差异返工率”KPI。借助它，您可以轻松筛选并汇总需要额外人工处理的发票，量化数据质量问题或流程异常带来的影响。重要性直接衡量返工，便于量化和分析流程异常及效率低下问题。获取途径这不是源系统字段，而是在流程挖掘工具中通过检查案例是否出现特定返工活动计算得出。示例 truefalse
	是否逾期付款 IsLatePayment	用于标记付款是否晚于到期日的计算标记。
说明如果“付款执行”活动发生在“发票到期日”之后，此布尔属性就会设为true，清晰标记该案例是否未遵守付款条件。此属性简化了“付款条件合规率”KPI的计算。您可以快速筛选并在仪表板中查看逾期付款的数量和金额，还可以针对这些案例分析根本原因，了解延迟原因。重要性清晰标记未遵守付款条件的付款，便于分析并计算按时付款KPI。获取途径这不是源系统字段，而是在流程挖掘工具中通过比较“付款执行”活动的时间戳与“InvoiceDueDate”属性计算得出。示例 truefalse
	源系统 SourceSystem	提取数据的系统。
说明此属性标识事件数据的来源，在此场景中通常为“Microsoft Dynamics 365”。当合并多个系统的数据（例如OCR扫描工具和D365）时，这一属性尤为重要。在流程挖掘分析中，它有助于追踪数据来源、排查问题并了解流程所涉及的技术环境。您还可以按特定系统筛选事件。重要性提供数据来源的关键信息，有助于验证数据并开展多系统流程分析。获取途径这通常是在数据提取和转换过程中添加的静态值（“Microsoft Dynamics 365”）。示例 Microsoft Dynamics 365 FinanceD365 F&OAX2012

必填建议填写选填

应付账款发票处理活动

以下是建议记录在事件日志中的关键流程步骤和重要里程碑，可用于准确发现流程并分析流程表现。

5 建议填写 9 选填

	活动	说明
	发票已批准	表示工作流中的最终审批，授权将发票过账以供付款。这是发票进入付款阶段前的关键里程碑。
重要性这标志着审批周期结束。从“已提交审批”到此活动的时间是关键绩效指标，可用于发现审批瓶颈和耗时过长的工作流。获取途径当工作流状态更新为“已完成”或“已批准”时，工作流历史日志（WorkflowTrackingStatusTable）中会记录此明确事件。采集从工作流跟踪历史中提取该发票的“已批准”或“已完成”事件。事件类型 explicit
	发票已提交审批	发票正式提交至工作流，由授权人员审核和审批。这标志着审批周期开始。
重要性这是衡量发票审批周期时间的关键里程碑。它有助于区分数据录入和匹配耗时与审批延迟。获取途径这是D365工作流历史日志（WorkflowTrackingStatusTable）中针对特定发票单据记录的明确事件，并会记录提交时间戳。采集从工作流跟踪历史中提取该发票的“已提交”事件。事件类型 explicit
	发票已登记	表示系统中首次创建发票记录，可通过人工录入、OCR扫描或电子数据交换（EDI）完成。这是发票处理生命周期的起点。
重要性这是流程的主要开始事件。分析从此时到付款的时间，可得出整体发票周期时间，这是关键绩效指标之一。获取途径可根据待处理供应商发票表或发票日记账表中发票抬头记录的创建时间戳（CreatedDateTime字段）推断，例如VendInvoiceInfoTable。采集使用发票抬头记录的创建时间戳。事件类型 inferred
	发票已过账	已批准的发票正式记入总账，形成财务负债。这是一项关键且通常不可撤销的会计交易。
重要性过账是重要里程碑，意味着发票符合付款条件。它确认所有验证和审批步骤均已完成，且负债已确认。获取途径这是明确记录的交易。过账日期和时间会记录在供应商发票日记账（VendInvoiceJour）及相关总账分录（GeneralJournalEntry）中。采集使用VendInvoiceJour或GeneralJournalEntry表中的过账时间戳。事件类型 explicit
	已完成付款	通过过账付款日记账正式完成付款。此交易用于结清已过账发票所形成的负债。
重要性这是流程的主要结束事件之一，可用于计算付款条件遵守情况、识别逾期付款并衡量端到端总周期时间。获取途径付款日记账过账时会记录此明确事件。结算详情存储在供应商交易表（VendTrans）中，并关联付款与发票。采集使用VendTrans中付款结算记录的交易日期。事件类型 explicit
	付款已清算	公司已执行的付款经银行对账确认已到账。这是付款完成的最终财务确认。
重要性此活动最终确认了现金流出。分析付款执行到清算之间的时间，对资金管理和现金管理十分重要。获取途径此信息来自银行对账模块。当与付款对应的银行对账单行在D365中完成匹配并过账时，可推断此事件。采集需要将银行交易数据（BankStmtISOAccountStatement）关联回原始付款。事件类型 inferred
	发现差异	发票未通过验证，或与采购订单、收货记录不匹配，需要人工处理。通常会对发票设置冻结标记或特定状态。
重要性此活动会启动返工循环。分析其发生频率和原因，对于发现流程效率问题、数据质量问题或供应商问题至关重要。获取途径此事件可能会明确记录在发票历史中，也可以根据发票因匹配差异原因代码而被设为“暂停处理”推断。请查找发票抬头中的状态变更。采集记录发票状态设为“暂停处理”或差异标记被设置时的时间戳。事件类型 inferred
	发票已取消	发票过账后因更正错误等原因被作废或取消。这是流程的另一种特殊结束情况。
重要性跟踪取消情况有助于了解流程质量和错误率。取消频率过高可能表明上游流程存在系统性问题。获取途径这是明确记录的事件。D365会创建冲销或贷项交易，并将其关联至原始发票日记账。冲销的过账日期即为取消日期。采集确定与原始发票关联的冲销交易的过账日期。事件类型 explicit
	发票已拒绝	审批人通过工作流拒绝发票，通常会将其退回发起人进行更正或补充说明。这会启动返工循环。
重要性跟踪拒绝情况有助于找出审批延误和返工的原因，也能揭示发票编码问题、违反政策或文档不完整等情况。获取途径审批人在工作流历史（WorkflowTrackingStatusTable）中选择“拒绝”操作时，会记录此明确事件。采集从工作流跟踪历史中提取该发票的“已拒绝”事件。事件类型 explicit
	发票数据已验证	表示系统或用户在匹配或审批前，对采集的发票数据进行完整性和准确性初步检查。此步骤可以是系统自动验证，也可以是人工审核。
重要性跟踪此活动有助于发现数据质量不佳造成的延误。如果验证失败率较高或耗时较长，可能说明数据采集流程存在问题，例如OCR识别准确率不足。获取途径这通常是推断得出的事件。可根据发票状态从“新建”或“草稿”变为“已验证”或“待匹配”等状态的时间戳推断，也可使用提交至工作流前的最后修改时间。采集记录表示验证成功的状态变更时间戳，或提交至工作流前的更新事件时间戳。事件类型 inferred
	差异已解决	发现差异后解除冻结或更正发票的操作。此时发票已准备好重新提交匹配或审批。
重要性此活动会结束返工循环。解决差异所需的时间是衡量异常处理效率的关键指标。获取途径可根据发票“暂停处理”状态解除的时间，或发票被拒后重新提交至工作流的时间戳推断。采集记录解除冻结或发票被拒后重新提交时的时间戳。事件类型 inferred
	已匹配收货记录	在三方匹配中，此活动通过与收货单交叉核对，确认发票所列商品或服务已收到。此步骤用于核实实物交付与发票是否一致。
重要性跟踪此活动有助于分析三方匹配流程的效率，并发现因收货信息缺失或错误造成的延误。获取途径与采购订单匹配类似，可根据发票状态更新推断此事件，确认其已成功与产品收货日记账（装箱单）匹配。采集查找表示三方匹配成功的状态变更或标记。事件类型 inferred
	已匹配采购订单	将发票与一个或多个采购订单关联，以核对数量、价格和条款。这是基于采购订单的发票必须经过的关键验证步骤。
重要性此活动对于衡量首次匹配成功率和发现匹配流程中的瓶颈至关重要。匹配失败往往会导致差异处理循环。获取途径当发票记录中的匹配状态更新为“已匹配”，或成功保存行级匹配详情时，可以推断此事件。相关信息通常存储在供应商发票行相关表中。采集识别发票抬头或行项目中与采购订单匹配成功相关的状态变更。事件类型 inferred
	已安排付款	已过账的发票被选中并加入付款建议或付款日记账，安排在未来某次付款批次中支付。这表示付款意向已确定。
重要性此活动对于现金流预测和分析付款条件遵守情况至关重要，也有助于确认是否考虑并安排了提前付款折扣。获取途径可根据包含该发票的付款日记账行（LedgerJournalTrans）的创建情况推断。日记账行上的交易日期表示计划付款日期。采集使用引用该发票的付款日记账行的创建日期。事件类型 inferred

建议填写选填

数据提取指南

如何从Microsoft Dynamics 365获取数据

步骤

访问数据管理工作空间：登录Microsoft Dynamics 365 Finance and Operations。前往主仪表板，搜索或选择数据管理工作空间。
新建导出项目：在数据管理工作空间中，选择导出磁贴。点击新建创建导出项目，并填写易于识别的名称（例如AP_Invoice_Event_Log_Export）和说明。
添加所需数据实体：在项目详情中，点击添加实体。搜索并添加以下标准数据实体。您可能需要逐个添加：
- 供应商发票抬头（VendInvoiceInfoTable）
- 已过账供应商发票（VendInvoiceJour）
- 供应商交易（VendTrans）
- 供应商结算交易（VendSettlement）
- 工作流跟踪状态（WorkflowTrackingStatusTable）
- 付款日记账行（LedgerJournalTrans）
- 银行交易（BankAccountTrans）
设置导出格式：为每个已添加的实体指定目标数据格式。建议使用CSV，因为格式简单，且兼容大多数数据转换工具。首次运行时，请将“跳过暂存”选项设为“否”，以便筛选和检查数据。
应用筛选条件和日期范围：选择每个实体并点击筛选。请务必按您要分析的法人实体（公司）进行筛选。对主要日期字段（例如供应商发票抬头中的创建日期和时间）设置日期范围，将数据量控制在合理范围内，例如最近6个月。
配置导出作业：保存项目配置。点击操作窗格中的导出按钮即可手动运行导出。若需定期提取，可使用创建定期数据作业选项安排自动导出（例如每周一次）。
**下载导出文件：**作业完成后，前往作业历史记录下载导出包。该ZIP文件包含每个数据实体对应的CSV文件。
合并并转换数据：解压下载的文件包。使用Power BI（Power Query）、Python脚本或数据库（例如SQL Server）等外部工具加载各个CSV文件。按照下方查询部分提供的转换逻辑合并文件，并将数据透视为所需的事件日志格式（每项活动一行）。
**生成最终事件日志文件：**应用转换逻辑后，将结果表导出为单个CSV文件。请确保列标题与所需属性一致：Invoice、ActivityName、EventTime、UserName、VendorName、InvoiceAmount、InvoiceDueDate、PaymentTerms、PurchaseOrderNumber。
**上传至流程挖掘工具：**最终CSV文件现已准备就绪，可上传至您的流程挖掘应用。

配置

核心数据实体：提取过程依赖一组标准实体。最重要的实体包括供应商发票抬头（核心发票数据）、已过账供应商发票（过账数据）、工作流跟踪状态（审批事件）和供应商交易（付款信息）。
导出格式：将CSV用作目标数据格式。它体积小，且受到各类数据转换工具的广泛支持。
增量导出与完整导出：首次分析时，请导出指定历史时段的全部数据。持续监控时，可配置定期作业。数据管理框架支持增量导出，但需要仔细配置实体的更改跟踪。
日期范围筛选：请始终设置日期范围筛选，以限制数据集大小。通常可提取3至6个月的数据，既能支持有效分析，也不会给系统造成过大负担。请按供应商发票抬头实体的createdDateTime字段筛选。
公司（法人实体）筛选：必须按公司（DataAreaId）进行筛选。除非多个法人实体采用标准化流程，否则一次分析多个实体的数据可能较为复杂。
安全与权限：执行导出的用户需要具备可访问数据管理工作空间并能读取所有指定数据实体的安全角色。通常需要分配数据管理管理员角色或经过相应配置的自定义角色。

查询示例 sql

-- This is a conceptual SQL query representing the logic to be applied in an external tool (e.g., Power BI, Python, SQL DB)
-- to transform the exported D365 CSV files into a single event log.
-- Replace '[Your Company]' with the legal entity ID you are analyzing.

-- Step 1: Define CTEs (Common Table Expressions) to represent your loaded CSV files.

WITH VendInvoiceInfoTable AS (
    -- Represents the 'Vendor invoice headers' CSV file
    SELECT * FROM [dbo].[VendInvoiceInfoTable_Export]
),
VendInvoiceJour AS (
    -- Represents the 'Vendor posted invoices' CSV file
    SELECT * FROM [dbo].[VendInvoiceJour_Export]
),
VendTrans AS (
    -- Represents the 'Vendor transactions' CSV file
    SELECT * FROM [dbo].[VendTrans_Export]
),
VendSettlement AS (
    -- Represents the 'Vendor settlement transactions' CSV file
    SELECT * FROM [dbo].[VendSettlement_Export]
),
WorkflowTrackingStatusTable AS (
    -- Represents the 'Workflow tracking status' CSV file
    SELECT * FROM [dbo].[WorkflowTrackingStatusTable_Export]
),
LedgerJournalTrans AS (
    -- Represents the 'Payment journal lines' CSV file
    SELECT * FROM [dbo].[LedgerJournalTrans_Export]
),
BankAccountTrans AS (
    -- Represents the 'Bank transactions' CSV file
    SELECT * FROM [dbo].[BankAccountTrans_Export]
),
-- Step 2: Extract each activity as a separate SELECT statement and UNION them together.

FinalEventLog AS (
    -- 1. Invoice Registered
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Registered' AS "ActivityName",
        i.CREATEDDATETIME AS "EventTime",
        i.CREATEDBY AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceInfoTable i
    WHERE i.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 2. Invoice Data Validated (approximated by workflow start)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Data Validated' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 3 & 4. Purchase Order & Goods Receipt Matched (approximated by posting for PO invoices)
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Purchase Order Matched' AS "ActivityName",
        vj.INVOICEDATE AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.PURCHID IS NOT NULL AND vj.PURCHID <> '' AND vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 5. Discrepancy Identified (approximated by a rejection event)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Identified' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 6. Invoice Submitted For Approval
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Submitted For Approval' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 7. Invoice Approved
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Approved' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGSTATUS = 'Completed' AND w.TRACKINGTYPE = 'Approval' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Rejected' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 9. Discrepancy Resolved (approximated by resubmission after rejection)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Resolved' AS "ActivityName",
        w_submit.CREATEDDATETIME AS "EventTime",
        w_submit.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w_submit
    JOIN VendInvoiceInfoTable i ON w_submit.CONTEXTRECID = i.RECID
    WHERE w_submit.TRACKINGTYPE = 'Submission' AND w_submit.DATACOMPANY = '[Your Company]'
      AND EXISTS (
          SELECT 1 FROM WorkflowTrackingStatusTable w_reject
          WHERE w_reject.CONTEXTRECID = w_submit.CONTEXTRECID
            AND w_reject.TRACKINGTYPE = 'Rejection'
            AND w_reject.CREATEDDATETIME < w_submit.CREATEDDATETIME
      )

    UNION ALL

    -- 10. Invoice Posted
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Posted' AS "ActivityName",
        vj.CREATEDDATETIME AS "EventTime",
        vj.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 11. Payment Scheduled
    SELECT
        ljt.INVOICENUM AS "Invoice",
        'Payment Scheduled' AS "ActivityName",
        ljt.CREATEDDATETIME AS "EventTime",
        ljt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM LedgerJournalTrans ljt
    JOIN VendInvoiceJour vj ON ljt.INVOICENUM = vj.INVOICENUM AND ljt.DATACOMPANY = vj.DATACOMPANY
    WHERE ljt.POSTED <> 'Yes' AND ljt.INVOICENUM <> '' AND ljt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 12. Payment Executed
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Executed' AS "ActivityName",
        vt.TRANSDATE AS "EventTime",
        vt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendTrans vt
    JOIN VendSettlement vs ON vt.RECID = vs.OFFSETRECID
    JOIN VendTrans vt_inv ON vs.TRANSRECID = vt_inv.RECID
    JOIN VendInvoiceJour vj ON vt_inv.VOUCHER = vj.LEDGERVOUCHER AND vt_inv.DATACOMPANY = vj.DATACOMPANY
    WHERE vt.TRANSTYPE = 'Payment' AND vt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 13. Payment Cleared
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Cleared' AS "ActivityName",
        bt.CLEAREDDATE AS "EventTime",
        bt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM BankAccountTrans bt
    JOIN VendTrans vt ON bt.VOUCHER = vt.VOUCHER AND bt.DATACOMPANY = vt.DATACOMPANY
    JOIN VendInvoiceJour vj ON vt.INVOICE = vj.INVOICENUM AND vt.DATACOMPANY = vj.DATACOMPANY
    WHERE bt.CLEAREDDATE IS NOT NULL AND vt.TRANSTYPE = 'Payment' AND bt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 14. Invoice Cancelled
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Cancelled' AS "ActivityName",
        vj.MODIFIEDDATETIME AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.APPROVALSTATUS = 'Canceled' AND vj.DATACOMPANY = '[Your Company]'
)

SELECT * FROM FinalEventLog;

步骤

前提条件与访问权限：确保您拥有Microsoft Dynamics 365 Finance and Operations数据库的只读SQL访问权限。此方法适用于生产数据库副本（Azure SQL）或自带数据库（BYOD）实例。不建议直接查询主生产数据库。
连接数据库：使用SQL Server Management Studio（SSMS）或Azure Data Studio等SQL客户端，通过提供的凭据连接数据库实例。
准备SQL查询：将查询部分提供的T-SQL查询复制到SQL客户端的新查询窗口中。
配置参数：在查询顶部找到参数部分。设置@StartDate和@EndDate以定义分析时段，并将@CompanyId设为您要分析的特定法人实体（例如'USMF'）。
执行查询：运行完整的T-SQL脚本。执行时间取决于日期范围以及系统中的发票数据量。
检查初步结果：查询完成后，检查结果网格，确认其中包含数据且列显示正确。查看ActivityName是否包含多种值。
导出为CSV：将结果集导出为CSV文件。在SSMS中，右键点击结果网格，选择“另存结果为…”，然后选择CSV格式。将文件命名为event_log.csv。
验证CSV格式：打开CSV文件，确认首行为正确的列名（Invoice、ActivityName、EventTime等）。确保EventTime列采用标准格式，例如YYYY-MM-DD HH:MI:SS。
上传至流程挖掘工具：CSV文件现已准备就绪，可上传至您的流程挖掘应用。按以下方式映射列：Invoice映射到案例ID，ActivityName映射到活动，EventTime映射到时间戳。

配置

数据库连接：此方法需要直接连接Dynamics 365 Azure SQL数据库。强烈建议使用只读副本或BYOD实例，以免影响生产环境性能。
日期范围：查询包含@StartDate和@EndDate参数。建议先使用较短的时间范围（例如3至6个月），以控制查询性能和数据量。筛选条件应用于发票创建日期。
公司筛选：必须设置@CompanyId参数（在D365中称为DataAreaId）。您必须指定一个法人实体ID，确保仅分析单一公司的数据。
工作流数据：查询依赖D365标准工作流历史记录表（WorkflowTrackingStatusTable）。如果您的组织使用高度定制的审批工作流，可能需要调整查询中的审批步骤逻辑。
性能注意事项：对大型数据集执行此查询可能占用大量资源。建议在业务低峰时段运行，以尽量减少对系统性能的影响。对于超大型数据集，可考虑按较短的时间段分批运行查询。

查询示例 sql

-- ==========================================================================================
-- T-SQL EXTRACTOR FOR DYNAMICS 365 F&O - ACCOUNTS PAYABLE INVOICE PROCESSING
-- ==========================================================================================

-- Parameters: Configure these values for your extraction
DECLARE @StartDate DATE = '2023-01-01'; -- The start date of the extraction window
DECLARE @EndDate DATE = '2023-12-31';   -- The end date of the extraction window
DECLARE @CompanyId NVARCHAR(4) = '[YourCompanyId]'; -- The legal entity/company ID (e.g., 'USMF')

-- ==========================================================================================
-- Main Query: Do not edit below this line
-- ==========================================================================================

WITH BaseInvoices AS (
    -- Selects the core set of invoices to be analyzed based on the date range and company
    SELECT
        viit.NUM AS Invoice,
        viit.PURCHID AS PurchaseOrderNumber,
        viit.INVOICEACCOUNT,
        viit.INVOICEDATE,
        viit.INVOICEAMOUNTMST AS InvoiceAmount,
        dp.NAME AS VendorName,
        pt.DESCRIPTION AS PaymentTerms,
        viit.DUEDATE AS InvoiceDueDate,
        viit.CREATEDBY AS CreatedUserId,
        viit.CREATEDDATETIME,
        viit.RECID AS VendInvoiceInfoTableRecId,
        viit.DATAREAID
    FROM VendInvoiceInfoTable viit
    LEFT JOIN VendTable vt ON viit.INVOICEACCOUNT = vt.ACCOUNTNUM AND viit.DATAREAID = vt.DATAREAID
    LEFT JOIN DirPartyTable dp ON vt.PARTY = dp.RECID
    LEFT JOIN PaymTerm pt ON vt.PAYMTERMID = pt.PAYMTERMID AND vt.DATAREAID = pt.DATAREAID
    WHERE viit.CREATEDDATETIME >= @StartDate 
      AND viit.CREATEDDATETIME < DATEADD(day, 1, @EndDate) 
      AND viit.DATAREAID = @CompanyId
)

-- 1. Invoice Registered
SELECT
    bi.Invoice,
    'Invoice Registered' AS ActivityName,
    bi.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
LEFT JOIN USERINFO u ON bi.CreatedUserId = u.ID

UNION ALL

-- 2. Invoice Data Validated (Approximated by first review step in workflow)
SELECT
    bi.Invoice,
    'Invoice Data Validated' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable')
  AND wst.TRACKINGTYPE = 2 -- Task/Work Item
  AND wst.WORKFLOWSTEPID LIKE '%Review%'
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 3 & 4. Purchase Order / Goods Receipt Matched (Approximated by last modification when status is 'Passed')
SELECT
    bi.Invoice,
    'Purchase Order Matched' AS ActivityName, -- Assuming PO and GR matching is one event
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 2 -- Passed

UNION ALL

-- 5. Discrepancy Identified (Approximated by last modification when status is 'Failed')
SELECT
    bi.Invoice,
    'Discrepancy Identified' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 3 -- Failed

UNION ALL

-- 6. Invoice Submitted For Approval
SELECT
    bi.Invoice,
    'Invoice Submitted For Approval' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 1 -- Submission
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 7. Invoice Approved (Final approval of the workflow)
SELECT
    bi.Invoice,
    'Invoice Approved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 5 -- Completion
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 8. Invoice Rejected
SELECT
    bi.Invoice,
    'Invoice Rejected' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 7 -- Rejection
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 9. Discrepancy Resolved (Approximated by workflow 'Request Change' step)
SELECT
    bi.Invoice,
    'Discrepancy Resolved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 3 -- RequestChange
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 10. Invoice Posted
SELECT
    bi.Invoice,
    'Invoice Posted' AS ActivityName,
    vij.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.CREATEDBY = u.ID

UNION ALL

-- 11. Payment Scheduled (Creation of a payment journal line for the invoice)
SELECT DISTINCT
    bi.Invoice,
    'Payment Scheduled' AS ActivityName,
    ljt.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt ON bi.Invoice = vt.INVOICENUM AND bi.DATAREAID = vt.DATAREAID
JOIN LedgerJournalTrans ljt ON vt.VOUCHER = ljt.VOUCHER AND vt.TRANSDATE = ljt.TRANSDATE AND vt.DATAREAID = ljt.DATAREAID
JOIN LedgerJournalTable ljh ON ljt.JOURNALNUM = ljh.JOURNALNUM AND ljt.DATAREAID = ljh.DATAREAID
LEFT JOIN USERINFO u ON ljt.CREATEDBY = u.ID
WHERE ljh.JOURNALTYPE = 2 -- Vendor Payment
  AND ljh.POSTED = 0

UNION ALL

-- 12. Payment Executed (Posting of the payment journal)
SELECT
    bi.Invoice,
    'Payment Executed' AS ActivityName,
    vs.TRANSDATE AS EventTime, -- Settlement Date
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
LEFT JOIN USERINFO u ON vs.CREATEDBY = u.ID

UNION ALL

-- 13. Payment Cleared (Bank reconciliation date)
SELECT 
    bi.Invoice,
    'Payment Cleared' AS ActivityName,
    bt.CLEAREDDATE AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
JOIN BankAccountTrans bat ON vt_pay.VOUCHER = bat.VOUCHER AND vt_pay.TRANSDATE = bat.TRANSDATE AND vt_pay.DATAREAID = bat.DATAREAID
JOIN BankTrans bt ON bat.RECID = bt.BANKACCOUNTTRANSRECID
LEFT JOIN USERINFO u ON bt.CREATEDBY = u.ID
WHERE bt.CLEAREDDATE > '1900-01-01'

UNION ALL

-- 14. Invoice Cancelled
SELECT
    bi.Invoice,
    'Invoice Cancelled' AS ActivityName,
    vij.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.MODIFIEDBY = u.ID
WHERE vij.INVOICECANCELLED_RU = 1 -- Note: This field can be localization-specific. Adjust if needed.

ORDER BY Invoice, EventTime;

步骤

前提条件：在Azure AD中注册应用程序
- 前往Azure Active Directory管理中心。
- 打开“应用注册”，创建新的注册。
- 在“API权限”下，为Dynamics ERP添加权限，并选择user_impersonation（委托权限）。
- 在“证书和机密”下创建新的客户端机密，并妥善保存其值。
- 记下应用程序（客户端）ID和目录（环境）ID。
前提条件：在Dynamics 365中创建应用程序用户
- 在Dynamics 365 F&O中，前往系统管理>用户>用户。
- 创建新用户并设置用户ID和用户名。将提供程序设为您的Azure AD环境（例如https://sts.windows.net/[your_tenant_id]）。
- 将Azure AD应用注册中的应用程序（客户端）ID填入“客户ID”字段。
- 为此用户分配适当的安全角色（例如，具有供应商发票、付款和工作流历史记录读取权限的角色）。
设置本地Python环境
- 确保已安装Python 3.7或更高版本。
- 在终端中运行以下命令安装所需库：pip install requests msal pandas
配置Python脚本
- 将提供的Python脚本复制到文件中（例如extract_d365_ap.py）。
- 填写脚本顶部的占位变量：D365_URL、TENANT_ID、CLIENT_ID、CLIENT_SECRET、DATA_AREA_ID（您的D365公司ID，例如'USMF'）、START_DATE和END_DATE。
运行脚本
- 在终端中运行脚本：python extract_d365_ap.py
- 脚本将通过Azure AD进行身份验证，从Dynamics 365 OData API获取各项已定义活动的数据，并自动处理分页。
转换并汇总数据
- 脚本会处理API为每项活动返回的JSON数据。
- 它会将不同D365数据实体（VendorInvoiceHeaders、WorkflowTrackingStatusTable、VendTrans等）中的源字段映射到所需的事件日志结构（Invoice、ActivityName、EventTime等）。
- 所有活动数据将合并为一份主事件日志。
排序和清理数据
- 按时间顺序对每张发票的事件日志进行排序，确保事件顺序正确。
- pandas库会处理基本的数据类型转换。
导出最终事件日志
- 脚本会将汇总后的事件日志保存为同一目录下名为d365_ap_event_log.csv的CSV文件。
- 此文件已完成格式整理，可上传至ProcessMind等流程挖掘工具。

配置

身份验证：脚本使用OAuth 2.0客户端凭据流。必须注册Azure AD应用，并在Dynamics 365中创建具有适当读取权限的对应应用程序用户。
数据实体：提取过程依赖D365 F&O标准OData实体。主要实体包括：VendorInvoiceHeaders、VendorInvoiceLines、WorkflowTrackingStatusTable、VendTrans、VendorPaymentJournalLines和BankCanceledChecks。请确保应用程序用户具有这些实体的读取权限。
日期范围：设置START_DATE和END_DATE变量以定义提取时间范围。首次分析建议使用3至6个月的数据，以平衡数据量和性能。
公司筛选：DATA_AREA_ID变量是必需的筛选条件，用于指定要从D365提取数据的法人实体（公司）。
工作流配置：脚本假定工作流历史记录中包含特定活动名称（例如'Approve vendor invoice'、'Resolve Discrepancy'）。您可能需要调整脚本的筛选逻辑，使其与组织的工作流配置一致。
性能：处理超大型数据集时，OData API可能较慢。脚本支持分页，但不包含针对API限流的高级重试逻辑。提取大量数据时，建议在业务低峰时段运行。

查询示例 python

import requests
import msal
import pandas as pd
import time
import urllib.parse

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Dynamics 365 Finance & Operations details
D365_URL = class="hl-string">"https://[your_d365_instance].operations.dynamics.com"
TENANT_ID = class="hl-string">"[your_azure_tenant_id]"
CLIENT_ID = class="hl-string">"[your_azure_app_client_id]"
CLIENT_SECRET = class="hl-string">"[your_azure_app_client_secret]"

class=class="hl-string">"hl-comment"># Replace with your specific data filters
DATA_AREA_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"># --- AUTHENTICATION --- 
def get_access_token():
    class="hl-string">"""Authenticates with Azure AD and returns an access token."""
    authority = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
    scope = [fclass="hl-string">"{D365_URL}/.default"]
    app = msal.ConfidentialClientApplication(
        CLIENT_ID, authority=authority, client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=scope)
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        raise Exception(fclass="hl-string">"Error acquiring token: {result.get('error_description')}")

class=class="hl-string">"hl-comment"># --- DATA FETCHING --- 
def fetch_odata_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given OData entity URL."""
    all_records = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'odata.maxpagesize=1000' class=class="hl-string">"hl-comment"># Request pagination
    }
    while entity_url:
        try:
            response = requests.get(entity_url, headers=headers, timeout=60)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            all_records.extend(data[class="hl-string">'value'])
            entity_url = data.get(class="hl-string">'@odata.nextLink') class=class="hl-string">"hl-comment"># Get URL for the next page
            print(fclass="hl-string">"Fetched {len(all_records)} records so far...")
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            time.sleep(5) class=class="hl-string">"hl-comment"># Simple retry logic
            class=class="hl-string">"hl-comment"># More robust error handling might be needed for production
            continue
    return all_records

class=class="hl-string">"hl-comment"># --- MAIN SCRIPT --- 
def main():
    print(class="hl-string">"Acquiring access token...")
    token = get_access_token()
    all_activity_dfs = []

    base_api_url = fclass="hl-string">"{D365_URL}/data"

    class=class="hl-string">"hl-comment"># Common attributes to select
    common_attrs = class="hl-string">'VendorAccount,InvoiceNumber,InvoiceAmount,InvoiceDate,DueDate,PaymentTermsName,PurchaseOrderNumber'

    class=class="hl-string">"hl-comment"># 1. Invoice Registered
    print(class="hl-string">"\nExtracting 'Invoice Registered'...")
    entity = class="hl-string">'VendorInvoiceHeaders'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select={common_attrs},CreatedDateTime,CreatedBy&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Registered'
        df.rename(columns={class="hl-string">'InvoiceNumber': class="hl-string">'Invoice', class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'PaymentTerms'] = df[class="hl-string">'PaymentTermsName']
        df[class="hl-string">'InvoiceDueDate'] = df[class="hl-string">'DueDate']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># Get a list of invoice numbers for subsequent queries
    if class="hl-string">'Invoice' in df.columns:
        invoice_list = df[class="hl-string">'Invoice'].unique().tolist()
        if not invoice_list:
            print(class="hl-string">"No invoices found in the specified date range. Exiting.")
            return
        class=class="hl-string">"hl-comment"># Prepare for class="hl-string">'in' clause in OData filter
        invoice_filter_part = class="hl-string">" or ".join([fclass="hl-string">"RefRecIdTable/InvoiceNumber eq '{inv}'" for inv in invoice_list])
        vendtrans_filter_part = class="hl-string">" or ".join([fclass="hl-string">"Invoice eq '{inv}'" for inv in invoice_list])
    else:
        print(class="hl-string">"No invoices registered in the period. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Activities from Workflow History (Submitted, Approved, Rejected, Discrepancy related)
    print(class="hl-string">"\nExtracting workflow activities...")
    entity = class="hl-string">'WorkflowTrackingStatusTables'
    class=class="hl-string">"hl-comment"># NOTE: The class="hl-string">'Comment' field filter is highly dependent on your specific workflow configuration.
    class=class="hl-string">"hl-comment"># Adjust the activity names like class="hl-string">'Submit', class="hl-string">'Approve', class="hl-string">'Reject', class="hl-string">'Resolve' as needed.
    activity_filters = class="hl-string">"(contains(Comment, 'Submit') or contains(Comment, 'Approv') or contains(Comment, 'Reject') or contains(Comment, 'Discrepancy') or contains(Comment, 'Validat'))"
    filters = fclass="hl-string">"({invoice_filter_part}) and {activity_filters}"
    class=class="hl-string">"hl-comment"># Expand to get invoice number and other details
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Comment,CreatedDateTime,UserId&$expand=RefRecIdTable($select=InvoiceNumber,VendorAccount,InvoiceAmount,DueDate,PaymentTermsName,PurchaseOrderNumber)&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_wf = pd.DataFrame(records)
    if not df_wf.empty:
        df_wf[class="hl-string">'Invoice'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceNumber') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'VendorName'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'VendorAccount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceAmount'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceAmount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceDueDate'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'DueDate') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PaymentTerms'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PaymentTermsName') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PurchaseOrderNumber'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PurchaseOrderNumber') if isinstance(x, dict) else None)
        
        def map_activity(comment):
            comment = str(comment).lower()
            if class="hl-string">'submit' in comment: return class="hl-string">'Invoice Submitted For Approval'
            if class="hl-string">'approv' in comment: return class="hl-string">'Invoice Approved'
            if class="hl-string">'reject' in comment: return class="hl-string">'Invoice Rejected'
            if class="hl-string">'discrepancy identified' in comment: return class="hl-string">'Discrepancy Identified' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'discrepancy resolved' in comment: return class="hl-string">'Discrepancy Resolved' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'match' in comment: return class="hl-string">'Purchase Order Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'receipt matched' in comment: return class="hl-string">'Goods Receipt Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'validat' in comment: return class="hl-string">'Invoice Data Validated' class=class="hl-string">"hl-comment"># Assumes step name
            return None

        df_wf[class="hl-string">'ActivityName'] = df_wf[class="hl-string">'Comment'].apply(map_activity)
        df_wf.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'UserId': class="hl-string">'UserName'}, inplace=True)
        df_wf.dropna(subset=[class="hl-string">'ActivityName', class="hl-string">'Invoice'], inplace=True)
        all_activity_dfs.append(df_wf)

    class=class="hl-string">"hl-comment"># Activities from Posted Transactions (Posted, Paid, Cancelled)
    print(class="hl-string">"\nExtracting posted transactions (Invoice, Payment, Cancellation)...")
    entity = class="hl-string">'VendTrans'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Invoice,TransDate,CreatedDateTime,TransactionType,LastSettleVoucher,VendorAccount&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_vt = pd.DataFrame(records)
    if not df_vt.empty:
        class=class="hl-string">"hl-comment"># Invoice Posted
        df_posted = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice'].copy()
        df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted'
        class=class="hl-string">"hl-comment"># Payment Executed
        df_paid = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Payment'].copy()
        df_paid[class="hl-string">'ActivityName'] = class="hl-string">'Payment Executed'
        class=class="hl-string">"hl-comment"># Invoice Cancelled (often a reversal of the invoice transaction)
        class=class="hl-string">"hl-comment"># This logic may need adjustment based on how cancellations are recorded
        df_cancelled = df_vt[(df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice') & (df_vt[class="hl-string">'Invoice'].str.contains(class="hl-string">'CANCEL', na=False))].copy()
        df_cancelled[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Cancelled'

        df_trans = pd.concat([df_posted, df_paid, df_cancelled])
        df_trans.rename(columns={class="hl-string">'TransDate': class="hl-string">'EventTime', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df_trans[class="hl-string">'UserName'] = class="hl-string">'System' class=class="hl-string">"hl-comment"># Usually a system-driven event
        all_activity_dfs.append(df_trans)

    class=class="hl-string">"hl-comment"># 11. Payment Scheduled
    print(class="hl-string">"\nExtracting 'Payment Scheduled'...")
    entity = class="hl-string">'VendorPaymentJournalLines'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=JournalBatchNumber,CreatedDateTime,AccountDisplayValue,Invoice,AmountInTransactionCurrency&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Payment Scheduled'
        df.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'AccountDisplayValue': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'UserName'] = class="hl-string">'System' 
        df[class="hl-string">'InvoiceAmount'] = df[class="hl-string">'AmountInTransactionCurrency']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># 13. Payment Cleared
    print(class="hl-string">"\nExtracting 'Payment Cleared'...")
    class=class="hl-string">"hl-comment"># This requires linking payments to bank data, which can be complex.
    class=class="hl-string">"hl-comment"># This example assumes we can find cleared checks and relate them back via voucher.
    settle_vouchers = df_vt[df_vt[class="hl-string">'LastSettleVoucher'].notna()][class="hl-string">'LastSettleVoucher'].unique().tolist()
    if settle_vouchers:
        voucher_filter = class="hl-string">" or ".join([fclass="hl-string">"CheckNumber eq '{v}'" for v in settle_vouchers])
        entity = class="hl-string">'BankCanceledChecks' class=class="hl-string">"hl-comment"># This entity name might vary
        filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({voucher_filter})"
        url = fclass="hl-string">"{base_api_url}/{entity}?$select=CheckNumber,ClearedDate,AccountID&$filter={filters}"
        records = fetch_odata_data(token, url)
        df_cleared = pd.DataFrame(records)
        if not df_cleared.empty:
            class=class="hl-string">"hl-comment"># Map back to invoice
            map_voucher_invoice = df_vt.set_index(class="hl-string">'LastSettleVoucher')[class="hl-string">'Invoice'].to_dict()
            df_cleared[class="hl-string">'Invoice'] = df_cleared[class="hl-string">'CheckNumber'].map(map_voucher_invoice)
            df_cleared.dropna(subset=[class="hl-string">'Invoice'], inplace=True)
            df_cleared[class="hl-string">'ActivityName'] = class="hl-string">'Payment Cleared'
            df_cleared.rename(columns={class="hl-string">'ClearedDate': class="hl-string">'EventTime'}, inplace=True)
            df_cleared[class="hl-string">'UserName'] = class="hl-string">'System'
            all_activity_dfs.append(df_cleared)

    class=class="hl-string">"hl-comment"># --- FINAL ASSEMBLY --- 
    print(class="hl-string">"\nAssembling final event log...")
    if not all_activity_dfs:
        print(class="hl-string">"No activities were extracted. Exiting.")
        return

    event_log = pd.concat(all_activity_dfs, ignore_index=True)

    class=class="hl-string">"hl-comment"># Select and reorder columns
    final_cols = [
        class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName',
        class="hl-string">'VendorName', class="hl-string">'InvoiceAmount', class="hl-string">'InvoiceDueDate', 
        class="hl-string">'PaymentTerms', class="hl-string">'PurchaseOrderNumber'
    ]
    class=class="hl-string">"hl-comment"># Add missing columns with None
    for col in final_cols:
        if col not in event_log.columns:
            event_log[col] = None
    
    event_log = event_log[final_cols]
    
    class=class="hl-string">"hl-comment"># Clean up and sort
    event_log.dropna(subset=[class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    event_log.sort_values(by=[class="hl-string">'Invoice', class="hl-string">'EventTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Export to CSV
    output_filename = class="hl-string">'d365_ap_event_log.csv'
    event_log.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

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

步骤

在Microsoft Power Platform中，创建或打开一个数据流，并确保其所在环境有权访问Microsoft Dynamics 365。确认已具备所需的Dynamics 365连接器、Power Query创作体验、Dataverse或Dynamics 365权限以及目标位置权限。
添加原生Microsoft Dynamics 365连接器，并使用已批准的环境、组织或环境详细信息配置连接。将[Your connection configuration]替换为组织所需的连接设置。请勿在数据流定义中暴露凭据。
选择包含源记录的发票、工作流、采购订单、收货、付款、银行对账和审计实体。实体名称和字段名称因Dynamics 365应用及配置而异，请将每个[Your ...]占位符替换为您环境中对应的实体或字段。
按所需公司、法人实体、单据类型、发票状态和提取时段设置源数据筛选条件。首次使用三至六个月的滚动时间范围，并在验证性能和数据完整性后再延长。
在Power Query中，将源字段统一为所需的事件日志架构。输出必须包含Invoice、ActivityName、EventTime、UserName、VendorName、InvoiceAmount、InvoiceDueDate、PaymentTerms和PurchaseOrderNumber。将EventTime转换为统一的日期时间类型，将InvoiceAmount转换为数值类型。
为每项所需活动创建一个事件查询。每个查询都必须为每条符合条件的源事件返回一行，赋予查询中指定的准确ActivityName值，并保留发票标识符。不要依赖ProcessMind根据状态、日期或字段变更推断活动。
使用等效于UNION ALL的方式合并14个活动查询。如果重复事件代表不同的工作流操作、匹配尝试、更正、付款操作或其他有效源事件，请予以保留。只有确认属于源数据提取产生的技术性重复时，才删除重复记录。
验证Invoice字段已填充，ActivityName仅包含14个已批准的值，EventTime已填充且可用于时间排序，并且在数据源提供时已映射建议属性。检查所有联接是否保留发票案例标识符，且不会意外增加行数。
将转换后的事件日志加载到配置好的Power Platform数据流目标位置，或导出为CSV及其他ProcessMind支持的格式。确保导出列名准确为Invoice、ActivityName、EventTime、UserName、VendorName、InvoiceAmount、InvoiceDueDate、PaymentTerms和PurchaseOrderNumber。
将完成的事件日志上传至ProcessMind，并确认Invoice已设为案例标识符、ActivityName已设为活动、EventTime已设为事件时间戳。确认可视化仅显示明确提取的行，不会要求ProcessMind推导缺失的生命周期事件。

配置

连接：通过Power Platform数据流使用原生Microsoft Dynamics 365连接器。将[Your connection configuration]替换为适用于您环境的已批准环境和组织配置。
源实体：选择已配置的发票、工作流、采购订单、收货、付款、银行对账和审计实体。实体名称因Dynamics 365产品、版本、模块和定制情况而异。
必需输出列：可用的事件行必须包含已填充的Invoice、ActivityName和EventTime。
建议输出列：如果源实体中提供，建议包含UserName、VendorName、InvoiceAmount、InvoiceDueDate、PaymentTerms和PurchaseOrderNumber。
活动值：请使用以下准确值：Invoice Registered、Invoice Data Validated、Purchase Order Matched、Goods Receipt Matched、Discrepancy Identified、Invoice Submitted For Approval、Invoice Approved、Invoice Rejected、Discrepancy Resolved、Invoice Posted、Payment Scheduled、Payment Executed、Payment Cleared和Invoice Cancelled。
日期范围：先使用三至六个月的历史数据。定期加载时，可根据所选连接器和目标位置的功能，使用增量刷新或按EventTime设置滚动筛选条件。
筛选条件：应用相关法人实体或公司筛选、发票单据类型筛选、过账状态筛选和源系统日期筛选。将[Your company filter]和[Your document type filter]替换为您环境中配置的值。
时区：将源时间戳转换为统一且有记录的时区。如需确保可完整审计，请保留原始时间戳，并记录任何夏令时转换。
性能：在联接前筛选源实体，只选择所需列，避免不必要地展开大型关联实体，并在支持时使用增量加载。匹配和审计实体的数据量可能远大于发票抬头实体。
联接行为：使用稳定的发票标识符进行联接。验证一对多关系，避免单个源事件因无关的发票行、采购订单、收货或付款记录而重复扩增。
前提条件：需要具备Dynamics 365读取权限、Power Platform数据流访问权限、适用于该环境的连接器许可、目标存储或目标位置访问权限，以及读取工作流、会计、付款和银行对账数据的授权。
配置差异：实体名称、状态值、审计数据可用性、工作流历史记录保留期限和付款清算数据均取决于Dynamics 365应用及组织配置。需要本地映射时，请使用[Configure based on your system]。

查询示例 sql

WITH
InvoiceRegistered AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Registered' AS varchar(100)) AS ActivityName,
        CAST([Your invoice creation timestamp field] AS datetime2) AS EventTime,
        CAST([Your invoice creation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice entity]
    WHERE [Your invoice creation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your invoice creation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your company filter field] = '[Your company filter]'
      AND [Your document type field] = '[Your document type filter]'
),
InvoiceDataValidated AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Data Validated' AS varchar(100)) AS ActivityName,
        CAST([Your validation timestamp field] AS datetime2) AS EventTime,
        CAST([Your validation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice validation entity]
    WHERE [Your validation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your validation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your validation result field] = '[Your successful validation value]'
),
PurchaseOrderMatched AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Purchase Order Matched' AS varchar(100)) AS ActivityName,
        CAST([Your purchase order match timestamp field] AS datetime2) AS EventTime,
        CAST([Your match user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your purchase order matching entity]
    WHERE [Your purchase order match timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your purchase order match timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your match result field] = '[Your successful match value]'
),
GoodsReceiptMatched AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Goods Receipt Matched' AS varchar(100)) AS ActivityName,
        CAST([Your goods receipt match timestamp field] AS datetime2) AS EventTime,
        CAST([Your match user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your goods receipt matching entity]
    WHERE [Your goods receipt match timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your goods receipt match timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your match result field] = '[Your successful match value]'
),
DiscrepancyIdentified AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Discrepancy Identified' AS varchar(100)) AS ActivityName,
        CAST([Your discrepancy creation timestamp field] AS datetime2) AS EventTime,
        CAST([Your discrepancy user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice discrepancy entity]
    WHERE [Your discrepancy creation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your discrepancy creation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your discrepancy status field] = '[Your open discrepancy value]'
),
InvoiceSubmittedForApproval AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Submitted For Approval' AS varchar(100)) AS ActivityName,
        CAST([Your submission timestamp field] AS datetime2) AS EventTime,
        CAST([Your submission user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your submission timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your submission timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your submission action value]'
),
InvoiceApproved AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Approved' AS varchar(100)) AS ActivityName,
        CAST([Your approval timestamp field] AS datetime2) AS EventTime,
        CAST([Your approval user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your approval timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your approval timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your approval action value]'
),
InvoiceRejected AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Rejected' AS varchar(100)) AS ActivityName,
        CAST([Your rejection timestamp field] AS datetime2) AS EventTime,
        CAST([Your rejection user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your rejection timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your rejection timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your rejection action value]'
),
DiscrepancyResolved AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Discrepancy Resolved' AS varchar(100)) AS ActivityName,
        CAST([Your discrepancy resolution timestamp field] AS datetime2) AS EventTime,
        CAST([Your discrepancy resolution user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice discrepancy entity]
    WHERE [Your discrepancy resolution timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your discrepancy resolution timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your discrepancy status field] = '[Your resolved discrepancy value]'
),
InvoicePosted AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Posted' AS varchar(100)) AS ActivityName,
        CAST([Your posting timestamp field] AS datetime2) AS EventTime,
        CAST([Your posting user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice posting entity]
    WHERE [Your posting timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your posting timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your posting status field] = '[Your posted value]'
),
PaymentScheduled AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Scheduled' AS varchar(100)) AS ActivityName,
        CAST([Your payment scheduling timestamp field] AS datetime2) AS EventTime,
        CAST([Your payment scheduling user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your payment proposal or journal entity]
    WHERE [Your payment scheduling timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your payment scheduling timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your payment scheduling status field] = '[Your scheduled value]'
),
PaymentExecuted AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Executed' AS varchar(100)) AS ActivityName,
        CAST([Your payment execution timestamp field] AS datetime2) AS EventTime,
        CAST([Your payment execution user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your payment journal entity]
    WHERE [Your payment execution timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your payment execution timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your payment execution status field] = '[Your executed value]'
),
PaymentCleared AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Cleared' AS varchar(100)) AS ActivityName,
        CAST([Your bank clearing timestamp field] AS datetime2) AS EventTime,
        CAST([Your bank reconciliation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your bank reconciliation entity]
    WHERE [Your bank clearing timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your bank clearing timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your bank clearing status field] = '[Your cleared value]'
),
InvoiceCancelled AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Cancelled' AS varchar(100)) AS ActivityName,
        CAST([Your cancellation timestamp field] AS datetime2) AS EventTime,
        CAST([Your cancellation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice cancellation entity]
    WHERE [Your cancellation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your cancellation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your cancellation status field] = '[Your cancelled value]'
)
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceRegistered
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceDataValidated
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PurchaseOrderMatched
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM GoodsReceiptMatched
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM DiscrepancyIdentified
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceSubmittedForApproval
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceApproved
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceRejected
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM DiscrepancyResolved
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoicePosted
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentScheduled
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentExecuted
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentCleared
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceCancelled;

准备好开始了吗？

借助此数据模板，开启应付账款发票处理流程提效之旅。充分利用流程数据，推动显著改进。

返回改进指南

下载模板

杜绝重复付款，优化应付账款发票处理。

找准低效环节，减少滞纳金，将处理成本降低60%。

立即开始免费试用

免费试用14天，无需信用卡。

您的应付账款发票处理数据模板

您的应付账款发票处理数据模板

应付账款发票处理属性

应付账款发票处理活动

数据提取指南

Dynamics 365数据管理框架导出

步骤

配置

查询示例 sql

Dynamics 365 Azure SQL数据库直接查询

步骤

配置

查询示例 sql

使用Dynamics 365 OData API的Python脚本

步骤

配置

查询示例 python

Microsoft Power Platform数据流提取

步骤

配置

查询示例 sql

准备好开始了吗？

杜绝重复付款，优化应付账款发票处理。