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.
