1 PACKAGE BODY pqp_schedule_calculation_pkg AS
2 /* $Header: pqschcal.pkb 120.4 2011/12/14 12:04:35 rajganga ship $ */
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 NUMBER DEFAULT 0
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 l_is_assignment_wp BOOLEAN := FALSE ;
1082
1083 BEGIN
1084
1085 if p_is_assignment_wp <> 0 then
1086 l_is_assignment_wp := TRUE;
1087 end if;
1088
1089 l_hours_worked := get_time_worked
1090 (p_assignment_id => p_assignment_id
1091 ,p_business_group_id => p_business_group_id
1092 ,p_date_start => p_date_start
1093 ,p_date_end => p_date_end
1094 ,p_dimension => 'HOURS'
1095 ,p_default_wp => p_default_wp
1096 ,p_override_wp => p_override_wp
1097 ,p_working_dates => l_working_dates -- OUT
1098 ,p_error_code => p_error_code -- OUT
1099 ,p_error_message => p_error_message -- OUT
1100 ,p_is_assignment_wp => l_is_assignment_wp
1101 );
1102 -- Check for errors
1103 IF p_error_code <> 0 THEN
1104 l_hours_worked := 0;
1105 END IF;
1106
1107 RETURN l_hours_worked;
1108
1109 -- Added by tmehra for nocopy changes Feb'03
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 hr_utility.trace('Exception Block : When others');
1114 p_error_code := SQLCODE;
1115 p_error_message := SQLERRM;
1116 raise;
1117
1118 END get_hours_worked;
1119
1120
1121 FUNCTION is_working_day
1122 (p_assignment_id IN NUMBER
1123 ,p_business_group_id IN NUMBER
1124 ,p_date IN DATE
1125 ,p_error_code OUT NOCOPY NUMBER
1126 ,p_error_message OUT NOCOPY VARCHAR2
1127 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1128 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1129 ) RETURN VARCHAR2
1130 IS
1131
1132 l_days_worked NUMBER := 0;
1133 l_is_working_day VARCHAR2(1) := 'N';
1134 l_working_dates t_working_dates;
1135
1136 BEGIN /*is_working_day*/
1137
1138 hr_utility.trace('Entered Is Working Day?'||
1139 fnd_date.date_to_canonical(p_date));
1140 hr_utility.trace('p_assignment_id:'||
1141 fnd_number.number_to_canonical(p_assignment_id));
1142 hr_utility.trace('p_override_wp:'||p_override_wp);
1143
1144 l_days_worked := get_days_worked
1145 (p_assignment_id => p_assignment_id
1146 ,p_business_group_id => p_business_group_id
1147 ,p_date_start => p_date
1148 ,p_date_end => p_date
1149 ,p_default_wp => p_default_wp
1150 ,p_override_wp => p_override_wp
1151 ,p_working_dates => l_working_dates -- OUT
1152 ,p_error_code => p_error_code -- OUT
1153 ,p_error_message => p_error_message -- OUT
1154 );
1155
1156 IF l_days_worked = 1 AND p_error_code = 0 THEN
1157 l_is_working_day := 'Y';
1158 END IF;
1159
1160 hr_utility.trace('p_assignment_id:'||
1161 fnd_number.number_to_canonical(p_assignment_id));
1162 hr_utility.trace('Leaving Is Working Day?'||
1163 fnd_date.date_to_canonical(p_date));
1164
1165
1166
1167 RETURN l_is_working_day;
1168
1169 -- Added by tmehra for nocopy changes Feb'03
1170
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 hr_utility.trace('Exception Block : When others');
1174 p_error_code := SQLCODE;
1175 p_error_message := SQLERRM;
1176 raise;
1177
1178
1179 END is_working_day; /*is_working_day*/
1180 --
1181 --
1182 --
1183 PROCEDURE add_working_days_wp
1184 (p_wp_dets IN c_wp_dets%ROWTYPE
1185 ,p_curr_date IN OUT NOCOPY DATE
1186 ,p_balance_days IN OUT NOCOPY NUMBER
1187 )
1188 IS
1189
1190 l_calc_stdt DATE;
1191 l_calc_endt DATE;
1192 l_day_no NUMBER;
1193 l_days_in_wp NUMBER;
1194 l_curr_day_no NUMBER;
1195 l_day VARCHAR2(30);
1196 l_hours NUMBER := 0;
1197 l_continue VARCHAR2(1) := 'Y';
1198
1199 l_curr_date_nc DATE;
1200 l_balance_days_nc NUMBER;
1201
1202
1203 BEGIN -- add_working_days_wp
1204
1205 hr_utility.set_location('Entered get_next_working_date_WP', 10);
1206
1207 -- nocopy changes tmehra
1208 l_curr_date_nc := p_curr_date;
1209 l_balance_days_nc := p_balance_days;
1210
1211 /* Determine Calculation Start Date for this Work Pattern */
1212 if p_curr_date > p_wp_dets.effective_start_date
1213 then
1214 l_calc_stdt := p_curr_date;
1215 else
1216 l_calc_stdt := p_wp_dets.effective_start_date;
1217 end if;
1218
1219 /* Set Calculation End Date for this Work Pattern */
1220 l_calc_endt := p_wp_dets.effective_end_date;
1221
1222 /* Get day number on calculation start date and number of days in Work Pattern
1223 */
1224 get_day_dets
1225 (p_wp_dets => p_wp_dets
1226 ,p_calc_stdt => l_calc_stdt
1227 ,p_calc_edt => l_calc_endt
1228 ,p_day_no => l_day_no /* OUT NOCOPY */
1229 ,p_days_in_wp => l_days_in_wp /* OUT NOCOPY */
1230 );
1231
1232 l_curr_day_no := l_day_no;
1233
1234 hr_utility.set_location('l_curr_day_no :'||l_curr_day_no, 20);
1235 hr_utility.set_location('p_curr_date :'||p_curr_date, 30);
1236 hr_utility.set_location('Work Pattern :'||p_wp_dets.work_pattern, 40);
1237
1238 -- Loop throug the dates starting from p_curr_date
1239 -- PS : we don't know till when to loop, so p_balance_days
1240 -- will be used as a balance counter
1241 loop -- Through dates starting with p_curr_date
1242
1243 l_day := 'Day '||lpad(l_curr_day_no,2,0);
1244
1245 hr_utility.set_location('Processing date :'||to_char(p_curr_date,
1246 'DD/MM/YYYY'), 60);
1247
1248 begin
1249 l_hours := hruserdt.get_table_value
1250 (p_bus_group_id => p_wp_dets.business_group_id
1251 ,p_table_name => g_udt_name
1252 ,p_col_name => p_wp_dets.work_pattern
1253 ,p_row_value => l_day
1254 ,p_effective_date => p_curr_date
1255 );
1256 exception
1257 when no_data_found then
1258 /*
1259 * No data was entered. Do not add to total
1260 * or count the day in the loop.
1261 */
1262 l_hours := 0;
1263 end;
1264
1265 hr_utility.set_location('Hours on '||l_day||' = '||l_hours, 70);
1266
1267 -- Decrement working days balance if l_curr_day_no
1268 -- is a working day
1269 if l_hours > 0 then
1270
1271 p_balance_days := p_balance_days - 1;
1272
1273 end if;
1274
1275 -- If we have counted down all the working days then exit
1276 if p_balance_days = 0 then
1277 l_continue := 'N';
1278 exit;
1279 end if;
1280
1281 /* Calculate next day no */
1282 if l_curr_day_no = l_days_in_wp then
1283 l_curr_day_no := 1;
1284 else
1285 l_curr_day_no := l_curr_day_no + 1;
1286 end if;
1287
1288 -- Increment to the next date
1289 p_curr_date := p_curr_date + 1;
1290
1291 -- The WP has changed, exit, but continue process using the next
1292 -- effective work pattern row
1293 if p_curr_date > p_wp_dets.effective_end_date then
1294 l_continue := 'Y';
1295 exit;
1296 end if;
1297
1298 end loop; -- Through dates starting with p_curr_date
1299
1300 hr_utility.set_location('Leaving get_next_working_date_wp', 100);
1301 RETURN;
1302
1303 -- Added by tmehra for nocopy changes Feb'03
1304
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 hr_utility.set_location('Exception Block : When others',110);
1308 p_curr_date := l_curr_date_nc;
1309 p_balance_days := l_balance_days_nc;
1310 raise;
1311
1312
1313 END add_working_days_wp;
1314
1315 FUNCTION add_working_days
1316 (p_assignment_id IN NUMBER
1317 ,p_business_group_id IN NUMBER
1318 ,p_date_start IN DATE
1319 ,p_days IN NUMBER
1320 ,p_error_code OUT NOCOPY NUMBER
1321 ,p_error_message OUT NOCOPY VARCHAR2
1322 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1323 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1324 ) RETURN DATE
1325 IS
1326
1327 l_balance_days NUMBER;
1328 l_curr_date DATE;
1329 l_calc_stdt DATE;
1330 l_calc_endt DATE;
1331 l_day_no NUMBER;
1332 l_days_in_wp NUMBER;
1333 l_curr_day_no NUMBER;
1334 l_day VARCHAR2(30);
1335 l_hours NUMBER := 0;
1336 l_continue VARCHAR2(1) := 'Y';
1337 l_asg_wp_found BOOLEAN := FALSE;
1338 l_error_code NUMBER := 0;
1339 l_err_msg_name fnd_new_messages.message_name%TYPE;
1340
1341 r_wp_dets c_wp_dets%ROWTYPE;
1342 r_def_wp_dets c_wp_dets%ROWTYPE;
1343
1344 BEGIN /*add_working_days*/
1345
1346 hr_utility.set_location('Entered add_working_days', 10);
1347 hr_utility.set_location('UDT Name :'||g_udt_name, 20);
1348
1349 -- Add 1 to working days and assign to balance days
1350 -- We need to this as we want to return the date
1351 -- prior to the NEXT working day after p_days working days
1352 -- have been added to start date
1353 l_balance_days := floor(p_days) + 1;
1354
1355 l_curr_date := p_date_start;
1356
1357 for r_wp_dets in c_wp_dets_up(p_assignment_id, p_date_start)
1358 loop /* Get Work Pattern Details */
1359
1360 -- Only if this aat record contains a work pattern
1361 if r_wp_dets.work_pattern is not null then
1362
1363 hr_utility.set_location('Asg WP Found', 30);
1364 l_asg_wp_found := TRUE;
1365
1366 if l_curr_date between r_wp_dets.effective_start_date
1367 and r_wp_dets.effective_end_date then
1368
1369 add_working_days_wp
1370 (p_wp_dets => r_wp_dets
1371 ,p_curr_date => l_curr_date -- IN OUT
1372 ,p_balance_days => l_balance_days -- IN OUT
1373 );
1374 --
1375 elsif p_default_wp IS NOT NULL then
1376 -- Use the default work pattern for the period where there is no
1377 -- work pattern on assignment and then use the asg work pattern
1378
1379 hr_utility.set_location('Using default and Asg WP', 50);
1380
1381 -- Step 1) Add days for the default work pattern
1382 r_def_wp_dets := NULL;
1383 r_def_wp_dets.effective_start_date := l_curr_date;
1384 -- set effective end date as the day before the Asg WP becomes effective
1385 r_def_wp_dets.effective_end_date := (r_wp_dets.effective_start_date -
1386 1);
1387 r_def_wp_dets.business_group_id := p_business_group_id;
1388 r_def_wp_dets.work_pattern := p_default_wp;
1389 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
1390 (NEXT_DAY(l_curr_date, g_default_start_day) -
1391 l_curr_date)),2,'0');
1392
1393 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 50);
1394
1395 add_working_days_wp
1396 (p_wp_dets => r_def_wp_dets
1397 ,p_curr_date => l_curr_date -- IN OUT
1398 ,p_balance_days => l_balance_days -- IN OUT
1399 );
1400
1401 -- Step 2) Add days for the assignment work pattern
1402 -- But, only if there are more days to be added
1403 if l_balance_days > 0 then
1404 add_working_days_wp
1405 (p_wp_dets => r_wp_dets
1406 ,p_curr_date => l_curr_date -- IN OUT
1407 ,p_balance_days => l_balance_days -- IN OUT
1408 );
1409 end if;
1410 --
1411 else -- No default work pattern found, raise error and exit the loop.
1412 l_error_code := -1;
1413 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
1414 exit;
1415 end if; -- l_calc_stdt between r_wp_dets.effective_start_date
1416 --
1417 end if; -- if r_wp_dets.work_pattern is not null then
1418 -- Exit the loop if there are no more days to add
1419 if l_balance_days = 0 then
1420 exit;
1421 end if;
1422 --
1423 end loop; /* Get Work Pattern Details */
1424
1425 if l_error_code = 0 -- No errors have occured
1426 AND
1427 ( -- No WP found on AAT
1428 NOT l_asg_wp_found
1429 OR
1430 -- not enough WP history on AAT so more days still to be added
1431 l_balance_days > 0
1432 ) then
1433
1434 if p_default_wp IS NOT NULL then
1435
1436 hr_utility.set_location('Asg WP NOT Found, default WP available', 40);
1437
1438 r_def_wp_dets := NULL;
1439 r_def_wp_dets.effective_start_date := l_curr_date;
1440 r_def_wp_dets.effective_end_date := hr_api.g_eot; -- End of Time
1441 r_def_wp_dets.business_group_id := p_business_group_id;
1442 r_def_wp_dets.work_pattern := p_default_wp;
1443 r_def_wp_dets.start_day := 'Day '||LPAD(TO_CHAR(8 -
1444 (NEXT_DAY(l_curr_date, g_default_start_day) -
1445 l_curr_date)),2,'0');
1446
1447 hr_utility.set_location('Start Day :'||r_def_wp_dets.start_day, 50);
1448
1449 add_working_days_wp
1450 (p_wp_dets => r_def_wp_dets
1451 ,p_curr_date => l_curr_date -- IN OUT
1452 ,p_balance_days => l_balance_days -- IN OUT
1453 );
1454 else
1455 l_error_code := -1;
1456 l_err_msg_name := 'PQP_230589_NO_WORK_PATTERN';
1457 end if;
1458 --
1459 end if; -- if NOT l_asg_wp_found the
1460
1461 -- Check for errors
1462 if l_error_code = 0 then
1463
1464 -- Check if balance has been zeroed, if not, error.
1465 if l_balance_days > 0 then
1466 l_error_code := -2;
1467 l_err_msg_name := 'PQP_230590_WP_HIST_INCOMPLETE';
1468 else -- No errors
1469 l_curr_date := l_curr_date - 1; -- previous day to next working day
1470 end if;
1471 --
1472 end if; -- l_error_code = 0 then
1473
1474 p_error_code := l_error_code;
1475 --
1476 if l_err_msg_name IS NOT NULL then
1477 p_error_message := substr(fnd_message.get_string('PQP',l_err_msg_name)
1478 ,255 -- Bugfix 3405270
1479 );
1480 end if;
1481
1482 return l_curr_date;
1483
1484 -- Added by tmehra for nocopy changes Feb'03
1485
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 hr_utility.trace('Exception Block : When others');
1489 p_error_code := SQLCODE;
1490 p_error_message := SQLERRM;
1491 raise;
1492
1493
1494 END add_working_days;
1495
1496 FUNCTION calculate_days_worked
1497 (p_assignment_id IN NUMBER
1498 ,p_date_start IN DATE
1499 ,p_date_end IN DATE
1500 ) RETURN NUMBER
1501 IS
1502
1503 l_hours_worked NUMBER;
1504
1505 BEGIN /*calculate_days_worked*/
1506
1507 -- Reset g_days_worked
1508 g_days_worked := 0;
1509
1510 -- Call the time(hours) worked function.
1511 -- This function calculatea the days worked and stores it in g_days_worked
1512 l_hours_worked := calculate_time_worked
1513 (p_assignment_id => p_assignment_id
1514 ,p_date_start => p_date_start
1515 ,p_date_end => p_date_end
1516 );
1517
1518 RETURN g_days_worked;
1519
1520 END calculate_days_worked;
1521
1522 -- Returns the number of Working Days in a Workpattern
1523 -- as on the effective date
1524 -- it takes 2 optional parameters p_override_wp, p_default_wp
1525 -- Order of precedence is Override->Assignment->Default
1526 FUNCTION get_working_days_in_week (
1527 p_assignment_id IN NUMBER
1528 ,p_business_group_id IN NUMBER
1529 ,p_effective_date IN DATE
1530 ,p_default_wp IN VARCHAR2
1531 ,p_override_wp IN VARCHAR2
1532 ) RETURN NUMBER
1533 IS
1534 l_retval number ;
1535 l_value pay_user_column_instances_f.value%TYPE ;
1536 l_error_message fnd_new_messages.message_text%TYPE ;
1537 l_proc_name VARCHAR2(61) := g_package_name||'get_working_days_in_week';
1538 l_proc_step NUMBER(20,10) ;
1539 l_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
1540 l_errbuff VARCHAR2(200);
1541 l_retcode NUMBER;
1542
1543 --l_count number ;
1544
1545 CURSOR csr_get_wp IS
1546 select work_pattern
1547 from pqp_assignment_attributes_f paa
1548 where assignment_id = p_assignment_id
1549 and business_group_id = p_business_group_id
1550 and p_effective_date between paa.effective_start_date
1551 and paa.effective_end_date ;
1552 begin
1553 IF g_debug THEN
1554 pqp_utilities.debug_enter(l_proc_name);
1555 pqp_utilities.debug('p_assignment_id:'||p_assignment_id);
1556 pqp_utilities.debug('p_business_group_id:'||p_business_group_id);
1557 pqp_utilities.debug('p_effective_date:'||p_effective_date);
1558 END IF;
1559
1560 IF p_override_wp IS NOT NULL THEN
1561 l_proc_step := 10 ;
1562 IF g_debug THEN
1563 pqp_utilities.debug(' Override WP:'||p_override_wp);
1564 END IF;
1565 l_work_pattern := p_override_wp ;
1566 ELSE
1567 l_proc_step := 20 ;
1568
1569 OPEN csr_get_wp ;
1570 FETCH csr_get_wp INTO l_work_pattern ;
1571 CLOSE csr_get_wp ;
1572
1573 END IF;
1574
1575 l_work_pattern := NVL(l_work_pattern,p_default_wp);
1576
1577 l_proc_step := 30 ;
1578 IF g_debug THEN
1579 pqp_utilities.debug('Work Pattern:'||l_work_pattern);
1580 END IF ;
1581
1582 IF l_work_pattern IS NOT NULL THEN
1583
1584 -- FOR i in 1..7 loop
1585 l_retval := pqp_utilities.pqp_gb_get_table_value(
1586 p_business_group_id => p_business_group_id
1587 ,p_effective_date => p_effective_date
1588 ,p_table_name => 'PQP_COMPANY_WORK_PATTERNS'
1589 ,p_column_name => l_work_pattern
1590 ,p_row_name => 'Average Working Days Per Week'
1591 --'Day 0'||i
1592 ,p_value => l_value
1593 ,p_error_msg => l_error_message
1594 ) ;
1595
1596
1597 -- if l_value > 0 then
1598 -- l_count := nvl(l_count,0) + 1 ;
1599 -- end if ;
1600 -- end loop ;
1601 -- If the value for Average working days per week does not exist
1602 -- calculate the same and update the udt and refetch the value.
1603 IF l_value IS NULL THEN
1604
1605 l_proc_step := 35 ;
1606 IF g_debug THEN
1607 pqp_utilities.debug(l_proc_step);
1608 pqp_utilities.debug('p_assignment_id:'||p_assignment_id);
1609 END IF;
1610
1611 pqp_update_work_pattern_table.update_working_days_in_week
1612 (errbuf => l_errbuff
1613 ,retcode => l_retcode
1614 ,p_column_name => l_work_pattern
1615 ,p_business_group_id => p_business_group_id
1616 ,p_overwrite_if_exists => 'Y'
1617 );
1618
1619 l_proc_step := 40 ;
1620 IF g_debug THEN
1621 pqp_utilities.debug(l_proc_step);
1622 pqp_utilities.debug('errbuf:'||l_errbuff);
1623 pqp_utilities.debug('retcode:',l_retcode);
1624 END IF;
1625
1626 l_retval :=
1627 pqp_utilities.pqp_gb_get_table_value
1628 (p_business_group_id => p_business_group_id
1629 ,p_effective_date => p_effective_date
1630 ,p_table_name => 'PQP_COMPANY_WORK_PATTERNS'
1631 ,p_column_name => l_work_pattern
1632 ,p_row_name => 'Average Working Days Per Week'
1633 ,p_value => l_value
1634 ,p_error_msg => l_error_message
1635 ,p_refresh_cache =>'Y'
1636 ) ;
1637
1638 l_proc_step := 45 ;
1639 IF g_debug THEN
1640 pqp_utilities.debug(l_proc_step);
1641 pqp_utilities.debug('l_error_message:'||l_error_message);
1642 pqp_utilities.debug('l_retval:',l_retval);
1643 pqp_utilities.debug('l_value:',l_value);
1644 END IF;
1645
1646 IF l_value IS NULL THEN
1647 fnd_message.set_name( 'PQP', 'PQP_230138_INV_WORK_PATTERN' );
1648 fnd_message.set_token( 'WORKPATTERN ',l_work_pattern);
1649 fnd_message.raise_error ;
1650 END IF;
1651
1652
1653 END IF ;
1654
1655 END IF; -- l_work_pattern IS NOT NULL THEN
1656
1657
1658 IF g_debug THEN
1659 pqp_utilities.debug_exit(l_proc_name) ;
1660 END IF ;
1661
1662 RETURN l_value ; -- l_count ;
1663
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1667 pqp_utilities.debug_others
1668 (l_proc_name
1669 ,l_proc_step
1670 );
1671 IF g_debug THEN
1672 pqp_utilities.debug('Leaving: '||l_proc_name,-999);
1673 END IF;
1674 fnd_message.raise_error;
1675 ELSE
1676 RAISE;
1677 END IF;
1678
1679 END get_working_days_in_week ;
1680 --
1681 --
1682 --
1683 FUNCTION get_day_index_for_date
1684 (p_asg_work_pattern_start_date IN DATE
1685 ,p_asg_work_pattern_start_day_n IN NUMBER
1686 ,p_total_days_in_work_pattern IN NUMBER
1687 ,p_date_to_index IN DATE
1688 ) RETURN NUMBER
1689 IS
1690
1691 l_date_index BINARY_INTEGER;
1692 l_days_to_first_day_of_Day01 BINARY_INTEGER;
1693 l_first_date_of_asg_on_Day01 DATE;
1694 l_days_between_start_and_first NUMBER;
1695
1696 l_proc_step NUMBER(20,10):=0;
1697 l_proc_name VARCHAR2(61):= g_package_name||'get_day_index_for_date';
1698
1699 BEGIN
1700
1701 g_debug := hr_utility.debug_enabled;
1702 IF g_debug THEN
1703 debug_enter(l_proc_name);
1704 END IF;
1705
1706 IF p_asg_work_pattern_start_date IS NULL
1707 THEN
1708 -- then it was either the default or override
1709 -- so use the 7 day week logic with wp starting on Sunday(or preset global day of week) on Day 1
1710
1711 l_proc_step := 10;
1712 IF g_debug THEN
1713 debug(l_proc_name,l_proc_step);
1714 END IF;
1715
1716 l_date_index := NEXT_DAY(p_date_to_index, g_default_start_day) - p_date_to_index;
1717
1718 ELSE
1719 -- it is assignment level work patter, duplicate get_day_dets logic
1720 -- save on the perf issue in this function
1721
1722 l_proc_step := 20;
1723 IF g_debug THEN
1724 debug(l_proc_name,l_proc_step);
1725 END IF;
1726
1727 l_days_to_first_day_of_Day01 := p_total_days_in_work_pattern - p_asg_work_pattern_start_day_n + 1;
1728 l_first_date_of_asg_on_Day01 := p_asg_work_pattern_start_date + l_days_to_first_day_of_Day01;
1729 l_days_between_start_and_first := p_date_to_index - l_first_date_of_asg_on_Day01;
1730
1731 IF l_days_between_start_and_first < 0 THEN
1732 l_proc_step := 22;
1733 IF g_debug THEN
1734 debug(l_proc_name,l_proc_step);
1735 END IF;
1736 l_date_index := p_total_days_in_work_pattern - ABS(l_days_between_start_and_first) + 1;
1737 ELSE
1738 l_proc_step := 25;
1739 IF g_debug THEN
1740 debug(l_proc_name,l_proc_step);
1741 END IF;
1742 l_date_index := MOD(ABS(l_days_between_start_and_first),p_total_days_in_work_pattern) + 1;
1743 END IF;
1744
1745 END IF; -- IF p_asg_work_pattern_start_date IS NULL
1746
1747 IF g_debug THEN
1748 debug('l_date_index:'||l_date_index);
1749 debug_exit(l_proc_name);
1750 END IF;
1751
1752 RETURN l_date_index;
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 clear_cache;
1757 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1758 debug_others(l_proc_name,l_proc_step);
1759 IF g_debug THEN
1760 debug('Leaving: '||l_proc_name,-999);
1761 END IF;
1762 fnd_message.raise_error;
1763 ELSE
1764 RAISE;
1765 END IF;
1766 END get_day_index_for_date;
1767 --
1768 --
1769 --
1770 PROCEDURE load_work_pattern_into_cache
1771 (p_assignment_id IN NUMBER
1772 ,p_business_group_id IN NUMBER
1773 ,p_date_start IN DATE
1774 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1775 ,p_override_wp IN VARCHAR2 DEFAULT NULL
1776 ,p_work_pattern_used OUT NOCOPY VARCHAR2
1777 ,p_asg_work_pattern_start_day_n OUT NOCOPY BINARY_INTEGER
1778 ,p_asg_work_pattern_start_date OUT NOCOPY DATE
1779 ,p_date_start_day_index OUT NOCOPY BINARY_INTEGER
1780 )
1781 IS
1782
1783 l_work_pattern_to_use pay_user_columns.user_column_name%TYPE;
1784 l_user_column_id pay_user_columns.user_column_id%TYPE;
1785 l_pqp_assignment_attributes c_wp_dets_up%ROWTYPE;
1786 l_day_NN_name pay_user_rows_f.row_low_range_or_name%TYPE;
1787 l_asg_work_pattern_start_day_n BINARY_INTEGER;
1788 i BINARY_INTEGER;
1789 j BINARY_INTEGER;
1790 l_asg_work_pattern_start_date DATE;
1791 l_date_start_day_index BINARY_INTEGER;
1792 l_legislation_code pay_user_rows_f.legislation_code%TYPE;
1793 l_next_working_day_found BOOLEAN;
1794 l_hours NUMBER;
1795
1796 l_proc_step NUMBER(20,10):=0;
1797 l_proc_name VARCHAR2(61):= g_package_name||'load_work_pattern_into_cache';
1798
1799
1800 CURSOR csr_get_user_column_id
1801 (p_user_table_name VARCHAR2
1802 ,p_user_column_name VARCHAR2
1803 ,p_business_group_id NUMBER
1804 ,p_legislation_code VARCHAR2
1805 ) IS
1806 SELECT ucs.user_column_id
1807 FROM pay_user_tables uts
1808 ,pay_user_columns ucs
1809 WHERE uts.user_table_name = p_user_table_name -- PQP_COMPANY_WORK_PATTERNS
1810 AND uts.business_group_id IS NULL
1811 AND uts.legislation_code = p_legislation_code -- as one table is seeded per legislation
1812 AND ucs.user_table_id = uts.user_table_id -- only work patterns that belong to the above table
1813 AND ucs.user_column_name = p_user_column_name -- which match this name work_pattern_name
1814 AND ( ucs.business_group_id = p_business_group_id -- in the users bg
1815 OR
1816 (ucs.business_group_id IS NULL -- or seeded
1817 AND
1818 ucs.legislation_code = p_legislation_code -- for the users legislation code
1819 )
1820 );
1821
1822
1823 --local cursor to pull work pattern this will looped and cached into t_work_pattern_cache_type
1824 --
1825
1826 CURSOR csr_work_pattern_hours
1827 (p_user_column_id pay_user_columns.user_column_id%TYPE
1828 ,p_business_group_id NUMBER
1829 ,p_legislation_code VARCHAR2
1830 ,p_effective_date DATE
1831 ) IS
1832 SELECT uci.user_row_id
1833 ,uci.value hours_in_text
1834 ,uci.effective_start_date
1835 ,uci.effective_end_date
1836 FROM pay_user_column_instances_f uci
1837 WHERE uci.user_column_id = p_user_column_id -- represents the work pattern
1838 AND p_effective_date
1839 BETWEEN uci.effective_start_date
1840 AND uci.effective_end_date
1841 AND ( uci.business_group_id = p_business_group_id
1842 OR
1843 ( uci.business_group_id IS NULL
1844 AND
1845 uci.legislation_code = p_legislation_code
1846 )
1847 );
1848
1849 CURSOR csr_work_pattern_days
1850 (p_user_row_id pay_user_rows_f.user_row_id%TYPE
1851 ,p_effective_date DATE
1852
1853 ) IS
1854 SELECT urw.row_low_range_or_name day_name
1855 FROM pay_user_rows_f urw
1856 WHERE urw.user_row_id = p_user_row_id
1857 AND p_effective_date
1858 BETWEEN urw.effective_start_date
1859 AND urw.effective_end_date
1860 AND urw.row_low_range_or_name like
1861 'Day __';
1862
1863 --9059381
1864 l_empty_work_patterns_cache t_work_pattern_cache_type;
1865 --9059381
1866
1867 BEGIN
1868 --/*
1869 --1. Determine the required working pattern, ie assignment, default or override
1870 --2. Cache if not allready (and if not effective as if date_start)
1871 --*/
1872
1873 --1. Determine the required working pattern, ie assignment, default or override
1874
1875 g_debug := hr_utility.debug_enabled;
1876 IF g_debug THEN
1877 debug_enter(l_proc_name);
1878 END IF;
1879
1880
1881 IF p_override_wp IS NOT NULL
1882 THEN
1883
1884 l_proc_step := 10;
1885 IF g_debug THEN
1886 debug(l_proc_name,l_proc_step);
1887 END IF;
1888
1889 l_work_pattern_to_use := p_override_wp;
1890
1891 ELSE
1892 --1. Is there an assignment level work pattern effective as of date start
1893 --2. If use the default work pattern
1894 -- ideally from a CS perspective this shouldn't happen
1895 -- but the function is generic so we code for default also.
1896
1897 l_proc_step := 20;
1898 IF g_debug THEN
1899 debug(l_proc_name,l_proc_step);
1900 END IF;
1901
1902 OPEN c_wp_dets_up(p_assignment_id, p_date_start);
1903 FETCH c_wp_dets_up INTO l_pqp_assignment_attributes;
1904 IF c_wp_dets_up%FOUND
1905 AND
1906 l_pqp_assignment_attributes.work_pattern IS NOT NULL
1907 THEN
1908 l_proc_step := 22;
1909 IF g_debug THEN
1910 debug(l_proc_name,l_proc_step);
1911 END IF;
1912 l_work_pattern_to_use := l_pqp_assignment_attributes.work_pattern;
1913 l_asg_work_pattern_start_day_n
1914 := fnd_number.canonical_to_number(TRIM(SUBSTR(l_pqp_assignment_attributes.start_day,5,2)));
1915 l_asg_work_pattern_start_date := l_pqp_assignment_attributes.effective_start_date;
1916 ELSE
1917 l_proc_step := 25;
1918 IF g_debug THEN
1919 debug(l_proc_name,l_proc_step);
1920 END IF;
1921 l_work_pattern_to_use := NVL(p_default_wp,'PQP_MON_FRI_8_HOURS');
1922 END IF;
1923 CLOSE c_wp_dets_up;
1924
1925 END IF; -- IF p_override_wp IS NOT NULL
1926
1927 IF g_debug THEN
1928 debug('Cache Reload Check');
1929 debug('g_last_business_group_id:'||g_last_business_group_id);
1930 debug('g_last_used_work_pattern:'||g_last_used_work_pattern);
1931 debug('l_work_pattern_to_use:'||l_work_pattern_to_use);
1932 debug('p_date_start:'||fnd_date.date_to_canonical(p_date_start));
1933 debug('g_last_max_effective_start_dt:'||
1934 fnd_date.date_to_canonical(g_last_max_effective_start_dt));
1935 debug('g_last_min_effective_end_dt:'||
1936 fnd_date.date_to_canonical(g_last_min_effective_end_dt));
1937 END IF;
1938
1939 --2. Cache if not allready (and if not effective as if date_start)
1940
1941 IF g_last_business_group_id IS NULL
1942 OR
1943 g_last_used_work_pattern IS NULL
1944 OR
1945 g_last_max_effective_start_dt IS NULL
1946 OR
1947 g_last_min_effective_end_dt IS NULL
1948 OR
1949 g_last_business_group_id <> p_business_group_id -- if the bg has changed reload
1950 OR
1951 ( p_business_group_id = g_last_business_group_id -- OR if the bg is the same but the
1952 AND
1953 (
1954 l_work_pattern_to_use <> g_last_used_work_pattern -- work pattern has changed
1955 OR
1956 NOT p_date_start BETWEEN g_last_max_effective_start_dt -- or new cache may not be effective
1957 AND g_last_min_effective_end_dt --
1958
1959 )
1960 )
1961 THEN
1962
1963 l_proc_step := 35;
1964 IF g_debug THEN
1965 debug(l_proc_name,l_proc_step);
1966 END IF;
1967
1968 -- reload cache
1969 g_last_business_group_id := p_business_group_id;
1970 g_last_max_effective_start_dt := NULL;
1971 g_last_min_effective_end_dt := NULL;
1972
1973 g_last_used_work_pattern := l_work_pattern_to_use;
1974 g_asg_work_pattern_start_day_n:= l_asg_work_pattern_start_day_n;
1975 g_asg_work_pattern_start_date := l_asg_work_pattern_start_date;
1976 --9059381
1977 g_work_pattern_cache := l_empty_work_patterns_cache;
1978 --9059381
1979
1980
1981 -- at this time l_work_pattern_to_use represents the work pattern to be cached
1982
1983 l_legislation_code := get_legislation_code(p_business_group_id);
1984
1985 OPEN csr_get_user_column_id
1986 (p_user_table_name => g_udt_name
1987 ,p_user_column_name => l_work_pattern_to_use
1988 ,p_business_group_id => p_business_group_id
1989 ,p_legislation_code => l_legislation_code
1990 );
1991 FETCH csr_get_user_column_id INTO l_user_column_id;
1992 -- IF not found raise some error -- most probably override is misspelt
1993 CLOSE csr_get_user_column_id;
1994
1995 --g_user_column_id := l_user_column_id;
1996 --g_effective_date_of_wp := p_date_start;
1997
1998 l_proc_step := 40;
1999 IF g_debug THEN
2000 debug(l_proc_name,l_proc_step);
2001 END IF;
2002
2003
2004 i := 0;
2005 FOR this_day IN
2006 csr_work_pattern_hours
2007 (p_user_column_id => l_user_column_id
2008 ,p_business_group_id => p_business_group_id
2009 ,p_legislation_code => l_legislation_code
2010 ,p_effective_date => p_date_start
2011 )
2012 LOOP
2013
2014 i := i + 1;
2015
2016 l_proc_step := 40+i/10000;
2017 IF g_debug THEN
2018 debug_enter(l_proc_name,40+i/10000);
2019 END IF;
2020
2021
2022 l_hours := fnd_number.canonical_to_number(this_day.hours_in_text);
2023
2024 g_last_max_effective_start_dt
2025 := NVL(GREATEST(this_day.effective_start_date,g_last_max_effective_start_dt)
2026 ,this_day.effective_start_date);
2027
2028 g_last_min_effective_end_dt
2029 := NVL(LEAST(this_day.effective_end_date,g_last_min_effective_end_dt)
2030 ,this_day.effective_end_date);
2031
2032 --IF l_hours > 0 THEN --?? upload only working days --should we ??
2033 -- this is done in two steps to keep out of perf issues / being flagged
2034 OPEN csr_work_pattern_days
2035 (p_user_row_id => this_day.user_row_id
2036 ,p_effective_date => p_date_start
2037 );
2038 FETCH csr_work_pattern_days INTO l_day_NN_name;
2039
2040 IF csr_work_pattern_days%FOUND THEN
2041 l_proc_step := 45+i/10000;
2042 IF g_debug THEN
2043 debug_enter(l_proc_name,40+i/10000);
2044 END IF;
2045 -- l_day := 'Day '||lpad(l_curr_day_no,2,0);
2046 j := fnd_number.canonical_to_number(TRIM(SUBSTR(l_day_NN_name,5,2)));
2047 g_work_pattern_cache(j).hours := l_hours;
2048 END IF;
2049 CLOSE csr_work_pattern_days;
2050 --END IF; -- IF l_hours > 0 THEN
2051
2052 END LOOP; -- FOR every day in this work pattern load into cache
2053
2054 l_proc_step := 50;
2055 IF g_debug THEN
2056 debug(l_proc_name,l_proc_step);
2057 END IF;
2058
2059
2060 i := g_work_pattern_cache.FIRST;
2061 WHILE i IS NOT NULL
2062 LOOP
2063
2064 l_proc_step := 55+i/10000;
2065 IF g_debug THEN
2066 debug_enter(l_proc_name,55+i/10000);
2067 END IF;
2068
2069 j := g_work_pattern_cache.NEXT(i);
2070
2071 IF j IS NULL -- i is the last entry
2072 THEN
2073 -- so loop j around to the beginning
2074 j := g_work_pattern_cache.FIRST;
2075 END IF;
2076
2077 l_next_working_day_found := FALSE;
2078
2079 l_proc_step := 60+i/10000;
2080 IF g_debug THEN
2081 debug_enter(l_proc_name,65+i/10000);
2082 END IF;
2083
2084 WHILE j <> i -- if j is NULL and its a one day work pattern (j=i) this loop won't start
2085 LOOP
2086
2087 l_proc_step := 65+(i/10000)+(j/1000000);
2088 IF g_debug THEN
2089 debug_enter(l_proc_name,65+(i/10000)+(j/1000000));
2090 END IF;
2091
2092 g_work_pattern_cache(i).days_to_next_working_day :=
2093 NVL(g_work_pattern_cache(i).days_to_next_working_day,0) + 1;
2094
2095 IF g_work_pattern_cache(j).hours > 0 THEN
2096 l_proc_step := 67+(i/10000)+(j/1000000);
2097 IF g_debug THEN
2098 debug_enter(l_proc_name,67+(i/10000)+(j/1000000));
2099 END IF;
2100 g_work_pattern_cache(i).next_working_day_index := j;
2101 l_next_working_day_found := TRUE;
2102 EXIT; -- a working day has been found
2103 END IF;
2104
2105 j := g_work_pattern_cache.NEXT(j);
2106 IF j IS NULL THEN
2107 l_proc_step := 69+(i/10000)+(j/1000000);
2108 IF g_debug THEN
2109 debug_enter(l_proc_name,69+(i/10000)+(j/1000000));
2110 END IF;
2111 -- prev j was the last so loop around to the beginning
2112 j := g_work_pattern_cache.FIRST;
2113 END IF;
2114
2115 END LOOP; -- inner loop find next working day
2116
2117 l_proc_step := 70+(i/10000);
2118 IF g_debug THEN
2119 debug_enter(l_proc_name,70+(i/10000));
2120 END IF;
2121
2122 IF NOT l_next_working_day_found THEN
2123 -- we have looped around and no other working days were found and are back to the same day
2124 -- or that it was a one day work pattern
2125 -- in either case if this is the only working day so set i itself as its next index
2126 -- and one more to the days to next working day figure
2127 -- if this day itself is not a working day then it means that all days in this
2128 -- work pattern have been setup with 0, so exit loop, don't bother populating other days
2129
2130 l_proc_step := 72+(i/10000);
2131 IF g_debug THEN
2132 debug_enter(l_proc_name,72+(i/10000));
2133 END IF;
2134
2135 IF g_work_pattern_cache(i).hours > 0 THEN
2136 l_proc_step := 75+(i/10000);
2137 IF g_debug THEN
2138 debug_enter(l_proc_name,75+(i/10000));
2139 END IF;
2140 g_work_pattern_cache(i).days_to_next_working_day :=
2141 NVL(g_work_pattern_cache(i).days_to_next_working_day,0) + 1;
2142 g_work_pattern_cache(i).next_working_day_index := i;
2143 ELSE
2144 l_proc_step := 77+(i/10000);
2145 IF g_debug THEN
2146 debug_enter(l_proc_name,77+(i/10000));
2147 END IF;
2148 -- clear the days to next working day because there is no next working day
2149 g_work_pattern_cache(i).days_to_next_working_day := NULL;
2150 EXIT; -- outer loop
2151 END IF;
2152
2153 END IF;
2154
2155 l_proc_step := 80+(i/10000);
2156 IF g_debug THEN
2157 debug_enter(l_proc_name,80+(i/10000));
2158 END IF;
2159
2160 i := g_work_pattern_cache.NEXT(i);
2161
2162 END LOOP; -- loop thru each loaded day in prev step
2163
2164 END IF; --IF g_last_business_group_id <> p_business_group_id -- if the bg has changed reload
2165
2166 l_proc_step := 90;
2167 IF g_debug THEN
2168 debug(l_proc_name,l_proc_step);
2169 END IF;
2170
2171
2172 p_work_pattern_used := l_work_pattern_to_use;
2173 p_asg_work_pattern_start_day_n := l_asg_work_pattern_start_day_n;
2174 p_asg_work_pattern_start_date := l_asg_work_pattern_start_date;
2175
2176 l_proc_step := 95;
2177 IF g_debug THEN
2178 debug(l_proc_name,l_proc_step);
2179 END IF;
2180
2181 l_date_start_day_index :=
2182 get_day_index_for_date
2183 (p_asg_work_pattern_start_date => l_asg_work_pattern_start_date
2184 ,p_asg_work_pattern_start_day_n => l_asg_work_pattern_start_day_n
2185 ,p_total_days_in_work_pattern => g_work_pattern_cache.COUNT
2186 ,p_date_to_index => p_date_start
2187 );
2188
2189 p_date_start_day_index := l_date_start_day_index;
2190
2191 IF g_debug THEN
2192 debug('p_work_pattern_used:'||l_work_pattern_to_use);
2193 debug('p_asg_work_pattern_start_day_n:'||l_asg_work_pattern_start_day_n);
2194 debug('p_asg_work_pattern_start_date:'||
2195 fnd_date.date_to_canonical(l_asg_work_pattern_start_date));
2196 debug('p_date_start_day_index:'||l_date_start_day_index);
2197 debug_exit(l_proc_name);
2198 END IF;
2199
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 clear_cache;
2203 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2204 debug_others(l_proc_name,l_proc_step);
2205 IF g_debug THEN
2206 debug('Leaving: '||l_proc_name,-999);
2207 END IF;
2208 fnd_message.raise_error;
2209 ELSE
2210 RAISE;
2211 END IF;
2212 END load_work_pattern_into_cache;
2213
2214
2215 FUNCTION add_working_days_using_one_wp
2216 (p_assignment_id IN NUMBER
2217 ,p_business_group_id IN NUMBER
2218 ,p_date_start IN DATE
2219 ,p_working_days_to_add IN NUMBER
2220 ,p_default_wp IN VARCHAR2 DEFAULT NULL
2221 ,p_override_wp IN VARCHAR2 DEFAULT NULL
2222 ) RETURN DATE
2223 IS
2224
2225 l_work_pattern_days t_work_pattern_cache_type;
2226 l_work_pattern_used pay_user_columns.user_column_name%TYPE;
2227 l_asg_work_pattern_start_day_n BINARY_INTEGER;
2228 l_asg_work_pattern_start_date DATE;
2229 l_date_start_day_index BINARY_INTEGER;
2230 i BINARY_INTEGER;
2231 l_days_remaining_to_add NUMBER(20);
2232 l_total_calendar_days NUMBER(20);
2233 l_date_after_n_working_days DATE;
2234
2235 l_proc_step NUMBER(20,10):=0;
2236 l_proc_name VARCHAR2(61):= g_package_name||'add_working_days_using_one_wp';
2237
2238 BEGIN
2239 /*
2240 --3. Deterime the day index for date_start
2241 --4. Decrement the p_days by 1 as we add one day less
2242 --5. Loop thru the cache adding up the index offsets (stored or derived at run time)
2243 --6. With each jump decrement p_days by 1 more
2244 --7. Exit the loop when p_days is 0
2245 --8. Add the sum of index offsets to date_start and return that as the date
2246 --
2247 --9. part p_days is rounded down...ie adding 0.5 returns the same date as adding 1
2248 --10. special check for p_days
2249 */
2250
2251 g_debug := hr_utility.debug_enabled;
2252 IF g_debug THEN
2253 debug_enter(l_proc_name);
2254 END IF;
2255
2256 load_work_pattern_into_cache
2257 (p_assignment_id => p_assignment_id
2258 ,p_business_group_id => p_business_group_id
2259 ,p_date_start => p_date_start
2260 ,p_default_wp => p_default_wp
2261 ,p_override_wp => p_override_wp
2262 ,p_work_pattern_used => l_work_pattern_used
2263 ,p_asg_work_pattern_start_day_n => l_asg_work_pattern_start_day_n
2264 ,p_asg_work_pattern_start_date => l_asg_work_pattern_start_date
2265 ,p_date_start_day_index => l_date_start_day_index
2266 );
2267
2268 l_proc_step := 10;
2269 IF g_debug THEN
2270 debug(l_proc_name,l_proc_step);
2271 END IF;
2272
2273 -- never use g_work_pattern_cache without first calling load_work_pattern_into_cache
2274 l_work_pattern_days := g_work_pattern_cache;
2275 -- always assign cache to locally and then use it
2276
2277 -- now find out the day of the work pattern that date_start corresponds to
2278 -- if this work pattern was the override or the default wp then we simply need to know
2279 -- the day of week and determine the offset assuming Sunday (or a pre-se global) as Day 01
2280 -- if this work pattern was the assignment level work pattern then we need to use the logic in
2281 -- get_day_dets to determine the starting offset
2282
2283 l_proc_step := 20;
2284 IF g_debug THEN
2285 debug(l_proc_name,l_proc_step);
2286 END IF;
2287
2288
2289 l_days_remaining_to_add := CEIL(p_working_days_to_add);
2290 -- adding 0.5 working day is same adding 1 working day
2291 -- adding 1.5 working day is same as adding 2 working days
2292
2293 l_total_calendar_days := 0;
2294 i := l_date_start_day_index;
2295
2296 l_proc_step := 30;
2297 IF g_debug THEN
2298 debug(l_proc_name,l_proc_step);
2299 END IF;
2300
2301 WHILE l_days_remaining_to_add > 0
2302 AND i IS NOT NULL -- for wp with all 0 days this will become NULL
2303 --AND l_total_calendar_days IS NOT NULL -- for wp will all 0 days this will become NULL
2304 LOOP
2305
2306 l_proc_step := 32+i/10000;
2307 IF g_debug THEN
2308 debug(l_proc_name,32+i/10000);
2309 END IF;
2310
2311 IF l_work_pattern_days(i).hours > 0 THEN
2312
2313 l_proc_step := 35+i/10000;
2314 IF g_debug THEN
2315 debug(l_proc_name,35+i/10000);
2316 END IF;
2317
2318 l_days_remaining_to_add := l_days_remaining_to_add - 1;
2319
2320 END IF;
2321
2322 l_total_calendar_days :=
2323 l_total_calendar_days +
2324 l_work_pattern_days(i).days_to_next_working_day;
2325
2326 i := l_work_pattern_days(i).next_working_day_index;
2327
2328 END LOOP; -- loop thru each loaded day in prev step
2329
2330 l_proc_step := 40;
2331 IF g_debug THEN
2332 debug(l_proc_name,40);
2333 END IF;
2334
2335 l_date_after_n_working_days := p_date_start + l_total_calendar_days;
2336
2337 IF g_debug THEN
2338 debug('l_date_after_n_working_days:'||
2339 fnd_date.date_to_canonical(l_date_after_n_working_days));
2340 debug_exit(l_proc_name);
2341 END IF; -- IF g_debug THEN
2342
2343 RETURN l_date_after_n_working_days;
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 clear_cache;
2348 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2349 debug_others(l_proc_name,l_proc_step);
2350 IF g_debug THEN
2351 debug('Leaving: '||l_proc_name,-999);
2352 END IF;
2353 fnd_message.raise_error;
2354 ELSE
2355 RAISE;
2356 END IF;
2357 END add_working_days_using_one_wp;
2358
2359
2360 END pqp_schedule_calculation_pkg;
[Home] [Help]