Using system classes to connect to database x++

Here in this topic, we will learn about how to connect SQL database using standard classes (without creating ODBC connection.

The code below uses the ODBCConnection class and LoginProperty class to connect to SQL DB.

server static ODBCConnection createConnection(str _SQLServerName, str _DatabaseName)
{
    ODBCConnection  connection;
    LoginProperty   loginProperty;
    ;
    loginProperty = new LoginProperty();
    loginProperty.setServer(_SQLServerName);


    loginProperty.setDatabase(_DatabaseName);

    connection = new ODBCConnection(loginProperty);

    return connection;
}

It will return the connection, which can be used to execute SQL statements.

Unpicking the sales order all at once AX 2012 x++

Unpicking the sales order all at once AX 2012 x++

Some times we need to cancel the picking sales order, for whatever reason.
if you want to unpick an  sales order, you have to go to the sales line and, one-by-one, unpick the order. You click on the Inventory button and select the Pick option. In the form, check the autocreate box, then click Post All in the lower area of the form. If you regularly have to pick and unpick orders, especially if you have a lot of lines on your orders, this can become very tedious.

To achieve the above functionality, we can just create a button on Sales Order form . Now I'm going to put an Unpick button on the sales order form at the order level and allow certain users (security will be used) to do this. I will most likely allow multiple orders to be selected so you can unpick multiple orders with one click of a button. The code for the this is as below:

1. Create a class "Mk_PickSalesUnReservation"
2. Add a main method with Args as parameters // This will accept SalesLine as Data source
static void main(Args _args)
{
}
3. add a run method and call it.

///

/// Contains the code that does the actual job of the class.
///

void  run()
{
    InventTrans                 inventTransLocal;
    InventTransOriginSalesLine  inventTransOriginSalesLine;
    InventTransOrigin           inventTransOrigin;
    SalesLine                   salesLine;
    InventMovement              movement;
    InventTransWMS_Pick         inventTransWMS_Pick;
    TmpInventTransWMS           tmpInventTransWMS;
    Query                       baseQueryInventTrans;
    QueryBuildDataSource        qbdsInventTrans;
    MultiSelectionHelper helper = MultiSelectionHelper::construct();

    baseQueryInventTrans =  new Query();
    qbdsInventTrans = baseQueryInventTrans.addDataSource(tableNum(InventTrans));
    baseQueryInventTrans.dataSourceTable(tableNum(InventTrans));
    qbdsInventTrans.clearDynalinks();
    qbdsInventTrans.clearRanges();
    qbdsInventTrans.addRange(fieldNum(InventTrans,StatusReceipt)).value(SysQuery::value(StatusReceipt::None));
    qbdsInventTrans.addRange(fieldNum(InventTrans,StatusIssue)).value(SysQuery::range(StatusIssue::Picked,StatusIssue::OnOrder));

    //inventTransWMS_Pick = InventTransWMS_Pick::newStandard(tmpInventTransWMS,baseQueryInventTrans);

    helper.parmDatasource(salesLine_ds);

    salesLine = helper.getFirst();
    while (salesLine.RecId != 0)
    {
        salesLine.selectForUpdate(true);
        //sALESlINE.iNVENTtRANSiD -> inventtransorigin.inventtransid -> inventrans
        select inventTransLocal
        where  inventTransLocal.ItemId                  == salesline.ItemId
            && inventTransLocal.StatusIssue             == StatusIssue::Picked
        exists join inventTransOrigin
        where   inventTransOrigin.RecId                  == inventTransLocal.InventTransOrigin
        &&      inventtransorigin.inventtransid         == salesline.InventTransId;

        if (inventTransLocal.RecId)
        {          

            tmpInventTransWMS = null;
            tmpInventTransWMS.initFromInventTrans(inventTransLocal);
            tmpInventTransWMS.InventQty = inventTransLocal.StatusIssue == StatusIssue::Picked ? inventTransLocal.Qty : -inventTransLocal.Qty;
            tmpInventTransWMS.insert();
            inventTransWMS_Pick = InventTransWMS_Pick::newStandard(tmpInventTransWMS,baseQueryInventTrans);
            inventTransWMS_Pick.createFromInventTrans(inventTransLocal);
            inventTransWMS_Pick.updateInvent();
        }
        salesLine = helper.getNext();
    }

}

4. Create a action menu Item for the above class
5. Add a menu Item button on Sales order form.