Configurable Item (SOC) Import Guide

General Import Template File Notes

• Template files are typically generated from their respective Import screen using an 'Export Template' button.
• Using the 'Export Template' button from an updated system will help ensure that you are using the most up-to-date import file, and will help avoid errors.
• Templates generally (but not in all cases) contain 3 rows of header information.
  • The first row is generally labelled with f1 through f## and is used to give a consistent column id to each data column. Do not remove this row unless instructed.
  • The second row is labelled with the column data/field name. The values in this row are used for the file mapping screen. Editing these values is generally not needed and may make the field mapping process difficult.
  • The third row (if present) is labelled either with the same values from row 2, or with more detailed field descriptions. The third row is available to create a customer-friendly name for the associated data column.
    • The third row should be removed from the source file prior to importing. It will cause errors since it will be treated as the first import record on the file.
• Import files MUST always be saved as 'Microsoft Excel 5.0/95 Workbook (*.xls)' format until further notice. The import screens will generate errors if the file type is not correct.

• It is a good idea to perform a Mapping Table Import using a current source list before beginning on any imports. Without a current mapping table, the quality of imports can be compromised. The Mapping Table Import only needs to be run once on a newly set up Adjutant system. It does not need to be run before each import.
  • Export a current list from the Mapping Table Import screen from an updated system (or contact your Implementation Consultant for an updated file).
  • Perform a Mapping Table Import on the target system with the XLS file from the step above.

Configurable Item (SOC) Import Notes

Timing and Preparation

The Configurable Item (SOC) Import should occur soon after the Item Master records have been imported and reviewed. Importing the SOC details is essential for testing configurable items in the sales and production processes.

General Notes

The import template file for the SOC Import is generated by using the Export Configurable (SOC) Items screen, detailed below. The Export Configurable Items (SOC) can be requested from any system with configurable items. The export screen has multiple filters to help with selecting the right records for building an import file. The data can also be filled in manually, but it is much easier to use existing SOC records as a reference when creating a new import file.

If you intend to use most of the field data from an SOC Export file, review the data critically before attempting to associate it with a customer's item file. We do not want to propogate bad data.

The safest practice is to pick an accurate example of each question type and copy the single, reviewed question down to all new customer items. This way, the same data is used for all questions.

Be extremely careful when copying data in Excel that you don't accidentally increase values down the page. Use the correct procedures in Excel to copy, rather than copy and sequence, data down columns.

Configurable Item (SOC) Import File Data Scrubbing

Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing. This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import.

The SOC import file is generally built 'from scratch' or by exporting a known good list of SOC items and editing it heavily to match the customer's data. Because there is typically no single source data file from the customer, creating the SOC import file will typically involve pulling bits of data from one or more customer files to create the import. Below are some of the key data columns to review carefully prior to import:

Keyno: Remove the data in the keyno column before importing. The keyno value is not used during the import, and it can lead to confusion when reviewing and researching SOC imports. The SOC import process will generate new keyno values upon import, and the ones on the import file will no longer relate to anything.

Item: When copying data into the file, review the item number fields carefully. Carefully review any items that are being renumbered in Adjutant and make sure the item numbers are correct. Review for any transposed numbers or faulty copy and paste issues.

QuestNo: Re-sort the import file by item and look for any question numbering gaps or duplicates.

Question: Review the import file to standardize question labels for spelling and capitalization. Avoid importing things like 'LENGTH', 'Length', and 'length' for the same type of question. Make them all match before importing.

RCode (Report Code): Carefully review the report code values for all questions and ensure that there are no mismatched codes. Review to ensure that questions of the same type all use the same report code. For example, all weight questions should use 'W', rather than some using 'W', and some using 'WT'. If the customer needs multiple versions of the same question, such as Punch Code 1 and Punch Code 2, make sure the associated report codes are consistent.

Configurable Item (SOC) Export Screen (SOCEXPORT)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Configurable Item (SOC) Export

Class: Enter a single item class value or a range to filter the SOC Export results by Item Master class.

Item Range: Enter an item code range to filter the SOC Export results by all item numbers that fall between the from and to values.

Item Like Block: Enter up to 6 item-like searches to filter the SOC Export results by custom item number searches.

Commodity Code: Enter a single commodity code or a range to filter the SOC Export results by Item Master commodity code.

Held For: Enter a Held For name to filter the SOC Export by Held For.

Owner: Enter an Owner name to filter the SOC Export by Owner.

Configurable Item (SOC) Import Screen (SOCIMPORT)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Configurable Item (SOC) Import

Item File Name/Browse: Use the Browse button to locate and select the completed SOC import template file (in XLS format).

Configurable Item (SOC) Import File Definitions

Required fields are indicated with an * ; Required fields that can be auto-filled by the Import screen are indicated with **

*f1 - Item - Item number/code (20 character max, alphanumeric, no spaces allowed). The following special characters are allowed (. - + / _)

*f2 - Descrip - Item Description (99 alphanumeric character max)

**f3 - UnitGrp - Item Unit of Measure Group (examples are LENGTH, WEIGHT, TIME, IND). Must match a value in the UNITS rule.

**f4 - PurUnit - Item purchase unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

**f5 - StkUnit - Item stocking unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

**f6 - SellUnit - Item sell unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

*f7 - AvgCost - Item’s current average cost per stocking unit

*f8 - Price - Item’s price per pricing unit

**f9 - PriceUnit - Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

**f10 - CostUnit - Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

*f11 - StkFlag - Y if the item is stocked in the warehouse, N if it is not (made to order items are not stocked)

f12 - SerialFlag - Y if the item is serialized, N if it is not (serialized items have a serial number for every quantity of 1)

f13 - LotFlag - Y if the item is lot controlled, N if it is not (all coils are lot controlled).

f14 - Vendno - The vendor code of the item’s default supplier

f15 - Vpartno - The default supplier’s item code

f16 - HeldFor - Item's Held For name from the HOLDER rule. This is generally only used in locations where inventory can be held for other entities. Generally, all items should be imported with the default HeldFor name from the HOLDER rule, unless given specific instructions to create multiple HeldFor records.

f17 - Whse - Enter the warehouse name (not the ID code) for the primary warehouse where these items are located/produced

f18 - Taxable - Y if the item is taxable, N if it is not

f19 - UnitW - The item’s unit weight per stocking unit

f20 - Cubes - Used in Warehouse Management System shipping calculations and forms (rare)

f21 - DecQty - The number of decimals that should be available when entering quantities of the item on a purchase or sales order (valid entries are 0-5)

f22 - MiscItem - Y if the item is miscellaneous, N if it is not

f23 - Resell - Y if you plan on selling this item, N if you do not. If this field is left blank, the item cannot be added to a sales order.

f24 - PackCode - Item Packing Code (accepts single character letters from A - T). Can be used to group items that should be packed together by this code. (rare)

f25 - ABCCode - Item ABC ranking code (accepts single character letters from A - Z). Can be used to manually assign ABC rankings to inventory items. The Item ABC Code Assignments screen can be used to automatically assign the ABC Code based on usage or value, once usage and on-hand quantity information is available. (rarely used during item import)

f26 - Pgroup - The item’s pricing group. Customer pricing may be broken into item pricing groups; e.g. customer XYZ gets 40% off PANELS, 50% off COLDFORM, and 30% off ACCESSORIES, for component sales. Leaving this field blank can cause pricing issues if pricing is based on price group breakdowns.

f27 - ComCode - The item’s commodity code. Commodity codes are used in procurement and determine which suppliers can bid on which items. Commodity Codes can also be used as filters for certain inventory reports.

f28 - BaseCost - The item's base cost. This value can be useful if you are pricing your items using a cost-plus method.

f29 - ProdLine - This field is no longer used

*f30 - ItemCat - Item’s category for creating transaction categories. Item Categories are combined with Customer Categories to create a Transaction Category that is used to drive revenue and cost to the correct GL accounts. DEF should be entered for most items, unless a specific accounting model has been established based on item category. Review the revenue and cost accounting procedures carefully to determine the desired entries for each item . If left blank, the item record will not be usable until the category is filled in.

f31 - SqFt - The item's square footage per stocking unit

f32 - WarLen - If the item has an associated warranty length, enter it here (maximum 5 characters alphanumeric)

f33 - Produce - Y if the item is produced in the plant, N if the item is not produced

f34 - SearchCode - Custom text that can be used to generate a search screen for items with the same/similar search code value. For example, all items with the search code of 'Trim' will display in a search code display (by double-clicking on the item Search Code field), regardless of their item number or description. (maximum 35 characters, alphanumeric)

f35 - ProdGrp - Item’s product group. Enter one of the following in the column - M (Material), L (Labor), E (Equipment), X (Expense), or S (Subcontract). All items must be assigned to one of the 5 system-defined categories that drive revenue and cost accounting.

f36 - OwnerID - Item's inventory owner. This is generally only used in locations where inventory can be owned by other entities and the accounting needs to be kept separate. Generally, all items should be imported with the default OwnerID value, unless given specific instructions to create multiple Owner records, and multiple OHF combinations.

f37 - iClass - Custom text field available for further identifying item groups. Can be used on certain reports or custom reports. (maximum 10 characters, alphanumeric)

f38 - StdCost - Item’s standard cost per pricing unit

f39 - Coil - Y if the item is a coil. Coil items will automatically be assigned the Coil Receipts Default attribute. All coil items should also have the LotFlag column set to Y.

f40 - SingleIssue - Y if the item is the SingleIssue Item box should be checked in the Catch Weight attribute screen. This is only saved for catch-weight items (CW column set to Y).

f41 - CW - Y if the item is a catch-weight item. Catch-weight items will automatically be assigned the Catch Weight attribute.

f42 - OldItem - This field is no longer used

f43 - CWSPriceConv - Catch-Weight conversion factor between stock unit and pricing unit. (numeric, up to 5 digits)

f44 - CWSTKConv - Catch-Weight conversion factor between catch unit and stock unit. (numeric, up to 5 digits)

f45 - CWSellConv - Catch-Weight conversion factor between stock unit and selling unit. (numeric, up to 5 digits)

f46 - CWPurCon - Catch-Weight conversion factor between stock unit and purchase unit. (numeric, up to 5 digits)

f47 - CWUnit - Catch-Weight Unit

f48 - PackQty - Quantity to pack. (numeric, up to 5 digits)

f49 - AllOrder - Enter Y to set the Allocate at Order flag in Item Master

f50 - EDICode - Item's EDI code (alphanumeric, up to 10 digits, rare)

f51 - UPCCode - Item's UPC code (alphanumeric, up to 12 characters)

Configurable Item (SOC) Import Reconciliation

Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.

Run the Item Master Report and verify that the imported records all display with the key values filled in. You can also verify that the number of records imported matched the number of records reported. If there are fewer items reported, looks for items that did not get imported and reconcile.

Try to sell various items on a sales order to verify that the correct flags and defaults are working as expected.

Try to order various items on a purchase order to verify that costs and units work as expected.

Configurable Item (SOC) Import Additional Steps

Complete the required additional imports based on how the item will be used. For example:

• Item Quantity (O/H) Data
• SOC Data
• Profile Data
• Min/Max Data
• Coil Quantity Data
• and many more

Configurable Item (SOC) Import Database Tables

ITEMMASTER - Each imported item creates a unique IKEY tied to the ITEM value. Most of the Item Master fields are imported to the ITEMMASTER table.

ITEMDET - Item OHF details are created in the ITEMDET table. Each OHF combination has a unique KEYNO based on the associated IKEY (from ITEMMASTER) and the combination of Warehouse (LOCTID), Owner (VENDID), and HELDFOR.

PCXREF - Attributes added to item records will create cross-reference entries in the PCXREF table. The TYPE will generally be the attribute name or code. The PARENTID and CHILDID will generally match the IKEY value of the associated item.

ATTRIBUTE TABLES - Attributes such as Catch Weight and Coil Receipt Defaults create their own data tables with details from the attribute jump screens. For any attribute jump screens created that contain data, you should find a separate data table, such as CWINFO (for Catch Weight) or COILJUMP (for Coil Receipt Defaults). The associated FORM (jump screen) name from the ITEMATTRIB rule is generally the same name as the data table. These tables will usually link to the item using the IKEY value.