Import sales order lines.

Posted by

For those who are in wholesale or retail, there are cases where you will have to sell multiple items (articles) to your customers. If this is happening from the retail point of sale (POS), there orders will be posted during the retail statement posting. Retail statement posting is the functionality that creates sales orders(basis how you configure in the store(channel) master), and invoices all of them by itself.

Cases where there are no POS, and no brick and mortar stores, and still you are doing sales, be it service or goods, you will have to create sales orders in Dynamics AX or D365. Creating a sales order with one or two line items will not take much of your time, but imagine if you are creating a sales order with 500 or 1000 different line items in it. Selecting items(articles) manually in the sales order lines will take a whole day to complete and what if you have hundreds of sales orders a day!

I got many requests from business team members to have a utility that will allow them to upload the sales orders lines in a button click. This will definitely bring a smile on their face and you could save a lot of their valuable time.

I have created a simple button in the sales order form as shown below.

In the click event, I am using the below code.

void clicked()
{
    SalesTable.VV_ImportSalesLine();
    SalesLine_DS.research();
    super();
}

This is calling a method in the table named SalesTable. You need to create a new method in the SalesTable and add the below code.

public void vv_ImportSalesLine()
{
    #AviFiles
    Dialog                      d;
    DialogField                 df1, df2;
    SysOperationProgress        vvProgress;
    SalesTable      			salesTable;
    SalesLine       			salesLine;
    InventDim       			inventDim, inventDim1,inventDim2;
    container 					readcon,readcon1;
    NumberSeq        			num;
    CommaTextIo                 file;
    container                   rec;
    InventItemBarcode           inventItemBarcode,inventItemBarcode1;
    InventTable                 inventTable;
    PriceDiscTable              priceDiscTable;
    InventTableModule           inventTableModule;
    ItemId                      itemid;
    ItemBarCode                 itemBarcode;
    EcoResItemColorName         colorId;
    EcoResItemSizeName          sizeId;
    SalesQty                    salesQty;
    SalesPrice                  salesPrice;
    Name                        name;
    str 50                      activityNumber;
    int                         row = 1;
    DiscAmount                  discAmount;
    DiscPct                     discPct;
    EcoResItemConfigurationName config;
    vvProgress = new SysOperationProgress();
    vvProgress.setCaption("Sales order line upload : " );
    vvProgress.setAnimation(#AviUpdate);
    vvProgress.setText("Uploading lines.");
    d       = new Dialog("Import Sales lines");
    df1     = d.addField(ExtendedTypeStr("FilenameOpen"));
    if (d.run())
    {
        file = new CommaTextIo(df1.value(), 'r');
        file.inFieldDelimiter(',');

        rec = file.read();
        rec = file.read();
        ttsBegin;
        while (rec)
        {
            try
            {
                itemBarcode         = "";
                itemid              = "";
                colorId             = "";
                sizeId              = "";
                salesQty            = 0;
                salesPrice          = 0;
                name                = "";
                activityNumber      = "";
                config              = "";
                itemBarcode         = conpeek(rec, 1);
                itemid              = conpeek(rec, 2);
                colorId             = conpeek(rec, 3);
                sizeId              = conpeek(rec, 4);
                SalesQty            = str2num(conpeek(rec, 5));
                SalesPrice          = str2num(conpeek(rec, 6));
                discAmount          = conpeek(rec, 7);
                discPct             = conpeek(rec, 8);
                name                = conpeek(rec, 9);
                activityNumber      = conpeek(rec, 10);
                config              = conpeek(rec, 11);
                //Order line
                salesLine.clear();
                salesLine.initValue();
                salesLine.initFromSalesTable(this);
                salesLine.SalesId                   = this.SalesId;
                if (itemBarcode != "")
                {
                     if(config)
                    {
                        inventItemBarcode1.clear();
                        inventDim2.clear();
                        select inventItemBarcode1
                            where inventItemBarcode1.itemBarCode == itemBarcode
                        join inventDim2
                            where inventDim2.inventDimId == inventItemBarcode1.inventDimId
                            && inventDim2.configId == config;

                         if (!inventItemBarcode1)
                         {
                                throw error(strFmt('Barcode - %1 does not exists.', itemBarcode));
                         }
                        inventDim                   = inventItemBarcode1.inventDim();
                        salesLine.itemId            = inventItemBarcode1.itemId;
                        colorId                     = inventDim.InventColorId;
                        sizeId                      = inventDim.InventSizeId;
                        config                      = inventDim.configId;
                     }
                    else
                    {
                        inventItemBarcode           = InventItemBarcode::findRetailBarcode(itemBarcode);
                        if (!inventItemBarcode)
                        {
                            throw error(strFmt('Barcode - %1 does not exists.', itemBarcode));
                        }
                        inventDim                   = inventItemBarcode.inventDim();
                        salesLine.itemId            = inventItemBarcode.itemId;
                        colorId                     = inventDim.InventColorId;
                        sizeId                      = inventDim.InventSizeId;
                        config                      = inventDim.configId;
                    }
                }
                if (!salesLine.ItemId)
                {
                    salesLine.ItemId        = itemid;
                }

                inventTable             = InventTable::find(salesLine.ItemId);
                if (!inventTable)
                {
                    throw error(strFmt("Item id - %1 doesnot exists.", salesLine.ItemId));
                }
                if(inventTable.HSNCodeTable_IN  ==  0)
                {
                    throw error(strFmt("Item id - %1 does not have HSNCode.", salesLine.ItemId));
                }
                salesLine.validateField(fieldnum(salesLine, ItemId));
                salesLine.modifiedField(fieldnum(salesLine, ItemId));
                salesLine.initFromInventTable(InventTable::find(salesLine.ItemId));
                salesLine.Name          =   salesLine.itemName();
                if(activityNumber   != "")
                {
                    salesLine.ActivityNumber    =   activityNumber;
                }
                salesLine.SalesQty      = salesQty;
                salesLine.validateField(fieldnum(salesLine, SalesQty));
                salesLine.modifiedField(fieldnum(salesLine, SalesQty));
                salesLine.SalesUnit     = inventTable.salesUnitId();
                salesLine.validateField(fieldnum(salesLine, SalesUnit));
                salesLine.modifiedField(fieldnum(salesLine, SalesUnit));
                salesLine::modifySalesQty(salesLine, salesLine.inventDim());
                InventMovement::bufferSetTransQtyUnit(salesLine);
                salesLine.validateField(fieldnum(salesLine, SalesUnit));
                salesLine.LineDisc          = discAmount;
                salesLine.LinePercent       = discPct;
                salesLine.BarCode           = itemBarcode;
                if(inventItemBarcode.RecId)
                salesLine.BarCodeType       = inventItemBarcode.barcodeSetupId;
                if(inventItemBarcode1.RecId)
                salesLine.BarCodeType       = inventItemBarcode1.barcodeSetupId;
                if(salesPrice)
                {
                    salesLine.SalesPrice = salesPrice;
                }
                if(!salesPrice)
                {
                    select * from priceDiscTable
                    join inventDim1
                    where inventDim1.inventDimId        == priceDiscTable.InventDimId
                    && inventDim1.InventSizeId          == sizeId
                    && inventDim1.InventColorId         == colorId
                    && inventDim1.configId              == config
                    &&  priceDiscTable.ItemRelation     == itemid
                    &&  priceDiscTable.relation         == PriceType::PriceSales
                    &&  priceDiscTable.FromDate         <= today()
                    &&  priceDiscTable.ToDate           >= today();
                    salesLine.SalesPrice                = priceDiscTable.Amount;
                }
                if(!salesLine.SalesPrice)
                {
                    select * from inventTableModule
                    where inventTableModule.ItemId      == itemid
                    && inventTableModule.ModuleType     == ModuleInventPurchSales::Sales;
                    salesLine.SalesPrice                = inventTableModule.Price;                    
                }
                salesLine.validateField(fieldnum(salesLine, salesPrice));
                salesLine.modifiedField(fieldnum(salesLine, salesPrice));
                inventDim.clear();
                inventDim.InventLocationId      = this.InventLocationId;
                inventDim.InventSiteId          = this.InventSiteId;
                if (colorId)
                {
                    inventDim.InventColorId        = colorId;
                }
                if (sizeId)
                {
                    inventDim.InventSizeId         = sizeId;
                }
                if (config)
                {
                    inventDim.configId         = config;
                }
                inventDim                       = InventDim::findOrCreate(inventDim);
                salesLine.InventDimId           = inventDim.InventDimId;
                if(inventItemBarcode.RecId)
                salesLine.RetailVariantId       = inventItemBarcode.RetailVariantId;
                if(inventItemBarcode1.RecId)
                salesLine.RetailVariantId       = inventItemBarcode1.RetailVariantId;
                salesLine.validateField(fieldnum(salesLine, InventDimId));
                salesLine.modifiedField(fieldnum(salesLine, InventDimId));
                salesLine.RemainSalesPhysical   = salesLine.SalesQty;
                salesLine.QtyOrdered            = salesLine.calcQtyOrdered();
                salesLine.RemainInventPhysical  = salesLine.QtyOrdered;
                salesLine.createLine(NoYes::No,NoYes::No,NoYes::No,NoYes::No,NoYes::Yes,NoYes::No);
                salesLine.update();
            rec = file.read();
            row ++;
            }
            catch
            {
                error("Error!");
                return;
            }
        }
        ttsCommit;
    }
    info(strFmt('%1 records processed!', row - 1)) ;
}

This will let you to upload the sales order lines from a csv file.

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 *