The Import option may be used to import the following codes and transactions below. Please refer to the Inventory Import Criteria for further details on the import file format. Any rules pertaining to Inventory will also be applied when importing inventory data. The Inventory Standard role will be able to utilize the Import function.

Asset Class: Creates an Asset Class code under Core>Asset Class
Category Code: Creates an Item Category code under Core>Categories.
Condition Code: Creates a Condition code under Core>Condition.
Disposition: Creates a disposition transaction under Transactions>Dispositions. NOTE: The associated item must exist in order to post a disposition transaction against it. The status will be updated to disposed of on the Item record.
Disposition Code: Creates a Disposition code under Core>Dispositions
Item: Creates an Item under Transactions>Item and associated acquisition under Transactions>Acquisitions
Acquisition: Creates an Acquisition transaction under Transactions>Acquisitions. NOTE: The associated item must exist in order to post an additional acquisition against it.
Location Code: Creates a Location Code under Core>Locations
Organization Code: Create an Organization code under Core>Organization.

Field Definitions

Upload File: Only CSV (comma separate value) format may be used to import data.
Select Import Type: You may only select one import type at a time. Detailed formats for each import type are listed under "Inventory Import Criteria"
Update Records: you may choose to update information on records that already exist in Inventory.

If you choose to update existing records, all columns included in your spreadsheet will overwrite the existing data in Inventory. For example, if you have a Replacement Cost of $5000 on your spreadsheet and your current Replacement Cost for the item is $4500, when you select update, it will overwrite $4500 with the new Replacement Cost of $5000. To avoid accidentally overwriting columns that should not be updated, please remove columns on the spreadsheet you do not wish to update.

Create Transfers: If the fund, function or asset class will be modified for an existing item, 'Create Transfers' must be checked or an error will be generated. If checked, it will prompt for a transfer date and will automatically create Transfer transactions for the items. The modified fund, function or asset class will be updated on the item and related acquisition records.
Keep Leading Zeroes: You have the option to preserve leading zeros in tag numbers of newly imported items. In general, it is recommended that leading zeros be preserved otherwise the sort capabilities of the software may be hindered. The determining factor is the method the appraisal company used to load the tag numbers. Therefore, the decision whether or not to preserve leading spaces or zeros must be made on an individual basis.
Auto Assign Tag# If the existing spreadsheet does not contain a tag number, you may check this option to allow the system to auto assign a temporary tag when creating new items. The new tag items will always contain an item record and an acquisition record if the GAAP flag is checked. The program will prompt you to enter a starting tag prefix and a starting tag number. For example, if the prefix is NOTAG and the starting tag number is 1, the tag number created will be in the format "NOTAG1". The program will automatically increment the starting tag number for any other tag numbers created. If Auto Assign Tag is not checked, records on the import file without a tag number will not be imported.
- Starting Tag Prefix: may be numeric or alpha numeric
- Starting #: must be numeric

Inventory Import Criteria

Import template spreadsheets are available for item, acquisition and disposition import types. They contain the fields in the proper format (listed in the tables below). If you are not using the template spreadsheet and are creating a spreadsheet of your own, the field names on the spreadsheet must exactly match the Field names listed below. The field names are not case sensitive, but must be entered exactly as shown below (i.e inventory_tag and INVENTORY_TAG are both acceptable but inventory tag or inventorytag will cause an error.)

When creating new records, the fields specified below as "Required field" must be included in the spreadsheet. All other fields are optional. All date formats are accepted (i.e. mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc).

When updating existing records: If using the to extract existing records from any of the inventory grids (in order to mass update existing data), you must select the EXCEL file type in order to retain leading zeroes on fields that contain them.

Item Import Type

Item Import Template Spreadsheet (in EXCEL format)

When a new item is imported, the information will be stored in TRANSACTIONS>ITEMS and the associated acquisition record will be created simultaneously and stored in TRANSACTIONS>ACQUISITIONS. NOTE: Only limited data will be stored on the associated acquisition record (tag number, type (acquisition by default), date and amount). If you want to add additional details to the acquisitions created, you can extract the newly created acquisitions via acquisitions grid, add the additional acquisition information in EXCEL and import the updated spreadsheet using the Acquisition import type and ensure 'update records' is checked.

Updating existing items: If using the to extract data from any of the inventory grids, you will also notice the spreadsheet automatically includes an 'id' column that contains the existing item's ID number. Leave the 'id' column in the spreadsheet and do not edit the data in this column. This is needed when updating the existing items.

If you are mass-loading prior year items for a non-migrating district (and their GAAP flag is set), please include the beginning_balance column on the spreadsheet. Please refer to the Migration Import documentation for further details on how to import data for non-migrating districts.

Field	Format	Definition
record_id	Must contain an "I" (for item)	Required field: Identifies record as an item record
id	Automatically populated with the existing item's ID# if extracting from the items grid	Required field if updating an existing item. Otherwise, it is not required for new items.
inventory_tag	Maximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroes	Required field: Tag number associated with the item
second_inventory_tag	Maximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroes	Similar to the tag number but is used primarily to record tags supplied by the State of Ohio or the Federal Government to identify items purchased with matching state or federal dollars.
appraisal_tag	Maximum of 20 alphanumeric or numeric characters; must be in text format to retain leading zeroes	Usually an internal number assigned by the appraisal firm.
composite_id	No maximum number of characters	Can be used to indicate the item is a component of a larger asset. This identifier should be the same for all members of the composite item and can be alpha or numeric.
entity_id	No maximum number of characters	Used to identify different entities within the same reporting entity. This ID can be used to exclude all items belonging to a particular entity from the GAAP schedules or to generate schedules for specific entities. This code may be alpha or numeric.
description_1	No maximum number of characters.	Required field: Description of the asset. May be the PO Item description. NOTE: do not use commas
accessories	No maximum number of characters	A listing of accessories or enhancements for the item. NOTE: do not use commas
model_no	No maximum number of characters; must be in text format to retain leading zeroes	The item's model number if applicable.
serial_no	No maximum number of characters; must be in text format to retain leading zeroes	The item's serial number if applicable.
item_category	Maximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroes	The item's Category Code as defined in Core > Category codes. If the code does not currently exist in Core>Category, it will be automatically created upon import of the item.
items_in_lot	Maximum of 9 digits	Required field: Indicates the number of items with the same tag number. May use the quantity from the PO item
replacement_cost	numeric format: do not include commas or dollar signs	The estimated cost to replace the item. This may be different than the original cost.
insurable_value	numeric format; do not include commas or dollar signs	The estimated value of the item for insurance purposes
location_category location_number	Maximum of 4 alphanumeric or numeric characters each; must be in text format to retain leading zeroes; The spreadsheet must contain both fields in order for the location to import properly	The item's Location Code is made up of both the location_category and location_number and defined in Core > Location Codes. If the code does not exist, it will be automatically created in Core>Location Codes
organizational_unit	Maximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroes	The Organization Code as defined in Core > Organization Codes. If the codes does not exist, it will be automatically created in Core>Organization Codes
status	Accepted status codes are displayed to the right; If left blank or not included, the status of Active will be granted by default.	The item's status code as given by one of the following state defined codes: Active, Inactive, Disposed Of, New Item, Old Tag Item, Excess Asset Held For Sale, Excess Asset Not In Use. If left blank or not included, a status of Active will be assigned to newly imported items. Note: Field cannot be modified for capitalized items.
condition	Maximum of 2 alphanumeric or numeric characters	Specifies the physical condition of the item as defined in Core > Condition Codes. If the codes does not exist, it will be automatically created in Core>Condition Codes
asset_class	Maximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroes	Required field: The item's asset class code as defined in Core > Asset Class. If the code does not currently exist, it will be automatically created in Core>Asset Class
asset_function	Maximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroes	Required field: The item's function code as defined in Core > Functions. If the code does not currently exist in Core>Functions, the item will not be imported
asset_fund	Maximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroes	Required field: The item's fund code as defined in Core > Funds. If the code does not currently exist in Core>Funds, the item will not be imported.
acquisition_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Required field: Item's Acquisition Date NOTE: Must be a date in an open period.
acquisition_code	Accepted codes are displayed to the right	Required field: The item's method of acquisition as given by one of the following state defined codes: D(donated), L(leased), O(other) and P(purchased)
original_cost	numeric format; do not include commas or dollar signs	Required field: The cost of the item as given on the purchase order(s) or the sum of all acquisition amounts for the item.
discount	numeric format; do not include commas or dollar signs	Any discount excluded from the original cost.
salvage_value	numeric format; do not include commas or dollar signs	The estimated value of the asset at the end of the depreciation period.
depreciation_begin_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	The date in which depreciation is to start. If a depreciation method of 'S' is entered, a beginning depreciation date in required. NOTE: The depreciation_begin_date cannot be before the acquisition_date.
life	alphanumeric format	The number of years the item is to be depreciated. NOTE: If the district's is on GAAP and their capitalization threshold include a life limit, please ensure the life is included.
depreciation_method	Accepted codes are displayed to the right	Required field: The item's depreciation method as given by one of the following state defined codes: S(straight line), D(declining-balance) or N(none)
life_to_date_depreciation	numeric format; do not include commas or dollar signs NOTE: This field can only be imported when performing an item import via the System>Configuration>Migration Import option (ADMIN users only)	The Life-to-date depreciation. This is the total amount of depreciation for the item from the beginning depreciation date until the last fiscal year closed
beginning_balance	numeric format; do not include commas or dollar signs NOTE: Only available with Migration Import option under System>Configuration. Only users with INV_ADMIN role can import beginning balance amounts.	The beginning balance amount of a capitalized asset (current original cost of the asset). Beginning balance amounts are included on the GAAP reports.
lease_type	Accepted codes are displayed to the right	The item's lease type as given by the state defined codes of: C (capitalized)or O (operating)
lease_period	Accepted codes are displayed to the right	Length of lease payment period code. A (Annually); D (Daily); M (Monthly); Q (Quarterly); S (Semiannually); W (Weekly)
lease_vendor	alphanumeric format	The USAS vendor number for the vendor who is contracted to maintain the asset
lease_inception_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Date the lease begins.
lease_end_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Date the lease expires or the date the district takes full ownership of the item.
lease_payment	numeric format; do not include commas or dollar signs	Amount of the periodic lease payment.
lease_interest_rate	alphanumeric format	Rate of interest charged by the vendor.
factor	alphanumeric format	A factor used in the declining balance method of depreciation.
physical_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	The date of the last physical inventory. Usually included in appraisal company spreadsheet.
physical_comment	No maximum number of characters	Can be used to add any notes on the inventory. Usually included in appraisal company spreadsheet.
user_money	numeric format' do not include commas or dollar signs	User defined code
user_description	No maximum number of characters	User defined code
user_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	User defined code
user_code_1	No maximum number of characters	User defined code
user_code_2	No maximum number of characters	User defined code
user_code_3	No maximum number of characters	User defined code

Acquisition Import Type

Acquisition Import Template Spreadsheet (EXCEL format)

When a new acquisition is imported to an existing item, the information will be stored in TRANSACTIONS>ACQUISITIONS.

Updating existing acquisitions: If using the to extract data from any of the inventory grids, you will notice the spreadsheet automatically includes an 'id' column that contains the existing acquisition's ID number. Leave this column in the spreadsheet and do not edit the data in this column. This is needed when updating the existing acquisitions.

An Item record must exist before additional acquisition records can be created for the item.

Field	Format	Definition
record_id	Must contain an "A" (for acquisition)	Required field: Identifies record as an acquisition record
id	Automatically populated with the existing acquisition's ID# if extracting from the acquisition grid	Currently a required field if creating a new acquisition or updating an existing acquisition. If creating a new acquisition, you only need the header. The rest of the column may be left blank.
inventory_tag	Maximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroes	Required field: Tag number associated with the existing item
date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Required field: Acquisition Date. NOTE: New acquisitions must be a date in an open period.
amount	numeric format; do not include commas or dollar signs	Required field: The cost of the item as given on the purchase order,
acquisition_code	Accepted codes are displayed to the right	Required field if creating a new acquisition. Otherwise, it is not required if updating existing acquisitions. Acquisition Type (either Payment or Acquisition)
account_code	Full expenditure account code format FND-FUNC-OBJ-SCCC-SUBJCT-OPU-IL-JOB Example: 001-2411-640-0000-000000-001-00-000	USAS source account to which the item is charged.
vendor_number	alphanumeric format	USAS vendor number from which the item was purchased.
vendor_name	alphanumeric format	USAS Vendor name from which the item was purchased.
po_number	alphanumeric format	USAS Purchase order number used to purchase the item.
item_number	alphanumeric format	Item number from the USAS purchase order
po_date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Date of the USAS purchase order
check_number	alphanumeric format	Disbursement's check # used in purchasing the item
grant_id	alphanumeric format	CFDA (Catalog of Federal Domestic Assistance) number as found on the grant papers or a user supplied code to identify federal, state, or other grants.
grant_year	alphanumeric format	Project year for the associated grant code.
update_original_cost	"Y" or "N"	Required field: Mark "Y" for "Acquisition" types in order to update the original cost with acquisition amount. If entering an acquisition transaction for historical purposes or a "Payment" type, the flag may be set to "N" to prevent the acquisition amount from updating the item's original cost.
error_adjustment	"T" or "F"	Mark true if the acquisition transaction is an adjustment for an error made in a prior fiscal year. Otherwise leave blank and it will default to false.

Disposition Import Type

Disposition Transaction Spreadsheet Template (EXCEL format)

When a new disposition transaction is imported, the information will be stored in TRANSACTIONS>DISPOSITIONS and the item record's status will change to disposed of.

Updating existing dispositions: When choosing to update existing dispositions, only the following fields can be modified: Disposition_Code, Authorized_by, and Amount_received.

An Item record must exist before a disposition record can be created for that item.

Field	Format	Definition
record_id	Must contain a "K"	Required field: Identifies record as a disposition record
tag	Maximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroes	Required field: Tag number associated with the item
date	accepts any date format. (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)	Required Field: Disposition Date; NOTE: new disposition transactions must be a date in an open period.
disposition_code	alphanumeric format	Required Field: The disposition code as defined in Core > Disposition Codes. If the code does not currently exist, it will be automatically created in Core>Disposition Codes
authorized_by	alphanumeric format	Person who authorized disposition.
amount_received	numeric format; do not include commas or dollar signs	Dollar amount received for items.
error_adjustment	"T" or "F"	Mark true if the disposition transaction is an adjustment for an error made in a prior fiscal year. Otherwise leave blank and it will default to false.

Asset Class Code

Upon import, asset class codes are stored in Core>Asset Classes. For more information on asset classes, please click here.

Asset Class Import Template

Field	Format	Definition
record_id	Must be defined with an A	Required Field: Identifies the record as an asset class.
asset_class	Max 4 characters; If Asset Classes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes First two characters must be an AOS predefined code Second two characters is optional and may be numeric or alphanumeric	Required Field: This is the Asset Class Code. First two characters are pre-defined by AOS: 01 = Land and Improvements 02 = Buildings and Building Improvements 03 = Furniture, Fixtures & Equipment 04 = Vehicles 05 = Infrastructure 06 = Books 08 = Construction in Progress
class_description	No maximum number of characters.	Required Field: User defined asset class description

Field

Format

Definition

record_id

Must be defined with an A

Required Field: Identifies the record as an asset class.

asset_class

Max 4 characters;

If Asset Classes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes

First two characters must be an AOS predefined code

Second two characters is optional and may be numeric or alphanumeric

Required Field: This is the Asset Class Code.

First two characters are pre-defined by AOS:

01 = Land and Improvements
02 = Buildings and Building Improvements
03 = Furniture, Fixtures & Equipment
04 = Vehicles
05 = Infrastructure
06 = Books
08 = Construction in Progress

class_description

No maximum number of characters.

Required Field: User defined asset class description

Category Code

Upon import, category codes are stored in Core>Categories. For more information on categories, please click here.

Category Import Template

Field	Format	Definition
record_id	Must be defined with a C	Required Field: Identifies the record as a category code
item_category	Max 4 characters If Category codes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes	Required Field: User defined code to determine category of the asset
code_text	No maximum number of characters.	Required Field: Category description
insurance_class	Must use one of the predefined codes displayed in the next column or insurance class won't import; case sensitive	Insurance Class Codes B = Building C = Contents E = Excluded IM = Inland Marine (items that "float" between buildings) P = Property in the open V = Vehicles
inflation_rate	alphanumeric format	Percentage used to increase or decrease replacement and/or insurable values.
useful_life	alphanumeric format	Useful life expectancy. The code entered will be applied automatically to the life expectancy field when creating an item.
asset_class	If asset class contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes;	The asset class code as defined in Core > Asset Classes. If the code entered does not currently exist in Core>Asset Classes, it will be automatically created in Core>Asset Classes

Condition Code

Upon import, condition codes are stored in Core>Condition. For more information on conditions, please click here.

Condition Import Template

Field	Format	Definition
record_id	Must be defined with a D	Required Field: Identifies the record as a condition code
condition	alphanumeric format	Required Field: Condition Code
cond_desc	alphanumeric format	Required Field: Condition Description

Disposition Code

Upon import, disposition codes are stored in Core>Disposition. For more information on disposition codes, please click here.

Disposition Code Import Template

Field	Format	Definition
record_id	Must be defined with a B	Required Field: Identifies the record as a disposition code
disposition	If disposition code codes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes	Required Field: Disposition Code
code_text	alphanumeric format	Required Field: Disposition description

Location Code

Upon import, location codes are stored in Core>Locations. For more information on locations, please click here.

Location Code Import Template

Field	Format	Definition
record_id	Must be defined with a L	Required Field: Identifies the record as a location code.
location_category	numeric or alphanumeric If Location Category contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes	Required Field: User defined category portion of location. Determines area asset is located (i.e. building, center)
location_number	numeric or alphanumeric If Location Number codes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes	Required Field: User defined number portion of location. Determine specific area (room) within category asset is located.
code_text	numeric or alphanumeric	Required Field: Location description

Organization Code

Upon import, organization codes are stored in Core>Organization. For more information on organizations, please click here.

Organization Code Import Template

Field	Format	Definition
record_id	Must be defined with an O.	Required Field: Identifies the record as an organization code.
org_unit	numeric or alphanumeric If Organization codes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes	Required Field: Organization unit code
code_text	numeric or alphanumeric	Required Field: Organization description

Field

Format

Definition

record_id

Must be defined with an O.

Required Field: Identifies the record as an organization code.

org_unit

numeric or alphanumeric

If Organization codes contain leading zeroes, column format must be TEXT in spreadsheet to retain leading zeroes

Required Field: Organization unit code

code_text

numeric or alphanumeric

Required Field: Organization description

Import Report Messages

Listed below are some of the messages you may encounter on the resulting import report.

Importing New Data

Item Import Type: Error with tag #xxxxxxx on line: # Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Error while committing
- The error will include the tag number and the line on the spreadsheet that is unable to import. Please review your spreadsheet headers to ensure they contain the correct field name and also verify the data in each row is formatted correctly. If you are still unable to determine the issue, please contact your ITC and they will be able to review the inventory application log which may provide more details on what is causing the error.
Item Import Type: Item #: date is in a closed period. This message isn't referencing an actual tag # so it indicates they may have extra unintentional rows of data, possibly blank data, in the spreadsheet that do not contain the required fields thus causing the message. Please review the CSV file in an editor such as NOTEPAD or NOTEPAD++ to determine the extra rows of data. Here is an example below. Rows 2 and 3 contain proper item data that may have been imported but row 4 and subsequent rows contain what look to be empty data that is producing the message on the import results file.

Updating Existing Data

Item Import Type: If an existing item is updated successfully, the report will display "item updated" under the message column, along with the tag number and item description. Otherwise, review other possible message that may be displayed when attempting to update existing item data
- "Item #: xxxxxx date is in a closed period: If you are updating existing item data and you encounter this error, the system thinks you are trying to add a new item (in a closed period). Please review the tag number. If the leading zeroes were dropped from the tag number, you will need to re-add them to the spreadsheet so that the import recognizes this tag as an existing tag number.

Browser not supported