Oracle NetSuite Purchase-to-Pay

Extraction

This app template uses Netsuite version 2021.2 or higher as source system. NetSuite has two data model configurations: SuiteTalk and SuiteQL. Both SuiteTalk and SuiteQL schemas are used in this app template and both need to be extracted.

Extraction for the system is done using CData Sync. To set-up the extraction, make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

To be able to connect to Netsuite, certain features need to be enabled in Netsuite and a role should be created with the correct access. Furthermore a custom saved search needs to be created in Netsuite.

Features and user role

In NetSuite, go to Setup -> Company -> Enable Features and enable the following in the SuiteCloud tab:

A role should be created for connecting to NetSuite to extract the data. This role must have the permissions that are listed in CData's documentation for their NetSuite connector. Use a naming convention for the role that would be memorable, like CData Extractor. Create a token for this role to use in the extraction and note down the details.

Custom Saved Search Creation

One of the required tables must be created in NetSuite as a custom saved search. The custom saved search titled transaction item change log utilizes the Line System Notes Fields... in order to bring in line item level changes into the app template. Follow these steps in order to create the necessary saved search in NetSuite:

Go to Lists -> Search -> Saved Searches -> New. Choose Transaction for the Search Type. Under Search Title type in transaction item change log.

Under the Results tab, add the following fields:

Field
Line Unique Key
Transaction Number
Line System Notes: Context
Line System Notes: Date
Line System Notes: Field
Line System Notes: New Value
Line System Notes: Old Value
Line System Notes: Record
Line System Notes: Record ID
Line System Notes: Record Type
Line System Notes: Role
Line System Notes: Set by
Line System Notes: Type
Department
Status
Vendor: Country
Vendor: Company Name
Save & Run

Click Save & Run to save the search and look at the results.

Search_script for RESTlets

A JavaScript file, called search_script.js, must be added to NetSuite in order for RESTlet Saved Search Queries to work. Follow the steps from the CData documentation to set this up to be able to query the transaction item change log Saved Search. After following the steps, note down the SearchId, ScriptId, and DeploymentNum. These are necessary to set up the extraction connection for SuiteTalk.

NetSuite and CData Sync Date Matching

In order for filtering on dates to work, the date format in NetSuite should be used in the extraction configuration. Go to NetSuite -> SetUp -> Company -> General Preferences and make note of the date format (MM/DD/YYYY for instance). When configuring the data extraction job in CData, the dates in the date extraction range need to be specified in the same format.

Setting up the source connection in CData Sync

To be able to extract data from NetSuite, two source connections need to be created in CData Sync, one for SuiteTalk and one for SuiteQL.

SuiteQL

SuiteTalk

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To be able to extract the data from NetSuite, two jobs need to be set up in CData Sync, one using SuiteTalk as source and one using SuiteQL. Both will use the same destination connection.

SuiteQL

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.

Be mindful of choosing a start_extraction_date that encompasses the data that you want to capture, as orders are the starting point of the purchase to pay process.

In order to setup the environment variables:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Pre-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Make sure the date format matches the date format of NetSuite as noted down in the system configuration.
4 Click on Save Changes. 
<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date must be populated.--> 
<api:set attr="out.env:start_extraction_date"  value="01/01/2022" />
<!-- In case a specific end date is needed, replace the value string with the required date in the Date format listed in NetSuite. -->
<!-- i.e api:set attr="out.env:end_extraction_date"  value="01/01/2022" -->
<api:set attr="out.env:end_extraction_date"  value= '12/31/3000' />
<api:push item="out" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Table replication

Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

Use the following custom query for SuiteQL when creating the job:

REPLICATE [accountingperiod] WITH TruncateTableData = 'True' SELECT [id], [isyear], [parent], [periodname] FROM [accountingperiod];
REPLICATE [department] WITH TruncateTableData = 'True' SELECT [id], [fullname], [parent] FROM [department];
REPLICATE [employee] WITH TruncateTableData = 'True' SELECT [id], [department], [employeetype], [entityid], [title] FROM [employee];
REPLICATE [entity] WITH TruncateTableData = 'True' SELECT [id], [entityid], [vendor] FROM [entity];
REPLICATE [entityaddress] WITH TruncateTableData = 'True' SELECT [nkey], [city], [country], [state] FROM [entityaddress];
REPLICATE [item] WITH TruncateTableData = 'True' SELECT [id], [department], [fullname], [itemid], [itemtype], [subtype] FROM [item];
REPLICATE [location] WITH TruncateTableData = 'True' SELECT [id], [fullname], [mainaddress], [parent] FROM [location];
REPLICATE [previoustransactionlinelink] WITH TruncateTableData = 'True' SELECT [linktype], [nextdoc], [nextline], [nexttype], [previousdoc], [previousline] FROM [previoustransactionlinelink] WHERE [nexttype] IN ('VendCred', 'VendBill', 'VendPymt', 'ItemRcpt', 'PurchReq', 'PurchOrd');
REPLICATE [systemnote] WITH TruncateTableData = 'True' SELECT [id], [date], [field], [name], [newvalue], [oldvalue], [record], [recordid], [recordtypeid], [role] FROM [systemnote] WHERE [field] IN ('TRANDOC.KSTATUS', 'TRANLINE.MESTAMOUNT', 'TRANDOC.BPAYMENTHOLD', 'TRANDOC.STRANTYPE', 'TRANLINE.RITEMCOUNT', 'TRANLINE.MAMOUNT', 'TRANDOC.MAMOUNTMAIN', 'TRANLINE.RUNITPRICE', 'TRANDOC.DSHIP', 'CUSTBODY_REPORT_TIMESTAMP', 'TRANDOC.KFORMTEMPLATE') AND ([recordtypeid] = -30) AND ([date] >= '{env:start_extraction_date}') AND ([date] <= '{env:end_extraction_date}');
REPLICATE [term] WITH TruncateTableData = 'True' SELECT [id], [daysuntilexpiry], [daysuntilnetdue], [discountpercent], [name], [preferred] FROM [term];
REPLICATE [transaction] WITH TruncateTableData = 'True' SELECT [id], [approvalstatus], [billingaddress], [billingstatus], [closedate], [createdby], [createddate], [currency], [daysopen], [duedate], [employee], [entity], [exchangerate], [externalid], [foreigntotal], [paymentmethod], [postingperiod], [status], [terms], [trandate], [trandisplayname], [type], [void], [voided] FROM [transaction] WHERE [type] IN ('VendCred', 'VendBill', 'VendPymt', 'ItemRcpt', 'PurchReq', 'PurchOrd') AND ([createddate] >= '{env:start_extraction_date}') AND ([createddate] <= '{env:end_extraction_date}');
REPLICATE [transactionhistory] WITH TruncateTableData = 'True' SELECT [action], [amount], [datetime], [entity], [internalid], [transactiondate], [transactionnumber], [type], [username] FROM [transactionhistory] WHERE [type] IN ('VendCred', 'VendBill', 'VendPymt', 'ItemRcpt', 'PurchReq', 'PurchOrd') AND ([datetime] >= '{env:start_extraction_date}') AND ([datetime] <= '{env:end_extraction_date}');
REPLICATE [transactionline] WITH TruncateTableData = 'True' SELECT DISTINCT TL.[id], TL.[uniquekey], TL.[linelastmodifieddate], TL.[createdfrom], TL.[department], TL.[entity], TL.[expenseaccount], TL.[inventorylocation], TL.[isclosed], TL.[item], TL.[itemtype], TL.[linesequencenumber], TL.[location], TL.[netamount], TL.[quantity], TL.[subsidiary], TL.[taxline], TL.[transaction], TL.[units] FROM [transactionline] AS TL JOIN [transaction] as T ON TL.[transaction] =   T.[id] WHERE T.[type] IN ('VendCred', 'VendBill', 'VendPymt', 'ItemRcpt', 'PurchReq', 'PurchOrd');
REPLICATE [subsidiary] WITH TruncateTableData = 'True' SELECT [id], [country], [fullname], [mainaddress], [name], [parent], [returnaddress], [shippingaddress], [state] FROM [subsidiary];
REPLICATE [unitstypeuom] WITH TruncateTableData = 'True' SELECT [internalid], [unitname] FROM [unitstypeuom];
REPLICATE [vendor] WITH TruncateTableData = 'True' SELECT [id], [balance], [currency], [defaultbillingaddress], [entityid], [terms] FROM [vendor];

Please make sure that the query for transactionline works, as it is a custom case where it is joined to the transaction table in order to filter to only include transaction lines for transactions in the purchase to pay process.

SuiteTalk

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.

Be mindful of choosing a start_extraction_date that encompasses the data that you want to capture, as orders are the starting point of the purchase to pay process.

In order to setup the environment variables:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Pre-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Make sure the date format matches the date format of NetSuite as noted down in the system configuration.
4 Click on Save Changes. 
<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date must be populated.--> 
<api:set attr="out.env:start_extraction_date"  value="01/01/2022" />
<!-- In case a specific end date is needed, replace the value string with the required date in the Date format listed in NetSuite. -->
<!-- i.e api:set attr="out.env:end_extraction_date"  value="01/01/2022" -->
<api:set attr="out.env:end_extraction_date"  value= '12/31/3000' />
<api:push item="out" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Fill out the End of Upload API with the value provided.
4 In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file.
5 Click on Save Changes. 
<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>
Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following query. Make sure you save all changes. Use the following custom query for SuiteTalk when creating the job:

REPLICATE [transaction_item_change_log] WITH TruncateTableData = 'True' SELECT [Line_Unique_Key], [Transaction_Number], [Context], [Date], [Field_text] as [Field], [New_Value], [Old_Value], [Record], [Record_ID], [Record_Type], [Role_text] as [Role], [Set_by_text] as [Set_by], [Type], [Department_text] as [Department], [Status_text] as [Status], [Country_text] as [Country], [Internal_ID_text] as [Internal_ID] FROM [transaction_item_change_log] WHERE ([Date] >= '{env:start_extraction_date}') AND ([Date] <= '{env:end_extraction_date}');

Input fields

This section contains an overview of the fields for each of the input tables of the Oracle NetSuite Purchase-to-Pay app template. For each table the fields are listed. Below is a list of tables that are brought in with the NetSuite SuiteQL Schema specified:

The following is a list of tables that are brought in with the NetSuite SuiteTalk Schema specified:

The transaction_item_change_log table is a custom saved search that has been created for the purpose of bringing line item change history into the transformations.

For more information on these tables see NetSuite help center. The Analytics Browser tab is for SuiteQL tables.

Input tables & fields

accountingperiod

Field Data type Label Filtering
id Text Internal ID
parent Text Sub-period of
isyear Boolean Period is a Year
periodname Text Period Name

department

Field Data type Label Filtering
id Text The unique identifier of the department
fullname Text The display name of the department
parent Text Parent ID

employee

Field Data type Label Filtering
id Text Internal ID
department Text Department
employeetype Text Type
entityid Text Entity ID
title Text Job Title

entity

Field Data type Label Filtering
id Text Internal ID
entityid Text Entity name
vendor Text Vendor ID

entityaddress

This table does not appear in analytics browser.

Field Data type Label Filtering
nkey Text Internal ID
city Text City
country Text Country
state Text State

item

Field Data type Label Filtering
id Text Internal ID
department Text Department
fullname Text Internal ID
itemid Text Full Name
itemtype Text Address
subtype Text Sublocation of

location

Field Data type Label Filtering
id Text Internal ID
fullname Text Full Name
mainaddress Text Address
parent Text Sublocation of
Field Data type Label Filtering
linktype Text Link Type
nextdoc Text Transaction ID of next document
nextline Text Line ID of the next document line
nexttype Text Next document type in ('PurchReq', 'PurchOrd', 'VendBill', 'VendPymt', 'ItemRcpt', 'VendCred')
previousdoc Text Transaction ID of previous document
previousline Text Line ID of the previous document line

subsidiary

Field Data type Label Filtering
id Text Internal ID
country Text Country
fullname Text Full Name
mainaddress Text Address
parent Text SubSubsidiary of
returnaddress Text Return Address
shippingaddress Text Shipping Address
state Text State

systemnote

Field Data type Label Filtering
id Text Internal ID
date date Country
field Text Currency in ('TRANDOC.KSTATUS', 'TRANLINE.MESTAMOUNT', 'TRANDOC.BPAYMENTHOLD', 'TRANDOC.STRANTYPE', 'TRANLINE.RITEMCOUNT', 'TRANLINE.MAMOUNT', 'TRANDOC.MAMOUNTMAIN', 'TRANLINE.RUNITPRICE', 'TRANDOC.DSHIP')
name Text Full Name
newvalue Text Address
oldvalue Text SubSubsidiary of
record Text Return Address
recordid Text Shipping Address
recordtypeid Text State =-30
role Text Address

term

Field Data type Label Filtering
id Text Internal ID
daysuntilexpiry Integer Days Till Discount Expires
daysuntilnetdue Integer Days Till Net Due
discountpercent Double % Discount
name Text Terms
preferred Text Preferred

transaction

Field Data type Label Filtering
id Text Internal ID
approvalstatus Text Approval Status
billingaddress Text Billing Address
billingstatus Text Billing Status
closedate Date Date Closed
createdby Text Created By
createddate Date Date Created
currency Text Currency
daysopen Integer Days Open
duedate Date Due Date
employee Text Sales Rep
entity Text Entity
exchangerate Double Exchange Rate
externalid Text External ID
foreigntotal Double Total Amount (Transaction Currency)
paymentmethod Text Payment Method
postingperiod Text Posting Period
status Text Status
terms Text Terms
trandate Date Date
trandisplayname Text Transaction
type Text Type in ('PurchReq', 'PurchOrd', 'VendBill', 'VendPymt', 'ItemRcpt', 'VendCred')
void Text Void
voided Text Voided

transaction_item_change_log

This is the table created as a custom saved search and connected through the SuiteTalk RESTlet Saved Search Schema.

Field Data type Label Filtering
Line_Unique_Key Text Unique ID of the line item
Transaction_Number Text Transaction document number
Context Text Execution context of the change
Date Datetime Datetime
Field Text Field of change
New_Value Text New value
Old_Value Text Old value
Record Text Record type
Record_ID Text Transaction ID
Record_Type Text One word record type
Role Text Name of the role making the change
Set_by Text Name of user making the change
Internal_ID Text User ID of person making the change
Status Text Status of the transaction
Type Text Change log action type
Department Text Name of the department

transactionhistory

Field Data type Label Filtering
internalid Text Transaction ID
action Text Action
amount Double Price
datetime Datetime Datetime
entity Text Entity
transactiondate Text Transaction Date
transactionnumber Text Transaction Number
type Text Type in ('PurchReq', 'PurchOrd', 'VendBill', 'VendPymt', 'ItemRcpt', 'VendCred')
username Text User

transactionline

This table is joined to the transaction table in order to filter on transaction."type"

Field Data type Label Filtering
id Text Internal ID
uniquekey Text Unique Key
linelastmodifieddate Text Line Last Modified Date
createdfrom Text Created From
department Text Department
entity Text Entity
expenseaccount Text Expense Account
isclosed Boolean Closed
inventorylocation Text Inventory Location
item Text Item
itemtype Text Item Type
linesequencenumber Text Line Number !=0
location Text Location
netamount Double Amount (Net) (Transaction Currency)
quantity Text Quantity
subsidiary Text Subsidiary
taxline Text Tax Line
transaction Text Transaction ID
units Text Units

unitstypeuom

Field Data type Label Filtering
internalid Text Internal ID
unitname Text Name of the measuring unit

vendor

Field Data type Label Filtering
id Text Internal ID
balance Text Balance
currency Text Currency
defaultbillingaddress Text Default Billing Address
entityid Text Entity ID
terms Text Terms

Design specifications

Entities

Below is an overview of the entities, and their attributes, of the Oracle NetSuite Purchase-to-Pay app template.

Purchase_requisitions_base

Name SuiteQL Attribute Data type Description
Purchase requisition ID transactionline."uniquekey" Text The unique identifier of the purchase requisition.
Creation date min(transaction_item_change_log."Date" or transaction."createddate Date The date on which the purchase requisition is created.
Approval status transaction_item_change_log."Status" Text The status of the purchase requisition in the process. For example, ‘open’, ‘closed’, ‘pending’, ‘approved’, etc.
Material item_input."Full_name" Text The requested material.
Material group transactionline."itemtype" Text The categorization of the requested material.
Purchase requisition transaction."trandisplayname" Text A user-friendly name to identify the purchase requisition.
Purchase requisition type systemnote."New_value" Text The categorization of purchase requisitions.
Quantity concat(abs(transactionline."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) Text The quantity and the unit of measurement requested.
Requisitioner employee."entityid" Text The person who requested the purchase requisition.
Value abs(transactionline."Net_amount" * transaction."Exchange_rate") Double A monetary value related to the purchase requisition.

Purchase_orders_base

Name SuiteQL Attribute Data type Description
Purchase order ID transaction."id" Text The unique identifier of the purchase order.
Creation date transaction."createddate" Date The date on which the purchase order is created.
Approval status transaction_item_change_log."Status" Text The status of the purchase order in the process. For example, ‘open’, ‘closed’, ‘pending’, ‘approved’, etc.
Company subsidiary."fullname" Text The company for which the purchase order is created.
Purchase order transaction."trandisplayname" Text A user-friendly name to identify the purchase order.
Purchase order creator employee."entityid" Text The person who created the purchase order.
Purchase order type systemnote."New_value" Text The categorization of purchase orders.
Purchasing group department."fullname" Text The purchasing group associated with the purchase order.
Purchasing organization department."fullname" Text The purchasing organization associated with the purchase order.
Supplier vendor."entityid" Text The supplier associated with the purchase order.
One time supplier null Boolean An indicator if the supplier is a one time supplier.
Supplier country entityaddress."country" Text The country associated to the supplier.
Supplier region entityaddress."state" Text The region associated to the supplier.

Purchase_order_items_base

Name SuiteQL Attribute Data type Description
Purchase order item ID transactionline."uniquekey" Text The unique identifier of the purchase order item
Purchase order ID transaction."id" Text The unique identifier of the purchase order
Purchase requisition ID transactionline."uniquekey" Text The unique identifier of the purchase requisition.
Business area department."fullname" Text The business area associated with the purchase order item.
Cost center null Text The cost center associated with the purchase order item.
Customer null Text The customer on behalf of whom the purchase order item is created.
Customer country null Text The country associated to the customer.
Customer region null Text The region associated to the customer.
Delivery complete transactionline."isclosed" Boolean Indicator if all ordered goods are received.
Latest actual delivery date transaction."trandate" Date The latest actual delivery date for the purchase order item.
Latest expected delivery date transaction."duedate" Date The latest originally confirmed delivery date for the purchase order item.
Material item."Full_name" Text The ordered materials.
Material group transactionline."itemtype" Text The categorization of the ordered materials.
Plant location."fullname" Text The plant associated to the purchase order item.
Purchase order item concat(transaction."Tran_display_name", '-', transactionline."Line_id") Text A user-friendly name to identify the purchase order item.
Quantity concat(abs(transactionline."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) Text The quantity and the unit of measurement ordered.
Storage location transactionline."Inventory_location" Text The storage location associated to the purchase order item.
Value abs(transactionline."Net_amount" * transactionline."Exchange_rate") Double The monetary value related to the purchase order item.

Goods_receipt_base

Name SuiteQL Attribute Data type Description
Goods receipt ID transactionline."uniquekey" Text The unique identifier of the goods receipt.
Purchase order item ID transactionline."uniquekey" Text The unique identifier of the purchase order item.

Invoices_base

Name SuiteQL Attribute Data type Description
Invoice ID transaction."id" Text The unique identifier of the invoice.
Invoice ID (accounting relation) transaction."id" Text The unique identifier of the invoice as known in the accounting system.
Creation date transaction."createddate" Date The date on which invoice was created in the system.
Baseline date transaction."duedate" Date The baseline date for due date calculation of the invoice.
Company vendor."entityid" Text The company associated with the invoice.
Discount percentage 1 term."discountpercent" Double The discount percentage for discount period 1.
Discount percentage 2 null Double The discount percentage for discount period 2.
Discount period 1 term."daysuntilexpiry" Integer The payment period for which discount percentage 1 applies.
Discount period 2 null Integer The payment period for which discount percentage 2 applies.
Fiscal year accountingperiod."periodname" Text The fiscal year that the invoice belongs to.
Invoice transaction."trandisplayname" Text A user-friendly name to identify the invoice.
Invoice creator employee."entityid" Text The person who created the invoice.
Invoice date transaction."trandate" Date The date on which the received invoice was created.
Invoice type systemnote."New_value" Text Categorization of invoices.
Net payment period term."daysuntilnetdue" Integer The net payment period for the invoice in days.
Payment method transaction."paymentmethod" Text The payment method of the invoice.
Payment terms term."name" Text The payment terms applicable for the invoice.
Posting date transaction."trandate" Date The date on which the invoice was posted (accounting).

Invoice_items_base

Name SuiteQL Attribute Data type Description
Invoice Item ID transactionline."uniquekey" Text The unique identifier of the invoice item.
Invoice ID transactionline."transaction" Text The unique identifier of the invoice.
Purchase order item ID transactionline."uniquekey" Text The unique identifier of the purchase order item.
Invoice item concat(transaction."Tran_display_name", ' - ', transactionline."Line_id") Text A user-friendly name to identify the invoice item.
Material transactionline."item" Text The material invoiced.
Plant location."fullname" Text The plant associated to the invoice item.
Quantity concat(abs(transactionline."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) Text The quantity and the unit of measure for the invoice item.
Value abs(transactionline."Net_amount" * transactionline."Exchange_rate") Double The monetary value of the invoice item.

Accounting_documents_base

Name SuiteQL Attribute Data type Description
Accounting document ID concat(transactionline."Transaction", transactionline."Created_from") Text The unique identifier of the accounting document.
Invoice ID transactionline."createdfrom" Text The unique identifier of the invoice as known in the accounting system.

Payments_base

Name SuiteQL Attribute Data type Description
Payment ID transaction."id" Text The unique identifier of the payment.
Accounting document ID concat(transactionline."Transaction", transactionline."Created_from") Text The unique identifier of the accounting document.
Payment is complete transaction."billingstatus" Boolean An indicator if the payment is a full clearing

Activities

Create purchase requisition item

This identifies the creation action of a purchase requisition item.

Change requisition item price

This identifies a change to the price of a purchase requisition item.

transaction_item_change_log."Field" = 'Amount'

Change requisition item quantity

This identifies a change to the quantity of a purchase requisition item.

transaction_item_change_log."Field" = 'Quantity'

Create purchase order item

This identifies the creation action of a purchase order item.

Change purchase order item price

This identifies a change to the price of a purchase order item.

transaction_item_change_log."Field" = 'Amount'

Change purchase order item quantity

This identifies a change to the quantity of a purchase order item.

transaction_item_change_log."Field" = 'Quantity'

Create purchase order

This identifies the creation action of a purchase order item.

Filter transactionhistory on "type" = 'PurchOrd' and transactionhistory."action" = 'CREATE'

Delete purchase order

This identifies the deletion action of a purchase order. Filter transactionhistory on "type" = 'PurchOrd' and transactionhistory."action" = 'DELETE'

Close purchase order

This identifies a closed purchase order.

systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Closed'

Approve purchase order

This identifies the approval action of a purchase order

systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."oldvalue" = 'Pending Receipt' and systemnote."newvalue" = 'Pending Bill'

Goods received

This identifies the item receipt, or goods receipt.

transactionhistory."action" = 'CREATE'

Cancel goods receipt

This identifies an item receipt, or good receipt, being canceled.

transactionhistory."action" = 'DELETE'

Enter invoice

This identifies the creation action of an invoice.

Filter transactionhistory on "type" = 'VendBill' and transactionhistory."action" = 'CREATE'

Delete invoice

This identifies the deletion action of an invoice.

Filter transactionhistory on "type" = 'VendBill' and transactionhistory."action" = 'DELETE'

Hold payment

This identifies that "Hold Payment" has been checked on an invoice.

systemnote."field" = 'TRANDOC.BPAYMENTHOLD' and systemnote."newvalue" = 'T'

Create invoice item

This identifies the creation of an invoice item

Change invoice item price

This identifies a change to the invoice item price.

transaction_item_change_log."Field" = 'Amount'

Use the custom saved search transaction_item_change_log for the required data.

Change invoice item quantity

This identifies a change to the invoice item quantity.

transaction_item_change_log."Field" = 'Quantity'

Use the custom saved search transaction_item_change_log for the required data.

Create vendor credit

This identifies the creation action of a vendor credit.

Filter transactionhistory on "type" = 'VendCred' and transactionhistory."action" = 'CREATE'

Delete vendor credit

This identifies the deletion action of a vendor credit.

Filter transactionhistory on "type" = 'VendCred' and transactionhistory."action" = 'DELETE'

Create outgoing payment

This identifies that a bill payment has been created

Filter systemnote on "record" like 'Bill P%' and systemnote."field" = 'TRANDOC.STRANTYPE' and systemnote."newvalue" = 'Bill Payment'

Void payment

This identifies that a bill payment has been voided.

Filter systemnote on "record" like 'Bill P%' and systemnote."field" = ' TRANDOC.KSTATUS' and systemnote."newvalue" = 'Voided'

Events_base

The model Events_base, located in the 3_events directory, is a union of all the outputs from the other models in the 3_events directory. Each event is unique and corresponds to one ID (i.e. Purchase order ID, Purchase order item ID, etc.).

Customizing the transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Field Type Description
Activity Text Display name for the activity
Event_cost Double Cost associated with the activity
Event_processing_time Integer Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Field Type Description
Due_date Text The name of the due date
Due_date_type Text The Due date type
Fixed_costs Boolean An indication whether costs are fixed or time based
Cost Double Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time Integer A number indicating the amount of time in case of time-based costs
Time_type Text Type of time period for cost calculations. This can be any of the following values: day, hour, minute, second or millisecond

Automated Users

A variable exists in dbt_project.yml called Automated_users for users to specify the automated users that exist in the Entity table. Please add the entity.id for these users to this variable.

Limitations, known issues, common problems

Limitations

NetSuite SuiteQL relies on some incredibly large tables for its data, which can be filtered by specifying the record types required. Not filtering on these can cause a lot of performance issues, and will replicate a lot of superfluous data.

If you plan on using CSV files and are going to be using the SQL Query Editor for exporting the SuiteQL tables, the SQL Query Editor tool only allows for up to 5,000 rows to be exported in each query. If more rows are needed, one can filter based on date, and add the results together into a larger file.

Filtering the transactionline table is important, as it can be a rather large table if one does not. However, this requires joining the transactionline and transaction tables in order to filter transactionline to only include the transaction types that are listed in the filtering above. Please use the custom query for the CData job for transactionline in order to filter correctly.

DELETE activities like Delete purchase order or Delete invoice will only appear in the process graph if the case has been deleted after the initial data has been pulled. Deleted orders and invoices will appear in the transactionhistory table, but will be removed from the transaction, transactionline, systemnote, and transaction_item_change_log tables.

Known issues

NetSuite is structurally built around the header level, so tracking item level changes requires utilizing a mixture of SuiteTalk and SuiteQL Schemas to find and pull the requisite data for process mining.

If you encounter errors when running a job that states column not found, go to the Advanced tab of the NetSuite Connection and set the Row Scan Depth: to 0

Common problems

CData Sync may exhibit some issues in filtering the extraction tables on multiple values, so this is something to keep in mind if you do encounter similar issues. Minor changes to the filtering clause may cause it to work correctly if this issue is encountered.