Creating pay periods lookup in Payroll module

Hi Friends,
Recently I was working on some customizations in Payroll module and was required to create controls to select pay cycle and pay periods. I want to share this information on how we can create lookups for pay periods based on a pay cycle.
In standard AX, Pay cycles and pay periods are defined in payroll > setup > pay cycles and pay periods as shown below:

A standard AX example is the dialog of generate pay statements,where user first selects a pay cycle and based on that pay cycle, the pay periods are shown in the control as shown below, the pay periods are specific to the pay cycle:

So I looked into the class to understand how the lookup is coming and found the following, system was storing all the values in a container and then adding them to the combo box control. So I have tried to highlight the code used in the process:

When user selects a payCycle then the method to create the period list is called

In this method the container to hold the payPeriodList is populated with all the valid values, system is selecting all the pay period and then converting it into a string format and then adding it to container and the combo box selection list

In order to read the user selection the value is fetched from the container based on the selection in the combo Box control

So I followed the same pattern, and create a form as shown below :

1. Created a new reference group control for the pay cycle control, these controls are really helpful in AX2012.

Now if I open my form, I see all the pay cycles of the system

2. Then add a new combo box control to show the pay period lookup

Change the auto declaration to true

Declare the variables in class, a container to store the data and the variables to hold the user selection

Then copied the createPayPeriod method and removed the unwanted code. The same pattern is used in benefit register report UI builder class, the below code is copied from there:

On the modified of the pay cycle control I called this method:

And we are all set to go. On my form I have now lookups of pay cycle and pay period as per standard AX design.

Another great example of reusing standard AX code rather then reinventing the wheel. Thanks for reading the blog.

Date Effective tables with ValidTimeState and Dynamics AX 2012

You may have noticed the new ValidTimeStateFieldType property on tables in AX 2012. The enum values are None, Date and UtcDateTime. This is part of the new & cool Date Effective Table framework that comes with Dynamics AX 2012. The idea is to minimize the effort for managing period gaps, overlapping periods, period validation, etc. So, let's get down to how it works.

• We create new table called DEV_ValidTimeState and we add new field named ItemId
• We set the ValidTimeStateFieldType table property to UtcDateTime. At this point AX automatically creates 2 new fields: ValidFrom and ValidTo.  
   
• Now we need to create new unique index, which should include the fields ItemId, ValidFrom, and ValidTo
• We set the following index properties  
• AllowDuplicates to No.
• Alternate Key to Yes.
• ValidTimeStateKey to Yes. 
• ValidTimeStateMode to Gap. 
• Now let's insert some records.

static void createValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    ;

    delete_from table;

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(1\1\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1000';

    table.insert();

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(6\6\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1001';

    table.insert();
}

• Here is how to select and update records from the table. Please note the use of the validTimeStatekeyword with select statements and there is a new xRecord method calledvalidTimeStateUpdateMode.

static void updateValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    utcDateTime fromDateTime, toDateTime;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();


    select validTimeState(fromDateTime) table;

    info(table.ItemId);

    select validTimeState(fromDateTime) * from table;

    info(table.ItemId);

    select validTimeState(fromDateTime) ItemId from table;

    info(table.ItemId);

    select validTimeState(fromDateTime, toDateTime) ItemId from table;

    info(table.ItemId);

    ttsBegin;

    while select forUpdate validTimeState(fromDateTime) table
    {
        table.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
        table.ItemId = '1002';
        table.update();

    }

    ttsCommit;
}

• The query framework was updated to support the new Time Effectiveness feature. Here is the code sample:

static void queryValidTimeState(Args _args)
{
    DEV_ValidTimeState      table;
    utcDateTime             fromDateTime, toDateTime;       
    Query                   q;
    QueryRun                qr;   
    QueryBuildDataSource    qbds;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();

    
    q = new Query();    
    
    qbds = q.addDataSource(tableNum(DEV_ValidTimeState));
    
    q.validTimeStateAsOfDateTime(fromDateTime);
    
    qr = new QueryRun(q);
    
    while(qr.next())
    {
        table = qr.get(tableNum(DEV_ValidTimeState));
        info(table.ItemId);
    } 
} 

To sum up, in AX 2012 we have new feature that allows us to manage the time periods associated with an entity. We get all the benefits of validation and period gaps management for free (that's sweet). In order to support the new feature some changes have been introduced:

• All tables now have new property called ValidTimeStateFieldType
• Table indexes now have new properties
• ValidTimeStateMode 
• ValidTimeStateKey
•  Alternate Key should be set to Yes
• The kernel class xRecord and all tables now have the validTimeStateUpdateMode method. 
• There is new system enum ValidTimeStateUpdate with the following values:
• Correction – the ValidFrom or ValidTo values of existing rows must be modified to keep the date effective data valid after the update completes. 
•  CreateNewTimePeriod – a new record is inserted into the table to maintain the validity of the date effective data after the update completes. 
• EffectiveBased – forces the update process to switch to CreateNewTimePeriod for each row that spans the current date-time; otherwise to switch to Correction. 
• The kernel class Query now has 4 new methods:
• validTimeStateAsOfDate
• validTimeStateAsOfDateTime
• validTimeStateDateRange 
• validTimeStateDateTimeRange

X++ Code to import Movement Journal in ax 2012

static void importOpeningBal(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FilenameOpen                    filename;

    int row = 1; // if the excel has the header
    TransDate     empJoiningDate;
    real          salary;
    str           empId;
    int           totNoOfLeaves;
    ItemId        itemId;
    Name          name;
//    Table1        Table;
    InventJournalTable  journalTable;
    InventJournalTrans  journalTrans;
    InventJournalTableData journalTableData;
    InventJournalTransData journalTransData;
    InventTable     inventTable;
    InventDim       inventDim,_inventDim;
    int                 i=1;
    InventDimCombination        inventDimCombination;
    EcoResProductMaster         ecoResProductMaster;
    EcoResDistinctProductVariant        ecoResDistinctProductVariant;
    InventLocation      inventLocation;
    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //filename  = "C:\\Users\\ftp2012\\Desktop\\InventOnhandImport1.xlsx"; // file path
    filename  = "E:\\import\\GenJrl\\ItemOnHand_1.xlsx"; // file path
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

        //Iterate through cells and get the values
    do
    {
        //Incrementing the row line to next Row
        if(i == 1)
        {
        journalTableData = JournalTableData::newTable(journalTable);
        journalTransData = journalTableData.journalStatic().newJournalTransData(journalTrans,journalTableData);

        // Init JournalTable

        journalTable.clear();

        journalTable.JournalId      = journalTableData.nextJournalId();
        journalTable.JournalType    = InventJournalType::Movement;
        journalTable.JournalNameId  = journalTableData.journalStatic().standardJournalNameId(journalTable.JournalType);

journalTableData.initFromJournalName(journalTableData.journalStatic().findJournalName(journalTable.JournalNameId));
        journalTable.insert();
        journalTrans.clear();
        i++;
        }
        try
        {
        row++;
        journalTrans.clear();
        journalTransData.initFromJournalTable();

        journalTrans.ItemId        = cells.item(row,1).value().bStr();
        journalTrans.TransDate      = systemDateGet();//mkDate(15,11,2013);
        journalTrans.Qty  = Cells.item(Row,9).value().double();//.float();//.double(); //date();
        journalTrans.CostPrice = decRound(cells.item(row,10).value().double(),2);
        journalTrans.CostAmount = journalTrans.Qty * journalTrans.CostPrice;

        inventDim.clear();

        inventDim.InventSizeId = cells.item(row,4).value().bStr();
        inventDim.InventColorId =cells.item(row,3).value().bStr();// here EcoResColor=shade
        inventDim.configId =cells.item(row,15).value().bStr();//here (EcoResConfiguration)=color
      //  inventDim.InventGradeId = cells.item(row,9).value().bStr();
        inventDim.InventStyleId =cells.item(row,15).value().bStr();//"01";//cells.item(row,10).value().bStr();//EcoResStyle=caliber

        inventDim.InventLocationId = cells.item(row,6).value().bStr();//Ware House
        select firstFast inventLocation where inventLocation.InventLocationId == inventDim.InventLocationId;
        inventDim.InventSiteId = inventLocation.InventSiteId;//

        inventDim.inventBatchId = cells.item(row,7).value().bStr();
        inventDim.wMSLocationId = cells.item(row,8).value().bStr();//A-01-00
        inventDim.wMSPalletId = cells.item(row,15).value().bStr();
        //inventDim.inventSerialId = cells.item(row,16).value().bStr();
        //inventDim.InventLocationId = cells.item(row,14).value().bStr();*/
            //for offset account
       // journalTrans.LedgerDimension = cells.item(row,14).value().toString();//.bStr();//double();//.bStr();

        _inventDim=inventDim::findOrCreate(inventDim);

        journalTrans.InventDimId = _inventDim.inventDimId;//"FCC-000153";
        journalTransData.create();

    // Loads the next row into the variant type and validating that its is empty or not
    type = cells.item(row+1, 1).value().variantType();
        }
        catch
        {
            throw error("error");
        }
    }
    while (type != COMVariantType::VT_EMPTY);
        // quits the application
    application.quit();
    info(strFmt("Inventory Journal Imported   :%1",journalTable.JournalId));

}