Oracle-NetSuite-Fixed-Asset-Management.md

Introduction

Fixed Asset Management process

With the Oracle NetSuite Fixed Asset Management app template you can load raw input data from Oracle NetSuite for the Fixed Asset Management 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 Oracle NetSuite Fixed Asset Management process apps.

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 .

Entity relationship diagram

The below is an entity relationship diagram of the lifecycle of assets and activities for the FAM process.

Tags

Due Dates

Oracle NetSuite configuration

The Oracle NetSuite Fixed Asset Management app template requires NetSuite version 2021.2or 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 the official CData Sync documentation. Create a token for this role to connect it to CData.

Configuring CData Sync for Oracle NetSuite Fixed Asset Management

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

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:

<!-- 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 [account_raw] WITH TruncateTableData = 'True' SELECT [id],[accountsearchdisplayname], [accttype], [acctnumber] FROM [account]; REPLICATE [CUSTOMRECORD_NCFAR_ALTDEPRECIATION_raw] WITH TruncateTableData = 'True' SELECT [id],[custrecord_altdeprasset] FROM [CUSTOMRECORD_NCFAR_ALTDEPRECIATION]; REPLICATE [CUSTOMRECORD_NCFAR_ASSETPROPOSAL_raw] WITH TruncateTableData = 'True' SELECT [id], [name], [created], [owner], [custrecord_propasset], [custrecord_propsupplier] FROM [CUSTOMRECORD_NCFAR_ASSETPROPOSAL]; REPLICATE [systemnote_raw] 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_raw] 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_raw] 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_raw] 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.

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.

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.

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.

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:

<!-- Modify environment variables here. --> <!-- Variable start_extraction_date must be populated.--> <api:set attr="out.env:start_extraction_date" value="01/01/2000" /> <!-- 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/2000" --> <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 [FAM_Asset_raw] 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_raw] 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_raw] 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_raw] 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_raw] 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.

NetSuite SuiteQL

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

• CUSTOMRECORD_NCFAR_ASSETPROPOSAL
• CUSTOMRECORD_NCFAR_ALTDEPRECIATION
• nexus
• systemnote
• transaction
• transactionline
• vendor

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

CUSTOMRECORD_NCFAR_ASSETPROPOSAL

CUSTOMRECORD_NCFAR_ALTDEPRECIATION

nexus

systemnote

transaction

transactionline

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

NetSuite SuiteTalk

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

• FAM_Asset
• FAM_Asset_Proposal
• BG_Summary_Records
• FAM_Depreciation_History
• FAM_Alternate_Depreciation

FAM_Asset

FAM_Asset_Proposal

BG_Summary_Records

FAM_Depreciation_History

FAM_Alternate_Depreciation

Design specifications

Entities

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

Cases_base

Asset_proposals

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

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

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.

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.

ncG1vNJzZmicn5jCrrHNrZitoZ%2Bjvaqv066pnqqVpbxvrsuomWebn6eyb8PIp5uor6Nju6bAjp%2BgpZ2jZJ2zu8KeqqyXfZ67qrrGaGlpamJjfnF7rquYnKSVYpumwLKuoK2dXXu2ubHDZnisq5Wpeo6tzZqenqWVo8Fwm9GamqWdXYOytZ%2FUoqueZXaexaawjHqqrJ2kYpqiusCgnKadnql7qcDMpQ%3D%3D