Un-mark transactions marked for settlement

Scenario:

  • If you go to Vendors>>Functions>>Open transactions editing you can identify transactions that have been marked for settlement by the red hand in he "Is marked" column. Un-marking these transactions works only by finding the offset transaction and deleting the link from there.

Issue:

  • I came across transactions that have been identified as marked, however I did not know where the transactions are marked. The marking was created in one case because an update conflict occurred in the payment journal which cause the processing of the payment, casing the marked offset transaction to be lost but the transaction to be settled remained marked; in another case I think someone started to create a credit adjustment note, but deleted the credit adjustment before completing however the marking remained.

Investigation:

  • In the open transactions editing form (Vendors>>Functions>>Open transactions editing or Customers>>Functions>>Open transactions editing) and find the problematic transaction.
  • Go to Inquiries>>Specification and research the offset transactions. Try to un-mark the transaction with traditional means (i.e. Function>>Open transactions editing on the sales order etc.)
  • If you find a problem an want to continue with removing the marking then right click on the record in "Open transactions editing", select "Record info">>"Show all fields" and make a note of the Record-Id.
  • The next step must be done in the AOT using the table browser to directly access the data of certain tables. This is dangerous and definitely not good practice. However I do not know better and would appreciate if someone could show me how to do it better.
  • The table SpecTrans contains all transactions marked for settlement. Find th record-id you previously made a note of in the column RefRecId.
  • Delete the found SpecTrans record to remove the marking.

Better solutions?

If you have a cleaner and better solution, please tell me how you do it?

Thank you

Thomas

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

 

Alternative items in production

I do not quite understand why the alternative item functionality is only available for sales not for production. Consider the following scenario: Cream typically is bought in small volumes at around $3.00 per KG. A large order of a special product might allow us to buy larger volumes at say $2.00 per KG. The price of the standard cream should not be change for the costing of standard jobs because when the large job ceases, the small jobs need to pay for themselves.
An easy solution would be the use of an alternative item with an independent price, but of course the the two items can be used interchangeably on the shopfloor.
A small mod in one of the ProdUpd… classes (e.g. ProdUpdStartup.updateBOMConsumption()) activates alternative items for production. It functions by checking the availablility of the BOMItems when starting the job. Depending on the configuration it might be more appropriatly placed in another of ther ProdUpd… classes.
 
void updateBOMConsumption()
{
    […]
    Container                   c; //bw
    ItemId                      altItemId;    //bw
    InventDim                   altInventDim; //bw
    ;
    […]
    while(loop)
        {
            prodBOM = recListProdBOM.peek();
            if(!prodBOM.RecId)
            {
                loop = recListProdBOM.next();
                continue;
            }
            prodBOM.selectForUpdate(true);
//bw start
//Changed on 30 Apr 2007 by TW
/* Description:
Allow for alternative ingredients
*/
           //only if not yet consumed and no specific dimension has been assigned
           if(prodBOM.QtyInventCalc == prodBOM.RemainInventPhysical
              && (!prodBom.InventDimId || prodBom.InventDimId == InventDim::inventDimIdBlank()))
           {
                // use standard method to find an alternative item for the desired qty.
                c = InventTable::findAlternativeItem(prodBom.ItemId,prodBom.InventDim(),prodBom.QtyInventCalc);
                if (c)
                {
                    altItemId = conpeek(c,1);
                    altInventDim = conpeek(c,2);
                    prodBom.ItemId = altItemId;
                    if (altInventDim)
                        prodBom.InventDimId = altInventDim.inventDimId;
                    else
                        prodBom.InventDimId = InventDim::inventDimIdBlank();
                }
            }
//bw end
            prodBOMCalc = ProdBOMCalc::newBOMCalcData(BOMCalcData,
                                                      prodBOM,
                                                      consumpProposal,
                                                      prodParmStartUp.BOMAutoConsump,
                                                      !prodBOM.ConstantReleased,
                                                      NoYes::No,
                                                      true);
           […]
}
 

Allow changing of Inventory UnitId

Dynamics Ax does not allow you to change the Inventory UnitId of an item if there is stock or if there are open transactions. We as a food business wanted to change the inventory unit of canola oil from 200L barrels to 1000L Pallecons (or even better to Ltrs, avoiding future changes of the Inventory Stock Item), but as nearly every one of our products use canola oil the will be no point in time without open transactions (production orders) and deleting and reentering all open orders is also a nuisance. Therefore I decided to make a mod which allows us to change to unitId with open transactions and existing stock. The modifications only need to be done in the update method of the InventTableModule table and are marked with //bw.
IMPORTANT: The code here only updates InventTrans and InventSum. There are other tables with Quantities referring to the Inventory Unit such as InventJournalTrans which I do not update. This leads to inconsistencies which we can live with but you might not!
 
void update(boolean _updatePriceDate = true)
{
    InventTrans inventTrans;                //bw
    InventTableModule   old = this.orig();  //bw
    InventSum   inventSum;                  //bw
    boolean updPrice;
    FormRun         formRun;
    FormObjectSet   formObjSet;
    int     i;
    if (this.orig().Price     != this.Price     ||
        this.orig().Markup    != this.Markup    ||
        this.orig().PriceUnit != this.PriceUnit)
    {
        updPrice        = true;
        if (_updatePriceDate)
            this.PriceDate  = systemdateget();
    }
    ttsbegin;
//bw start
//Changed on 27 Apr 2007 by TW
/* Description:
allow update of units: modify stock level and inventJournals
Prerequisite: a unit conversion must exist between the old and the new UnitId.
The unit conversion will be used to adjust the quantities.
*/
    if (old.UnitId != this.UnitId && this.ModuleType==ModuleInventPurchSales::Invent) //only for inventory type
    {
        if(UnitConvert::canConvert(old.UnitId,this.UnitId,this.ItemId)//assured by validateField
            && UnitConvert::valueConvert(1,old.UnitId,this.UnitId,this.ItemId)!=1) //only bother if not one-to-one
        {
            //adjust inventTrans (this updates all transaction history of the item and might take a while)
            while select forupdate inventTrans where inventTrans.ItemId == this.ItemId
            {
                inventTrans.Qty = UnitConvert::valueConvert(inventTrans.Qty,old.UnitId,this.UnitId,this.ItemId);
                inventTrans.QtySettled = 
                    UnitConvert::valueConvert(inventTrans.QtySettled,old.UnitId,this.UnitId,this.ItemId);
                inventTrans.update();
            }
            //adjust inventSum
            select forupdate firstonly inventSum where inventSum.ItemId == this.ItemId;
            if (inventSum)
            {
                inventSum.AvailOrdered 
                    = UnitConvert::valueConvert(inventSum.AvailOrdered,old.UnitId,this.UnitId,this.ItemId);
                inventSum.AvailPhysical 
                    = UnitConvert::valueConvert(inventSum.AvailPhysical,old.UnitId,this.UnitId,this.ItemId);
                inventSum.OnOrder 
                    = UnitConvert::valueConvert(inventSum.OnOrder,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Ordered 
                    = UnitConvert::valueConvert(inventSum.Ordered,old.UnitId,this.UnitId,this.ItemId);
                inventSum.ReservOrdered 
                    = UnitConvert::valueConvert(inventSum.ReservOrdered,old.UnitId,this.UnitId,this.ItemId);
                inventSum.ReservPhysical 
                    = UnitConvert::valueConvert(inventSum.ReservPhysical,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Received 
                    = UnitConvert::valueConvert(inventSum.Received,old.UnitId,this.UnitId,this.ItemId);
                inventSum.PostedQty 
                    = UnitConvert::valueConvert(inventSum.PostedQty,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Deducted 
                    = UnitConvert::valueConvert(inventSum.Deducted,old.UnitId,this.UnitId,this.ItemId);
                inventSum.QuotationIssue 
                    = UnitConvert::valueConvert(inventSum.QuotationIssue,old.UnitId,this.UnitId,this.ItemId);
                inventSum.QuotationReceipt 
                    = UnitConvert::valueConvert(inventSum.QuotationReceipt,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Registered 
                    = UnitConvert::valueConvert(inventSum.Registered,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Picked 
                    = UnitConvert::valueConvert(inventSum.Picked,old.UnitId,this.UnitId,this.ItemId);
                inventSum.Arrived 
                    = UnitConvert::valueConvert(inventSum.Arrived,old.UnitId,this.UnitId,this.ItemId);
                inventSum.PhysicalInvent 
                    = UnitConvert::valueConvert(inventSum.PhysicalInvent,old.UnitId,this.UnitId,this.ItemId);
                inventSum.update();
            }
        }
    }
//bw end
    super();

    if (updPrice)
    {
        […]
    }

    ttscommit;
}