Tuesday, December 8, 2015

How to Setup Absence Management and Leave Accrual Plan in Oracle

How to Setup Absence Management

Absence Management:
 Oracle provides seeded functionality which can capture leave related information and provide extensibility so you can make changes as per your requirement. Absence management is provided in self service (OA framework pages) as well as from core HR (Oracle forms)

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 Absence Management

1)     Create New Element for Absence
Navigation: HRMS Super User à Total Compensation à Basic à Element Description
         Element Name: Vacation Leave                            
         Description: These Element Capture Days for Individual Annual Leave
         Primary Classification: Information
         Type: Nonrecurring
         Input Values  
Days à Day
Pay Value à Money
Future 1 à Character
Future 2 à Character
 

2)     Create Element Link
Navigation: HRMS Super User à Total Compensation à Basic à Link

3)     Create Absence Reason (Optional)
Navigation: HRMS Super User àOther Definitions à Application Utilities Lookups
Search for Type “ABSENCE_REASON”
Code: VN
Meaning: Vacation Leave
Description: Vacation Leave
From: 01-JAN-1950

4)     Create Absence Category
Navigation: HRMS Super User àOther Definitions à Application Utilities Lookups
Search for Type “ABSENCE_CATEGORY”
Code: VL
Meaning: Vacation Leave
Description: Vacation Leave
From: 01-JAN-1950     

5)     Create Absence Type
Navigation: HRMS Super User à Total Compensation à Basic à       Absence Types

6)     We are done with the Setup, now create vacation leave for an employee and test the same
Navigation: HRMS Super User à People à Enter & Maintain à Search for Any Employee à Others à Absence

7)     Once the Absence is confirmed, the element will be attached along with the No of days to the corresponding employee in Employee Element Entries
Navigation: HRMS Super User à People à Enter & Maintain à Search for Any Employee àAssignment à Entries


How to Setup up Leave Accrual Plan
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.xxx_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_id IN (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
/*
PURPOSE         : CALCULATE ACCRUAL PLAN          
*/

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 scenario

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect