Absence Integration with OTL Projects Layout

As per Doc ID 1285890.1 OTL-Absence Integration and Self Service Projects and Payroll Timecard layout is not supported. In this document I had developed work around for this functionality.

Goal:

 Prevent Timekeeper to enter timesheet in a day which has approved absence in HR (Part 1)

 Prevent HR to create absence in a day which has working hours in OTL module (Part 2)

Prevent Timekeeper to enter timesheet in a day which has approved absence in HR.

--  Create the following package:

CREATE OR REPLACE PACKAGE APPS.XXAA_PREV_ABSENCE_DAYS_IN_OTL

IS

   FUNCTION XXAA_OTL_CHECK_LEAVE_IN_DAY (p_resource_id NUMBER, P_DATE DATE)

      RETURN NUMBER;

   FUNCTION XXAA_OTL_PREV_ABSENCE_DAYS (p_resource_id NUMBER)

      RETURN NUMBER;

END XXAA_PREV_ABSENCE_DAYS_IN_OTL;

/

CREATE OR REPLACE PACKAGE BODY APPS.XXAA_PREV_ABSENCE_DAYS_IN_OTL

IS

   FUNCTION XXAA_OTL_CHECK_LEAVE_IN_DAY (p_resource_id NUMBER, P_DATE DATE)

      RETURN NUMBER

   IS

      L_COUNT   NUMBER := 0;

   --L_DATE    DATE        ;

   BEGIN

      SELECT

NVL((SELECT COUNT (*)

           FROM PER_ABSENCE_ATTENDANCES PAA

           WHERE PAA.PERSON_ID = p_resource_id

           AND ((PAA.DATE_END BETWEEN P_DATE AND P_DATE)

                 OR (PAA.DATE_START BETWEEN P_DATE AND P_DATE)

                 OR (P_DATE BETWEEN PAA.DATE_START AND PAA.DATE_END)

                 OR (P_DATE <= PAA.DATE_START AND P_DATE >= PAA.DATE_END))),

        0)

      INTO L_COUNT

      FROM DUAL;

      RETURN L_COUNT;

   END XXAA_OTL_CHECK_LEAVE_IN_DAY;

   FUNCTION XXAA_OTL_PREV_ABSENCE_DAYS (p_resource_id NUMBER)

      RETURN NUMBER

   IS

      l_return_rule_status   NUMBER := 0;

      L_LEAVE_OVERLAB        NUMBER := 0;

      L_WORKING_HOURS        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).SCOPE = 'DETAIL'

            THEN

               L_WORKING_HOURS := NVL (l_tbb_array (i).MEASURE, 0);

               FOR c IN l_tbb_array.FIRST .. l_tbb_array.LAST

               LOOP

                  IF l_tbb_array (c).SCOPE = 'DAY'

                     AND l_tbb_array (c).TIME_BUILDING_BLOCK_ID =

                            l_tbb_array (i).PARENT_BUILDING_BLOCK_ID

                  THEN

                     L_LEAVE_OVERLAB := XXAA_PREV_ABSENCE_DAYS_IN_OTL.XXAA_OTL_CHECK_LEAVE_IN_DAY (p_resource_id,FND_DATE.CANONICAL_TO_DATE(l_tbb_array (C).START_TIME));

                     IF L_WORKING_HOURS > 0 AND L_LEAVE_OVERLAB > 0

                     THEN

                        l_return_rule_status := l_return_rule_status + 1;

                     END IF;

                  END IF;

               END LOOP;

            END IF;

         END LOOP;

      END IF;

      RETURN l_return_rule_status;

   END XXAA_OTL_PREV_ABSENCE_DAYS;

END XXAA_PREV_ABSENCE_DAYS_IN_OTL;

2) Create a custom message to display when the users create the time card for approved leaves

Nav : Application Developer àApplication àMessages

Name      : XXAA_PREV_ABSENCE_DAYS_IN_OTL

Application  : Time and Labor Engine

Message   : You're not allowed to enter the hours for a day which has approved leave in HR

3) Create a Formula Function

Nav : Global Super HRMS Manager àOther Definitions àFormula Functions

Name             : XXAA_PREV_ABSENCE_DAYS_IN_OTL

Data Type     : Number

Class              : External function

Alias Name   : XXAA_PREV_ABSENCE_DAYS

Definition     : XXAA_PREV_ABSENCE_DAYS_IN_OTL.XXAA_OTL_PREV_ABSENCE_DAYS

Click on Parameters button

Parameter Name  : P_RESOURCE_ID

Type                       : Number

Class                      : Input Only

4) Create a Write Formulas

Nav : Global Super HRMS Manager àTotal Compensation àBasic àWrite Formulas

Name             : XXAA_PREV_ABSENCE_DAYS_IN_OTL

Type               : OTL Time Entry Rules

Description   : To Prevent when the Users Create the Timecard for Approved Leaves(Absence Days)

Save and re query the formula and click on Edit button

Enter the below code

/* *****************************************************************

Formula NamE    : XXAA_PREV_ABSENCE_DAYS_IN_OTL

AuthoR          :

Description     : To Prevent when the Users Create the Timecard for Approved Leaves(Absence Days)

***************************************************************** */

/* Initialize 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 ( XXAA_PREV_ABSENCE_DAYS_IN_OTL(resource_id) > 0 )

THEN

    (rule_status = 'E' message1 = 'XXAA_PREV_ABSENCE_DAYS_IN_OTL' return rule_status,message1)

ELSE

    (rule_status = 'S' return rule_status)

/* ***************************************************************** */

Clink on Verify button and Save

5) Create a Time Entry Rule with custom formula

Nav : OTL Super Administrator àTime Entry Rules àDefine Time Entry Rules

Name             : To Prevent when the Users Create the Timecard for Approved Leaves  - Rule

Description   : To Prevent when the Users Create the Timecard for Approved Leaves

Usage             : Save

Formula : To Prevent when the Users Create the Timecard for Approved Leaves
From               : 17-AUG-2015(Note : From date should be after the formula date)

6) Create a Time Entry Rule Group with the TER you just created in it

Nav : OTL Super Administrator àTime Entry Rules àDefine Time Entry Rule Group

Name             : To Prevent when the Users Create the Timecard for Approved Leaves

TER Name    : To Prevent when the Users Create the Timecard for Approved Leaves

From               : 19-DEC-2014

Outcome        : Error

7) Assign the Time Entry Rule Group to the employee(s) via the Time Entry Rule Group preference

Nav : OTL Super Administrator àPreferences

àPreferences

Right Click on any preference and click on New

Name in Tree : XXAA Exceeds Time Entry Hours Node

Right Click on XXAA Exceeds Time Entry Hours Node preference and click on New

Name in Tree               : Do Not Enter More Than 8 Hours - Preference

Preference             : Time Store Time Entry Rules

Preference Value àTime Entry Rule : Do Not Enter More Than 8 Hours

Click on Eligibility Criteria Tab

Name of rule                : Do Not Enter More Than 24 Hours - Eligibility Criteria

Name of Branch   : XXAA Exceeds Time Entry Hours Node

Link by          : All People

Precedence            : 90(Max Number)

From                       : 01-JAN-2000

Click on People tab and Check the preference is assigned or not

--  Create Time Entry Rule

Global OTL Application Developer > Time Entry Rules > Define Time Entry Rules

--  Create Time Entry Rule Group

Global OTL Application Developer > Time Entry Rules > Define Time Entry Rule Groups

--  Assign Time Entry Rule Group to Preferences

  Test Case

1. Create an absence for employee

2.  Create timesheet for him in the same day and try to save: