Import sales orders from master and details text files

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;

    }

}