Inventory Valuation report for Sage 500 ERP

Inventory Valuation report provides the value of inventory for some or all the inventory items in a warehouse for the prior periods. If you are troubleshooting Inventory Valuation report and need to dig into the tables and data that this report is using here is some helpful information on where the report data is coming from.

If you leave the report in the preview screen all the displayed data is still stored in the work table timPPInvtValLstWrk. So the following query will return all the data used in the report and it can be very helpful in troubleshooting any issues related to inventory values and quantities: 

SELECT * FROM timPPInvtValLstWrk 

spimPPInvtValWrk stored procedure populates all the data required for  inventory valuation report into the table timPPInvtValLstWrk. 

Depending on the report format there are two Crystal files used for the report: 

Imzrf001.rpt       Prints Summary Inventory Valuation report.

Imzrf002.rpt       Prints Detail Inventory Valuation report.

 

When report is run from the beginning (start date is equal or prior to the date of the very first transaction) and ends with the current or future date Sage 500 is  retrieving the quantity and value from timInvtTranCost (Inventory Transaction Cost Distribution) table instead of timInvtTran (inventory transaction) table. This is because transactions for the same item can use different units of measure and in timInvtTran the quantities are stored in transaction's unit of measure and in timInvtTranCost quantities are stored only in stock unit of measure so this value is used for Inventory Valuation report.

In my next blog I will talk about how quantity on hand and value are calculated for Inventory Valuation report for a specific date range based on transaction or posting dates.

Anonymous