> Continuous Process Improvement > Purchase to Pay Purchase Order > NetSuite > Data Template

Data Template: Purchase to Pay - Purchase Order

NetSuite

Purchase to Pay - Purchase Order Attributes (22) Purchase to Pay - Purchase Order Activities (14) Extraction Guides (3)

Your Purchase to Pay - Purchase Order Data Template

This template is designed to help you prepare your data for process mining, ensuring you capture all necessary information to analyze your Purchase to Pay - Purchase Order process effectively. It outlines the essential data fields, key activities to track, and provides guidance on how to extract this information from NetSuite. By following this template, you can streamline your data preparation and accelerate your path to actionable insights.

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

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

Download Template

Purchase to Pay - Purchase Order Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your Purchase to Pay - Purchase Order process.

5 Required 6 Recommended 11 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business event or step that occurred within the Purchase Order process.
Description This attribute describes a specific action or status change in the lifecycle of a Purchase Order, such as 'Purchase Order Created', 'Purchase Order Approved', or 'Bill Payment Made'. These activities form the nodes of the process map. Analyzing the sequence and frequency of these activities is the core of process mining. It helps to visualize the process flow, identify common and rare paths (variants), and pinpoint bottlenecks or deviations from the standard procedure. Why it matters It defines the steps in the process map, allowing for the visualization and analysis of the process flow, variants, and conformance. Where to get This value is typically derived from a combination of the transaction type and its status field (e.g., 'Order Status') or from system audit trail logs in NetSuite. Examples Purchase Order CreatedPurchase Order ApprovedItem Receipt CreatedBill Created From Purchase Order
	Event Time EventTime	The precise date and time when the activity occurred.
Description This timestamp marks the exact moment a specific activity took place. It is the temporal foundation for all performance and duration analysis in process mining. Event times are used to order activities chronologically within a case, calculate the duration between steps, and measure the overall case cycle time. This data is critical for identifying bottlenecks, measuring wait times, and analyzing performance against service level agreements. Why it matters This attribute is crucial for calculating all time-based metrics, such as cycle times and durations, which are fundamental to identifying process delays. Where to get This is the timestamp associated with each transaction or status change, often found in fields like 'Date Created' or in the system audit trail logs in NetSuite. Examples 2023-10-26T09:00:00Z2023-10-26T11:30:00Z2023-10-27T14:45:00Z
	Purchase Order PurchaseOrder	The unique identifier for the Purchase Order, serving as the primary case ID for tracking the procurement lifecycle.
Description The Purchase Order number is the central identifier that connects all related activities, from the initial creation to final payment and closure. Each distinct Purchase Order number represents a single instance of the procurement process. In process mining, this attribute is used to group all related events into a single case. Analyzing the journey of each Purchase Order allows for a clear view of the end-to-end process, identification of variants, and calculation of case-level metrics like total cycle time. Why it matters This is the essential case identifier, which makes it possible to reconstruct and analyze the entire lifecycle of each individual Purchase Order. Where to get This is typically the main transaction identifier on the Purchase Order record in NetSuite, often referred to as 'Transaction ID' or 'PO #'. Examples PO-001254PO-001299PO-001357
	Last Data Update LastDataUpdate	The timestamp indicating when the data for this event was last refreshed from the source system.
Description This attribute records the date and time the data was most recently extracted or updated. It provides context on the freshness of the data being analyzed. Knowing the last update time is crucial for understanding the timeliness of the insights generated. It helps analysts and business users confirm if they are viewing the most current process data, which is especially important for operational monitoring dashboards. Why it matters Indicates the freshness of the data, which is critical for ensuring that analysis and dashboards are based on up-to-date information. Where to get This is a timestamp generated and added during the data extraction, transformation, and loading (ETL) process. Examples 2024-05-21T02: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, the value will consistently be 'NetSuite'. In larger process mining initiatives that combine data from multiple systems, this field is critical for data lineage, troubleshooting, and understanding system-specific behaviors. It ensures clarity on where each piece of data originated. Why it matters Provides essential data lineage, ensuring clarity on the origin of the process data, especially in environments with multiple integrated systems. Where to get This is a static value ('NetSuite') added during the data extraction and transformation process. Examples NetSuite
	Department Name DepartmentName	The name of the department associated with the Purchase Order.
Description This attribute represents the business department or cost center that initiated the purchase or is financially responsible for it. It allows for filtering and segmenting the process data based on organizational structure. Analyzing the process by department is critical for identifying departmental bottlenecks, comparing efficiency across different business units, and understanding spending patterns. It directly supports dashboards like the 'Departmental Bottleneck Analysis'. Why it matters Enables process analysis by department, helping to identify department-specific bottlenecks, inefficiencies, and spending patterns. Where to get This is a standard field on the Purchase Order record header or line items in NetSuite. Examples FinanceIT ServicesMarketingOperations
	Purchase Category PurchaseCategory	The classification of the goods or services being purchased, such as IT Hardware, Marketing Services, or Office Supplies.
Description This attribute categorizes the items on the Purchase Order, allowing for grouped analysis based on the type of spend. This is often implemented in NetSuite using a custom classification field or segment. Analyzing the process by Purchase Category is essential for the 'Purchase Category Efficiency' dashboard. It helps to uncover whether certain types of purchases have longer cycle times, higher change rates, or more compliance issues, enabling targeted process improvements for specific spend areas. Why it matters Enables analysis of process efficiency by spend category, helping to identify which types of purchases are most inefficient or non-compliant. Where to get This may be a custom field or a standard classification segment like 'Class' or a custom segment applied at the PO header or line level. Consult NetSuite documentation. Examples IT HardwareProfessional ServicesOffice SuppliesSoftware Licenses
	Purchase Order Status PurchaseOrderStatus	The current or final status of the Purchase Order.
Description This attribute indicates the state of the Purchase Order at a given point in time, such as 'Pending Approval', 'Fully Billed', or 'Closed'. This is often the source for deriving many of the conceptual 'ActivityName' values. Analyzing by status helps in understanding the outcomes of purchase orders, such as how many are rejected versus approved, or how many remain open. It is key for KPIs like the 'PO Approval Failure Rate' and for filtering cases based on their completion state. Why it matters Provides a snapshot of the PO's state, enabling analysis of outcomes like rejections or closures and supporting KPIs related to approval failures. Where to get This is the 'Status' field on the Purchase Order transaction record in NetSuite. Examples Pending Supervisor ApprovalPending ReceiptFully BilledClosedRejected
	Total Amount TotalAmount	The total monetary value of the Purchase Order.
Description This attribute represents the total cost of all items and services included in the Purchase Order. It is a fundamental financial metric for procurement analysis. Analyzing process metrics by Total Amount can reveal important patterns, for example, if higher-value POs take longer to approve or are subject to more changes. It is essential for understanding the financial impact of process inefficiencies and for prioritizing improvement efforts. Why it matters Allows for financial analysis of the procurement process, helping to identify how PO value impacts cycle times, approval paths, and compliance. Where to get This corresponds to the 'Total' field in the summary section of the Purchase Order record in NetSuite. Examples 500.001250.7515000.00
	User Name UserName	The name of the user who performed the activity.
Description This attribute identifies the employee or system user responsible for executing a specific process step, such as approving a purchase order or creating a bill. It provides a human or system resource dimension to the process. Analyzing activities by user helps in understanding workload distribution, identifying training needs, and spotting irregularities. It is also crucial for compliance monitoring, as it can be used to track who performed critical actions like PO changes or approvals. Why it matters Attributes activities to specific users, enabling analysis of workload, performance, and compliance with authorization policies. Where to get This is available in the 'System Notes' or audit trail subtab on a transaction record, indicating the user who made the change. Examples Alice JohnsonBob WilliamsSystem Automation
	Vendor Name VendorName	The name of the vendor or supplier from whom the goods or services are being purchased.
Description This attribute identifies the external supplier for the Purchase Order. It is a critical dimension for analyzing supplier relationships and performance. Vendor name is essential for the 'Vendor Delivery Performance' dashboard and related KPIs. It allows for segmenting the process to compare lead times, on-time delivery rates, and quality inspection outcomes across different suppliers, helping to identify both reliable and underperforming vendors. Why it matters Allows for vendor-specific performance analysis, including delivery times and reliability, which is key to supply chain optimization. Where to get This corresponds to the 'Vendor' or 'Supplier' field on the main tab of the Purchase Order record in NetSuite. Examples Global Office SuppliesTech Solutions Inc.Creative Marketing Agency
	Change Reason ChangeReason	The reason provided for why a Purchase Order was changed after its creation.
Description This attribute captures the justification for modifications made to a Purchase Order, such as 'Price Update', 'Quantity Change', or 'Delivery Date Change'. This context is vital for understanding the drivers of rework. This is a crucial attribute for the 'Purchase Order Change Analysis' dashboard. Analyzing the frequency of different change reasons helps to identify the root causes of process instability, such as inaccurate initial requirements or volatile pricing from vendors. Why it matters Provides critical context for why POs are changed, enabling root cause analysis to reduce the overall PO change rate. Where to get This would likely be a custom field that is required to be filled out when a user edits an approved PO, or it could be in the 'Memo' field of the change event. Consult NetSuite documentation. Examples Incorrect item quantityVendor price changeUpdated delivery requirement
	Currency Currency	The currency code for the transaction amount.
Description This attribute specifies the currency in which the Purchase Order's total amount is denominated, such as USD, EUR, or GBP. It is essential context for any monetary values. In organizations that operate internationally, this attribute is critical for accurate financial reporting and analysis. It allows for proper aggregation and comparison of PO values across different regions and ensures that monetary KPIs are interpreted correctly. Why it matters Provides necessary context for monetary values, ensuring accurate financial analysis and reporting in multi-currency environments. Where to get This corresponds to the 'Currency' field on the Purchase Order record, often influenced by the selected Vendor's configuration. Examples USDEURGBP
	Delivery Location DeliveryLocation	The physical location or address where the goods are to be delivered.
Description This attribute specifies the destination for the goods ordered on the PO, such as a specific warehouse, office, or plant. It allows for segmenting the process based on geography or site. This is a key dimension for the 'Goods Receipt Processing Time' dashboard. Analyzing receipt times by location can help identify sites that may be understaffed or have inefficient receiving processes, enabling targeted operational improvements. Why it matters Allows for analysis of process performance by location, helping to identify site-specific bottlenecks in goods receipt or other activities. Where to get This corresponds to the 'Ship To' address or 'Location' field on the Purchase Order record in NetSuite. Examples Main Warehouse - Dock ACorporate HQ - 15th FloorWest Coast Distribution Center
	Event End Time EventEndTime	The precise date and time when the activity concluded.
Description This timestamp marks the completion of an activity. Paired with the Start Time, it defines the processing time of an event. For instantaneous events, End Time can be the same as Start Time. This attribute is essential for calculating the exact duration of specific tasks, known as processing time. It helps differentiate between the time spent actively working on a task versus the time spent waiting for the next step, which is key for detailed bottleneck analysis. Why it matters Enables the calculation of precise activity processing times, helping to distinguish active work time from idle wait time. Where to get For activities with a measurable duration, this may be recorded in a separate field or derived from audit logs. Often, it needs to be inferred or is the same as the Start Time. Examples 2023-10-26T09:05:12Z2023-10-26T11:30:45Z2023-10-27T15:00:00Z
	Is Late Delivery IsLateDelivery	A flag indicating whether the goods were received after the requested delivery date.
Description This is a calculated boolean attribute that is true if the 'Item Receipt Created' activity occurs after the 'Requested Delivery Date' specified on the Purchase Order. It provides a simple, clear indicator of delivery timeliness. This attribute simplifies the creation of vendor performance dashboards and KPIs. It allows for easy filtering and aggregation to calculate the 'Vendor Delivery On-Time Rate' and identify vendors who consistently fail to meet delivery deadlines. Why it matters Provides a simple true/false indicator for on-time delivery, simplifying performance analysis and KPI calculations for vendor management. Where to get This is a calculated field. The logic is: (Timestamp of 'Item Receipt Created') > ('RequestedDeliveryDate'). Examples truefalse
	Is Maverick Buy IsMaverickBuy	A flag indicating if a Purchase Order was created without a preceding Purchase Requisition.
Description This is a calculated boolean attribute that is set to true at the case level if the first activity in the process is 'Purchase Order Created' rather than 'Purchase Requisition Created'. It serves as a direct indicator of non-standard procurement. This attribute is used to calculate the 'Direct PO Rate', a key KPI for the 'Procurement Compliance Monitor'. It helps organizations quickly identify and quantify maverick buying, which can lead to higher costs and increased risk. Why it matters Directly flags non-compliant purchasing behavior, enabling organizations to easily monitor and reduce maverick buying. Where to get This is a calculated attribute. The logic is: (First Activity in Case = 'Purchase Order Created') AND (No 'Purchase Requisition' is linked). Examples truefalse
	Is Rework IsRework	A flag indicating if an activity represents rework, such as a second approval after a rejection.
Description This calculated boolean attribute is true for activities that indicate a process loop or repeated step. For example, a 'Purchase Order Approved' activity would be flagged as rework if it was preceded by a 'Purchase Order Rejected' activity within the same case. This attribute is essential for quantifying process friction and inefficiency. It allows for easy analysis of rework loops, helping to measure the 'PO Rejection & Resubmission Rate' and highlighting processes that are not right the first time. Why it matters Helps to quantify process inefficiencies by explicitly flagging activities that are part of a rework loop, making it easier to analyze and address them. Where to get This flag is calculated during data transformation by analyzing the sequence of activities within a case. Examples truefalse
	Processing Time ProcessingTime	The duration of time spent actively working on an activity.
Description This attribute measures the time between an activity's start and end timestamps. It represents the actual time a resource was engaged in performing a task, as opposed to waiting time between tasks. Calculated from the EventTime and EventEndTime, this metric is vital for detailed performance analysis. It helps pinpoint which specific activities are time-consuming and offers a more granular view of bottlenecks than overall cycle time alone. Why it matters Separates active work time from idle wait time, providing a more accurate measure of activity-level efficiency and resource utilization. Where to get This is calculated during data transformation by subtracting the 'EventTime' from the 'EventEndTime'. Examples 312645900
	Purchase Requisition PurchaseRequisition	The identifier of the Purchase Requisition that preceded the Purchase Order.
Description This attribute links a Purchase Order back to its originating Purchase Requisition. The absence of this link can indicate a deviation from the standard process. This field is critical for compliance monitoring and identifying maverick buying. The 'Direct PO Rate' KPI is calculated by checking for the absence of a linked Purchase Requisition, which highlights purchases made outside the standard approval workflow. Why it matters Crucial for compliance analysis, as it helps identify 'maverick buying' where Purchase Orders are created without an approved requisition. Where to get On the Purchase Order record, this information is typically found on the 'Related Records' subtab or a 'Created From' field. Examples PR-00582PR-00610PR-00715
	Rejection Reason RejectionReason	The reason a Purchase Order was rejected during the approval process.
Description When a Purchase Order is rejected, this attribute provides the specific reason for the rejection, such as 'Budget Exceeded', 'Incorrect Vendor', or 'Policy Violation'. This information is key to understanding approval failures. This attribute directly supports the 'PO Rejection & Resubmission Rate' dashboard. By analyzing rejection reasons, the business can identify common sources of errors in PO creation, such as insufficient training or unclear policies, and take corrective action to reduce the rework associated with rejections. Why it matters Explains why POs fail approval, enabling targeted actions to reduce rejection rates and improve first-time approval success. Where to get This information is often captured in the memo field of the rejection event or a custom 'Rejection Reason' field as part of the approval workflow. Consult NetSuite documentation. Examples Exceeds department budgetNon-preferred vendor selectedIncomplete line item description
	Requested Delivery Date RequestedDeliveryDate	The date on which the business requested the vendor to deliver the goods or services.
Description This attribute is the target delivery date set during the purchasing process. It serves as the baseline against which actual delivery performance is measured. This date is essential for calculating the 'Vendor Delivery On-Time Rate' KPI. By comparing the 'Requested Delivery Date' with the actual 'Item Receipt Created' timestamp, the analysis can determine whether vendors are meeting their delivery commitments, which is a key input for the 'Vendor Delivery Performance' dashboard. Why it matters Serves as the benchmark for measuring vendor on-time delivery performance, a critical KPI for supply chain management. Where to get This is likely a custom field or could be the 'Expected Receipt Date' on the Purchase Order line items. Consult NetSuite documentation. Examples 2023-11-152023-12-012024-01-10

Required Recommended Optional

Purchase to Pay - Purchase Order 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
	Bill Created From Purchase Order	A vendor bill has been received and entered into NetSuite, linked to the Purchase Order. This is an explicit transaction that formally records the liability for the goods or services received.
Why it matters This activity marks the transition from procurement to the accounts payable process. Analyzing the time between goods receipt and billing highlights potential delays in financial processing. Where to get Captured from the creation date of the Vendor Bill transaction record. The bill is directly linked to the originating Purchase Order. Capture Track the creation timestamp of the Vendor Bill transaction. Event type explicit
	Item Receipt Created	This activity represents the physical receipt of goods ordered on the Purchase Order. This is an explicit event in NetSuite, captured when an 'Item Receipt' transaction is created and linked back to the specific Purchase Order line.
Why it matters This is a critical milestone that marks the end of the vendor delivery cycle. It's essential for calculating vendor on-time delivery rates and overall lead times. Where to get Captured from the creation date of the Item Receipt transaction record. The record contains a link to the source Purchase Order. Capture Track the creation timestamp of the Item Receipt transaction. Event type explicit
	Purchase Order Approved	Represents the final, official authorization of the Purchase Order, allowing it to be sent to the vendor. This is a key milestone captured when the Purchase Order's 'Approval Status' field is updated to 'Approved'.
Why it matters This is a critical milestone for calculating approval cycle times and identifying bottlenecks in the approval chain. Delays here directly impact procurement lead times. Where to get Inferred from the system notes or workflow history tracking when the 'Approval Status' field on the Purchase Order record changes to 'Approved'. Capture Detect change in 'Approval Status' field on Purchase Order to 'Approved'. Event type inferred
	Purchase Order Closed	The Purchase Order is officially closed, meaning no further receipts or bills are expected against it. This is inferred when the Purchase Order status changes to 'Closed', which can happen automatically after being fully billed and received, or manually.
Why it matters This activity marks the operational end of the Purchase Order lifecycle. It confirms that the order has been completely fulfilled and processed, providing a definitive endpoint for cycle time analysis. Where to get Inferred from a change in the 'Status' field on the Purchase Order record to 'Closed'. Capture Detect change in 'Status' field on Purchase Order to 'Closed'. Event type inferred
	Purchase Order Created	This activity signifies the creation of the formal Purchase Order document, which is the central case for this analysis. In NetSuite, this is captured from the creation of the Purchase Order transaction record, either manually or from an approved requisition.
Why it matters This marks a critical step where a request becomes a formal order commitment. It serves as a key milestone and can also be the start of the process for direct POs, which is important for compliance monitoring. Where to get Captured from the 'Date Created' system field on the Purchase Order transaction record. Capture Use the creation timestamp of the Purchase Order transaction. Event type explicit
	Purchase Requisition Created	This activity marks the formal request for goods or services, initiating the procurement process. In NetSuite, this is captured when a new Purchase Requisition transaction record is created and saved.
Why it matters As the typical starting point of the process, this activity is crucial for analyzing the full end-to-end cycle time and identifying maverick buying when it is skipped. Where to get This event is captured from the creation date of the Purchase Requisition transaction record. The record is linked to the subsequent Purchase Order. Capture Track the creation timestamp of the Purchase Requisition transaction. Event type explicit
	Bill Approved	The vendor bill has been reviewed and approved for payment. Similar to Purchase Orders, this is captured by a change in the 'Approval Status' field on the Vendor Bill record.
Why it matters Bill approval is a key step in the payment process. Tracking its duration helps identify bottlenecks in accounts payable that could lead to late payments or missed early payment discounts. Where to get Inferred from the system notes or workflow history tracking when the 'Approval Status' field on the Vendor Bill record changes to 'Approved'. Capture Detect change in 'Approval Status' field on Vendor Bill to 'Approved'. Event type inferred
	Bill Payment Made	A payment has been issued to the vendor for the billed amount. This is captured when a 'Vendor Payment' transaction is created and applied to the Vendor Bill.
Why it matters This activity signifies the completion of the financial obligation for the Purchase Order. It is critical for analyzing on-time payment performance and cash flow. Where to get Captured from the creation date of the Vendor Payment transaction record that is applied to the Vendor Bill. Capture Track the creation timestamp of the Vendor Payment transaction applied to the bill. Event type explicit
	Purchase Order Changed	Indicates that a modification was made to the Purchase Order after its initial creation or approval. This can be inferred by comparing the 'Last Modified Date' to the 'Date Created' or a separate approval date.
Why it matters Frequent changes can signal inefficiencies, poor initial planning, or scope creep. Analyzing when and why changes occur helps streamline the process and reduce errors. Where to get Inferred by comparing the 'Last Modified Date' on the Purchase Order record with its creation or approval timestamp. The system notes log provides details of the specific fields changed. Capture Compare 'Last Modified Date' to creation or approval date; filter out system updates. Event type inferred
	Purchase Order Rejected	An approver has rejected the Purchase Order, requiring it to be revised and resubmitted. This event is inferred when the Purchase Order 'Approval Status' field is changed to 'Rejected'.
Why it matters Tracking rejections is crucial for identifying rework loops, understanding reasons for approval failure, and improving the quality of initial PO submissions. Where to get Inferred from the system notes or workflow history tracking when the 'Approval Status' field on the Purchase Order record changes to 'Rejected'. Capture Detect change in 'Approval Status' field on Purchase Order to 'Rejected'. Event type inferred
	Purchase Order Sent To Vendor	The approved Purchase Order has been transmitted to the vendor. In NetSuite, this is often inferred when a communication flag, such as the 'To Be Emailed' checkbox, is updated or a status changes to reflect it has been sent.
Why it matters This activity marks the start of the vendor lead time. Measuring from this point to goods receipt is essential for evaluating vendor delivery performance. Where to get Inferred from the timestamp when the 'To Be Emailed' or 'To Be Faxed' flag is cleared, or from an entry in the communication history linked to the Purchase Order. Capture Track timestamp of communication events (e.g., email sent) linked to the PO. Event type inferred
	Purchase Order Submitted	The Purchase Order has been finalized and submitted into an approval workflow. This is typically captured by a status change on the Purchase Order record, moving from a draft state like 'Pending Supervisor Approval' to an active review state.
Why it matters This event marks the beginning of the approval cycle. Measuring the time from this point to 'Purchase Order Approved' is key to analyzing approval efficiency and identifying delays. Where to get Inferred from a change in the 'Approval Status' field on the Purchase Order record (e.g., from 'Pending Approval' or a custom draft status). Capture Detect change in the 'Approval Status' field on the PO to an in-review state. Event type inferred
	Purchase Requisition Approved	Represents the official approval of a request for goods or services, authorizing the creation of a Purchase Order. This event is inferred from a change in the 'Approval Status' field on the Purchase Requisition record from 'Pending Approval' to 'Approved'.
Why it matters Tracking requisition approvals helps identify bottlenecks in the pre-procurement phase and measures the efficiency of internal request validation. Where to get Inferred from the system notes or workflow history tracking the change of the 'Approval Status' field on the Purchase Requisition record. Capture Detect change in 'Approval Status' field on Purchase Requisition to 'Approved'. Event type inferred
	Quality Inspection Performed	Goods received have undergone a quality inspection check. This is not a standard NetSuite transaction and is typically captured via a custom field update, custom record, or inferred from a status change on the Item Receipt.
Why it matters This activity helps measure the cycle time of the quality control process. Delays in inspection can create bottlenecks between receiving goods and making them available for use. Where to get Highly dependent on customization. May be captured from a custom 'QA Status' field on the Item Receipt or a separate custom record for 'Quality Inspection'. Capture Track update of a custom field or creation of a custom record related to quality inspection. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Understand the Multi-Search Approach: This method requires creating and exporting several distinct Saved Searches, one for each major transaction type in the Purchase to Pay process. The exported CSV files are then combined and transformed in a separate tool (like Excel, Power Query, or a script) to create the final, consolidated event log.
Navigate to Saved Search Creation: Log in to NetSuite. Go to Reports > Saved Searches > All Saved Searches > New. Select 'Transaction' as the search type.
Create the Purchase Requisition Search:
- Set the Search Title, for example, 'ProcessMind - Purchase Requisition Events'.
- Under the 'Criteria' tab, filter for Type is 'Purchase Requisition' and Main Line is 'Yes'. Add a Date Created filter for your desired analysis period.
- Under the 'Results' tab, add the columns specified in the query section below for requisitions. Use formulas to define the 'ActivityName' and 'EventTime' columns.
- Save and run the search. Export the results as a CSV file.
Create the Purchase Order Search:
- Create a new 'Transaction' Saved Search titled 'ProcessMind - Purchase Order Events'.
- Under 'Criteria', filter for Type is 'Purchase Order' and Main Line is 'Yes'. Use the same date filter as the previous search.
- Under 'Results', add the columns specified in the query section for purchase orders. This search will generate multiple event rows (Created, Approved, Closed, etc.) using CASE formulas.
- Save, run, and export the results as a CSV file.
Create the Item Receipt Search:
- Create a new 'Transaction' Saved Search titled 'ProcessMind - Item Receipt Events'.
- Under 'Criteria', filter for Type is 'Item Receipt' and Main Line is 'Yes'. Use the same date filter.
- Under 'Results', add the columns specified in the query section for item receipts.
- Save, run, and export the results as a CSV file.
Create the Vendor Bill & Payment Search:
- Create a new 'Transaction' Saved Search titled 'ProcessMind - Billing Events'.
- Under 'Criteria', filter for Type is any of 'Vendor Bill', 'Vendor Payment'. Set Main Line to 'Yes' and apply the same date filter.
- Under 'Results', add columns to capture bill creation, approval, and payment events using formulas as detailed in the query section.
- Save, run, and export the results as a CSV file.
Combine and Transform Data:
- Open your preferred data manipulation tool (e.g., Microsoft Excel with Power Query, a Python script with pandas).
- Import all the CSV files you exported from NetSuite.
- Append the data from all files into a single table. All searches are designed to have the same column structure.
- For the Purchase Order search output, you will need to unpivot the data. The query is designed with multiple timestamp columns, one for each PO activity. Unpivot these columns to create a single 'ActivityName' and 'EventTime' column, which transforms the wide format into the long, vertical format required for an event log.
Finalize and Export the Event Log:
- Review the combined data. Ensure the EventTime column is in a consistent timestamp format and PurchaseOrder is populated for all rows.
- Remove any rows where the EventTime is null, as these represent activities that have not yet occurred.
- Save the final, consolidated table as a single CSV file, which is now ready for upload to ProcessMind.

Configuration

Search Type: All Saved Searches should be of the type 'Transaction'.
Primary Filter (Main Line): Set the criteria Main Line to 'Yes' for all searches. This is crucial to avoid getting one row per item line, which would massively duplicate transaction-level events.
Date Range: Apply a consistent date filter, such as Date Created is 'within last 6 months', across all searches to ensure you are analyzing the same set of transactions.
Transaction Type Filter: Each search must be filtered by the specific transaction Type (e.g., Purchase Order, Item Receipt) to isolate the correct events.
Output Format: Set the export preference to CSV for easiest combination in external tools.
Permissions: The user running the searches needs view permissions for all relevant transaction records, including Purchase Requisitions, Purchase Orders, Item Receipts, Vendor Bills, and Vendor Payments.
Custom Fields: For activities like 'Quality Inspection Performed', you will likely need to add a custom field from your NetSuite configuration to the results of the Item Receipt search. The provided query uses a placeholder [Your Quality Inspection Timestamp Field]. You must replace this with the actual field ID from your system.

a Sample Query config

### Description of Saved Search Configurations

Below are the configurations for the required Saved Searches. The 'Results' tab columns should be created in the order listed. Use 'Custom Label' to name the columns exactly as shown.

**1. Search: ProcessMind - Purchase Requisition Events**
*   **Criteria**:
    *   Type = 'Purchase Requisition'
    *   Main Line = 'Yes'
    *   Date Created = [Your Desired Date Range]
*   **Results**:
    1.  Formula (Text): `REPLACE({createdfrom.number}, 'Purchase Order #', '')` -> Custom Label: `PurchaseOrder`
    2.  Formula (Text): `CASE WHEN {status} = 'Pending Approval' THEN 'Purchase Requisition Created' WHEN {status} = 'Approved' THEN 'Purchase Requisition Approved' ELSE NULL END` -> Custom Label: `ActivityName`
    3.  Formula (Date/Time): `CASE WHEN {status} = 'Pending Approval' THEN {datecreated} WHEN {status} = 'Approved' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime`
    4.  Formula (Text): `'NetSuite'` -> Custom Label: `SourceSystem`
    5.  Formula (Date/Time): `{today}` -> Custom Label: `LastDataUpdate`
    6.  Field: `Created By` -> Custom Label: `UserName`
    7.  Field: `Department` -> Custom Label: `DepartmentName`
    8.  Field: `Vendor Name` -> Custom Label: `VendorName`
    9.  Field: `Status` -> Custom Label: `PurchaseOrderStatus`
    10. Field: `Amount` -> Custom Label: `TotalAmount`
    11. Field: `Class` -> Custom Label: `PurchaseCategory`

**2. Search: ProcessMind - Purchase Order Events (Wide Format)**
*   **Criteria**:
    *   Type = 'Purchase Order'
    *   Main Line = 'Yes'
    *   Date = [Your Desired Date Range]
*   **Results (Note: This search creates a wide file that must be unpivoted later)**:
    1.  Field: `Document Number` -> Custom Label: `PurchaseOrder`
    2.  Field: `Date Created` -> Custom Label: `EventTime_Purchase Order Created`
    3.  Formula (Date/Time): `CASE WHEN {status} = 'Pending Supervisor Approval' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Submitted`
    4.  Formula (Date/Time): `CASE WHEN {approvalstatus} = 'Approved' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Approved`
    5.  Formula (Date/Time): `CASE WHEN {approvalstatus} = 'Rejected' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Rejected`
    6.  Formula (Date/Time): `CASE WHEN {lastmodifieddate} > {datecreated} + 1 THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Changed`
    7.  Formula (Date/Time): `CASE WHEN {status} = 'Pending Billing' OR {status} = 'Partially Received' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Sent To Vendor`
    8.  Formula (Date/Time): `CASE WHEN {status} = 'Closed' THEN {lastmodifieddate} ELSE NULL END` -> Custom Label: `EventTime_Purchase Order Closed`
    9.  Field: `Created By` -> Custom Label: `UserName`
    10. Field: `Department` -> Custom Label: `DepartmentName`
    11. Field: `Vendor Name` -> Custom Label: `VendorName`
    12. Field: `Status` -> Custom Label: `PurchaseOrderStatus`
    13. Field: `Total Amount` -> Custom Label: `TotalAmount`
    14. Field: `Class` -> Custom Label: `PurchaseCategory`

**3. Search: ProcessMind - Item Receipt Events**
*   **Criteria**:
    *   Type = 'Item Receipt'
    *   Main Line = 'Yes'
    *   Date Created = [Your Desired Date Range]
*   **Results**:
    1.  Field: `Created From : Document Number` -> Custom Label: `PurchaseOrder`
    2.  Formula (Text): `'Item Receipt Created'` -> Custom Label: `ActivityName`
    3.  Field: `Date Created` -> Custom Label: `EventTime`
    4.  Formula (Text): `'NetSuite'` -> Custom Label: `SourceSystem`
    5.  Formula (Date/Time): `{today}` -> Custom Label: `LastDataUpdate`
    6.  Field: `Created By` -> Custom Label: `UserName`
    7.  Field: `Created From : Department` -> Custom Label: `DepartmentName`
    8.  Field: `Created From : Vendor Name` -> Custom Label: `VendorName`
    9.  Field: `Created From : Status` -> Custom Label: `PurchaseOrderStatus`
    10. Field: `Created From : Total Amount` -> Custom Label: `TotalAmount`
    11. Field: `Created From : Class` -> Custom Label: `PurchaseCategory`

**4. Search: ProcessMind - Billing Events**
*   **Criteria**:
    *   Type = any of 'Vendor Bill', 'Vendor Payment'
    *   Main Line = 'Yes'
    *   Date = [Your Desired Date Range]
*   **Results**:
    1.  Field: `Created From : Document Number` -> Custom Label: `PurchaseOrder`
    2.  Formula (Text): `CASE WHEN {type} = 'Vendor Bill' AND {approvalstatus} <> 'Approved' THEN 'Bill Created From Purchase Order' WHEN {type} = 'Vendor Bill' AND {approvalstatus} = 'Approved' THEN 'Bill Approved' WHEN {type} = 'Vendor Payment' THEN 'Bill Payment Made' ELSE NULL END` -> Custom Label: `ActivityName`
    3.  Field: `Date` -> Custom Label: `EventTime`
    4.  Formula (Text): `'NetSuite'` -> Custom Label: `SourceSystem`
    5.  Formula (Date/Time): `{today}` -> Custom Label: `LastDataUpdate`
    6.  Field: `Created By` -> Custom Label: `UserName`
    7.  Field: `Created From : Department` -> Custom Label: `DepartmentName`
    8.  Field: `Created From : Vendor Name` -> Custom Label: `VendorName`
    9.  Field: `Created From : Status` -> Custom Label: `PurchaseOrderStatus`
    10. Field: `Created From : Total Amount` -> Custom Label: `TotalAmount`
    11. Field: `Created From : Class` -> Custom Label: `PurchaseCategory`

Steps

Prerequisite: Enable SuiteAnalytics Connect: Ensure that the SuiteAnalytics Connect feature is enabled in your NetSuite account. Navigate to Setup > Company > Enable Features. On the Analytics subtab, check the box for SuiteAnalytics Connect. Note that this is a paid add-on module.
Configure User Role and Permissions: Create or edit a user role to grant access to SuiteAnalytics Connect. The role must have the 'SuiteAnalytics Connect' permission. Additionally, grant 'View' level access to all necessary records, including Transactions (Purchase Order, Purchase Requisition, Item Receipt, Vendor Bill, Vendor Payment), Entities (Vendor, Employee), and Lists (Department, etc.).
Identify Your Data Source: In NetSuite, go to the SuiteAnalytics Connect Setup page (Setup > Company > SuiteAnalytics Connect). Note your Service Host, Port, Account ID, and the correct Data Source name (e.g., NetSuite.com, NetSuite2.com).
Install ODBC/JDBC Driver: Download and install the appropriate SuiteAnalytics Connect driver (ODBC or JDBC) for your operating system and SQL client. Drivers are available from the NetSuite Help Center.
Configure SQL Client Connection: Set up a new connection in your preferred SQL client (e.g., DBeaver, Power BI, Tableau). Use the connection details from Step 3 and the credentials of the user assigned the role from Step 2.
Customize the SQL Query: Copy the provided SQL query into your SQL client. Modify the placeholder values in the WHERE clauses, specifically the date range ('YYYY-MM-DD') and any company-specific filters like Subsidiary ID.
Execute the Query: Run the complete SQL query against the NetSuite database via your configured connection. The execution time will vary depending on the date range and volume of data.
Review the Results: The query will return a flat event log table with one row per activity. Review the output to ensure all columns (PurchaseOrder, ActivityName, EventTime, etc.) are populated as expected.
Export the Data: Export the query results as a CSV file. Ensure the file encoding is set to UTF-8 to prevent character issues.
Prepare for Upload: Before uploading to ProcessMind, confirm that the column headers in the CSV file match the required attributes (PurchaseOrder, ActivityName, EventTime) and that the date format in the EventTime column is consistent.

Configuration

SuiteAnalytics Connect License: Your organization must have a valid license for the SuiteAnalytics Connect add-on module to establish a direct database connection.
User Permissions: A dedicated NetSuite user role with the 'SuiteAnalytics Connect' permission and view access to all relevant transaction and entity records is required. Without proper permissions, the query will fail or return incomplete data.
Data Source Name (DSN): You must use the correct Service Host and DSN provided in the NetSuite UI under 'SuiteAnalytics Connect Setup'. The NetSuite2.com data source is generally recommended as it reflects the workbook-based data model and often has better performance.
Date Range Filtering: The provided query includes a WHERE clause (WHERE T.DateCreated >= TO_DATE('2023-01-01', 'YYYY-MM-DD')) to limit the data extraction period. It is critical to set a reasonable date range (e.g., 3-6 months) to ensure query performance. Extracting multiple years of data in a single execution is not recommended.
Subsidiary/Company Filters: For multi-subsidiary environments, add a filter on the Subsidiary ID in the WHERE clause of the main Transactions_CTE to isolate data for a specific legal entity (e.g., AND T.Subsidiary = 1).
Custom Fields: The query includes placeholders for common custom fields like PurchaseCategory. You must replace [Your Purchase Category Custom Field] with the actual field ID from your NetSuite implementation.

a Sample Query sql

WITH Transactions_CTE AS (
  SELECT
    T.Transaction_ID,
    T.TranID,
    T.Type,
    T.Status,
    T.ApprovalStatus,
    T.DateCreated,
    T.LastModifiedDate,
    T.Entity_ID AS Vendor_ID,
    T.CreatedBy_ID,
    T.Total,
    T.Department_ID,
    T.ToBeEmailed,
    TL.CreatedFrom_ID,
    -- Placeholder for a custom field. Replace with your actual field ID.
    T.custbody_purchase_category AS PurchaseCategory_ID 
  FROM
    Transaction AS T
    LEFT JOIN TransactionLine AS TL ON T.Transaction_ID = TL.Transaction_ID
  WHERE
    (T.Type IN ('PurchOrd', 'PurchReq', 'ItemRcpt', 'VendBill', 'VendPymt'))
    AND T.DateCreated >= TO_DATE('[YYYY-MM-DD]', 'YYYY-MM-DD') -- Set Start Date
    -- Optional: Add a subsidiary filter for multi-subsidiary environments
    -- AND T.Subsidiary_ID = [Your Subsidiary ID] 
    AND TL.MainLine = 'T' -- Focus on main line data to avoid row duplication
)
-- 1. Purchase Requisition Created
SELECT
  PR_PO_Map.PO_TranID AS "PurchaseOrder",
  'Purchase Requisition Created' AS "ActivityName",
  PR.DateCreated AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PR
  JOIN TransactionLine AS PR_Line ON PR.Transaction_ID = PR_Line.Transaction_ID
  JOIN Transaction AS PO_Link ON PR_Line.Transaction_ID = PO_Link.AppliedToTransaction_ID
  JOIN Transactions_CTE AS PO ON PO_Link.Transaction_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON PR.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PR.Department_ID = D.Department_ID
  CROSS JOIN (
      SELECT DISTINCT PO_Ref.TranID AS PO_TranID, PR_Ref.Transaction_ID AS PR_ID
      FROM Transaction AS PO_Ref JOIN TransactionLine AS TL_Ref ON PO_Ref.Transaction_ID = TL_Ref.Transaction_ID JOIN Transaction AS PR_Ref ON TL_Ref.CreatedFrom_ID = PR_Ref.Transaction_ID
      WHERE PO_Ref.Type = 'PurchOrd' AND PR_Ref.Type = 'PurchReq'
  ) AS PR_PO_Map ON PR.Transaction_ID = PR_PO_Map.PR_ID
WHERE
  PR.Type = 'PurchReq'

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  PR_PO_Map.PO_TranID AS "PurchaseOrder",
  'Purchase Requisition Approved' AS "ActivityName",
  PR.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PR
  JOIN TransactionLine AS PR_Line ON PR.Transaction_ID = PR_Line.Transaction_ID
  JOIN Transaction AS PO_Link ON PR_Line.Transaction_ID = PO_Link.AppliedToTransaction_ID
  JOIN Transactions_CTE AS PO ON PO_Link.Transaction_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON PR.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PR.Department_ID = D.Department_ID
  CROSS JOIN (
      SELECT DISTINCT PO_Ref.TranID AS PO_TranID, PR_Ref.Transaction_ID AS PR_ID
      FROM Transaction AS PO_Ref JOIN TransactionLine AS TL_Ref ON PO_Ref.Transaction_ID = TL_Ref.Transaction_ID JOIN Transaction AS PR_Ref ON TL_Ref.CreatedFrom_ID = PR_Ref.Transaction_ID
      WHERE PO_Ref.Type = 'PurchOrd' AND PR_Ref.Type = 'PurchReq'
  ) AS PR_PO_Map ON PR.Transaction_ID = PR_PO_Map.PR_ID
WHERE
  PR.Type = 'PurchReq'
  AND BUILTIN.DF(PR.ApprovalStatus) = 'Approved'

UNION ALL

-- 3. Purchase Order Created
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Created' AS "ActivityName",
  PO.DateCreated AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Submitted' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND BUILTIN.DF(PO.ApprovalStatus) = 'Pending Approval' AND PO.LastModifiedDate > PO.DateCreated

UNION ALL

-- 5. Purchase Order Approved
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Approved' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND BUILTIN.DF(PO.ApprovalStatus) = 'Approved'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Rejected' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND BUILTIN.DF(PO.ApprovalStatus) = 'Rejected'

UNION ALL

-- 7. Purchase Order Changed
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Changed' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND PO.LastModifiedDate > PO.DateCreated

UNION ALL

-- 8. Purchase Order Sent To Vendor
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Sent To Vendor' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND PO.ToBeEmailed = 'F' -- Assumes the PO was sent via email

UNION ALL

-- 9. Item Receipt Created
SELECT
  PO.TranID AS "PurchaseOrder",
  'Item Receipt Created' AS "ActivityName",
  IR.DateCreated AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS IR
  JOIN Transactions_CTE AS PO ON IR.CreatedFrom_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON IR.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  IR.Type = 'ItemRcpt' AND PO.Type = 'PurchOrd'

UNION ALL

-- 10. Quality Inspection Performed
SELECT
  PO.TranID AS "PurchaseOrder",
  'Quality Inspection Performed' AS "ActivityName",
  IR.LastModifiedDate AS "EventTime", -- Assuming date is updated on the Item Receipt
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS IR
  JOIN Transactions_CTE AS PO ON IR.CreatedFrom_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON IR.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  IR.Type = 'ItemRcpt' AND PO.Type = 'PurchOrd'
  -- This part is a placeholder. It assumes a custom checkbox `custbody_quality_inspected` is checked
  -- to indicate inspection. Adjust the field ID and logic based on your NetSuite configuration.
  AND IR.custbody_quality_inspected = 'T'

UNION ALL

-- 11. Bill Created From Purchase Order
SELECT
  PO.TranID AS "PurchaseOrder",
  'Bill Created From Purchase Order' AS "ActivityName",
  VB.DateCreated AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS VB
  JOIN Transactions_CTE AS PO ON VB.CreatedFrom_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON VB.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  VB.Type = 'VendBill' AND PO.Type = 'PurchOrd'

UNION ALL

-- 12. Bill Approved
SELECT
  PO.TranID AS "PurchaseOrder",
  'Bill Approved' AS "ActivityName",
  VB.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS VB
  JOIN Transactions_CTE AS PO ON VB.CreatedFrom_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON VB.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  VB.Type = 'VendBill' AND PO.Type = 'PurchOrd'
  AND BUILTIN.DF(VB.ApprovalStatus) = 'Approved'

UNION ALL

-- 13. Bill Payment Made
SELECT
  PO.TranID AS "PurchaseOrder",
  'Bill Payment Made' AS "ActivityName",
  VP.DateCreated AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS VP
  JOIN TransactionLink AS VL ON VP.Transaction_ID = VL.Transaction_ID AND VL.LinkType = 'Applied To'
  JOIN Transactions_CTE AS VB ON VL.PreviousTransaction_ID = VB.Transaction_ID
  JOIN Transactions_CTE AS PO ON VB.CreatedFrom_ID = PO.Transaction_ID
  LEFT JOIN Employee AS E_User ON VP.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  VP.Type = 'VendPymt' AND VB.Type = 'VendBill' AND PO.Type = 'PurchOrd'

UNION ALL

-- 14. Purchase Order Closed
SELECT
  PO.TranID AS "PurchaseOrder",
  'Purchase Order Closed' AS "ActivityName",
  PO.LastModifiedDate AS "EventTime",
  'NetSuite' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BUILTIN.DF(E_User.Employee_ID) AS "UserName",
  BUILTIN.DF(D.Department_ID) AS "DepartmentName",
  BUILTIN.DF(PO.Vendor_ID) AS "VendorName",
  BUILTIN.DF(PO.Status) AS "PurchaseOrderStatus",
  PO.Total AS "TotalAmount",
  BUILTIN.DF(PO.PurchaseCategory_ID) AS "PurchaseCategory"
FROM
  Transactions_CTE AS PO
  LEFT JOIN Employee AS E_User ON PO.CreatedBy_ID = E_User.Employee_ID
  LEFT JOIN Department AS D ON PO.Department_ID = D.Department_ID
WHERE
  PO.Type = 'PurchOrd'
  AND BUILTIN.DF(PO.Status) = 'Closed';

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.6 or newer installed. Install the necessary libraries using pip: pip install requests requests-oauthlib pandas. You will also need credentials for NetSuite's Token-Based Authentication (TBA).
Configure NetSuite for API Access: In NetSuite, an administrator must enable the SuiteTalk (REST Web Services) feature. Navigate to Setup > Company > Enable Features. On the SuiteCloud tab, under the SuiteTalk (Web Services) section, check the REST WEB SERVICES box and save.
Create Integration Record: Navigate to Setup > Integration > Manage Integrations > New. Create a new integration record, ensuring Token-Based Authentication is enabled. Note the generated Consumer Key and Consumer Secret.
Create Access Token: Navigate to Setup > Users/Roles > Access Tokens > New. Select the integration record you just created, a user, and a role with sufficient permissions to access purchase orders, requisitions, bills, and payments. Note the generated Token ID and Token Secret. You will also need your NetSuite Account ID, found at Setup > Integration > Web Services Preferences.
Prepare the Python Script: Copy the Python script from the 'Query' section below into a file named extract_netsuite_p2p.py. Populate the placeholder variables at the top of the script with your Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret.
Define Extraction Scope: Modify the SuiteQL query within the script to set the desired date range. Locate the WHERE clauses that contain TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD') and replace the placeholder dates with your desired start and end dates for the extraction.
Customize for Custom Fields (If Needed): Review the SuiteQL query, especially the section for 'Quality Inspection Performed'. This activity often relies on custom fields or records. Adjust the query logic to match your organization's specific NetSuite configuration for this step.
Execute the Script: Run the script from your terminal using the command: python extract_netsuite_p2p.py. The script will connect to the NetSuite REST API, execute the query, handle pagination, and collect the results.
Review the Output: Upon successful execution, the script will generate a CSV file named netsuite_p2p_event_log.csv in the same directory. This file contains the extracted event log.
Validate and Upload: Perform the checks outlined in the 'Validation Steps' section to ensure data quality and completeness. Once validated, the CSV file is ready to be uploaded to ProcessMind.

Configuration

Authentication: This script requires Token-Based Authentication (TBA). The associated NetSuite role needs permissions for 'REST Web Services', as well as view access to Transactions like Purchase Orders, Purchase Requisitions, Vendor Bills, Vendor Payments, and Item Receipts.
Date Range: The SuiteQL query includes a placeholder TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD') filter. It is critical to define a specific date range to manage the data volume and avoid API timeouts. For an initial extraction, a period of 3-6 months is recommended.
Subsidiary Filter: For NetSuite OneWorld accounts, it is highly recommended to add a filter for the specific subsidiary to ensure data is extracted for the correct legal entity. This can be added to the WHERE clause of each SELECT statement, for example: AND Transaction.subsidiary = [Your Subsidiary ID].
API Governance: NetSuite enforces API concurrency and usage limits. Executing the script for a very large date range may require running it in smaller, sequential batches to stay within these limits. The script's LIMIT is set to 1000, which is the maximum page size for SuiteQL queries.
Custom Fields and Workflows: The script assumes standard NetSuite fields and statuses (e.g., 'approvalStatus'). If your organization uses custom fields for statuses or custom approval workflows, the SuiteQL query must be modified to reflect these custom configurations.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your NetSuite Credentials ---
class=class="hl-string">"hl-comment"># Your NetSuite Account ID (e.g., 1234567 or 1234567_SB1 for sandbox)
ACCOUNT_ID = class="hl-string">"[Your NetSuite Account ID]"

class=class="hl-string">"hl-comment"># Token-Based Authentication Credentials
CONSUMER_KEY = class="hl-string">"[Your Consumer Key]"
CONSUMER_SECRET = class="hl-string">"[Your Consumer Secret]"
TOKEN_ID = class="hl-string">"[Your Token ID]"
TOKEN_SECRET = class="hl-string">"[Your Token Secret]"

class=class="hl-string">"hl-comment"># --- Script Settings ---
class=class="hl-string">"hl-comment"># The script will stop if it encounters this many consecutive errors.
MAX_CONSECUTIVE_ERRORS = 5

class=class="hl-string">"hl-comment"># Construct the base URL for the SuiteQL endpoint
class=class="hl-string">"hl-comment"># Use class="hl-string">'suitetalk.api.netsuite.com' for production or class="hl-string">'suitetalk.api.sandbox.netsuite.com' for sandbox
base_url = fclass="hl-string">"https://{ACCOUNT_ID.lower().replace('_', '-')}.suitetalk.api.netsuite.com"
suiteql_url = fclass="hl-string">"{base_url}/services/rest/query/v1/suiteql"

class=class="hl-string">"hl-comment"># --- SuiteQL Query ---
class=class="hl-string">"hl-comment"># This query extracts events for the Purchase-to-Pay process centered on Purchase Orders.
class=class="hl-string">"hl-comment"># !! IMPORTANT !!: Replace class="hl-string">'YYYY-MM-DD' with your desired start and end dates.
suiteql_query = class="hl-string">"""
SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Requisition Created' AS ActivityName,
    req.tranDate AS EventTime,
    BUILTIN.DF(req.entity) AS UserName,
    BUILTIN.DF(req.department) AS DepartmentName,
    NULL AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(req.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS req
    INNER JOIN Transaction AS po ON req.ID = po.createdFrom
WHERE
    req.type = 'PurchReq'
    AND po.type = 'PurchOrd'
    AND req.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Requisition Approved' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(req.department) AS DepartmentName,
    NULL AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(req.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS req ON note.recordId = req.ID
    INNER JOIN Transaction AS po ON req.ID = po.createdFrom
WHERE
    req.type = 'PurchReq'
    AND po.type = 'PurchOrd'
    AND note.field = 'TRN_APPROVALSTATUS'
    AND note.newValue = 'Approved'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Created' AS ActivityName,
    po.tranDate AS EventTime,
    BUILTIN.DF(po.createdBy) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS po
WHERE
    po.type = 'PurchOrd'
    AND po.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Submitted' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS po ON note.recordId = po.ID
WHERE
    po.type = 'PurchOrd'
    AND note.field = 'TRN_APPROVALSTATUS'
    AND note.newValue LIKE 'Pending%'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Approved' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS po ON note.recordId = po.ID
WHERE
    po.type = 'PurchOrd'
    AND note.field = 'TRN_APPROVALSTATUS'
    AND note.newValue = 'Approved'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Rejected' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS po ON note.recordId = po.ID
WHERE
    po.type = 'PurchOrd'
    AND note.field = 'TRN_APPROVALSTATUS'
    AND note.newValue = 'Rejected'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT DISTINCT
    po.tranId AS PurchaseOrder,
    'Purchase Order Changed' AS ActivityName,
    po.lastModifiedDate AS EventTime,
    BUILTIN.DF(po.lastModifiedBy) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS po
WHERE
    po.type = 'PurchOrd'
    AND po.lastModifiedDate > po.dateCreated
    AND po.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Sent To Vendor' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS po ON note.recordId = po.ID
WHERE
    po.type = 'PurchOrd'
    AND note.field = 'TRN_TOBEEMAILED' AND note.newValue = 'No'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    BUILTIN.DF(ir.createdFrom) AS PurchaseOrder,
    'Item Receipt Created' AS ActivityName,
    ir.tranDate AS EventTime,
    BUILTIN.DF(ir.createdBy) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS ir
    INNER JOIN Transaction AS po ON ir.createdFrom = po.ID
WHERE
    ir.type = 'ItemRcpt'
    AND po.type = 'PurchOrd'
    AND ir.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

-- Placeholder for Quality Inspection. This requires a custom field or record.
-- This query assumes a custom checkbox field 'custbody_quality_inspection_done' on the Item Receipt.
-- Please update 'custbody_quality_inspection_done' with your actual custom field ID.
SELECT
    BUILTIN.DF(ir.createdFrom) AS PurchaseOrder,
    'Quality Inspection Performed' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote note
    INNER JOIN Transaction ir ON note.recordId = ir.ID
    INNER JOIN Transaction po ON ir.createdFrom = po.ID
WHERE
    ir.type = 'ItemRcpt'
    AND note.field = 'CUSTBODY_QUALITY_INSPECTION_DONE'
    AND note.newValue = 'Yes'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    BUILTIN.DF(bill.createdFrom) AS PurchaseOrder,
    'Bill Created From Purchase Order' AS ActivityName,
    bill.tranDate AS EventTime,
    BUILTIN.DF(bill.createdBy) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(bill.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS bill
    INNER JOIN Transaction AS po ON bill.createdFrom = po.ID
WHERE
    bill.type = 'VendBill'
    AND po.type = 'PurchOrd'
    AND bill.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    BUILTIN.DF(po.tranId) AS PurchaseOrder,
    'Bill Approved' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(bill.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS bill ON note.recordId = bill.ID
    INNER JOIN transactionLine tl ON bill.id = tl.transaction
    INNER JOIN transaction po ON tl.createdFrom = po.id
WHERE
    bill.type = 'VendBill'
    AND po.type = 'PurchOrd'
    AND note.field = 'TRN_APPROVALSTATUS'
    AND note.newValue = 'Approved'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    BUILTIN.DF(po.tranId) AS PurchaseOrder,
    'Bill Payment Made' AS ActivityName,
    payment.tranDate AS EventTime,
    BUILTIN.DF(payment.createdBy) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(payment.entity) AS VendorName,
    BUILTIN.DF(po.status) AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    Transaction AS payment
    INNER JOIN TransactionLine AS paymentLine ON payment.id = paymentLine.transaction
    INNER JOIN Transaction AS bill ON paymentLine.appliedToTransaction = bill.id
    INNER JOIN TransactionLine AS billLine ON bill.id = billLine.transaction
    INNER JOIN Transaction AS po ON billLine.createdFrom = po.id
WHERE
    payment.type = 'VendPymt'
    AND bill.type = 'VendBill'
    AND po.type = 'PurchOrd'
    AND payment.lastModifiedDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

SELECT
    po.tranId AS PurchaseOrder,
    'Purchase Order Closed' AS ActivityName,
    note.noteDate AS EventTime,
    BUILTIN.DF(note.author) AS UserName,
    BUILTIN.DF(po.department) AS DepartmentName,
    BUILTIN.DF(po.entity) AS VendorName,
    'Closed' AS PurchaseOrderStatus,
    po.total AS TotalAmount,
    BUILTIN.DF(po.class) AS PurchaseCategory,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM
    SystemNote AS note
    INNER JOIN Transaction AS po ON note.recordId = po.ID
WHERE
    po.type = 'PurchOrd'
    AND note.field = 'TRN_STATUS'
    AND note.newValue = 'Closed'
    AND note.noteDate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
"""

def execute_suiteql(query, oauth_session, limit=1000, offset=0):
    class="hl-string">"""Executes a SuiteQL query and returns the JSON response."""
    headers = {
        class="hl-string">'Prefer': class="hl-string">'transient',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    payload = {
        class="hl-string">'q': query,
        class="hl-string">'limit': limit,
        class="hl-string">'offset': offset
    }
    try:
        response = oauth_session.post(suiteql_url, json=payload, headers=headers)
        response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)
        return response.json()
    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"HTTP Error: {e.response.status_code} {e.response.reason}")
        print(fclass="hl-string">"Response body: {e.response.text}")
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Request Error: {e}")
    return None

def main():
    class="hl-string">"""Main function to run the data extraction process."""
    print(class="hl-string">"Starting NetSuite P2P data extraction...")

    class=class="hl-string">"hl-comment"># Set up OAuth1 session
    oauth = OAuth1(
        client_key=CONSUMER_KEY,
        client_secret=CONSUMER_SECRET,
        resource_owner_key=TOKEN_ID,
        resource_owner_secret=TOKEN_SECRET,
        realm=ACCOUNT_ID,
        signature_method=class="hl-string">'HMAC-SHA256'
    )

    all_results = []
    offset = 0
    limit = 1000  class=class="hl-string">"hl-comment"># NetSuite's max page size for SuiteQL
    consecutive_errors = 0

    with requests.Session() as session:
        session.auth = oauth
        while True:
            if consecutive_errors >= MAX_CONSECUTIVE_ERRORS:
                print(fclass="hl-string">"Stopping due to {MAX_CONSECUTIVE_ERRORS} consecutive errors.")
                break

            print(fclass="hl-string">"Fetching records with offset: {offset}...")
            data = execute_suiteql(suiteql_query, session, limit, offset)

            if data and class="hl-string">'items' in data:
                consecutive_errors = 0
                items = data[class="hl-string">'items']
                class=class="hl-string">"hl-comment"># The structure of items is a list of dictionaries with key class="hl-string">'links' and other keys as data
                class=class="hl-string">"hl-comment"># We need to flatten this structure.
                for item in items:
                    flat_item = {k: v for k, v in item.items() if k != class="hl-string">'links'}
                    all_results.append(flat_item)

                if data.get(class="hl-string">'hasMore', False):
                    offset = data.get(class="hl-string">'offset', offset) + data.get(class="hl-string">'count', limit)
                else:
                    print(class="hl-string">"No more records to fetch.")
                    break
            elif data is None: class=class="hl-string">"hl-comment"># An error occurred in execute_suiteql
                consecutive_errors += 1
                print(fclass="hl-string">"Retrying... (Attempt {consecutive_errors})")
            else: class=class="hl-string">"hl-comment"># No items in response, or malformed response
                print(class="hl-string">"No items found in the response or response was malformed. Stopping.")
                break

    if not all_results:
        print(class="hl-string">"Extraction complete, but no data was returned.")
        return

    class=class="hl-string">"hl-comment"># Convert results to a pandas DataFrame
    df = pd.DataFrame(all_results)

    class=class="hl-string">"hl-comment"># Data type conversions and formatting
    if class="hl-string">'eventtime' in df.columns:
        df[class="hl-string">'eventtime'] = pd.to_datetime(df[class="hl-string">'eventtime'])

    if class="hl-string">'lastdataupdate' in df.columns:
        df[class="hl-string">'lastdataupdate'] = pd.to_datetime(df[class="hl-string">'lastdataupdate'])

    class=class="hl-string">"hl-comment"># Rename columns to match the required schema
    column_mapping = {
        class="hl-string">'purchaseorder': class="hl-string">'PurchaseOrder',
        class="hl-string">'activityname': class="hl-string">'ActivityName',
        class="hl-string">'eventtime': class="hl-string">'EventTime',
        class="hl-string">'sourcesystem': class="hl-string">'SourceSystem',
        class="hl-string">'lastdataupdate': class="hl-string">'LastDataUpdate',
        class="hl-string">'username': class="hl-string">'UserName',
        class="hl-string">'departmentname': class="hl-string">'DepartmentName',
        class="hl-string">'vendorname': class="hl-string">'VendorName',
        class="hl-string">'purchaseorderstatus': class="hl-string">'PurchaseOrderStatus',
        class="hl-string">'totalamount': class="hl-string">'TotalAmount',
        class="hl-string">'purchasecategory': class="hl-string">'PurchaseCategory'
    }
    df.rename(columns=column_mapping, inplace=True)
    
    class=class="hl-string">"hl-comment"># Save to CSV
    output_filename = class="hl-string">'netsuite_p2p_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f')
    print(fclass="hl-string">"Successfully extracted {len(df)} events.")
    print(fclass="hl-string">"Event log saved to {output_filename}")

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

Data Template: Purchase to Pay - Purchase Order

Your Purchase to Pay - Purchase Order Data Template

Purchase to Pay - Purchase Order Attributes

Purchase to Pay - Purchase Order Activities

Extraction Guides

NetSuite Saved Search CSV/XLS Export

Steps

Configuration

a Sample Query config

NetSuite SuiteAnalytics Connect Direct SQL Query

Steps

Configuration

a Sample Query sql

Python Script using SuiteTalk REST API and SuiteQL

Steps

Configuration

a Sample Query python

Ready to Get Started?

Streamline Purchase Orders: Boost NetSuite Efficiency Now