Item Import Utility

Application Overview

 The purpose of this utility is to provide an easy way for a third party application to add items into Elliott.  It can also be used for transferring the item master records added in a different company or location.  It is very similar to the Elliott Sales Order Import utility.

 This utility will import from a fixed length ASCII file and append items in the ASCII file into the Elliott item master file.  An import journal will provide the details of the import result.  The utility will validate the data in the ASCII file before it adds to item master table.  A pre-interface report can be used to check for errors before importing.

 This utility makes it easy for a third party developer by requiring only the ITEM_NO & ITEM_DESC1 fields.  The other fields can be left blank.  When a field is left blank, it means “let the import utility determine the value.”  For example, if ITEM_STOCK_FLAG is left blank, then the default value should be “Y.”  However, the system will also check for DEFAULT-ITEM.  If it exists, then the value in this record will be used.

 This utility also makes it easy because the third party developer does not need to know all the associated tables that get updated when an item is created.  This includes the IMINVLOC and SYUSRIDX tables.

 As a result, a third party developer does not need to know how Elliott creates an item master record.  They simply create an ASCII file by supplying the values that are meaningful to them and leave all the other fields blank.  This import utility will assign the default values automatically.

 The utility is intended for adding items to Elliott and not intended for changing item data.

Import Item File

 By default, the import item file name is IMITMFIL.TXT.  However, you can change this to be any name you want, as long as you don’t use long file names.  The path can be any pre-defined path.  The default path and file name can be entered in the setup under Global Setup, Add-ons and Item Import Utility.  The system will use the default path and file name as defined in global setup, but the user can override it.

 The ASCII file to import does not have to provide all the fields in the item file.  When fields are not provided, default values are assumed.  Only Item Number and Item Description 1 are required.  Everything else can be left blank.

 The Item Import Utility is similar to Sales Order Import (used to import EDI sales orders).  It has two options; pre-interface and interface.  The pre-interface will print out a report showing the import data and will report if any of the data provided is invalid.  At the end of the pre-interface report, it will show the total number of errors it encountered so users can easily tell whether if there are any errors.

 The “Interface” produces a report identical to “Pre-Interface.”  However, it will actually add new items to the Elliott item master file based on the data in the interface file.  Once the interface is successful, it will delete the interface file.  If there are any errors in the interface, it will not interface the entire batch until the error is corrected and re-interfaced.  During the interface, you have the option to create other inventory location records.

CSV Import File

 In addition to the fixed length file, you can now import the items from a CSV file.  There is a separate menu for the CSV with the Pre-interface, Interface and Layout options.  The layout for the CSV import file is as follows:

Only Item Number and Item Description fields are required.  All other fields are optional and, if not provided, will assume a default value.  The default value can either be the Elliott’s default value when adding a new item or the value in DEFAULT-ITEM, if it exists.

The CSV Import option also supports copying a default value from another item.  If the item you are copying from is a Kit, Feature or BOMP parent item, then its components structure can be optionally copied as well.   A special feature will also make the copy from item as the only kit component if the copy from item is a regular item (not a kit or feature/option) and the copy to is a Kit item.

The CSV Import option can also automatically assign UPC codes upon importing new items.

File Layout for IMITMFIL.TXT (Page 1/3)

File Layout for IMITMFIL.TXT (Page 2/3)

File Layout for IMITMFIL.TXT (Page 3/3)

• File layout is a fixed length ASCII file with CR/LF at the end of each record.
• The data type “char” should be left justified.
• The Numeric Field is free form text and can be placed anywhere in the space reserved.  The following are examples of valid numeric fields; “1.25”, “-1.25”, “+1.25”, “1.25-“, “1.25+”, “1.2”, “1”, “01.25”.  Make sure to verify the size and decimal precision capability of each numeric field.  Typically, NumericSA fields do not allow the value to be signed and NumericSTS allows signs.
• The date field will be in NumericSA format.  However, you must provide the date data in YYYYMMDD format.
• The following are required fields:
• ITEM_NO
• ITEM_DESC1
• For the non-required fields, you can either leave them blank or you can provide a value.  When you leave the field blank, the system will default its value to data in item number “DEFAULT-ITEM”.  If “DEFAULT-ITEM” is not defined in the item file, the system will use the default value when you add a new item.
• During import, the system will make sure ITEM_NO does not currently exist in the item file.
• If the user provides a value for the following fields, then they will be validated against the corresponding master table.  We recommend you leave these fields blank and let system handle them with a default value if you don’t know what they are.
• ITEM_PROD_CAT: Validate Product Category file
• ITEM_USER_DEF_CD: Validate User Defined Code file
• ITEM_MFG_LOC: Validate Location file
• ITEM_MAT_TYPE: Validate Material Cost Type file
• ITEM_SUBSTITUTE: Validate Substitute Class file
• ITEM_PRIME_VEND_NO: Validate Vendor file
• Typically, a flag may contain “Y” or “N”.  However, confirm the possible values of each flag in item file maintenance to make sure the value you provide is a valid choice.
• Please leave ITEM_STYLE_CODE blank.
• If you do not know the meaning of a field, leave it blank and the system can handle it with a default value.
• If your UPC code is stored in ITEM_NOTE_1 – ITEM_NOTE_5, the system will make sure the UPC code is unique and passes the checksum validation.
• The system will update the SYUSRIDX file for any item fields that are defined as a user search key.