Share via

The beginning balance is incorrect on the Inventory Trial Bal / Valuation Variance (10.640.00) report after closing Inventory for the year

  • Article

This article provides a resolution for the issue that the beginning balance isn't correct issue on the Inventory Trial Bal / Valuation Variance (10.640.00) report.

Applies to:   Microsoft Dynamics SL
Original KB number:   2950119

Symptoms

When you return an inventory item by using an RFC sales order with a disposition of scrap, the BegBal for that item isn't calculated correctly if the Inventory Module is closed into the new fiscal year. When this issue occurs, the inventory item incorrectly appears on the Inventory Trial Bal / Valuation Variance (10.640.00) report even though the item balances on the Trial Balance (10.630.00) and the Inventory Valuation (10.620.00) reports.

Cause

A bug was reported where the beginning balance wasn't calculated correctly when closing Inventory for the year for items having return for scrap transactions.

Resolution

Run the following script to update the BegBal for the incorrect items. Replace the FiscYR value with your current fiscal year.

update I set i.user3=i.begbal, i.begbal=c.theBegBal
from ItemHist I
inner join (select sum(-ytdcogs+YTDCostAdjd-YTDCostIssd+YTDCostRcvd
+YTDCostTrsfrIn-YTDCostTrsfrOut) as theBegBal, invtid, siteid
from ItemHist where FiscYr<'2010'
group by invtid, siteid) c on I.InvtID=c.InvtID and i.SiteID=c.SiteID
inner join vr_10640_costvariance v on v.InvtID=I.InvtID and v.SiteID=I.SiteID
where I.FiscYr='2010' and i.User3=0

update I set i.user3=i.begqty, i.begqty=c.theBegQty
from Item2Hist I
inner join (select sum(-ytdQtysls+YTDqtyAdjd-YTDQtyIssd+YTDQtyRcvd
+YTDQtyTrsfrIn-YTDQtyTrsfrOut) as theBegqty, invtid, siteid
from Item2Hist where FiscYr<'2010'
group by invtid, siteid) c on I.InvtID=c.InvtID and i.SiteID=c.SiteID
inner join vr_10640_Qtyvariance v on v.InvtID=I.InvtID and v.SiteID=I.SiteID
where I.FiscYr='2010' and i.User3=0