The entities that are involved in the Q2C process for TemplateOne are: quote items, sales order items, delivery items, invoice items, and payments.
A user creates a quote with a series of quote items, which are converted into a sales order with a series of sales order items that are being purchased by a customer. Once approved, these sales orders are sent to order fulfillment, where delivery of the items is planned, scheduled, and executed. These items are then invoiced to the customer, with payments then collected around pre-defined payment terms.
The below is an entity relationship diagram of the entities used in the creation of the events for the Q2C process.
| Tag | Description |
|---|---|
| Throughput time more than 30 days | max(event_end) - (min event_end) > 30 per case id |
| Quote item price differs from sales order item price | quote_item."price" != sales_order_item."price" |
| Total invoice item prices differs from sales order item price | sales_order_item."price" != sum(invoice_item."Price") |
| Change in sales order item price | Quote_item_event_log."Activity" = 'Change sales order item price' |
| Change in sales order item quantity | Quote_item_event_log."Activity" = 'Change sales order item quantity' |
| Partial goods delivery | sales_order_item."Quantity" > sum(delivery_item."Quantity") |
| Due Date | Description |
|---|---|
| Payment due date | Difference between invoice item due date and create payment date |
The Oracle NetSuite Quote 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.
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 CData's documentation. Create a token for this role to connect it to CData.
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.
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
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 |
| User: Internal ID |
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 Quote to Cash process app template.
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. 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.
| Step | Action |
|---|---|
| 1 | Define a New Connection of type NetSuite and name the connection SuiteQL_Connection. |
| 2 | Select Auth Scheme = Token and enter the token credentials from the role created in NetSuite |
| 3 | Regarding Account Id, this can be found in the URL for your NetSuite instance: Account Id.app.netsuite.com |
| 4 | Under the Advanced tab, set the Schema to SuiteQL |
| Step | Action |
|---|---|
| 1 | Define a New Connection of type SQL Server or Snowflake. |
| 2 | Setup connection details as required. |
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. |
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 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], [department], [employeetype], [entityid], [title] FROM [employee];
REPLICATE [Entity_raw] WITH TruncateTableData = 'True' SELECT [id], [entityid], [vendor] FROM [entity];
REPLICATE [Item_raw] WITH TruncateTableData = 'True' SELECT [id], [department], [fullname], [itemid], [itemtype], [subtype] FROM [item];
REPLICATE [Previoustransactionlinelink_raw] WITH TruncateTableData = 'True' SELECT [linktype], [nextdoc], [nextline], [nexttype], [previousdoc], [previousline] FROM [previoustransactionlinelink] WHERE [nexttype] IN ('Estimate', 'SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale');
REPLICATE [Systemnote_raw] WITH TruncateTableData = 'True' SELECT * 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 [Transaction_raw] WITH TruncateTableData = 'True' SELECT [id], [approvalstatus], [billingaddress], [billingstatus], [closedate], [createdby], [createddate], [currency], [daysopen], [daysoverduesearch], [duedate], [employee], [entity], [exchangerate], [externalid], [foreigntotal], [paymentmethod], [postingperiod], [status], [terms], [trandate], [trandisplayname], [type], [void], [voided] FROM [transaction] WHERE [type] IN ('Estimate', 'SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') AND ([createddate] >= '{env:start_extraction_date}') AND ([createddate] <= '{env:end_extraction_date}');
REPLICATE [Transactionline_raw] WITH TruncateTableData = 'True' SELECT DISTINCT TL.[id], [uniquekey], TL.[linelastmodifieddate], TL.[createdfrom], [department], TL.[entity], [expenseaccount], [inventorylocation], [isclosed], [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 ('Estimate', 'SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale');
REPLICATE [Unitstypeuom_raw] WITH TruncateTableData = 'True' SELECT [internalid], [unitname] FROM [unitstypeuom];
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.
| Step | Action |
|---|---|
| 1 | Define a New Connection of type NetSuite and name the connection SuiteTalk_Connection. |
| 2 | Select Auth Scheme = Token and enter the token credentials from the role created in NetSuite |
| 3 | Regarding Account Id, this can be found in the URL for your NetSuite instance: Account Id.app.netsuite.com |
| 4 | Under the Advanced tab, set the Schema to SuiteTalk |
| 5 | Under the Custom Schemas tab, click Add Schema and choose CreateRestletSchema |
| 6 | Enter Transaction_item_change_log_raw for the TableName |
| 7 | Enter the SearchId, ScriptId, and DeploymentNum and click Create |
| Step | Action |
|---|---|
| 1 | Define a New Connection of type SQL Server or Snowflake. |
| 2 | Setup connection details as required. |
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. |
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], [Company_Name], [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 Quote to Cash app template. For each table the fields are listed.
The following is a list of tables that are brought in with the NetSuite SuiteQL Schema specified:
More information can be found on these tables in the NetSuite help center. The Analytics Browser tab is for SuiteQL tables.
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| currency | Text | Currency | |
| 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 |
| Field | Data type | Label | Filtering |
|---|---|---|---|
| id | Text | Internal ID | |
| cost | Double | Cost | |
| department | Text | Department | |
| fullname | Text | Internal ID | |
| itemid | Text | Full Name | |
| itemtype | Text | Address | |
| subtype | 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 ('Estimate', 'SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') |
| 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 | |
| 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', 'CUSTBODY_REPORT_TIMESTAMP') |
| 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 | |
| lastmodifieddate | Date | Date Last Modified | |
| approvalstatus | Text | Approval Status | |
| billingaddress | Text | Billing Address | |
| billingstatus | Text | Billing Status | |
| closedate | Text | Date Closed | |
| createdby | Text | Created By | |
| createddate | Text | Date Created | |
| currency | Text | Currency | |
| daysopen | Text | Days Open | |
| daysoverduesearch | Text | Days Overdue | |
| duedate | Text | Due Date | |
| employee | Text | Sales Rep | |
| entity | Text | Entity | |
| exchangerate | Text | Exchange Rate | |
| externalid | Text | External ID | |
| foreigntotal | Text | 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 ('Estimate', 'SalesOrd', 'CustPymt', 'ItemShip', 'Deposit', 'CustInvc', 'RtnAuth', 'CashSale') |
| void | Text | Void | |
| voided | Text | Voided |
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 | |
| inventorylocation | Text | Storage location | |
| isclosed | Text | Closed | |
| item | Text | Item | |
| itemtype | Text | Item Type | |
| linesequencenumber | Text | Line Number | |
| location | Text | Location | |
| netamount | Text | Amount (Net) (Transaction Currency) | |
| quantity | Text | Quantity | |
| subsidiary | Text | Subsidiary | |
| taxline | Text | Tax Line | |
| transaction | Text | Transaction ID | |
| units | Text | Unit ID |
| Field | Data type | Label | Filtering |
|---|---|---|---|
| internalid | Text | Internal ID | |
| unitname | Text | Name of the measuring unit |
The following is a list of tables that are brought in with the NetSuite SuiteTalk Schema specified:
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.
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 | 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 | Text | Name of the role making the change | |
| Set_by_text | Text | Name of user making the change | |
| Set_by_value | Text | User ID of person making the change | |
| Status_text | Text | Status of the transaction | |
| Type | Text | Change log action type | |
| Department_text | Text | Name of the department |
Below is an overview of the entities, and their attributes, of the Oracle NetSuite Quote to Cash app template.
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Case_ID | Quote_items."Quote_item_id" |
Text | Y | Unique identifier of quote item |
| Case | concat(transactions."Tran_display_name", '-', transactionlines."Line_id") |
Text | N | Display name of quote item |
| Case_status | transaction_item_change_log."Status" |
Text | N | Quote item status |
| Case_type | systemnote_order_types_input."New_value" |
Text | N | Quote item order type |
| Case_value | Quote_items."Price" |
Integer | N | Quote item value |
| Quote_item_quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | N | Quote item quantity and unit type |
| Created_by | Quote_items."Creator" |
Text | N | Quote item creator |
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Quote_item_ID | transactionlines."Unique_key" |
Text | Y | Unique identifier of the quote item |
| Line_id | transactionlines."Line_id" |
Text | Y | Identifier of the line in the quote transaction |
| Quote_ID | transactionlines."Transaction" |
Text | Y | Unique identifier of the quote |
| Created_at | transactions."Created_date" |
Date | Y | Creation date |
| Creator | employee."Entity_id" |
Text | Y | Employee name |
| Price | abs(transactionlines."Net_amount") |
Double | Y | Value of the quote item |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | Y | Quote item quantity and unit |
| Status | transaction_item_change_log."Status" |
Text | Y | Quote status |
| Team | employee."Department" |
Text | Y | Employee team |
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Quote_item_ID | transactionlines."Unique_key" |
Text | Y | Unique identifier of the quote item |
| Line_id | transactionlines."Line_id" |
Text | Y | Identifier of the line in the quote transaction |
| Quote_ID | transactionlines."Transaction" |
Text | Y | Unique identifier of the quote |
| Created_at | transactions."Created_date" |
Date | Y | Creation date |
| Creator | employee."Entity_id" |
Text | Y | Employee name |
| Price | abs(transactionlines."Net_amount") |
Double | Y | Value of the quote item |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | Y | Quote item quantity and unit |
| Status | transaction_item_change_log."Status" |
Text | Y | Quote status |
| Team | employee."Department" |
Text | Y | Employee team |
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Quote_item_ID | transactionlines."Unique_key" |
Text | Y | Unique identifier of the quote item |
| Line_id | transactionlines."Line_id" |
Text | Y | Identifier of the line in the quote transaction |
| Quote_ID | transactionlines."Transaction" |
Text | Y | Unique identifier of the quote |
| Created_at | transactions."Created_date" |
Date | Y | Creation date |
| Creator | employee."Entity_id" |
Text | Y | Employee name |
| Price | abs(transactionlines."Net_amount") |
Double | Y | Value of the quote item |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | Y | Quote item quantity and unit |
| Status | transaction_item_change_log."Status" |
Text | Y | Quote status |
| Team | employee."Department" |
Text | Y | Employee team |
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Quote_item_ID | transactionlines."Unique_key" |
Text | Y | Unique identifier of the quote item |
| Line_id | transactionlines."Line_id" |
Text | Y | Identifier of the line in the quote transaction |
| Quote_ID | transactionlines."Transaction" |
Text | Y | Unique identifier of the quote |
| Created_at | transactions."Created_date" |
Date | Y | Creation date |
| Creator | employee."Entity_id" |
Text | Y | Employee name |
| Price | abs(transactionlines."Net_amount") |
Double | Y | Value of the quote item |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | Y | Quote item quantity and unit |
| Status | transaction_item_change_log."Status" |
Text | Y | Quote status |
| Team | employee."Department" |
Text | Y | Employee team |
| Name | Attribute | Data type | Mandatory Y/N | Description |
|---|---|---|---|---|
| Quote_item_ID | transactionlines."Unique_key" |
Text | Y | Unique identifier of the quote item |
| Line_id | transactionlines."Line_id" |
Text | Y | Identifier of the line in the quote transaction |
| Quote_ID | transactionlines."Transaction" |
Text | Y | Unique identifier of the quote |
| Created_at | transactions."Created_date" |
Date | Y | Creation date |
| Creator | employee."Entity_id" |
Text | Y | Employee name |
| Price | abs(transactionlines."Net_amount") |
Double | Y | Value of the quote item |
| Quantity | concat(abs(transactionlines."Quantity"), ' ', coalesce(unitstypeuom."Unit_name", 'Units')) |
Text | Y | Quote item quantity and unit |
| Status | transaction_item_change_log."Status" |
Text | Y | Quote status |
| Team | employee."Department" |
Text | Y | Employee team |
A break down of the activities is listed below, based on each model that contains the activity:
This identifies the creation action of a quote item
transaction_item_change_log."Field" = 'Amount'
This identifies a change to the price of a quote item
transaction_item_change_log."Field" = 'Quantity'
This identifies a change to the quantity of a quote item
transaction_item_change_log."Status" = 'Closed'
This identifies that a quote item has been canceled or voided
This identifies the creation action of a sales order item
transaction_item_change_log."Field" = 'Amount'
This identifies a change to the price of a sales order item
transaction_item_change_log."Field" = 'Quantity'
This identifies a change to the quantity of a sales order item
transaction_item_change_log."Status" = 'Closed'
This identifies that a sales order item has been canceled or voided
This identifies the creation action of a delivery item
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Picked'
This identifies that the goods for this delivery item have been picked
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Packed'
This identifies that the goods for this delivery item have been packed
systemnote."field" = 'TRANDOC.KSTATUS' and systemnote."newvalue" = 'Shipped'
This identifies that the goods for this delivery item have been shipped
This identifies the creation action of an invoice item
transaction_item_change_log."Field" = 'Amount'
This identifies a change to the price of an invoice item
transaction_item_change_log."Field" = 'Quantity'
This identifies a change to the quantity of an invoice item
systemnote."Field" = 'TRANDOC.KSTATUS' and systemnote."New_value" = 'Voided'
This identifies the cancellation of an invoice, which is applied to the individual invoice items
This identifies payment creation
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 sales order or Delete invoice will remove the transaction, and subsequent transaction lines, 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.
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 in CData 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.