The following sample formulas show how you can create payroll formulas to be used in different situations requiring proration.
Oracle may change or upgrade these formulas in future releases of HRMS programs. These formulas are strictly for example or prototype purposes only, and are not intended to provide a ready-made solution to be used in your environment. You can make a copy of these formulas, subject to the terms of the license agreement for the programs, but you should not change your copied version for prototyping purposes. You should always write a new formula of your own to meet your particular requirements. These formulas may contain certain hard-coded values to simplify the use of formulas for proration.
A typical example of proration would be when a new employee starts work in the middle of a monthly payroll period and your payroll department makes a pro-rata payment to reflect the proportion of monthly pay to which the employee is entitled.
The prototype formula supplied is only designed to work for payroll calculations involving proration and should not be used for any other purpose. Any use of the formula is subject to the terms of the Oracle license agreement for the HRMS programs and documentation.
/***************************************************
Formula name : FF_PRORATION_SAL_MANAGEMENT
Formula to calculate salary in proration
DISCLAIMER: Oracle may change or upgrade this FastFormula in the future releases. This FastFormula is strictly for example or prototype purposes only. This FastFormula is not intended to provide a ready-made solution to the users. This formula may contain certain hard-coded values added to simplify the concept of usage of FastFormulas in proration. Users should make a copy of this formula and not change this formula. Users need to write their own new formula(s) to meet requirements.
****************************************************
default for ASG_SALARY_BASIS_GRADE_ANNUALIZATION_FACTOR is 0
default for prorate_start is '01-JAN-1990' (date)
default for prorate_end is '01-JAN-1900' (date)
default for PAY_PROC_PERIOD_START_DATE IS '01-JAN-1950' (date)
default for PAY_PROC_PERIOD_END_DATE IS '01-JAN-1950' (date)
default for NI_NEW_TAX_YEAR IS '01-JAN-1950' (date)
default for annual_salary is 0.0
inputs are annual_salary (number),
prorate_start (date),
prorate_end (date)
/**
Prorate_start and prorate_end dates are passed from the payroll engine. These dates basically represent the dates on which the changes occur in the salary amount during the pay period. annual_salary is an input value created in the element to which this formula will be tied. Here the assumption is that the user enters the annual salary amount through salary management.
**/
l_amount=annual_salary
l_string = ''
if(ASG_SALARY_BASIS_GRADE_ANNUALIZATION_FACTOR was not defaulted) then
(
/**
This calculation makes sure that l_amount has the annual salary.
**/
l_amount =
amount*ASG_SALARY_BASIS_GRADE_ANNUALIZATION_FACTOR
)
/**
l_tax_year_start_date derives the value from the DBI
NI_NEW_TAX_YEAR.
The following calculation sets the start date of the tax year to 01-APR-YYYY and end date of the tax year to 31-MAR-(YYYY+1)
**/
l_tax_year_start_date = NI_NEW_TAX_YEAR
l_tax_year_start_date = TRUNC(l_tax_year_start_date,'month')
l_tax_year =
TO_NUMBER(TO_CHAR(l_tax_year_start_date,'YYYY')) + 1
l_tax_year_end_date =
TO_DATE('31/03/'||TO_CHAR(1_tax_year),'DD/MM/YYYY')
/**
The following calculation is for the case when no proration occurs during the pay period and element entry start date and element entry end date is not equal to the pay period start and end dates respectively.
**/
IF(prorate_start was defaulted) then
(
IF(PAY_PROC_PERIOD_END_DATE<>ENTRY_END_DATE) THEN
(
IF(PAY_PROC_PERIOD_START_DATE<>ENTRY_START_DATE) THEN
(
/**
Since prorate_start date is defaulted, it means no proration occured, therefore we simply return the annual salary amount divided by 12.
**/
l_amount = ROUND(amount/12,2)
result1 = l_amount
return result1
)
)
)
l_post_calc = 'N'
/**
l_post_calc is a flag used to do the calculation differently depending on the values of this flag.
**/
/**
The following if condition takes care of the case when
Either
The element entry starts on the same day as pay period start date
OR
Prorate start date IS EQUAL TO element entry start date AND
Prorate start date IS NOT EQUAL TO one day prior to pay period start date.
For example, in a monthly payroll running for the month of June 2000.
Either
Element entry start date is )01-JUN-2000 (employee starts on the first day of the payroll period)
OR
Prorate start date is 14-JUN-2000 (employee starts in the middle of the month or gets a salary change in the middle of the month)
Prorate start date IS NOT 31-MAY-2000
**/
if((prorate_start = ENRTY_START_DATE and
prorate_start <> ADD_DAYS(PAY_PROC_PERIOD_START_DATE,-1
)) or
ENTRY_START_DATE = PAY_PROC_PERIOD_START_DATE) then
(
/**
This code is executed when it is a new entry. Thus the post calc must be performed.
**/
l_post_calc = 'Y'
)
else
(
if(prorate_end<>ENTRY_END_DATE) then
(
if(prorate_end = PAY_PROC_PERIOD_END_DATE) then
)
/**
This code is executed when it is a normal last proration entry. Thus the post calc must be performed.
**/
l_post_calc = 'Y'
)
)
)
/**
The following code sets the right proration start and end dates*/
**/
if(prorate_start was defaulted) then
(
/**
Prorate start is defaulted when no proration occurs
**/
if(ENTRY_START_DATE=PAY_PROC_PERIOD_START_DATE) then
(
/**
Element entry start date is the same as the payroll period start date
**/
prorate_start=ENTRY_START_DATE
if(ENTRY_END_DATE=PAY_PROC_PERIOD_END_DATE) then
(
prorate_end=ENTRY_END_DATE
)
else
(
prorate_end=PAY_PROC_PERIOD_END_DATE
)
r)
else if(ENTRY_END_DATE=PAY_PROC_PERIOD_END_DATE) then
(
prorate_end=ENTRY_END_DATE
prorate_start=PAY_PROC_PERIOD_START_DATE
)
)
/*Do we have to do daily calc*/
if((prorate_start>PAY_PROC_PERIOD_START_DATE
and prorate_end<PAY_PROC_PEROD_END_DATE)
or
(prorate_start=PAY_PROC_PERIOD_START_DATE
and prorate_end<PAY_PROC_PERIOD_END_DATE
and prorate_start=ENTRY_START_DATE)) then
(
l_post_calc='D'
)
/*Now perform the calculations*/
if(l_post_calc='N') then
(
/*Perform in the pre mode, i.e.start of year to current date*/
l_days=days_between(prorate_end,
l_tax_year_start_date) + 1
l_amount1=l_amount * 1_days/365
l_months=TRUNC(months_between(PAY_PROC_PERIOD_START_DATE,
l_tax_year_start_date
))
l_amount2=1_amount * l_months/12
result1=1_amount1-l_amount2
)
else
)
if(l_postcalc+'Y') then
)
/*perform in post mode, i.e.current date to end of year*/
l_days=days_between(l_tax_year_end_date,
prorate_start) + 1
l_amount1=l_amount*l_days/365
l_months=TRUNC(months_between(l_tax_year_end_date,
PAY_PROC_PERIOD_END_DATE
l_amount2=l_amount*l_months/12
result1=l_amount1-l_amount2
)
else
(
/**
This code is executed when l_post_calc=D. Perform in daily mode.
**/
l_days=days_between(prorate_end,prorate-start) + 1
l_amount1=0
l_months=0
l_amount2=0
result1=l_amount*l_days/365
)
)
/**
The following code is just to convert all the non-character variables into the character variables, so that the values of the variables could be displayed in the messages available either in SOE form or messages.
**/
prorate_start_res=to_char(prorate_start,'DD-MON-YYYY')
prorate_end_res=to_char(prorate_end,'DD-MON-YYYY')
l_days_res=TO_CHAR(l_days)
l_amount1_res=TO_CHAR(l_amount1)
l_months_res=TO_CHAR(l_months)
l_amount2_res=TO_CHAR(l_amount2)
return result1,
l_post_calc,
l_days_res,
l_amount1_res,
l_months_res,
l_amount2_res,
prorate_start_res,
prorate_end_res
/***********************************************************
Formula Name = UK_PRORATION_SPINAL_POINT
Formula Type = Oracle Payroll
Description: This formula is executed from within the payroll run by processing the element UK Salary Spinal Point. It calculates the rate of pay and returns this value. It uses the version of the function RATES_HISTORY which requires a date input to be passed. This formula is just a prototype.
DISCLAIMER: Oracle may change or upgrade this fast formula in the future releases. This FastFormula is strictly for example or prototype purposes. This FastFormula is not intended to provide a ready-made solution to the users. This formula may contain certain hard-coded values added to simplify the concept of usage of FastFormulas in proration. Users should make a copy of this formula and not change this formula. Users need to write their own new formula(s) to meet requirements.
************************************************************/
DEFAULT FOR date_worked IS '01-JAN-1950'(date)
DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '01-JAN-1950'(date)
DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '01-JAN-1950'(date)
DEFAULT FOR prorate_start IS '01-JAN-1950'(date)
DEFAULT FOR prorate_end IS '01-JAN-1950'(date)
inputs are prorate_start(date),
prorate_end(date)
/**
Prorate_start and prorate_end dates are passed from the payroll engine. These dates basically represent the dates on which the changes occur in the pay scale/grade rate during the pay period.
**/
amount=0
message=''
IF(prorate_start WAS DEFAULTED)then
(
/**
prorate_start date is defaulted when no proration occurs
**/
l_date_worked=PAY_PROC_PERIOD_END_DATE
prorate_start=PAY_PROC_PERIOD_START_DATE
prorate_end=PAY_PROC_PERIOD_END_DATE
)
else
)
l_date_worked=prorate_end
)
message1='Date defaulted to'||TO_CHAR(date_worked,'DD-MON-YYYY')
/**
The following function returns the value of the pay scale on the l_date_worked which is last date of hte payroll period if no proration occurs, otherwise it is the proration end date.
**/
if rates_history(l_date_worked,
'UK Spinal Point,
'E',
'P',
amount,
message)= -1 then
(
return message, message1
)
else
(
l_days = days_between(prorate_end,prorate_start)+ 1
/**
l_days stores the number of days between prorate start and prorate end dates
**/
message1 = 'The value is'||TO_CHAR(ROUND(amount,2))||for='||TO_CHAR(l_days||'days from '||TO_CHAR(prorate_start,'DD-MON-YYYY')||'to'||TO_CHAR(prorate_end,'DD-MON-YYYY')
/**
The message is just for informational purposes.
**/
l_amount=amount*(12*1_days/365)
/**
The above calculation is hard-coded to simplify the calculation. Please write your own calculation logic. The assumption was that the value in the pay scale is monthly. Therefore to get annual amount it is multiplied by 12. Then it is divided by 365 days to get the amount for a single day. Once it is multiplied by l_days, we get the amount for the days we want.
**/
/**
It is another assumption that a year contains 365 days. Please change this assumption to take account of the leap year. This formula will not work properly in a leap year.
**/
return l_amount, message1
)
/****************************************************************
Formula Name = UK_PRORATION_GRADE_RATE
Formula Type = Oracle Payroll
Description: This formula is executed from within the payroll run by processing the element UK Salary Grade Rate. It calculates the rate of pay and returns this value. It uses the version of the function RATES_HISTORY which requires a date input to be passed. This formula is just a prototype.
DISCLAIMER: Oracle may change or upgrade this FastFormula in the future releases. This FastFormula is strictly for example or prototype purposes only. This FastFormula is not intended to provide a ready-made solution to the users. This formula may contain certain hard-coded values added to simplify the concept of usage of FastFormulas in proration. Users should make a copy of this formula and not change this formula. Users need to write their own new formula(s) to meet requirements.
****************************************************************/
DEFAULT FOR date_worked IS '01-JAN-1950'(date)
DEFAULT FOR PAY_PROC_PERIOD-END_DATE IS '01-JAN-1950'(date)
DEFAULT FOR PAY_PROC_PERIOD-START-DATE IS '01-JAN-1950'(date)
DEFAULT FOR prorate_start IS '01-JAN-1950'(date)
DEFAULT FOR prorate_end IS '01-JAN-1950'(date)
inputs are prorate_start(date),
prorate_end(date)
/**
Prorate_start and prorate_end dates are passed from the payroll engine. These dates basically represent the dates on which the changes occur in the pay scale/grade rate during the pay period.
**/
amount = 0
message = ''
IF(prorate_start WAS DEFAULTED)then
(
/**
prorate_start date is defaulted when no proration occurs
**/
l_date_worked=PAY_PROC_PERIOD_END_DATE
prorate_start=PAY_PROC_PERIOD_START_DATE
prorate_end=PAY_PROC_PERIOD_END_DATE
)
else
(
l_date_worked=prorate_end
)
message1='Date defaulted to'||TO_CHAR(date_worked,'DD-MON-YYYY')
/**
The following function returns the value of the pay scale on the l_date_worked which is last date of the payroll period if no proration occurs, therwise it is the proration end date.
**/
if rates_history(l_date_worked,
'UK Grade Rate',
'E',
'H',
amount,
message) = -1 then
(
return message, message1
)
else
(
l_days = days_between(prorate_end,prorate_start) + 1
/**
l_days stores the number f days between prorate start and prorate end dates
**/
message1 = 'The value is'||TO_CHAR(ROUND(amount,2))||for='||TO_CHAR(1_days||'days from '||TO_CHAR(prorate_start,'DD-MON-YYYY')||'to'||TO_CHAR(prorate_end,'DD-MON-YYYY')
/**
The message is just for informational purposes.
**/
l_amount=amount * ((l_days*1600)/365)
/**
The above calculation is hard-coded to simplify the calcuation. Please write your own calculatioin logic. The assumption was that the value in the grade rate is hourly. Therefore to get the annual amount it is multiplied by 16000 hours (assuming that there are 1600 hours in a year). Then it is divided by 365 days to get the amount for a single day. Once it is multiplied by l_days, we get the amount for the days we want.
**/
/**
It is another assumption that a year contains 365 days. Please change this asusmption to take account of leap year. This formula will not work properly in a leap year.
**/
return l-amount,message1
)
/*****************************************************************
Formula name: UK_PRORATION_DEDUCTIONS
Purpose: Formula prototype to calculate prorated deductions.
DISCLAIMER: Oracle may change or upgrade this FastFormula in future releases. This FastFormula is strictly for example or prototype purposes only. This FastFormula is ot intended to provide a ready-made solution for the user. This formula may contain certain hard-coded values added to simplify the concept of usage of FastFormulas in proration. Users should make a copy of this formula and not change this formula. Users need to write their own new formula(s) to meet requirements.
*****************************************************************/
default for prorate_start is '01-JAN-1900'(date)
default for prorate_end is '01-JAN-1900'(date)
default for amount is 0.0
inputs are
prorate_start(date),
prorate_end(date),
annual_deduction(number)
/**
Prorate-start and prorate_end dates are passed from the payroll engine. These dates basically represent the dates on which the changes occur in the deduction amount during the pay perod. annual_deduction is an input value created in the element to which this formula will be tied. Here the assumption is that the user enters the annual deduction amount in the input value.
**/
l_amount=annual_deduction
message='Proration Start Date'||TO_CHAR(prorate_start, 'DD-MON-YYYY')
IF(prorate_start was defaulted)then
(
/**
prorate_start date is defaulted when no proration occurs. Therefore we should just return the annual deduction amount divided by 12.
**/
l_amount=l_amount/12
return l_amount
)
else
(
l_days=days_between(prorate_end, prorate_start) + 1
/**
l_days stores the number of days between prorate start and prorate end dates
**/
l_days_in_fiscal_year=365
/**
It is another assumption that a year contains 365 days. Please change this assumption to take care of leap year. This formula will not work properly in a leap year.
**/
l_amount=(l_amount*l_days)/l_days_in_fiscal_year
/**
In the above calculation, since l_amount contains the annual deduction amount, it is divided by 365 days of the year to get the deduction amount per day. This amount is then multiplied by the number of days in question to get the proper deduction amount
**/
return l_amount, message
)