Import
The Import option may be used to import the following codes and transactions. 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 alphanumeric
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; however, 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
(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 the 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.
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 importing 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 | Connects data to record when updating an existing item. Leave blank when creating 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 that 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 is 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 do not exist, they 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 is given by one of the following state-defined codes: Active, Inactive, Disposed Of, New Item, Old Tag Item, Excess Asset Held For Sale, or Excess Asset Not In Use. If left blank or not included, a status of Active will be assigned to newly imported items. Note: You cannot update the status to Disposed Of using the Item import; however, you can use the Disposition Import. If changing a capitalized Item, this Field can only be modified if the Acquisition date is in an open fiscal year. If changing a Non-capitalized Item, the item Status may be updated regardless of the fiscal year. |
condition | Maximum of 2 alphanumeric or numeric characters. | Specifies the physical condition of the item as defined in Core > Condition Codes. If the codes do not exist, they will be automatically created in Core>Condition Codes |
asset_class | 1-4 characters long. First two characters must match the Asset Class types defined by the State of Ohio. The last two characters are optional and can be used to subdivide the asset classes. Must be in text format to retain leading zeroes.
| Required field: The item's asset class code as defined in Core > Asset Class. The importer will match the Asset Class Type to the first 2 characters of the imported code. If the 2 characters do not match any of the 8 Asset Class Types, the code will be rejected with New asset class types cannot be created. If the code meets the length requirement and class type but does not currently exist in Core, a new code is created with the description User Import Fund/Function/Asset Class. |
asset_function | Can be 1-4 characters long with a 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 | Can be 1-4 characters long with a 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 is 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 is 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 is on GAAP and their capitalization threshold includes 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 the Migration Import option under System>Configuration. Only users with the 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 the appraisal company spreadsheet. |
physical_comment | No maximum number of characters | Can be used to add any notes on the inventory. Usually included in the 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 when 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 have 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 the 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" |