Sage 500 inventory history is tracked in multiple tables and by inventory calendar and fiscal calendar. If you are running into a situation where current quantities on hand and value are different when Inventory Valuation Report is run by different start dates but the same end date then the most likely issue is that inventory history tables are out of synch the inventory transaction detail tables. In order to set them in synch you will need to recalculate the fiscal and inventory calendar period balances. If the issue is related to fiscal calendar period balances it will surface when running Inventory Valuation report by Post Date. If the issue is related to the inventory calendar period balances it will surface when running Inventory Valuation report by Tran Date. Unfortunately in Sage 500 there is no front end client utility to recalculate the fiscal and inventory calendar period balances. However this is possible thru the ‘back end’ using Transact SQL queries. The SQL query below is written for a specific warehouse/item combination (WhseKey/ItemKey) but it can be modified to include more than one item or warehouse by inserting records into #InvtItemToProcess Table.

Here is the script to recalculate the fiscal and inventory calendar period balances:

Database Warning
These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.

IF OBJECT_ID('tempdb..#InvtItemToProcess') IS NULL

       BEGIN

     CREATE TABLE #InvtItemToProcess (

         WhseKey INTEGER NOT NULL,

         ItemKey INTEGER NOT NULL)

     CREATE UNIQUE CLUSTERED INDEX #InvtItemToProcess_cls ON #InvtItemToProcess(ItemKey, WhseKey)

       END

INSERT INTO #InvtItemToProcess Values (xxx,yyyyyy) -- (WhseKey,ItemKey)

--     replace xxx with your WhseKey and yyyyyy with your ItemKey. You can add rows if running for more than one item/warehouse

       DECLARE @_RetVal INT

          EXEC spimRecalcInvtHist 'SOA',1

-- change SOA to a company ID you are running script for. Also use 0 for diagnostic results; 1 to commit changes

EXEC spimRecalcPeriodHist 'SOA',2, @_RetVal

-- change xxx to company id running script for. also use:

-- 0 = Inventory Periods only

-- 1 = Fiscal Periods only

-- 2 = Fiscal and Inventory Periods

       Drop Table #InvtItemToProcess