Oracle-NetSuite-Fixed-Asset-Management.md
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 |
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:
- 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 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
- In CData - Connections, add a new source connection for NetSuite. If this option is not in the list, click + Add More, search for NetSuite and click Download & install
- 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.
SuiteTalk
- In CData - Connections, add a new source connection for NetSuite.
- 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.
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.
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
- In CData - Jobs, create a new job.
- Select SuiteQL_Connection as source connection, select the destination connection created and create the job.
Advanced job settings
In the Advanced tab in the Job Settings panel, define the following settings:
- Select the Alter Schema option.
- Select the Drop Table option.
- If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.
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
- In CData - Jobs, create a new job.
- Select SuiteTalk_Connection as source connection, select the destination connection created and create the job.
Advanced job settings
In the Advanced tab in the Job Settings panel, define the following settings:
- Select the Alter Schema option.
- Select the Drop Table option.
- If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.
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:
- CUSTOMRECORD_NCFAR_ASSETPROPOSAL
- CUSTOMRECORD_NCFAR_ALTDEPRECIATION
- nexus
- systemnote
- transaction
- transactionline
- vendor
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
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.
ncG1vNJzZmiZoKXBprnPpZitnaOZvKS%2FjZujqJpemLyzsY2woKecn6zAb7rErWadp5Oquqa605qroqeeZMKqvMCtn395fYOytb%2FUoqueZ3%2BnrqS4xGaFnqyDqra1sYx%2FoLGdlGKOtL%2FErWSGmZ6WtKa5xKerZ6Ckork%3D