How to restrict the Order of Absence (Leave) Entry in SSHR in Oracle HRMS ?

 

Business Scenario:

    In our organization, HR has a policy that, each employee should apply Absence(Leave) only based on the Orders.

    Example, There are 3 types of Leaves are available in the Organization,

    1.Casual Leave, 

    2.Pay leave, 

    3.Earned leave

    Each employee has to first consume the leave balances of Casual Leave first then he/she has to utilize the Pay Leave & then finally he/she must avail Earned leave.

Solution:

    In eBS HRMS, R12.2 We can achieve this logic by enable the Custom Logics in BG_ABSENCE_DURATION Fast formula.

Step 1: Get the Accrual balance of the Leave types 

     Create the Custom Function to get the Accrual Balance of the leave types with input as Assignment ID, Calculation Date, Accrual Element Name.

 

create or replace FUNCTION XXBES_GET_NET_VALUE_BG (
    p_assg_id            IN                   NUMBER,
    p_calculation_date   IN                   DATE,
    p_accrual_element_name IN    VARCHAR2
) RETURN NUMBER IS

    l_start_date     DATE;
    l_end_date       DATE;
    l_acc_end_date   DATE;
    l_value          NUMBER := 0;
    l_net_value      NUMBER(15, 2);
    p_plan_id        NUMBER;
    p_bg_id          NUMBER;
    p_payroll_id     NUMBER;

/* -------FETCHING PAYROLL_ID BEGINs---------- */
BEGIN
    SELECT
        payroll_id
    INTO p_payroll_id
    FROM
        per_all_assignments_f
    WHERE
        p_calculation_date BETWEEN effective_start_date AND effective_end_date
        AND assignment_id = p_assg_id;

/* xxxxxxxxxxxFETCHING PAYROLL_ID </ENDs>xxxxxxxxxxxxxxxxxx */   


/* -------FETCHING ACCRUAL_PLAN_ID BEGINs---------- */

    SELECT
        pap.accrual_plan_id
 INTO p_plan_id
    FROM
        pay_accrual_plans pap
    WHERE
        pap.accrual_plan_name  IN (
            SELECT
            petf.element_name
            FROM
                pay_element_entries_f   peef,
                pay_element_types_f     petf
            WHERE
                peef.element_type_id = petf.element_type_id
                AND petf.element_name =  p_accrual_element_name
                AND petf.processing_type = 'R'
                AND peef.assignment_id = p_assg_id
                AND p_calculation_date BETWEEN peef.effective_start_date AND peef.effective_end_date)
        ;

/* xxxxxxxxxxxFETCHING ACCRUAL_PLAN_ID </ENDs>xxxxxxxxxxxxxxxxxx */

/*--------Getting Business Group ID-----------*/

    SELECT
        business_group_id
    INTO p_bg_id
    FROM
        per_all_assignments_f
    WHERE
        assignment_id = p_assg_id
        AND p_calculation_date BETWEEN effective_start_date AND effective_end_date;

/*xxxxxxxxGetting Business Group ID - </ENDs>xxxxxxxx */                                           

/*--------------- Execution of Seeded GET_NET_ACCRUAL Function from the PACKAGE - PER_ACCRUAL_CALC_FUNCTIONS-------------------------*/

            per_accrual_calc_functions.get_net_accrual
            (
                                                p_assignment_id                 => p_assg_id,
                                                p_plan_id                         => p_plan_id,
                                                p_payroll_id                     => p_payroll_id,
                                                p_business_group_id             => p_bg_id,   -- Kindly change your business group id accordingly
                                                p_assignment_action_id             => -1,
                                                p_calculation_date                 => TO_DATE(p_calculation_date),
                                            -- DATE YOU WANT TO CHECK THE NET ENTITLEMENT EX. TO_DATE('01-MAR-2009', 'DD-MON-YYYY')
                                                p_accrual_start_date             => NULL,
                                                p_accrual_latest_balance         => NULL,
                                                p_calling_point                 => 'FRM',
                                                p_start_date                     => l_start_date,
                                                p_end_date                      => l_end_date,
                                                p_accrual_end_date                 => l_acc_end_date,
                                                p_accrual                         => l_value,
                                                p_net_entitlement                 => l_net_value);

/*xxxxxxxxxxxxxxxxExecution of Seeded GET_NET_ACCRUAL Function from the PACKAGE - PER_ACCRUAL_CALC_FUNCTIONS </ENDs >xxxxxxxxxxxxxxxxxxxxxx*/

    RETURN nvl(l_net_value, 0);
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END; 

Step 2: Create Formula Function.

Navigation : India HRMS Manager --> Other Definition --> Formula Function

Step 3: Enable the Validation in BG_ABSENCE_DURATION Formula.

     Based on the Business need, Update the Custom logics in BG_ABSENCE_DURATION Fast Formula

Note: Here absence_attendance_type_id is Earned Leave Absence Type ID.

XXBES_LEAVE_ORDER_VALIDATION,  XXBES_HPL_ORDER_VALIDATION - These are the  

Custom Messages created in System administrator Responsibility --> Messages form.

 

/*Earned Leave Validations*/

 if absence_attendance_type_id = 63 then
  (
    CL_DAYS = 0
    CL_DAYS = XXBES_GET_NET_VALUE_BG (date_start,'Casual_LeaveName')
    
    HPL_DAYS = 0
    HPL_DAYS = XXBES_GET_NET_VALUE_BG (date_start,'Half Pay Leave Accrual')
    
    if to_num(duration) < 1 then
    (
    duration = 'FAILED'
    invalid_msg = 'XXBES_MIN_DAYS_VALIDATION'
    )
    
    if CL_DAYS > 0 then

   (
    duration = 'FAILED'
    invalid_msg = 'XXBES_LEAVE_ORDER_VALIDATION'
    )
   
    if HPL_DAYS > 0 then

   (
    duration = 'FAILED'
    invalid_msg = 'XXBES_HPL_ORDER_VALIDATION'
    )
  )

Compile the formula & Create Absence Entry and Check the Validation.

Exclude Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence

 

If Customer wants to exclude the  Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence while applying the Absence request, then follow the below steps to achieve it Oracle HRMS R12.2.8

Scenario: While applying Causal Leave , System should not consider the In between 2nd & 4th Saturday , Sunday &  Paid Holidays from the Duration count. But for as Other Absence Types, it should include the in between days.

 

Step 1: Create the Function.

 Compile the Below function in the Sql Developer.

create or replace FUNCTION XX_GET_HOLI_WEEK_COUNT(
    p_start_date DATE,
    p_end_date   DATE )
  RETURN NUMBER
IS
  T_DAYS NUMBER (15) := NULL;
BEGIN
  T_DAYS := 0;
  SELECT XX.week_count + XX."Holi_Count" + XX.Saturday
  INTO T_DAYS
  FROM
    (SELECT week_count,
            (
            SELECT COUNT(holi.name)
            FROM hxt_holiday_days holi
            WHERE holiday_date BETWEEN ( p_start_date ) AND ( p_end_date )
            ) AS "Holi_Count",
            (
            SELECT count(*)
            FROM
              (WITH ALL_SATURDAYS AS
              (SELECT TO_CHAR(to_date('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW')                        * (level)                                                                                             AS WEEK_NO,
                NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR')                                   + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT')                        AS SATURDAY_DATE,
                row_number() OVER (PARTITION BY TO_CHAR(NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR') + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT'),'MON') ORDER BY level) AS Pos
              FROM DUAL
                CONNECT BY level<= 52
              ORDER BY 1
              )
            SELECT SATURDAY_DATE ,POS FROM ALL_SATURDAYS WHERE POS IN (2,4) ORDER BY 1,2
              )
            WHERE SATURDAY_DATE BETWEEN p_start_date AND p_end_date
            ) AS Saturday
    FROM
      ( WITH dates AS
      (SELECT TO_DATE(p_start_date) + level - 1 datum
      FROM dual
        CONNECT BY level <= TO_DATE(p_end_date) - TO_DATE(p_start_date) + 1
      )
    SELECT COUNT(datum) AS week_count
    FROM dates
    WHERE TO_CHAR(datum,'dy','nls_date_language = english') = 'sun'
      )
    )XX;
  RETURN T_DAYS;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || sqlerrm);
END;

 

Step 2: Create the Formula Function

Navigation : India HRMS Manager  --> Other Definitions --> Formula Function

Create the Formula Function as per the Screen shot mentioned below,

 

Step 3: Add the Validation in BG Absence Duration Fast Formula

Navigation : India HRMS Manager  --> Total Compensation  --> Write Formula.

Query the BG_ABSENCE_DURATION Formula

 Add the Below conditions in the Fast formula,

If absence_attendance_type_id = 61 then
         (
           L_DAYS = XX_GET_HOLI_WEEK_COUNT (date_start,date_end)
           /*Duration = TO_CHAR(TO_NUMBER(Duration) - L_DAYS)*/
           l_duration = (DAYS_BETWEEN(date_end,date_start) + 1) - L_DAYS
          )
        ELSE
        l_duration = (DAYS_BETWEEN(date_end,date_start) + 1)
       
        duration = to_char( l_duration - l_half_day_leave)
  )

Note : Here 61 is the Absence type ID for which the Exclude logics to be enabled.