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

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

Microsoft Dynamics 365

← 返回改进指南

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

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

此模板将指导您收集分析和优化应付账款发票处理所需的关键数据，列出重要属性和关键活动，并提供实用的信息提取指引。使用此模板可以确保您获取完整的事件日志，从而深入了解流程绩效。

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

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

下载模板

应付账款发票处理属性

以下是建议纳入事件日志的数据字段，用于全面分析您的应付账款发票处理流程，帮助您获得深入洞察。

3 必需 6 建议 12 可选

	名称	说明
	发票 Invoice	每张供应商发票单据的唯一标识符。
说明 Invoice是主要案例标识符，将从收到发票到最终付款的所有活动关联起来。借助该标识符，可以端到端分析每张发票在应付账款流程中的处理历程。在分析中，按该标识符对事件分组，是重建每张发票流程路径的第一步。它支持计算案例级KPI，例如总周期时间，并帮助识别特定发票处理路径中的变体和瓶颈。为什么重要这是追踪发票完整生命周期的关键，也是应付账款流程挖掘分析的基础。获取位置通常是Dynamics 365 Finance“Vendor invoice”页面或相关数据实体中的供应商发票编号。示例 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 LtdFabrikam IncNorthwind Traders
	发票到期日 InvoiceDueDate	根据付款条款计算出的发票付款截止日期。
说明该日期表示为避免罚金并遵守供应商协议而应支付发票的截止时间，通常根据发票日期和指定付款条款计算。该属性是“付款条款合规性”分析的基础。通过比较“Payment Executed”日期与“Invoice Due Date”，分析可以自动标记逾期付款、计算合规率，并帮助安排付款以维护良好的供应商关系。为什么重要对于衡量按时付款表现、管理供应商关系和避免逾期付款费用至关重要。获取位置根据发票日期和付款条款计算得出，存储在供应商交易表（例如“VendTrans”）的“DueDate”等字段中。示例 2023-05-152023-06-302023-07-01
	发票金额 InvoiceAmount	发票的货币总金额。
说明表示供应商发票应付的总金额，是每个案例的重要财务指标。该属性支持对应付账款流程进行财务分析，可用于优先处理高价值发票，按金额阈值分析审批时间，并了解付款延迟或提前付款折扣带来的财务影响。为什么重要提供财务背景信息，支持根据金额分析流程行为，例如识别高价值发票是否采用不同的处理方式。获取位置位于供应商发票抬头中，通常为“VendInvoiceInfoTable”中的“InvoiceAmount”等字段。示例 1500.75250.0012345.50
	用户 UserName	执行该活动的用户。
说明标识负责完成流程步骤的具体用户，例如登记或审批发票的用户。该信息通常与Dynamics 365中的用户系统ID关联。按用户分析绩效，有助于识别培训需求、高绩效人员和工作量分配情况。对于关注审批时间和资源效率的仪表板而言，这一点至关重要，可帮助管理者了解哪些用户或团队构成瓶颈。为什么重要将工作归属到具体人员，从而支持分析工作量和绩效，并识别培训机会。获取位置通常来自交易记录中的“Created by”或“Modified by”字段，或Dynamics 365中的工作流历史日志。示例 j.doea.smithr.williams
	采购订单编号 PurchaseOrderNumber	与发票关联的采购订单唯一标识符。
说明该属性将发票与对应的采购订单（PO）关联。发票可以有PO支持，也可以没有PO支持。分析该属性有助于区分基于PO和非PO的发票处理流程，因为两者通常遵循不同的工作流。它对于衡量PO匹配效率，以及识别发票明细与采购订单不一致时产生的问题至关重要。为什么重要区分遵循不同流程的PO发票和非PO发票，对于分析匹配效率至关重要。获取位置位于供应商发票抬头或发票行中，通常是“VendInvoiceInfoTable”中的“PurchId”字段。示例 PO-000432PO-000511
	公司代码 CompanyCode	处理发票的法人实体标识。
说明此属性用于标识组织内负责发票的具体公司或法人实体。在多公司环境中，这是关键的组织维度。按公司代码分析AP流程，可以比较不同业务部门或法人实体的绩效，帮助识别效率较低、返工率较高或采用不同流程变体的实体，从而发现标准化和最佳实践共享的机会。为什么重要支持在组织内不同法人实体或业务部门之间进行绩效基准比较和流程对比。获取位置 Dynamics 365几乎所有事务表中的标准字段，通常命名为“DataAreaId”。示例 USMFDEMFGBSI
	最后数据更新时间 LastDataUpdate	源系统最后一次刷新数据的时间戳。
说明该属性表示事件日志最后一次从Microsoft Dynamics 365更新为最新数据的时间，为所分析数据的新鲜度提供背景信息。对于仪表板和持续监控，该时间戳有助于用户了解当前查看的是否为最新流程数据，也能帮助管理对数据时效性的预期，并监控数据管道的运行状况。为什么重要告知用户数据的新鲜度，对于基于分析及时、准确地做出业务决策至关重要。获取位置这是由数据提取和加载（ETL）工具在数据接入时生成并写入的元数据字段。示例 2023-06-01T02:00:00Z2023-06-02T02:00:00Z
	发票状态 InvoiceStatus	发票当前的处理状态。
说明此属性反映发票在流程中的最后已知状态，例如“处理中”“已批准”“已付款”或“已取消”。它展示了发票在生命周期中的当前位置。流程挖掘通过活动推导流程，而最终状态可用于验证，并创建按业务层面汇总所有未结发票当前状态的仪表板。您可以用它筛选出当前“已批准”但尚未“已付款”的所有发票。为什么重要提供发票当前状态的概览，便于筛选并创建基于状态的仪表板。获取位置通常取自AP模块中的单据状态或工作流状态字段。示例处理中已批准已付款已取消
	审批人 ApproverName	在特定步骤批准或拒绝发票的用户。
说明此属性用于标识工作流中负责审批决策的人员。对于多级审批发票，不同步骤可能对应不同审批人。它是“Invoice Approval Cycle Time Analysis”仪表板的关键数据。按审批人拆分分析，可以识别因工作量或其他因素成为流程瓶颈的人员，从而采取有针对性的措施，加快审批周期。为什么重要支持详细分析审批流程，帮助识别个人或团队层面的瓶颈。获取位置从工作流历史日志（“WorkflowTrackingStatusTable”）中提取，该日志记录完成每个审批步骤的用户。示例 David ChenMaria GarciaAP_Manager_Group
	差异原因 DiscrepancyReason	发票差异的原因代码或说明。
说明当发票被挂起或发现差异时，此属性记录具体原因，例如“Price Mismatch”“Quantity Difference”或“Missing Goods Receipt”。此属性是“Discrepancy Resolution & Rework”分析的关键。按原因对返工进行分类，可以识别流程低效的根本原因。例如，如果“Price Mismatch”最常见，说明采购方与供应商之间需要更好地对齐数据。为什么重要提供返工的根本原因，支持有针对性地改进流程，减少例外和人工干预。获取位置可能存储在发票挂起表、工作流评论或AP模块中的专用差异记录字段中。示例价格不匹配数量差异采购订单无效
	折扣截止日期 EarlyPaymentDiscountDate	为获得提前付款折扣而支付发票的截止日期。
说明此属性指定支付发票并获得供应商折扣的最后日期。它根据发票日期和付款条件中的折扣部分计算得出，例如“2/10 Net 30”中的“10”。该日期是“Early Payment Discount Status”分析的主要依据。将“Payment Scheduled”或“Payment Executed”日期与此截止日期比较，系统即可判断是否成功获得折扣，并提供财务优化指标。为什么重要定义实现成本节约的目标日期，是付款优先级排序和效率改进的重要依据。获取位置系统根据付款条件和发票日期计算得出，存储在“VendTrans”的“CashDiscDate”等字段中。示例 2023-04-252023-05-102023-06-15
	提前付款折扣金额 EarlyPaymentDiscountAmount	提前支付发票时可能获得的折扣金额。
说明此属性显示供应商根据付款条件为提前付款提供的折扣金额。这项财务数据对“Early Payment Discount Status”仪表板至关重要。它可以量化已获得和错失的折扣价值，为提升发票审批和付款排程效率提供明确的财务依据。为什么重要量化流程效率带来的财务机会，直接体现流程绩效与成本节约之间的关系。获取位置根据发票金额和付款条件计算得出。该值可在“VendTrans”或相关现金折扣字段中获取。示例 30.015.00246.91
	收货单号 GoodsReceiptNumber	与发票相关的收货单据标识。
说明此属性将发票关联到已收货物或服务的记录，这是执行三方匹配（采购订单、收货单和发票）的必要条件。它对于分析匹配效率至关重要。对于“Goods Receipt Matched”活动中的延迟或失败，可使用此标识追溯源收货单据，帮助识别采购或收货环节中影响应付账款的问题。为什么重要支持分析三方匹配效率，并帮助定位源自收货流程的问题。获取位置通过采购订单行关联，通常可在装箱单日记账（“VendPackingSlipJour”）或相关表中找到。示例 GRN-00981GRN-01024
	是否自动执行 IsAutomated	用于标识活动是否由系统自动执行的标志。
说明此布尔属性用于区分人工用户执行的活动与系统自动执行的活动，例如自动过账或匹配发票。分析此属性是衡量AP流程自动化水平的关键。它有助于计算直通处理（STP）率，并识别最适合纳入后续自动化计划的人工活动，最终降低成本和处理时间。为什么重要帮助衡量直通处理率，并识别提升自动化水平和效率的机会。获取位置通过检查活动关联用户是否为系统用户或批处理用户得出，例如“Admin”“BatchUser”。示例 truefalse
	是否返工 IsRework	用于识别发票是否经历过返工的计算标志。
说明如果发票流程包含返工活动，例如“Discrepancy Resolved”，或初次失败后再次执行“Invoice Data Validated”，则此布尔标志设为true。该标志基于整个案例计算。此属性直接支持“Discrepancy Rework Rate”KPI。通过筛选和汇总所有需要额外人工处理的发票，可以简化分析，并量化数据质量问题或流程例外的影响。为什么重要直接衡量返工情况，便于量化和分析流程例外及低效问题。获取位置这不是源系统字段，而是流程挖掘工具通过检查案例中是否出现特定返工活动计算得出。示例 truefalse
	是否逾期付款 IsLatePayment	用于标识付款是否晚于到期日完成的计算标志。
说明如果“Payment Executed”活动发生在“Invoice Due Date”之后，此布尔属性设为true，用于清晰标识案例层面的付款条件不合规情况。此属性简化了“Payment Terms Compliance Rate”KPI的计算。通过筛选和创建仪表板，可以快速展示逾期付款的数量和金额，并对这些案例进行根因分析，了解延迟原因。为什么重要提供清晰、简单的标志，用于分析不合规付款并计算按时付款KPI。获取位置这不是源系统字段，而是流程挖掘工具通过比较“Payment Executed”活动的时间戳与“InvoiceDueDate”属性计算得出。示例 truefalse
	源系统 SourceSystem	提取数据的系统。
说明该属性标识事件数据的来源。在此场景中通常为“Microsoft Dynamics 365”。当合并多个系统的数据时，例如OCR扫描工具和D365的数据，该属性尤为重要。在流程挖掘分析中，它有助于追溯数据来源、排查问题并了解流程的技术环境，也支持将分析筛选为特定系统中的事件。为什么重要提供有关数据来源的关键背景信息，对于数据验证和多系统流程分析至关重要。获取位置通常是在数据提取和转换过程中添加的静态值（“Microsoft Dynamics 365”）。示例 Microsoft Dynamics 365 FinanceD365 F&OAX2012

必需建议可选

应付账款发票处理活动

以下是建议采集的关键流程步骤和重要里程碑，用于准确发现流程并分析绩效。

5 建议 9 可选

	活动	说明
	付款已执行	通过过账付款日记账正式完成付款。该交易用于结清已过账发票形成的负债。
为什么重要这是流程的主要结束事件，用于计算付款条款合规性、识别逾期付款并衡量最终端到端周期时间。获取位置这是付款日记账过账时记录的明确事件。结算明细存储在供应商交易表（VendTrans）中，并将付款与发票关联。采集使用VendTrans中付款结算记录的交易日期。事件类型 explicit
	发票已审批	表示工作流中的最终审批已完成，授权将发票过账并付款。这是发票进入付款阶段前的关键里程碑。
为什么重要标志着审批周期结束。“Submitted for Approval”与该活动之间的时间，是识别审批瓶颈和长期运行工作流的关键KPI。获取位置这是工作流历史日志（WorkflowTrackingStatusTable）中的明确事件，在工作流状态更新为“Completed”或“Approved”时记录。采集从工作流跟踪历史中提取该发票的“Approved”或“Completed”事件。事件类型 explicit
	发票已提交审批	发票正式提交至工作流，由授权人员进行审核和审批。这标志着审批周期开始。
为什么重要这是启动发票审批周期计时的关键里程碑，有助于区分数据录入和匹配耗时与审批延迟。获取位置这是在D365工作流历史日志（WorkflowTrackingStatusTable）中针对特定发票单据记录的明确事件，并会记录提交时间戳。采集从工作流跟踪历史中提取该发票的“Submitted”事件。事件类型 explicit
	发票已登记	表示发票记录通过人工录入、OCR扫描或电子数据交换（EDI）在系统中完成初始创建。这是发票处理生命周期的起点。
为什么重要该活动是流程的主要开始事件。分析从此时到付款的时间，可以得到整体发票周期时间，这是关键绩效指标。获取位置根据待处理供应商发票表或发票日记账表中发票抬头记录的创建时间戳（CreatedDateTime字段）推断，例如VendInvoiceInfoTable。采集使用发票抬头记录的创建时间戳。事件类型 inferred
	发票已过账	已审批发票正式记入总账，形成财务负债。这是一项关键且通常不可逆的会计交易。
为什么重要过账是使发票具备付款资格的重要里程碑，表明所有验证和审批步骤均已完成，并且负债已确认。获取位置这是一个明确的交易事件。过账日期和时间会记录在供应商发票日记账（VendInvoiceJour）及相关总账分录（GeneralJournalEntry）中。采集使用VendInvoiceJour或GeneralJournalEntry表中的过账时间戳。事件类型 explicit
	付款已安排	已过账发票被选中并纳入付款建议或付款日记账，安排在未来的付款批次中处理。这表示企业已计划付款。
为什么重要该活动对于现金流预测和分析付款条款合规性至关重要，也有助于识别是否已考虑并安排提前付款折扣。获取位置根据创建包含该发票的付款日记账行（LedgerJournalTrans）推断。日记账行上的交易日期表示计划付款日期。采集使用引用该发票的付款日记账行创建日期。事件类型 inferred
	付款已清算	公司执行的付款已通过银行清算，并由银行对账流程确认。这是付款完成的最终财务确认。
为什么重要该活动提供现金流出的最终确认。分析付款执行到银行清算之间的时间，对于资金和现金管理十分重要。获取位置该信息来自银行对账模块。当与付款对应的银行对账单行在D365中完成匹配并过账时，可推断该活动已发生。采集需要将银行交易数据（BankStmtISOAccountStatement）关联回原始付款。事件类型 inferred
	发票已取消	发票过账后被作废或取消，通常用于更正错误。这是流程的替代性异常终点。
为什么重要跟踪取消有助于了解流程质量和错误率。取消频率较高，可能表明上游流程存在系统性问题。获取位置这是一个明确事件。D365会创建冲销或贷项交易，并将其关联回原始发票日记账。该冲销交易的过账日期标志着取消发生。采集识别与原始发票关联的冲销交易过账日期。事件类型 explicit
	发票已拒绝	审批人在工作流中拒绝发票，通常会将其退回发起人进行更正或说明，从而启动返工循环。
为什么重要跟踪拒绝有助于识别审批延迟和返工原因，也能揭示发票编码问题、政策违规或文档不完整等情况。获取位置这是工作流历史（WorkflowTrackingStatusTable）中的明确事件，在审批人选择“Reject”操作时记录。采集从工作流跟踪历史中提取该发票的“Rejected”事件。事件类型 explicit
	发票数据已验证	表示系统或用户在匹配或审批前，对采集的发票数据进行完整性和正确性初检。该步骤可以是自动系统验证，也可以是人工审核。
为什么重要跟踪该活动有助于识别由数据质量不佳造成的延迟。此处失败率较高或持续时间较长，通常表明数据采集流程存在问题，例如OCR准确率不足。获取位置这通常是一个推断事件。可以根据发票状态从“New”或“Draft”变更为“Validated”或“Ready for Matching”等状态的时间戳推导，也可以使用提交至工作流前的最后一次修改时间。采集记录表示验证成功的状态变更时间戳，或提交至工作流前的更新事件时间戳。事件类型 inferred
	差异已解决	在识别差异后，解除挂起或更正发票的操作。此时发票已准备好重新提交匹配或审批。
为什么重要该活动会关闭返工循环。解决差异所需的时间，是衡量异常处理流程效率的关键指标。获取位置根据发票移除“On Hold”状态的时间戳，或拒绝后重新提交至工作流的时间戳推断。采集记录解除挂起或拒绝后重新提交发票时的时间戳。事件类型 inferred
	已识别差异	当发票未通过与采购订单或收货记录相关的验证或匹配，需要人工干预时，就会发生该活动。发票通常会被挂起或设置特定状态。
为什么重要该活动会启动返工循环。分析其发生频率和原因，对于识别流程低效、数据质量问题或供应商问题至关重要。获取位置该活动可以明确记录在发票历史中，也可以根据发票因匹配差异而被置为“On Hold”并附带原因代码推断。请查找发票抬头中的状态变更。采集记录发票状态设置为“On Hold”或差异标记被设置时的时间戳。事件类型 inferred
	收货已匹配	在三方匹配中，该活动通过与收货单交叉核对，确认发票所列货物或服务已被接收。此步骤用于核实实际交付与发票是否一致。
为什么重要跟踪该活动有助于分析三方匹配流程的效率，并识别由收货信息缺失或错误造成的延迟。获取位置与PO匹配类似，该活动可根据发票中显示的状态更新推断，确认其已成功与产品收货日记账（Packing Slips）匹配。采集查找表示三方匹配成功的状态变更或标记。事件类型 inferred
	采购订单已匹配	将发票与一个或多个采购订单关联，以核对数量、价格和条款的过程。对于基于PO的发票，这是关键的验证步骤。
为什么重要该活动对于衡量一次匹配成功率和识别匹配流程中的瓶颈至关重要。此处失败往往会导致差异处理循环。获取位置当发票记录中的匹配状态更新为“Matched”，或成功保存行级匹配明细时，可以推断该活动已发生。相关信息通常存储在供应商发票行相关表中。采集识别发票抬头或发票行中与PO匹配成功相关的状态变更。事件类型 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）筛选。除非各法人实体的流程已标准化，否则一次分析多个法人实体的数据可能较为复杂。
安全与权限：执行导出的用户需要具备访问数据管理工作区的安全角色，以及对所有指定数据实体的读取权限。通常需要数据管理管理员角色或经过相应配置的自定义角色。

a 示例查询 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中右键点击结果网格，选择“Save Results As...”，然后选择CSV格式。将文件命名为event_log.csv。
**验证CSV格式：**打开CSV文件，确认包含正确列名的标题行（Invoice、ActivityName、EventTime等）。确保EventTime列采用YYYY-MM-DD HH:MI:SS等标准格式。
**上传到流程挖掘工具：**CSV文件现在可以上传到您的流程挖掘应用程序。按以下方式映射列：Invoice映射到Case ID，ActivityName映射到Activity，EventTime映射到Timestamp。

配置

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

a 示例查询 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限流重试逻辑。进行大规模提取时，建议在业务低峰期运行。

a 示例查询 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 Dynamics 365 访问权限的环境中，于 Microsoft Power Platform 创建或打开数据流。确认所需的 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 的方式合并十四个活动查询。当重复事件代表独立的工作流操作、匹配尝试、更正、付款操作或其他有效源事件时，请予以保留。仅在确认其为源提取产生的技术性重复记录时才删除。
验证 Invoice 已填充，ActivityName 仅包含十四个已批准值，EventTime 已填充且可按时间顺序使用，并在可用时完成推荐属性的映射。检查所有连接是否保留发票案例标识符，且不会无意中增加行数。
将转换后的事件日志加载到已配置的 Power Platform 数据流目标位置，或导出为 CSV 或其他 ProcessMind 支持的格式。确保导出的列名准确为 Invoice、ActivityName、EventTime、UserName、VendorName、InvoiceAmount、InvoiceDueDate、PaymentTerms 和 PurchaseOrderNumber。
将完成的事件日志上传到 ProcessMind，并确认 Invoice 配置为案例标识符，ActivityName 配置为活动，EventTime 配置为事件时间戳。确认可视化仅显示明确提取的行，不依赖 ProcessMind 推导缺失的生命周期事件。

配置

连接：通过Power Platform Dataflows使用原生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 Dataflows访问权限、适用于租户的连接器许可、目标存储或目标位置访问权限，以及读取工作流、会计、付款和银行对账数据的授权。
配置差异：确切的实体名称、状态值、审计可用性、工作流历史保留期限和付款清算数据取决于Dynamics 365应用及组织配置。需要本地映射时，请使用[Configure based on your system]。

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

步骤

配置

a 示例查询 sql

Dynamics 365 Azure SQL数据库直接查询

步骤

配置

a 示例查询 sql

使用Dynamics 365 OData API的Python脚本

步骤

配置

a 示例查询 python

Microsoft Power Platform数据流提取

步骤

配置

a 示例查询 sql

准备开始了吗？

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

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

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

应付账款发票处理属性

应付账款发票处理活动

提取指南

Dynamics 365数据管理框架导出

步骤

配置

a 示例查询 sql

Dynamics 365 Azure SQL数据库直接查询

步骤

配置

a 示例查询 sql

使用Dynamics 365 OData API的Python脚本

步骤

配置

a 示例查询 python

Microsoft Power Platform数据流提取

步骤

配置

a 示例查询 sql

准备开始了吗？

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

我们重视您的隐私