Inventory Quantity 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.

Inventory Quantity Import Notes

Timing and Preparation

The Inventory Quantity import should occur after all of the item records have been imported. Quantity imports should be completed for parallel testing to create available inventory to test orders and production. The Item Control rules should be complete prior to importing item master records. The Item Control Setup Guide covers all of the Rule Maintenance records that should be completed prior to importing item master records.

The Inventory Quantity Import will need to be run again at go-live to import the final available inventory as of the go-live date. A 'difference conversion' or 'catch-up conversion' is generally not worth it after quantities have been used in testing, and after purchase order receipts have been completed during testing. The best practice is to delete all of the inventory on hand data, and re-import with the current, final on hand quantities at go-live.

General Notes

While the Inventory Quantity import can be used for lot controlled items, it should not be used for coil items. Follow the [Coil Import Guide] for detailed information on how to import coil items.

IMPORTANT!! - Perform the Cost Tiers Import for all imported quantities to establish the historical receipt cost tier. The purchase order receipt process automatically builds cost tier data, but the quantity import process does not create cost tier data. Failing to import cost tiers for the imported quantities can have adverse effects down the road during procurement and production processes. Follow the Cost Tiers Import Guide for details on importing cost tiers.

Inventory Quantity 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. Some common things that need attention during Item Master data scrubbing include:

• Average Cost - The average cost for each imported quantity should be entered as the average cost per stocking unit. Verify that the source data has the correct cost per unit that matches the item's stock unit settings in Item Master. The imported average cost will be factored into the average cost calculations. Do not leave the average cost field empty.

• Quantity - Verify that the quantities imported are entered in each item's stocking unit. If there are any customized stock units such as BAG250 or BOX1000, review the Item Master stock unit to confirm that the imported quantity relates to the item's stock unit.

Inventory Quantity Import Screen (ITEMQTYIMP)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Inventory Qty Import

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

Export Template: Generates a blank Inventory Quantity import template file

Map Fields: Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.

Default Owner: Sets the inventory owner for all imported quantity records. This list is populated with Organization records that have the Inventory Owner attribute.

Default Warehouse: Sets the warehouse location for all imported quantity records. This list is populated by Warehouse Control records that have been created.

Default HeldFor: Sets the inventory holder for all imported quantity records. This list is populated from the Item Held For Codes (HOLDER) Rule Maintenance record. If a catch-all code such as 'DEF' is used, make sure the HOLDER rule is set up properly.

Transaction Date: Sets the transaction date for the QTYIMP transaction.

Validate to TXT File: Selecting this box will create a TXT errors file listing any rows that would create errors such as invalid data, or duplicate records. If no errors are found in the file, the import will still process and no error file will be generated. Checking the validate box only prevents the import process if errors are encountered.

Add to OnHand: The default option is to add the imported quantities to any existing on hand quantity.

Replace OnHand: Select the replace option if the quantity values in the import file represent the total on hand quantity for each item and they should replace the current on hand quantity.

Skip OHF Counter Update:

Skip Asset Check:

Inventory Quantity Import File Definitions

Required fields are indicated with an *

f1 - Lot - Lot number for lot controlled inventory. Do not use the Inventory Quantity import for coil items. Follow the [Coil Import Guide] for details on importing coil quantities.

f2 - PrintLabel - Enter a 'Y' to indicate that a label should be printed

f3 - LabelNo - Enter the number of labels that should be printed

f4 - OwnerID - Inventory Owner organization ID. If left blank, the import request screen can fill in the Owner for ALL imported records.

f5 - HeldFor - Inventory Holder organization ID. If left blank, the import request screen can fill in the Held For for ALL imported records.

f6 - Serial - Serial ID for serialized inventory items

*f7 - AvgCost - Average cost per stocking unit, as of the transaction date for the associated quantity

*f8 - Qty - On Hand quantity (in stocking unit) for the associated item

f9 - Whse - Inventory Warehouse location name. If left blank, the import request screen can fill in the Warehouse For for ALL imported records.

*f10 - Item - Item number

f11 - Bin - Item bin location. If the Bin does not exist in the Bin Control screen, the import program will add the needed Bin record.

Inventory Quantity Import Additional Steps

IMPORTANT!! - Perform the Cost Tiers Import for all imported quantities to establish the historical receipt cost tier. The purchase order receipt process automatically builds cost tier data, but the quantity import process does not create cost tier data. Failing to import cost tiers for the imported quantities can have adverse effects down the road during procurement and production processes. Follow the Cost Tiers Import Guide for details on importing cost tiers.

Run the Update Item OH/AL/OO (ITEMUPDATE) report after all quantities have been imported. This will update the Item Master on hand displays and item search results with the imported values.

Inventory Quantity 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 Inventory On Hand Report filtered for the imported records and verify that the imported records all display as expected. Review the lots, cost entries, and on hand value for all imported records.

Compare the Adjutant cost and on hand inventory value for several records against the source data. Verify that imported inventory value matches the source data value. If there are any discrepancies, review, adjust, and try to correct with an updated import. If the differences can't be corrected with a new import, document the reasons for the differences and discuss with customer.

Inventory Quantity Import Database Tables

ITEMTRACK - Each imported quantity creates a TRACKNO in this table, linked to the imported item's IKEY and Location (Bin).

ITEMTRAN - Each item quantity will create a unique KEYNO with a Note of 'QTYIMP' tied to the item's IKEY value. The ITEM9 field shows the actual item number for the IKEY.

ITEMMACT - Each imported quantity will create a unique KEYNO with a TYPE of 'IA' (Inventory Adjustment) tied to the item's IKEY (and Lot, if lot controlled).