Table of Contents
...
| Note |
|---|
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 |
| Anchor | ||||
|---|---|---|---|---|
|
...
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.
| Note |
|---|
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 |
...
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.
| Note |
|---|
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. |
...
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.
| Note |
|---|
An Item record must exist before a disposition record can be created for that item. |
...