应付账款付款处理数据模板

NetSuite

← 返回改进指南

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

应付账款付款处理数据模板

此模板为梳理NetSuite应付账款工作流提供了完整框架，列出关键数据属性、核心流程活动和专门的数据提取说明，帮助您获得准确且可用于行动的分析结果。

深入分析建议使用的属性
需要跟踪的关键流程节点
NetSuite数据提取指南

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

下载模板

应付账款付款处理属性

以下是建议纳入事件日志的数据字段，便于您在NetSuite环境中全面分析应付账款付款处理流程。

5 必填 10 建议填写 6 选填

	名称	说明
	事件时间 EventTime	活动发生的时间戳。
说明活动发生的准确日期和时间。此字段用于按顺序排列事件，并计算各步骤之间的时长。准确的时间戳对于识别瓶颈至关重要。它们可用于计算周期时间，例如审批耗时，或发票处于冻结状态的时长。重要性用于排列事件顺序和计算所有基于时间的KPI，是必需数据。获取途径系统备注中的“date”字段示例 2023-10-01T14:30:00Z2023-10-02T09:15:00Z
	最后更新时间 LastDataUpdate	数据提取或刷新的时间戳。
说明表示数据最近一次从源系统提取到流程挖掘平台的时间，帮助用户了解分析数据的时效性。此字段用于判断仪表板中的数据是实时状态，还是之前某个时间段的快照。这对于信任“逾期天数”的计算结果至关重要。重要性了解数据时效性和同步状态时必不可少。获取途径 ETL时间戳示例 2023-11-01T00:00:00Z
	发票编号 InvoiceNumber	供应商发票的唯一标识符。
说明此属性表示NetSuite中分配给供应商账单的唯一参考编号，是应付账款流程的唯一案例标识符，可将与特定付款义务相关的所有活动归为一组。在分析中，此字段是流程实例的主键。分析人员可以据此追踪负债的完整生命周期，从收到并记录发票，直到付款最终结清。系统会根据此标识符汇总不同变体，以确定常见流程路径。重要性这是必填的案例ID，用于关联所有事件并构成一个流程案例。获取途径交易表中类型为“Vendor Bill”的“tranid”字段示例 INV-2023-001VB-99822ACME-10299982311
	活动 Activity	执行的流程步骤或事件名称。
说明此属性记录特定时间点发生的操作或状态变更。例如“发票已创建”“审批状态已变更”或“付款已处理”。这些数据对于重建流程图至关重要。按时间顺序排列每个发票编号对应的活动后，流程挖掘工具便可呈现实际工作流，并识别循环、返工以及偏离理想路径的情况。重要性定义流程图中的步骤，是开展流程挖掘分析的必需数据。获取途径系统备注或工作流历史日志示例发票已创建发票已批准已安排付款付款已结清
	源系统 SourceSystem	数据来源系统的名称。
说明标识提取事件数据的软件应用。在此场景中，该值通常固定为“NetSuite”。在多系统环境中，此属性有助于追踪数据来源。虽然此视图聚焦于NetSuite，保留该字段仍可确保与企业级数据模型兼容。重要性数据血缘追踪及区分多系统环境中的数据时必不可少。获取途径提取时硬编码示例 NetSuiteNetSuite OneWorld
	付款日期 PaymentDate	执行付款的日期。
说明与账单关联的供应商付款交易中记录的日期，表示应付账款周期的完成时间。此字段用于计算“端到端付款周期时间”。将此日期与“InvoiceDate”比较，即可得出流程总耗时。重要性主要流程周期的结束时间戳。获取途径关联的供应商付款交易日期示例 2023-11-202023-12-05
	供应商名称 VendorName	开具发票的供应商名称。
说明此属性包含与供应商账单关联的实体名称，对于分析供应链各环节的表现至关重要。分析时，此字段用于支持“供应商付款周期差异”仪表板。分析人员可按供应商对案例分组，找出经常导致匹配差异或采用非标准付款条款的供应商。重要性按供应商分组分析、查找特定供应商造成的瓶颈时必不可少。获取途径与Vendor记录关联的Transaction“entity”字段示例 Office DepotOracleFedEx
	到期日 DueDate	必须向供应商付款的日期。
说明此属性根据发票条款标明合同约定的付款期限，是计算按时付款率的基准。此字段是“按时付款率”KPI的核心组成部分。系统将“付款执行日期”与此字段进行比较，标记逾期付款并帮助估算潜在罚金。重要性计算按时付款率和识别逾期负债的基础。获取途径 Transaction“duedate”字段示例 2023-11-152023-12-01
	发票金额 InvoiceAmount	发票的总金额。
说明供应商账单的总金额，通常以本位币或交易币种计价。此指标可用于分析财务影响。此字段用于“逾期付款与罚金跟踪”仪表板，量化延迟付款带来的现金流风险，也可用于优先处理金额较高、需要人工介入的发票。重要性量化流程低效造成的财务影响时必不可少。获取途径 Transaction“usertotal”或“foreignamount”字段示例 1500.00250.5010000.00
	子公司 Subsidiary	接收发票的公司子公司或法人实体。
说明在NetSuite OneWorld环境中，此属性用于标识发票所属的具体法人实体（子公司），便于比较不同组织的表现。此属性用于生成“端到端付款周期时间”仪表板，比较不同业务单元的效率，并帮助找出表现不佳的特定地区或实体。重要性多实体组织比较各业务单元表现时至关重要。获取途径 Transaction“subsidiary”字段示例美国东部欧洲、中东和非洲总部亚太销售部
	审批状态 ApprovalStatus	发票当前的工作流审批状态。
说明表示发票处于待审批、已批准或已拒绝状态。此状态决定工作流的流转逻辑。此字段是“审批工作流合规审计”的核心。状态变更会生成活动事件，最终状态则决定发票是否可以付款。重要性跟踪发票通过审批关卡的进度。获取途径 Transaction“approvalstatus”字段示例待审批已批准已拒绝
	是否为非PO发票 IsNonPO	标记发票是否未关联采购订单。
说明根据是否关联采购订单生成的布尔属性，用于区分有PO支持的发票和直接FI发票。此属性用于“非PO发票处理速度”仪表板，突出显示人工处理发票与三方匹配流程相比的效率差异。重要性用于分析流程变体的高层级分类。获取途径根据“PurchaseOrderNumber”为空值推导示例 truefalse
	用户 User	执行该活动的员工。
说明标识负责特定事件的人员，例如审批发票或解除付款冻结的人员。此属性用于“审批工作流瓶颈监控”，以识别特定人员造成的延误，帮助管理者平衡工作量并确定团队成员的培训需求。重要性进行资源分析和识别用户层面的瓶颈时必不可少。获取途径 System Notes“name”或Transaction“employee” 示例 John Doe财务管理员系统
	部门 Department	与发票关联的内部部门。
说明按承担费用的成本中心或内部部门（如IT、市场部）对发票分类。此属性支持按部门分析周期时间，帮助识别哪些部门在发票编码或审批方面较慢，从而有针对性地改进流程。重要性支持从组织层面分析流程效率。获取途径 Transaction“department”字段示例 IT市场部制造部
	采购订单编号 PurchaseOrderNumber	相关采购订单的参考编号。
说明将供应商账单关联到对应的采购订单（PO）。如果此字段为空，则该发票被视为非PO发票。此属性对于“非PO发票处理速度”仪表板至关重要。分析时可将流程变体分为有PO支持和非PO（FI）发票，以比较效率和合规情况。重要性区分基于PO和非PO的发票处理方式时必不可少。获取途径 Transaction“createdfrom”字段示例 PO-8821PO-1002null
	付款冻结 PaymentHold	表示发票当前是否被禁止付款。
说明用于标记发票是否已被冻结付款。冻结期间，发票不会纳入付款批次。此属性支持“付款冻结影响分析”仪表板。分析此字段为“true”的持续时间，可发现由争议或信息缺失造成的处理延误。重要性分析发票停滞或延误原因时至关重要。获取途径 Transaction“paymenthold”字段示例 truefalse
	付款条款 PaymentTerms	约定的付款时间和折扣条件。
说明指定付款条件，例如“Net 30”或“2% 10 Net 30”。这些信息有助于解释“到期日”并判断是否有提前付款折扣。此属性用于“提前付款折扣获取率”仪表板，可识别符合折扣条件的发票，并帮助分析折扣损失是否与特定限制性条款有关。重要性说明到期日的依据，并识别折扣机会。获取途径 Transaction“terms”字段示例 Net 302% 10 Net 30收到后付款
	发票日期 InvoiceDate	供应商发票上注明的日期。
说明供应商提供的单据日期，通常是计算账龄的起点。此字段用于计算“端到端处理周期时间”。该日期与NetSuite中的创建日期之间的间隔，可能反映扫描或收发室处理延误。重要性标记供应商时间线的正式起点。获取途径 Transaction“trandate”字段示例 2023-10-012023-10-05
	可用折扣 DiscountAvailable	提前付款可享受的潜在折扣金额。
说明根据发票金额和付款条款计算的提前付款折扣金额，代表潜在节省。此属性是“提前付款折扣获取率”仪表板的核心。分析人员可汇总逾期付款对应的折扣金额，计算错失的总节省额。重要性量化加快流程所带来的财务收益。获取途径根据Terms和InvoiceAmount计算示例 30.000.0015.50
	币种 Currency	开具发票所用的币种。
说明表示交易币种的ISO代码，对跨国组织尤为重要。此字段用于“发票金额”分析中的财务金额标准化，也有助于判断外汇处理是否延长了“付款结清”活动的耗时。重要性确保财务汇总和外汇分析准确时必不可少。获取途径 Transaction“currency”字段示例 USDEURGBP
	逾期天数 DaysOverdue	付款日期晚于到期日的天数。
说明此计算指标用于衡量付款相对于到期日的延迟天数。负值表示提前付款。此属性用于“逾期付款与罚金跟踪”仪表板，对于量化逾期付款的严重程度和识别长期逾期模式至关重要。重要性分析付款合规情况和罚金风险的关键指标。获取途径计算方式：PaymentDate - DueDate 示例 50-2

必填建议填写选填

应付账款付款处理活动

以下是建议记录在事件日志中的关键流程步骤和节点，可帮助您准确还原付款处理的完整周期。

5 建议填写 9 选填

	活动	说明
	付款已处理	Bill Payment记录已成功过账，发票状态变为Paid in Full或Partially Paid。总账中的负债随之冲销。
重要性这是付款流程的主要完成节点，用于计算“端到端处理周期时间”。获取途径交易表。查找“Bill Payment”记录的创建情况，或状态变为“Deposited”（支票）或“Paid”的记录。采集执行Bill Payment交易时记录事件类型 explicit
	付款已结清	在银行对账过程中，Bill Payment被标记为已结清。这表示资金已从银行账户转出。
重要性为现金流分析提供实际财务结束日期，与会计付款日期区分开来。获取途径交易行。Bill Payment交易行中的“cleared”标记被设为True。采集执行交易X时记录事件类型 explicit
	发票已创建	在NetSuite中首次创建供应商账单记录。这是流程的起点，表示发票已录入ERP。
重要性为整个应付账款周期和账龄计算设定基准时间戳。获取途径交易表。筛选记录类型为Vendor Bill的记录，并提取datecreated或trandate字段。采集执行Vendor Bill交易时记录事件类型 explicit
	发票已匹配采购订单	表示供应商账单是根据现有采购订单创建的，或与其关联。这有助于区分有采购订单支持的发票和无采购订单发票。
重要性对于分析采购政策的遵循情况，以及比较有采购订单和无采购订单发票的周期时间至关重要。获取途径交易表。检查供应商账单记录中的“createdfrom”字段。如果该字段填写了采购订单ID，则表示此活动已发生。采集比较状态字段变更前后的值事件类型 inferred
	发票已批准	供应商账单状态变为Open，表示该账单已获准付款，并已影响总账。这是付款前的重要节点。
重要性结束审批周期，并使发票进入付款安排阶段。对于衡量审批效率至关重要。获取途径系统备注。查找“approvalstatus”字段变为“2”（Approved）或状态变为“Open”的记录。采集比较状态字段变更前后的值事件类型 inferred
	付款已作废	此前已处理的Bill Payment被作废，供应商账单重新打开。这属于重大异常和流程失败。
重要性对于审计合规和识别付款失败原因（例如支票打印错误、银行拒付）至关重要。获取途径 Bill Payment的系统备注。状态变为“Voided”。采集执行交易X时记录事件类型 explicit
	发票已拒绝	供应商账单退回提交人进行更正，或被拒绝。这表示审批工作流出现循环或终止。
重要性较高的拒绝率可能表明流程受阻、培训不足，或上游数据质量存在问题。获取途径系统备注。查找“approvalstatus”字段变为“3”（Rejected）的记录。采集比较状态字段变更前后的值事件类型 inferred
	发票科目已修改	发票行项目的总账费用科目发生变更。这表示编码错误已更正，或科目已重新分类。
重要性直接衡量“发票编码返工率”KPI，并揭示会计编码环节的效率损失。获取途径系统备注。查找Transaction Line子列表中“account”字段的变更。采集执行交易X时记录事件类型 explicit
	发票金额已修改	供应商账单总金额在初次创建后发生变更。这可能意味着返工、数据录入错误或差异处理。
重要性表示存在人工干预和返工，会对直通处理率产生负面影响。获取途径系统备注。查找供应商账单记录中“total”或“usertotal”字段的变更。采集执行交易X时记录事件类型 explicit
	已冻结付款	供应商账单上的Payment Hold复选框被设为True。这会阻止该发票进入付款批次。
重要性找出因争议或现金流管理决策而停滞的发票，识别其中的瓶颈。获取途径系统备注。查找“paymenthold”字段从“F”变为“T”的记录。采集执行交易X时记录事件类型 explicit
	已安排付款	已创建Bill Payment记录，状态为“Pending Approval”。这通常发生在创建电子银行付款（EFT）批次时。
重要性区分付款意向与资金实际支付，揭示付款授权环节的延误。获取途径交易表。创建与账单关联的“Bill Payment”记录，状态为“Pending Approval”。采集比较状态字段变更前后的值事件类型 inferred
	已应用供应商贷项	供应商贷项已应用于供应商账单，从而减少应付金额。这是现金付款之外的另一种处理方式。
重要性说明付款金额为何可能与发票金额不同，并确保负债得到正确结清。获取途径交易关联。查找与供应商账单关联的“Vendor Credit”类型交易。采集比较状态字段变更前后的值事件类型 inferred
	已解除付款冻结	Payment Hold复选框被设为False，发票因此可以进入付款处理流程。这标志着冻结期结束。
重要性用于计算“付款冻结时长”KPI，并了解争议发票的解决时间。获取途径系统备注。查找“paymenthold”字段从“T”变为“F”的记录。采集执行交易X时记录事件类型 explicit
	已请求审批发票	供应商账单状态变为Pending Approval，表示发票已进入授权审批工作流。
重要性标记审批周期时间的起点，便于识别授权链中的瓶颈。获取途径系统备注。查找“approvalstatus”字段变为“1”（Pending Approval）的记录。采集比较状态字段变更前后的值事件类型 inferred

建议填写选填

数据提取指南

如何从NetSuite获取数据

步骤

以管理员身份登录NetSuite，或使用有权创建和导出已保存搜索的用户账号登录。
依次进入Lists > Search > Saved Searches > New。
在记录类型列表中选择交易，创建新的交易已保存搜索。
在Criteria选项卡中，确认Use Expressions复选框初始未选中，然后添加筛选条件：Type，选择Vendor Bill、Bill Payment或Bill Credit。
仍在Criteria选项卡中，添加筛选条件：Main Line，并将其设为True。这样可聚焦于创建和状态事件的单据表头。
如需提取历史记录，请在筛选列表底部的下拉菜单中找到System Notes关联项，添加System Notes : Date，并设置所需日期范围，例如最近90天。
切换到Results选项卡，删除所有默认列。
添加案例标识列：选择Transaction Name或Document Number，并将自定义标签设为InvoiceNumber。
添加事件时间列：选择System Notes fields... > Date，并将自定义标签设为EventTime。
添加活动列：选择Formula (Text)。在公式窗口中粘贴下方查询部分提供的CASE语句。此逻辑会将NetSuite字段变更映射为标准活动名称。将自定义标签设为活动。
使用Formula (Text)添加其他必需属性（Source System、Last Data Update），并填入静态值，例如'NetSuite'，以及内置的{today}标签。
点击Save & Run。结果加载后，点击结果工具栏左上角的Export - CSV图标，下载ProcessMind所需的事件日志。

配置

交易类型：仅筛选Vendor Bill、Bill Payment和Bill Credit，避免Journal Entries或Purchase Orders带来的无关数据。
System Notes关联项：此配置主要依靠System Notes关联项生成历史记录，而非当前状态快照。请确保您的用户角色有权查看System Notes。
日期范围：将日期筛选条件应用于System Notes : Date字段，而非交易Date字段。这样可以捕获旧发票的近期活动，例如今天支付去年创建的发票。
Main Line：建议将Main Line设为True，以减少行数；但如果需要跟踪'Invoice Account Modified'，可能需要将其设为False，或另建搜索来提取行级变更。
公式限制：NetSuite公式最多支持4000个字符。如果Activity逻辑过于复杂，请拆分为两个搜索，或在NetSuite之外进行映射处理。

a 查询示例 config

Type: Transaction

CRITERIA TAB:
- Type: is any of Vendor Bill, Bill Payment, Bill Credit
- Main Line: is True
- System Notes : Date: is within [Your Date Range]

RESULTS TAB:

Column 1: Document Number
- Label: InvoiceNumber

Column 2: System Notes : Date
- Label: EventTime

Column 3: Formula (Text)
- Label: Activity
- Formula:
CASE 
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' THEN 'Invoice Created'
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' AND {createdfrom} IS NOT NULL THEN 'Invoice Matched to PO'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Pending Approval' THEN 'Invoice Approval Requested'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Open' THEN 'Invoice Approved'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Rejected' THEN 'Invoice Rejected'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Amount' THEN 'Invoice Amount Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Account' THEN 'Invoice Account Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'T' THEN 'Payment Hold Applied'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'F' THEN 'Payment Hold Released'
   WHEN {type} = 'Bill Credit' AND {systemnotes.type} = 'Create' THEN 'Vendor Credit Applied'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Pending Approval' THEN 'Payment Scheduled'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Paid In Full' THEN 'Payment Processed'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Void' THEN 'Payment Voided'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Cleared' AND {systemnotes.newvalue} = 'T' THEN 'Payment Cleared'
   ELSE 'General Update'
END

Column 4: Formula (Text)
- Label: SourceSystem
- Formula: 'NetSuite'

Column 5: Formula (Text)
- Label: LastDataUpdate
- Formula: TO_CHAR({today}, 'YYYY-MM-DD HH24:MI:SS')

Column 6: Vendor : Name
- Label: VendorName

Column 7: Subsidiary
- Label: Subsidiary

Column 8: Amount
- Label: InvoiceAmount

Column 9: Due Date
- Label: DueDate

Column 10: Created From
- Label: PurchaseOrderNumber

Column 11: System Notes : Set by
- Label: User

Column 12: Department
- Label: Department

Column 13: Status
- Label: ApprovalStatus

Column 14: Formula (Text)
- Label: IsNonPO
- Formula: CASE WHEN {createdfrom} IS NULL THEN 'True' ELSE 'False' END

步骤

配置ODBC驱动程序：确保已在数据提取设备上安装并配置SuiteAnalytics Connect ODBC驱动程序。您需要NetSuite账号ID、角色ID和有效凭据。
确认权限：与ODBC连接关联的用户角色必须拥有'SuiteAnalytics Connect'权限，并具有对Transactions、System Notes和Entities的'View'访问权限。
准备环境：打开SQL客户端（例如DBeaver、DataGrip或专用ETL工具），连接到NetSuite数据源。
定义范围：确定要提取的子公司ID和日期范围。更新所提供查询中名为'VendorBills'的公用表表达式（CTE）里的WHERE子句。
执行查询：运行所提供的SQL脚本。该脚本汇总'Transaction'、'TransactionLine'、'SystemNote'和'TransactionLink'表中的数据，以还原流程历史。
监控执行情况：处理大型数据集时，NetSuite ODBC连接可能超时。如果查询失败，可使用'trandate'筛选条件按月或季度拆分提取。
验证数据：检查输出中'Invoice Created'和'Payment Processed'事件的分布是否合理，并确认每一行都填有'InvoiceNumber'。
导出数据：将查询结果保存为CSV文件，或直接导入您的数据框架。请将字符编码设为UTF-8，以正确处理供应商名称中的特殊字符。
设置日期格式：将数据导入ProcessMind时，确保'EventTime'列解析为日期时间对象（YYYY-MM-DD HH:MM:SS）。
处理空值：'User'列中的空值通常表示系统生成的事件（例如计划脚本）。如有需要，可在后续处理中将其替换为'System'。
最终检查：确认'Activity'列包含所需的具体事件名称（例如'Invoice Created'、'Payment Processed'），且没有发生映射错误。

配置

日期范围：使用'Transaction.trandate'筛选。首次进行流程挖掘分析时，建议选择3至12个月的数据。
子公司筛选：如果您的NetSuite环境使用OneWorld，请务必按'Transaction.subsidiary'筛选，确保分析对象是正确的法人实体。
System Notes：提取过程主要依赖'SystemNote'表获取审批和变更记录。请确认'Log System Notes on Update Only'未阻止关键工作流步骤的记录。
交易状态：NetSuite交易状态各不相同（例如'A'、'B'）。查询会将其映射为易读文本。请确认您的环境使用标准状态代码。
ODBC驱动程序版本：使用最新版本的SuiteAnalytics Connect驱动程序，避免与近期NetSuite升级发生兼容性问题。

a 查询示例 sql

WITH VendorBills AS (
    SELECT
        t.ID AS TransactionID,
        t.tranid AS InvoiceNumber,
        t.trandate AS InvoiceDate,
        t.createDate AS CreatedDate,
        t.lastModifiedDate AS LastModified,
        e.altName AS VendorName,
        s.name AS Subsidiary,
        t.foreignTotal AS InvoiceAmount,
        t.dueDate AS DueDate,
        t.createdFrom AS CreatedFromID,
        CASE WHEN t.createdFrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
        dep.name AS Department,
        'NetSuite' AS SourceSystem
    FROM
        Transaction t
        LEFT JOIN Entity e ON t.entity = e.id
        LEFT JOIN Subsidiary s ON t.subsidiary = s.id
        LEFT JOIN Department dep ON t.department = dep.id
    WHERE
        t.type = 'VendBill'
        AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD') -- REPLACE WITH YOUR START DATE
        AND t.trandate <= TO_DATE('2023-12-31', 'YYYY-MM-DD') -- REPLACE WITH YOUR END DATE
),
PO_Data AS (
    SELECT
        t.ID AS PO_ID,
        t.tranid AS PONumber
    FROM
        Transaction t
    WHERE
        t.type = 'PurchOrd'
)

-- 1. Invoice Created
SELECT
    vb.InvoiceNumber,
    'Invoice Created' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 2. Invoice Matched to PO
SELECT
    vb.InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 3. Invoice Approval Requested (Derived from System Notes)
SELECT
    vb.InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Pending Approval' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Pending Approval'

UNION ALL

-- 4. Invoice Approved
SELECT
    vb.InvoiceNumber,
    'Invoice Approved' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Open' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND (sn.newValue = 'Approved' OR sn.newValue = 'Open')

UNION ALL

-- 5. Invoice Rejected
SELECT
    vb.InvoiceNumber,
    'Invoice Rejected' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Rejected' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Rejected'

UNION ALL

-- 6. Invoice Amount Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    (sn.standardfield = 'foreigntotal' OR sn.standardfield = 'total')

UNION ALL

-- 7. Invoice Account Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'account'

UNION ALL

-- 8. Payment Hold Applied
SELECT
    vb.InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Hold' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'T'

UNION ALL

-- 9. Payment Hold Released
SELECT
    vb.InvoiceNumber,
    'Payment Hold Released' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Released' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'F'

UNION ALL

-- 10. Vendor Credit Applied
SELECT
    vb.InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    vc.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Credit Applied' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vc ON tl.nextDoc = vc.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vc.type = 'VendCred'

UNION ALL

-- 11. Payment Scheduled (Bill Payment created but pending)
SELECT
    vb.InvoiceNumber,
    'Payment Scheduled' AS Activity,
    vp.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Scheduled' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.status = 'Pending Approval'

UNION ALL

-- 12. Payment Processed (Payment posted)
SELECT
    vb.InvoiceNumber,
    'Payment Processed' AS Activity,
    vp.trandate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Paid' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND (vp.status = 'PaidInFull' OR vp.status = 'Deposited' OR vp.status = 'NotDeposited')

UNION ALL

-- 13. Payment Voided
SELECT
    vb.InvoiceNumber,
    'Payment Voided' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Voided' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    INNER JOIN SystemNote sn ON vp.ID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    vp.type = 'VendPymt'
    AND sn.standardfield = 'status'
    AND sn.newValue LIKE '%Void%'

UNION ALL

-- 14. Payment Cleared
SELECT
    vb.InvoiceNumber,
    'Payment Cleared' AS Activity,
    vp.clearedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Cleared' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.clearedDate IS NOT NULL

步骤

前提条件：确保您拥有 NetSuite 账户，并具备管理员或开发人员访问权限。您必须在 Setup > Company > Enable Features 中启用“SuiteTalk REST Web Services”和“SuiteAnalytics Connect”功能。
设置集成：前往 Setup > Integration > Manage Integrations > New。创建名为“ProcessMining_ETL”的集成。勾选“Rest Web Services”和“Token-Based Authentication”。记录生成的 Consumer Key 和 Consumer Secret。
生成令牌：前往“管理访问令牌”页面。为具有查看“交易”、“系统备注”和“列表”权限的角色创建新令牌。记录 Token ID 和 Token Secret。
Python 环境：安装 Python 3.x。使用 pip 安装所需库：pip install requests requests-oauthlib pandas。
配置脚本：复制下方“查询”部分提供的 Python 脚本。使用您的凭据更新 ACCOUNT_ID、CONSUMER_KEY、CONSUMER_SECRET、TOKEN_ID 和 TOKEN_SECRET 变量。将 START_DATE 设为所需的数据提取起始日期（例如，'2023-01-01'）。
SuiteQL 逻辑：脚本使用 UNION ALL 查询。它从 交易 表中获取创建和付款事件，从 SystemNote 表中获取审计记录事件（审批、变更和保留）。
运行脚本：通过命令行运行：python netsuite_extract.py。脚本会自动处理分页（SuiteQL 每页最多返回 1,000 条记录）。
处理数据：脚本会自动将 JSON 响应转换为适用于流程挖掘的扁平表格格式，并处理列映射和日期格式。
验证：脚本会显示提取的总行数。必要时，可与 NetSuite 保存的搜索结果进行核对。
导出：最终数据框会保存为 netsuite_ap_event_log.csv。该文件可上传到 ProcessMind。
上传：将 CSV 导入 ProcessMind。将 InvoiceNumber 映射为案例ID、活动映射为活动、EventTime映射为时间戳。

配置

身份验证：使用 OAuth 1.0a（基于令牌的身份验证）。这是 NetSuite REST API 的标准安全模型。
批次大小：脚本每页请求 1,000 行（NetSuite 对 SuiteQL 设定的默认上限）。
日期筛选：按 trandate（交易）和 date（系统备注）筛选，条件为大于或等于 START_DATE，以提升性能。
范围：按类型 'VendBill'（供应商账单）、'BillPymt'（账单付款）和 'VendCred'（供应商贷项）筛选 交易 表。
系统备注：此表对于捕获审批循环和变更至关重要。表中数据量可能较大，请确保集成用户有权查询 SystemNote。
时区：NetSuite API 返回的日期采用账户时区。脚本假设日期使用标准 ISO 格式。

a 查询示例 python

import requests
from requests_oauthlib import OAuth1
import pandas as pd
import time

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
ACCOUNT_ID = class="hl-string">'1234567_SB1'  class=class="hl-string">"hl-comment"># Your NetSuite Account ID (Realm). Use hyphens for sandbox (e.g., 1234567-sb1)
CONSUMER_KEY = class="hl-string">'your_consumer_key'
CONSUMER_SECRET = class="hl-string">'your_consumer_secret'
TOKEN_ID = class="hl-string">'your_token_id'
TOKEN_SECRET = class="hl-string">'your_token_secret'
START_DATE = class="hl-string">'2024-01-01'  class=class="hl-string">"hl-comment"># Filter for data extraction

class=class="hl-string">"hl-comment"># Normalize Account ID for URL (NetSuite requires lowercase and hyphens)
url_account_id = ACCOUNT_ID.lower().replace(class="hl-string">'_', class="hl-string">'-')
BASE_URL = fclass="hl-string">'https://{url_account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># === SUITEQL QUERY ===
class=class="hl-string">"hl-comment"># This query extracts 14 activities using UNION ALL to combine Transaction data and System Notes.
suiteql_query = fclass="hl-string">"""
/* 1. Invoice Created */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Created' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Matched to PO */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    'False' AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.createdfrom IS NOT NULL
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Approval Requested (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30 /* Internal ID for Vendor Bill */
AND sn.field = 'status'
AND sn.value = 'Pending Approval'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 4. Invoice Approved (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approved' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Open'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 5. Invoice Rejected (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Rejected' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Rejected'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 6. Invoice Amount Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'total'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 7. Invoice Account Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'account'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 8. Payment Hold Applied (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'T'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 9. Payment Hold Released (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Released' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'F'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 10. Vendor Credit Applied */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    TO_CHAR(t_cred.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_cred.trandate AS PaymentDate
FROM Transaction t_cred
JOIN TransactionLine tl ON t_cred.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_cred.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_cred.type = 'VendCred'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 11. Payment Scheduled */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Scheduled' AS Activity,
    TO_CHAR(t_pay.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Pending Approval'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 12. Payment Processed */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Processed' AS Activity,
    TO_CHAR(t_pay.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status <> 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 13. Payment Voided */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Voided' AS Activity,
    TO_CHAR(t_pay.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 14. Payment Cleared (Logic depends on bank rec features, using Check Cleared date or modification) */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Cleared' AS Activity,
    /* Fallback to trandate if clearedDate is unavailable in your config */
    TO_CHAR(COALESCE(t_pay.createddate, t_pay.trandate), 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Paid In Full' /* Indicates cleared/posted in some contexts */
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')
"""

class=class="hl-string">"hl-comment"># === AUTH AND EXECUTION ===

def get_suiteql_results(query):
    oauth = OAuth1(
        client_key=CONSUMER_KEY,
        client_secret=CONSUMER_SECRET,
        resource_owner_key=TOKEN_ID,
        resource_owner_secret=TOKEN_SECRET,
        realm=ACCOUNT_ID,
        signature_method=class="hl-string">'HMAC-SHA256'
    )
    
    all_rows = []
    offset = 0
    has_more = True
    
    print(class="hl-string">"Starting extraction...")
    
    while has_more:
        class=class="hl-string">"hl-comment"># SuiteQL REST API payload
        headers = {
            class="hl-string">'Prefer': class="hl-string">'transient',
            class="hl-string">'Content-Type': class="hl-string">'application/json'
        }
        
        class=class="hl-string">"hl-comment"># Add LIMIT/OFFSET to query if not using built-in cursors (SuiteQL doesn't support OFFSET well natively in string, 
        class=class="hl-string">"hl-comment"># but REST API handles pagination via links. However, for raw query, we usually rely on class="hl-string">'items' return)
        class=class="hl-string">"hl-comment"># Note: Pure SuiteQL via REST returns a set number of records. We must iterate via the class="hl-string">'links' provided in response
        class=class="hl-string">"hl-comment"># or typically, SuiteQL REST creates a job for large queries. 
        class=class="hl-string">"hl-comment"># For simplicity in this script, we assume a direct synchronous call pattern.
        
        class=class="hl-string">"hl-comment"># Actually, for large datasets, we must use the class="hl-string">'q' parameter. 
        class=class="hl-string">"hl-comment"># But SuiteQL POST body is standard. We will implement simple looping if class="hl-string">'hasMore' is present.
        
        payload = {
            class="hl-string">"q": query
        }
        
        response = requests.post(BASE_URL, headers=headers, auth=oauth, json=payload)
        
        if response.status_code != 200:
            print(fclass="hl-string">"Error: {response.text}")
            break
            
        data = response.json()
        items = data.get(class="hl-string">'items', [])
        all_rows.extend(items)
        
        class=class="hl-string">"hl-comment"># Check if there are more results (SuiteQL REST paging)
        class=class="hl-string">"hl-comment"># Standard REST response has class="hl-string">'hasMore' and class="hl-string">'links'. 
        class=class="hl-string">"hl-comment"># However, Query API V1 often requires traversing the class="hl-string">'next' link.
        has_more = data.get(class="hl-string">'hasMore', False)
        
        if has_more:
            class=class="hl-string">"hl-comment"># For subsequent pages, we follow the class="hl-string">'next' link logic or offset logic.
            class=class="hl-string">"hl-comment"># Note: The Query API creates a paged result set. We strictly need to follow the class="hl-string">'links' > class="hl-string">'next'.
            links = data.get(class="hl-string">'links', [])
            next_link = next((l[class="hl-string">'href'] for l in links if l[class="hl-string">'rel'] == class="hl-string">'next'), None)
            if next_link:
                BASE_URL = next_link class=class="hl-string">"hl-comment"># Update URL for next loop
                class=class="hl-string">"hl-comment"># Payload is not needed for next link GET usually, but Query API might differ.
                class=class="hl-string">"hl-comment"># If using links, we switch to GET for the next page.
                class=class="hl-string">"hl-comment"># (Simplified logic: In production, robustly handle the href)
            else:
                has_more = False
        
        print(fclass="hl-string">"Fetched {len(items)} rows. Total: {len(all_rows)}")
        time.sleep(1) class=class="hl-string">"hl-comment"># Rate limit protection

    return pd.DataFrame(all_rows)

class=class="hl-string">"hl-comment"># === RUN ===
if __name__ == class="hl-string">'__main__':
    df = get_suiteql_results(suiteql_query)
    
    class=class="hl-string">"hl-comment"># Post-processing
    if not df.empty:
        class=class="hl-string">"hl-comment"># Ensure numeric formats
        df[class="hl-string">'invoiceamount'] = pd.to_numeric(df[class="hl-string">'invoiceamount'], errors=class="hl-string">'coerce').fillna(0)
        
        class=class="hl-string">"hl-comment"># Save
        filename = class="hl-string">'netsuite_ap_event_log.csv'
        df.to_csv(filename, index=False)
        print(fclass="hl-string">"Success! Saved {len(df)} rows to {filename}")
    else:
        print(class="hl-string">"No data found.")

准备好开始了吗？

立即将此模板应用于您的NetSuite环境，开启流程挖掘之旅，发现效率提升空间并优化付款工作流。

返回改进指南下载模板

立即优化应付账款付款处理

识别瓶颈，将周期时间缩短30%。

开始免费试用

无需信用卡，几分钟即可完成设置。

应付账款付款处理数据模板

应付账款付款处理数据模板

应付账款付款处理属性

应付账款付款处理活动

数据提取指南

导出NetSuite应付账款交易的已保存搜索结果

步骤

配置

a 查询示例 config

通过SuiteAnalytics Connect ODBC使用SuiteQL提取数据

步骤

配置

a 查询示例 sql

使用SuiteTalk REST API和SuiteQL的Python ETL

步骤

配置

a 查询示例 python

准备好开始了吗？

立即优化应付账款付款处理

应付账款付款处理数据模板

应付账款付款处理数据模板

应付账款付款处理属性

应付账款付款处理活动

数据提取指南

导出NetSuite应付账款交易的已保存搜索结果

步骤

配置

a 查询示例 config

通过SuiteAnalytics Connect ODBC使用SuiteQL提取数据

步骤

配置

a 查询示例 sql

使用SuiteTalk REST API和SuiteQL的Python ETL

步骤

配置

a 查询示例 python

准备好开始了吗？

立即优化应付账款付款处理

我们重视您的隐私