Situation:
- All finished products and sub-assemblies are batch controlled, but the assignment of the batches is not always known in advance and in other cases mixed batches are used which is also not handled automatically. More technical: Blank receipts are not allowed, but blank issuing is.
- For our purposes FIFO is good enough for assigning the batches to transactions after the fact.
Solution:
- The following methods identify existing batches and transactions without batchnumber assign.
- The positiv and negative quantities are matched and if nescessary some transactions are split.
- (The following static methods are part of the BWBatches class)
//bw start
//Changed on 30 May 2007 by TW
/* Description:
This method with fix the Batchnumber assignment of the item identified by the parameter
In the method the is only the identification of the positive and negative quantities.
In the end the auxillary method fixBatchAssignmentItemMatch is used to match positive and negative quantities.
And the auxillary method fixBatchAssignmentItemEdit is used to edit the transactions.
*/
public static void fixBatchAssignmentItem(ItemId _itemId)
{
InventBatch inventBatch;
InventOnhand inventOnhand;
InventDim inventDim;
InventTable item = InventTable::find(_itemId);
Qty remaining;
array positives = new array(Types::Container); //positives container: inventDimId, qty, prodDate
Int positivesI = 0;
array negatives = new array(Types::Container); //negatives container: inventDimId, qty, prodDate
Int negativesI = 0;
array matches; //matches container: pos inventDimId, neg inventDimId, qty, pos prodDate
Int i;
boolean hasInventDimIdBlank = false;
;
setprefix(item.ItemId);
//———————-find stock levels for all batch numbers——————————–
//—————add non-zero stock levels to postive or negative lists————————
while select inventBatch order by prodDate asc where inventBatch.itemId == item.ItemId
{
inventOnhand = InventOnhand::newInventBatch(inventBatch);
remaining = inventOnhand.physicalInvent();
if (remaining)
{
inventDim = null;
inventDim.inventBatchId = inventBatch.inventBatchId;
inventDim = InventDim::findOrCreate(inventDim);
if (remaining<0) //add negative stock levels to negatives array
{
negativesI++;
negatives.value(negativesI,[inventDim.inventDimId,remaining, inventBatch.prodDate]);
}
else if (remaining >0) //add positive stock levels to positives array
{
positivesI++;
positives.value(positivesI,[inventDim.inventDimId,remaining, inventBatch.prodDate]);
}
}
}
//add empty dimension last
inventDim = InventDim::findOrCreateBlank(false);
inventOnhand = InventOnhand::newItemDim(item.ItemId,inventDim,InventDimParm::activeDimFlag(item.DimGroupId));
remaining = inventOnhand.physicalInvent();
if (remaining<0) //add negative stock levels to negatives array
{
hasInventDimIdBlank=true;
negativesI++;
negatives.value(negativesI,[inventDim.inventDimId,remaining,1\1\1900]);
}
else if (remaining >0) //add positive stock levels to positives array
{
hasInventDimIdBlank=true;
positivesI++;
positives.value(positivesI,[inventDim.inventDimId,remaining,1\1\1900]);
}
//———————-match positives and negatives——————————–
//———————————————————————————–
if (negativesI || hasInventDimIdBlank)//only negatives und unassigned batches are a problem
{
matches = BWBatches::fixBatchAssignmentItemMatch(positives,negatives,item);
//———————-edit dimensions of transactions——————————
//———————————————————————————–
BWBatches::fixBatchAssignmentItemEdit(matches,item);
}
}
//bw end
//bw start
//Changed on 22 May 2007 by TW
/* Description:
match positive and negative stocklevels of an item
_positives, _negatives: array of containers with three elements [inventDimId, qty, prodDate]
return value: array of containers with 4 elements [pos inventDimId, neg inventDimId, qty, pos prodDate]
*/
client server private static array fixBatchAssignmentItemMatch(array _positives, array _negatives, InventTable _item)
{
int i,j;
Container posC;
Qty posQty;
Container negC;
Qty negQty;
Date batchDate,batchDate2;
array matches = new array(Types::Container); //container elements [pos inventDimId, neg inventDimId, qty, pos prodDate]
int matchesI;
;
for (i=1; i<=_positives.lastIndex(); i++)//run through all negative stock levels
{
posC = _positives.value(i); //_positives container elements [inventDimId, qty, prodDate]
posQty = conpeek(posC,2);
batchDate = conpeek(posC,3);
if (posQty)
{
for (j=1; j<=_negatives.lastIndex(); j++)//try to match negative stock levels with positiv stock levels
{
negC = _negatives.value(j); //_negatives container elements [inventDimId, qty, prodDate]
negQty = conpeek(negC,2);
batchDate2 = conpeek(negC,3);
if (negQty)
{
if (posQty >= -negQty) //more that enough available -> use all needed (-negQty)
{
matchesI++;
matches.value(matchesI,[conpeek(posC,1),conpeek(negC,1),-negQty, batchDate]);
negQty -= negQty; //reduce needed Qty (=0)
posQty += negQty; //reduce available Qty
}
else //if (posQty < -negQty) //not enough available -> use all available (posQty)
{
matchesI++;
matches.value(matchesI,[conpeek(posC,1),conpeek(negC,1),posQty, batchDate]);
negQty += posQty; //reduce needed Qty
posQty -= posQty; //reduce available Qty (=0)
}
_negatives.value(j,[conpeek(negC,1),negQty,batchDate2]); //update needed value
}
}
_positives.value(i,[conpeek(posC,1),posQty,batchDate]); //update available value
}
}
//finally check if any negatives remain unmatched
for (j=1; j<=_negatives.lastIndex(); j++)
{
negC = _negatives.value(j);
negQty = conpeek(negC,2);
if (negQty)
error(strfmt("Can not completely resolve stocklevels of %1. Please fix manually.",_item.ItemId),"",SysInfoAction_TableField::newBuffer(_item));
}
return matches;
}
//bw end
//bw start
//Changed on 22 May 2007 by TW
/* Description:
edit dimensions of transactions
_matches: array of containers with 4 elements [pos inventDimId, neg inventDimId, qty, pos prodDate]
*/
private static void fixBatchAssignmentItemEdit(array _matches, InventTable _item)
{
#OCCRetryCount
int i;
Qty remaining;
Date batchDate;
InventDim inventDimPos;
InventDim inventDimNeg;
InventTrans inventTrans;
;
try
{
ttsbegin;
for (i=1;i<=_matches.lastIndex();i++)
{
//_matches container elements [pos inventDimId, neg inventDimId, qty, pos prodDate]
inventDimPos = InventDim::find(conpeek(_matches.value(i),1));
inventDimNeg = InventDim::find(conpeek(_matches.value(i),2));
remaining = conpeek(_matches.value(i),3);
batchDate = conpeek(_matches.value(i),4);
remaining = -remaining; //running from the negative perspective
//fix negative (i.e. issues with unassigned or wrongly assign batch numbers)
//there might be many transactions with the same BatchId
if (inventDimNeg.inventBatchId <= inventDimPos.inventBatchId)
{
//the issued batch (or blank batch) will be fullfilled by newer batch
//run through through all transactions by order by date DESCENDING
while select forupdate inventTrans order by DatePhysical desc
where inventTrans.ItemId == _item.ItemId
&& inventTrans.inventDimId == inventDimNeg.inventDimId
{
//execute changes:
remaining = BWBatches::auxBatchAssignment(inventTrans,remaining,inventDimPos,inventDimNeg);
if (!remaining)
break;
}
}
else
{
//the issued batch will be fullfilled by older batch
//run through through all transactions by order by date ASCENDING
while select forupdate inventTrans order by DatePhysical asc
where inventTrans.ItemId == _item.ItemId
&& inventTrans.inventDimId == inventDimNeg.inventDimId
&& inventTrans.DatePhysical >= batchDate
{
//execute changes:
remaining = BWBatches::auxBatchAssignment(inventTrans,remaining,inventDimPos,inventDimNeg);
if (!remaining)
break;
}
}
if (remaining)
error(strfmt("Can not completely resolve stocklevels of %1. Please fix manually.",_item.ItemId),"",SysInfoAction_TableField::newBuffer(_item));
}
ttscommit;
}
catch (Exception::Deadlock)
{
if (xSession::currentRetryCount() >= #RetryNum)
throw Exception::UpdateConflictNotRecovered;
else
retry;
}
}
//bw end
//bw start
//Changed on 30 May 2007 by TW
/* Description:
Auxiliary method that assign a new batchnumber to a transaction and splits the transaction if nescessary
*/
private static qty auxBatchAssignment(InventTrans _inventTrans, Qty _remaining, InventDim _inventDimPos, InventDim _inventDimNeg)
{
InventTrans inventTrans = _inventTrans;
Qty remaining = _remaining;
InventDim inventDimPos=_inventDimPos;
InventDim inventDimNeg=_inventDimNeg;
InventTransSplit inventTransSplit;
;
if (remaining>0 || inventTrans.Qty>0)
{
error(strfmt("auxBatchAssignment assertion: remaining (%1) and inventTrans.Qty (%2) are expected to be less than zero.",remaining,inventTrans.Qty));
return 0;
}
if (-inventTrans.Qty <= -remaining)
{//The transactions qty is smaller than the required quantity
// -> use all (i.e. change dimension for entire transaction)
inventTrans.inventDimId = inventDimPos.inventDimId; //assign the new batch number
inventTrans.update();
BWBatches::auxBatchAssignmentRef(inventTrans); //change associated sales and production lines
info(strfmt("%3: %2 %4 -> %2 %5",inventTrans.InventTransId, inventTrans.Qty, inventTrans.DatePhysical,
inventDimNeg.inventBatchId?inventDimNeg.inventBatchId:inventDimNeg.inventDimId,inventDimPos.inventBatchId?inventDimPos.inventBatchId:inventDimPos.inventDimId));
remaining -= inventTrans.Qty;
}
else if (-inventTrans.Qty > -remaining)
{//The transactions qty is larger than the required quantity
// -> use only part of the transaction (i.e. it is nescessary to plit the transaction!)
inventTransSplit = InventTransSplit::newInventTransSplit(inventTrans.RecId);
warning(strfmt(strfmt("Spliting %1: %2 %5 -> %3 + %4 %5",inventTrans.DatePhysical, inventTrans.Qty, remaining, inventTrans.Qty – remaining, (inventDimNeg.inventBatchId?inventDimNeg.inventBatchId:inventDimNeg.inventDimId))));
inventTransSplit.parmSplitQty(inventTrans.Qty – remaining); //inventTrans will contain the desired qty
try
{
inventTransSplit.run();
}
catch (Exception::Error)
{
error(strfmt("Can not split transaction. Please fix stocklevels of %1 manually.",inventTrans.ItemId),"",SysInfoAction_TableField::newBuffer(InventTable::find(inventTrans.ItemId)));
return 0;
}
inventTrans = InventTrans::findRecId(inventTrans.RecId,true); //reload the record (for update)
inventTrans.inventDimId = inventDimPos.inventDimId; //assign the new batch number
inventTrans.update();
BWBatches::auxBatchAssignmentRef(inventTrans); //change associated sales and production lines
info(strfmt("%3: %2 %4 -> %2 %5",inventTrans.InventTransId, inventTrans.Qty, inventTrans.DatePhysical,
inventDimNeg.inventBatchId?inventDimNeg.inventBatchId:inventDimNeg.inventDimId,inventDimPos.inventBatchId?inventDimPos.inventBatchId:inventDimPos.inventDimId));
remaining -= inventTrans.Qty;
}
return remaining;
}
//bw end
//bw start
//Changed on 30 May 2007 by TW
/* Description:
The auxilary method replicated the inventTrans changes to sales lines and production lines.
*/
public static void auxBatchAssignmentRef(InventTrans _inventTrans)
{
SalesLine salesline;
ProdBom prodBOM
;
select forupdate firstonly salesline where salesline.InventTransId == _inventTrans.InventTransId;
if (salesline)
{
if (salesline.QtyOrdered == _inventTrans.Qty && salesline.InventDimId != _inventTrans.inventDimId)
{
salesline.InventDimId = _inventTrans.inventDimId;
salesline.update();
}
}
else
{
select forupdate firstonly prodBOM where prodBOM.InventTransId == _inventTrans.InventTransId;
if (prodBOM)
{
if (prodBOM.QtyInventCalc == _inventTrans.Qty && prodBOM.InventDimId != _inventTrans.inventDimId)
{
prodBOM.InventDimId = _inventTrans.inventDimId;
prodBOM.update();
}
}
}
}
//bw end
