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

Data Template: Accounts Payable Invoice Processing

NetSuite

Accounts Payable Invoice Processing Attributes (22) Accounts Payable Invoice Processing Activities (14) Extraction Guides (4)

Your Accounts Payable Invoice Processing Data Template

This comprehensive template is designed to guide you through collecting the necessary data for optimizing your Accounts Payable Invoice Processing. It outlines the essential attributes to collect and the key activities to track within your event log. Additionally, you will find practical guidance on extracting this data specifically from NetSuite, ensuring you have all the information needed for accurate process analysis and improvement.

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

Accounts Payable Invoice Processing Attributes

These are the recommended data fields to include in your event log for comprehensive accounts payable invoice processing analysis.

5 Required 6 Recommended 11 Optional

	Name	Description
	Activity ActivityName	The name of the business activity or event that occurred in the invoice process.
Description This attribute describes a specific step or milestone in the Accounts Payable process, such as 'Vendor Bill Created', 'Invoice Approved', or 'Payment Executed'. Analyzing the sequence and frequency of these activities is the core of process mining. It helps visualize the process flow, identify common and rare process variants, and pinpoint bottlenecks or deviations from the standard procedure. The defined activities form the nodes in the process map. Why it matters It defines the steps of the process, allowing for the visualization of the process map, calculation of transition times, and identification of bottlenecks and rework loops. Where to get Derived from system logs, audit trails, or status changes on the Vendor Bill and related payment records in NetSuite. This often requires transformation logic. Examples Vendor Bill CreatedInvoice ApprovedPayment ExecutedDiscrepancy Identified
	Invoice Invoice	The unique identifier for each vendor invoice.
Description The 'Invoice' serves as the primary case identifier, linking all activities from the moment an invoice is received until its final payment. This attribute is essential for reconstructing the end-to-end journey of each invoice. In analysis, it allows for filtering, aggregation, and detailed examination of individual invoice process flows, forming the backbone of variant analysis and cycle time calculations. Why it matters It is the fundamental attribute that connects all related events into a single process instance, enabling end-to-end analysis of each invoice's lifecycle. Where to get This is the primary key of the Vendor Bill transaction record in NetSuite, often referred to as the Transaction ID or Document Number. Examples INV-2023-001237894561VN54321A
	Start Time EventTime	The timestamp indicating when an activity or event started.
Description This attribute records the precise date and time that a specific activity occurred. It provides the chronological order of events within each case. This timestamp is critical for all time-based analysis, including calculating cycle times between activities, total end-to-end process duration, and identifying delays. It is used to order events correctly and forms the basis for performance KPIs like 'Average Invoice Approval Time' and 'Days Payable Outstanding'. Why it matters It provides the chronological sequence of events, which is essential for calculating all duration-based metrics, discovering bottlenecks, and understanding process performance. Where to get Timestamp fields associated with status changes or creation/modification dates in NetSuite's transaction records and system notes. Examples 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:05Z
	Last Data Update LastDataUpdate	The timestamp when the data for this event was last refreshed from the source system.
Description This attribute indicates the most recent time the event data was extracted from NetSuite. It reflects the freshness of the data in the process mining tool. This is important for understanding the timeliness of the analysis. Users can see how current the data is, which is especially important for operational dashboards tracking near real-time invoice statuses. Why it matters Provides transparency on data freshness, allowing users to understand how current the process analysis is and when the next data refresh is expected. Where to get This timestamp is generated by the data extraction tool or pipeline at the moment of data ingestion. Examples 2024-03-10T02:00:00Z2024-03-11T02:00:00Z
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the origin of the process data. For this view, it will consistently be 'NetSuite'. In environments with multiple systems (e.g., an external OCR system and an ERP), this field is crucial for data lineage and troubleshooting. It confirms that all events are being correctly attributed to the source system of record. Why it matters Ensures data provenance and is critical in multi-system environments to trace data back to its origin for validation and context. Where to get This is a static value ('NetSuite') populated during the data extraction and transformation process. Examples NetSuite
	Approver Approver	The user responsible for approving the invoice for payment.
Description This attribute identifies the manager or budget holder who provided the final approval for the invoice. An invoice may have multiple approvers in its lifecycle. This is a critical dimension for the 'Approval Bottleneck Analysis' dashboard. By analyzing approval times per approver, the business can identify individuals who are slow to approve, re-route invoices to balance workload, and streamline the overall approval process. Why it matters Directly supports the identification of approval bottlenecks and helps in analyzing the workload and performance of managers involved in the approval chain. Where to get This information is often stored in the workflow history or approval logs associated with the Vendor Bill transaction. Examples s.chenp.williamsr.davis
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description This attribute represents the total gross amount due on the vendor invoice, before any deductions or discounts. It is a core financial metric for each case. Invoice amount is used for financial analysis, such as categorizing invoices into value bands (high-value vs. low-value) to see if their process paths differ. It's also used in dashboards to understand the financial impact of bottlenecks or delays and is fundamental for DPO calculations. Why it matters Enables financial impact analysis of process inefficiencies and allows for segmentation of invoices by value to prioritize high-value items. Where to get The 'Amount' or 'Total' field in the header of the Vendor Bill transaction record. Examples 1500.75250.0012500.50
	Invoice Due Date InvoiceDueDate	The date by which the invoice must be paid to avoid penalties.
Description This attribute specifies the payment due date as determined by the vendor's payment terms and the invoice date. It is a critical deadline in the AP process. This date is the benchmark for the 'Payment Compliance Dashboard' and the 'Payment Term Adherence Rate' KPI. It is used to determine if payments are on-time, early, or late, which directly impacts vendor relationships and can incur late payment fees. Why it matters Serves as the primary benchmark for measuring on-time payment performance, which is crucial for vendor relations and financial compliance. Where to get This is a standard 'Due Date' field on the Vendor Bill transaction record. Examples 2023-11-302023-12-152024-02-28
	Processor User ProcessorUser	The user responsible for processing or entering the invoice.
Description This attribute identifies the Accounts Payable team member who performed data entry or initial processing steps on the invoice. This data is essential for the 'AP Staff Workload Distribution' dashboard. It helps managers analyze workload balance, identify training needs, and measure individual or team performance. It can also be used to investigate the root cause of errors by tracing them back to the processor. Why it matters Enables workload analysis, performance monitoring, and root cause analysis of errors by attributing activities to specific AP staff members. Where to get The 'Created By' or 'Modified By' fields in the system notes or audit trail for early-stage activities on the Vendor Bill. Examples j.doea.smithm.jones
	Purchase Order Number PurchaseOrderNumber	The identifier of the purchase order associated with the invoice, if applicable.
Description This attribute links the vendor invoice to a pre-approved purchase order (PO). Its presence or absence is key to distinguishing between PO-backed and non-PO invoices. This field is crucial for the 'PO/GR Matching Efficiency' dashboard and the 'PO/GR Matching Success Rate' KPI. Analyzing processes based on whether an invoice is PO-backed helps identify efficiency gains from three-way matching and opportunities for automation. Why it matters Distinguishes between PO and non-PO invoices, which often follow different process paths and have different automation potential. It's key to analyzing matching efficiency. Where to get This is typically found on the line items or in the header of the Vendor Bill record, linking it to a Purchase Order transaction. Examples PO-004589PO-005123null
	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. It is a critical piece of master data linked to the transaction. Analyzing the process by vendor helps identify supplier-specific issues, such as frequent discrepancies, payment delays, or opportunities for dynamic discounting. It is essential for the 'Invoice Status and Aging' dashboard to categorize open invoices and for relationship management. Why it matters Allows for performance analysis by vendor, helping to identify problematic suppliers, negotiate better terms, and manage relationships effectively. Where to get From the Vendor record linked to the Vendor Bill transaction. The field is typically labeled 'Vendor Name' or 'Company Name'. Examples Global Office Supplies Inc.Tech Solutions LLCCreative Marketing Co.
	Company Code CompanyCode	The identifier for the legal entity or subsidiary processing the invoice.
Description This attribute specifies the company or subsidiary within the organization that is liable for the invoice. This is particularly relevant for businesses with multiple legal entities. Analyzing the process by Company Code allows for performance comparison between different parts of the business. It can highlight variations in efficiency, compliance, or workload that are specific to a subsidiary, enabling targeted improvement initiatives. Why it matters Enables process performance to be compared across different legal entities or subsidiaries, facilitating targeted improvements and ensuring corporate governance. Where to get In NetSuite, this corresponds to the 'Subsidiary' field on the transaction record in a OneWorld account. Examples US01DE01UK01
	Currency Currency	The currency code for the invoice amount (e.g., USD, EUR).
Description This attribute specifies the currency in which the invoice is denominated. It provides essential context for the 'InvoiceAmount'. For multinational organizations, analyzing data in a single, consistent currency is vital. This field allows for proper currency conversion and ensures that financial metrics are compared on a like-for-like basis across different regions or business units. Why it matters Provides necessary context for all monetary values, ensuring accurate financial analysis, especially in multi-national operations. Where to get The 'Currency' field on the Vendor Bill transaction record, often linked to the vendor's master data. Examples USDEURGBPCAD
	Discount Date EarlyPaymentDiscountDate	The deadline to pay an invoice to be eligible for an early payment discount.
Description This attribute specifies the last date a payment can be made to qualify for a vendor discount, as defined by the 'Payment Terms' (e.g., the '10' in '2% 10, Net 30'). This date is the primary driver for the 'Early Payment Discount Opportunities' dashboard. By comparing this date to the current date and payment date, the system can identify opportunities, measure the 'Early Payment Discount Capture Rate' KPI, and help the company maximize cost savings. Why it matters This is the critical date for maximizing cost savings through early payment discounts; tracking it helps optimize cash management. Where to get This is a calculated field on the Vendor Bill record, derived from the invoice date and the 'Terms' field. Examples 2023-11-102024-01-20null
	Document Type DocumentType	The type of document being processed (e.g., Invoice, Credit Memo).
Description This attribute differentiates between standard invoices and other related documents like credit memos, which may follow different processing rules or workflows. Analyzing the process by document type ensures that you are comparing similar items. For instance, the approval process for a large invoice may be much more rigorous than for a small credit memo. This segmentation provides a more accurate view of process performance. Why it matters Allows for the segmentation of the process for different types of financial documents, which may have distinct paths and KPIs, leading to more precise analysis. Where to get This can be identified from the transaction type in NetSuite (e.g., 'Vendor Bill', 'Vendor Credit'). Examples Standard InvoiceCredit MemoDebit Memo
	Invoice Status InvoiceStatus	The current processing status of the invoice.
Description This attribute indicates the current state of the vendor bill within the AP lifecycle, such as 'Open', 'Pending Approval', or 'Paid in Full'. This is a key attribute for the 'Invoice Status and Aging' dashboard, providing a snapshot of the current workload and liabilities. It allows for filtering to focus on open or problematic invoices and helps track progress towards completion. Why it matters Provides a real-time snapshot of where an invoice is in the process, which is essential for operational monitoring, workload management, and financial forecasting. Where to get The 'Status' field on the Vendor Bill transaction record. Examples OpenPending ApprovalPaid in Full
	Is Early Pay Captured IsEarlyPaymentCaptured	A flag indicating if an available early payment discount was successfully captured.
Description This calculated boolean attribute checks if an invoice with an available discount was paid on or before the 'Early Payment Discount Date'. This attribute is essential for measuring the 'Early Payment Discount Capture Rate' KPI and for the 'Early Payment Discount Opportunities' dashboard. It provides a clear success/failure metric for the company's discount capture strategy, highlighting missed savings. Why it matters Directly measures the effectiveness of the treasury or AP function in capturing cost savings, a key financial performance indicator. Where to get Calculated during data transformation: `PaymentDate <= EarlyPaymentDiscountDate` for invoices where a discount is available. Examples truefalsenull
	Is On Time Payment IsOnTimePayment	A flag indicating if the invoice was paid on or before its due date.
Description This calculated boolean attribute evaluates whether the 'Payment Date' is less than or equal to the 'Invoice Due Date'. It provides a clear, binary outcome for each invoice's payment timeliness. This attribute directly supports the 'Payment Compliance Dashboard' and is used to calculate the 'Payment Term Adherence Rate' KPI. It simplifies analysis by allowing users to instantly filter for all late payments and investigate their root causes. Why it matters Simplifies the analysis of payment timeliness and directly fuels KPIs related to payment term adherence and vendor relationship management. Where to get Calculated during data transformation: `PaymentDate <= InvoiceDueDate`. Examples truefalse
	Is Rework IsRework	A flag indicating if the activity is part of a rework loop.
Description This calculated boolean attribute identifies activities that represent repeated work, such as a second 'Discrepancy Identified' event or an invoice being rejected and then resubmitted for approval. This attribute is critical for the 'Rework and Error Analysis' dashboard and the 'Invoice Rework Rate' KPI. It allows the business to quantify the frequency and impact of rework, helping to pinpoint sources of inefficiency, errors, and process deviations. Why it matters Quantifies process inefficiency by flagging activities that represent wasted effort, allowing for targeted initiatives to improve quality and first-time-right rates. Where to get Calculated during data transformation by analyzing the sequence of activities for a given case. For example, if 'Invoice Approved' is followed by 'Invoice Rejected' and then another 'Invoice Approved'. Examples truefalse
	Payment Date PaymentDate	The date when the payment for the invoice was executed.
Description This attribute records the date that the cash disbursement was made to the vendor. It marks the final financial settlement of the invoice. This date is used to determine the actual end of the process for many KPIs, including 'Days Payable Outstanding' and 'Payment Term Adherence Rate'. It is the factual event used to measure against the 'Invoice Due Date' and 'Early Payment Discount Date'. Why it matters It marks the financial conclusion of the process and is the basis for calculating payment compliance, DPO, and discount capture KPIs. Where to get From the 'Date' field of the associated Bill Payment transaction record in NetSuite. Examples 2023-11-282023-12-142024-02-25
	Payment Terms PaymentTerms	The agreed-upon terms for invoice payment (e.g., Net 30, 2% 10 Net 30).
Description This attribute defines the conditions under which the vendor expects payment, including the timeframe and any potential discounts for early payment. Payment terms are used to calculate the 'Invoice Due Date' and any 'Early Payment Discount Date'. They are essential for the 'Payment Compliance Dashboard' and for analyzing opportunities to capture early payment discounts, thereby optimizing working capital. Why it matters It determines payment deadlines and discount opportunities, directly impacting cash flow management and the ability to capture savings. Where to get The 'Terms' field on the Vendor Bill transaction, typically inherited from the Vendor master record. Examples Net 30Net 602% 10, Net 30
	Processing Time ProcessingTime	The time spent actively working on an activity.
Description This attribute measures the duration from the start to the end of a single activity. It represents the time a resource was actively engaged in a task. Unlike cycle time (which includes waiting time), processing time helps analyze the actual effort required for specific tasks. For example, it could measure the time spent on 'PO Matching Attempted' to assess the efficiency of that specific step and identify opportunities for automation. Why it matters Measures the actual work duration of an activity, helping to distinguish between active work time and idle wait time for better resource and efficiency analysis. Where to get Calculated by taking the difference between an activity's EndTime and StartTime. Requires both timestamps to be available for each activity. Examples PT5MPT1H30MPT8H

Required Recommended Optional

Accounts Payable Invoice Processing Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and bottleneck identification.

6 Recommended 8 Optional

	Activity	Description
	Approval Submitted	This event marks the point where the Vendor Bill is formally entered into an approval workflow. It is captured when the bill's 'Approval Status' changes from a draft state to 'Pending Approval'.
Why it matters This activity is the starting point for measuring the approval cycle time, which is essential for identifying bottlenecks with specific approvers or departments. Where to get Inferred from the timestamp when the 'Approval Status' on the Vendor Bill record changes to 'Pending Approval', as tracked in the System Notes. Capture Timestamp of 'Approval Status' field changing to 'Pending Approval'. Event type inferred
	Invoice Approved	Signifies that the Vendor Bill has been authorized for payment by the designated approver(s). This is captured when the bill's 'Approval Status' is set to 'Approved'.
Why it matters This is a critical milestone that concludes the approval phase and allows the invoice to be posted and scheduled for payment. It is a key data point for measuring approval efficiency. Where to get Timestamp of the 'Approval Status' field on the Vendor Bill record changing to 'Approved', as recorded in System Notes or a workflow log. Capture Workflow log or System Notes timestamp for 'Approval Status' = 'Approved'. Event type explicit
	Invoice Closed	Marks the final closure of the Vendor Bill record, typically after it has been fully paid. This is inferred when the Vendor Bill's status changes to 'Paid in Full'.
Why it matters Provides a definitive end event for the process lifecycle, confirming that no further action is required for that specific invoice. Where to get Inferred from the timestamp when the 'Status' field on the Vendor Bill record changes to 'Paid in Full', as tracked in System Notes. Capture Timestamp of 'Status' field changing to 'Paid in Full'. Event type inferred
	Invoice Posted To GL	Marks the moment the financial impact of the Vendor Bill is officially recorded in the General Ledger. This is captured by the transaction date when the bill is approved and the 'Posting' flag is set.
Why it matters This is a key financial control point, confirming the liability has been formally recognized by the company. Delays in posting can affect financial reporting accuracy. Where to get The posting date associated with the GL transaction created from the Vendor Bill. The 'Posting' field on the bill will be checked. Capture Transaction date of the GL posting associated with the Vendor Bill. Event type explicit
	Payment Executed	Represents the final execution of payment for the invoice. This event is captured from the transaction date of the Vendor Payment record applied to the Vendor Bill.
Why it matters This is the primary end-point for the AP process and is essential for calculating end-to-end cycle time, payment term adherence, and Days Payable Outstanding (DPO). Where to get The 'Date' field from the Vendor Payment transaction record linked to the Vendor Bill. Capture Transaction date of the Vendor Payment record applied to the bill. Event type explicit
	Vendor Bill Created	Marks the entry of a new supplier invoice into NetSuite. This is captured from the creation date of the Vendor Bill transaction record, representing the official start of the AP process.
Why it matters Establishes the starting point for all invoice processing cycle time calculations, which is critical for measuring overall process efficiency and tracking invoice aging. Where to get The 'Date Created' field from the Vendor Bill transaction record. Capture Transaction creation event for Vendor Bill. Event type explicit
	Credit Memo Applied	Occurs when a Vendor Credit is applied to a Vendor Bill, reducing the amount owed. This is captured from the application event linking the two transactions.
Why it matters Applying credit memos is an important alternate path to closing an invoice, and tracking it helps to understand how write-offs and returns affect the overall AP process. Where to get The application date recorded when a Vendor Credit transaction is applied to the Vendor Bill. This is visible in the bill's related records. Capture Timestamp of the application of a Vendor Credit to a Vendor Bill. Event type explicit
	Discrepancy Identified	This activity occurs when a mismatch is found between the invoice, purchase order, or item receipt, preventing further processing. It is often inferred by a user setting a specific 'Approval Status' like 'On Hold' or 'Pending Clarification'.
Why it matters Highlighting this activity helps pinpoint sources of friction and rework in the validation process, which are major causes of payment delays. Where to get Inferred from a change in the Vendor Bill's 'Approval Status' field to a non-standard pending state (e.g., 'On Hold'). May require custom status configuration. Capture Track changes to 'Approval Status' field indicating a hold or query. Event type inferred
	Early Payment Captured	A calculated event that occurs if the payment for an invoice is executed before the early payment discount date. This is derived by comparing the payment date to the discount terms on the Vendor Bill.
Why it matters Directly measures the success of the AP team in capturing discounts, which has a direct impact on the company's profitability and cash management strategy. Where to get This is a calculated event. It requires comparing the 'Date' from the Vendor Payment transaction against the discount date derived from the 'Terms' field on the Vendor Bill. Capture Compare Vendor Payment 'Date' with discount date derived from Vendor Bill 'Terms'. Event type calculated
	Invoice Re-Routed For Approval	Occurs when an invoice is sent back to an earlier stage or a different approver due to a change or rejection. This is inferred when the 'Approval Status' changes from 'Pending Approval' back to another state and then to 'Pending Approval' again.
Why it matters Identifying re-routing events is key to understanding rework loops within the approval process, which significantly extend cycle times. Where to get Inferred from System Notes by detecting sequences where 'Approval Status' changes multiple times to 'Pending Approval' for the same bill. Capture Analyze sequences of 'Approval Status' changes in System Notes. Event type inferred
	Invoice Rejected	Represents the formal rejection of a Vendor Bill during the approval process, requiring correction and resubmission. This event is captured when the 'Approval Status' is updated to 'Rejected'.
Why it matters Tracking rejections is crucial for rework analysis, helping to identify common reasons for failure (e.g., incorrect coding, policy violations) and improve first-pass approval rates. Where to get Timestamp of the 'Approval Status' field on the Vendor Bill record changing to 'Rejected', as recorded in System Notes. Capture Workflow log or System Notes timestamp for 'Approval Status' = 'Rejected'. Event type explicit
	Late Payment Identified	A calculated event triggered when an invoice is paid after its due date. This is determined by comparing the payment execution date with the due date specified on the Vendor Bill.
Why it matters This activity is critical for monitoring payment compliance and managing supplier relationships. A high frequency of late payments can damage vendor trust. Where to get Calculated by comparing the 'Date' from the Vendor Payment transaction to the 'Due Date' field on the Vendor Bill record. If Payment Date > Due Date, this event occurs. Capture Compare Vendor Payment 'Date' with Vendor Bill 'Due Date'. Event type calculated
	Payment Scheduled	This activity indicates that a Vendor Bill has been selected for payment and included in a payment batch. It is captured when a Vendor Payment transaction is created with a future payment date.
Why it matters This event provides visibility into the payment pipeline, helping with cash flow forecasting and managing payment timing to capture early payment discounts. Where to get Creation date of the Vendor Payment transaction record that is applied to the Vendor Bill. Capture Creation timestamp of the associated Vendor Payment transaction record. Event type explicit
	PO Matching Attempted	Represents the action of associating a Vendor Bill with a corresponding Purchase Order for validation. This is inferred when a PO is first linked on the Vendor Bill record's 'Items' or 'Expenses' sublist before approval.
Why it matters Tracking this step helps analyze the efficiency of the matching process and identify delays before invoices are even submitted for approval. Where to get Inferred from the System Notes log showing the initial population of the 'Created From' field or line-item level PO references on the Vendor Bill. Capture Detect population of PO reference field in System Notes. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Navigate to Saved Search Creation: Log in to NetSuite with a role that has permissions to create/edit Saved Searches. Navigate to Reports > Saved Searches > All Saved Searches > New. Select Transaction from the list of search types.
Define Search Criteria: On the Criteria tab, define the primary filters to select the relevant invoices (Vendor Bills).
- Set Type to is Vendor Bill.
- Set Main Line to is True. This focuses the search on header-level information.
- Add a Date Created filter to limit the data extraction to a specific period (e.g., is within last 6 months).
Define Results Columns for Common Attributes: Go to the Results tab. Add the following fields which will be common to all events for an invoice.
- Document Number (Custom Label: Invoice)
- Entity (Custom Label: VendorName)
- Amount (Custom Label: InvoiceAmount)
- Due Date (Custom Label: InvoiceDueDate)
- Created By (Custom Label: ProcessorUser)
- Formula (Text): Set formula to '{source}' (Custom Label: SourceSystem). This will populate the column with 'NetSuite'.
- Formula (Date/Time): Set formula to {today} (Custom Label: LastDataUpdate).
Define Results Columns for Activities: This is the most critical step. For each of the 14 activities, you will add two formula columns: one for the ActivityName and one for the EventTime. This creates a wide dataset that must be transformed later.
- Example for 'Invoice Approved':
  - Add Formula (Text) with the value CASE WHEN {approvalstatus} = 'Approved' THEN 'Invoice Approved' ELSE NULL END. Set a custom label like ActivityName_Approved.
  - Add Formula (Date/Time) with the value CASE WHEN {approvalstatus} = 'Approved' THEN {systemnotes.date} WHERE systemnotes.field = 'Approval Status' and systemnotes.newvalue = 'Approved' ELSE NULL END. Set a custom label like EventTime_Approved. *Note: Accessing the exact timestamp for a status change often requires drilling into system notes, which can be complex. A simpler but less precise alternative is using a dedicated {approveddate}` field if available.*
- Repeat this process for all 14 required activities, using the logic provided in the query section below.
Configure Search Settings: Under the Audience subtab, ensure the search is accessible to the appropriate roles or users who will run the export. Give the search a descriptive name like ProcessMind AP Extraction.
Save and Run the Search: Click Save & Run to execute the search. Review the results in the NetSuite UI to ensure data is appearing as expected in the wide format.
Export Data to CSV: From the search results page, click the Export icon (CSV, Excel, or PDF) and choose CSV.
Transform Data (Unpivot): The exported CSV will have one row per invoice and many columns for activities and timestamps (e.g., ActivityName_Created, EventTime_Created, ActivityName_Approved, EventTime_Approved). This 'wide' format must be transformed into a 'long' format event log.
- Open the CSV in a tool like Microsoft Excel (using Power Query), Python (using Pandas), or another data manipulation tool.
- Use the 'Unpivot' function. Select all the common attribute columns (Invoice, VendorName, etc.) and unpivot all the ActivityName_... and EventTime_... columns.
- This transformation will create the required event log structure, with one row per activity.
Clean and Format the Final File: After unpivoting, you will have columns representing the attribute and value. Rename these columns to ActivityName and EventTime. Filter out any rows where the ActivityName or EventTime is NULL. Ensure the final column headers match the ProcessMind schema (Invoice, ActivityName, EventTime, etc.).
Save Final Event Log: Save the transformed data as a new CSV file, ready for upload to ProcessMind.

Configuration

Prerequisites: A user role with Create or Full level permissions for Saved Searches. The role must also have view access to Transaction records (specifically Vendor Bills, Vendor Payments, Vendor Credits) and related entity records (Vendor, Employee).
Search Type: Always start with a Transaction search type for this process.
Date Range Filtering: It is critical to apply a date range filter (e.g., Date Created) to limit the dataset size. Start with a smaller range (e.g., 3-6 months) to test the search performance and validate the results before running it for a larger historical period.
Main Line Filter: Using Criteria > Main Line > is True is generally recommended to get one result per transaction. However, for some activities like 'PO Matching Attempted' which occurs at the line level, you may need a separate search or a more complex formula that doesn't use this filter.
Custom Fields: Your NetSuite instance may use custom fields to manage the AP process (e.g., a custom AP Process Status field instead of the standard Approval Status). You must identify these fields and substitute them in the search criteria and result formulas.
Performance Considerations: A single saved search with formulas for all 14 activities can be slow or may time out in NetSuite. Consider breaking the logic into multiple saved searches (e.g., one for Bill creation/approval, one for Payments) and joining the exported files before the unpivot step.

a Sample Query config

# NetSuite Saved Search Configuration for Accounts Payable Event Log Extraction
# This configuration describes the settings for a single, comprehensive Saved Search.
# The output is a 'wide' file that must be unpivoted into an event log.

SearchType: "Transaction"

# ====================================================================
# CRITERIA
# ====================================================================
# Define the filters to select the base records
# --------------------------------------------------------------------

Filters: [
  {
    "field": "Type",
    "operator": "is",
    "value": "Vendor Bill"
  },
  {
    "field": "Main Line",
    "operator": "is",
    "value": "True"
  },
  {
    "field": "Date Created",
    "operator": "within",
    "value": "[Specify Date Range, e.g., thisfiscalquarter]"
  }
]

# ====================================================================
# RESULTS
# ====================================================================
# Define the columns for the output. The final CSV will have these columns.
# Common attributes are listed first, followed by pairs of columns for each activity.
# --------------------------------------------------------------------

Columns: [
  // --- Common Attributes ---
  { "field": "Document Number", "label": "Invoice" },
  { "field": "Entity", "label": "VendorName" },
  { "field": "Amount", "label": "InvoiceAmount" },
  { "field": "Applied To Transaction", "label": "PurchaseOrderNumber" },
  { "field": "Created By", "label": "ProcessorUser" },
  { "field": "Next Approver", "label": "Approver" },
  { "field": "Due Date", "label": "InvoiceDueDate" },
  { "formula": "'NetSuite'", "type": "Text", "label": "SourceSystem" },
  { "formula": "{today}", "type": "Date/Time", "label": "LastDataUpdate" },

  // --- Activity 1: Vendor Bill Created ---
  { "formula": "'Vendor Bill Created'", "type": "Text", "label": "ActivityName_Created" },
  { "field": "Date Created", "label": "EventTime_Created" },

  // --- Activity 2: PO Matching Attempted ---
  { "formula": "CASE WHEN {appliedtotransaction} IS NOT NULL THEN 'PO Matching Attempted' ELSE NULL END", "type": "Text", "label": "ActivityName_POMatched" },
  { "formula": "CASE WHEN {appliedtotransaction} IS NOT NULL THEN {datecreated} ELSE NULL END", "type": "Date/Time", "label": "EventTime_POMatched" },

  // --- Activity 3: Discrepancy Identified ---
  { "formula": "CASE WHEN {approvalstatus} = 'Rejected' THEN 'Discrepancy Identified' ELSE NULL END", "type": "Text", "label": "ActivityName_Discrepancy" },
  { "formula": "(SELECT MIN(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Rejected')", "type": "Date/Time", "label": "EventTime_Discrepancy" },

  // --- Activity 4: Approval Submitted ---
  { "formula": "CASE WHEN {approvalstatus} != 'Pending Approval' AND {approvalstatus} != 'Approved' THEN NULL ELSE 'Approval Submitted' END", "type": "Text", "label": "ActivityName_Submitted" },
  { "formula": "(SELECT MIN(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Pending Approval')", "type": "Date/Time", "label": "EventTime_Submitted" },

  // --- Activity 5: Invoice Re-Routed For Approval ---
  { "formula": "CASE WHEN (SELECT COUNT(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Pending Approval') > 1 THEN 'Invoice Re-Routed For Approval' ELSE NULL END", "type": "Text", "label": "ActivityName_ReRouted" },
  { "formula": "(SELECT MAX(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Pending Approval' AND systemnotes.date < (SELECT MAX(sub_notes.date) FROM systemnote sub_notes WHERE sub_notes.recordid = {internalid} AND sub_notes.field = 'TRANSACTION.APPROVALSTATUS' AND sub_notes.newvalue = 'Pending Approval'))", "type": "Date/Time", "label": "EventTime_ReRouted" },

  // --- Activity 6: Invoice Approved ---
  { "formula": "CASE WHEN {approvalstatus} = 'Approved' THEN 'Invoice Approved' ELSE NULL END", "type": "Text", "label": "ActivityName_Approved" },
  { "formula": "(SELECT MIN(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Approved')", "type": "Date/Time", "label": "EventTime_Approved" },

  // --- Activity 7: Invoice Rejected ---
  { "formula": "CASE WHEN {approvalstatus} = 'Rejected' THEN 'Invoice Rejected' ELSE NULL END", "type": "Text", "label": "ActivityName_Rejected" },
  { "formula": "(SELECT MAX(systemnotes.date) FROM systemnote WHERE systemnote.recordid = {internalid} AND systemnote.field = 'TRANSACTION.APPROVALSTATUS' AND systemnote.newvalue = 'Rejected')", "type": "Date/Time", "label": "EventTime_Rejected" },

  // --- Activity 8: Invoice Posted To GL ---
  { "formula": "CASE WHEN {posting} = 'Yes' THEN 'Invoice Posted To GL' ELSE NULL END", "type": "Text", "label": "ActivityName_Posted" },
  { "field": "Date", "label": "EventTime_Posted" },

  // --- Activity 9: Payment Scheduled ---
  { "formula": "CASE WHEN {payingtransaction.type} = 'Vendor Payment' AND {payingtransaction.trandate} > {today} THEN 'Payment Scheduled' ELSE NULL END", "type": "Text", "label": "ActivityName_Scheduled" },
  { "formula": "CASE WHEN {payingtransaction.type} = 'Vendor Payment' AND {payingtransaction.trandate} > {today} THEN {payingtransaction.datecreated} ELSE NULL END", "type": "Date/Time", "label": "EventTime_Scheduled" },

  // --- Activity 10: Payment Executed ---
  { "formula": "CASE WHEN {status} = 'Paid in Full' OR {status} = 'Pending Approval' THEN 'Payment Executed' ELSE NULL END", "type": "Text", "label": "ActivityName_Executed" },
  { "field": "Paying Transaction Date", "label": "EventTime_Executed" },

  // --- Activity 11: Invoice Closed ---
  { "formula": "CASE WHEN {status} = 'Paid in Full' THEN 'Invoice Closed' ELSE NULL END", "type": "Text", "label": "ActivityName_Closed" },
  { "field": "Paying Transaction Date", "label": "EventTime_Closed" },

  // --- Activity 12: Early Payment Captured ---
  { "formula": "CASE WHEN {payingtransaction.trandate} < {discountdate} AND {discountamount} IS NOT NULL THEN 'Early Payment Captured' ELSE NULL END", "type": "Text", "label": "ActivityName_EarlyPay" },
  { "formula": "CASE WHEN {payingtransaction.trandate} < {discountdate} AND {discountamount} IS NOT NULL THEN {payingtransaction.trandate} ELSE NULL END", "type": "Date/Time", "label": "EventTime_EarlyPay" },

  // --- Activity 13: Late Payment Identified ---
  { "formula": "CASE WHEN {daysoverdue} > 0 AND {status} = 'Paid in Full' THEN 'Late Payment Identified' ELSE NULL END", "type": "Text", "label": "ActivityName_LatePay" },
  { "formula": "CASE WHEN {daysoverdue} > 0 AND {status} = 'Paid in Full' THEN {payingtransaction.trandate} ELSE NULL END", "type": "Date/Time", "label": "EventTime_LatePay" },

  // --- Activity 14: Credit Memo Applied ---
  { "formula": "CASE WHEN {payingtransaction.type} = 'Vendor Credit' THEN 'Credit Memo Applied' ELSE NULL END", "type": "Text", "label": "ActivityName_CreditMemo" },
  { "formula": "CASE WHEN {payingtransaction.type} = 'Vendor Credit' THEN {payingtransaction.trandate} ELSE NULL END", "type": "Date/Time", "label": "EventTime_CreditMemo" }
]

Steps

Prerequisites: Ensure your NetSuite account has the SuiteAnalytics Connect module enabled. Confirm you have a user role with the 'SuiteAnalytics Connect' permission and access to the required records (Vendor Bills, Vendor Payments, Purchase Orders, System Notes).
Install and Configure Driver: Download the appropriate NetSuite ODBC (or JDBC) driver for your operating system from the NetSuite UI under Home > Set Preferences > Analytics Driver. Follow the installation instructions and configure a new Data Source Name (DSN) pointing to the NetSuite service.
Establish Connection: Open your preferred SQL client (e.g., DBeaver, Power BI, Tableau). Create a new connection using the configured driver/DSN. You will need your NetSuite Account ID, Role ID, and user credentials for authentication.
Prepare the Query: Copy the complete SuiteQL query provided in the 'query' section below into your SQL client's query editor.
Customize Placeholders: Modify the placeholder values in the query's WHERE clauses. At a minimum, you must set the [START_DATE] and [END_DATE] to define your analysis period. Also, adjust the [SUBSIDIARY_ID] if you are using a NetSuite OneWorld account.
Adapt Custom Logic: Review the sections marked with comments like -- CUSTOMIZABLE. These parts of the query infer activities like 'Discrepancy Identified' based on specific status values. You must update these values to match your organization's specific NetSuite configuration and business process.
Execute the Query: Run the customized query against the NetSuite database. Depending on the date range and data volume, this may take several minutes to complete.
Export the Results: Once the query finishes, export the result set from your SQL client to a CSV file.
Finalize for Upload: Ensure the exported CSV file is UTF-8 encoded. Verify that the column headers match the required and recommended attributes for ProcessMind (e.g., Invoice, ActivityName, EventTime). The file is now ready for upload.

Configuration

SuiteAnalytics Connect: This NetSuite add-on module is required to use this extraction method. It provides read-only access to a replica of your NetSuite data.
User Role Permissions: The NetSuite role used for the connection must have the 'SuiteAnalytics Connect' permission enabled. It also requires view-level access to the following records: Transactions (Vendor Bill, Vendor Payment, Vendor Credit), Entities (Vendor, Employee), and System Notes.
Date Range Filtering: It is critical to filter the query by a specific date range (e.g., trandate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD') to ensure manageable execution times. Start with a smaller range, such as 3-6 months, before running a full extraction.
Subsidiary Filtering: For NetSuite OneWorld accounts, always include a WHERE clause to filter by subsidiary ID to isolate data for a specific legal entity and improve query performance.
Custom Approval Workflows: The provided query assumes the use of the standard 'Approval Status' field on Vendor Bills. If your organization uses a custom SuiteFlow or script with custom fields/records for approvals, the query sections referencing SystemNote for the approvalstatus field will need significant modification.

a Sample Query sql

-- This query extracts an event log for the Accounts Payable Invoice Processing from NetSuite.
-- It uses a UNION ALL approach to gather 14 distinct activities into a single file.

WITH VendorBillsInScope AS (
    -- CTE to select the primary Vendor Bills to analyze.
    -- Filtering here is critical for performance.
    SELECT
        Transaction.ID AS BillID,
        Transaction.tranid AS Invoice,
        Transaction.trandate AS BillDate,
        Transaction.duedate AS InvoiceDueDate,
        Transaction.foreignTotal AS InvoiceAmount,
        Transaction.entity AS VendorID,
        Transaction.createdBy AS ProcessorUserID,
        Transaction.status AS BillStatus,
        Transaction.approvalStatus AS ApprovalStatus,
        Transaction.discountDate AS DiscountDate
    FROM
        Transaction
    WHERE
        Transaction.type = 'VendBill'
        -- CRITICAL: Define the date range for the extraction
        AND Transaction.trandate BETWEEN TO_DATE('[START_DATE]', 'YYYY-MM-DD') AND TO_DATE('[END_DATE]', 'YYYY-MM-DD')
        -- OPTIONAL: Filter by a specific subsidiary for NetSuite OneWorld accounts
        -- AND Transaction.subsidiary = [SUBSIDIARY_ID]
),

-- Base information for joins, including vendor, processor, and PO data.
BaseInfo AS (
    SELECT
        vb.BillID,
        vb.Invoice,
        vb.InvoiceAmount,
        vb.InvoiceDueDate,
        vb.DiscountDate,
        vendor.companyName AS VendorName,
        processor.entityId AS ProcessorUser,
        LISTAGG(DISTINCT po.tranid, ', ') WITHIN GROUP (ORDER BY po.tranid) AS PurchaseOrderNumber
    FROM
        VendorBillsInScope vb
    LEFT JOIN
        Entity vendor ON vb.VendorID = vendor.ID
    LEFT JOIN
        Employee processor ON vb.ProcessorUserID = processor.ID
    LEFT JOIN
        TransactionLine tl ON vb.BillID = tl.Transaction
    LEFT JOIN
        Transaction po ON tl.purchaseOrder = po.ID AND po.type = 'PurchOrd'
    GROUP BY
        vb.BillID, vb.Invoice, vb.InvoiceAmount, vb.InvoiceDueDate, vb.DiscountDate, vendor.companyName, processor.entityId
),

-- System Notes are used to track status changes for approvals, rejections, etc.
Notes AS (
    SELECT
        sn.transactionId AS BillID,
        sn.date AS NoteDate,
        sn.field AS NoteField,
        sn.newValue AS NoteNewValue,
        sn.oldValue AS NoteOldValue,
        approver.entityId AS Approver
    FROM
        SystemNote sn
    JOIN
        VendorBillsInScope vb ON sn.transactionId = vb.BillID
    LEFT JOIN
        Employee approver ON sn.author = approver.ID
    WHERE
        -- Filtering to relevant fields improves performance
        sn.field IN ('approvalstatus', 'status')
)

-- 1. Vendor Bill Created
SELECT
    bi.Invoice AS "Invoice",
    'Vendor Bill Created' AS "ActivityName",
    vb.BillDate AS "EventTime",
    'NetSuite' AS "SourceSystem",
    BUILTIN.CURRRENT_TIMESTAMP() AS "LastDataUpdate",
    bi.VendorName AS "VendorName",
    bi.InvoiceAmount AS "InvoiceAmount",
    bi.PurchaseOrderNumber AS "PurchaseOrderNumber",
    bi.ProcessorUser AS "ProcessorUser",
    NULL AS "Approver",
    bi.InvoiceDueDate AS "InvoiceDueDate"
FROM
    VendorBillsInScope vb
JOIN
    BaseInfo bi ON vb.BillID = bi.BillID

UNION ALL

-- 2. PO Matching Attempted (Inferred from PO link on creation date)
SELECT
    bi.Invoice,
    'PO Matching Attempted',
    vb.BillDate, -- Occurs at the same time as creation
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    NULL,
    bi.InvoiceDueDate
FROM
    VendorBillsInScope vb
JOIN
    BaseInfo bi ON vb.BillID = bi.BillID
WHERE
    bi.PurchaseOrderNumber IS NOT NULL

UNION ALL

-- 3. Discrepancy Identified (CUSTOMIZABLE: Inferred from status change to 'On Hold')
SELECT
    bi.Invoice,
    'Discrepancy Identified',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver, -- User who placed it on hold
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' 
    -- CUSTOMIZABLE: Update 'On Hold' to match the status your company uses for discrepancies.
    AND n.NoteNewValue = 'On Hold'

UNION ALL

-- 4. Approval Submitted
SELECT
    bi.Invoice,
    'Approval Submitted',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver, -- User who submitted for approval
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' AND n.NoteNewValue = 'Pending Approval'

UNION ALL

-- 5. Invoice Re-Routed For Approval (Inferred by status returning to Pending Approval)
SELECT
    bi.Invoice,
    'Invoice Re-Routed For Approval',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver,
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' 
    AND n.NoteNewValue = 'Pending Approval'
    AND n.NoteOldValue IN ('Rejected', 'On Hold') -- Capture re-submission after rejection/hold

UNION ALL

-- 6. Invoice Approved
SELECT
    bi.Invoice,
    'Invoice Approved',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver,
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' AND n.NoteNewValue = 'Approved'

UNION ALL

-- 7. Invoice Rejected
SELECT
    bi.Invoice,
    'Invoice Rejected',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver,
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' AND n.NoteNewValue = 'Rejected'

UNION ALL

-- 8. Invoice Posted To GL (Happens upon approval)
SELECT
    bi.Invoice,
    'Invoice Posted To GL',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    n.Approver,
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'approvalstatus' AND n.NoteNewValue = 'Approved'

UNION ALL

-- 9. Payment Scheduled & 10. Payment Executed & 12. Early & 13. Late Payments
WITH Payments AS (
    SELECT
        tp.previousDoc AS BillID,
        vp.trandate AS PaymentExecutionDate,
        vp.createdDate AS PaymentScheduledDate
    FROM
        TransactionPayment tp
    JOIN
        Transaction vp ON tp.nextDoc = vp.ID AND vp.type = 'VendPymt'
    WHERE
        tp.previousDoc IN (SELECT BillID FROM VendorBillsInScope)
)
SELECT p.Invoice, p.ActivityName, p.EventTime, p.SourceSystem, p.LastDataUpdate, p.VendorName, p.InvoiceAmount, p.PurchaseOrderNumber, p.ProcessorUser, p.Approver, p.InvoiceDueDate FROM (
    SELECT bi.Invoice, 'Payment Scheduled' AS ActivityName, p.PaymentScheduledDate AS EventTime, 'NetSuite' AS SourceSystem, BUILTIN.CURRRENT_TIMESTAMP() AS LastDataUpdate, bi.VendorName, bi.InvoiceAmount, bi.PurchaseOrderNumber, bi.ProcessorUser, NULL AS Approver, bi.InvoiceDueDate FROM Payments p JOIN BaseInfo bi ON p.BillID = bi.BillID
    UNION ALL
    SELECT bi.Invoice, 'Payment Executed', p.PaymentExecutionDate, 'NetSuite', BUILTIN.CURRRENT_TIMESTAMP(), bi.VendorName, bi.InvoiceAmount, bi.PurchaseOrderNumber, bi.ProcessorUser, NULL, bi.InvoiceDueDate FROM Payments p JOIN BaseInfo bi ON p.BillID = bi.BillID
    UNION ALL
    SELECT bi.Invoice, 'Early Payment Captured', p.PaymentExecutionDate, 'NetSuite', BUILTIN.CURRRENT_TIMESTAMP(), bi.VendorName, bi.InvoiceAmount, bi.PurchaseOrderNumber, bi.ProcessorUser, NULL, bi.InvoiceDueDate FROM Payments p JOIN BaseInfo bi ON p.BillID = bi.BillID WHERE p.PaymentExecutionDate <= bi.DiscountDate
    UNION ALL
    SELECT bi.Invoice, 'Late Payment Identified', p.PaymentExecutionDate, 'NetSuite', BUILTIN.CURRRENT_TIMESTAMP(), bi.VendorName, bi.InvoiceAmount, bi.PurchaseOrderNumber, bi.ProcessorUser, NULL, bi.InvoiceDueDate FROM Payments p JOIN BaseInfo bi ON p.BillID = bi.BillID WHERE p.PaymentExecutionDate > bi.InvoiceDueDate
) p

UNION ALL

-- 11. Invoice Closed (Paid in Full)
SELECT
    bi.Invoice,
    'Invoice Closed',
    n.NoteDate,
    'NetSuite',
    BUILTIN.CURRRENT_TIMESTAMP(),
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    NULL,
    bi.InvoiceDueDate
FROM
    Notes n
JOIN
    BaseInfo bi ON n.BillID = bi.BillID
WHERE
    n.NoteField = 'status' AND n.NoteNewValue = 'Paid In Full'

UNION ALL

-- 14. Credit Memo Applied
SELECT 
    bi.Invoice,
    'Credit Memo Applied',
    vc.trandate AS EventTime,
    'NetSuite' AS SourceSystem,
    BUILTIN.CURRRENT_TIMESTAMP() AS LastDataUpdate,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.PurchaseOrderNumber,
    bi.ProcessorUser,
    NULL AS Approver,
    bi.InvoiceDueDate
FROM 
    TransactionLink tl
JOIN 
    Transaction vc ON tl.previousDoc = vc.ID AND vc.type = 'VendCred'
JOIN 
    BaseInfo bi ON tl.nextDoc = bi.BillID;

Steps

Prerequisites: Ensure you have Python 3.6+ installed along with the requests, requests-oauthlib, and pandas libraries. You can install them using pip: pip install requests requests-oauthlib pandas.
Enable NetSuite Features: In NetSuite, an administrator must enable SuiteTalk Web Services. Navigate to Setup > Company > Enable Features > SuiteCloud. Check the boxes for SUITEANALYTICS WORKBOOK and SUITETALK (WEB SERVICES).
Create Integration Record & Access Token: Navigate to Setup > Integration > Manage Integrations > New. Create a new integration record, enabling TOKEN-BASED AUTHENTICATION and TBA: AUTHORIZATION FLOW. Note the CONSUMER KEY and CONSUMER SECRET. Then, navigate to Setup > Users/Roles > Access Tokens > New to create an access token for the desired user/role, linking it to the integration record. Note the TOKEN ID and TOKEN SECRET.
Configure the Python Script: Copy the provided Python script into a file (e.g., extract_netsuite_ap.py). Fill in the placeholder values in the NETSUITE_CREDENTIALS dictionary with your Account ID, Consumer Key/Secret, and Token ID/Secret.
Set Extraction Parameters: Modify the START_DATE and END_DATE variables in the script to define the desired time window for the extraction. It's recommended to start with a smaller period (e.g., one month) for initial testing.
Execute the Script: Run the script from your terminal using the command: python extract_netsuite_ap.py. The script will authenticate with NetSuite, execute several SuiteQL queries to gather data, and process it.
Monitor Script Execution: The script will print progress updates to the console, indicating which data set it is currently fetching (Vendor Bills, System Notes, Payments, etc.). Be patient, as this can take several minutes for large data volumes.
Review the Output: Upon successful completion, the script will generate a CSV file named netsuite_ap_event_log.csv in the same directory. This file contains the structured event log.
Data Transformation Logic: The script automatically transforms the raw NetSuite data into the required event log format. It identifies activities like 'Invoice Approved' by parsing system notes for changes to the 'Approval Status' field and calculates derived events like 'Late Payment Identified' by comparing payment dates to due dates.
Prepare for Upload: The generated CSV file is formatted for direct upload into process mining tools. Ensure the column headers (Invoice, ActivityName, EventTime, etc.) match the expected schema in your target application.

Configuration

Authentication: The script uses Token-Based Authentication (TBA) with OAuth 1.0, which is NetSuite's standard for server-to-server integrations. You must provide the Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret.
API Endpoint: The script constructs the REST API endpoint URL automatically using your Account ID. Ensure your NetSuite account is configured to allow REST Web Services requests.
Date Range: The extraction is controlled by START_DATE and END_DATE variables. It's crucial to filter the primary query on an indexed date field like lastmodifieddate or datecreated to ensure efficient data retrieval and avoid timeouts.
Data Filters: The SuiteQL queries can be customized to filter for specific subsidiaries, vendors, or document types by adding WHERE clauses (e.g., WHERE subsidiary = 'YourSubsidiaryID').
Performance Considerations: NetSuite imposes API governance limits. The script uses pagination (LIMIT and OFFSET) to handle large datasets gracefully. For millions of records, consider running the extraction in smaller, sequential date chunks.
Prerequisites: The role associated with the access token requires view permissions for the following record types: Vendor Bill, Vendor Payment, Vendor Credit, Purchase Order, System Note, Vendor, and Employee.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your NetSuite Token-Based Authentication credentials
NETSUITE_CREDENTIALS = {
    class="hl-string">"account": class="hl-string">"[Your_NetSuite_Account_ID]", class=class="hl-string">"hl-comment"># e.g., 1234567_SB1
    class="hl-string">"consumer_key": class="hl-string">"[Your_Consumer_Key]",
    class="hl-string">"consumer_secret": class="hl-string">"[Your_Consumer_Secret]",
    class="hl-string">"token_id": class="hl-string">"[Your_Token_ID]",
    class="hl-string">"token_secret": class="hl-string">"[Your_Token_Secret]"
}

class=class="hl-string">"hl-comment"># Set the date range for the extraction
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-12-31'

class=class="hl-string">"hl-comment"># --- NetSuite API Client ---
def get_netsuite_api_url(account_id):
    class=class="hl-string">"hl-comment"># Replace class="hl-string">'-' with class="hl-string">'_' for sandbox/release preview accounts
    account_id_formatted = account_id.replace(class="hl-string">'-', class="hl-string">'_').lower()
    return fclass="hl-string">"https://{account_id_formatted}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql"

def execute_suiteql(query):
    class="hl-string">"""Executes a SuiteQL query against the NetSuite REST API with pagination."""
    url = get_netsuite_api_url(NETSUITE_CREDENTIALS[class="hl-string">"account"])
    auth = OAuth1(
        client_key=NETSUITE_CREDENTIALS[class="hl-string">"consumer_key"],
        client_secret=NETSUITE_CREDENTIALS[class="hl-string">"consumer_secret"],
        resource_owner_key=NETSUITE_CREDENTIALS[class="hl-string">"token_id"],
        resource_owner_secret=NETSUITE_CREDENTIALS[class="hl-string">"token_secret"],
        realm=NETSUITE_CREDENTIALS[class="hl-string">"account"]
    )
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'transient'
    }
    
    all_results = []
    offset = 0
    limit = 1000 class=class="hl-string">"hl-comment"># Max limit per page

    while True:
        paginated_query = fclass="hl-string">"{query} LIMIT {limit} OFFSET {offset}"
        payload = json.dumps({class="hl-string">'q': paginated_query})
        
        try:
            response = requests.post(url, headers=headers, data=payload, auth=auth)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_results.extend(items)
            
            if not data.get(class="hl-string">'hasMore', False):
                break
            
            offset += limit
            print(fclass="hl-string">"Fetched {len(all_results)} records...")
            time.sleep(1) class=class="hl-string">"hl-comment"># Be respectful to the API

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"API Request Error: {e}")
            if e.response is not None:
                print(fclass="hl-string">"Response Body: {e.response.text}")
            break

    return pd.DataFrame(all_results)

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    print(class="hl-string">"Starting NetSuite AP Data Extraction...")
    
    class=class="hl-string">"hl-comment"># 1. Fetch Vendor Bills
    print(class="hl-string">"\n--- Step 1: Fetching Vendor Bills ---")
    vendor_bill_query = fclass="hl-string">"""
        SELECT
            tran.ID as TransactionId,
            tran.tranId as Invoice,
            tran.entity as VendorId,
            BUILTIN.DF(tran.entity) as VendorName,
            tran.tranDate as TransactionDate,
            tran.dueDate as InvoiceDueDate,
            tran.createdDate as CreatedDate,
            tran.lastModifiedDate as LastDataUpdate,
            tran.total as InvoiceAmount,
            BUILTIN.DF(tran.createdBy) as ProcessorUser,
            BUILTIN.DF(tran.approvalStatus) as ApprovalStatusText,
            tran.approvalStatus as ApprovalStatusId,
            tran.status as StatusId,
            tran.terms as TermsId,
            BUILTIN.DF(tran.terms) as TermsName
        FROM
            Transaction AS tran
        WHERE
            tran.type = 'VendBill'
            AND tran.lastModifiedDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    """
    df_bills = execute_suiteql(vendor_bill_query)
    if df_bills.empty:
        print(class="hl-string">"No vendor bills found for the specified date range. Exiting.")
        return
    
    bill_ids = class="hl-string">','.join(df_bills[class="hl-string">'TransactionId'].astype(str).tolist())
    print(fclass="hl-string">"Found {len(df_bills)} vendor bills.")

    class=class="hl-string">"hl-comment"># 2. Fetch System Notes for status changes
    print(class="hl-string">"\n--- Step 2: Fetching System Notes for status changes ---")
    system_notes_query = fclass="hl-string">"""
        SELECT
            transactionId,
            field,
            newValue,
            oldValue,
            date as EventTime
        FROM
            SystemNote
        WHERE
            transactionId IN ({bill_ids})
            AND field IN ('transaction.approvalstatus', 'transaction.status')
    """
    df_notes = execute_suiteql(system_notes_query)
    print(fclass="hl-string">"Found {len(df_notes)} relevant system notes.")

    class=class="hl-string">"hl-comment"># 3. Fetch PO Links
    print(class="hl-string">"\n--- Step 3: Fetching Purchase Order Links ---")
    po_link_query = fclass="hl-string">"""
        SELECT DISTINCT
            vb.ID as BillId,
            po.tranId as PurchaseOrderNumber
        FROM
            Transaction AS vb
            JOIN TransactionLine AS vbl ON vb.ID = vbl.Transaction
            JOIN Transaction AS po ON vbl.purchaseOrder = po.ID
        WHERE
            vb.ID IN ({bill_ids})
    """
    df_po = execute_suiteql(po_link_query)
    df_po.rename(columns={class="hl-string">'BillId': class="hl-string">'TransactionId'}, inplace=True)
    print(fclass="hl-string">"Found {len(df_po)} PO links.")

    class=class="hl-string">"hl-comment"># 4. Fetch Payments
    print(class="hl-string">"\n--- Step 4: Fetching Payment Information ---")
    payment_query = fclass="hl-string">"""
        SELECT
            link.prevtranid AS BillId,
            pymt.tranDate AS PaymentDate,
            pymt.ID AS PaymentId
        FROM
            NextTransactionLineLink AS link
            JOIN Transaction AS pymt ON link.nexttranid = pymt.ID
        WHERE
            link.prevtranid IN ({bill_ids})
            AND pymt.type = 'VendPymt'
    """
    df_payments = execute_suiteql(payment_query)
    df_payments.rename(columns={class="hl-string">'BillId': class="hl-string">'TransactionId'}, inplace=True)
    print(fclass="hl-string">"Found {len(df_payments)} payment applications.")

    class=class="hl-string">"hl-comment"># 5. Fetch Credit Memo Applications
    print(class="hl-string">"\n--- Step 5: Fetching Credit Memo Applications ---")
    credit_query = fclass="hl-string">"""
        SELECT
            link.prevtranid AS BillId,
            credit.tranDate AS CreditApplyDate
        FROM
            NextTransactionLineLink AS link
            JOIN Transaction AS credit ON link.nexttranid = credit.ID
        WHERE
            link.prevtranid IN ({bill_ids})
            AND credit.type = 'VendCred'
    """
    df_credits = execute_suiteql(credit_query)
    df_credits.rename(columns={class="hl-string">'BillId': class="hl-string">'TransactionId'}, inplace=True)
    print(fclass="hl-string">"Found {len(df_credits)} credit memo applications.")

    class=class="hl-string">"hl-comment"># --- Event Log Generation ---
    print(class="hl-string">"\n--- Step 6: Generating Event Log ---")
    event_log = []
    source_system = class="hl-string">"NetSuite SuiteTalk API"

    for _, bill in df_bills.iterrows():
        base_event = {
            class="hl-string">'Invoice': bill[class="hl-string">'Invoice'],
            class="hl-string">'SourceSystem': source_system,
            class="hl-string">'LastDataUpdate': bill[class="hl-string">'LastDataUpdate'],
            class="hl-string">'VendorName': bill[class="hl-string">'VendorName'],
            class="hl-string">'InvoiceAmount': bill[class="hl-string">'InvoiceAmount'],
            class="hl-string">'InvoiceDueDate': bill[class="hl-string">'InvoiceDueDate'],
            class="hl-string">'ProcessorUser': bill[class="hl-string">'ProcessorUser'],
            class="hl-string">'Approver': None, class=class="hl-string">"hl-comment"># Placeholder, can be enriched from workflow data if available
        }

        po_info = df_po[df_po[class="hl-string">'TransactionId'] == bill[class="hl-string">'TransactionId']]
        base_event[class="hl-string">'PurchaseOrderNumber'] = po_info[class="hl-string">'PurchaseOrderNumber'].iloc[0] if not po_info.empty else None

        class=class="hl-string">"hl-comment"># Activity: Vendor Bill Created
        event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Vendor Bill Created', class="hl-string">'EventTime': bill[class="hl-string">'CreatedDate']})

        class=class="hl-string">"hl-comment"># Activity: PO Matching Attempted (inferred at creation if PO exists)
        if base_event[class="hl-string">'PurchaseOrderNumber'] is not None:
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'PO Matching Attempted', class="hl-string">'EventTime': bill[class="hl-string">'CreatedDate']})

        class=class="hl-string">"hl-comment"># Activities from System Notes
        notes = df_notes[df_notes[class="hl-string">'transactionId'] == bill[class="hl-string">'TransactionId']].sort_values(class="hl-string">'EventTime')
        for _, note in notes.iterrows():
            activity = None
            if note[class="hl-string">'field'] == class="hl-string">'transaction.approvalstatus':
                if note[class="hl-string">'newValue'] == class="hl-string">'Pending Approval': activity = class="hl-string">'Approval Submitted'
                elif note[class="hl-string">'newValue'] == class="hl-string">'Approved': 
                    activity = class="hl-string">'Invoice Approved'
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Posted To GL', class="hl-string">'EventTime': note[class="hl-string">'EventTime']})
                elif note[class="hl-string">'newValue'] == class="hl-string">'Rejected': activity = class="hl-string">'Invoice Rejected'
                elif note[class="hl-string">'oldValue'] == class="hl-string">'Pending Approval' and note[class="hl-string">'newValue'] != class="hl-string">'Approved': activity = class="hl-string">'Invoice Re-Routed For Approval'
            
            if note[class="hl-string">'field'] == class="hl-string">'transaction.status' and note[class="hl-string">'newValue'] == class="hl-string">'Paid In Full':
                activity = class="hl-string">'Invoice Closed'

            class=class="hl-string">"hl-comment"># Logic for discrepancy is highly custom. We infer it from a move to a non-standard approval status like class="hl-string">'On Hold'.
            class=class="hl-string">"hl-comment"># This requires knowing the specific numeric ID for such a status.
            class=class="hl-string">"hl-comment"># Example: elif note[class="hl-string">'field'] == class="hl-string">'transaction.approvalstatus' and note[class="hl-string">'newValueId'] == class="hl-string">'[Your_On_Hold_Status_ID]': activity = class="hl-string">'Discrepancy Identified'

            if activity:
                event_log.append({**base_event, class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': note[class="hl-string">'EventTime']})

        class=class="hl-string">"hl-comment"># Payment Activities
        payments = df_payments[df_payments[class="hl-string">'TransactionId'] == bill[class="hl-string">'TransactionId']]
        if not payments.empty:
            first_payment = payments.sort_values(class="hl-string">'PaymentDate').iloc[0]
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled', class="hl-string">'EventTime': first_payment[class="hl-string">'PaymentDate']}) class=class="hl-string">"hl-comment"># Simplified: using payment date for schedule
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Payment Executed', class="hl-string">'EventTime': first_payment[class="hl-string">'PaymentDate']})

            class=class="hl-string">"hl-comment"># Derived Payment Events
            payment_date = pd.to_datetime(first_payment[class="hl-string">'PaymentDate'])
            due_date = pd.to_datetime(bill[class="hl-string">'InvoiceDueDate'])
            if due_date and payment_date > due_date:
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Late Payment Identified', class="hl-string">'EventTime': first_payment[class="hl-string">'PaymentDate']})
            
            class=class="hl-string">"hl-comment"># Early payment requires discount date from terms, which needs another join. Simplified for this example.
            class=class="hl-string">"hl-comment"># If you query class="hl-string">'discountdate' from the transaction you can compare it here.
            class=class="hl-string">"hl-comment"># For example: if pd.to_datetime(bill[class="hl-string">'discountdate']) and payment_date <= pd.to_datetime(bill[class="hl-string">'discountdate']):
            class=class="hl-string">"hl-comment">#    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Early Payment Captured', class="hl-string">'EventTime': first_payment[class="hl-string">'PaymentDate']})

        class=class="hl-string">"hl-comment"># Credit Memo Activity
        credits = df_credits[df_credits[class="hl-string">'TransactionId'] == bill[class="hl-string">'TransactionId']]
        for _, credit in credits.iterrows():
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Credit Memo Applied', class="hl-string">'EventTime': credit[class="hl-string">'CreditApplyDate']})

    class=class="hl-string">"hl-comment"># Finalize DataFrame
    df_event_log = pd.DataFrame(event_log)
    df_event_log.sort_values(by=[class="hl-string">'Invoice', class="hl-string">'EventTime'], inplace=True)
    
    class=class="hl-string">"hl-comment"># Save to CSV
    output_filename = class="hl-string">'netsuite_ap_event_log.csv'
    df_event_log.to_csv(output_filename, index=False)
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

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

Steps

Enable SuiteScript: Navigate to Setup > Company > Enable Features. Under the SuiteCloud tab, ensure Server SuiteScript is enabled.
Create the Script File: Copy the complete JavaScript code from the query section below and save it as a .js file (e.g., processmind_ap_extraction.js).
Upload Script to File Cabinet: In NetSuite, go to Documents > Files > SuiteScripts. Create a new folder (e.g., ProcessMind) and upload the .js file you created.
Create the Script Record: Navigate to Customization > Scripting > Scripts > New. Select the file you just uploaded and click Create Script Record. Name the script (e.g., "ProcessMind AP Extraction"), provide an ID (e.g., _pm_ap_extraction), and save.
Define Script Parameters: On the script record page, go to the Parameters subtab. Create the following parameters:
- custscript_pm_start_date: Label Start Date, Type Date.
- custscript_pm_end_date: Label End Date, Type Date.
- custscript_pm_folder_id: Label Target Folder ID, Type Integer Number. This is the internal ID of the File Cabinet folder where the output will be saved.
- custscript_pm_subsidiary_id: Label Subsidiary ID (Optional), Type Integer Number.
Deploy the Script: Click the Deploy Script button on the Script record page. Select Scheduled Script and click Save.
Configure the Deployment: On the Script Deployment page, give the deployment an ID (e.g., _pm_ap_extraction_deployment). Set the Status to Not Scheduled for initial testing.
Set the Schedule: On the Schedule subtab, configure the frequency of the extraction. For an initial run, you can set it to run once manually. For ongoing extraction, set a daily or weekly schedule during off-peak hours.
Set Parameters for Execution: On the Parameters subtab of the deployment, enter the desired Start Date, End Date, and Target Folder ID for the extraction.
Execute and Retrieve the File: To run manually, save the deployment with Status as Not Scheduled, then click Save and Execute. Monitor the script's progress under Customization > Scripting > Script Execution Logs. Once complete, navigate to the target folder in the File Cabinet to download the generated CSV event log.
Format for ProcessMind: Ensure the downloaded CSV file has the correct headers as specified in the script (Invoice, ActivityName, EventTime, etc.). The file is now ready for upload into ProcessMind.

Configuration

Prerequisites: A NetSuite user role with permissions to create and execute SuiteScripts, access Vendor Bill and Vendor Payment records, and view System Notes.
Script Parameters: The script is configurable via deployment parameters, allowing for reuse without code changes:
- Start Date & End Date: Defines the processing period for invoices based on their creation date. For an initial load, a period of 6-12 months is recommended. For subsequent delta loads, this can be set to the last 7 days.
- Target Folder ID: Specifies the internal ID of the NetSuite File Cabinet folder where the output CSV will be stored. This must be created beforehand.
- Subsidiary ID (Optional): In a OneWorld account, this filters the extraction to a specific subsidiary. Leave blank to run for all subsidiaries the user has access to.
Performance Considerations: For environments with high transaction volumes (over 50,000 invoices per month), a Map/Reduce script may be more suitable to avoid exceeding governance limits. Schedule the script to run during non-business hours to minimize performance impact.
Custom Fields: If your approval process or other key data points rely on custom fields or records, the saved search filters and columns within the script must be adjusted to include the internal IDs of those custom elements.

a Sample Query javascript

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @NModuleScope SameAccount
 */

/*

ProcessMind NetSuite AP Invoice Extraction Script

This script extracts Accounts Payable invoice processing events from NetSuite.
It queries Vendor Bill and related transaction data, formats it into a single event log, and saves it as a CSV file in the File Cabinet.

Script Parameters:
- custscript_pm_start_date: (Date) The start date for the query window (based on bill creation date).
- custscript_pm_end_date: (Date) The end date for the query window.
- custscript_pm_folder_id: (Integer) The internal ID of the File Cabinet folder to save the CSV file in.
- custscript_pm_subsidiary_id: (Integer, Optional) The internal ID of the subsidiary to filter on.

*/

define(['N/search', 'N/runtime', 'N/file', 'N/format', 'N/log'], (search, runtime, file, format, log) => {

    const execute = (context) => {
        log.debug('Script Execution Started', 'Starting AP Invoice event log extraction.');

        const script = runtime.getCurrentScript();
        const startDate = script.getParameter({ name: 'custscript_pm_start_date' });
        const endDate = script.getParameter({ name: 'custscript_pm_end_date' });
        const folderId = script.getParameter({ name: 'custscript_pm_folder_id' });
        const subsidiaryId = script.getParameter({ name: 'custscript_pm_subsidiary_id' });

        if (!startDate || !endDate || !folderId) {
            log.error('Missing Parameters', 'Start Date, End Date, and Folder ID are required.');
            return;
        }

        let allEvents = [];
        const now = new Date();

        // Helper to format date/time for CSV
        const formatDateTime = (dateStr) => {
            if (!dateStr) return '';
            try {
                const parsedDate = format.parse({ value: dateStr, type: format.Type.DATETIME });
                return format.format({ value: parsedDate, type: format.Type.DATETIMETZ });
            } catch (e) {
                return dateStr; // Fallback for simple date strings
            }
        };

        // Helper function to run a search and format results
        const runSearchAndCollectEvents = (config) => {
            log.debug('Running Search', `Activity: ${config.activityName}`);
            const searchObj = search.create({
                type: config.type,
                filters: config.filters,
                columns: config.columns
            });

            const pagedData = searchObj.runPaged({ pageSize: 1000 });
            pagedData.pageRanges.forEach(pageRange => {
                const page = pagedData.fetch({ index: pageRange.index });
                page.data.forEach(result => {
                    allEvents.push({
                        'Invoice': result.getValue(config.mapping.Invoice) || '',
                        'ActivityName': config.activityName,
                        'EventTime': formatDateTime(result.getValue(config.mapping.EventTime)) || '',
                        'SourceSystem': 'NetSuite',
                        'LastDataUpdate': format.format({ value: now, type: format.Type.DATETIMETZ }),
                        'VendorName': result.getText(config.mapping.VendorName) || result.getValue(config.mapping.VendorName) || '',
                        'InvoiceAmount': result.getValue(config.mapping.InvoiceAmount) || '',
                        'PurchaseOrderNumber': result.getText(config.mapping.PurchaseOrderNumber) || result.getValue(config.mapping.PurchaseOrderNumber) || '',
                        'ProcessorUser': result.getText(config.mapping.ProcessorUser) || '',
                        'Approver': result.getText(config.mapping.Approver) || '',
                        'InvoiceDueDate': result.getValue(config.mapping.InvoiceDueDate) || ''
                    });
                });
            });
            log.debug('Search Complete', `Found ${allEvents.length} total events so far.`);
        };

        // Common filters
        let baseBillFilters = [
            ['mainline', 'is', 'T'], 'AND',
            ['type', 'anyof', 'VendBill'], 'AND',
            ['datecreated', 'within', startDate, endDate]
        ];
        if (subsidiaryId) {
            baseBillFilters.push('AND', ['subsidiary', 'anyof', subsidiaryId]);
        }

        // --- Define Search Configurations for each Activity ---

        const searchConfigs = [
            // 1. Vendor Bill Created
            {
                activityName: 'Vendor Bill Created',
                type: 'vendorbill',
                filters: baseBillFilters,
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'datecreated', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'createdby', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: 'datecreated', VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: 'createdby', InvoiceDueDate: 'duedate' }
            },
            // 2. PO Matching Attempted (Inferred from System Notes on first PO application)
            {
                activityName: 'PO Matching Attempted',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'appliedtotransaction'], 'AND', ['systemnotes.newvalue', 'contains', 'Purchase Order']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', sort: search.Sort.ASC, label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'createdby', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' }),
                    search.createColumn({ name: 'appliedtotransaction', label: 'PurchaseOrderNumber' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: 'createdby', InvoiceDueDate: 'duedate', PurchaseOrderNumber: 'appliedtotransaction' }
            },
            // 3. Discrepancy Identified (Uses a placeholder for custom status, e.g., 'On Hold')
            {
                activityName: 'Discrepancy Identified',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'approvalstatus'], 'AND', ['systemnotes.newvalue', 'is', '[On Hold]']]), // Placeholder: Replace '[On Hold]' with your actual status text
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'name', join: 'systemNotes', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: search.createColumn({name: 'name', join: 'systemNotes'}), InvoiceDueDate: 'duedate' }
            },
            // 4. Approval Submitted & 5. Invoice Re-Routed For Approval
            {
                activityName: 'Approval Submitted', // This will capture initial and subsequent submissions
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'approvalstatus'], 'AND', ['systemnotes.newvalue', 'is', 'Pending Approval']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'name', join: 'systemNotes', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: search.createColumn({name: 'name', join: 'systemNotes'}), InvoiceDueDate: 'duedate' }
            },
            // 6. Invoice Approved & 8. Invoice Posted to GL
            {
                activityName: 'Invoice Approved', // Approval event often coincides with posting
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'approvalstatus'], 'AND', ['systemnotes.newvalue', 'is', 'Approved']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'name', join: 'systemNotes', label: 'Approver' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', Approver: search.createColumn({name: 'name', join: 'systemNotes'}), InvoiceDueDate: 'duedate' }
            },
            // 7. Invoice Rejected
            {
                activityName: 'Invoice Rejected',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'approvalstatus'], 'AND', ['systemnotes.newvalue', 'is', 'Rejected']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'name', join: 'systemNotes', label: 'Approver' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', Approver: search.createColumn({name: 'name', join: 'systemNotes'}), InvoiceDueDate: 'duedate' }
            },
            // 9. Payment Scheduled
            {
                activityName: 'Payment Scheduled',
                type: 'vendorpayment',
                filters: [['mainline', 'is', 'T'], 'AND', ['type', 'anyof', 'VendPymt'], 'AND', ['appliedtotransaction.type', 'anyof', 'VendBill'], 'AND', ['appliedToTransaction.datecreated', 'within', startDate, endDate]],
                columns: [
                    search.createColumn({ name: 'tranid', join: 'appliedToTransaction', label: 'Invoice' }),
                    search.createColumn({ name: 'datecreated', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', join: 'appliedToTransaction', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'createdby', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', join: 'appliedToTransaction', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: search.createColumn({name: 'tranid', join: 'appliedToTransaction'}), EventTime: 'datecreated', VendorName: 'entity', InvoiceAmount: search.createColumn({name: 'total', join: 'appliedToTransaction'}), ProcessorUser: 'createdby', InvoiceDueDate: search.createColumn({name: 'duedate', join: 'appliedToTransaction'}) }
            },
            // 10. Payment Executed
            {
                activityName: 'Payment Executed',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['payingtransaction.trandate', 'isnotempty', '']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'trandate', join: 'payingTransaction', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'createdby', join: 'payingTransaction', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'trandate', join: 'payingTransaction'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: search.createColumn({name: 'createdby', join: 'payingTransaction'}), InvoiceDueDate: 'duedate' }
            },
            // 11. Invoice Closed
            {
                activityName: 'Invoice Closed',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['systemnotes.field', 'is', 'Status'], 'AND', ['systemnotes.newvalue', 'is', 'Vendor Bill : Paid in Full']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'date', join: 'systemNotes', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'name', join: 'systemNotes', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'date', join: 'systemNotes'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: search.createColumn({name: 'name', join: 'systemNotes'}), InvoiceDueDate: 'duedate' }
            },
            // 12. Early Payment Captured
            {
                activityName: 'Early Payment Captured',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['payingtransaction.trandate', 'isnotempty', ''], 'AND', ['formuladate: {payingtransaction.trandate} <= {discountdate}', 'is', '1']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'trandate', join: 'payingTransaction', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'trandate', join: 'payingTransaction'}), VendorName: 'entity', InvoiceAmount: 'total', InvoiceDueDate: 'duedate' }
            },
            // 13. Late Payment Identified
            {
                activityName: 'Late Payment Identified',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['payingtransaction.trandate', 'isnotempty', ''], 'AND', ['formuladate: {payingtransaction.trandate} > {duedate}', 'is', '1']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'trandate', join: 'payingTransaction', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'trandate', join: 'payingTransaction'}), VendorName: 'entity', InvoiceAmount: 'total', InvoiceDueDate: 'duedate' }
            },
            // 14. Credit Memo Applied
            {
                activityName: 'Credit Memo Applied',
                type: 'vendorbill',
                filters: baseBillFilters.concat(['AND', ['applyingtransaction.type', 'anyof', 'VendCred']]),
                columns: [
                    search.createColumn({ name: 'tranid', label: 'Invoice' }),
                    search.createColumn({ name: 'trandate', join: 'applyingTransaction', label: 'EventTime' }),
                    search.createColumn({ name: 'entity', label: 'VendorName' }),
                    search.createColumn({ name: 'total', label: 'InvoiceAmount' }),
                    search.createColumn({ name: 'createdby', join: 'applyingTransaction', label: 'ProcessorUser' }),
                    search.createColumn({ name: 'duedate', label: 'InvoiceDueDate' })
                ],
                mapping: { Invoice: 'tranid', EventTime: search.createColumn({name: 'trandate', join: 'applyingTransaction'}), VendorName: 'entity', InvoiceAmount: 'total', ProcessorUser: search.createColumn({name: 'createdby', join: 'applyingTransaction'}), InvoiceDueDate: 'duedate' }
            },
        ];

        // Execute all searches
        searchConfigs.forEach(config => {
            try {
                runSearchAndCollectEvents(config);
            } catch (e) {
                log.error(`Search Failed for ${config.activityName}`, e);
            }
        });

        // Convert to CSV and save
        if (allEvents.length > 0) {
            const headers = Object.keys(allEvents[0]);
            let csvContent = headers.join(',') + '\n';
            allEvents.forEach(event => {
                const row = headers.map(header => {
                    let value = event[header] ? event[header].toString() : '';
                    // Escape commas and quotes
                    if (value.includes(',') || value.includes('"') || value.includes('\n')) {
                        value = '"' + value.replace(/"/g, '""') + '"';
                    }
                    return value;
                });
                csvContent += row.join(',') + '\n';
            });

            const fileName = `ProcessMind_AP_EventLog_${new Date().toISOString().slice(0, 10)}.csv`;
            const csvFile = file.create({
                name: fileName,
                fileType: file.Type.CSV,
                contents: csvContent,
                folder: folderId
            });
            const fileId = csvFile.save();
            log.audit('File Created', `Successfully created file ${fileName} with ID ${fileId} in folder ${folderId}.`);
        } else {
            log.audit('No Events Found', 'No events found for the specified criteria.');
        }

        log.debug('Script Execution Finished', `Processed a total of ${allEvents.length} events.`);
    };

    return { execute };
});

Data Template: Accounts Payable Invoice Processing

Your Accounts Payable Invoice Processing Data Template

Accounts Payable Invoice Processing Attributes

Accounts Payable Invoice Processing Activities

Extraction Guides

NetSuite Saved Search and Standard Report Export

Steps

Configuration

a Sample Query config

SuiteQL Query via SuiteAnalytics Connect (ODBC/JDBC)

Steps

Configuration

a Sample Query sql

Python Script using SuiteTalk REST/SOAP API

Steps

Configuration

a Sample Query python

Automated Data Export using Server-Side SuiteScript

Steps

Configuration

a Sample Query javascript

Ready to Get Started?

Gain Control: Optimize Accounts Payable Invoices Today