> Continuous Process Improvement > Accounts Payable Payment Processing > NetSuite > Data Template

Your Accounts Payable Payment Processing Data Template

NetSuite

Accounts Payable Payment Processing Attributes (21) Accounts Payable Payment Processing Activities (14) Extraction Guides (3)

Your Accounts Payable Payment Processing Data Template

This template offers a comprehensive framework for mapping your NetSuite accounts payable workflows. It provides a detailed list of essential data attributes, core process activities, and specialized extraction instructions to ensure your analysis is both accurate and actionable.

Recommended attributes for deep analysis
Essential process milestones to track
NetSuite specific extraction guidance

New to event logs? Learn how to create a process mining event log.

Download Template

Accounts Payable Payment Processing Attributes

These are the recommended data fields to include in your event log for comprehensive accounts payable payment processing analysis within your NetSuite environment.

5 Required 10 Recommended 6 Optional

	Name	Description
	Activity Activity	The name of the process step or event performed.
Description This attribute records the specific action or state change that occurred at a given point in time. Examples include 'Invoice Created', 'Approval Status Changed', or 'Payment Processed'. This data is essential for reconstructing the process flow map. By sequencing these activities chronologically for each Invoice Number, the process mining tool visualizes the actual workflow, identifying loops, rework, and deviations from the happy path. Why it matters Defines the steps in the process map and is mandatory for any process mining analysis. Where to get System Notes or Workflow History logs Examples Invoice CreatedInvoice ApprovedPayment ScheduledPayment Cleared
	Event Time EventTime	The timestamp when the activity occurred.
Description The precise date and time when an activity took place. This is used to order events sequentially and calculate durations between steps. Accurate timestamps are critical for identifying bottlenecks. They enable the calculation of cycle times, such as the time taken for approval or the duration an invoice remains in a blocked state. Why it matters Mandatory for sequencing events and calculating all time-based KPIs. Where to get System Notes 'date' field Examples 2023-10-01T14:30:00Z2023-10-02T09:15:00Z
	Invoice Number InvoiceNumber	The unique identifier for the vendor invoice.
Description This attribute represents the unique reference number assigned to a vendor bill within NetSuite. It serves as the definitive case identifier for the Accounts Payable process, grouping all activities related to a specific payment obligation. In analysis, this field acts as the primary key for process instances. It allows analysts to trace the complete lifecycle of a liability from the moment the invoice is received and recorded until the final payment is cleared. Variants are aggregated based on this identifier to determine common process paths. Why it matters It is the mandatory Case ID that binds all events together to form a process case. Where to get Transaction table, field 'tranid' where type is 'Vendor Bill' Examples INV-2023-001VB-99822ACME-10299982311
	Last Data Update LastDataUpdate	The timestamp when the data was extracted or refreshed.
Description Indicates when the data was last pulled from the source system into the process mining platform. This helps users understand the currency of the analysis. It is used to determine if the data displayed in dashboards represents real-time status or a snapshot from a previous time period. This is vital for trusting the 'Days Overdue' calculations. Why it matters Mandatory for understanding data freshness and sync status. Where to get ETL Timestamp Examples 2023-11-01T00:00:00Z
	Source System SourceSystem	The name of the system where the data originated.
Description Identifies the software application from which the event data was extracted. In this context, the value is typically static, such as 'NetSuite'. This attribute is useful in multi-system environments to track data lineage. While this specific view focuses on NetSuite, maintaining this field ensures compatibility with enterprise-wide data models. Why it matters Mandatory for data lineage and distinguishing data in multi-system environments. Where to get Hardcoded during extraction Examples NetSuiteNetSuite OneWorld
	Approval Status ApprovalStatus	The current workflow approval state of the invoice.
Description Indicates whether the invoice is Pending Approval, Approved, or Rejected. This state drives the workflow transition logic. This field is central to the 'Approval Workflow Compliance Audit'. Changes in this status generate activity events, and the final state determines if the invoice is ready for payment. Why it matters Tracks the progress of the invoice through the approval gate. Where to get Transaction 'approvalstatus' field Examples Pending ApprovalApprovedRejected
	Department Department	The internal department associated with the invoice.
Description Categorizes the invoice by the cost center or internal department (e.g., IT, Marketing) that incurred the expense. This attribute allows for a departmental breakdown of cycle times. It helps identify if specific departments are slower at coding or approving invoices, enabling targeted process improvements. Why it matters Enables organizational analysis of process efficiency. Where to get Transaction 'department' field Examples ITMarketingManufacturing
	Due Date DueDate	The date by which the payment must be made to the vendor.
Description This attribute indicates the contractual deadline for payment based on the invoice terms. It serves as the baseline for on-time delivery calculations. This field is the core component of the 'On Time Payment Rate' KPI. By comparing the 'Payment Executed' date against this field, the system flags late payments and helps estimate potential penalties. Why it matters Fundamental for calculating on-time payment rates and identifying late liabilities. Where to get Transaction 'duedate' field Examples 2023-11-152023-12-01
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description The total amount of the vendor bill, typically in the base currency or transaction currency. This metric allows for financial impact analysis. Used in the 'Late Payment and Penalty Tracking' dashboard to quantify the cash flow risk of delayed payments. It is also used to prioritize high-value invoices for manual intervention. Why it matters Necessary for quantifying the financial impact of process inefficiencies. Where to get Transaction 'usertotal' or 'foreignamount' field Examples 1500.00250.5010000.00
	Is Non PO IsNonPO	Flag indicating if the invoice is not linked to a Purchase Order.
Description A boolean attribute derived from the absence of a linked Purchase Order. It segments the process into PO-backed vs. direct FI invoices. Used in the 'Non PO Invoice Processing Speed' dashboard to highlight the inefficiency of manual invoice processing compared to the 3-way match process. Why it matters High-level segment for analyzing process variants. Where to get Derived from 'PurchaseOrderNumber' is null Examples truefalse
	Payment Date PaymentDate	The date the payment was executed.
Description The date recorded on the Vendor Payment transaction linked to the bill. This represents the completion of the AP cycle. Used to calculate the 'End to End Payment Cycle Times'. Comparing this date to 'InvoiceDate' gives the total process duration. Why it matters End timestamp for the primary process cycle. Where to get Linked Vendor Payment transaction date Examples 2023-11-202023-12-05
	Purchase Order Number PurchaseOrderNumber	The reference number of the related Purchase Order.
Description Links the vendor bill to an originating Purchase Order (PO). If this field is empty, the invoice is considered a Non-PO invoice. This attribute is vital for the 'Non PO Invoice Processing Speed' dashboard. It allows the analysis to segregate flow variants into PO-backed vs. Non-PO (FI) invoices to compare efficiency and compliance. Why it matters Required to distinguish between PO-based and Non-PO based invoice processing. Where to get Transaction 'createdfrom' field Examples PO-8821PO-1002null
	Subsidiary Subsidiary	The company subsidiary or legal entity receiving the invoice.
Description In NetSuite OneWorld environments, this identifies the specific legal entity (Subsidiary) that the invoice belongs to. It allows for organizational comparisons. This attribute drives the 'End to End Payment Cycle Times' dashboard, enabling a comparative view of efficiency across different business units. It helps isolate if specific regions or entities are underperforming. Why it matters Critical for multi-entity organizations to compare performance across business units. Where to get Transaction 'subsidiary' field Examples US EastEMEA HeadquartersAPAC Sales
	User User	The employee who performed the activity.
Description Identifies the individual responsible for a specific event, such as approving an invoice or releasing a payment block. Used in the 'Approval Workflow Bottleneck Monitor' to identify resource-specific delays. It helps management balance workload and identify training needs for specific team members. Why it matters Required for resource analysis and identifying bottlenecks at the user level. Where to get System Notes 'name' or Transaction 'employee' Examples John DoeFinance AdminSystem
	Vendor Name VendorName	The name of the supplier issuing the invoice.
Description This attribute contains the name of the entity associated with the vendor bill. It is critical for analyzing performance across the supply chain. In analysis, this field supports the 'Vendor Payment Cycle Variance' dashboard. It allows analysts to group cases by vendor to identify specific suppliers that consistently cause matching discrepancies or have non-standard payment terms. Why it matters Essential for grouping analysis by supplier to find vendor-specific bottlenecks. Where to get Transaction 'entity' field joined to Vendor record Examples Office DepotOracleFedEx
	Currency Currency	The currency in which the invoice was issued.
Description The ISO code representing the currency of the transaction. This is important for multinational organizations. Used to normalize financial values in the 'Invoice Amount' analysis. It also helps identify if foreign exchange processes are adding time to the 'Payment Cleared' activity. Why it matters Necessary for correct financial aggregation and FX analysis. Where to get Transaction 'currency' field Examples USDEURGBP
	Days Overdue DaysOverdue	The number of days payment occurred after the due date.
Description A calculated metric measuring the delay in payment relative to the due date. Negative values indicate early payment. This attribute feeds the 'Late Payment and Penalty Tracking' dashboard. It is essential for quantifying the severity of late payments and identifying chronic late-paying patterns. Why it matters Key metric for analyzing payment compliance and penalty risk. Where to get Calculated: PaymentDate - DueDate Examples 50-2
	Discount Available DiscountAvailable	The potential discount amount available for early payment.
Description The monetary value of the early payment discount calculated based on the invoice amount and payment terms. It represents the potential savings. This attribute is central to the 'Early Payment Discount Capture Rate' dashboard. By summing this value for late payments, analysts can calculate total missed savings. Why it matters Quantifies the financial opportunity of process acceleration. Where to get Calculated from Terms and InvoiceAmount Examples 30.000.0015.50
	Invoice Date InvoiceDate	The date printed on the vendor's invoice.
Description The document date as provided by the vendor. This is often the starting point for aging calculations. Used to calculate the 'End to End Processing Cycle Time'. The gap between this date and the creation date in NetSuite can indicate scanning or mailroom delays. Why it matters Marks the official start of the vendor's timeline. Where to get Transaction 'trandate' field Examples 2023-10-012023-10-05
	Payment Hold PaymentHold	Indicates if the invoice is currently blocked from payment.
Description A flag or status indicating that a payment hold has been placed on the invoice, preventing it from being included in payment runs. This attribute supports the 'Payment Block Impact Analysis' dashboard. Analyzing the duration this field is set to 'true' highlights processing delays caused by disputes or missing information. Why it matters Critical for analyzing why invoices are stuck or delayed. Where to get Transaction 'paymenthold' field Examples truefalse
	Payment Terms PaymentTerms	The agreed terms dictating payment timing and discounts.
Description Specifies the payment conditions, such as 'Net 30' or '2% 10 Net 30'. This context helps explain the 'Due Date' and potential for early payment discounts. This attribute is used in the 'Early Payment Discount Capture Rate' dashboard. It identifies which invoices were eligible for discounts and helps correlate lost discounts with specific restrictive terms. Why it matters Contextualizes the due date and identifies discount opportunities. Where to get Transaction 'terms' field Examples Net 302% 10 Net 30Due on Receipt

Required Recommended Optional

Accounts Payable Payment Processing Activities

These are the key process steps and milestones to capture in your event log for accurate discovery of your payment processing lifecycle.

5 Recommended 9 Optional

	Activity	Description
	Invoice Approved	The Vendor Bill status changes to Open, indicating it is authorized for payment and has impacted the General Ledger. This is a key milestone before payment.
Why it matters Ends the approval cycle and makes the invoice available for payment scheduling. Essential for measuring approval efficiency. Where to get System Notes. Look for a change in the 'approvalstatus' field to '2' (Approved) or status to 'Open'. Capture Compare status field before/after Event type inferred
	Invoice Created	The initial creation of the Vendor Bill record in NetSuite. This serves as the process start and indicates the invoice has been entered into the ERP.
Why it matters Establishes the baseline timestamp for the entire Accounts Payable cycle and aging calculations. Where to get Transaction table. Filter where record type is Vendor Bill. Capture the datecreated or trandate field. Capture Logged when transaction Vendor Bill executed Event type explicit
	Invoice Matched to PO	Identifies that the Vendor Bill was created from or linked to an existing Purchase Order. This distinguishes PO-backed invoices from non-PO invoices.
Why it matters Critical for analyzing compliance with procurement policies and comparing cycle times between PO and non-PO invoices. Where to get Transaction table. Check the 'createdfrom' field on the Vendor Bill record. If populated with a Purchase Order ID, this activity occurred. Capture Compare status field before/after Event type inferred
	Payment Cleared	The Bill Payment is marked as cleared during the bank reconciliation process. This confirms the funds have left the bank account.
Why it matters Provides the true financial end date for cash flow analysis, distinct from the accounting payment date. Where to get Transaction Line. The 'cleared' flag is set to True on the Bill Payment transaction line. Capture Logged when transaction X executed Event type explicit
	Payment Processed	A Bill Payment record is successfully posted, marking the invoice as Paid in Full or Partially Paid. The liability is relieved in the GL.
Why it matters The primary completion milestone for the payment process. Used to calculate 'End to End Processing Cycle Time'. Where to get Transaction table. Identify 'Bill Payment' record creation or status change to 'Deposited' (for checks) or 'Paid'. Capture Logged when transaction Bill Payment executed Event type explicit
	Invoice Account Modified	The General Ledger expense account on the invoice line items is changed. This indicates a coding error correction or re-classification.
Why it matters Directly measures the 'Invoice Coding Rework Rate' KPI and highlights efficiency losses in the accounting coding step. Where to get System Notes. Look for changes to the 'account' field on the Transaction Line sublist. Capture Logged when transaction X executed Event type explicit
	Invoice Amount Modified	The total amount of the Vendor Bill is changed after initial creation. This suggests rework, data entry errors, or discrepancy resolution.
Why it matters Indicates manual intervention and rework, which negatively impacts Straight Through Processing rates. Where to get System Notes. Look for changes to the 'total' or 'usertotal' field on the Vendor Bill record. Capture Logged when transaction X executed Event type explicit
	Invoice Approval Requested	The status of the Vendor Bill changes to Pending Approval. This indicates the invoice has entered the workflow routing for authorization.
Why it matters Marks the beginning of the approval cycle time, allowing for the identification of bottlenecks in the authorization chain. Where to get System Notes. Look for a change in the 'approvalstatus' field to '1' (Pending Approval). Capture Compare status field before/after Event type inferred
	Invoice Rejected	The Vendor Bill is returned to the submitter for correction or denial. This represents a loop or termination in the approval workflow.
Why it matters High rejection rates indicate process friction, training issues, or data quality problems upstream. Where to get System Notes. Look for a change in the 'approvalstatus' field to '3' (Rejected). Capture Compare status field before/after Event type inferred
	Payment Hold Applied	The Payment Hold checkbox is set to True on the Vendor Bill. This stops the invoice from being selected for payment runs.
Why it matters Identifies bottlenecks where invoices are stalled due to disputes or cash flow management decisions. Where to get System Notes. Look for the 'paymenthold' field changing from 'F' to 'T'. Capture Logged when transaction X executed Event type explicit
	Payment Hold Released	The Payment Hold checkbox is set to False, releasing the invoice for payment processing. This marks the end of a blockage period.
Why it matters Used to calculate the 'Payment Block Duration' KPI and understand resolution times for disputed invoices. Where to get System Notes. Look for the 'paymenthold' field changing from 'T' to 'F'. Capture Logged when transaction X executed Event type explicit
	Payment Scheduled	A Bill Payment record is created in a 'Pending Approval' state. This often occurs during Electronic Bank Payment (EFT) batch creation.
Why it matters Differentiates between the intent to pay and the actual execution of funds, highlighting delays in payment authorization. Where to get Transaction table. Creation of a 'Bill Payment' record linked to the Bill, with status 'Pending Approval'. Capture Compare status field before/after Event type inferred
	Payment Voided	A previously processed Bill Payment is voided, reopening the Vendor Bill. This represents a major exception and process failure.
Why it matters Critical for audit compliance and identifying reasons for failed payments (e.g., check printing errors, bank rejections). Where to get System Notes on Bill Payment. Status change to 'Voided'. Capture Logged when transaction X executed Event type explicit
	Vendor Credit Applied	A Bill Credit (Vendor Credit) is applied to the Vendor Bill, reducing the amount due. This is an alternative to cash payment.
Why it matters Explains why a payment amount might differ from the invoice amount and correctly closes out the liability. Where to get Transaction Links. Identify when a transaction of type 'Vendor Credit' is linked to the Vendor Bill. Capture Compare status field before/after Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Log in to NetSuite as an Administrator or a user with permission to create and export Saved Searches.
Navigate to Lists > Search > Saved Searches > New.
Select Transaction from the list of record types to start a new Transaction Saved Search.
On the Criteria tab, verify the Use Expressions checkbox is unchecked initially, then add the Filter: Type, select any of Vendor Bill, Bill Payment, Bill Credit.
Still on the Criteria tab, add the Filter: Main Line, and set it to True. This ensures we focus on the header level for creation and status events.
To capture history, navigate to the System Notes join in the filter list (bottom of the dropdown) and add System Notes : Date. Set your desired date range (e.g., within last 90 days).
Switch to the Results tab. Remove all default columns.
Add the Case Identifier column: Select Transaction Name or Document Number. Assign the Custom Label InvoiceNumber.
Add the Event Time column: Select System Notes fields... > Date. Assign the Custom Label EventTime.
Add the Activity column: Select Formula (Text). In the formula window, paste the CASE statement provided in the Query section below. This logic maps NetSuite field changes to your standard activity names. Assign the Custom Label Activity.
Add other required attributes (Source System, Last Data Update) using Formula (Text) with static values (e.g., 'NetSuite') and the built-in {today} tag.
Click Save & Run. Once the results load, click the Export - CSV icon in the top left of the results toolbar to download the event log for ProcessMind.

Configuration

Transaction Types: Filter strictly to Vendor Bill, Bill Payment, and Bill Credit to avoid noise from Journal Entries or Purchase Orders.
System Notes Join: This configuration relies heavily on the System Notes join to generate a history log rather than a current-state snapshot. Ensure your user role has permission to view System Notes.
Date Range: Apply the date filter to the System Notes : Date field, not the transaction Date field. This ensures you capture recent activities on old invoices (like a payment today for an invoice created last year).
Main Line: Setting Main Line = True is recommended to reduce row volume, but if you need to track 'Invoice Account Modified', you may need to set Main Line = False or create a separate search for line-level changes.
Formula Limits: NetSuite formulas are limited to 4000 characters. If the Activity logic becomes too complex, split it into two searches or process the mapping outside NetSuite.

a Sample Query config

Type: Transaction

CRITERIA TAB:
- Type: is any of Vendor Bill, Bill Payment, Bill Credit
- Main Line: is True
- System Notes : Date: is within [Your Date Range]

RESULTS TAB:

Column 1: Document Number
- Label: InvoiceNumber

Column 2: System Notes : Date
- Label: EventTime

Column 3: Formula (Text)
- Label: Activity
- Formula:
CASE 
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' THEN 'Invoice Created'
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' AND {createdfrom} IS NOT NULL THEN 'Invoice Matched to PO'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Pending Approval' THEN 'Invoice Approval Requested'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Open' THEN 'Invoice Approved'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Rejected' THEN 'Invoice Rejected'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Amount' THEN 'Invoice Amount Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Account' THEN 'Invoice Account Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'T' THEN 'Payment Hold Applied'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'F' THEN 'Payment Hold Released'
   WHEN {type} = 'Bill Credit' AND {systemnotes.type} = 'Create' THEN 'Vendor Credit Applied'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Pending Approval' THEN 'Payment Scheduled'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Paid In Full' THEN 'Payment Processed'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Void' THEN 'Payment Voided'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Cleared' AND {systemnotes.newvalue} = 'T' THEN 'Payment Cleared'
   ELSE 'General Update'
END

Column 4: Formula (Text)
- Label: SourceSystem
- Formula: 'NetSuite'

Column 5: Formula (Text)
- Label: LastDataUpdate
- Formula: TO_CHAR({today}, 'YYYY-MM-DD HH24:MI:SS')

Column 6: Vendor : Name
- Label: VendorName

Column 7: Subsidiary
- Label: Subsidiary

Column 8: Amount
- Label: InvoiceAmount

Column 9: Due Date
- Label: DueDate

Column 10: Created From
- Label: PurchaseOrderNumber

Column 11: System Notes : Set by
- Label: User

Column 12: Department
- Label: Department

Column 13: Status
- Label: ApprovalStatus

Column 14: Formula (Text)
- Label: IsNonPO
- Formula: CASE WHEN {createdfrom} IS NULL THEN 'True' ELSE 'False' END

Steps

Configure ODBC Driver: Ensure the SuiteAnalytics Connect ODBC driver is installed and configured on your extraction machine. You will need your NetSuite Account ID, Role ID, and valid credentials.
Verify Permissions: The user role associated with the ODBC connection must have 'SuiteAnalytics Connect' permission and 'View' access to Transactions, System Notes, and Entities.
Prepare the Environment: Open your SQL client (e.g., DBeaver, DataGrip, or a proprietary ETL tool) and establish a connection to the NetSuite data source.
Define Scope: Identify the specific Subsidiary IDs and the Date Range for the extraction. Update the WHERE clause in the Common Table Expression (CTE) named 'VendorBills' in the provided query.
Execute the Query: Run the provided SQL script. This script aggregates data from the 'Transaction', 'TransactionLine', 'SystemNote', and 'TransactionLink' tables to reconstruct the process history.
Monitor Execution: NetSuite ODBC connections can time out with large datasets. If the query fails, consider splitting the extraction by month or quarter using the 'trandate' filter.
Validate Data: Check the output for a reasonable distribution of 'Invoice Created' events versus 'Payment Processed' events. Ensure 'InvoiceNumber' is populated for every row.
Export Data: Save the query results as a CSV file or ingest directly into your dataframe. Ensure the character encoding is set to UTF-8 to handle special characters in Vendor Names.
Format Dates: Ensure the 'EventTime' column is parsed as a datetime object (YYYY-MM-DD HH:MM:SS) during the import into ProcessMind.
Handle Nulls: In the 'User' column, null values often indicate system-generated events (like scheduled scripts). You may replace these with 'System' during post-processing if desired.
Final Review: Confirm that the 'Activity' column contains the specific event names required (e.g., 'Invoice Created', 'Payment Processed') and that no mapping errors occurred.

Configuration

Date Range: Filter using 'Transaction.trandate'. A range of 3-12 months is recommended for initial process mining analysis.
Subsidiary Filtering: If your NetSuite environment is OneWorld, always filter by 'Transaction.subsidiary' to ensure you are analyzing the correct legal entity.
System Notes: The extraction relies heavily on the 'SystemNote' table for approval and change logs. Ensure 'Log System Notes on Update Only' is NOT preventing the capture of critical workflow steps.
Transaction Status: NetSuite transaction statuses are distinct (e.g., 'A', 'B'). The query maps these to readable strings. Verify your environment uses standard status codes.
ODBC Driver Version: Use the latest available SuiteAnalytics Connect driver to avoid compatibility issues with recent NetSuite upgrades.

a Sample Query sql

WITH VendorBills AS (
    SELECT
        t.ID AS TransactionID,
        t.tranid AS InvoiceNumber,
        t.trandate AS InvoiceDate,
        t.createDate AS CreatedDate,
        t.lastModifiedDate AS LastModified,
        e.altName AS VendorName,
        s.name AS Subsidiary,
        t.foreignTotal AS InvoiceAmount,
        t.dueDate AS DueDate,
        t.createdFrom AS CreatedFromID,
        CASE WHEN t.createdFrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
        dep.name AS Department,
        'NetSuite' AS SourceSystem
    FROM
        Transaction t
        LEFT JOIN Entity e ON t.entity = e.id
        LEFT JOIN Subsidiary s ON t.subsidiary = s.id
        LEFT JOIN Department dep ON t.department = dep.id
    WHERE
        t.type = 'VendBill'
        AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD') -- REPLACE WITH YOUR START DATE
        AND t.trandate <= TO_DATE('2023-12-31', 'YYYY-MM-DD') -- REPLACE WITH YOUR END DATE
),
PO_Data AS (
    SELECT
        t.ID AS PO_ID,
        t.tranid AS PONumber
    FROM
        Transaction t
    WHERE
        t.type = 'PurchOrd'
)

-- 1. Invoice Created
SELECT
    vb.InvoiceNumber,
    'Invoice Created' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 2. Invoice Matched to PO
SELECT
    vb.InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 3. Invoice Approval Requested (Derived from System Notes)
SELECT
    vb.InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Pending Approval' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Pending Approval'

UNION ALL

-- 4. Invoice Approved
SELECT
    vb.InvoiceNumber,
    'Invoice Approved' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Open' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND (sn.newValue = 'Approved' OR sn.newValue = 'Open')

UNION ALL

-- 5. Invoice Rejected
SELECT
    vb.InvoiceNumber,
    'Invoice Rejected' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Rejected' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Rejected'

UNION ALL

-- 6. Invoice Amount Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    (sn.standardfield = 'foreigntotal' OR sn.standardfield = 'total')

UNION ALL

-- 7. Invoice Account Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'account'

UNION ALL

-- 8. Payment Hold Applied
SELECT
    vb.InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Hold' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'T'

UNION ALL

-- 9. Payment Hold Released
SELECT
    vb.InvoiceNumber,
    'Payment Hold Released' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Released' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'F'

UNION ALL

-- 10. Vendor Credit Applied
SELECT
    vb.InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    vc.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Credit Applied' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vc ON tl.nextDoc = vc.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vc.type = 'VendCred'

UNION ALL

-- 11. Payment Scheduled (Bill Payment created but pending)
SELECT
    vb.InvoiceNumber,
    'Payment Scheduled' AS Activity,
    vp.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Scheduled' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.status = 'Pending Approval'

UNION ALL

-- 12. Payment Processed (Payment posted)
SELECT
    vb.InvoiceNumber,
    'Payment Processed' AS Activity,
    vp.trandate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Paid' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND (vp.status = 'PaidInFull' OR vp.status = 'Deposited' OR vp.status = 'NotDeposited')

UNION ALL

-- 13. Payment Voided
SELECT
    vb.InvoiceNumber,
    'Payment Voided' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Voided' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    INNER JOIN SystemNote sn ON vp.ID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    vp.type = 'VendPymt'
    AND sn.standardfield = 'status'
    AND sn.newValue LIKE '%Void%'

UNION ALL

-- 14. Payment Cleared
SELECT
    vb.InvoiceNumber,
    'Payment Cleared' AS Activity,
    vp.clearedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Cleared' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.clearedDate IS NOT NULL

Steps

Prerequisites: Ensure you have a NetSuite account with Administrator or Developer access. You must have 'SuiteTalk REST Web Services' and 'SuiteAnalytics Connect' features enabled in Setup > Company > Enable Features.
Integration Setup: Go to Setup > Integration > Manage Integrations > New. Create an integration named 'ProcessMining_ETL'. Check 'Rest Web Services' and 'Token-Based Authentication'. Note the Consumer Key and Consumer Secret generated.
Token Generation: Go to the Manage Access Tokens page. Create a new token for the role that has permissions to view Transactions, System Notes, and Lists. Note the Token ID and Token Secret.
Python Environment: Install Python 3.x. Install required libraries using pip: pip install requests requests-oauthlib pandas.
Script Configuration: Copy the Python script provided in the Query section below. Update the ACCOUNT_ID, CONSUMER_KEY, CONSUMER_SECRET, TOKEN_ID, and TOKEN_SECRET variables with your credentials. Set the START_DATE variable to your desired extraction window (e.g., '2023-01-01').
SuiteQL Logic: The script uses a UNION ALL approach. It queries the Transaction table for the creation and payment events and the SystemNote table for audit trail events (approvals, changes, holds).
Execution: Run the script via command line: python netsuite_extract.py. The script handles pagination automatically (SuiteQL creates pages of 1,000 records).
Data Processing: The script automatically converts the JSON response into a flat tabular format suitable for process mining. It handles column mapping and date formatting.
Validation: The script prints the total number of rows extracted. Verify this against a NetSuite Saved Search if needed.
Export: The final dataframe is saved as netsuite_ap_event_log.csv. This file is ready for upload to ProcessMind.
Upload: Import the CSV into ProcessMind. Map InvoiceNumber as Case ID, Activity as Activity, and EventTime as Timestamp.

Configuration

Authentication: Uses OAuth 1.0a (Token-Based Authentication). This is the standard security model for NetSuite REST API.
Batch Size: The script requests 1,000 rows per page (NetSuite's default maximum for SuiteQL).
Date Filtering: Configured to filter by trandate (for transactions) and date (for system notes) >= START_DATE to optimize performance.
Scope: Filters Transaction table by types 'VendBill' (Vendor Bill), 'BillPymt' (Bill Payment), and 'VendCred' (Vendor Credit).
System Notes: This table is critical for capturing approval loops and changes. It can be large; ensure the integration user has permission to query SystemNote.
Timezone: NetSuite API returns dates in the account's timezone. The script assumes standard ISO formats.

a Sample Query python

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
ACCOUNT_ID = class="hl-string">'1234567_SB1'  class=class="hl-string">"hl-comment"># Your NetSuite Account ID (Realm). Use hyphens for sandbox (e.g., 1234567-sb1)
CONSUMER_KEY = class="hl-string">'your_consumer_key'
CONSUMER_SECRET = class="hl-string">'your_consumer_secret'
TOKEN_ID = class="hl-string">'your_token_id'
TOKEN_SECRET = class="hl-string">'your_token_secret'
START_DATE = class="hl-string">'2024-01-01'  class=class="hl-string">"hl-comment"># Filter for data extraction

class=class="hl-string">"hl-comment"># Normalize Account ID for URL (NetSuite requires lowercase and hyphens)
url_account_id = ACCOUNT_ID.lower().replace(class="hl-string">'_', class="hl-string">'-')
BASE_URL = fclass="hl-string">'https://{url_account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># === SUITEQL QUERY ===
class=class="hl-string">"hl-comment"># This query extracts 14 activities using UNION ALL to combine Transaction data and System Notes.
suiteql_query = fclass="hl-string">"""
/* 1. Invoice Created */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Created' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Matched to PO */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    'False' AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.createdfrom IS NOT NULL
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Approval Requested (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30 /* Internal ID for Vendor Bill */
AND sn.field = 'status'
AND sn.value = 'Pending Approval'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 4. Invoice Approved (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approved' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Open'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 5. Invoice Rejected (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Rejected' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Rejected'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 6. Invoice Amount Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'total'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 7. Invoice Account Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'account'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 8. Payment Hold Applied (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'T'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 9. Payment Hold Released (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Released' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'F'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 10. Vendor Credit Applied */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    TO_CHAR(t_cred.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_cred.trandate AS PaymentDate
FROM Transaction t_cred
JOIN TransactionLine tl ON t_cred.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_cred.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_cred.type = 'VendCred'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 11. Payment Scheduled */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Scheduled' AS Activity,
    TO_CHAR(t_pay.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Pending Approval'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 12. Payment Processed */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Processed' AS Activity,
    TO_CHAR(t_pay.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status <> 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 13. Payment Voided */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Voided' AS Activity,
    TO_CHAR(t_pay.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 14. Payment Cleared (Logic depends on bank rec features, using Check Cleared date or modification) */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Cleared' AS Activity,
    /* Fallback to trandate if clearedDate is unavailable in your config */
    TO_CHAR(COALESCE(t_pay.createddate, t_pay.trandate), 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Paid In Full' /* Indicates cleared/posted in some contexts */
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')
"""

class=class="hl-string">"hl-comment"># === AUTH AND EXECUTION ===

def get_suiteql_results(query):
    oauth = OAuth1(
        client_key=CONSUMER_KEY,
        client_secret=CONSUMER_SECRET,
        resource_owner_key=TOKEN_ID,
        resource_owner_secret=TOKEN_SECRET,
        realm=ACCOUNT_ID,
        signature_method=class="hl-string">'HMAC-SHA256'
    )
    
    all_rows = []
    offset = 0
    has_more = True
    
    print(class="hl-string">"Starting extraction...")
    
    while has_more:
        class=class="hl-string">"hl-comment"># SuiteQL REST API payload
        headers = {
            class="hl-string">'Prefer': class="hl-string">'transient',
            class="hl-string">'Content-Type': class="hl-string">'application/json'
        }
        
        class=class="hl-string">"hl-comment"># Add LIMIT/OFFSET to query if not using built-in cursors (SuiteQL doesn't support OFFSET well natively in string, 
        class=class="hl-string">"hl-comment"># but REST API handles pagination via links. However, for raw query, we usually rely on class="hl-string">'items' return)
        class=class="hl-string">"hl-comment"># Note: Pure SuiteQL via REST returns a set number of records. We must iterate via the class="hl-string">'links' provided in response
        class=class="hl-string">"hl-comment"># or typically, SuiteQL REST creates a job for large queries. 
        class=class="hl-string">"hl-comment"># For simplicity in this script, we assume a direct synchronous call pattern.
        
        class=class="hl-string">"hl-comment"># Actually, for large datasets, we must use the class="hl-string">'q' parameter. 
        class=class="hl-string">"hl-comment"># But SuiteQL POST body is standard. We will implement simple looping if class="hl-string">'hasMore' is present.
        
        payload = {
            class="hl-string">"q": query
        }
        
        response = requests.post(BASE_URL, headers=headers, auth=oauth, json=payload)
        
        if response.status_code != 200:
            print(fclass="hl-string">"Error: {response.text}")
            break
            
        data = response.json()
        items = data.get(class="hl-string">'items', [])
        all_rows.extend(items)
        
        class=class="hl-string">"hl-comment"># Check if there are more results (SuiteQL REST paging)
        class=class="hl-string">"hl-comment"># Standard REST response has class="hl-string">'hasMore' and class="hl-string">'links'. 
        class=class="hl-string">"hl-comment"># However, Query API V1 often requires traversing the class="hl-string">'next' link.
        has_more = data.get(class="hl-string">'hasMore', False)
        
        if has_more:
            class=class="hl-string">"hl-comment"># For subsequent pages, we follow the class="hl-string">'next' link logic or offset logic.
            class=class="hl-string">"hl-comment"># Note: The Query API creates a paged result set. We strictly need to follow the class="hl-string">'links' > class="hl-string">'next'.
            links = data.get(class="hl-string">'links', [])
            next_link = next((l[class="hl-string">'href'] for l in links if l[class="hl-string">'rel'] == class="hl-string">'next'), None)
            if next_link:
                BASE_URL = next_link class=class="hl-string">"hl-comment"># Update URL for next loop
                class=class="hl-string">"hl-comment"># Payload is not needed for next link GET usually, but Query API might differ.
                class=class="hl-string">"hl-comment"># If using links, we switch to GET for the next page.
                class=class="hl-string">"hl-comment"># (Simplified logic: In production, robustly handle the href)
            else:
                has_more = False
        
        print(fclass="hl-string">"Fetched {len(items)} rows. Total: {len(all_rows)}")
        time.sleep(1) class=class="hl-string">"hl-comment"># Rate limit protection

    return pd.DataFrame(all_rows)

class=class="hl-string">"hl-comment"># === RUN ===
if __name__ == class="hl-string">'__main__':
    df = get_suiteql_results(suiteql_query)
    
    class=class="hl-string">"hl-comment"># Post-processing
    if not df.empty:
        class=class="hl-string">"hl-comment"># Ensure numeric formats
        df[class="hl-string">'invoiceamount'] = pd.to_numeric(df[class="hl-string">'invoiceamount'], errors=class="hl-string">'coerce').fillna(0)
        
        class=class="hl-string">"hl-comment"># Save
        filename = class="hl-string">'netsuite_ap_event_log.csv'
        df.to_csv(filename, index=False)
        print(fclass="hl-string">"Success! Saved {len(df)} rows to {filename}")
    else:
        print(class="hl-string">"No data found.")

Your Accounts Payable Payment Processing Data Template

Your Accounts Payable Payment Processing Data Template

Accounts Payable Payment Processing Attributes

Accounts Payable Payment Processing Activities

Extraction Guides

NetSuite Saved Search Export for AP Transactions

Steps

Configuration

a Sample Query config

SuiteQL Extraction via SuiteAnalytics Connect ODBC

Steps

Configuration

a Sample Query sql

Python ETL using SuiteTalk REST API with SuiteQL

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your Accounts Payable Payment Processing Today

Your Accounts Payable Payment Processing Data Template

Your Accounts Payable Payment Processing Data Template

Accounts Payable Payment Processing Attributes

Accounts Payable Payment Processing Activities

Extraction Guides

NetSuite Saved Search Export for AP Transactions

Steps

Configuration

a Sample Query config

SuiteQL Extraction via SuiteAnalytics Connect ODBC

Steps

Configuration

a Sample Query sql

Python ETL using SuiteTalk REST API with SuiteQL

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Your Accounts Payable Payment Processing Today