Inventory Valuation report allows you to determine inventory value according to a specific date range based on transaction or posting dates. To generate the Past Period Inventory Valuation Report, the user will have to define the date of valuation and base the valuation on either the Transaction Date or Post Date.

Case 'Transaction Date'

We are looking at the QtyOnHand and Value in timInvtCalPerHist for the end of period immediately prior to the specified Valuation Date and adding the following quantity and the amount  based on the inventory transaction type where Transaction Date is between timInvtPeriod.StartDate and  timInvtPeriod.EndDate:

Adjustment Quantity = timInvtHist.AdjQty,                Adjustment Amount = timInvtHist.AdjAmt

Physical Count Quantity = timInvtHist.PhysCountQty,                Physical Count Amount = timInvtHist. PhysCountAmt

Received Quantity = timInvtHist.RcvdQty,                Received Amount = timInvtHist.RcvdAmt

Transferred Quantity = (timInvtHist.TrnsfrInQty - timInvtHist.TrnsfrOutQty),                Transferred Amount = (timInvtHist.TrnsfrInAmt - timInvtHist.TrnsfrOutAmt)

Kit Assembled Quantity = timInvtHist. KitAssemblyQty,                 Kit Assembled Amount = timInvtHist. KitAssemblyAmt

Sales Quantity = timInvtHist.SalesQty, COS (Cost of Sales) Amount = timInvtHist.COSAmt

and here is the formula:

Quantity On Hand = Quantity On Hand + Adjustment Quantity+ Physical Count Quantity+ Received Quantity + Transferred Quantity - Sales Quantity + Kit Assembled Quantity - Kit Assembled Amount 

Value = Value + Adjustment Amount + Physical Count Amount + Received Amount + Transferred Amount - Kit Assembled Amount - COGS Amount

Case 'Post Date'

When the  report is run by post date the formula is the same.

We are looking for FiscYear and FiscPer from tglFiscalPeriod where tglFiscalPeriod,Status = “Closed” and tglFiscalPeriod,EndDate is immediately prior to the  specified Valuation Date to get

Quantity On Hand = timInvtFiscPerHist.QtyOnHand and Value = timInvtFiscPerHist.Value

and then adding the quantity and the amount  based on the inventory transaction type where Post Date is between timInvtPeriod.StartDate and  timInvtPeriod.EndDate from the table timInvtHist the same way as it is calculated in the case of 'Transaction Date' above to arrive to the same formula:

Quantity On Hand = Quantity On Hand + Adjustment Quantity+ Physical Count Quantity+ Received Quantity + Transferred Quantity - Sales Quantity + Kit Assembled Quantity - Kit Assembled Amount 

Value = Value + Adjustment Amount + Physical Count Amount + Received Amount + Transferred Amount - Kit Assembled Amount - COGS Amount