Creating inventory quantity adjustments from a stock count dashboard

For those still following this thread, I have come up with a solution based around DMT. Still in Pilot, but pretty much finalised now. It involves 2 stages, first we create a DMT-compatible CSV file from a button-click action on the dashboard and writte this to a specific Windows directory. Second, a batch command file that contains the DMT command line is run on a Windows schedule to import the CSV file.

OK, here are the details…

First, in the button-click script we need to define a few variables and then iterate through our dashboard results:

        var csvLines = new StringBuilder(); //holds the CSV lines we want to write
        string serialList = string.Empty; //holds info on any serial-tracked parts that can’t be adjusted programatically

        int qtyAdjRowCount = 0; //keeps track of how many rows are valid for a qty adjustment
        int serialRowCount = 0; //keeps track of how many rows are serial-tracked
        EpiDataView edvMyDataView = (EpiDataView)(oTrans.EpiDataViews["V_DKHstockCount_1View1"]);
        foreach (DataRow row in edvMyDataView.dataView.Table.Rows)
        {

OK, now for each row we need to decide if we are going to do a quantity adjustment or not. In my dashboard there is a field called Discrepency (yes, I know I misspelled it!) that performs a simple subtraction on the physical count quantity that the user enters and the On Hand quantity for that part, warehouse and bin. If this is non-zero and the part is not serial-tracked we add this to csvLines. If the part is serial tracked and has a non-zero discrepancy we need to keep track of it by adding the details to serialList so we can alert the user in the next step…

                if (Convert.ToInt32(row["Calculated_Discrepency"]) != 0)
                {
                    var fldCompany = row["Part_Company"].ToString();
                    var fldPlant = row["PlantWhse_Plant"].ToString();
                    var fldPartNum = row["Part_PartNum"].ToString();
                    var fldAdjustQty = row["Calculated_Discrepency"].ToString();
                    var fldUOM = row["Part_IUM"].ToString();
                    var fldWarehouse = row["PartWhse_WarehouseCode"].ToString();
                    var fldBinNum = row["Calculated_BinNumber"].ToString();
                    var fldReason = "DISCR";
                    var fldSerial = (bool)row["Part_TrackSerialNum"];
                    if (fldSerial == false)
                    {
                        var newLine = string.Format("{0},{1},{2},{3},{4},{5},{6},{7}", fldCompany, fldPlant, fldPartNum, fldAdjustQty, fldUOM, fldWarehouse, fldBinNum, fldReason);
                        csvLines.AppendLine(newLine);
                        qtyAdjRowCount ++;
                    }
                    if (fldSerial == true)
                    {
                        serialList = serialList + '\n' + "Part: " + fldPartNum + " Warehouse: " + fldWarehouse + " Bin: " + fldBinNum + " Adjust Qty: " + fldAdjustQty + " " + fldUOM;
                        serialRowCount ++;
                    }
                }

Now, we’ve got all the info we need to write the CSV file, so we close the iteration and proceed thus:

        if (qtyAdjRowCount > 0)
        {
            var filePath = @"\\dk-epicor10\DMT-Autorun\Queue\InvQtyAdjPilot.csv";
            if (File.Exists(filePath))
            {
                File.AppendAllText(filePath, csvLines.ToString());
            }
            else
            {
                var csvHeader = string.Format("{0},{1},{2},{3},{4},{5},{6},{7}", "Company", "Plant", "PartNum", "AdjustQuantity", "UnitOfMeasure", "WareHseCode", "BinNum", "ReasonCode");
                var csvFile = csvHeader + '\n' + csvLines;
                File.WriteAllText(filePath, csvFile.ToString());
            }
        }

So what this does is it checks to see if the named CSV file already exists in the named directory and if it does it appends csvLines to it. If not, the file is created with a header string plus csvLines. If we picked up any serial-tracked parts that need adjusting we have to alert the user in the next step. I have chosen to simply throw this up as a MessageBox message, thus:

        if (serialRowCount > 0)
        {
            MessageBox.Show("The following quantity adjustments could not be made because the parts are serial tracked:" + '\n' + serialList);
        }

I should also mention that you will need to include some sort of control to prevent duplication if the user clicks the button twice. I have chosen to add a couple of UD fields to PartWhse and use these to control the workflow in the dashboard. This should be done within the iteration through the relevant dashboard rows:

                row.BeginEdit();
                row["PartWhse_CountInProg_c"] = false;
                row["PartWhse_CountComplete_c"] = true;
                row["RowMod"] = "U";
                row.EndEdit();

Now we are ready to import the CSV file. I have created a shared directory on our ERP10 server called \DMT-Autorun, with the subdirectories \Queue, Process and \Logs. The code above writes the CSV file to \Queue and then my batch file, when the schedule calls for it to be run, moves it to \Process and runs the DMT command on it. Any logs produced are written to \Logs. This is the code I used…

@echo off
if exist e:\DMT-Autorun\Queue\InvQtyAdjPilot.csv goto RUNDMT
goto END

:RUNDMT
move e:\DMT-Autorun\Queue\InvQtyAdjPilot.csv e:\DMT-Autorun\Process\
e:
cd \Epicor\ERP10\LocalClients\EpicorERPPilot
DMT.exe -NoUI=True -Import="Quantity Adjustment" -Source=e:\DMT-Autorun\Process\InvQtyAdjPilot.csv -Add=True -Update=False -user=manager -pass=[your manager password] -ConnectionUrl="net.tcp://localhost/EpicorERPPilot"
cd \DMT-Autorun\Process\
if exist DMT_NoUIImport_Error.log rename DMT_NoUIImport_Error.log "InvQtyAdjPilot-%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%%time:~3,2%%time:~6,2%-FileErrors.txt"
if exist InvQtyAdjPilot.csv.CompleteLog.txt rename InvQtyAdjPilot.csv.CompleteLog.txt "InvQtyAdjPilot-%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%%time:~3,2%%time:~6,2%-CompleteLog.txt"
if exist InvQtyAdjPilot.csv.Errors.txt rename InvQtyAdjPilot.csv.Errors.txt "InvQtyAdjPilot-%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%%time:~3,2%%time:~6,2%-Errors.txt"
if exist InvQtyAdjPilot.csv.Errors.Reprocess.csv rename InvQtyAdjPilot.csv.Errors.Reprocess.csv "InvQtyAdjPilot-%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%%time:~3,2%%time:~6,2%-Errors.Reprocess.csv"
del InvQtyAdjPilot.csv
move InvQtyAdjPilot* \DMT-Autorun\Logs\
exit

:END
exit

I hope this helps.