您的应收账款数据模板

NetSuite

← 返回改进指南

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

您的应收账款数据模板

该模板为您在 NetSuite 中映射发票和催收周期提供了全面的蓝图。它概述了构建高质量事件日志所需的基本属性和流程活动，确保您拥有优化现金流所需的可见性。遵循此指南，您可以简化数据准备工作，并加速缩短应收账款周转天数。

深度分析所需的关键 NetSuite 属性
标准化的应收账款活动跟踪里程碑
NetSuite 系统的战略提取指南

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

下载模板

应收账款属性

下表列出了为完整呈现 NetSuite 发票生命周期建议的 data 字段和维度属性。

5 必填 7 推荐 10 可选

	名称	描述
	事件timestamp DateCreated	活动发生的精确日期和时间。
描述此属性记录 event 发生的精确时刻。对于发票的创建，这是交易表中的 `datecreated` 字段；对于后续更新，则是 `SystemNote` 或 `Message` 表中的 timestamp。准确的 timestamp 对于计算周期时间（如“平均发票到收款周期时间”和“发票开具提前期”）至关重要。它们能确保分析正确排列 event 顺序，识别各流程步骤间的延迟。为何重要对于排列事件顺序以及计算流程步骤之间的持续时间指标至关重要。获取方式交易 'datecreated' 或 SystemNote 'date' 示例 2023-10-15T08:30:00Z2023-10-15T14:45:22Z2023-11-01T09:00:00Z
	发票编号 TranId	NetSuite 中分配给发票的唯一参考编号。
描述此属性作为应收账款流程的主要 case 标识符。在 NetSuite 中，`tranid` 字段通常包含单据编号（如 INV-10023）。它将随后的所有 event（如付款、争议和贷项通知单）链接到特定的财务义务。在分析中，此标识符用于将单个 event 归组为单个流程 case。它实现了从订单到收款周期的端到端可视化，并且对于跨 NetSuite 表（如 `Transaction`、`TransactionLine` 和 `SystemNote`）连接 data 至关重要。为何重要这是跟踪应收账款生命周期和计算周期时间的核心键值。获取方式交易表，'tranid' 字段示例 INV-2023-001INV-998822CM-4022JE-10023
	活动 Activity	对发票执行的具体 event 或操作。
描述此属性表示流程步骤名称，如“已创建发票”、“已核销付款”或“争议已开启”。在 NetSuite 中，这通常派生自创建 event 的交易 `type` 字段，或派生自状态变更及修改的 `SystemNote` 表。它是 Process Mining 的核心，定义了流程图中的节点。通过分析这些活动的顺序，分析师可以识别瓶颈、循环（如重复的争议处理）以及与标准顺畅路径的偏差。为何重要定义工作流步骤并支持构建流程图。获取方式源自 Transaction 'type' 或 SystemNote 'field'/'value' 示例发票已创建已收到付款Credit Memo IssuedDispute Opened
	最后数据更新 LastDataUpdate	data 提取或上次刷新的 timestamp。
描述此属性指示从 NetSuite 获取 data 快照的时间。它用于确定分析的新鲜度，并计算未结 case 相对于分析日期的时长。在 Dashboard 仪表板中，它帮助用户了解查看的是实时数据还是历史提取。对于尚未结清的发票，它作为计算当前未结时长的参考点。为何重要确保数据新鲜度的透明化，并有助于计算未结 Case 的运行持续时间。获取方式提取时的系统时间示例 2023-12-01T00:00:00Z2023-12-02T12:00:00Z
	源系统 SourceSystem	提取 data 的记录系统。
描述此属性标识 data 的来源，在此场景下为 “NetSuite”。在应收账款 data 可能与 CRM 或外部银行系统合并的多系统环境中，此字段可确保可追溯性。如果同时分析多个 ERP，分析师可以按数据源过滤或细分流程视图。它通常是在提取和转换过程中添加的静态值。为何重要在多系统 Process Mining 实施中提供数据血缘和上下文。获取方式静态值 'NetSuite' 示例 NetSuiteNetSuite ERPNetSuite OneWorld
	业务单元 Subsidiary	发票所属的子公司或业务单位。
描述在 NetSuite OneWorld 环境中，此属性标识签发发票的具体法律实体或子公司。它支持对公司不同分支机构或部门进行对比分析。此属性通过允许用户钻取到特定业务单元，来支持“发票分发效率”仪表板。它有助于确定流程问题是全公司系统性的，还是局部存在于特定运营单位。为何重要对于在不同组织实体之间进行性能基准测试至关重要。获取方式交易表，'subsidiary' 字段示例美国西部欧洲、中东和非洲区运营 (EMEA)亚太区销售
	到期日期 DueDate	根据条款预期的付款日期。
描述此属性包含发票的计划到期日，位于 NetSuite 的 `duedate` 字段中。它是判断付款是否准时的基准。它是计算“平均超过期限天数”和识别“高价值催收风险”的主要变量。通过比较此日期与付款 event 的 `DateCreated`，可以揭示延迟程度并衡量对信用条款的遵守情况。为何重要所有账龄、逾期和合规计算的参考点。获取方式交易表，'duedate' 字段示例 2023-11-152023-12-012024-01-30
	发票状态 Status	发票当前的系统状态。
描述此属性反映了 NetSuite 中交易的状态，如“未结”、“已全额支付”或“待审批”。它通常派生自 `status` 或 `statusRef` 字段。 “活动”属性追踪发生过什么的历史记录，而此属性则提供当前的快照状态。对于过滤“高价值催收风险”Dashboard 仪表板、仅显示当前未结和逾期的项目至关重要。为何重要立即显示哪些 case 已结案，哪些需要采取行动。获取方式交易表，'status' 字段示例已全额支付未结待审批
	发票金额 TotalAmount	发票的总财务价值。
描述此属性代表发票的总金额，通常位于 NetSuite 交易表的 `foreignTotal` 或 `total` 字段中。它代表需要向客户收取的总价值。该指标用于分析应收账款流程中的价值流，优先处理高价值催收，并计算“争议待处理金额”等 KPI。它允许按财务等级过滤 case，以便将优化工作集中在对财务影响较大的交易上。为何重要对于量化流程效率低下的财务影响以及确定催收优先级至关重要。获取方式交易表，'foreignTotal' 或 'total' 字段示例 1500.00250.5010000.00
	客户名称 Entity	与发票关联的客户或实体名称。
描述此属性标识负责支付发票的客户。在 NetSuite 中，这链接到 `Entity` 表（客户记录）。它是所有以客户为中心分析的基础。分析师使用此字段按客户细分绩效，识别那些经常逾期付款、产生争议或偏离标准付款条件的客户。它为“信用条款合规监控”和“催收专员吞吐量”Dashboard 仪表板提供支持。为何重要支持按客户行为和风险概况对流程绩效进行细分。获取方式交易表，'entity' 字段（连接到实体/客户表）示例 Acme公司Globex CorporationSoylent Corp
	部门 Department	负责该交易的内部部门。
描述此属性映射到 NetSuite 的 `department` 细分，指示哪个职能部门（如销售、服务、IT）生成了发票。它支持对应收账款绩效进行跨部门分析。通过按部门细分数据，分析师可以识别特定业务领域是否容易出现较慢的计费周期或较高的争议率，从而辅助针对性的流程改进。为何重要识别内部流程所有者及其开票效率的差异。获取方式交易表，'department' 字段示例专业服务软件销售硬件履行
	销售代表 SalesRep	与发票关联的销售代表。
描述此属性标识负责该账户或交易的销售代表。在 NetSuite 中，这通常是交易记录上的 `salesrep` 字段。它用于将计费问题或贷项通知单与特定的销售人员关联。例如，“贷项通知单和收入流失”Dashboard 仪表板利用此属性来观察某些代表是否具有较高的发票后调整率，从而发现销售流程中潜在的上游质量问题。为何重要将下游的应收账款问题关联回上游的销售流程所有者。获取方式交易表，'salesrep' 字段示例约翰·史密斯Sarah ConnorKyle Reese
	争议原因 DisputeReason	发票处于争议中的原因代码或描述。
描述此属性捕获客户拒绝付款或要求更正的具体原因。在 NetSuite 中，这通常是自定义字段或链接到发票的特定“案件”记录。这对于“争议生命周期分析”Dashboard 仪表板至关重要。按争议原因进行分组有助于识别根本原因——如“定价错误”、“货物损坏”或“缺少采购订单”——这些原因需要上游的系统性修复，以防止未来的收入流失。为何重要识别未付款和行政耗损的根本原因。获取方式关联的 Support Case 或自定义交易主体字段示例价格错误未收到货物税务错误
	产品类别 Class	所售商品或服务的类别。
描述此属性映射到 NetSuite 的 `class` 细分。它对收入流进行分类（如硬件 vs. 咨询）。了解产品组合对分析至关重要。通过“贷项通知单和收入流失”Dashboard 仪表板，它可以分析特定产品线是否比其他产品线更容易发生退货或计费错误。为何重要按销售的产品或服务类型细分流程绩效。获取方式交易表，'class' 字段示例授权收入咨询服务硬件
	付款条款 Terms	发票约定的付款条件。
描述此属性指定分配给发票的付款条件，如“Net 30”或“到账即付”。它位于交易记录的 `terms` 字段中。此 data 用于计算预期付款日期，并分析付款条件宽松的客户是否比条件严格的客户回款更可靠。它有助于审查信用政策并了解“平均超过期限天数”KPI 的基准。为何重要定义客户付款周期的预期行为和 SLA。获取方式交易表，'terms' 字段示例净 30 天净 60 天见票即付2% 10 Net 30
	催收人员 CollectionAgent	负责此发票催收活动的用户。
描述此属性标识管理发票催收工作的特定员工。可能与销售代表相同，也可能是专门的应收账款专员，通常位于自定义字段中或派生自记录“付款提醒”活动的用户。此属性驱动“催收专员吞吐量”Dashboard 仪表板，方便管理者比较催收团队的绩效，识别培训需求或工作量不均的问题。为何重要支持对单个催收团队成员进行绩效评估。获取方式催收活动或自定义字段上的 SystemNotes“name” 示例 Agent Smith结算专员 A催收 1 组
	区域 Location	与交易关联的地点或区域。
描述在 NetSuite 中，`location` 字段通常代表仓库、办公室或地理区域。此属性将该字段映射为分析用的区域概念。它被用于“应收账款周转天数 (DSO) 绩效概览”，以识别不同区域在付款行为上的差异。这有助于回答催收问题是全球性的还是仅局限于特定地区。为何重要支持应收账款绩效的地理细分。获取方式交易表，'location' 字段示例北美仓库伦敦办公室新加坡
	发票备注 Memo	附加在发票顶部的备注或描述。
描述此属性包含发票上输入的自由文本描述或备注，位于 `memo` 字段中。它通常包含有关特殊处理或客户协议的人工标注。对该字段进行文本挖掘可以揭示结构化字段可能遗漏的非结构化延迟或争议原因。它为定量流程 data 增加了定性背景。为何重要提供有关交易的背景和定性 data。获取方式交易表，'memo' 字段示例十月份服务费对 Inv #1002 的修正已应用特殊付款条件
	承诺付款日期 PromiseDate	客户承诺付款的日期。
描述此属性记录客户承诺支付逾期发票的日期，通常由催收专员记录。在 NetSuite 中，这通常存储在“电话”记录或自定义交易正文字段中。此日期对于“承诺付款合规性”Dashboard 仪表板至关重要。通过将此承诺日期与实际付款日期进行对比，组织可以衡量客户承诺的可靠性和催收电话的有效性。为何重要衡量客户承诺的可靠性和催收效率。获取方式任务/电话记录或自定义字段示例 2023-11-202023-12-05
	是否有争议 IsDisputed	指示发票当前是否处于或曾经处于争议状态的标志。
描述此布尔属性指示发票是否经历了争议流程。它派生自发票记录上是否存在争议相关活动或特定的状态标志。它是“争议生命周期分析”的关键过滤器，在计算“首次催收成功率”时有助于将“问题”发票与“正常”发票区分开。为何重要将复杂 case 与标准顺畅路径 case 分开。获取方式根据是否存在 'Dispute Opened' Activity 进行计算示例 truefalse
	货币代码 Currency	开具发票的货币。
描述此属性指定交易的 ISO 货币代码（如 USD、EUR），位于 NetSuite 的 `currency` 字段中。对于正确处理多货币环境是必要的。虽然通常是后台字段，但在汇总 `TotalAmount` 时，它对于确保不同货币在没有转换的情况下不会被错误相加至关重要。它也为区域绩效差异提供了背景信息。为何重要对于全球性组织中正确的财务聚合至关重要。获取方式交易表，'currency' 字段示例美元EURGBP
	逾期天数 DaysOverdue	未结发票超过其到期日的天数。
描述这是一个计算属性，代表 `DueDate` 与当前分析日期（或结案时的付款日期）之间的差距。它为账龄分析提供了一个连续的指标。它是“高价值催收风险”Dashboard 仪表板中使用的账龄区间（如 30-60 天，60-90 天）的主要驱动因素，可以立即识别系统中最重要的延迟。为何重要评估付款延迟严重程度的标准指标。获取方式计算方式：(当前日期 - 截止日期) 示例 150-5

必填推荐可选

应收账款 Activities

这些是应在事件日志中捕获的关键流程步骤和交易里程碑，用于准确建模您的催收流程。

6 推荐 7 可选

	活动	描述
	Credit Memo Issued	应用于发票的贷项通知单交易的创建。这会减少未结余额，通常代表退货或计费错误。
为何重要可视化收入流失。频繁的贷项通知单通常表明销售或履约环节存在系统性流程失效。获取方式交易表：通过 'appliedtotransaction' 应用于发票 ID 的 'CustCred' 记录类型。捕获在执行贷记单交易时记录事件类型 explicit
	Full Payment Received	客户付款的应用使发票余额归零，标志着催收工作的有效结束。
为何重要 DSO 和“平均超过期限天数”计算的主要 timestamp，代表收入的成功实现。获取方式交易表：应用于发票的 'CustPymt' 类型，发票最终 'status' 变为“已全额支付”。捕获在执行付款交易时记录事件类型 explicit
	发票已创建	发票记录在系统中的初始创建。这从记录类型为“发票”的交易 (Transaction) 表中捕获。
为何重要标志着应收账款周转周期的开始，并为账龄计算建立基准。这对于计算“从发票到收款”的周期时间至关重要。获取方式交易表：类型为 'CustInvc' 记录的 'trandate' 或 'datecreated' 字段。捕获在执行发票交易时记录事件类型 explicit
	发票已审批	发票状态从“待审批”到“未结”的转换。通过监控与发票关联的系统备注或 workflow 历史日志来捕获。
为何重要指示向客户开票前的内部处理时间。此处的延迟会导致总周期变长，且不会为客户增加任何价值。获取方式系统备注：过滤“status”字段从“待审批”更改为“未结”的记录。捕获比较前/后状态字段事件类型 inferred
	发票已寄出	向客户发送发票的行为（通过电子邮件、邮寄或传真）。这可以通过查看链接到特定发票交易的“消息”记录来捕获。
为何重要对发票签发前置时间 KPI 至关重要。识别此处的延迟可揭示阻碍及时收款的行政瓶颈。获取方式消息表：在交易 ID 上连接，其中“authoremail”为内部，“recipientemail”为外部。捕获在执行电子邮件交易时记录事件类型 explicit
	发票已清算	发票状态最终更改为“已全额支付”。这作为成功 case 的流程结束状态。
为何重要计算“平均发票到收款周期时间”所必需。在流程变体中区分未结项与已结项。获取方式系统备注：发票记录的状态更改为“已全额支付”。捕获比较前/后状态字段事件类型 inferred
	Bank Statement Matched	付款交易与银行对账单流的核销。通过“匹配银行数据”或对账历史记录捕获。
为何重要跟踪自动化银行清算率。此处的延迟会掩盖真实的现金头寸，并表明后台存在人工操作摩擦。获取方式交易状态：检查付款交易状态是否更改为“已存入”或被标记为“已核销”。捕获比较前/后状态字段事件类型 inferred
	争议已解决	争议案件的结束，使发票恢复为可收回状态或产生贷项。当链接的支持案件状态转为“已关闭”时捕获。
为何重要计算“平均争议解决时间”所必需。解决时间过长会对应收账款周转天数 (DSO) 和客户满意度产生负面影响。获取方式支持案件记录：与发票关联的案件的“dateclosed”字段。捕获比较前/后状态字段事件类型 inferred
	争议案件已开启	针对发票提出的争议，通常由链接的支持案件或特定的自定义状态推断。这会中断标准的催收流程。
为何重要争议生命周期分析仪表板的关键指标。此处的高业务量通常表明上游的履行或开票环节存在质量问题。获取方式支持案件记录：连接到交易，其中类别为“账单争议”，或交易状态更改为“争议中”。捕获比较状态字段或关联记录的创建事件类型 inferred
	坏账核销	通过日记账分录或针对不可收回金额的特定贷项通知单类型冲销发票余额。这代表催收流程的失败。
为何重要在分析中将未收回的收入与成功催收的收入分开，这对于评估真实的催收效率至关重要。获取方式日记账分录 (Journal Entry) 或贷记单：通过链接到发票的特定总账账户 (坏账费用) 进行过滤。捕获源自总账账户的使用情况事件类型 inferred
	已入账部分付款	客户付款的应用减少了发票余额但未完全结清。从交易行 (Transaction Line) 表中捕获。
为何重要支持部分付款流程流分析。识别零散的现金流和面临流动性压力的客户。获取方式交易表：应用于发票的 'CustPymt' 类型，其中发票上的 'amountremaining' > 0。捕获在执行付款交易时记录事件类型 explicit
	已发送付款提醒	针对逾期付款生成并发送给客户的催收邮件或信函。通过 Dunning Letters 模块日志或与发票关联的 Message 记录进行捕获。
为何重要衡量收款所需的工作量以及催收策略的有效性。这对于“催收提醒有效性”Dashboard 仪表板至关重要。获取方式消息记录或催收包日志：检查特定主题行（如“提醒”、“逾期”）。捕获源自消息主题模式事件类型 inferred
	已收到付款承诺	指示客户已承诺在特定日期前付款的记录。通常记录在 User Notes 或专业的催收管理组件中。
为何重要跟踪承诺付款合规性 KPI。有助于区分配合良好的客户与需要升级处理的客户。获取方式备注记录：针对“PTP”或“承诺”进行文本分析，或 Collections SuiteApp 中的特定字段。捕获源自注释的文本分析事件类型 inferred

推荐可选

提取指南

如何从 NetSuite 获取您的数据

步骤

前提条件: 确保已启用“SuiteAnalytics Workbook”功能，并拥有访问 Transactions、System Notes、Messages 和 Support Cases 的权限。
访问 SuiteAnalytics: 导航至 分析 (Analytics) > 工作簿 (Workbooks) > 新建工作簿。
选择根记录: 选择 Transaction 作为根记录类型。这将作为财务数据的核心锚点。
数据集策略: 由于需要将多种 Activity (审批、消息、争议) 提取到单个事件日志中，在 UI 中使用单一关联数据集会产生宽行 (笛卡尔积)。专家推荐的方法是利用 SuiteAnalytics 底层的 SuiteQL 功能。您可以使用“SuiteAnalytics Connect” (ODBC/JDBC) 接口，或通过 UI 中的“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”值 (例如 Created、Approved、Payment Received) 多次出现。
摄取: 将 CSV 上传至 ProcessMind。将“TranId”映射到 Case ID，将“Activity”映射到 Activity Name，将“DateCreated”映射到 Timestamp。

配置

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 查询示例 config

/* 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 驱动程序连接。
了解架构: 此提取依赖于 Transaction、TransactionLine、SystemNote、Message、Note 和 SupportCase 表。NetSuite 将历史字段变更 (如状态更新) 存储在 SystemNote 中，这对于获取审批和状态变更的时间戳至关重要。
确定范围: 确定分析所需的特定子公司、日期范围和记录类型 (发票通常为 'CustInvc')。
配置 SuiteQL 查询: 提供的查询使用 UNION ALL 将来自不同表的数据合并为单一的事件日志格式。由于 NetSuite SuiteQL 不支持复杂的存储过程 SQL，因此逻辑被扁平化为针对每个 Activity 的特定选择块。
执行策略: 使用 NetSuite REST API /services/rest/query/v1/suiteql 或通过 ODBC 执行查询。由于 SystemNote 表可能非常庞大，强烈建议按 trandate 分批次 (例如按月) 运行提取。
分页处理: NetSuite REST API 通常限制每页 1,000 行。您必须在提取脚本中实现偏移量处理或“Link”头遍历，以检索完整的数据集。
数据转换: 查询会将所有结果对齐到标准架构 (TranId、Activity、Date 等)。如果原始 SQL 输出使用不同的区域设置，请确保在导出时将日期格式化为 ISO 8601 (YYYY-MM-DD THH:MM:SS)。
争议与催收逻辑: 查询会查找链接到客户的 SupportCase 记录以及“付款承诺”的 Note 记录。请核实您的组织是否使用自定义字段而非标准对象来标记这些内容，并相应调整 WHERE 子句。
验证: 将提取文件中的“Invoice Created”数量与 NetSuite 中同一时期的“已保存搜索 (Saved Search)”进行对比，以确保数据的完整性。
最终导出: 将结果数据集保存为 CSV 文件。在上传至 ProcessMind 之前，确保表头与“包含属性”列表 (TranId、Activity、DateCreated 等) 一致。

配置

日期范围: 通过 Transaction 表中的 trandate 和 SystemNote 中的 date 进行过滤。对于活跃流程，建议将时间窗口设为滚动 12 个月。
子公司过滤器: 对多子公司环境至关重要。在所有 WHERE 子句中添加 AND t.subsidiary = [Your Subsidiary ID]。
记录类型: 该查询侧重于 CustInvc (发票)、CustPymt (付款)、CustCred (贷记单) 和 Journal (日记账)。
System Notes: 该表数据量庞大。出于性能考虑，必须按 recordtype = 'invoice' 进行过滤。
状态映射: NetSuite 状态为内部 ID (例如 'A'、'B')。查询使用 BUILTIN.DF 来获取显示名称，但请确认这些名称与您的本地化 UI 标签一致。
关联策略: 在 transaction.id 上使用标准关联 (Join)。确保 '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”。在“设置 > 集成 (Integration) > 管理集成”下，创建一个名为“ProcessMining_Extractor”的新集成。启用“基于令牌的身份验证 (Token-based Authentication)”，如果使用标准令牌生成，请取消勾选“TBA: Authorization Flow”。记录下 Consumer Key 和 Consumer Secret。
生成访问令牌 (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 端点。虽然可以逐条遍历记录，但在进行 Process Mining 时，通过 REST API 使用 SuiteQL 的性能要高得多，因为它能以更少的 HTTP 请求完成 Transaction、TransactionLine、Message 和 SystemNote 表的关联。
执行提取脚本 通过终端或 IDE 运行脚本。脚本会执行五次独立的提取：分别针对主发票流、付款、贷记单、通信日志 (Message) 和系统状态变更 (审计追踪)。
监控执行情况 脚本会在控制台打印进度，显示每种 Activity 类型检索到的记录数。注意 HTTP 400 或 500 错误，这通常表示权限问题或 SuiteQL 语法错误。
数据转换与合并 脚本会自动处理数据转换。它会将 NetSuite 特定的日期格式转换为标准 ISO 8601 时间戳。它还会将 tranid 等字段名标准化为 Case ID，并将特定的交易类型映射到需求中定义的标准 Activity 名称。
处理后处理逻辑 脚本包含通过比较支付金额与原始发票总额来区分 'Partial Payment Posted' 和 'Full Payment Received' 的逻辑。它还会根据最终状态变更推断 'Invoice Cleared' 状态。
验证输出文件 完成后，脚本会生成一个名为 netsuite_ar_event_log.csv 的文件。打开此文件查看前几行，核实 TranId 已填充且 DateCreated 格式正确。
导入到 ProcessMind 生成的 CSV 格式专为 ProcessMind 设计。将 TranId 映射到 Case ID，将 Activity 映射到 Activity，将 DateCreated 映射到 Timestamp。确保 Amount 和 Status 被映射为 Case 属性。

配置

身份验证方式: OAuth 1.0a (基于令牌的身份验证)。这是 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 标志以及偏移量/范围逻辑来对大数据集进行分页处理。
所需权限: 用户角色必须拥有以下项的“查看”权限：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")

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款 Activities

提取指南

NetSuite SuiteAnalytics 工作簿数据导出

步骤

配置

a 查询示例 config

NetSuite SuiteQL 交易数据提取

步骤

配置

a 查询示例 sql

Python SuiteTalk REST API 提取脚本

步骤

配置

a 查询示例 python

准备好开始了吗？

立即加速您的 NetSuite 应收账款周期

您的应收账款数据模板

您的应收账款数据模板

应收账款属性

应收账款 Activities

提取指南

NetSuite SuiteAnalytics 工作簿数据导出

步骤

配置

a 查询示例 config

NetSuite SuiteQL 交易数据提取

步骤

配置

a 查询示例 sql

Python SuiteTalk REST API 提取脚本

步骤

配置

a 查询示例 python

我们重视您的隐私

准备好开始了吗？

立即加速您的 NetSuite 应收账款周期