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!