您的应收账款数据模板

NetSuite

← 返回改进指南

应收账款属性(22) 应收账款活动(13) 数据提取指南(3)

您的应收账款数据模板

此模板为您提供在NetSuite中梳理开票与催收周期的完整蓝图，列出了构建高质量事件日志所需的关键属性和流程活动，帮助您全面了解流程并优化现金流。按照本指南准备数据，可简化数据整理工作，并加快缩短应收账款周转天数的进程。

支持深入分析的NetSuite关键属性
标准化的应收账款活动跟踪节点
NetSuite系统数据提取实用指南

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

下载模板

应收账款属性

下表列出了全面了解NetSuite发票生命周期所需的建议数据字段和维度属性。

5 必填 7 建议填写 10 选填

	名称	说明
	事件时间戳 DateCreated	活动发生的准确日期和时间。
说明此属性记录事件发生的准确时间。发票创建时间取自交易表中的`datecreated`字段；后续更新的时间则取自`SystemNote`或“消息”表中的时间戳。准确的时间戳对于计算周期时间至关重要，例如平均发票到收款周期时间和发票开具提前期。借助这些时间戳，您可以按正确顺序排列事件，并识别流程各步骤之间的延迟。重要性用于排列事件顺序并计算流程步骤之间的时长指标。获取途径交易的'datecreated'字段或SystemNote的'date'字段示例 2023-10-15T08:30:00Z2023-10-15T14:45:22Z2023-11-01T09:00:00Z
	发票编号 TranId	NetSuite中分配给发票的唯一参考编号。
说明此属性是应收账款流程的主要案例标识。在NetSuite中，`tranid`字段通常存储单据编号（例如INV-10023），供用户和客户查看。它将付款、争议和贷项通知单等后续事件关联到特定的财务义务。分析时，此标识用于将各个事件归入同一个流程案例，从而端到端呈现订单到收款周期。它对于关联NetSuite中的不同表（如`交易`、`TransactionLine`和`SystemNote`）中的数据至关重要。重要性这是跟踪应收账款生命周期和计算周期时间的基础标识。获取途径交易表，字段'tranid' 示例 INV-2023-001INV-998822CM-4022JE-10023
	活动 Activity	对发票执行的具体事件或操作。
说明此属性表示流程步骤名称，例如“发票已创建”“已应用付款”或“已发起争议”。在NetSuite中，创建事件通常取自交易的`type`字段；状态变更和修改则通常取自`SystemNote`表。它是流程挖掘的核心属性，用于定义流程图中的节点。通过分析活动顺序，分析人员可以发现瓶颈、循环（例如反复处理争议）以及偏离标准理想路径的情况。重要性定义工作流步骤，并用于构建流程图。获取途径取自交易的'type'字段或SystemNote的'field'/'value'字段示例发票已创建已收到付款贷项通知单已开具已发起争议
	最近数据更新时间 LastDataUpdate	数据提取或最近刷新的时间戳。
说明此属性表示从NetSuite获取数据快照的时间。它用于判断分析数据的新鲜度，并根据分析日期计算未结案例的账龄。在仪表板中，它有助于用户判断查看的是实时数据还是历史提取数据。对于尚未结清的发票，此时间也可作为计算当前未结时长的参考点。重要性确保数据新鲜度清晰可见，并帮助计算未结案例的持续时长。获取途径提取时的系统时间示例 2023-12-01T00:00:00Z2023-12-02T12:00:00Z
	源系统 SourceSystem	提取数据的记录系统。
说明此属性标识数据来源，在此处为“NetSuite”。在应收账款数据可能与CRM或外部银行系统数据合并的多系统环境中，该字段可确保数据可追溯。如果同时分析多个ERP系统，分析人员可以按数据源筛选或细分流程视图。此属性通常是在数据提取和转换过程中添加的固定值。重要性在多系统流程挖掘实施中提供数据沿袭信息和背景。获取途径固定值'NetSuite' 示例 NetSuiteNetSuite ERPNetSuite OneWorld
	业务单元 Subsidiary	发票所属的子公司或业务单元。
说明在NetSuite OneWorld环境中，此属性用于标识开具发票的具体法人实体或子公司，支持比较公司不同分支机构或部门的表现。此属性支持“发票发送效率”仪表板，用户可逐级下钻至具体业务单元，判断流程问题是普遍存在于整个组织，还是仅限于特定运营单元。重要性对于比较不同组织实体的绩效至关重要。获取途径交易表，'subsidiary'字段示例美国西部欧洲、中东和非洲运营部亚太销售部
	到期日 DueDate	根据付款条款约定的付款截止日期。
说明此属性存储发票的计划到期日，取自NetSuite中的`duedate`字段。它是判断付款是否按时或逾期的基准。该属性是计算平均逾期天数和识别高金额催收风险的主要变量。将此日期与付款事件的`DateCreated`进行比较，可以了解延迟程度并衡量客户是否遵守信用条款。重要性用于计算账龄、逾期情况和合规指标的参考点。获取途径交易表，'duedate'字段示例 2023-11-152023-12-012024-01-30
	发票状态 Status	发票在系统中的当前状态。
说明此属性反映交易在NetSuite中的状态，例如“未结”“已全额付款”或“待审批”，通常取自`status`或`statusRef`字段。活动属性记录已发生的事件，此属性则提供当前状态快照。它对于筛选“高金额催收风险”仪表板至关重要，可用于仅显示当前未结且逾期的项目。重要性快速查看哪些案例已关闭，哪些仍需处理。获取途径交易表，'status'字段示例已全额付款未结待审批
	发票金额 TotalAmount	发票的总金额。
说明此属性表示发票总金额，通常取自NetSuite交易表中的`foreignTotal`或`total`字段，即客户应付金额。此指标用于分析应收账款流程中的金额流转、优先处理高金额催收，并计算争议积压金额等KPI。按金额等级筛选案例，有助于将优化工作聚焦于影响较大的交易。重要性对于量化流程低效造成的财务影响和确定催收优先级至关重要。获取途径交易表，'foreignTotal'或'total'字段示例 1500.00250.5010000.00
	客户名称 Entity	与发票关联的客户或实体名称。
说明此属性用于标识负责支付发票的客户。在NetSuite中，它关联到`Entity`表中的客户记录，是所有客户维度分析的基础。分析人员可使用此字段按客户细分绩效，识别经常逾期付款、引发争议或偏离标准付款条款的客户。该属性也支持“信用条款合规监控”和“催收专员处理量”仪表板。重要性支持按客户行为和风险状况细分流程绩效。获取途径交易表，'entity'字段（关联Entity/Customer表）示例 Acme CorpGlobex CorporationSoylent Corp
	部门 Department	负责该交易的内部部门。
说明此属性对应NetSuite的`department`分类，表示开具发票的职能部门（例如销售、服务或IT），支持跨部门分析应收账款绩效。按部门细分数据后，分析人员可以识别哪些运营部门的开票周期较长或争议率较高，从而有针对性地改进流程。重要性用于识别内部流程负责人，并比较各部门的开票效率。获取途径交易表，'department'字段示例专业服务软件销售硬件履约
	销售代表 SalesRep	与发票关联的销售代表。
说明此属性用于标识负责该客户账户或交易的销售代表。在NetSuite中，通常取自交易记录的`salesrep`字段。它可用于将开票问题或贷项通知单与具体销售人员关联起来。例如，“贷项通知单与收入流失”仪表板可借此查看某些销售代表的发票后调整率是否偏高，从而发现销售流程前端可能存在的质量问题。重要性将应收账款后续问题关联回销售流程的负责人。获取途径交易表，'salesrep'字段示例 John SmithSarah ConnorKyle Reese
	争议原因 DisputeReason	发票发生争议的原因代码或说明。
说明此属性记录客户拒绝付款或要求更正的具体原因。在NetSuite中，它通常来自自定义字段，或与发票关联的特定“案例”记录。它对于“争议生命周期分析”仪表板至关重要。按争议原因分组有助于识别根本原因，例如“价格错误”“货物损坏”或“缺少采购订单”，以便从流程前端进行系统性改进，避免未来收入流失。重要性识别未付款和行政流程反复处理的根本原因。获取途径关联的支持案例或自定义交易主体字段示例价格错误未收到货物税务错误
	产品类别 Class	所售商品或服务的类别或类型。
说明此属性对应NetSuite的`class`分类，用于划分收入来源（例如硬件或咨询服务）。了解产品组合对于分析至关重要。通过此属性，可在“贷项通知单与收入流失”仪表板中分析不同产品线，判断哪些产品更容易发生退货或开票错误。重要性按所售产品或服务类型细分流程绩效。获取途径交易表，'class'字段示例许可证收入咨询服务硬件
	付款条款 Terms	发票约定的付款条件。
说明此属性指定发票的付款条款，例如“Net 30”或“收到即付”，取自交易记录的`terms`字段。这些数据用于计算预期付款日期，并分析采用宽松条款的客户是否比采用严格条款的客户更可靠地付款。它有助于审查信用政策，并了解平均逾期天数KPI的计算基准。重要性定义客户付款周期的预期行为和SLA。获取途径交易表，'terms'字段示例 Net 30Net 60收到即付2% 10 Net 30
	催收专员 CollectionAgent	负责催收此发票的用户。
说明此属性用于标识负责该发票催收工作的具体员工。该员工可能是销售代表，也可能是专职应收账款专员；信息通常来自自定义字段，或根据记录“付款提醒”活动的用户推导得出。此属性为“催收专员处理量”仪表板提供数据，管理者可据此比较催收团队成员的绩效，并发现培训需求或工作量失衡。重要性支持评估催收团队成员的个人绩效。获取途径催收活动中SystemNotes的'name'字段或自定义字段示例 Smith专员开票专员A催收团队1
	发票备注 Memo	附在发票抬头上的备注或说明。
说明此属性包含发票中填写的自由文本说明或备注，取自`memo`字段。内容通常包括特殊处理要求或客户协议等手动备注。对该字段进行文本挖掘，可以发现结构化字段未能体现的延迟或争议原因，为定量流程数据补充定性背景。重要性为交易提供背景信息和定性数据。获取途径交易表，'memo'字段示例十月服务费更正发票#1002已应用特殊付款条款
	地区 Location	与交易关联的地点或地区。
说明在NetSuite中，`location`字段通常表示仓库、办公室或地理区域。此属性将该字段映射为地区概念，以便开展分析。它用于“DSO绩效概览”，帮助识别各地区付款行为的差异，并判断催收问题是普遍存在，还是仅限于特定区域。重要性支持按地理区域细分应收账款绩效。获取途径交易表，'location'字段示例北美仓库伦敦办公室新加坡
	承诺付款日期 PromiseDate	客户承诺付款的日期。
说明此属性记录客户承诺支付逾期发票的日期，通常由催收专员录入。在NetSuite中，该信息通常存储在“电话”记录或自定义交易主体字段中。此日期对于“承诺付款履约”仪表板至关重要。将承诺日期与实际付款日期进行比较，组织便可衡量客户承诺的可靠性以及催收电话的效果。重要性衡量客户承诺的可靠性和催收效果。获取途径 Task/电话记录或自定义字段示例 2023-11-202023-12-05
	是否存在争议 IsDisputed	标记发票当前或曾经是否存在争议。
说明此布尔属性表示发票是否经历过争议流程，可根据争议相关活动或发票记录中的特定状态标记推导得出。它是“争议生命周期分析”的重要筛选条件；计算首次催收成功率时，可用它将“问题”发票与正常发票区分开来。重要性将复杂案例与标准理想路径案例区分开来。获取途径根据是否存在“已发起争议”活动计算示例 truefalse
	货币代码 Currency	开具发票所用的货币。
说明此属性指定交易使用的ISO货币代码（例如USD、EUR），取自NetSuite中的`currency`字段。在多币种环境中，正确处理交易时需要此属性。虽然它通常作为后台字段使用，但汇总`TotalAmount`时必须考虑该属性，避免未换算不同币种就直接相加。它也为地区绩效差异提供背景信息。重要性对于全球化组织正确汇总财务数据至关重要。获取途径交易表，'currency'字段示例 USDEURGBP
	逾期天数 DaysOverdue	未结发票超过到期日的天数。
说明这是一个计算属性，表示`DueDate`与当前分析日期之间的间隔（已结清的发票则使用付款日期计算），用于持续衡量账龄。它是“高金额催收风险”仪表板中账龄区间（例如30至60天、60至90天）的主要依据，可帮助快速识别系统中最严重的延迟。重要性用于评估付款延迟严重程度的标准指标。获取途径计算方式：（当前日期-DueDate）示例 150-5

必填建议填写选填

应收账款活动

要准确呈现催收流程，应在事件日志中记录以下关键流程步骤和交易节点。

6 建议填写 7 选填

	活动	说明
	发票已创建	在系统中创建发票记录。此事件从Transaction表中类型为Invoice的记录获取。
重要性标志着应收账款周期开始，并为账龄计算设定基准。计算开票到收款周期时间时必不可少。获取途径 Transaction表：类型为CustInvc的记录，使用“trandate”或“datecreated”字段。采集交易Invoice执行时记录事件类型 explicit
	发票已发送	通过电子邮件、邮寄或传真向客户发送发票。通过查找与特定Invoice交易关联的Message记录捕获此事件。
重要性这是发票发送周期时间指标的关键环节。找出此处的延迟，有助于发现阻碍及时收款的行政流程瓶颈。获取途径 Messages表：按交易ID关联，其中“authoremail”为内部邮箱，“recipientemail”为外部邮箱。采集交易Email执行时记录事件类型 explicit
	发票已批准	发票状态从Pending Approval变为Open。通过监控与发票关联的System Notes或工作流历史记录捕获此事件。
重要性表示向客户开票前的内部处理时间。此处的延迟会延长总周期，却不会为客户增加价值。获取途径 System Notes：筛选“status”字段从“Pending Approval”变为“Open”的记录。采集比较变更前后的状态字段事件类型 inferred
	发票已结清	发票状态最终变为“Paid In Full”。这是成功案例的流程结束状态。
重要性计算平均开票到收款周期时间时必不可少，也用于在流程变体中区分未结项目和已结项目。获取途径 System Notes：Invoice记录的状态变为“Paid In Full”。采集比较变更前后的状态字段事件类型 inferred
	已收到全额付款	应用Customer Payment后，发票余额降为零。这标志着催收工作实际结束。
重要性这是计算应收账款周转天数和超出条款天数平均值的主要时间戳，代表收入已成功收回。获取途径 Transaction表：类型为CustPymt的记录应用于Invoice，且Invoice的“status”变为“Paid In Full”。采集交易Payment执行时记录事件类型 explicit
	贷项通知单已开具	创建应用于发票的Credit Memo交易。此操作会减少未结余额，通常表示发生了退货或开票错误。
重要性用于呈现收入流失情况。频繁开具贷项通知单，可能表明销售或履约流程存在系统性问题。获取途径 Transaction表：类型为CustCred的记录，通过“appliedtotransaction”关联到Invoice ID。采集交易Credit Memo执行时记录事件类型 explicit
	争议已解决	争议案例结束，发票恢复为可催收状态或产生贷项。通过关联的Support Case状态变为Closed捕获此事件。
重要性计算平均争议解决时间时必不可少。争议解决时间过长会对应收账款周转天数和客户满意度造成负面影响。获取途径 Support Case记录：使用与发票关联案例的“dateclosed”字段。采集比较变更前后的状态字段事件类型 inferred
	争议案例已创建	针对发票发起争议，通常根据关联的Support Case或特定自定义状态推断。此事件会中断常规催收流程。
重要性这是争议生命周期分析仪表板的关键指标。此类案例数量较多，可能表明履约或开票的前端环节存在质量问题。获取途径 Support Case记录：关联到category为“Billing Dispute”的Transaction，或捕获Transaction状态变为“Disputed”的记录。采集比较状态字段或关联记录的创建情况事件类型 inferred
	付款提醒已发送	生成并发送给客户的催收邮件或信函，提醒其支付逾期款项。通过催款信模块日志或与发票关联的Message记录捕获此事件。
重要性衡量催收现金所需的工作量和催款策略的成效，是催收提醒效果仪表板的重要指标。获取途径 Message记录或Dunning Bundle Log：检查特定主题行，例如“Reminder”或“Overdue”。采集根据邮件主题模式推断事件类型 inferred
	坏账已核销	通过日记账分录或特定类型的Credit Memo冲销无法收回的发票余额，代表催收流程未能成功。
重要性在分析中区分未收回收入和成功收款，有助于准确了解催收效率。获取途径 Journal Entry或Credit Memo：筛选与Invoice关联的特定总账科目（Bad Debt Expense）。采集根据总账科目使用情况推断事件类型 inferred
	已收到付款承诺	记录客户承诺在特定日期前付款。通常记录在User Notes中，或专用的催收管理套件中。
重要性跟踪付款承诺履约率指标，帮助区分愿意配合的客户和需要升级处理的客户。获取途径 Note记录：分析文本中的“PTP”或“Promise”，或检查Collections SuiteApp中的特定字段。采集通过分析备注文本推断事件类型 inferred
	部分付款已入账	应用Customer Payment后，发票余额有所减少但未完全结清。此事件从Transaction Line表中获取。
重要性支持部分付款流程分析，帮助发现现金流分散以及流动性困难的客户。获取途径 Transaction表：类型为CustPymt的记录应用于Invoice，且Invoice的“amountremaining”大于0。采集交易Payment执行时记录事件类型 explicit
	银行对账单已匹配	将付款交易与银行对账单数据进行核对。通过“Match Bank Data”或对账历史记录捕获此事件。
重要性跟踪银行自动清算率。此处的延迟会掩盖真实现金状况，也可能表明后台流程存在人工处理阻碍。获取途径交易状态：检查Payment交易状态是否变为“Deposited”，或是否标记为已对账。采集比较变更前后的状态字段事件类型 inferred

建议填写选填

数据提取指南

如何从NetSuite获取数据

步骤

前提条件：确保已启用“SuiteAnalytics Workbook”功能，并拥有访问交易、系统备注、消息和支持案例的权限。
访问SuiteAnalytics：依次进入分析>工作簿>新建工作簿。
选择根记录：将交易选为根记录类型，作为财务数据的锚点。
数据集策略：由于需要将审批、消息和争议等不同活动提取到同一个实际事件日志中，在界面中使用单个联接数据集会生成宽行（笛卡尔积）。建议由熟悉系统的人员采用SuiteAnalytics底层的SuiteQL功能。您可以通过“SuiteAnalytics Connect”（ODBC/JDBC）接口，或在界面中使用“SuiteQL Query Tool”套件，执行下方提供的查询。
执行查询：复制“Query”部分提供的完整SuiteQL脚本。将占位符{{LastDataUpdate}}替换为数据提取时间戳，将{{StartDate}}替换为分析起始日期（例如“2023-01-01”）。
核对列：确认结果中包含所有选定的列（TranId、Activity、DateCreated等）。
导出数据：运行查询，并将结果导出为CSV。
格式设置：打开CSV文件，确保“DateCreated”列采用YYYY-MM-DD HH:MM:SS格式。“TotalAmount”列仅保留数值，不含货币符号。
验证：检查同一个发票ID（TranId）是否出现多次，且对应不同的“Activity”值（例如已创建、已批准、已收到付款）。
导入：将CSV上传到ProcessMind。将“TranId”映射为案例ID，将“Activity”映射为活动名称，将“DateCreated”映射为时间戳。

配置

SuiteAnalytics Connect：必须在“启用功能”>“分析”中启用，才能通过外部工具运行复杂的UNION查询。
权限：用户需要拥有交易、系统备注、支持案例和消息的“查看”权限。
日期筛选：建议使用滚动12个月的日期范围。提取大量历史数据可能超时；请确认行数限制（NetSuite会根据接口将结果限制在5,000至100,000行；提取完整历史数据建议使用ODBC）。
子公司范围：如果账户使用OneWorld，请确保用户有权访问所有目标子公司，或按t.subsidiary筛选。
性能：SystemNote表数据量很大。为保证性能，必须按recordtype和date筛选。

查询示例 sql

/* SuiteQL Query for Accounts Receivable Process Mining */
/* Combined Event Log Extraction */

/* 1. Invoice Created */
SELECT
  t.tranid AS TranId,
  'Invoice Created' AS Activity,
  TO_CHAR(t.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  emp.entityid AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
  LEFT JOIN Employee emp ON t.salesrep = emp.id
WHERE
  t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Approved (via System Notes) */
SELECT
  t.tranid AS TranId,
  'Invoice Approved' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND sn.recordtypeid = -30 /* Transaction Record Type */
  AND sn.standardfield = 'STATUS'
  AND sn.oldvalue = 'Pending Approval'
  AND sn.newvalue = 'Open'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Dispatched / 4. Payment Reminder (via Messages) */
SELECT
  t.tranid AS TranId,
  CASE 
    WHEN m.subject LIKE '%Reminder%' THEN 'Payment Reminder Sent'
    ELSE 'Invoice Dispatched'
  END AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND (m.subject LIKE '%Invoice%' OR m.subject LIKE '%Reminder%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 5. Dispute Opened / 6. Dispute Resolved (via Support Cases) */
SELECT
  t.tranid AS TranId,
  CASE 
    WHEN sc.status = 'Closed' THEN 'Dispute Resolved'
    ELSE 'Dispute Case Opened'
  END AS Activity,
  TO_CHAR(sc.createddate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SupportCase sc ON sc.custevent_linked_invoice = t.id /* Adjust custom field name */
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 7. Promise to Pay (via Notes) */
SELECT
  t.tranid AS TranId,
  'Promise to Pay Received' AS Activity,
  TO_CHAR(n.notedate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN Note n ON n.transaction = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND (n.title LIKE '%Promise%' OR n.note LIKE '%Promise%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 8. Credit Memo Issued */
/* Logic: Find Credit Memos applied to the Invoice */
SELECT
  inv.tranid AS TranId,
  'Credit Memo Issued' AS Activity,
  TO_CHAR(cm.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  cm.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  inv.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  inv.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction inv
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = inv.id
  JOIN Transaction cm ON ntll.nextdoc = cm.id
  LEFT JOIN Customer c ON inv.entity = c.id
  LEFT JOIN Subsidiary s ON inv.subsidiary = s.id
WHERE
  inv.type = 'CustInvc'
  AND cm.type = 'CustCred'
  AND inv.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 9. Partial Payment / 10. Full Payment Received */
SELECT
  inv.tranid AS TranId,
  CASE
    WHEN (inv.foreigntotal - py.foreigntotal) <= 0 THEN 'Full Payment Received'
    ELSE 'Partial Payment Posted'
  END AS Activity,
  TO_CHAR(py.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  py.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  inv.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  inv.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction inv
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = inv.id
  JOIN Transaction py ON ntll.nextdoc = py.id
  LEFT JOIN Customer c ON inv.entity = c.id
  LEFT JOIN Subsidiary s ON inv.subsidiary = s.id
WHERE
  inv.type = 'CustInvc'
  AND py.type = 'CustPymt'
  AND inv.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 11. Bad Debt Written Off */
SELECT
  t.tranid AS TranId,
  'Bad Debt Written Off' AS Activity,
  TO_CHAR(je.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = t.id
  JOIN Transaction je ON ntll.nextdoc = je.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND je.type = 'Journal'
  AND (je.memo LIKE '%Bad Debt%' OR je.memo LIKE '%Write Off%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 13. Invoice Cleared */
SELECT
  t.tranid AS TranId,
  'Invoice Cleared' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  'Paid In Full' AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND sn.standardfield = 'STATUS'
  AND sn.newvalue = 'Paid In Full'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

步骤

前提条件与权限：确保您的角色具有“SuiteAnalytics Connect”或“SuiteQL”权限。您需要有效的Token ID和Token Secret，以通过REST进行身份验证；也可以使用已配置的ODBC驱动程序连接。
了解数据结构：此数据提取依赖交易、TransactionLine、SystemNote、“消息”、“备注”和SupportCase表。NetSuite会在SystemNote中存储历史字段变更（例如状态更新），这对于获取审批和状态变更的时间戳至关重要。
确定提取范围：确定分析所需的子公司、日期范围和记录类型（发票通常为“CustInvc”）。
配置SuiteQL查询：提供的查询使用UNION ALL将不同表中的数据合并为统一的事件日志格式。NetSuite SuiteQL不支持复杂的过程式SQL，因此每项活动的逻辑都被展开为独立的查询块。
执行策略：通过NetSuite REST API的/services/rest/query/v1/suiteql端点或ODBC执行查询。由于SystemNote表可能包含大量数据，强烈建议按trandate分批提取，例如逐月提取。
处理分页：NetSuite REST API通常将响应限制为1000行。您必须在提取脚本中实现偏移量处理或遍历“Link”响应头，以获取完整数据集。
转换数据：查询会将所有结果整理为标准结构（TranId、Activity、Date等）。如果原始SQL输出使用了其他区域格式，请在导出时将日期转换为ISO 8601格式（YYYY-MM-DD THH:MM:SS）。
争议与催收逻辑：查询会查找与客户关联的SupportCase记录，以及表示“付款承诺”的“备注”记录。请确认贵组织是否使用自定义字段而非标准对象记录这些信息，并据此调整WHERE子句。
验证数据：将提取文件中的“发票已创建”数量与NetSuite中同一时间段的已保存搜索结果进行比较，确保数据完整。
导出文件：将结果保存为CSV文件。上传至ProcessMind前，请确认列标题与“待包含的属性”列表一致，例如TranId、Activity、DateCreated等。

配置

日期范围：按Transaction表中的trandate和SystemNote表中的date筛选。对于活跃流程，建议使用滚动12个月的时间范围。
子公司筛选：在多子公司环境中必须设置。请在所有WHERE子句中添加AND t.subsidiary = [Your Subsidiary ID]。
记录类型：查询针对CustInvc（发票）、CustPymt（付款）、CustCred（贷项通知单）和Journal。
系统备注：此表数据量巨大。为保证性能，必须按recordtype = 'invoice'筛选。
状态映射：NetSuite状态使用内部ID（例如“A”“B”）。查询使用BUILTIN.DF获取显示名称，但请确认这些名称与本地化界面标签一致。
联接策略：使用transaction.id进行标准联接。请确保“应用至”关联在付款历史记录中完整保留。

查询示例 sql

/* 1. Invoice Created */
SELECT
  t.tranid AS TranId,
  'Invoice Created' AS Activity,
  TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
WHERE
  t.recordtype = 'invoice'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Approved (Detected via SystemNote status change to Open) */
SELECT
  t.tranid AS TranId,
  'Invoice Approved' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
WHERE
  t.recordtype = 'invoice'
  AND sn.recordtypeid = -30 /* Invoice Record Type ID */
  AND sn.field = 'status'
  AND (sn.after LIKE '%Open%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Dispatched (Email Sent) */
SELECT
  t.tranid AS TranId,
  'Invoice Dispatched' AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND m.subject LIKE '%Invoice%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 4. Payment Reminder Sent */
SELECT
  t.tranid AS TranId,
  'Payment Reminder Sent' AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND (m.subject LIKE '%Reminder%' OR m.subject LIKE '%Overdue%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 5. Dispute Case Opened (Linked Support Case) */
SELECT
  t.tranid AS TranId,
  'Dispute Case Opened' AS Activity,
  TO_CHAR(sc.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SupportCase sc ON sc.company = t.entity
WHERE
  t.recordtype = 'invoice'
  AND sc.title LIKE '%Invoice%'
  AND sc.createddate >= t.createddate
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 6. Dispute Resolved */
SELECT
  t.tranid AS TranId,
  'Dispute Resolved' AS Activity,
  TO_CHAR(sc.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SupportCase sc ON sc.company = t.entity
WHERE
  t.recordtype = 'invoice'
  AND sc.status = 'Closed'
  AND sc.title LIKE '%Invoice%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 7. Promise to Pay Received */
SELECT
  t.tranid AS TranId,
  'Promise to Pay Received' AS Activity,
  TO_CHAR(n.notedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Note n ON n.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND (n.note LIKE '%Promise to Pay%' OR n.note LIKE '%PTP%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 8. Credit Memo Issued */
SELECT
  t.tranid AS TranId,
  'Credit Memo Issued' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'creditmemo'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 9. Partial Payment Posted */
SELECT
  t.tranid AS TranId,
  'Partial Payment Posted' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND ABS(tl.foreignamount) < t.foreigntotal
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 10. Full Payment Received */
SELECT
  t.tranid AS TranId,
  'Full Payment Received' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND ABS(tl.foreignamount) >= (t.foreigntotal - 1.00) /* Approx check for full payment allowing small variance */
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 11. Bad Debt Written Off */
SELECT
  t.tranid AS TranId,
  'Bad Debt Written Off' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'journalentry'
  AND (link_t.memo LIKE '%Write off%' OR link_t.memo LIKE '%Bad Debt%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 12. Bank Statement Matched (via Payment Status) */
SELECT
  t.tranid AS TranId,
  'Bank Statement Matched' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
  JOIN SystemNote sn ON sn.recordid = link_t.id
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND sn.field = 'status'
  AND sn.after LIKE '%Deposited%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 13. Invoice Cleared (Final Status) */
SELECT
  t.tranid AS TranId,
  'Invoice Cleared' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
WHERE
  t.recordtype = 'invoice'
  AND sn.recordtypeid = -30
  AND sn.field = 'status'
  AND (sn.after LIKE '%Paid In Full%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

步骤

前提条件和权限设置 确保您拥有NetSuite账户的管理员访问权限。依次进入“Setup”>“Company”>“Enable Features”。在SuiteTalk选项卡下，确保已启用“REST Web Services”。然后依次进入“Setup”>“Integration”>“Manage Integrations”，创建名为“ProcessMining_Extractor”的新集成。启用“Token-based Authentication”；如果使用标准令牌生成方式，请取消选中“TBA: Authorization Flow”。记下Consumer Key和Consumer Secret。
生成访问令牌 进入“Manage Access Tokens”。为管理员角色（或拥有交易、系统备注和列表权限的特定角色）创建新令牌。复制Token ID和Token Secret。现在您已获得OAuth 1.0a所需的四个密钥。
准备Python环境 确保已安装Python 3.x。使用pip安装所需的库：pip install requests requests-oauthlib pandas。这些库用于处理OAuth 1.0a握手和数据操作。
配置脚本参数 打开提供的Python脚本，将ACCOUNT_ID、CONSUMER_KEY、CONSUMER_SECRET、TOKEN_ID和TOKEN_SECRET占位符替换为您的凭据。将TARGET_START_DATE设为所需的历史分析起始日期（例如“2023-01-01”）。
了解API策略 脚本使用NetSuite REST API的/query/v1/suiteql端点。虽然也可以逐条遍历记录，但通过REST API使用SuiteQL进行流程挖掘效率更高，因为它可以通过更少的HTTP请求联接Transaction、TransactionLine、Message和SystemNote表。
运行提取脚本 在终端或IDE中运行脚本。脚本会分五轮提取数据：主发票流程、付款、贷项通知单、通信记录（消息）和系统状态变更（审计跟踪）。
监控运行情况 脚本会在控制台显示进度和各类活动检索到的记录数。请留意HTTP 400或500错误，这通常表示权限不足或SuiteQL语法有误。
数据转换与合并 脚本会自动转换数据，将NetSuite特定的日期格式转换为标准ISO 8601时间戳，并将tranid等字段名称规范为“案例ID”，同时根据要求将特定交易类型映射为标准活动名称。
处理后续逻辑 脚本会比较已应用的付款金额与原始发票总额，以区分“已登记部分付款”和“已收到全额付款”。脚本还会根据最终状态变更推断“发票已结清”。
检查输出文件 脚本运行完成后会生成netsuite_ar_event_log.csv文件。打开文件检查前几行，确认TranId已填入，且DateCreated格式正确。
导入ProcessMind 生成的CSV已按ProcessMind要求设置格式。将TranId映射为案例ID，将Activity映射为活动，将DateCreated映射为时间戳。请将Amount和Status映射为案例属性。

配置

身份验证方式：OAuth 1.0a（基于令牌的身份验证）。这是NetSuite REST API要求使用的安全标准。
API端点：https://[AccountID].suitetalk.api.netsuite.com/services/rest/query/v1/suiteql。REST架构中的SuiteQL端点支持复杂筛选和系统备注联接。
并发限制：NetSuite会根据服务层级限制并发数（通常为5至20个并发线程）。此脚本采用单线程运行，以避免出现“429 Too Many Requests”错误。
日期格式：NetSuite支持自定义日期格式。脚本默认使用标准ISO格式或标准美国格式；如果您的实例使用DMY格式，可能需要调整日期解析逻辑。
查询限制：SuiteQL每页最多返回1,000行。脚本通过while循环处理hasMore标记及偏移量或范围，支持对大型数据集进行分页提取。
所需权限：用户角色必须拥有以下对象的“查看”权限：发票、客户付款、贷项通知单、日记账分录、支持案例、消息和系统备注。

查询示例 python

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
class=class="hl-string">"hl-comment"># Replace these with your actual NetSuite credentials
ACCOUNT_ID = class="hl-string">'123456_SB1'  class=class="hl-string">"hl-comment"># Use hyphens for sandbox, e.g., 123456-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'
TARGET_START_DATE = class="hl-string">'2023-01-01'

class=class="hl-string">"hl-comment"># NetSuite REST API Base URL (Format account ID correctly for URL)
class=class="hl-string">"hl-comment"># For URL, usually lowercase and hyphens: 123456-sb1
NS_ACCOUNT_URL_PART = ACCOUNT_ID.lower().replace(class="hl-string">'_', class="hl-string">'-')
BASE_URL = fclass="hl-string">'https://{NS_ACCOUNT_URL_PART}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># Authentication
auth = OAuth1(CONSUMER_KEY, CONSUMER_SECRET, TOKEN_ID, TOKEN_SECRET, realm=ACCOUNT_ID)
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"># === HELPER FUNCTION ===
def run_suiteql(query):
    all_rows = []
    offset = 0
    limit = 1000
    has_more = True
    
    print(fclass="hl-string">"Executing Query starting with: {query[:50]}...")
    
    while has_more:
        class=class="hl-string">"hl-comment"># Note: Pagination in SuiteQL via REST often involves ROWNUM logic or limits
        class=class="hl-string">"hl-comment"># Standard REST paging links are preferred, but for SuiteQL body, we iterate logic
        class=class="hl-string">"hl-comment"># For simplicity in this script, we assume the query returns < 100k or we would implement ID-based iteration.
        class=class="hl-string">"hl-comment"># Ideally, we inject LIMIT/OFFSET if supported or filter by ID ranges.
        class=class="hl-string">"hl-comment"># Here we use a robust wrapper handling the class="hl-string">'items' response.
        
        class=class="hl-string">"hl-comment"># Adjust query for paging if necessary, or rely on requests if result set is small.
        class=class="hl-string">"hl-comment"># For production large scale, add class="hl-string">'AND rownum between X and Y' logic.
        
        payload = {class="hl-string">"q": query}
        response = requests.post(BASE_URL, auth=auth, headers=headers, json=payload)
        
        if response.status_code == 200:
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_rows.extend(items)
            
            class=class="hl-string">"hl-comment"># Check for generic SuiteTalk pagination links
            if len(items) < limit:
                has_more = False
            else:
                class=class="hl-string">"hl-comment"># In strict SuiteQL, you must handle offset manually in SQL
                class=class="hl-string">"hl-comment"># For this script, we assume strict date filtering keeps batches manageable
                has_more = False class=class="hl-string">"hl-comment"># Preventing infinite loop in this demo template
        else:
            print(fclass="hl-string">"Error {response.status_code}: {response.text}")
            has_more = False
            
    return all_rows

class=class="hl-string">"hl-comment"># === EXTRACTION LOGIC ===

events = []

class=class="hl-string">"hl-comment"># 1. INVOICES (Created, Due Date, Attributes)
print(class="hl-string">"Extracting Invoices...")
q_invoices = fclass="hl-string">"""
    SELECT 
        t.tranid,
        'Invoice Created' as activity,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        t.foreignTotal as amount,
        e.entityid as customer,
        t.duedate,
        s.name as subsidiary,
        emp.entityid as salesrep,
        t.status,
        d.name as department
    FROM 
        transaction t
        LEFT JOIN entity e ON t.entity = e.id
        LEFT JOIN subsidiary s ON t.subsidiary = s.id
        LEFT JOIN employee emp ON t.salesrep = emp.id
        LEFT JOIN department d ON t.department = d.id
    WHERE 
        t.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
invoices = run_suiteql(q_invoices)
for row in invoices:
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'tranid'),
        class="hl-string">'Activity': row.get(class="hl-string">'activity'),
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'TotalAmount': row.get(class="hl-string">'amount'),
        class="hl-string">'Entity': row.get(class="hl-string">'customer'),
        class="hl-string">'DueDate': row.get(class="hl-string">'duedate'),
        class="hl-string">'Subsidiary': row.get(class="hl-string">'subsidiary'),
        class="hl-string">'SalesRep': row.get(class="hl-string">'salesrep'),
        class="hl-string">'Status': row.get(class="hl-string">'status'),
        class="hl-string">'Department': row.get(class="hl-string">'department'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })

class=class="hl-string">"hl-comment"># 2. PAYMENTS (Partial, Full, Bank Match)
print(class="hl-string">"Extracting Payments...")
q_payments = fclass="hl-string">"""
    SELECT 
        inv.tranid as invoice_num,
        t.tranid as payment_ref,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        tl.foreignAmount as payment_amt,
        inv.foreignTotal as invoice_total,
        t.status as payment_status
    FROM 
        transaction t
        JOIN transactionLine tl ON t.id = tl.transaction
        JOIN transaction inv ON tl.createdfrom = inv.id
    WHERE 
        t.recordtype = 'customerpayment'
        AND inv.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
payments = run_suiteql(q_payments)
for row in payments:
    class=class="hl-string">"hl-comment"># Logic to distinguish Partial vs Full
    act = class="hl-string">'Partial Payment Posted'
    class=class="hl-string">"hl-comment"># Simple heuristic: if remaining amount is small or payment equals total
    class=class="hl-string">"hl-comment"># In a real scenario, we would check class="hl-string">'amountremaining' on the invoice
    try:
        if float(row.get(class="hl-string">'payment_amt', 0)) >= float(row.get(class="hl-string">'invoice_total', 0)):
            act = class="hl-string">'Full Payment Received'
    except:
        pass
        
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': act,
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })
    
    class=class="hl-string">"hl-comment"># Bank Match / Cleared Check
    class=class="hl-string">"hl-comment"># Assuming class="hl-string">'deposited' status or similar indicates bank matching
    if row.get(class="hl-string">'payment_status') == class="hl-string">'deposited':
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': class="hl-string">'Bank Statement Matched',
            class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class=class="hl-string">"hl-comment"># Using payment date as proxy if match date unavailable
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 3. CREDIT MEMOS & BAD DEBT
print(class="hl-string">"Extracting Credit Memos...")
q_credits = fclass="hl-string">"""
    SELECT 
        inv.tranid as invoice_num,
        t.tranid as credit_ref,
        t.recordtype,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        t.memo
    FROM 
        transaction t
        JOIN transactionLine tl ON t.id = tl.transaction
        JOIN transaction inv ON tl.createdfrom = inv.id
    WHERE 
        t.recordtype IN ('creditmemo', 'journalentry')
        AND inv.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
credits = run_suiteql(q_credits)
for row in credits:
    act = class="hl-string">'Credit Memo Issued'
    if class="hl-string">'write off' in str(row.get(class="hl-string">'memo')).lower() or class="hl-string">'bad debt' in str(row.get(class="hl-string">'memo')).lower():
        act = class="hl-string">'Bad Debt Written Off'
        
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': act,
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })

class=class="hl-string">"hl-comment"># 4. MESSAGES (Dispatch, Reminders)
print(class="hl-string">"Extracting Messages...")
q_msgs = fclass="hl-string">"""
    SELECT 
        t.tranid as invoice_num,
        m.subject,
        TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        m.authoremail
    FROM 
        message m
        JOIN transaction t ON m.transaction = t.id
    WHERE 
        t.recordtype = 'invoice'
        AND m.messagedate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
msgs = run_suiteql(q_msgs)
for row in msgs:
    subj = str(row.get(class="hl-string">'subject')).lower()
    act = None
    if class="hl-string">'invoice' in subj and class="hl-string">'attached' in subj:
        act = class="hl-string">'Invoice Dispatched'
    elif class="hl-string">'reminder' in subj or class="hl-string">'overdue' in subj:
        act = class="hl-string">'Payment Reminder Sent'
        
    if act:
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': act,
            class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 5. SUPPORT CASES (Disputes)
print(class="hl-string">"Extracting Support Cases...")
class=class="hl-string">"hl-comment"># Requires linking Case to Invoice (often custom field or text match)
class=class="hl-string">"hl-comment"># Here assuming generic link via Customer + Time logic or direct field if exists
class=class="hl-string">"hl-comment"># Using standard support case table
q_cases = fclass="hl-string">"""
    SELECT 
        c.casenumber,
        c.title,
        c.status,
        TO_CHAR(c.createddate, 'YYYY-MM-DD HH24:MI:SS') as create_time,
        TO_CHAR(c.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') as close_time,
        t.tranid as invoice_num
    FROM 
        supportcase c
        JOIN transaction t ON c.company = t.entity -- Loose join by customer
    WHERE 
        t.recordtype = 'invoice'
        AND (c.title LIKE '%' || t.tranid || '%' OR c.incomingmessage LIKE '%' || t.tranid || '%')
        AND c.createddate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
cases = run_suiteql(q_cases)
for row in cases:
    class=class="hl-string">"hl-comment"># Dispute Opened
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': class="hl-string">'Dispute Case Opened',
        class="hl-string">'DateCreated': row.get(class="hl-string">'create_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })
    class=class="hl-string">"hl-comment"># Dispute Resolved (if closed)
    if class="hl-string">'closed' in str(row.get(class="hl-string">'status')).lower():
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': class="hl-string">'Dispute Resolved',
            class="hl-string">'DateCreated': row.get(class="hl-string">'close_time'),
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 6. SYSTEM NOTES (Approvals, Status Changes, Cleared)
print(class="hl-string">"Extracting System Notes...")
class=class="hl-string">"hl-comment"># This query can be heavy. Ensure proper filtering.
q_notes = fclass="hl-string">"""
    SELECT 
        t.tranid as invoice_num,
        sn.field,
        sn.oldvalue,
        sn.newvalue,
        TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') as event_time
    FROM 
        systemnote sn
        JOIN transaction t ON sn.recordid = t.id
    WHERE 
        sn.recordtypeid = -30 -- Transaction Record Type ID usually -30, verify in setup
        AND t.recordtype = 'invoice'
        AND sn.date >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
notes = run_suiteql(q_notes)
for row in notes:
    field = str(row.get(class="hl-string">'field')).lower()
    old_v = str(row.get(class="hl-string">'oldvalue')).lower()
    new_v = str(row.get(class="hl-string">'newvalue')).lower()
    
    if class="hl-string">'status' in field:
        if class="hl-string">'pending approval' in old_v and class="hl-string">'open' in new_v:
            events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Invoice Approved', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})
        elif class="hl-string">'paid in full' in new_v:
            events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Invoice Cleared', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})
            
    class=class="hl-string">"hl-comment"># Promise to Pay check (User Notes often appear in System Notes or separate Note table)
    class=class="hl-string">"hl-comment"># Assuming Note capture via System Note for context
    if class="hl-string">'note' in field: 
        events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Promise to Pay Received', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})

class=class="hl-string">"hl-comment"># === EXPORT ===
print(class="hl-string">"Generating CSV...")
df = pd.DataFrame(events)

class=class="hl-string">"hl-comment"># Deduplicate and Sort
df.drop_duplicates(inplace=True)
df[class="hl-string">'LastDataUpdate'] = datetime.now().isoformat()

class=class="hl-string">"hl-comment"># Save
df.to_csv(class="hl-string">'netsuite_ar_event_log.csv', index=False)
print(class="hl-string">"Done. File saved as netsuite_ar_event_log.csv")

准备好开始了吗？

将此模板应用于您的NetSuite环境，开启提升现金周转效率的进程。如果您需要数据映射或技术集成方面的帮助，我们的团队随时为您提供支持。

返回改进指南

下载模板

立即加快NetSuite应收账款周转

将应收账款周转天数缩短15至20天，及时弥补现金流缺口。

开始免费试用

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

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

数据提取指南

NetSuite SuiteAnalytics Workbook数据导出

步骤

配置

查询示例 sql

NetSuite SuiteQL交易数据提取

步骤

配置

查询示例 sql

Python SuiteTalk REST API提取脚本

步骤

配置

查询示例 python

准备好开始了吗？

立即加快NetSuite应收账款周转