Create Excel BOM Report Template from X++ in AX 2012

BOM Excel Template report:

bo1

Path: Product information management/common/release products

Select each grid record whose Production type is BOM or Formula as shown in below screen and click Lines in BOM Engineer action pane or Lines in Formula Engineer action pane

bo2

Add Export to excel button on version Group for exporting excel report template

bo3

Override Export to Excel click button which will call a new class through action menu item

bo4

void clicked()
{
     BOMVersion      BOMVersionlocal;
    str             menuItemStr;
    MenuFunction    menuFunction;
    Args            args = new args();

    super();
    //getFirst method gets all the selected records in the grid
    BOMVersionlocal = BOMVersion_ds.getFirst(1,true);
    args.record(BOMVersionlocal);

    menuItemStr = menuitemActionStr(SL_BOMExport);   
    menuFunction = new MenuFunction(menuItemStr, MenuItemType::Action);
    if (BOMVersionlocal.RecId != 0)
    {
       menuFunction.run(args);
    }
    else
    {
        warning('No record selected');
    }

}

Create a new Class which exporting BOM header and Lines into excel

bo5

public static void Main(Args _args)
{
    ItemId          itemid;
    BOMVersion      BOMVersion;

    BOMVersion = _args.record();

    itemid = BOMVersion.ItemId;


    SL_BOMExport::exportBOM(itemid,BOMVersion.BOMId);
}

public static void exportBOM(ItemId _itemId, BOMId _bomId )
{

    BOMVersion               BOMVersion;
    BOM                      BOM;
    Description              personalNumbervalue;

    SysExcelApplication     application;
    SysExcelWorkBooks       workbooks;
    SysExcelWorkBook        workbook;
    SysExcelWorksheets      worksheets;
    sysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            SysExcelFont;

    int                     row = 1;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();            //gets the workbook object
    workbook = workbooks.add();                     // creates a new workbook
    worksheets = workbook.worksheets();             //gets the worksheets object
    worksheet = worksheets.itemFromNum(1);          //Selects the first worksheet in the workbook to insert data
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');           // numberFormat ‘@’ is to insert data as Text


        //header
    worksheet.cells().item(1,1).value(CompanyInfo::Find().Name);
    worksheet.cells().item(1,1).font().bold(true);

    worksheet.cells().item(2,1).value("List of item formula / BOM");
    worksheet.cells().item(2,1).font().bold(true);


    // bom header
    worksheet.cells().item(4,1).value("BOM");
    worksheet.cells().item(4,1).font().bold(true);

    worksheet.cells().item(5,1).value("Name");
    worksheet.cells().item(5,1).font().bold(true);

    worksheet.cells().item(6,1).value("Site");
    worksheet.cells().item(6,1).font().bold(true);

    worksheet.cells().item(4,4).value("From date");
    worksheet.cells().item(4,4).font().bold(true);

    worksheet.cells().item(5,4).value("To date");
    worksheet.cells().item(5,4).font().bold(true);

    worksheet.cells().item(6,4).value("From qty");
    worksheet.cells().item(6,4).font().bold(true);

    worksheet.cells().item(4,7).value("Active");
    worksheet.cells().item(4,7).font().bold(true);

    worksheet.cells().item(5,7).value("Approved by");
    worksheet.cells().item(5,7).font().bold(true);

    worksheet.cells().item(6,7).value("Approved");
    worksheet.cells().item(6,7).font().bold(true);


     select BOMVersion
           where BOMVersion.ItemId == _itemId
           && BOMVersion.BOMId     == _bomId;

        cell = cells.item(4,2);
        cell.value(BOMVersion.BOMId);

        cell = cells.item(5,2);
        cell.value(BOMVersion.Name);

        cell = cells.item(6,2);
        cell.value(InventDim::find(BOMVersion.InventDimId).InventSiteId);

        cell = cells.item(4,5);
        if (BOMVersion.FromDate != dateNull())
        {
            cell.value(BOMVersion.FromDate);
        }
        else
        {
            cell.value("");
        }

        cell = cells.item(5,5);
        if (BOMVersion.ToDate != dateNull())
        {
             cell.value(BOMVersion.ToDate);
        }
        else
        {
             cell.value("");
        }
        cell = cells.item(6,5);
        cell.value(BOMVersion.FromQty);

        cell = cells.item(4,8);
        cell.value(enum2Value(BOMVersion.Active));

        cell = cells.item(5,8);
        personalNumbervalue = HcmWorker::find(BOMVersion.Approver).name();
        cell.value(personalNumbervalue);

        cell = cells.item(6,8);
        cell.value(enum2Value(BOMVersion.Approved));

    row = 6 ;
    row+=2;


     // header lines

       worksheet.cells().item(row,1).value("Item number");
       worksheet.cells().item(row,1).font().bold(true);

       worksheet.cells().item(row,2).value("Product name");
       worksheet.cells().item(row,2).font().bold(true);

       worksheet.cells().item(row,3).value("Configuration");
       worksheet.cells().item(row,3).font().bold(true);

       worksheet.cells().item(row,4).value("Quantity");
       worksheet.cells().item(row,4).font().bold(true);

       worksheet.cells().item(row,5).value("Unit");
       worksheet.cells().item(row,5).font().bold(true);

       worksheet.cells().item(row,6).value("Per series");
       worksheet.cells().item(row,6).font().bold(true);

       worksheet.cells().item(row,7).value("Size");
       worksheet.cells().item(row,7).font().bold(true);

       worksheet.cells().item(row,8).value("Color");
       worksheet.cells().item(row,8).font().bold(true);

       worksheet.cells().item(row,9).value("Style");
       worksheet.cells().item(row,9).font().bold(true);


     while select BOMVersion
           where BOMVersion.ItemId == _itemId
            &&   BOMVersion.BOMId  == _bomId
         join BOM
            where BOMVersion.BOMId == BOM.BOMId

    {

            row++;

            cell = cells.item(row,1);
            cell.value(BOM.ItemId);

            cell = cells.item(row,2);
            cell.value(BOM.itemNameGrid());

            cell = cells.item(row,3);
            cell.value(InventDim::find(BOM.InventDimId).configId);

            cell = cells.item(row,4);
            cell.value(BOM.BOMQty);

            cell = cells.item(row,5);
            cell.value(BOM.UnitId);

            cell = cells.item(row,6);
            cell.value(BOM.BOMQtySerie);

            cell = cells.item(row,7);
            cell.value(InventDim::find(BOM.InventDimId).InventSizeId);

            cell = cells.item(row,8);
            cell.value(InventDim::find(BOM.InventDimId).InventColorId);

            cell = cells.item(row,9);
            cell.value(InventDim::find(BOM.InventDimId).InventStyleId);

    }

    application.visible(true);  // opens the excel worksheet

}

Exchange Currency Service Provider

Exchange currency service provider is OOTB available in MS Dynamics AX and by default three AX provide three service exchange provider i.e. Central Bank of the Russian Federal, Central bank of Europe and Lithuanian Bank.

Following is the below screen for Exchange Rate provider which is Available in General Ledger Module

Path: General ledger /setup /Configure exchange rate providers

You must first set up the exchange rate provider you want to use, and AX 2012 R2 comes with 3 providers ready to use in General Ledger -> Setup -> Currency -> Configure Exchange Rate Providers:

fk

fk01

There is reset value option also available to restore default value.

After adding exchange rate provider run this service through batch Job exchange service as shown in below screen

Path: General ledger / Periodic / Import currency exchange rates

Import currency exchange rates batch job take Exchange rate type and exchange rate provider. If exchange rate type is not created then create it from General ledger / setup / Exchange rate type.

Prerequisite:

Before going to run Currency exchange batch job, make sure that all mentioned Exchange currency must create once in exchange rate type.

fk02

Run this batch job

fk03

After running Service provider batch job see today’s exchange currency rate in General ledger / setup / Exchange rate type.

fk04


Region as an Internal Organisation

System should allow users to create Region within the internal organization hierarchy. For this we have to do some customization

First create new operating unit type element in OMOperatingUnitType base enum

f1

OMOperatingUnitType is used in OMOperatingUnit tables so we need to Restore OMOperatingUnit tables.

Create new View which named as DimAttributeOMRegion as shown in below screen

f2

 Create Internal Region Organization type

Organization Administration > Common > Organization > Internal Organization

Create new region from Internal Organization action pane button as shown in below screen

f3

Click Ok

f4

General Ledger > Setup > Financial Dimensions > Financials Dimensions

Newly created organization should appear within Financial Dimension Form and could be used throughout AX.

f5


Customize Existing Number Sequence to Fiscal Year Number Sequence

Here we are customizing existing Number sequence and convert it in to Fiscal year number sequence Many customer demands for Purchase order creation with automatic year means how to add year to a number sequence in AX2012, e.g. to generate IDs such as 2013-xxxxx and 2014-xxxxx, that would automatically use the current year. Some people understand that number sequence scopes should allow that, but they don’t know how, from very little bit of customization we can automate purchase order number sequence with year

Customize Purchase order Number Sequence Class:

Add fiscal calendar parameter in NumberSeqModulePurchaseIOrder (LoadModule) method as shown in below screen

 datatype.addParameterType(NumberSeqParameterType::FiscalCalendarPeriod, true, true);
fk1

Load NumberSeqModulePurchaseIOrder (Load Module) method Through Job

 NumberSeqModulePurchaseOrder NumberSeqModulePurchaseOrder = new NumberSeqModulePurchaseOrder();
NumberSeqModulePurchaseOrder.load();
fk2

Before going to run the Job, Remove purchase order existing scope parameter record in NumberSequenceDatatype table, if we don’t remove previous Purchase order scope parameter record it will not effect in Segment configuration as shown below

CEU/Organization administration/Number Sequence/ Segment configuration

fk3

Add Fiscal Year Relation to PurchParamater Table:

CEU/Procurement and sourcing/procurement and Sourcing parameters

fk4

We have customized procurement and Sourcing parameters form for Purchase order Fiscal Number sequence, because we need to select which Fiscal year the purchase order is created every year.

Add fiscal year table relation to PurchParamter table

fk5

Add a fiscal year Field on Procurement and Sourcing parameters form

fk6

Customize Purchase Order Number Sequence Table (PurchParameter):

Add following code in Purchase order Number sequence Parameter table method (numRefPurchId), in this existing method we have change code according to fiscal year.

static client server NumberSequenceReference  numRefPurchId(TransDate _date = systemdateget())
{
   //
   PurchParameters PurchParameters;
   NumberSeqScope  scope;

select firstOnly PurchParameters; // get Selected fiscal year in 
                                  // procurement and    Sourcing parameters Form 

   scope = NumberSeqScopeFactory::CreateDataAreaFiscalCalendarPeriodScope
                            (curext(),FiscalCalendars::findPeriodByPeriodCodeDate(FiscalCalendar::findByCalendarId(PurchParameters.FiscalCalendar).RecId, _date).RecId);
   //

     return NumberSeqReference::findReference(extendedTypeNum(PurchId),scope);
}
fk7

Fiscal Year Number Sequence Configuration

 

General ledger -> Setup -> Fiscal calendars.

Create a new Fiscal year or use existing Fiscal year, but In our case we have created our own Fiscal year for Ten years because we need to create Purchase order automated for Ten years

fk8

If you want to add more years follow below screen.

fk9

Click the Calendar -> Calendar year -> New fiscal year button.

Change Copy from last fiscal year from ‘true’ to ‘false’.

Change Unit from ‘Months’ to ‘Years’.

Click the Create button.

Close the Fiscal calendars form.

Number sequences creation

 

fk10

 

Click Area Page node: Organization administration -> Common -> Number sequences -> Number sequences. Click the Number sequence -> New -> Number sequence button.

fk11

Change Number sequence code from ” to ‘POFiscal’.

Change Name from ” to ‘2013’.

Change Scope from ‘Shared’ to ‘Company and Fiscal calendar period’.

Change Company from ” to ‘ceu’.

Change Calendar from ” to ‘FiscalYear’.

Change Fiscal year from ” to ‘2013’.

Change Period name from ” to ‘Period 1′.

fk12

 

Make it very clear that we are creating PO number sequence for the year 2013 and we have to create PO number sequence again for Year 2014, 2015, 2016.xxxxxx 2023 with same Number sequence Code i.e. ‘POFiscal’  as shown in below screen for Year 2014

fk13

 

Creating Purchase orders:

Before going to create Purchase order make sure that Fiscal year must be selected in Procurement and sourcing Parameter form which we have customized earlier above

fk14

 

This selected Fiscal year must be same which we have selected in creating Purchase order Fiscal year Number Sequence.