您的应收账款数据模板

NetSuite

← 返回改进指南

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

您的应收账款数据模板

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

用于深度分析的NetSuite关键属性
标准化应收账款活动跟踪节点
NetSuite系统数据提取指导

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

下载模板

应收账款属性

下表列出了提供NetSuite发票生命周期完整视图所需的推荐数据字段和维度属性。

5 必需 7 建议 10 可选

	名称	说明
	事件时间戳 DateCreated	Activity发生的准确日期和时间。
说明此属性记录事件发生的准确时刻。对于发票创建事件，它对应Transaction表中的`datecreated`字段；对于后续更新，则对应`SystemNote`或`Message`表中的时间戳。准确的时间戳对于计算周期时间至关重要，例如平均发票到现金周期时间和发票开具提前期。它们可帮助分析人员正确排列事件顺序，并识别具体流程步骤之间的延迟。为什么重要用于排列事件顺序，并计算流程步骤之间的时长指标。获取位置 Transaction的'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`。为什么重要这是跟踪应收账款生命周期和计算周期时间的基础键。获取位置 Transaction表，字段'tranid' 示例 INV-2023-001INV-998822CM-4022JE-10023
	活动 Activity	在发票上执行的具体事件或操作。
说明此属性表示流程步骤名称，例如“Invoice Created”“Payment Applied”或“Dispute Opened”。在NetSuite中，创建事件通常取自交易的`type`字段，状态变更和修改则通常取自`SystemNote`表。它是流程挖掘的核心属性，用于定义流程图中的节点。通过分析这些Activity的顺序，分析人员可以识别瓶颈、循环（例如反复处理争议）以及偏离标准顺畅路径的情况。为什么重要定义工作流步骤，并支持构建流程图。获取位置取自Transaction的'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环境中，此属性标识开具发票的具体法人实体或子公司，支持跨不同分支机构或事业部进行比较分析。该属性支持Invoice Dispatch Efficiency仪表板，用户可以下钻到具体业务单元，判断流程问题是组织层面的系统性问题，还是局限于特定运营单元。为什么重要对于比较不同组织实体的绩效至关重要。获取位置 Transaction表，字段'subsidiary' 示例美国西部EMEA运营APAC销售
	到期日 DueDate	根据付款条款，预计应完成付款的日期。
说明此属性保存发票的计划到期日，取自NetSuite中的`duedate`字段。它是判断付款是否按时完成的基准。它是计算条款外平均天数和识别高价值催收风险的主要变量。将该日期与付款事件的`DateCreated`进行比较，可以了解延迟程度，并衡量客户对信用条款的遵守情况。为什么重要所有账龄、逾期和合规计算的参考点。获取位置 Transaction表，字段'duedate' 示例 2023-11-152023-12-012024-01-30
	发票状态 Status	发票当前的系统状态。
说明此属性反映NetSuite中交易的状态，例如“Open”“Paid In Full”或“Pending Approval”。它通常取自`status`或`statusRef`字段。 Activity属性记录发生过什么，而此属性提供当前状态快照。它对于筛选High Value Collection Risk仪表板至关重要，可仅显示当前未结且逾期的项目。为什么重要直观显示哪些案例已关闭，哪些仍需处理。获取位置 Transaction表，字段'status' 示例已全额付款未结清待审批
	发票金额 TotalAmount	发票的财务总金额。
说明此属性表示发票总金额，通常取自NetSuite Transaction表中的`foreignTotal`或`total`字段，即需要向客户收取的金额。该指标用于分析应收账款流程中的价值流，优先处理高价值催收，并计算争议积压金额等KPI。它还支持按金额层级筛选案例，将优化工作聚焦于影响较大的交易。为什么重要对于量化流程低效的财务影响和确定催收优先级至关重要。获取位置 Transaction表，字段'foreignTotal'或'total' 示例 1500.00250.5010000.00
	客户名称 Entity	与发票关联的客户或实体名称。
说明此属性标识负责支付发票的客户。在NetSuite中，它关联到`Entity`表中的Customer记录，是所有客户维度分析的基础。分析人员可利用该字段按客户细分绩效，识别经常延迟付款、发起争议或偏离标准付款条款的客户。它支持Credit Terms Compliance Monitor和Collection Agent Throughput仪表板。为什么重要支持根据客户行为和风险特征细分流程绩效。获取位置 Transaction表，字段'entity'（连接到Entity/Customer表）示例 Acme CorpGlobex CorporationSoylent Corp
	部门 Department	负责该交易的内部部门。
说明此属性映射到NetSuite的`department`维度，表示生成发票的职能领域，例如Sales、Services或IT，支持跨职能分析应收账款绩效。按部门细分数据后，分析人员可以识别哪些运营领域更容易出现开票周期较长或争议率较高的问题，从而开展有针对性的流程改进。为什么重要识别内部流程负责人，并反映不同部门的开票效率差异。获取位置 Transaction表，字段'department' 示例专业服务软件销售硬件履约
	销售代表 SalesRep	与发票关联的销售代表。
说明此属性标识负责该客户或交易的销售代表。在NetSuite中，通常对应交易记录中的`salesrep`字段。它用于将开票问题或贷项通知单与具体销售人员关联。例如，Credit Memo and Revenue Leakage仪表板可借此识别某些销售代表的发票后调整率是否较高，从而发现销售流程上游潜在的质量问题。为什么重要将下游应收账款问题追溯到上游销售流程负责人。获取位置 Transaction表，字段'salesrep' 示例 John SmithSarah ConnorKyle Reese
	争议原因 DisputeReason	发票进入争议状态的原因代码或说明。
说明此属性记录客户拒绝付款或要求更正的具体原因。在NetSuite中，它通常来自自定义字段，或与发票关联的特定“Case”记录。它是Dispute Lifecycle Analysis仪表板的关键属性。按争议原因分组，有助于识别“Pricing Error”“Damaged Goods”或“Missing PO”等根因，并推动上游系统性改进，避免未来收入流失。为什么重要识别未付款和行政反复处理的根因。获取位置关联的Support Case或Custom Transaction Body Field 示例价格错误未收到货物税务错误
	产品类别 Class	所售商品或服务的类别或分类。
说明此属性映射到NetSuite的`class`维度，用于对收入来源进行分类，例如Hardware或Consulting。了解产品组合对于分析至关重要。它支持Credit Memo and Revenue Leakage仪表板的分析，用于判断某些产品线是否更容易出现退货或开票错误。为什么重要按所售产品或服务类型细分流程绩效。获取位置 Transaction表，字段'class' 示例许可收入咨询服务硬件
	付款条款 Terms	发票约定的付款条件。
说明此属性指定发票适用的付款条款，例如“Net 30”或“Due on Receipt”，取自Transaction记录的`terms`字段。该数据用于计算预计付款日期，并分析宽松条款客户是否比严格条款客户更可靠地付款。它有助于审查信用政策，并明确条款外平均天数KPI的计算基准。为什么重要定义客户付款周期的预期行为和SLA。获取位置 Transaction表，字段'terms' 示例 30天账期60天账期收到即付2%10天内付款，30天账期
	催收专员 CollectionAgent	负责该发票催收工作的用户。
说明此属性标识负责发票催收工作的具体员工。他可能与Sales Rep是同一人，也可能是专职应收账款专员。该信息通常来自自定义字段，或根据记录“Payment Reminder”活动的用户推导得出。此属性驱动Collection Agent Throughput仪表板，帮助管理人员比较催收团队绩效，识别培训需求或工作量失衡。为什么重要支持评估催收团队成员的个人绩效。获取位置催收Activity中的SystemNotes'name'或Custom Field 示例 Smith代理账单专员A催收团队1
	区域 Location	与交易关联的地点或区域。
说明在NetSuite中，`location`字段通常表示仓库、办公室或地理区域。此属性将该字段映射为用于分析的区域概念。它用于DSO Performance Overview，识别不同区域在付款行为上的差异，并帮助判断催收问题是全球性的，还是集中在特定地区。为什么重要支持按地理区域细分应收账款绩效。获取位置 Transaction表，字段'location' 示例北美仓库伦敦办公室新加坡
	发票备注 Memo	附加在发票抬头上的备注或说明。
说明此属性包含发票中录入的自由文本说明或备注，取自`memo`字段。它通常记录特殊处理要求或客户约定。对该字段进行文本挖掘，可以发现结构化字段无法体现的延迟或争议原因，为量化流程数据补充定性背景。为什么重要为交易提供背景和定性数据。获取位置 Transaction表，字段'memo' 示例十月服务费发票#1002更正已应用特殊付款条款
	币种代码 Currency	开具发票时使用的货币。
说明此属性指定交易使用的ISO货币代码（例如USD、EUR），取自NetSuite中的`currency`字段，是正确处理多币种环境的必要条件。它通常是后台属性，但在汇总`TotalAmount`时至关重要，可避免未换算就错误合计不同货币。它还为区域绩效差异提供背景信息。为什么重要对于全球组织进行准确的财务汇总至关重要。获取位置 Transaction表，字段'currency' 示例 USDEURGBP
	承诺付款日期 PromiseDate	客户承诺发送付款的日期。
说明此属性记录客户承诺支付逾期发票的日期，通常由催收人员录入。在NetSuite中，它通常存储在“Phone Call”记录或自定义交易主体字段中。该日期是Promise to Pay Compliance仪表板的基础。将承诺日期与实际付款日期比较，组织可以衡量客户承诺的可靠性和催收电话的有效性。为什么重要衡量客户承诺的可靠性和催收工作的有效性。获取位置 Task/Phone Call记录或Custom Field 示例 2023-11-202023-12-05
	是否存在争议 IsDisputed	表示发票当前或曾经处于争议状态的标志。
说明此布尔属性表示发票是否经历过争议流程。它根据是否存在争议相关Activity，或发票记录中的特定状态标志推导得出。它是Dispute Lifecycle Analysis的重要筛选条件，并有助于在计算First-Pass Collection Rate时，将“问题”发票与“正常”发票区分开来。为什么重要将复杂案例与标准顺畅路径案例区分开来。获取位置根据是否存在'Dispute Opened'Activity计算示例 truefalse
	逾期天数 DaysOverdue	未结发票超过到期日的天数。
说明这是一个计算属性，表示`DueDate`与当前分析日期之间的差值；如果案例已关闭，则使用付款日期。它为账龄分析提供持续性指标。它是High Value Collection Risk仪表板中账龄区间（例如30至60天、60至90天）的主要依据，可快速识别系统中最严重的延迟。为什么重要评估付款延迟严重程度的标准指标。获取位置计算方式：（当前日期－DueDate）示例 150-5

必需建议可选

应收账款活动

这些是应采集到事件日志中的关键流程步骤和交易里程碑，用于准确建模收款流程。

6 建议 7 可选

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

建议可选

提取指南

如何从NetSuite获取数据

步骤

前置条件：确保已启用“SuiteAnalytics Workbook”功能，并拥有访问Transactions、System Notes、Messages和Support Cases的权限。
访问SuiteAnalytics：导航至Analytics > Workbooks > New Workbook。
选择根记录：选择交易作为根记录类型，作为财务数据的锚点。
数据集策略：由于需要将多种活动（审批、消息、争议）提取到单一的“现状”事件日志中，在界面中使用单个连接数据集会产生宽表行（笛卡尔积）。推荐的专家做法是使用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）是否以不同的“活动”值多次出现，例如已创建、已批准、已收到付款。
导入：将CSV上传至ProcessMind。将“TranId”映射为案例ID，将“Activity”映射为活动名称，将“DateCreated”映射为时间戳。

配置

SuiteAnalytics Connect：必须在Enable Features > Analytics中启用，才能在外部运行复杂的UNION查询。
权限：用户需要拥有Transactions、System Notes、Support Cases和Messages的“View”访问权限。
日期筛选：建议使用滚动12个月的范围。大规模历史数据提取可能超时，请确认行数限制（NetSuite通常会根据接口将结果限制在5,000至100,000行；完整历史数据建议使用ODBC）。
子公司范围：如果使用OneWorld账户，请确保用户有权访问所有目标子公司，或按t.subsidiary进行筛选。
性能：SystemNote表规模很大。为保证性能，必须按recordtype和date进行筛选。

a 示例查询 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”权限。通过REST或已配置的ODBC驱动连接进行身份验证时，需要有效的Token ID和Token Secret。
了解架构：此提取依赖交易、TransactionLine、SystemNote、Message、备注和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（Invoice）、CustPymt（Payment）、CustCred（Credit Memo）和Journal。
系统备注：该表规模庞大。为保证性能，必须按recordtype = 'invoice'筛选。
状态映射：NetSuite状态使用内部ID（例如“A”“B”）。查询通过BUILTIN.DF获取显示名称，但请确认这些名称与本地化界面标签一致。
连接策略：使用transaction.id上的标准连接。确保“Applied To”链接在付款历史中保持完整。

a 示例查询 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账户的Administrator访问权限。依次进入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。为Administrator角色（或拥有Transactions、System Notes和Lists权限的特定角色）创建新令牌。复制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，将活动映射为活动，将DateCreated映射为时间戳。确保将“金额”和“状态”映射为案例属性。

配置

身份验证方式：OAuth 1.0a（Token Based Authentication）。这是NetSuite REST API安全性的强制标准。
API端点：https://[AccountID].suitetalk.api.netsuite.com/services/rest/query/v1/suiteql。SuiteQL端点位于REST架构中，用于对System Notes进行复杂筛选和连接。
并发治理：NetSuite会根据服务层级限制并发量（通常为5至20个并发线程）。此脚本以单线程运行，以避免出现“429 Too Many Requests”错误。
日期格式：NetSuite支持自定义日期格式。脚本默认使用标准ISO格式或标准美国格式；如果您的实例使用DMY格式，可能需要调整日期解析方式。
查询限制：SuiteQL每页最多返回1,000行。脚本通过while循环处理hasMore标记和偏移量或范围，实现大数据集分页。
所需权限：用户角色必须拥有以下对象的“View”权限：Invoice、Customer Payment、Credit Memo、Journal Entry、Support Case、Message和System Note。

a 示例查询 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应收账款周期

立即将DSO缩短15至20天，弥合现金流缺口。

开始免费试用

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

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

提取指南

NetSuite SuiteAnalytics Workbook数据导出

步骤

配置

a 示例查询 sql

NetSuite SuiteQL交易数据提取

步骤

配置

a 示例查询 sql

Python SuiteTalk REST API提取脚本

步骤

配置

a 示例查询 python

准备好开始了吗？

立即加快NetSuite应收账款周期

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款活动

提取指南

NetSuite SuiteAnalytics Workbook数据导出

步骤

配置

a 示例查询 sql

NetSuite SuiteQL交易数据提取

步骤

配置

a 示例查询 sql

Python SuiteTalk REST API提取脚本

步骤

配置

a 示例查询 python

准备好开始了吗？

立即加快NetSuite应收账款周期

我们重视您的隐私