Oracle NetSuite Fixed Asset Management

Process description

Fixed Asset Management process

The Fixed Asset Management Process is about the process of proposing an Asset, then creating this Asset and following its use, depreciation, location transfers, revaluations, impairments/repairs, through to its eventual disposal. More information on the fixed asset management process in NetSuite can be found in this FAM handbook .

Available tags & due dates

The following tags are available by default.

Tag Description
Current NBV higher than previous year NBV Assets."Current_net_book_value" > Assets."Prior_year_nbv"
Asset creation time more than 30 days {{ pm_utils.datediff('day', 'asset_proposal_to_asset_event_ends."Proposal_event_end"', 'asset_proposal_to_asset_event_ends."Creation_event_end"') }} > 30
Asset does not have asset proposal Asset_proposals."custrecord_propasset" is null
Write down account different to write off account Assets."Write_down_account" != Assets."Write_off_account"
Fully depreciated asset still in use Assets."Asset_status" = 'Fully depreciated'

The following due dates are available by default.

Due Date Description
Last Depreciation Date Different to Depreciation End Date Last depreciation date occurs on a different date to the depreciation end date that is specified

Extraction

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

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

System configuration

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

Features and user role

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

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

NetSuite and CData Sync Date Matching

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

Setting up the source connection in CData Sync

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

SuiteQL

SuiteTalk

Setting up the destination connection in CData Sync

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

Creating the extraction job in CData Sync

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

SuiteQL

Advanced job settings

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

Setting up environment variables in the extraction job

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

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

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

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

In order to setup the environment variables:

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

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

Table replication

Once the job is correctly setup, click on Add Custom Query under the 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 [account] WITH TruncateTableData = 'True' SELECT [id],[accountsearchdisplayname], [accttype], [acctnumber] FROM [account]; REPLICATE [CUSTOMRECORD_NCFAR_ALTDEPRECIATION] WITH TruncateTableData = 'True' SELECT [id],[custrecord_altdeprasset] FROM [CUSTOMRECORD_NCFAR_ALTDEPRECIATION]; REPLICATE [CUSTOMRECORD_NCFAR_ASSETPROPOSAL] WITH TruncateTableData = 'True' SELECT [id], [name], [created], [owner], [custrecord_propasset], [custrecord_propsupplier] FROM [CUSTOMRECORD_NCFAR_ASSETPROPOSAL]; REPLICATE [systemnote] WITH TruncateTableData = 'True' SELECT * FROM [systemnote] WHERE ([field] IN ('CUSTRECORD_PROPASSET', 'CUSTRECORD_PROPSTATUS', 'CUSTRECORD_PROPSOURCEID', 'CUSTRECORD_DEPRHISTDATE', 'CUSTRECORD_ASSETSTATUS', 'CUSTRECORD_ASSETMAINTNEXTDATE', 'CUSTRECORD_ASSETTYPE', 'TRANDOC.KSTATUS', 'TRANDOC.KFORMTEMPLATE', 'TRANDOC.STRANTYPE', 'TRANDOC.NKEY', 'CUSTRECORD_ASSETSOURCETRN')) AND ([date] >= '{env:start_extraction_date}') AND ([date] <= '{env:end_extraction_date}'); REPLICATE [transaction] WITH TruncateTableData = 'True' SELECT [id], [createdby], [createddate], [currency], [duedate], [employee], [entity], [exchangerate], [nexus], [status], [terms], [trandisplayname], [type], [voided] FROM [transaction] WHERE [type] IN ('PurchOrd', 'Journal', 'VendBill', 'Custom') AND ([createddate] >= '{env:start_extraction_date}') AND ([createddate] <= '{env:end_extraction_date}'); REPLICATE [transactionline] WITH TruncateTableData = 'True' SELECT DISTINCT TL.[id], [uniquekey], TL.[createdfrom], [department], TL.[expenseaccount], TL.[entity], [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 ('PurchOrd', 'Journal', 'VendBill', 'Custom'); REPLICATE [nexus] WITH TruncateTableData = 'True' SELECT [id], [description] FROM [nexus];

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 Fixed Asset Management process.

SuiteTalk

Advanced job settings

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

Setting up environment variables in the extraction job

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

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

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

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

In order to setup the environment variables:

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

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

Setting up the data ingestion call

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

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

Once the job is correctly setup, click on Add Custom Query under the 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 [FAM_Asset] WITH TruncateTableData = 'True' SELECT [InternalId], [Asset Current Cost] AS [Asset_current_cost], [Asset Original Cost] AS [Asset_original_cost], [Asset Is Leased] AS [Asset_is_leased], [Next Inspection Date] AS [Next_inspection_date], [Last Inspection Date] AS [Last_inspection_date], [Lease Company] AS [Lease_company], [Asset Status] AS [Asset_status], [Asset Type] AS [Asset_type], [Component Of] AS [Component_of], [Cumulative Depreciation] AS [Cumulative_depreciation], [Current Net Book Value] AS [Current_net_book_value], [CustomRecordId], [Depreciation Account] AS [Depreciation_account], [Depreciation Charge Account] AS [Depreciation_charge_account], [Depreciation End Date] AS [Depreciation_end_date], [Depreciation Method] AS [Depreciation_method], [Disposal Cost Account] AS [Disposal_cost_account], [Maintenance Company] AS [Maintenance_company], [Name], [Owner_InternalId], [Owner_Name], [Parent Asset] AS [Parent_asset], [Parent Transaction] AS [Parent_transaction], [Parent Transaction Line] AS [Parent_transaction_line], [Prior Year NBV] AS [Prior_year_nbv], [Quantity], [Residual Value] AS [Residual_value], [Custodian], [Location], [Subsidiary], [Supplier], [Write Down Account] AS [Write_down_account], [Write Off Account] AS [Write_off_account] FROM [FAM Asset] AS [FAM_Asset]; REPLICATE [BG_Summary_Records] WITH TruncateTableData = 'True' SELECT [InternalId], [Depreciation Date] AS [Depreciation_date], [Name], [Journal Internal Id] AS [Journal_internal_ID] FROM [BG Summary Records] AS [BG_Summary_Records]; REPLICATE [FAM_Depreciation_History] WITH TruncateTableData = 'True' SELECT [InternalId], [Alternate Method] AS [Alternate_method], [Transaction Type] AS [Transaction_type], [Asset], [Date], [Subsidiary], [Owner_Name], [Owner_InternalId], [Depreciation Period] AS [Depreciation_period], [Name], [Posting Reference] AS [Posting_reference], [Transaction Amount] AS [Transaction_amount] FROM [FAM Depreciation History] AS [FAM_Depreciation_History]; REPLICATE [FAM_Asset_Proposal] WITH TruncateTableData = 'True' SELECT [InternalId], [Accounting Method] AS [Accounting_method], [Asset], [Asset Type] AS [Asset_type], [Custodian], [Depreciation Account] AS [Depreciation_account], [Disposal Cost Account] AS [Disposal_cost_account], [Location], [Source Transaction] AS [Source_transaction], [Write Down Account] AS [Write_down_account], [Write Off Account] AS [Write_off_account], [Proposal Status] AS [Proposal_status], [Original Cost] AS [Original_cost], [Parent Proposal] AS [Parent_proposal], [Quantity], [Accounting Method] AS [Accounting_method], [Custodian], [Owner_Name], [Subsidiary], [Supplier], [Source Transaction] AS [Source_transaction] FROM [FAM Asset Proposal] AS [FAM_Asset_Proposal]; REPLICATE [FAM_Alternate_Depreciation] WITH TruncateTableData = 'True' SELECT [InternalId], [Accounting Book] AS [Accounting_book], [Alternate Method] AS [Alternate_method], [Cumulative Depreciation] AS [Cumulative_depreciation], [Book Value (NBV)] AS [Book_value], [Current Cost] AS [Current_cost], [Original Cost] AS [Original_cost], [Residual Value (RV)] SA [Residual_value], [Depreciation Account] AS [Depreciation_account], [Depreciation Charge Account] AS [Depreciation_charge_account], [Depreciation Method] AS [Depreciation_method], [Depreciation Start Date] AS [Depreciation_start_date], [Depreciation End Date] AS [Depreciation_end_date], [Disposal Cost Account] AS [Disposal_cost_account], [Prior Year NBV] AS [Prior_year_nbv], [Residual Value Percentage] AS [Residual_value_percentage], [Write Down Account] AS [Write_down_account], [Write Off Account] AS [Write_off_account] FROM [FAM Alternate Depreciation] AS [FAM_Alternate_Depreciation];

Input fields

This section contains an overview of the fields for each of the input tables of the Oracle NetSuite Fixed Asset Management app template. For each table the fields are listed.

Below is a list of tables that are brought in with the NetSuite SuiteQL Schema specified:

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

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

Input tables & fields

CUSTOMRECORD_NCFAR_ASSETPROPOSAL

Field Data type Label Filtering
id Text Internal id
custrecord_propasset Text Asset id
owncustrecord_propsupplier Text Proposed Supplier
owner Text owner
created Date Created Date

CUSTOMRECORD_NCFAR_ALTDEPRECIATION

Field Data type Label Filtering
custrecord_altdeprasset Text Asset Name
id Text Asset id

nexus

Field Data type Label Filtering
id Text Internal id
description Text Tax location description

systemnote

Field Data type Label Filtering
id Text Internal ID
date Date Date
field Text Field where systemnote change occur in ('CUSTRECORD_PROPASSET', 'CUSTRECORD_PROPSTATUS', 'CUSTRECORD_PROPSOURCEID', 'CUSTRECORD_DEPRHISTDATE', 'CUSTRECORD_ASSETSTATUS', 'CUSTRECORD_ASSETMAINTNEXTDATE', 'CUSTRECORD_ASSETTYPE', 'TRANDOC.KSTATUS', 'TRANDOC.KFORMTEMPLATE', 'TRANDOC.STRANTYPE', 'TRANDOC.NKEY', 'CUSTRECORD_ASSETSOURCETRN')
name Text Owner internal id
newvalue Text New value
oldvalue Text Old value
record Text Record name
recordid Text Transaction id
role Text User role
recordtypeid Text Record type

transaction

Field Data type Label Filtering
id Text Internal ID
createdby Text Created By
createddate Date Date Created
currency Text Currency
duedate Date Due Date
exchangerate Double Exchange Rate
nexus Text Nexus ID
status Text Status
trandisplayname Text Transaction
type Text Type in ('PurchOrd', 'Journal', 'VendBill', 'Custom')
voided Boolean Voided

transactionline

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

Field Data type Label Filtering
id Text Internal ID
uniquekey Text Unique Key
createdfrom Text Created From
expenseaccount Text Expense Account
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

FAM_Asset

Field Data type Label Filtering
Internal_id Text Internal id
Next_inspection_date Date Next inspection date
Last_inspection_date Date Last inspection date
Asset_current_cost Double Asset current value
Asset_original_cost Double Asset original value
Asset_is_leased Boolean Asset is leased or not
Asset_status Text Asset status
Asset_type Text Assert type
Component_of Text Component of parent asset (compound assets)
Cumulative_depreciation Double Total asset depreciation
Current_net_book_value Double Current asset net book value
Custom_record_id Text Custom asset id
Depreciation_account Text Depreciation account
Depreciation_charge_account Text Depreciation charge account
Depreciation_end_date Date Depreciation end date
Depreciation_method Text Depreciation method
Disposal_cost_account Text Disposal account
Lease_company Text Lease company
Maintenance_company Text Maintenance company
Name Text Asset Name
Owner_name Text Owner name
Owner_internal_id Text Owner internal id
Parent_asset Text Parent asset
Parent_transaction Text Parent transaction name
Parent_transaction_line Text Line number of parent transaction
Prior_year_nbv Double Prior year net book value
Quantity Integer Quantity
Residual_value Double Residual value of asset after depreciation
Custodian Text Responsible party
Location Text Location
Subsidiary Text Subsidiary
Supplier Text Supplier
Write_down_account Text Write-down account
Write_off_account Text Write-off account

FAM_Asset_Proposal

Field Data type Label Filtering
Internal_id Text Internal id
Asset Text Asset name
Asset_type Text Asset type
Location Text Location
Supplier Text Supplier
Subsidiary Text Subsidiary
Depreciation_account Text Depreciation account
Disposal_cost_account Text Disposal account
Source_transaction Text Parent transaction
Write_down_account Text Write-down account
Write_off_account Text Write-off account
Proposal_status Text Status
Original_cost Double Original cost
Parent_proposal Text Parent asset proposal (compound assets)
Quantity Double Quantity
Accounting_method Text Accounting method
Custodian Text Responsible party
Owner_name Text Creator

BG_Summary_Records

Field Data type Label Filtering
Internal_id Text Internal ID
Depreciation_date Date Depreciation date
Journal_internal_id Text Journal transaction ID
Name Text BG Summary Record Name

FAM_Depreciation_History

Field Data type Label Filtering
Internal_ID Text Internal ID
Alternate_method Text Alternate Depreciation Method
Transaction_type Text Transaction type
Transaction_amount Double Transaction Amount
Asset Text Asset name
Date Date Date
Subsidiary Text Subsidiary
Owner_name Text Creator
Owner_internal_ID Text Creator internal ID
Posting_reference Text Depreciation Posting Reference
Depreciation_preiod Text Depreciation period
Name Text Unique Name of Depreciation Journal (Script Name)

FAM_Alternate_Depreciation

Field Data type Label Filtering
Internal_ID Text Internal ID
Accounting_book Text Accounting book
Alternate_method Text Alternate method
Cumulative_depreciation Double Cumulative depreciation
Book_value Double Asset book value
Current_costs Double Asset current cost
Original_costs Double Asset original cost
Residual_value Double Residual value
Depreciation_account Text Depreciation account
Depreciation_charge_account Text Depreciation charge account
Depreciation_method Text Depreciation method
Depreciation_start_date Date Depreciation start date
Depreciation_end_date Date Depreciation end date
Disposal_cost_account Text Disposal account
Prior_year_nbv Double Prior year net book value
Residual_value_percentage Double Residual value percentage
Write_down_account Text Write-down account
Write_off_account Text Write-off account

Design specifications

Objects

Below is an overview of the objects, and their attributes, of the Oracle NetSuite Fixed Asset Management app template.

Asset_proposals

Name Attribute Data type Mandatory Y/N Description
Asset_proposal_ID coalesce(Asset_input."Custom_record_id", concat('PROP-00',Aggregated_proposals."id") ) Text Y Unique identifier of asset or asset proposal
Asset_proposal coalesce(Asset_input."Custom_record_id", concat('PROP-00',Aggregated_proposals."id") ) Text N Display name of asset or asset proposal
Name coalesce(Asset_input."Name", Aggregated_proposals."Asset") Text N Alternate display name of asset or asset proposal
Type coalesce(Asset_input."Asset_type", Aggregated_proposals."Asset_type") Text N Asset or asset proposal type
Status coalesce(Asset_input."Asset_status", Aggregated_proposals."Proposal_status") Text N Asset or asset proposal status
Book_value coalesce(Asset_input."Current_Net_Book_Value", Aggregated_proposals."Original_cost") Double N Asset or asset proposal value
Parent_transaction coalesce(Asset_input."Parent_transaction", Aggregated_proposals."Source_transaction") Text N Asset or asset proposal parent transaction
Location coalesce(Asset_input."Location", Aggregated_proposals."Location") Text N Asset or asset proposal parent transaction
Maintenance_company Asset_input."Maintenance_company" Text N Asset or asset proposal parent transaction
Parent coalesce(Asset_input."Parent_asset", Aggregated_proposals."Parent_proposal") Text N Asset or asset proposal parent asset
Parent_asset Asset_input."Component_of" Text N Asset or asset proposal parent compound asset
Quantity coalesce(Asset_input."Quantity", Aggregated_proposals."Quantity") Text N Asset or asset proposal quantity
Subsidiary coalesce(Asset_input."Subsidiary", Aggregated_proposals."Subsidiary") Text N Asset or asset proposal subsidiary
Supplier coalesce(Asset_input."Supplier", Aggregated_proposals."Supplier") Text N Asset or asset proposal supplier
Write_down_account coalesce(Asset_input."Write_down_account", Aggregated_proposals."Write_down_account") Text N Asset or asset proposal write down account
Write_off_account coalesce(Asset_input."Write_off_account", Aggregated_proposals."Write_off_account") Text N Asset or asset proposal write off account
Depreciation_account coalesce(Asset_input."Depreciation_account", Aggregated_proposals."Depreciation_account") Text N Asset or asset proposal depreciation account
Depreciation_method case when Alternate_depreciations."Alternate_method" is null then coalesce(Asset_input."Depreciation_method", Aggregated_proposals."Accounting_method")else Alternate_depreciations."Alternate_method" Text N Asset or asset proposal depreciation method
Custodian coalesce(Asset_input."Depreciation_account", Aggregated_proposals."Depreciation_account") Text N Asset or asset proposal custodian
Alternate Depreciation Method coalesce(Alternate_depreciations."Alternate_method") Text N Asset Alternate Depreciation Method
Alternate_current_cost Alternate_depreciations."Current_costs" Text N Alternate asset Current Cost
Alternate_original_cost Alternate_depreciations."Original_costs" Text N Alternate asset Original Cost
Alternate_book_value Alternate_depreciations."Book_value" Text N Alternate asset Book Value (sale as Asset proposal value)
Disposal Account coalesce(Asset_input."Disposal_cost_account", Aggregated_proposals."Disposal_cost_account") Text N Asset or asset Disposal Account
Leasing_company Asset_input."Lease_company" Text N Asset Leasing Company
Residual_value Asset_input."Residual_value" Text N Asset Residual Value
Asset_original_cost Asset_input."Asset_original_cost" Text N Asset Original Cost
Asset_current_cost Asset_input."Asset_original_cost" Text N Asset current Cost
Cumulative_depreciation Asset_input."Cumulative_depreciation" Text N Asset Cumulative Depreciation

Aggregated_proposals

Aggregated_proposals is a combination of SuiteQL and SuiteTalk Asset proposal inputs to create a singular object with requisite fields.

Name Attribute Data type Mandatory Y/N Description
ID SuiteQL_proposals."ID" Text Y Unique identifier of the asset proposal
Custrecord_propasset SuiteQL_proposals."Custrecord_propasset" Text Y Relation of the asset proposal to asset
Asset SuiteTalk_proposals."Asset" Text N Name of the asset proposal
Asset_type SuiteTalk_proposals."Asset_type" Text N Asset proposal type
Location SuiteTalk_proposals."Location" Text N Proposed asset location
Supplier SuiteTalk_proposals."Supplier" Text N Supplier of proposed asset
Subsidiary SuiteTalk_proposals."Subsidiary" Text N Subsidiary requesting asset proposal
Depreciation_account SuiteTalk_proposals."Depreciation_account" Text N Asset proposal depreciation account
Source_transaction SuiteTalk_proposals."Source_transaction" Text N Parent transaction of asset proposal
Write_down_account SuiteTalk_proposals."Write_down_account" Text N Asset proposal write-down account
Write_off_account SuiteTalk_proposals."Write_off_account" Text N Asset proposal write-off account
Proposal_status SuiteTalk_proposals."Proposal_status" Text N Asset proposal status
Original_cost SuiteTalk_proposals."Original_cost" Text N Original cost of asset proposal
Parent_proposal SuiteTalk_proposals."Parent_proposal" Text N Parent of asset proposal
Quantity SuiteTalk_proposals."Quantity" Text N Asset proposal quantity
Accounting_method SuiteTalk_proposals."Accounting_method" Text N Asset proposal accounting method
Custodian SuiteTalk_proposals."Custodian" Text N Asset proposal custodian
Owner_name SuiteTalk_proposals."Owner_name" Text N Owner of asset proposal
Disposal_cost_account SuiteTalk_proposals."Disposal_cost_account" Text N Disposal account of asset proposal

Activities

A break down of the activities is listed below, based on each model that contains the activity:

Acquire Asset

This identifies the acquisition action of an asset.

Acquire Asset via Transfer

This identifies the movement of an asset to another location.

Create Asset

This identifies the creation of an asset from asset proposal.

Systemnote_input."New_value" = 'New'

Fully Depreciated Asset

This identifies a an asset's full depreciation.

Systemnote_input."New_value" = 'Fully Depreciated'

Splitting Asset

This identifies an asset being split.

Systemnote_input."New_value" = 'Splitting'

Depreciate Asset

This identifies the depreciation of an asset.

Depreciation_history."Transaction_type" = 'Depreciation'

Depreciate Asset (tax)

This identifies the alternate tax depreciation of an asset.

Depreciation_history."Transaction_type" = 'Depreciation' where Depreciation_history."Alternate_method" is not null

Dispose Asset

This identifies the disposal of an asset.

Journal Entry - Debit

This identifies a debit journal entry.

when transactionline."Net_amount" < 0

Journal Entry - Credit

This identifies a credit journal entry.

when transactionline."Net_amount" > 0

Proposed manual depreciation

This identifies a manual depreciation.

systemnote."Field" = 'TRANDOC.KSTATUS' and systemnote."New_value" = 'Pending Approval'

Approved manual depreciation

This identifies the approval of the manual depreciation.

systemnote."Field" = 'TRANDOC.KSTATUS' and systemnote."New_value" = 'Approved'

Propose Asset

This identifies the creation of an asset proposal.

Systemnote_input."New_value" = 'New'

Reject Asset

This identifies the rejection of an asset proposal.

Systemnote_input."New_value" = 'Rejected'

Revalue Asset

This identifies the revaluation, or write-down, of an asset.

Depreciation_history where "Transaction_type" = 'Write-down'

Transfer Asset

This identifies the transfer of an asset.

Depreciation_history where "Transaction_type" = 'Transfer'


Customizing the transformations

Seed files

Automation_estimates_raw

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

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

Due_dates_configuration_raw

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

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

Automated Users

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


Limitations, known issues, common problems

Limitations

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

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

Void activities are built with NetSuite having Void transactions using reversing disabled. In NetSuite, go to Setup -> Accounting -> Accounting Preferences to check.

The Asset ID used in events is created as left(Depreciation_history."Asset", 9). Please make sure that the number of characters required to capture the asset ID is 9, or this will need to be changed to grab the correct Asset ID.

When exporting data directly from NetSuite, -System- and (-4), the Owner_Name and Owner_InternalId respectively, do not appear. This will not capture automated actions as this is not present in the data. If you would like to have blank values appear as "Automated", add the following wherever Owner_InternalId is used for defining "Automated_Users":

case when Depreciation_history."Owner_internal_id" is null then {{ pm_utils.to_boolean('true') }}

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.