[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_FF_UDFS
Source
1 PACKAGE BODY PAY_CORE_FF_UDFS as
2 /* $Header: paycoreffudfs.pkb 120.7 2008/01/21 11:29:14 sudedas noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_core_ff_udfs
11 Filename : paycoreffudfs.sql
12 Change List
13 --------------- -----------
14 01-May-2005 sodhingr 115.0 Defines user defined function
15 used by international payroll
16 14-JUN-2005 sodhingr 115.1 Added the function get_hourly_rate,
17 that returns the hourly rate based on
18 Salary Basis. also added the function
19 calculate_actual_hours_worked that
20 calculates the hours worked based on
21 ATG/workschedule/Std hrs
22 07-OCT-2005 sodhingr 115.2 Changed the function calculate_Actual_hours_worked
23 to use the CORE HR API to calculate work_schedule
24 and changed the procedure convert_period_type
25 to use the lookup_code instead of meaning to avoid
26 translation issue.
27 07-FEB-2005 sodhingr 115.3 added convert_period_type and calculate_period_earnings
28 that can be used by the localization team and take care
29 of proration if core proration is not enabled.
30
31 30-MAR-2005 sodhingr 115.4 change variable v_hrs_per_range
32 to v_hours_in_range in convert_period_type
33 Also, changed to exclude the exception
34 This is for bug 5127891, 5102813
35 20-JUN-2006 sodhingr 115.5 5161241 changed get_hourly_rate to get
36 the salary as of the termination
37 date if the salary is null.
38
39 21-AUG-2007 sodhingr 115.6 6163428 Changed the procedure, Conver_Period_Type to uncomment
40 the code to check the payroll calculation rule defined at the payroll level.
41 Also, moved the variable name l_normal_hours to global variable as this
42 will be used by the uncommented code to get the standard hours.
43 08-Jan-2008 sudedas 115.7 6718164 Added new Function term_skip_rule_rwage
44 21-Jan-2008 sudedas 115.8 Corrected Logic for Term Rule LSPD
45 Used Cursor csr_lspprocd_min_dtearned.
46 Contexxt
47 =========
48
49 BUSINESS_GROUP_ID
50 ASSIGNMENT_ID
51 PAYROLL_ID
52 ELEMENT_ENTRY_ID
53 DATE_EARNED
54 ASSIGNMENT_ACTION_ID
55
56 parameters
57 ===========
58 p_period_start_date
59 p_period_end_date
60 p_schedule_category
61 p_include_exceptions
62 p_busy_tentative_as
63 p_legislation_code
64 p_schedule_source
65 p_schedule
66 p_return_status
67 p_return_message
68 */
69
70
71 -- **********************************************************************
72
73 --
74 -- ----------------------------------------------------------------------------
75 -- | Private Global Definitions |
76 -- ----------------------------------------------------------------------------
77 --
78 g_package varchar2(33) := ' pay_core_ff_udfs.'; -- Global package name
79 g_legislation_code VARCHAR2(10);
80 l_normal_hours NUMBER := 0;
81
82
83
84
85
86 --- Functions
87 FUNCTION get_legislation_code(p_business_group_id NUMBER)
88 RETURN VARCHAR2 IS
89 CURSOR c_get_legislation_code(p_business_group_id VARCHAR2) IS
90 select legislation_code
91 from per_business_groups_perf
92 where business_group_id = p_business_group_id;
93
94 BEGIN
95 OPEN c_get_legislation_code(p_business_group_id);
96 FETCH c_get_legislation_code INTO g_legislation_code;
97 CLOSE c_get_legislation_code;
98 return g_legislation_code;
99 END;
100
101 FUNCTION Convert_Period_Type(
102 p_bg in NUMBER -- context
103 ,p_assignment_id in NUMBER -- context
104 ,p_payroll_id in NUMBER -- context
105 ,p_element_entry_id in NUMBER -- context
106 ,p_date_earned in DATE -- context
107 ,p_assignment_action_id in NUMBER -- context
108 ,p_period_start_date IN DATE
109 ,p_period_end_date IN DATE
110 /*,p_schedule_category IN varchar2 --Optional
111 ,p_include_exceptions IN varchar2 --Optional
112 ,p_busy_tentative_as IN varchar2 --Optional
113 ,p_schedule_source IN varchar2
114 ,p_schedule IN varchar2*/
115 ,p_figure in NUMBER
116 ,p_from_freq in VARCHAR2
117 ,p_to_freq in VARCHAR2
118 ,p_asst_std_freq in VARCHAR2
119 ,p_rate_calc_override in VARCHAR2)
120 RETURN NUMBER IS
121
122 -- local vars
123 v_calc_type VARCHAR2(50);
124 v_from_stnd_factor NUMBER(30,7);
125 v_stnd_start_date DATE;
126
127 v_converted_figure NUMBER(27,7);
128 v_from_annualizing_factor NUMBER(30,7);
129 v_to_annualizing_factor NUMBER(30,7);
130 v_return_status NUMBER;
131 v_return_message VARCHAR2(500);
132 v_from_freq VARCHAR2(200);
133 v_to_freq VARCHAR2(200);
134 v_rate_calc_override VARCHAR2(200);
135 v_schedule_source varchar2(100);
136 v_schedule varchar2(200);
137
138
139
140 -- local fun
141
142 FUNCTION Get_Annualizing_Factor
143 ( p_bg IN number -- context
144 ,p_assignment_id IN number -- context
145 ,p_payroll_id IN number -- context
146 ,p_element_entry_id IN number -- context
147 ,p_date_earned IN date -- context
148 ,p_assignment_action_id IN number -- context
149 ,p_period_start_date IN DATE
150 ,p_period_end_date IN DATE
151 ,p_freq IN varchar2)
152
153
154 RETURN NUMBER IS
155
156 CURSOR c_get_lookupcode_freq IS
157 SELECT lookup_code
158 FROM hr_lookups lkp
159 WHERE lkp.application_id = 800
160 AND lkp.lookup_type = 'PAY_BASIS'
161 AND lkp.lookup_code = p_freq;
162
163 CURSOR c_get_lookupmeaning_freq IS
164 SELECT lookup_code
165 FROM hr_lookups lkp
166 WHERE lkp.application_id = 800
167 AND lkp.lookup_type = 'PAY_BASIS'
168 AND lkp.meaning = p_freq;
169
170 -- local constants
171
172 c_weeks_per_year NUMBER(3);
173 c_days_per_year NUMBER(3);
174 c_months_per_year NUMBER(3);
175
176 -- local vars
177
178 v_annualizing_factor NUMBER(30,7);
179 v_periods_per_fiscal_yr NUMBER(5);
180 v_hrs_per_wk NUMBER(15,7);
181 v_hrs_per_range NUMBER(15,7);
182 v_days_per_range NUMBER(15,7);
183 v_use_pay_basis NUMBER(1);
184 v_pay_basis VARCHAR2(80);
185 v_range_start DATE;
186 v_range_end DATE;
187 v_work_sched_name VARCHAR2(80);
188 v_ws_id NUMBER(9);
189 v_period_hours BOOLEAN;
190
191 lv_period_type varchar2(150);
192
193
194 BEGIN -- Get_Annualizing_Factor
195
196 /* Init */
197
198 c_weeks_per_year := 52;
199 c_days_per_year := 200;
200 c_months_per_year := 12;
201 v_use_pay_basis := 0;
202
203 --
204 -- Check for use of salary admin (ie. pay basis) as frequency.
205 -- Selecting "count" because we want to continue processing even if
206 -- the from_freq is not a pay basis.
207 --
208
209 hr_utility.trace(' Entered Get_Annualizing_Factor ');
210
211 BEGIN -- Is Freq pay basis?
212
213 --
214 -- Decode pay basis and set v_annualizing_factor accordingly.
215 -- PAY_BASIS "Meaning" is passed from FF !
216 --
217
218 hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
219 hr_utility.trace(' p_freq = '||p_freq);
220
221
222 IF p_freq IS NULL THEN
223 v_use_pay_basis := 0;
224 ELSE
225 v_use_pay_basis := 1;
226
227 OPEN c_get_lookupcode_freq;
228 FETCH c_get_lookupcode_freq INTO v_pay_basis;
229 CLOSE c_get_lookupcode_freq;
230
231 IF v_pay_basis IS NULL THEN
232 OPEN c_get_lookupmeaning_freq;
233 FETCH c_get_lookupmeaning_freq INTO v_pay_basis;
234 CLOSE c_get_lookupmeaning_freq;
235 END IF;
236
237 --v_pay_basis := p_freq;
238
239 hr_utility.trace(' Lookup_code ie v_pay_basis ='||v_pay_basis);
240
241 IF v_pay_basis = 'MONTHLY' THEN
242
243 hr_utility.trace(' Entered for MONTHLY v_pay_basis');
244
245 v_annualizing_factor := 12;
246
247 hr_utility.trace(' v_annualizing_factor = 12 ');
248
249 ELSIF v_pay_basis = 'HOURLY' THEN
250
251 hr_utility.trace(' Entered for HOURLY v_pay_basis');
252
253 IF p_period_start_date IS NOT NULL THEN
254
255 hr_utility.trace(' p_period_start_date IS NOT NULL ' ||
256 ' v_period_hours=T');
257
258 v_range_start := p_period_start_date;
259 v_range_end := p_period_end_date;
260 v_period_hours := TRUE;
261
262 ELSE
263
264 hr_utility.trace(' p_period_start_date IS NULL');
265
266 v_range_start := sysdate;
267 v_range_end := sysdate + 6;
268 v_period_hours := FALSE;
269
270 END IF;
271
272 /* Use new function to calculate hours */
273 v_hrs_per_range := calculate_actual_hours_worked
274 ( p_assignment_action_id
275 ,p_assignment_id
276 ,p_bg
277 ,p_element_entry_id
278 ,p_date_earned
279 ,p_period_start_date
280 ,p_period_end_date
281 ,NULL
282 ,'N'
283 ,'BUSY'
284 ,''--p_legislation_code
285 ,v_schedule_source
286 ,v_schedule
287 ,v_return_status
288 ,v_return_message);
289
290 IF v_period_hours THEN
291
292 hr_utility.trace(' v_period_hours is TRUE');
293
294 SELECT TPT.number_per_fiscal_year
295 INTO v_periods_per_fiscal_yr
296 FROM pay_payrolls_f PPF,
297 per_time_period_types TPT,
298 fnd_sessions fs
299 WHERE PPF.payroll_id = p_payroll_id
300 AND fs.session_id = USERENV('SESSIONID')
301 AND fs.effective_date between PPF.effective_start_date
302 and PPF.effective_end_date
303 AND TPT.period_type = PPF.period_type;
304
305 v_annualizing_factor :=
306 v_hrs_per_range * v_periods_per_fiscal_yr;
307
308 ELSE
309
310 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
311
312 END IF;
313
314 ELSIF v_pay_basis = 'PERIOD' THEN
315
316 hr_utility.trace(' v_pay_basis = PERIOD');
317
318 SELECT TPT.number_per_fiscal_year
319 INTO v_annualizing_factor
320 FROM pay_payrolls_f PRL,
321 per_time_period_types TPT,
322 fnd_sessions fs
323 WHERE TPT.period_type = PRL.period_type
327 AND PRL.payroll_id = p_payroll_id
324 and fs.session_id = USERENV('SESSIONID')
325 and fs.effective_date BETWEEN PRL.effective_start_date
326 AND PRL.effective_end_date
328 AND PRL.business_group_id + 0 = p_bg;
329
330
331 ELSIF v_pay_basis = 'ANNUAL' THEN
332
333
334 hr_utility.trace(' v_pay_basis = ANNUAL');
335
336 v_annualizing_factor := 1;
337
338 ELSE
339
340 -- Did not recognize "pay basis", return -999 as annualizing factor.
341 -- Remember this for debugging when zeroes come out as results!!!
342
343 hr_utility.trace(' Did not recognize pay basis');
344
345 v_annualizing_factor := 0;
346
347 RETURN v_annualizing_factor;
348
349 END IF;
350 END IF;
351
352 EXCEPTION
353
354 WHEN NO_DATA_FOUND THEN
355
356 hr_utility.trace(' When no data found' );
357 v_use_pay_basis := 0;
358
359 END; /* SELECT LOOKUP CODE */
360
361
362 IF v_use_pay_basis = 0 THEN
363
364 hr_utility.trace(' Not using pay basis as frequency');
365
366 -- Not using pay basis as frequency...
367
368 IF (p_freq IS NULL) OR
369 (UPPER(p_freq) = 'PERIOD') OR
370 (UPPER(p_freq) = 'NOT ENTERED')
371 THEN
372
373 -- Get "annuallizing factor" from period type of the payroll.
374
375 hr_utility.trace('Get annuallizing factor from period '||
376 'type of the payroll');
377
378 SELECT TPT.number_per_fiscal_year
379 INTO v_annualizing_factor
380 FROM pay_payrolls_f PRL,
381 per_time_period_types TPT,
382 fnd_sessions fs
383 WHERE TPT.period_type = PRL.period_type
384 AND fs.session_id = USERENV('SESSIONID')
385 AND fs.effective_date BETWEEN PRL.effective_start_date
386 AND PRL.effective_end_date
387 AND PRL.payroll_id = p_payroll_id
388 AND PRL.business_group_id + 0 = p_bg;
389
390 hr_utility.trace('v_annualizing_factor ='||
391 to_number(v_annualizing_factor));
392
393
394 ELSIF UPPER(p_freq) = 'HOURLY' THEN -- Hourly employee...
395
396 hr_utility.trace(' Hourly Employee');
397
398 IF p_period_start_date IS NOT NULL THEN
399 v_range_start := p_period_start_date;
400 v_range_end := p_period_end_date;
401 v_period_hours := TRUE;
402 ELSE
403 v_range_start := sysdate;
404 v_range_end := sysdate + 6;
405 v_period_hours := FALSE;
406 END IF;
407
408 /* Use new function to calculate hours */
409 v_hrs_per_range := calculate_actual_hours_worked
410 ( p_assignment_action_id
411 ,p_assignment_id
412 ,p_bg
413 ,p_element_entry_id
414 ,p_date_earned
415 ,p_period_start_date
416 ,p_period_end_date
417 ,NULL
418 ,'N'
419 ,'BUSY'
420 ,''--p_legislation_code
421 ,v_schedule_source
422 ,v_schedule
423 ,v_return_status
424 ,v_return_message);
425
426 hr_utility.trace('v_hrs_per_range ='||v_hrs_per_range);
427 IF v_period_hours THEN
428
429 hr_utility.trace('v_period_hours = TRUE');
430
431 SELECT TPT.number_per_fiscal_year
432 INTO v_periods_per_fiscal_yr
433 FROM pay_payrolls_f ppf,
434 per_time_period_types tpt,
435 fnd_sessions fs
436 WHERE ppf.payroll_id = p_payroll_id
437 AND fs.session_id = USERENV('SESSIONID')
438 AND fs.effective_date BETWEEN ppf.effective_start_date
439 AND ppf.effective_end_date
440 AND tpt.period_type = ppf.period_type;
441
442 v_annualizing_factor :=
443 v_hrs_per_range * v_periods_per_fiscal_yr;
444
445 hr_utility.trace('v_hrs_per_range ='||
446 to_number(v_hrs_per_range));
450 to_number(v_annualizing_factor));
447 hr_utility.trace('v_periods_per_fiscal_yr ='||
448 to_number(v_periods_per_fiscal_yr));
449 hr_utility.trace('v_annualizing_factor ='||
451
452 ELSE
453
454 hr_utility.trace('v_period_hours = FALSE');
455
456 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
457
458 hr_utility.trace('v_hrs_per_range ='||
459 to_number(v_hrs_per_range));
460 hr_utility.trace('c_weeks_per_year ='||
461 to_number(c_weeks_per_year));
462 hr_utility.trace('v_annualizing_factor ='||
463 to_number(v_annualizing_factor));
464
465 END IF;
466
467 ELSE
468
469 -- Not hourly, an actual time period type!
470
471 hr_utility.trace('Not hourly - an actual time period type');
472
473 BEGIN
474
475 hr_utility.trace(' selecting from per_time_period_types');
476
477 SELECT PT.number_per_fiscal_year
478 INTO v_annualizing_factor
479 FROM per_time_period_types PT
480 WHERE UPPER(PT.period_type) = UPPER(p_freq);
481
482 hr_utility.trace('v_annualizing_factor ='||
483 to_number(v_annualizing_factor));
484
485 EXCEPTION WHEN no_data_found THEN
486
487 -- Added as part of SALLY CLEANUP.
488 -- Could have been passed in an ASG_FREQ dbi which
489 -- might have the values of
490 -- 'Day' or 'Month' which do not map to a time period type.
491 -- So we'll do these by hand.
492
493 IF UPPER(p_freq) = 'DAY' THEN
494 hr_utility.trace(' p_freq = DAY');
495 v_annualizing_factor := c_days_per_year;
496 ELSIF UPPER(p_freq) = 'MONTH' THEN
497 v_annualizing_factor := c_months_per_year;
498 hr_utility.trace(' p_freq = MONTH');
499 END IF;
500
501 END;
502
503 END IF;
504
505 END IF; -- (v_use_pay_basis = 0)
506
507
508 hr_utility.trace(' Getting out of Get_Annualizing_Factor for '||
509 v_pay_basis);
510 RETURN v_annualizing_factor;
511
512 END Get_Annualizing_Factor;
513
514
515 BEGIN -- Convert Figure
516 --begin_convert_period_type
517
518 --hr_utility.trace_on(null,'pay_core_ff_udfs');
519
520 IF p_from_freq IS NULL THEN
521 v_from_freq := 'NOT ENTERED';
522 END IF;
523
524 IF p_to_freq IS NULL THEN
525 v_to_freq := 'NOT ENTERED';
526 END IF;
527
528 /* IF p_rate_calc_override IS NULL THEN
529 v_rate_calc_override := 'NOT ENTERED';
530 END IF;
531 */
532 hr_utility.trace('COREUDFS Entered Convert_Period_Type');
533
534 hr_utility.trace('assignment_action_id=' || p_assignment_action_id);
535 hr_utility.trace('assignment_id=' || p_assignment_id);
536 hr_utility.trace('business_group_id=' || p_bg);
537 hr_utility.trace('element_entry_id=' || p_element_entry_id);
538 hr_utility.trace( 'p-date_earned '||p_date_earned);
539 hr_utility.trace(' p_payroll_id: '||p_payroll_id);
540 hr_utility.trace(' p_figure: '||p_figure);
541 hr_utility.trace('p_period_start_date=' || p_period_start_date);
542 hr_utility.trace('p_period_end_date=' || p_period_end_date);
543 /*hr_utility.trace('p_schedule_category=' || p_schedule_category);
544 hr_utility.trace('p_schedule_source=' || p_schedule_source);
545 hr_utility.trace('p_include_exceptions=' || p_include_exceptions);
546 hr_utility.trace('p_busy_tentative_as=' || p_busy_tentative_as);
547 hr_utility.trace('p_schedule=' || p_schedule);*/
548
549 hr_utility.trace(' p_from_freq : '||p_from_freq);
550 hr_utility.trace(' p_to_freq: '||p_to_freq);
551 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
552
553
554 IF g_legislation_code IS NULL THEN
555 hr_utility.trace('g_legislation_code is null ');
556 g_legislation_code := get_legislation_code(p_bg);
557 END IF;
558
559 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
560
561
562 --
563 -- If From_Freq and To_Freq are the same, then we're done.
564 --
565
566 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
567
568 RETURN p_figure;
569
570 END IF;
571 hr_utility.trace('Calling Get_Annualizing_Factor for FROM case');
572 v_from_annualizing_factor := Get_Annualizing_Factor
573 ( p_bg
574 ,p_assignment_id
575 ,p_payroll_id
576 ,p_element_entry_id
577 ,p_date_earned
581 ,p_from_freq);
578 ,p_assignment_action_id
579 ,p_period_start_date
580 ,p_period_end_date
582
583
584
585
586 hr_utility.trace('Calling Get_Annualizing_Factor for TO case');
587
588 v_to_annualizing_factor := Get_Annualizing_Factor(
589 p_bg -- context
590 ,p_assignment_id -- context
591 ,p_payroll_id -- context
592 ,p_element_entry_id -- context
593 ,p_date_earned -- context
594 ,p_assignment_action_id -- context
595 ,p_period_start_date
596 ,p_period_END_date
597 ,p_to_freq);
598 --,p_asst_std_freq);
599
600 --
601 -- Annualize "Figure" and convert to To_Freq.
602 --
603 hr_utility.trace('v_from_annualizing_factor ='||to_char(v_from_annualizing_factor));
604 hr_utility.trace('v_to_annualizing_factor ='||to_char(v_to_annualizing_factor));
605
606 IF v_to_annualizing_factor = 0 OR
607 v_to_annualizing_factor = -999 OR
608 v_from_annualizing_factor = -999 THEN
609
610 hr_utility.trace(' v_to_ann =0 or -999 or v_from = -999');
611
612 v_converted_figure := 0;
613 RETURN v_converted_figure;
614
615 ELSE
616
617 hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
618
619 hr_utility.trace('p_figure Monthly Salary = '||p_figure);
620 hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
621 hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
622
623 v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
624 hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
625
626 hr_utility.trace('CORE UDFS v_converted_figure := '||v_converted_figure);
627
628 END IF;
629
630 -- Done
631
632 /***********************************************************
633 The is wrapper is added to check the caluclation rule given
634 at the payroll level. Depending upon the Rule we will the
635 Get_Annualizing_Factor fun calls. If the rule is
636 standard it goes to Standard Caluclation type. If the rule
637 is Annual then it goes to ANNU rule
638 **************************************************************/
639 IF p_period_start_date IS NULL THEN
640 v_stnd_start_date := sysdate;
641 ELSE
642 v_stnd_start_date := p_period_start_date ;
643 END IF;
644
645 begin
646 select nvl(ppf.prl_information2,'NOT ENTERED')
647 into v_calc_type
648 from pay_payrolls_f ppf
649 where payroll_id = p_payroll_id
650 and v_stnd_start_date between ppf.effective_start_date
651 and ppf.effective_end_Date;
652 exception
653 when others then
654 v_calc_type := null;
655 end;
656
657 IF
658 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
659 and p_rate_calc_override = 'FIXED') OR
660 (v_calc_type = 'NOT ENTERED' and p_to_freq <> 'NOT ENTERED'
661 and p_rate_calc_override = 'FIXED') OR
662 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
663 and p_rate_calc_override = 'NOT ENTERED') OR
664 (v_calc_type = 'ANNU' and p_to_freq <> 'NOT ENTERED'
665 and p_rate_calc_override = 'FIXED')
666 THEN
667
668 v_from_stnd_factor := Get_Annualizing_Factor
669 ( p_bg
670 ,p_assignment_id
671 ,p_payroll_id
672 ,p_element_entry_id
673 ,p_date_earned
674 ,p_assignment_action_id
675 ,p_period_start_date
676 ,p_period_end_date
677 ,p_from_freq);
678 hr_utility.trace('l_normal_hours := '||l_normal_hours);
679 hr_utility.trace('p_figure := '||p_figure);
680 hr_utility.trace('v_from_stnd_factor := '||v_from_stnd_factor);
681
682
683 v_converted_figure :=(p_figure * v_from_stnd_factor/(52 * l_normal_hours ));
684 hr_utility.trace('v_converted_figure := '||v_converted_figure);
685
686 END IF;
687
688
689 RETURN v_converted_figure;
690
691 END Convert_Period_Type;
692 --
693 -- **********************************************************************
694 --
695
696 FUNCTION Calculate_Period_Earnings (
697 p_bus_grp_id in NUMBER,
698 p_asst_id in NUMBER,
699 p_payroll_id in NUMBER,
700 p_ele_entry_id in NUMBER,
701 p_tax_unit_id in NUMBER,
702 p_date_earned in DATE,
703 p_assignment_action_id in NUMBER,
704 p_pay_basis in VARCHAR2,
705 p_inpval_name in VARCHAR2,
706 p_ass_hrly_figure in NUMBER,
707 p_period_start in DATE,
708 p_period_end in DATE,
709 --p_work_schedule in VARCHAR2,
710 --p_asst_std_hrs in NUMBER,
711 p_actual_hours_worked in out nocopy NUMBER,
712 p_vac_hours_worked in out nocopy NUMBER,
716 p_prorate in VARCHAR2,
713 p_vac_pay in out nocopy NUMBER,
714 p_sick_hours_worked in out nocopy NUMBER,
715 p_sick_pay in out nocopy NUMBER,
717 p_asst_std_freq in VARCHAR2)
718 RETURN NUMBER IS
719
720 l_asg_info_changes NUMBER(1);
721 l_eev_info_changes NUMBER(1);
722 v_earnings_entry NUMBER(27,7);
723 v_inpval_id NUMBER(9);
724 v_pay_basis VARCHAR2(80);
725 v_pay_periods_per_year NUMBER(3);
726 v_period_earn NUMBER(27,7) ; -- Pay Period earnings.
727 v_hourly_earn NUMBER(27,7); -- Hourly Rate (earnings).
728 v_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
729 v_curr_day VARCHAR2(3); -- Currday while summing hrs for range of dates.
730 v_hrs_per_wk NUMBER(15,7);
731 v_hrs_per_range NUMBER(15,7);
732 v_asst_std_hrs NUMBER(15,7);
733 v_asst_std_freq VARCHAR2(30);
734 v_asg_status VARCHAR2(30);
735 v_hours_in_range NUMBER(15,7);
736 v_curr_hrly_rate NUMBER(27,7) ;
737 v_range_start DATE; -- range start of ASST rec
738 v_range_end DATE; -- range end of ASST rec
739 v_entry_start DATE; -- start date of ELE ENTRY rec
740 v_entry_end DATE; -- end date of ELE ENTRY rec
741 v_entrange_start DATE; -- max of entry or asst range start
742 v_entrange_end DATE; -- min of entry or asst range end
743 v_work_schedule VARCHAR2(60); -- Work Schedule ID (stored as varchar2
744 -- in HR_SOFT_CODING_KEYFLEX; convert
745 -- fnd_number.canonical_to_number when calling wshours fn.
746 v_work_sched_name VARCHAR2(80);
747 v_ws_id NUMBER(9);
748
749 b_entries_done BOOLEAN; -- flags no more entry changes in paypd
750 b_asst_changed BOOLEAN; -- flags if asst changes at least once.
751 b_on_work_schedule BOOLEAN; -- use wrk scheds or std hours
752 l_mid_period_asg_change BOOLEAN ;
753
754 v_return_status NUMBER;
755 v_return_message VARCHAR2(500);
756 v_schedule_source varchar2(100);
757 v_schedule varchar2(200);
758 v_total_hours NUMBER(15,7) ;
759
760 /*
761 -- ************************************************************************
762 --
763 -- The following cursor "get_asst_chgs" looks for *changes* to or from
764 -- 'ACTIVE' per_assignment
765 -- records within the supplied range of dates, *WITHIN THE SAME TAX UNIT*
766 -- (ie. the tax unit as of the end of the period specified).
767 -- If no "changes" are found, then assignment information is consistent
768 -- over entire period specified.
769 -- Before calling this cursor, will need to select tax_unit_name
770 -- according to p_tax_unit_id.
771 --
772 -- ************************************************************************
773 */
774
775 --
776 -- This cursor finds ALL ASG records that are WITHIN Period Start and End Dates
777 -- including Period End Date - NOT BETWEEN since the ASG record existing across
778 -- Period Start date has already been retrieved in SELECT (ASG1).
779 -- Work Schedule segment is segment4 on assignment DDF
780 --
781
782 CURSOR get_asst_chgs IS
783 SELECT ASG.effective_start_date,
784 ASG.effective_end_date,
785 NVL(ASG.normal_hours, 0),
786 NVL(HRL.meaning, 'NOT ENTERED'),
787 NVL(SCL.segment4, 'NOT ENTERED')
788 FROM per_assignments_f ASG,
789 per_assignment_status_types AST,
790 hr_soft_coding_keyflex SCL,
791 hr_lookups HRL
792 WHERE ASG.assignment_id = p_asst_id
793 AND ASG.business_group_id + 0 = p_bus_grp_id
794 AND ASG.effective_start_date > p_period_start
795 AND ASG.effective_end_date <= p_period_end
796 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
797 AND AST.per_system_status = 'ACTIVE_ASSIGN'
798 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
799 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
800 AND SCL.enabled_flag = 'Y'
801 AND HRL.lookup_code(+) = ASG.frequency
802 AND HRL.lookup_type(+) = 'FREQUENCY';
803
804 FUNCTION Prorate_Earnings (
805 p_bg_id IN NUMBER,
806 p_asg_hrly_rate IN NUMBER,
807 -- p_wsched IN VARCHAR2 DEFAULT 'NOT ENTERED',
808 -- p_asg_std_hours IN NUMBER,
809 -- p_asg_std_freq IN VARCHAR2,
810 p_range_start_date IN DATE,
811 p_range_end_date IN DATE,
812 p_act_hrs_worked IN OUT nocopy NUMBER) RETURN NUMBER IS
813
814 v_prorated_earn NUMBER(27,7) ; -- RETURN var
815 v_hours_in_range NUMBER(15,7);
816 v_ws_id NUMBER(9);
817 v_ws_name VARCHAR2(80);
818
819 BEGIN
820
821 /* Init */
822
823 --p_wsched := 'NOT ENTERED';
824 v_prorated_earn := 0;
825
826 hr_utility.trace('UDFS Entered Prorate Earnings');
827 hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
828 hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
829 -- hr_utility.trace('p_wsched ='||p_wsched);
830 -- hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
831 -- hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
832 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
833 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
834 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
835
836 -- Prorate using hourly rate passed in as param:
837
838 /*
839 IF UPPER(p_wsched) = 'NOT ENTERED' THEN
840
841 hr_utility.set_location('Prorate_Earnings', 7);
845 v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
842 hr_utility.trace('p_wsched NOT ENTERED');
843 hr_utility.trace('Calling Standard Hours Worked');
844
846 p_range_start_date,
847 p_range_end_date,
848 p_asg_std_freq);
849
850 -- Keep running total of ACTUAL hours worked.
851 hr_utility.set_location('Prorate_Earnings', 11);
852
853 hr_utility.trace('Keep running total of ACTUAL hours worked');
854
855 hr_utility.trace('actual_hours_worked before call= '||
856 to_char(p_act_hrs_worked));
857 hr_utility.trace('v_hours_in_range in current call= '||
858 to_char(v_hours_in_range));
859
860 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
861
862 hr_utility.trace('UDFS actual_hours_worked after call = '||
863 to_char(p_act_hrs_worked));
864
865 ELSE
866
867 hr_utility.set_location('Prorate_Earnings', 17);
868 hr_utility.trace('Entered WORK SCHEDULE');
869
870 hr_utility.trace('Getting WORK SCHEDULE Name');
871
872 -- Get work schedule name:
873
874 v_ws_id := fnd_number.canonical_to_number(p_wsched);
875
876 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
877
878 SELECT user_column_name
879 INTO v_ws_name
880 FROM pay_user_columns
881 WHERE user_column_id = v_ws_id
882 AND NVL(business_group_id, p_bg_id) = p_bg_id
883 AND NVL(legislation_code,'US') = 'US';
884
885 hr_utility.trace('v_ws_name ='||v_ws_name );
886 hr_utility.trace('Calling Work_Schedule_Total_Hours');
887
888 v_hours_in_range := Work_Schedule_Total_Hours(
889 p_bg_id,
890 v_ws_name,
891 p_range_start_date,
892 p_range_end_date);
893
894 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
895 hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
896
897 END IF; -- Hours in date range via work schedule or std hours.
898 */
899
900
901 hr_utility.trace('calling PAY_CORE_FF_UDFS.calculate_actual_hours_worked');
902 v_hours_in_range := pay_core_ff_udfs.calculate_actual_hours_worked (
903 null
904 ,p_asst_id
905 ,p_bus_grp_id
906 ,p_ele_entry_id
907 ,p_date_earned
908 ,p_range_start_date
909 ,p_range_end_date
910 ,NULL
911 ,'Y'
912 ,'BUSY'
913 ,''--p_legislation_code
914 ,v_schedule_source
915 ,v_schedule
916 ,v_return_status
917 ,v_return_message);
918
919 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
920 hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
921
922
923 hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
924
925
926 hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
927
928 v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
929
930 hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
931 hr_utility.set_location('Prorate_Earnings', 97);
932 p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
933 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
934 hr_utility.trace('UDFS Leaving Prorated Earnings');
935
936 RETURN v_prorated_earn;
937
938 END Prorate_Earnings;
939
940 FUNCTION Prorate_EEV ( p_bus_group_id IN NUMBER,
941 p_pay_id IN NUMBER,
942 --p_work_sched IN VARCHAR2 DEFAULT 'NOT ENTERED',
943 --p_asg_std_hrs IN NUMBER,
944 --p_asg_std_freq IN VARCHAR2,
945 p_pay_basis IN VARCHAR2,
946 p_hrly_rate IN OUT nocopy NUMBER,
947 p_range_start_date IN DATE,
948 p_range_end_date IN DATE,
949 p_actual_hrs_worked IN OUT nocopy NUMBER,
950 p_element_entry_id IN NUMBER,
951 p_inpval_id IN NUMBER) RETURN NUMBER IS
952 --
953 -- local vars
954 --
955 v_eev_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
956 v_earnings_entry VARCHAR2(60);
957 v_entry_start DATE;
958 v_entry_end DATE;
959 v_hours_in_range NUMBER(15,7);
960 v_curr_hrly_rate NUMBER(27,7);
961 v_ws_id NUMBER(9);
962 v_ws_name VARCHAR2(80);
963 --
964 -- Select for ALL records that are WITHIN Range Start and End Dates
965 -- including Range End Date - NOT BETWEEN since the EEV record existing across
966 -- Range Start date has already been retrieved and dealt with in SELECT (EEV1).
967 -- A new EEV record results in a change of the current hourly rate being used
968 -- in proration calculation.
969 --
970 CURSOR get_entry_chgs ( p_range_start date,
971 p_range_end date) IS
972 SELECT EEV.screen_entry_value,
973 EEV.effective_start_date,
974 EEV.effective_end_date
975 FROM pay_element_entry_values_f EEV
976 WHERE EEV.element_entry_id = p_element_entry_id
977 AND EEV.input_value_id = p_inpval_id
981 --
978 AND EEV.effective_start_date > p_range_start
979 AND EEV.effective_end_date <= p_range_end
980 ORDER BY EEV.effective_start_date;
982 BEGIN
983
984
985 /* Init */
986 --p_work_sched := 'NOT ENTERED';
987 v_eev_prorated_earnings := 0;
988
989
990 hr_utility.trace('UDFS Entering PRORATE_EEV');
991 hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
992 hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
993 -- hr_utility.trace('p_work_sched ='||p_work_sched);
994 --hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
995 -- hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
996 hr_utility.trace('p_pay_basis ='||p_pay_basis);
997 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
998 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
999 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
1000 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1001 hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
1002 hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
1003 --
1004 -- Find all EEV changes, calculate new hourly rate, prorate:
1005 -- SELECT (EEV1):
1006 -- Select for SINGLE record that includes Period Start Date but does not
1007 -- span entire period.
1008 -- We know this select will return a row, otherwise there would be no
1009 -- EEV changes to detect.
1010 --
1011 hr_utility.set_location('Prorate_EEV', 103);
1012 SELECT EEV.screen_entry_value,
1013 GREATEST(EEV.effective_start_date, p_range_start_date),
1014 EEV.effective_end_date
1015 INTO v_earnings_entry,
1016 v_entry_start,
1017 v_entry_end
1018 FROM pay_element_entry_values_f EEV
1019 WHERE EEV.element_entry_id = p_element_entry_id
1020 AND EEV.input_value_id = p_inpval_id
1021 AND EEV.effective_start_date <= p_range_start_date
1022 AND EEV.effective_end_date >= p_range_start_date
1023 AND EEV.effective_end_date < p_range_end_date;
1024
1025
1026 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
1027 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1028 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1029 hr_utility.trace('Calling Convert_Period_Type ');
1030 hr_utility.set_location('Prorate_EEV', 105);
1031
1032 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1033 ,p_asst_id
1034 ,p_payroll_id
1035 ,p_ele_entry_id
1036 ,p_date_earned
1037 ,p_assignment_action_id
1038 ,p_period_start -- period start date
1039 ,p_period_end -- period end date
1040 ,v_earnings_entry -- p_figure, salary amount
1041 ,p_pay_basis -- p_from freq, salary basis
1042 ,'HOURLY'); -- p_to_freq
1043
1044
1045 /*get_hourly_rate(
1046 p_bus_grp_id
1047 ,p_asst_id
1048 ,p_payroll_id
1049 ,p_ele_entry_id
1050 ,p_date_earned
1051 ,p_assignment_action_id );
1052 */
1053 /*Convert_Period_Type( p_bus_group_id,
1054 p_pay_id,
1055 p_work_sched,
1056 p_asg_std_hrs,
1057 v_earnings_entry,
1058 p_pay_basis,
1059 'HOURLY',
1060 p_period_start,
1061 p_period_end,
1062 p_asg_std_freq); */
1063 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1064 hr_utility.set_location('Prorate_EEV', 107);
1065
1066 v_eev_prorated_earnings := v_eev_prorated_earnings +
1067 Prorate_Earnings (
1068 p_bg_id => p_bus_group_id,
1069 p_asg_hrly_rate => v_curr_hrly_rate,
1070 p_range_start_date => v_entry_start,
1071 p_range_end_date => v_entry_end,
1072 p_act_hrs_worked => p_actual_hrs_worked);
1073
1074 hr_utility.trace('v_eev_prorated_earnings ='||
1075 to_char(v_eev_prorated_earnings));
1076 -- SELECT (EEV2):
1077 hr_utility.trace('Opening get_entry_chgs cursor EEV2');
1078
1079 OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
1080 LOOP
1081 --
1082 FETCH get_entry_chgs
1083 INTO v_earnings_entry,
1084 v_entry_start,
1085 v_entry_end;
1086 EXIT WHEN get_entry_chgs%NOTFOUND;
1087 --
1088 hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
1089 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1090 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1091 hr_utility.set_location('Prorate_EEV', 115);
1092 --
1093 -- For each range of dates found, add to running prorated earnings total.
1094 --
1095 hr_utility.trace('Calling Convert_Period_Type ');
1096
1097 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1098 ,p_asst_id
1099 ,p_payroll_id
1100 ,p_ele_entry_id
1101 ,p_date_earned
1102 ,p_assignment_action_id
1103 ,p_period_start -- period start date
1104 ,p_period_end -- period end date
1105 ,v_earnings_entry -- p_figure, salary amount
1109 /*Convert_Period_Type( p_bus_group_id,
1106 ,p_pay_basis -- p_from freq, salary basis
1107 ,'HOURLY'); -- p_to_freq
1108
1110 p_pay_id,
1111 p_work_sched,
1112 p_asg_std_hrs,
1113 v_earnings_entry,
1114 p_pay_basis,
1115 'HOURLY',
1116 p_period_start,
1117 p_period_end,
1118 p_asg_std_freq); */
1119
1120
1121 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1122 hr_utility.set_location('Prorate_EEV', 119);
1123 v_eev_prorated_earnings := v_eev_prorated_earnings +
1124 Prorate_Earnings (
1125 p_bg_id => p_bus_group_id,
1126 p_asg_hrly_rate => v_curr_hrly_rate,
1127 p_range_start_date => v_entry_start,
1128 p_range_end_date => v_entry_end,
1129 p_act_hrs_worked => p_actual_hrs_worked);
1130
1131 hr_utility.trace('v_eev_prorated_earnings ='||to_char(v_eev_prorated_earnings));
1132
1133 END LOOP;
1134 --
1135 CLOSE get_entry_chgs;
1136 --
1137 -- SELECT (EEV3)
1138 -- Select for SINGLE record that exists across Period End Date:
1139 -- NOTE: Will only return a row if select (2) does not return a row where
1140 -- Effective End Date = Period End Date !
1141
1142 hr_utility.trace('Select EEV3');
1143 hr_utility.set_location('Prorate_EEV', 141);
1144 SELECT EEV.screen_entry_value,
1145 EEV.effective_start_date,
1146 LEAST(EEV.effective_end_date, p_range_end_date)
1147 INTO v_earnings_entry,
1148 v_entry_start,
1149 v_entry_end
1150 FROM pay_element_entry_values_f EEV
1151 WHERE EEV.element_entry_id = p_element_entry_id
1152 AND EEV.input_value_id = p_inpval_id
1153 AND EEV.effective_start_date > p_range_start_date
1154 AND EEV.effective_start_date <= p_range_end_date
1155 AND EEV.effective_end_date > p_range_end_date;
1156 hr_utility.set_location('Prorate_EEV', 147);
1157 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
1158 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1159 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1160
1161 hr_utility.trace('Calling Convert_Period_Type ');
1162
1163 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1164 ,p_asst_id
1165 ,p_payroll_id
1166 ,p_ele_entry_id
1167 ,p_date_earned
1168 ,p_assignment_action_id
1169 ,p_period_start -- period start date
1170 ,p_period_end -- period end date
1171 ,v_earnings_entry -- p_figure, salary amount
1172 ,p_pay_basis -- p_from freq, salary basis
1173 ,'HOURLY'); -- p_to_freq
1174 /*Convert_Period_Type( p_bus_group_id,
1175 p_pay_id,
1176 p_work_sched,
1177 p_asg_std_hrs,
1178 v_earnings_entry,
1179 p_pay_basis,
1180 'HOURLY',
1181 p_period_start,
1182 p_period_end,
1183 p_asg_std_freq);
1184 */
1185 hr_utility.set_location('Prorate_EEV', 151);
1186 hr_utility.trace('After Call v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1187
1188 v_eev_prorated_earnings := v_eev_prorated_earnings +
1189 Prorate_Earnings (
1190 p_bg_id => p_bus_group_id,
1191 p_asg_hrly_rate => v_curr_hrly_rate,
1192 p_range_start_date => v_entry_start,
1193 p_range_end_date => v_entry_end,
1194 p_act_hrs_worked => p_actual_hrs_worked);
1195
1196 -- We're Done!
1197 hr_utility.trace('v_eev_prorated_earnings ='||
1198 to_char(v_eev_prorated_earnings));
1199 hr_utility.set_location('Prorate_EEV', 167);
1200 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
1201 p_hrly_rate := v_curr_hrly_rate;
1202
1203 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1204 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
1205
1206 hr_utility.trace('UDFS Leaving Prorated EEV');
1207
1208 RETURN v_eev_prorated_earnings;
1209
1210 EXCEPTION WHEN NO_DATA_FOUND THEN
1211 hr_utility.set_location('Prorate_EEV', 177);
1212 hr_utility.trace('Into exception of Prorate_EEV');
1213
1214 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
1215 p_hrly_rate := v_curr_hrly_rate;
1216
1217 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1218 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
1219
1220 RETURN v_eev_prorated_earnings;
1221
1222 END Prorate_EEV;
1223
1224 FUNCTION vacation_pay ( p_vac_hours IN OUT nocopy NUMBER,
1225 p_asg_id IN NUMBER,
1226 p_eff_date IN DATE,
1227 p_curr_rate IN NUMBER) RETURN NUMBER IS
1228
1229 l_vac_pay NUMBER(27,7) ;
1230 l_vac_hours NUMBER(10,7);
1231
1232 CURSOR get_vac_hours ( v_asg_id NUMBER,
1233 v_eff_date DATE) IS
1234 select fnd_number.canonical_to_number(pev.screen_entry_value)
1235 from per_absence_attendance_types abt,
1239 and abt.absence_category = 'V'
1236 pay_element_entries_f pee,
1237 pay_element_entry_values_f pev
1238 where pev.input_value_id = abt.input_value_id
1240 and v_eff_date between pev.effective_start_date
1241 and pev.effective_end_date
1242 and pee.element_entry_id = pev.element_entry_id
1243 and pee.assignment_id = v_asg_id
1244 and v_eff_date between pee.effective_start_date
1245 and pee.effective_end_date;
1246
1247 -- The "vacation_pay" fn looks for hours entered against absence types
1248 -- in the current period. The number of hours are summed and multiplied by
1249 -- the current rate of Regular Pay..
1250 -- Return immediately when no vacation time has been taken.
1251 -- Need to loop thru all "Vacation Plans" and check for entries in the current
1252 -- period for this assignment.
1253
1254 BEGIN
1255
1256 /* Init */
1257 l_vac_pay := 0;
1258
1259 hr_utility.set_location('get_vac_pay', 11);
1260 hr_utility.trace('Entered Vacation Pay');
1261
1262 OPEN get_vac_hours (p_asg_id, p_eff_date);
1263 LOOP
1264
1265 hr_utility.set_location('get_vac_pay', 13);
1266 hr_utility.trace('Opened get_vac_hours');
1267
1268 FETCH get_vac_hours
1269 INTO l_vac_hours;
1270 EXIT WHEN get_vac_hours%NOTFOUND;
1271
1272 p_vac_hours := p_vac_hours + l_vac_hours;
1273
1274 END LOOP;
1275 CLOSE get_vac_hours;
1276
1277 hr_utility.set_location('get_vac_pay', 15);
1278
1279 IF p_vac_hours <> 0 THEN
1280
1281 l_vac_pay := p_vac_hours * p_curr_rate;
1282
1283 END IF;
1284
1285 hr_utility.trace('Leaving Vacation Pay');
1286 RETURN l_vac_pay;
1287
1288 END vacation_pay;
1289
1290 FUNCTION sick_pay ( p_sick_hours IN OUT nocopy NUMBER,
1291 p_asg_id IN NUMBER,
1292 p_eff_date IN DATE,
1293 p_curr_rate IN NUMBER) RETURN NUMBER IS
1294
1295 l_sick_pay NUMBER(27,7) ;
1296 l_sick_hours NUMBER(10,7);
1297
1298 CURSOR get_sick_hours ( v_asg_id NUMBER,
1299 v_eff_date DATE) IS
1300 select fnd_number.canonical_to_number(pev.screen_entry_value)
1301 from per_absence_attendance_types abt,
1302 pay_element_entries_f pee,
1303 pay_element_entry_values_f pev
1304 where pev.input_value_id = abt.input_value_id
1305 and abt.absence_category = 'S'
1306 and v_eff_date between pev.effective_start_date
1307 and pev.effective_end_date
1308 and pee.element_entry_id = pev.element_entry_id
1309 and pee.assignment_id = v_asg_id
1310 and v_eff_date between pee.effective_start_date
1311 and pee.effective_end_date;
1312
1313 -- The "sick_pay" looks for hours entered against Sick absence types in
1314 -- the current period. The number of hours are summed and multiplied by the
1315 -- current rate of Regular Pay.
1316 -- Return immediately when no sick time has been taken.
1317
1318 BEGIN
1319
1320 /* Init */
1321 l_sick_pay :=0;
1322
1323 hr_utility.set_location('get_sick_pay', 11);
1324 hr_utility.trace('Entered Sick Pay');
1325
1326 OPEN get_sick_hours (p_asg_id, p_eff_date);
1327 LOOP
1328
1329 hr_utility.trace('get_sick_pay');
1330 hr_utility.set_location('get_sick_pay', 13);
1331
1332 FETCH get_sick_hours
1333 INTO l_sick_hours;
1334 EXIT WHEN get_sick_hours%NOTFOUND;
1335
1336 p_sick_hours := p_sick_hours + l_sick_hours;
1337
1338 END LOOP;
1339 CLOSE get_sick_hours;
1340
1341 hr_utility.set_location('get_sick_pay', 15);
1342 hr_utility.trace('get_sick_pay');
1343
1344 IF p_sick_hours <> 0 THEN
1345
1346 l_sick_pay := p_sick_hours * p_curr_rate;
1347
1348 END IF;
1349
1350 hr_utility.trace('Leaving get_sick_pay');
1351 RETURN l_sick_pay;
1352
1353 END sick_pay;
1354
1355 BEGIN -- Calculate_Period_Earnings
1356 --BEGINCALC
1357
1358 /* Init */
1359 v_period_earn := 0;
1360 v_prorated_earnings := 0;
1361 v_curr_hrly_rate := 0;
1362 l_mid_period_asg_change := FALSE;
1363
1364 -- hr_utility.trace_on(null,'coreff');
1365
1366 hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
1367 hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
1368 hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
1369 hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1370 hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1371 hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1372 hr_utility.trace('p_pay_basis ='||p_pay_basis);
1373 hr_utility.trace('p_inpval_name ='||p_inpval_name);
1374 hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1375 hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1376 hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1377 --hr_utility.trace('p_work_schedule ='||p_work_schedule);
1378 --hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1379 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1380 hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1381 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1382 hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1383 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1384 hr_utility.trace('UDFS p_prorate ='||p_prorate);
1388
1385 hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1386
1387 hr_utility.trace('Find earnings element input value id');
1389 p_actual_hours_worked := 0;
1390
1391 -- Step (1): Find earnings element input value.
1392 -- Get input value and pay basis according to salary admin (if exists).
1393 -- If not using salary admin, then get "Rate", "Rate Code", or "Monthly Salary"
1394 -- input value id as appropriate (according to ele name).
1395 IF g_legislation_code IS NULL THEN
1396 g_legislation_code := get_legislation_code(p_bus_grp_id);
1397 END IF;
1398 IF p_pay_basis IS NOT NULL THEN
1399
1400 BEGIN
1401
1402 hr_utility.trace(' p_pay_basis IS NOT NULL');
1403 hr_utility.set_location('calculate_period_earnings', 10);
1404
1405 SELECT PYB.input_value_id,
1406 FCL.meaning
1407 INTO v_inpval_id,
1408 v_pay_basis
1409 FROM per_assignments_f ASG,
1410 per_pay_bases PYB,
1411 hr_lookups FCL
1412 WHERE FCL.lookup_code = PYB.pay_basis
1413 AND FCL.lookup_type = 'PAY_BASIS'
1414 AND FCL.application_id = 800
1415 AND PYB.pay_basis_id = ASG.pay_basis_id
1416 AND ASG.assignment_id = p_asst_id
1417 AND p_date_earned BETWEEN ASG.effective_start_date
1418 AND ASG.effective_end_date;
1419
1420 EXCEPTION WHEN NO_DATA_FOUND THEN
1421 hr_utility.set_location('calculate_period_earnings', 11);
1422 hr_utility.trace(' In EXCEPTION p_pay_basis IS NOT NULL');
1423
1424 v_period_earn := 0;
1425 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1426
1427 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1428
1429 RETURN v_period_earn;
1430
1431
1432 END;
1433
1434 hr_utility.trace('p_inpval_name = '||p_inpval_name);
1435
1436 ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1437
1438 hr_utility.trace(' p_pay_basis IS NULL');
1439 hr_utility.trace('In p_inpval_name = RATE');
1440 /* Changed the element_name and name to init case and added
1441 the date join for pay_element_types_f */
1442
1443 begin
1444 SELECT IPV.input_value_id
1445 INTO v_inpval_id
1446 FROM pay_input_values_f IPV,
1447 pay_element_types_f ELT
1448 WHERE ELT.element_name = 'Regular Wages'
1449 and p_period_start BETWEEN ELT.effective_start_date
1450 AND ELT.effective_end_date
1451 and ELT.element_type_id = IPV.element_type_id
1452 and p_period_start BETWEEN IPV.effective_start_date
1453 AND IPV.effective_end_date
1454 and IPV.name = 'Rate'
1455 and ELT.legislation_code = g_legislation_code;
1456 --
1457 v_pay_basis := 'HOURLY';
1458 --
1459 EXCEPTION WHEN NO_DATA_FOUND THEN
1460
1461 hr_utility.trace('Exception of RATE ');
1462
1463 v_period_earn := 0;
1464 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1465
1466 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1467
1468 RETURN v_period_earn;
1469 end;
1470 --
1471 ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
1472 /* Changed the element_name and name to init case and added
1473 the date join for pay_element_types_f */
1474
1475 begin
1476 hr_utility.trace('In RATE CODE');
1477
1478 SELECT IPV.input_value_id
1479 INTO v_inpval_id
1480 FROM pay_input_values_f IPV,
1481 pay_element_types_f ELT
1482 WHERE ELT.element_name = 'Regular Wages'
1483 and p_period_start BETWEEN ELT.effective_start_date
1484 AND ELT.effective_end_date
1485 and ELT.element_type_id = IPV.element_type_id
1486 and p_period_start BETWEEN IPV.effective_start_date
1487 AND IPV.effective_end_date
1488 and IPV.name = 'Rate Code'
1489 and ELT.legislation_code = g_legislation_code;
1490 --
1491 v_pay_basis := 'HOURLY';
1492 --
1493 EXCEPTION WHEN NO_DATA_FOUND THEN
1494 hr_utility.trace('Exception of Rate Code');
1495
1496 v_period_earn := 0;
1497 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1498
1499 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1500
1501 RETURN v_period_earn;
1502
1503 end;
1504 --
1505 ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
1506
1507 /* Changed the element_name and name to init case and added
1508 the date join for pay_element_types_f */
1509
1510 begin
1511 hr_utility.trace('in MONTHLY SALARY');
1512
1513 SELECT IPV.input_value_id
1514 INTO v_inpval_id
1515 FROM pay_input_values_f IPV,
1516 pay_element_types_f ELT
1517 WHERE ELT.element_name = 'Regular Salary'
1518 and p_period_start BETWEEN ELT.effective_start_date
1519 AND ELT.effective_end_date
1520 and ELT.element_type_id = IPV.element_type_id
1521 and p_period_start BETWEEN IPV.effective_start_date
1522 AND IPV.effective_end_date
1523 and IPV.name = 'Monthly Salary'
1524 and ELT.legislation_code = g_legislation_code;
1525
1529 hr_utility.set_location('calculate_period_earnings', 18);
1526 v_pay_basis := 'MONTHLY';
1527
1528 EXCEPTION WHEN NO_DATA_FOUND THEN
1530 v_period_earn := 0;
1531 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1532 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1533 RETURN v_period_earn;
1534 END;
1535
1536 END IF;
1537
1538 hr_utility.trace('Now know the pay basis for this assignment');
1539 hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1540 hr_utility.trace('v_pay_basis ='||v_pay_basis);
1541 /*
1542 -- Now know the pay basis for this assignment (v_pay_basis).
1543 -- Want to convert entered earnings to pay period earnings.
1544 -- For pay basis of Annual, Monthly, Bi-Weekly, Semi-Monthly,
1545 -- or Period (ie. anything
1546 -- other than Hourly):
1547 -- Annualize entered earnings according to pay basis;
1548 -- then divide by number of payroll periods per fiscal
1549 -- yr for pay period earnings.
1550 -- 02 Dec 1993:
1551 -- Actually, passing in an "Hourly" figure from formula alleviates
1552 -- having to convert in here --> we have Convert_Period_Type fn
1553 -- available to formulae, so a Monthly Salary can be converted before
1554 -- calling this fn. Then we just find the hours scheduled for current period as
1555 -- per the Hourly pay basis algorithm below.
1556 --
1557 -- For Hourly pay basis:
1558 -- Get hours scheduled for the current period either from:
1559 -- 1. ASG work schedule
1560 -- 2. ORG default work schedule
1561 -- 3. ASG standard hours and frequency
1562 -- Multiply the hours scheduled for period by normal Hourly Rate (ie. from
1563 -- pre-defined earnings, REGULAR_WAGES_RATE) pay period earnings.
1564 --
1565 -- In either case, need to find the payroll period type, let's do it upfront:
1566 -- Assignment.payroll_id --> Payroll.period_type
1567 -- --> Per_time_period_types.number_per_fiscal_year.
1568 -- Actually, the number per fiscal year could be found in more than one way:
1569 -- Could also go to per_time_period_rules, but would mean decoding the
1570 -- payroll period type to an appropriate proc_period_type code.
1571 --
1572 */
1573
1574 -- Find # of payroll period types per fiscal year:
1575
1576 begin
1577
1578 hr_utility.trace('Find # of payroll period types per fiscal year');
1579 hr_utility.set_location('calculate_period_earnings', 40);
1580
1581 SELECT TPT.number_per_fiscal_year
1582 INTO v_pay_periods_per_year
1583 FROM pay_payrolls_f PRL,
1584 per_time_period_types TPT
1585 WHERE TPT.period_type = PRL.period_type
1586 AND p_period_end between PRL.effective_start_date
1587 and PRL.effective_end_date
1588 AND PRL.payroll_id = p_payroll_id
1589 AND PRL.business_group_id + 0 = p_bus_grp_id;
1590
1591 hr_utility.trace('v_pay_periods_per_year ='||to_char(v_pay_periods_per_year));
1592
1593 exception when NO_DATA_FOUND then
1594
1595 hr_utility.set_location('calculate_period_earnings', 41);
1596 hr_utility.trace('Exception Find # of payroll period');
1597 v_period_earn := 0;
1598 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1599 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1600
1601 RETURN v_period_earn;
1602
1603 end;
1604
1605 /*
1606 -- Pay basis is hourly,
1607 -- Get hours scheduled for the current period either from:
1608 -- 1. ASG work schedule
1609 -- 2. ORG default work schedule
1610 -- 3. ASG standard hours and frequency
1611 -- Do we pass in Work Schedule from asst scl db item? Yes
1612 -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
1613 -- a weekly figure. We also no longer use a week as
1614 -- the basis for annualization,
1615 -- even when using work schedule - ie. need to find ACTUAL
1616 -- scheduled hours, not
1617 -- actual hours for a week, converted to a period figure.
1618 */
1619 --
1620 hr_utility.set_location('calculate_period_earnings', 45);
1621 hr_utility.trace('Get hours scheduled for the current period');
1622
1623 /*IF p_work_schedule <> 'NOT ENTERED' THEN
1624 --
1625 -- Find hours worked between period start and end dates.
1626 --
1627 hr_utility.trace('Asg has Work Schedule');
1628 hr_utility.trace('p_work_schedule ='||p_work_schedule);
1629
1630 v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
1631 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1632 --
1633 SELECT user_column_name
1634 INTO v_work_sched_name
1635 FROM pay_user_columns
1636 WHERE user_column_id = v_ws_id
1637 AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
1638 AND NVL(legislation_code,'US') = 'US';
1639
1640 hr_utility.trace('v_work_sched_name ='||v_work_sched_name);
1641 hr_utility.trace('Calling Work_Schedule_Total_Hours');
1642
1643 v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
1644 v_work_sched_name,
1645 p_period_start,
1646 p_period_end);
1647 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1648 ELSE
1649
1650 hr_utility.trace('Asg has No Work Schedule');
1651 hr_utility.trace('Calling Standard_Hours_Worked');
1652
1653 v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
1654 p_period_start,
1655 p_period_end,
1656 p_asst_std_freq);
1660 */
1657 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1658
1659 END IF;
1661
1662 v_hrs_per_range := pay_core_ff_udfs.calculate_actual_hours_worked (
1663 null
1664 ,p_asst_id
1665 ,p_bus_grp_id
1666 ,p_ele_entry_id
1667 ,p_date_earned
1668 ,p_period_start
1669 ,p_period_end
1670 ,NULL
1671 ,'Y'
1672 ,'BUSY'
1673 ,''--p_legislation_code
1674 ,v_schedule_source
1675 ,v_schedule
1676 ,v_return_status
1677 ,v_return_message);
1678 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1679
1680 hr_utility.trace('Compute earnings and actual hours');
1681 hr_utility.trace('calling convert_period_type from calculate_period_earnings');
1682 hr_utility.set_location('calculate_period_earnings', 46);
1683
1684 v_period_earn := Convert_Period_Type(p_bus_grp_id
1685 ,p_asst_id
1686 ,p_payroll_id
1687 ,p_ele_entry_id
1688 ,p_date_earned
1689 ,p_assignment_action_id
1690 ,p_period_start -- period start date
1691 ,p_period_end -- period end date
1692 ,p_ass_hrly_figure -- p_figure, salary amount
1693 ,'HOURLY' -- p_from freq, salary basis
1694 ,NULL); -- p_to_freq
1695
1696 /*Convert_Period_Type( p_bus_grp_id,
1697 p_payroll_id,
1698 p_work_schedule,
1699 p_asst_std_hrs,
1700 p_ass_hrly_figure,
1701 'HOURLY',
1702 NULL,
1703 p_period_start,
1704 p_period_end,
1705 p_asst_std_freq); */
1706
1707 hr_utility.trace('v_period_earn ='||to_char(v_period_earn));
1708 hr_utility.set_location('calculate_period_earnings', 47);
1709
1710 p_actual_hours_worked := v_hrs_per_range;
1711
1712 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1713
1714 IF p_prorate = 'N' THEN
1715
1716 hr_utility.trace('No proration');
1717 hr_utility.trace('Calling p_vac_pay');
1718 hr_utility.set_location('calculate_period_earnings', 49);
1719
1720 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1721 p_asg_id => p_asst_id,
1722 p_eff_date => p_period_end,
1723 p_curr_rate => p_ass_hrly_figure);
1724
1725 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1726
1727 hr_utility.trace('Calling sick Pay');
1728 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1729 p_asg_id => p_asst_id,
1730 p_eff_date => p_period_end,
1731 p_curr_rate => p_ass_hrly_figure);
1732
1733
1734 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1735
1736 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1737
1738 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1739 hr_utility.trace('UDFS v_period_earn ='||to_char(v_period_earn));
1740
1741 RETURN v_period_earn;
1742
1743 END IF; /* IF p_prorate = 'N' */
1744
1745
1746 hr_utility.trace('UDFS check for ASGMPE changes');
1747 hr_utility.set_location('calculate_period_earnings', 51);
1748 /* ************************************************************** */
1749
1750 BEGIN /* Check ASGMPE */
1751
1752 select 1 INTO l_asg_info_changes
1753 from dual
1754 where exists (
1755 SELECT 1
1756 FROM per_assignments_f ASG,
1757 per_assignment_status_types AST,
1758 hr_soft_coding_keyflex SCL
1759 WHERE ASG.assignment_id = p_asst_id
1760 AND ASG.effective_start_date <= p_period_start
1761 AND ASG.effective_end_date >= p_period_start
1762 AND ASG.effective_end_date < p_period_end
1763 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1764 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1765 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1766 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1767 AND SCL.enabled_flag = 'Y' );
1768
1769 hr_utility.trace('ASGMPE Changes found');
1770 hr_utility.trace('Need to prorate b/c of ASGMPE');
1771 hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1772
1773 l_mid_period_asg_change := TRUE;
1774
1775 hr_utility.set_location('calculate_period_earnings', 56);
1776 hr_utility.trace('Look for EEVMPE changes');
1777
1778 BEGIN /* EEVMPE check - maybe pick*/
1779
1780 select 1 INTO l_eev_info_changes
1781 from dual
1782 where exists (
1783 SELECT 1
1784 FROM pay_element_entry_values_f EEV
1785 WHERE EEV.element_entry_id = p_ele_entry_id
1786 AND EEV.input_value_id+0 = v_inpval_id
1787 AND ( ( EEV.effective_start_date <= p_period_start
1791 ) );
1788 AND EEV.effective_end_date >= p_period_start
1789 AND EEV.effective_end_date < p_period_end)
1790 OR ( EEV.effective_start_date between p_period_start and p_period_end)
1792
1793
1794
1795 hr_utility.trace('EEVMPE changes found after ASGMPE');
1796
1797 EXCEPTION
1798
1799 WHEN NO_DATA_FOUND THEN
1800 l_eev_info_changes := 0;
1801
1802 hr_utility.trace('From EXCEPTION ASGMPE changes found No EEVMPE changes');
1803
1804 END; /* EEV1 check*/
1805
1806 EXCEPTION
1807
1808 WHEN NO_DATA_FOUND THEN
1809
1810 l_asg_info_changes := 0;
1811 hr_utility.trace('From EXCEPTION No ASGMPE changes, nor EEVMPE changes');
1812
1813 END; /* ASGMPE check*/
1814
1815 /* ************************************************ */
1816
1817 IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
1818
1819 hr_utility.trace(' Into l_asg_info_changes = 0');
1820 hr_utility.trace('UDFS looking for ASGMPS changes');
1821 hr_utility.set_location('calculate_period_earnings', 56);
1822
1823 BEGIN /* ASGMPS changes */
1824
1825 select 1 INTO l_asg_info_changes
1826 from dual
1827 where exists (
1828 SELECT 1
1829 FROM per_assignments_f ASG,
1830 per_assignment_status_types AST,
1831 hr_soft_coding_keyflex SCL
1832 WHERE ASG.assignment_id = p_asst_id
1833 AND ASG.effective_start_date > p_period_start
1834 AND ASG.effective_start_date <= p_period_end
1835 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1836 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1837 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1838 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1839 AND SCL.enabled_flag = 'Y');
1840
1841 l_mid_period_asg_change := TRUE;
1842
1843 hr_utility.trace('Need to prorate for ASGMPS changes');
1844 hr_utility.set_location('calculate_period_earnings', 57);
1845
1846 BEGIN /* EEVMPE changes ASGMPS */
1847
1848 select 1 INTO l_eev_info_changes
1849 from dual
1850 where exists (
1851 SELECT 1
1852 FROM pay_element_entry_values_f EEV
1853 WHERE EEV.element_entry_id = p_ele_entry_id
1854 AND EEV.input_value_id+0 = v_inpval_id
1855 AND ( ( EEV.effective_start_date <= p_period_start
1856 AND EEV.effective_end_date >= p_period_start
1857 AND EEV.effective_end_date < p_period_end)
1858 --OR ( EEV.effective_start_date between p_period_start and p_period_end)
1859 ) );
1860
1861
1862 hr_utility.trace('Need to prorate EEVMPS changes after ASGMPS ');
1863
1864 EXCEPTION
1865
1866 WHEN NO_DATA_FOUND THEN
1867
1868 l_eev_info_changes := 0;
1869
1870 hr_utility.trace('From EXCEPTIION No EEVMPE changes');
1871
1872 END; /* EEVMPE changes */
1873
1874 EXCEPTION
1875
1876 WHEN NO_DATA_FOUND THEN
1877
1878 l_asg_info_changes := 0;
1879
1880 hr_utility.trace('From EXCEPTION no changes due to ASGMPS or EEVMPE');
1881
1882 END; /* ASGMPS changes */
1883
1884 END IF; /* Check ASGMPS */
1885
1886 /* *************************************************** */
1887
1888 IF l_asg_info_changes = 0 THEN /* ASGMPE=0 and ASGMPS=0 */
1889
1890 BEGIN /* Check for EEVMPE changes */
1891
1892 hr_utility.set_location('calculate_period_earnings', 58);
1893 hr_utility.trace('Check for EEVMPE changes nevertheless');
1894
1895 select 1 INTO l_eev_info_changes
1896 from dual
1897 where exists (
1898 SELECT 1
1899 FROM pay_element_entry_values_f EEV
1900 WHERE EEV.element_entry_id = p_ele_entry_id
1901 AND EEV.input_value_id+0 = v_inpval_id
1902 AND EEV.effective_start_date <= p_period_start
1903 AND EEV.effective_end_date >= p_period_start
1904 AND EEV.effective_end_date < p_period_end);
1905
1906 hr_utility.trace('Proration due to EEVMPE changes');
1907
1908
1909 EXCEPTION
1910
1911 WHEN NO_DATA_FOUND THEN
1912
1913 hr_utility.trace('ASG AND EEV changes DO NOT EXIST EXCEPT ');
1914
1915 -- Either there are no changes to an Active Assignment OR
1916 -- the assignment was not active at all this period.
1917 -- Check assignment status of current asg record.
1918
1919 hr_utility.trace(' Check assignment status of current asg record');
1920
1921 SELECT AST.per_system_status
1922 INTO v_asg_status
1923 FROM per_assignments_f ASG,
1924 per_assignment_status_types AST,
1925 hr_soft_coding_keyflex SCL
1926 WHERE ASG.assignment_id = p_asst_id
1927 AND p_period_start BETWEEN ASG.effective_start_date
1928 AND ASG.effective_end_date
1929 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1930 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1931 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1932 AND SCL.enabled_flag = 'Y';
1933
1934 IF v_asg_status <> 'ACTIVE_ASSIGN' THEN
1935
1939
1936 hr_utility.trace(' Asg not active');
1937 v_period_earn := 0;
1938 p_actual_hours_worked := 0;
1940 END IF;
1941
1942 hr_utility.trace('Chk for vac pay since no ASG EEV changes to prorate' );
1943
1944 p_vac_pay := vacation_pay(p_vac_hours => p_vac_hours_worked,
1945 p_asg_id => p_asst_id,
1946 p_eff_date => p_period_end,
1947 p_curr_rate => p_ass_hrly_figure);
1948
1949 hr_utility.trace('p_vac_pay ='||p_vac_pay);
1950 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1951 p_asg_id => p_asst_id,
1952 p_eff_date => p_period_end,
1953 p_curr_rate => p_ass_hrly_figure);
1954
1955
1956 hr_utility.trace('p_sick_pay ='||p_sick_pay);
1957
1958 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1959 RETURN v_period_earn;
1960
1961 END; /* Check for EEVMPE changes */
1962
1963 END IF; /* ASGMPE=0 ASGMPS =0 */
1964
1965 /* **************************************************************
1966 If code reaches here, then we're prorating for one reason or the other.
1967 ***************************************************************** */
1968
1969
1970 IF (l_asg_info_changes > 0) AND (l_eev_info_changes = 0) THEN /*ASG =1 EEV =0*/
1971
1972
1973 /* ************** ONLY ASG CHANGES START **** */
1974
1975 p_actual_hours_worked := 0;
1976 hr_utility.set_location('calculate_period_earnings', 70);
1977 hr_utility.trace('UDFS ONLY ASG CHANGES START');
1978
1979 BEGIN /* Get Asg Details ASGMPE */
1980
1981 hr_utility.trace('Get Asg details - ASGMPE');
1982 hr_utility.set_location('calculate_period_earnings', 71);
1983
1984 SELECT GREATEST(ASG.effective_start_date, p_period_start),
1985 ASG.effective_end_date,
1986 NVL(ASG.NORMAL_HOURS, 0),
1987 NVL(HRL.meaning, 'NOT ENTERED'),
1988 NVL(SCL.segment4, 'NOT ENTERED')
1989 INTO v_range_start,
1990 v_range_end,
1991 v_asst_std_hrs,
1992 v_asst_std_freq,
1993 v_work_schedule
1994 FROM per_assignments_f ASG,
1995 per_assignment_status_types AST,
1996 hr_soft_coding_keyflex SCL,
1997 hr_lookups HRL
1998 WHERE ASG.assignment_id = p_asst_id
1999 AND ASG.business_group_id + 0 = p_bus_grp_id
2000 AND ASG.effective_start_date <= p_period_start
2001 AND ASG.effective_end_date >= p_period_start
2002 AND ASG.effective_end_date < p_period_end
2003 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2004 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2005 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2006 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2007 AND SCL.enabled_flag = 'Y'
2008 AND HRL.lookup_code(+) = ASG.frequency
2009 AND HRL.lookup_type(+) = 'FREQUENCY';
2010
2011
2012 hr_utility.trace('If ASGMPE Details succ. then Calling Prorate_Earnings');
2013 hr_utility.set_location('calculate_period_earnings', 72);
2014 v_prorated_earnings := v_prorated_earnings +
2015 Prorate_Earnings (
2016 p_bg_id => p_bus_grp_id,
2017 p_asg_hrly_rate => p_ass_hrly_figure,
2018 p_range_start_date => v_range_start,
2019 p_range_end_date => v_range_end,
2020 p_act_hrs_worked => p_actual_hours_worked);
2021
2022 hr_utility.trace('After Calling Prorate_Earnings');
2023
2024 EXCEPTION WHEN NO_DATA_FOUND THEN
2025
2026 NULL;
2027
2028 END; /* Get Asg Details */
2029
2030
2031 hr_utility.trace('ONLY ASG , select MULTIASG');
2032 hr_utility.set_location('calculate_period_earnings', 77);
2033
2034 OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
2035 LOOP
2036
2037 FETCH get_asst_chgs
2038 INTO v_range_start,
2039 v_range_end,
2040 v_asst_std_hrs,
2041 v_asst_std_freq,
2042 v_work_schedule;
2043 EXIT WHEN get_asst_chgs%NOTFOUND;
2044 hr_utility.set_location('calculate_period_earnings', 79);
2045
2046
2047 hr_utility.trace('ONLY ASG Calling Prorate_Earning as MULTIASG successful');
2048
2049 v_prorated_earnings := v_prorated_earnings +
2050 Prorate_Earnings (
2051 p_bg_id => p_bus_grp_id,
2052 p_asg_hrly_rate => p_ass_hrly_figure,
2053 p_range_start_date => v_range_start,
2054 p_range_end_date => v_range_end,
2055 p_act_hrs_worked => p_actual_hours_worked);
2056
2057
2058 hr_utility.trace('After calling Prorate_Earnings from MULTIASG');
2059
2060 END LOOP;
2061
2062 CLOSE get_asst_chgs;
2063
2064 BEGIN /* END_SPAN_RECORD */
2065
2066 hr_utility.set_location('calculate_period_earnings', 89);
2067 hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
2068
2069 SELECT ASG.effective_start_date,
2070 LEAST(ASG.effective_end_date, p_period_end),
2071 NVL(ASG.normal_hours, 0),
2072 NVL(HRL.meaning, 'NOT ENTERED'),
2073 NVL(SCL.segment4, 'NOT ENTERED')
2074 INTO v_range_start,
2075 v_range_end,
2076 v_asst_std_hrs,
2077 v_asst_std_freq,
2078 v_work_schedule
2079 FROM hr_soft_coding_keyflex SCL,
2080 per_assignment_status_types AST,
2081 per_assignments_f ASG,
2082 hr_lookups HRL
2086 AND ASG.effective_start_date <= p_period_end
2083 WHERE ASG.assignment_id = p_asst_id
2084 AND ASG.business_group_id + 0 = p_bus_grp_id
2085 AND ASG.effective_start_date > p_period_start
2087 AND ASG.effective_end_date > p_period_end
2088 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2089 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2090 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2091 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2092 AND SCL.enabled_flag = 'Y'
2093 AND HRL.lookup_code(+) = ASG.frequency
2094 AND HRL.lookup_type(+) = 'FREQUENCY';
2095
2096 hr_utility.trace('Calling Prorate_Earnings for ONLY ASG END_SPAN_RECORD');
2097 hr_utility.set_location('calculate_period_earnings', 91);
2098 v_prorated_earnings := v_prorated_earnings +
2099 Prorate_Earnings (
2100 p_bg_id => p_bus_grp_id,
2101 p_asg_hrly_rate => p_ass_hrly_figure,
2102 p_range_start_date => v_range_start,
2103 p_range_end_date => v_range_end,
2104 p_act_hrs_worked => p_actual_hours_worked);
2105
2106
2107 hr_utility.trace('Calling Vacation Pay as END_SPAN succ');
2108 hr_utility.set_location('calculate_period_earnings', 101);
2109
2110 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2111 p_asg_id => p_asst_id,
2112 p_eff_date => p_period_end,
2113 p_curr_rate => p_ass_hrly_figure);
2114
2115 hr_utility.trace('Calling Sick Pay as ASG3 succ');
2116
2117 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2118 p_asg_id => p_asst_id,
2119 p_eff_date => p_period_end,
2120 p_curr_rate => p_ass_hrly_figure);
2121
2122
2123 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2124 RETURN v_prorated_earnings;
2125
2126 EXCEPTION WHEN NO_DATA_FOUND THEN
2127 hr_utility.set_location('calculate_period_earnings', 102);
2128 hr_utility.trace('Exception of ASG_MID_START_LAST_SPAN_END_DT');
2129
2130 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2131 p_asg_id => p_asst_id,
2132 p_eff_date => p_period_end,
2133 p_curr_rate => p_ass_hrly_figure);
2134
2135 hr_utility.trace('Calling Sick Pay as ASG3 not succ');
2136 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2137 p_asg_id => p_asst_id,
2138 p_eff_date => p_period_end,
2139 p_curr_rate => p_ass_hrly_figure);
2140
2141
2142 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2143 RETURN v_prorated_earnings;
2144
2145 END; /* ASG_MID_START_LAST_SPAN_END_DT */
2146
2147 /* ************** ONLY ASG CHANGES END **** */
2148
2149
2150 ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
2151
2152 /* ******************* ONLY EEV CHANGES START ****** */
2153
2154 hr_utility.trace(' Only EEV changes exist');
2155 hr_utility.set_location('calculate_period_earnings', 103);
2156 p_actual_hours_worked := 0;
2157
2158
2159 hr_utility.trace('Calling Prorate_EEV');
2160
2161 v_prorated_earnings := v_prorated_earnings +
2162 Prorate_EEV (
2163 p_bus_group_id => p_bus_grp_id,
2164 p_pay_id => p_payroll_id,
2165 p_pay_basis => p_pay_basis,
2166 p_hrly_rate => v_curr_hrly_rate,
2167 p_range_start_date => p_period_start,
2168 p_range_end_date => p_period_end,
2169 p_actual_hrs_worked => p_actual_hours_worked,
2170 p_element_entry_id => p_ele_entry_id,
2171 p_inpval_id => v_inpval_id);
2172
2173 hr_utility.trace('After Calling Prorate_EEV');
2174 hr_utility.set_location('calculate_period_earnings', 127);
2175
2176 hr_utility.trace('Calling vacation_pay');
2177
2178 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2179 p_asg_id => p_asst_id,
2180 p_eff_date => p_period_end,
2181 p_curr_rate => p_ass_hrly_figure);
2182
2183 hr_utility.trace('Calling sick_pay');
2184
2185 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2186 p_asg_id => p_asst_id,
2187 p_eff_date => p_period_end,
2188 p_curr_rate => p_ass_hrly_figure);
2189
2190
2191 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2192 RETURN v_prorated_earnings;
2193
2194 /* ******************* ONLY EEV CHANGES END ****** */
2195
2196 ELSE /*BOTH ASG AND EEV CHANGES =0*/
2197
2198 /* ******************* BOTH ASG AND EEV CHANGES START ************ */
2199
2200
2201 hr_utility.trace('UDFS BOTH ASG and EEV chages exist');
2202
2203
2204 p_actual_hours_worked := 0;
2205
2206
2207 BEGIN /* Latest Screen Entry Value */
2208
2209 hr_utility.trace('BOTH ASG Get latest screen entry value for EEVMPE');
2210 hr_utility.set_location('calculate_period_earnings', 128);
2211
2212 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2213 INTO v_earnings_entry
2214 FROM pay_element_entry_values_f EEV
2215 WHERE EEV.element_entry_id = p_ele_entry_id
2216 AND EEV.input_value_id = v_inpval_id
2217 AND p_period_start between EEV.effective_start_date
2218 AND EEV.effective_end_date;
2219 /*4750302
2220 AND EEV.effective_start_date <= p_period_start
2221 AND EEV.effective_end_date > p_period_start;
2222 */
2223 --AND EEV.effective_end_date < p_period_end
2227 SELECT GREATEST(ASG.effective_start_date, p_period_start),
2224
2225 hr_utility.trace('BOTH ASG Get ASGMPE ');
2226
2228 ASG.effective_end_date,
2229 NVL(ASG.NORMAL_HOURS, 0),
2230 NVL(HRL.meaning, 'NOT ENTERED'),
2231 NVL(SCL.segment4, 'NOT ENTERED')
2232 INTO v_range_start,
2233 v_range_end,
2234 v_asst_std_hrs,
2235 v_asst_std_freq,
2236 v_work_schedule
2237 FROM per_assignments_f ASG,
2238 per_assignment_status_types AST,
2239 hr_soft_coding_keyflex SCL,
2240 hr_lookups HRL
2241 WHERE ASG.assignment_id = p_asst_id
2242 AND ASG.business_group_id + 0 = p_bus_grp_id
2243 AND ASG.effective_start_date <= p_period_start
2244 AND ASG.effective_end_date >= p_period_start
2245 AND ASG.effective_end_date < p_period_end
2246 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2247 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2248 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2249 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2250 AND SCL.enabled_flag = 'Y'
2251 AND HRL.lookup_code(+) = ASG.frequency
2252 AND HRL.lookup_type(+) = 'FREQUENCY';
2253
2254 hr_utility.trace('Calling Convert_Period_Type from ASGMPE');
2255 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
2256
2257 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2258 ,p_asst_id
2259 ,p_payroll_id
2260 ,p_ele_entry_id
2261 ,p_date_earned
2262 ,p_assignment_action_id
2263 ,p_period_start -- period start date
2264 ,p_period_end -- period end date
2265 ,v_earnings_entry -- p_figure, salary amount
2266 ,v_pay_basis -- p_from freq, salary basis
2267 ,'HOURLY'); -- p_to_freq
2268 /*Convert_Period_Type( p_bus_grp_id,
2269 p_payroll_id,
2270 v_work_schedule,
2271 v_asst_std_hrs,
2272 v_earnings_entry,
2273 v_pay_basis,
2274 'HOURLY',
2275 p_period_start,
2276 p_period_end,
2277 v_asst_std_freq);*/
2278
2279 hr_utility.trace('Select app. EEVMPE again after range is determined');
2280 hr_utility.set_location('calculate_period_earnings', 130);
2281
2282 SELECT COUNT(EEV.element_entry_value_id)
2283 INTO l_eev_info_changes
2284 FROM pay_element_entry_values_f EEV
2285 WHERE EEV.element_entry_id = p_ele_entry_id
2286 AND EEV.input_value_id = v_inpval_id
2287 AND EEV.effective_start_date <= v_range_start
2288 AND EEV.effective_end_date >= v_range_start
2289 AND EEV.effective_end_date < v_range_end;
2290
2291 IF l_eev_info_changes = 0 THEN
2292
2293
2294 hr_utility.trace('NO EEVMPE changes');
2295 hr_utility.set_location('calculate_period_earnings', 132);
2296
2297 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2298 INTO v_earnings_entry
2299 FROM pay_element_entry_values_f EEV
2300 WHERE EEV.element_entry_id = p_ele_entry_id
2301 AND EEV.input_value_id = v_inpval_id
2302 AND v_range_end BETWEEN EEV.effective_start_date
2303 AND EEV.effective_end_date;
2304
2305 hr_utility.trace('Calling Convert_Period_Type');
2306 hr_utility.set_location('calculate_period_earnings', 134);
2307
2308 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2309 ,p_asst_id
2310 ,p_payroll_id
2311 ,p_ele_entry_id
2312 ,p_date_earned
2313 ,p_assignment_action_id
2314 ,p_period_start -- period start date
2315 ,p_period_end -- period end date
2316 ,v_earnings_entry -- p_figure, salary amount
2317 ,v_pay_basis -- p_from freq, salary basis
2318 ,'HOURLY'); -- p_to_freq
2319 /*Convert_Period_Type( p_bus_grp_id,
2320 p_payroll_id,
2321 v_work_schedule,
2322 v_asst_std_hrs,
2323 v_earnings_entry,
2324 v_pay_basis,
2325 'HOURLY',
2326 p_period_start,
2327 p_period_end,
2328 v_asst_std_freq);*/
2329
2330 hr_utility.trace('Calling Prorate_Earnings');
2331 hr_utility.set_location('calculate_period_earnings', 135);
2332
2333 v_prorated_earnings := v_prorated_earnings +
2334 Prorate_Earnings (
2335 p_bg_id => p_bus_grp_id,
2336 p_asg_hrly_rate => v_curr_hrly_rate,
2337 p_range_start_date => v_range_start,
2338 p_range_end_date => v_range_end,
2339 p_act_hrs_worked => p_actual_hours_worked);
2340
2341 hr_utility.set_location('calculate_period_earnings', 137);
2342
2343 ELSE
2344 -- Do proration for this ASG range by EEV !
2345
2346 hr_utility.trace('EEVMPE True');
2350 hr_utility.trace('Calling Prorate_EEV');
2347 hr_utility.trace('Do proration for this ASG range by EEV');
2348 hr_utility.set_location('calculate_period_earnings', 139);
2349
2351
2352 v_prorated_earnings := v_prorated_earnings +
2353 Prorate_EEV (
2354 p_bus_group_id => p_bus_grp_id,
2355 p_pay_id => p_payroll_id,
2356 p_pay_basis => v_pay_basis,
2357 p_hrly_rate => v_curr_hrly_rate,
2358 p_range_start_date => v_range_start,
2359 p_range_end_date => v_range_end,
2360 p_actual_hrs_worked => p_actual_hours_worked,
2361 p_element_entry_id => p_ele_entry_id,
2362 p_inpval_id => v_inpval_id);
2363 hr_utility.set_location('calculate_period_earnings', 140);
2364
2365 END IF; -- EEV info changes
2366
2367 EXCEPTION WHEN NO_DATA_FOUND THEN
2368 NULL;
2369
2370 END; /* Latest Screen Entry Value */
2371
2372 hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
2373 hr_utility.set_location('calculate_period_earnings', 141);
2374
2375 OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
2376 LOOP
2377
2378 FETCH get_asst_chgs
2379 INTO v_range_start,
2380 v_range_end,
2381 v_asst_std_hrs,
2382 v_asst_std_freq,
2383 v_work_schedule;
2384 EXIT WHEN get_asst_chgs%NOTFOUND;
2385
2386 --EEV_BEFORE_RANGE_END
2387 hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
2388 hr_utility.set_location('calculate_period_earnings', 145);
2389
2390 SELECT COUNT(EEV.element_entry_value_id)
2391 INTO l_eev_info_changes
2392 FROM pay_element_entry_values_f EEV
2393 WHERE EEV.element_entry_id = p_ele_entry_id
2394 AND EEV.input_value_id = v_inpval_id
2395 AND EEV.effective_start_date <= v_range_start
2396 AND EEV.effective_end_date >= v_range_start
2397 AND EEV.effective_end_date < v_range_end;
2398
2399 IF l_eev_info_changes = 0 THEN /* IF l_eev_info_changes = 0 */
2400
2401 -- EEV_FOR_CURR_RANGE_END
2402
2403 hr_utility.trace('BOTH ASG - EEV false');
2404 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2405 INTO v_earnings_entry
2406 FROM pay_element_entry_values_f EEV
2407 WHERE EEV.element_entry_id = p_ele_entry_id
2408 AND EEV.input_value_id = v_inpval_id
2409 AND v_range_end BETWEEN EEV.effective_start_date
2410 AND EEV.effective_end_date;
2411 hr_utility.set_location('calculate_period_earnings', 150);
2412 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2413 ,p_asst_id
2414 ,p_payroll_id
2415 ,p_ele_entry_id
2416 ,p_date_earned
2417 ,p_assignment_action_id
2418 ,p_period_start -- period start date
2419 ,p_period_end -- period end date
2420 ,v_earnings_entry -- p_figure, salary amount
2421 ,v_pay_basis -- p_from freq, salary basis
2422 ,'HOURLY'); -- p_to_freq
2423 /*Convert_Period_Type( p_bus_grp_id,
2424 p_payroll_id,
2425 v_work_schedule,
2426 v_asst_std_hrs,
2427 v_earnings_entry,
2428 v_pay_basis,
2429 'HOURLY',
2430 p_period_start,
2431 p_period_end,
2432 v_asst_std_freq);*/
2433
2434 v_prorated_earnings := v_prorated_earnings +
2435 Prorate_Earnings (
2436 p_bg_id => p_bus_grp_id,
2437 p_asg_hrly_rate => v_curr_hrly_rate,
2438 p_range_start_date => v_range_start,
2439 p_range_end_date => v_range_end,
2440 p_act_hrs_worked => p_actual_hours_worked);
2441
2442 hr_utility.set_location('calculate_period_earnings', 155);
2443 ELSE
2444 hr_utility.trace('BOTH ASG - EEV true');
2445 v_prorated_earnings := v_prorated_earnings +
2446 Prorate_EEV (
2447 p_bus_group_id => p_bus_grp_id,
2448 p_pay_id => p_payroll_id,
2449 p_pay_basis => v_pay_basis,
2450 p_hrly_rate => v_curr_hrly_rate,
2451 p_range_start_date => v_range_start,
2452 p_range_end_date => v_range_end,
2453 p_actual_hrs_worked => p_actual_hours_worked,
2454 p_element_entry_id => p_ele_entry_id,
2455 p_inpval_id => v_inpval_id);
2456
2457 END IF; /* IF l_eev_info_changes = 0 */
2458
2459 END LOOP;
2460
2461 CLOSE get_asst_chgs;
2462
2463
2464 BEGIN /* SPAN_RECORD */
2465
2466 hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
2467 hr_utility.set_location('calculate_period_earnings', 160);
2468
2469 SELECT ASG.effective_start_date,
2470 LEAST(ASG.effective_end_date, p_period_end),
2471 NVL(ASG.normal_hours, 0),
2472 NVL(HRL.meaning, 'NOT ENTERED'),
2473 NVL(SCL.segment4, 'NOT ENTERED')
2474 INTO v_range_start,
2475 v_range_end,
2476 v_asst_std_hrs,
2477 v_asst_std_freq,
2478 v_work_schedule
2479 FROM hr_soft_coding_keyflex SCL,
2480 per_assignment_status_types AST,
2481 per_assignments_f ASG,
2482 hr_lookups HRL
2486 AND ASG.effective_start_date <= p_period_end
2483 WHERE ASG.assignment_id = p_asst_id
2484 AND ASG.business_group_id + 0 = p_bus_grp_id
2485 AND ASG.effective_start_date > p_period_start
2487 AND ASG.effective_end_date > p_period_end
2488 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2489 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2490 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2491 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2492 AND SCL.enabled_flag = 'Y'
2493 AND HRL.lookup_code(+) = ASG.frequency
2494 AND HRL.lookup_type(+) = 'FREQUENCY';
2495
2496
2497
2498 hr_utility.trace('SELECT EEVMPE');
2499
2500 SELECT COUNT(EEV.element_entry_value_id)
2501 INTO l_eev_info_changes
2502 FROM pay_element_entry_values_f EEV
2503 WHERE EEV.element_entry_id = p_ele_entry_id
2504 AND EEV.input_value_id = v_inpval_id
2505 AND EEV.effective_start_date <= v_range_start
2506 AND EEV.effective_end_date >= v_range_start
2507 AND EEV.effective_end_date < v_range_end;
2508
2509 IF l_eev_info_changes = 0 THEN
2510
2511 hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
2512 hr_utility.set_location('calculate_period_earnings', 165);
2513
2514 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2515 INTO v_earnings_entry
2516 FROM pay_element_entry_values_f EEV
2517 WHERE EEV.element_entry_id = p_ele_entry_id
2518 AND EEV.input_value_id = v_inpval_id
2519 AND v_range_end BETWEEN EEV.effective_start_date
2520 AND EEV.effective_end_date;
2521
2522 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2523 ,p_asst_id
2524 ,p_payroll_id
2525 ,p_ele_entry_id
2526 ,p_date_earned
2527 ,p_assignment_action_id
2528 ,p_period_start -- period start date
2529 ,p_period_end -- period end date
2530 ,v_earnings_entry -- p_figure, salary amount
2531 ,v_pay_basis -- p_from freq, salary basis
2532 ,'HOURLY'); -- p_to_freq
2533 /*Convert_Period_Type( p_bus_grp_id,
2534 p_payroll_id,
2535 p_work_schedule,
2536 p_asst_std_hrs,
2537 v_earnings_entry,
2538 v_pay_basis,
2539 'HOURLY',
2540 p_period_start,
2541 p_period_end,
2542 v_asst_std_freq);*/
2543
2544 v_prorated_earnings := v_prorated_earnings +
2545 Prorate_Earnings (
2546 p_bg_id => p_bus_grp_id,
2547 p_asg_hrly_rate => v_curr_hrly_rate,
2548 p_range_start_date => v_range_start,
2549 p_range_end_date => v_range_end,
2550 p_act_hrs_worked => p_actual_hours_worked);
2551
2552 hr_utility.set_location('calculate_period_earnings', 170);
2553 ELSE /* EEV succ */
2554
2555 hr_utility.trace('BOTH ASG END_SPAN - EEV true');
2556 v_prorated_earnings := v_prorated_earnings +
2557 Prorate_EEV (
2558 p_bus_group_id => p_bus_grp_id,
2559 p_pay_id => p_payroll_id,
2560 p_pay_basis => v_pay_basis,
2561 p_hrly_rate => v_curr_hrly_rate,
2562 p_range_start_date => v_range_start,
2563 p_range_end_date => v_range_end,
2564 p_actual_hrs_worked => p_actual_hours_worked,
2565 p_element_entry_id => p_ele_entry_id,
2566 p_inpval_id => v_inpval_id);
2567 hr_utility.set_location('calculate_period_earnings', 175);
2568 END IF;
2569
2570
2571 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2572 p_asg_id => p_asst_id,
2573 p_eff_date => p_period_end,
2574 p_curr_rate => p_ass_hrly_figure);
2575 hr_utility.set_location('calculate_period_earnings', 180);
2576
2577 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2578 p_asg_id => p_asst_id,
2579 p_eff_date => p_period_end,
2580 p_curr_rate => p_ass_hrly_figure);
2581 hr_utility.set_location('calculate_period_earnings', 185);
2582
2583 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2584 RETURN v_prorated_earnings;
2585
2586 EXCEPTION WHEN NO_DATA_FOUND THEN
2587
2588 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2589 p_asg_id => p_asst_id,
2590 p_eff_date => p_period_end,
2591 p_curr_rate => p_ass_hrly_figure);
2592
2593 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2594 p_asg_id => p_asst_id,
2595 p_eff_date => p_period_end,
2596 p_curr_rate => p_ass_hrly_figure);
2597
2598 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2599 RETURN v_prorated_earnings;
2600
2601 END;
2602
2603
2604 /* ******************* BOTH ASG AND EEV CHANGES ENDS ************ */
2605
2606 END IF; /*END IF OF BOTH ASG AND EEV CHANGES */
2607
2608 EXCEPTION
2609 WHEN NO_DATA_FOUND THEN
2610
2611 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2612 p_asg_id => p_asst_id,
2613 p_eff_date => p_period_end,
2614 p_curr_rate => p_ass_hrly_figure);
2615
2619 p_curr_rate => p_ass_hrly_figure);
2616 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2617 p_asg_id => p_asst_id,
2618 p_eff_date => p_period_end,
2620
2621
2622 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2623
2624 RETURN v_prorated_earnings;
2625
2626 END Calculate_Period_Earnings;
2627
2628 -- **********************************************************************
2629
2630 -- **********************************************************************
2631 -- converts the amount from one salary basis to another e.g. montly to hourly
2632
2633 -- Calculates hourly rate
2634 FUNCTION get_hourly_rate(
2635 p_bg IN NUMBER -- context
2636 ,p_assignment_id IN NUMBER -- context
2637 ,p_payroll_id IN NUMBER -- context
2638 ,p_element_entry_id IN NUMBER -- context
2639 ,p_date_earned IN DATE -- context
2640 ,p_assignment_action_id IN NUMBER )-- context
2641 RETURN NUMBER IS
2642
2643 CURSOR get_period_dates (l_date_earned date,
2644 l_payroll_id number) IS
2645 select start_date, end_date
2646 from per_time_periods pt
2647 where payroll_id = l_payroll_id
2648 and l_date_earned between start_date and end_date;
2649
2650 CURSOR get_salary_basis(l_date_earned date,
2651 l_assignment_id number) IS
2652 /* using lookup_code to avoid the translation issue*/
2653
2654 select /*hr_general.decode_lookup('PAY_BASIS',BASES.pay_basis)*/
2655 BASES.pay_basis
2656 , INPUTV.input_value_id
2657 from
2658 per_all_assignments_f ASSIGN
2659 , per_pay_bases BASES
2660 , pay_input_values_f INPUTV
2661 , pay_element_types_f ETYPE
2662 , pay_rates RATE
2663 where l_date_earned BETWEEN ASSIGN.effective_start_date
2664 AND ASSIGN.effective_end_date
2665 and ASSIGN.assignment_id = l_assignment_id
2666 and BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
2667 and INPUTV.input_value_id (+)= BASES.input_value_id
2668 and l_date_earned between nvl (INPUTV.effective_start_date, l_date_earned)
2669 and nvl (INPUTV.effective_end_date, l_date_earned)
2670 and ETYPE.element_type_id (+)= INPUTV.element_type_id
2671 and RATE.rate_id (+)= BASES.rate_id
2672 and l_date_earned between nvl (ETYPE.effective_start_date, l_date_earned)
2673 and nvl (ETYPE.effective_end_date, l_date_earned) ;
2674
2675 CURSOR get_salary (l_date_earned date,
2676 l_assignment_id number,
2677 l_input_value_id number) IS
2678 select fnd_number.canonical_to_number (EEV.screen_entry_value)
2679 from pay_element_entries_f EE
2680 , pay_element_entry_values_f EEV
2681 where EEV.input_value_id = l_input_value_id
2682 and l_date_earned BETWEEN EEV.effective_start_date
2683 AND EEV.effective_end_date
2684 and EE.assignment_id = l_assignment_id
2685 and EE.entry_type = 'E'
2686 and l_date_earned BETWEEN EE.effective_start_date
2687 AND EE.effective_end_date
2688 and EEV.element_entry_id = EE.element_entry_id;
2689
2690
2691 CURSOR get_termination_date(l_date_earned date,
2692 l_assignment_id number) IS
2693 select actual_termination_date
2694 from per_assignments_f paf,
2695 per_periods_of_service pps
2696 where paf.assignment_id = l_assignment_id
2697 and l_date_earned between paf.effective_start_date and
2698 paf.effective_end_date
2699 and paf.PERIOD_OF_SERVICE_ID = pps.period_of_service_id;
2700
2701 l_period_start_date date;
2702 l_period_end_date date;
2703 l_salary_basis VARCHAR2(200);
2704 l_input_value_id NUMBER;
2705 l_asg_salary NUMBER;
2706 l_hourly_rate NUMBER;
2707 l_date_used date;
2708 l_termination_date date;
2709
2710
2711 BEGIN
2712
2713 hr_utility.trace(' Entered get_hourly_rate ');
2714 --hr_utility.trace_on(null,'wrkschd');
2715
2716
2717 hr_utility.trace('assignment_action_id=' || p_assignment_action_id);
2718 hr_utility.trace('assignment_id=' || p_assignment_id);
2719 hr_utility.trace('business_group_id=' || p_bg);
2720 hr_utility.trace('element_entry_id=' || p_element_entry_id);
2721 hr_utility.trace('p_date_earned '||p_date_earned);
2722 hr_utility.trace('p_payroll_id: '||p_payroll_id);
2723
2724 l_hourly_rate := 0;
2725
2726 OPEN get_period_dates(p_date_earned,p_payroll_id);
2727 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
2728 CLOSE get_period_dates;
2729
2730
2731 hr_utility.trace('l_period_start_date =' || l_period_start_date);
2732 hr_utility.trace('l_period_end_date =' || l_period_end_date);
2733
2734 OPEN get_salary_basis(p_date_earned, p_assignment_id);
2735 FETCH get_salary_basis INTO l_salary_basis, l_input_value_id;
2736 CLOSE get_salary_basis;
2737
2741 OPEN get_salary(p_date_earned, p_assignment_id,l_input_value_id);
2738 hr_utility.trace('l_salary_basis =' || l_salary_basis);
2739 hr_utility.trace('l_input_value_id =' || l_input_value_id);
2740
2742 FETCH get_salary INTO l_asg_salary;
2743 CLOSE get_salary;
2744
2745 IF l_asg_salary IS NULL THEN
2746
2747 OPEN get_termination_date(p_date_earned, p_assignment_id);
2748 FETCH get_termination_date INTO l_termination_date;
2749 CLOSE get_termination_date;
2750
2751 hr_utility.trace('l_termination_date =' || l_termination_date);
2752
2753 OPEN get_salary(l_termination_date,
2754 p_assignment_id,l_input_value_id);
2755 FETCH get_salary INTO l_asg_salary;
2756 CLOSE get_salary;
2757 l_date_used := nvl(l_termination_date,p_date_earned);
2758 END IF;
2759
2760 hr_utility.trace('l_asg_salary =' || l_asg_salary);
2761
2762 l_hourly_rate := Convert_Period_Type(p_bg
2763 ,p_assignment_id
2764 ,p_payroll_id
2765 ,p_element_entry_id
2766 ,p_date_earned
2767 ,p_assignment_action_id
2768 ,l_period_start_date -- period start date
2769 ,l_period_end_date -- period end date
2770 ,l_asg_salary -- p_figure, salary amount
2771 ,l_salary_basis -- p_from freq, salary basis
2772 ,'HOURLY'); -- p_to_freq
2773
2774 return l_hourly_rate;
2775
2776 END get_hourly_rate;
2777
2778
2779 FUNCTION standard_hours_worked(
2780 p_std_hrs in NUMBER,
2781 p_range_start in DATE,
2782 p_range_end in DATE,
2783 p_std_freq in VARCHAR2) RETURN NUMBER IS
2784
2785 c_wkdays_per_week NUMBER(5,2) ;
2786 c_wkdays_per_month NUMBER(5,2) ;
2787 c_wkdays_per_year NUMBER(5,2) ;
2788
2789 /* 353434, 368242 : Fixed number width for total hours */
2790 v_total_hours NUMBER(15,7) ;
2791 v_wrkday_hours NUMBER(15,7) ; -- std hrs/wk divided by 5 workdays/wk
2792 v_curr_date DATE;
2793 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
2794 v_day_no NUMBER;
2795
2796 BEGIN -- standard_hours_worked
2797
2798 /* Init */
2799 c_wkdays_per_week := 5;
2800 c_wkdays_per_month := 20;
2801 c_wkdays_per_year := 250;
2802 v_total_hours := 0;
2803 v_wrkday_hours :=0;
2804 v_curr_date := NULL;
2805 v_curr_day :=NULL;
2806
2807 -- Check for valid range
2808 hr_utility.trace('Entered standard_hours_worked');
2809
2810 IF p_range_start > p_range_end THEN
2811 hr_utility.trace('p_range_start greater than p_range_end');
2812 RETURN v_total_hours;
2813 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2814 -- hr_utility.raise_error;
2815 END IF;
2816 --
2817
2818 IF UPPER(p_std_freq) = 'WEEK' THEN
2819 hr_utility.trace('p_std_freq = WEEK ');
2820
2821 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2822
2823 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2824 hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
2825 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2826
2827 ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2828
2829 hr_utility.trace('p_std_freq = MONTH ');
2830
2831 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
2832
2833
2834 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2835 hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
2836 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2837
2838 ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2839
2840 hr_utility.trace('p_std_freq = YEAR ');
2841 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2842
2843 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2844 hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2845 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2846
2847 ELSE
2848 hr_utility.trace('p_std_freq in ELSE ');
2849 v_wrkday_hours := p_std_hrs;
2850 END IF;
2851
2852 v_curr_date := p_range_start;
2853
2854 hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
2855
2856
2857 LOOP
2858
2859 v_day_no := TO_CHAR(v_curr_date, 'D');
2860
2861
2862 IF v_day_no > 1 and v_day_no < 7 then
2863
2864
2865 v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
2866
2867 hr_utility.trace(' v_day_no = '||to_char(v_day_no));
2868 hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
2869 END IF;
2870
2871 v_curr_date := v_curr_date + 1;
2872 EXIT WHEN v_curr_date > p_range_end;
2873 END LOOP;
2874 hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
2875 hr_utility.trace(' Leaving standard_hours_worked' );
2876 --
2877 RETURN v_total_hours;
2878 --
2879 END standard_hours_worked;
2880
2881 -- +-------------------------------------------------------------------------+
2882 -- |-----------------< good_time_format >-------------------------|
2883 -- +-------------------------------------------------------------------------+
2884 -- Description:
2885 -- Tests CHAR values for valid time.
2886 --
2887 -- Pre-conditions:
2888 -- None.
2889 --
2890 -- In Arguments:
2891 -- p_time VARCHAR2
2895 --
2892 --
2893 -- Out Arguments:
2894 -- BOOLEAN
2896 -- Post Success:
2897 -- Returns TRUE or FALSE depending on valid time or not.
2898 --
2899 -- Post Failure:
2900 -- Returns FALSE for invalid time.
2901 --
2902 -- Access Status:
2903 -- Internal Development Use Only.
2904 --
2905 -- {End Of Comments}
2906 -- ----------------------------------------------------------------------------
2907 --
2908 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
2909 --
2910 BEGIN
2911 --
2912 IF p_time IS NOT NULL THEN
2913 --
2914 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
2915 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
2916 SUBSTR(p_time,3,1) = ':' AND
2917 LENGTH(p_time) = 5) THEN
2918 RETURN FALSE;
2919 ELSE
2920 RETURN TRUE;
2921 END IF;
2922 --
2923 ELSE
2924 RETURN FALSE;
2925 END IF;
2926 --
2927 EXCEPTION
2928 --
2929 WHEN OTHERS THEN
2930 RETURN FALSE;
2931 --
2932 END good_time_format;
2933 --
2934
2935 --
2936 -- +-------------------------------------------------------------------------+
2937 -- |-----------------< calc_sch_based_dur >-------------------------|
2938 -- +-------------------------------------------------------------------------+
2939 -- Description:
2940 -- Calculate the duration in hours/days based on the work schedule.
2941 --
2942 -- Pre-conditions:
2943 -- None.
2944 --
2945 -- In Arguments:
2946 -- p_days_or_hours VARCHAR2
2947 -- p_date_start DATE
2948 -- p_date_end DATE
2949 -- p_time_start VARCHAR2
2950 -- p_time_end VARCHAR2
2951 -- p_assignment_id NUMBER
2952 --
2953 -- Out Arguments:
2954 -- p_duration NUMBER
2955 --
2956 -- Post Success:
2957 -- Value returned for duration.
2958 --
2959 -- Post Failure:
2960 -- If a failure occurs, an application error is raised and
2961 -- processing terminates.
2962 --
2963 -- Access Status:
2964 -- Internal Development Use Only.
2965 --
2966 -- {End Of Comments}
2967 -- ----------------------------------------------------------------------------
2968 --
2969 PROCEDURE calc_sch_based_dur ( p_days_or_hours IN VARCHAR2,
2970 p_date_start IN DATE,
2971 p_date_end IN DATE,
2972 p_time_start IN VARCHAR2,
2973 p_time_end IN VARCHAR2,
2974 p_assignment_id IN NUMBER,
2975 p_duration IN OUT NOCOPY NUMBER
2976 ) IS
2977 --
2978 p_start_duration NUMBER;
2979 p_end_duration NUMBER;
2980 l_idx NUMBER;
2981 l_ref_date DATE;
2982 l_first_band BOOLEAN;
2983 l_day_start_time VARCHAR2(5);
2984 l_day_end_time VARCHAR2(5);
2985 l_start_time VARCHAR2(5);
2986 l_end_time VARCHAR2(5);
2987 --
2988 l_start_date DATE;
2989 l_end_date DATE;
2990 l_schedule cac_avlblty_time_varray;
2991 l_schedule_source VARCHAR2(10);
2992 l_return_status VARCHAR2(1);
2993 l_return_message VARCHAR2(2000);
2994 --
2995 l_time_start VARCHAR2(5);
2996 l_time_end VARCHAR2(5);
2997 --
2998 e_bad_time_format EXCEPTION;
2999 --
3000 BEGIN
3001 hr_utility.set_location('Entering '||g_package||'.calc_sch_based_dur',10);
3002 p_duration := 0;
3003 l_time_start := p_time_start;
3004 l_time_end := p_time_end;
3005 --
3006 IF l_time_start IS NULL THEN
3007 l_time_start := '00:00';
3008 ELSE
3009 IF NOT good_time_format(l_time_start) THEN
3010 RAISE e_bad_time_format;
3011 END IF;
3012 END IF;
3013 IF l_time_end IS NULL THEN
3014 l_time_end := '00:00';
3015 ELSE
3016 IF NOT good_time_format(l_time_end) THEN
3017 RAISE e_bad_time_format;
3018 END IF;
3019 END IF;
3020 IF p_days_or_hours = 'D' THEN
3021 l_time_end := '23:59';
3022 END IF;
3023 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
3024 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
3025
3026 hr_utility.trace('p_assignment_id ' ||p_assignment_id);
3027 hr_utility.trace('l_start_date ' ||l_start_date);
3028 hr_utility.trace('l_end_date ' ||l_end_date);
3029 hr_utility.trace('p_time_start ' ||p_time_start);
3030 hr_utility.trace('p_time_end ' ||p_time_end);
3031 hr_utility.trace('p_days_or_hours ' ||p_days_or_hours);
3032
3033 --
3034 -- Fetch the work schedule
3035 --
3036 hr_wrk_sch_pkg.get_per_asg_schedule
3037 ( p_person_assignment_id => p_assignment_id
3038 , p_period_start_date => l_start_date
3039 , p_period_end_date => l_end_date
3040 , p_schedule_category => NULL
3041 , p_include_exceptions => 'N'-- for bug 5102813 'Y'
3042 , p_busy_tentative_as => 'FREE'
3043 , x_schedule_source => l_schedule_source
3044 , x_schedule => l_schedule
3045 , x_return_status => l_return_status
3046 , x_return_message => l_return_message
3050 hr_utility.trace('l_return_status ' ||l_return_status);
3047 );
3048 --
3049
3051 IF l_return_status = '0' THEN
3052 --
3053 -- Calculate duration
3054 --
3055 l_idx := l_schedule.first;
3056 hr_utility.trace('l_idx ' || l_idx);
3057 hr_utility.trace('Schedule Counts ' ||l_schedule.count);
3058 --
3059 IF p_days_or_hours = 'D' THEN
3060 --
3061 l_first_band := TRUE;
3062 l_ref_date := NULL;
3063 WHILE l_idx IS NOT NULL
3064 LOOP
3065 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3066 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3067 IF l_first_band THEN
3068 l_first_band := FALSE;
3069 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
3070 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
3071 ELSE -- not first time
3072 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
3073 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
3074 ELSE
3075 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
3076 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
3077 END IF;
3078 END IF;
3079 END IF;
3080 END IF;
3081 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3082 END LOOP;
3083 --
3084 ELSE -- p_days_or_hours is 'H'
3085 --
3086 l_day_start_time := '00:00';
3087 l_day_end_time := '23:59';
3088 WHILE l_idx IS NOT NULL
3089 LOOP
3090 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3091 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
3092 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
3093
3094 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3095 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is not null ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3096 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3097 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is FREE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3098 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
3099 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
3100 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
3101 -- Skip this invalid slot which ends before it starts
3102 NULL;
3103 ELSE
3104 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
3105 -- Start and End on different days
3106 --
3107 -- Get first day hours
3108 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
3109 hr_utility.trace('l_start_time ' || l_start_time);
3110
3111 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
3112 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
3113 INTO p_duration
3114 FROM DUAL;
3115 -- hr_utility.trace('p_start_duration ' || p_start_duration);
3116 hr_utility.trace('Start p_duration ' || p_duration);
3117
3118 --
3119 -- Get last day hours
3120 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
3121 hr_utility.trace('l_end_time ' || l_end_time);
3122 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
3123 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
3124 INTO p_duration
3125 FROM DUAL;
3126 --hr_utility.trace('p_end_duration ' || p_end_duration);
3127 hr_utility.trace('End p_duration ' || p_duration);
3128 --
3129 -- Get between full day hours
3130 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
3131 INTO p_duration
3132 FROM DUAL;
3133 ELSE
3134 -- Start and End on same day
3135 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
3136 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
3137
3138 hr_utility.trace('l_start_time ' || l_start_time);
3139 hr_utility.trace('l_end_time ' || l_end_time);
3140
3141 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
3142 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
3143 INTO p_duration
3144 FROM DUAL;
3145 hr_utility.trace('duration l_idx '||l_idx||' ' ||p_duration);
3146
3147 END IF;
3148 END IF;
3149 END IF;
3150 END IF;
3151 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3155 p_duration := ROUND(p_duration,2);
3152 END LOOP;
3153 hr_utility.trace('duration ' ||p_duration);
3154
3156 --
3157 END IF;
3158 END IF;
3159 --
3160 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
3161 EXCEPTION
3162 --
3163 WHEN e_bad_time_format THEN
3164 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
3165 hr_utility.set_location(SQLERRM,35);
3166 RAISE;
3167 --
3168 WHEN OTHERS THEN
3169 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
3170 hr_utility.set_location(SQLERRM,45);
3171 RAISE;
3172 --
3173 END calc_sch_based_dur;
3174
3175
3176 FUNCTION calculate_actual_hours_worked
3177 (assignment_action_id IN number --Context
3178 ,assignment_id IN number --Context
3179 ,business_group_id IN number --Context
3180 ,element_entry_id IN number --Context
3181 ,date_earned IN date --Context
3182 ,p_period_start_date IN date
3183 ,p_period_end_date IN date
3184 ,p_schedule_category IN varchar2 --Optional
3185 ,p_include_exceptions IN varchar2 --Optional
3186 ,p_busy_tentative_as IN varchar2 --Optional
3187 ,p_legislation_code IN varchar2 -- Optional
3188 ,p_schedule_source IN OUT nocopy varchar2 --OPtional
3189 ,p_schedule IN OUT nocopy varchar2-- Optional
3190 ,p_return_status OUT nocopy number -- Optional
3191 ,p_return_message OUT nocopy varchar2 -- Optional
3192 ,p_days_or_hours IN VARCHAR2 default 'H')
3193 RETURN NUMBER IS
3194 l_work_schedule_found BOOLEAN;
3195 l_total_hours NUMBER;
3196 l_asg_frequency VARCHAR2(20);
3197 l_duration NUMBER;
3198 -- l_legislation_code VARCHAR2(10);
3199
3200 CURSOR get_asg_hours_freq(p_date_earned date,
3201 p_assignment_id number)IS
3202 SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
3203 ,ASSIGN.normal_hours
3204 FROM per_all_assignments_f ASSIGN
3205 where date_earned
3206 BETWEEN ASSIGN.effective_start_date
3207 AND ASSIGN.effective_end_date
3208 and ASSIGN.assignment_id = p_assignment_id;
3209
3210
3211 BEGIN
3212 l_work_schedule_found := FALSE;
3213 l_total_hours := 0;
3214 -- hr_utility.trace_on(NULL, 'PAY_CALC_HOURS_WORKED');
3215 hr_utility.trace( 'Assignment Id '||assignment_id);
3216 hr_utility.trace( 'date_earned '||date_earned);
3217 hr_utility.trace( 'p_days_or_hours '||p_days_or_hours);
3218 hr_utility.trace( 'p_period_start_date '||p_period_start_date);
3219 hr_utility.trace( 'p_period_end_date '||p_period_end_date);
3220 hr_utility.trace( 'p_legislation_code '||p_legislation_code);
3221
3222 IF (p_legislation_code) IS NULL or (p_legislation_code ='') or
3223 (g_legislation_code IS NULL) THEN
3224 g_legislation_code := get_legislation_code(business_group_id);
3225 ELSE
3226 g_legislation_code := nvl(g_legislation_code,p_legislation_code);
3227 END IF;
3228
3229 hr_utility.trace( 'Legislation code : g_legislation_code '||g_legislation_code);
3230
3231 /* Calculate hours worked based on ATG work schedule information using
3232 API : HR_WRK_SCH_PKG.GET_PER_ASG_SCHEDULE ()
3233 This part will be coded later once this API is available from HR
3234 IF p_include_exceptions IS NULL THEN
3235 use p_include_exceptions = 'Y';
3236
3237 */
3238 hr_utility.trace( 'getting work schedule from ATG ');
3239
3240 calc_sch_based_dur ( p_days_or_hours,
3241 p_period_start_date,
3242 p_period_end_date+1,
3243 null,
3244 null,
3245 assignment_id,
3246 l_duration
3247 );
3248
3249
3250 IF (l_duration > 0) THEN
3251 l_work_schedule_found := true;
3252 hr_utility.trace( 'Got work schedule from ATG,duration : '||l_duration);
3253
3254 return l_duration;
3255 END IF;
3256
3257 IF NOT l_work_schedule_found THEN
3258 BEGIN
3259 hr_utility.trace( 'getting work schedule from SCL ');
3260 EXECUTE IMMEDIATE 'BEGIN :1 := PAY_'||g_legislation_code||
3261 '_RULES.Work_Schedule_Total_Hours(:2,:3,:4,:5,:6,:7,:8); END;'
3262 USING OUT l_total_hours,
3263 IN assignment_action_id,assignment_id,business_group_id,element_entry_id
3264 ,date_earned,p_period_start_date,p_period_end_date;
3265
3266 IF l_total_hours > 0 THEN
3267 hr_utility.trace( 'work schedule found from SCL ');
3268 l_work_schedule_found := TRUE;
3269 return l_total_hours;
3270 END IF;
3271 -- hr_utility.trace_off;
3272 EXCEPTION
3273 WHEN OTHERS THEN
3274 -- hr_utility.trace_off;
3275
3276 --Raise;
3277 null;
3278 END;
3279 END IF;
3280
3281 -- hr_utility.trace_off;
3282
3283
3284 /* Calculate hours worked based on standard conditions if the actual hours
3285 worked are not available from either ATG work schedule or work schedule
3286 at assignment/org level */
3287
3288 IF NOT l_work_schedule_found THEN
3289 hr_utility.trace('Calculating hours based on Standard conditions ');
3290 hr_utility.trace( 'Assignment Id '||assignment_id);
3291 hr_utility.trace( 'date_earned '||date_earned);
3292 OPEN get_asg_hours_freq(date_earned,assignment_id);
3293 FETCH get_asg_hours_freq
3294 INTO l_asg_frequency, l_normal_hours;
3295 CLOSE get_asg_hours_freq;
3296
3297 hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
3298 hr_utility.trace( 'l_normal_hours '||l_normal_hours);
3299
3300 IF l_asg_frequency IS NOT NULL and l_normal_hours IS NOT NULL THEN
3301 l_total_hours := standard_hours_worked(l_normal_hours
3302 ,p_period_start_date
3303 ,p_period_end_date
3304 ,l_asg_frequency);
3305 return l_total_hours;
3306 END IF;
3307
3308 END IF;
3309 return 0;
3310 -- hr_utility.trace_off;
3311 END calculate_actual_hours_worked;
3312
3313 -- Added For Skip Rule for "Regular Wages" Element, "REGULAR_PAY"
3314
3315 FUNCTION term_skip_rule_rwage(ctx_payroll_id NUMBER
3316 ,ctx_assignment_id NUMBER
3317 ,ctx_date_earned DATE
3318 ,p_user_entered_time VARCHAR2
3319 ,p_final_pay_processed VARCHAR2
3320 ,p_lspd_pay_processed VARCHAR2
3321 ,p_payroll_termination_type VARCHAR2
3322 ,p_bg_termination_type VARCHAR2
3323 ,p_already_processed VARCHAR2)
3324 RETURN VARCHAR2 is
3325
3326 -- Get Current Pay Period Start and End Date
3327
3328 CURSOR csr_pay_period(p_date_earned date
3329 ,p_payroll_id number) is
3330 select ptp.start_date
3331 ,ptp.end_date
3332 from per_time_periods ptp
3333 where ptp.payroll_id = p_payroll_id
3334 and p_date_earned between ptp.start_date and ptp.end_date;
3335
3336 -- Get ATD, LSPD, FPD for the Terminated EE
3337
3338 CURSOR csr_term_dates(p_date_earned date
3339 ,p_assignment_id number) is
3340 select pds.actual_termination_date
3341 ,pds.last_standard_process_date
3342 ,pds.final_process_date
3343 from per_periods_of_service PDS,
3344 per_assignments_f ASS
3345 WHERE PDS.actual_termination_date <= p_date_earned
3346 AND PDS.period_of_service_id = ASS.period_of_service_id
3347 AND p_date_earned BETWEEN ASS.effective_start_date
3351
3348 AND ASS.effective_end_date
3349 AND ASS.primary_flag = 'Y'
3350 AND ASS.assignment_id = p_assignment_id;
3352 -- Get the Min Date Earned after ATD
3353
3354 CURSOR csr_fpprocd_min_dtearned(p_atd DATE
3355 ,p_assignment_id NUMBER) IS
3356 SELECT min(ppa_run.date_earned)
3357 FROM pay_payroll_actions ppa_run,
3358 pay_assignment_actions paa_run
3359 WHERE ppa_run.date_earned >= p_atd
3360 AND ppa_run.action_status = 'C'
3361 AND ppa_run.action_type in ('Q','R','B','I')
3362 AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
3363 paa_run.source_action_id is null) or
3364 (nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
3365 paa_run.source_action_id is not null))
3366 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
3367 AND paa_run.action_status = 'C'
3368 AND paa_run.assignment_id = p_assignment_id
3369 AND NOT EXISTS (
3370 SELECT 1
3371 FROM pay_payroll_actions ppa_rev,
3372 pay_assignment_actions paa_rev,
3373 pay_action_interlocks pai
3374 WHERE pai.locked_Action_id = paa_run.assignment_action_id
3375 AND pai.locking_action_id = paa_rev.assignment_action_id
3376 AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
3377 AND ppa_rev.action_type = 'V');
3378
3379 -- Get the Min Date Earned after LSPD
3380
3381 CURSOR csr_lspprocd_min_dtearned(p_lspd DATE
3382 ,p_assignment_id NUMBER) IS
3383 SELECT min(ppa_run.date_earned)
3384 FROM pay_payroll_actions ppa_run,
3385 pay_assignment_actions paa_run
3386 WHERE ppa_run.date_earned >= p_lspd
3387 AND ppa_run.action_status = 'C'
3388 AND ppa_run.action_type in ('Q','R','B','I')
3389 AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
3390 paa_run.source_action_id is null) or
3391 (nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
3392 paa_run.source_action_id is not null))
3393 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
3394 AND paa_run.action_status = 'C'
3395 AND paa_run.assignment_id = p_assignment_id
3396 AND NOT EXISTS (
3397 SELECT 1
3398 FROM pay_payroll_actions ppa_rev,
3399 pay_assignment_actions paa_rev,
3400 pay_action_interlocks pai
3401 WHERE pai.locked_Action_id = paa_run.assignment_action_id
3402 AND pai.locking_action_id = paa_rev.assignment_action_id
3403 AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
3404 AND ppa_rev.action_type = 'V');
3405
3406 lv_term_typ varchar2(1);
3407 ld_pay_start_date date;
3408 ld_pay_end_date date;
3409 ld_atd date;
3410 ld_lspd date;
3411 ld_fpd date;
3412 ld_fp_dt_earned date;
3413 ld_lsp_dt_earned date;
3414
3415 begin
3416
3417 hr_utility.trace('ctx_date_earned := '|| to_char(ctx_date_earned));
3418 hr_utility.trace('ctx_payroll_id := '|| ctx_payroll_id);
3419 hr_utility.trace('ctx_assignment_id := '|| ctx_assignment_id);
3420 hr_utility.trace('p_user_entered_time := '|| p_user_entered_time);
3421 hr_utility.trace('p_final_pay_processed := '|| p_final_pay_processed);
3422 hr_utility.trace('p_lspd_pay_processed := '|| p_lspd_pay_processed);
3423 hr_utility.trace('p_payroll_termination_type := '|| p_payroll_termination_type);
3424 hr_utility.trace('p_bg_termination_type := '|| p_bg_termination_type);
3425 hr_utility.trace('p_already_processed := '|| p_already_processed);
3426
3427 OPEN csr_pay_period(ctx_date_earned
3428 ,ctx_payroll_id) ;
3429 FETCH csr_pay_period INTO ld_pay_start_date
3430 ,ld_pay_end_date;
3431 CLOSE csr_pay_period;
3432
3433 OPEN csr_term_dates(ctx_date_earned
3434 ,ctx_assignment_id) ;
3435 FETCH csr_term_dates INTO ld_atd
3436 ,ld_lspd
3437 ,ld_fpd;
3438 CLOSE csr_term_dates;
3439
3440 IF p_payroll_termination_type = 'A' THEN
3441 lv_term_typ := 'A';
3442 ELSIF p_payroll_termination_type = 'L' THEN
3443 lv_term_typ := 'L';
3444 ELSE
3445 IF p_bg_termination_type = 'A' THEN
3446 lv_term_typ := 'A';
3447 ELSIF p_bg_termination_type = 'L' THEN
3448 lv_term_typ := 'L';
3449 ELSE
3450 lv_term_typ := 'L';
3451 END IF;
3452 END IF;
3453
3454 hr_utility.trace('ld_pay_start_date := '|| to_char(ld_pay_start_date));
3455 hr_utility.trace('ld_pay_end_date := '|| to_char(ld_pay_end_date));
3456 hr_utility.trace('ld_atd := '|| to_char(ld_atd));
3457 hr_utility.trace('ld_lspd := '|| to_char(ld_lspd));
3458 hr_utility.trace('ld_fpd := '|| to_char(ld_fpd));
3459 hr_utility.trace('lv_term_typ := '|| lv_term_typ);
3460
3461 IF lv_term_typ = 'A' THEN -- Termination Rule 'First Pay After Term Date'
3462 IF ld_atd <= ctx_date_earned THEN
3463 IF p_final_pay_processed = 'Y' THEN
3464 OPEN csr_fpprocd_min_dtearned(ld_atd
3465 ,ctx_assignment_id);
3466 FETCH csr_fpprocd_min_dtearned INTO ld_fp_dt_earned;
3467 CLOSE csr_fpprocd_min_dtearned;
3468
3469 hr_utility.trace('ld_fp_dt_earned := '|| TO_CHAR(ld_fp_dt_earned));
3470
3471 IF ctx_date_earned > ld_fp_dt_earned THEN
3472 return 'Y';
3473 ELSE
3474 IF p_already_processed <> 'Y' THEN
3475 IF p_user_entered_time = 'Y' THEN
3476 return 'Y';
3477 ELSE
3478 return 'N';
3479 END IF;
3480 ELSIF p_already_processed = 'Y' THEN
3481 return 'Y';
3482 END IF;
3483 END IF; -- Current PayPeriod Date Earned > Date Earned of Final Pay Processed
3484 ELSE
3485 IF p_already_processed <> 'Y' THEN
3486 IF p_user_entered_time = 'Y' THEN
3487 return 'Y';
3488 ELSE
3489 return 'N';
3490 END IF;
3491 ELSIF p_already_processed = 'Y' THEN
3492 return 'Y';
3493 END IF;
3494 END IF; -- Final Pay Processed = 'Y'
3495 ELSE
3496 IF p_already_processed <> 'Y' THEN
3497 IF p_user_entered_time = 'Y' THEN
3498 return 'Y';
3499 ELSE
3500 return 'N';
3501 END IF;
3502 ELSIF p_already_processed = 'Y' THEN
3503 return 'Y';
3504 END IF;
3505 END IF; -- ATD <= Current Pay Period Date Earned
3506
3507 ELSIF lv_term_typ = 'L' THEN -- Term Rule 'Last Standard Process Date'
3508 IF ((ld_atd <= ctx_date_earned
3509 AND ld_lspd <= ctx_date_earned) OR
3510 (ld_atd <= ctx_date_earned
3511 AND ld_lspd > ctx_date_earned)) THEN
3512
3513 IF p_lspd_pay_processed = 'Y' THEN
3514
3515 OPEN csr_lspprocd_min_dtearned(ld_lspd
3516 ,ctx_assignment_id);
3517 FETCH csr_lspprocd_min_dtearned INTO ld_lsp_dt_earned;
3518 CLOSE csr_lspprocd_min_dtearned;
3519
3520 hr_utility.trace('ld_fp_dt_earned := '|| TO_CHAR(ld_lsp_dt_earned));
3521
3522 IF ctx_date_earned > ld_lsp_dt_earned THEN
3523 return 'Y';
3524 ELSE
3525 IF p_already_processed <> 'Y' THEN
3526 IF p_user_entered_time = 'Y' THEN
3527 return 'Y';
3528 ELSE
3529 return 'N';
3530 END IF;
3531 ELSIF p_already_processed = 'Y' THEN
3532 return 'Y';
3533 END IF;
3534 END IF; -- -- Current PayPeriod Date Earned > Date Earned of LSPD Pay Processed
3535 ELSE
3536 IF p_already_processed <> 'Y' THEN
3537 IF p_user_entered_time = 'Y' THEN
3538 return 'Y';
3539 ELSE
3540 return 'N';
3541 END IF;
3542 ELSIF p_already_processed = 'Y' THEN
3543 return 'Y';
3544 END IF;
3545 END IF; -- LSPD Pay Processed = 'Y'
3546 ELSE
3547 IF p_already_processed <> 'Y' THEN
3548 IF p_user_entered_time = 'Y' THEN
3549 return 'Y';
3550 ELSE
3551 return 'N';
3552 END IF;
3553 ELSIF p_already_processed = 'Y' THEN
3554 return 'Y';
3555 END IF;
3556 END IF; -- ATD <= Current Pay Period Date Earned AND LSPD <= OR > Current Pay Period Date Earned
3557 ELSE
3558 return 'N';
3559 END IF; -- Term Rule Neither 'A' nor 'L'
3560
3561 END term_skip_rule_rwage;
3562
3563 END pay_core_ff_udfs ;