Import
- Michelle Drewes
- Jodi Becher
- Pat Zelei
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.
| 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 |
| 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.
| 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.
| 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.
| 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 |
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.