dinsdag 26 juli 2016

Oracle Time and Labor in the Cloud and complex overtime rules

During on of our projects we were implementing Oracle Time and Labor in the cloud using quite complex overtime rules. Depending on your schedule and time entered, the system should calculate different buckets for your overtime ranging from 100% time for time, 120%, 125%, 140%, 150%, 175%, 200% and 220%.
All calculated overtime should be transferred to absence management so you can use it as time for time. We were not using Projects, but Payroll time cards (without actual payroll integration).

In order to set this up in Cloud you need to do the following steps and please note we are using R11!

Step 1: Enable Payroll using Setup and Maintenance, Manage Features by Country or Territory


In our case for The Netherlands we've set it to Payroll. This does not mean you have to pay Payroll license, but you can setup Payroll Time Types.
Only if you enable this you will be able to define payroll time types with Hours x Rate!

Step 2: For each overtime element we want to use navigate to Setup and Maintenance, Manage Elements and create a new element under actions.


Note that the category seems to differ between different releases. In R10 you did not have the Timecard category and you should choose Standard, in R11 you do have two options, but make sure you choose Standard.
Now enter the following information for the element. Make sure the effective date is 1-1-01 for example.



On the next page you choose the calculation method. Only Hours x Rate and Days x Rate appear on the timecard.



Choose next and the element is created.

Step 3: Create element eligibility. On the element created navigate to the Element Eligibility and choose Actions, Create Eligibility.



Step 4: Navigate to the menu menu and choose Payroll, Checklist.
On the right side of the screen choose the icon and Submit a Process or Report.



Choose your legislation and the process Create Time Card Calculation Components.


In the parameters you can choose the elements that are not yet created (so your Overtime X from above). Choose submit.

Step 5: Now the system has created a new element Overtime X CIR. Navigate back to Manage Elements, find the element and set the Element Eligibility like we did for the original element.

Step 6: Navigate to Setup and Maintenance, Define Time and Labor.
Now choose Generate Data Dictionary Time Attributes.


Choose your legislation and make sure you choose Yes for Include Payroll Time Types.

Step 7: Now the new payroll time type will appear in your list on your timecard (if your timecard layout has been setup correctly of course). In our case we wanted the user to enter 'Regular Time' and split the timecard (explosion) to different overtime types, but the user should not be allowed to choose the overtime types manually in the list.
In order to do that you have to define a multiple attribute time card field that we will use on our time card layout.
Navigate to Setup and Maintenance, Define time and labor, manage layout components.
Create a new type. The setup is described here as well
 http://docs.oracle.com/cloud/latest/globalcs_gs/FAITL/FAITL1470626.htm#FAITL934832



So you define a new type and add the payroll time types you ARE allowed to enter. Important are the filters and the settings at the bottom.



Here you specify the default, but also to show the list as a smart choice list. And the filters are setup for the payroll time types based on assignment and start time.



Step 8: So the base is setup now. To understand the setup for OTL take a look at the following diagram.



So there are two parts we need to setup: the layout and the processing. In our example I only added the time calculation rule and no time entry rules (like max your scheduled hours, etc).
Let's first take a look at the layout.

Step 9: Navigate to Setup and Maintenance, Define Time and Labor and choose Manage Time Layout Sets. In our case we create a new layout set based on Payroll.



You have different layouts used for different purposes, but if you change one it usually asks to copy the changes to the other layouts. Make sure if you want that or not.


In our case we want the user to enter time using start and end time, but show calculated hours on the calculated sheet. So the Time Entry Layout is setup as follows



Here we change the default Payroll Time Type to our Restricted time type. We display only start and end time and do not span across midnight.
Note that how the hours are entered is determined by the user's profile setting. We use HH:mi to make sure you can enter 18:00 - 23:00 etc. Otherwise you have to specify AM or PM for example, but this may be valid for other clients of course.

Note that we also did not manage to enter 00:00 (midnight), because it always assumed this was the beginning of the day rather than the end, so we allow users to enter 23:59 and correct this in the time calculation rule to midnight.

The layout for the calculated time shows Payroll Time Types and Hours instead of the restricted list and start/end times. This way it will show our overtime elements in calculated hours.

I will not describe setting up the entry profile here and relating it to your HCM Group, but that is a step you have to do as well of course.
Next is how we setup the time calculation rules.

Step 10: Navigate to Setup and Maintenance, Define time and Labor, Manage Fast Formula.
Our next step is to define the actual formula that is going to do the overtime split.

The first part is mostly copied from the default overtime rule

 /*+======================================================================+
   |                Copyright (c) 2016 Profource                          |
   |                  Rotterdam                                           |
   |                        All rights reserved.                          |
   +======================================================================+
 *
 * Formula Name : XXX_HCM_FF_CALC_OVERTIME 
 *   
 * Formula Type:  Time Calculation Rules
 *  
 * Description:   Calculates overtime split into buckets.
 * 
 * Detail:        Compares the start and end time for the detailed entries  
 *                to the defined time schedule. Splits according to 
 *                rules into different overtime buckets.
 *
 *
 *  Change History
 *  -------------- 
 *
 *  Who               Ver         Date          Description
 *-----------------  ------      ------------  -----------------------------------------
 * Pam Koertshuis     2016.01      2016-JUL-12 Initial creation
 *
 * 
***************************************************************************/  
 default for measure(number)  is  0  
 default for PayrollTimeType(text) is  'NULL' 
 default for StartTime(Date)  is   '01-JAN-1900'(DATE)  
 default for StopTime(Date)   is  '01-JAN-1900'(DATE)   

INPUTS ARE  
  measure(number) ,
  PayrollTimeType(text),
  StartTime(Date),
  StopTime(Date)

paytypeIn = PayrollTimeType
blankDate =  '01-JAN-1900'(DATE) 
ffName = 'XXVTTI_HCM_FF_CALC_OVERTIME  -v2016.01'

NullDate     =  '01-JAN-1900'(DATE) 
NullDateTime = '1900/01/01 00:00:00' (date) 
NullText =  '***NULL***'

ffs_id = GET_CONTEXT(HWM_FFS_ID, 0)
rule_id = GET_CONTEXT(HWM_RULE_ID,  0)  
rec_position = GET_CONTEXT(HWM_RECORD_POSITION, 'DETAIL')


 /* Get context, inorder to makt available for util formula) */
 ctx_personId = GET_CONTEXT(HWM_RESOURCE_ID, 0)
ctx_subResource = GET_CONTEXT(HWM_SUBRESOURCE_ID, 0)
ctx_start_date = GET_CONTEXT(HWM_CTX_SEARCH_START_DATE,  NullDate)
ctx_end_date = GET_CONTEXT(HWM_CTX_SEARCH_END_DATE, NullDate) 

one_minute = 1 / (60 * 24)

/* Since we cannot enter 24:00 we interpret 23:59 as 24:00 */
IF (to_char (ctx_end_date,'HH24:MI') = '23:59')
THEN
(
  rLog  = add_rlog (ffs_id, rule_id,   'Wait a minute ... '  )
  ctx_end_date = add_days (ctx_end_date,one_minute)
  new_measure  = (((days_between(ctx_end_date, ctx_start_date ))*24*60*60)/3600)
)

So this gets the context and adds a minute to midnight. In our case we want to execute for each detail record, because it depends on the payroll time type entered how overtime is calculated. We have Regular Time, Education, specific shift types, etc.

So to check this, we use something like this

IF (rec_position = 'DETAIL' and (PayrollTimeType = 'Regular Time' or PayrollTimeType = 'Education'))
THEN
(

Now our ruling depends on whether it's a public holiday or not. So we add a call to a function to determine if today or tomorrow is a public holiday (in our case a public holiday starts at a certain time the previous day).

  CALL_FORMULA ('XXX_PUBLIC_HOLIDAY' 
  , starttime > 'Start_Time'
  , l_today < 'ToDayHoliday' DEFAULT FALSE
  , l_tomorrow < 'TomorrowHoliday' DEFAULT FALSE 
  )

Now depending on your rules you could add things like

  l_day = to_number (to_char (StartTime,'D'))
  
  G_SUNDAY = 1  
  G_MONDAY = 2
  G_TUESDAY = 3
  G_WEDNESDAY = 4
  G_THURSDAY = 5
  G_FRIDAY = 6
  G_SATURDAY = 7 

  G_TRUE                = 1
  G_FALSE               = 0

IF (l_day = G_SUNDAY OR l_today = G_TRUE)
THEN
(
  x_overtime_100 = new_measure


...

x_total = x_overtime_100 * 2 +
          x_overtime_50 * 1.5 +
          x_overtime_25 * 1.25 
          etc.

So, what this would do is if it today is a sunday or today is a public holiday your time gets rewarded by 100% (so 1 hour results in 2 hours off). At the end you can calculate the total based on your separate components.

Now depending on what you want to see eventually in your exploded time card you add the outputs like

RETURN x_overtime_25, x_overtime_50, x_overtime_100, x_total

Step 11: When you are happy with your formula, we will define the Time Repository Rule template. Here you give the type and the formula you've just created.



Now you can enter the name.



We also want it to fire when you save and on detail level (each entered record).
Now the system will prompt you with the inputs from the formula. The default example of the Shift Premium Calculation uses a time range to determine when overtime is calculated. We did that differently, because we checked his schedule and if your time was written after the schedule you get overtime rewarded.
To fetch the schedule we slightly changed the HCM_WFM_UTIL_GET_EMPLOYMENT_SCHEDULE to fetch both schedule time/start ranges next to the total hours (which it default returns).

The actual percentage depends on when time was entered. For example:
If your schedule is 09:00 - 17:00 and you work
17:00 - 23:00 - you get 100%
23:00 - 07:00 - you get 150%
07:00 - 09:00 - you get 125%
But rules on saturday and sunday and public holidays are different. And this is for regular time and education. There are also other time types you can enter, which get rewarded differently.
And in our case it also depends in which group you fall. So for people from city X the overtime calculations were different.

So .. in our case, the formula did NOT have any input parameters :-).
Then for the output parameters add the grouping and the time attribute to Measure.


For each element we also add an OUT_{..} column with time attribute PayRollTimeType.


Now enter the rest of the information like the message. For example

Assigns regular time to {OUT_REGULAR} and splits overtime to {OUT_OVERTIME100} for 100% time for time, {OUT_OVERTIME120} for 120%, {OUT_OVERTIME140} for 140%, {OUT_OVERTIME200} for 200%, {OUT_OVERTIME220} for 220% with the total calculated in {OUT_TOTAL}.

IMPORTANT: You cannot change the template and rules after you've set them up and you cannot save in between :-(.

Step 12: Now we setup the Rule itself. Here we specify the actual values for the parameters we defined on the template.


Again, you cannot make changes later or add outcome, etc.

Step 13: Now we have our rule complete, we only have to add it to a rule set and attach that rule set to our time processing profile.

Navigate to Setup and Maintenance, Define time and Labor, Manage Time Repository Rule sets. Define a new ruleset with date 1-1-50 and add your rule to it. Relate it to the appropriate HCM Group and make sure you set the priority on top (so it overrules the default Payroll catch all groups).

In Manage Worker Time Processing Profile you bring all the things together (also take a look again at the diagram in the beginning). You specify your consumer set, your period and your time calculation rule set and/or time entry rule set (which we did not setup in our case).

Step 14: When the user enters time, the timecard is automatically split.


So some other considerations ..

  • You don't need to add Regular Time as output of your fast formula like we did here (which were hours within schedule). 
  • You can show all the different elements to clarify how the amount was calculated, or only add a Overtime as total of course (calculated by the individual components).
  • Any formulas you create to support your time calculation formula must be Workforce Management Utility type, no legislation and start date 1-1-01.


Step 15: Now to send the time as time for time to absence management we setup an Global Absence Accrual Matrix fast formula as follows.

/*****************************************************************
FORMULA NAME: XXX_NL_ACCRUAL_MATRIX_WITH_OVERTIME
FORMULA TYPE: Global Absence Accrual Matrix
DESCRIPTION:  This formula calculates the accrual rate
Change History:
Name                Date           Comments 
------------------------------------------------------------------
Pam Koertshuis      21-JUL-2016    Added overtime
------------------------------------------------------------------
*****************************************************************/ 

/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/ 
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
DEFAULT for PER_ASG_REL_DATE_START is '4712/12/31 00:00:00' (date) 
DEFAULT for PER_ASG_REL_ACTUAL_TERMINATION_DATE is '4712/12/31 00:00:00' (date) 
DEFAULT for PER_ACCRUAL_BAL_ACCRUAL_RATE is 0
DEFAULT for PER_ASG_FTE_VALUE is 0
DEFAULT for adjustmentvalues is EMPTY_NUMBER_NUMBER
DEFAULT for adjustmentdates is EMPTY_DATE_NUMBER
DEFAULT for adjustmenttypes is EMPTY_TEXT_NUMBER
DEFAULT for IV_EVENT_DATES is EMPTY_DATE_NUMBER
DEFAULT for IV_ACCRUAL_VALUES is EMPTY_NUMBER_NUMBER
DEFAULT FOR PER_ASG_STANDARD_WORKING_HOURS is 0
DEFAULT FOR PER_ASG_COL_AGREEMENT_COLLECTIVE_AGREEMENT_NAME is 'X'
DEFAULT FOR PER_ASG_EMPLOYEE_CATEGORY is 'X'


DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)


/*=========== DATABASE ITEM DEFAULTS ENDS=======================*/ 
Inputs are IV_EVENT_DATES, IV_ACCRUAL_VALUES
/*, IV_ACCRUALPERIODENDDATE(date) */

v_person_id = get_context(PERSON_ID,0)

Beginning_Of_Calculation_Year = to_date('0101'||to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)),'YYYY')
                                         ,'DDMMYYYY')
                                         
                                         
  End_Of_Calculation_Year = to_date('3112'||to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)),'YYYY')
                                         ,'DDMMYYYY')                                         
                                         
start_date = Beginning_Of_Calculation_Year
stop_date = End_Of_Calculation_Year

/*IV_ACCRUALPERIODENDDATE*/

accrual = 0
x_tot = 0

x_tot0 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 0', 'APPROVED', 0)
x_tot25 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 25', 'APPROVED', 0)
x_tot50 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 50', 'APPROVED', 0)
x_tot75 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 75', 'APPROVED', 0)
x_tot100 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 100', 'APPROVED', 0)
x_tot120 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 120', 'APPROVED', 0)
x_tot125 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 125', 'APPROVED', 0)
x_tot140 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 140', 'APPROVED', 0)
x_tot150 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 150', 'APPROVED', 0)
x_tot200 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 200', 'APPROVED', 0)
x_tot220 = GET_CALCULATED_HOURS(v_person_id, 'Y', start_date, stop_date, 'Overtime 220', 'APPROVED', 0)

x_tot = x_tot0 * 1 +
        x_tot25 * 1.25 +
x_tot50 * 1.5 +
        x_tot75 * 1.75 +
        x_tot100 * 2 +
        x_tot120 * 2.2 +
        x_tot125 * 2.25 +
        x_tot140 * 2.4 + 
        x_tot150 * 2.5 +
        x_tot200 * 3 + 
        x_tot220 * 3.2


ceiling = 0
carryover=9999


accrual = x_tot

/*================ FORMULA SECTION END =========================*/ 

RETURN 
accrual,ceiling,carryover,adjustmentvalues,adjustmentdates,adjustmenttypes

This formula needs to be attached to a vacation plan you relate to the user. Now the total calculated hours are shown on that plan.