Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 Image Modifiedto 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.   

    

Anchor
itmimport
itmimport
Item Import Type

...

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 Image Modifiedto 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.


FieldFormatDefinition
record_idMust contain an "I" (for item)Required field: Identifies record as an item record
idAutomatically populated with the existing item's ID#  if extracting from the items gridRequired field if updating an existing item.  Otherwise, it is not required for new items.
inventory_tagMaximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroesRequired field: Tag number associated with the item
second_inventory_tagMaximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroesSimilar 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_tagMaximum of 20 alphanumeric or numeric characters; must be in text format to retain leading zeroesUsually an internal number assigned by the appraisal firm.
composite_idNo maximum number of charactersCan 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_idNo maximum number of charactersUsed 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_1No maximum number of characters.Required field: Description of the asset. May be the PO Item description.  NOTE:  do not use commas
accessoriesNo maximum number of charactersA listing of accessories or enhancements for the item. NOTE:  do not use commas
model_noNo maximum number of characters; must be in text format to retain leading zeroesThe item's model number if applicable.
serial_noNo maximum number of characters; must be in text format to retain leading zeroesThe item's serial number if applicable.
item_categoryMaximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroesThe 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_lotMaximum of 9 digitsRequired field: Indicates the number of items with the same tag number. May use the quantity from the PO item
replacement_costnumeric format: do not include commas or dollar signsThe estimated cost to replace the item. This may be different than the original cost. 
insurable_valuenumeric format; do not include commas or dollar signsThe 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 properlyThe 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_unitMaximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroesThe 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.

conditionMaximum of 2 alphanumeric or numeric charactersSpecifies 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_classMaximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroesRequired 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_functionMaximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroesRequired 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_fundMaximum of 4 alphanumeric or numeric characters; must be in text format to retain leading zeroesRequired 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_dateaccepts 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_codeAccepted codes are displayed to the rightRequired 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_costnumeric format; do not include commas or dollar signsRequired field: The cost of the item as given on the purchase order(s) or the sum of all acquisition amounts for the item.
discountnumeric format; do not include commas or dollar signsAny discount excluded from the original cost.
salvage_valuenumeric format; do not include commas or dollar signsThe estimated value of the asset at the end of the depreciation period.
depreciation_begin_dateaccepts 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.
lifealphanumeric formatThe 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_methodAccepted codes are displayed to the rightRequired 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_typeAccepted codes are displayed to the rightThe item's lease type as given by the state defined codes of: C (capitalized)or O (operating)
lease_periodAccepted codes are displayed to the rightLength of lease payment period code. A (Annually); D (Daily); M (Monthly); Q (Quarterly); S (Semiannually); W (Weekly)
lease_vendoralphanumeric formatThe 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_dateaccepts 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_paymentnumeric format; do not include commas or dollar signsAmount of the periodic lease payment.
lease_interest_ratealphanumeric formatRate of interest charged by the vendor.
factoralphanumeric formatA factor used in the declining balance method of depreciation.
physical_dateaccepts 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_commentNo maximum number of charactersCan be used to add any notes on the inventory. Usually included in appraisal company spreadsheet.
user_moneynumeric format' do not include commas or dollar signsUser defined code
user_descriptionNo maximum number of charactersUser defined code
user_dateaccepts any date format.  (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)User defined code
user_code_1No maximum number of charactersUser defined code
user_code_2No maximum number of charactersUser defined code
user_code_3No maximum number of charactersUser 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 Image Modifiedto 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.

...

FieldFormatDefinition
record_idMust contain an "A" (for acquisition)Required field: Identifies record as an acquisition record
idAutomatically populated with the existing acquisition's ID#  if extracting from the acquisition gridCurrently 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_tagMaximum of 20 alphanumeric or numeric characters; must be in text format to retain tags with leading zeroesRequired field: Tag number associated with the existing item
dateaccepts 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.
amountnumeric format; do not include commas or dollar signsRequired field: The cost of the item as given on the purchase order,
acquisition_codeAccepted codes are displayed to the rightRequired 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_numberalphanumeric formatUSAS vendor number from which the item was purchased.
vendor_namealphanumeric formatUSAS Vendor name from which the item was purchased.
po_numberalphanumeric formatUSAS Purchase order number used to purchase the item.
item_numberalphanumeric formatItem number from the USAS purchase order
po_dateaccepts any date format.  (mm-dd-yyyy, mm/dd/yyyy, mmddyyyy, etc.)Date of the USAS purchase order
check_numberalphanumeric formatDisbursement's check # used in purchasing the item
grant_idalphanumeric formatCFDA (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_yearalphanumeric formatProject 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.

...