> Continuous Process Improvement > Purchase to Pay Invoice Processing > NetSuite > Data Template

Your Purchase to Pay - Invoice Processing Data Template

NetSuite

Purchase to Pay - Invoice Processing Attributes (20) Purchase to Pay - Invoice Processing Activities (14) Extraction Guides (3)

Your Purchase to Pay - Invoice Processing Data Template

This template provides a comprehensive guide for collecting the right data to analyze your invoice processing within NetSuite. It outlines the essential data fields, key process steps, and guidance for extracting this information effectively. By following this template, you can ensure your event log is complete and ready for powerful process mining analysis.

Recommended attributes to collect
Key activities to track
Extraction guidance for NetSuite

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

Download Template

Purchase to Pay - Invoice Processing Attributes

These are the recommended data fields to include in your event log for comprehensive purchase to pay - invoice processing analysis, ensuring all necessary context is captured.

5 Required 7 Recommended 8 Optional

	Name	Description
	Invoice Number InvoiceNumber	The unique identifier for a vendor invoice within the system.
Description The Invoice Number serves as the primary case identifier, linking all activities and events related to a single vendor bill from its creation to its final payment. This attribute is crucial for tracing the end-to-end journey of each invoice. In process analysis, this number allows the system to reconstruct the entire lifecycle of an invoice. By grouping all related activities under this common identifier, analysts can visualize the process flow, measure cycle times, and identify variations or bottlenecks that affect individual invoices. Why it matters It is the essential key for tracking each invoice's journey through the payment process, enabling a complete case-level analysis. Where to get This is typically the 'Transaction ID' or 'Invoice #' field on the Vendor Bill record in NetSuite. Examples INV-0012345789-ABC-654202405-101
	Activity ActivityName	The name of the business process step that was performed.
Description This attribute describes a specific action or event that occurred within the invoice processing lifecycle, such as 'Vendor Bill Created', 'Bill Approved', or 'Bill Paid In Full'. Each activity represents a distinct point in the process. Activities are the building blocks of the process map. Analyzing the sequence and frequency of these activities helps to understand the actual process flow, identify deviations from the standard procedure, and pinpoint common rework loops or inefficient steps. Why it matters It forms the backbone of the process map, allowing for visualization and analysis of the sequence of events in the invoice lifecycle. Where to get Derived from system logs, status changes in the Vendor Bill record, or specific user actions recorded in the system history. Examples Vendor Bill CreatedBill ApprovedPayment Scheduled In Batch
	Last Data Update LastDataUpdate	The timestamp when the data was last refreshed or extracted from the source system.
Description This attribute indicates the date and time of the most recent data extraction from the source system. It provides context on the freshness of the data being analyzed. Knowing the last data update time is essential for users to understand if they are viewing the most current information. It helps manage expectations about data latency and ensures that decisions are based on data of a known age. Why it matters It informs analysts about the freshness of the data, ensuring they are aware of the data's timeliness when making decisions. Where to get This timestamp is generated and added during the data extraction and loading (ETL) process. Examples 2024-05-21T02:00:00Z
	Source System SourceSystem	Identifies the system from which the data originated.
Description This attribute specifies the source application where the event data was generated. For this process, it will typically be 'NetSuite'. In environments with multiple integrated systems, this field is crucial for understanding data lineage and assessing data quality. It helps differentiate activities that occur in different systems and is important for building a holistic view of the end-to-end process. Why it matters It provides context about the data's origin, which is crucial in multi-system landscapes for troubleshooting and data validation. Where to get This is typically a static value ('NetSuite') added during the data extraction process. Examples NetSuite
	Start Time EventTime	The timestamp indicating when an activity occurred.
Description This attribute captures the precise date and time that a specific activity or event was recorded in the system. It provides the chronological context for the entire process. Timestamps are fundamental for any time-based process analysis. They are used to calculate durations between activities, measure overall case cycle times, and analyze performance against service level agreements. Accurate timestamps are essential for identifying bottlenecks and understanding process delays. Why it matters It provides the chronological data necessary to calculate all durations, cycle times, and performance metrics, forming the basis of temporal process analysis. Where to get Sourced from date fields or system logs on the Vendor Bill record and related transaction records, such as 'Date Created', 'Last Modified Date', or approval date fields. Examples 2023-04-15T09:00:00Z2023-04-16T14:30:15Z2023-04-20T11:05:45Z
	Approver Approver	The user responsible for approving or rejecting the invoice.
Description This attribute identifies the employee or manager who performed the approval step for an invoice. In workflows with multiple approval levels, this may capture the most recent or final approver. Tracking the approver is essential for analyzing the approval cycle. It helps identify bottlenecks in the approval process by showing which individuals or departments have the longest average approval times. This information can be used to balance workloads or provide additional training. Why it matters Helps pinpoint bottlenecks in the approval process and analyze performance by individual or department, enabling targeted improvements. Where to get Sourced from the workflow or approval history logs associated with the Vendor Bill transaction. This may be in the 'System Notes' or a custom approval log. Examples John SmithJane DoeFinance Department Head
	Invoice Amount InvoiceAmount	The total monetary value of the vendor invoice.
Description This attribute represents the total amount due as stated on the invoice, including any taxes or other charges. It is a key financial metric for each case. Invoice Amount is used for various analytical purposes, including segmenting invoices into value bands (e.g., high-value vs. low-value) to see if processing times or approval paths differ. It is also essential for financial reporting, calculating the value of invoices in different stages, and analyzing the financial impact of payment delays. Why it matters Allows for financial analysis and value-based segmentation, helping to prioritize high-value invoices and understand cost drivers. Where to get This corresponds to the 'Total' or 'Amount' field on the header of the Vendor Bill record in NetSuite. Examples 1500.75250.0012500.50
	Invoice Status InvoiceStatus	The current processing status of the invoice.
Description This attribute reflects the current state of the vendor bill within its lifecycle, such as 'Pending Approval', 'Approved', 'Paid in Full', or 'Rejected'. The status provides a snapshot of where an invoice is in the process at any given time. It is used in operational dashboards to monitor the current workload and throughput. Analyzing status transitions is also a key part of process mining, as it often forms the basis for defining activities. Why it matters Provides a real-time snapshot of invoice progress, enabling workload management and throughput analysis. Where to get This is the 'Status' field on the Vendor Bill record in NetSuite. Examples OpenPending ApprovalApprovedPaid in Full
	Is Auto Matched IsAutoMatched	A flag indicating if the invoice was matched to a purchase order automatically without manual intervention.
Description This boolean attribute indicates whether the system was able to automatically match the invoice to a purchase order and goods receipt (in a three-way match scenario) based on predefined rules and tolerances. A 'true' value signifies a touchless matching process. This attribute is the basis for the 'Invoice Auto-Matching Rate' KPI. A high rate of automatic matching is a key indicator of a highly efficient and automated invoice processing system. Analyzing the cases where this is 'false' helps identify the reasons for matching failures and opportunities for automation improvement. Why it matters Directly measures the level of automation and efficiency in the invoice matching process, highlighting opportunities to reduce manual work. Where to get This is typically a derived attribute. It can be inferred by the absence of a 'Matching Discrepancy Identified' activity or by checking if the bill was created and approved by a system user in a very short time. Examples truefalse
	Is Paid On Time IsPaidOnTime	A flag that indicates whether the invoice was paid on or before its due date.
Description This calculated boolean attribute compares the timestamp of the 'Bill Paid In Full' activity with the 'Payment Due Date'. It is 'true' if the payment was made on time and 'false' otherwise. This attribute directly supports the 'On-Time Payment Rate' KPI, a critical measure of financial discipline and vendor relationship management. It allows for easy filtering and analysis of late payments to identify root causes, such as approval delays or payment run scheduling issues. Why it matters Directly measures adherence to payment terms, which is critical for vendor relations, avoiding penalties, and financial planning. Where to get Calculated field: TRUE if the timestamp of the final payment activity is less than or equal to the 'PaymentDueDate'. Examples truefalse
	Payment Due Date PaymentDueDate	The date by which the invoice must be paid to meet the agreed payment terms.
Description The Payment Due Date is calculated based on the invoice date and the payment terms negotiated with the vendor (e.g., Net 30, Net 60). It serves as the primary deadline for payment execution. This date is crucial for measuring on-time payment performance, a key KPI for vendor relationships and financial health. By comparing the actual payment date to the due date, organizations can track their payment timeliness, identify vendors who are consistently paid late, and manage cash flow more effectively. Why it matters It is the benchmark for measuring on-time payment performance, which directly impacts vendor relationships and can help avoid late fees. Where to get This is the 'Due Date' field on the Vendor Bill record, which is often calculated automatically based on the 'Terms' field. Examples 2023-05-152023-06-302023-07-01
	Vendor Name VendorName	The name of the vendor or supplier who submitted the invoice.
Description This attribute identifies the legal name of the vendor associated with the invoice. The vendor is a key entity in the Purchase-to-Pay process. Analyzing the process by vendor is critical for identifying specific supplier-related issues. It helps answer questions such as which vendors have the longest approval times, which ones submit the most invoices requiring rework, and which are most frequently paid late. This segmentation is fundamental for vendor relationship management and performance analysis. Why it matters It enables critical segmentation of the process data to identify vendor-specific bottlenecks, payment performance, and compliance issues. Where to get Sourced from the 'Vendor' or 'Supplier' field on the Vendor Bill record, linked to the Vendor master data. Examples Office Supplies Inc.Global Tech ServicesCreative Marketing Agency
	Currency Currency	The currency of the invoice amount.
Description This attribute specifies the currency in which the invoice is denominated, such as USD, EUR, or GBP. This is especially important for companies operating in multiple countries. Currency provides essential context for all monetary values. In analysis, it ensures that financial data is interpreted correctly and allows for proper currency conversion when aggregating financial metrics across different regions. Why it matters Provides necessary context for all financial amounts, ensuring accurate financial analysis in multi-currency environments. Where to get This is the 'Currency' field on the Vendor Bill record, typically defaulted from the Vendor master record. Examples USDEURGBP
	Discount Date DiscountDate	The deadline for paying an invoice to be eligible for an early payment discount.
Description This attribute specifies the date by which an invoice must be paid to take advantage of any early payment discounts offered by the vendor, as defined in the payment terms (e.g., '2% 10, Net 30'). This date is essential for calculating the 'Early Payment Discount Capture Rate' KPI. Tracking performance against this date allows the organization to identify missed savings opportunities and optimize its payment strategy to maximize captured discounts, which can have a significant positive impact on profitability. Why it matters It is the key date for identifying and capturing early payment discounts, directly impacting cost savings and profitability. Where to get This date is derived from the invoice date and the 'Terms' field on the Vendor Bill record. NetSuite calculates this as the 'Discount Date'. Examples 2023-04-252023-05-10
	Discount Taken DiscountTaken	A flag indicating if an available early payment discount was successfully taken.
Description This calculated boolean attribute checks if an invoice with an available discount was paid on or before the 'Discount Date'. It is 'true' if the discount was captured and 'false' if the opportunity was missed. This attribute is essential for the 'Early Payment Discount Capture Rate' KPI. It helps finance departments quantify missed savings and identify process bottlenecks, such as slow approvals, that prevent them from taking advantage of favorable payment terms. Why it matters Measures the effectiveness of the process in capturing cost-saving opportunities, directly impacting the company's bottom line. Where to get Calculated field: TRUE if the invoice had a 'DiscountDate' and the final payment activity occurred on or before that date. Examples truefalse
	Invoice Cycle Time InvoiceCycleTime	The total time elapsed from when the invoice was created to when it was fully paid.
Description This calculated metric measures the end-to-end duration of the invoice processing lifecycle. It is typically calculated as the time difference between the 'Vendor Bill Created' activity and the 'Bill Paid In Full' activity. As a primary KPI, average invoice cycle time provides a high-level measure of the overall efficiency of the AP process. Analyzing this metric, segmented by vendor or subsidiary, helps identify broad areas of inefficiency and track the impact of process improvement initiatives over time. Why it matters Measures the end-to-end efficiency of the invoice processing workflow, directly impacting vendor relationships and working capital. Where to get Calculated field based on the timestamps of the first and last events for each case (invoice). Examples 25 days 4 hours15 days 11 hours45 days 2 hours
	Payment Block Reason PaymentBlockReason	The reason why a payment for an approved invoice has been blocked.
Description An invoice that is approved for payment may still be put on a payment block for various reasons, such as a vendor dispute, a quality issue with goods received, or a cash management decision. This attribute captures the specific reason for the block. Analyzing payment block reasons and their resolution times is important for optimizing cash flow and maintaining good vendor relationships. It helps identify recurring issues that lead to payment delays even after an invoice has been successfully processed and approved. Why it matters Explains why approved invoices are not being paid, highlighting operational or financial issues that delay payments. Where to get This is often managed via a custom 'Payment Hold' or 'Payment Block' checkbox and a corresponding reason list or text field on the Vendor Bill. Examples Vendor Account on HoldQuality Inspection PendingAwaiting Credit Memo
	Purchase Order Number PurchaseOrderNumber	The identifier of the purchase order related to the invoice.
Description This attribute links a vendor invoice to the corresponding purchase order (PO) that authorized the procurement of the goods or services. Invoices can be PO-backed or non-PO. Analyzing by PO provides crucial context for invoice matching efficiency. It helps determine the rate of auto-matching versus manual intervention and identify discrepancies between the invoice, PO, and goods receipt. A high rate of mismatches can indicate problems in the procurement process. Why it matters Links the invoice to the procurement process, which is essential for analyzing invoice matching efficiency and three-way match compliance. Where to get Available on the Vendor Bill record, typically on the line items or in a header field linking it to a PO. Examples PO-005678PO-005891N/A
	Rejection Reason RejectionReason	The reason provided when an invoice is rejected during the approval process.
Description When an approver rejects an invoice, they typically provide a reason for the rejection, such as 'Incorrect Amount', 'Duplicate Invoice', or 'Missing PO'. This attribute captures that reason. This information is vital for root cause analysis of process inefficiencies. By categorizing and trending rejection reasons, organizations can identify common problems, like issues with specific vendors or data entry errors, and implement corrective actions to reduce rework and delays. Why it matters Provides direct insight into the root causes of rework and process failures, helping to improve first-time-right rates. Where to get This data is typically captured in a memo or comment field during the rejection step of an approval workflow. It may be stored in the 'System Notes' or a custom field. Examples Incorrect quantityDuplicate invoicePrice mismatch with PO
	Subsidiary Subsidiary	The company or legal entity within the organization that is responsible for the invoice.
Description In a multi-entity organization, the Subsidiary attribute identifies which legal entity the vendor bill belongs to. This is a fundamental organizational data point in NetSuite OneWorld accounts. Analyzing the process by subsidiary allows for performance comparison across different business units or geographic locations. It helps identify which parts of the organization are most efficient and where best practices can be shared, or where specific process issues exist. Why it matters Enables performance benchmarking and analysis across different legal entities or business units within the organization. Where to get This is the standard 'Subsidiary' field on all transaction records in a NetSuite OneWorld account. Examples US WestEMEA HeadquartersAPAC Services

Required Recommended Optional

Purchase to Pay - Invoice Processing Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and identifying bottlenecks in your invoice workflow.

5 Recommended 9 Optional

	Activity	Description
	Bill Approved	Marks the final approval of the vendor bill, authorizing it for payment. This is a key milestone, typically captured as an explicit status change to 'Approved' in the system's approval workflow.
Why it matters This is a critical milestone that gates the payment process. Delays here are a common bottleneck, and tracking this activity is essential for managing payment timeliness and cash flow. Where to get Captured from the system notes or audit trail for the Vendor Bill, specifically the timestamp when the 'Approval Status' field is set to 'Approved'. Capture Timestamp of status change to 'Approved' in the Vendor Bill's audit trail. Event type explicit
	Bill Paid In Full	This activity signifies that the vendor bill has been fully paid and its status is updated accordingly. This is the successful completion of the process, inferred from a status change on the bill itself.
Why it matters This is the primary 'happy path' end event for the invoice lifecycle. Measuring the total time to reach this state provides the end-to-end cycle time for the process. Where to get Inferred from the system notes or audit trail on the Vendor Bill record, specifically the timestamp when the status changes to 'Paid in Full'. Capture Timestamp of status change to 'Paid in Full' on the Vendor Bill. Event type inferred
	Bill Posted To GL	This activity represents the moment the approved vendor bill impacts the general ledger. In standard NetSuite configurations, this occurs automatically upon final approval of the bill.
Why it matters This is a key financial control point, ensuring that liabilities are recognized correctly. Monitoring for deviations, such as payments made before posting, is critical for compliance analysis. Where to get The 'Posting' flag on the Vendor Bill is set to true. The timestamp can be sourced from the approval event date, as posting is typically synchronous with approval. Capture Timestamp of the final approval action which triggers GL posting. Event type explicit
	Vendor Bill Created	This activity marks the creation of a vendor bill record in NetSuite, which serves as the starting point for the invoice processing journey. This is captured from the creation date of the Vendor Bill transaction record.
Why it matters This is the primary start event for the invoice lifecycle. Analyzing the time from this activity to others reveals the total processing time and highlights initial data entry delays. Where to get From the 'Date Created' or transaction date field on the Vendor Bill record. Each Vendor Bill has a unique internal ID and transaction number. Capture Timestamp of Vendor Bill transaction creation. Event type explicit
	Vendor Payment Created	This activity marks the creation of the payment transaction that settles the vendor bill. This is an explicit event captured from the creation of a Vendor Payment record linked to the bill.
Why it matters Represents the execution of the payment, a critical step for on-time payment analysis and cash flow forecasting. It is a key milestone before the process end. Where to get From the creation date of the Vendor Payment transaction record. The payment record will have a reference in its 'Apply' sublist to the Vendor Bill it is paying. Capture Timestamp of Vendor Payment transaction creation. Event type explicit
	Bill Rejected	The vendor bill is rejected by an approver and removed from the active processing queue. This is captured from an explicit status change to 'Rejected' and often represents a terminal state unless resubmitted.
Why it matters Highlights process failures, such as duplicate invoices or incorrect vendor information. Tracking the frequency and reasons for rejection helps improve first-time accuracy and reduce wasted effort. Where to get Captured from the system notes or audit trail for the Vendor Bill, recording the timestamp when the 'Approval Status' field is set to 'Rejected'. Capture Timestamp of status change to 'Rejected' in the Vendor Bill's audit trail. Event type explicit
	Bill Sent For Rework	Occurs when an approver or reviewer sends the invoice back to the originator for correction. This is inferred from a status change that moves the bill backward in the workflow, for example from 'Pending Approval' back to 'Pending Submission'.
Why it matters Identifies rework loops that significantly extend processing times and increase manual effort. Tracking this helps pinpoint common causes for errors, such as incorrect coding or data entry mistakes. Where to get Inferred from the system notes or audit trail on the Vendor Bill, looking for a status regression or a specific 'Rework' status. Capture Timestamp of status change indicating a backward step in the approval workflow. Event type inferred
	Bill Submitted For Approval	Represents the point where the created vendor bill is formally submitted into an approval workflow. This is typically inferred from a change in the bill's status from 'Open' or 'Pending Submission' to 'Pending Approval'.
Why it matters Initiates the approval cycle. Measuring the time from this event to 'Bill Approved' or 'Bill Rejected' is critical for the Invoice Approval Cycle Time Analysis dashboard. Where to get Inferred from the system notes or audit trail on the Vendor Bill record, specifically tracking changes to the 'Approval Status' field. Capture Timestamp of status change to 'Pending Approval' in the Vendor Bill's audit trail. Event type inferred
	Credit Memo Applied	Represents the application of a vendor credit memo to a bill, reducing the amount owed. This is an explicit event captured from the Vendor Credit transaction's application record.
Why it matters Shows an alternative path to resolving an invoice that doesn't involve a direct cash payment. Frequent credit memos may indicate issues with initial order accuracy or vendor performance. Where to get From the application record linking a Vendor Credit to a Vendor Bill. The date of application would serve as the event timestamp. Capture Timestamp of applying a Vendor Credit transaction to the Vendor Bill. Event type explicit
	Early Payment Discount Missed	A calculated event indicating that the payment for an invoice with available early payment terms was not executed within the discount window. This event is derived by comparing the payment date to the discount due date.
Why it matters Directly supports the 'Early Payment Discount Capture Rate' KPI by quantifying lost savings. It helps organizations identify process or cash flow issues preventing them from capturing discounts. Where to get This is a calculated event. It requires comparing the 'Date' of the applied Vendor Payment transaction to the 'Discount Date' field on the source Vendor Bill record. Capture Compare Vendor Payment date with Vendor Bill discount date. If payment date > discount date, event occurs. Event type calculated
	Matching Discrepancy Identified	This activity occurs when a bill that is supposed to match a purchase order fails automated matching rules, requiring manual review. It's often inferred by the bill entering a specific 'Matching Hold' or 'Discrepancy' status.
Why it matters Highlights inefficiencies in the procurement and receiving processes that lead to manual rework. This directly supports the 'Invoice Matching Efficiency' dashboard and the 'Matching Discrepancy Rework Rate' KPI. Where to get Inferred from a status change on the Vendor Bill indicating a hold or exception, or by identifying bills linked to a PO that required significant manual edits before approval. Capture Timestamp of status change to a 'hold' or 'mismatch' status. May require analysis of custom fields or workflows. Event type inferred
	Payment Block Released	The payment block on an invoice is removed, making it eligible for payment again. This is inferred from the clearing of the 'Payment Hold' checkbox or a status change away from a 'Hold' state.
Why it matters Measuring the duration between 'Payment Block Set' and this activity reveals the efficiency of the resolution process. It helps identify common reasons for blocks and streamline their removal. Where to get Inferred from the audit trail for the 'Payment Hold' checkbox being unchecked or a custom status field being updated on the Vendor Bill. Capture Timestamp of change event for a 'Payment Hold' or similar field being cleared. Event type inferred
	Payment Block Set	A payment block is actively placed on an approved invoice, preventing it from being paid. This is often inferred from a specific checkbox being marked or a custom 'Hold' status being applied.
Why it matters This activity is crucial for the 'Payment Block Resolution Analysis' dashboard. It pinpoints delays between approval and payment readiness, which can harm vendor relationships. Where to get Inferred from the audit trail for a 'Payment Hold' checkbox or a custom status field on the Vendor Bill transaction. Capture Timestamp of change event for a 'Payment Hold' or similar field. Event type inferred
	Payment Scheduled In Batch	The approved invoice is selected and included in a payment batch for future execution. This is an explicit action where bills are added to a Vendor Payment Batch or processed through the 'Pay Bills' page.
Why it matters Shows the transition from an approved liability to an intended cash outflow. Analyzing delays between approval and scheduling can reveal cash management strategies or inefficiencies in the payment run process. Where to get This can be difficult to capture as a distinct event. It is often part of the payment creation process itself. It may be inferred from the creation date of a payment batch record that includes the bill. Capture Creation timestamp of a payment batch record that references the invoice. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Log into NetSuite: Access your NetSuite account with a role that has permissions to create and run 'Transaction' type Saved Searches.
Navigate to Saved Searches: Go to Reports > Saved Searches > All Saved Searches > New.
Select Search Type: From the list of new search types, select 'Transaction'.
Define Search Criteria: On the 'Criteria' tab, configure the following filters to narrow down the data:
- Set 'Type' to 'is any of' 'Vendor Bill', 'Vendor Payment', 'Vendor Credit'. This ensures you capture all relevant transaction types for the invoice lifecycle.
- Set 'Main Line' to 'is' 'true'. This filters for header-level information to avoid duplicate rows for each line item.
- Add a 'Date' filter (e.g., 'Date Created') for a specific period, such as the last 6 months, to keep the initial dataset manageable. For example, 'is within' 'last fiscal year'.
- (Optional) Add a 'Subsidiary' filter if your organization uses multiple subsidiaries.
Specify Result Columns: Navigate to the 'Results' tab. This is the most critical step. Add all the columns required for the event log. You must add fields that contain timestamps for different business events, statuses, and dimensional attributes. Refer to the 'query' section for a complete list of recommended columns and their NetSuite field IDs.
Set Additional Options: On the 'Available Filters' tab, consider adding 'Date' as a filter. This allows users to change the date range when running the search without editing the search definition.
Save and Run the Search: Give your Saved Search a descriptive name, such as 'ProcessMind_P2P_Invoice_Extraction', and click 'Save & Run'.
Export the Results: Once the search results are displayed, click the 'Export - CSV' icon to download the data as a CSV file.
Transform the Data: The exported file is a flat table, with one row per transaction. This must be transformed into an event log structure where each row represents a single activity. Use a script (e.g., Python with pandas) or a data preparation tool to pivot the data. For each row in the CSV, create multiple output rows, one for each relevant date column (e.g., 'Date Created', 'Date Approved', 'Payment Date'). Each new row will represent an activity like 'Vendor Bill Created', 'Bill Approved', or 'Vendor Payment Created', using the corresponding date as the 'EventTime'.
Calculate Derived Events: During the transformation step, calculate derived events. For example, to generate the 'Early Payment Discount Missed' activity, compare the 'Payment Date' field with the 'Discount Due Date' field. If the payment was made after the discount date, create an event row for it.
Finalize the Event Log: Ensure the final transformed CSV file contains the required columns: InvoiceNumber, ActivityName, EventTime, SourceSystem, and LastDataUpdate, along with any other recommended attributes. The file is now ready for upload into a process mining tool.

Configuration

Search Type: Transaction. This search type provides access to vendor bills, payments, and credit memos.
Criteria - Main Line: Set this to 'True' to ensure you are only pulling header-level information for each transaction, preventing duplicate events from transaction lines.
Criteria - Transaction Type: Filter for 'Vendor Bill', 'Vendor Payment', and 'Vendor Credit' to capture the complete lifecycle from invoice receipt to payment and any credit applications.
Criteria - Date Range: Use the 'Date Created' field to filter for a manageable period, typically 3 to 12 months of data, to ensure performance and relevance.
Results - Columns: You must add a comprehensive set of columns to capture all potential event timestamps and attributes. This includes creation dates, status fields, approval dates, payment dates, due dates, and related transaction details. See the query section for the full list.
Permissions: The user role running the search needs view permissions for Vendor Bill, Vendor Payment, Vendor Credit, and related records like Vendor and Employee.
Performance: For very large datasets (millions of transactions), consider running the search during off-peak hours or breaking the export into smaller time periods (e.g., quarterly) to avoid timeouts.

a Sample Query config

Below is the configuration for the NetSuite Saved Search, detailing the criteria (filters) and the result columns needed to extract the data.

**Search Type**: Transaction

**Criteria (Filters)**:
| Field | Description | Value |
|---|---|---|
| Type | Transaction Type | is any of: Vendor Bill, Vendor Payment, Vendor Credit |
| Main Line | Main Line Filter | is true |
| Date Created | Date Filter | is within: [Specify Date Range, e.g., last 6 months] |
| Subsidiary | Subsidiary Filter | is: [Your Subsidiary Name] (if applicable) |

**Results (Columns)**:
| Column Label | Field ID | Description |
|---|---|---|
| InvoiceNumber | tranid | Case ID. The unique number for the bill, payment, or credit. |
| InternalID | internalid | Internal system ID for joining. |
| DateCreated | datecreated | Timestamp for 'Vendor Bill Created' activity. |
| VendorName | entity | Required attribute 'Vendor Name'. |
| InvoiceAmount | amount | Required attribute 'Invoice Amount'. |
| DueDate | duedate | Required attribute 'Payment Due Date'. |
| Approver | approver | Recommended attribute 'Approver'. May require drilling into related fields. |
| ApprovalStatus | approvalstatus | Field for inferring activities like 'Bill Submitted For Approval', 'Bill Approved', 'Bill Rejected'. |
| SystemNotes_ApprovalStatus_Date | formuladate | Formula: `NS_CONCAT('systemnotes.newvalue=''Approved''')` to get the approval date. Similar formulas needed for other statuses. |
| PaymentDueDate | duedate | Attribute for 'Payment Due Date'. |
| Terms | terms | Used with Discount Date to derive 'Early Payment Discount Missed'. |
| DiscountDate | discountdate | The date by which payment must be made to receive a discount. |
| Status | statusref | The current status of the transaction, e.g., 'Open', 'Paid in Full'. |
| DatePostedToGL | trandate | Timestamp for 'Bill Posted To GL', typically the transaction date. |
| AppliedToTransaction | appliedtotransaction | Links payments/credits to the original bill. |
| ApplyingTransaction | applyingtransaction | Links bills to their payments/credits. |
| PaymentDate | applyingtransaction.trandate | The date of the payment transaction. Used for 'Vendor Payment Created' and 'Bill Paid In Full' events. |
| CreditMemoDate | applyingtransaction.trandate | The date a credit memo was applied. |
| PaymentHold | custbody_payment_hold | Placeholder for a custom 'Payment Block Set' / 'Released' checkbox. Track changes via System Notes. |
| MatchingStatus | custbody_matching_status | Placeholder for a custom field to identify 'Matching Discrepancy Identified'. |
| IsAutoMatched | custbody_is_auto_matched | Placeholder for a custom field for the 'Is Auto Matched' attribute. |

Steps

Prerequisites: Ensure your NetSuite account has the SuiteAnalytics Connect module enabled. Download and install the appropriate ODBC or JDBC driver for your operating system from the NetSuite home dashboard under 'Settings' > 'Set Up SuiteAnalytics Connect'.
Configure Driver: Set up a new DSN (Data Source Name) for the ODBC driver or configure the connection properties for the JDBC driver. You will need your Service Host, Port, Data Source, Account ID, and the Role ID you intend to use for the connection.
Establish Connection: Use a SQL client tool, such as DBeaver, DBVisualizer, or Microsoft Excel, to connect to the NetSuite data source using the driver and credentials you configured. The user credentials must be associated with a role that has permission to access transaction and entity records.
Prepare the SQL Query: Copy the complete SQL query provided in the 'query' section of this document. This query is designed to extract all required activities and attributes for the invoice processing analysis.
Customize Query Parameters: Before executing, you must modify the placeholders within the query. Replace [START_DATE] and [END_DATE] with the desired date range (e.g., '2023-01-01'). If you operate in a multi-subsidiary environment, replace [Your_Company_Subsidiary_ID] with the correct numerical ID for the subsidiary you wish to analyze.
Adjust for Customizations: Review the query for sections marked with comments about custom fields, especially for activities like 'Matching Discrepancy Identified' and 'Payment Block Set'. Adjust the field names (e.g., custbody_approval_status, custbody_payment_hold) to match your organization's specific NetSuite configuration.
Execute the Query: Run the customized SQL query in your connected SQL client. The execution time will vary depending on the date range and the volume of data in your NetSuite account.
Review the Results: Once the query finishes, perform a brief review of the returned data to ensure the columns and rows appear as expected. Check for a variety of activities and complete attributes.
Export to CSV: Export the entire result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding to prevent issues with special characters.
Prepare for Upload: Confirm the CSV file has the required headers (InvoiceNumber, ActivityName, EventTime, SourceSystem, LastDataUpdate) and that the EventTime column is in a consistent timestamp format (e.g., YYYY-MM-DD HH:MI:SS). The file is now ready for upload into the process mining tool.

Configuration

SuiteAnalytics Connect License: This extraction method requires a valid and active license for the NetSuite SuiteAnalytics Connect module. This provides the necessary drivers and access to the backend database.
User Role Permissions: The NetSuite role used for the connection must have sufficient permissions. At a minimum, it requires 'View' access to Transactions (specifically Vendor Bills, Vendor Payments, Vendor Credits), Lists > Entities, and Setup > System Notes.
Date Range: It is critical to filter the query by a specific date range using the [START_DATE] and [END_DATE] parameters. For an initial analysis, a range of 3 to 6 months is recommended to ensure manageable data volume and performance. Pulling multiple years of data in a single query is not recommended.
Subsidiary Filter: In NetSuite OneWorld accounts, filtering by subsidiary (subsidiary_id) is essential to ensure the data corresponds to a single legal entity and a consistent process. The placeholder [Your_Company_Subsidiary_ID] must be replaced.
Custom Fields: The query assumes a mix of standard and common custom fields for tracking status. Be prepared to identify and substitute your company's specific custom field IDs for approval statuses, payment holds, and matching information. These are noted in the query comments.

a Sample Query sql

WITH BaseInvoices AS (
    SELECT 
        T.transaction_id,
        T.tranid AS InvoiceNumber,
        E.companyname AS VendorName,
        T.foreignTotal AS InvoiceAmount,
        T.dueDate AS PaymentDueDate,
        T.createdDate,
        T.status AS InvoiceStatus,
        T.approvalstatus,
        T.tranDate,
        -- Placeholder for IsAutoMatched logic. Adjust based on your configuration.
        -- This might be a custom field on the bill or inferred from other data.
        'No' AS IsAutoMatched 
    FROM 
        Transaction T
    JOIN 
        Entity E ON T.entity_id = E.entity_id
    WHERE 
        T.type = 'VendBill'
        AND T.createdDate BETWEEN TO_DATE('[START_DATE]', 'YYYY-MM-DD') AND TO_DATE('[END_DATE]', 'YYYY-MM-DD')
        AND T.subsidiary_id = [Your_Company_Subsidiary_ID]
),
Payments AS (
    SELECT 
        ATL.prev_tran_id AS bill_id,
        MAX(P.trandate) AS final_payment_date
    FROM
        Transaction P
    JOIN 
        TransactionLine APL ON P.transaction_id = APL.transaction_id
    JOIN
        AppliedToTransactionLink ATL ON APL.transactionline_id = ATL.appliedtolink_id
    WHERE P.type = 'VendPymt'
    GROUP BY ATL.prev_tran_id
)
-- 1. Vendor Bill Created
SELECT 
    B.InvoiceNumber,
    'Vendor Bill Created' AS ActivityName,
    B.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id

UNION ALL

-- 2. Bill Submitted For Approval / 5. Bill Approved / 6. Bill Rejected
SELECT 
    B.InvoiceNumber,
    CASE 
        WHEN SN.new_value_id = '2' THEN 'Bill Approved' -- '2' is typically the internal ID for 'Approved'
        WHEN SN.new_value_id = '3' THEN 'Bill Rejected' -- '3' is typically the internal ID for 'Rejected'
        WHEN SN.new_value_id = '1' AND SN.old_value_id = '2' THEN 'Bill Sent For Rework' -- Example: Approved back to Pending
        WHEN SN.new_value_id = '1' THEN 'Bill Submitted For Approval' -- '1' is 'Pending Approval'
    END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_APPROVALSTATUS' 
    AND SN.new_value_id IN ('1', '2', '3')

UNION ALL

-- 3. Matching Discrepancy Identified / 4. Bill Sent For Rework (via custom status)
-- This section is highly dependent on custom fields and workflow. Adjust field name as needed.
SELECT 
    B.InvoiceNumber,
    CASE 
        WHEN SN.new_value = 'Matching Hold' THEN 'Matching Discrepancy Identified'
        WHEN SN.new_value = 'Pending Submission' AND SN.old_value = 'Pending Approval' THEN 'Bill Sent For Rework'
    END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = '[Your_Custom_Status_Field_ID]' -- E.g., 'CUSTBODY_INVOICE_STATUS'
    AND (SN.new_value = 'Matching Hold' OR (SN.new_value = 'Pending Submission' AND SN.old_value = 'Pending Approval'))

UNION ALL

-- 7. Payment Block Set / 8. Payment Block Released
-- Assumes a custom checkbox field 'custbody_payment_hold'. 'T' for True, 'F' for False.
SELECT 
    B.InvoiceNumber,
    CASE WHEN SN.new_value = 'T' THEN 'Payment Block Set' ELSE 'Payment Block Released' END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = '[Your_Payment_Hold_Field_ID]' -- E.g., 'CUSTBODY_PAYMENT_HOLD'
    AND SN.new_value IN ('T', 'F')

UNION ALL

-- 9. Bill Posted To GL
-- This typically happens upon approval in standard configurations.
SELECT 
    B.InvoiceNumber,
    'Bill Posted To GL' AS ActivityName,
    SN.date AS EventTime, -- Using approval date as posting date
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_APPROVALSTATUS' 
    AND SN.new_value_id = '2' -- 'Approved'

UNION ALL

-- 10. Payment Scheduled In Batch / 12. Vendor Payment Created
-- Using Vendor Payment creation as the event for both.
SELECT 
    B.InvoiceNumber,
    'Vendor Payment Created' AS ActivityName, 
    P.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN PayDates.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    AppliedToTransactionLink ATL ON B.transaction_id = ATL.prev_tran_id
JOIN 
    TransactionLine APL ON ATL.appliedtolink_id = APL.transactionline_id
JOIN
    Transaction P ON APL.transaction_id = P.transaction_id
LEFT JOIN
    Payments PayDates ON B.transaction_id = PayDates.bill_id
WHERE P.type = 'VendPymt'

UNION ALL

-- 11. Early Payment Discount Missed
-- This event is created if a payment exists and was made after the discount date.
SELECT
    B.InvoiceNumber,
    'Early Payment Discount Missed' AS ActivityName,
    T.discountDate + 1 AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    'No' AS IsPaidOnTime
FROM
    BaseInvoices B
JOIN 
    Transaction T ON B.transaction_id = T.transaction_id
JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    T.discountDate IS NOT NULL
    AND P.final_payment_date > T.discountDate

UNION ALL

-- 13. Bill Paid In Full
SELECT 
    B.InvoiceNumber,
    'Bill Paid In Full' AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_STATUS' AND SN.new_value_id = 'B' -- 'B' is 'Paid In Full' for Vendor Bills

UNION ALL

-- 14. Credit Memo Applied
SELECT 
    B.InvoiceNumber,
    'Credit Memo Applied' AS ActivityName, 
    C.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    AppliedToTransactionLink ATL ON B.transaction_id = ATL.prev_tran_id
JOIN 
    TransactionLine APL ON ATL.appliedtolink_id = APL.transactionline_id
JOIN
    Transaction C ON APL.transaction_id = C.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE C.type = 'VendCred'

Steps

Enable NetSuite Web Services: Log in to NetSuite as an administrator. Navigate to Setup > Company > Enable Features. On the SuiteCloud subtab, ensure that REST WEB SERVICES is enabled. Also, enable SUITEQL if it is not already.
Create an Integration Record: Navigate to Setup > Integration > Manage Integrations > New. Create a new integration record, ensuring you enable Token,Based Authentication and OAuth 2.0. Save the record and securely store the generated Consumer Key and Consumer Secret.
Create an Access Token: Navigate to Setup > Users/Roles > Access Tokens > New. Select the Integration record you just created, the appropriate User, and a Role with permissions to view Vendor Bills, Vendor Payments, and System Notes. Save the token and securely store the Token ID and Token Secret.
Set Up Python Environment: Ensure you have Python 3 installed. Install the necessary libraries by running the following command in your terminal: pip install requests requests-oauthlib pandas.
Configure the Python Script: Copy the provided Python script into a file, for example, extract_netsuite_p2p.py. Replace the placeholder values for ns_account_id, consumer_key, consumer_secret, token_id, and token_secret with the credentials you obtained in the previous steps.
Customize the SuiteQL Query: Review the large SuiteQL query string within the script. Pay close attention to placeholders for custom fields and statuses, such as custbody_payment_hold or [Matching Discrepancy Status]. Adjust these based on your specific NetSuite configuration. Also, set the date range filter in the WHERE clause to limit the initial data pull.
Execute the Script: Run the script from your terminal using the command: python extract_netsuite_p2p.py. The script will connect to the NetSuite API, execute the query, handle pagination, and retrieve the data.
Monitor the Extraction: The script will print progress updates to the console, showing how many records have been fetched. For large datasets, this process may take some time.
Review the Output File: Once the script completes, it will generate a file named netsuite_invoice_event_log.csv. This CSV file contains the extracted event log.
Prepare for Upload: Open the CSV file to ensure the data is structured correctly with the required headers: InvoiceNumber, ActivityName, EventTime, SourceSystem, and LastDataUpdate. The file is now ready for upload into ProcessMind.

Configuration

Authentication Credentials: You must have the following credentials from a NetSuite Integration Record and Access Token: Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret.
User Role Permissions: The NetSuite role associated with the access token must have at least 'View' level permissions for the following record types: Vendor Bill, Vendor Payment, Vendor Credit, Entity (for vendor details), and System Note.
Date Range Filtering: It is critical to apply a date range filter within the SuiteQL query to manage performance and data volume. The provided query includes a placeholder WHERE transaction.trandate >= '[YYYY-MM-DD]'. We recommend starting with a 3 to 6 month period of data.
Subsidiary Filtering: For NetSuite OneWorld accounts, you must add a filter for the specific subsidiary you want to analyze, for example, AND transaction.subsidiary = [Your Subsidiary ID], to avoid extracting data from all legal entities.
Custom Field Adjustments: The query uses placeholders for activities that are often tracked via custom fields or statuses, such as custbody_payment_hold for payment blocks. You must identify the correct field IDs in your NetSuite instance and update the query accordingly.
API Rate Limiting: Be aware of NetSuite's API concurrency and rate limits. Running very large extractions may require adding wait times or back,off logic to the script to avoid being throttled by the API.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your NetSuite Credentials ---
ns_account_id = os.environ.get(class="hl-string">'NS_ACCOUNT_ID', class="hl-string">'YOUR_ACCOUNT_ID')
consumer_key = os.environ.get(class="hl-string">'NS_CONSUMER_KEY', class="hl-string">'YOUR_CONSUMER_KEY')
consumer_secret = os.environ.get(class="hl-string">'NS_CONSUMER_SECRET', class="hl-string">'YOUR_CONSUMER_SECRET')
token_id = os.environ.get(class="hl-string">'NS_TOKEN_ID', class="hl-string">'YOUR_TOKEN_ID')
token_secret = os.environ.get(class="hl-string">'NS_TOKEN_SECRET', class="hl-string">'YOUR_TOKEN_SECRET')

class=class="hl-string">"hl-comment"># Construct the NetSuite REST API URL
class=class="hl-string">"hl-comment"># Use .suitetalk.api for production, .connect.api for sandbox
url_base = fclass="hl-string">"https://{ns_account_id.lower().replace('_', '-')}.suitetalk.api.netsuite.com"
query_url = fclass="hl-string">"{url_base}/services/rest/query/v1/suiteql"

class=class="hl-string">"hl-comment"># --- SuiteQL Query ---
class=class="hl-string">"hl-comment"># This query unions multiple subqueries to create a complete event log.
class=class="hl-string">"hl-comment"># You MUST customize placeholders like class="hl-string">'[Matching Discrepancy Status]' and class="hl-string">'custbody_payment_hold'.
suiteql_query = class="hl-string">"""
SELECT
    InvoiceNumber,
    ActivityName,
    EventTime,
    'NetSuite' AS SourceSystem,
    BUILTIN.TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') AS LastDataUpdate,
    VendorName,
    InvoiceAmount,
    PaymentDueDate,
    Approver,
    InvoiceStatus,
    IsAutoMatched,
    IsPaidOnTime
FROM (
    -- 1. Vendor Bill Created
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Vendor Bill Created' AS ActivityName,
        transaction.trandate AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        transaction.nextapprover AS Approver,
        transaction.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD') -- Set your date range

    UNION ALL

    -- 2. Bill Status Changes (Submitted, Approved, Rejected, Rework) from System Notes
    SELECT
        transaction.tranid AS InvoiceNumber,
        CASE
            WHEN systemnote.newvalue = 'Pending Approval' THEN 'Bill Submitted For Approval'
            WHEN systemnote.newvalue = 'Approved' THEN 'Bill Approved'
            WHEN systemnote.newvalue = 'Rejected' THEN 'Bill Rejected'
            -- Configure the 'old value' based on your rework process
            WHEN systemnote.newvalue = 'Open' AND systemnote.oldvalue = 'Pending Approval' THEN 'Bill Sent For Rework'
            ELSE NULL
        END AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_APPROVALSTATUS' -- This is a common field, but may vary
        AND ActivityName IS NOT NULL
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 3. Matching Discrepancy Identified
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Matching Discrepancy Identified' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        NULL AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        'No' AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_STATUS' -- May also be approvalstatus
        AND systemnote.newvalue = '[Matching Discrepancy Status]' -- *** CONFIGURE THIS VALUE ***
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 7 & 8. Payment Block Set/Released (using a custom field)
    SELECT
        transaction.tranid AS InvoiceNumber,
        CASE
            WHEN systemnote.newvalue = 'T' THEN 'Payment Block Set'
            WHEN systemnote.newvalue = 'F' THEN 'Payment Block Released'
        END AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        transaction.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'CUSTBODY_PAYMENT_HOLD' -- *** CONFIGURE YOUR CUSTOM FIELD ID ***
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 9. Bill Posted to GL (assumed to occur at approval time)
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Bill Posted To GL' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        'Posted' AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_APPROVALSTATUS' AND systemnote.newvalue = 'Approved'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 12. Vendor Payment Created & 10. Payment Scheduled In Batch (Approximated)
    SELECT
        bill.tranid AS InvoiceNumber,
        'Vendor Payment Created' AS ActivityName,
        payment.trandate AS EventTime,
        vendor.companyname AS VendorName,
        bill.foreignTotal AS InvoiceAmount,
        bill.duedate AS PaymentDueDate,
        payment.createdby AS Approver,
        payment.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        CASE WHEN payment.trandate <= bill.duedate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
    FROM
        Transaction AS payment
    JOIN
        TransactionLine AS paymentLine ON payment.id = paymentLine.transaction
    JOIN
        Transaction AS bill ON paymentLine.appliedToTransaction = bill.id
    JOIN
        Entity AS vendor ON bill.entity = vendor.id
    WHERE
        payment.type = 'VendPymt' AND payment.mainline = 'F' AND bill.type = 'VendBill'
        AND payment.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 13. Bill Paid In Full
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Bill Paid In Full' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        NULL AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_STATUS' AND systemnote.newvalue = 'Paid In Full'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 14. Credit Memo Applied
    SELECT
        bill.tranid AS InvoiceNumber,
        'Credit Memo Applied' AS ActivityName,
        credit.trandate AS EventTime,
        vendor.companyname AS VendorName,
        bill.foreignTotal AS InvoiceAmount,
        bill.duedate AS PaymentDueDate,
        credit.createdby AS Approver,
        credit.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction AS credit
    JOIN
        TransactionLine AS creditLine ON credit.id = creditLine.transaction
    JOIN
        Transaction AS bill ON creditLine.appliedToTransaction = bill.id
    JOIN
        Entity AS vendor ON bill.entity = vendor.id
    WHERE
        credit.type = 'VendCred' AND credit.mainline = 'F' AND bill.type = 'VendBill'
        AND credit.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    -- Note: 'Early Payment Discount Missed' is best derived in post-processing. A query for it is complex and unreliable.
)
ORDER BY
    InvoiceNumber, EventTime
"""

def execute_suiteql(query, oauth_session):
    headers = {
        class="hl-string">'Prefer': class="hl-string">'transient',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    payload = json.dumps({class="hl-string">'q': query})
    all_results = []
    offset = 0
    limit = 1000  class=class="hl-string">"hl-comment"># NetSuite's max page size

    while True:
        paginated_url = fclass="hl-string">"{query_url}?limit={limit}&offset={offset}"
        try:
            response = oauth_session.post(paginated_url, headers=headers, data=payload)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()

            if not data or class="hl-string">'items' not in data or not data[class="hl-string">'items']:
                print(class="hl-string">"No more items found.")
                break
            
            results = data.get(class="hl-string">'items', [])
            all_results.extend(results)
            print(fclass="hl-string">"Fetched {len(results)} records, total so far: {len(all_results)}")

            has_more = data.get(class="hl-string">'hasMore', False)
            if not has_more:
                break

            offset += limit

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error: {e.response.status_code} {e.response.text}")
            break
        except Exception as e:
            print(fclass="hl-string">"An error occurred: {e}")
            break

    return all_results

def main():
    print(class="hl-string">"Starting NetSuite data extraction...")
    oauth = OAuth1(
        client_key=consumer_key,
        client_secret=consumer_secret,
        resource_owner_key=token_id,
        resource_owner_secret=token_secret,
        realm=ns_account_id
    )

    results = execute_suiteql(suiteql_query, requests.Session()) class=class="hl-string">"hl-comment"># Use a basic session, OAuth1 handles auth
    class=class="hl-string">"hl-comment"># The above line needs correction, the oauth object should be passed to the session
    class=class="hl-string">"hl-comment"># Correct implementation:
    session = requests.Session()
    session.auth = oauth
    
    results = execute_suiteql(suiteql_query, session)

    if results:
        df = pd.DataFrame(results)
        class=class="hl-string">"hl-comment"># Re-map columns from the response which are often dictionaries
        for col in df.columns:
            df[col] = df[col].apply(lambda x: x[class="hl-string">'value'] if isinstance(x, dict) and class="hl-string">'value' in x else x)
        
        output_filename = class="hl-string">'netsuite_invoice_event_log.csv'
        df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')
        print(fclass="hl-string">"Successfully extracted {len(df)} events to {output_filename}")
    else:
        print(class="hl-string">"No data was extracted.")

if __name__ == class="hl-string">"__main__":
    main()

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

NetSuite Saved Search Export for Invoice Processing

Steps

Configuration

a Sample Query config

Direct SQL Queries via NetSuite SuiteAnalytics Connect

Steps

Configuration

a Sample Query sql

Python Extraction using NetSuite REST API (SuiteQL)

Steps

Configuration

a Sample Query python

Ready to Get Started?

Transform Purchase to Pay Invoice Processing Today

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

NetSuite Saved Search Export for Invoice Processing

Steps

Configuration

a Sample Query config

Direct SQL Queries via NetSuite SuiteAnalytics Connect

Steps

Configuration

a Sample Query sql

Python Extraction using NetSuite REST API (SuiteQL)

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Transform Purchase to Pay Invoice Processing Today