Counting journal creation in Dynamics AX

Posted by

Physical stock verification and matching that with the ERP stock is a routine activity in retail,distribution or any other commodity based businesses. In most cases, there will be full stock count for a particular warehouse or an aisles or even a bin location, the counted stock has to be updated in Dynamics AX / D365 for the audit team to analyze the differences if any and finally the finance team to approve and then post the counting journal.

After creating the counting journal in Dynamics AX, the system on-hand should be created. After that, the counted quantity needs to be updated in the same counting journal. If you are doing it for a warehouse or even for an outlet, manually updating it will take most of your time. What if there is a utility that can take care of the upload, if you want to do it with your barcode or even with the item-size-color-style-configuration combinations this utility will take care of it.

Below is the code to achieve this.

Method 1 Class declaration

class Vv_ImportCountJournalLineWithBarcode
{
    Dialog                      d;
    DialogField                 df;
    CommaTextIo                 file;
    container                   rec;
    int                         row;
    real                        lineNum;
    InventJournalType           journalType;
    str                         jourType;
    ItemBarCode                 itemBarcode;
    ItemId                      itemId;
    EcoResItemColorName         color;
    EcoResItemSizeName          size;
    EcoResItemStyleName         style;
    InventSiteId                site;
    InventLocationId            warehouse;
    WMSLocationId               location;
    InventQtyCounted            qty;
    date                        countedDate;
    InventDimId                 inventDimId;
    JournalId                   JournalId;
    AxInventJournalTrans        axJournalTrans;
    InventJournalTable          inventJournalTable;
    InventJournalTrans          inventJournalTrans, inventJournalTransRun,journalTransNotcounted;
    InventDim                   inventDim, inventDimBarcode;
    InventItemBarcode           inventItemBarcode;
    InventTable                 inventTable;
}

Method 2 InsertCountedLines

void insertCountedLines()
{
    ;
    ttsBegin;
    itemBarcode = conPeek(rec, 1);
    itemId      = conPeek(rec, 2);
    color       = conPeek(rec, 3);
    size        = conPeek(rec, 4);
    style       = conPeek(rec, 5);
    site        = conPeek(rec, 6);
    warehouse   = conPeek(rec, 7);
    location    = conPeek(rec, 8);
    qty         = conPeek(rec, 9);
    countedDate = str2Date(conPeek(rec, 10), 123);
    inventJournalTrans.clear();
    inventJournalTrans.initValue();
    inventJournalTrans.initFromInventJournalTable(inventJournalTable);
    inventJournalTrans.LineNum      = InventJournalTrans::lastLineNum(inventJournalTable.JournalId)+1;
    inventJournalTrans.TransDate    = countedDate;//today();
    inventJournalTrans.barcode      = itemBarcode;

    if(inventJournalTrans.barcode != '')
    {
        select inventItemBarcode
            join inventDimBarcode
            where inventItemBarcode.itemBarCode == itemBarcode
                && inventDimBarcode.inventDimId == inventItemBarcode.inventDimId;

        itemId  = inventItemBarcode.itemId;
        color   = inventDimBarcode.InventColorId;
        size    = inventDimBarcode.InventSizeId;
        style   = inventDimBarcode.InventStyleId;
    }

    inventTable = InventTable::find(itemId);
    inventJournalTrans.ItemId           = itemId;        

    //Create Invent Dim :: Start
    inventDim.clear();
    inventDim.InventColorId         = color;
    inventDim.InventSizeId          = size;
    inventDim.InventStyleId         = style;
    inventDim.InventSiteId          = site;
    inventDim.InventLocationId      = warehouse;
    inventDim.wMSLocationId         = location;

    inventDim                       = InventDim::findOrCreate(inventDim);
    //Create Invent Dim :: End

    inventJournalTrans.barcode      = InventItemBarcode::findByProductDimensions(itemId, inventDim, NoYes::No, NoYes::No).itemBarCode;
    inventJournalTrans.InventDimId  = inventDim.inventDimId;
    inventJournalTrans.Counted      = qty;
    inventJournalTrans.Qty          = inventJournalTrans.Counted - inventJournalTrans.InventOnHand;

    inventJournalTrans.insert();
    ttsCommit;
}

Method 3 parmJournalTable

InventJournalTable parmJournalTable(InventJournalTable _InventJournalTable = InventJournalTable)
{
    inventJournalTable = _InventJournalTable;
    return inventJournalTable;
}

Method 4 run

public void run()
{
    d   = new Dialog("Import Count Journal Line ");
    df  = d.addField(ExtendedTypeStr("FilenameOpen"));

    if (d.run())
    {
        file = new CommaTextIo(df.value(), 'r');
        file.inFieldDelimiter(',');
        rec = file.read();
        rec = file.read();
        inventJournalTable  =   this.parmJournalTable();
        while (rec)
        {
            try
            {
                itemBarcode = conPeek(rec, 1)!=''?conPeek(rec, 1):'*';
                itemId      = conPeek(rec, 2)!=''?conPeek(rec, 2):'*';
                color       = conPeek(rec, 3);
                size        = conPeek(rec, 4);
                style       = conPeek(rec, 5);
                site        = conPeek(rec, 6);
                warehouse   = conPeek(rec, 7);
                location    = conPeek(rec, 8);
                qty         = conPeek(rec, 9);
                countedDate = str2Date(conPeek(rec, 10), 123);

                if(itemBarcode!='*')
                {
                    select inventItemBarcode
                        join inventDimBarcode
                        where inventItemBarcode.itemBarCode == itemBarcode
                            && inventDimBarcode.inventDimId  == inventItemBarcode.inventDimId
                            ;

                    itemId  = inventItemBarcode.itemId;
                    color   = inventDimBarcode.InventColorId;
                    size    = inventDimBarcode.InventSizeId;
                    style   = inventDimBarcode.InventStyleId;                    
                    inventDim.clear();
                    inventDim.InventColorId         = color;
                    inventDim.InventSizeId          = size;
                    inventDim.InventStyleId         = style;
                    inventDim.InventSiteId          = site;
                    inventDim.InventLocationId      = warehouse;
                    inventDim.wMSLocationId         = location;
                    inventDim                       = InventDim::findOrCreate(inventDim);
                }
                if(itemId && itemBarcode=='*' )
                {                    
                    inventDim.clear();
                    inventDim.InventColorId         = color;
                    inventDim.InventSizeId          = size;
                    inventDim.InventStyleId         = style;
                    inventDim.InventSiteId          = site;
                    inventDim.InventLocationId      = warehouse;
                    inventDim.wMSLocationId         = location;
                    inventDim                       = InventDim::findOrCreate(inventDim);
                }
                select inventJournalTransRun
                    where inventJournalTransRun.JournalId       == inventJournalTable.JournalId                        
                        && inventJournalTransRun.ItemId         == itemId
                        && inventJournalTransRun.InventDimId    == inventDim.inventDimId;

                if(inventJournalTransRun)
                {
                    this.updateCountJournalLine(inventJournalTransRun.JournalId, inventJournalTransRun.barcode, inventJournalTransRun.ItemId, inventJournalTransRun.InventDimId);
                }
                else
                {
                    this.insertCountJournalLine();
                }
            }
            catch
            {
                Error(strfmt("Upload Failed at barcode  %1", itemBarcode));
            }
            rec = file.read();
            row ++;
        }
    }    
    info(strFmt('%1 records processed', row)) ;
}

Method 5 updateCountJournalLine

void updateCountJournalLine(JournalId   _JournalId      = JournalId,
                            ItemBarCode _ItemBarCode    = ItemBarCode,
                            ItemID      _ItemId         = ItemID,
                            InventDimId _InventDimId    = InventDimId)
{
    InventJournalTrans              inventJournalTransUpd;

     select forUpdate inventJournalTransUpd
       where inventJournalTransUpd.JournalId    == _JournalId
          && (inventJournalTransUpd.barcode      == _ItemBarCode
          && (inventJournalTransUpd.itemid       == _ItemId
          && inventJournalTransUpd.InventDimid  == _InventDimId));

    if (inventJournalTransUpd.barcode || (inventJournalTransUpd.itemId && inventJournalTransUpd.InventDimId))
    {
        if (conPeek(rec, 9) && conPeek(rec, 9) != '0')
        {
            inventJournalTransUpd.Counted   += conPeek(rec, 9);
            inventJournalTransUpd.Qty       = inventJournalTransUpd.Counted - inventJournalTransUpd.InventOnHand;
            ttsBegin;
            inventJournalTransUpd.update();
            ttsCommit;
        }
    }
}

Method 6 updateJournalNotCounted

void updateJournalNotCounted()
{
    InventJournalTrans              inventJournalTransLoop;
    InventJournalTable              inventJournTable;
    inventJournTable                = this.parmJournalTable();

    while select forUpdate inventJournalTransLoop
       where inventJournalTransLoop.JournalId   == inventJournTable.JournalId
        && inventJournalTransLoop.Qty           ==  0
        && inventJournalTransLoop.Counted       ==  0
        && inventJournalTransLoop.InventOnHand  !=  0
        {
            inventJournalTransLoop.Counted   = 0;
            inventJournalTransLoop.Qty       = inventJournalTransLoop.Counted - inventJournalTransLoop.InventOnHand;
            ttsBegin;
            inventJournalTransLoop.update();
            ttsCommit;
        }
}

Method 7 main

static client void main(Args args)
{
    Vv_ImportCountJournalLineWithBarcode    countJournal;
    InventJournalTable                      inventJournalTableLoc;
    countJournal           	= new Vv_ImportCountJournalLineWithBarcode();
    inventJournalTableLoc   = args.record() as InventJournalTable;
    countJournal.parmJournalTable(inventJournalTableLoc);
    countJournal.run();
    countJournal.updateJournalNotCounted();
}

As I say always, before you deploy this in your production system, please test it properly in the UAT and pre-production environments.

Happy Daxing!

Leave a Reply

Your email address will not be published. Required fields are marked *