Custom Time Entry Rules in Time and Labour (OTL) - Prevent 0 hours time entry

Last week I received a requirement from a client for Oracle Time and Labour and Timecard entries. They wanted to prevent the entry of 0 hours on a timecard. There is unfortunately no implementation step for OTL or any seeded functionality to have this behaviour.

To prevent the entry of 0 hours on a timecard we have to use Timecard Entry Rules. A number of seeded available Timecard Entry Rules are available which you can use however they can't be used for my requirement. So, we create a new one with some help of a custom Pl/SQL function :-)

To solve the requirement a number of steps need to be taken:

• Create a Pl/SQL function which checks the active timecard for any 0 hour entries
• Register the Pl/SQL function in HR
• Create a new FastFormula in HR
• Create a Time Entry Rule in OTL
• Create a Time Entry Rule Group in OTL
• Assign the Time Entry Rule Group to the correct node in OTL preferences
• Register a new application message in eBS to be used as message to the user in case of 0 hour entry
• (Possibly bounce Apache)

See below for the detailed steps to get the functionality as described.

First step is to create a new database function which we can reference in our FastFormula. This database function will check whether the user has entered 0 (hours) in one of the day fields on a timecard entry line. Create the below function and compile it.

CREATE OR REPLACE FUNCTION APPS.XXMP_PREV_0_HRS(p_resource_id NUMBER)
RETURN number
IS

l_return_rule_status number := 0;
l_timecard_info  hxc_self_service_time_deposit.timecard_info;
l_tbb_array HXC_BLOCK_TABLE_TYPE;

BEGIN
l_timecard_info := hxc_self_service_time_deposit.get_building_blocks;
l_tbb_array := hxc_deposit_wrapper_utilities.blocks_to_array(p_blocks => l_timecard_info);

IF l_tbb_array.FIRST IS NOT NULL THEN
    FOR i IN l_tbb_array.FIRST .. l_tbb_array.LAST
    LOOP
        IF l_tbb_array(i).MEASURE = 0 and l_tbb_array(i).SCOPE = 'DETAIL'  THEN
            l_return_rule_status := 1;
            return l_return_rule_status;
        END IF;
        END LOOP;
END IF;

RETURN l_return_rule_status;
END XXMP_PREV_0_HRS;

The function will return 1 in case of an 0 hour entry and 0 if not. One of the key things you probably will use most with custom timecard validation are the functions get_building_blocks and get_block_attributes of the hxc_self_service_time_deposit package. The first function retrieves the time building blocks of the current active (displayed) timecard on screen of the user. The get_block_attributes retrieves the building block attributes of the active (displayed) timecard. In our case we only need the time building block function as the time entries are in there. If you need some validation on any additional attributes (also) use the other one.

With the seeded function blocks_to_array I convert my time building blocks in an easy to process Array.

As you might know timecard entries are recorded in Oracle E-Business Suite in one table (HXC_TIME_BUILDING_BLOCKS of the HXC schema). Entries are recorded with a column SCOPE which maintains the timecard period (SCOPE = TIMECARD), the days of the entries (SCOPE = DAY) and the hours itself (SCOPE = DETAIL). We need the hours so I check within the range of the building block array on the SCOPE = 'DETAIL' with a MEASURE of 0 which means 0 is entered in one of the days of the timecard and that's what we NOT want so we return a 1 :-). As soon as I find a 0 hour value I stop processing the function.

Make sure the function compiles without problems and is VALID.

Our Pl/SQL function will be referenced in a FastFormula in HR so we need to register this function there also so we can use it. Go to responsibility Global Super HRMS Manager and menu Other Definitions - Formula Functions. Add a new record and enter below details of the function:

Important is the Data Type which must be Number and the Alias Name which must be XXMP_PREV_0_HRS (or your own alias which you will reference later in te FastFormula of course). Our function is using one Input parameter which we also need to register so we click on Parameters and enter the following details.

The parameter name must be exactly the same as in our Pl/SQL function which is p_resource_id. It's having a Number type and is only used as an input value for the function so the Class is Input only. Save your function.

Next we create a new FastFormula in Oracle HR. Go or stay in responsibility Global Super HRMS Manager and navigate to Total Compensation - Basic - Write Formulas. Give you FastFormula a name and define it with Type OTL Time Entry Rules, optionally provide a description.

Next step is to define the code for the FastFormula itself so we click on the Edit button. Paste below code in the Edit Formula window.

 /* *****************************************************************
Formula Name : CVS_PREV_0_HOURS
Author : Cyriel van Schaik
Description : Prevent 0 hour entry in OTL
Contexts : None
*****************************************************************

*/

/*
      Initialise Variables which can be null
*****************************************************************

*/

default for db_pre_period_start is ' '
default for db_pre_period_end is ' '
default for db_post_period_start is ' '
default for db_post_period_end is ' '
default for db_ref_period_start is ' '
default for db_ref_period_end is ' '
default for timecard_hrs is 0

/* ******************************************************************
         READ IN INPUT VARIABLES
****************************************************************** */

INPUTS ARE resource_id     (number)
,    submission_date  (text)
,    db_pre_period_start (text)
,    db_pre_period_end (text)
,    db_post_period_start (text)
,    db_post_period_end (text)
,    db_ref_period_start (text)
,    db_ref_period_end   (text)
,    timecard_hrs     (number)

IF ( xxmp_prev_0_hrs(resource_id) = 1 )
THEN
 (rule_status = 'E'
    message1 = 'CVS_PREV_0_HOURS'
    return rule_status,message1)
ELSE
    (rule_status = 'S'
    return rule_status)

Important here are the INPUTS ARE definitions which is a required block for FastFormulas. As you can see I call my defined function in the IF part by using the alias of the HR function I registered which in his turn is calling the Pl/SQL function we registered in the database. I provide the standard INPUT resource_id to my function. When my function result is 1 it means we have a 0 hour entry and we return an E (for Error) as the FastFormula result - else we return an S (for Succes) which basically tells OTL that the entries in the timecard are valid. When returning an E in the FastFormula you have the ability to provide up to 5 application messages which will be displayed in the error block on the timecard. A message can be a defined Application Message which we'll do. Return the name of the Application Message you want to present to the end-user in case of an invalid timecard entry. So I return CVS_PREV_0_HOURS as a message which I'll define in eBS as one of the last steps.

When you have added the formula itself click the Verify button to check for any code errors. It should result in a nice message on the status bar. If not than review your code and correct it.

Next thing is the definition of our new custom Time Entry Rule so go to responsibility Global OTL Application Developer and menu option Time Entry Rules - Define Time Entry Rules. Add a new rule and give your rule a meaningfull name. Decide for youself when you want the validation to happen by using the Usage option. You have for example the option to start validation when the user saves the timecard or when the timecard is submitted. I'll use Usage Submission / Resubmission. Select your defined FastFormula function. We don't have any input values for our formula so don't need a context in the Descriptive Flexfield of Formulas and don't need a Mapping. Note that your Time Entry Rule can be active from the time the earlier defined function is active. (The HR function and HR FastFormula has the DateTracking functionality of HR!) 

Next step is to add our new Time Entry Rule to a new or existing Time Entry Rule Group. Go to menu option Time Entry Rules - Define Time Entry Rule Groups. Select an existing Time Entry Rule group or create a new one and add the earlier defined rule to the group.

For the Outcome you have a number of possibilities which will have direct impact to the user and a succesfull timecard submission. If you require that the user always corrects the timecard to succesfully submit it use the Error outcome. If you want to only show a message to the user and allow him to submit the timecard (with the 0 hour enry) than select Warning as the Outcome.

One of the final steps is to assign the Time Entry Rule Group to the OTL preferences. Add a new node to your preference tree and assign your Time Entry Rule Group to the Time Store Time Entry Rules preference. Make sure your users are incorporating this preference by checking the Eligibility Criteria of the preferences.

Final step is that we add our custom error message in E-Business Suite which we will present to the user in case of a 0 hour time entry (remember our application message CVS_PREV_0_HOURS which we returned in our FastFormula?). We'll define that message now. Go to responsibility Application Developer and menu Application - Messages. Define your message and use exactly the same Name as the one you're returning in the FastFormula code. In my case I'll add a message with name CVS_PREV_0_HOURS for all application languages which are in place. It's important to assign the message to application Time and Labor Engine otherwise it can't be found during timecard validation.

In most cases when working with Application Messages and these kind of customizations a bounce of Apache is needed to reflect these kind of changes. So bounce Apache if required.

We're done now with the implementation. The end result will be that the timecard is validated for 0 hour entries and an user friendly error is displayed when applicable.

Good luck with your custom timecard entry validation!

Any comments or feedback? Feel free to use the Comment form below.