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

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

Microsoft Dynamics 365

← 返回改进指南

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

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

此模板旨在指导您收集分析和优化应付账款发票处理所需的核心数据。它概述了关键属性、核心活动，并提供了提取这些信息的实用指南。使用此模板将确保您捕获全面的事件日志，从而深入洞察您的流程绩效。

推荐收集的 attributes
关键跟踪活动
提取指南

不熟悉事件日志？了解如何创建流程挖掘事件日志.

下载模板

应付账款发票处理属性

这些是建议您在事件日志中包含的数据字段，以便对您的应付账款发票处理进行全面分析，以获得深入洞察。

3 必填 6 推荐 13 可选

	名称	描述
	发票 Invoice	每张供应商发票单据的唯一标识符。
描述发票作为主要的案例标识符，关联了从发票接收到最终付款的所有活动。通过它，可以对每张发票在应付账款流程中的端到端旅程进行分析。在分析中，根据此标识符对事件进行分组是重建每张发票流程的第一步。它能够计算案例层面的KPI（如总周期时间），并有助于识别特定发票处理路径中的变体和瓶颈。为何重要这是追踪发票完整生命周期的关键，是应付账款所有流程挖掘分析的基础。获取方式这通常是 Dynamics 365 Finance 中“供应商发票”页面或相关数据实体中的供应商发票号码。示例 INV-00125475000921DE-8832-2023
	开始时间 EventTime	指示某个活动或事件发生的时间戳。
描述此 attribute 提供每个 activity 的日期和时间，这对于按时间顺序排列 events 并计算持续时间至关重要。它构成了 event log 的时间维度的骨干。在 analysis 中，Start Time 用于计算所有时间相关 metrics，包括 activities 之间的 cycle times、等待时间以及整体 case duration。它对于识别 bottlenecks 以及根据 SLA 衡量 process performance 至关重要。为何重要此时间戳对于正确排序事件以及计算所有绩效指标（例如周期时间与瓶颈）至关重要。获取方式从 Dynamics 365 的交易记录、workflow 历史 logs 或过账日期字段中的创建/修改日期获取。示例 2023-04-15T09:00:12Z2023-04-18T14:35:00Z2023-05-02T11:21:45Z
	活动 ActivityName	已执行的业务流程步骤名称。
描述此 attribute 记录了发票在某个时间点发生的特定 action 或 event，例如“Invoice Registered”或“Invoice Approved”。这些 activities 构成了发现的 process map 中的节点。分析 activities 的顺序和频率是 Process Mining 的基础。它有助于可视化 process flow，识别 common 和 rare paths（variants），并找出导致 delays 或 rework 的 activities。为何重要活动定义了流程中的“做什么”，有助于构建流程图，并分析流程流转及变体。获取方式这通常是从 Dynamics 365 应付账款模块内的状态变更、工作流历史记录或文档过账记录中推导出来的。示例发票已登记发票已批准差异已解决付款已执行
	付款条款 PaymentTerms	与供应商约定的发票付款条款。
描述此 attribute 指明了供应商发票的 payment terms，例如“净30天”（表示30天内到期）或“2/10净30天”（表示10天内付款可享受2% discount，否则30天内到期）。通过分析 payment terms，有助于理解不同 agreements 如何影响支付及时性和现金流。这也是识别 early payment discount 机会、以及对 compliance analysis 进行细分，从而判断某些 terms 是否更难遵守的重要依据。为何重要定义了付款截止日期和折扣机会，直接影响现金流管理和成本节约。获取方式在供应商主数据中或在采购订单/发票抬头中指定。存储在如 'PaymTermId' 等字段中。示例 Net 30Net 602/10 Net 30
	供应商 VendorName	提交发票的供应商名称。
描述此 attribute 标识了与发票关联的 vendor。Vendor data 通常包含 name、ID 和 category 等详细信息。通过按 vendor 分析应付账款 process，可以揭示哪些 suppliers 持续提交 problematic invoices（例如，频繁存在 discrepancies 的 invoices），哪些 suppliers 有 special payment terms，以及哪些 suppliers 的 processing cycles 较长。这些洞察可用于改善 supplier relationships 和 collaboration。为何重要支持对流程进行细分，以识别供应商特定问题，例如频繁差异或付款延迟。获取方式通常与供应商发票表头关联，一般基于供应商账号，从‘VendTable’数据实体获取。示例 Contoso 有限公司Fabrikam IncNorthwind Traders
	发票到期日 InvoiceDueDate	根据付款条款计算出的发票付款到期日。
描述此日期指示了支付发票以避免罚款并遵守 vendor agreements 的截止日期。它通常根据 invoice date 和指定的 payment terms 计算得出。此 attribute 对于“Payment Terms Compliance”analysis 至关重要。通过将“Payment Executed”日期与“Invoice Due Date”进行比较，analysis 可以自动标记 late payments，计算 compliance rates，并有助于优先处理付款以保持良好的 vendor relationships。为何重要对于衡量准时付款绩效、管理供应商关系以及避免滞纳金至关重要。获取方式基于发票日期和付款条款的计算字段。存储在供应商交易表（例如“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	与发票关联的采购订单的唯一标识符。
描述此 attribute 将发票与其对应的 purchase order (PO) 关联起来。发票可能关联 purchase order (PO)，也可能不关联 PO。分析此 attribute 有助于区分基于 PO 和非基于 PO 的 invoice processing，这两种情况通常遵循不同的 workflows。它对于衡量 PO matching efficiency 以及识别发票详情与 purchase order 不一致时出现的问题至关重要。为何重要区分采购订单（PO）发票和非采购订单发票，两者遵循不同的处理流程，这对于分析匹配效率至关重要。获取方式在供应商发票抬头或行项目中找到，通常位于 'VendInvoiceInfoTable' 表中名为 'PurchId' 的字段。示例 PO-000432PO-000511
	公司代码 CompanyCode	处理该发票的法人实体标识符。
描述此 attribute 标识了组织内负责该发票的特定 company 或 legal entity。在 multi-company setups 中，这是一个关键的 organizational dimension。通过按 Company Code 分析应付账款 process，可以对不同 business units 或 legal entities 之间进行 performance comparison。它有助于识别某些 entities 是否 efficiency 较低、rework rates 较高或遵循不同的 process variants，从而突出 standardization 和 best-practice sharing 的机会。为何重要支持在组织内部跨不同法人实体或业务部门进行绩效基准测试和流程对比。获取方式 Dynamics 365 中几乎所有交易表上的一个标准字段，通常名为 'DataAreaId'。示例 USMFDEMFGBSI
	最后数据更新 LastDataUpdate	源系统上次数据刷新的时间戳。
描述此 attribute 指示 event log 最后一次从 Microsoft Dynamics 365 更新新数据的时间。它提供了所分析 data 的时效性信息。对于 dashboards 和持续监控而言，此 timestamp 对于用户理解他们是否正在查看最新的 process data 至关重要。它有助于管理关于 data freshness 的预期，并有助于监控 data pipeline 的健康状况。为何重要告知用户数据的时效性，这对于根据分析做出及时准确的业务决策至关重要。获取方式这是一个元数据字段，由数据提取、转换和加载 (ETL) 工具在数据摄取时生成并标记。示例 2023-06-01T02:00:00Z2023-06-02T02:00:00Z
	发票处理时长 InvoiceProcessingTime	发票从接收到支付的总周期时间。
描述此指标代表一张发票案例的总耗时，通常从首个活动（例如“发票已登记”）的时间戳计算至最终活动（例如“付款已执行”）的时间戳。这是衡量整体流程效率的主要关键绩效指标（KPI）（“平均发票周期时间”）。分析此持续时间有助于识别系统性延误，并为流程改进举措提供高层基准。它还可以按供应商或公司代码等维度进行切片，以找出周期时间最长的区域。为何重要直接衡量端到端流程效率，并作为监控应付账款整体健康状况的关键绩效指标。获取方式这并非源系统字段。它是在流程挖掘工具中，通过计算案例中第一个和最后一个事件时间戳的差值来计算的。示例 15天4小时3天11小时32天1小时
	发票状态 InvoiceStatus	发票的当前处理状态。
描述此 attribute 反映了发票在 process 中的最新已知状态，例如“In Progress”、“Approved”、“Paid”或“Cancelled”。它提供了发票在其生命周期中位置的快照。尽管 Process Mining 从 activities 中推导出 flow，但拥有最终 status 对于 validation 和创建 business-level dashboards 以总结所有 open invoices 的当前状态非常有用。它可用于 filter 所有当前“Approved”但尚未“Paid”的发票。为何重要提供发票当前状态的高层级概述，有助于筛选和创建基于状态的 dashboard。获取方式通常源自应付账款模块中的文档状态或工作流状态字段。示例进行中已审批已支付已取消
	审批人 ApproverName	在特定步骤批准或拒绝该发票的用户。
描述此 attribute 用于识别 workflow 中负责审批 decision 的个人。对于 multi-level approvals 的发票，不同 steps 可能由不同的 approver 负责。它对“发票审批周期时间分析”dashboard 至关重要。通过按 approver 进行 analysis，可以识别因 workload 或其他 factors 而可能成为流程 bottlenecks 的个人。这有助于采取有针对性的干预措施，以加快 approval cycle。为何重要支持对审批流程进行详细分析，有助于识别个人或团队层面的瓶颈。获取方式从工作流历史日志（'WorkflowTrackingStatusTable'）中提取，该日志记录了完成每个审批步骤的用户。示例陈大卫Maria GarciaAP_Manager_Group
	差异原因 DiscrepancyReason	发票差异的原因代码或描述。
描述当发票被搁置或发现差异时，此属性会记录具体原因，例如“价格不匹配”、“数量差异”或“缺少收货单”等。此属性对于“差异解决与返工”分析至关重要。通过根据原因对返工情况进行分类，能够帮助分析找出流程低效的根本原因。例如，若“价格不匹配”是最常见的原因，则意味着采购部门与供应商之间需要更好地进行数据同步和对齐。为何重要提供返工的根本原因，从而实现有针对性的流程改进，以减少异常情况和人工干预。获取方式可能存储在发票挂起表、工作流备注或应付账款模块中的特定差异记录字段。示例价格不符数量差异无效采购订单
	折扣到期日 EarlyPaymentDiscountDate	享受提前付款折扣的发票支付截止日期。
描述此 attribute 指定了发票为获得 vendor 提供的 discount 而必须支付的最后日期。它源自 invoice date 和 payment terms 的 discount 期限部分（例如，“2/10 Net 30”中的“10”）。此日期是“Early Payment Discount Status”analysis 的主要驱动因素。通过将“Payment Scheduled”或“Payment Executed”日期与此截止日期进行比较，system 可以确定 discount 是否成功获得，从而提供明确的财务优化 metrics。为何重要定义了实现成本节约的目标日期，使其成为付款优先级和效率提升的关键驱动因素。获取方式系统根据付款条款和发票日期计算。存储在“VendTrans”表中的“CashDiscDate”等字段。示例 2023-04-252023-05-102023-06-15
	提前付款折扣金额 EarlyPaymentDiscountAmount	如果发票提前支付可获得的潜在折扣金额。
描述此 attribute 显示了 vendor 根据 payment terms 提供的 early payment discount 金额。此 financial data 对于“Early Payment Discount Status”dashboard 至关重要。它允许企业量化已获得的 discounts 与错失的 discounts 的价值，为提高 invoice approval 和 payment scheduling processes 的 efficiency 提供了明确的财务激励。为何重要量化流程效率带来的财务机会，直接关联流程绩效与成本节约。获取方式基于发票金额和付款条款的计算字段。此值可在“VendTrans”等表或相关的现金折扣字段中获取。示例 30.015.00246.91
	收货单号 GoodsReceiptNumber	与发票相关的收货凭证标识符。
描述此 attribute 将发票关联到收到的 goods 或 services 记录，这是进行 three-way matching（PO、Goods Receipt 和 Invoice）的必要条件。它对于分析流程的 matching efficiency 至关重要。“Goods Receipt Matched” activities 中的 delays 或 failures 可以使用此标识符进行追溯，以查明原始收货凭证，有助于识别 procurement 或 receiving steps 中影响应付账款的问题。为何重要有助于分析三方匹配效率，并帮助查明源自收货流程的问题。获取方式通过采购订单行关联，通常可在发货单日记账（‘VendPackingSlipJour’）或相关表格中找到。示例 GRN-00981GRN-01024
	是否自动化 IsAutomated	一个标志，指示该活动是否由系统自动执行。
描述此 boolean attribute 区分了由人工执行的 activities 和由 system automation 执行的 activities，例如自动发票过账或 matching。分析此 attribute 是衡量应付账款 process 自动化水平的关键。它有助于计算 Straight-Through Processing (STP) rate，并识别哪些手动 activities 是未来自动化举措的主要候选对象，最终达到降低成本和处理时间的目的。为何重要有助于衡量直通处理率，并识别提高自动化和效率的机会。获取方式通过检查与活动关联的用户是否为系统或批处理用户（例如，“Admin”、“BatchUser”）来推导。示例真false
	是否返工 IsRework	一个计算出的标记，用于识别发票是否经历过返工。
描述如果发票的 process flow 包含 rework activities，例如“Discrepancy Resolved”或在初始 failure 后的第二次“Invoice Data Validated”步骤，此 boolean flag 将被设置为 true。它在整个 case 中计算。此 attribute 直接支持“Discrepancy Rework Rate”KPI。它通过 easy filtering 和 aggregation 所有需要额外人工操作的发票来简化 analysis，有助于量化不良 data quality 或 process exceptions 的影响。为何重要直接衡量返工量，便于对流程异常和低效进行简单量化和分析。获取方式这并非源系统字段。它是在流程挖掘工具中，通过检查案例中是否存在特定的返工指示性活动来计算的。示例真false
	是否逾期支付 IsLatePayment	一个计算出的标记，表示付款是否逾期。
描述此 boolean attribute 在“Payment Executed” activity 发生在“Invoice Due Date”之后时被设置为 true。它提供了不符合 payment terms 情况的清晰案例级别指标。此 attribute 简化了“Payment Terms Compliance Rate”KPI 的计算。它支持 quick filtering 和 dashboard 展示，以显示逾期付款的数量和金额，并对这些具体 cases 进行根本原因分析，以了解其延迟的原因。为何重要提供清晰简明的标记，用于分析不合规付款并计算准时付款 KPI。获取方式这并非源系统字段。它是在流程挖掘工具中，通过比较“付款执行”活动的时间戳与“发票到期日”属性来计算的。示例真false
	源系统 SourceSystem	数据提取来源系统。
描述此 attribute 标识了 event data 的来源，在此上下文中通常是“Microsoft Dynamics 365”。当来自多个 systems（例如OCR scanning tool和D365）的 data 结合时，此 attribute 变得尤为重要。在 Process Mining analysis 中，它有助于追踪 data lineage、进行 troubleshooting，并理解流程的技术概况。通过此 attribute，可以过滤 analysis，只关注来自特定 system 的 event。为何重要提供关于 data 来源的重要背景信息，这对于 data 验证以及多系统流程分析至关重要。获取方式这通常是一个静态值（'Microsoft Dynamics 365'），在数据提取和转换过程中添加。示例 Microsoft Dynamics 365 FinanceD365 F&OAX2012

必填推荐可选

应付账款发票处理活动

这些是您在事件日志中应记录的关键流程步骤和重要里程碑，以便准确地进行流程发现和绩效分析。

5 推荐 9 可选

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

推荐可选

提取指南

如何从 Microsoft Dynamics 365 获取您的数据

步骤

访问数据管理工作区： 登录 Microsoft Dynamics 365 Finance and Operations。导航至主 dashboard，搜索或选择 Data management（数据管理）工作区。
创建新的导出项目： 在 Data management 工作区中，选择 Export（导出）磁贴。点击 New（新建）创建新的导出项目。提供一个有意义的名称（例如 AP_Invoice_Event_Log_Export）和描述。
添加所需数据实体： 在项目详情中，点击 Add entity（添加实体）。搜索并添加以下标准数据实体。您可能需要逐一添加：
- Vendor invoice headers (供应商发票头) (VendInvoiceInfoTable)
- Vendor posted invoices (已过账供应商发票) (VendInvoiceJour)
- Vendor transactions (供应商交易) (VendTrans)
- Vendor settlement transactions (供应商结算交易) (VendSettlement)
- Workflow tracking status (工作流跟踪状态) (WorkflowTrackingStatusTable)
- Payment journal lines (付款日记账行) (LedgerJournalTrans)
- Bank transactions (银行交易) (BankAccountTrans)
设置导出格式： 对于每个添加的实体，指定 Target data format（目标数据格式）。CSV 因其简洁性和与大多数数据转换工具的兼容性而被推荐使用。确保在首次运行时将“Skip staging”（跳过暂存）选项设置为“No”，以便进行筛选和审查。
应用筛选器和日期范围： 选中每个实体并点击 Filter（筛选）。务必根据您要分析的法人实体 (Company) 进行筛选。在主要日期字段（例如，Vendor invoice headers 上的 Created date and time）上应用日期范围筛选器，将数据量限制在可管理的时间范围内，例如最近 6 个月。
配置导出作业： 保存项目配置。您现在可以通过点击操作窗格中的 Export（导出）按钮手动运行导出。对于定期提取，使用 Create recurring data job（创建定期数据作业）选项来安排导出自动运行（例如，每周）。
下载导出的文件： 作业完成后，导航到作业历史记录。您可以从那里下载软件包，它将是一个 ZIP 文件，其中包含每个数据实体的独立 CSV 文件。
合并和转换数据： 解压下载的软件包。使用 Power BI (Power Query)、Python 脚本或数据库（例如 SQL Server）等外部工具加载各个 CSV 文件。应用下面 Query 部分提供的转换逻辑，将这些文件连接起来，并将数据透视成所需的 event log（事件日志）格式（每个活动一行）。
生成最终 event log 文件： 应用转换逻辑后，将结果表导出为单个 CSV 文件。确保列标题与所需属性匹配：Invoice（发票）、ActivityName（活动名称）、EventTime（事件时间）、UserName（用户名）、VendorName（供应商名称）、InvoiceAmount（发票金额）、InvoiceDueDate（发票到期日）、PaymentTerms（付款条款）、PurchaseOrderNumber（采购订单号）。
上传至 Process Mining 工具： 最终的 CSV 文件现已准备好上传到您的 Process Mining（流程挖掘）应用程序。

配置

核心数据实体：数据抽取需基于一组标准实体。其中最关键的实体有：Vendor invoice headers（核心发票信息）、Vendor posted invoices（已过账发票数据）、Workflow tracking status（审批事件）以及Vendor transactions（支付信息）。
导出格式：目标数据格式请选用 CSV。CSV 格式轻量，并受到数据转换工具的广泛支持。
增量导出与全量导出：初次分析时，建议针对特定的历史时期执行一次全量导出。而日常监控则可配置周期性任务。数据管理框架支持增量导出，但这需要对实体进行细致的变更追踪配置。
日期范围筛选：务必设置日期范围筛选，以控制数据集大小。常见做法是抽取3-6个月的数据，既能进行有意义的分析，又不会给系统带来过重负担。筛选条件请使用Vendor invoice headers实体中的createdDateTime字段。
公司（法人实体）筛选：务必根据公司（DataAreaId）进行筛选。除非流程在多个法人实体间已标准化，否则同时分析来自不同法人实体的数据会非常复杂。
安全与权限：执行导出的用户需具备安全角色，该角色应拥有数据管理工作区的权限，并对所有指定数据实体具备读取权限。通常需要Data management administrator等角色，或配置相应的自定义角色。

a 查询示例 config

`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 查询：在 SQL 客户端中新建查询窗口，将 query 部分提供的 T-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

`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 admin center（Azure Active Directory 管理中心）。
- 前往 App registrations（应用注册）并创建新的注册。
- 在 API permissions（API 权限）下，为 Dynamics ERP 添加权限并选择 user_impersonation（委托权限）。
- 在 Certificates & secrets（证书和机密）下创建新的 client secret（客户端机密）并妥善存储其值。
- 记下 Application (client) ID（应用程序（客户端）ID）和 Directory (tenant) ID（目录（租户）ID）。
先决条件：在 Dynamics 365 中创建应用程序用户
- 在 Dynamics 365 F&O 中，导航到 System administration > Users > Users（系统管理 > 用户 > 用户）。
- 创建新用户并设置 User ID（用户 ID）和 User name（用户名）。将 provider（提供程序）设置为您的 Azure AD tenant（租户）（例如 https://sts.windows.net/[your_tenant_id]）。
- 将 Azure AD 应用程序注册中的 Application (client) ID（应用程序（客户端）ID）输入到 Client ID（客户端 ID）字段中。
- 为该用户分配适当的 security roles（安全角色）（例如，具有供应商发票、付款和工作流历史记录读取权限的角色）。
设置本地 Python 环境
- 确保您已安装 Python 3.7 或更高版本。
- 在您的终端中运行以下命令以安装所需的库：pip install requests msal pandas
配置 Python 脚本
- 将提供的 Python script（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 等）的源字段映射到所需的 event log（事件日志）结构（Invoice、ActivityName、EventTime 等）。
- 所有单个活动数据都将合并到一个主 event log 中。
数据排序与清理
- 合并后的 event log 将为每张发票按时间顺序排序，以确保事件的正确顺序。
- 基本的数据类型转换由 pandas 库处理。
导出最终 event log
- 该脚本将最终整合的 event log 保存为 d365_ap_event_log.csv 文件，位于同一目录中。
- 该文件现在已格式化，可上传到 Process Mining（流程挖掘）工具，例如 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 中哪个法人实体（公司）抽取数据。
工作流配置：脚本假定工作流历史中包含特定的活动名称（例如，“批准供应商发票”，“解决差异”）。您可能需要根据组织的具体工作流配置，调整脚本中的筛选逻辑。
性能：对于超大型数据集，OData API 的响应速度可能会较慢。脚本包含分页处理机制，但缺乏针对 API 流量限制的高级重试逻辑。因此，建议在非高峰时段执行大量数据抽取操作。

a 查询示例 python

`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 Finance and Operations。导航至主 dashboard，搜索或选择 Data management（数据管理）工作区。
创建新的导出项目： 在 Data management 工作区中，选择 Export（导出）磁贴。点击 New（新建）创建新的导出项目。提供一个有意义的名称（例如 AP_Invoice_Event_Log_Export）和描述。
添加所需数据实体： 在项目详情中，点击 Add entity（添加实体）。搜索并添加以下标准数据实体。您可能需要逐一添加：
- Vendor invoice headers (供应商发票头) (VendInvoiceInfoTable)
- Vendor posted invoices (已过账供应商发票) (VendInvoiceJour)
- Vendor transactions (供应商交易) (VendTrans)
- Vendor settlement transactions (供应商结算交易) (VendSettlement)
- Workflow tracking status (工作流跟踪状态) (WorkflowTrackingStatusTable)
- Payment journal lines (付款日记账行) (LedgerJournalTrans)
- Bank transactions (银行交易) (BankAccountTrans)
设置导出格式： 对于每个添加的实体，指定 Target data format（目标数据格式）。CSV 因其简洁性和与大多数数据转换工具的兼容性而被推荐使用。确保在首次运行时将“Skip staging”（跳过暂存）选项设置为“No”，以便进行筛选和审查。
应用筛选器和日期范围： 选中每个实体并点击 Filter（筛选）。务必根据您要分析的法人实体 (Company) 进行筛选。在主要日期字段（例如，Vendor invoice headers 上的 Created date and time）上应用日期范围筛选器，将数据量限制在可管理的时间范围内，例如最近 6 个月。
配置导出作业： 保存项目配置。您现在可以通过点击操作窗格中的 Export（导出）按钮手动运行导出。对于定期提取，使用 Create recurring data job（创建定期数据作业）选项来安排导出自动运行（例如，每周）。
下载导出的文件： 作业完成后，导航到作业历史记录。您可以从那里下载软件包，它将是一个 ZIP 文件，其中包含每个数据实体的独立 CSV 文件。
合并和转换数据： 解压下载的软件包。使用 Power BI (Power Query)、Python 脚本或数据库（例如 SQL Server）等外部工具加载各个 CSV 文件。应用下面 Query 部分提供的转换逻辑，将这些文件连接起来，并将数据透视成所需的 event log（事件日志）格式（每个活动一行）。
生成最终 event log 文件： 应用转换逻辑后，将结果表导出为单个 CSV 文件。确保列标题与所需属性匹配：Invoice（发票）、ActivityName（活动名称）、EventTime（事件时间）、UserName（用户名）、VendorName（供应商名称）、InvoiceAmount（发票金额）、InvoiceDueDate（发票到期日）、PaymentTerms（付款条款）、PurchaseOrderNumber（采购订单号）。
上传至 Process Mining 工具： 最终的 CSV 文件现已准备好上传到您的 Process Mining（流程挖掘）应用程序。

配置

核心数据实体：数据抽取需基于一组标准实体。其中最关键的实体有：Vendor invoice headers（核心发票信息）、Vendor posted invoices（已过账发票数据）、Workflow tracking status（审批事件）以及Vendor transactions（支付信息）。
导出格式：目标数据格式请选用 CSV。CSV 格式轻量，并受到数据转换工具的广泛支持。
增量导出与全量导出：初次分析时，建议针对特定的历史时期执行一次全量导出。而日常监控则可配置周期性任务。数据管理框架支持增量导出，但这需要对实体进行细致的变更追踪配置。
日期范围筛选：务必设置日期范围筛选，以控制数据集大小。常见做法是抽取3-6个月的数据，既能进行有意义的分析，又不会给系统带来过重负担。筛选条件请使用Vendor invoice headers实体中的createdDateTime字段。
公司（法人实体）筛选：务必根据公司（DataAreaId）进行筛选。除非流程在多个法人实体间已标准化，否则同时分析来自不同法人实体的数据会非常复杂。
安全与权限：执行导出的用户需具备安全角色，该角色应拥有数据管理工作区的权限，并对所有指定数据实体具备读取权限。通常需要Data management administrator等角色，或配置相应的自定义角色。

a 查询示例 config

`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;
`

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

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

应付账款发票处理属性

应付账款发票处理活动

提取指南

Dynamics 365 数据管理框架导出

步骤

配置

a 查询示例 config

Dynamics 365 Azure SQL 数据库直接查询

步骤

配置

a 查询示例 sql

使用 Dynamics 365 OData API 的 Python 脚本

步骤

配置

a 查询示例 python

Microsoft Power Platform Dataflows数据提取

步骤

配置

a 查询示例 config

准备好开始了吗？

阻止重复付款。优化应付发票处理。