Accrual Plan:
Oracle Provides Accrual plan
Functionality for calculating leaves balance calculation and Balance Carry Over
calculations
The following points should be taken
into consideration for calculation Balances
1. Opening
Balance : -
Some of the Organization Provides
Yearly opening balance to employee. We need to store these opening balances in
an Element
2. Leave
Adjustment: -
There are cases in which adjustments
are made because of redundancies in calculation of leave balance. In such cases
we need to store these adjustments in an element and make the use of the same.
3. Leave
Encashment:
Organizations provide policy for
encashment of leave when Leave balance exceeds a given amount. In order to
capture these details, we need to have an element for the same
How to Setup Leave Accruals
1) Create
New Element for Opening Balance
Navigation: HRMS Super User à Total
Compensation à Basic à Element Description
2) Create
New Element for Adjustment Days
Navigation: HRMS Super User à Total
Compensation à Basic à Element Description
3) Create
New Element for Leave Encashment
Navigation: HRMS Super User à Total
Compensation à Basic à Element Description
4) Create
Link for all the three Elements created Above
Navigation: HRMS Super User à Total
Compensation à Basic à Link
5) Define
Accrual Plan
Navigation: HRMS Super User à Total
Compensation à Basic à Accrual Plan
6) Go
to Accrual Bands to define the No of Leaves employee could avail during the
Year
7) Go
to Net Accrual Calculations to record the leave calculation
8) When
one creates an accrual plan, system automatically generate below said elements
and their corresponding links too (Create Links Manually if not created
Automatically)
· Vacation
Leave Plan
· Vacation
Leave Plan Carried Over
· Vacation
Leave Plan Payroll Balance
· Vacation
Leave Plan Residual
· Vacation
Leave Plan Tagging
9) Register
Employee with the Accrual Plan
Navigation: HRMS Super User à People à Enter
&Maintain à Search for an
Employee à Assignment à Entries à Add “Vacation
Leave Plan” on Effective Date
10) How to View Employee Accruals
Navigation: HRMS
Super User à Fastpath à Accruals
Now in the Above
Scenario we are using the seeded Formula “PTO_PAYROLL_CALCULATION”, this would
calculate as per the accrual bands set for the said duration of Service.
Requirements could
vary as per each of the clients; we have created a scenario wherein we would be
taking the Accruals as per the contract type of the employee
1. The
Total Accruals are stored in User Tables
Navigation: HRMS Super User à Other
Definitions à Table Structure / Table Values
2. Create
New Function which would return, Leaves Accrued as on Effective Date
CREATE OR REPLACE FUNCTION APPS.XXAA_GET_LEAVE_ACCRUAL_F
(
p_business_group_id NUMBER,
p_assignment_id VARCHAR2,
p_calculation_date DATE,
p_contract_start_date OUT DATE,
p_contract_end_date OUT DATE
)
RETURN NUMBER
AS
l_err_msg VARCHAR2 (500) := NULL;
l_person_id NUMBER := NULL;
l_hire_date DATE := NULL;
l_nationality VARCHAR2 (250) := NULL;
l_contract_type VARCHAR2 (250) := NULL;
l_yearly_accrual NUMBER := NULL;
l_per_day_accrual NUMBER := NULL;
l_accrual_start_date DATE := NULL;
l_calc_start_date DATE := NULL;
l_accrual_total_days NUMBER := NULL;
l_net_accrual NUMBER := 0;
BEGIN
BEGIN
SELECT per.person_id,
NVL (hr_general.decode_lookup('NATIONALITY', per.nationality),'XX') nationality,
pps.date_start hire_date,
ppg.segment6 contract_type
INTO l_person_id,
l_nationality,
l_hire_date,
l_contract_type
FROM per_all_people_f
per,
per_all_assignments_f paaf,
per_periods_of_service pps,
pay_people_groups
ppg
WHERE per.person_id = paaf.person_id
AND per.person_type_id IN (1126,1127)
AND paaf.assignment_type = 'E'
AND paaf.assignment_status_type_idIN (1, 2)
AND paaf.primary_flag = 'Y'
AND paaf.business_group_id =p_business_group_id
AND paaf.period_of_service_id =pps.period_of_service_id
AND p_calculation_date BETWEEN per.effective_start_date AND per.effective_end_date
AND p_calculation_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_id =p_assignment_id
AND paaf.people_group_id =ppg.people_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
debug_script_p ('Exception - Employee Details:
' || l_err_msg);
END;
IF l_contract_type
IS NOT NULL
THEN
SELECT hruserdt.get_table_value(p_business_group_id,
'Leave Accruals',
'Vacation Leave',
l_contract_type,
p_calculation_date)
INTO l_yearly_accrual
FROM DUAL;
END IF;
l_per_day_accrual := ROUND ( (l_yearly_accrual/ 360), 3);
l_accrual_start_date := TO_DATE ('01-JAN-' || TO_CHAR(p_calculation_date, 'YYYY'),'DD-MON-YYYY');
IF
l_accrual_start_date > l_hire_date
THEN
l_calc_start_date := l_accrual_start_date;
ELSE
l_calc_start_date := l_hire_date;
END IF;
l_accrual_total_days := p_calculation_date -l_accrual_start_date;
l_net_accrual := ROUND (l_accrual_total_days * l_per_day_accrual, 0);
p_contract_start_date := l_hire_date;
p_contract_end_date := TO_DATE ('31-DEC-4312','DD-MON-YYYY');
RETURN
l_net_accrual;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
3. Define
the above create Function in the System as “Database Function”
Navigation: HRMS Super User à Other
Definitions àFormula Function
Define the Context
Usage Parameters & Actual Parameters used in SQL Function
4. Define
Fast Formula
Navigation: HRMS Super User à Total
Compensation àWrite Formula
/*
CREATED BY :
PURPOSE :
CALCULATE ACCRUAL PLAN
CREATION DATE :
*/
ALIAS ASG_GRADE AS GRADE
ALIAS ASG_EMPLOYMENT_CATEGORY AS EMP_CAT
/* DEFAULT SECTION */
DEFAULT FOR LRATE
IS '0'
DEFAULT FOR GNONQAT
IS '0'
DEFAULT FOR GQAT
IS '0'
DEFAULT FOR BGROUP
IS 'NOT
ENTERED'
DEFAULT FOR EMP_CAT
IS ' '
DEFAULT FOR ASG_GRADE
IS 'NOT
ENTERED'
DEFAULT FOR ASG_NUMBER
IS '0'
DEFAULT FOR ACCRUAL_PLAN_ID
IS 0
DEFAULT FOR EMP_HIRE_DATE
IS '01-JAN-0001' (DATE)
DEFAULT FOR EMP_TERM_DATE
IS '01-JAN-0001' (DATE)
DEFAULT FOR CALCULATION_DATE
IS '02-JAN-0001' (DATE)
DEFAULT FOR SYSDATE IS '01-jan-0001'(DATE)
DEFAULT FOR SUS_DATE
IS '01-jan-0001'(DATE)
DEFAULT FOR SESSION_DATE
IS '01-jan-0001'(DATE)
DEFAULT FOR YEAR_START
IS '01-jan-0001'(DATE)
/* INPUT SECTION */
INPUTS ARE CALCULATION_DATE (DATE)
ACCRUAL_DAYS=0
L_CONTRACT_SD ='0001/01/01 00:00:00'(DATE)
L_CONTRACT_ED ='0001/01/01 00:00:00'(DATE)
ACCRUAL_DAYS = AQS_GET_LEAVE_ACCRUALS(CALCULATION_DATE,L_CONTRACT_SD,L_CONTRACT_ED)
EFFECTIVE_START_DATE = GREATEST(EMP_HIRE_DATE, L_CONTRACT_SD)
IF (EMP_TERM_DATE WAS DEFAULTED )
THEN
(
EFFECTIVE_END_DATE = L_CONTRACT_ED
)
ELSE
(
EFFECTIVE_END_DATE = EMP_TERM_DATE
)
EFFECTIVE_END_DATE = LEAST(EFFECTIVE_END_DATE,L_CONTRACT_ED)
ACCRUAL_END_DATE = CALCULATION_DATE
TOTAL_ACCRUED_PTO = ROUND(ACCRUAL_DAYS,0)
RETURN
TOTAL_ACCRUED_PTO,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ACCRUAL_END_DATE
5. Attach
the Accrual Formula to the corresponding PLAN and test various scenarios
No comments:
Post a Comment