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