1 PACKAGE BODY pqp_fte_utilities AS
2 /* $Header: pqftepkg.pkb 120.4 2006/11/16 04:23:08 agolechh noship $ */
3 --
4 g_package_name VARCHAR2(31) := 'pqp_fte_utilities.';
5 g_debug BOOLEAN:=FALSE;
6 --
7 --
8 TYPE g_output_file_rec_type IS RECORD
9 (assignment_id per_all_assignments_f.assignment_id%TYPE
10 ,status VARCHAR2(80)
11 ,employee_number per_all_people_f.employee_number%TYPE
12 ,assignment_number per_all_assignments_f.assignment_number%TYPE
13 ,effective_date per_all_assignments_f.effective_start_date%TYPE
14 ,change_in VARCHAR2(80)
15 ,FTE_old_value per_assignment_budget_values_f.value%TYPE
16 ,change_type VARCHAR2(80)
17 ,FTE_new_value per_assignment_budget_values_f.value%TYPE
18 ,normal_hours per_all_assignments_f.normal_hours%TYPE
19 ,frequency per_all_assignments_f.frequency%TYPE
20 ,contract_type pqp_assignment_attributes_f.contract_type%TYPE
21 ,annual_hours pay_user_column_instances_f.value%TYPE
22 ,period_divisor pay_user_column_instances_f.value%TYPE
23 ,message fnd_new_messages.message_text%TYPE
24 );
25 TYPE t_output_file_record_type IS TABLE OF g_output_file_rec_type
26 INDEX BY BINARY_INTEGER;
27 g_output_file_records t_output_file_record_type; -- do not include in clear cache
28 g_is_concurrent_program_run BOOLEAN:= FALSE;
29 g_column_separator VARCHAR2(10):=' , ';
30 --
31 --
32 -- cache for get_installation_status
33 g_application_id fnd_product_installations.application_id%TYPE;
34 g_status fnd_product_installations.status%TYPE;
35
36 -- cache for load_cached_constants
37 g_pqp_contract_table_id pay_user_tables.user_table_id%TYPE;
38 g_annual_hours_col_id pay_user_columns.user_column_id%TYPE;
39 g_period_divisor_col_id pay_user_columns.user_column_id%TYPE;
40 g_not_cached_constants BOOLEAN:=TRUE;
41
42 -- cache for chk_fte_exists
43 g_fte_exists_assignment_id per_all_assignments_f.assignment_id%TYPE;
44 g_fte_exists BOOLEAN;
45
46 -- cache for get_earliest_possible_date
47 g_epFd_assignment_id per_all_assignments_f.assignment_id%TYPE;
48 g_epFd_earliest_possible_date DATE;
49
50
51 --
52 --
53 --
54 CURSOR csr_fte_exists
55 (p_assignment_id NUMBER
56 ) IS
57 SELECT 1
58 FROM per_assignment_budget_values_f
59 WHERE assignment_id = p_assignment_id
60 AND unit = 'FTE'
61 AND ROWNUM < 2;
62
63 CURSOR csr_effective_fte
64 (p_assignment_id NUMBER
65 ,p_effective_date DATE
66 ) IS
67 SELECT assignment_budget_value_id,
68 value,
69 effective_start_date,
70 effective_end_date,
71 object_version_number
72 FROM per_assignment_budget_values_f
73 WHERE assignment_id = p_assignment_id
74 AND unit = 'FTE'
75 AND p_effective_date
76 BETWEEN effective_start_date
77 AND effective_end_date;
78
79 CURSOR csr_assignment_details
80 (p_assignment_id NUMBER
81 ,p_effective_date DATE
82 ) IS
83 SELECT asg.business_group_id
84 ,asg.normal_hours
85 ,asg.frequency
86 FROM per_all_assignments_f asg
87 WHERE asg.assignment_id = p_assignment_id
88 AND p_effective_date
89 BETWEEN asg.effective_start_date
90 AND asg.effective_end_date;
91
92 -- dummy cursor for record structure
93 CURSOR csr_contract_details IS
94 SELECT TO_NUMBER('0') annual_hours
95 ,TO_NUMBER('0') period_divisor
96 ,row_low_range_or_name contract_type
97 ,user_row_id
98 FROM pay_user_rows_f
99 WHERE user_row_id = 0;
100
101
102 CURSOR csr_assignment_contract
103 (p_assignment_id NUMBER
104 ,p_effective_date DATE
105 ,p_pqp_contract_table_id NUMBER
106 ) IS
107 SELECT pur.user_row_id, aat.contract_type
108 FROM pqp_assignment_attributes_f aat
109 ,pay_user_rows_f pur
110 WHERE aat.assignment_id = p_assignment_id
111 AND p_effective_date
112 BETWEEN aat.effective_start_date
113 AND aat.effective_end_date
114 AND pur.user_table_id = p_pqp_contract_table_id
115 AND pur.business_group_id = aat.business_group_id
116 AND pur.row_low_range_or_name = aat.contract_type
117 AND aat.effective_start_date
118 BETWEEN pur.effective_start_date
119 AND pur.effective_end_date;
120
121
122 CURSOR csr_get_contract_value
123 (p_contract_column_id NUMBER
124 ,p_contract_row_id NUMBER
125 ,p_effective_date DATE
126 ) IS
127 SELECT inst.value
128 FROM pay_user_column_instances_f inst
129 WHERE inst.user_column_id = p_contract_column_id
130 AND inst.user_row_id = p_contract_row_id
131 AND p_effective_date
132 BETWEEN inst.effective_start_date
133 AND inst.effective_end_date;
134
135 --
136 --
137 --
138 PROCEDURE debug(
139 p_trace_message IN VARCHAR2
140 ,p_trace_location IN NUMBER DEFAULT NULL
141 )
142 IS
143 BEGIN
144 IF NOT g_is_concurrent_program_run THEN
145 pqp_utilities.debug(p_trace_message, p_trace_location);
146 ELSE
147 IF p_trace_location IS NULL THEN
148 fnd_file.put_line(fnd_file.log,p_trace_message);
149 ELSE
150 fnd_file.put_line(fnd_file.log,RPAD(p_trace_message,80,' ')||TO_CHAR(p_trace_location));
151 END IF;
152 END IF;
153 END debug;
154 --
155 --
156 --
157 --PROCEDURE debug(p_trace_number IN NUMBER)
158 --IS
159 --BEGIN
160 -- pqp_utilities.debug(p_trace_number);
161 --END debug;
162 ----
163 ----
164 ----
165 --PROCEDURE debug(p_trace_date IN DATE)
166 --IS
167 --BEGIN
168 -- pqp_utilities.debug(p_trace_date);
169 --END debug;
170 --
171 --
172 --
173 PROCEDURE debug_enter(
174 p_proc_name IN VARCHAR2
175 ,p_trace_on IN VARCHAR2 DEFAULT NULL
176 )
177 IS
178 BEGIN
179 IF NOT g_is_concurrent_program_run THEN
180 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
181 ELSE
182 fnd_file.put_line(fnd_file.log,RPAD(p_proc_name,80,' ')||'+0');
183 END IF;
184 END debug_enter;
185 --
186 --
187 --
188 PROCEDURE debug_exit(
189 p_proc_name IN VARCHAR2
190 ,p_trace_off IN VARCHAR2 DEFAULT NULL
191 )
192 IS
193 BEGIN
194 IF NOT g_is_concurrent_program_run THEN
195 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
196 ELSE
197 fnd_file.put_line(fnd_file.log,RPAD(p_proc_name,80,' ')||'-0');
198 END IF;
199 END debug_exit;
200 --
201 --
202 --
203 PROCEDURE debug_others(
204 p_proc_name IN VARCHAR2
205 ,p_proc_step IN NUMBER DEFAULT NULL
206 )
207 IS
208 BEGIN
209 pqp_utilities.debug_others(p_proc_name, p_proc_step);
210 END debug_others;
211 --
212 --
213 --
214 PROCEDURE check_error_code
215 (p_error_code IN NUMBER
216 ,p_error_message IN VARCHAR2
217 )
218 IS
219 BEGIN
220 pqp_utilities.check_error_code(p_error_code, p_error_message);
221 END;
222 --
223 --
224 --
225 PROCEDURE clear_cache
226 IS
227 BEGIN
228 -- cache for get_installation_status
229 g_application_id := NULL;
230 g_status := NULL;
231
232 -- cache for load_cached_constants
233 g_pqp_contract_table_id :=NULL;
234 g_annual_hours_col_id :=NULL;
235 g_period_divisor_col_id :=NULL;
236 g_not_cached_constants :=TRUE;
237
238 -- cache for chk_fte_exists
239 g_fte_exists_assignment_id :=NULL;
240 g_fte_exists :=NULL;
241
242 -- cache for get_earliest_possible_date
243 g_epFd_assignment_id :=NULL;
244 g_epFd_earliest_possible_date :=NULL;
245
246 END clear_cache;
247 --
248 --
249 --
250 FUNCTION convert_record_to_outputstring
251 (p_output_file_record g_output_file_rec_type
252 ) RETURN VARCHAR2
253 IS
254 l_proc_step NUMBER(20,10);
255 l_proc_name VARCHAR2(61):=
256 g_package_name||'convert_record_to_outputstring';
257
258 l_outputstring VARCHAR2(4000);
259
260 BEGIN -- convert_record_to_outputstring
261
262 IF g_debug THEN
263 debug_enter(l_proc_name);
264 END IF;
265
266 l_outputstring :=
267 RPAD(NVL(p_output_file_record.status,' '),30,' ')||g_column_separator||
268 RPAD(NVL(p_output_file_record.employee_number,' '),20,' ')||g_column_separator||
269 RPAD(NVL(p_output_file_record.assignment_number,'AsgId:'||p_output_file_record.assignment_id),30,' ')||g_column_separator||
270 RPAD(NVL(fnd_date.date_to_displaydate(p_output_file_record.effective_date),' '),15,' ')||g_column_separator||
271 RPAD(NVL(p_output_file_record.change_in,' '),30,' ')||g_column_separator||
272 RPAD(NVL(TO_CHAR(p_output_file_record.FTE_old_value),' '),20,' ')||g_column_separator||
273 RPAD(NVL(p_output_file_record.change_type,' '),15,' ')||g_column_separator||
274 RPAD(NVL(TO_CHAR(p_output_file_record.FTE_new_value),' '),20,' ')||g_column_separator||
275 RPAD(NVL(TO_CHAR(p_output_file_record.normal_hours),' '),15,' ')||g_column_separator||
276 RPAD(NVL(HR_GENERAL.DECODE_LOOKUP('FREQUENCY',p_output_file_record.frequency),' '),10,' ')||g_column_separator||
277 RPAD(NVL(p_output_file_record.contract_type,' '),30,' ')||g_column_separator||
278 RPAD(NVL(p_output_file_record.annual_hours,' '),15,' ')||g_column_separator||
279 RPAD(NVL(p_output_file_record.period_divisor,' '),15,' ')||g_column_separator||
280 RPAD(p_output_file_record.message,255,' ')
281 ;
282
283 IF g_debug THEN
284 debug_exit(l_proc_name);
285 debug('l_outputstring_1_200:'||SUBSTR(l_outputstring,1,200));
286 debug('l_outputstring_201_400:'||SUBSTR(l_outputstring,201,200));
287 debug('l_outputstring_401_600:'||SUBSTR(l_outputstring,401,200));
288 END IF;
289
290 RETURN l_outputstring;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 clear_cache;
295 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
296 debug_others(l_proc_name,l_proc_step);
297 IF g_debug THEN
298 debug('Leaving: '||l_proc_name,-999);
299 END IF;
300 fnd_message.raise_error;
301 ELSE
302 RAISE;
303 END IF;
304 END convert_record_to_outputstring;
305 --
306 --
307 --
308 PROCEDURE write_output_file_records
309 IS
310
311 l_proc_step NUMBER(20,10);
312 l_proc_name VARCHAR2(61):=
313 g_package_name||'write_output_file_records';
314
315 i BINARY_INTEGER;
316
317 BEGIN -- write_output_file_records
318
319 IF g_debug THEN
320 debug_enter(l_proc_name);
321 END IF;
322
323 i:= g_output_file_records.FIRST;
324
325 WHILE i IS NOT NULL
326 LOOP
327
328 fnd_file.put_line
329 (fnd_file.output
330 ,convert_record_to_outputstring(g_output_file_records(i))
331 );
332
333 i := g_output_file_records.NEXT(i);
334
335 END LOOP;
336
337 IF g_debug THEN
338 debug_exit(l_proc_name);
339 END IF;
340
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 clear_cache;
345 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
346 debug_others(l_proc_name,l_proc_step);
347 IF g_debug THEN
348 debug('Leaving: '||l_proc_name,-999);
349 END IF;
350 fnd_message.raise_error;
351 ELSE
352 RAISE;
353 END IF;
354 END write_output_file_records;
355 --
356 --
357 --
358 FUNCTION get_installation_status
359 (p_application_id IN NUMBER
360 ) RETURN VARCHAR2
361 IS
362
363 l_proc_step NUMBER(20,10):=0;
364 l_proc_name VARCHAR2(61):=
365 g_package_name||'get_installation_status';
366
367 CURSOR csr_is_installed
368 (p_application_id NUMBER
369 ) IS
370 SELECT status
371 FROM fnd_product_installations
372 WHERE application_id = p_application_id;
373
374 l_status fnd_product_installations.status%TYPE;
375
376 BEGIN -- get_installation_status
377
378 IF g_debug THEN
379 debug_enter(l_proc_name);
380 debug('p_application_id:'||p_application_id);
381 debug('g_application_id:'||g_application_id);
382 debug('g_status:'||g_status);
383 END IF;
384
385 IF g_application_id <> p_application_id
386 OR
387 g_application_id IS NULL
388 OR
389 g_status IS NULL
390 THEN
391
392 OPEN csr_is_installed(p_application_id);
393 FETCH csr_is_installed INTO l_status;
394 IF csr_is_installed%FOUND THEN
395 IF l_status = 'I' THEN
396 g_application_id := p_application_id;
397 g_status := l_status;
398 ELSE
399 g_application_id := p_application_id;
400 g_status := l_status;
401 END IF;
402 ELSE
403 -- invalid application id, destroy cache, set status to null -- redundant
404 g_application_id := NULL;
405 g_status := NULL;
406 l_status := NULL;
407 END IF;
408 CLOSE csr_is_installed;
409
410 ELSE
411
412 l_status := g_status;
413
414 END IF; -- IF g_application_id <> p_application_id
415
416
417 IF g_debug THEN
418 debug('l_status:'||l_status);
419 debug_exit(l_proc_name);
420 END IF;
421
422 RETURN l_status;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 clear_cache;
427 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
428 debug_others(l_proc_name,l_proc_step);
429 IF g_debug THEN
430 debug('Leaving: '||l_proc_name,-999);
431 END IF;
432 fnd_message.raise_error;
433 ELSE
434 RAISE;
435 END IF;
436 END get_installation_status;
437 --
438 --
439 --
440 PROCEDURE load_cached_constants
441 IS
442 l_proc_step NUMBER(20,10):= 0;
443 l_proc_name VARCHAR2(61):=
444 g_package_name||'load_cached_constants';
445
446 CURSOR csr_pqp_contract_table IS
447 SELECT user_table_id
448 FROM pay_user_tables
449 WHERE user_table_name = 'PQP_CONTRACT_TYPES'
450 AND legislation_code = 'GB';
451
452 CURSOR csr_relevant_columns
453 (p_user_table_id IN NUMBER
454 ,p_user_column_name IN VARCHAR2
455 ) IS
456 SELECT user_column_id
457 FROM pay_user_columns
458 WHERE user_table_id = p_user_table_id
459 AND UPPER(user_column_name) = UPPER(p_user_column_name)
460 AND legislation_code = 'GB';
461
462 l_status fnd_product_installations.status%TYPE;
463
464
465 BEGIN -- load_cached_constants
466
467 IF g_debug THEN
468 debug_enter(l_proc_name);
469 debug('g_pqp_contract_table_id:'||g_pqp_contract_table_id);
470 debug('g_annual_hours_col_id:'||g_annual_hours_col_id);
471 debug('g_period_divisor_col_id:'||g_period_divisor_col_id);
472 IF g_not_cached_constants THEN
473 debug('g_not_cached_constants:TRUE');
474 ELSE
475 debug('g_not_cached_constants:FALSE');
476 END IF;
477 END IF;
478
479 IF g_not_cached_constants THEN
480
481 g_pqp_contract_table_id := NULL;
482 g_annual_hours_col_id := NULL;
483 g_period_divisor_col_id := NULL;
484 g_not_cached_constants := FALSE;
485
486 l_status := get_installation_status(801);
487
488 OPEN csr_pqp_contract_table;
489 FETCH csr_pqp_contract_table INTO g_pqp_contract_table_id;
490 IF csr_pqp_contract_table%NOTFOUND THEN
491 l_proc_step := 10;
492 IF g_debug THEN
493 debug(l_proc_name,l_proc_step);
494 END IF;
495 g_not_cached_constants := TRUE;
496 RAISE NO_DATA_FOUND;
497 END IF;
498 CLOSE csr_pqp_contract_table;
499
500 IF g_debug THEN
501 debug('g_pqp_contract_table_id:'||g_pqp_contract_table_id);
502 END IF;
503
504 OPEN csr_relevant_columns
505 (p_user_table_id => g_pqp_contract_table_id
506 ,p_user_column_name => 'Annual Hours'
507 );
508 FETCH csr_relevant_columns INTO g_annual_hours_col_id;
509 IF csr_relevant_columns%NOTFOUND THEN
510 l_proc_step := 20;
511 IF g_debug THEN
512 debug(l_proc_name,l_proc_step);
513 END IF;
514 g_not_cached_constants := TRUE;
515 RAISE NO_DATA_FOUND;
516 END IF;
517 CLOSE csr_relevant_columns;
518
519 IF g_debug THEN
520 debug('g_annual_hours_col_id:'||g_annual_hours_col_id);
521 END IF;
522
523
524 OPEN csr_relevant_columns
525 (p_user_table_id => g_pqp_contract_table_id
526 ,p_user_column_name => 'Period Divisor'
527 );
528 FETCH csr_relevant_columns INTO g_period_divisor_col_id;
529 IF csr_relevant_columns%NOTFOUND THEN
530 l_proc_step := 30;
531 IF g_debug THEN
532 debug(l_proc_name,l_proc_step);
533 END IF;
534 g_not_cached_constants := TRUE;
535 RAISE NO_DATA_FOUND;
536 END IF;
537 CLOSE csr_relevant_columns;
538
539 IF g_debug THEN
540 debug('g_period_divisor_col_id:'||g_period_divisor_col_id);
541 END IF;
542
543 ELSE
544 IF g_debug THEN
545 debug('g_not_cached_constants:FALSE');
546 END IF;
547 END IF;
548
549 IF g_debug THEN
550 debug('g_pqp_contract_table_id:'||g_pqp_contract_table_id);
551 debug('g_annual_hours_col_id:'||g_annual_hours_col_id);
552 debug('g_period_divisor_col_id:'||g_period_divisor_col_id);
553 debug('g_application_id:'||g_application_id);
554 debug('g_status:'||g_status);
555 debug_exit(l_proc_name);
556 END IF;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 clear_cache;
561 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
562 debug_others(l_proc_name,l_proc_step);
563 IF g_debug THEN
564 debug('Leaving: '||l_proc_name,-999);
565 END IF;
566 fnd_message.raise_error;
567 ELSE
568 RAISE;
569 END IF;
570 END load_cached_constants;
571 --
572 --
573 --
574 FUNCTION get_earliest_possible_FTE_date
575 (p_assignment_id NUMBER
576 ,p_reload_cache BOOLEAN DEFAULT FALSE
577 ) RETURN DATE
578 IS
579
580 l_proc_step NUMBER(20,10):=0;
581 l_proc_name VARCHAR2(61):=
582 g_package_name||'get_earliest_possible_FTE_date';
583
584 CURSOR csr_min_aat_start_date
585 (p_assignment_id NUMBER
586 ) IS
587 SELECT MIN(aat.effective_start_date)
588 FROM pqp_assignment_attributes_f aat
589 WHERE aat.assignment_id = p_assignment_id
590 AND aat.contract_type IS NOT NULL;
591
592 CURSOR csr_min_asg_start_date
593 (p_assignment_id NUMBER
594 ) IS
595 SELECT MIN(asg.effective_start_date)
596 FROM per_all_assignments_f asg
597 WHERE asg.assignment_id = p_assignment_id
598 AND asg.normal_hours IS NOT NULL;
599
600
601 l_aat_effective_start_date DATE;
602 l_asg_effective_start_date DATE;
603 --l_earliest_possible_FTE_date DATE;
604
605 BEGIN -- get_earliest_possible_FTE_date
606
607 IF g_debug THEN
608 debug_enter(l_proc_name);
609 debug('p_assignment_id:'||p_assignment_id);
610 debug('g_epFd_assignment_id:'||g_epFd_assignment_id);
611 debug('g_epFd_earliest_possible_date:'||
612 fnd_date.date_to_canonical(g_epFd_earliest_possible_date));
613 IF p_reload_cache = TRUE THEN
614 debug('p_reload_cache:TRUE');
615 END IF;
616 IF p_reload_cache = FALSE THEN
617 debug('p_reload_cache:FALSE');
618 END IF;
619 IF p_reload_cache IS NULL THEN
620 debug('p_reload_cache:IS NULL');
621 END IF;
622 END IF;
623
624
625 IF p_assignment_id <> g_epFd_assignment_id
626 OR
627 g_epFd_assignment_id IS NULL
628 OR
629 g_epFd_earliest_possible_date IS NULL
630 OR
631 p_reload_cache = TRUE
632 THEN
633
634 -- always clear cache before reloading it
635 g_epFd_assignment_id := NULL;
636 g_epFd_earliest_possible_date := NULL;
637
638 OPEN csr_min_aat_start_date(p_assignment_id);
639 FETCH csr_min_aat_start_date INTO l_aat_effective_start_date;
640 CLOSE csr_min_aat_start_date;
641
642 OPEN csr_min_asg_start_date(p_assignment_id);
643 FETCH csr_min_asg_start_date INTO l_asg_effective_start_date;
644 CLOSE csr_min_asg_start_date;
645
646 IF g_debug THEN
647 debug('l_aat_effective_start_date:'||l_aat_effective_start_date);
648 debug('l_asg_effective_start_date:'||l_asg_effective_start_date);
649 END IF;
650
651 g_epFd_assignment_id := p_assignment_id;
652
653 g_epFd_earliest_possible_date :=
654 GREATEST(l_aat_effective_start_date, l_asg_effective_start_date);
655
656 END IF;
657
658 IF g_debug THEN
659 debug('g_epFd_assignment_id:'||g_epFd_assignment_id);
660 debug('g_epFd_earliest_possible_date:'||
661 fnd_date.date_to_canonical(g_epFd_earliest_possible_date));
662 debug_exit(l_proc_name);
663 END IF;
664
665 RETURN g_epFd_earliest_possible_date;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 clear_cache;
670 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
671 debug_others(l_proc_name,l_proc_step);
672 IF g_debug THEN
673 debug('Leaving: '||l_proc_name,-999);
674 END IF;
675 fnd_message.raise_error;
676 ELSE
677 RAISE;
678 END IF;
679 END get_earliest_possible_FTE_date;
680 --
681 --
682 --
683 FUNCTION chk_fte_exists
684 (p_assignment_id NUMBER
685 ,p_reload_cache BOOLEAN DEFAULT FALSE
686 ) RETURN BOOLEAN
687 IS
688 l_proc_step NUMBER(20,10):=0;
689 l_proc_name VARCHAR2(61):=
690 g_package_name||'chk_fte_exists';
691
692 l_fte_exists_tmp csr_fte_exists%ROWTYPE;
693 l_fte_exists BOOLEAN;
694 l_FTE_processing_start_date DATE;
695
696 BEGIN -- chk_fte_exists
697
698 IF g_debug THEN
699 debug_enter(l_proc_name);
700 debug('p_assignment_id:'||p_assignment_id);
701 debug('g_fte_exists_assignment_id:'||g_fte_exists_assignment_id);
702 IF g_fte_exists = TRUE THEN
703 debug('g_fte_exists:TRUE');
704 END IF;
705 IF g_fte_exists = FALSE THEN
706 debug('g_fte_exists:FALSE');
707 END IF;
708 IF g_fte_exists IS NULL THEN
709 debug('g_fte_exists:IS NULL');
710 END IF;
711 IF p_reload_cache = TRUE THEN
712 debug('p_reload_cache:TRUE');
713 END IF;
714 IF p_reload_cache = FALSE THEN
715 debug('p_reload_cache:FALSE');
716 END IF;
717 IF p_reload_cache IS NULL THEN
718 debug('p_reload_cache:IS NULL');
719 END IF;
720 END IF;
721
722 IF p_assignment_id <> g_fte_exists_assignment_id
723 OR
724 g_fte_exists IS NULL
725 OR
726 g_fte_exists_assignment_id IS NULL
727 OR
728 p_reload_cache = TRUE
729 THEN
730 IF p_assignment_id IS NOT NULL
731 THEN
732 OPEN csr_fte_exists(p_assignment_id);
733 FETCH csr_fte_exists INTO l_fte_exists_tmp;
734 g_fte_exists := csr_fte_exists%FOUND;
735 g_fte_exists_assignment_id := p_assignment_id;
736 CLOSE csr_fte_exists;
737 ELSE
738 RAISE NO_DATA_FOUND;
739 -- do not allow function to return
740 -- as this may cause the calling logic to make
741 -- an incorrect decision
742 END IF;
743 END IF; -- IF g_fte_exists_assignment_id <> p_assignment_id
744
745 -- DO NOT RETURN cache globals always copy to local first
746 l_fte_exists := g_fte_exists;
747
748 IF g_debug THEN
749 IF g_fte_exists = TRUE THEN
750 debug('g_fte_exists:TRUE');
751 END IF;
752 IF g_fte_exists = FALSE THEN
753 debug('g_fte_exists:FALSE');
754 END IF;
755 IF g_fte_exists IS NULL THEN
756 debug('g_fte_exists:IS NULL');
757 END IF;
758 debug('g_fte_exists_assignment_id:'||g_fte_exists_assignment_id);
759 debug_exit(l_proc_name);
760 END IF;
761
762 RETURN l_fte_exists;
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 clear_cache;
767 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
768 debug_others(l_proc_name,l_proc_step);
769 IF g_debug THEN
770 debug('Leaving: '||l_proc_name,-999);
771 END IF;
772 fnd_message.raise_error;
773 ELSE
774 RAISE;
775 END IF;
776 END chk_fte_exists;
777 --
778 --
779 --
780 FUNCTION get_FTE_processing_start_date
781 (p_assignment_id IN NUMBER
782 ,p_effective_date IN DATE
783 ) RETURN DATE
784 IS
785 l_proc_step NUMBER(20,10):=0;
786 l_proc_name VARCHAR2(61):=
787 g_package_name||'get_FTE_processing_start_date';
788
789 l_FTE_processing_start_date DATE;
790
791 BEGIN -- get_FTE_processing_start_date
792
793 IF g_debug THEN
794 debug_enter(l_proc_name);
795 debug('p_assignment_id:'||p_assignment_id);
796 debug('p_effective_date:'||p_effective_date);
797 END IF;
798
799 -- IF FTE does not exist, processing start date = earliest possible
800 -- IF FTE does exist and effective date passed < earliest possible use earliest possible
801 -- IF FTE does exist and effective date passed > earliest possible use effective date
802
803 l_FTE_processing_start_date :=
804 get_earliest_possible_FTE_date(p_assignment_id,TRUE);
805
806 l_proc_step := 10;
807 IF g_debug THEN
808 debug(l_proc_name,l_proc_step);
809 debug('l_FTE_processing_start_date:'||l_FTE_processing_start_date);
810 END IF;
811
812 IF chk_fte_exists(p_assignment_id,TRUE)
813 AND
814 p_effective_date > l_FTE_processing_start_date
815 THEN
816 l_proc_step := 15;
817 IF g_debug THEN
818 debug(l_proc_name,l_proc_step);
819 END IF;
820 l_FTE_processing_start_date := p_effective_date;
821 END IF;
822
823 IF g_debug THEN
824 debug('l_FTE_processing_start_date:'||l_FTE_processing_start_date);
825 debug_exit(l_proc_name);
826 END IF;
827
828 RETURN l_FTE_processing_start_date;
829
830 EXCEPTION
831 WHEN OTHERS THEN
832 clear_cache;
833 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
834 debug_others(l_proc_name,l_proc_step);
835 IF g_debug THEN
836 debug('Leaving: '||l_proc_name,-999);
837 END IF;
838 fnd_message.raise_error;
839 ELSE
840 RAISE;
841 END IF;
842 END get_FTE_processing_start_date;
843 --
844 --
845 --
846 FUNCTION is_fte_enabled
847 (p_assignment_id NUMBER
848 ) RETURN BOOLEAN
849 IS
850 l_proc_step NUMBER(20,10):=0;
851 l_proc_name VARCHAR2(61):=
852 g_package_name||'is_fte_enabled';
853
854 -- l_does_fte_exist BOOLEAN;
855 l_earliest_possible_FTE_date DATE;
856
857 l_fte_is_enabled BOOLEAN;
858
859
860 BEGIN -- is_fte_enabled
861
862 -- setting reload cache to true here is ok
863 -- as this will if this true
864
865 -- l_does_fte_exist := chk_fte_exists(p_assignment_id);
866
867 l_earliest_possible_FTE_date :=
868 get_earliest_possible_FTE_date(p_assignment_id);
869
870 -- l_fte_is_enabled :=
871 -- (
872 -- l_earliest_possible_FTE_date IS NOT NULL
873 -- OR
874 -- l_does_fte_exist = TRUE
875 -- );
876
877 IF l_earliest_possible_FTE_date IS NOT NULL
878 THEN
879 l_fte_is_enabled := TRUE;
880 ELSE
881 l_fte_is_enabled := FALSE;
882 END IF;
883
884 RETURN l_fte_is_enabled;
885
886 END is_fte_enabled;
887 --
888 --
889 --
890 /* =====================================================================
891 Name : set_fte_value
892 Purpose : Calculate FTE and write to database.
893 Returns :
894 ---------------------------------------------------------------------*/
895 PROCEDURE set_fte_value
896 (p_assignment_id IN NUMBER
897 ,p_business_group_id IN NUMBER
898 ,p_calculation_date IN DATE
899 ,p_fte_value IN NUMBER
900 )
901 IS
902
903 l_proc_step NUMBER(20,10):= 0;
904 l_proc_name VARCHAR2(61):=
905 g_package_name||'set_fte_value';
906
907 l_datetrack_mode VARCHAR2(30);
908 l_future_end_date DATE;
909
910 -- Added a new variable obj
911
912 --l_object_version_number number;
913
914 l_fte_exists csr_fte_exists%ROWTYPE;
915 l_effective_fte_row csr_effective_fte%ROWTYPE;
916
917 -- Retrieve object_version_number
918 -- as this col is added recently
919 -- PS bug 2093889 for details
920
921 CURSOR csr_chk_future_fte_rows
922 (p_assignment_budget_value_id NUMBER
923 ,p_effective_date DATE
924 )
925 IS
926 SELECT effective_end_date
927 FROM per_assignment_budget_values_f
928 WHERE assignment_budget_value_id = p_assignment_budget_value_id
929 AND effective_start_date > p_effective_date
930 AND ROWNUM < 2;
931
932 BEGIN -- set_fte_value
933
934 IF g_debug THEN
935 debug_enter(l_proc_name);
936 debug('p_assignment_id:'||p_assignment_id);
937 debug('p_calculation_date:'||
938 fnd_date.date_to_canonical(p_calculation_date)
939 );
940 debug('p_fte_value:'||p_fte_value);
941 END IF;
942
943
944 IF NOT chk_fte_exists(p_assignment_id)
945 THEN
946
947 l_proc_step := 10;
948 IF g_debug THEN
949 debug(l_proc_name,l_proc_step);
950 END IF;
951
952 per_abv_ins.ins(
953 p_effective_date => p_calculation_date
954 ,p_business_group_id => p_business_group_id
955 ,p_assignment_id => p_assignment_id
956 ,p_unit => 'FTE'
957 ,p_value => p_fte_value
958 ,p_request_id => null
959 ,p_program_application_id => null
960 ,p_program_id => null
961 ,p_program_update_date => null
962 ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
963 ,p_object_version_number => l_effective_fte_row.object_version_number -- new param added
964 ,p_effective_start_date => l_effective_fte_row.effective_start_date
965 ,p_effective_end_date => l_effective_fte_row.effective_end_date
966 );
967
968 IF g_is_concurrent_program_run THEN
969 g_output_file_records(g_output_file_records.LAST).status := 'Processed';
970 g_output_file_records(g_output_file_records.LAST).change_type := 'INSERT';
971 END IF;
972
973 -- dummy call to repopulate chk_fte_exists cache
974 -- as in the subsequent call we want it to know that an fte exists
975 -- after this insert has taken and subsequent calls to set_fte_value
976 -- should attempt updates
977 IF NOT chk_fte_exists(p_assignment_id,TRUE)
978 THEN
979 -- if all goes well I never expect code to reach here
980 -- if it does it implies that some fatal error during insert
981 -- has been masked so abort processing now
982 l_proc_step := 15;
983 IF g_debug THEN
984 debug(l_proc_name,l_proc_step);
985 END IF;
986 RAISE NO_DATA_FOUND;
987 END IF;
988
989 ELSE -- an FTE record was found
990
991 OPEN csr_effective_fte(p_assignment_id,p_calculation_date);
992 FETCH csr_effective_fte INTO l_effective_fte_row;
993 CLOSE csr_effective_fte;
994
995 l_proc_step := 20;
996 IF g_debug THEN
997 debug('ROUND(p_fte_value,5):'||ROUND(p_fte_value,5));
998 debug('l_effective_fte_row.assignment_budget_value_id:'||
999 l_effective_fte_row.assignment_budget_value_id);
1000 debug('l_effective_fte_row.object_version_number:'||
1001 l_effective_fte_row.object_version_number);
1002 debug('l_effective_fte_row.effective_start_date:'||
1003 fnd_date.date_to_canonical(l_effective_fte_row.effective_start_date)
1004 );
1005 debug('l_effective_fte_row.effective_end_date:'||
1006 fnd_date.date_to_canonical(l_effective_fte_row.effective_end_date)
1007 );
1008 debug('l_effective_fte_row.value:'||l_effective_fte_row.value);
1009 END IF;
1010
1011 IF g_is_concurrent_program_run THEN
1012 g_output_file_records(g_output_file_records.LAST).FTE_old_value :=
1013 l_effective_fte_row.value;
1014 END IF;
1015
1016 OPEN csr_chk_future_fte_rows
1017 (l_effective_fte_row.assignment_budget_value_id
1018 ,p_calculation_date
1019 );
1020 FETCH csr_chk_future_fte_rows INTO l_future_end_date;
1021 IF csr_chk_future_fte_rows%FOUND
1022 THEN
1023 --
1024 --
1025 -- For updates, if future rows exist, use update override.
1026 -- This has been agreed as a valid requirement
1027 --
1028 l_datetrack_mode := 'UPDATE_OVERRIDE';
1029 ELSE
1030 --
1031 -- If no future changes exist, just use update
1032 --
1033 IF l_effective_fte_row.effective_start_date <> p_calculation_date
1034 THEN
1035 l_datetrack_mode := 'UPDATE';
1036 ELSE
1037 l_datetrack_mode := 'CORRECTION';
1038 END IF;
1039 END IF;
1040 CLOSE csr_chk_future_fte_rows;
1041
1042 l_proc_step := 30;
1043 IF g_debug THEN
1044 debug('l_future_end_date:'||l_future_end_date);
1045 debug('l_datetrack_mode:'||l_datetrack_mode);
1046 END IF;
1047
1048 IF l_datetrack_mode <> 'UPDATE_OVERRIDE'
1049 THEN
1050
1051 -- only do a datetrack UPDATE or correction if the value is different
1052
1053 IF ROUND(l_effective_fte_row.value,5) <> ROUND(p_fte_value,5)
1054 THEN
1055
1056 per_abv_upd.upd(
1057 p_effective_date => p_calculation_date
1058 ,p_datetrack_mode => l_datetrack_mode
1059 ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
1060 ,p_object_version_number => l_effective_fte_row.object_version_number -- new param added
1061 ,p_unit => 'FTE'
1062 ,p_value => p_fte_value
1063 ,p_request_id => null
1064 ,p_program_application_id => null
1065 ,p_program_id => null
1066 ,p_program_update_date => null
1067 ,p_effective_start_date => l_effective_fte_row.effective_start_date
1068 ,p_effective_end_date => l_effective_fte_row.effective_end_date
1069 );
1070
1071 IF g_is_concurrent_program_run THEN
1072 g_output_file_records(g_output_file_records.LAST).status := 'Processed';
1073 g_output_file_records(g_output_file_records.LAST).change_type := l_datetrack_mode;
1074 END IF;
1075
1076 ELSE
1077 IF g_is_concurrent_program_run THEN
1078 g_output_file_records(g_output_file_records.LAST).status := 'Processed (No Change)';
1079 g_output_file_records(g_output_file_records.LAST).change_type := l_datetrack_mode;
1080 END IF;
1081 END IF;
1082
1083 ELSE
1084
1085 IF g_debug THEN
1086 IF g_is_concurrent_program_run THEN
1087 debug('g_is_concurrent_program_run:TRUE');
1088 ELSE
1089 debug('g_is_concurrent_program_run:FALSE');
1090 END IF;
1091 END IF;
1092
1093 IF ( g_is_concurrent_program_run
1094 AND
1095 ROUND(l_effective_fte_row.value,5) <> ROUND(p_fte_value,5)
1096 )
1097 OR
1098 NOT g_is_concurrent_program_run
1099 THEN
1100
1101 l_proc_step := 40;
1102 IF g_debug THEN
1103 debug(l_proc_name,l_proc_step);
1104 END IF;
1105
1106 IF l_effective_fte_row.effective_start_date <> p_calculation_date
1107 THEN
1108
1109 per_abv_upd.upd(
1110 p_effective_date => p_calculation_date
1111 ,p_datetrack_mode => l_datetrack_mode
1112 ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
1113 ,p_object_version_number => l_effective_fte_row.object_version_number -- new param added
1114 ,p_unit => 'FTE'
1115 ,p_value => p_fte_value
1116 ,p_request_id => null
1117 ,p_program_application_id => null
1118 ,p_program_id => null
1119 ,p_program_update_date => null
1120 ,p_effective_start_date => l_effective_fte_row.effective_start_date
1121 ,p_effective_end_date => l_effective_fte_row.effective_end_date
1122 );
1123
1124 ELSE
1125
1126 l_datetrack_mode := hr_api.g_future_change;
1127 per_abv_del.del(
1128 p_effective_date => p_calculation_date
1129 ,p_datetrack_mode => l_datetrack_mode
1130 ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
1131 ,p_object_version_number => l_effective_fte_row.object_version_number -- new param added
1132 ,p_effective_start_date => l_effective_fte_row.effective_start_date
1133 ,p_effective_end_date => l_effective_fte_row.effective_end_date
1134 );
1135
1136 l_datetrack_mode := hr_api.g_correction;
1137 per_abv_upd.upd(
1138 p_effective_date => p_calculation_date
1139 ,p_datetrack_mode => l_datetrack_mode
1140 ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
1141 ,p_object_version_number => l_effective_fte_row.object_version_number -- new param added
1142 ,p_unit => 'FTE'
1143 ,p_value => p_fte_value
1144 ,p_request_id => null
1145 ,p_program_application_id => null
1146 ,p_program_id => null
1147 ,p_program_update_date => null
1148 ,p_effective_start_date => l_effective_fte_row.effective_start_date
1149 ,p_effective_end_date => l_effective_fte_row.effective_end_date
1150 );
1151
1152 END IF; -- IF l_effective_fte_row.effective_start_date <> p_calculation_date
1153
1154 IF g_is_concurrent_program_run THEN
1155 g_output_file_records(g_output_file_records.LAST).change_type := 'UPDATE_OVERRIDE';
1156 g_output_file_records(g_output_file_records.LAST).status := 'Processed';
1157 END IF;
1158
1159 ELSE
1160
1161 IF g_is_concurrent_program_run THEN
1162 g_output_file_records(g_output_file_records.LAST).change_type := 'UPDATE_OVERRIDE';
1163 g_output_file_records(g_output_file_records.LAST).status := 'Processed(No Change)';
1164 END IF;
1165
1166 END IF; -- IF ( g_is_concurrent_program_run AND ROUND(l_effective_fte_row.value,5)...
1167
1168
1169 END IF; -- IF l_datetrack_mode <> 'UPDATE_OVERRIDE'
1170
1171 END IF; -- IF NOT chk_fte_exists(p_assignment_id) THEN
1172
1173 IF g_debug THEN
1174 debug('l_effective_fte_row.assignment_budget_value_id:'||
1175 l_effective_fte_row.assignment_budget_value_id);
1176 debug('l_effective_fte_row.object_version_number:'||
1177 l_effective_fte_row.object_version_number);
1178 debug('l_effective_fte_row.effective_start_date:'||
1179 fnd_date.date_to_canonical(l_effective_fte_row.effective_start_date)
1180 );
1181 debug('l_effective_fte_row.effective_end_date:'||
1182 fnd_date.date_to_canonical(l_effective_fte_row.effective_end_date)
1183 );
1184 debug('l_effective_fte_row.value:'||l_effective_fte_row.value);
1185 debug('ROUND(p_fte_value,5):'||ROUND(p_fte_value,5));
1186 debug_exit(l_proc_name);
1187 END IF;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 clear_cache;
1192 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1193 debug_others(l_proc_name,l_proc_step);
1194 IF g_debug THEN
1195 debug('Leaving: '||l_proc_name,-999);
1196 END IF;
1197 fnd_message.raise_error;
1198 ELSE
1199 RAISE;
1200 END IF;
1201 END set_fte_value;
1202 -- =====================================================================
1203 -- Name : Get_FTE_Value
1204 -- Purpose : Query FTE value from database.
1205 -- Returns : FTE
1206 -- -------------------------------------------------------------------
1207 FUNCTION get_fte_value
1208 (p_assignment_id IN NUMBER
1209 ,p_calculation_date IN DATE
1210 ) RETURN NUMBER
1211 IS
1212
1213 l_proc_step NUMBER(20,10):=0;
1214 l_proc_name VARCHAR2(61):=
1215 g_package_name||'get_fte_value';
1216
1217 l_effective_fte_row csr_effective_fte%ROWTYPE;
1218
1219
1220 BEGIN -- get_fte_value
1221
1222 IF NOT g_is_concurrent_program_run THEN
1223 g_debug := hr_utility.debug_enabled;
1224 END IF;
1225
1226 IF g_debug THEN
1227 debug_enter(l_proc_name);
1228 debug('p_assignment_id:'||p_assignment_id);
1229 debug('p_calculation_date:'||p_calculation_date);
1230 END IF;
1231
1232 OPEN csr_effective_fte(p_assignment_id,p_calculation_date);
1233 FETCH csr_effective_fte INTO l_effective_fte_row;
1234 CLOSE csr_effective_fte;
1235
1236 IF g_debug THEN
1237 debug('l_effective_fte_row.value:'||l_effective_fte_row.value);
1238 debug_exit(l_proc_name);
1239 END IF;
1240
1241 RETURN l_effective_fte_row.value;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 clear_cache;
1246 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1247 debug_others(l_proc_name,l_proc_step);
1248 IF g_debug THEN
1249 debug('Leaving: '||l_proc_name,-999);
1250 END IF;
1251 fnd_message.raise_error;
1252 ELSE
1253 RAISE;
1254 END IF;
1255 END get_fte_value;
1256 --
1257 --
1258 --
1259 PROCEDURE get_assignment_details
1260 (p_assignment_id IN NUMBER
1261 ,p_effective_date IN DATE
1262 ,p_assignment_details IN OUT NOCOPY csr_assignment_details%ROWTYPE
1263 )
1264 IS
1265 l_proc_step NUMBER(20,10):=0;
1266 l_proc_name VARCHAR2(61):= 'get_assignment_details';
1267 l_assignment_details csr_assignment_details%ROWTYPE;
1268
1269 BEGIN -- get_assignment_details
1270
1271 IF g_debug THEN
1272 debug_enter(l_proc_name);
1273 debug('p_assignment_id:'||p_assignment_id);
1274 debug('p_effective_date:'||p_effective_date);
1275 debug('p_assignment_details.business_group_id:'||p_assignment_details.business_group_id);
1276 debug('p_assignment_details.normal_hours:'||p_assignment_details.normal_hours);
1277 debug('p_assignment_details.frequency:'||p_assignment_details.frequency);
1278 END IF;
1279
1280 OPEN csr_assignment_details(p_assignment_id,p_effective_date);
1281 FETCH csr_assignment_details INTO p_assignment_details;
1282 IF csr_assignment_details%NOTFOUND THEN
1283 l_proc_step := 10;
1284 IF g_debug THEN
1285 debug(l_proc_name,l_proc_step);
1286 END IF;
1287 p_assignment_details := l_assignment_details; -- empty it
1288 ELSE
1289 l_proc_step := 15;
1290 IF g_debug THEN
1291 debug(l_proc_name,l_proc_step);
1292 END IF;
1293 END IF;
1294 CLOSE csr_assignment_details;
1295
1296 IF p_assignment_details.normal_hours IS NULL
1297 OR
1298 p_assignment_details.frequency IS NULL
1299 THEN
1300 l_proc_step := 20;
1301 IF g_debug THEN
1302 debug(l_proc_name,l_proc_step);
1303 END IF;
1304 IF g_is_concurrent_program_run
1305 OR
1306 is_fte_enabled(p_assignment_id)
1307 THEN
1308 fnd_message.set_name('PQP','PQP_230456_FTE_NO_ASG_DETAILS');
1309 fnd_message.set_token
1310 ('EFFECTIVEDATE'
1311 ,fnd_date.date_to_displaydate(p_effective_date)
1312 );
1313 fnd_message.raise_error;
1314 END IF;
1315 END IF;
1316
1317 IF g_debug THEN
1318 debug('p_assignment_details.business_group_id:'||p_assignment_details.business_group_id);
1319 debug('p_assignment_details.normal_hours:'||p_assignment_details.normal_hours);
1320 debug('p_assignment_details.frequency:'||p_assignment_details.frequency);
1321 debug_exit(l_proc_name);
1322 END IF;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 p_assignment_details := l_assignment_details; -- nocopy
1327 clear_cache;
1328 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1329 debug_others(l_proc_name,l_proc_step);
1330 IF g_debug THEN
1331 debug('Leaving: '||l_proc_name,-999);
1332 END IF;
1333 fnd_message.raise_error;
1334 ELSE
1335 RAISE;
1336 END IF;
1337 END get_assignment_details;
1338 --
1339 --
1340 --
1341 PROCEDURE get_contract_details
1342 (p_assignment_id IN NUMBER
1343 ,p_effective_date IN DATE
1344 ,p_contract_details IN OUT NOCOPY csr_contract_details%ROWTYPE
1345 )
1346 IS
1347 l_proc_step NUMBER(20,10):=0;
1348 l_proc_name VARCHAR2(61):= 'get_contract_details';
1349 l_contract_details csr_contract_details%ROWTYPE;
1350 l_assignment_contract csr_assignment_contract%ROWTYPE;
1351
1352 l_pqp_contract_table_id pay_user_tables.user_table_id%TYPE;
1353 l_annual_hours_col_id pay_user_columns.user_column_id%TYPE;
1354 l_period_divisor_col_id pay_user_columns.user_column_id%TYPE;
1355
1356 BEGIN -- get_contract_details
1357
1358 IF g_debug THEN
1359 debug_enter(l_proc_name);
1360 debug('p_assignment_id:'||p_assignment_id);
1361 debug('p_effective_date:'||p_effective_date);
1362 debug('p_contract_details.annual_hours:'||p_contract_details.annual_hours);
1363 debug('p_contract_details.period_divisor:'||p_contract_details.period_divisor);
1364 END IF;
1365
1366 IF g_not_cached_constants THEN
1367 load_cached_constants;
1368 END IF;
1369
1370 l_pqp_contract_table_id := g_pqp_contract_table_id;
1371 l_annual_hours_col_id := g_annual_hours_col_id;
1372 l_period_divisor_col_id := g_period_divisor_col_id;
1373
1374 IF g_debug THEN
1375 debug('l_pqp_contract_table_id:'||l_pqp_contract_table_id);
1376 debug('l_annual_hours_col_id:'||l_annual_hours_col_id);
1377 debug('l_period_divisor_col_id:'||l_period_divisor_col_id);
1378 END IF;
1379
1380 OPEN csr_assignment_contract
1381 (p_assignment_id
1382 ,p_effective_date
1383 ,l_pqp_contract_table_id
1384 );
1385 FETCH csr_assignment_contract INTO l_assignment_contract;
1386 IF csr_assignment_contract%FOUND
1387 THEN
1388
1389 l_proc_step := 10;
1390 IF g_debug THEN
1391 debug(l_proc_name,l_proc_step);
1392 debug('l_assignment_contract.user_row_id:'||l_assignment_contract.user_row_id);
1393 END IF;
1394
1395 IF l_assignment_contract.user_row_id IS NOT NULL
1396 THEN
1397 p_contract_details.contract_type := l_assignment_contract.contract_type;
1398 p_contract_details.user_row_id := l_assignment_contract.user_row_id;
1399
1400
1401 l_proc_step := 20;
1402 IF g_debug THEN
1403 debug(l_proc_name,l_proc_step);
1404 END IF;
1405
1406 OPEN csr_get_contract_value
1407 (l_annual_hours_col_id
1408 ,l_assignment_contract.user_row_id
1409 ,p_effective_date
1410 );
1411 FETCH csr_get_contract_value INTO p_contract_details.annual_hours;
1412 IF csr_get_contract_value%NOTFOUND THEN
1413 p_contract_details := l_contract_details; -- empty
1414 l_proc_step := 25;
1415 IF g_debug THEN
1416 debug(l_proc_name,l_proc_step);
1417 END IF;
1418 END IF;
1419 CLOSE csr_get_contract_value;
1420
1421 l_proc_step := 30;
1422 IF g_debug THEN
1423 debug(l_proc_name,l_proc_step);
1424 END IF;
1425
1426 OPEN csr_get_contract_value
1427 (l_period_divisor_col_id
1428 ,l_assignment_contract.user_row_id
1429 ,p_effective_date
1430 );
1431 FETCH csr_get_contract_value INTO p_contract_details.period_divisor;
1432 IF csr_get_contract_value%NOTFOUND
1433 THEN
1434 p_contract_details := l_contract_details; -- empty
1435 l_proc_step := 35;
1436 IF g_debug THEN
1437 debug(l_proc_name,l_proc_step);
1438 END IF;
1439 END IF;
1440 CLOSE csr_get_contract_value;
1441
1442 END IF; -- IF l_assignment_contract.user_row_id IS NOT NULL
1443 ELSE
1444
1445 p_contract_details := l_contract_details; -- empty
1446
1447 l_proc_step := 40;
1448 IF g_debug THEN
1449 debug(l_proc_name,l_proc_step);
1450 END IF;
1451
1452 END IF; -- IF csr_assignment_contract%FOUND
1453 CLOSE csr_assignment_contract;
1454
1455 IF p_contract_details.annual_hours IS NULL
1456 OR
1457 p_contract_details.period_divisor IS NULL
1458 THEN
1459 l_proc_step := 50;
1460 IF g_debug THEN
1461 debug(l_proc_name,l_proc_step);
1462 END IF;
1463 IF g_is_concurrent_program_run
1464 OR
1465 is_fte_enabled(p_assignment_id)
1466 THEN
1467 IF l_assignment_contract.contract_type IS NOT NULL
1468 THEN
1469 fnd_message.set_name('PQP','PQP_230457_FTE_NO_CTR_DETAILS');
1470 fnd_message.set_token
1471 ('CONTRACTTYPE'
1472 ,l_assignment_contract.contract_type
1473 );
1474
1475 fnd_message.set_token
1476 ('EFFECTIVEDATE'
1477 ,fnd_date.date_to_displaydate(p_effective_date)
1478 );
1479 fnd_message.raise_error;
1480 ELSE
1481 fnd_message.set_name('PQP','PQP_230113_AAT_MISSING_CONTRCT');
1482 fnd_message.set_token
1483 ('EFFECTIVEDATE'
1484 ,fnd_date.date_to_displaydate(p_effective_date)
1485 );
1486 fnd_message.raise_error;
1487 END IF;
1488 END IF;
1489 END IF; -- IF p_contract_details.annual_hours IS NULL
1490
1491 IF g_debug THEN
1492 debug('p_contract_details.annual_hours:'||p_contract_details.annual_hours);
1493 debug('p_contract_details.period_divisor:'||p_contract_details.period_divisor);
1494 debug_exit(l_proc_name);
1495 END IF;
1496
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 p_contract_details := l_contract_details; -- empty for nocopy
1500 clear_cache;
1501 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1502 debug_others(l_proc_name,l_proc_step);
1503 IF g_debug THEN
1504 debug('Leaving: '||l_proc_name,-999);
1505 END IF;
1506 fnd_message.raise_error;
1507 ELSE
1508 RAISE;
1509 END IF;
1510 END get_contract_details;
1511 --
1512 --
1513 -- procedure to calculate and store FTE called in a loop from the main
1514 PROCEDURE calculate_and_store_fte
1515 (p_assignment_id NUMBER
1516 ,p_effective_date DATE
1517 )
1518 IS
1519
1520 l_proc_name VARCHAR2(61):= 'calculate_and_store_fte';
1521 l_proc_step NUMBER:=0;
1522 l_FTE_value NUMBER;
1523
1524 l_assignment_details csr_assignment_details%ROWTYPE;
1525 l_contract_details csr_contract_details%ROWTYPE;
1526 BEGIN -- calculate_and_store_fte
1527
1528
1529 IF g_debug THEN
1530 debug_enter(l_proc_name);
1531 debug('p_assignment_id:'||p_assignment_id);
1532 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
1533 END IF;
1534
1535 -- fetch the relevant assignment details normal hours and contract
1536
1537 -- get_assignment_normal_hours_and_frequency
1538 -- use out paramater to capitalize on nocopy of output parameters
1539
1540 get_assignment_details
1541 (p_assignment_id
1542 ,p_effective_date
1543 ,l_assignment_details
1544 );
1545
1546 IF g_is_concurrent_program_run THEN
1547 g_output_file_records(g_output_file_records.LAST).normal_hours:=
1548 l_assignment_details.normal_hours;
1549
1550 g_output_file_records(g_output_file_records.LAST).frequency:=
1551 l_assignment_details.frequency;
1552 END IF;
1553
1554 l_proc_step :=10;
1555 IF g_debug THEN
1556 debug(l_proc_name, l_proc_step);
1557 END IF;
1558
1559 get_contract_details
1560 (p_assignment_id
1561 ,p_effective_date
1562 ,l_contract_details
1563 );
1564
1565 IF g_is_concurrent_program_run THEN
1566 g_output_file_records(g_output_file_records.LAST).contract_type:=
1567 l_contract_details.contract_type;
1568
1569 g_output_file_records(g_output_file_records.LAST).annual_hours:=
1570 l_contract_details.annual_hours;
1571
1572 g_output_file_records(g_output_file_records.LAST).period_divisor:=
1573 l_contract_details.period_divisor;
1574 END IF;
1575
1576 l_proc_step :=20;
1577 IF g_debug THEN
1578 debug('l_contract_details.annual_hours:'||l_contract_details.annual_hours);
1579 debug('l_contract_details.period_divisor:'||l_contract_details.period_divisor);
1580 debug(l_proc_name,l_proc_step);
1581 END IF;
1582
1583 IF l_assignment_details.frequency <> 'Y'
1584 THEN
1585 l_fte_value :=
1586 l_assignment_details.normal_hours /
1587 ( l_contract_details.annual_hours / l_contract_details.period_divisor );
1588 ELSE
1589 l_fte_value := l_assignment_details.normal_hours / l_contract_details.annual_hours;
1590 END IF; -- IF l_assignment_details.frequency = 'Y' THEN
1591
1592 IF g_is_concurrent_program_run THEN
1593 g_output_file_records(g_output_file_records.LAST).FTE_new_value:=
1594 l_fte_value;
1595 END IF;
1596
1597 IF l_fte_value IS NOT NULL
1598 THEN
1599 l_proc_step :=35;
1600 IF g_debug THEN
1601 debug(l_proc_name,l_proc_step);
1602 debug('l_FTE_value:'||l_FTE_value);
1603 END IF;
1604 set_fte_value
1605 (p_assignment_id => p_assignment_id
1606 ,p_business_group_id => l_assignment_details.business_group_id
1607 ,p_calculation_date => p_effective_date
1608 ,p_FTE_value => l_FTE_value
1609 );
1610
1611 IF g_debug THEN
1612 hr_utility.trace(
1613 'Updated, '||p_assignment_id||', '||
1614 fnd_date.date_to_displaydate(p_effective_date)||', '||
1615 l_FTE_value||', '||
1616 l_assignment_details.normal_hours||', '||
1617 -- l_assignment_details.contract_type||', '||
1618 l_contract_details.annual_hours||', '||
1619 l_contract_details.period_divisor
1620 );
1621 END IF;
1622
1623 ELSE
1624
1625 IF g_is_concurrent_program_run THEN
1626 g_output_file_records(g_output_file_records.LAST).change_type:= 'Not Known';
1627 g_output_file_records(g_output_file_records.LAST).status:= 'Errored(Skipped)';
1628 END IF;
1629
1630 END IF;
1631
1632 IF g_debug THEN
1633 debug_exit(l_proc_name);
1634 END IF;
1635
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 clear_cache;
1639 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1640 debug_others(l_proc_name,l_proc_step);
1641 IF g_debug THEN
1642 debug('Leaving: '||l_proc_name,-999);
1643 END IF;
1644 fnd_message.raise_error;
1645 ELSE
1646 RAISE;
1647 END IF;
1648 END calculate_and_store_fte;
1649 --
1650 --
1651 PROCEDURE update_fte_for_assignment
1652 (p_assignment_id NUMBER
1653 ,p_effective_date DATE
1654 )
1655 IS
1656
1657 l_proc_name VARCHAR2(61):=
1658 g_package_name||'update_fte_for_assignment';
1659 l_proc_step NUMBER(20,10):=0;
1660
1661 i BINARY_INTEGER:=0;
1662 l_max_iterations BINARY_INTEGER:= 10000;
1663
1664 l_pqp_contract_table_id pay_user_tables.user_table_id%TYPE;
1665 l_annual_hours_col_id pay_user_columns.user_column_id%TYPE;
1666 l_period_divisor_col_id pay_user_columns.user_column_id%TYPE;
1667
1668
1669
1670 -- there is no need for the change type column in the following cursor
1671 -- it gives rise to have logic to eliminate duplicate dates to be implemented in loop
1672 -- as we use UNION ALL as opposed to UNION
1673 -- but when debugging , knowing the change type that was relevant can speed up investigation
1674 CURSOR csr_get_relevant_change_dates
1675 (p_assignment_id IN NUMBER
1676 ,p_min_effective_start_date IN DATE
1677 ,p_pqp_contract_table_id IN NUMBER
1678 ,p_annual_hours_col_id IN NUMBER
1679 ,p_period_divisor_col_id IN NUMBER
1680 ) IS
1681 SELECT 'Normal Hours' change_type,asg2.effective_start_date
1682 FROM per_all_assignments_f asg1
1683 ,per_all_assignments_f asg2
1684 WHERE asg1.assignment_id = p_assignment_id
1685 AND ( asg1.effective_start_date >= p_min_effective_start_date
1686 OR
1687 p_min_effective_start_date
1688 BETWEEN asg1.effective_start_date
1689 AND asg1.effective_end_date
1690 )
1691 AND asg2.assignment_id = asg1.assignment_id
1692 AND asg2.effective_start_date = asg1.effective_end_date+1
1693 AND NVL(asg2.normal_hours,-1) <> NVL(asg1.normal_hours,-2)
1694 UNION ALL
1695 SELECT 'Assignment Contract' change_type,aat2.effective_start_date
1696 FROM pqp_assignment_attributes_f aat1
1697 ,pqp_assignment_attributes_f aat2
1698 WHERE aat1.assignment_id = p_assignment_id
1699 AND ( aat1.effective_start_date >= p_min_effective_start_date
1700 OR
1701 p_min_effective_start_date
1702 BETWEEN aat1.effective_start_date
1703 AND aat1.effective_end_date
1704 )
1705 AND aat1.assignment_id = aat2.assignment_id
1706 AND aat2.effective_start_date = aat1.effective_end_date+1
1707 AND NVL(aat2.contract_type,'{null}') <> NVL(aat1.contract_type,'[NULL]')
1708 UNION ALL
1709 SELECT 'Contract Type' change_type,inst2.effective_start_date
1710 FROM pqp_assignment_attributes_f aat
1711 ,pay_user_rows_f pur
1712 ,pay_user_column_instances_f inst1
1713 ,pay_user_column_instances_f inst2
1714 WHERE aat.assignment_id = p_assignment_id
1715 AND ( aat.effective_start_date >= p_min_effective_start_date
1716 OR
1717 p_min_effective_start_date
1718 BETWEEN aat.effective_start_date
1719 AND aat.effective_end_date
1720 )
1721 AND pur.user_table_id = p_pqp_contract_table_id
1722 AND pur.business_group_id = aat.business_group_id
1723 AND pur.row_low_range_or_name = aat.contract_type
1724 AND aat.effective_start_date
1725 BETWEEN pur.effective_start_date
1726 AND pur.effective_end_date
1727 AND inst1.user_column_id IN
1728 (p_annual_hours_col_id
1729 ,p_period_divisor_col_id
1730 )
1731 AND ( inst1.effective_start_date >= p_min_effective_start_date
1732 OR
1733 p_min_effective_start_date
1734 BETWEEN inst1.effective_start_date
1735 AND inst1.effective_end_date
1736 )
1737 AND inst1.user_row_id = pur.user_row_id
1738 AND inst2.user_column_instance_id = inst1.user_column_instance_id
1739 AND inst2.effective_start_date = inst1.effective_end_date+1
1740 AND NVL(inst2.value,'{null}') <> NVL(inst1.value,'~NULL~')
1741 ORDER BY 2 ASC;
1742
1743
1744 l_last_change_date DATE;
1745 l_relevant_change csr_get_relevant_change_dates%ROWTYPE;
1746 l_status VARCHAR2(30);
1747 l_fte_exists BOOLEAN;
1748 l_earliest_possible_FTE_date DATE;
1749 l_effective_date DATE;
1750
1751 BEGIN -- update_fte_for_assignment
1752
1753
1754 IF NOT g_is_concurrent_program_run THEN
1755 g_debug := hr_utility.debug_enabled;
1756 END IF;
1757
1758 IF g_debug THEN
1759 debug_enter(l_proc_name);
1760 debug('p_assignment_id:'||p_assignment_id);
1761 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
1762 IF g_is_concurrent_program_run = TRUE THEN
1763 debug('g_is_concurrent_program_run:TRUE');
1764 END IF;
1765 IF g_is_concurrent_program_run = FALSE THEN
1766 debug('g_is_concurrent_program_run:FALSE');
1767 END IF;
1768 IF g_is_concurrent_program_run IS NULL THEN
1769 debug('g_is_concurrent_program_run:IS NULL');
1770 END IF;
1771 END IF;
1772
1773 l_status := get_installation_status(801);
1774
1775 IF l_status = 'I' THEN -- do nothing unless payroll installed
1776
1777 IF g_not_cached_constants THEN
1778 load_cached_constants;
1779 ELSE
1780 IF g_debug THEN
1781 debug('g_not_cached_constants:FALSE');
1782 END IF;
1783 END IF;
1784
1785 l_pqp_contract_table_id := g_pqp_contract_table_id;
1786 l_annual_hours_col_id := g_annual_hours_col_id;
1787 l_period_divisor_col_id := g_period_divisor_col_id;
1788
1789 IF g_is_concurrent_program_run THEN
1790 g_output_file_records(g_output_file_records.LAST).effective_date := p_effective_date;
1791 g_output_file_records(g_output_file_records.LAST).change_in := 'Initial';
1792 END IF;
1793
1794 -- force a cache reload of this check as
1795 -- we cannot be sure that the underlying assignment
1796 -- data has not changed between two calls to
1797 -- update_fte_for_assignment when being invoked
1798 -- from forms
1799
1800 IF NOT g_is_concurrent_program_run
1801 THEN
1802
1803 l_fte_exists := chk_fte_exists(p_assignment_id,TRUE);
1804
1805 l_earliest_possible_FTE_date :=
1806 get_earliest_possible_FTE_date(p_assignment_id,TRUE);
1807 -- if earliest possible date is null
1808 -- it implies
1809 -- a) it will not be possible to calculate FTE for this asg
1810 -- b) he has either never had any normal hours recorded against his asg
1811 -- c) or he has never had any contract type on extra details of service
1812 -- in which case its no use processing further
1813 -- if however there do exist an FTE row they have either been manually created
1814 -- or the user has performed an operation such a nulling out the contract type
1815 -- or a purge which has led to earliest processing date being null
1816 -- in which case we do want to allow FTE processing to take place, error
1817 -- (converted to message) and allow the overall operation to succeeed
1818 -- so that the user is aware that his action has resulted in
1819 -- the FTE not being changed
1820
1821 IF l_fte_exists
1822 THEN
1823 IF p_effective_date IS NULL -- e.g. when a purge takes place in online mode
1824 THEN
1825 IF l_earliest_possible_FTE_date IS NULL THEN
1826 l_effective_date := HR_GBNICAR.NICAR_SESSION_DATE(0);
1827 ELSE
1828 l_effective_date := l_earliest_possible_FTE_date;
1829 END IF;
1830 -- if at this point l_effective_date is still null
1831 -- it implies
1832 ELSE
1833 l_effective_date := p_effective_date;
1834 END IF;
1835 -- if p_effective_date is > earliest processing date
1836 -- the expectation that processing will complet normally
1837 -- if the p_eff_date < earliest process date
1838 -- then the processing will fail to update an FTE
1839 -- unlike a conc pgm rum we do not change the processing
1840 -- date at this stage in order to allow it to proceed and
1841 -- error
1842 ELSE
1843 IF l_earliest_possible_FTE_date < p_effective_date THEN
1844 l_effective_date := l_earliest_possible_FTE_date;
1845 ELSE
1846 l_effective_date := p_effective_date; -- only because its not conc run
1847 END IF;
1848 -- it is possible at this stage for earliest processing to be null
1849 -- in which case the subsequent check for l_eff being not null
1850 -- will prevent any processing from taking place and raising no errors
1851 -- which is ok becase that should only happen when an employee
1852 -- has never had any FTE and neither has sufficent data for FTE to
1853 -- be calculated
1854 END IF; -- IF NOT l_fte_exists THEN
1855
1856 ELSE
1857
1858 l_effective_date := p_effective_date;
1859
1860 END IF; -- IF NOT g_is_concurrent_program_run
1861
1862 IF g_debug THEN
1863 debug('l_effective_date:'||fnd_date.date_to_displaydate(l_effective_date));
1864 END IF;
1865
1866 IF l_effective_date IS NOT NULL
1867 THEN
1868
1869 -- create the first FTE row
1870 calculate_and_store_fte
1871 (p_assignment_id
1872 ,l_effective_date
1873 );
1874
1875 -- iterate thru relevant dates for assignment normal hours and contract changes
1876 l_last_change_date := hr_api.g_eot;
1877 FOR this_change IN csr_get_relevant_change_dates
1878 (p_assignment_id => p_assignment_id
1879 ,p_min_effective_start_date => l_effective_date
1880 ,p_pqp_contract_table_id => l_pqp_contract_table_id
1881 ,p_annual_hours_col_id => l_annual_hours_col_id
1882 ,p_period_divisor_col_id => l_period_divisor_col_id
1883 )
1884 LOOP
1885
1886 l_relevant_change := this_change;
1887
1888 IF g_is_concurrent_program_run THEN
1889 IF g_debug THEN
1890 debug('In the Debug -1: ');
1891 END IF;
1892 g_output_file_records(g_output_file_records.LAST+1).assignment_id := p_assignment_id;
1893 g_output_file_records(g_output_file_records.LAST).employee_number :=
1894 g_output_file_records(g_output_file_records.LAST-1).employee_number;
1895 g_output_file_records(g_output_file_records.LAST).assignment_number :=
1896 g_output_file_records(g_output_file_records.LAST-1).assignment_number;
1897 g_output_file_records(g_output_file_records.LAST).effective_date := this_change.effective_start_date;
1898 g_output_file_records(g_output_file_records.LAST).change_in := this_change.change_type;
1899 END IF;
1900
1901
1902 IF g_debug THEN
1903 debug('this_change.effective_start_date:'||this_change.effective_start_date);
1904 debug('this_change.change_type:'||this_change.change_type);
1905 debug('l_last_change_date:'||l_last_change_date);
1906 END IF;
1907
1908 IF this_change.effective_start_date <> l_last_change_date THEN
1909 IF g_debug THEN
1910 debug('In the Debug -2 This_change.effective_start_date:-'||this_change.effective_start_date);
1911 debug('In the Debug -3 l_last_change_date:-'||l_last_change_date);
1912 END IF;
1913 l_last_change_date := this_change.effective_start_date;
1914 calculate_and_store_fte
1915 (p_assignment_id
1916 ,this_change.effective_start_date
1917 );
1918 ELSE
1919 BEGIN -- For bug 5531482
1920 IF g_debug THEN
1921 hr_utility.set_location('In the Debug -4 This_change.effective_start_date:-'||this_change.effective_start_date,70);
1922 hr_utility.set_location('In the Debug -5 l_last_change_date:-'||l_last_change_date,80);
1923 END IF;
1924 g_output_file_records(g_output_file_records.LAST) :=
1925 g_output_file_records(g_output_file_records.LAST-1);
1926 g_output_file_records(g_output_file_records.LAST).change_in :=
1927 this_change.change_type;
1928 g_output_file_records(g_output_file_records.LAST).status :=
1929 'Processed(Skipped)';
1930 g_output_file_records(g_output_file_records.LAST).message :=
1931 'Processing was skipped as this change effective the same date as the previous record.';
1932 EXCEPTION
1933 when VALUE_ERROR then
1934 hr_utility.set_location('In the Debug -6 VALUE_ERROR',90);
1935 null;
1936 when others then
1937 hr_utility.set_location('In the Debug -7 OTHERS',100);
1938 hr_utility.set_location('In the Debug -8:'||sqlerrm,110);
1939 Raise;
1940 END; -- End For bug 5531482
1941 END IF;
1942 END LOOP; --FOR this_change IN csr_get_relevant_change_dates
1943
1944 END IF; -- IF l_effective_date IS NOT NULL
1945
1946 END IF; -- IF l_status = 'I' THEN
1947
1948 IF g_debug THEN
1949 debug('l_status:'||l_status);
1950 debug_exit(l_proc_name);
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 clear_cache;
1956 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1957 debug_others(l_proc_name,l_proc_step);
1958 IF g_debug THEN
1959 debug('Leaving: '||l_proc_name,-999);
1960 END IF;
1961 fnd_message.raise_error;
1962 ELSE
1963 RAISE;
1964 END IF;
1965 END update_fte_for_assignment;
1966 --
1967 /* =====================================================================
1968 Name : Update_FTE_For Assignment_Set
1969 Purpose : Update multiple FTE values. Normally called as a
1970 concurrent process.
1971 Returns :
1972 ---------------------------------------------------------------------*/
1973 PROCEDURE update_fte_for_assignment_set
1974 (ERRBUF OUT NOCOPY VARCHAR2
1975 ,RETCODE OUT NOCOPY NUMBER
1976 ,p_contract_type IN VARCHAR2
1977 ,p_payroll_id IN NUMBER
1978 ,p_calculation_date IN VARCHAR2
1979 ,p_trace IN VARCHAR2
1980 )
1981 IS
1982
1983 l_proc_step NUMBER(20,10):=0;
1984 l_proc_name VARCHAR2(61) :=
1985 g_package_name||'update_fte_for_assignment_set';
1986
1987 l_calculation_date DATE;
1988 l_error VARCHAR2(2000);
1989 l_message VARCHAR2(240);
1990 l_full_name VARCHAR2(80);
1991 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1992 l_contract_type pay_user_rows_f.row_low_range_or_name%TYPE;
1993 i BINARY_INTEGER;
1994
1995 CURSOR csr_payroll_assignment_set
1996 (p_payroll_id NUMBER
1997 ,p_effective_date DATE
1998 )
1999 IS
2000 SELECT DISTINCT asg.assignment_id
2001 FROM per_all_assignments_f asg
2002 WHERE asg.payroll_id = p_payroll_id
2003 AND ( p_effective_date
2004 BETWEEN asg.effective_start_date AND asg.effective_end_date
2005 OR
2006 asg.effective_start_date > p_effective_date
2007 );
2008
2009
2010 CURSOR csr_contract_assignment_set
2011 (p_business_group_id NUMBER
2012 ,p_contract_type VARCHAR2
2013 ,p_effective_date DATE
2014 )
2015 IS
2016 SELECT DISTINCT aat.assignment_id
2017 FROM pqp_assignment_attributes_f aat
2018 WHERE aat.business_group_id = p_business_group_id
2019 AND aat.contract_type = NVL(p_contract_type,aat.contract_type)
2020 AND ( p_effective_date
2021 BETWEEN aat.effective_start_date AND aat.effective_end_date
2022 OR
2023 aat.effective_start_date > p_effective_date
2024 );
2025
2026
2027 CURSOR csr_payroll_and_contract
2028 (p_payroll_id NUMBER
2029 ,p_contract_type VARCHAR2
2030 ,p_effective_date DATE
2031 ) IS
2032 SELECT DISTINCT asg.assignment_id
2033 FROM per_all_assignments_f asg,
2034 pqp_assignment_attributes_f aat
2035 WHERE asg.payroll_id = p_payroll_id
2036 AND ( p_effective_date
2037 BETWEEN asg.effective_start_date AND asg.effective_end_date
2038 OR
2039 asg.effective_start_date > p_effective_date
2040 )
2041 AND aat.assignment_id = asg.assignment_id
2042 AND aat.contract_type = p_contract_type
2043 AND ( p_effective_date
2044 BETWEEN aat.effective_start_date AND aat.effective_end_date
2045 OR
2046 aat.effective_start_date > p_effective_date
2047 );
2048
2049 CURSOR csr_person_details
2050 (p_assignment_id NUMBER)
2051 IS
2052 SELECT per.full_name
2053 FROM per_all_people_f per,
2054 per_all_assignments_f asg
2055 WHERE asg.person_id = per.person_id
2056 AND asg.assignment_id = p_assignment_id
2057 AND l_calculation_date
2058 BETWEEN asg.effective_start_date AND asg.effective_end_date
2059 AND l_calculation_date
2060 BETWEEN per.effective_start_date AND per.effective_end_date;
2061
2062 l_assignment t_asg_details;
2063 l_FTE_processing_start_date DATE;
2064 l_errored BINARY_INTEGER:=0;
2065 l_processed BINARY_INTEGER:=0;
2066 l_log_string VARCHAR2(4000);
2067
2068 BEGIN -- update_fte_for_assignment_set
2069
2070 g_is_concurrent_program_run := TRUE;
2071
2072 g_debug := hr_utility.debug_enabled;
2073
2074 IF p_trace = 'Y' THEN
2075 g_debug := TRUE;
2076 END IF;
2077
2078 IF g_debug THEN
2079 debug_enter(l_proc_name);
2080 debug('p_payroll_id:'||p_payroll_id);
2081 debug('p_contract_type:'||p_contract_type);
2082 debug('p_calculation_date_txt:'||p_calculation_date);
2083 END IF;
2084
2085 fnd_file.put_line
2086 (fnd_file.log
2087 ,'Calculate FTE For Assignments - '||
2088 fnd_date.date_to_displaydate(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2089 );
2090
2091 -- Set the standard concurrent program out paramters
2092 ERRBUF:= NULL;
2093 RETCODE:= 0;
2094
2095 -- In concurrent programs, the date is passed in as a string in canonical format. We must convert this
2096 -- to a date
2097
2098 --l_calculation_date := to_date(substr(p_calculation_date, 1, 10), 'YYYY/MM/DD');
2099 l_calculation_date := fnd_date.canonical_to_date(p_calculation_date);
2100
2101 -- as business group id is not passed as a parameter use the fnd_global value
2102 -- this implies that concurrent process must be run from within apps
2103 -- and cannot be run from sql unless an explicit apps initialization is done
2104 -- as a prereq step.
2105 -- we donot want to add the parameter as that implies a conc spec change
2106 -- which in turn will cause the patch size for this change to increase
2107 -- as it will force us to include/pre-req several
2108 l_business_group_id := fnd_global.per_business_group_id;
2109
2110 IF g_debug THEN
2111 debug('l_business_group_id:'||l_business_group_id);
2112 END IF;
2113
2114 l_log_string := NULL;
2115 SELECT name
2116 INTO l_log_string
2117 FROM per_business_groups_perf
2118 WHERE business_group_id = l_business_group_id;
2119
2120 fnd_file.put_line
2121 (fnd_file.log
2122 ,RPAD('Business Group',30,' ')||':'||l_log_string
2123 );
2124
2125 l_log_string := NULL;
2126 IF p_payroll_id IS NOT NULL
2127 THEN
2128 SELECT a.payroll_name
2129 INTO l_log_string
2130 FROM pay_all_payrolls_f a
2131 WHERE a.payroll_id = p_payroll_id
2132 AND effective_start_date =
2133 (SELECT MAX(b.effective_start_date)
2134 FROM pay_all_payrolls_f b
2135 WHERE b.payroll_id = a.payroll_id
2136 );
2137 END IF;
2138
2139 fnd_file.put_line
2140 (fnd_file.log
2141 ,RPAD('Payroll Name',30,' ')||':'||l_log_string
2142 );
2143
2144 fnd_file.put_line
2145 (fnd_file.log
2146 ,RPAD('Contract Type',30,' ')||':'||p_contract_type
2147 );
2148
2149 fnd_file.put_line
2150 (fnd_file.log
2151 ,RPAD('Effective On or After',30,' ')||':'||fnd_date.date_to_displaydate(l_calculation_date)
2152 );
2153
2154
2155 /* if P_Payroll_ID IS NULL AND P_Contract_Type IS NULL then
2156 fnd_message.set_name('PQP', 'PQP_230686_FTE_PROG_FAIL_PARAM');
2157 l_message := fnd_message.get;
2158 fnd_file.put_line(fnd_file.log, l_message);
2159 fnd_message.raise_error;
2160 */
2161
2162 IF (p_contract_type IS NOT NULL AND p_payroll_id IS NOT NULL)
2163 THEN
2164
2165 l_proc_step := 10;
2166 IF g_debug THEN
2167 debug(l_proc_name,l_proc_step);
2168 END IF;
2169
2170 OPEN csr_payroll_and_contract
2171 (p_payroll_id
2172 ,p_contract_type
2173 ,l_calculation_date
2174 );
2175 FETCH csr_payroll_and_contract BULK COLLECT INTO l_assignment;
2176 CLOSE csr_payroll_and_contract;
2177
2178 ELSIF (p_contract_type IS NULL AND p_payroll_id IS NOT NULL)
2179 THEN
2180
2181 l_proc_step := 20;
2182 IF g_debug THEN
2183 debug(l_proc_name,l_proc_step);
2184 END IF;
2185
2186 OPEN csr_payroll_assignment_set(p_payroll_id,l_calculation_date);
2187 FETCH csr_payroll_assignment_set BULK COLLECT INTO l_assignment;
2188 CLOSE csr_payroll_assignment_set;
2189
2190 ELSE
2191
2192 l_proc_step := 20;
2193 IF g_debug THEN
2194 debug(l_proc_name,l_proc_step);
2195 END IF;
2196
2197 --fetch data if both the contract type and payroll are null
2198 --or only when the contract type is not null
2199 OPEN csr_contract_assignment_set
2200 (l_business_group_id
2201 ,p_contract_type
2202 ,l_calculation_date
2203 );
2204 FETCH csr_contract_assignment_set BULK COLLECT INTO l_assignment;
2205 CLOSE csr_contract_assignment_set;
2206
2207 END IF;
2208
2209 l_proc_step := 30;
2210 IF g_debug THEN
2211 debug(l_proc_name,l_proc_step);
2212 END IF;
2213
2214
2215 DELETE FROM fnd_sessions WHERE session_id = USERENV('sessionid');
2216 INSERT INTO fnd_sessions
2217 (session_id
2218 ,effective_date
2219 )
2220 VALUES
2221 (USERENV('sessionid')
2222 ,l_calculation_date
2223 );
2224
2225 l_proc_step := 40;
2226 IF g_debug THEN
2227 debug(l_proc_name,l_proc_step);
2228 END IF;
2229
2230 fnd_file.put_line
2231 (fnd_file.log
2232 ,'Number of Assignments To Process: '||l_assignment.COUNT
2233 );
2234
2235
2236 fnd_file.put_line
2237 (fnd_file.log
2238 ,'Error Log:'
2239 );
2240
2241
2242 i := l_assignment.FIRST();
2243
2244 WHILE i IS NOT NULL
2245 LOOP
2246
2247 IF i = l_assignment.FIRST() THEN
2248 fnd_file.put_line
2249 (fnd_file.output
2250 ,RPAD('Status',30,' ')||g_column_separator||
2251 RPAD('Employee Number',20,' ')||g_column_separator||
2252 RPAD('Assignment_Number',30,' ')||g_column_separator||
2253 RPAD('Effective Date',15,' ')||g_column_separator||
2254 RPAD('Change In',30,' ')||g_column_separator||
2255 RPAD('FTE - Before Change',20,' ')||g_column_separator||
2256 RPAD('Change Type',15,' ')||g_column_separator||
2257 RPAD('FTE - After Change',20,' ')||g_column_separator||
2258 RPAD('Normal Hours',15,' ')||g_column_separator||
2259 RPAD('Frequency',10,' ')||g_column_separator||
2260 RPAD('Contract Type',30,' ')||g_column_separator||
2261 RPAD('Annual Hours',15,' ')||g_column_separator||
2262 RPAD('Period Divisor',15,' ')||g_column_separator||
2263 RPAD('Message',255,' ')
2264 );
2265
2266 fnd_file.put_line
2267 (fnd_file.output
2268 ,RPAD('-',30,'-')||g_column_separator||
2269 RPAD('-',20,'-')||g_column_separator||
2270 RPAD('-',30,'-')||g_column_separator||
2271 RPAD('-',15,'-')||g_column_separator||
2272 RPAD('-',30,'-')||g_column_separator||
2273 RPAD('-',20,'-')||g_column_separator||
2274 RPAD('-',15,'-')||g_column_separator||
2275 RPAD('-',20,'-')||g_column_separator||
2276 RPAD('-',15,'-')||g_column_separator||
2277 RPAD('-',10,'-')||g_column_separator||
2278 RPAD('-',30,'-')||g_column_separator||
2279 RPAD('-',15,'-')||g_column_separator||
2280 RPAD('-',15,'-')||g_column_separator||
2281 RPAD('-',255,'-')
2282 );
2283
2284 END IF;
2285
2286 l_proc_step := 40+i/100000;
2287 IF g_debug THEN
2288 debug(l_proc_name,l_proc_step);
2289 END IF;
2290
2291 --
2292 --
2293 -- Update assignments within new block. This way, if one
2294 -- assignment should error, we can still process the rest,
2295 -- whilst writing the error details to the concurrent
2296 -- manager log, if available.
2297 --
2298
2299 g_output_file_records.DELETE;
2300
2301 IF g_debug THEN
2302 debug('l_assignment(i):'||l_assignment(i));
2303 END IF;
2304
2305 g_output_file_records(i).assignment_id:= l_assignment(i);
2306
2307 l_proc_step := 45+i/100000;
2308 IF g_debug THEN
2309 debug(l_proc_name,l_proc_step);
2310 END IF;
2311
2312 BEGIN
2313
2314 -- if assignment has duplicate FTE rows, do a zap on both and then call
2315 -- get relevant date, it will return earliest possible
2316 -- not implemented above comment
2317
2318 l_proc_step := 50+(i/100000);
2319 IF g_debug THEN
2320 debug(l_proc_name,l_proc_step);
2321 END IF;
2322
2323 l_FTE_processing_start_date :=
2324 get_FTE_processing_start_date
2325 (p_assignment_id => l_assignment(i)
2326 ,p_effective_date => l_calculation_date
2327 );
2328
2329 l_proc_step := 60+(i/100000);
2330 IF g_debug THEN
2331 debug(l_proc_name,l_proc_step);
2332 END IF;
2333
2334 g_output_file_records(i).effective_date:= l_FTE_processing_start_date;
2335
2336 SELECT employee_number
2337 INTO g_output_file_records(i).employee_number
2338 FROM per_all_people_f a
2339 WHERE a.person_id =
2340 (SELECT asg.person_id
2341 FROM per_all_assignments_f asg
2342 WHERE asg.assignment_id = l_assignment(i)
2343 AND ROWNUM < 2
2344 )
2345 AND effective_start_date =
2346 (SELECT MAX(b.effective_start_date)
2347 FROM per_all_people_f b
2348 WHERE b.person_id = a.person_id
2349 );
2350
2351 l_proc_step := 70+(i/100000);
2352 IF g_debug THEN
2353 debug(l_proc_name,l_proc_step);
2354 END IF;
2355
2356 SELECT assignment_number
2357 INTO g_output_file_records(i).assignment_number
2358 FROM per_all_assignments_f a
2359 WHERE a.assignment_id = l_assignment(i)
2360 AND a.effective_start_date =
2361 (SELECT MAX(b.effective_start_date)
2362 FROM per_all_assignments_f b
2363 WHERE b.assignment_id = a.assignment_id
2364 );
2365
2366
2367 l_proc_step := 80+(i/100000);
2368 IF g_debug THEN
2369 debug(l_proc_name,l_proc_step);
2370 END IF;
2371
2372 IF l_FTE_processing_start_date IS NOT NULL
2373 THEN
2374
2375 update_fte_for_assignment
2376 (p_assignment_id => l_assignment(i)
2377 ,p_effective_date => l_FTE_processing_start_date
2378 );
2379
2380 ELSE
2381 g_output_file_records(i).status := 'Processed(Warning)';
2382 g_output_file_records(i).message :=
2383 'This person has no assignment normal hours or '||
2384 'an extra details of service contract type at any point in time.';
2385 END IF;
2386
2387 l_proc_step := 90+(i/100000);
2388 IF g_debug THEN
2389 debug(l_proc_name,l_proc_step);
2390 END IF;
2391
2392 write_output_file_records;
2393
2394 l_proc_step := 100+(i/100000);
2395 IF g_debug THEN
2396 debug(l_proc_name,l_proc_step);
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN OTHERS THEN
2401
2402 clear_cache;
2403
2404 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2405 debug_others(l_proc_name,l_proc_step);
2406 IF g_debug THEN
2407 debug('Leaving: '||l_proc_name,-999);
2408 END IF;
2409 g_output_file_records(g_output_file_records.LAST).status := 'Errored(Fatal)';
2410 g_output_file_records(g_output_file_records.LAST).message :=
2411 l_proc_name||'{'||
2412 fnd_number.number_to_canonical(l_proc_step)|| '}: '||
2413 SUBSTRB(SQLERRM, 1, 2000);
2414 ELSE
2415 g_output_file_records(g_output_file_records.LAST).status := 'Errored';
2416 g_output_file_records(g_output_file_records.LAST).message := SQLCODE||':'||SQLERRM;
2417 END IF;
2418
2419 l_errored := l_errored + 1;
2420
2421 IF l_errored = 1 THEN
2422
2423 fnd_file.put_line
2424 (fnd_file.log
2425 ,RPAD('Employee Number',15,' ')||g_column_separator||RPAD('Error Message',255,' ')
2426 );
2427
2428 fnd_file.put_line
2429 (fnd_file.log
2430 ,RPAD('-',15,'-')||g_column_separator||RPAD('-',255,'-')
2431 );
2432
2433 END IF;
2434
2435 fnd_file.put_line
2436 (fnd_file.log
2437 ,RPAD(NVL(g_output_file_records(g_output_file_records.LAST).employee_number
2438 ,'Asg_Id:'||l_assignment(i)
2439 )
2440 ,15,' '
2441 )||g_column_separator||
2442 RPAD(g_output_file_records(g_output_file_records.LAST).message,255,' ')
2443 );
2444
2445 write_output_file_records;
2446 g_output_file_records.DELETE; -- do not include in clear cache
2447
2448 END;
2449
2450 l_processed := l_processed + 1;
2451 i := l_assignment.NEXT(i);
2452
2453 IF g_debug THEN
2454 debug('NEXT i:'||i);
2455 END IF;
2456
2457 END LOOP;
2458
2459 DELETE FROM fnd_sessions WHERE session_id = USERENV('sessionid');
2460
2461 ERRBUF := null;
2462 RETCODE:= 0;
2463
2464 fnd_file.put_line
2465 (fnd_file.log
2466 ,'Number of Assignments Processed:'||l_processed
2467 );
2468
2469 fnd_file.put_line
2470 (fnd_file.log
2471 ,'Number of Assignments Errored:'||l_errored
2472 );
2473
2474
2475 IF g_debug THEN
2476 debug_exit(l_proc_name);
2477 END IF;
2478
2479 EXCEPTION
2480 WHEN OTHERS THEN
2481 clear_cache;
2482 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2483 debug_others(l_proc_name,l_proc_step);
2484 IF g_debug THEN
2485 debug('Leaving: '||l_proc_name,-999);
2486 END IF;
2487 fnd_message.raise_error;
2488 ELSE
2489 RAISE;
2490 END IF;
2491 END update_fte_for_assignment_set;
2492
2493 END pqp_fte_utilities;
[Home] [Help]