Ceravolo Consulting

Doral, FL

Difficulty Level: Advanced Level AccountMate User

Version(s) Affected: AccountMate 7 for SQL, Express and LAN
AccountMate 6.5 for SQL, MSDE and LAN

Module(s) Affected: IC, PO, SO, MI

 

DESCRIPTION

The AccountMate’s Recalculate Inventory Data feature provides solutions to inventory data inconsistencies caused by inevitable circumstances that occur in the workstation or in the server. It is important to understand the feature’s appropriate use, the kind of inventory data inconsistencies that can be resolved, and the proper procedure to use when you perform this function.

This Technical Note enumerates the conditions that require inventory data recalculation as well as the AccountMate tables affected and validated during recalculation. This document also discusses the prerequisites that must be in place prior to performing inventory data recalculation, the procedures to use, and the issues that will cause the recalculation to fail.

SOLUTION

It is necessary to perform Recalculate Inventory Data if any of the following conditions occur:

  1. The inventory item quantities, item unit cost, and item total cost are incorrect.
  2. Inventory data inconsistency exists in the Inventory Control (IC) tables as discussed in Article # 1110 in the section entitles Conditions That May Trigger the Demand for Inventory Recalculation.

The procedures to recalculate inventory data vary between the AccountMate for LAN and AccountMate for SQL/MSDE/Express versions. The following sections discuss the procedures that apply in each AccountMate platform.

Recalculate Inventory Data Procedures

Note: It is important to back up the company database prior to recalculating inventory.

To recalculate inventory data in AccountMate for SQL/MSDE/Express, perform the following:

  1. Access the Data Manager function from the Utilities menu.
  2. Enter the Supervisor password when prompted.
  3. In the Data Manager function, mark the Recalculate Inventory Data checkbox.
  4. Click the Process button.

To recalculate inventory data in AccountMate for LAN, perform the following:

  1. Access the Data Manager function from the Utilities menu.
  2. Enter the Supervisor password when prompted.
  3. In the Data Manager function, mark the Recalculate Inventory Data checkbox.
  4. Click the Process button.

Tables Validated and Updated during Inventory Data Recalculation

During inventory data recalculation, AccountMate recalculates first the on-order, booked, allocated, and in-process quantities in the Inventory Warehouse File (ICIWHS) table. The recalculated inventory item quantities are computed based on the Purchase Order (PO), Sales Order (SO), or Manufacturing Inventory transactions (MI). Below is a table that shows the source of the quantities used in the recalculation and the formula to recalculate the inventory data quantities in ICIWHS.

ICIWHS Values Source
(Recalculated From)
Formula
On-order quantity POPTRS (Purchase Order Line Item File) Order quantity – Received quantity
Booked quantity SOSTRS (Sales Order Line Item File),
RACSHP (Customer Shipment Line Item File)
Order quantity – Shipped quantity
Allocated quantity MIWTRS (Work Order Line Item File) Allocated quantity
In-process quantity MIWTRS (Work Order Line Item File) In-process quantity

The next step in the Recalculate Inventory Data process is the validation and recalculation of the ICIWHS on-hand quantity, unit cost, and total cost. The inventory data validation and recalculation process is as follows:

  1. Compares the inventory item on-hand quantity in Inventory Bin File (ICIBIN) with the quantities in Inventory Transaction Specification File (ICITSP), Inventory Specification Table (ICITSN), and Inventory Item Transaction Table (ICITRS)/Inventory Transaction File (ICICST). An on-hand quantity discrepancy between ICIBIN and ICITSP, ICITSN, or ICITRS (AM6.5) / ICICST (AM7) will trigger the procedure to update the on-hand quantity in ICIBIN. with the on-hand quantity in either ICITSP, ICITSN, or ICITRS / ICICST. See the note below for an explanation of how the function determines which table to use.

    Note: The inventory items stored in ICITSP, ICITSN, and ICITRS / ICICST are as follows:

    • ICITSP contains the lot-controlled, kit, and serialized inventory items. The serialized inventory items are stored in ICITSP starting only in AM7.
    • ICITSN (AM6.5 only) contains the serialized inventory items.
    • In AM6.5, ICITRS contains all other inventory items not in ICITSP or ICITSN; while in AM7, ICICST contains all other inventory items not in ICITSP.
  2. Deletes ICITRS or ICICST records that do not exist in ICIBIN.

  3. Deletes ICITRS or ICICST records with zero on-hand quantity.

  4. Updates the on-hand quantity, unit cost, and total cost in ICIWHS based on the on-hand quantity, computed unit cost and total cost in ICITRS or ICICST. The inventory item unit cost in ICIWHS is the item total cost divided by the total quantity in ICITRS or ICICST. The inventory item total cost in ICIWHS is the on-hand quantity multiplied by the item unit cost in ICITRS or ICICST.

    Note: When there is a difference in the inventory item’s on-hand quantity and cost in the ICITRS / ICICST and ICIWHS tables, AccountMate will automatically create a system-generated adjustment for the difference.

Inventory Data Recalculation Issues

Inventory data recalculation fails if there is an integrity issue with the Data Manager table (AMMGR.DBF) or an IC table validated during the recalculation process. Below are some examples of inventory data integrity issues:

  • Unmatched quantity or cost between the following tables:

    • ICIWHS and ICITRS / ICICST
    • ICITRS / ICICST and ICIBIN
    • ICIBIN and ICITSN/ICITSP
  • Unmatched quantity between ICIWHS and ICIBIN

  • Duplicate inventory item records between the following tables:

    • ICIWHS and ICITRS / ICICST
    • ICITRS / ICICST and ICIBIN
    • ICIWHS and ICIBIN
    • ICIBIN and ICITSN/ICITSP

The information provided in this document may help you to understand how the Recalculate Inventory Data feature works. Once you have a better understanding about the Recalculate Inventory Data feature, you can effectively manage the inventory data and clearly identify when, how, and what to do if inventory data inconsistencies occur.

Copyright © 2010 Ceravolo Consulting, Inc.
All Rights Reserved.