Executing direct MS SQL statements in ax 2012 connecting to external data base.

Below is the link to understand

How to get AX applciation preiod start date and end date in D365 FO or ax7

PeriodStart             periodStartDate;
PeriodEnd               periodEndDate;

periodStartDate = FiscalCalendarYear::findYearByCalendarDate(Ledger::fiscalCalendar(), DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone())).StartDate;
        periodEndDate   = FiscalCalendarYear::findYearByCalendarDate(Ledger::fiscalCalendar(), DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone())).EndDate;

How to explode BOM through X++ BOM Explode and subBOM X++ BOM versions and BOM

class BOMDesign
{
TmpBOMExplode tmpBOM;
}
private int InsertParentItem(ItemId _itemId, int _level)
{
InventTable inventTable;
;

//Gets the parent information and then insert on TmpBOMExplode Table with level 0.
select firstOnly inventTable where inventTable.ItemId == _itemId;

tmpBOM.ItemId = _itemId;
tmpBOM.ItemName = inventTable.itemName();
tmpBOM.Level = _level;
tmpBOM.BOMQty = 1 ;
tmpBOM.insert();

return _level+ 1 ;
}

boolean hasChild(ItemId _itemId)
{
BOMVersion bomVersion;
;

//Check if the item is also a BOM item.
select firstonly bomVersion
where bomVersion.ItemId == _itemid
&& bomVersion.Active
&& bomVersion.FromDate <= systemdateget () && (!bomVersion.ToDate || bomVersion.ToDate >= systemdateget ());

if (bomVersion.RecId)
return true ;

return false ;
}


void itemExplode(ItemId _ItemId, int _level = 0, BOMQty _bomQty = 1)
{
BOM bomTable;
InventTable inventTable;
BOMVersion bomVersion;
InventDim   inventDim;
;

//Insert parent Item
if (_level == 0)
_level = this.InsertParentItem(_ItemId, _level);

//Verifies if the Item exists in BOMVersion Table.
//The item must be active and not expired.
while select firstonly bomVersion
where bomVersion.ItemId == _itemid
&& bomVersion.Active
&& bomVersion.FromDate <= systemdateget() && (!bomVersion.ToDate || bomVersion.ToDate >= systemdateget());
// optoin comparing with Dimensions//
/*
exists join inventDim
            where inventDim.inventDimId == bomVersion.InventDimId &&
                  (inventDim.InventColorId == inventColorId || inventColorId == "") &&
                  (inventDim.InventSizeId == inventSizeId || inventSizeId == "");
*/
{
if (bomVersion.RecId)
{
//Every item on BOMVersion has a BOMId which is used to show
//which products belong to a BOM Item.
While select bomTable
where bomTable.BOMId == bomVersion.BOMId
join inventTable
where bomTable.ItemId == inventTable.ItemId
{
//Insert the items that compose the BOM Item with level 1.
tmpBOM.ItemId = bomTable.ItemId;
tmpBOM.ItemName = inventTable.itemName();
tmpBOM.RefItemId = bomVersion.ItemId;
tmpBOM.BOMQty = bomTable.BOMQty / bomTable.BOMQtySerie * _bomQty;
tmpBOM.Level = _level;
tmpBOM.insert();

//This method is used to check if the BOM Item is composed by
//another BOM Item, case true it will call the method recursively
// with level 2.
if (this.hasChild(bomTable.ItemId))
this.itemExplode(bomTable.ItemId, _level+ 1, tmpBOM.BOMQty);
}
}
}
public static void main(Args args)
{
BOMDesign bomDesign = new BOMDesign();
;

bomDesign.itemExplode( 'D0001', 0 );
}
}

For more info please click here for reference

Display inventory dimensions dynamically in D365 / Add display inventory dimensions in form

Display inventory dimensions dynamically in D365

Please click here for link

and use the below code to 

InventDimCtrl_Frm_EditDimensions    inventDimFormSetup;

 /// <summary>
    /// Inventory dimension group method.
    /// </summary>
    /// <returns>
    /// A <c>InventDimCtrl_Frm_EditDimensions</c> object.
    /// </returns>
    InventDimCtrl_Frm_EditDimensions inventDimSetupObject()
    {
        return inventDimFormSetup;
    }

 /// <summary>
    /// Initializes the range and inventory dimensions to be display while opening the form
    /// </summary>
    public void init()
    {
        super();
        // Add a filter to the query. Specify the field to use in the filter.
       
        //This method will be used to show default fields at the form setup
        element.updateDesign(InventDimFormDesignUpdate::Init);
    }
/// <summary>
    /// Update design mode as per inventory dimension
    /// </summary>
    /// <param name="_mode">
    /// Inventory dimension design mode.
    /// </param>
    void updateDesign(InventDimFormDesignUpdate _mode)
    {
        switch (_mode)
        {
            // Form Init
            case InventDimFormDesignUpdate::Init    :
                if (!inventDimFormSetup)
                {
                    inventDimFormSetup  = InventDimCtrl_Frm_EditDimensions::newFromForm(element);
                    inventDimFormSetup.parmSkipOnHandLookUp( true);
                }

            // Datasource Active
            case InventDimFormDesignUpdate::Active  :
                inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(DataSourceTable.ItemId)); //InventDimDisplay is the datasource name.
                inventDimFormSetup.formSetControls( true);
                break;

            // Datasource Field change
            case InventDimFormDesignUpdate::FieldChange :
                inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(DataSourceTable.ItemId)); //InventDimDisplay is the datasource name.
                InventDim.clearNotSelectedDim(inventDimFormSetup.parmDimParmEnabled()); // InventDim is referring to datasource name
                inventDimFormSetup.formSetControls( true);
                break;

            default :
                throw error(strFmt ("@SYS54195", funcName()));

        }
    }
Add the below code in the DS active method.
int  active()
        {
            int ret;

            ret = super();

            element.updateDesign(InventDimFormDesignUpdate::Active);

            return ret;

        }

How to populate form data source with temp table. D365 FO and AX7

Create new form, add temptable as DataSource.

DS-> Init()

public void init() // init method of form data source. 
{
   CustGroupTmp tmpTable; 

   super();
   tmpTable.CustGroupId= "Test1";
   tmpTable.insert();
   tmpTable.CustGroupId= "Test2";
   tmpTable.insert(); 
   CustGroupTmp.linkPhysicalTableInstance(tmpTable); // CustGroupTmp is the form datasource name this cursor

//or 

CustGroupTmp.setTmpData(tmpTable);

TableType should be: InMemory

}

work in class inserting temptable records.

// to use the same logic in the class, pass the formDatasource-CustGroupTmp to class

if (_args.record().TableId == tableNum(CustGroupTmp))
{
 FormDataSource formDS = _args.caller().datasource(formDataSourceStr(CustGroupTmpForm, CustGroupTmp)) as FormDataSource;
 CustGroupTmp = formDS.cursor();
 formDS.executeQuery();
}

// FormDataSource fds

// CustGroupTmp custGroupTempBuffer = fds.cursor()

// custGroupTempBuffer.linkPhysicalTableInstance(tmpTable);

X++ code to write data to Notepad or CSV file in D365FO or AX7

TextStreamIo                        streamIO;
FileName                            fileNameWithExt;
    FileName                            fileName;

 public void initFile()
    {
        #define.validationFileSuffix('-data-validation-errors.txt')
        InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
       
        perm.assert();
        streamIO = TextStreamIO::constructForWrite(#utf8format);
        streamIO.outRecordDelimiter(#delimiterCRLF);

        fileName += #validationFileSuffix;
    }

boolean validationFailed(FreeTxt _errorTxt)
    {
        boolean ret;

        if(!streamIO)
        {
            this.initFile();
        }
           
        streamIO.write(_errorTxt);

        return ret;
    }


 public void downloadFile()
    {
        if (streamIo != null)
        {
            File::SendFileToUser(streamIo.getStream(), fileName);
        }

        CodeAccessPermission::revertAssert();
    }

How to calculate Available physical of an item given the inventLocationId that shown on the on hand form

How to get available physical for an Item and for Warehouse in AX 2012 / D365 FO 

InventDimParm       invDimParm;

   InventDim           invDim;

   Qty                 availPhys;

   invDim.InventLocationId = 'INVENTLOCATIONID';

   invDim = InventDim::findOrCreate(invDim);

   invDimParm.initFromInventDim(InventDim::find(invDim.inventDimId));

   availPhys = InventSum::findSum("ITEMID",invDim,invDimParm).availPhysical();

   info(strfmt("availPhys:%1 ",availPhys));

========

InventDim           inventDim;

   InventDimParm       inventDimParm;

   InventOnhand        inventOnhand;

  

   inventDim.InventLocationId = 'Yourwarehouse';

   inventDimParm.initFromInventDim(inventDim);

   inventOnhand = InventOnhand::newParameters('Youritem', inventDim, inventDimParm);

   info(strfmt("Available Physical: %1", inventOnhand.availPhysical()));