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.

How to restrict the absence type list of values in Oracle Self-Service Human Resources.R 12.2.8

 

    Oracle Self-Service HR enables users to apply for absences. When applying for an absence, users enter the absence date or time, absence type, absence status, and absence reason. The absence types (for example, Vacation, Sick Leave etc.) are defined in Oracle HRMS application. Currently, all the absence types defined in Oracle HRMS application are displayed to the self-service user. 

    The user may not be eligible for all the absence types displayed in the self-service application.

    Example, In Our Organization, Unauthorized LWA Should not be viewed in the SSHR - Absence entry page. But the same to be displayed for the Payroll User in Enter & maintain - Absence Screen.

Solution :

    Oracle Self-Service HR introduces a custom package that enables customers to write the logic for deriving the absence type values based on their business requirements.

    Use the following the sample steps to derive a restricted list of values for absence type LOV in Absence Management. The custom logic has to be written in the New Package function HR_ABSENCE_RESTRICTED.ABSENCES_RESTRICTED Login person id and selected person id will be passed by default – using these two parameters the rest of the data or parameters can be queried from the tables.

Step 1: Query the HR_ABSENCE_RESTRICTED Package 

Step 2: Add the below function in the Package Body.

 

function absences_restricted(selected_person_id in varchar2,
         login_person_id in varchar2
         )return varchar2 is
l_sex VARCHAR2 (15) := NULL;

begin

IF to_number(selected_person_id) is not null THEN
 
    select sex into l_sex from per_all_people_f where person_id= to_number(selected_person_id)
    and sysdate between effective_start_date and effective_end_date;
 
        if l_sex = 'F' THEN
        return  '66,3061';
    elsif l_sex = 'M' THEN
    return '65,3061';
    else
    return '66,65,3061';
    END IF;

END IF;

end absences_restricted;
 

The above function is to restrict, the Maternity Leave  view access only to the Female Employees & Paternity Leave view access only to the Male Employees & Restrict Unauthorized LWA Leave for the Both female , male & Others.

Note: In this example, ‘66,3061,66’ are the id of the absence type, which has to restricted.

After Complied this, Go to Employee Self Service, Absence --> Create Absence, In the Absence Type you cant find the Unauthorized LWA Leaves.