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.