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

NetSuite

← 返回改进指南

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

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

此模板为梳理NetSuite应付账款工作流提供完整框架，涵盖关键数据属性、核心流程活动和专用提取说明，确保分析准确且可执行。

深度分析所需的属性
需要跟踪的关键流程里程碑
NetSuite专用提取指南

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

下载模板

应付账款付款处理属性

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

5 必需 10 建议 6 可选

	名称	说明
	上次数据更新 LastDataUpdate	数据提取或刷新的时间戳。
说明表示数据最近一次从源系统提取至流程挖掘平台的时间，帮助用户了解分析数据的时效性。用于判断仪表板中的数据反映的是实时状态，还是之前某个时间段的快照。这对于确保“逾期天数”计算结果可信至关重要。为什么重要了解数据新鲜度和同步状态所必需。获取位置 ETL时间戳示例 2023-11-01T00:00:00Z
	事件时间 EventTime	活动发生时的时间戳。
说明活动发生的准确日期和时间。该字段用于按顺序排列事件，并计算步骤之间的持续时间。准确的时间戳对于识别瓶颈至关重要。它们支持计算审批所需时间、发票处于阻止状态的持续时间等周期指标。为什么重要事件排序和计算所有基于时间的KPI时的必填项。获取位置 System Notes中的“date”字段示例 2023-10-01T14:30:00Z2023-10-02T09:15:00Z
	发票编号 InvoiceNumber	供应商发票的唯一标识符。
说明此属性表示NetSuite中供应商账单的唯一参考编号。它是应付账款流程的确定性案例标识符，用于汇总与特定付款义务相关的所有活动。在分析中，该字段作为流程实例的主键。分析人员可以据此追踪负债从收到并记录发票到最终付款清算的完整生命周期。系统会根据此标识符汇总不同变体，以确定常见流程路径。为什么重要这是将所有事件绑定在一起、形成流程案例的必填Case ID。获取位置交易表中type为“Vendor Bill”的记录，其“tranid”字段示例 INV-2023-001VB-99822ACME-10299982311
	活动 Activity	执行的流程步骤或事件名称。
说明此属性记录特定时间点发生的具体操作或状态变更。例如“发票已创建”“审批状态已变更”或“付款已处理”。这些数据对于重建流程图至关重要。按每个发票编号的时间顺序排列这些活动后，流程挖掘工具可以可视化实际工作流，识别循环、返工以及与理想路径的偏差。为什么重要定义流程图中的步骤，是任何流程挖掘分析的必填项。获取位置 System Notes或Workflow History日志示例发票已创建发票已批准已安排付款付款已清算
	源系统 SourceSystem	数据来源系统的名称。
说明标识提取事件数据的软件应用。在此场景中，该值通常是固定的，例如“NetSuite”。在多系统环境中，此属性有助于追踪数据血缘。虽然当前视图聚焦于NetSuite，但保留该字段可以确保与企业级数据模型兼容。为什么重要数据血缘追踪以及区分多系统环境中的数据时的必填项。获取位置提取过程中硬编码示例 NetSuiteNetSuite OneWorld
	付款日期 PaymentDate	执行付款的日期。
说明与账单关联的VendorPayment交易中记录的日期，表示应付账款周期的完成时间。用于计算“端到端付款周期时间”。将该日期与“InvoiceDate”比较，即可得到流程总时长。为什么重要主要流程周期的结束时间戳。获取位置关联VendorPayment交易日期示例 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	接收发票的公司子公司或法人实体。
说明在NetSuiteOneWorld环境中，用于标识发票所属的特定法人实体（Subsidiary），支持组织间比较。该属性驱动“端到端付款周期时间”仪表板，可比较不同业务单元的效率，帮助判断特定地区或实体是否表现不佳。为什么重要对于需要比较各业务单元绩效的多实体组织至关重要。获取位置 Transaction“subsidiary”字段示例美国东部EMEA总部APAC销售
	审批状态 ApprovalStatus	发票当前的工作流审批状态。
说明表示发票处于待审批、已批准或已拒绝状态。该状态决定工作流转换逻辑。该字段是“审批工作流合规审计”的核心。状态变化会生成活动事件，最终状态决定发票是否可以付款。为什么重要跟踪发票通过审批关卡的进度。获取位置 Transaction“approvalstatus”字段示例待审批已批准已拒绝
	是否为Non-PO IsNonPO	表示发票是否未关联PurchaseOrder的标志。
说明根据是否存在关联PurchaseOrder生成的布尔属性，将流程划分为有PO支持和直接FI发票。用于“Non-PO发票处理速度”仪表板，突出手工发票处理相较三方匹配流程的低效。为什么重要分析流程变体的高层级分类。获取位置由“PurchaseOrderNumber”为空派生示例 truefalse
	用户 User	执行该活动的员工。
说明标识负责特定事件的人员，例如审批发票或解除付款冻结的人员。用于“审批工作流瓶颈监控”，识别特定资源造成的延迟，帮助管理层平衡工作量并确定团队成员的培训需求。为什么重要进行资源分析并识别用户层面的瓶颈所必需。获取位置 SystemNotes“name”或Transaction“employee” 示例 John Doe财务管理员系统
	部门 Department	与发票关联的内部部门。
说明按产生费用的成本中心或内部部门（如IT、市场营销）对发票分类。该属性支持按部门拆分周期时间，帮助识别特定部门在发票编码或审批环节是否较慢，从而开展有针对性的流程改进。为什么重要支持从组织维度分析流程效率。获取位置 Transaction“department”字段示例 IT市场营销制造
	采购订单号 PurchaseOrderNumber	相关PurchaseOrder的参考编号。
说明将供应商账单关联至源PurchaseOrder（PO）。如果该字段为空，则发票被视为Non-PO发票。该属性对于“Non-PO发票处理速度”仪表板至关重要，可将流程变体区分为有PO支持和无PO（FI）发票，以比较效率和合规性。为什么重要区分基于PO和非PO的发票处理所必需。获取位置 Transaction“createdfrom”字段示例 PO-8821PO-1002null
	付款冻结 PaymentHold	表示发票当前是否被阻止付款。
说明表示发票已被设置付款冻结，因此无法纳入付款批次的标志或状态。该属性支持“付款冻结影响分析”仪表板。分析该字段保持为“true”的时长，可以突出争议或信息缺失造成的处理延迟。为什么重要分析发票卡住或延迟原因的关键属性。获取位置 Transaction“paymenthold”字段示例 truefalse
	付款条款 PaymentTerms	约定的付款时间和折扣条款。
说明规定付款条件，例如“Net30”或“2%10Net30”。这些信息有助于解释“到期日”和提前付款折扣机会。该属性用于“提前付款折扣获取率”仪表板，识别符合折扣条件的发票，并帮助将未获得的折扣与具体限制性条款关联起来。为什么重要为到期日提供背景，并识别折扣机会。获取位置 Transaction“terms”字段示例 30天账期2%10天内付款，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。负债在GL中得到冲销。
为什么重要付款流程的主要完成里程碑，用于计算“端到端处理周期时间”。获取位置交易表。识别“Bill Payment”记录的创建，或状态变为“Deposited”（支票）或“Paid”。采集交易Bill Payment执行时记录事件类型 explicit
	付款已清算	Bill Payment在银行对账过程中被标记为已清算，确认资金已从银行账户中划出。
为什么重要为现金流分析提供真实的财务结束日期，与会计付款日期相区别。获取位置 Transaction Line。Bill Payment交易行中的“cleared”标志被设置为True。采集交易X执行时记录事件类型 explicit
	发票已创建	在NetSuite中首次创建供应商账单记录。这标志着流程开始，表示发票已录入ERP。
为什么重要为整个应付账款周期和账龄计算建立基准时间戳。获取位置交易表。筛选记录类型为Vendor Bill的记录，获取datecreated或trandate字段。采集交易Vendor Bill执行时记录事件类型 explicit
	发票已匹配采购订单	表示Vendor Bill由现有Purchase Order创建或与其关联，用于区分有采购订单支持的发票和无采购订单发票。
为什么重要对于分析采购政策合规情况，以及比较有采购订单和无采购订单发票的周期时间至关重要。获取位置交易表。检查Vendor Bill记录中的“createdfrom”字段。如果该字段填有Purchase Order ID，则表示发生了此活动。采集比较前后状态字段事件类型 inferred
	发票已批准	Vendor Bill状态变为Open，表示已获授权付款，并已影响General Ledger。这是付款前的关键里程碑。
为什么重要结束审批周期，使发票可以进入付款安排。对于衡量审批效率至关重要。获取位置 System Notes。查找“approvalstatus”字段变为“2”（Approved）或状态变为“Open”的记录。采集比较前后状态字段事件类型 inferred
	付款已作废	此前已处理的Bill Payment被作废，Vendor Bill重新打开。这表示重大异常和流程失败。
为什么重要对于审计合规和识别付款失败原因至关重要，例如支票打印错误或银行拒绝。获取位置 Bill Payment的System Notes。状态变为“Voided”。采集交易X执行时记录事件类型 explicit
	发票已拒绝	Vendor Bill被退回提交人处进行更正或拒绝处理，表示审批工作流出现循环或终止。
为什么重要较高的拒绝率通常表明流程存在摩擦、培训不足或上游数据质量问题。获取位置 System Notes。查找“approvalstatus”字段变为“3”（Rejected）的记录。采集比较前后状态字段事件类型 inferred
	发票科目已修改	发票行项目中的General Ledger费用科目发生变化，表示正在更正编码错误或重新分类。
为什么重要直接衡量“发票编码返工率”KPI，并突出显示会计编码环节的效率损失。获取位置 System Notes。查找Transaction Line子列表中“account”字段的变化。采集交易X执行时记录事件类型 explicit
	发票金额已修改	Vendor Bill总金额在首次创建后发生变化，可能表明存在返工、数据录入错误或差异解决。
为什么重要表示发生了人工干预和返工，会对直通式处理率产生负面影响。获取位置 System Notes。查找Vendor Bill记录中“total”或“usertotal”字段的变化。采集交易X执行时记录事件类型 explicit
	已安排付款	Bill Payment记录以“Pending Approval”状态创建。这通常发生在创建Electronic Bank Payment（EFT）批次期间。
为什么重要区分付款意图与资金实际执行，突出显示付款授权中的延迟。获取位置交易表。创建与Bill关联、状态为“Pending Approval”的“Bill Payment”记录。采集比较前后状态字段事件类型 inferred
	已应用付款暂停	Vendor Bill上的Payment Hold复选框被设置为True，发票因此不会被付款运行选中。
为什么重要识别因争议或现金流管理决策而停滞的发票瓶颈。获取位置 System Notes。查找“paymenthold”字段从“F”变为“T”的记录。采集交易X执行时记录事件类型 explicit
	已应用供应商贷项	Bill Credit（Vendor Credit）被应用于Vendor Bill，减少应付金额。这是现金付款之外的另一种方式。
为什么重要解释付款金额可能与发票金额不同的原因，并正确结清负债。获取位置 Transaction Links。识别类型为“Vendor Credit”的交易与Vendor Bill建立关联的时间。采集比较前后状态字段事件类型 inferred
	已解除付款暂停	Payment Hold复选框被设置为False，发票重新进入付款处理。这标志着阻塞期结束。
为什么重要用于计算“付款阻止持续时间”KPI，并了解争议发票的解决时间。获取位置 System Notes。查找“paymenthold”字段从“T”变为“F”的记录。采集交易X执行时记录事件类型 explicit
	已请求发票审批	Vendor Bill状态变为Pending Approval，表示发票已进入用于授权的工作流路由。
为什么重要标志着审批周期时间的开始，可用于识别授权链中的瓶颈。获取位置 System Notes。查找“approvalstatus”字段变为“1”（Pending Approval）的记录。采集比较前后状态字段事件类型 inferred

建议可选

提取指南

如何从NetSuite获取数据

步骤

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

配置

Transaction Types：严格筛选 Vendor Bill、Bill Payment 和 Bill Credit，避免 Journal Entries 或 Purchase Orders 带来的噪声。
System Notes Join：此配置高度依赖 System Notes 连接来生成历史日志，而不是当前状态快照。请确保用户角色拥有查看 System Notes 的权限。
Date Range：将日期筛选应用于 System Notes : Date 字段，而不是交易 Date 字段。这样可以捕获旧发票的近期活动，例如今天为去年创建的发票付款。
Main Line：建议将 Main Line = True，以减少行数。但如果需要跟踪“Invoice Account Modified”，可能需要将 Main Line = False，或为行级变更创建单独的搜索。
Formula Limits：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 Driver：确保已在提取机器上安装并配置 SuiteAnalytics Connect ODBC 驱动程序。您需要 NetSuite Account ID、Role ID 和有效凭据。
验证权限：与 ODBC 连接关联的用户角色必须拥有 SuiteAnalytics Connect 权限，以及对 Transactions、System Notes 和 Entities 的 View 访问权限。
准备环境：打开 SQL 客户端，例如 DBeaver、DataGrip 或专有 ETL 工具，并建立与 NetSuite 数据源的连接。
定义范围：确定提取所需的具体 Subsidiary ID 和 Date Range。更新所提供查询中名为 'VendorBills' 的 Common Table Expression（CTE）里的 WHERE 子句。
执行查询：运行所提供的 SQL 脚本。该脚本会汇总 'Transaction'、'TransactionLine'、'SystemNote' 和 'TransactionLink' 表中的数据，以重建流程历史。
监控执行：处理大型数据集时，NetSuite ODBC 连接可能超时。如果查询失败，请使用 'trandate' 筛选条件按月或按季度拆分提取。
验证数据：检查 'Invoice Created' 事件与 'Payment Processed' 事件的分布是否合理。确保每一行都已填充 'InvoiceNumber'。
导出数据：将查询结果保存为 CSV 文件，或直接载入数据框。确保字符编码设置为 UTF-8，以正确处理 Vendor Names 中的特殊字符。
格式化日期：导入 ProcessMind 时，确保将 'EventTime' 列解析为日期时间对象（YYYY-MM-DD HH:MM:SS）。
处理空值：'User' 列中的空值通常表示系统生成的事件，例如计划脚本生成的事件。如有需要，您可以在后处理阶段将其替换为 'System'。
最终检查：确认 'Activity' 列包含所需的具体事件名称，例如 'Invoice Created'、'Payment Processed'，且不存在映射错误。

配置

Date Range：使用 'Transaction.trandate' 进行筛选。初始流程挖掘分析建议使用 3 至 12 个月的范围。
Subsidiary Filtering：如果 NetSuite 环境为 OneWorld，请始终按 'Transaction.subsidiary' 筛选，确保分析正确的法人实体。
System Notes：提取高度依赖 'SystemNote' 表来获取审批和变更日志。请确保 'Log System Notes on Update Only' 未阻止关键工作流步骤的捕获。
Transaction Status：NetSuite 交易状态彼此独立，例如 'A'、'B'。查询会将其映射为可读字符串。请确认您的环境使用标准状态代码。
ODBC Driver Version：使用最新可用的 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账户的管理员或开发者访问权限。在设置 > 公司 > 启用功能中，必须启用“SuiteTalk REST Web Services”和“SuiteAnalytics Connect”功能。
集成设置：转到设置 > 集成 > 管理集成 > 新建。创建名为“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方式。它查询Transaction表获取创建和付款事件，查询SystemNote表获取审计轨迹事件，例如批准、变更和冻结。
执行：通过命令行运行脚本：python netsuite_extract.py。脚本会自动处理分页（SuiteQL每页生成1,000条记录）。
数据处理：脚本会自动将JSON响应转换为适用于流程挖掘的扁平表格格式，并处理列映射和日期格式化。
验证：脚本会输出提取的总行数。如有需要，请将其与NetSuite中的已保存搜索进行核对。
导出：最终DataFrame会保存为netsuite_ap_event_log.csv。该文件已可以上传到ProcessMind。
上传：将CSV导入ProcessMind。将InvoiceNumber映射为案例ID，将Activity映射为活动，将EventTime映射为时间戳。

配置

Authentication：使用 OAuth 1.0a（Token-Based Authentication）。这是 NetSuite REST API 的标准安全模型。
Batch Size：脚本每页请求 1,000 行，这是 NetSuite 对 SuiteQL 设置的默认最大值。
Date Filtering：配置为按 trandate（交易）和 date（系统备注）大于等于 START_DATE 进行筛选，以优化性能。
Scope：按类型 'VendBill'（Vendor Bill）、'BillPymt'（Bill Payment）和 'VendCred'（Vendor Credit）筛选 Transaction 表。
System Notes：该表对于捕获审批循环和变更至关重要。该表数据量可能较大，请确保集成用户拥有查询 SystemNote 的权限。
Timezone：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

准备开始了吗？

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

我们重视您的隐私