MS SQL - Split a String

This is the first time that i used the apply operator. As the name suggests the apply is an operator hence it works at each row level rather than at a data set level and this comes truly handy to solve the problem on hand.

As defined in TechNet : The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

I wanted to update a worker information and the data provided by the customer has the employee name in a single string using space as separator.

Below is the SQL statement for same.

select TCE.EmpCode   
    , substring( Emp.[Employee Name] , 1, P1.pos ) FirstName
    , case when p2.pos = 0 then '' else substring( Emp.[Employee Name] , P1.pos + 1, P2.pos - P1.pos) end MiddleName
    , case when p2.pos = 0 then substring( Emp.[Employee Name] , P1.pos + 1, len( Emp.[Employee Name] ) - P1.pos)
        else substring( Emp.[Employee Name] , P2.pos + 1, len([Employee Name]) - P2.pos) end LastName  
from TCE
left join EMP
on Emp.[Employee Number] = TCE.EmpCode
cross apply (select (charindex(' ', EMP.[Employee Name] ) ) ) as P1(pos)
cross apply (select (charindex(' ', EMP.[Employee Name] , P1.Pos + 1) ) ) as P2(pos)

AX 2012 Time Profiles

1. Profile :A Time profile is one of the key setups that is required for the time and attendance functionality. A time profile dictates the different types of attendance transactions to expect for a day.

2. Profile Type: A profile type is used to identify how a time interval is classified as either Standard time, overtime, break, and flex time on a given day. A profile type can be configured as one of the 8 profile specification types given below :

1. Clock in : Time when the worker is expected to clock in
2. Clock out: Time when the worker is expected to clock out.
3. Standard time : Time to be considered as work hours during the calculation
4. Overtime : Time to be considered as overtime during the calculation
5. Break : Time to be considered as unpaid break during the calculation
6. Paid Break : Breaks may be paid by the legal entity (Paid break). Otherwise the break time will be deducted from the work time.
7. Flex + : indicates that the flex balance is increased if the worker is at work. Typically, the Flex+ periods are put before clock-in and after clock-out. .
8. Flex -  indicates that hours are deducted from the flex balance if the worker is not at work. Typically, the Flex- periods are put between clock-in and clock-out times.

3. Profile Groups: When there is a need to group a set to time profiles together and set rules for selection of a certain profiles based on date and time constraints then profile groups can be used. 

4. Profile Relation: The profile relation on a profile group defines the profile to be activated for the worker based on their clock in time. In the screen below we can see that from 28 June to 27 July a Ramadan profile is activated which would reduce the work timings during the holy month of Ramadan.

A more visible use of the profile being decided based on the clock in time is the use of the shifts in a company. Consider a scenario when an employee could clock into any of the 3 shift that cover the 24 hours in a day one way to apply the relevant profile would be by using profile relations to decided what time profile to be applied to the worker which in turn would decide his break and overtimes.

4. Profile Calendar: Profile calendar is used to a specific work time profile to one or more workers on a specific date regardless of the clock in time.

In the screen below july 27 has been set as half day for the entire company. This form can also be used to setup holidays.

4.1 Work Planner: In the profile calendar form above there are links available to populate entries into it while compose and copy interval are simple. Work planner is a graphical tool used to create profile calendar entries graphically.

To use the form first set a selection in the list of profiles available (marked 1 in the screen below) then click on the desired cell in the grid (marked 2 in the screen below)

The data changes from this screen would be committed to the profile calendar form however records might not be visible due to the active filters on the form so make sure you open the form from the menu Human Resouce -> Area Page -> Setup -> Time and Attendance-> Time Profiles -> Profile Calendar.

Import a Department Hierarchy

Department hierarchy is one of the key elements of implementing the AX 2012 HR solution.

In this example I have used the DIXF framework for data import. The first thing that i realized is that once a hierarchy is imported it appears as a draft version which has to be published. So don't get worried when you import a hierarchy and you don't see the changes visually on the hierarchy.

The table that stores the import from the DIXF is OMREVISIONEDIT. This can be confirmed looking at the definition of the Organization Hierarchy entity in the DIXF module.

The first issue i encountered is that my legal entity was not getting attached to the department as per the csv file created. As a result i explored the transformation class in DIXF called "DMFOMHierarchyEntityClass" and looked at the function establishing the link called "generateOmParentLink" it didn't take me time to realize that the function was expecting the dataareaid instead of the name in case the organization unit was of type "legal entity"

Once updated success was on its way.