Inventory Turns Report

static void InventoryTurns(Args _args) { itemId itemId; inventTrans inventTrans; inventSum inventSum; qty thisQty, qtyOnHand, totalDailyQtyOnHand, avgDailyQtyOnHand, usageQty; Amount thisValue, valueOnHand, totalDailyValueOnHand, avgDailyValueOnHand, usageValue; Real turnsByQty, turnsByValue; date lastDate, fromDate, toDate; ; //set the itemID and date range itemId = '00001'; fromDate = mkDate(1,1,2010); toDate = mkDate(31,12,2010); //****average daily on-hand lastDate = systemDateGet(); //initialize with today. //inventSum contains today's values for qty and value while select inventSum where inventSum.ItemId == itemId && inventSum.Closed == NoYes::No { qtyOnHand += inventSum.PhysicalInvent; valueOnHand += (inventSum.PhysicalValue + inventSum.PostedValue); } //starting with today, we work backward to the from date and manually calculate what the on-hand qty/value was each day while (lastDate >= fromDate) { if (lastDate <= toDate) //if we're within the date range, then sum the on-hand qty/value for determination of the average { //add one days worth of the current OnHand figures totalDailyQtyOnHand += qtyOnHand; totalDailyValueOnHand += valueOnHand; //update average calc avgDailyQtyOnHand = totalDailyQtyOnHand/((toDate + 1) - lastDate); avgDailyValueOnHand = totalDailyValueOnHand/((toDate + 1) - lastDate); } lastDate --; thisQty = 0; thisValue = 0; while select Qty, CostAmountPosted, CostAmountPhysical from inventTrans where inventTrans.ItemId == itemId && inventTrans.DatePhysical == lastDate { thisQty += inventTrans.Qty; //use the posted (financial) value if available if (inventTrans.CostAmountPosted != 0) { thisValue += inventTrans.CostAmountPosted; } else { thisValue += inventTrans.CostAmountPhysical; } } //calc the new onHand values qtyOnHand += -1 * thisQty; valueOnHand += -1 * thisValue; } //****annual usage while select inventTrans order by DatePhysical desc where inventTrans.ItemId == itemId && inventTrans.datePhysical >= fromDate //this should work in place of limits on statusIssue < 3 and statusReceipt < 3 && inventTrans.datePhysical <= toDate && (inventTrans.TransType == InventTransType::Sales || inventTrans.TransType == InventTransType::ProdLine || inventTrans.TransType == InventTransType::Project || inventTrans.TransType == InventTransType::InventTransaction) { usageQty += -1*inventTrans.Qty; usageValue += -1*inventTrans.CostAmountPhysical; } //****calc turns = usageQty divided by average on-hand qty if (avgDailyQtyOnHand == 0) //divide by zero protection { turnsByQty = 0; } else { turnsByQty = usageQty / avgDailyQtyOnHand; } if (avgDailyValueOnHand == 0) { turnsByValue = 0; } else { turnsByValue = usageValue / avgDailyValueOnHand; } info(strfmt("Item Id: %1, Turns by Qty: %2, Turns by Value: %3",itemId,turnsByQty,turnsByValue)); }