1 PACKAGE BODY pqp_schedule_calculation_pkg AS
2 /* $Header: pqschcal.pkb 120.2.12000000.1 2007/01/16 04:32:45 appldev noship $ */
3
4 -- IMPORTANT : Declarations global within package body
5
6 --TYPE t_wp_days_type IS TABLE OF NUMBER
7 --INDEX BY BINARY_INTEGER;
8
9
10
11 --User Defined Table Name, to be treated as a constant unless table name
12 --changes
13 -- g_udt_name VARCHAR2(50) := 'PQP_COMPANY_WORK_PATTERNS';
14 -- g_default_start_day VARCHAR2(10) := 'sunday';
15 g_days_worked NUMBER;
16
17 g_legislation_code pay_user_tables.legislation_code%TYPE := NULL;
18
19 --g_wp_days t_wp_days_type;
20
21 g_package_name VARCHAR2(31) := 'pqp_schedule_calculation_pkg.' ;
22 g_debug BOOLEAN := hr_utility.debug_enabled ;
23
24
25 -- cache for load_work_pattern_into_cache
26 g_last_business_group_id pay_user_tables.business_group_id%TYPE;
27 g_last_max_effective_start_dt DATE;
28 g_last_min_effective_end_dt DATE;
29 g_last_used_work_pattern pay_user_columns.user_column_name%TYPE;
30 g_asg_work_pattern_start_day_n BINARY_INTEGER;
31 g_asg_work_pattern_start_date DATE;
32 g_work_pattern_cache t_work_pattern_cache_type;
33
34
35 -- cache for get_legislation_code
36 g_business_group_id pay_user_rows_f.business_group_id%TYPE;
37 --
38 --
39 --
40 PROCEDURE debug(
41 p_trace_message IN VARCHAR2
42 ,p_trace_location IN NUMBER DEFAULT NULL
43 )
44 IS
45 BEGIN
46 pqp_utilities.debug(p_trace_message, p_trace_location);
47 END debug;
48
49 --
50 --
51 --
52 PROCEDURE debug(p_trace_number IN NUMBER)
53 IS
54 BEGIN
55 pqp_utilities.debug(p_trace_number);
56 END debug;
57
58 --
59 --
60 --
61 PROCEDURE debug(p_trace_date IN DATE)
62 IS
63 BEGIN
64 pqp_utilities.debug(p_trace_date);
65 END debug;
66
67 --
68 --
69 --
70 PROCEDURE debug_enter(
71 p_proc_name IN VARCHAR2
72 ,p_trace_on IN VARCHAR2 DEFAULT NULL
73 )
74 IS
75 BEGIN
76 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
77 END debug_enter;
78
79 --
80 --
81 --
82 PROCEDURE debug_exit(
83 p_proc_name IN VARCHAR2
84 ,p_trace_off IN VARCHAR2 DEFAULT NULL
85 )
86 IS
87 BEGIN
88 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
89 END debug_exit;
90
91 --
92 --
93 --
94 PROCEDURE debug_others(
95 p_proc_name IN VARCHAR2
96 ,p_proc_step IN NUMBER DEFAULT NULL
97 )
98 IS
99 BEGIN
100 pqp_utilities.debug_others(p_proc_name, p_proc_step);
101 END debug_others;
102 --
103 --
104 --
105 PROCEDURE check_error_code
106 (p_error_code IN NUMBER
107 ,p_error_message IN VARCHAR2
108 )
109 IS
110 BEGIN
111 pqp_utilities.check_error_code(p_error_code, p_error_message);
112 END;
113 --
114 --
115 --
116 PROCEDURE clear_cache
117 IS
118
119 --l_empty_wp_days_type t_wp_days_type;
120 l_empty_work_patterns_cache t_work_pattern_cache_type;
121
122 BEGIN
123
124 g_days_worked := NULL;
125
126 --g_wp_days := l_empty_wp_days_type;
127
128 -- cache for load_work_pattern_into_cache
129 g_last_business_group_id := NULL;
130 g_last_max_effective_start_dt := NULL;
131 g_last_min_effective_end_dt := NULL;
132 g_last_used_work_pattern := NULL;
133 g_asg_work_pattern_start_day_n := NULL;
134 g_asg_work_pattern_start_date := NULL;
135 g_work_pattern_cache := l_empty_work_patterns_cache;
136
137
138 -- cache for get_legislation_code
139 g_business_group_id := NULL;
140 g_legislation_code := NULL;
141
142 END clear_cache;
143
144 FUNCTION get_legislation_code
145 (p_business_group_id IN NUMBER
146 ) RETURN pay_user_rows_f.legislation_code%TYPE
147 IS
148
149 l_legislation_code pay_user_rows_f.legislation_code%TYPE;
150
151 BEGIN
152
153 IF g_legislation_code IS NULL
154 OR
155 g_business_group_id IS NULL
156 OR
157 g_business_group_id <> p_business_group_id
158 THEN
159
160 g_business_group_id := p_business_group_id;
161
162 OPEN c_get_legcode(p_business_group_id);
163 FETCH c_get_legcode INTO l_legislation_code;
164 CLOSE c_get_legcode;
165
166 g_legislation_code := l_legislation_code;
167
168
169 ELSE
170
171 l_legislation_code := g_legislation_code;
172
173 END IF;
174
175 RETURN l_legislation_code;
176
177 END get_legislation_code;
178
179
180
181
182 PROCEDURE get_day_dets(p_wp_dets IN c_wp_dets%ROWTYPE
183 ,p_calc_stdt IN DATE
184 ,p_calc_edt IN DATE
185 ,p_day_no OUT NOCOPY NUMBER
186 ,p_days_in_wp OUT NOCOPY NUMBER
187 ) IS
188
189 -- Local Declarations
190
191 -- Bug : 2732955
192 -- Date : 02/01/2003
193 -- Name : rtahilia
194 -- Desc : Added this cursor to get Leg. code for the BG
195 -- Moved to header -- RRAZDAN
196
197 -- Bug : 2732955
198 -- Date : 02/01/2003
199 -- Name : rtahilia
200 -- Desc : Modified cursor, added legislation code check
201 -- Legislation Heirarchy
202 -- Table of Work Patterns : Legislatively Seeded
203 -- Implies that work patterns in that table can either be
204 -- legislatively seeded or belong to a specific business group
205 -- A work pattern is represented by a user column name
206 -- ie for a given table id (guaranteed leg specific) if a matching user
207 -- column name it could either itself be seeded of specific to a bg
208 -- so check for bg/leg in user columns
209 -- If the column was seeded (seeded work pattern) then it could have
210 -- column instances (day values) which were either also seeded or
211 -- specific to the bg. This last bit implies that a user may have
212 -- extended a seeded work patterns. While evaluating a particular wp
213 -- both the seeded and the values in that row must be evaluated.
214 -- so check for bg/leg in the user column instances also.
215 -- Note the user rows (the actual days themselves) can only be seeded
216 -- the user may extend those but functionally such extensions would have no
217 -- impact.
218 -- PQP_CWP(GB) PQP_CWP (NL) UT
219 -- | |
220 -- |-------|-------| |-------|--------|
221 -- WP1(GB) WP2(bg1) WP3(bg2) WP4(NL) WP5(bg3) WP6(bg4) UC
222 -- | |
223 -- |-----|----| |
224 -- D1-7(GB) D8(bg1) D1-14(bg3) UCI
225 --
226 --
227 -- So when counting distinct user_rows for bg1 (WP1) we should get 8
228 -- = D1-7 (seeded) + 1 (D8 bg1)
229 --
230 -- When counting distinct user_rows for bg2 (WP1) we should get 7
231 -- = D1-7 (seeded)
232 --
233 -- When counting distinct user rows for bg3 (WP5) we should get 14
234 -- = D1-14 , ie for a bg specific work pattern the would only exist in
235 -- the business group itself.
236 --
237 -- NOTE: D1-D28 themselves are seeded repectively in each legislation
238 -- as user rows (UR)
239
240
241 CURSOR c_get_days IS
242 SELECT COUNT(pur.row_low_range_or_name)
243 FROM pay_user_rows_f pur
244 WHERE pur.user_row_id IN
245 (SELECT DISTINCT uci.user_row_id
246 FROM pay_user_tables put,
247 pay_user_columns puc,
248 pay_user_column_instances_f uci
249 WHERE put.user_table_name = g_udt_name
250 AND put.legislation_code = g_legislation_code -- Added on 02/01/2003
251 AND puc.user_table_id = put.user_table_id
252 AND puc.user_column_name = p_wp_dets.work_pattern
253 AND (
254 puc.business_group_id = p_wp_dets.business_group_id
255 OR
256 (puc.business_group_id IS NULL
257 AND puc.legislation_code = g_legislation_code)
258 --OR global
259 -- CANNOT BE as the table itself is legislatively seeded.
260 )
261 AND uci.user_column_id = puc.user_column_id
262 AND (
263 uci.business_group_id = p_wp_dets.business_group_id
264 OR
265 (uci.business_group_id IS NULL
266 AND uci.legislation_code = g_legislation_code)
267 --OR global
268 -- CANNOT BE as the work pattern itself is either
269 -- legislative or business group specific
270 )
271 AND (p_calc_stdt BETWEEN uci.effective_start_date
272 AND uci.effective_end_date
273 OR
274 p_calc_edt BETWEEN uci.effective_start_date
275 AND uci.effective_end_date)
276
277 ) AND pur.row_low_range_or_name like
278 'Day __';
279
280 l_days_in_wp NUMBER;
281 l_day_no NUMBER;
282 l_diff_days NUMBER;
283 l_diff_CalcStDt_DtOnDay1 NUMBER;
284 l_diff_temp NUMBER;
285 l_dt_on_day1 DATE;
286
287 BEGIN /* get_day_dets */
288
289 -- Get the legislation code for this business group,
290 -- if not already populated.
291
292 hr_utility.trace('in get_day_dets:'||p_wp_dets.work_pattern);
293
294 if g_legislation_code is NULL then
295 open c_get_legcode(p_wp_dets.business_group_id);
296 fetch c_get_legcode into g_legislation_code;
297 close c_get_legcode;
298 end if;
299
300 /* Get the number of days in the Work Pattern */
301 open c_get_days;
302 fetch c_get_days into l_days_in_wp;
303 hr_utility.trace('get_days_dets:l_days_in_wp:'||
304 fnd_number.number_to_canonical(l_days_in_wp));
305 close c_get_days;
306
307 /* Find number of days to be added to effective date to get next date on 'Day
308 01' */
309 --hr_utility.trace('get_days_dets:p_wp_dets.start_day:'||
310 --p_wp_dets.start_day);
311
312 l_diff_days := l_days_in_wp - to_number(substr(p_wp_dets.start_day,5,2)) + 1;
313
314 --hr_utility.trace('get_days_dets:l_diff_days:'||
315 --fnd_number.number_to_canonical(l_diff_days));
316
317
318 /* Find the next date that would be 'Day 01' w.r.t. the p_wp_dets record */
319 l_dt_on_day1 := p_wp_dets.effective_start_date + l_diff_days;
320
321 --hr_utility.trace('get_days_dets:l_dt_on_day1:'||
322 --fnd_date.date_to_canonical(l_dt_on_day1));
323
324
325 /* Find difference between calculation start_date and date on 'Day 01' */
326 l_diff_temp := p_calc_stdt - l_dt_on_day1;
327
328 hr_utility.trace('get_days_dets:l_diff_temp:'||
329 fnd_number.number_to_canonical(l_diff_temp));
330
331
332 /* If difference is negative, multiply by -1 to make it positive */
333 l_diff_CalcStDt_DtOnDay1 := l_diff_temp * sign(l_diff_temp);
334
335 --hr_utility.trace('get_days_dets:l_diff_CalcStDt_DtOnDay1:'||
336 --fnd_number.number_to_canonical(l_diff_CalcStDt_DtOnDay1));
337
338
339 /* Calculate Day Number on Calculation Start Date */
340 if l_diff_temp < 0
341 then
342 l_day_no := l_days_in_wp - l_diff_CalcStDt_DtOnDay1 + 1;
343 --hr_utility.trace('get_days_dets:l_day_no1:'||
344 --fnd_number.number_to_canonical(l_day_no));
345
346 else
347 l_day_no := mod(l_diff_CalcStDt_DtOnDay1,l_days_in_wp) + 1;
348 --hr_utility.trace('get_days_dets:l_day_no2:'||
349 --fnd_number.number_to_canonical(l_day_no));
350 end if;
351
352 /* Assign values to be returned */
353
354 hr_utility.trace('get_days_dets:l_day_no:'||
355 fnd_number.number_to_canonical(l_day_no));
356
357 p_day_no := l_day_no;
358 p_days_in_wp := l_days_in_wp;
359
360 -- Added by tmehra for nocopy changes Feb'03
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 hr_utility.trace('in get_day_dets: Exception block');
365 p_day_no := NULL;
366 p_days_in_wp := NULL;
367 raise;
368
369 END get_day_dets;
370
371
372 FUNCTION calculate_time_worked(p_assignment_id IN NUMBER
373 ,p_date_start IN DATE
374 ,p_date_end IN DATE
375 ) RETURN NUMBER IS
376
377
378 /* Local variable declarations */
379 l_calc_stdt DATE;
380 l_calc_endt DATE;
381 l_curr_date DATE;
382
383 l_day_no NUMBER;
384 l_curr_day_no NUMBER;
385 l_days_in_wp NUMBER;
386 l__curr_day_no NUMBER;
387 l_hours NUMBER := 0;
388 l_total_hours NUMBER := 0;
389
390 l_day VARCHAR2(30);
391
392 r_wp_dets c_wp_dets%ROWTYPE;
393
394
395 BEGIN /* calculate_time_worked */
396
397 hr_utility.set_location('Entered calculate_time_worked', 10);
398 hr_utility.set_location('UDT Name :'||g_udt_name, 15);
399
400 /* If start date is greater than end date then return zero hours */
401 if p_date_start > p_date_end
402 then
403 return l_total_hours;
404 end if;
405
406 for r_wp_dets in c_wp_dets(p_assignment_id, p_date_start, p_date_end)
407 loop /* Get Work Pattern Details */
408
409
410 hr_utility.set_location('Inside Loop to get WP detail', 20);
411
412 /* Determine Calculation Start Date for this Work Pattern */
413 if p_date_start > r_wp_dets.effective_start_date
414 then
415 l_calc_stdt := p_date_start;
416 else
417 l_calc_stdt := r_wp_dets.effective_start_date;
418 end if;
419
420 /* Determine Calculation End Date for this Work Pattern */
421 if p_date_end < r_wp_dets.effective_end_date
422 then
423 l_calc_endt := p_date_end;
424 else
425 l_calc_endt := r_wp_dets.effective_end_date;
426 end if;
427
428
429
430 /* Get day number on calculation start date and number of days in Work
431 Pattern */
432 get_day_dets(p_wp_dets => r_wp_dets
433 ,p_calc_stdt => l_calc_stdt
434 ,p_calc_edt => l_calc_endt
435 ,p_day_no => l_day_no /* OUT NOCOPY */
436 ,p_days_in_wp => l_days_in_wp /* OUT NOCOPY */
437 );
438
439
440 l_curr_day_no := l_day_no;
441 l_curr_date := l_calc_stdt;
442
443 hr_utility.set_location('l_curr_day_no :'||l_curr_day_no, 30);
444 hr_utility.set_location('l_curr_date :'||l_curr_date, 35);
445 hr_utility.set_location('Work Pattern :'||r_wp_dets.work_pattern, 40);
446
447 for l_loopindx in 1..(l_calc_endt - l_calc_stdt + 1)
448 loop /* Process dates in range */
449
450 l_day := 'Day '||lpad(l_curr_day_no,2,0);
451
452 begin
453 l_hours := hruserdt.get_table_value
454 (p_bus_group_id => r_wp_dets.business_group_id
455 ,p_table_name => g_udt_name
456 ,p_col_name => r_wp_dets.work_pattern
457 ,p_row_value => l_day
458 ,p_effective_date => l_curr_date
459 );
460
461 exception
462 when no_data_found then
463
464 /*
465 * No data was entered. Do not add to total
466 * or count the day in the loop.
467 */
468 l_hours := 0;
469
470 end;
471
472 hr_utility.set_location('Hours on '||l_day||' = '||l_hours, 50);
473
474 l_total_hours := l_total_hours + l_hours;
475
476 -- add this date to the number of days in the date range.
477 if l_hours > 0 then
478
479 g_days_worked := g_days_worked + 1;
480 hr_utility.set_location('Adding day for date :'||to_char(l_curr_date,
481 'DD/MM/YYYY'), 60);
482
483 end if;
484
485 /* Calculate next day no */
486 if l_curr_day_no = l_days_in_wp then
487 l_curr_day_no := 1;
488 else
489 l_curr_day_no := l_curr_day_no + 1;
490 end if;
491
492 l_curr_date := l_curr_date + 1;
493
494 end loop; /* Process dates in range */
495
496 end loop; /* Get Work Pattern Details */
497
498 return l_total_hours;
499
500 END calculate_time_worked;
501
502
503 -- This procedure calculates and returns the hours and days
504 -- worked given the WP details and the start and end dates
505 PROCEDURE calculate_time_worked_wp
506 (p_date_start IN DATE
507 ,p_date_end IN DATE
508 ,p_wp_dets IN c_wp_dets%ROWTYPE
509 ,p_hours_worked OUT NOCOPY NUMBER
510 ,p_days_worked OUT NOCOPY NUMBER
511 ,p_working_dates IN OUT NOCOPY t_working_dates
512 )
513 IS
514 /* Local variable declarations */
515 l_calc_stdt DATE;
516 l_calc_endt DATE;
517 l_curr_date DATE;
518
519 l_day_no NUMBER;
520 l_curr_day_no NUMBER;
521 l_days_in_wp NUMBER;
522 l_hours NUMBER := 0;
523 l_total_hours NUMBER := 0;
524 l_days_worked NUMBER := 0;
525 l_day VARCHAR2(30);
526
527 r_wp_dets c_wp_dets%ROWTYPE;
528 l_working_dates t_working_dates;
529
530 BEGIN -- calculate_time_worked_wp
531
532 hr_utility.set_location('Entered calculate_time_worked_wp', 10);
533
534 r_wp_dets := p_wp_dets;
535 l_working_dates := p_working_dates;
536
537 /* Determine Calculation Start Date for this Work Pattern */
538 if p_date_start > r_wp_dets.effective_start_date
539 then
540 l_calc_stdt := p_date_start;
541 else
542 l_calc_stdt := r_wp_dets.effective_start_date;
543 end if;
544
545 /* Determine Calculation End Date for this Work Pattern */
546 if p_date_end < r_wp_dets.effective_end_date
547 then
548 l_calc_endt := p_date_end;
549 else
550 l_calc_endt := r_wp_dets.effective_end_date;
551 end if;
552
553 /* Get day number on calculation start date and number of days in Work Pattern
554 */
555 get_day_dets(p_wp_dets => r_wp_dets
556 ,p_calc_stdt => l_calc_stdt
557 ,p_calc_edt => l_calc_endt
558 ,p_day_no => l_day_no /* OUT NOCOPY */
559 ,p_days_in_wp => l_days_in_wp /* OUT NOCOPY */
560 );
561
562
563 l_curr_day_no := l_day_no;
564 l_curr_date := l_calc_stdt;
565
566 hr_utility.set_location('l_curr_day_no :'||l_curr_day_no, 20);
567 hr_utility.set_location('l_curr_date :'||l_curr_date, 30);
568 hr_utility.set_location('Work Pattern :'||r_wp_dets.work_pattern, 40);
569
570 for l_loopindx in 1..(l_calc_endt - l_calc_stdt + 1)
571 loop /* Process dates in range */
572
573 hr_utility.set_location('Processing date :'||to_char(l_curr_date,
574 'DD/MM/YYYY'), 60);
575
576 l_day := 'Day '||lpad(l_curr_day_no,2,0);
577
578 begin
579 l_hours := hruserdt.get_table_value
580 (p_bus_group_id => r_wp_dets.business_group_id
581 ,p_table_name => g_udt_name
582 ,p_col_name => r_wp_dets.work_pattern
583 ,p_row_value => l_day
584 ,p_effective_date => l_curr_date
585 );
586 exception
587 when no_data_found then
588 /*
589 * No data was entered. Do not add to total
590 * or count the day in the loop.
591 */
592 l_hours := 0;
593 end;
594
595 hr_utility.set_location('Hours on '||l_day||' = '||l_hours, 70);
596
597 l_total_hours := l_total_hours + l_hours;
598
599 -- add this date to the number of days in the date range.
600 if l_hours > 0 then
601
602 l_days_worked := l_days_worked + 1;
603 p_working_dates(p_working_dates.COUNT + 1) := l_curr_date;
604
605 end if;
606
607 /* Calculate next day no */
608 if l_curr_day_no = l_days_in_wp then
609 l_curr_day_no := 1;
610 else
611 l_curr_day_no := l_curr_day_no + 1;
612 end if;
613
614 l_curr_date := l_curr_date + 1;
615
616 end loop; /* Process dates in range */
617
618 p_hours_worked := l_total_hours;
619 p_days_worked := l_days_worked;
620 -- p_working_dates := l_working_dates;
621
622 hr_utility.set_location('Leaving calculate_time_worked_wp', 100);
623 RETURN;
624
625 -- Added by tmehra for nocopy changes Feb'03
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 hr_utility.set_location('Entering excep:', 110);
630 p_hours_worked := NULL;
631 p_days_worked := NULL;
632 p_working_dates := l_working_dates;
633 raise;
634
635 END calculate_time_worked_wp;
636
637
638 -- This function returns the time worked as specified in p_dimension(DAYS or
639 -- HOURS)
640 -- If the assignment does not have a WP then the default work pattern is used
641 FUNCTION get_time_worked
642 (p_assignment_id IN NUMBER
643 ,p_business_group_id IN NUMBER
644 ,p_date_start IN DATE
645 ,p_date_end IN DATE
646 ,p_dimension IN VARCHAR2 -- DAYS OR HOURS
647 ,p_default_wp IN VARCHAR2 DEFAULT NULL
648 ,p_override_wp IN VARCHAR2 DEFAULT NULL
649 ,p_working_dates OUT NOCOPY t_working_dates
650 ,p_error_code OUT NOCOPY NUMBER
651 ,p_error_message OUT NOCOPY VARCHAR2
652 ,p_is_assignment_wp IN BOOLEAN DEFAULT FALSE
653 ) RETURN NUMBER IS
654
655
656 /* Local variable declarations */
657 l_calc_stdt DATE;
658 l_calc_endt DATE;
659 l_curr_date DATE;
660
661 l_day_no NUMBER;
662 l_curr_day_no NUMBER;
663 l_days_in_wp NUMBER;
664 l__curr_day_no NUMBER;
665 l_hours NUMBER := 0;
666 l_hours_worked NUMBER := 0;
667 l_total_hours NUMBER := 0;
668 l_days_worked NUMBER := 0;
669 l_total_days NUMBER := 0;
670 l_retval NUMBER := 0;
671
672 l_asg_wp_found BOOLEAN := FALSE;
673
674 l_day VARCHAR2(30);
675
676 r_wp_dets c_wp_dets%ROWTYPE;
677 r_def_wp_dets c_wp_dets%ROWTYPE;
678 r_tmp_wp_dets c_wp_dets%ROWTYPE;
679 l_working_dates t_working_dates;
680 l_alt_work_pattern pay_user_columns.user_column_name%TYPE;
681
682 l_error_code NUMBER := 0;
683 l_err_msg_name fnd_new_messages.message_name%TYPE;
684 l_working_dates_nc t_working_dates;
685
686 BEGIN /* get_time_worked */
687
688 hr_utility.set_location('Entered get_time_worked', 10);
689 hr_utility.set_location('UDT Name :'||g_udt_name, 20);
690 hr_utility.trace('get_time_worked:p_override_wp:'||p_override_wp);
691
692 -- nocopy changes tmehra
693 l_working_dates_nc := p_working_dates;
694
695 /* If start date is greater than end date then return zero hours */
696 IF p_date_start > p_date_end
697 THEN
698 RETURN l_hours_worked;
699 END IF;
700
701 IF p_is_assignment_wp = FALSE THEN
702 -- If an override work pattern is supplied then no matter
703 -- whether the person had a work pattern or work pattern changes
704 -- or regardless of what the default work pattern is always use the
705 -- override the same
706 hr_utility.trace('p_is_assignment_wp FALSE:'||p_override_wp);
707 r_def_wp_dets := NULL;
708 r_def_wp_dets.effective_start_date := p_date_start;
709 r_def_wp_dets.effective_end_date := p_date_end;
710 r_def_wp_dets.business_group_id := p_business_group_id;
711 IF p_override_wp IS NULL THEN
712 r_def_wp_dets.work_pattern := p_default_wp;
713 ELSE
714 r_def_wp_dets.work_pattern := p_override_wp;
715 END IF;
716 hr_utility.trace('r_def_wp_dets.work_pattern:'||
717 r_def_wp_dets.work_pattern);
718
719 r_def_wp_dets.start_day
720 := 'Day '||LPAD
721 (TO_CHAR
722 (8 - (NEXT_DAY
723 (p_date_start, g_default_start_day)
724 - p_date_start
725 )
726 )
727 ,2,'0');
728
729 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 100);
730
731 calculate_time_worked_wp
732 (p_date_start => p_date_start
733 ,p_date_end => p_date_end
734 ,p_wp_dets => r_def_wp_dets
735 ,p_hours_worked => l_hours_worked -- OUT
736 ,p_days_worked => l_days_worked -- OUT
737 ,p_working_dates => l_working_dates -- IN OUT
738 );
739
740 l_total_hours := l_total_hours + l_hours_worked;
741 l_total_days := l_total_days + l_days_worked;
742
743
744 ELSE -- IF p_override_wp IS NOT NULL THEN
745
746 l_curr_date := p_date_start;
747
748
749 FOR r_wp_dets IN c_wp_dets(p_assignment_id, p_date_start, p_date_end)
750 LOOP /* GET WORK PATTERN DETAILS */
751
752 -- Only if this aat record contains a work pattern
753 IF r_wp_dets.work_pattern IS NOT NULL THEN
754
755 l_calc_stdt := l_curr_date;
756
757 hr_utility.set_location('Asg WP Found', 30);
758 l_asg_wp_found := TRUE;
759
760 -- And Calc Start Date is between ESD and EED
761 IF l_calc_stdt BETWEEN r_wp_dets.effective_start_date
762 AND r_wp_dets.effective_end_date THEN
763
764 -- Use only the AAT work pattern
765 hr_utility.set_location('Using only Asg WP', 40);
766
767 l_calc_endt := LEAST(p_date_end, r_wp_dets.effective_end_date);
768
769 hr_utility.trace(fnd_date.date_to_canonical(l_calc_stdt));
770 calculate_time_worked_wp
771 (p_date_start => l_calc_stdt
772 ,p_date_end => l_calc_endt
773 ,p_wp_dets => r_wp_dets
774 ,p_hours_worked => l_hours_worked -- OUT
775 ,p_days_worked => l_days_worked -- OUT
776 ,p_working_dates => l_working_dates -- IN OUT
777 );
778
779 l_total_hours := l_total_hours + l_hours_worked;
780 l_total_days := l_total_days + l_days_worked;
781
782 ELSIF p_default_wp IS NOT NULL THEN
783 -- Use the default work pattern for the period where there is no
784 -- work pattern on assignment and then use the asg work pattern
785
786 hr_utility.set_location('Using default and Asg WP', 50);
787
788 -- Step 1) Get working hours and days for the default work pattern
789 l_calc_endt := LEAST(p_date_end, (r_wp_dets.effective_start_date - 1));
790
791 r_def_wp_dets := NULL;
792 r_def_wp_dets.effective_start_date := l_calc_stdt;
793 r_def_wp_dets.effective_end_date := l_calc_endt;
794 r_def_wp_dets.business_group_id := p_business_group_id;
795 r_def_wp_dets.work_pattern := p_default_wp;
796 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
797 (NEXT_DAY(l_calc_stdt, g_default_start_day) -
798 l_calc_stdt)),2,'0');
799
800 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 60);
801
802 hr_utility.trace(fnd_date.date_to_canonical(l_calc_stdt));
803 calculate_time_worked_wp
804 (p_date_start => l_calc_stdt
805 ,p_date_end => l_calc_endt
806 ,p_wp_dets => r_def_wp_dets
807 ,p_hours_worked => l_hours_worked -- OUT
808 ,p_days_worked => l_days_worked -- OUT
809 ,p_working_dates => l_working_dates -- IN OUT
810 );
811
812 l_total_hours := l_total_hours + l_hours_worked;
813 l_total_days := l_total_days + l_days_worked;
814
815 -- Step 2) Get working hours and days for the assignment work pattern
816
817 -- If still there are dates to be dealth with
818 IF l_calc_endt < p_date_end THEN
819 --
820 l_calc_stdt := l_calc_endt + 1;
821 l_calc_endt := LEAST(p_date_end, r_wp_dets.effective_end_date);
822
823 calculate_time_worked_wp
824 (p_date_start => l_calc_stdt
825 ,p_date_end => l_calc_endt
826 ,p_wp_dets => r_wp_dets
827 ,p_hours_worked => l_hours_worked -- OUT
828 ,p_days_worked => l_days_worked -- OUT
829 ,p_working_dates => l_working_dates -- IN OUT
830 );
831
832 l_total_hours := l_total_hours + l_hours_worked;
833 l_total_days := l_total_days + l_days_worked;
834 --
835 END IF; -- l_calc_endt < p_date_end then
836 --
837 ELSE -- No default work pattern found, raise error and exit the loop.
838 l_error_code := -1;
839 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
840 EXIT;
841 END IF; -- l_calc_stdt between r_wp_dets.effective_start_date
842
843 -- Set up the next start date
844 l_curr_date := l_calc_endt + 1;
845
846 END IF; -- r_wp_dets.work_pattern is not null then
847 --
848 END LOOP; /* Get Work Pattern Details */
849
850 -- If ASG Work Pattern not found at AAT level or WP history not sufficient on
851 -- AAT then do the calculation using the default work pattern if it has been
852 -- passed
853 IF l_error_code = 0 -- No errors have occured
854 AND
855 ( -- No WP found on AAT
856 NOT l_asg_wp_found
857 OR
858 -- not enough WP history on AAT
859 l_curr_date <= p_date_end
860 ) THEN
861
862 IF p_default_wp IS NOT NULL THEN
863
864 hr_utility.set_location('Default WP available', 70);
865
866 -- Set the start and end dates
867 IF NOT l_asg_wp_found THEN
868 hr_utility.set_location('Asg WP was NOT Found', 80);
869 l_calc_stdt := p_date_start;
870 ELSE
871 hr_utility.set_location('Asg WP history insufficient or incomplete',
872 90);
873 l_calc_stdt := l_curr_date;
874 END IF;
875 --
876 l_calc_endt := p_date_end;
877
878 r_def_wp_dets := NULL;
879 r_def_wp_dets.effective_start_date := l_calc_stdt;
880 r_def_wp_dets.effective_end_date := l_calc_endt;
881 r_def_wp_dets.business_group_id := p_business_group_id;
882 r_def_wp_dets.work_pattern := p_default_wp;
883 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
884 (NEXT_DAY(l_calc_stdt, g_default_start_day) -
885 l_calc_stdt)),2,'0');
886
887 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 100);
888
889 calculate_time_worked_wp
890 (p_date_start => l_calc_stdt
891 ,p_date_end => l_calc_endt
892 ,p_wp_dets => r_def_wp_dets
893 ,p_hours_worked => l_hours_worked -- OUT
894 ,p_days_worked => l_days_worked -- OUT
895 ,p_working_dates => l_working_dates -- IN OUT
896 );
897
898 l_total_hours := l_total_hours + l_hours_worked;
899 l_total_days := l_total_days + l_days_worked;
900
901 ELSE -- no default wp and no wp on assignment, raise error
902 l_error_code := -1;
903 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
904 END IF;
905 --
906 END IF; -- l_error_code = 0 AND (NOT l_asg_wp_found...
907
908 END IF; -- IF p_override_wp IS NOT NULL THEN ... ELSE ...
909
910 -- Check for errors
911
912 hr_utility.trace('l_error_code:'||
913 fnd_number.number_to_canonical(l_error_code));
914 IF l_error_code <> 0 THEN
915 l_retval := 0;
916 ELSE -- No errors, assign the value
917 -- Decide what to return
918 hr_utility.trace('p_dimension:'||p_dimension);
919
920 IF p_dimension = 'DAYS' THEN
921 hr_utility.trace('l_total_days:'||
922 fnd_number.number_to_canonical(l_total_days));
923 l_retval := l_total_days;
924 ELSIF p_dimension = 'HOURS' THEN
925 hr_utility.trace('l_total_hours:'||
926 fnd_number.number_to_canonical(l_total_hours));
927 l_retval := l_total_hours;
928 END IF;
929 --
930 p_working_dates := l_working_dates;
931 --
932 END IF; -- l_error_code <> 0 then
933 --
934 p_error_code := l_error_code;
935 --
936 IF l_err_msg_name IS NOT NULL THEN
937 p_error_message := substr(fnd_message.get_string('PQP',l_err_msg_name)
938 ,255 -- Bugfix 3405270
939 );
940 END IF;
941 --
942 hr_utility.trace('get_time_worked:l_retval:'||
943 fnd_number.number_to_canonical(l_retval));
944
945 RETURN l_retval;
946
947 -- Added by tmehra for nocopy changes Feb'03
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 hr_utility.trace('Exception Block : When others');
952 p_error_code := SQLCODE;
953 p_error_message := SQLERRM;
954 p_working_dates := l_working_dates_nc;
955 raise;
956
957
958 END get_time_worked;
959
960
961 FUNCTION get_days_worked
962 (p_assignment_id IN NUMBER
963 ,p_business_group_id IN NUMBER
964 ,p_date_start IN DATE
965 ,p_date_end IN DATE
966 ,p_working_dates OUT NOCOPY t_working_dates
967 ,p_error_code OUT NOCOPY NUMBER
968 ,p_error_message OUT NOCOPY VARCHAR2
969 ,p_default_wp IN VARCHAR2 DEFAULT NULL
970 ,p_override_wp IN VARCHAR2 DEFAULT NULL
971 ) RETURN NUMBER IS
972
973 l_days_worked NUMBER := 0;
974 l_working_dates t_working_dates;
975 l_working_dates_nc t_working_dates;
976
977 BEGIN
978
979 hr_utility.trace('get_days_worked2:p_override_wp:'||p_override_wp);
980
981 -- nocopy changes tmehra
982 l_working_dates_nc := p_working_dates;
983
984 l_days_worked := get_time_worked
985 (p_assignment_id => p_assignment_id
986 ,p_business_group_id => p_business_group_id
987 ,p_date_start => p_date_start
988 ,p_date_end => p_date_end
989 ,p_dimension => 'DAYS'
990 ,p_default_wp => p_default_wp
991 ,p_override_wp => p_override_wp
992 ,p_working_dates => l_working_dates -- OUT
993 ,p_error_code => p_error_code -- OUT
994 ,p_error_message => p_error_message -- OUT
995 ,p_is_assignment_wp => TRUE
996 );
997
998 -- Check for errors
999 if p_error_code <> 0 then
1000 l_days_worked := 0;
1001 else -- No errors, assign values
1002 p_working_dates := l_working_dates;
1003 end if;
1004
1005 RETURN l_days_worked;
1006
1007 -- Added by tmehra for nocopy changes Feb'03
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 hr_utility.trace('Exception Block : When others');
1012 p_error_code := SQLCODE;
1013 p_error_message := SQLERRM;
1014 p_working_dates := l_working_dates_nc;
1015 raise;
1016 END get_days_worked;
1017
1018 -- OVERLOADED get_days_worked
1019 -- Returns the number of days worked in the given date range
1020 -- Uses Default Work Pattern if Assignment does not have a WP
1021 FUNCTION get_days_worked
1022 (p_assignment_id IN NUMBER
1023 ,p_business_group_id IN NUMBER
1024 ,p_date_start IN DATE
1025 ,p_date_end IN DATE
1026 ,p_error_code OUT NOCOPY NUMBER
1027 ,p_error_message OUT NOCOPY VARCHAR2
1028 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1029 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1030 ) RETURN NUMBER IS
1031
1032 l_days_worked NUMBER := 0;
1033 l_working_dates t_working_dates;
1034
1035 BEGIN
1036
1037 hr_utility.trace('get_days_worked1:p_override_wp:'||p_override_wp);
1038
1039 l_days_worked := get_days_worked
1040 (p_assignment_id => p_assignment_id
1041 ,p_business_group_id => p_business_group_id
1042 ,p_date_start => p_date_start
1043 ,p_date_end => p_date_end
1044 ,p_default_wp => p_default_wp
1045 ,p_override_wp => p_override_wp
1046 ,p_working_dates => l_working_dates -- OUT
1047 ,p_error_code => p_error_code -- OUT
1048 ,p_error_message => p_error_message -- OUT
1049 );
1050
1051 RETURN l_days_worked;
1052
1053 -- Added by tmehra for nocopy changes Feb'03
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 hr_utility.trace('Exception Block : When others');
1058 p_error_code := SQLCODE;
1059 p_error_message := SQLERRM;
1060 raise;
1061
1062 END get_days_worked;
1063
1064
1065 FUNCTION get_hours_worked
1066 (p_assignment_id IN NUMBER
1067 ,p_business_group_id IN NUMBER
1068 ,p_date_start IN DATE
1069 ,p_date_end IN DATE
1070 ,p_error_code OUT NOCOPY NUMBER
1071 ,p_error_message OUT NOCOPY VARCHAR2
1072 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1073 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1074 ,p_is_assignment_wp IN BOOLEAN DEFAULT FALSE
1075 ) RETURN NUMBER
1076 IS
1077
1078 l_hours_worked NUMBER := 0;
1079 l_working_dates t_working_dates;
1080 l_error_code VARCHAR2(10) := NULL;
1081
1082 BEGIN
1083
1084 l_hours_worked := get_time_worked
1085 (p_assignment_id => p_assignment_id
1086 ,p_business_group_id => p_business_group_id
1087 ,p_date_start => p_date_start
1088 ,p_date_end => p_date_end
1089 ,p_dimension => 'HOURS'
1090 ,p_default_wp => p_default_wp
1091 ,p_override_wp => p_override_wp
1092 ,p_working_dates => l_working_dates -- OUT
1093 ,p_error_code => p_error_code -- OUT
1094 ,p_error_message => p_error_message -- OUT
1095 ,p_is_assignment_wp => p_is_assignment_wp
1096 );
1097 -- Check for errors
1098 IF p_error_code <> 0 THEN
1099 l_hours_worked := 0;
1100 END IF;
1101
1102 RETURN l_hours_worked;
1103
1104 -- Added by tmehra for nocopy changes Feb'03
1105
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 hr_utility.trace('Exception Block : When others');
1109 p_error_code := SQLCODE;
1110 p_error_message := SQLERRM;
1111 raise;
1112
1113 END get_hours_worked;
1114
1115 FUNCTION is_working_day
1116 (p_assignment_id IN NUMBER
1117 ,p_business_group_id IN NUMBER
1118 ,p_date IN DATE
1119 ,p_error_code OUT NOCOPY NUMBER
1120 ,p_error_message OUT NOCOPY VARCHAR2
1121 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1122 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1123 ) RETURN VARCHAR2
1124 IS
1125
1126 l_days_worked NUMBER := 0;
1127 l_is_working_day VARCHAR2(1) := 'N';
1128 l_working_dates t_working_dates;
1129
1130 BEGIN /*is_working_day*/
1131
1132 hr_utility.trace('Entered Is Working Day?'||
1133 fnd_date.date_to_canonical(p_date));
1134 hr_utility.trace('p_assignment_id:'||
1135 fnd_number.number_to_canonical(p_assignment_id));
1136 hr_utility.trace('p_override_wp:'||p_override_wp);
1137
1138 l_days_worked := get_days_worked
1139 (p_assignment_id => p_assignment_id
1140 ,p_business_group_id => p_business_group_id
1141 ,p_date_start => p_date
1142 ,p_date_end => p_date
1143 ,p_default_wp => p_default_wp
1144 ,p_override_wp => p_override_wp
1145 ,p_working_dates => l_working_dates -- OUT
1146 ,p_error_code => p_error_code -- OUT
1147 ,p_error_message => p_error_message -- OUT
1148 );
1149
1150 IF l_days_worked = 1 AND p_error_code = 0 THEN
1151 l_is_working_day := 'Y';
1152 END IF;
1153
1154 hr_utility.trace('p_assignment_id:'||
1155 fnd_number.number_to_canonical(p_assignment_id));
1156 hr_utility.trace('Leaving Is Working Day?'||
1157 fnd_date.date_to_canonical(p_date));
1158
1159
1160
1161 RETURN l_is_working_day;
1162
1163 -- Added by tmehra for nocopy changes Feb'03
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 hr_utility.trace('Exception Block : When others');
1168 p_error_code := SQLCODE;
1169 p_error_message := SQLERRM;
1170 raise;
1171
1172
1173 END is_working_day; /*is_working_day*/
1174 --
1175 --
1176 --
1177 PROCEDURE add_working_days_wp
1178 (p_wp_dets IN c_wp_dets%ROWTYPE
1179 ,p_curr_date IN OUT NOCOPY DATE
1180 ,p_balance_days IN OUT NOCOPY NUMBER
1181 )
1182 IS
1183
1184 l_calc_stdt DATE;
1185 l_calc_endt DATE;
1186 l_day_no NUMBER;
1187 l_days_in_wp NUMBER;
1188 l_curr_day_no NUMBER;
1189 l_day VARCHAR2(30);
1190 l_hours NUMBER := 0;
1191 l_continue VARCHAR2(1) := 'Y';
1192
1193 l_curr_date_nc DATE;
1194 l_balance_days_nc NUMBER;
1195
1196
1197 BEGIN -- add_working_days_wp
1198
1199 hr_utility.set_location('Entered get_next_working_date_WP', 10);
1200
1201 -- nocopy changes tmehra
1202 l_curr_date_nc := p_curr_date;
1203 l_balance_days_nc := p_balance_days;
1204
1205 /* Determine Calculation Start Date for this Work Pattern */
1206 if p_curr_date > p_wp_dets.effective_start_date
1207 then
1208 l_calc_stdt := p_curr_date;
1209 else
1210 l_calc_stdt := p_wp_dets.effective_start_date;
1211 end if;
1212
1213 /* Set Calculation End Date for this Work Pattern */
1214 l_calc_endt := p_wp_dets.effective_end_date;
1215
1216 /* Get day number on calculation start date and number of days in Work Pattern
1217 */
1218 get_day_dets
1219 (p_wp_dets => p_wp_dets
1220 ,p_calc_stdt => l_calc_stdt
1221 ,p_calc_edt => l_calc_endt
1222 ,p_day_no => l_day_no /* OUT NOCOPY */
1223 ,p_days_in_wp => l_days_in_wp /* OUT NOCOPY */
1224 );
1225
1226 l_curr_day_no := l_day_no;
1227
1228 hr_utility.set_location('l_curr_day_no :'||l_curr_day_no, 20);
1229 hr_utility.set_location('p_curr_date :'||p_curr_date, 30);
1230 hr_utility.set_location('Work Pattern :'||p_wp_dets.work_pattern, 40);
1231
1232 -- Loop throug the dates starting from p_curr_date
1233 -- PS : we don't know till when to loop, so p_balance_days
1234 -- will be used as a balance counter
1235 loop -- Through dates starting with p_curr_date
1236
1237 l_day := 'Day '||lpad(l_curr_day_no,2,0);
1238
1239 hr_utility.set_location('Processing date :'||to_char(p_curr_date,
1240 'DD/MM/YYYY'), 60);
1241
1242 begin
1243 l_hours := hruserdt.get_table_value
1244 (p_bus_group_id => p_wp_dets.business_group_id
1245 ,p_table_name => g_udt_name
1246 ,p_col_name => p_wp_dets.work_pattern
1247 ,p_row_value => l_day
1248 ,p_effective_date => p_curr_date
1249 );
1250 exception
1251 when no_data_found then
1252 /*
1253 * No data was entered. Do not add to total
1254 * or count the day in the loop.
1255 */
1256 l_hours := 0;
1257 end;
1258
1259 hr_utility.set_location('Hours on '||l_day||' = '||l_hours, 70);
1260
1261 -- Decrement working days balance if l_curr_day_no
1262 -- is a working day
1263 if l_hours > 0 then
1264
1265 p_balance_days := p_balance_days - 1;
1266
1267 end if;
1268
1269 -- If we have counted down all the working days then exit
1270 if p_balance_days = 0 then
1271 l_continue := 'N';
1272 exit;
1273 end if;
1274
1275 /* Calculate next day no */
1276 if l_curr_day_no = l_days_in_wp then
1277 l_curr_day_no := 1;
1278 else
1279 l_curr_day_no := l_curr_day_no + 1;
1280 end if;
1281
1282 -- Increment to the next date
1283 p_curr_date := p_curr_date + 1;
1284
1285 -- The WP has changed, exit, but continue process using the next
1286 -- effective work pattern row
1287 if p_curr_date > p_wp_dets.effective_end_date then
1288 l_continue := 'Y';
1289 exit;
1290 end if;
1291
1292 end loop; -- Through dates starting with p_curr_date
1293
1294 hr_utility.set_location('Leaving get_next_working_date_wp', 100);
1295 RETURN;
1296
1297 -- Added by tmehra for nocopy changes Feb'03
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 hr_utility.set_location('Exception Block : When others',110);
1302 p_curr_date := l_curr_date_nc;
1303 p_balance_days := l_balance_days_nc;
1304 raise;
1305
1306
1307 END add_working_days_wp;
1308
1309 FUNCTION add_working_days
1310 (p_assignment_id IN NUMBER
1311 ,p_business_group_id IN NUMBER
1312 ,p_date_start IN DATE
1313 ,p_days IN NUMBER
1314 ,p_error_code OUT NOCOPY NUMBER
1315 ,p_error_message OUT NOCOPY VARCHAR2
1316 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1317 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1318 ) RETURN DATE
1319 IS
1320
1321 l_balance_days NUMBER;
1322 l_curr_date DATE;
1323 l_calc_stdt DATE;
1324 l_calc_endt DATE;
1325 l_day_no NUMBER;
1326 l_days_in_wp NUMBER;
1327 l_curr_day_no NUMBER;
1328 l_day VARCHAR2(30);
1329 l_hours NUMBER := 0;
1330 l_continue VARCHAR2(1) := 'Y';
1331 l_asg_wp_found BOOLEAN := FALSE;
1332 l_error_code NUMBER := 0;
1333 l_err_msg_name fnd_new_messages.message_name%TYPE;
1334
1335 r_wp_dets c_wp_dets%ROWTYPE;
1336 r_def_wp_dets c_wp_dets%ROWTYPE;
1337
1338 BEGIN /*add_working_days*/
1339
1340 hr_utility.set_location('Entered add_working_days', 10);
1341 hr_utility.set_location('UDT Name :'||g_udt_name, 20);
1342
1343 -- Add 1 to working days and assign to balance days
1344 -- We need to this as we want to return the date
1345 -- prior to the NEXT working day after p_days working days
1346 -- have been added to start date
1347 l_balance_days := floor(p_days) + 1;
1348
1349 l_curr_date := p_date_start;
1350
1351 for r_wp_dets in c_wp_dets_up(p_assignment_id, p_date_start)
1352 loop /* Get Work Pattern Details */
1353
1354 -- Only if this aat record contains a work pattern
1355 if r_wp_dets.work_pattern is not null then
1356
1357 hr_utility.set_location('Asg WP Found', 30);
1358 l_asg_wp_found := TRUE;
1359
1360 if l_curr_date between r_wp_dets.effective_start_date
1361 and r_wp_dets.effective_end_date then
1362
1363 add_working_days_wp
1364 (p_wp_dets => r_wp_dets
1365 ,p_curr_date => l_curr_date -- IN OUT
1366 ,p_balance_days => l_balance_days -- IN OUT
1367 );
1368 --
1369 elsif p_default_wp IS NOT NULL then
1370 -- Use the default work pattern for the period where there is no
1371 -- work pattern on assignment and then use the asg work pattern
1372
1373 hr_utility.set_location('Using default and Asg WP', 50);
1374
1375 -- Step 1) Add days for the default work pattern
1376 r_def_wp_dets := NULL;
1377 r_def_wp_dets.effective_start_date := l_curr_date;
1378 -- set effective end date as the day before the Asg WP becomes effective
1379 r_def_wp_dets.effective_end_date := (r_wp_dets.effective_start_date -
1380 1);
1381 r_def_wp_dets.business_group_id := p_business_group_id;
1382 r_def_wp_dets.work_pattern := p_default_wp;
1383 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
1384 (NEXT_DAY(l_curr_date, g_default_start_day) -
1385 l_curr_date)),2,'0');
1386
1387 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 50);
1388
1389 add_working_days_wp
1390 (p_wp_dets => r_def_wp_dets
1391 ,p_curr_date => l_curr_date -- IN OUT
1392 ,p_balance_days => l_balance_days -- IN OUT
1393 );
1394
1395 -- Step 2) Add days for the assignment work pattern
1396 -- But, only if there are more days to be added
1397 if l_balance_days > 0 then
1398 add_working_days_wp
1399 (p_wp_dets => r_wp_dets
1400 ,p_curr_date => l_curr_date -- IN OUT
1401 ,p_balance_days => l_balance_days -- IN OUT
1402 );
1403 end if;
1404 --
1405 else -- No default work pattern found, raise error and exit the loop.
1406 l_error_code := -1;
1407 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
1408 exit;
1409 end if; -- l_calc_stdt between r_wp_dets.effective_start_date
1410 --
1411 end if; -- if r_wp_dets.work_pattern is not null then
1412 -- Exit the loop if there are no more days to add
1413 if l_balance_days = 0 then
1414 exit;
1415 end if;
1416 --
1417 end loop; /* Get Work Pattern Details */
1418
1419 if l_error_code = 0 -- No errors have occured
1420 AND
1421 ( -- No WP found on AAT
1422 NOT l_asg_wp_found
1423 OR
1424 -- not enough WP history on AAT so more days still to be added
1425 l_balance_days > 0
1426 ) then
1427
1428 if p_default_wp IS NOT NULL then
1429
1430 hr_utility.set_location('Asg WP NOT Found, default WP available', 40);
1431
1432 r_def_wp_dets := NULL;
1433 r_def_wp_dets.effective_start_date := l_curr_date;
1434 r_def_wp_dets.effective_end_date := hr_api.g_eot; -- End of Time
1435 r_def_wp_dets.business_group_id := p_business_group_id;
1436 r_def_wp_dets.work_pattern := p_default_wp;
1437 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
1438 (NEXT_DAY(l_curr_date, g_default_start_day) -
1439 l_curr_date)),2,'0');
1440
1441 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 50);
1442
1443 add_working_days_wp
1444 (p_wp_dets => r_def_wp_dets
1445 ,p_curr_date => l_curr_date -- IN OUT
1446 ,p_balance_days => l_balance_days -- IN OUT
1447 );
1448 else
1449 l_error_code := -1;
1450 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
1451 end if;
1452 --
1453 end if; -- if NOT l_asg_wp_found the
1454
1455 -- Check for errors
1456 if l_error_code = 0 then
1457
1458 -- Check if balance has been zeroed, if not, error.
1459 if l_balance_days > 0 then
1460 l_error_code := -2;
1461 l_err_msg_name := 'PQP_230590_WP_HIST_INCOMPLETE';
1462 else -- No errors
1463 l_curr_date := l_curr_date - 1; -- previous day to next working day
1464 end if;
1465 --
1466 end if; -- l_error_code = 0 then
1467
1468 p_error_code := l_error_code;
1469 --
1470 if l_err_msg_name IS NOT NULL then
1471 p_error_message := substr(fnd_message.get_string('PQP',l_err_msg_name)
1472 ,255 -- Bugfix 3405270
1473 );
1474 end if;
1475
1476 return l_curr_date;
1477
1478 -- Added by tmehra for nocopy changes Feb'03
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 hr_utility.trace('Exception Block : When others');
1483 p_error_code := SQLCODE;
1484 p_error_message := SQLERRM;
1485 raise;
1486
1487
1488 END add_working_days;
1489
1490 FUNCTION calculate_days_worked
1491 (p_assignment_id IN NUMBER
1492 ,p_date_start IN DATE
1493 ,p_date_end IN DATE
1494 ) RETURN NUMBER
1495 IS
1496
1497 l_hours_worked NUMBER;
1498
1499 BEGIN /*calculate_days_worked*/
1500
1501 -- Reset g_days_worked
1502 g_days_worked := 0;
1503
1504 -- Call the time(hours) worked function.
1505 -- This function calculatea the days worked and stores it in g_days_worked
1506 l_hours_worked := calculate_time_worked
1507 (p_assignment_id => p_assignment_id
1508 ,p_date_start => p_date_start
1509 ,p_date_end => p_date_end
1510 );
1511
1512 RETURN g_days_worked;
1513
1514 END calculate_days_worked;
1515
1516 -- Returns the number of Working Days in a Workpattern
1517 -- as on the effective date
1518 -- it takes 2 optional parameters p_override_wp, p_default_wp
1519 -- Order of precedence is Override->Assignment->Default
1520 FUNCTION get_working_days_in_week (
1521 p_assignment_id IN NUMBER
1522 ,p_business_group_id IN NUMBER
1523 ,p_effective_date IN DATE
1524 ,p_default_wp IN VARCHAR2
1525 ,p_override_wp IN VARCHAR2
1526 ) RETURN NUMBER
1527 IS
1528 l_retval number ;
1529 l_value pay_user_column_instances_f.value%TYPE ;
1530 l_error_message fnd_new_messages.message_text%TYPE ;
1531 l_proc_name VARCHAR2(61) := g_package_name||'get_working_days_in_week';
1532 l_proc_step NUMBER(20,10) ;
1533 l_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
1534 l_errbuff VARCHAR2(200);
1535 l_retcode NUMBER;
1536
1537 --l_count number ;
1538
1539 CURSOR csr_get_wp IS
1540 select work_pattern
1541 from pqp_assignment_attributes_f paa
1542 where assignment_id = p_assignment_id
1543 and business_group_id = p_business_group_id
1544 and p_effective_date between paa.effective_start_date
1545 and paa.effective_end_date ;
1546 begin
1547 IF g_debug THEN
1548 pqp_utilities.debug_enter(l_proc_name);
1549 pqp_utilities.debug('p_assignment_id:'||p_assignment_id);
1550 pqp_utilities.debug('p_business_group_id:'||p_business_group_id);
1551 pqp_utilities.debug('p_effective_date:'||p_effective_date);
1552 END IF;
1553
1554 IF p_override_wp IS NOT NULL THEN
1555 l_proc_step := 10 ;
1556 IF g_debug THEN
1557 pqp_utilities.debug(' Override WP:'||p_override_wp);
1558 END IF;
1559 l_work_pattern := p_override_wp ;
1560 ELSE
1561 l_proc_step := 20 ;
1562
1563 OPEN csr_get_wp ;
1564 FETCH csr_get_wp INTO l_work_pattern ;
1565 CLOSE csr_get_wp ;
1566
1567 END IF;
1568
1569 l_work_pattern := NVL(l_work_pattern,p_default_wp);
1570
1571 l_proc_step := 30 ;
1572 IF g_debug THEN
1573 pqp_utilities.debug('Work Pattern:'||l_work_pattern);
1574 END IF ;
1575
1576 IF l_work_pattern IS NOT NULL THEN
1577
1578 -- FOR i in 1..7 loop
1579 l_retval := pqp_utilities.pqp_gb_get_table_value(
1580 p_business_group_id => p_business_group_id
1581 ,p_effective_date => p_effective_date
1582 ,p_table_name => 'PQP_COMPANY_WORK_PATTERNS'
1583 ,p_column_name => l_work_pattern
1584 ,p_row_name => 'Average Working Days Per Week'
1585 --'Day 0'||i
1586 ,p_value => l_value
1587 ,p_error_msg => l_error_message
1588 ) ;
1589
1590
1591 -- if l_value > 0 then
1592 -- l_count := nvl(l_count,0) + 1 ;
1593 -- end if ;
1594 -- end loop ;
1595 -- If the value for Average working days per week does not exist
1596 -- calculate the same and update the udt and refetch the value.
1597 IF l_value IS NULL THEN
1598
1599 l_proc_step := 35 ;
1600 IF g_debug THEN
1601 pqp_utilities.debug(l_proc_step);
1602 pqp_utilities.debug('p_assignment_id:'||p_assignment_id);
1603 END IF;
1604
1605 pqp_update_work_pattern_table.update_working_days_in_week
1606 (errbuf => l_errbuff
1607 ,retcode => l_retcode
1608 ,p_column_name => l_work_pattern
1609 ,p_business_group_id => p_business_group_id
1610 ,p_overwrite_if_exists => 'Y'
1611 );
1612
1613 l_proc_step := 40 ;
1614 IF g_debug THEN
1615 pqp_utilities.debug(l_proc_step);
1616 pqp_utilities.debug('errbuf:'||l_errbuff);
1617 pqp_utilities.debug('retcode:',l_retcode);
1618 END IF;
1619
1620 l_retval :=
1621 pqp_utilities.pqp_gb_get_table_value
1622 (p_business_group_id => p_business_group_id
1623 ,p_effective_date => p_effective_date
1624 ,p_table_name => 'PQP_COMPANY_WORK_PATTERNS'
1625 ,p_column_name => l_work_pattern
1626 ,p_row_name => 'Average Working Days Per Week'
1627 ,p_value => l_value
1628 ,p_error_msg => l_error_message
1629 ,p_refresh_cache =>'Y'
1630 ) ;
1631
1632 l_proc_step := 45 ;
1633 IF g_debug THEN
1634 pqp_utilities.debug(l_proc_step);
1635 pqp_utilities.debug('l_error_message:'||l_error_message);
1636 pqp_utilities.debug('l_retval:',l_retval);
1637 pqp_utilities.debug('l_value:',l_value);
1638 END IF;
1639
1640 IF l_value IS NULL THEN
1641 fnd_message.set_name( 'PQP', 'PQP_230138_INV_WORK_PATTERN' );
1642 fnd_message.set_token( 'WORKPATTERN ',l_work_pattern);
1643 fnd_message.raise_error ;
1644 END IF;
1645
1646
1647 END IF ;
1648
1649 END IF; -- l_work_pattern IS NOT NULL THEN
1650
1651
1652 IF g_debug THEN
1653 pqp_utilities.debug_exit(l_proc_name) ;
1654 END IF ;
1655
1656 RETURN l_value ; -- l_count ;
1657
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1661 pqp_utilities.debug_others
1662 (l_proc_name
1663 ,l_proc_step
1664 );
1665 IF g_debug THEN
1666 pqp_utilities.debug('Leaving: '||l_proc_name,-999);
1667 END IF;
1668 fnd_message.raise_error;
1669 ELSE
1670 RAISE;
1671 END IF;
1672
1673 END get_working_days_in_week ;
1674 --
1675 --
1676 --
1677 FUNCTION get_day_index_for_date
1678 (p_asg_work_pattern_start_date IN DATE
1679 ,p_asg_work_pattern_start_day_n IN NUMBER
1680 ,p_total_days_in_work_pattern IN NUMBER
1681 ,p_date_to_index IN DATE
1682 ) RETURN NUMBER
1683 IS
1684
1685 l_date_index BINARY_INTEGER;
1686 l_days_to_first_day_of_Day01 BINARY_INTEGER;
1687 l_first_date_of_asg_on_Day01 DATE;
1688 l_days_between_start_and_first NUMBER;
1689
1690 l_proc_step NUMBER(20,10):=0;
1691 l_proc_name VARCHAR2(61):= g_package_name||'get_day_index_for_date';
1692
1693 BEGIN
1694
1695 g_debug := hr_utility.debug_enabled;
1696 IF g_debug THEN
1697 debug_enter(l_proc_name);
1698 END IF;
1699
1700 IF p_asg_work_pattern_start_date IS NULL
1701 THEN
1702 -- then it was either the default or override
1703 -- so use the 7 day week logic with wp starting on Sunday(or preset global day of week) on Day 1
1704
1705 l_proc_step := 10;
1706 IF g_debug THEN
1707 debug(l_proc_name,l_proc_step);
1708 END IF;
1709
1710 l_date_index := NEXT_DAY(p_date_to_index, g_default_start_day) - p_date_to_index;
1711
1712 ELSE
1713 -- it is assignment level work patter, duplicate get_day_dets logic
1714 -- save on the perf issue in this function
1715
1716 l_proc_step := 20;
1717 IF g_debug THEN
1718 debug(l_proc_name,l_proc_step);
1719 END IF;
1720
1721 l_days_to_first_day_of_Day01 := p_total_days_in_work_pattern - p_asg_work_pattern_start_day_n + 1;
1722 l_first_date_of_asg_on_Day01 := p_asg_work_pattern_start_date + l_days_to_first_day_of_Day01;
1723 l_days_between_start_and_first := p_date_to_index - l_first_date_of_asg_on_Day01;
1724
1725 IF l_days_between_start_and_first < 0 THEN
1726 l_proc_step := 22;
1727 IF g_debug THEN
1728 debug(l_proc_name,l_proc_step);
1729 END IF;
1730 l_date_index := p_total_days_in_work_pattern - ABS(l_days_between_start_and_first) + 1;
1731 ELSE
1732 l_proc_step := 25;
1733 IF g_debug THEN
1734 debug(l_proc_name,l_proc_step);
1735 END IF;
1736 l_date_index := MOD(ABS(l_days_between_start_and_first),p_total_days_in_work_pattern) + 1;
1737 END IF;
1738
1739 END IF; -- IF p_asg_work_pattern_start_date IS NULL
1740
1741 IF g_debug THEN
1742 debug('l_date_index:'||l_date_index);
1743 debug_exit(l_proc_name);
1744 END IF;
1745
1746 RETURN l_date_index;
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 clear_cache;
1751 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1752 debug_others(l_proc_name,l_proc_step);
1753 IF g_debug THEN
1754 debug('Leaving: '||l_proc_name,-999);
1755 END IF;
1756 fnd_message.raise_error;
1757 ELSE
1758 RAISE;
1759 END IF;
1760 END get_day_index_for_date;
1761 --
1762 --
1763 --
1764 PROCEDURE load_work_pattern_into_cache
1765 (p_assignment_id IN NUMBER
1766 ,p_business_group_id IN NUMBER
1767 ,p_date_start IN DATE
1768 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1769 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1770 ,p_work_pattern_used OUT NOCOPY VARCHAR2
1771 ,p_asg_work_pattern_start_day_n OUT NOCOPY BINARY_INTEGER
1772 ,p_asg_work_pattern_start_date OUT NOCOPY DATE
1773 ,p_date_start_day_index OUT NOCOPY BINARY_INTEGER
1774 )
1775 IS
1776
1777 l_work_pattern_to_use pay_user_columns.user_column_name%TYPE;
1778 l_user_column_id pay_user_columns.user_column_id%TYPE;
1779 l_pqp_assignment_attributes c_wp_dets_up%ROWTYPE;
1780 l_day_NN_name pay_user_rows_f.row_low_range_or_name%TYPE;
1781 l_asg_work_pattern_start_day_n BINARY_INTEGER;
1782 i BINARY_INTEGER;
1783 j BINARY_INTEGER;
1784 l_asg_work_pattern_start_date DATE;
1785 l_date_start_day_index BINARY_INTEGER;
1786 l_legislation_code pay_user_rows_f.legislation_code%TYPE;
1787 l_next_working_day_found BOOLEAN;
1788 l_hours NUMBER;
1789
1790 l_proc_step NUMBER(20,10):=0;
1791 l_proc_name VARCHAR2(61):= g_package_name||'load_work_pattern_into_cache';
1792
1793
1794 CURSOR csr_get_user_column_id
1795 (p_user_table_name VARCHAR2
1796 ,p_user_column_name VARCHAR2
1797 ,p_business_group_id NUMBER
1798 ,p_legislation_code VARCHAR2
1799 ) IS
1800 SELECT ucs.user_column_id
1801 FROM pay_user_tables uts
1802 ,pay_user_columns ucs
1803 WHERE uts.user_table_name = p_user_table_name -- PQP_COMPANY_WORK_PATTERNS
1804 AND uts.business_group_id IS NULL
1805 AND uts.legislation_code = p_legislation_code -- as one table is seeded per legislation
1806 AND ucs.user_table_id = uts.user_table_id -- only work patterns that belong to the above table
1807 AND ucs.user_column_name = p_user_column_name -- which match this name work_pattern_name
1808 AND ( ucs.business_group_id = p_business_group_id -- in the users bg
1809 OR
1810 (ucs.business_group_id IS NULL -- or seeded
1811 AND
1812 ucs.legislation_code = p_legislation_code -- for the users legislation code
1813 )
1814 );
1815
1816
1817 --local cursor to pull work pattern this will looped and cached into t_work_pattern_cache_type
1818 --
1819
1820 CURSOR csr_work_pattern_hours
1821 (p_user_column_id pay_user_columns.user_column_id%TYPE
1822 ,p_business_group_id NUMBER
1823 ,p_legislation_code VARCHAR2
1824 ,p_effective_date DATE
1825 ) IS
1826 SELECT uci.user_row_id
1827 ,uci.value hours_in_text
1828 ,uci.effective_start_date
1829 ,uci.effective_end_date
1830 FROM pay_user_column_instances_f uci
1831 WHERE uci.user_column_id = p_user_column_id -- represents the work pattern
1832 AND p_effective_date
1833 BETWEEN uci.effective_start_date
1834 AND uci.effective_end_date
1835 AND ( uci.business_group_id = p_business_group_id
1836 OR
1837 ( uci.business_group_id IS NULL
1838 AND
1839 uci.legislation_code = p_legislation_code
1840 )
1841 );
1842
1843 CURSOR csr_work_pattern_days
1844 (p_user_row_id pay_user_rows_f.user_row_id%TYPE
1845 ,p_effective_date DATE
1846
1847 ) IS
1848 SELECT urw.row_low_range_or_name day_name
1849 FROM pay_user_rows_f urw
1850 WHERE urw.user_row_id = p_user_row_id
1851 AND p_effective_date
1852 BETWEEN urw.effective_start_date
1853 AND urw.effective_end_date
1854 AND urw.row_low_range_or_name like
1855 'Day __';
1856
1857
1858 BEGIN
1859 --/*
1860 --1. Determine the required working pattern, ie assignment, default or override
1861 --2. Cache if not allready (and if not effective as if date_start)
1862 --*/
1863
1864 --1. Determine the required working pattern, ie assignment, default or override
1865
1866 g_debug := hr_utility.debug_enabled;
1867 IF g_debug THEN
1868 debug_enter(l_proc_name);
1869 END IF;
1870
1871
1872 IF p_override_wp IS NOT NULL
1873 THEN
1874
1875 l_proc_step := 10;
1876 IF g_debug THEN
1877 debug(l_proc_name,l_proc_step);
1878 END IF;
1879
1880 l_work_pattern_to_use := p_override_wp;
1881
1882 ELSE
1883 --1. Is there an assignment level work pattern effective as of date start
1884 --2. If use the default work pattern
1885 -- ideally from a CS perspective this shouldn't happen
1886 -- but the function is generic so we code for default also.
1887
1888 l_proc_step := 20;
1889 IF g_debug THEN
1890 debug(l_proc_name,l_proc_step);
1891 END IF;
1892
1893 OPEN c_wp_dets_up(p_assignment_id, p_date_start);
1894 FETCH c_wp_dets_up INTO l_pqp_assignment_attributes;
1895 IF c_wp_dets_up%FOUND
1896 AND
1897 l_pqp_assignment_attributes.work_pattern IS NOT NULL
1898 THEN
1899 l_proc_step := 22;
1900 IF g_debug THEN
1901 debug(l_proc_name,l_proc_step);
1902 END IF;
1903 l_work_pattern_to_use := l_pqp_assignment_attributes.work_pattern;
1904 l_asg_work_pattern_start_day_n
1905 := fnd_number.canonical_to_number(TRIM(SUBSTR(l_pqp_assignment_attributes.start_day,5,2)));
1906 l_asg_work_pattern_start_date := l_pqp_assignment_attributes.effective_start_date;
1907 ELSE
1908 l_proc_step := 25;
1909 IF g_debug THEN
1910 debug(l_proc_name,l_proc_step);
1911 END IF;
1912 l_work_pattern_to_use := NVL(p_default_wp,'PQP_MON_FRI_8_HOURS');
1913 END IF;
1914 CLOSE c_wp_dets_up;
1915
1916 END IF; -- IF p_override_wp IS NOT NULL
1917
1918 IF g_debug THEN
1919 debug('Cache Reload Check');
1920 debug('g_last_business_group_id:'||g_last_business_group_id);
1921 debug('g_last_used_work_pattern:'||g_last_used_work_pattern);
1922 debug('l_work_pattern_to_use:'||l_work_pattern_to_use);
1923 debug('p_date_start:'||fnd_date.date_to_canonical(p_date_start));
1924 debug('g_last_max_effective_start_dt:'||
1925 fnd_date.date_to_canonical(g_last_max_effective_start_dt));
1926 debug('g_last_min_effective_end_dt:'||
1927 fnd_date.date_to_canonical(g_last_min_effective_end_dt));
1928 END IF;
1929
1930 --2. Cache if not allready (and if not effective as if date_start)
1931
1932 IF g_last_business_group_id IS NULL
1933 OR
1934 g_last_used_work_pattern IS NULL
1935 OR
1936 g_last_max_effective_start_dt IS NULL
1937 OR
1938 g_last_min_effective_end_dt IS NULL
1939 OR
1940 g_last_business_group_id <> p_business_group_id -- if the bg has changed reload
1941 OR
1942 ( p_business_group_id = g_last_business_group_id -- OR if the bg is the same but the
1943 AND
1944 (
1945 l_work_pattern_to_use <> g_last_used_work_pattern -- work pattern has changed
1946 OR
1947 NOT p_date_start BETWEEN g_last_max_effective_start_dt -- or new cache may not be effective
1948 AND g_last_min_effective_end_dt --
1949
1950 )
1951 )
1952 THEN
1953
1954 l_proc_step := 35;
1955 IF g_debug THEN
1956 debug(l_proc_name,l_proc_step);
1957 END IF;
1958
1959 -- reload cache
1960 g_last_business_group_id := p_business_group_id;
1961 g_last_max_effective_start_dt := NULL;
1962 g_last_min_effective_end_dt := NULL;
1963
1964 g_last_used_work_pattern := l_work_pattern_to_use;
1965 g_asg_work_pattern_start_day_n:= l_asg_work_pattern_start_day_n;
1966 g_asg_work_pattern_start_date := l_asg_work_pattern_start_date;
1967
1968
1969 -- at this time l_work_pattern_to_use represents the work pattern to be cached
1970
1971 l_legislation_code := get_legislation_code(p_business_group_id);
1972
1973 OPEN csr_get_user_column_id
1974 (p_user_table_name => g_udt_name
1975 ,p_user_column_name => l_work_pattern_to_use
1976 ,p_business_group_id => p_business_group_id
1977 ,p_legislation_code => l_legislation_code
1978 );
1979 FETCH csr_get_user_column_id INTO l_user_column_id;
1980 -- IF not found raise some error -- most probably override is misspelt
1981 CLOSE csr_get_user_column_id;
1982
1983 --g_user_column_id := l_user_column_id;
1984 --g_effective_date_of_wp := p_date_start;
1985
1986 l_proc_step := 40;
1987 IF g_debug THEN
1988 debug(l_proc_name,l_proc_step);
1989 END IF;
1990
1991
1992 i := 0;
1993 FOR this_day IN
1994 csr_work_pattern_hours
1995 (p_user_column_id => l_user_column_id
1996 ,p_business_group_id => p_business_group_id
1997 ,p_legislation_code => l_legislation_code
1998 ,p_effective_date => p_date_start
1999 )
2000 LOOP
2001
2002 i := i + 1;
2003
2004 l_proc_step := 40+i/10000;
2005 IF g_debug THEN
2006 debug_enter(l_proc_name,40+i/10000);
2007 END IF;
2008
2009
2010 l_hours := fnd_number.canonical_to_number(this_day.hours_in_text);
2011
2012 g_last_max_effective_start_dt
2013 := NVL(GREATEST(this_day.effective_start_date,g_last_max_effective_start_dt)
2014 ,this_day.effective_start_date);
2015
2016 g_last_min_effective_end_dt
2017 := NVL(LEAST(this_day.effective_end_date,g_last_min_effective_end_dt)
2018 ,this_day.effective_end_date);
2019
2020 --IF l_hours > 0 THEN --?? upload only working days --should we ??
2021 -- this is done in two steps to keep out of perf issues / being flagged
2022 OPEN csr_work_pattern_days
2023 (p_user_row_id => this_day.user_row_id
2024 ,p_effective_date => p_date_start
2025 );
2026 FETCH csr_work_pattern_days INTO l_day_NN_name;
2027
2028 IF csr_work_pattern_days%FOUND THEN
2029 l_proc_step := 45+i/10000;
2030 IF g_debug THEN
2031 debug_enter(l_proc_name,40+i/10000);
2032 END IF;
2033 -- l_day := 'Day '||lpad(l_curr_day_no,2,0);
2034 j := fnd_number.canonical_to_number(TRIM(SUBSTR(l_day_NN_name,5,2)));
2035 g_work_pattern_cache(j).hours := l_hours;
2036 END IF;
2037 CLOSE csr_work_pattern_days;
2038 --END IF; -- IF l_hours > 0 THEN
2039
2040 END LOOP; -- FOR every day in this work pattern load into cache
2041
2042 l_proc_step := 50;
2043 IF g_debug THEN
2044 debug(l_proc_name,l_proc_step);
2045 END IF;
2046
2047
2048 i := g_work_pattern_cache.FIRST;
2049 WHILE i IS NOT NULL
2050 LOOP
2051
2052 l_proc_step := 55+i/10000;
2053 IF g_debug THEN
2054 debug_enter(l_proc_name,55+i/10000);
2055 END IF;
2056
2057 j := g_work_pattern_cache.NEXT(i);
2058
2059 IF j IS NULL -- i is the last entry
2060 THEN
2061 -- so loop j around to the beginning
2062 j := g_work_pattern_cache.FIRST;
2063 END IF;
2064
2065 l_next_working_day_found := FALSE;
2066
2067 l_proc_step := 60+i/10000;
2068 IF g_debug THEN
2069 debug_enter(l_proc_name,65+i/10000);
2070 END IF;
2071
2072 WHILE j <> i -- if j is NULL and its a one day work pattern (j=i) this loop won't start
2073 LOOP
2074
2075 l_proc_step := 65+(i/10000)+(j/1000000);
2076 IF g_debug THEN
2077 debug_enter(l_proc_name,65+(i/10000)+(j/1000000));
2078 END IF;
2079
2080 g_work_pattern_cache(i).days_to_next_working_day :=
2081 NVL(g_work_pattern_cache(i).days_to_next_working_day,0) + 1;
2082
2083 IF g_work_pattern_cache(j).hours > 0 THEN
2084 l_proc_step := 67+(i/10000)+(j/1000000);
2085 IF g_debug THEN
2086 debug_enter(l_proc_name,67+(i/10000)+(j/1000000));
2087 END IF;
2088 g_work_pattern_cache(i).next_working_day_index := j;
2089 l_next_working_day_found := TRUE;
2090 EXIT; -- a working day has been found
2091 END IF;
2092
2093 j := g_work_pattern_cache.NEXT(j);
2094 IF j IS NULL THEN
2095 l_proc_step := 69+(i/10000)+(j/1000000);
2096 IF g_debug THEN
2097 debug_enter(l_proc_name,69+(i/10000)+(j/1000000));
2098 END IF;
2099 -- prev j was the last so loop around to the beginning
2100 j := g_work_pattern_cache.FIRST;
2101 END IF;
2102
2103 END LOOP; -- inner loop find next working day
2104
2105 l_proc_step := 70+(i/10000);
2106 IF g_debug THEN
2107 debug_enter(l_proc_name,70+(i/10000));
2108 END IF;
2109
2110 IF NOT l_next_working_day_found THEN
2111 -- we have looped around and no other working days were found and are back to the same day
2112 -- or that it was a one day work pattern
2113 -- in either case if this is the only working day so set i itself as its next index
2114 -- and one more to the days to next working day figure
2115 -- if this day itself is not a working day then it means that all days in this
2116 -- work pattern have been setup with 0, so exit loop, don't bother populating other days
2117
2118 l_proc_step := 72+(i/10000);
2119 IF g_debug THEN
2120 debug_enter(l_proc_name,72+(i/10000));
2121 END IF;
2122
2123 IF g_work_pattern_cache(i).hours > 0 THEN
2124 l_proc_step := 75+(i/10000);
2125 IF g_debug THEN
2126 debug_enter(l_proc_name,75+(i/10000));
2127 END IF;
2128 g_work_pattern_cache(i).days_to_next_working_day :=
2129 NVL(g_work_pattern_cache(i).days_to_next_working_day,0) + 1;
2130 g_work_pattern_cache(i).next_working_day_index := i;
2131 ELSE
2132 l_proc_step := 77+(i/10000);
2133 IF g_debug THEN
2134 debug_enter(l_proc_name,77+(i/10000));
2135 END IF;
2136 -- clear the days to next working day because there is no next working day
2137 g_work_pattern_cache(i).days_to_next_working_day := NULL;
2138 EXIT; -- outer loop
2139 END IF;
2140
2141 END IF;
2142
2143 l_proc_step := 80+(i/10000);
2144 IF g_debug THEN
2145 debug_enter(l_proc_name,80+(i/10000));
2146 END IF;
2147
2148 i := g_work_pattern_cache.NEXT(i);
2149
2150 END LOOP; -- loop thru each loaded day in prev step
2151
2152 END IF; --IF g_last_business_group_id <> p_business_group_id -- if the bg has changed reload
2153
2154 l_proc_step := 90;
2155 IF g_debug THEN
2156 debug(l_proc_name,l_proc_step);
2157 END IF;
2158
2159
2160 p_work_pattern_used := l_work_pattern_to_use;
2161 p_asg_work_pattern_start_day_n := l_asg_work_pattern_start_day_n;
2162 p_asg_work_pattern_start_date := l_asg_work_pattern_start_date;
2163
2164 l_proc_step := 95;
2165 IF g_debug THEN
2166 debug(l_proc_name,l_proc_step);
2167 END IF;
2168
2169 l_date_start_day_index :=
2170 get_day_index_for_date
2171 (p_asg_work_pattern_start_date => l_asg_work_pattern_start_date
2172 ,p_asg_work_pattern_start_day_n => l_asg_work_pattern_start_day_n
2173 ,p_total_days_in_work_pattern => g_work_pattern_cache.COUNT
2174 ,p_date_to_index => p_date_start
2175 );
2176
2177 p_date_start_day_index := l_date_start_day_index;
2178
2179 IF g_debug THEN
2180 debug('p_work_pattern_used:'||l_work_pattern_to_use);
2181 debug('p_asg_work_pattern_start_day_n:'||l_asg_work_pattern_start_day_n);
2182 debug('p_asg_work_pattern_start_date:'||
2183 fnd_date.date_to_canonical(l_asg_work_pattern_start_date));
2184 debug('p_date_start_day_index:'||l_date_start_day_index);
2185 debug_exit(l_proc_name);
2186 END IF;
2187
2188 EXCEPTION
2189 WHEN OTHERS THEN
2190 clear_cache;
2191 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2192 debug_others(l_proc_name,l_proc_step);
2193 IF g_debug THEN
2194 debug('Leaving: '||l_proc_name,-999);
2195 END IF;
2196 fnd_message.raise_error;
2197 ELSE
2198 RAISE;
2199 END IF;
2200 END load_work_pattern_into_cache;
2201
2202
2203 FUNCTION add_working_days_using_one_wp
2204 (p_assignment_id IN NUMBER
2205 ,p_business_group_id IN NUMBER
2206 ,p_date_start IN DATE
2207 ,p_working_days_to_add IN NUMBER
2208 ,p_default_wp IN VARCHAR2 DEFAULT NULL
2209 ,p_override_wp IN VARCHAR2 DEFAULT NULL
2210 ) RETURN DATE
2211 IS
2212
2213 l_work_pattern_days t_work_pattern_cache_type;
2214 l_work_pattern_used pay_user_columns.user_column_name%TYPE;
2215 l_asg_work_pattern_start_day_n BINARY_INTEGER;
2216 l_asg_work_pattern_start_date DATE;
2217 l_date_start_day_index BINARY_INTEGER;
2218 i BINARY_INTEGER;
2219 l_days_remaining_to_add NUMBER(20);
2220 l_total_calendar_days NUMBER(20);
2221 l_date_after_n_working_days DATE;
2222
2223 l_proc_step NUMBER(20,10):=0;
2224 l_proc_name VARCHAR2(61):= g_package_name||'add_working_days_using_one_wp';
2225
2226 BEGIN
2227 /*
2228 --3. Deterime the day index for date_start
2229 --4. Decrement the p_days by 1 as we add one day less
2230 --5. Loop thru the cache adding up the index offsets (stored or derived at run time)
2231 --6. With each jump decrement p_days by 1 more
2232 --7. Exit the loop when p_days is 0
2233 --8. Add the sum of index offsets to date_start and return that as the date
2234 --
2235 --9. part p_days is rounded down...ie adding 0.5 returns the same date as adding 1
2236 --10. special check for p_days
2237 */
2238
2239 g_debug := hr_utility.debug_enabled;
2240 IF g_debug THEN
2241 debug_enter(l_proc_name);
2242 END IF;
2243
2244 load_work_pattern_into_cache
2245 (p_assignment_id => p_assignment_id
2246 ,p_business_group_id => p_business_group_id
2247 ,p_date_start => p_date_start
2248 ,p_default_wp => p_default_wp
2249 ,p_override_wp => p_override_wp
2250 ,p_work_pattern_used => l_work_pattern_used
2251 ,p_asg_work_pattern_start_day_n => l_asg_work_pattern_start_day_n
2252 ,p_asg_work_pattern_start_date => l_asg_work_pattern_start_date
2253 ,p_date_start_day_index => l_date_start_day_index
2254 );
2255
2256 l_proc_step := 10;
2257 IF g_debug THEN
2258 debug(l_proc_name,l_proc_step);
2259 END IF;
2260
2261 -- never use g_work_pattern_cache without first calling load_work_pattern_into_cache
2262 l_work_pattern_days := g_work_pattern_cache;
2263 -- always assign cache to locally and then use it
2264
2265 -- now find out the day of the work pattern that date_start corresponds to
2266 -- if this work pattern was the override or the default wp then we simply need to know
2267 -- the day of week and determine the offset assuming Sunday (or a pre-se global) as Day 01
2268 -- if this work pattern was the assignment level work pattern then we need to use the logic in
2269 -- get_day_dets to determine the starting offset
2270
2271 l_proc_step := 20;
2272 IF g_debug THEN
2273 debug(l_proc_name,l_proc_step);
2274 END IF;
2275
2276
2277 l_days_remaining_to_add := CEIL(p_working_days_to_add);
2278 -- adding 0.5 working day is same adding 1 working day
2279 -- adding 1.5 working day is same as adding 2 working days
2280
2281 l_total_calendar_days := 0;
2282 i := l_date_start_day_index;
2283
2284 l_proc_step := 30;
2285 IF g_debug THEN
2286 debug(l_proc_name,l_proc_step);
2287 END IF;
2288
2289 WHILE l_days_remaining_to_add > 0
2290 AND i IS NOT NULL -- for wp with all 0 days this will become NULL
2291 --AND l_total_calendar_days IS NOT NULL -- for wp will all 0 days this will become NULL
2292 LOOP
2293
2294 l_proc_step := 32+i/10000;
2295 IF g_debug THEN
2296 debug(l_proc_name,32+i/10000);
2297 END IF;
2298
2299 IF l_work_pattern_days(i).hours > 0 THEN
2300
2301 l_proc_step := 35+i/10000;
2302 IF g_debug THEN
2303 debug(l_proc_name,35+i/10000);
2304 END IF;
2305
2306 l_days_remaining_to_add := l_days_remaining_to_add - 1;
2307
2308 END IF;
2309
2310 l_total_calendar_days :=
2311 l_total_calendar_days +
2312 l_work_pattern_days(i).days_to_next_working_day;
2313
2314 i := l_work_pattern_days(i).next_working_day_index;
2315
2316 END LOOP; -- loop thru each loaded day in prev step
2317
2318 l_proc_step := 40;
2319 IF g_debug THEN
2320 debug(l_proc_name,40);
2321 END IF;
2322
2323 l_date_after_n_working_days := p_date_start + l_total_calendar_days;
2324
2325 IF g_debug THEN
2326 debug('l_date_after_n_working_days:'||
2327 fnd_date.date_to_canonical(l_date_after_n_working_days));
2328 debug_exit(l_proc_name);
2329 END IF; -- IF g_debug THEN
2330
2331 RETURN l_date_after_n_working_days;
2332
2333 EXCEPTION
2334 WHEN OTHERS THEN
2335 clear_cache;
2336 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2337 debug_others(l_proc_name,l_proc_step);
2338 IF g_debug THEN
2339 debug('Leaving: '||l_proc_name,-999);
2340 END IF;
2341 fnd_message.raise_error;
2342 ELSE
2343 RAISE;
2344 END IF;
2345 END add_working_days_using_one_wp;
2346
2347
2348 END pqp_schedule_calculation_pkg;
[Home] [Help]