Asset Import Guide - Revision history

Freddy at 14:57, 24 May 2019

2019-05-24T14:57:28Z

Freddy at 19:37, 22 May 2019

2019-05-22T19:37:55Z

Freddy at 19:34, 22 May 2019

2019-05-22T19:34:21Z

Freddy at 18:12, 22 May 2019

2019-05-22T18:12:51Z

Freddy at 18:04, 22 May 2019

2019-05-22T18:04:22Z

Freddy at 16:12, 22 May 2019

2019-05-22T16:12:32Z

Freddy: Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

2019-05-22T15:45:24Z

Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

New page

==General Data Import Notes==

Refer to the [[Data Import General Information]] page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.

==Asset Import Notes==

===Timing and Preparation===

The Asset Import can occur any time after all customers have been imported, all Inventory Owners have been established, and Item Master records have been imported. Workflow Routes, Asset Type definitions, and Departments should also be set up prior to importing Assets if the customer intends to use those features with Assets.

===General Notes===

The Asset Tag ID is the key value used to check for duplicate records. The Asset Import will not create or updates duplicate records.

If using asset depreciation options, the 'DEPSCH' Asset Attribute must be established in the Asset Attributes (ATT_ASSET) Rule Maintenance record and tied to the 'ASSETDEP' form in the rule.

==Asset Import File Data Scrubbing==

Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing. This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Sales Order data scrubbing include:

* '''Dates''' - Make sure all dates follow the MM/DD/YY format before importing. Also review each date column and work with the customer to ensure the right dates are being imported in the right columns.

* '''TagID''' - The 'TAGID' column establishes the unique key (along with the database keyno) for the imported assets. Review the file carefully for duplicates, blank entries, or incomplete data.

* '''Depreciation Fields''' - If using, review the depreciation values carefully with the customer and confirm that the correct methods and values are being imported.

==Asset Import Screen (IMPORTASSET)==

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Asset Import

[[File:IMPORTASSET.png]]

'''File Name/Browse:''' Use the Browse button to locate and select the completed Asset Import template file (in XLS format).

'''Export Template:''' Generates a blank Asset Import template file

'''Map Fields:''' Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.

==Asset Import File Definitions==

Required fields are indicated with an *

'''f1 - CurLoc''' - Organization number for the current location of the asset

'''f2 - Owner''' - Asset Owner organization number

'''f3 - BusUse''' - Business Use percentage value as a whole number. For example, for 50% business use, enter '50'.

'''f4 - EDate''' - Project End Date. Must be formatted as a date in MM/DD/YY format. This is the second of the two date fields under the Ship To field in the Project Master tab.

'''f5 - CompDate''' - Project Completion Date. Must be formatted as a date in MM/DD/YY format.

'''f6 - ProdDate''' - Production/Fab Date. Must be formatted as a date in MM/DD/YY format.

'''f7 - AddDate''' - Project Add/Creation Date. Must be formatted as a date in MM/DD/YY format. If left blank, the added date will be set to the system date at the time of import.

'''*f8 - Whse''' - Warehouse name for the project. Must match a warehouse name that has been set up in the Warehouses screen.

'''f9 - PM''' - Name of the Project Manager. Must match the full name of a contact in Adjutant with the Project Manager attribute.

'''f10 - ShortNote''' - A note or extended description of the project. Updates the Project Master Remarks area. 200 characters max.

'''f11 - BillType''' - Project Billing Type. Must match a value in the Project Billing Types (BILLTYPE) rule.

'''f12 - BillAmount''' - The amount that has been billed on the project.

'''f13 - AccrAmount''' - The current accrual amount on the project.

'''f14 - ContractAmt''' - The contract amount of the project.

'''*f15 - TCode''' - Project Type. Must match a value in the Project Types (PROJTYPE) rule.

'''*f16 - SCode''' - Project Status. Must match a value in the Project Status (PROJSTATUS) rule.

'''f17 - AccMgr''' - Name of the Account Manager. Must match the full name of a contact in Adjutant with the Account Manager attribute.

'''f18 - ContractDate''' - Contract Date. Must be formatted as a date in MM/DD/YY format.

'''f19 - SP''' - Name of the Salesperson . Must match the full name of a contact in Adjutant with the Salesperson attribute.

'''f20 - CompFlag''' - Completion status of the project. Use a 'y' if the project is complete and a 'n' if it is still active. '''Must be entered as a lowercase letter.'''

'''f21 - RevNum''' - Revision number of the project.

'''f22 - OnHold''' - On hold status of the project. Use a 'y' if the project is on hold and a 'n' if it is not. '''Must be entered as a lowercase letter.'''

'''*f23 - SoldTo''' - Organization ID of the customer.

'''*f24 - ShipTo''' - Organization ID of the shipping/work location.

'''f25 through f31 - Custno1/2/3/4''' - Organization ID of other companies linked to the project.

'''f26 through f32 - Attrib1/2/3/4''' - Attribute code that signifies the relationship of the other companies linked to the project. Use the Attribute Code from text1 of the Organization Attributes rule - i.e SOLDTO, SHIPTO, BILLTO, REMITTO, SOLDFROM, etc.

==Project Import Reconciliation==

Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.

Run the Project List report to select the imported records. Review the report for basic details and look for any missing or incorrect records.

==Project Import Additional Steps==

If Project Phases need to be imported, run the Project Phase Import.

==Project Import Database Tables==

'''MSN''' - Each imported project creates a new KEYNO in the this table. The MSNNUM value holds the project number.

'''MSNPHASE''' - Each imported project will create an entry in this table for a default phase. The KEYNO value of the default phase will be linked to the MSNID value which should match the MSN table KEYNO value for the associated project.

'''MSNCUST''' - All organization IDs linked to the project are saved in this table as CUSTIDs with a unique KEYNO value for each. They are linked to the MSN table by KEYNOH which will match the MSN KEYNO.

[[Category: Item Control]]
[[Category: Import Guides]]

@@ Line 47: / Line 47: @@
 ==Asset Import File Definitions==
 '''CurLoc''' - Organization number for the current location of the asset
@@ Line 62: / Line 64: @@
 '''BasedOn''' - Item code that the asset records is based on. Must be an existing item code from Item Master.
-'''TagID''' - Asset Tag ID (30 characters max, alphanumeric)
+'''*TagID''' - Asset Tag ID (30 characters max, alphanumeric)
-'''Serial''' - Serial Number 1 for the asset (30 characters max, alphanumeric)
+'''*Serial''' - Serial Number 1 for the asset (30 characters max, alphanumeric). If no serial number is available, copy the TagID value to this column.
-'''Name''' - Asset Name (30 characters max, alphanumeric)
+'''*Name''' - Asset Name (30 characters max, alphanumeric)
 '''Descrip''' - Longer description of asset (240 characters max)

← Older revision		Revision as of 19:37, 22 May 2019
Line 35:		Line 35:
	Menu Location: Transaction>>Importers/Exporters>>Item Control>>Asset Import		Menu Location: Transaction>>Importers/Exporters>>Item Control>>Asset Import

−	[[File:~~IMPORTASSET~~.png]]	+	[[File:IMPORTASSET1.png]]

@@ Line 17: / Line 17: @@
 If using asset depreciation options, the 'DEPSCH' Asset Attribute must be established in the Asset Attributes (ATT_ASSET) Rule Maintenance record and tied to the 'ASSETDEP' form in the rule.
@@ Line 46: / Line 47: @@
 ==Asset Import File Definitions==
 '''CurLoc''' - Organization number for the current location of the asset
@@ Line 99: / Line 98: @@
 '''FedMeth''' - Federal Depreciation Method code. Valid entries are 'SL', 'DB200', 'DB150', 'SY', and 'CDB'. SL = Straight LIne. DB200 = Double Declining Balance 2X. DB150 = Double Declining Balance 1.5X. SY = Sum of Years Digits.
-'''FedConv''' - Federal Deprecitaion Table COnvention. Valid entries are
+'''FedConv''' - Federal Depreciation Table Convention. Valid entries are 'Half-Year', 'Quarter', 'Mid Month', and 'Full Month'.
-'''f25 through f31 - Custno1/2/3/4''' - Organization ID of other companies linked to the project.
+'''AMTCost''' - Alternative Minimum Tax Asset Cost in dollars, two decimals allowed
-'''f26 through f32 - Attrib1/2/3/4''' - Attribute code that signifies the relationship of the other companies linked to the project. Use the Attribute Code from text1 of the Organization Attributes rule - i.e SOLDTO, SHIPTO, BILLTO, REMITTO, SOLDFROM, etc.
+'''AMTSalvage''' - Alternative Minimum Tax Salvage value in dollars, two decimals allowed
-Run the Project List report to select the imported records. Review the report for basic details and look for any missing or incorrect records.
+==Asset Import Reconciliation==
-'''MSNPHASE''' - Each imported project will create an entry in this table for a default phase. The KEYNO value of the default phase will be linked to the MSNID value which should match the MSN table KEYNO value for the associated project.
+==Asset Import Database Tables==
-'''MSNCUST''' - All organization IDs linked to the project are saved in this table as CUSTIDs with a unique KEYNO value for each. They are linked to the MSN table by KEYNOH which will match the MSN KEYNO.
+'''RTASSET''' - Each imported asset creates a new KEYNO in the this table. The TAGID value holds the asset ID value.
 [[Category: Item Control]]
 [[Category: Import Guides]]

@@ Line 91: / Line 91: @@
 '''Route4''' - Optional workflow route name from the Route Maintenance screen
-'''FedLife''' -
+'''FedLife''' - Federal Depreciation Life/Period in years
-'''FedCost''' -
+'''FedCost''' - Federal Asset Cost in dollars, two decimals allowed
-'''FedSalvage''' -
+'''FedSalvage''' - Federal Salvage value in dollars, two decimals allowed
-'''FedMeth''' -
+'''FedMeth''' - Federal Depreciation Method code. Valid entries are 'SL', 'DB200', 'DB150', 'SY', and 'CDB'. SL = Straight LIne. DB200 = Double Declining Balance 2X. DB150 = Double Declining Balance 1.5X. SY = Sum of Years Digits.
-'''FedConv''' -
+'''FedConv''' - Federal Deprecitaion Table COnvention. Valid entries are

@@ Line 49: / Line 49: @@
 Required fields are indicated with an *
-'''f1 - CurLoc''' - Organization number for the current location of the asset
+'''CurLoc''' - Organization number for the current location of the asset
-'''f2 - Owner''' - Asset Owner organization number
+'''Owner''' - Asset Owner organization number
-'''f3 - BusUse''' - Business Use percentage value as a whole number. For example, for 50% business use, enter '50'.
+'''BusUse''' - Business Use percentage value as a whole number. For example, for 50% business use, enter '50'.
-'''f4 - Note''' - Project End Date. Must be formatted as a date in MM/DD/YY format. This is the second of the two date fields under the Ship To field in the Project Master tab.
+'''Note''' - This field is not in use
-'''f5 - CompDate''' - Project Completion Date. Must be formatted as a date in MM/DD/YY format.
+'''AssetDep''' - Enter 'y' to add the Depreciation Schedule attribute for this record
-'''f6 - ProdDate''' - Production/Fab Date. Must be formatted as a date in MM/DD/YY format.
+'''Dept''' - Department name for this asset. Entries must exactly match the name value from the Departments (DEPT) rule.
-'''f7 - AddDate''' - Project Add/Creation Date. Must be formatted as a date in MM/DD/YY format. If left blank, the added date will be set to the system date at the time of import.
+'''BasedOn''' - Item code that the asset records is based on. Must be an existing item code from Item Master.
-'''*f8 - Whse''' - Warehouse name for the project. Must match a warehouse name that has been set up in the Warehouses screen.
+'''TagID''' - Asset Tag ID (30 characters max, alphanumeric)
-'''f9 - PM''' - Name of the Project Manager. Must match the full name of a contact in Adjutant with the Project Manager attribute.
+'''Serial''' - Serial Number 1 for the asset (30 characters max, alphanumeric)
-'''f10 - ShortNote''' - A note or extended description of the project. Updates the Project Master Remarks area. 200 characters max.
+'''Name''' - Asset Name (30 characters max, alphanumeric)
-'''f11 - BillType''' - Project Billing Type. Must match a value in the Project Billing Types (BILLTYPE) rule.
+'''Descrip''' - Longer description of asset (240 characters max)
-'''f12 - BillAmount''' - The amount that has been billed on the project.
+'''EntryDate''' - Asset Entry Date value. Must be formatted as a date in MM/DD/YY format.
-'''f13 - AccrAmount''' - The current accrual amount on the project.
+'''SvrDate''' - Asset In-Service Date value. Must be formatted as a date in MM/DD/YY format.
-'''f14 - ContractAmt''' - The contract amount of the project.
+'''Life''' - This field is not in use
-'''*f15 - TCode''' - Project Type. Must match a value in the Project Types (PROJTYPE) rule.
+'''SubLoct''' - SubLoaction additional details (50 characters max)
-'''*f16 - SCode''' - Project Status. Must match a value in the Project Status (PROJSTATUS) rule.
+'''RFID''' - RFID tracking code value (30 characters max, alphanumeric)
-'''f17 - AccMgr''' - Name of the Account Manager. Must match the full name of a contact in Adjutant with the Account Manager attribute.
+'''CType''' - Asset Type code from the Asset Type (ASSETTYPE) rule
-'''f18 - ContractDate''' - Contract Date. Must be formatted as a date in MM/DD/YY format.
+'''Route1''' - Optional workflow route name from the Route Maintenance screen
-'''f19 - SP''' - Name of the Salesperson . Must match the full name of a contact in Adjutant with the Salesperson attribute.
+'''Route2''' - Optional workflow route name from the Route Maintenance screen
-'''f20 - CompFlag''' - Completion status of the project. Use a 'y' if the project is complete and a 'n' if it is still active. '''Must be entered as a lowercase letter.'''
+'''Route3''' - Optional workflow route name from the Route Maintenance screen
 '''f25 through f31 - Custno1/2/3/4''' - Organization ID of other companies linked to the project.