Oracle-NetSuite-Order-to-Cash.md
Introduction
With the Oracle NetSuite Order-to-Cash app template you can load raw input data from Oracle NetSuite for the Order-to-Cash process, extracted to either Snowflake or SQl Server 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 Order-to-Cash process apps. Order-to-Cash is the process from receiving and processing sales orders for goods and services to payment. The Order-to-Cash process starts from the order and completes at payment received from the customer.
Oracle NetSuite configuration
The Oracle NetSuite Order-to-Cash 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.
NetSuite and CData Sync Date Matching
In order for filtering on dates to work, the date format in NetSuite and CData Sync must match. Go to NetSuite -> SetUp -> Company -> General Preferences and make note of the date format (MM/DD/YYYY for instance)
In CData Sync, 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)
System specific settings
In NetSuite, go to Setup -> Company -> Enable Features and enable the following in the SuiteCloud tab:
- CLIENT SUITESCRIPT
- SERVER SUITESCRIPT
- SUITESCRIPT SERVER PAGES
- SOAP WEB SERVICES
- REST WEB SERVICES
- TOKEN-BASED AUTHENTICATION
- OAUTH 2.0
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 Connector. Create a token for this role to connect it to CData.
Custom Saved Search Creation
One of the tables that the connector requires 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 connector. Follow these steps in order to create the necessary saved search in NetSuite:
New Saved Search
Go to Lists -> Search -> Saved Searches -> New. Choose Transaction for the Search Type. Under Search Title type in transaction item change log.
Filtering the Search
- Go to Criteria -> Standard and fill in the following:
- Choose Line System Notes Fields... from the dropdown, and choose
Line System Notes: Context. Choose none of and -None-. - This will set the description parameter to be is not none
Columns required for the search
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.
Configuring CData Sync for Oracle Netsuite Order-to-Cash
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 Order-to-Cash 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
SuiteQL is the newer schema that NetSuite uses to interact with the source system data.
Setting up the source connection
- Select NetSuite as the source system to which you want to create a connection from the list and name the connection SuiteQL_Connection.
- The Account Id can be found in the URL for your NetSuite instance: Account Id.app.netsuite.com.
- Select SuiteQL from the Schema drop-down list.
- Select Token from the Auth Scheme options and enter the token credentials from the role created in NetSuite.
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 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.
Table Replication
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 [employee_raw] WITH TruncateTableData = 'True' SELECT [id], [employeetype], [entityid], [title] FROM [employee]; REPLICATE [entity_raw] WITH TruncateTableData = 'True' SELECT [id], [entityid] FROM [entity]; REPLICATE [item_raw] WITH TruncateTableData = 'True' SELECT [id], [fullname] FROM [item]; REPLICATE [location_raw] WITH TruncateTableData = 'True' SELECT [id], [fullname] FROM [location]; REPLICATE [previoustransactionlinelink_raw] WITH TruncateTableData = 'True' SELECT [linktype], [nextdoc], [nextline], [nexttype], [previousdoc], [previousline] FROM [previoustransactionlinelink] WHERE [nexttype] IN ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale'); REPLICATE [systemnote_raw] WITH TruncateTableData = 'True' SELECT [id], [date], [field], [name], [newvalue], [oldvalue], [record], [recordid], [recordtypeid] 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] FROM [term]; REPLICATE [transaction_raw] WITH TruncateTableData = 'True' SELECT [id], [createdby], [createddate], [currency], [duedate], [employee], [exchangerate], [status], [terms], [trandisplayname], [type], [voided] FROM [transaction] WHERE [type] IN ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') AND ([createddate] >= '{env:start_extraction_date}') AND ([createddate] <= '{env:end_extraction_date}'); REPLICATE [transactionhistory_raw] WITH TruncateTableData = 'True' SELECT [action], [datetime], [entity], [internalid], [type], [username] FROM [transactionhistory] WHERE [type] IN ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') AND ([datetime] >= '{env:start_extraction_date}') AND ([datetime] <= '{env:end_extraction_date}'); REPLICATE [transactionline_raw] WITH TruncateTableData = 'True' SELECT DISTINCT [uniquekey], TL.[createdfrom], [department], TL.[entity], [inventorylocation], [item], [itemtype], [linesequencenumber], [location], [netamount], [quantity], [subsidiary], [taxline], [transaction], [units] FROM [transactionline] AS TL JOIN [transaction] as T ON TL.[transaction] = t.[id] WHERE [type] IN ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale'); REPLICATE [unitstypeuom_raw] WITH TruncateTableData = 'True' SELECT [internalid], [unitname] FROM [unitstypeuom];
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 order to cash 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
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.
Setting up the source connection
- Select NetSuite as the source system to which you want to create a connection from the list and name the connection SuiteTalk_Connection.
- The Account Id can be found in the URL for your NetSuite instance: Account Id.app.NetSuite.com.
- Select SuiteTalk from the Schema drop-down list.
- Select Token from the Auth Scheme options and enter the token credentials from the role created in NetSuite.
- On the Custom Schemas tab, add a new CreateRestletSchema schema.
- Enter transaction_item_change_log_raw for the TableName.
- Enter the SearchId, ScriptId, and DeploymentNum and click Create.
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. 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.
Table Replication
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], [Internal_ID_text] as [Internal_ID] FROM [transaction_item_change_log] WHERE ([Date] >= '{env:start_extraction_date}') AND ([Date] <= '{env:end_extraction_date}'); REPLICATE [SalesOrder_raw] WITH TruncateTableData = 'True' SELECT [InternalId], [BillingAddress_Country], [BillingAddress_State], [Department_Name], [Entity_Name], [SalesGroup_Name], [ShippingAddress_Country],[Subsidiary_Name] FROM [SalesOrder];
Input fields
This section contains an overview of the fields for each of the input tables of the Oracle NetSuite Order-to-Cash 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:
- employee
- entity
- item
- location
- systemnote
- term
- transaction
- transactionhistory
- transactionline
- unitstypeuom
- previoustransactionlinelink
The following is a list of tables that are brought in with the NetSuite SuiteTalk Schema specified:
- SalesOrder
- transaction_item_change_log
The 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.
More information can be found on these tables in the NetSuite help center. The Analytics Browser tab is for SuiteQL tables.
employee
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| 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 |
item
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| fullname | Text | Internal ID |
location
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| fullname | Text | Full Name |
previoustransactionlinelink
| 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 ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') |
| previousdoc | Text | Transaction ID of previous document | |
| previousline | Text | Line ID of the previous document line |
systemnote
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| date | date | Date | |
| field | Text | Field where systemnote change occurs | in ('TRANDOC.KSTATUS', 'TRANLINE.MESTAMOUNT', 'TRANDOC.BPAYMENTHOLD', 'TRANDOC.STRANTYPE', 'TRANLINE.RITEMCOUNT', 'TRANLINE.MAMOUNT', 'TRANDOC.MAMOUNTMAIN', 'TRANLINE.RUNITPRICE', 'TRANDOC.DSHIP', 'CUSTBODY_REPORT_TIMESTAMP') |
| name | Text | Owner internal id | |
| newvalue | Text | New value | |
| oldvalue | Text | Old value | |
| record | Text | Record name | |
| recordid | Text | Transaction id | |
| recordtypeid | Text | State | -30 |
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 |
transaction
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| createdby | Text | Created By | |
| createddate | Text | Date Created | |
| currency | Text | Currency | |
| duedate | Text | Due Date | |
| employee | Text | Sales Rep | |
| exchangerate | Text | Exchange Rate | |
| status | Text | Status | |
| terms | Text | Terms | |
| trandisplayname | Text | Transaction Name | |
| type | Text | Type | in ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') |
| voided | Boolean | Voided |
transactionhistory
| Field | Data type | Label | Filtering |
|---|---|---|---|
| internalid | Text | Transaction ID | |
| action | Text | Action | |
| datetime | Datetime | Datetime | |
| entity | Text | Entity | |
| type | Text | Type | in ('SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') |
| username | Text | User |
transactionline
This table is joined to the transaction table in order to filter on transaction."type"
| Field | Data type | Label | Filtering |
|---|---|---|---|
| uniquekey | Text | Unique Key | |
| createdfrom | Text | Created From | |
| department | Text | Department | |
| entity | Text | Entity | |
| inventorylocation | Text | Storage location | |
| item | Text | Item | |
| itemtype | Text | Item Type | |
| linesequencenumber | Text | Line Number | |
| location | Text | Location | |
| netamount | Double | Amount (Net) (Transaction Currency) | |
| quantity | Double | Quantity | |
| subsidiary | Text | Subsidiary | |
| taxline | Text | Tax Line | |
| transaction | Text | Transaction ID | |
| units | Text | Unit ID |
unitstypeuom
| Field | Data type | Label | Filtering |
|---|---|---|---|
| internalid | Text | Internal ID | |
| unitname | Text | Name of the measuring unit |
SalesOrder
| Field | Data type | Label | Filtering |
|---|---|---|---|
| InternalId | Text | Unique ID of the sales order | |
| BillingAddress_Country | Text | Billing country of the customer | |
| BillingAddress_State | Text | Billing state/region of the customer | |
| Department_Name | Text | The display name of the department | |
| Entity_Name | Text | The display name of the customer | |
| SalesGroup_Name | Text | The display name of the sales group | |
| ShippingAddress_Country | Text | Shipping country of the sales order | |
| Subsidiary_Name | Text | The display name of the subsidiary |
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 |
Design specifications
Entities
Below is an overview of the entities, and their attributes, of the Oracle NetSuite Order-to-Cash app template.
Sales_orders_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Sales order ID | SalesOrder."InternalId" | Text | Y | The unique identifier of the sales order. |
| Company | SalesOrder."Subsidiary_Name" | Text | N | The company for which the sales order is created. |
| Customer | SalesOrder."Entity_Name" | Text | N | The customer for whom the sales order is created. |
| Customer country | SalesOrder."BillingAddress_Country" | Text | N | The country associated to the customer. |
| Customer region | SalesOrder."BillingAddress_State" | Text | N | The region associated to the customer. |
| Distribution channel | attribute | Text | N | The distribution channel associated to the sales order. |
| Division | SalesOrder."Department_Name" | Text | N | The division associated to the sales order. |
| Requested delivery date | attribute | Date | N | The customer’s requested delivery date for the sales order. |
| Sales group | SalesOrder."SalesGroup_Name" | Text | N | The sales group associated to the sales order. |
| Sales office | attribute | Text | N | The sales office associated to the sales order. |
| Sales order type | attribute | Text | N | The sales order type of the sales order. |
| Sales organization | attribute | Text | N | The sales organization of the sales order. |
Sales_order_items_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Sales order item ID | transactionline."uniquekey" | Text | Y | The unique identifier of the sales order item. |
| Sales order ID | transactionline."transaction" | Text | Y | The unique identifier of the sales order. |
| Creation date | transaction."createddate | Date | Y | The date on which the sales order item is created. |
| Category | transactionline."itemtype" | Text | N | The category of sales order item. |
| Incoterms | attribute | Text | N | The incoterms associated to the sales order item. |
| Latest expected delivery date | transaction."duedate" | Date | N | The latest expected delivery date of (parts of) the sales order item. |
| Material | items."fullname" | Text | N | The material sold. |
| Material group | transactionline."itemtype" | Text | N | The categorization of the material sold. |
| Payment terms | term."name" | Text | N | The terms of payment associated to the sales order item. |
| Plant | location."fullname" | Text | N | The plant associated to the sales order item. |
| Profit center | attribute | Text | N | The profit center associated to the sales order item. |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', ifnull(unitstypeuom."Unit_name", 'Units')) | Text | N | The quantity and unit of measurement sold. |
| Sales order item | concat(transactions."trandisplayname", '-', transactionlines."lineid") | Text | N | A user-friendly name to identify the sales order item |
| Shipping point country | SalesOrder."ShippingAddress_Country" | Text | N | The country where the goods are delivered associated to the sales order item. |
| Storage location | transactionlines."Inventory_location" | Text | N | Storage location associated to the sales order item. |
| Value | abs(transactionline."netamount" * transaction."exchangerate") | Double | N | A monetary value related to the sales order item. |
Deliveries_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Delivery ID | transaction."id" | Text | Y | The unique identifier of the delivery. |
Delivery_items_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Delivery item ID | transactionline."uniquekey" | Text | Y | The unique identifier of the delivery item. |
| Delivery ID | transactionline."transaction" | Text | N* | The unique identifier of the delivery. |
| Sales order items ID | transactionline."uniquekey" | Text | N* | The unique identifier of the sales order item. |
| Delivery items is invoiced | attribute | Boolean | N | Indicates if the sales order item is released to invoicing. |
Invoices_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Invoice ID | transaction."id" | Text | Y | The unique identifier of the invoice. |
Invoice_items_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Invoice item ID | transactionline."uniquekey" | Text | Y | The unique identifier of the invoice item. |
| Invoice ID | transactionline."transaction" | Text | N* | The unique identifier of the invoice. |
| Delivery item ID | transactionline."uniquekey" | Text | N* | The unique identifier of the delivery item. |
| Invoice item is cancelled | transaction."voided" | Boolean | N | Indicates if the invoice item is cancelled. |
| Invoice item is released into accounting | attribute | Boolean | N | Indicates if the invoice item is billable. |
Invoice_cancellations_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Invoice cancellation ID | transaction."id" | Text | Y | The unique identifier of the invoice cancellation. |
| Invoice ID | transaction."id" | Text | N* | The unique identifier of the invoice that is cancelled. |
Accounting_documents_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Accounting document ID | concat(transactionline."transaction", transactionline."createdfrom") | Text | Y | The unique identifier of the accounting document. |
| Invoice ID | transactionline."createdfrom" | Text | N* | The unique identifier of the invoice. |
| Accounting document is cleared | attribute | Boolean | N | Indicates if the accounting document has been handled and is closed. |
Payments_base
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Payment ID | transaction."id" | Text | Y | The unique identifier of the payment. |
| Accounting document ID | concat(transactionline."transaction", transactionline."createdfrom") | Text | N* | The unique identifier of the accounting document. |
Activities
Create sales order
This identifies the creation action of a sales order.
transactionhistory."action" = 'CREATE' WHERE transactionhistory."context" = 'SalesOrd'
Delete sales order
This identifies the deletion action of a sales order.
transactionhistory."action" = 'DELETE' WHERE transactionhistory."context" = 'SalesOrd'
Create sales order item
This identifies the creation action of a sales order item.
Change sales order item price
This identifies a change to the price of a sales order item.
transaction_item_change_log."Field" = 'Amount'
Change sales order item quantity
This identifies a change to the quantity of a sales order item.
transaction_item_change_log."Field" = 'Quantity'
Change sales order item location
This identifies a change to the location of a sales order item.
transaction_item_change_log."Field" = 'Location'
Close sales order item
This identifies that a sales order item has been closed or voided.
transaction_item_change_log."Field" = 'Closed'
Create customer delivery
This identifies the creation action of a delivery.
transactionhistory."action" = 'CREATE' WHERE transactionhistory."context" = 'ItemShip'
Delete customer delivery
This identifies the deletion action of a delivery.
transactionhistory."action" = 'DELETE' WHERE transactionhistory."context" = 'ItemShip'
Goods picked
This identifies that the goods for this delivery have been picked.
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Picked'
Goods packed
This identifies that the goods for this delivery have been packed.
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Packed'
Goods shipped
This identifies that the goods for this delivery have been shipped.
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Shipped'
Create delivery item
This identifies the creation action of a delivery item.
Change delivery item price
This identifies a change to the delivery item price.
transaction_item_change_log."Field" = 'Amount'
Change delivery item quantity
This identifies a change to the delivery item quantity.
transaction_item_change_log."Field" = 'Quantity'
Change delivery item location
This identifies a change to the delivery item location.
transaction_item_change_log."Field" = 'Location'
Create customer invoice
This identifies the creation action of a customer invoice.
transactionhistory."action" = 'CREATE' WHERE transactionhistory."context" = 'CustInvc'
Delete customer invoice
This identifies the deletion action of a customer invoice.
transactionhistory."action" = 'DELETE' WHERE transactionhistory."context" = 'CustInvc'
Create invoice item
This identifies the creation action of an invoice item.
Change invoice item price
This identifies a change to the price of an invoice item.
transaction_item_change_log."Field" = 'Amount'
Change invoice item quantity
This identifies a change to the quantity of an invoice item.
transaction_item_change_log."Field" = 'Quantity'
Cancel invoice
This identifies the cancellation of an invoice.
systemnote."Field" = 'TRANDOC.KSTATUS' and systemnote."New_value" = 'Voided'
Item returned
This identifies an item return.
transaction_item_change_log."Field" = 'Closed' where transaction_item_change_log."Record_type" = 'returnauthorization'
Receive payment
This identifies payment receipt.
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Paid In Full'
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. Sales order ID, Sales order item ID, etc.).
Customizing the transformations
Configuration
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 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 located in load-from-source for transactionline in order to filter correctly.
DELETE activities like Delete sales 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.
Void activities are built with NetSuite having Void transactions using reversing disabled. In NetSuite, go to Setup -> Accounting -> Accounting Preferences to check.
The line link between delivery items and invoice items is implied, since they link back to the same sales order item line. Staging models Delivery_invoice_relation and Invoice_sales_relation create a rownum to match the delivery item and invoice item, as a sales order item may have multiple deliveries and multiple invoices. This assumes that the first delivery corresponds to the first invoice and so on. If the second delivery is invoiced first, then the relation will be incorrect. We expect this issue to be highly unlikely to occur, but wanted to make users aware of the potentiality.
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 state 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. Some of the input fields of the entities are blank as they are not in the NetSuite system. Sales order type is an example.
ncG1vNJzZmicn5jCrrHNrZitoZ%2Bjvaqv066pnqqVpbxvrsuomWebn6eyb8PIp5uor6Nju6bAjp%2BgpZ2jZJ2zu8KeqqyXfZ67qrrGaGlpamJjfnF7rquYnKSVYpumwLKuoK2dXYS%2FpbHRZquoZXOWwKl7rquYnKSVYpumwLKuoK2dXYS%2FpbHRZquoZXOWwKl6x62kpQ%3D%3D