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

NetSuite

← 返回改进指南

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

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

此模板为映射您的 NetSuite 应付账款工作流提供了一个全面的框架。它包含基本数据属性的详细列表、核心流程活动以及专门的提取说明，以确保您的分析准确且具有可操作性。

深度分析的推荐属性
需追踪的关键流程里程碑
NetSuite 特定提取指南

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

下载模板

应付账款付款处理属性

这些是推荐包含在事件日志中的数据字段，用于在 NetSuite 环境中进行全面的应付账款付款处理分析。

5 必填 10 推荐 6 可选

	名称	描述
	Event 时间 EventTime	活动发生时的时间戳。
描述活动发生的精确日期和时间。用于按顺序排列事件并计算步骤之间的时间跨度。准确的时间戳对于识别瓶颈至关重要。它们支持计算周期时间，例如审批所需的时间或发票处于阻塞状态的时长。为何重要对于事件排序和计算所有基于时间的 KPI 是必不可少的。获取方式系统说明“日期”字段示例 2023-10-01T14:30:00Z2023-10-02T09:15:00Z
	最后数据更新 LastDataUpdate	提取或刷新数据时的时间戳。
描述指示数据最近一次从源系统提取到 Process Mining 平台的时间。这有助于用户了解分析的时效性。它用于确定 dashboard 中显示的数据是实时状态还是历史快照。这对于确保“逾期天数”计算的可靠性至关重要。为何重要对于了解数据新鲜度和同步状态是必不可少的。获取方式 ETL Timestamp 示例 2023-11-01T00:00:00Z
	发票编号 InvoiceNumber	供应商发票的唯一标识符。
描述此属性代表 NetSuite 中分配给供应商账单的唯一参考编号。它作为应付账款流程的确定性案例标识符，对与特定付款义务相关的所有活动进行分组。在分析中，此字段充当流程实例的主键。它允许分析师追踪负债从发票收到并录入那一刻起到最终付款结清为止的完整生命周期。变体基于此标识符进行汇总，以确定常见的流程路径。为何重要这是必需的 Case ID，用于将所有 event 绑定在一起以形成完整的流程 case。获取方式交易表，类型为“供应商账单”的“交易 ID”字段示例 INV-2023-001VB-99822ACME-10299982311
	活动 Activity	所执行流程步骤或事件的名称。
描述此属性记录在特定时间点发生的具体操作或状态更改。示例包括“发票已创建”、“审批状态已更改”或“付款已处理”。这些数据对于重建流程流图至关重要。通过按时间顺序为每个发票编号排列这些活动，流程挖掘工具可以将实际工作流可视化，从而识别循环、返工以及偏离“标准流程”的情况。为何重要定义流程图中的步骤，是任何 Process Mining 分析的必选项。获取方式系统说明或工作流历史日志示例发票已创建发票已审批付款已安排付款已清算
	源系统 SourceSystem	数据来源系统的名称。
描述识别提取事件数据所在的软件应用程序。在此场景下，该值通常是静态的，例如 'NetSuite'。此属性在多系统环境中对于追踪数据血缘非常有用。虽然此特定视图专注于 NetSuite，但保留此字段可确保与企业级数据模型的兼容性。为何重要对于数据血缘分析以及在多系统环境中区分数据是必不可少的。获取方式数据提取期间硬编码示例 NetSuiteNetSuite OneWorld
	付款日期 PaymentDate	执行付款的日期。
描述记录在与账单关联的供应商付款交易中的日期。这代表应付账款周期的完成。用于计算“端到端付款周期时间”。将此日期与“发票日期”进行比较，可得出流程的总时长。为何重要主流程周期的结束 timestamp。获取方式关联的供应商付款交易日期示例 2023-11-202023-12-05
	供应商名称 VendorName	开具发票的供应商名称。
描述此属性包含与供应商账单关联的实体名称。这对于分析整个供应链的绩效至关重要。在分析中，此字段支持“供应商付款周期差异”仪表板。它允许分析师按供应商对案例进行分组，以识别始终导致匹配差异或具有非标准付款条款的特定供应商。为何重要对于按供应商进行分组分析以发现特定供应商的瓶颈至关重要。获取方式交易“主体”字段，关联至供应商记录示例 Office DepotOracle联邦快递
	到期日期 DueDate	必须向供应商支付款项的日期。
描述此属性根据发票条款指示付款的合同截止日期。它作为按时支付计算的基准。此字段是“按时付款率”KPI 的核心组成部分。通过将“付款执行”日期与此字段进行比较，系统会标记逾期付款并协助估算潜在罚金。为何重要计算按时付款率和识别逾期负债的基础。获取方式交易“到期日”字段示例 2023-11-152023-12-01
	发票金额 InvoiceAmount	发票的总货币价值。
描述供应商账单的总金额，通常以本位币或交易币种表示。该指标支持财务影响分析。用于“逾期付款及罚金跟踪”仪表板，以量化延迟付款的现金流风险。它还用于优先处理高价值发票，以便进行手动干预。为何重要对于量化流程效率低下的财务影响是必要的。获取方式交易“用户合计”或“外币金额”字段示例 1500.00250.5010000.00
	子公司 Subsidiary	接收发票的公司子公司或法人实体。
描述在 NetSuite OneWorld 环境中，这标识了发票所属的具体法律实体（子公司）。它支持进行组织间的对比分析。此属性驱动“端到端付款处理周期”dashboard，支持对不同业务单元的效率进行对比查看。它有助于隔离并找出表现不佳的特定地区或实体。为何重要对于多实体组织横向对比各业务单元绩效至关重要。获取方式交易“子公司”字段示例美国东部欧洲、中东及非洲总部亚太区销售
	审批状态 ApprovalStatus	发票当前的工作流审批状态。
描述指示发票处于待审批、已批准还是已拒绝状态。该状态驱动 workflow 的跳转逻辑。此字段是“审批 Workflow 合规性审计”的核心。该状态的变更会生成活动event，且最终状态决定了发票是否可以进行支付。为何重要跟踪发票通过审批节点的过程。获取方式交易“审批状态”字段示例待审批已批准已驳回
	是否为非 PO IsNonPO	指示发票是否未关联采购订单的标识。
描述一个布尔属性，根据是否缺少关联的采购订单生成。它将流程细分为“基于 PO 的发票”与“直接财务发票”。用于“非 PO 发票处理速度”dashboard，以突出手动处理发票与“三单匹配”流程相比的低效率。为何重要用于分析流程变体的高级维度。获取方式派生自 'PurchaseOrderNumber' 为空的情况示例 truefalse
	用户 User	执行该活动的员工。
描述识别负责特定 event 的个人，例如批准发票或解除付款拦截。用于“审批 Workflow 瓶颈监控”，以识别特定人员导致的延误。这有助于管理层平衡工作负载，并确定特定团队成员的培训需求。为何重要对于资源分析和识别用户层面的瓶颈是必需的。获取方式系统说明“姓名”或交易“员工” 示例 John Doe财务管理员系统
	部门 Department	与发票关联的内部部门。
描述按产生费用的成本中心或内部部门（如 IT、市场部）对发票进行分类。此属性支持按部门细分处理周期。它有助于识别特定部门在发票编码或审批方面是否存在延误，从而实现针对性的流程改进。为何重要支持对流程效率进行组织架构维度的分析。获取方式交易“部门”字段示例 IT市场营销制造业
	采购订单号 PurchaseOrderNumber	相关采购订单的参考编号。
描述将供应商账单关联至原始采购订单 (PO)。如果此字段为空，则该发票被视为非 PO 发票。此属性对“非 PO 发票处理速度”仪表板至关重要。它允许分析将流程变体划分为受 PO 支持的发票与非 PO (FI) 发票，以比较效率和合规性。为何重要用于区分基于 PO 和非基于 PO 的发票处理。获取方式交易“创建自”字段示例 PO-8821PO-1002null
	付款暂停 PaymentHold	指示发票当前是否被拦截付款。
描述表示发票已被设置为付款拦截的状态标识，防止其被纳入付款运行计划。此属性支持“付款拦截影响分析”dashboard。分析该字段设置为 'true' 的时长，有助于发现由争议或信息缺失导致的流程延误。为何重要对于分析发票滞留或延误的原因至关重要。获取方式交易“付款暂缓”字段示例 truefalse
	付款条款 PaymentTerms	规定付款时间及折扣的协定条款。
描述指定付款条件，例如“Net 30”或“2% 10 Net 30”。此上下文有助于解释“到期日”和早期付款折扣的潜力。此属性用于“早期付款折扣获取率”仪表板。它识别哪些发票符合折扣条件，并有助于将错失的折扣与特定的限制性条款关联起来。为何重要将到期日背景化并识别折扣机会。获取方式交易“条款”字段示例净 30 天2% 10 Net 30见票即付
	发票日期 InvoiceDate	供应商发票上打印的日期。
描述供应商提供的单据日期。这通常是账龄计算的起点。用于计算“端到端处理周期时间”。此日期与 NetSuite 中的创建日期之间的差距可能表明存在扫描或收发室延迟。为何重要标记供应商时间线的正式开始。获取方式交易“交易日期”字段示例 2023-10-012023-10-05
	可用折扣 DiscountAvailable	早期付款可获得的潜在折扣金额。
描述根据发票金额和付款条件计算出的早期付款折扣的货币价值。它代表了潜在的节省。此属性是“早期付款折扣获取率”仪表板的核心。通过对逾期付款的该数值求和，分析师可以计算出总计错失的节省金额。为何重要量化流程加速带来的财务机会。获取方式根据条款和发票金额计算示例 30.000.0015.50
	延期天数 DaysOverdue	到期日之后发生付款的天数。
描述衡量相对于到期日支付延迟的计算指标。负值表示提前付款。此属性用于“逾期付款与罚金追踪”dashboard。它对于量化逾期付款的严重程度以及识别长期逾期付款模式至关重要。为何重要分析付款合规性和罚金风险的关键指标。获取方式计算结果：付款日期 - 到期日示例 50-2
	货币 Currency	发票开具的币种。
描述代表交易币种的 ISO 代码。这对跨国企业至关重要。用于在“发票金额”分析中对财务值进行归一化。它还有助于识别外汇处理是否增加了“付款已结清”活动的时间。为何重要对于正确的财务汇总和外汇 (FX) 分析是必要的。获取方式交易“币种”字段示例美元EURGBP

必填推荐可选

应付账款付款处理活动

这些是在事件日志中需要捕获的关键流程步骤和里程碑，用于准确发现您的付款处理生命周期。

5 推荐 9 可选

	活动	描述
	付款已处理	账单付款记录已成功过账，发票被标记为“已全部支付”或“已部分支付”。总账中的负债得以解除。
为何重要付款流程的主要完成里程碑。用于计算“端到端处理周期时间”。获取方式交易表。识别“账单支付”记录的创建或状态更改为“已存入”（针对支票）或“已支付”。捕获记录执行账单支付交易的时间事件类型 explicit
	付款已清算	账单支付在银行对账过程中被标记为“已结清”。这确认了资金已离开银行账户。
为何重要为现金流分析提供真实的财务结束日期，区别于会计付款日期。获取方式交易行。账单支付交易行上的“已结清”标志被设为 True。捕获记录执行交易 X 的时间事件类型 explicit
	发票已与采购订单匹配	识别供应商账单是根据现有采购订单创建或与其关联的。这有助于区分基于 PO 的发票与非 PO 发票。
为何重要对于分析采购政策合规性以及对比 PO 与非 PO 发票的处理周期至关重要。获取方式交易表。检查供应商账单记录上的“创建自”字段。如果填充了采购订单 ID，则表示发生了此活动。捕获对比状态字段的前后变化事件类型 inferred
	发票已创建	在 NetSuite 中初始创建供应商账单记录。这标志着流程的开始，表示发票已录入 ERP。
为何重要为整个应付账款周期和账龄计算建立基准 timestamp。获取方式交易表。过滤记录类型为“供应商账单”的数据。获取创建日期或交易日期字段。捕获记录执行供应商账单交易的时间事件类型 explicit
	发票已审批	供应商账单状态更改为“打开”，表示它已被授权付款并已影响总账。这是付款前的关键里程碑。
为何重要结束审批周期并使发票进入付款计划阶段。对于衡量审批效率至关重要。获取方式系统说明。查看“approvalstatus”字段是否更改为“2”（已批准）或状态更改为“打开”。捕获对比状态字段的前后变化事件类型 inferred
	付款已作废	先前处理过的账单付款被作废，供应商账单重新开启。这代表了严重的异常和流程失效。
为何重要对于审计合规和识别付款失败原因（如支票打印错误、银行退回）至关重要。获取方式账单支付系统说明。状态更改为“已作废”。捕获记录执行交易 X 的时间事件类型 explicit
	付款已安排	账单付款记录以“待审批”状态创建。这通常发生在电子银行付款 (EFT) 批处理创建期间。
为何重要区分付款意向与实际资金执行，突出显示付款授权阶段的延误。获取方式交易表。创建关联至账单的“账单支付”记录，状态为“待审批”。捕获对比状态字段的前后变化事件类型 inferred
	发票已拒绝	供应商账单退回给提交者进行更正或拒绝。这代表审批工作流中的循环或终止。
为何重要高拒绝率通常意味着流程摩擦、培训不足或上游数据质量问题。获取方式系统说明。查看“approvalstatus”字段是否更改为“3”（已拒绝）。捕获对比状态字段的前后变化事件类型 inferred
	发票科目已修改	发票行项目上的总账费用科目已更改。这表明存在编码错误更正或重新分类。
为何重要直接衡量“发票编码返工率”KPI，并突出显示会计编码步骤中的效率损失。获取方式系统说明。查看“交易行”子列表上“科目”字段的更改。捕获记录执行交易 X 的时间事件类型 explicit
	发票金额已修改	供应商账单的总金额在初始创建后被更改。这通常意味着返工、数据录入错误或差异解决。
为何重要表示存在人工干预和返工，会对直通式处理率产生负面影响。获取方式系统说明。查看供应商账单记录上“合计”或“用户合计”字段的更改。捕获记录执行交易 X 的时间事件类型 explicit
	已应用付款暂缓	供应商账单上的“付款暂缓”复选框设为 True。这会阻止该发票被选中进入付款运行流程。
为何重要识别因争议或现金流管理决策而导致发票停滞的瓶颈。获取方式系统说明。查看“paymenthold”字段是否从“F”更改为“T”。捕获记录执行交易 X 的时间事件类型 explicit
	已应用供应商贷项	账单贷项通知单（供应商贷项）应用于供应商账单，减少了应付金额。这是现金支付的一种替代方式。
为何重要解释付款金额为何可能与发票金额不符，并正确结清负债。获取方式交易关联。识别“供应商贷项”类型的交易何时关联至供应商账单。捕获对比状态字段的前后变化事件类型 inferred
	已解除付款暂缓	“付款暂缓”复选框设为 False，释放发票以进行付款处理。这标志着阻塞期的结束。
为何重要用于计算“付款冻结时长”KPI，并了解争议发票的解决时间。获取方式系统说明。查看“paymenthold”字段是否从“T”更改为“F”。捕获记录执行交易 X 的时间事件类型 explicit
	已请求发票审批	供应商账单的状态更改为“待审批”。这表示发票已进入工作流路由进行授权。
为何重要标记审批周期时间的开始，以便识别授权链中的瓶颈。获取方式系统说明。查看“approvalstatus”字段是否更改为“1”（待审批）。捕获对比状态字段的前后变化事件类型 inferred

推荐可选

提取指南

如何从 NetSuite 获取您的数据

步骤

以管理员或拥有创建及导出“已保存搜索”权限的用户身份登录 NetSuite。
导航至“列表 > 搜索 > 已保存搜索 > 新建”。
从记录类型列表中选择“交易 (Transaction)”，开始新的交易搜索。
在“标准 (Criteria)”选项卡上，先确保未勾选“使用表达式”，然后添加筛选器：“类型 (Type)”，选择“供应商账单”、“账单付款”及“供应商贷项通知单”。
同样在“标准”选项卡中，添加筛选器：“主行 (Main Line)”，并设置为 True。这确保我们关注的是抬头级别的创建和状态event。
为了捕获历史记录，在筛选列表（下拉菜单底部）中找到“系统注释 (System Notes)”关联项，并添加“系统注释：日期”。设置您所需的日期范围（例如最近 90 天内）。
切换到“结果 (Results)”选项卡。移除所有默认列。
添加 Case ID 列：选择“交易名称”或“文档编号”。分配自定义标签：InvoiceNumber。
添加 Event Time 列：选择“系统注释字段... > 日期”。分配自定义标签：EventTime。
添加 Activity 列：选择“公式（文本）”。在公式窗口中，粘贴下方“查询”部分提供的 CASE 语句。该逻辑会将 NetSuite 字段变更映射为您的标准活动名称。分配自定义标签：Activity。
使用“公式（文本）”添加其他必需属性（Source System, Last Data Update），并使用静态值（如 'NetSuite'）和内置的 {today} 标签。
点击“保存并运行”。结果加载后，点击工具栏左上角的“导出 - CSV”图标，下载供 ProcessMind 使用的事件日志。

配置

交易类型：严格筛选“供应商账单”、“账单付款”和“供应商贷项通知单”，以排除日记账分录或采购订单的干扰。
System Notes 关联：此配置高度依赖 System Notes 关联来生成历史日志而非当前状态快照。请确保您的用户角色拥有查看 System Notes 的权限。
日期范围：请将日期筛选应用于“System Notes：日期”字段，而非交易的“日期”字段。这能确保捕获到旧发票的最新活动（例如今天支付了去年创建的发票）。
主行 (Main Line)：建议设置“主行 = True”以减少行数。但如果您需要追踪“发票科目修改”，则可能需要设置“主行 = 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”权限，以及对交易、系统注释（System Notes）和实体的“查看”权限。
准备环境：打开您的 SQL 客户端（如 DBeaver、DataGrip 或专用 ETL 工具），并建立与 NetSuite 数据源的连接。
定义范围：确定要提取的具体子公司 ID 和日期范围。更新提供的查询语句中名为 'VendorBills' 的公用表表达式 (CTE) 里的 WHERE 子句。
执行查询：运行提供的 SQL 脚本。该脚本会聚合来自 'Transaction'、'TransactionLine'、'SystemNote' 和 'TransactionLink' 表的数据，以重构流程历史。
监控执行情况：处理大型数据集时，NetSuite ODBC 连接可能会超时。如果查询失败，请考虑使用 'trandate' 筛选器按月或季度拆分提取任务。
验证数据：检查输出结果中“发票创建”event与“付款处理”event的分布是否合理。确保每一行都填写了 'InvoiceNumber'。
导出数据：将查询结果保存为 CSV 文件或直接导入您的数据帧。确保字符编码设置为 UTF-8，以正确处理供应商名称中的特殊字符。
格式化日期：在导入 ProcessMind 时，确保 'EventTime' 列被解析为日期时间对象 (YYYY-MM-DD HH:MM:SS)。
处理空值：在 'User' 列中，空值通常表示系统生成的event（如计划脚本）。如果需要，您可以在后期处理中将其替换为 'System'。
最终检查：确认 'Activity' 列包含所需的特定event名称（例如“发票创建”、“付款处理”），且未发生映射错误。

配置

日期范围：使用 'Transaction.trandate' 进行筛选。初次进行 Process Mining 分析时，建议提取 3-12 个月的数据。
子公司筛选：如果您的 NetSuite 环境是 OneWorld，请务必通过 'Transaction.subsidiary' 筛选，以确保分析的是正确的法律实体。
System Notes：数据提取高度依赖 'SystemNote' 表来获取审批和变更日志。请确保“仅在更新时记录系统注释”设置不会阻碍关键 workflow 步骤的捕获。
交易状态：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 账号。必须在“设置 > 公司 > 启用功能”中开启“SuiteTalk REST Web Services”和“SuiteAnalytics Connect”功能。
集成设置：前往“设置 > 集成 > 管理集成 > 新建”。创建一个名为 'ProcessMining_ETL' 的集成。勾选“Rest Web Services”和“基于令牌的身份验证”。记录生成的 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 表以获取创建和付款event，并查询 SystemNote 表以获取审计追踪event（审批、变更、拦截）。
执行：通过命令行运行脚本：python netsuite_extract.py。脚本会自动处理分页（SuiteQL 每页生成 1,000 条记录）。
数据处理：脚本会自动将 JSON 响应转换为适合 Process Mining 的扁平表格格式，并处理列映射和日期格式化。
验证：脚本会打印提取的总行数。如有需要，可对照 NetSuite 的“已保存搜索”进行核实。
导出：最终的数据帧将保存为 netsuite_ap_event_log.csv。该文件可直接上传至 ProcessMind。
上传：将 CSV 导入 ProcessMind。将 InvoiceNumber 映射为 Case ID，Activity 映射为 Activity，并将 EventTime 映射为 Timestamp。

配置

身份验证：采用 OAuth 1.0a（基于令牌的身份验证），这是 NetSuite REST API 的标准安全模型。
Batch Size：脚本每页请求 1,000 行（NetSuite SuiteQL 的默认最大值）。
日期筛选：配置为按 trandate（交易）和 date（系统注释）>= START_DATE 进行筛选，以优化性能。
范围：筛选 Transaction 表，类型包括 'VendBill'（供应商账单）、'BillPymt'（账单付款）和 'VendCred'（供应商贷项通知单）。
System Notes：该表对于捕获审批流转和变更至关重要。由于数据量可能较大，请确保集成用户拥有查询 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 应付账款交易保存的搜索导出

步骤

配置

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

我们重视您的隐私

准备好开始了吗？

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