The are many code snippets which you can find easily over the internet to import sales orders from a text file or from excel. The purpose of this post is to share my experience of uploading data into sales order form from two different text files.
void ImportSalesOrders()
{
Container con;
CustTable custTable;
SalesTable salesTable;
SalesLine salesLine;
Headers headers;
Stock details;
num newSalesId;
Description custDescription;
InventTable inventTable;
InventDim inventDim;
InventSiteId inventSiteId;
InventLocationId inventLocationId;
CustAccount custAccount;
real itemPrice;
ItemId itemId;
InventDim frominventDim,ToinventDim;
int x,y;
NumberSeq num;
SysOperationProgress progressbar = new SysOperationProgress();
int i;
;
progressbar.setCaption(‘Importing Journals’);
progressbar.setAnimation(filenameHeader);
progressbar.setTotal(30000);
for (i = 1; i <= 30000; i++)
{
progressbar.setText(strfmt(“@SYS105740”, i));
progressbar.setCount(i, 1);
}
this.importData();
ttsbegin;
while select headers
{
SalesTable = SalesTable::findDocumentNumber(headers.DocumentNumber);
if(!SalesTable)
{
//create sales table
salesTable.initValue();
num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
newSalesId = num.num();
if (salesTable::exist(newSalesId))
{
num.abort();
throw error(“@SYS23020”);
}
salesTable.SalesId = newSalesId;
num.used();
if(custDescription != “”)
{
salesTable.SalesName = custDescription;
}
custAccount = headers.CustAccount;
//fetch the customer details from cust table using account no selected from drop-down list
select * from custTable where custTable.AccountNum == custAccount;
salesTable.CustAccount = custTable.AccountNum;
salesTable.InvoiceAccount = custTable.InvoiceAccount;
salesTable.SalesType = SalesType::Sales;
salesTable.SalesStatus = SalesStatus::None;
salesTable.CurrencyCode = custTable.Currency;
salesTable.CustGroup = custTable.CustGroup;
salesTable.DeliveryDateControlType = SalesDeliveryDateControlType::SalesLeadTime;
//Set dates as systemDateTime
salesTable.ReceiptDateRequested = systemdateget();
salesTable.ShippingDateRequested = systemdateget();
salesTable.LanguageId = ‘en-us’;
salesTable.DlvMode = ’10’;
inventSiteId = headers.InventSiteId;
inventLocationId = headers.InventSiteId;
salesTable.InventSiteId = inventSiteId;
salesTable.InventLocationId = inventLocationId;
salesTable.DocumentNumber = headers.DocumentNumber;
//Craete Sales Order
salesTable.insert();
while select details
where headers.DocumentNumber == details.DocumentNumber
{
salesLine.clear();
// Create Sales Order Line
salesLine.SalesId = salesTable.SalesId;
salesLine.initFromSalesTable(salesTable);
itemId = details.ItemId;
salesLine.ItemId = itemId;
salesLine.SalesUnit = InventTable::find(salesLine.ItemId).salesUnitId();
select * from inventTable where inventTable.ItemId == salesLine.ItemId;
salesLine.initFromInventTable(inventTable);
inventDim.clear();
inventDim.InventSiteId = inventSiteId;
inventDim.InventLocationId = inventSiteId;
salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
//Set Qty as 1 by default
salesLine.SalesQty = details.Qty;
salesLine.ConfirmedDlv = salesTable.ShippingDateConfirmed;
salesLine.lineNum = SalesLine::lastLineNum(salesLine.salesId) + 1.0;
salesLine.RemainInventPhysical = 1;
salesLine.RemainSalesPhysical = 1;
salesLine.DlvMode = ’10’;
salesLine.SalesStatus = SalesStatus::None;
salesLine.SalesPrice = details.UnitPrice;
salesLine.LineAmount = salesLine.SalesQty * salesLine.SalesPrice;
//Insert sales line items
salesLine.insert();
}
}
++x;
}
ttscommit;
info(strfmt(“%1 Header(s) imported with their details”, x));
}
void importData()
{
Container con;
// I created these two table headers and details to make the import process fast I had huge data files to import
Headers headers;
Stock details;
TextBuffer tbHeader = new TextBuffer();
TextBuffer tbDetails = new TextBuffer();
int cntHeader, cntDetails;
int numLinesHeader, numLinesDetails;
container inLineHeader, inLineDetails;
int i;
;
tbHeader.fromFile(filenameHeader);
tbDetails.fromFile(filenameDetail);
numLinesHeader = tbHeader.numLines();
numLinesDetails = tbDetails.numLines();
delete_from headers;
delete_from details;
if(numLinesHeader)
{
ttsbegin;
inLineHeader = str2Con(tbHeader.nextToken(true));
for(cntHeader = 1; cntHeader < numLinesHeader; ++cntHeader)
{
inLineHeader = str2Con(tbHeader.nextToken(true));
headers.DocumentNumber = conpeek(inLineHeader, 1);
headers.CustAccount = conpeek(inLineHeader, 2);
headers.insert();
}
inLineDetails = str2Con(tbDetails.nextToken(true));
for(cntDetails = 1; cntDetails < numLinesDetails; ++cntDetails)
{
inLineDetails = str2Con(tbDetails.nextToken(true));
details.DocumentNumber = conpeek(inLineDetails, 1);
details.ItemId = conpeek(inLineDetails, 2);
details.Qty = conpeek(inLineDetails, 3);
details.UnitPrice = conpeek(inLineDetails, 4);
details.Discount = conpeek(inLineDetails, 5);
details.UnitDiscount = conpeek(inLineDetails, 6);
details.insert();
}
ttscommit;
}
}
