[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ff.formula_id
from ff_formulas_f ff
,ff_formula_types ft
where ff.formula_name = cp_formula_name
and ff.formula_type_id = ft.formula_type_id
and ft.formula_type_name = 'Oracle Payroll'
and ff.business_group_id IS NULL
and ff.legislation_code = 'US';
25-Aug-2008 sudedas Updated Formula Text to enable Core Proration
Functionality. Related Bugs 3556204, 5895804
And ER 3855241.
--
INPUTS: Monthly_Salary
--
DBI Required: ASG_SALARY_BASIS
TERMINATED_EMPLOYEE
FINAL_PAY_PROCESSED
PAYROLL_TERMINATION_TYPE
BG_TERMINATION_TYPE
SCL_ASG_US_WORK_SCHEDULE
ASG_HOURS
SCL_ASG_US_TIMECARD_REQUIRED
LABOR_RECORDING_COUNT
PAY_PERIOD_TYPE
--
******************************************************************
DESCRIPTION:
******************************************************************
Computes earnings per pay period for salaried employees.
Proration function must be available to determine if employee worked entire
period - earnings will be adjusted accordingly by proration fn to account
for new hire, termination, leave of absence, etc.
*** Handling Salaried Earnings ***
Monthly earnings for salaried employees are processed every pay-period.
Salaried employees may or may not be required to submit a
timecard for days worked. If timecard is not required,
then the normal hours worked used for calculating employee pay are
derived from the Work Schedule they are ASGigned to or by the Standard
Hours entered on the ASGignment.
The Monthly Salary input value will be boiled down to an hourly rate in
order to utilize the pro-ration capabilities of the system(Issue 1).
The formula will make use of the pro-ration function for handling
adjustment of employee pay in case of mid-period events such as
termination, leave of absence, change of salary, etc. So an employee
is only paid for the days actually worked in a period with no
calculations or adjustments required by the user prior to or after
the payroll run (for a detailed discussion of Work Schedules and
Pro-Ration, see the Work Schedules - High Level Design).
When a timecard is submitted for an employee NOT REQUIRED to submit one,
then the "timecard" element entry (or entries) is treated as an override to
normal salary processing for the employee - IF the timecard entry has
a Rate or Rate Code entered(Issue 2). When a timecard is required
for a salaried employee and one is not submitted by the payroll cutoff
date, then the employee'' s pay will not be processed - and will have to wait
for a subsequent run.
NOTE: On a timecard submitted for a Salaried-Timecard Required employee,
a Rate (or Rate Code and Table) can be entered - ELSE the Monthly Salary input
value will be converted to an hourly rate using Convert_Figure function.
If this is the Final Pay run for the employee'' s ASGignment, then the
Regular Salary element will be discontinued after this run.
******************************************************************
ALGORITHM:
******************************************************************
If timecard required and time entries NOT FOUND, then
message= ''No timecards entered for Salaried, Timecard Required employee.''
return message
-- NOTE: If tc was req''d and time entries WERE found - then the skip rule
-- for this Regular Salary element would have skipped this processing.
Endif
Convert monthly salary to an hourly rate;
business groups will be updated with Actual Termination
Date for the termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
The skip rule is changed so that for the New payroll or
business group created since July FP/2002, seeded skip
rule will skip element processing using LSP date and
the existing payroll/business group, it will continue
to use Actual Termination date.
*/
MESG = '' ''
/* Why is this check not performed in skip formula? */
IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
(MESG = ''No timecards entered for Salaried, Timecard Required employee''
soe_run = REGULAR_SALARY_ASG_GRE_RUN
soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
RETURN MESG
)
IF REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN = 0 THEN
(
actual_hours_worked = 0
t_vac_hours_taken = 0
t_vac_pay = 0
t_sick_hours_taken = 0
t_sick_pay = 0
IF ASG_SALARY_BASIS WAS DEFAULTED THEN
MESG = ''Pay Basis MUST be entered for Regular Salary calculation.''
/* Start of Proration Logic */
IF PRORATE_START was defaulted THEN
PRORATE_START = PAY_PROC_PERIOD_START_DATE
IF PRORATE_END was defaulted THEN
PRORATE_END = PAY_PROC_PERIOD_END_DATE
/* Initializing Local variables */
t_schedule_source = '' ''
t_schedule = '' ''
t_return_status = -1
t_return_message = '' ''
hours_in_proration = 0
hours_in_period = 1
earnings_factor = 1
regular_salaried_earnings = 0
hours_in_proration = HOURS_BETWEEN(PRORATE_START
, PRORATE_END
, ''WORK''
,''N''
,''BUSY''
,''US''
,t_schedule_source
,t_schedule
,t_return_status
,t_return_message
,''H'')
hours_in_period = HOURS_BETWEEN( PAY_PROC_PERIOD_START_DATE
, PAY_PROC_PERIOD_END_DATE
, ''WORK''
,''N''
,''BUSY''
,''US''
,t_schedule_source
,t_schedule
,t_return_status
,t_return_message
,''H'')
earnings_factor = hours_in_proration/hours_in_period
regular_salaried_earnings = earnings_factor * ( ( Monthly_Salary * 12 ) / PAY_PERIODS_PER_FISCAL_YEAR )
actual_hours_worked = hours_in_proration
/* End Proration Logic */
/* For Sick and Vacation Pay */
hourly_rate = 0
hourly_rate = get_hourly_rate()
t_sick_pay = calc_sick_pay(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,hourly_rate
,t_sick_hours_taken)
t_vac_pay = calc_vac_pay(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,hourly_rate
,t_vac_hours_taken)
/* 330341 FIX GOES HERE */
/* 392177 added the and PAY_PERIOD_TYPE ... portion of if below */
IF (ASG_SALARY_BASIS_CODE = ''MONTHLY'' AND PAY_PERIOD_TYPE NOT LIKE ''%Week%'') THEN
/*IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings > ASG_SALARY THEN */
IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings - ASG_SALARY <= 0.01 AND (REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings) > ASG_SALARY THEN
regular_salaried_earnings = ASG_SALARY - REGULAR_SALARY_ASG_GRE_MONTH
/* 392177 END */
/* 330341 END */
)
ELSE
(regular_salaried_earnings = REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
clear_repl_amt = -1 * REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
/* WWBug 323639 */
actual_hours_worked = 0
t_vac_hours_taken = 0
t_vac_pay = 0
t_sick_hours_taken = 0
t_sick_pay = 0
)
regular_salaried_earnings = regular_salaried_earnings
+ REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
+ REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
/* Reduce Regular Changes Start Here */
RED_REG_ADJUST_AMOUNT = 0.05
RED_REG_ADJUST_HOURS = 0.01
t_reduce_regular_earnings = REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
t_reduce_regular_hours = REDUCE_REGULAR_HOURS_ASG_GRE_RUN
t_return = REDUCED_REGULAR_CALC(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,t_reduce_regular_earnings
,t_reduce_regular_hours)
reduce_regular_earnings = t_reduce_regular_earnings
reduce_regular_hours = t_reduce_regular_hours
diff_earnings = regular_salaried_earnings - reduce_regular_earnings + RED_REG_ADJUST_AMOUNT
diff_hours = actual_hours_worked - reduce_regular_hours + RED_REG_ADJUST_HOURS
/* Reduce Regular Changes Start Here */
IF Timecard_Required = ''N'' AND
reduce_regular_earnings <> 0 THEN
(
/*
* We need to carry over reduce regular Earnings/Hours to the next Pay
* Period if it is more than regular salaried earnings so that we
* never have regular salaried earnings less than ZERO
*/
IF diff_earnings >= 0 THEN
(
regular_salaried_earnings = regular_salaried_earnings - reduce_regular_earnings
if regular_salaried_earnings < 0 then
(
regular_salaried_earnings = 0
)
)
ELSE
(
/* reduce_regular_earnings = regular_salaried_earnings */
regular_salaried_earnings = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
IF diff_hours >= 0 THEN
(
actual_hours_worked = actual_hours_worked - reduce_regular_hours
if actual_hours_worked < 0 then
(
actual_hours_worked = 0
)
)
ELSE
(
/* reduce_regular_hours = actual_hours_worked */
actual_hours_worked = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
)
ELSE
(
reduce_regular_earnings = 0
reduce_regular_hours = 0
)
/* Reduce Regular Changes End Here */
/*
At the Business Group and Organization level, termination
rule is determined which all terminated employees will
follow for the payment. All existing payrolls and/or
business groups (prior to July Family Pack 2002)
will be updated with Actual Termination Date for the
termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
*/
IF regular_salaried_earnings < 0 THEN
(IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
OR
(PAYROLL_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
neg_earn = 0
ELSE
(neg_earn = regular_salaried_earnings - REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
regular_salaried_earnings = 0
)
)
ELSE
(IF REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
neg_earn = -1 * REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
)
IF REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
clear_addl_amt = -1 * REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
IF t_vac_pay <> 0 THEN
(vac_pay = t_vac_pay
vac_hours_taken = t_vac_hours_taken
regular_salaried_earnings = regular_salaried_earnings - vac_pay
actual_hours_worked = actual_hours_worked - vac_hours_taken
)
IF t_sick_pay <> 0 THEN
(sick_pay = t_sick_pay
sick_hours_taken = t_sick_hours_taken
regular_salaried_earnings = regular_salaried_earnings - sick_pay
actual_hours_worked = actual_hours_worked - sick_hours_taken
)
/* Create latest balances */
/* There is no RUN level leatest balances
and REGULAR_SALARY_ASG_GRE_YTD is added to to latest balance script.
soe_run = REGULAR_SALARY_ASG_GRE_RUN
soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
*/
IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'' ) OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
(STOP_ENTRY = ''Y''
mesg = ''Regular Salary being stopped after Final Pay.''
RETURN regular_salaried_earnings, actual_hours_worked,
clear_repl_amt, clear_addl_amt, neg_earn, vac_pay,
vac_hours_taken, sick_pay, sick_hours_taken,
STOP_ENTRY, mesg, reduce_regular_earnings,
reduce_regular_hours
)
ELSE
RETURN regular_salaried_earnings, actual_hours_worked,
clear_repl_amt, clear_addl_amt, neg_earn,
vac_pay, vac_hours_taken, sick_pay, sick_hours_taken,
reduce_regular_earnings, reduce_regular_hours, mesg
';
business groups will be updated with Actual Termination
Date for the termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
The skip rule is changed so that for the New payroll or
business group created since July FP/2002, seeded skip
rule will skip element processing using LSP date and
the existing payroll/business group, it will continue
to use Actual Termination date.
*/
/* Changed for new Termination Rule
There will be no stop entry using USER_ENTERED_TIME
as of July FP/2002
MESG = '' ''
IF TERMINATED_EMPLOYEE = ''Y'' AND USER_ENTERED_TIME = ''Y'' THEN
(STOP_ENTRY = ''Y''
mesg = ''Regular Salary being stopped after Final Pay.''
soe_run = REGULAR_SALARY_ASG_GRE_RUN
soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
RETURN STOP_ENTRY, mesg
)
*/
/* Why is this check not performed in skip formula? */
IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
(mesg = ''No timecards entered for Salaried, Timecard Required employee''
soe_run = REGULAR_SALARY_ASG_GRE_RUN
soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
RETURN mesg
)
IF REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN = 0 THEN
(hourly_rate = get_hourly_rate()
/*Convert_Period_Type( Work_Schedule,
ASG_HOURS,
Monthly_Salary,
ASG_SALARY_BASIS,
''HOURLY'',
PAY_EARNED_START_DATE,
PAY_EARNED_END_DATE,
ASG_FREQ)*/
/* WWBug 323639 */
actual_hours_worked = 0
t_vac_hours_taken = 0
t_vac_pay = 0
t_sick_hours_taken = 0
t_sick_pay = 0
IF ASG_SALARY_BASIS WAS DEFAULTED THEN
mesg = ''Pay Basis MUST be entered for Regular Salary calculation.''
regular_salaried_earnings = Calc_Period_Earnings (
ASG_SALARY_BASIS,
''MONTHLY SALARY'',
hourly_rate,
PAY_EARNED_START_DATE,
PAY_EARNED_END_DATE,
actual_hours_worked,
t_vac_hours_taken,
t_vac_pay,
t_sick_hours_taken,
t_sick_pay,
''Y'',
ASG_FREQ)
/*Calculate_Period_Earnings(
ASG_SALARY_BASIS,
''MONTHLY SALARY'',
hourly_rate,
PAY_EARNED_START_DATE,
PAY_EARNED_END_DATE,
Work_Schedule,
ASG_HOURS,
actual_hours_worked,
t_vac_hours_taken,
t_vac_pay,
t_sick_hours_taken,
t_sick_pay,
''Y'',
ASG_FREQ) */
/* WWBug 323639 */
/* 330341 FIX GOES HERE */
/* 392177 added the and PAY_PERIOD_TYPE ... portion of if below */
IF (ASG_SALARY_BASIS_CODE = ''MONTHLY'' AND PAY_PERIOD_TYPE NOT LIKE ''%Week%'') THEN
/*IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings > ASG_SALARY THEN */
IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings - ASG_SALARY <= 0.01 AND (REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings) > ASG_SALARY THEN
regular_salaried_earnings = ASG_SALARY - REGULAR_SALARY_ASG_GRE_MONTH
/* 392177 END */
/* 330341 END */
)
ELSE
(regular_salaried_earnings = REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
clear_repl_amt = -1 * REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
/* WWBug 323639 */
actual_hours_worked = 0
t_vac_hours_taken = 0
t_vac_pay = 0
t_sick_hours_taken = 0
t_sick_pay = 0
)
regular_salaried_earnings = regular_salaried_earnings
+ REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
+ REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
/* Reduce Regular Changes Start Here */
RED_REG_ADJUST_AMOUNT = 0.05
RED_REG_ADJUST_HOURS = 0.01
reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
diff_earnings = regular_salaried_earnings - reduce_regular_earnings
+ RED_REG_ADJUST_AMOUNT
diff_hours = actual_hours_worked - reduce_regular_hours
+ RED_REG_ADJUST_HOURS
/* Reduce Regular Changes Start Here */
IF Timecard_Required = ''N'' AND
reduce_regular_earnings <> 0 THEN
(
/*
* We need to carry over reduce regular Earnings/Hours to the next Pay
* Period if it is more than regular salaried earnings so that we
* never have regular salaried earnings less than ZERO
*/
IF diff_earnings >= 0 THEN
(
regular_salaried_earnings = regular_salaried_earnings - reduce_regular_earnings
if regular_salaried_earnings < 0 then
(
regular_salaried_earnings = 0
)
)
ELSE
(
/* reduce_regular_earnings = regular_salaried_earnings */
regular_salaried_earnings = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
IF diff_hours >= 0 THEN
(
actual_hours_worked = actual_hours_worked - reduce_regular_hours
if actual_hours_worked < 0 then
(
actual_hours_worked = 0
)
)
ELSE
(
/* reduce_regular_hours = actual_hours_worked */
actual_hours_worked = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
)
ELSE
(
reduce_regular_earnings = 0
reduce_regular_hours = 0
)
/* Reduce Regular Changes End Here */
/*
At the Business Group and Organization level, termination
rule is determined which all terminated employees will
follow for the payment. All existing payrolls and/or
business groups (prior to July Family Pack 2002)
will be updated with Actual Termination Date for the
termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
*/
IF regular_salaried_earnings < 0 THEN
(IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
OR
(PAYROLL_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
THEN
neg_earn = 0
ELSE
(neg_earn = regular_salaried_earnings
- REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
regular_salaried_earnings = 0
)
)
ELSE
(IF REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
neg_earn = -1 * REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
)
IF REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
clear_addl_amt = -1 * REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
IF t_vac_pay <> 0 THEN
(vac_pay = t_vac_pay
vac_hours_taken = t_vac_hours_taken
regular_salaried_earnings = regular_salaried_earnings - vac_pay
actual_hours_worked = actual_hours_worked - vac_hours_taken
)
IF t_sick_pay <> 0 THEN
(sick_pay = t_sick_pay
sick_hours_taken = t_sick_hours_taken
regular_salaried_earnings = regular_salaried_earnings - sick_pay
actual_hours_worked = actual_hours_worked - sick_hours_taken
)
/* Create latest balances */
/* There is no RUN level leatest balances
and REGULAR_SALARY_ASG_GRE_YTD is added to to latest balance script.
soe_run = REGULAR_SALARY_ASG_GRE_RUN
soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
*/
IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'' ) OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
(STOP_ENTRY = ''Y''
mesg = ''Regular Salary being stopped after Final Pay.''
RETURN regular_salaried_earnings, actual_hours_worked,
clear_repl_amt, clear_addl_amt, neg_earn, vac_pay,
vac_hours_taken, sick_pay, sick_hours_taken,
STOP_ENTRY, mesg, reduce_regular_earnings,
reduce_regular_hours
)
ELSE
RETURN regular_salaried_earnings, actual_hours_worked,
clear_repl_amt, clear_addl_amt, neg_earn,
vac_pay, vac_hours_taken, sick_pay, sick_hours_taken,
reduce_regular_earnings, reduce_regular_hours, mesg
';
25-Aug-2008 sudedas Updated Formula Text to enable Core Proration
Functionality. Related Bugs 3556204, 5895804
And ER 3855241.
--
--
INPUTS: Rate
Rate Code (text)
--
DBI Required: ASG_SALARY_BASIS
TERMINATED_EMPLOYEE
FINAL_PAY_PROCESSED
PAYROLL_TERMINATION_TYPE
BG_TERMINATION_TYPE
LABOR_RECORDING_COUNT
SCL_ASG_US_WORK_SCHEDULE
ASG_HOURS
SCL_ASG_US_TIMECARD_REQUIRED
CURRENT_ELEMENT_TYPE_ID
******************************************************************
DESCRIPTION:
******************************************************************
Computes earnings per pay period for hourly employees.
Proration function must be available to determine if employee worked entire
period - earnings will be adjusted accordingly by proration fn to account
for new hire, termination, leave of absence, etc.
*** Hourly handling ***
Regular wages earned per pay period for employees paid by the hour.
Hourly employees can either be "Hourly-Automatic" (ie. timecard not
required) or "Hourly-Timecard" where a timecard is required for pay. The
hourly rate for an employee is entered as the input value for this element.
This rate is used with the number of hours worked to calculate earnings.
Hours worked will be indicated by one of the following:
- time entry or entries (ie. timecard)
- ASGigned Work Schedule
- standard hours entered at the Organization and ASGignment levels.
For an Hourly-Timecard or "timecard required" employee, when a timecard
is not submitted by the payroll input cutoff date - the wages for that
employee will not be calculated and will have to wait for a subsequent
payroll run for processing. When a timecard is submitted for an Hourly-
Automatic employee, then the time entry (or entries) is treated as the source
for Hours - if a rate is entered on the time entry, then this rate is used
along with the hours to compute pay, otherwise the normal rate (Regular
Wages rate) is used for computation.
If this is the Final Pay run for the employee''s ASGignment, then the
Regular Wages element will be discontinued after this run.
******************************************************************
ALGORITHM:
******************************************************************
If timecard required and time entries NOT FOUND, then
message=''No timecards entered for Hourly, Timecard Required employee.''
return message
-- NOTE: If tc was req''d and time entries WERE found - then the skip rule
-- for this Regular Salary element would have skipped this processing.
Endif
Call proration function with hourly rate; --> Regular_Wage_Earnings
will be updated with Actual Termination Date for the
termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
*/
t_sick_pay = calc_sick_pay(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,hourly_rate
,t_sick_hours_taken)
t_vac_pay = calc_vac_pay(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,hourly_rate
,t_vac_hours_taken)
RED_REG_ADJUST_AMOUNT = 0.05
RED_REG_ADJUST_HOURS = 0.01
/* Replacing with below code for Enabling Proration */
/*
reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
diff_earnings = regular_wage_earnings - reduce_regular_earnings
+ RED_REG_ADJUST_AMOUNT
diff_hours = actual_hours_worked - reduce_regular_hours
+ RED_REG_ADJUST_HOURS
*/
t_reduce_regular_earnings = REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
t_reduce_regular_hours = REDUCE_REGULAR_HOURS_ASG_GRE_RUN
t_return = REDUCED_REGULAR_CALC(PAY_PROC_PERIOD_END_DATE
,PRORATE_START
,PRORATE_END
,t_reduce_regular_earnings
,t_reduce_regular_hours)
reduce_regular_earnings = t_reduce_regular_earnings
reduce_regular_hours = t_reduce_regular_hours
diff_earnings = regular_wage_earnings - reduce_regular_earnings
+ RED_REG_ADJUST_AMOUNT
diff_hours = actual_hours_worked - reduce_regular_hours
+ RED_REG_ADJUST_HOURS
/* Reduce Regular Changes Start Here */
IF Timecard_Required = ''N'' AND
reduce_regular_earnings <> 0 THEN
(
/*
* We need to carry over reduce regular Earnings/Hours to the next Pay
* Period if it is more than regular salaried earnings so that we
* never have regular salaried earnings less than ZERO
*/
IF diff_earnings >= 0 THEN
(
regular_wage_earnings = regular_wage_earnings - reduce_regular_earnings
if regular_wage_earnings < 0 then
(
regular_wage_earnings = 0
)
)
ELSE
(
/* reduce_regular_earnings = regular_wage_earnings */
regular_wage_earnings = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
IF diff_hours >= 0 THEN
(
actual_hours_worked = actual_hours_worked - reduce_regular_hours
if actual_hours_worked < 0 then
(
actual_hours_worked = 0
)
)
ELSE
(
/* reduce_regular_hours = actual_hours_worked */
actual_hours_worked = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
)
ELSE
(
reduce_regular_earnings = 0
reduce_regular_hours = 0
)
/* Reduce Regular Changes End Here */
IF regular_wage_earnings < 0 THEN
(IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
neg_earn = 0
ELSE
(neg_earn = regular_wage_earnings
regular_wage_earnings = 0
)
)
ELSE
(IF REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
neg_earn = -1 * REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
)
IF REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
clear_addl_amt = -1 * REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
IF t_vac_pay <> 0 THEN
(vac_pay = t_vac_pay
vac_hours_taken = t_vac_hours_taken
regular_wage_earnings = regular_wage_earnings - vac_pay
actual_hours_worked = actual_hours_worked - vac_hours_taken
)
IF t_sick_pay <> 0 THEN
(sick_pay = t_sick_pay
sick_hours_taken = t_sick_hours_taken
regular_wage_earnings = regular_wage_earnings - sick_pay
actual_hours_worked = actual_hours_worked - sick_hours_taken
)
/* Create latest balances */
soe_run = REGULAR_WAGES_ASG_GRE_RUN
soe_ytd = REGULAR_WAGES_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
HOURS_PASSED = actual_hours_worked
IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
( STOP_ENTRY = ''Y''
if RATE_PASSED = 0 then
( RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, STOP_ENTRY, reduce_regular_earnings,
reduce_regular_hours,mesg
)
else
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, STOP_ENTRY,ELEMENT_TYPE_ID_PASSED,
RATE_PASSED,HOURS_PASSED,reduce_regular_earnings,reduce_regular_hours,
mesg
)
)
ELSE
(
if RATE_PASSED = 0 then
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, reduce_regular_earnings, reduce_regular_hours,
mesg
)
else
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken,ELEMENT_TYPE_ID_PASSED,RATE_PASSED,HOURS_PASSED,
reduce_regular_earnings, reduce_regular_hours, mesg
)
)';
will be updated with Actual Termination Date for the
termination rule and for new Business Group
or payroll, user can decide whether to use Actual Term date
or Last Standard Process Date. When no term rule is
specified, it defaults to Last Standard Process date.
*/
RED_REG_ADJUST_AMOUNT = 0.05
RED_REG_ADJUST_HOURS = 0.01
reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
diff_earnings = regular_wage_earnings - reduce_regular_earnings
+ RED_REG_ADJUST_AMOUNT
diff_hours = actual_hours_worked - reduce_regular_hours
+ RED_REG_ADJUST_HOURS
/* Reduce Regular Changes Start Here */
IF Timecard_Required = ''N'' AND
reduce_regular_earnings <> 0 THEN
(
/*
* We need to carry over reduce regular Earnings/Hours to the next Pay
* Period if it is more than regular salaried earnings so that we
* never have regular salaried earnings less than ZERO
*/
IF diff_earnings >= 0 THEN
(
regular_wage_earnings = regular_wage_earnings - reduce_regular_earnings
if regular_wage_earnings < 0 then
(
regular_wage_earnings = 0
)
)
ELSE
(
/* reduce_regular_earnings = regular_wage_earnings */
regular_wage_earnings = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
IF diff_hours >= 0 THEN
(
actual_hours_worked = actual_hours_worked - reduce_regular_hours
if actual_hours_worked < 0 then
(
actual_hours_worked = 0
)
)
ELSE
(
/* reduce_regular_hours = actual_hours_worked */
actual_hours_worked = 0
mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
)
)
ELSE
(
reduce_regular_earnings = 0
reduce_regular_hours = 0
)
/* Reduce Regular Changes End Here */
IF regular_wage_earnings < 0 THEN
(IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''L'' AND
TERMINATED_EMPLOYEE = ''Y'' AND
LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
neg_earn = 0
ELSE
(neg_earn = regular_wage_earnings
regular_wage_earnings = 0
)
)
ELSE
(IF REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
neg_earn = -1 * REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
)
IF REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
clear_addl_amt = -1 * REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
IF t_vac_pay <> 0 THEN
(vac_pay = t_vac_pay
vac_hours_taken = t_vac_hours_taken
regular_wage_earnings = regular_wage_earnings - vac_pay
actual_hours_worked = actual_hours_worked - vac_hours_taken
)
IF t_sick_pay <> 0 THEN
(sick_pay = t_sick_pay
sick_hours_taken = t_sick_hours_taken
regular_wage_earnings = regular_wage_earnings - sick_pay
actual_hours_worked = actual_hours_worked - sick_hours_taken
)
/* Create latest balances */
soe_run = REGULAR_WAGES_ASG_GRE_RUN
soe_ytd = REGULAR_WAGES_ASG_GRE_YTD
soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
HOURS_PASSED = actual_hours_worked
IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
BG_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
(PAYROLL_TERMINATION_TYPE = ''A'' AND
TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
( STOP_ENTRY = ''Y''
if RATE_PASSED = 0 then
( RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, STOP_ENTRY, reduce_regular_earnings,
reduce_regular_hours,mesg
)
else
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, STOP_ENTRY,ELEMENT_TYPE_ID_PASSED,
RATE_PASSED,HOURS_PASSED,reduce_regular_earnings,reduce_regular_hours,
mesg
)
)
ELSE
(
if RATE_PASSED = 0 then
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken, reduce_regular_earnings, reduce_regular_hours,
mesg
)
else
(RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
sick_hours_taken,ELEMENT_TYPE_ID_PASSED,RATE_PASSED,HOURS_PASSED,
reduce_regular_earnings, reduce_regular_hours, mesg
)
)';
update ff_formulas_f
set formula_text = l_new_formula_text
where formula_name = l_formula_name
and business_group_id IS NULL
and legislation_code = 'US';
hr_utility.trace('Formula Updated.');
hr_utility.trace('Formula Update Commited!');
update ff_formulas_f
set formula_text = l_old_formula_text
where formula_name = l_formula_name
and business_group_id IS NULL
and legislation_code = 'US';
hr_utility.trace('Formula Updated.');
hr_utility.trace('Formula Update Commited!');
delete
from ff_compiled_info_f
where formula_id = l_formula_id;
delete
from ff_fdi_usages_f
where formula_id = l_formula_id;
SELECT dated_table_id
FROM pay_dated_tables pdt
WHERE pdt.application_id = p_application_id
AND pdt.table_name = p_table_name
AND pdt.legislation_code IS NULL
AND pdt.business_group_id IS NULL;
col_name_tbl.delete;
col_name_tbl.delete;
col_name_tbl.delete;
Name : delete_proration_group
Description : This Function deletes the Proration Group called
'Proration Group for Regular Salary'.
*****************************************************************************/
PROCEDURE delete_proration_group IS
CURSOR get_proration_event_grp(cp_event_grp_nm IN VARCHAR2) IS
SELECT event_group_id
,object_version_number
FROM pay_event_groups peg
WHERE peg.event_group_name = cp_event_grp_nm
AND peg.event_group_type = 'P'
AND peg.proration_type = 'P'
AND peg.legislation_code = 'US'
AND peg.business_group_id IS NULL;
hr_utility.trace('Entered into pay_us_rsrw_upgrev.delete_proration_group');
delete from pay_datetracked_events
where event_group_id = ln_event_grp_id;
pay_event_groups_api.delete_event_group(p_event_group_id => ln_event_grp_id
,p_object_version_number => ln_obj_ver_num);
END delete_proration_group;
SELECT element_type_id
,element_name
,proration_group_id
FROM pay_element_types_f
WHERE element_name = cp_ele_name
AND legislation_code = 'US'
AND business_group_id IS NULL
AND element_information_category = 'US_EARNINGS'
AND element_information1 = 'REG';
select pus.status
from pay_upgrade_definitions pud
,pay_upgrade_status pus
where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
and pud.legislation_code = 'US'
and pud.upgrade_definition_id = pus.upgrade_definition_id
and pus.legislation_code = 'US';
SELECT event_group_id
FROM pay_event_groups peg
WHERE peg.event_group_name = cp_event_grp_nm
AND peg.event_group_type = 'P'
AND peg.proration_type = 'P'
AND peg.legislation_code = 'US'
AND peg.business_group_id IS NULL;
delete_proration_group();
/* Update Proration Group */
BEGIN
ln_dbg_step := 1;
update pay_element_types_f
set proration_group_id = ln_proration_event_grp_id
where element_name in ('Regular Salary', 'Regular Wages')
AND business_group_id IS NULL
AND legislation_code = 'US'
AND element_information_category = 'US_EARNINGS'
AND element_information1 = 'REG';
/* Insert records into pay_upgrade_definitions and pay_upgrade_status */
ln_dbg_step := 2;
insert into pay_upgrade_definitions(UPGRADE_DEFINITION_ID
,SHORT_NAME
,NAME
,LEGISLATION_CODE
,DESCRIPTION
,UPGRADE_LEVEL
,CRITICALITY
,THREADING_LEVEL
,FAILURE_POINT
,LEGISLATIVELY_ENABLED
,UPGRADE_METHOD
,UPGRADE_PROCEDURE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,ADDITIONAL_INFO)
select PAY_UPGRADE_DEFINITIONS_S.nextval
,'US_REG_EARNINGS_UPGRADE'
,'Upgrade Regular Earnings Elements for all US Business Groups'
,'US'
,'Upgrade Regular Earnings Elements for all US Business Groups'
,'L'
,'R'
,'PET'
,'N'
,'N'
,'PYUGEN'
,'pay_us_rsrw_upgrev.upgrade_reg_salarywages'
,sysdate
,1
,-1
,1
,sysdate
,'Run through separate conc program' from sys.dual;
insert into pay_upgrade_status(UPGRADE_DEFINITION_ID
,STATUS
,LEGISLATION_CODE)
select PAY_UPGRADE_DEFINITIONS_S.currval
,'C'
,'US' from sys.dual;
hr_utility.trace('Insertion Commited!');
/* Update Proration Group */
BEGIN
ln_dbg_step := 4;
update pay_element_types_f
set proration_group_id = ln_proration_event_grp_id
where element_name in ('Regular Salary', 'Regular Wages')
AND business_group_id IS NULL
AND legislation_code = 'US'
AND element_information_category = 'US_EARNINGS'
AND element_information1 = 'REG';
hr_utility.trace('Update of proration group Commited!');
SELECT element_type_id
,element_name
,proration_group_id
FROM pay_element_types_f
WHERE element_name = cp_ele_name
AND legislation_code = 'US'
AND business_group_id IS NULL
AND element_information_category = 'US_EARNINGS'
AND element_information1 = 'REG';
select pus.status
,pud.upgrade_definition_id
from pay_upgrade_definitions pud
,pay_upgrade_status pus
where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
and pud.legislation_code = 'US'
and pud.upgrade_definition_id = pus.upgrade_definition_id
and pus.legislation_code = 'US';
/* Update Proration Group */
ln_dbg_step := 1;
update pay_element_types_f
set proration_group_id = NULL
where element_name in ('Regular Salary', 'Regular Wages')
and business_group_id is null
and legislation_code = 'US'
AND element_information_category = 'US_EARNINGS'
AND element_information1 = 'REG';
delete from pay_upgrade_status
where upgrade_definition_id = ln_upg_defn_id;
delete from pay_upgrade_definitions
where upgrade_definition_id = ln_upg_defn_id
and short_name like 'US_REG_EARNINGS_UPGRADE'
and legislation_code = 'US';
hr_utility.trace('Update/Delete Commited!');
select pus.status
,pud.upgrade_definition_id
from pay_upgrade_definitions pud
,pay_upgrade_status pus
where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
and pud.legislation_code = 'US'
and pud.upgrade_definition_id = pus.upgrade_definition_id
and pus.legislation_code = 'US';
SELECT petei.eei_information11
FROM pay_element_types_f pet
,pay_element_type_extra_info petei
where pet.element_type_id = cp_eletyp_ctx_id
and pet.element_type_id = petei.element_type_id
and petei.information_type = 'US_EARNINGS'
and petei.eei_information_category = 'US_EARNINGS';
select max(PTP.period_num)
from per_time_periods PTP
,pay_payrollS_f PRL
where PTP.payroll_id = ctx_payroll_id
and PTP.payroll_id = PRL.payroll_id
and PRL.business_group_id = ctx_bg_id
and to_char(period_end_date, 'YYYY') = to_char(PTP.start_date, 'YYYY');
select TPT.number_per_fiscal_year
from per_time_period_types TPT,
pay_payrolls_f PRL
WHERE TPT.period_type = PRL.period_type
AND PRL.business_group_id + 0 = ctx_bg_id
AND PRL.payroll_id = ctx_payroll_id;
SELECT past.per_system_status
FROM per_assignments_f paf
,per_assignment_status_types past
WHERE paf.assignment_id = p_ctx_asg_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND p_prorate_end_dt between paf.effective_start_date and paf.effective_end_date;