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!