hljs.configure({cssSelector: "code"}); hljs.highlightAll();

Tuesday, September 29, 2020

create purchase order from Excel in AX using X++

 static void ImportPurchaseorder(Args _args)

{

    Dialog _dialog;

    DialogField _file;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    FileName filename;

    PurchTable purchTable;

    PurchLine purchline;

    NumberSeq numberSeq;

    InventDim InventDimloc;

    int  row = 1;

    CustAccount     custAccount,orderaccount,site,warehouse;

    CurrencyCode    currencyCode;

    ItemId          itemId;

    real            Quantity,Lineamount,Unitprice;

    str             unit;

    _dialog = new Dialog("Please select the file to load");

    _dialog.addText("Select file:");

    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();

    if (_dialog.closedOK())

    {

    //info(_file.value() );

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    filename =_file.value();

    try

    {

            workbooks.open(filename);

    }

    catch (Exception::Error)

    {

            throw error('File cannot be opened');

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    do

    {

        row++;

        custAccount = cells.item(row, 1).value().bStr();

        orderaccount = cells.item(row, 2).value().bStr();

        currencyCode = cells.item(row, 3).value().bStr();

        itemId = cells.item(row, 4).value().bStr();

        site = any2str(cells.item(row, 5).value().bStr());

        warehouse = any2str(cells.item(row, 6).value().bStr());

        Quantity = cells.item(row, 7).value().double();

        unit = cells.item(row, 8).value().bStr();

        Unitprice = cells.item(row, 9).value().double();

        numberSeq =NumberSeq::newGetNum(purchparameters::numRefPurchId(),true);

        purchTable.initValue();

        purchTable.PurchId = numberSeq.num();

        purchTable.OrderAccount = custAccount;

        purchTable.InvoiceAccount =orderaccount ;

        purchTable.CurrencyCode = currencyCode;

        purchTable.LanguageId = currentUserLanguage();

        purchTable.initFromVendTable();

        if (purchTable.validateWrite())

        {

            purchTable.insert();

        }

        purchLine.PurchId = purchTable.PurchId;

        purchLine.ItemId = itemId ;

        purchLine.itemIdChanged();

        select InventDimId from inventDimLoc

        where inventDimLoc.InventLocationId== site

        && inventDimLoc.InventSiteId== warehouse;

        purchLine.InventDimId=inventDimLoc.InventDimId;

        purchLine.PurchQty = Quantity;

        purchLine.modifiedField(fieldNum(purchLine, PurchQty));

        purchLine.PurchUnit = unit;

        purchLine.modifiedField(fieldNum(purchLine, PurchPrice));

        purchLine.setPriceDiscChangePolicy(PriceDiscSystemSource::ManualEntry,fieldNum(purchLine, PurchPrice));

        purchLine.createLine(true, true, true, true, true, true);

        ttsBegin;

        purchLine.selectForUpdate(true);

        purchLine.PurchPrice = Unitprice ;

        purchLine.LineAmount = purchLine.PurchQty * purchLine.PurchPrice ;

        purchLine.update();

        ttscommit;

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info(strFmt("@DAR91",purchTable.PurchId));

}

}

No comments:

Post a Comment