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