Online Payslip enables employees to view their own payslip, via the Employee Self Service menu after payroll processing has been completed. Managers can also search for, and access the payslip of individual employees via the Payroll Professional menu.
The information displayed on the payslip is generated by an archiver program during the payroll process. This process and the payslip are localised for each payroll legislation supplied by Oracle to satisfy local requirements.
Each legislation has it's own requirements regarding what information to display on the Online Payslip. There will be standard information including header details such as Name, Address, Employee Number and National Identifier, and also statutory deductions and balances. There is also a user-definable capability as each legislation allows the user to specify additional site specific elements and balances that they wish to include on their payslip.
Basic Requirement
Our basic requirement is related to US legislation requirement.
It says:- Over Time rate must be displayed for all "Hourly" employee irrespective of their elligibility of OverTime payment.
This can be extent to any legislation or method can be used to fullfill other business requirement of similar kind.
Pre-Requisite
Following are the pre-requisites for applicability of the solution.
1) The related Business Group is defined and configured.
2) All the necessary setup for Payroll is present
3) Employees are attached with the payroll
Solution Approach
Oracle prints the overtime rate only when person has a overtime payment in that pay-period.Now to achive our requirement,we consider the following design
Design:- 1) Two recurring element element of type "Information" with "Standard Link" is created.
2) One recurring element will be attached with a fast formulla which is responsible for calculating the overtime rate and feed into the other element.**
** ==> Before, we proceed with our desgin implementation approach, We must remember that the archive program picks the value from element entry screen for "Information Type" element. Whereas in other cases, the value picks up from the run results.
Payroll does not update the element entry screen of any processed element. It stores the value in Run Results.Hence we need to find a way that will feed the calculated value in element entry screen.
1) Create a recurring element "OT Rate Calculator" with classification "Information"
Navigation :- XX HRMS Manager -> Total Compensation -> Basic -> Element Description
2) Create another recurring element "OT Rate" with classification "Information"
Navigation :- XX HRMS Manager -> Total Compensation -> Basic -> Element Description
3) Create a Standard link for "OT Rate Calculator" with the salary basis "Hourly"
This will ensure that whenever any hourly employee is attached with the payroll is will be automatically attached with the employee.
Navigation :- XX HRMS Manager -> Total Compensation -> Basic -> Link
3) Create a Standard link for "OT Rate" with the salary basis "Hourly"
This will ensure that whenever any hourly employee is attached with the payroll is will be automatically attached with the employee.
Navigation :- XX HRMS Manager -> Total Compensation -> Basic -> Link
4) Now we have to find some way that will update the "Pay Value" of the "OT Rate" element.
To achieve that we will create a fast formula that will calculate and update the "pay value" of "OT Rate" in element entry screen.
a) Create a pl/sql Procedure that will update the input value of the element "OT Rate".
For this purpose we will use the following API "hr_entry_api.update_element_entry". Sample code is given below.
FUNCTION FF_UPD_ELE_ENTRY_SCREEN_VALUE(p_assignment_id IN NUMBER--- From Context Usages
,p_date_earned IN DATE --- From Context Usages
,p_Payroll_ID IN NUMBER--- From Context Usages
,P_ENTRY_VALUE IN NUMBER--- From Parameters
,p_period_start_date IN DATE --- From Parameters
) RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_eff_start_date DATE;
l_business_gr_id NUMBER;
l_ele_entry_id NUMBER;
l_obj_no NUMBER;
l_input_value_id NUMBER;
l_effective_start_date DATE;
l_effective_end_date DATE;
l_upd_warn BOOLEAN;
l_link_id NUMBER;
l_warn BOOLEAN;
l_start_date DATE;
BEGIN
IF (to_char(p_period_start_date,'YYYY')='0001' OR p_period_start_date IS NULL) THEN--default value in fast formula
BEGIN
SELECT ptp.start_date
INTO l_start_date
FROM per_time_periods ptp
WHERE ptp.payroll_id=p_Payroll_ID
AND ptp.end_date=p_date_earned;
EXCEPTION
WHEN OTHERS THEN
l_start_date:=trunc(SYSDATE);
END;
ELSE
l_start_date:=p_period_start_date;
END IF;
BEGIN
SELECT peevf.effective_start_date
,petf.business_group_id
,peevf.element_entry_id
,peef.object_version_number
,peevf.input_value_id
INTO l_eff_start_date
,l_business_gr_id
,l_ele_entry_id
,l_obj_no
,l_input_value_id
FROM pay_element_types_f petf,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_input_values_f pivf,
pay_element_entry_values_f peevf
WHERE petf.element_type_id=peef.element_type_id
AND petf.element_name='OT Rate'
AND peef.assignment_id=p_assignment_id
AND peef.element_link_id=pelf.element_link_id
AND peef.element_entry_id=peevf.element_entry_id
AND pivf.element_type_id=peef.element_type_id
AND pivf.NAME='Pay Value'
AND pivf.input_value_id=peevf.input_value_id
AND trunc(p_date_earned) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND trunc(p_date_earned) BETWEEN peef.effective_start_date AND peef.effective_end_date
AND trunc(p_date_earned) BETWEEN pelf.effective_start_date AND pelf.effective_end_date
AND trunc(p_date_earned) BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND trunc(p_date_earned) BETWEEN peevf.effective_start_date AND peevf.effective_end_date;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;
BEGIN
hr_entry_api.update_element_entry(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => l_start_date,--p_session_date date on which change is taking place. It will always be start date of pay period
p_check_for_update =>'Y', ---- Check to see if the entry is being updated
p_date_earned => p_date_earned,
p_element_entry_id => l_ele_entry_id,
p_input_value_id1 => l_input_value_id,
p_entry_value1 => P_ENTRY_VALUE,-- the value will be calculated within the fast-formula using db item.
p_override_user_ent_chk => 'N'
);
COMMIT;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN 1;
END;
END FF_UPD_ELE_ENTRY_SCREEN_VALUE;
b) Create a formula function and attached the above pl/sql function
Navigation :- XX HRMS Manager -> Total Compensation -> Fast Formula ->Formula Function
c) Create a fast formula that will calculate the overtime rate and call the above pl/sql function.
A sample code is pasted below
Navigation :- XX HRMS Manager -> Total Compensation -> Fast Formula -> Write Formula
DEFAULT FOR L is 0
DEFAULT FOR Calculation_Period_SD is '0001/01/01 00:00:00' (DATE)
DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '0001/01/02 00:00:00' (DATE)
DEFAULT FOR ASG_SALARY IS 0
DEFAULT FOR ASG_SALARY_BASIS IS 'NOT ENTERED'
DEFAULT FOR OT_REDUCE_REGULAR IS 'N'
DEFAULT FOR USER_ENTERED_TIME IS 'N'
DEFAULT FOR Timecard_Required IS 'N'
DEFAULT FOR LABOR_RECORDING_COUNT IS 0
DEFAULT FOR OT_ASG_GRE_RUN IS 0
DEFAULT FOR OT_ASG_GRE_YTD IS 0
DEFAULT FOR OT_HOURS_ASG_GRE_RUN IS 0
DEFAULT FOR Work_Schedule IS 'NOT ENTERED'
DEFAULT FOR ASG_HOURS IS 0
DEFAULT FOR Hours IS 0
DEFAULT FOR Multiple IS 1
DEFAULT FOR Rate IS 0
DEFAULT FOR Rate_Code IS 'NOT ENTERED'
DEFAULT FOR ASG_FREQ IS 'NOT ENTERED'
DEFAULT FOR CURRENT_ELEMENT_TYPE_ID IS 0
DEFAULT FOR OT_ACCRUED_ASG_ITD IS 0
DEFAULT FOR MAXIMUM_AMOUNT IS 0
DEFAULT FOR OT_ASG_GRE_ITD IS 0
DEFAULT FOR AUTHORIZATION_END_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR PRORATE_START IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR PRORATE_END IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR ENTRY_DATE_EARNED IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR p_return_no IS 0
DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_TD_BD_RUN IS 0
DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_TD_BD_RUN IS 0
/* ===== Defaults Section End ===== */
/* ===== Inputs Section Begin ===== */
INPUTS ARE
Hours,
Multiple,
Rate,
Rate_Code (text),
maximum_amount,
authorization_end_date,
prorate_start (date),
prorate_end (date)
/* ===== Inputs Section End ===== */
Multiple = 2.5 -- Multipler for Overtime rate, specific to Organization
calc_rate = ASG_SALARY --- This will give the hourly rate
Rate =calc_rate * Multiple -- this will give the Over time Rate
Calculation_Period_SD=get_date('PAYROLL_PERIOD_START_DATE')--Provides the pay period start date
L=XX_OT_RATE_UPD_ELE_VAL(Rate,Calculation_Period_SD)
if(L=1) then
(
E=PUT_MESSAGE('Error While Determining Overtime Rate')
)
RETURN Rate
d) Now we have to attached the fast formula with the "OT Rate Calculator" element, so that when payroll process the element, execute the fast formula and update the "Pay Value" of "OT Rate" in element entry screen.
Navigation :- XX HRMS Manager -> Total Compensation -> Fast Formula ->Formula Results
5) Our element is ready for use. Now if we run the payroll, it will populate the Overtime rate. Now to make it visible in the online payslip , we have to add it to the "Payslip Information" of the Business group.
Navigation :- XX HRMS Manager -> Work Structure -> Organization -> Description -> Query for business Group --> Others--> Payslip Information
Note:- 1) Instead of using API to update the element entry via API, we can use the "Update recurring entry" option available in Formula Results screen.
The above mentioned option also update the value in the element entry screen of an recurring element. But main problem with the option is that it updates the
element w.e.f check date. Generally check date always lie on next pay period. Hence the element will have the value in next pay period, where as the current pay period won't have any value or value of previous pay period. If there is any changes in hourly rate for the current pay period that won't get reflected.
[Screen shot of the option is shown below]
References:- 1) https://metalink.oracle.com
Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle may not provide you a support for any data corruption or any other problem in your custom code/problem arises because of the custom code. The author is not responsible for any kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.