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

}

}

XDS Framework in D365FO/AX 2012

 

XDS is used to restrict the data to the users based on their role. XDS replaces the Record level security framework in previous Microsoft Dynamics AX versions.

The security policies are applied on AOS and hence applied to any data retrieved from any client,whether in Rich, AIF web services or Enterprise portal.

Primary Table:

A primary table is used to restricts the data in the Constrained table.This table we will Specify in policy Query.

Constrained Table:

A constrained table is a table on which the filtering applied.  Based on the query range the data filterfrom constrained table.

Policy Query:

It will secure the data in constrained table define in the XDS.It is used to fetch the data from primary table and then it restricts the constrained table data.

Scenario:

As part of this tutorial, security policy will be applied to the Customer role, who can only view customers having financial dimension value (BusinessUnit,001).

 Here  I have joined the four tables to retrieve dimension values. The tables are DimensionAttributeValueSet, DimensionAttributeValueSetItem, DimensionAttributeValue and DimensionAttribute.

Steps:

1.Go to AOT Queries and Create a query and specify the datasources.

 Set dynamic property value to Yes.

 Go to dimensionAttributeValueSetItem  datasource ranges and set display value as “001”.

 Go to dimensionAttribute datasource ranges and set name value as “BusinessUnit”.

2.Go to Securities and create a new security policy.

Set the properties as shown in the below screen.

In context type property we will get  three options

·         Context string

 Defines a specific application context on which security policy will be enabled. It is also

called an application context. 

·         RoleName:

It is selected when the policy has to apply on a particular role.

·         RoleProperty:

It is used to define multiple roles for a single security policy.

Policy will be  applied only when the context string property of both Security policy and Security Role matches.

And  specify the constrained table under  security policy constrained table node.

Here I have added custtable  and vendtable.

3.Create a Role and specify it in security policy property.

Just go and check the result. You can only see customers who is having financial dimension values as BusinessUnit-001.

Note:

    Xds is not applied  to system administrator roles. 

Keep Learning!!