Sunday, February 14, 2016

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:

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect