FIFO Automate “Edit Dimensions” to assign Batchnumbers (FIFO) in ax 2009

Automate “Edit Dimensions” to 

assign Batchnumbers (FIFO)

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