1 PACKAGE BODY pay_us_rsrw_upgrev AS
2 /* $Header: payusrsrwupg.pkb 120.6 2011/12/21 04:14:53 emunisek ship $ */
3 /*****************************************************************************
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_regsal_upgrade
21
22 Description : This package is called by a concurrent program.
23 In this package we upgrade Old Seeded Earnings
24 Elements Regular Salary and Regular Wages
25 to New Architecture (with Enabled Functionality
26 of Core Proration) for US Legislation.
27
28 NOTE : Customer needs to recompile Fast Formula
29 'REGULAR_SALARY', 'REGULAR_WAGES' after Upgradation.
30
31 Change List
32 -----------
33 Date Name Ver Bug No Description
34 ----------- -------- ------- --------- -------------------------------
35 11-Aug-2008 sudedas 115.0 5895804
36 3556204
37 ER 3855241 Created.
38 17-Sep-2008 sudedas 115.1 Corrected GSCC Errors.
39 20-Oct-2008 sudedas 115.2 Corrected chksql Errors.
40 27-Apr-2009 sudedas 115.3 8464127 Added functions get_upgrade_flag
41 and get_payprd_per_fiscal_yr.
42 Modified procedures to make the
43 changes permanent except for
44 proration group.
45 22-Jun-2009 sudedas 115.4 Added function to check status
46 type by get_assignment_status.
47 Added assignment_status_type_id
48 in Proration Event Group.
49 06-Jul-2009 sudedas 115.7 8637053 Added context element_type_id to
50 function get_payprd_per_fiscal_yr
51 13-Nov-2011 emunisek 115.8 13484606 Replaced FF_FORMULAS_F.FORMULA_TEXT%TYPE
52 for variables based on formula_text column.
53 *****************************************************************************/
54
55 /************************************************************
56 ** Local Package Variables
57 ************************************************************/
58
59 gv_package_name varchar2(50) := 'pay_us_regsal_upgrade';
60 gv_location number;
61
62 /*****************************************************************************
63 Name : modify_formula_text
64
65 Description : This function modifies the text of the 'REGULAR_SALARY'
66 and 'REGULAR_WAGES' Fast Formula.
67 *****************************************************************************/
68 PROCEDURE modify_formula_text(p_ele_name IN VARCHAR2
69 ,p_mode IN VARCHAR2 DEFAULT 'OLD') IS
70
71 -- Get Formula Text for modification
72 cursor c_formula_id(cp_formula_name varchar2) IS
73 select ff.formula_id
74 from ff_formulas_f ff
75 ,ff_formula_types ft
76 where ff.formula_name = cp_formula_name
77 and ff.formula_type_id = ft.formula_type_id
78 and ft.formula_type_name = 'Oracle Payroll'
79 and ff.business_group_id IS NULL
80 and ff.legislation_code = 'US';
81
82 l_formula_id number;
83 l_formula_name varchar2(100);
84 l_old_formula_text FF_FORMULAS_F.FORMULA_TEXT%TYPE;
85 l_new_formula_text FF_FORMULAS_F.FORMULA_TEXT%TYPE;
86
87 BEGIN
88 hr_utility.trace('Entering ' || gv_package_name || '.modify_formula_text');
89 hr_utility.trace('Passed Parameter p_ele_name := ' || p_ele_name);
90 hr_utility.trace('Passed Parameter p_mode := ' || p_mode);
91
92 -- Modification of Fast Formula Text of Regular_Salary
93
94 IF p_ele_name = 'Regular Salary' THEN
95 l_formula_name := 'REGULAR_SALARY';
96 hr_utility.trace('Modifying Formula ' || l_formula_name);
97
98 open c_formula_id(l_formula_name);
99 fetch c_formula_id into l_formula_id ;
100 close c_formula_id;
101
102 hr_utility.trace('l_formula_id := ' || l_formula_id);
103
104 l_old_formula_text := NULL;
105 l_new_formula_text := NULL;
106
107 l_new_formula_text := '
108 /* ***************************************************************
109 $Header: payusrsrwupg.pkb 120.6 2011/12/21 04:14:53 emunisek ship $
110 FORMULA NAME: REGULAR_SALARY
111 FORMULA TYPE: Payroll
112
113 Change History
114 29 Sep 1993 hparicha Created.
115 14 Oct 1993 hparicha Moved skip rule formulae to separate
116 file ".SKIP".
117 30 Nov 1993 hparicha G187
118 06 Dec 1993 jmychale G305. Renamed Convert_Figure to Convert_
119 Period_Type; reference TIME_ENTRY_WAGES
120 temporarily in search for timecards until
121 information hours element is defined
122 07 Jan 1994 jmychale G491. Removed actual hours worked
123 parameter from
124 Calculate_Period_Earnings()
125 13 Jan 1994 hparicha G497. Reverted calc period earnings
126 to us ACTUAL_HOURS_WORKED! Used by
127 Statement of Earnings report. Replace
128 TIME_ENTR_WAGES_COUNT with
129 LABOR_RECORDING_COUNT for timecard
130 req''d employees.
131 24 Feb 1994 hparicha G560. ASS -> ASG; TU -> GRE
132 24 Feb 1994 hparicha G581. Handles negative earnings.
133 09 Jun 1994 hparicha G907. New implementation of generated and
134 startup earnings and deductions using
135 "<ELE_NAME> Special Features" shadow element
136 to feed balances and handle Addl/Repl Amounts.
137 04 Jan 1995 hparicha Vacation/Sick correlation to Regular Pay.
138 New results need to be passed when vac/sick
139 pay are present.
140 04 May 1995 hparicha Defaulted values for PAY_PROC_PERIOD_START/
141 END_DATE. Default dates should be obvious
142 when default is used.
143 10 Jan 1996 hparicha 323639 Major cleanup effort involving
144 proration and other user defined functions
145 and formulae changes.
146 16 Apr 1996 ssdesai Latest balances creation.
147 25 Apr 1996 hparicha 344018. Replacing reference to
148 TIME_ENTRY_WAGES_COUNT with new dbi for
149 USER_ENTERED_TIME which looks for any time
150 entered for regular pay which should override
151 Regular Salary and Regular Wages...ie. not
152 just the seeded Time Entry Wages element is
153 able to override Regular.
154 11 Jun 1996 hparicha 330341. Ensures that the sum of salary in pay
155 periods adds up to monthly amount when pay
156 basis is monthly. We may want to add checks
157 for other pay basis types - ie.
158 annual pay basis, period pay basis.
159 3 SEP 1997 lwthomps 392177. Check if weekly or biweekly payroll
160 before caping salary to monthly salary basis.
161 17 Mar 1997 djeng 465454, modified PAY_PROC_PERIOD_END_DATE
162 and PAY_PROC_PERIOD_END_DATE to
163 PAY_EARNED_START_DATE / PAY_EARNED_END_DATE
164 13 AUG 1997 Lwhtomps BUG 525859. Payments dimension can not be held
165 as a latest balance.
166 21-JAN-1999 RAMURTHY BUG 803578 - no monthly cap for semi-monthly
167 except for a penny difference.
168 24-Apr-2002 ekim Changed Terminated Employee logic, added
169 PAYROLL_TERMINATION_TYPE and
170 BG_TERMINATION_TYPE
171 24-MAY-2005 asasthan Added Reduce Regula logic
172 14-MAR-2006 asasthan Modifed Reduce Regular to use ASG_GRE_RUN dbi
173 01-Mar-2007 kvsankar Modified the formula to take care of
174 round off issues in Reduce Regular
175 26-Mar-2007 kvsankar Modified the formula to use _ASG_GRE_RUN
176 dimension for Replacement and Additional
177 balances
178 25-Aug-2008 sudedas Updated Formula Text to enable Core Proration
179 Functionality. Related Bugs 3556204, 5895804
180 And ER 3855241.
181 --
182 INPUTS: Monthly_Salary
183 --
184 DBI Required: ASG_SALARY_BASIS
185 TERMINATED_EMPLOYEE
186 FINAL_PAY_PROCESSED
187 PAYROLL_TERMINATION_TYPE
188 BG_TERMINATION_TYPE
189 SCL_ASG_US_WORK_SCHEDULE
190 ASG_HOURS
191 SCL_ASG_US_TIMECARD_REQUIRED
192 LABOR_RECORDING_COUNT
193 PAY_PERIOD_TYPE
194 --
195 ******************************************************************
196 DESCRIPTION:
197 ******************************************************************
198 Computes earnings per pay period for salaried employees.
199 Proration function must be available to determine if employee worked entire
200 period - earnings will be adjusted accordingly by proration fn to account
201 for new hire, termination, leave of absence, etc.
202
203 *** Handling Salaried Earnings ***
204 Monthly earnings for salaried employees are processed every pay-period.
205 Salaried employees may or may not be required to submit a
206 timecard for days worked. If timecard is not required,
207 then the normal hours worked used for calculating employee pay are
208 derived from the Work Schedule they are ASGigned to or by the Standard
209 Hours entered on the ASGignment.
210
211 The Monthly Salary input value will be boiled down to an hourly rate in
212 order to utilize the pro-ration capabilities of the system(Issue 1).
213 The formula will make use of the pro-ration function for handling
214 adjustment of employee pay in case of mid-period events such as
215 termination, leave of absence, change of salary, etc. So an employee
216 is only paid for the days actually worked in a period with no
217 calculations or adjustments required by the user prior to or after
218 the payroll run (for a detailed discussion of Work Schedules and
219 Pro-Ration, see the Work Schedules - High Level Design).
220
221 When a timecard is submitted for an employee NOT REQUIRED to submit one,
222 then the "timecard" element entry (or entries) is treated as an override to
223 normal salary processing for the employee - IF the timecard entry has
224 a Rate or Rate Code entered(Issue 2). When a timecard is required
225 for a salaried employee and one is not submitted by the payroll cutoff
226 date, then the employee'' s pay will not be processed - and will have to wait
227 for a subsequent run.
228
229 NOTE: On a timecard submitted for a Salaried-Timecard Required employee,
230 a Rate (or Rate Code and Table) can be entered - ELSE the Monthly Salary input
231 value will be converted to an hourly rate using Convert_Figure function.
232
233 If this is the Final Pay run for the employee'' s ASGignment, then the
234 Regular Salary element will be discontinued after this run.
235 ******************************************************************
236 ALGORITHM:
237 ******************************************************************
238
239 If timecard required and time entries NOT FOUND, then
240 message= ''No timecards entered for Salaried, Timecard Required employee.''
241 return message
242 -- NOTE: If tc was req''d and time entries WERE found - then the skip rule
243 -- for this Regular Salary element would have skipped this processing.
244 Endif
245
246 Convert monthly salary to an hourly rate;
247 Call proration function with hourly rate; --> Regular_Salaried_Earnings
248
249 If this is final pay for employee(ASGignment), then
250 discontinue further processing of this element -- This is last time.
251 endif
252
253 Return REGULAR_SALARIED_EARNINGS
254 ******************************************************************
255 --
256 The earnings calculation for salaried employees will primarily be
257 performed by the "calculate_period_earnings" function. This function
258 has its'' own hld (in Work Schedules Functionality doc) and lld to be
259 called calc_period_earnings.lld
260 --
261 For a Salaried employee, if a timecard is entered (ie. a time entry
262 with "Hours" AND a "Rate" or "Rate Code - then this formula
263 will be skipped.
264 Note: there is an element used solely for the entry of hours worked
265 for SALARIED employees.
266 --
267 ******************************************************************
268 FORMULA_TEXT: REGULAR SALARY
269 **************************************************************** */
270
271 /* Alias Section */
272 ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
273 ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required
274
275 /* DBI Defaults */
276 DEFAULT FOR ASG_SALARY_BASIS IS ''NOT ENTERED''
277 /* 330341 FIX GOES HERE */
278 DEFAULT FOR ASG_SALARY IS 0
279 DEFAULT FOR ASG_SALARY_BASIS_CODE IS ''NOT ENTERED''
280 /* 330341 FIX END */
281 DEFAULT FOR TERMINATED_EMPLOYEE IS ''N''
282 DEFAULT FOR FINAL_PAY_PROCESSED IS ''N''
283 DEFAULT FOR PAYROLL_TERMINATION_TYPE IS ''L''
284 DEFAULT FOR BG_TERMINATION_TYPE IS ''L''
285 default for LAST_STANDARD_PROCESS_DATE_PROCESSED IS ''N''
286
287 default for PAY_PROC_PERIOD_START_DATE is ''1900/01/01 00:00:00'' (DATE)
288 default for PAY_PROC_PERIOD_END_DATE is ''1900/01/02 00:00:00'' (DATE)
289 default for PAY_EARNED_START_DATE is ''1900/01/01 00:00:00'' (DATE)
290 default for PAY_EARNED_END_DATE is ''1900/01/02 00:00:00'' (DATE)
291
292 DEFAULT FOR LABOR_RECORDING_COUNT IS 0
293 DEFAULT FOR Work_Schedule IS ''NOT ENTERED''
294 DEFAULT FOR ASG_HOURS IS 0
295 DEFAULT FOR ASG_FREQ IS ''NOT ENTERED'' /* WWBug 323639 */
296 DEFAULT FOR Timecard_Required IS ''N''
297 DEFAULT FOR REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD IS 0
298 DEFAULT FOR REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN IS 0
299 DEFAULT FOR REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN IS 0
300 DEFAULT FOR REGULAR_SALARY_ASG_GRE_RUN IS 0
301 /* 330341
302 DEFAULT FOR REGULAR_SALARY_ASG_GRE_MONTH IS 0
303 */
304 DEFAULT FOR REGULAR_SALARY_ASG_GRE_YTD IS 0
305 DEFAULT FOR REGULAR_HOURS_WORKED_ASG_GRE_RUN IS 0
306
307 DEFAULT FOR USER_ENTERED_TIME IS ''N''
308 DEFAULT FOR PAY_PERIOD_TYPE IS ''MONTH'' /*added for 392177*/
309
310 /* Input Value Defaults */
311 DEFAULT FOR Monthly_Salary IS 0
312 DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
313 DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
314 /* Added For Enabling Core Proration */
315 DEFAULT FOR PRORATE_START IS ''1900/01/01 00:00:00'' (DATE)
316 DEFAULT FOR PRORATE_END IS ''1900/01/02 00:00:00'' (DATE)
317 DEFAULT FOR PAY_PERIODS_PER_FISCAL_YEAR IS 1
318 DEFAULT FOR MESG IS '' ''
319
320
321 INPUTS ARE Monthly_Salary,
322 prorate_start (date),
323 prorate_end (date)
324
325 /* Monthly salary must be converted to an hourly rate - using
326 1) work schedule hours, or
327 2) standard hours on ASGignment as hours per week
328 In either case, Convert_Period_Type function will handle it.
329
330 At the Business Group and Organization level, termination
331 rule is determined which all terminated employees will
332 follow for the payment. All existing payrolls and/or
333 business groups will be updated with Actual Termination
334 Date for the termination rule and for new Business Group
335 or payroll, user can decide whether to use Actual Term date
336 or Last Standard Process Date. When no term rule is
337 specified, it defaults to Last Standard Process date.
338
339 The skip rule is changed so that for the New payroll or
340 business group created since July FP/2002, seeded skip
341 rule will skip element processing using LSP date and
342 the existing payroll/business group, it will continue
343 to use Actual Termination date.
344 */
345
346 MESG = '' ''
347
348 /* Why is this check not performed in skip formula? */
349 IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
350 (MESG = ''No timecards entered for Salaried, Timecard Required employee''
351 soe_run = REGULAR_SALARY_ASG_GRE_RUN
352 soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
353 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
354 RETURN MESG
355 )
356
357 IF REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
358 REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN = 0 THEN
359 (
360
361 actual_hours_worked = 0
362 t_vac_hours_taken = 0
363 t_vac_pay = 0
364 t_sick_hours_taken = 0
365 t_sick_pay = 0
366
367 IF ASG_SALARY_BASIS WAS DEFAULTED THEN
368 MESG = ''Pay Basis MUST be entered for Regular Salary calculation.''
369
370 /* Start of Proration Logic */
371
372 IF PRORATE_START was defaulted THEN
373 PRORATE_START = PAY_PROC_PERIOD_START_DATE
374
375 IF PRORATE_END was defaulted THEN
376 PRORATE_END = PAY_PROC_PERIOD_END_DATE
377
378 /* Initializing Local variables */
379 t_schedule_source = '' ''
380 t_schedule = '' ''
381 t_return_status = -1
382 t_return_message = '' ''
383
384 hours_in_proration = 0
385 hours_in_period = 1
386 earnings_factor = 1
387 regular_salaried_earnings = 0
388
389 hours_in_proration = HOURS_BETWEEN(PRORATE_START
390 , PRORATE_END
391 , ''WORK''
392 ,''N''
393 ,''BUSY''
394 ,''US''
395 ,t_schedule_source
396 ,t_schedule
397 ,t_return_status
398 ,t_return_message
399 ,''H'')
400
401 hours_in_period = HOURS_BETWEEN( PAY_PROC_PERIOD_START_DATE
402 , PAY_PROC_PERIOD_END_DATE
403 , ''WORK''
404 ,''N''
405 ,''BUSY''
406 ,''US''
407 ,t_schedule_source
408 ,t_schedule
409 ,t_return_status
410 ,t_return_message
411 ,''H'')
412
413 earnings_factor = hours_in_proration/hours_in_period
414 regular_salaried_earnings = earnings_factor * ( ( Monthly_Salary * 12 ) / PAY_PERIODS_PER_FISCAL_YEAR )
415 actual_hours_worked = hours_in_proration
416
417 /* End Proration Logic */
418 /* For Sick and Vacation Pay */
419
420 hourly_rate = 0
421 hourly_rate = get_hourly_rate()
422
423 t_sick_pay = calc_sick_pay(PAY_PROC_PERIOD_END_DATE
424 ,PRORATE_START
425 ,PRORATE_END
426 ,hourly_rate
427 ,t_sick_hours_taken)
428
429 t_vac_pay = calc_vac_pay(PAY_PROC_PERIOD_END_DATE
430 ,PRORATE_START
431 ,PRORATE_END
432 ,hourly_rate
433 ,t_vac_hours_taken)
434
435 /* 330341 FIX GOES HERE */
436 /* 392177 added the and PAY_PERIOD_TYPE ... portion of if below */
437
438 IF (ASG_SALARY_BASIS_CODE = ''MONTHLY'' AND PAY_PERIOD_TYPE NOT LIKE ''%Week%'') THEN
439
440 /*IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings > ASG_SALARY THEN */
441
442 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
443 regular_salaried_earnings = ASG_SALARY - REGULAR_SALARY_ASG_GRE_MONTH
444
445 /* 392177 END */
446 /* 330341 END */
447
448 )
449 ELSE
450 (regular_salaried_earnings = REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
451 clear_repl_amt = -1 * REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
452
453 /* WWBug 323639 */
454 actual_hours_worked = 0
455 t_vac_hours_taken = 0
456 t_vac_pay = 0
457 t_sick_hours_taken = 0
458 t_sick_pay = 0
459 )
460
461 regular_salaried_earnings = regular_salaried_earnings
462 + REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
463 + REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
464
465 /* Reduce Regular Changes Start Here */
466
467 RED_REG_ADJUST_AMOUNT = 0.05
468 RED_REG_ADJUST_HOURS = 0.01
469
470
471 t_reduce_regular_earnings = REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
472 t_reduce_regular_hours = REDUCE_REGULAR_HOURS_ASG_GRE_RUN
473
474 t_return = REDUCED_REGULAR_CALC(PAY_PROC_PERIOD_END_DATE
475 ,PRORATE_START
476 ,PRORATE_END
477 ,t_reduce_regular_earnings
478 ,t_reduce_regular_hours)
479
480 reduce_regular_earnings = t_reduce_regular_earnings
481 reduce_regular_hours = t_reduce_regular_hours
482
483 diff_earnings = regular_salaried_earnings - reduce_regular_earnings + RED_REG_ADJUST_AMOUNT
484 diff_hours = actual_hours_worked - reduce_regular_hours + RED_REG_ADJUST_HOURS
485
486 /* Reduce Regular Changes Start Here */
487
488 IF Timecard_Required = ''N'' AND
489 reduce_regular_earnings <> 0 THEN
490 (
491 /*
492 * We need to carry over reduce regular Earnings/Hours to the next Pay
493 * Period if it is more than regular salaried earnings so that we
494 * never have regular salaried earnings less than ZERO
495 */
496 IF diff_earnings >= 0 THEN
497 (
498 regular_salaried_earnings = regular_salaried_earnings - reduce_regular_earnings
499 if regular_salaried_earnings < 0 then
500 (
501 regular_salaried_earnings = 0
502 )
503 )
504 ELSE
505 (
506 /* reduce_regular_earnings = regular_salaried_earnings */
507 regular_salaried_earnings = 0
508 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
509 )
510
511 IF diff_hours >= 0 THEN
512 (
513 actual_hours_worked = actual_hours_worked - reduce_regular_hours
514 if actual_hours_worked < 0 then
515 (
516 actual_hours_worked = 0
517 )
518 )
519 ELSE
520 (
521 /* reduce_regular_hours = actual_hours_worked */
522 actual_hours_worked = 0
523 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
524 )
525 )
526 ELSE
527 (
528 reduce_regular_earnings = 0
529 reduce_regular_hours = 0
530 )
531
532 /* Reduce Regular Changes End Here */
533
534 /*
535 At the Business Group and Organization level, termination
536 rule is determined which all terminated employees will
537 follow for the payment. All existing payrolls and/or
538 business groups (prior to July Family Pack 2002)
539 will be updated with Actual Termination Date for the
540 termination rule and for new Business Group
541 or payroll, user can decide whether to use Actual Term date
542 or Last Standard Process Date. When no term rule is
543 specified, it defaults to Last Standard Process date.
544 */
545
546 IF regular_salaried_earnings < 0 THEN
547 (IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''A'' AND
548 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
549 (PAYROLL_TERMINATION_TYPE = ''A'' AND
550 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
551 (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''L'' AND
552 TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
553 OR
554 (PAYROLL_TERMINATION_TYPE = ''L'' AND
555 TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
556 neg_earn = 0
557 ELSE
558 (neg_earn = regular_salaried_earnings - REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
559 regular_salaried_earnings = 0
560 )
561 )
562 ELSE
563 (IF REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
564 neg_earn = -1 * REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
565 )
566
567 IF REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
568 clear_addl_amt = -1 * REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
569
570 IF t_vac_pay <> 0 THEN
571 (vac_pay = t_vac_pay
572 vac_hours_taken = t_vac_hours_taken
573 regular_salaried_earnings = regular_salaried_earnings - vac_pay
574 actual_hours_worked = actual_hours_worked - vac_hours_taken
575 )
576
577 IF t_sick_pay <> 0 THEN
578 (sick_pay = t_sick_pay
579 sick_hours_taken = t_sick_hours_taken
580 regular_salaried_earnings = regular_salaried_earnings - sick_pay
581 actual_hours_worked = actual_hours_worked - sick_hours_taken
582 )
583
584 /* Create latest balances */
585 /* There is no RUN level leatest balances
586 and REGULAR_SALARY_ASG_GRE_YTD is added to to latest balance script.
587 soe_run = REGULAR_SALARY_ASG_GRE_RUN
588 soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
589 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
590 */
591
592 IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
593 BG_TERMINATION_TYPE = ''A'' AND
594 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'' ) OR
595 (PAYROLL_TERMINATION_TYPE = ''A'' AND
596 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
597 (STOP_ENTRY = ''Y''
598 mesg = ''Regular Salary being stopped after Final Pay.''
599 RETURN regular_salaried_earnings, actual_hours_worked,
600 clear_repl_amt, clear_addl_amt, neg_earn, vac_pay,
601 vac_hours_taken, sick_pay, sick_hours_taken,
602 STOP_ENTRY, mesg, reduce_regular_earnings,
603 reduce_regular_hours
604 )
605 ELSE
606 RETURN regular_salaried_earnings, actual_hours_worked,
607 clear_repl_amt, clear_addl_amt, neg_earn,
608 vac_pay, vac_hours_taken, sick_pay, sick_hours_taken,
609 reduce_regular_earnings, reduce_regular_hours, mesg
610 ';
611
612 l_old_formula_text := '
613 /* ***************************************************************
614 $Header: payusrsrwupg.pkb 120.6 2011/12/21 04:14:53 emunisek ship $
615 FORMULA NAME: REGULAR_SALARY
616 FORMULA TYPE: Payroll
617
618 Change History
619 29 Sep 1993 hparicha Created.
620 14 Oct 1993 hparicha Moved skip rule formulae to separate
621 file ".SKIP".
622 30 Nov 1993 hparicha G187
623 06 Dec 1993 jmychale G305. Renamed Convert_Figure to Convert_
624 Period_Type; reference TIME_ENTRY_WAGES
625 temporarily in search for timecards until
626 information hours element is defined
627 07 Jan 1994 jmychale G491. Removed actual hours worked
628 parameter from
629 Calculate_Period_Earnings()
630 13 Jan 1994 hparicha G497. Reverted calc period earnings
631 to us ACTUAL_HOURS_WORKED! Used by
632 Statement of Earnings report. Replace
633 TIME_ENTR_WAGES_COUNT with
634 LABOR_RECORDING_COUNT for timecard
635 req''d employees.
636 24 Feb 1994 hparicha G560. ASS -> ASG; TU -> GRE
637 24 Feb 1994 hparicha G581. Handles negative earnings.
638 09 Jun 1994 hparicha G907. New implementation of generated and
639 startup earnings and deductions using
640 "<ELE_NAME> Special Features" shadow element
641 to feed balances and handle Addl/Repl Amounts.
642 04 Jan 1995 hparicha Vacation/Sick correlation to Regular Pay.
643 New results need to be passed when vac/sick
644 pay are present.
645 04 May 1995 hparicha Defaulted values for PAY_PROC_PERIOD_START/
646 END_DATE. Default dates should be obvious
647 when default is used.
648 10 Jan 1996 hparicha 323639 Major cleanup effort involving
649 proration and other user defined functions
650 and formulae changes.
651 16 Apr 1996 ssdesai Latest balances creation.
652 25 Apr 1996 hparicha 344018. Replacing reference to
653 TIME_ENTRY_WAGES_COUNT with new dbi for
654 USER_ENTERED_TIME which looks for any time
655 entered for regular pay which should override
656 Regular Salary and Regular Wages...ie. not
657 just the seeded Time Entry Wages element is
658 able to override Regular.
659 11 Jun 1996 hparicha 330341. Ensures that the sum of salary in pay
660 periods adds up to monthly amount when pay
661 basis is monthly. We may want to add checks
662 for other pay basis types - ie.
663 annual pay basis, period pay basis.
664 3 SEP 1997 lwthomps 392177. Check if weekly or biweekly payroll
665 before caping salary to monthly salary basis.
666 17 Mar 1997 djeng 465454, modified PAY_PROC_PERIOD_END_DATE
667 and PAY_PROC_PERIOD_END_DATE to
668 PAY_EARNED_START_DATE / PAY_EARNED_END_DATE
669 13 AUG 1997 Lwhtomps BUG 525859. Payments dimension can not be held
670 as a latest balance.
671 21-JAN-1999 RAMURTHY BUG 803578 - no monthly cap for semi-monthly
672 except for a penny difference.
673 24-Apr-2002 ekim Changed Terminated Employee logic, added
674 PAYROLL_TERMINATION_TYPE and
675 BG_TERMINATION_TYPE
676 24-MAY-2005 asasthan Added Reduce Regula logic
677 14-MAR-2006 asasthan Modifed Reduce Regular to use ASG_GRE_RUN dbi
678 01-Mar-2007 kvsankar Modified the formula to take care of
679 round off issues in Reduce Regular
680 26-Mar-2007 kvsankar Modified the formula to use _ASG_GRE_RUN
681 dimension for Replacement and Additional
682 balances
683 --
684 INPUTS: Monthly_Salary
685 --
686 DBI Required: ASG_SALARY_BASIS
687 TERMINATED_EMPLOYEE
688 FINAL_PAY_PROCESSED
689 PAYROLL_TERMINATION_TYPE
690 BG_TERMINATION_TYPE
691 SCL_ASG_US_WORK_SCHEDULE
692 ASG_HOURS
693 SCL_ASG_US_TIMECARD_REQUIRED
694 LABOR_RECORDING_COUNT
695 PAY_PERIOD_TYPE
696 --
697 ******************************************************************
698 DESCRIPTION:
699 ******************************************************************
700 Computes earnings per pay period for salaried employees.
701 Proration function must be available to determine if employee worked entire
702 period - earnings will be adjusted accordingly by proration fn to account
703 for new hire, termination, leave of absence, etc.
704
705 *** Handling Salaried Earnings ***
706 Monthly earnings for salaried employees are processed every pay-period.
707 Salaried employees may or may not be required to submit a
708 timecard for days worked. If timecard is not required,
709 then the normal hours worked used for calculating employee pay are
710 derived from the Work Schedule they are ASGigned to or by the Standard
711 Hours entered on the ASGignment.
712
713 The Monthly Salary input value will be boiled down to an hourly rate in
714 order to utilize the pro-ration capabilities of the system(Issue 1).
715 The formula will make use of the pro-ration function for handling
716 adjustment of employee pay in case of mid-period events such as
717 termination, leave of absence, change of salary, etc. So an employee
718 is only paid for the days actually worked in a period with no
719 calculations or adjustments required by the user prior to or after
720 the payroll run (for a detailed discussion of Work Schedules and
721 Pro-Ration, see the Work Schedules - High Level Design).
722
723 When a timecard is submitted for an employee NOT REQUIRED to submit one,
724 then the "timecard" element entry (or entries) is treated as an override to
725 normal salary processing for the employee - IF the timecard entry has
726 a Rate or Rate Code entered(Issue 2). When a timecard is required
727 for a salaried employee and one is not submitted by the payroll cutoff
728 date, then the employee''s pay will not be processed - and will have to wait
729 for a subsequent run.
730
731 NOTE: On a timecard submitted for a Salaried-Timecard Required employee,
732 a Rate (or Rate Code and Table) can be entered - ELSE the Monthly Salary input
733 value will be converted to an hourly rate using Convert_Figure function.
734
735 If this is the Final Pay run for the employee''s ASGignment, then the
736 Regular Salary element will be discontinued after this run.
737 ******************************************************************
738 ALGORITHM:
739 ******************************************************************
740
741 If timecard required and time entries NOT FOUND, then
742 message=''No timecards entered for Salaried, Timecard Required employee.''
743 return message
744 -- NOTE: If tc was req''d and time entries WERE found - then the skip rule
745 -- for this Regular Salary element would have skipped this processing.
746 Endif
747
748 Convert monthly salary to an hourly rate;
749 Call proration function with hourly rate; --> Regular_Salaried_Earnings
750
751 If this is final pay for employee(ASGignment), then
752 discontinue further processing of this element -- This is last time.
753 endif
754
755 Return REGULAR_SALARIED_EARNINGS
756 ******************************************************************
757 --
758 The earnings calculation for salaried employees will primarily be
759 performed by the "calculate_period_earnings" function. This function
760 has its'' own hld (in Work Schedules Functionality doc) and lld to be
761 called calc_period_earnings.lld
762 --
763 For a Salaried employee, if a timecard is entered (ie. a time entry
764 with "Hours" AND a "Rate" or "Rate Code - then this formula
765 will be skipped.
766 Note: there is an element used solely for the entry of hours worked
767 for SALARIED employees.
768 --
769 ******************************************************************
770 FORMULA_TEXT: REGULAR SALARY
771 **************************************************************** */
772
773 /* Alias Section */
774 ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
775 ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required
776
777 /* DBI Defaults */
778 DEFAULT FOR ASG_SALARY_BASIS IS ''NOT ENTERED''
779 /* 330341 FIX GOES HERE */
780 DEFAULT FOR ASG_SALARY IS 0
781 DEFAULT FOR ASG_SALARY_BASIS_CODE IS ''NOT ENTERED''
782 /* 330341 FIX END */
783 DEFAULT FOR TERMINATED_EMPLOYEE IS ''N''
784 DEFAULT FOR FINAL_PAY_PROCESSED IS ''N''
785 DEFAULT FOR PAYROLL_TERMINATION_TYPE IS ''L''
786 DEFAULT FOR BG_TERMINATION_TYPE IS ''L''
787 default for LAST_STANDARD_PROCESS_DATE_PROCESSED IS ''N''
788
789 default for PAY_PROC_PERIOD_START_DATE is ''1900/01/01 00:00:00'' (DATE)
790 default for PAY_PROC_PERIOD_END_DATE is ''1900/01/02 00:00:00'' (DATE)
791 default for PAY_EARNED_START_DATE is ''1900/01/01 00:00:00'' (DATE)
792 default for PAY_EARNED_END_DATE is ''1900/01/02 00:00:00'' (DATE)
793
794 DEFAULT FOR LABOR_RECORDING_COUNT IS 0
795 DEFAULT FOR Work_Schedule IS ''NOT ENTERED''
796 DEFAULT FOR ASG_HOURS IS 0
797 DEFAULT FOR ASG_FREQ IS ''NOT ENTERED'' /* WWBug 323639 */
798 DEFAULT FOR Timecard_Required IS ''N''
799 DEFAULT FOR REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD IS 0
800 DEFAULT FOR REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN IS 0
801 DEFAULT FOR REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN IS 0
802 DEFAULT FOR REGULAR_SALARY_ASG_GRE_RUN IS 0
803 /* 330341
804 DEFAULT FOR REGULAR_SALARY_ASG_GRE_MONTH IS 0
805 */
806 DEFAULT FOR REGULAR_SALARY_ASG_GRE_YTD IS 0
807 DEFAULT FOR REGULAR_HOURS_WORKED_ASG_GRE_RUN IS 0
808
809 DEFAULT FOR USER_ENTERED_TIME IS ''N''
810 DEFAULT FOR PAY_PERIOD_TYPE IS ''MONTH'' /*added for 392177*/
811
812 /* Input Value Defaults */
813 DEFAULT FOR Monthly_Salary IS 0
814 DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
815 DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
816
817 INPUTS ARE Monthly_Salary
818
819 /* Monthly salary must be converted to an hourly rate - using
820 1) work schedule hours, or
821 2) standard hours on ASGignment as hours per week
822 In either case, Convert_Period_Type function will handle it.
823
824
825 At the Business Group and Organization level, termination
826 rule is determined which all terminated employees will
827 follow for the payment. All existing payrolls and/or
828 business groups will be updated with Actual Termination
829 Date for the termination rule and for new Business Group
830 or payroll, user can decide whether to use Actual Term date
831 or Last Standard Process Date. When no term rule is
832 specified, it defaults to Last Standard Process date.
833
834 The skip rule is changed so that for the New payroll or
835 business group created since July FP/2002, seeded skip
836 rule will skip element processing using LSP date and
837 the existing payroll/business group, it will continue
838 to use Actual Termination date.
839 */
840
841 /* Changed for new Termination Rule
842 There will be no stop entry using USER_ENTERED_TIME
843 as of July FP/2002
844 MESG = '' ''
845
846 IF TERMINATED_EMPLOYEE = ''Y'' AND USER_ENTERED_TIME = ''Y'' THEN
847 (STOP_ENTRY = ''Y''
848 mesg = ''Regular Salary being stopped after Final Pay.''
849 soe_run = REGULAR_SALARY_ASG_GRE_RUN
850 soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
851
852 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
853
854 RETURN STOP_ENTRY, mesg
855 )
856 */
857 /* Why is this check not performed in skip formula? */
858 IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
859 (mesg = ''No timecards entered for Salaried, Timecard Required employee''
860 soe_run = REGULAR_SALARY_ASG_GRE_RUN
861 soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
862
863 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
864
865 RETURN mesg
866 )
867
868 IF REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
869 REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN = 0 THEN
870 (hourly_rate = get_hourly_rate()
871 /*Convert_Period_Type( Work_Schedule,
872 ASG_HOURS,
873 Monthly_Salary,
874 ASG_SALARY_BASIS,
875 ''HOURLY'',
876 PAY_EARNED_START_DATE,
877 PAY_EARNED_END_DATE,
878 ASG_FREQ)*/
879 /* WWBug 323639 */
880
881 actual_hours_worked = 0
882 t_vac_hours_taken = 0
883 t_vac_pay = 0
884 t_sick_hours_taken = 0
885 t_sick_pay = 0
886
887 IF ASG_SALARY_BASIS WAS DEFAULTED THEN
888 mesg = ''Pay Basis MUST be entered for Regular Salary calculation.''
889
890 regular_salaried_earnings = Calc_Period_Earnings (
891 ASG_SALARY_BASIS,
892 ''MONTHLY SALARY'',
893 hourly_rate,
894 PAY_EARNED_START_DATE,
895 PAY_EARNED_END_DATE,
896 actual_hours_worked,
897 t_vac_hours_taken,
898 t_vac_pay,
899 t_sick_hours_taken,
900 t_sick_pay,
901 ''Y'',
902 ASG_FREQ)
903
904 /*Calculate_Period_Earnings(
905 ASG_SALARY_BASIS,
906 ''MONTHLY SALARY'',
907 hourly_rate,
908 PAY_EARNED_START_DATE,
909 PAY_EARNED_END_DATE,
910 Work_Schedule,
911 ASG_HOURS,
912 actual_hours_worked,
913 t_vac_hours_taken,
914 t_vac_pay,
915 t_sick_hours_taken,
916 t_sick_pay,
917 ''Y'',
918 ASG_FREQ) */
919 /* WWBug 323639 */
920
921 /* 330341 FIX GOES HERE */
922 /* 392177 added the and PAY_PERIOD_TYPE ... portion of if below */
923 IF (ASG_SALARY_BASIS_CODE = ''MONTHLY'' AND PAY_PERIOD_TYPE NOT LIKE ''%Week%'') THEN
924
925 /*IF REGULAR_SALARY_ASG_GRE_MONTH + regular_salaried_earnings > ASG_SALARY THEN */
926 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
927
928 regular_salaried_earnings = ASG_SALARY - REGULAR_SALARY_ASG_GRE_MONTH
929
930 /* 392177 END */
931 /* 330341 END */
932
933
934 )
935 ELSE
936 (regular_salaried_earnings = REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
937 clear_repl_amt = -1 * REGULAR_SALARY_REPLACEMENT_ASG_GRE_RUN
938
939 /* WWBug 323639 */
940 actual_hours_worked = 0
941 t_vac_hours_taken = 0
942 t_vac_pay = 0
943 t_sick_hours_taken = 0
944 t_sick_pay = 0
945 )
946
947 regular_salaried_earnings = regular_salaried_earnings
948 + REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
949 + REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
950
951 /* Reduce Regular Changes Start Here */
952
953 RED_REG_ADJUST_AMOUNT = 0.05
954 RED_REG_ADJUST_HOURS = 0.01
955
956 reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
957 reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
958 diff_earnings = regular_salaried_earnings - reduce_regular_earnings
959 + RED_REG_ADJUST_AMOUNT
960 diff_hours = actual_hours_worked - reduce_regular_hours
961 + RED_REG_ADJUST_HOURS
962
963 /* Reduce Regular Changes Start Here */
964
965 IF Timecard_Required = ''N'' AND
966 reduce_regular_earnings <> 0 THEN
967 (
968 /*
969 * We need to carry over reduce regular Earnings/Hours to the next Pay
970 * Period if it is more than regular salaried earnings so that we
971 * never have regular salaried earnings less than ZERO
972 */
973 IF diff_earnings >= 0 THEN
974 (
975 regular_salaried_earnings = regular_salaried_earnings - reduce_regular_earnings
976 if regular_salaried_earnings < 0 then
977 (
978 regular_salaried_earnings = 0
979 )
980 )
981 ELSE
982 (
983 /* reduce_regular_earnings = regular_salaried_earnings */
984 regular_salaried_earnings = 0
985 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
986 )
987
988 IF diff_hours >= 0 THEN
989 (
990 actual_hours_worked = actual_hours_worked - reduce_regular_hours
991 if actual_hours_worked < 0 then
992 (
993 actual_hours_worked = 0
994 )
995 )
996 ELSE
997 (
998 /* reduce_regular_hours = actual_hours_worked */
999 actual_hours_worked = 0
1000 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
1001 )
1002 )
1003 ELSE
1004 (
1005 reduce_regular_earnings = 0
1006 reduce_regular_hours = 0
1007 )
1008
1009 /* Reduce Regular Changes End Here */
1010
1011 /*
1012 At the Business Group and Organization level, termination
1013 rule is determined which all terminated employees will
1014 follow for the payment. All existing payrolls and/or
1015 business groups (prior to July Family Pack 2002)
1016 will be updated with Actual Termination Date for the
1017 termination rule and for new Business Group
1018 or payroll, user can decide whether to use Actual Term date
1019 or Last Standard Process Date. When no term rule is
1020 specified, it defaults to Last Standard Process date.
1021 */
1022
1023 IF regular_salaried_earnings < 0 THEN
1024 (IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''A'' AND
1025 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
1026 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1027 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
1028 (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND BG_TERMINATION_TYPE = ''L'' AND
1029 TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
1030 OR
1031 (PAYROLL_TERMINATION_TYPE = ''L'' AND
1032 TERMINATED_EMPLOYEE = ''Y'' AND LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'')
1033 THEN
1034 neg_earn = 0
1035 ELSE
1036 (neg_earn = regular_salaried_earnings
1037 - REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
1038 regular_salaried_earnings = 0
1039 )
1040 )
1041 ELSE
1042 (IF REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
1043 neg_earn = -1 * REGULAR_SALARY_NEG_EARNINGS_ASG_GRE_ITD
1044 )
1045
1046 IF REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
1047 clear_addl_amt = -1 * REGULAR_SALARY_ADDITIONAL_ASG_GRE_RUN
1048
1049 IF t_vac_pay <> 0 THEN
1050 (vac_pay = t_vac_pay
1051 vac_hours_taken = t_vac_hours_taken
1052 regular_salaried_earnings = regular_salaried_earnings - vac_pay
1053 actual_hours_worked = actual_hours_worked - vac_hours_taken
1054 )
1055
1056 IF t_sick_pay <> 0 THEN
1057 (sick_pay = t_sick_pay
1058 sick_hours_taken = t_sick_hours_taken
1059 regular_salaried_earnings = regular_salaried_earnings - sick_pay
1060 actual_hours_worked = actual_hours_worked - sick_hours_taken
1061 )
1062
1063 /* Create latest balances */
1064 /* There is no RUN level leatest balances
1065 and REGULAR_SALARY_ASG_GRE_YTD is added to to latest balance script.
1066 soe_run = REGULAR_SALARY_ASG_GRE_RUN
1067 soe_ytd = REGULAR_SALARY_ASG_GRE_YTD
1068 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
1069 */
1070 IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1071 BG_TERMINATION_TYPE = ''A'' AND
1072 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'' ) OR
1073 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1074 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
1075 (STOP_ENTRY = ''Y''
1076 mesg = ''Regular Salary being stopped after Final Pay.''
1077 RETURN regular_salaried_earnings, actual_hours_worked,
1078 clear_repl_amt, clear_addl_amt, neg_earn, vac_pay,
1079 vac_hours_taken, sick_pay, sick_hours_taken,
1080 STOP_ENTRY, mesg, reduce_regular_earnings,
1081 reduce_regular_hours
1082 )
1083 ELSE
1084 RETURN regular_salaried_earnings, actual_hours_worked,
1085 clear_repl_amt, clear_addl_amt, neg_earn,
1086 vac_pay, vac_hours_taken, sick_pay, sick_hours_taken,
1087 reduce_regular_earnings, reduce_regular_hours, mesg
1088 ';
1089
1090 -- Modification of Fast Formula Text of Regular_Wages
1091
1092 ELSIF p_ele_name = 'Regular Wages' THEN
1093 l_formula_name := 'REGULAR_WAGES';
1094
1095 hr_utility.trace('Modifying Formula ' || l_formula_name);
1096
1097 open c_formula_id(l_formula_name);
1098 fetch c_formula_id into l_formula_id ;
1099 close c_formula_id;
1100
1101 hr_utility.trace('l_formula_id := ' || l_formula_id);
1102
1103 l_old_formula_text := NULL;
1104 l_new_formula_text := NULL;
1105
1106 l_new_formula_text :=
1107 '/* ***************************************************************
1108 $Header: payusrsrwupg.pkb 120.6 2011/12/21 04:14:53 emunisek ship $
1109 FORMULA NAME: REGULAR_WAGES
1110 FORMULA TYPE: Payroll
1111 Change History
1112 29 Sep 1993 hparicha Created.
1113 14 Oct 1993 hparicha Moved skip rule formulae to separate
1114 file ".SKIP".
1115 30 Nov 1993 hparicha G187
1116 06 Dec 1993 jmychale G305. Added aliases, entered database items
1117 and tidied up formula
1118 07 Jan 1994 jmychale G491. Removed actual hours worked
1119
1120 parameter from Calculate_Period_
1121 Earnings ()
1122 13 Jan 1994 hparicha G497. Reverted calc period earnings to
1123 use ACTUAL_HOURS_WORKED! Used by
1124 Statement of Earnings report. Replaced
1125 TIME_ENTRY_WAGES_COUNT with
1126 LABOR_RECORDING_COUNT for timecard
1127 req''d employees.
1128 24 Feb 1994 hparicha G560. ASS -> ASG; TU -> GRE
1129 24 Feb 1994 hparicha G581. Handles negative earnings.
1130 09 Jun 1994 hparicha G907. New implementation of generated and
1131 startup earnings and deductions using
1132 "<ELE_NAME> Special Features" shadow element
1133 to feed balances and handle Addl/Repl Amounts.
1134 04 Jan 1995 hparicha Vacation/Sick correlation to Regular Pay.
1135 New results need to be passed when vac/sick
1136 pay are present.
1137 04 May 1995 hparicha Defaulted values for PAY_PROC_PERIOD_
1138 START/END_DATE. Default dates should
1139 be obvious when default is used.
1140 10 Jan 1996 hparicha 323639 Major cleanup effort involving
1141 proration and other user
1142 defined functions and formulae changes.
1143 16 Apr 1996 ssdesai Latest balances creation.
1144 25 Apr 1996 hparicha 344018 Added check for USER_ENTERED_TIME.
1145 17 Apr 1996 djeng Changed PAY_PROC_PERIOD_START_DATE, and
1146 PAY_PROC_PERIOD_END_DATE to
1147 PAY_EARNED_START_DATE and PAY_EARNED_END_DATE
1148 13 AUG 1997 Lwhtomps BUG 525859. Payments dimension can not be held
1149 as a latest balance.
1150 21 MAY 2001 ssarma 3 formula result rules added for Hours by rate
1151 calculation. bug#1550323. They are:
1152 ELEMENT_TYPE_ID_PASSED
1153 RATE_PASSED
1154 HOURS_PASSED
1155 24 AUG 2001 pganguly Changed the default for USER_ENTERED_TIME
1156 from Y to N
1157 24-AUG-2002 ekim Changed the logic of Terminated employee.
1158 24-MAY-2005 kvsankar Cheanged the formula to use New Balance
1159 ''Reduce Regular Earnings'' and ''Reduce Regular
1160 Hours'' for Reduce Regular functionality
1161 14-MAR-2006 asasthan Modifed Reduce Regular to use ASG_GRE_RUN dbi
1162 01-Mar-2007 kvsankar Modified the formula to take care of
1163 round off issues in Reduce Regular
1164 26-Mar-2007 kvsankar Modified the formula to use _ASG_GRE_RUN
1165 dimension for Replacement and Additional
1166 balances
1167 25-Aug-2008 sudedas Updated Formula Text to enable Core Proration
1168 Functionality. Related Bugs 3556204, 5895804
1169 And ER 3855241.
1170 --
1171 --
1172 INPUTS: Rate
1173 Rate Code (text)
1174 --
1175 DBI Required: ASG_SALARY_BASIS
1176 TERMINATED_EMPLOYEE
1177 FINAL_PAY_PROCESSED
1178 PAYROLL_TERMINATION_TYPE
1179 BG_TERMINATION_TYPE
1180
1181 LABOR_RECORDING_COUNT
1182 SCL_ASG_US_WORK_SCHEDULE
1183 ASG_HOURS
1184 SCL_ASG_US_TIMECARD_REQUIRED
1185
1186 CURRENT_ELEMENT_TYPE_ID
1187 ******************************************************************
1188 DESCRIPTION:
1189 ******************************************************************
1190 Computes earnings per pay period for hourly employees.
1191 Proration function must be available to determine if employee worked entire
1192 period - earnings will be adjusted accordingly by proration fn to account
1193 for new hire, termination, leave of absence, etc.
1194 *** Hourly handling ***
1195 Regular wages earned per pay period for employees paid by the hour.
1196
1197 Hourly employees can either be "Hourly-Automatic" (ie. timecard not
1198 required) or "Hourly-Timecard" where a timecard is required for pay. The
1199 hourly rate for an employee is entered as the input value for this element.
1200 This rate is used with the number of hours worked to calculate earnings.
1201 Hours worked will be indicated by one of the following:
1202 - time entry or entries (ie. timecard)
1203 - ASGigned Work Schedule
1204 - standard hours entered at the Organization and ASGignment levels.
1205 For an Hourly-Timecard or "timecard required" employee, when a timecard
1206 is not submitted by the payroll input cutoff date - the wages for that
1207 employee will not be calculated and will have to wait for a subsequent
1208 payroll run for processing. When a timecard is submitted for an Hourly-
1209 Automatic employee, then the time entry (or entries) is treated as the source
1210 for Hours - if a rate is entered on the time entry, then this rate is used
1211 along with the hours to compute pay, otherwise the normal rate (Regular
1212 Wages rate) is used for computation.
1213 If this is the Final Pay run for the employee''s ASGignment, then the
1214 Regular Wages element will be discontinued after this run.
1215 ******************************************************************
1216 ALGORITHM:
1217 ******************************************************************
1218 If timecard required and time entries NOT FOUND, then
1219 message=''No timecards entered for Hourly, Timecard Required employee.''
1220 return message
1221 -- NOTE: If tc was req''d and time entries WERE found - then the skip rule
1222 -- for this Regular Salary element would have skipped this processing.
1223
1224
1225 Endif
1226 Call proration function with hourly rate; --> Regular_Wage_Earnings
1227 If this is final pay for employee(ASGignment), then
1228 discontinue further processing of this element -- This is last time.
1229 endif
1230 Return Regular_Wage_Earnings
1231 -- The earnings calculation for hourly employees will primarily be
1232 calculated by the calculate_period_earnings() function. This function
1233 has its'' own hld (in Work Schedules Functionality doc) and lld to be
1234 called calc_period_earnings.lld
1235 ******************************************************************
1236 FORMULA_TEXT:
1237
1238 *******************************************************************/
1239 /* Alias Section */
1240 ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
1241 ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required
1242 /* dbi defaults */
1243 DEFAULT FOR ASG_SALARY_BASIS IS ''NOT ENTERED''
1244 DEFAULT FOR TERMINATED_EMPLOYEE IS ''N''
1245 DEFAULT FOR FINAL_PAY_PROCESSED IS ''N''
1246 DEFAULT FOR PAYROLL_TERMINATION_TYPE IS ''L''
1247 DEFAULT FOR BG_TERMINATION_TYPE IS ''L''
1248 default for LAST_STANDARD_PROCESS_DATE_PROCESSED IS ''N''
1249 DEFAULT FOR LABOR_RECORDING_COUNT IS 0
1250 DEFAULT FOR USER_ENTERED_TIME IS ''N''
1251
1252 default for PAY_PROC_PERIOD_START_DATE is ''1900/01/01 00:00:00'' (DATE)
1253 default for PAY_PROC_PERIOD_END_DATE is ''1900/01/02 00:00:00'' (DATE)
1254 default for PAY_EARNED_START_DATE is ''1900/01/01 00:00:00'' (DATE)
1255 default for PAY_EARNED_END_DATE is ''1900/01/02 00:00:00'' (DATE)
1256
1257 DEFAULT FOR Work_Schedule IS ''NOT ENTERED''
1258 DEFAULT FOR ASG_HOURS IS 0
1259 DEFAULT FOR ASG_FREQ IS ''NOT ENTERED'' /* WWBug 323639 */
1260 DEFAULT FOR Timecard_Required IS ''N''
1261 DEFAULT FOR REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD IS 0
1262 DEFAULT FOR REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN IS 0
1263 DEFAULT FOR REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN IS 0
1264 DEFAULT FOR REGULAR_WAGES_ASG_GRE_RUN IS 0
1265 DEFAULT FOR REGULAR_HOURS_WORKED_ASG_GRE_RUN IS 0
1266 DEFAULT FOR CURRENT_ELEMENT_TYPE_ID IS 0
1267 DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
1268 DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
1269
1270 /* inpval defaults */
1271 DEFAULT FOR Rate IS 0
1272 DEFAULT FOR Rate_Code (text) IS ''NOT ENTERED''
1273
1274 /* Added For Enabling Core Proration */
1275 DEFAULT FOR PRORATE_START IS ''1900/01/01 00:00:00'' (DATE)
1276 DEFAULT FOR PRORATE_END IS ''1900/01/02 00:00:00'' (DATE)
1277 DEFAULT FOR PAY_PERIODS_PER_FISCAL_YEAR IS 1
1278
1279 INPUTS ARE Rate,
1280 Rate_Code (text),
1281 prorate_start (date),
1282 prorate_end (date)
1283
1284 /* Updatable Values */
1285 RATE_TABLE = ''WAGE RATES''
1286 RATE_TABLE_COLUMN = ''Wage Rate''
1287
1288 MESG = '' ''
1289 /* Changed for new Termination Rule
1290 IF TERMINATED_EMPLOYEE = ''Y'' AND USER_ENTERED_TIME = ''Y'' THEN
1291 (STOP_ENTRY = ''Y''
1292 mesg = ''Regular Wages being stopped after Final Pay.''
1293 RETURN STOP_ENTRY, mesg
1294 )
1295 */
1296 IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
1297 (mesg = ''No timecards entered for Hourly, Timecard Required employee''
1298 RETURN mesg
1299
1300 )
1301
1302 ELEMENT_TYPE_ID_PASSED = CURRENT_ELEMENT_TYPE_ID
1303 RATE_PASSED = 0
1304 HOURS_PASSED = 0
1305
1306 /* Start of Proration Logic */
1307
1308 IF PRORATE_START was defaulted THEN
1309 PRORATE_START = PAY_PROC_PERIOD_START_DATE
1310
1311 IF PRORATE_END was defaulted THEN
1312 PRORATE_END = PAY_PROC_PERIOD_END_DATE
1313
1314 /* Initializing Local variables */
1315
1316 t_schedule_source = '' ''
1317 t_schedule = '' ''
1318 t_return_status = -1
1319 t_return_message = '' ''
1320
1321 hours_in_proration = 0
1322 hours_in_period = 1
1323 earnings_factor = 1
1324 regular_salaried_earnings = 0
1325 actual_hours_worked = 0
1326
1327 hours_in_proration = HOURS_BETWEEN(PRORATE_START
1328 , PRORATE_END
1329 , ''WORK''
1330 ,''N''
1331 ,''BUSY''
1332 ,''US''
1333 ,t_schedule_source
1334 ,t_schedule
1335 ,t_return_status
1336 ,t_return_message
1337 ,''H'')
1338
1339 hours_in_period = HOURS_BETWEEN( PAY_PROC_PERIOD_START_DATE
1340 , PAY_PROC_PERIOD_END_DATE
1341 , ''WORK''
1342 ,''N''
1343 ,''BUSY''
1344 ,''US''
1345 ,t_schedule_source
1346 ,t_schedule
1347 ,t_return_status
1348 ,t_return_message
1349 ,''H'')
1350
1351 earnings_factor = hours_in_proration/hours_in_period
1352 actual_hours_worked = hours_in_proration
1353
1354 IF REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
1355 REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN = 0 THEN
1356 IF Rate WAS NOT DEFAULTED THEN
1357 (hourly_rate = Rate
1358 t_vac_hours_taken = 0
1359 t_vac_pay = 0
1360 t_sick_hours_taken = 0
1361 t_sick_pay = 0
1362
1363 regular_wage_earnings = earnings_factor * ( Rate * hours_in_period )
1364 RATE_PASSED = hourly_rate
1365 )
1366
1367 ELSE
1368 IF Rate_Code WAS NOT DEFAULTED THEN
1369 (hourly_rate = To_Number(Get_Table_Value( RATE_TABLE,
1370 RATE_TABLE_COLUMN,
1371 Rate_Code))
1372 t_vac_hours_taken = 0
1373 t_vac_pay = 0
1374 t_sick_hours_taken = 0
1375 t_sick_pay = 0
1376 RATE_PASSED = hourly_rate
1377
1378 regular_wage_earnings = earnings_factor * ( hourly_rate * hours_in_period )
1379 )
1380
1381 ELSE
1382 (mesg = ''No Hourly Rate or Rate Code entered for this employee''
1383 RETURN mesg
1384 )
1385 ELSE
1386 (regular_wage_earnings = REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN
1387 clear_repl_amt = -1 * REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN
1388 /* WWBug 323639 */
1389 t_vac_hours_taken = 0
1390 t_vac_pay = 0
1391 t_sick_hours_taken = 0
1392 t_sick_pay = 0
1393 )
1394 regular_wage_earnings = regular_wage_earnings
1395 + REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
1396 + REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
1397 /*
1398 At the Business Group and Organization level, termination
1399 rule is determined which all terminated employees will
1400 follow for the payment. All existing payrolls and/or
1401 business groups (prior to July Family Pack 2002)
1402 will be updated with Actual Termination Date for the
1403 termination rule and for new Business Group
1404 or payroll, user can decide whether to use Actual Term date
1405 or Last Standard Process Date. When no term rule is
1406 specified, it defaults to Last Standard Process date.
1407 */
1408
1409 t_sick_pay = calc_sick_pay(PAY_PROC_PERIOD_END_DATE
1410 ,PRORATE_START
1411 ,PRORATE_END
1412 ,hourly_rate
1413 ,t_sick_hours_taken)
1414
1415 t_vac_pay = calc_vac_pay(PAY_PROC_PERIOD_END_DATE
1416 ,PRORATE_START
1417 ,PRORATE_END
1418 ,hourly_rate
1419 ,t_vac_hours_taken)
1420
1421 RED_REG_ADJUST_AMOUNT = 0.05
1422 RED_REG_ADJUST_HOURS = 0.01
1423
1424 /* Replacing with below code for Enabling Proration */
1425 /*
1426 reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
1427 reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
1428 diff_earnings = regular_wage_earnings - reduce_regular_earnings
1429 + RED_REG_ADJUST_AMOUNT
1430 diff_hours = actual_hours_worked - reduce_regular_hours
1431 + RED_REG_ADJUST_HOURS
1432 */
1433
1434 t_reduce_regular_earnings = REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
1435 t_reduce_regular_hours = REDUCE_REGULAR_HOURS_ASG_GRE_RUN
1436
1437 t_return = REDUCED_REGULAR_CALC(PAY_PROC_PERIOD_END_DATE
1438 ,PRORATE_START
1439 ,PRORATE_END
1440 ,t_reduce_regular_earnings
1441 ,t_reduce_regular_hours)
1442
1443 reduce_regular_earnings = t_reduce_regular_earnings
1444 reduce_regular_hours = t_reduce_regular_hours
1445
1446 diff_earnings = regular_wage_earnings - reduce_regular_earnings
1447 + RED_REG_ADJUST_AMOUNT
1448
1449 diff_hours = actual_hours_worked - reduce_regular_hours
1450 + RED_REG_ADJUST_HOURS
1451
1452 /* Reduce Regular Changes Start Here */
1453
1454 IF Timecard_Required = ''N'' AND
1455 reduce_regular_earnings <> 0 THEN
1456 (
1457 /*
1458 * We need to carry over reduce regular Earnings/Hours to the next Pay
1459 * Period if it is more than regular salaried earnings so that we
1460 * never have regular salaried earnings less than ZERO
1461 */
1462 IF diff_earnings >= 0 THEN
1463 (
1464 regular_wage_earnings = regular_wage_earnings - reduce_regular_earnings
1465 if regular_wage_earnings < 0 then
1466 (
1467 regular_wage_earnings = 0
1468 )
1469 )
1470 ELSE
1471 (
1472 /* reduce_regular_earnings = regular_wage_earnings */
1473 regular_wage_earnings = 0
1474 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
1475 )
1476
1477 IF diff_hours >= 0 THEN
1478 (
1479 actual_hours_worked = actual_hours_worked - reduce_regular_hours
1480 if actual_hours_worked < 0 then
1481 (
1482 actual_hours_worked = 0
1483 )
1484 )
1485 ELSE
1486 (
1487 /* reduce_regular_hours = actual_hours_worked */
1488 actual_hours_worked = 0
1489 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
1490 )
1491 )
1492 ELSE
1493 (
1494 reduce_regular_earnings = 0
1495 reduce_regular_hours = 0
1496 )
1497
1498 /* Reduce Regular Changes End Here */
1499
1500 IF regular_wage_earnings < 0 THEN
1501 (IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1502 BG_TERMINATION_TYPE = ''A'' AND
1503 TERMINATED_EMPLOYEE = ''Y'' AND
1504 FINAL_PAY_PROCESSED = ''N'') OR
1505 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1506 TERMINATED_EMPLOYEE = ''Y'' AND
1507 FINAL_PAY_PROCESSED = ''N'') OR
1508 (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1509 BG_TERMINATION_TYPE = ''L'' AND
1510 TERMINATED_EMPLOYEE = ''Y'' AND
1511 LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') OR
1512 (PAYROLL_TERMINATION_TYPE = ''L'' AND
1513 TERMINATED_EMPLOYEE = ''Y'' AND
1514 LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
1515 neg_earn = 0
1516 ELSE
1517 (neg_earn = regular_wage_earnings
1518 regular_wage_earnings = 0
1519 )
1520 )
1521 ELSE
1522
1523 (IF REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
1524 neg_earn = -1 * REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
1525 )
1526 IF REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
1527 clear_addl_amt = -1 * REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
1528 IF t_vac_pay <> 0 THEN
1529 (vac_pay = t_vac_pay
1530 vac_hours_taken = t_vac_hours_taken
1531 regular_wage_earnings = regular_wage_earnings - vac_pay
1532 actual_hours_worked = actual_hours_worked - vac_hours_taken
1533 )
1534 IF t_sick_pay <> 0 THEN
1535 (sick_pay = t_sick_pay
1536
1537 sick_hours_taken = t_sick_hours_taken
1538 regular_wage_earnings = regular_wage_earnings - sick_pay
1539 actual_hours_worked = actual_hours_worked - sick_hours_taken
1540 )
1541 /* Create latest balances */
1542 soe_run = REGULAR_WAGES_ASG_GRE_RUN
1543 soe_ytd = REGULAR_WAGES_ASG_GRE_YTD
1544 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
1545
1546 HOURS_PASSED = actual_hours_worked
1547
1548 IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1549 BG_TERMINATION_TYPE = ''A'' AND
1550 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
1551
1552 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1553 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
1554 ( STOP_ENTRY = ''Y''
1555 if RATE_PASSED = 0 then
1556 ( RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
1557 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
1558 sick_hours_taken, STOP_ENTRY, reduce_regular_earnings,
1559 reduce_regular_hours,mesg
1560 )
1561 else
1562 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
1563 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
1564 sick_hours_taken, STOP_ENTRY,ELEMENT_TYPE_ID_PASSED,
1565 RATE_PASSED,HOURS_PASSED,reduce_regular_earnings,reduce_regular_hours,
1566 mesg
1567 )
1568 )
1569 ELSE
1570 (
1571 if RATE_PASSED = 0 then
1572 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
1573 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
1574 sick_hours_taken, reduce_regular_earnings, reduce_regular_hours,
1575 mesg
1576 )
1577 else
1578 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
1579 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
1580 sick_hours_taken,ELEMENT_TYPE_ID_PASSED,RATE_PASSED,HOURS_PASSED,
1581 reduce_regular_earnings, reduce_regular_hours, mesg
1582 )
1583 )';
1584
1585
1586 l_old_formula_text :=
1587 '/* ***************************************************************
1588 $Header: payusrsrwupg.pkb 120.6 2011/12/21 04:14:53 emunisek ship $
1589 FORMULA NAME: REGULAR_WAGES
1590 FORMULA TYPE: Payroll
1591 Change History
1592 29 Sep 1993 hparicha Created.
1593 14 Oct 1993 hparicha Moved skip rule formulae to separate
1594 file ".SKIP".
1595 30 Nov 1993 hparicha G187
1596 06 Dec 1993 jmychale G305. Added aliases, entered database items
1597 and tidied up formula
1598 07 Jan 1994 jmychale G491. Removed actual hours worked
1599
1600 parameter from Calculate_Period_
1601 Earnings ()
1602 13 Jan 1994 hparicha G497. Reverted calc period earnings to
1603 use ACTUAL_HOURS_WORKED! Used by
1604 Statement of Earnings report. Replaced
1605 TIME_ENTRY_WAGES_COUNT with
1606 LABOR_RECORDING_COUNT for timecard
1607 req''d employees.
1608 24 Feb 1994 hparicha G560. ASS -> ASG; TU -> GRE
1609 24 Feb 1994 hparicha G581. Handles negative earnings.
1610 09 Jun 1994 hparicha G907. New implementation of generated and
1611 startup earnings and deductions using
1612 "<ELE_NAME> Special Features" shadow element
1613 to feed balances and handle Addl/Repl Amounts.
1614 04 Jan 1995 hparicha Vacation/Sick correlation to Regular Pay.
1615 New results need to be passed when vac/sick
1616 pay are present.
1617 04 May 1995 hparicha Defaulted values for PAY_PROC_PERIOD_
1618 START/END_DATE. Default dates should
1619 be obvious when default is used.
1620 10 Jan 1996 hparicha 323639 Major cleanup effort involving
1621 proration and other user
1622 defined functions and formulae changes.
1623 16 Apr 1996 ssdesai Latest balances creation.
1624 25 Apr 1996 hparicha 344018 Added check for USER_ENTERED_TIME.
1625 17 Apr 1996 djeng Changed PAY_PROC_PERIOD_START_DATE, and
1626 PAY_PROC_PERIOD_END_DATE to
1627 PAY_EARNED_START_DATE and PAY_EARNED_END_DATE
1628 13 AUG 1997 Lwhtomps BUG 525859. Payments dimension can not be held
1629 as a latest balance.
1630 21 MAY 2001 ssarma 3 formula result rules added for Hours by rate
1631 calculation. bug#1550323. They are:
1632 ELEMENT_TYPE_ID_PASSED
1633 RATE_PASSED
1634 HOURS_PASSED
1635 24 AUG 2001 pganguly Changed the default for USER_ENTERED_TIME
1636 from Y to N
1637 24-AUG-2002 ekim Changed the logic of Terminated employee.
1638 24-MAY-2005 kvsankar Cheanged the formula to use New Balance
1639 ''Reduce Regular Earnings'' and ''Reduce Regular
1640 Hours'' for Reduce Regular functionality
1641 14-MAR-2006 asasthan Modifed Reduce Regular to use ASG_GRE_RUN dbi
1642 01-Mar-2007 kvsankar Modified the formula to take care of
1643 round off issues in Reduce Regular
1644 26-Mar-2007 kvsankar Modified the formula to use _ASG_GRE_RUN
1645 dimension for Replacement and Additional
1646 balances
1647
1648 --
1649 --
1650 INPUTS: Rate
1651 Rate Code (text)
1652 --
1653 DBI Required: ASG_SALARY_BASIS
1654 TERMINATED_EMPLOYEE
1655 FINAL_PAY_PROCESSED
1656 PAYROLL_TERMINATION_TYPE
1657 BG_TERMINATION_TYPE
1658
1659 LABOR_RECORDING_COUNT
1660 SCL_ASG_US_WORK_SCHEDULE
1661 ASG_HOURS
1662 SCL_ASG_US_TIMECARD_REQUIRED
1663
1664 CURRENT_ELEMENT_TYPE_ID
1665 ******************************************************************
1666 DESCRIPTION:
1667 ******************************************************************
1668 Computes earnings per pay period for hourly employees.
1669 Proration function must be available to determine if employee worked entire
1670 period - earnings will be adjusted accordingly by proration fn to account
1671 for new hire, termination, leave of absence, etc.
1672 *** Hourly handling ***
1673 Regular wages earned per pay period for employees paid by the hour.
1674
1675 Hourly employees can either be "Hourly-Automatic" (ie. timecard not
1676 required) or "Hourly-Timecard" where a timecard is required for pay. The
1677 hourly rate for an employee is entered as the input value for this element.
1678 This rate is used with the number of hours worked to calculate earnings.
1679 Hours worked will be indicated by one of the following:
1680 - time entry or entries (ie. timecard)
1681 - ASGigned Work Schedule
1682 - standard hours entered at the Organization and ASGignment levels.
1683 For an Hourly-Timecard or "timecard required" employee, when a timecard
1684 is not submitted by the payroll input cutoff date - the wages for that
1685 employee will not be calculated and will have to wait for a subsequent
1686 payroll run for processing. When a timecard is submitted for an Hourly-
1687 Automatic employee, then the time entry (or entries) is treated as the source
1688 for Hours - if a rate is entered on the time entry, then this rate is used
1689 along with the hours to compute pay, otherwise the normal rate (Regular
1690 Wages rate) is used for computation.
1691 If this is the Final Pay run for the employee''s ASGignment, then the
1692 Regular Wages element will be discontinued after this run.
1693 ******************************************************************
1694 ALGORITHM:
1695 ******************************************************************
1696 If timecard required and time entries NOT FOUND, then
1697 message=''No timecards entered for Hourly, Timecard Required employee.''
1698 return message
1699 -- NOTE: If tc was req''d and time entries WERE found - then the skip rule
1700 -- for this Regular Salary element would have skipped this processing.
1701
1702
1703 Endif
1704 Call proration function with hourly rate; --> Regular_Wage_Earnings
1705 If this is final pay for employee(ASGignment), then
1706 discontinue further processing of this element -- This is last time.
1707 endif
1708 Return Regular_Wage_Earnings
1709 -- The earnings calculation for hourly employees will primarily be
1710 calculated by the calculate_period_earnings() function. This function
1711 has its'' own hld (in Work Schedules Functionality doc) and lld to be
1712 called calc_period_earnings.lld
1713 ******************************************************************
1714 FORMULA_TEXT:
1715
1716 *******************************************************************/
1717 /* Alias Section */
1718 ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
1719 ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required
1720 /* dbi defaults */
1721 DEFAULT FOR ASG_SALARY_BASIS IS ''NOT ENTERED''
1722 DEFAULT FOR TERMINATED_EMPLOYEE IS ''N''
1723 DEFAULT FOR FINAL_PAY_PROCESSED IS ''N''
1724 DEFAULT FOR PAYROLL_TERMINATION_TYPE IS ''L''
1725 DEFAULT FOR BG_TERMINATION_TYPE IS ''L''
1726 default for LAST_STANDARD_PROCESS_DATE_PROCESSED IS ''N''
1727 DEFAULT FOR LABOR_RECORDING_COUNT IS 0
1728 DEFAULT FOR USER_ENTERED_TIME IS ''N''
1729
1730 default for PAY_PROC_PERIOD_START_DATE is ''1900/01/01 00:00:00'' (DATE)
1731 default for PAY_PROC_PERIOD_END_DATE is ''1900/01/02 00:00:00'' (DATE)
1732 default for PAY_EARNED_START_DATE is ''1900/01/01 00:00:00'' (DATE)
1733 default for PAY_EARNED_END_DATE is ''1900/01/02 00:00:00'' (DATE)
1734
1735 DEFAULT FOR Work_Schedule IS ''NOT ENTERED''
1736 DEFAULT FOR ASG_HOURS IS 0
1737 DEFAULT FOR ASG_FREQ IS ''NOT ENTERED'' /* WWBug 323639 */
1738 DEFAULT FOR Timecard_Required IS ''N''
1739 DEFAULT FOR REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD IS 0
1740 DEFAULT FOR REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN IS 0
1741 DEFAULT FOR REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN IS 0
1742 DEFAULT FOR REGULAR_WAGES_ASG_GRE_RUN IS 0
1743 DEFAULT FOR REGULAR_HOURS_WORKED_ASG_GRE_RUN IS 0
1744 DEFAULT FOR CURRENT_ELEMENT_TYPE_ID IS 0
1745 DEFAULT FOR REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
1746 DEFAULT FOR REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
1747
1748 /* inpval defaults */
1749 DEFAULT FOR Rate IS 0
1750 DEFAULT FOR Rate_Code (text) IS ''NOT ENTERED''
1751
1752 INPUTS ARE Rate,
1753 Rate_Code (text)
1754 /* Updatable Values */
1755 RATE_TABLE = ''WAGE RATES''
1756 RATE_TABLE_COLUMN = ''Wage Rate''
1757
1758 MESG = '' ''
1759 /* Changed for new Termination Rule
1760 IF TERMINATED_EMPLOYEE = ''Y'' AND USER_ENTERED_TIME = ''Y'' THEN
1761 (STOP_ENTRY = ''Y''
1762 mesg = ''Regular Wages being stopped after Final Pay.''
1763 RETURN STOP_ENTRY, mesg
1764 )
1765 */
1766 IF Timecard_Required = ''Y'' AND LABOR_RECORDING_COUNT = 0 THEN
1767 (mesg = ''No timecards entered for Hourly, Timecard Required employee''
1768 RETURN mesg
1769
1770 )
1771
1772 ELEMENT_TYPE_ID_PASSED = CURRENT_ELEMENT_TYPE_ID
1773 RATE_PASSED = 0
1774 HOURS_PASSED = 0
1775
1776 IF REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN WAS DEFAULTED OR
1777 REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN = 0 THEN
1778 IF Rate WAS NOT DEFAULTED THEN
1779 (hourly_rate = Rate
1780 actual_hours_worked = 0
1781 t_vac_hours_taken = 0
1782 t_vac_pay = 0
1783 t_sick_hours_taken = 0
1784 t_sick_pay = 0
1785 regular_wage_earnings = Calc_Period_Earnings (
1786 ASG_SALARY_BASIS,
1787 ''RATE'',
1788 hourly_rate,
1789 PAY_EARNED_START_DATE,
1790 PAY_EARNED_END_DATE,
1791 actual_hours_worked,
1792 t_vac_hours_taken,
1793 t_vac_pay,
1794 t_sick_hours_taken,
1795 t_sick_pay,
1796 ''Y'',
1797 ASG_FREQ)
1798 /*Calculate_Period_Earnings(
1799 ASG_SALARY_BASIS,
1800 ''RATE'',
1801 hourly_rate,
1802 PAY_EARNED_START_DATE,
1803 PAY_EARNED_END_DATE,
1804 Work_Schedule,
1805 ASG_HOURS,
1806 actual_hours_worked,
1807 t_vac_hours_taken,
1808 t_vac_pay,
1809 t_sick_hours_taken,
1810 t_sick_pay,
1811 ''Y'',
1812 ASG_FREQ)*/
1813 /* WWBug 323639 */
1814 RATE_PASSED = hourly_rate
1815 )
1816
1817 ELSE
1818 IF Rate_Code WAS NOT DEFAULTED THEN
1819 (hourly_rate = To_Number(Get_Table_Value( RATE_TABLE,
1820 RATE_TABLE_COLUMN,
1821 Rate_Code))
1822 actual_hours_worked = 0
1823 t_vac_hours_taken = 0
1824 t_vac_pay = 0
1825 t_sick_hours_taken = 0
1826 t_sick_pay = 0
1827 RATE_PASSED = hourly_rate
1828 regular_wage_earnings = Calc_Period_Earnings (
1829 ''HOURLY'',
1830 ''RATE CODE'',
1831 hourly_rate,
1832 PAY_EARNED_START_DATE,
1833 PAY_EARNED_END_DATE,
1834 actual_hours_worked,
1835 t_vac_hours_taken,
1836 t_vac_pay,
1837 t_sick_hours_taken,
1838 t_sick_pay,
1839 ''Y'',
1840 ASG_FREQ)
1841 /*Calculate_Period_Earnings(
1842 ''HOURLY'',
1843 ''RATE CODE'',
1844 hourly_rate,
1845 PAY_EARNED_START_DATE,
1846 PAY_EARNED_END_DATE,
1847 Work_Schedule,
1848 ASG_HOURS,
1849 actual_hours_worked,
1850 t_vac_hours_taken,
1851 t_vac_pay,
1852 t_sick_hours_taken,
1853 t_sick_pay,
1854 ''Y'',
1855 ASG_FREQ)*/
1856 /* WWBug 323639 */
1857 )
1858
1859 ELSE
1860 (mesg = ''No Hourly Rate or Rate Code entered for this employee''
1861 RETURN mesg
1862 )
1863 ELSE
1864 (regular_wage_earnings = REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN
1865 clear_repl_amt = -1 * REGULAR_WAGES_REPLACEMENT_ASG_GRE_RUN
1866 /* WWBug 323639 */
1867 actual_hours_worked = 0
1868 t_vac_hours_taken = 0
1869 t_vac_pay = 0
1870 t_sick_hours_taken = 0
1871 t_sick_pay = 0
1872 )
1873 regular_wage_earnings = regular_wage_earnings
1874 + REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
1875 + REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
1876 /*
1877 At the Business Group and Organization level, termination
1878 rule is determined which all terminated employees will
1879 follow for the payment. All existing payrolls and/or
1880 business groups (prior to July Family Pack 2002)
1881 will be updated with Actual Termination Date for the
1882 termination rule and for new Business Group
1883 or payroll, user can decide whether to use Actual Term date
1884 or Last Standard Process Date. When no term rule is
1885 specified, it defaults to Last Standard Process date.
1886 */
1887
1888 RED_REG_ADJUST_AMOUNT = 0.05
1889 RED_REG_ADJUST_HOURS = 0.01
1890
1891 reduce_regular_earnings = -1 * REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN
1892 reduce_regular_hours = -1 * REDUCE_REGULAR_HOURS_ASG_GRE_RUN
1893 diff_earnings = regular_wage_earnings - reduce_regular_earnings
1894 + RED_REG_ADJUST_AMOUNT
1895 diff_hours = actual_hours_worked - reduce_regular_hours
1896 + RED_REG_ADJUST_HOURS
1897
1898 /* Reduce Regular Changes Start Here */
1899
1900 IF Timecard_Required = ''N'' AND
1901 reduce_regular_earnings <> 0 THEN
1902 (
1903 /*
1904 * We need to carry over reduce regular Earnings/Hours to the next Pay
1905 * Period if it is more than regular salaried earnings so that we
1906 * never have regular salaried earnings less than ZERO
1907 */
1908 IF diff_earnings >= 0 THEN
1909 (
1910 regular_wage_earnings = regular_wage_earnings - reduce_regular_earnings
1911 if regular_wage_earnings < 0 then
1912 (
1913 regular_wage_earnings = 0
1914 )
1915 )
1916 ELSE
1917 (
1918 /* reduce_regular_earnings = regular_wage_earnings */
1919 regular_wage_earnings = 0
1920 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
1921 )
1922
1923 IF diff_hours >= 0 THEN
1924 (
1925 actual_hours_worked = actual_hours_worked - reduce_regular_hours
1926 if actual_hours_worked < 0 then
1927 (
1928 actual_hours_worked = 0
1929 )
1930 )
1931 ELSE
1932 (
1933 /* reduce_regular_hours = actual_hours_worked */
1934 actual_hours_worked = 0
1935 mesg = GET_MESG(''PAY'',''PAY_74069_HIGH_REDUCE_REG_EARN'')
1936 )
1937 )
1938 ELSE
1939 (
1940 reduce_regular_earnings = 0
1941 reduce_regular_hours = 0
1942 )
1943
1944 /* Reduce Regular Changes End Here */
1945
1946 IF regular_wage_earnings < 0 THEN
1947 (IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1948 BG_TERMINATION_TYPE = ''A'' AND
1949 TERMINATED_EMPLOYEE = ''Y'' AND
1950 FINAL_PAY_PROCESSED = ''N'') OR
1951 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1952 TERMINATED_EMPLOYEE = ''Y'' AND
1953 FINAL_PAY_PROCESSED = ''N'') OR
1954 (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1955 BG_TERMINATION_TYPE = ''L'' AND
1956 TERMINATED_EMPLOYEE = ''Y'' AND
1957 LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') OR
1958 (PAYROLL_TERMINATION_TYPE = ''L'' AND
1959 TERMINATED_EMPLOYEE = ''Y'' AND
1960 LAST_STANDARD_PROCESS_DATE_PROCESSED = ''N'') THEN
1961 neg_earn = 0
1962 ELSE
1963 (neg_earn = regular_wage_earnings
1964 regular_wage_earnings = 0
1965 )
1966 )
1967 ELSE
1968
1969 (IF REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD <> 0 THEN
1970 neg_earn = -1 * REGULAR_WAGES_NEG_EARNINGS_ASG_GRE_ITD
1971 )
1972 IF REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN <> 0 THEN
1973 clear_addl_amt = -1 * REGULAR_WAGES_ADDITIONAL_ASG_GRE_RUN
1974 IF t_vac_pay <> 0 THEN
1975 (vac_pay = t_vac_pay
1976 vac_hours_taken = t_vac_hours_taken
1977 regular_wage_earnings = regular_wage_earnings - vac_pay
1978 actual_hours_worked = actual_hours_worked - vac_hours_taken
1979 )
1980 IF t_sick_pay <> 0 THEN
1981 (sick_pay = t_sick_pay
1982
1983 sick_hours_taken = t_sick_hours_taken
1984 regular_wage_earnings = regular_wage_earnings - sick_pay
1985 actual_hours_worked = actual_hours_worked - sick_hours_taken
1986 )
1987 /* Create latest balances */
1988 soe_run = REGULAR_WAGES_ASG_GRE_RUN
1989 soe_ytd = REGULAR_WAGES_ASG_GRE_YTD
1990 soe_hrs = REGULAR_HOURS_WORKED_ASG_GRE_RUN
1991
1992 HOURS_PASSED = actual_hours_worked
1993
1994 IF (PAYROLL_TERMINATION_TYPE WAS DEFAULTED AND
1995 BG_TERMINATION_TYPE = ''A'' AND
1996 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') OR
1997
1998 (PAYROLL_TERMINATION_TYPE = ''A'' AND
1999 TERMINATED_EMPLOYEE = ''Y'' AND FINAL_PAY_PROCESSED = ''N'') THEN
2000 ( STOP_ENTRY = ''Y''
2001 if RATE_PASSED = 0 then
2002 ( RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
2003 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
2004 sick_hours_taken, STOP_ENTRY, reduce_regular_earnings,
2005 reduce_regular_hours,mesg
2006 )
2007 else
2008 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
2009 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
2010 sick_hours_taken, STOP_ENTRY,ELEMENT_TYPE_ID_PASSED,
2011 RATE_PASSED,HOURS_PASSED,reduce_regular_earnings,reduce_regular_hours,
2012 mesg
2013 )
2014 )
2015 ELSE
2016 (
2017 if RATE_PASSED = 0 then
2018 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
2019 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
2020 sick_hours_taken, reduce_regular_earnings, reduce_regular_hours,
2021 mesg
2022 )
2023 else
2024 (RETURN regular_wage_earnings, actual_hours_worked, clear_repl_amt,
2025 clear_addl_amt, neg_earn, vac_pay, vac_hours_taken, sick_pay,
2026 sick_hours_taken,ELEMENT_TYPE_ID_PASSED,RATE_PASSED,HOURS_PASSED,
2027 reduce_regular_earnings, reduce_regular_hours, mesg
2028 )
2029 )';
2030
2031 END IF; -- Regular Salary / Regular Wages
2032
2033 IF p_mode = 'NEW' THEN
2034
2035 hr_utility.trace('Updating Formula Text in Mode := ' || p_mode);
2036
2037 BEGIN
2038 update ff_formulas_f
2039 set formula_text = l_new_formula_text
2040 where formula_name = l_formula_name
2041 and business_group_id IS NULL
2042 and legislation_code = 'US';
2043
2044 hr_utility.trace('Formula Updated.');
2045 COMMIT;
2046 hr_utility.trace('Formula Update Commited!');
2047 EXCEPTION
2048 WHEN OTHERS THEN
2049 hr_utility.trace('SQLCODE := ' || TO_CHAR(SQLCODE));
2050 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2051 END;
2052
2053 ELSIF p_mode = 'OLD' THEN
2054
2055 hr_utility.trace('Updating Formula Text in Mode := ' || p_mode);
2056 BEGIN
2057 update ff_formulas_f
2058 set formula_text = l_old_formula_text
2059 where formula_name = l_formula_name
2060 and business_group_id IS NULL
2061 and legislation_code = 'US';
2062 hr_utility.trace('Formula Updated.');
2063 COMMIT;
2064 hr_utility.trace('Formula Update Commited!');
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 hr_utility.trace('SQLCODE := ' || TO_CHAR(SQLCODE));
2068 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2069 END;
2070
2071 END IF;
2072
2073 /*
2074 * Removing Entry from ff_compiled_info and ff_fdi_usages for both the formulas
2075 * that are created for the seeded elements. Customer needs to recompile all the
2076 * formulae after running this process.
2077 */
2078 delete
2079 from ff_compiled_info_f
2080 where formula_id = l_formula_id;
2081
2082 delete
2083 from ff_fdi_usages_f
2084 where formula_id = l_formula_id;
2085
2086 hr_utility.trace('Leaving ' || gv_package_name || '.modify_formula_text');
2087
2088 END modify_formula_text;
2089
2090 /*****************************************************************************
2091 Name : create_proration_group
2092 Description : This Function creates a Proration Group called
2093 'Proration Group for Regular Salary'.
2094 *****************************************************************************/
2095 FUNCTION create_proration_group
2096 RETURN NUMBER IS
2097
2098 TYPE typ_col_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
2099 col_name_tbl typ_col_name;
2100
2101 CURSOR get_dated_table_id(p_application_id IN NUMBER
2102 ,p_table_name IN VARCHAR2) IS
2103 SELECT dated_table_id
2104 FROM pay_dated_tables pdt
2105 WHERE pdt.application_id = p_application_id
2106 AND pdt.table_name = p_table_name
2107 AND pdt.legislation_code IS NULL
2108 AND pdt.business_group_id IS NULL;
2109
2110 ln_event_group_id pay_event_groups.event_group_id%TYPE;
2111 ln_event_grp_ovn pay_event_groups.object_version_number%TYPE;
2112 ln_eef_dt_ovn pay_datetracked_events.object_version_number%TYPE;
2113 ln_eevf_dt_ovn pay_datetracked_events.object_version_number%TYPE;
2114 ln_paaf_dt_ovn pay_datetracked_events.object_version_number%TYPE;
2115
2116 ln_eef_dt_event_id pay_datetracked_events.datetracked_event_id%TYPE;
2117 ln_eevf_dt_event_id pay_datetracked_events.datetracked_event_id%TYPE;
2118 ln_paaf_dt_event_id pay_datetracked_events.datetracked_event_id%TYPE;
2119
2120 ln_eef_dt_tbl_id pay_dated_tables.dated_table_id%TYPE;
2121 ln_eevf_dt_tbl_id pay_dated_tables.dated_table_id%TYPE;
2122 ln_paaf_dt_tbl_id pay_dated_tables.dated_table_id%TYPE;
2123
2124 ln_index NUMBER;
2125
2126 BEGIN
2127 hr_utility.trace('Entering into create_proration_group.');
2128 hr_utility.trace('col_name_tbl.count() := ' || col_name_tbl.count());
2129
2130 IF col_name_tbl.count() > 0 THEN
2131 col_name_tbl.delete;
2132 END IF;
2133
2134 hr_utility.trace('Before Event Group Creation.');
2135 pay_event_groups_api.create_event_group(FALSE
2136 ,fnd_date.canonical_to_date('0001/01/01')
2137 ,'Proration Group for Regular Salary'
2138 ,'P'
2139 ,'P'
2140 ,NULL
2141 ,'US'
2142 ,ln_event_group_id
2143 ,ln_event_grp_ovn
2144 ,NULL);
2145
2146 hr_utility.trace('After Event Group Creation.');
2147
2148 OPEN get_dated_table_id(801, 'PAY_ELEMENT_ENTRIES_F');
2149 FETCH get_dated_table_id INTO ln_eef_dt_tbl_id;
2150 CLOSE get_dated_table_id;
2151 hr_utility.trace('ln_eef_dt_tbl_id := ' || ln_eef_dt_tbl_id);
2152
2153 ln_index := 1;
2154 col_name_tbl(ln_index) := 'EFFECTIVE_START_DATE';
2155 ln_index := ln_index + 1;
2156 col_name_tbl(ln_index) := 'DATE_EARNED';
2157 ln_index := ln_index + 1;
2158 col_name_tbl(ln_index) := 'ELEMENT_TYPE_ID';
2159 ln_index := ln_index + 1;
2160 col_name_tbl(ln_index) := 'EFFECTIVE_END_DATE';
2161
2162 hr_utility.trace('col_name_tbl.count() := ' || col_name_tbl.count());
2163 hr_utility.trace('Before Creating Date-Tracked Events for PAY_ELEMENT_ENTRIES_F.');
2164
2165 FOR i IN 1..col_name_tbl.count()
2166 LOOP
2167 hr_utility.trace('Within create_datetracked_event i := ' || i);
2168 hr_utility.trace('ln_event_group_id := ' || ln_event_group_id);
2169 hr_utility.trace('ln_eef_dt_tbl_id := ' || ln_eef_dt_tbl_id);
2170 hr_utility.trace('col_name_tbl(i) := ' || col_name_tbl(i));
2171
2172 pay_datetracked_events_api.create_datetracked_event(FALSE
2173 ,fnd_date.canonical_to_date('0001/01/01')
2174 ,ln_event_group_id
2175 ,ln_eef_dt_tbl_id
2176 ,'U'
2177 ,col_name_tbl(i)
2178 ,NULL
2179 ,'US'
2180 ,NULL
2181 ,ln_eef_dt_event_id
2182 ,ln_eef_dt_ovn);
2183 END LOOP;
2184 hr_utility.trace('After Creating Date-Tracked Events for PAY_ELEMENT_ENTRIES_F.');
2185
2186 OPEN get_dated_table_id(801, 'PAY_ELEMENT_ENTRY_VALUES_F');
2187 FETCH get_dated_table_id INTO ln_eevf_dt_tbl_id;
2188 CLOSE get_dated_table_id;
2189
2190 hr_utility.trace('ln_eevf_dt_tbl_id := ' || ln_eevf_dt_tbl_id);
2191
2192 col_name_tbl.delete;
2193 ln_index := 1;
2194 col_name_tbl(ln_index) := 'ELEMENT_ENTRY_VALUE_ID';
2195 ln_index := ln_index + 1;
2196 col_name_tbl(ln_index) := 'ELEMENT_ENTRY_ID';
2197 ln_index := ln_index + 1;
2198 col_name_tbl(ln_index) := 'SCREEN_ENTRY_VALUE';
2199 ln_index := ln_index + 1;
2200 col_name_tbl(ln_index) := 'EFFECTIVE_END_DATE';
2201 ln_index := ln_index + 1;
2202 col_name_tbl(ln_index) := 'EFFECTIVE_START_DATE';
2203 ln_index := ln_index + 1;
2204 col_name_tbl(ln_index) := 'INPUT_VALUE_ID';
2205
2206 hr_utility.trace('col_name_tbl.count() := ' || col_name_tbl.count());
2207 hr_utility.trace('Before Creating Date-Tracked Events for PAY_ELEMENT_ENTRY_VALUES_F.');
2208
2209 FOR i IN 1..col_name_tbl.count()
2210 LOOP
2211 hr_utility.trace('Within create_datetracked_event i := ' || i);
2212 hr_utility.trace('ln_event_group_id := ' || ln_event_group_id);
2213 hr_utility.trace('ln_eevf_dt_tbl_id := ' || ln_eevf_dt_tbl_id);
2214 hr_utility.trace('col_name_tbl(i) := ' || col_name_tbl(i));
2215
2216 pay_datetracked_events_api.create_datetracked_event(FALSE
2217 ,fnd_date.canonical_to_date('0001/01/01')
2218 ,ln_event_group_id
2219 ,ln_eevf_dt_tbl_id
2220 ,'U'
2221 ,col_name_tbl(i)
2222 ,NULL
2223 ,'US'
2224 ,NULL
2225 ,ln_eevf_dt_event_id
2226 ,ln_eevf_dt_ovn);
2227 END LOOP;
2228
2229 hr_utility.trace('After Creating Date-Tracked Events for PAY_ELEMENT_ENTRY_VALUES_F.');
2230
2231 OPEN get_dated_table_id(800, 'PER_ALL_ASSIGNMENTS_F');
2232 FETCH get_dated_table_id INTO ln_paaf_dt_tbl_id;
2233 CLOSE get_dated_table_id;
2234
2235 hr_utility.trace('ln_paaf_dt_tbl_id := ' || ln_paaf_dt_tbl_id);
2236 hr_utility.trace('Before Creating Date-Tracked Events for PER_ALL_ASSIGNMENTS_F');
2237
2238 col_name_tbl.delete;
2239 ln_index := 1;
2240 col_name_tbl(ln_index) := 'PAY_BASIS_ID';
2241 ln_index := ln_index + 1;
2242 col_name_tbl(ln_index) := 'SOFT_CODING_KEYFLEX_ID';
2243 ln_index := ln_index + 1;
2244 col_name_tbl(ln_index) := 'PAYROLL_ID';
2245 ln_index := ln_index + 1;
2246 col_name_tbl(ln_index) := 'NORMAL_HOURS';
2247 ln_index := ln_index + 1;
2248 col_name_tbl(ln_index) := 'ASSIGNMENT_STATUS_TYPE_ID';
2249
2250 hr_utility.trace('col_name_tbl.count() := ' || col_name_tbl.count());
2251 hr_utility.trace('Before Creating Date-Tracked Events for PER_ALL_ASSIGNMENTS_F.');
2252
2253 FOR i IN 1..col_name_tbl.count()
2254 LOOP
2255 hr_utility.trace('Within create_datetracked_event i := ' || i);
2256 hr_utility.trace('ln_event_group_id := ' || ln_event_group_id);
2257 hr_utility.trace('ln_paaf_dt_tbl_id := ' || ln_paaf_dt_tbl_id);
2258 hr_utility.trace('col_name_tbl(i) := ' || col_name_tbl(i));
2259
2260 pay_datetracked_events_api.create_datetracked_event(FALSE
2261 ,fnd_date.canonical_to_date('0001/01/01')
2262 ,ln_event_group_id
2263 ,ln_paaf_dt_tbl_id
2264 ,'U'
2265 ,col_name_tbl(i)
2266 ,NULL
2267 ,'US'
2268 ,NULL
2269 ,ln_paaf_dt_event_id
2270 ,ln_paaf_dt_ovn);
2271 END LOOP;
2272 hr_utility.trace('After Creating Date-Tracked Events for PER_ALL_ASSIGNMENTS_F.');
2273 COMMIT;
2274 hr_utility.trace('Returning ln_event_group_id := ' || ln_event_group_id);
2275 RETURN ln_event_group_id;
2276
2277 END create_proration_group;
2278
2279 /*****************************************************************************
2280 Name : delete_proration_group
2281 Description : This Function deletes the Proration Group called
2282 'Proration Group for Regular Salary'.
2283 *****************************************************************************/
2284
2285 PROCEDURE delete_proration_group IS
2286
2287 CURSOR get_proration_event_grp(cp_event_grp_nm IN VARCHAR2) IS
2288 SELECT event_group_id
2289 ,object_version_number
2290 FROM pay_event_groups peg
2291 WHERE peg.event_group_name = cp_event_grp_nm
2292 AND peg.event_group_type = 'P'
2293 AND peg.proration_type = 'P'
2294 AND peg.legislation_code = 'US'
2295 AND peg.business_group_id IS NULL;
2296
2297 ln_event_grp_id pay_event_groups.event_group_id%TYPE;
2298 ln_obj_ver_num pay_event_groups.object_version_number%TYPE;
2299
2300 BEGIN
2301
2302 hr_utility.trace('Entered into pay_us_rsrw_upgrev.delete_proration_group');
2303
2304 open get_proration_event_grp('Proration Group for Regular Salary');
2305 fetch get_proration_event_grp into ln_event_grp_id, ln_obj_ver_num;
2306 close get_proration_event_grp;
2307
2308 hr_utility.trace('ln_event_grp_id := ' || ln_event_grp_id);
2309 hr_utility.trace('ln_obj_ver_num := ' || ln_obj_ver_num);
2310
2311 delete from pay_datetracked_events
2312 where event_group_id = ln_event_grp_id;
2313
2314 pay_event_groups_api.delete_event_group(p_event_group_id => ln_event_grp_id
2315 ,p_object_version_number => ln_obj_ver_num);
2316
2317 commit;
2318 END delete_proration_group;
2319
2320 /*****************************************************************************
2321 Name : upgrade_reg_salarywages
2322
2323 Description : This procedure is called from the Concurrent Request. Based on
2324 element name passed in as a parameter, we will execute the
2325 procedure that will migrate the element in ine request.
2326 *****************************************************************************/
2327 PROCEDURE upgrade_reg_salarywages(errbuf out nocopy varchar2
2328 ,retcode out nocopy number)
2329 IS
2330
2331 -- Get the element type id
2332
2333 CURSOR get_ele_typ_id(cp_ele_name IN VARCHAR2) IS
2334 SELECT element_type_id
2335 ,element_name
2336 ,proration_group_id
2337 FROM pay_element_types_f
2338 WHERE element_name = cp_ele_name
2339 AND legislation_code = 'US'
2340 AND business_group_id IS NULL
2341 AND element_information_category = 'US_EARNINGS'
2342 AND element_information1 = 'REG';
2343
2344 -- Get Upgrade Status
2345 CURSOR get_upg_status is
2346 select pus.status
2347 from pay_upgrade_definitions pud
2348 ,pay_upgrade_status pus
2349 where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
2350 and pud.legislation_code = 'US'
2351 and pud.upgrade_definition_id = pus.upgrade_definition_id
2352 and pus.legislation_code = 'US';
2353
2354 -- Get Proration Event Group
2355 CURSOR get_proration_event_grp(cp_event_grp_nm IN VARCHAR2) IS
2356 SELECT event_group_id
2357 FROM pay_event_groups peg
2358 WHERE peg.event_group_name = cp_event_grp_nm
2359 AND peg.event_group_type = 'P'
2360 AND peg.proration_type = 'P'
2361 AND peg.legislation_code = 'US'
2362 AND peg.business_group_id IS NULL;
2363
2364 -- Local Variable Declaration
2365 l_date_of_mig date;
2366 ln_proration_event_grp_id pay_event_groups.event_group_id%TYPE;
2367 ln_rs_ele_typ_id number;
2368 lv_rs_ele_name pay_element_types_f.element_name%TYPE;
2369 ln_rs_proration_grp_id pay_element_types_f.proration_group_id%TYPE;
2370 ln_rw_ele_typ_id number;
2371 lv_rw_ele_name pay_element_types_f.element_name%TYPE;
2372 ln_rw_proration_grp_id pay_element_types_f.proration_group_id%TYPE;
2373 lv_upg_status varchar2(30);
2374 ln_dbg_step number;
2375
2376 begin
2377
2378 --hr_utility.trace_on(NULL, 'Oracle');
2379
2380 /*
2381 * Initialization Code
2382 */
2383 gv_package_name := 'pay_us_rsrw_upgrev';
2384 -- Initialise Variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
2385
2386 /*
2387 * Initial Trace
2388 */
2389
2390 hr_utility.trace('Entering ' || gv_package_name || '.upgrade_regular_salary');
2391
2392 /*
2393 * Legislation Level Migration
2394 */
2395
2396 l_date_of_mig := fnd_date.canonical_to_date(trunc(sysdate));
2397 hr_utility.trace('Date of Migration ' || l_date_of_mig);
2398
2399 /*
2400 * STEP 1
2401 */
2402
2403 open get_ele_typ_id('Regular Salary');
2404 fetch get_ele_typ_id into ln_rs_ele_typ_id, lv_rs_ele_name, ln_rs_proration_grp_id;
2405 close get_ele_typ_id;
2406
2407 open get_ele_typ_id('Regular Wages');
2408 fetch get_ele_typ_id into ln_rw_ele_typ_id, lv_rw_ele_name, ln_rw_proration_grp_id;
2409 close get_ele_typ_id;
2410
2411 hr_utility.trace('ln_rs_ele_typ_id := ' || ln_rs_ele_typ_id);
2412 hr_utility.trace('ln_rs_proration_grp_id := ' || ln_rs_proration_grp_id);
2413 hr_utility.trace('ln_rw_ele_typ_id := ' || ln_rw_ele_typ_id);
2414 hr_utility.trace('ln_rw_proration_grp_id := ' || ln_rw_proration_grp_id);
2415
2416 lv_upg_status := NULL;
2417
2418 open get_upg_status;
2419 fetch get_upg_status into lv_upg_status;
2420 close get_upg_status;
2421
2422 hr_utility.trace('lv_upg_status := ' || lv_upg_status);
2423
2424 IF nvl(lv_upg_status, 'N') <> 'C' THEN
2425 /*
2426 * STEP 2
2427 */
2428
2429 OPEN get_proration_event_grp('Proration Group for Regular Salary');
2430 FETCH get_proration_event_grp INTO ln_proration_event_grp_id;
2431
2432 IF get_proration_event_grp%FOUND THEN
2433 delete_proration_group();
2434 END IF;
2435 CLOSE get_proration_event_grp;
2436
2437 ln_proration_event_grp_id := create_proration_group();
2438 commit;
2439
2440 hr_utility.trace('ln_proration_event_grp_id := ' || ln_proration_event_grp_id);
2441
2442 /* Update Proration Group */
2443
2444 BEGIN
2445
2446 ln_dbg_step := 1;
2447
2448 update pay_element_types_f
2449 set proration_group_id = ln_proration_event_grp_id
2450 where element_name in ('Regular Salary', 'Regular Wages')
2451 AND business_group_id IS NULL
2452 AND legislation_code = 'US'
2453 AND element_information_category = 'US_EARNINGS'
2454 AND element_information1 = 'REG';
2455
2456 /* Insert records into pay_upgrade_definitions and pay_upgrade_status */
2457
2458 ln_dbg_step := 2;
2459
2460 insert into pay_upgrade_definitions(UPGRADE_DEFINITION_ID
2461 ,SHORT_NAME
2462 ,NAME
2463 ,LEGISLATION_CODE
2464 ,DESCRIPTION
2465 ,UPGRADE_LEVEL
2466 ,CRITICALITY
2467 ,THREADING_LEVEL
2468 ,FAILURE_POINT
2469 ,LEGISLATIVELY_ENABLED
2470 ,UPGRADE_METHOD
2471 ,UPGRADE_PROCEDURE
2472 ,LAST_UPDATE_DATE
2473 ,LAST_UPDATED_BY
2474 ,LAST_UPDATE_LOGIN
2475 ,CREATED_BY
2476 ,CREATION_DATE
2477 ,ADDITIONAL_INFO)
2478 select PAY_UPGRADE_DEFINITIONS_S.nextval
2479 ,'US_REG_EARNINGS_UPGRADE'
2480 ,'Upgrade Regular Earnings Elements for all US Business Groups'
2481 ,'US'
2482 ,'Upgrade Regular Earnings Elements for all US Business Groups'
2483 ,'L'
2484 ,'R'
2485 ,'PET'
2486 ,'N'
2487 ,'N'
2488 ,'PYUGEN'
2489 ,'pay_us_rsrw_upgrev.upgrade_reg_salarywages'
2490 ,sysdate
2491 ,1
2492 ,-1
2493 ,1
2494 ,sysdate
2495 ,'Run through separate conc program' from sys.dual;
2496
2497 ln_dbg_step := 3;
2498
2499 insert into pay_upgrade_status(UPGRADE_DEFINITION_ID
2500 ,STATUS
2501 ,LEGISLATION_CODE)
2502 select PAY_UPGRADE_DEFINITIONS_S.currval
2503 ,'C'
2504 ,'US' from sys.dual;
2505
2506 EXCEPTION
2507 WHEN OTHERS THEN
2508 hr_utility.trace('Error Occured in Step = ' || ln_dbg_step || ', ' || 'SQLCODE := ' || TO_CHAR(SQLCODE));
2509 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2510 END;
2511 COMMIT;
2512 hr_utility.trace('Insertion Commited!');
2513
2514 ELSIF nvl(lv_upg_status, 'N') = 'C'
2515 AND (ln_rs_proration_grp_id IS NULL OR ln_rw_proration_grp_id IS NULL) THEN
2516
2517 /* Following scenario will occur when hrglobal is run
2518 after running Upgrade seeded Regular Earnings
2519 */
2520
2521 OPEN get_proration_event_grp('Proration Group for Regular Salary');
2522 FETCH get_proration_event_grp INTO ln_proration_event_grp_id;
2523
2524 IF get_proration_event_grp%NOTFOUND THEN
2525 ln_proration_event_grp_id := create_proration_group();
2526 COMMIT;
2527 END IF;
2528 CLOSE get_proration_event_grp;
2529
2530 hr_utility.trace('ln_proration_event_grp_id := ' || ln_proration_event_grp_id);
2531
2532 /* Update Proration Group */
2533
2534 BEGIN
2535
2536 ln_dbg_step := 4;
2537
2538 update pay_element_types_f
2539 set proration_group_id = ln_proration_event_grp_id
2540 where element_name in ('Regular Salary', 'Regular Wages')
2541 AND business_group_id IS NULL
2542 AND legislation_code = 'US'
2543 AND element_information_category = 'US_EARNINGS'
2544 AND element_information1 = 'REG';
2545
2546 EXCEPTION
2547 WHEN OTHERS THEN
2548 hr_utility.trace('Error Occured in Step = ' || ln_dbg_step || ', ' || 'SQLCODE := ' || TO_CHAR(SQLCODE));
2549 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2550 END;
2551 COMMIT;
2552 hr_utility.trace('Update of proration group Commited!');
2553
2554 ELSE
2555 fnd_file.put_line(FND_FILE.LOG,'Elements ''Regular Salary'' and ''Regular Wages'' have already been upgraded for Legislation ''US''.');
2556 hr_utility.trace('Elements ''Regular Salary'' and ''Regular Wages'' have already been upgraded for Legislation ''US''.');
2557 END IF;
2558
2559 fnd_file.put_line(FND_FILE.LOG, 'Regular Salary and Regular Wages have been successfully upgraded for ''US''.');
2560
2561 hr_utility.trace('Leaving ' || gv_package_name || '.upgrade_reg_salarywages');
2562
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 fnd_file.put_line(FND_FILE.LOG, '''Regular Salary''/''Regular Wages'' Element upgradation failed for Legislation ''US''.');
2566 hr_utility.raise_error;
2567 hr_utility.trace('SQLCODE := ' || TO_CHAR(SQLCODE));
2568 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2569 end upgrade_reg_salarywages;
2570
2571 /*****************************************************************************
2572 Name : revert_upg_reg_salarywages
2573
2574 Description : This Procedure is responsible for Reverting Back the Upgradation
2575 of Seeded Earnings Elements "Regular Salary" and "Regula Wages"
2576 done by the earlier Upgradation Process. This is called by
2577 Concurrent Program "Revert back upgradation of Seeded Earnings
2578 Elements for US"
2579 *****************************************************************************/
2580 PROCEDURE revert_upg_reg_salarywages(errbuf out nocopy varchar2
2581 ,retcode out nocopy number) IS
2582 -- Get the element type id
2583
2584 CURSOR get_ele_typ_id(cp_ele_name IN VARCHAR2) IS
2585 SELECT element_type_id
2586 ,element_name
2587 ,proration_group_id
2588 FROM pay_element_types_f
2589 WHERE element_name = cp_ele_name
2590 AND legislation_code = 'US'
2591 AND business_group_id IS NULL
2592 AND element_information_category = 'US_EARNINGS'
2593 AND element_information1 = 'REG';
2594
2595 -- Get Upgrade Status
2596 CURSOR get_upg_status is
2597 select pus.status
2598 ,pud.upgrade_definition_id
2599 from pay_upgrade_definitions pud
2600 ,pay_upgrade_status pus
2601 where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
2602 and pud.legislation_code = 'US'
2603 and pud.upgrade_definition_id = pus.upgrade_definition_id
2604 and pus.legislation_code = 'US';
2605
2606 -- Local Variable Declaration
2607 l_date_of_mig date;
2608 ln_rs_ele_typ_id number;
2609 lv_rs_ele_name pay_element_types_f.element_name%TYPE;
2610 ln_rs_proration_grp_id pay_element_types_f.proration_group_id%TYPE;
2611 ln_rw_ele_typ_id number;
2612 lv_rw_ele_name pay_element_types_f.element_name%TYPE;
2613 ln_rw_proration_grp_id pay_element_types_f.proration_group_id%TYPE;
2614 ln_proration_event_grp_id pay_event_groups.event_group_id%TYPE;
2615 lv_upg_status varchar2(30);
2616 ln_upg_defn_id NUMBER;
2617 ln_dbg_step NUMBER;
2618
2619 begin
2620 /*
2621 * Initialization Code
2622 */
2623 gv_package_name := 'pay_us_rsrw_upgrev';
2624 -- Initialise Variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
2625
2626 /*
2627 * Initial Trace
2628 */
2629 hr_utility.trace('Entering ' || gv_package_name || '.revert_upg_reg_salary');
2630
2631 /*
2632 * Legislation Level Migration
2633 */
2634
2635 l_date_of_mig := fnd_date.canonical_to_date(trunc(sysdate));
2636 hr_utility.trace('Date of Migration ' || l_date_of_mig);
2637
2638 /*
2639 * STEP 1
2640 */
2641
2642 open get_ele_typ_id('Regular Salary');
2643 fetch get_ele_typ_id into ln_rs_ele_typ_id, lv_rs_ele_name, ln_rs_proration_grp_id;
2644 close get_ele_typ_id;
2645
2646 open get_ele_typ_id('Regular Wages');
2647 fetch get_ele_typ_id into ln_rw_ele_typ_id, lv_rw_ele_name, ln_rw_proration_grp_id;
2648 close get_ele_typ_id;
2649
2650 hr_utility.trace('ln_rs_ele_typ_id := ' || ln_rs_ele_typ_id);
2651 hr_utility.trace('ln_rs_proration_grp_id := ' || ln_rs_proration_grp_id);
2652 hr_utility.trace('ln_rw_ele_typ_id := ' || ln_rw_ele_typ_id);
2653 hr_utility.trace('ln_rw_proration_grp_id := ' || ln_rw_proration_grp_id);
2654
2655 lv_upg_status := NULL;
2656
2657 open get_upg_status;
2658 fetch get_upg_status into lv_upg_status, ln_upg_defn_id;
2659 close get_upg_status;
2660
2661 hr_utility.trace('lv_upg_status := ' || lv_upg_status);
2662 hr_utility.trace('ln_upg_defn_id := ' || ln_upg_defn_id);
2663
2664 IF nvl(lv_upg_status, 'N') = 'C' THEN
2665 /*
2666 * STEP 2
2667 */
2668
2669 BEGIN
2670
2671 /* Update Proration Group */
2672
2673 ln_dbg_step := 1;
2674
2675 update pay_element_types_f
2676 set proration_group_id = NULL
2677 where element_name in ('Regular Salary', 'Regular Wages')
2678 and business_group_id is null
2679 and legislation_code = 'US'
2680 AND element_information_category = 'US_EARNINGS'
2681 AND element_information1 = 'REG';
2682
2683 ln_dbg_step := 2;
2684
2685 delete from pay_upgrade_status
2686 where upgrade_definition_id = ln_upg_defn_id;
2687
2688 ln_dbg_step := 3;
2689
2690 delete from pay_upgrade_definitions
2691 where upgrade_definition_id = ln_upg_defn_id
2692 and short_name like 'US_REG_EARNINGS_UPGRADE'
2693 and legislation_code = 'US';
2694
2695
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698 hr_utility.trace('Error Occured in Step = ' || ln_dbg_step || ', ' || 'SQLCODE := ' || TO_CHAR(SQLCODE));
2699 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2700 END;
2701
2702 COMMIT;
2703 hr_utility.trace('Update/Delete Commited!');
2704
2705 ELSE
2706 fnd_file.put_line(FND_FILE.LOG, 'Elements Regular Salary and Regular Wages have not been upgraded for Legislation ''US''.');
2707 hr_utility.trace('Elements Regular Salary and Regular Wages have not been upgraded for Legislation ''US''.');
2708 END IF;
2709
2710 fnd_file.put_line(FND_FILE.LOG, '''Regular Salary'' and ''Regular Wages'' has been successfully reverted back for ''US''.');
2711 hr_utility.trace('Leaving ' || gv_package_name || '.revert_upg_reg_salarywages');
2712
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 fnd_file.put_line(FND_FILE.LOG, 'Reverting back upgradation of ''Regular Salary''/''Regular Wages'' Element failed for Legislation ''US''.');
2716 hr_utility.raise_error;
2717 hr_utility.trace('SQLCODE := ' || TO_CHAR(SQLCODE));
2718 hr_utility.trace('SQLERRM := ' || SUBSTR(SQLERRM, 1, 200));
2719
2720 END revert_upg_reg_salarywages;
2721
2722 /*****************************************************************************
2723 Name : get_upgrade_flag
2724
2725 Description : This Function checks record from pay_upgrade_status and
2726 pay_upgrade_definitions tables for Upgrade of seeded
2727 Regular Earnings elements "Regular Salary" and "Regular
2728 Wages" and return 'Y' or 'N' to be used by respective
2729 Fast Formula to determine what logic is to be used.
2730 *****************************************************************************/
2731
2732 FUNCTION get_upgrade_flag(p_ctx_ele_typ_id IN NUMBER)
2733 RETURN VARCHAR2 IS
2734
2735 -- Get Upgrade Status
2736 CURSOR get_upg_status is
2737 select pus.status
2738 ,pud.upgrade_definition_id
2739 from pay_upgrade_definitions pud
2740 ,pay_upgrade_status pus
2741 where pud.short_name = 'US_REG_EARNINGS_UPGRADE'
2742 and pud.legislation_code = 'US'
2743 and pud.upgrade_definition_id = pus.upgrade_definition_id
2744 and pus.legislation_code = 'US';
2745
2746 lv_upg_flag VARCHAR2(20);
2747 lv_upg_status varchar2(30);
2748 ln_upg_defn_id number;
2749
2750 BEGIN
2751
2752 hr_utility.trace('Entered into pay_us_rsrw_upgrev.get_upgrade_flag');
2753
2754 lv_upg_status := NULL;
2755 ln_upg_defn_id := NULL;
2756
2757 open get_upg_status;
2758 fetch get_upg_status into lv_upg_status, ln_upg_defn_id;
2759 close get_upg_status;
2760
2761 hr_utility.trace('lv_upg_status := ' || lv_upg_status);
2762 hr_utility.trace('ln_upg_defn_id := ' || ln_upg_defn_id);
2763
2764 lv_upg_flag := 'N';
2765
2766 if NVL(lv_upg_status, 'N') = 'C' then
2767 lv_upg_flag := 'Y';
2768 else
2769 lv_upg_flag := 'N';
2770 end if;
2771
2772 hr_utility.trace('Before returning lv_upg_flag := ' || lv_upg_flag);
2773 return lv_upg_flag;
2774
2775 END get_upgrade_flag;
2776
2777 /*****************************************************************************
2778 Name : get_payprd_per_fiscal_yr
2779
2780 Description : This Function returns number of pay periods in the current
2781 fiscal year. This can be different from standard number
2782 of pay periods per fiscal year especially in case of
2783 "Weekly" and "Bi-Weekly" payroll.
2784 *****************************************************************************/
2785
2786 FUNCTION get_payprd_per_fiscal_yr(p_ctx_bg_id in number
2787 ,p_ctx_payroll_id in number
2788 ,p_eletyp_ctx_id in number
2789 ,p_period_end_date in date) RETURN NUMBER IS
2790
2791
2792 CURSOR csr_get_ele_xtra_info(cp_eletyp_ctx_id in number)
2793 IS
2794 SELECT petei.eei_information11
2795 FROM pay_element_types_f pet
2796 ,pay_element_type_extra_info petei
2797 where pet.element_type_id = cp_eletyp_ctx_id
2798 and pet.element_type_id = petei.element_type_id
2799 and petei.information_type = 'US_EARNINGS'
2800 and petei.eei_information_category = 'US_EARNINGS';
2801
2802 CURSOR csr_get_prd_num(ctx_bg_id in number
2803 ,ctx_payroll_id in number
2804 ,period_end_date in date) is
2805 select max(PTP.period_num)
2806 from per_time_periods PTP
2807 ,pay_payrollS_f PRL
2808 where PTP.payroll_id = ctx_payroll_id
2809 and PTP.payroll_id = PRL.payroll_id
2810 and PRL.business_group_id = ctx_bg_id
2811 and to_char(period_end_date, 'YYYY') = to_char(PTP.start_date, 'YYYY');
2812
2813 CURSOR csr_get_num_prd(ctx_bg_id in number
2814 ,ctx_payroll_id in number) IS
2815 select TPT.number_per_fiscal_year
2816 from per_time_period_types TPT,
2817 pay_payrolls_f PRL
2818 WHERE TPT.period_type = PRL.period_type
2819 AND PRL.business_group_id + 0 = ctx_bg_id
2820 AND PRL.payroll_id = ctx_payroll_id;
2821
2822
2823 ln_max_period_num NUMBER;
2824 ln_period_num NUMBER;
2825 lv_ele_xtra_info PAY_ELEMENT_TYPE_EXTRA_INFO.eei_information11%TYPE;
2826
2827 BEGIN
2828
2829 hr_utility.trace('Entering into pay_us_rsrw_upgrev.get_payprd_per_fiscal_yr');
2830
2831 lv_ele_xtra_info := NULL;
2832
2833 open csr_get_ele_xtra_info(cp_eletyp_ctx_id => p_eletyp_ctx_id);
2834 fetch csr_get_ele_xtra_info into lv_ele_xtra_info;
2835 close csr_get_ele_xtra_info;
2836
2837 open csr_get_num_prd(ctx_bg_id => p_ctx_bg_id
2838 ,ctx_payroll_id => p_ctx_payroll_id);
2839 fetch csr_get_num_prd into ln_period_num;
2840 close csr_get_num_prd;
2841
2842 hr_utility.trace('ln_period_num := ' || ln_period_num);
2843
2844 IF NVL(lv_ele_xtra_info, 'N') = 'N' THEN
2845 return ln_period_num;
2846 ELSE
2847
2848 open csr_get_prd_num(ctx_bg_id => p_ctx_bg_id
2849 ,ctx_payroll_id => p_ctx_payroll_id
2850 ,period_end_date => p_period_end_date);
2851 fetch csr_get_prd_num into ln_max_period_num;
2852 close csr_get_prd_num;
2853
2854 hr_utility.trace('ln_max_period_num := ' || ln_max_period_num);
2855
2856 return GREATEST(ln_max_period_num, ln_period_num);
2857
2858 END IF;
2859
2860 END get_payprd_per_fiscal_yr;
2861
2862 /*****************************************************************************
2863 Name : get_assignment_status
2864
2865 Description : This Function checks system status type for assignment
2866 effective on the prorate_end date passed to it as parameter.
2867 *****************************************************************************/
2868
2869 FUNCTION get_assignment_status(p_ctx_asg_id IN NUMBER
2870 ,p_prorate_end_dt IN DATE) RETURN VARCHAR2 IS
2871
2872 CURSOR csr_get_asg_status(p_ctx_asg_id IN NUMBER
2873 ,p_prorate_end_dt IN DATE) IS
2874 SELECT past.per_system_status
2875 FROM per_assignments_f paf
2876 ,per_assignment_status_types past
2877 WHERE paf.assignment_id = p_ctx_asg_id
2878 AND paf.assignment_status_type_id = past.assignment_status_type_id
2879 AND p_prorate_end_dt between paf.effective_start_date and paf.effective_end_date;
2880
2881 lv_asg_status per_assignment_status_types.per_system_status%TYPE;
2882
2883 BEGIN
2884 lv_asg_status := NULL;
2885
2886 open csr_get_asg_status(p_ctx_asg_id, p_prorate_end_dt);
2887 fetch csr_get_asg_status into lv_asg_status;
2888 close csr_get_asg_status;
2889
2890 hr_utility.trace('lv_asg_status := ' || lv_asg_status);
2891 return lv_asg_status;
2892
2893 end get_assignment_status;
2894
2895 end pay_us_rsrw_upgrev;
[Home] [Help]