Cost Tiers 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.

Cost Tier Import Notes

Cost Tiers are used to accurately track cost for all cost methods. Receiving both lot-controlled and non lot-controlled items on purchase order receipts will automatically create cost tier data for all system cost methods. It is critical to import cost tier data after all quantity imports because the imports will not create the needed cost tier data.

Regardless of the cost method used at go-live, cost tiers should be imported after every quantity import to accurately set the historical cost tier based on the material receipt date.

Timing and Preparation

The cost tier import should be completed after each quantity import. In most cases, the data from the quantity import (whether it is a lot-controlled import or not) can be used to create the cost tier import data.

Cost tier imports should be performed along with all parallel testing quantity imports to accurately set the historical cost tiers. This way, all testing transactions can be expected to track cost correctly throughout the various procedures that use cost.

Best practices are to immediately create a corresponding cost tier import file for each quantity import file, and import the cost tiers directly after importing the quantities.

It can be extremely difficult (or impossible) to fix cost data after items have been used in production or sales with incorrect cost data.

General Notes

The combination of date, quantity, and cost on the cost tier import represents an inventory receipt that establishes the system cost for that receipt transaction. For customers using an average cost method, the quantity import (and the corresponding cost tier import) will already reflect an average cost, or a specific instance cost for lot-controlled items. For customers using FIFO or LIFO methods, the cost tier import should ideally be separated into a distinct cost tier for each combination of item and receipt date, representing the cost for each receipt, AND the qty remaining in that cost tier. This level of detail is difficult to recreate for most systems, and may not be possible. For FIFO and LIFO customers that can't provide the data needed to recreate each tier accurately, inform the customer that all imported quantities will be costed according to the imported cost tier cost until the cost tier quantity is depleted.

Date: The cost tier date represents the date that the material was received, and is critically important for determining the correct cost tier to use for LIFO and FIFO methods. The FIFO and LIFO methods rely on the received date and quantity received to track inventory usage cost according to FIFO and LIFO rules. Review the Inventory Costing Methods Wiki page for more details on how cost tiers affect inventory value.

Qty: The cost tier quantity represents the quantity that was received on the date specified. FIFO and LIFO cost accounting will deplete the quantity in the cost tier at the related cost based on the transaction date and how it relates to the cost tier received date. Review the Inventory Costing Methods Wiki page for more details on how cost tiers affect inventory value.

Cost: The cost should be expressed per the stocking units for this import file. As stated above, each combination of date, quantity, and cost represents the cost of inventory items for that receipt. Review the Inventory Costing Methods Wiki page for more details on how cost tiers affect inventory value.

Cost Tier 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 Check/Correct Cost Tiers (MATCHCTALL) report can be used after a quantity import to generate most of the data needed to create a cost tier import file. After importing quantities, but before cost tiers have been created, this report will show each item or item/lot with an empty tier quantity. The data can be exported to an Excel spreadsheet, then copied and pasted into the cost tier import template. The cost and date values will be missing and must be filled in appropriately for all lines. This report can also be used to create the cost tiers by using the 'Correct' button, however, it only builds the tier for the item's specified cost method, and it assumes the current date as the tier date, which is generally not accurate. Best practices are to use the import to create all cost tiers with the correct tier date based on material receipt date.

The Cost tier import file is generally created either from data on quantity import files, or from queries into the database after quantity imports have been completed. It is not common during an implementation for customers to provide a file with cost tier data. However, if a customer supplies cost tier data, review the file to standardize the key data fields.

• Item - Review for item numbers that may have changed during implementation
• Qty - Verify that all quantities are provided in the item's stocking unit
• Date - Verify that dates are correct and expressed in MM/DD/YYYY format
• Cost - Verify that the cost is expressed per the item's stocking unit
• Seriallot - If the lot numbers have changed, or have been auto-generated during import, update the lot numbers to reflect the actual lot numbers created in Adjutant

Cost Tier Import Screen (IMPORTCOSTTIER)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Cost Tier Import

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

Export Template: Generates a blank Cost Tier 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 any cost tier lines with a blank owner value. This list is populated with Organization records that have the Inventory Owner attribute.

Default HedlFor: Sets the inventory holder for any cost tier lines with a blank heldfor value. 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.

Cost Tier Import File Definitions

Required fields are indicated with an *

*f1 - Item - Item Master item number

*f2 - Whse - Warehouse name

*f3 - Qty - Stocking unit quantity received at the associated cost tier

*f4 - Date - Received date for the associated quantity. This is also the date used to determine which cost tier is used for FIFO and LIFO methods.

*f5 - Cost - Cost per stocking unit for the associated cost tier

f6 - Owner - Inventory owner (Organization ID)

f7 - HeldFor - Inventory holder (Organization ID)

f8 - SerialLot - Serial or Lot# value for the associated cost tier item

f9 - OCost - Other cost associated with the cost tier (rare)

f10 - OOwner - Other owner associated with the cost tier (rare)

Cost Tier 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.

Review the Cost Tiers tab in Item Master for several imported records and verify that all imported data displays as expected. Review all cost method types in the drop-down.

Perform cost-related transactions with the imported records and verify that the Cost Tier Usage tab in Item Master shows the expected cost tier used.

Run the The Check/Correct Cost Tiers (MATCHCTALL) report and check for discrepancies and missing cost tier data.

Cost Tier Import Additional Steps

There are no additional steps needed for the cost tier import.

Cost Tier Import Database Tables

COSTSI - Each imported cost tier line creates a unique keyno in this table tied to the item, lot number, and tier date.

COSTFIFO - Each imported cost tier line creates a unique keyno in this table tied to the item and tier date.

COSTLIFO - Each imported cost tier line creates a unique keyno in this table tied to the item and tier date.

COSTUSAGE - The import doesn't create any data in the COSTUSAGE table, but any cost testing will create details in this table showing which cost tier was used for each transaction.