With the Oracle NetSuite Purchase-to-Pay app template you can load raw input data from Oracle NetSuite for the Purchase-to-Pay process using CData Sync. The raw input data is transformed through a series of transformations that take place via dbt (data build tool) and produce the required input data used in Process Mining Purchase-to-Pay process apps.
The Oracle NetSuite Purchase-to-Pay app template requires NetSuite version 2021.2 or higher. NetSuite has two data model configurations: SuiteTalk and SuiteQL. Both SuiteTalk and SuiteQL schemas are used in this app template.
Important: You need a valid Oracle NetSuite license. NetSuite requires a specific role, which is explained below in the system specific settings.
In order for filtering on dates to work, the date format in NetSuite and CData must match. Go to NetSuite -> SetUp -> Company -> General Preferences and make note of the date format (MM/DD/YYYY for instance).
In CData, go to the NetSuite Connection, then to the Advanced tab, and make sure that Net Suite Date Format: is set to the same as above (MM/DD/YYYY for instance)
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 the official CData Sync documentation. Use a naming convention for the role that would be memorable, like CData Connector. Create a token for this role to connect it to CData.
If purchase requisitions are not part of the company Purchase to Pay process, this app template will still work without errors. Purchase_requisitions_base, Purchase_requisition_create_events, and Purchase_requisition_change_events will be created, but they will be empty.
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.
Line System Notes: Context. Choose none of and -None-.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 |
Click Save & Run to save the search and look at the results.
Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Oracle NetSuite Purchase-to-Pay process app.
In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using Oracle NetSuite, pay attention to the steps described below.
As NetSuite has two schema types: SuiteQL and SuiteTalk, it will be required to create two connections, with one for each schema type. Both connections have their own custom queries, which are listed below.
SuiteQL is the newer schema that NetSuite uses to interact with the source system data.
Use this source connection when creating the extraction job in CData.
Important: Make sure you edit the Pre-job Event.
CData 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 order to cash process.
In order to setup the environment variables:
| Step | Action |
|---|---|
| 1 | Access the job |
| 2 | Click on the Events tab. |
| 3 | Edit the Pre-Job Event section to add the code displayed below |
<!-- 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" />
Important: Do not modify the api:info details that are shown by default.
In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.
Once the job is correctly setup, click on Add Custom Query under the Tables 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_raw] WITH TruncateTableData = 'True' SELECT [id], [isyear], [parent], [periodname] FROM [accountingperiod];
REPLICATE [department_raw] WITH TruncateTableData = 'True' SELECT [id], [fullname], [parent] FROM [department];
REPLICATE [employee_raw] WITH TruncateTableData = 'True' SELECT [id], [department], [employeetype], [entityid], [title] FROM [employee];
REPLICATE [entity_raw] WITH TruncateTableData = 'True' SELECT [id], [entityid], [vendor] FROM [entity];
REPLICATE [entityaddress_raw] WITH TruncateTableData = 'True' SELECT [nkey], [city], [country], [state] FROM [entityaddress];
REPLICATE [item_raw] WITH TruncateTableData = 'True' SELECT [id], [department], [fullname], [itemid], [itemtype], [subtype] FROM [item];
REPLICATE [location_raw] WITH TruncateTableData = 'True' SELECT [id], [fullname], [mainaddress], [parent] FROM [location];
REPLICATE [previoustransactionlinelink_raw] WITH TruncateTableData = 'True' SELECT [linktype], [nextdoc], [nextline], [nexttype], [previousdoc], [previousline] FROM [previoustransactionlinelink] WHERE [nexttype] IN ('VendCred', 'VendBill', 'VendPymt', 'ItemRcpt', 'PurchReq', 'PurchOrd');
REPLICATE [systemnote_raw] 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_raw] WITH TruncateTableData = 'True' SELECT [id], [daysuntilexpiry], [daysuntilnetdue], [discountpercent], [name], [preferred] FROM [term];
REPLICATE [transaction_raw] 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_raw] 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_raw] 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_raw] WITH TruncateTableData = 'True' SELECT [id], [country], [fullname], [mainaddress], [name], [parent], [returnaddress], [shippingaddress], [state] FROM [subsidiary];
REPLICATE [unitstypeuom_raw] WITH TruncateTableData = 'True' SELECT [internalid], [unitname] FROM [unitstypeuom];
REPLICATE [vendor_raw] 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.
Following these steps will set up all the requirements for the SuiteQL connection tables. Next, a connection to SuiteTalk will be covered to bring in the required custom saved search.
SuiteTalk is the schema required to bring in saved searches, and custom saved searches.
In order for the custom saved search to work, the steps to allow for CData to find RESTlet schemas must be followed first. These directions are listed in the Search_script for RESTlets section below. The custom saved search, transaction_item_change_log must also be created first in the NetSuite instance in order for it to be found and connected with CData.
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. This will allow for the Custom Saved Search transaction_item_change_log to be used.
Important: Make sure you edit the Pre-job Event. CData 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 order to cash process.
In order to setup the environment variables:
| Step | Action |
|---|---|
| 1 | Access the job created in the previous step. |
| 2 | Click on the Events tab. |
| 3 | Add the following lines to the Pre-Job Event script. |
<!-- 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" />
Important: Do not modify the api:info details that are shown by default.
In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.
Once the job is correctly setup, click on Add Custom Query under the Tables 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_raw] 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}');
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:
For more information on these tables see NetSuite help center. The Analytics Browser tab is for SuiteQL tables.
transaction_item_change_log is a custom saved search that has been created for the purpose of bringing line item change history into the transformations.
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| parent | Text | Sub-period of | |
| isyear | Boolean | Period is a Year | |
| periodname | Text | Period Name |
| 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 |
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| department | Text | Department | |
| employeetype | Text | Type | |
| entityid | Text | Entity ID | |
| title | Text | Job Title |
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| entityid | Text | Entity name | |
| vendor | Text | Vendor ID |
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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
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 |
This table is joined to the transaction table in order to filter on transaction."type"
Use the query provided in the above section Configuring CData Sync for Oracle NetSuite Purchase-to-Pay in order to filter this correctly.
| 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 |
| Field | Data type | Label | Filtering |
|---|---|---|---|
| internalid | Text | Internal ID | |
| unitname | Text | Name of the measuring unit |
| 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 |
Below is an overview of the entities, and their attributes, of the Oracle NetSuite Purchase-to-Pay app template.
| 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. |
| 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. |
| 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. |
| 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. |
| 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). |
| 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. |
| 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. |
| 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 |
This identifies the creation action of a purchase requisition item.
This identifies a change to the price of a purchase requisition item.
transaction_item_change_log."Field" = 'Amount'
This identifies a change to the quantity of a purchase requisition item.
transaction_item_change_log."Field" = 'Quantity'
This identifies the creation action of a purchase order item.
This identifies a change to the price of a purchase order item.
transaction_item_change_log."Field" = 'Amount'
This identifies a change to the quantity of a purchase order item.
transaction_item_change_log."Field" = 'Quantity'
This identifies the creation action of a purchase order item.
Filter transactionhistory on "type" = 'PurchOrd' and transactionhistory."action" = 'CREATE'
This identifies the deletion action of a purchase order.
Filter transactionhistory on "type" = 'PurchOrd' and transactionhistory."action" = 'DELETE'
This identifies a closed purchase order.
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Closed'
This identifies the approval action of a purchase order
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."oldvalue" = 'Pending Receipt' and systemnote."newvalue" = 'Pending Bill'
This identifies the item receipt, or goods receipt.
transactionhistory."action" = 'CREATE'
This identifies an item receipt, or good receipt, being canceled.
transactionhistory."action" = 'DELETE'
This identifies the creation action of an invoice.
Filter transactionhistory on "type" = 'VendBill' and transactionhistory."action" = 'CREATE'
This identifies the deletion action of an invoice.
Filter transactionhistory on "type" = 'VendBill' and transactionhistory."action" = 'DELETE'
This identifies that "Hold Payment" has been checked on an invoice.
systemnote."field" = 'TRANDOC.BPAYMENTHOLD' and systemnote."newvalue" = 'T'
This identifies the creation of an invoice item
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.
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.
This identifies the creation action of a vendor credit.
Filter transactionhistory on "type" = 'VendCred' and transactionhistory."action" = 'CREATE'
This identifies the deletion action of a vendor credit.
Filter transactionhistory on "type" = 'VendCred' and transactionhistory."action" = 'DELETE'
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'
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'
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.).
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.
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 with either Snowflake or SQL Server, 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.
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
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.