Inventory Quantity Import Guide

General Data Import Notes

Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.

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 inventory, 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

Item File Name/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. Make certain that any items imported with lot information are actually set up as 'Lot# Control' in Item Master. Importing lot data for non-lot-controlled items can adversely affect cost tiers and cost accounting.

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 name from the HOLDER rule, which must also match a valid 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 - ID1 - If the item is a 'Track Length' item, enter the ID1 value here. You can learn more about the 'Track Length' feature here.

f10 - ID2 - If the item is a 'Track Length' item, enter the ID2 value here.

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

*f12 - Item - Item number

f13 - 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).