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)); }
We have situations where there are lots of open transactions that need to be settled against each other. This can be the case if auto settlement is turned off. One solution is to add a "Mark All" button to the custOpenTrans or vendOpenTrans forms. This button "checks" the mark checkbox on every line. The user can then uncheck several lines if needed and Update to settle the lines. The code below is an example of what we used on the open vendor transaction screen. The code is very similar on the AR side. One note: I used vendTable.AccountNum in the code below. That should be generalized to work with any buffer that is passed into the open trans form. void customMarkAll() { VendTransOpen localVendTransOpen; VendTrans localVendTrans; container conSum; int linesProcessed; ; //show wait cursor startLengthyOperation(); element.lock(); //remove all prior markings specOffsetVoucher.deleteSpe...