Saturday, December 24, 2011

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;
    }
}

6 comments:

  1. Hello Fareed,

    If I understand it right - are you using Headers and details tables are your Temp tables and then loading the data into Salestable, Salesline tables.

    Thanks,

    ReplyDelete
  2. Hi, You don't need to use temp tables rather then using sales order and sales line table. I used temp tables for some customized requirement.

    ReplyDelete
  3. Hi,

    Do you have an example for upload ONE file to table Header-detail in AX 2012?

    Thanks.

    ReplyDelete
  4. Hi Joicehelena,

    I haven't updated this code for AX 2012 but I am sure it will not be that much tough to update it. If you have some specific requirement I would be nice to discuss it with you.

    ReplyDelete
  5. how does it handle with item dimensions.

    ReplyDelete
  6. and inventTransId..?

    ReplyDelete

I will appreciate your comments !