[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_SERVICE_HISTORY
Source
1 PACKAGE BODY pqp_gb_psi_service_history
2 -- /* $Header: pqpgbpsiser.pkb 120.36 2012/01/19 07:39:09 rpahune noship $ */
3 AS
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------------< debug >---------------------------------|
6 -- ----------------------------------------------------------------------------
7
8 PROCEDURE DEBUG(p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
9 IS
10 --
11 BEGIN
12 --
13
14 pqp_utilities.DEBUG(
15 p_trace_message => p_trace_message
16 ,p_trace_location => p_trace_location
17 );
18 --
19 END DEBUG;
20
21 -- This procedure is used for debug purposes
22 -- debug_enter checks the debug flag and sets the trace on/off
23 --
24 -- ----------------------------------------------------------------------------
25 -- |----------------------------< debug_enter >-------------------------------|
26 -- ----------------------------------------------------------------------------
27
28 PROCEDURE debug_enter(p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
29 IS
30 BEGIN
31 --
32 IF pqp_utilities.g_nested_level = 0
33 THEN
34 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
35 END IF;
36
37 -- g_nested_level := g_nested_level + 1;
38 -- debug('Entering: ' || NVL(p_proc_name, g_proc_name)
39 -- ,g_nested_level * 100);
40
41 pqp_utilities.debug_enter(p_proc_name => p_proc_name
42 ,p_trace_on => p_trace_on);
43 --
44 END debug_enter;
45
46 -- This procedure is used for debug purposes
47 --
48 -- ----------------------------------------------------------------------------
49 -- |----------------------------< debug_exit >--------------------------------|
50 -- ----------------------------------------------------------------------------
51
52 PROCEDURE debug_exit(p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
53 IS
54 BEGIN
55 --
56 -- DEBUG (
57 -- 'Leaving: '
58 -- || NVL (p_proc_name, g_proc_name),
59 -- -g_nested_level * 100
60 -- );
61 -- g_nested_level := g_nested_level
62 -- - 1;
63 pqp_utilities.debug_exit(p_proc_name => p_proc_name
64 ,p_trace_off => p_trace_off);
65
66 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
67 -- so we must turn it off for the same condition
68 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
69 IF pqp_utilities.g_nested_level = 0
70 THEN
71 hr_utility.trace_off;
72 END IF; -- (g_nested_level = 0
73
74 --
75 END debug_exit;
76
77 -- This procedure is used for debug purposes
78 --
79 -- ----------------------------------------------------------------------------
80 -- |----------------------------< debug_others >------------------------------|
81 -- ----------------------------------------------------------------------------
82
83 PROCEDURE debug_others(p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
84 IS
85 BEGIN
86 --
87 pqp_utilities.debug_others(p_proc_name => p_proc_name
88 ,p_proc_step => p_proc_step);
89 --
90 END debug_others;
91
92 -- This procedure is used to clear all cached global variables
93 --
94 -- ----------------------------------------------------------------------------
95 -- |----------------------------< clear_cache >-------------------------------|
96 -- ----------------------------------------------------------------------------
97 PROCEDURE clear_cache
98 IS
99 --
100 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
101 l_proc_step PLS_INTEGER;
102 --
103 BEGIN
104 --
105 IF g_debug
106 THEN
107 l_proc_step := 10;
108 debug_enter(l_proc_name);
109 END IF;
110
111 -- Clear all global variables first
112 g_business_group_id := NULL;
113 g_effective_date := NULL;
114 g_extract_type := NULL;
115 g_paypoint := NULL;
116 g_cutover_date := NULL;
117 g_ext_dfn_id := NULL;
118 g_start_reason := NULL;
119 g_scheme_category := NULL;
120 g_scheme_status := NULL;
121 g_opt_in := NULL;
122 g_opt_out := NULL;
123 g_active_asg_sts_id := NULL;
124 g_terminate_asg_sts_id := NULL;
125 g_prev_pay_proc_evnts := NULL;
126 -- Clear all global collections
127 g_tab_event_map_cv.DELETE;
128 g_tab_abs_types.DELETE;
129 g_tab_asg_status.DELETE;
130 g_tab_pen_sch_map_cv.DELETE;
131 g_tab_pen_ele_ids.DELETE;
132 g_tab_prs_dfn_cv.DELETE;
133 g_tab_dated_table.DELETE;
134 g_tab_lvrsn_map_cv.DELETE;
135
136 IF g_debug
137 THEN
138 debug_exit(l_proc_name);
139 END IF;
140 EXCEPTION
141 WHEN OTHERS
142 THEN
143 IF SQLCODE <> hr_utility.hr_error_number
144 THEN
145 debug_others(l_proc_name, l_proc_step);
146
147 IF g_debug
148 THEN
149 DEBUG('Leaving: ' || l_proc_name, -999);
150 END IF;
151
152 fnd_message.raise_error;
153 ELSE
154 RAISE;
155 END IF;
156 END clear_cache;
157
158 -- This procedure is used to clear all cached assignment variables
159 --
160 -- ----------------------------------------------------------------------------
161 -- |----------------------------< clear_per_cache >---------------------------|
162 -- ----------------------------------------------------------------------------
163 PROCEDURE clear_per_cache
164 IS
165 --
166 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_per_cache';
167 l_proc_step PLS_INTEGER;
168 --
169 BEGIN
170 --
171 IF g_debug
172 THEN
173 l_proc_step := 10;
174 debug_enter(l_proc_name);
175 END IF;
176
177 g_event_counter := ben_ext_person.g_pay_proc_evt_tab.FIRST;
178 g_min_effective_date := NULL;
179 g_min_eff_date_exists := 'N';
180
181 IF g_debug
182 THEN
183 debug_exit(l_proc_name);
184 END IF;
185 EXCEPTION
186 WHEN OTHERS
187 THEN
188 IF SQLCODE <> hr_utility.hr_error_number
189 THEN
190 debug_others(l_proc_name, l_proc_step);
191
192 IF g_debug
193 THEN
194 DEBUG('Leaving: ' || l_proc_name, -999);
195 END IF;
196
197 fnd_message.raise_error;
198 ELSE
199 RAISE;
200 END IF;
201 END clear_per_cache;
202
203 -- This function returns input value id for a given element type id
204 -- and input value name
205
206
207 -- This procedure is used to fetch event map configuration values for
208 -- this business group setup
209 -- ----------------------------------------------------------------------------
210 -- |----------------------------< get_input_value_id >------------------------|
211 -- ----------------------------------------------------------------------------
212 FUNCTION get_input_value_id(
213 p_element_type_id IN NUMBER
214 ,p_effective_date IN DATE
215 ,p_input_value_name IN VARCHAR2
216 )
217 RETURN NUMBER
218 IS
219 --
220 -- Cursor to get input value id
221 CURSOR csr_get_iv_id
222 IS
223 SELECT input_value_id
224 FROM pay_input_values_f
225 WHERE element_type_id = p_element_type_id
226 AND NAME = p_input_value_name
227 AND p_effective_date BETWEEN effective_start_date
228 AND effective_end_date;
229
230 l_proc_name VARCHAR2(80) := g_proc_name || 'get_input_value_id';
231 l_proc_step PLS_INTEGER;
232 l_input_value_id NUMBER;
233 --
234 BEGIN
235 --
236 IF g_debug
237 THEN
238 l_proc_step := 10;
239 debug_enter(l_proc_name);
240 DEBUG('p_element_type_id: ' || p_element_type_id);
241 DEBUG('p_effective_date: '
242 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
243 DEBUG('p_input_value_name: ' || p_input_value_name);
244 END IF;
245
246 OPEN csr_get_iv_id;
247 FETCH csr_get_iv_id INTO l_input_value_id;
248 CLOSE csr_get_iv_id;
249
250 IF g_debug
251 THEN
252 l_proc_step := 20;
253 DEBUG('l_input_value_id: ' || l_input_value_id);
254 debug_exit(l_proc_name);
255 END IF;
256
257 RETURN l_input_value_id;
258 EXCEPTION
259 WHEN OTHERS
260 THEN
261 clear_cache;
262
263 IF SQLCODE <> hr_utility.hr_error_number
264 THEN
265 debug_others(l_proc_name, l_proc_step);
266
267 IF g_debug
268 THEN
269 DEBUG('Leaving: ' || l_proc_name, -999);
270 END IF;
271
272 fnd_message.raise_error;
273 ELSE
274 RAISE;
275 END IF;
276 END get_input_value_id;
277
278 -- This function returns screen entry value for a given element entry id
279 -- ----------------------------------------------------------------------------
280 -- |----------------------------< get_screen_entry_value >--------------------|
281 -- ----------------------------------------------------------------------------
282 FUNCTION get_screen_entry_value(
283 p_element_entry_id IN NUMBER
284 ,p_effective_start_date IN DATE
285 ,p_effective_end_date IN DATE
286 ,p_input_value_id IN NUMBER
287 )
288 RETURN VARCHAR2
289 IS
290 --
291 -- Cursor to fetch screen entry value
292 CURSOR csr_get_screen_ent_val
293 IS
294 SELECT screen_entry_value
295 FROM pay_element_entry_values_f
296 WHERE element_entry_id = p_element_entry_id
297 AND effective_start_date = p_effective_start_date
298 AND effective_end_date = p_effective_end_date
299 AND input_value_id = p_input_value_id;
300
301 l_proc_name VARCHAR2(80)
302 := g_proc_name || 'get_screen_entry_value';
303 l_proc_step PLS_INTEGER;
304 l_screen_ent_value pay_element_entry_values_f.screen_entry_value%TYPE;
305 --
306 BEGIN
307 --
308 IF g_debug
309 THEN
310 l_proc_step := 10;
311 debug_enter(l_proc_name);
312 DEBUG('p_element_entry_id: ' || p_element_entry_id);
313 DEBUG(
314 'p_effective_start_date: '
315 || TO_CHAR(p_effective_start_date, 'DD/MON/YYYY')
316 );
317 DEBUG(
318 'p_effective_end_date: '
319 || TO_CHAR(p_effective_end_date, 'DD/MON/YYYY')
320 );
321 DEBUG('p_input_value_id: ' || p_input_value_id);
322 END IF;
323
324 OPEN csr_get_screen_ent_val;
325 FETCH csr_get_screen_ent_val INTO l_screen_ent_value;
326 CLOSE csr_get_screen_ent_val;
327
328 IF g_debug
329 THEN
330 l_proc_step := 20;
331 DEBUG('l_screen_ent_value: ' || l_screen_ent_value);
332 debug_exit(l_proc_name);
333 END IF;
334
335 RETURN l_screen_ent_value;
336 EXCEPTION
337 WHEN OTHERS
338 THEN
339 clear_cache;
340
341 IF SQLCODE <> hr_utility.hr_error_number
342 THEN
343 debug_others(l_proc_name, l_proc_step);
344
345 IF g_debug
346 THEN
347 DEBUG('Leaving: ' || l_proc_name, -999);
348 END IF;
349
350 fnd_message.raise_error;
351 ELSE
352 RAISE;
353 END IF;
354 END get_screen_entry_value;
355
356 -- This function returns the configuration type description for a given
357 -- configuration type
358 -- ----------------------------------------------------------------------------
359 -- |----------------------------< get_config_type_desc >----------------------|
360 -- ----------------------------------------------------------------------------
361 FUNCTION get_config_type_desc(p_config_type IN VARCHAR2)
362 RETURN VARCHAR2
363 IS
364 --
365 -- Cursor to fetch config desc
366 CURSOR csr_get_config_desc
367 IS
368 SELECT dfc.descriptive_flex_context_name
369 FROM pqp_configuration_types pct, fnd_descr_flex_contexts_vl dfc
370 WHERE pct.configuration_type = p_config_type
371 AND dfc.descriptive_flex_context_code = pct.configuration_type
372 AND dfc.application_id = 8303
373 AND dfc.descriptive_flexfield_name =
374 'Configuration Value Info DDF'
375 AND dfc.enabled_flag = 'Y';
376
377 l_proc_name VARCHAR2(80) := g_proc_name || 'get_config_type_desc';
378 l_proc_step PLS_INTEGER;
379 l_config_desc fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
380 --
381 BEGIN
382 --
383 IF g_debug
384 THEN
385 l_proc_step := 10;
386 debug_enter(l_proc_name);
387 DEBUG('p_config_type: ' || p_config_type);
388 END IF;
389
390 OPEN csr_get_config_desc;
391 FETCH csr_get_config_desc INTO l_config_desc;
392 CLOSE csr_get_config_desc;
393
394 IF g_debug
395 THEN
396 l_proc_step := 20;
397 DEBUG('l_config_desc: ' || l_config_desc);
398 debug_exit(l_proc_name);
399 END IF;
400
401 RETURN l_config_desc;
402 --
403 EXCEPTION
404 WHEN OTHERS
405 THEN
406 clear_cache;
407
408 IF SQLCODE <> hr_utility.hr_error_number
409 THEN
410 debug_others(l_proc_name, l_proc_step);
411
412 IF g_debug
413 THEN
414 DEBUG('Leaving: ' || l_proc_name, -999);
415 END IF;
416
417 fnd_message.raise_error;
418 ELSE
419 RAISE;
420 END IF;
421 END get_config_type_desc;
422
423 -- Ths function returns a yes or no flag to identify whether a value
424 -- is in the collection or not
425 -- ----------------------------------------------------------------------------
426 -- |---------------------< chk_value_in_collection >--------------------------|
427 -- ----------------------------------------------------------------------------
428 FUNCTION chk_value_in_collection(
429 p_collection_name IN t_number
430 ,p_value IN NUMBER
431 ,p_index OUT NOCOPY NUMBER
432 )
433 RETURN VARCHAR2
434 IS
435 --
436 l_proc_name VARCHAR2(80) := g_proc_name || 'chk_value_in_collection';
437 l_proc_step PLS_INTEGER;
438 i NUMBER;
439 l_return VARCHAR2(10);
440 l_index NUMBER;
441 --
442 BEGIN
443 --
444 IF g_debug
445 THEN
446 l_proc_step := 10;
447 debug_enter(l_proc_name);
448 DEBUG('p_value: ' || p_value);
449 END IF;
450
451 i := p_collection_name.FIRST;
452 l_return := 'N';
453 l_index := NULL;
454
455 WHILE i IS NOT NULL
456 LOOP
457 IF g_debug
458 THEN
459 l_proc_step := 20;
460 DEBUG(l_proc_name, l_proc_step);
461 DEBUG('i: ' || i);
462 DEBUG('p_collection_name(i): ' || p_collection_name(i));
463 END IF;
464
465 IF p_collection_name(i) = p_value
466 THEN
467 l_return := 'Y';
468 l_index := i;
469 EXIT;
470 END IF;
471
472 i := p_collection_name.NEXT(i);
473 END LOOP;
474
475 IF g_debug
476 THEN
477 l_proc_step := 30;
478 DEBUG('l_return: ' || l_return);
479 debug_exit(l_proc_name);
480 END IF;
481
482 p_index := l_index;
483 RETURN l_return;
484 EXCEPTION
485 WHEN OTHERS
486 THEN
487 clear_cache;
488
489 IF SQLCODE <> hr_utility.hr_error_number
490 THEN
491 debug_others(l_proc_name, l_proc_step);
492
493 IF g_debug
494 THEN
495 DEBUG('Leaving: ' || l_proc_name, -999);
496 END IF;
497
498 fnd_message.raise_error;
499 ELSE
500 RAISE;
501 END IF;
502 END chk_value_in_collection;
503
504 -- Ths function returns a yes or no flag to identify whether a code
505 -- is in the collection or not
506 -- ----------------------------------------------------------------------------
507 -- |---------------------< chk_event_in_collection >--------------------------|
508 -- ----------------------------------------------------------------------------
509 FUNCTION chk_event_in_collection(
510 p_event_code IN pqp_configuration_values.pcv_information1%TYPE
511 )
512 RETURN VARCHAR2
513 IS
514 --
515 l_proc_name VARCHAR2(80) := g_proc_name || 'chk_event_in_collection';
516 l_proc_step PLS_INTEGER;
517 i NUMBER;
518 l_return VARCHAR2(10);
519 --
520 BEGIN
521 --
522 IF g_debug
523 THEN
524 l_proc_step := 10;
525 debug_enter(l_proc_name);
526 DEBUG('p_event_code: ' || p_event_code);
527 END IF;
528
529 i := g_tab_event_desc_lov.FIRST;
530 l_return := 'Y';
531
532 WHILE i IS NOT NULL
533 LOOP
534 IF g_debug
535 THEN
536 l_proc_step := 20;
537 DEBUG(l_proc_name, l_proc_step);
538 DEBUG('i: ' || i);
539 DEBUG(
540 'g_tab_event_desc_lov(i).lookup_code: '
541 || g_tab_event_desc_lov(i).lookup_code
542 );
543 END IF;
544
545 IF g_tab_event_desc_lov(i).lookup_code = p_event_code
546 THEN
547 l_return := 'N';
548 EXIT;
549 END IF;
550
551 i := g_tab_event_desc_lov.NEXT(i);
552 END LOOP;
553
554 IF g_debug
555 THEN
556 l_proc_step := 30;
557 DEBUG('l_return: ' || l_return);
558 debug_exit(l_proc_name);
559 END IF;
560
561 RETURN l_return;
562 EXCEPTION
563 WHEN OTHERS
564 THEN
565 clear_cache;
566
567 IF SQLCODE <> hr_utility.hr_error_number
568 THEN
569 debug_others(l_proc_name, l_proc_step);
570
571 IF g_debug
572 THEN
573 DEBUG('Leaving: ' || l_proc_name, -999);
574 END IF;
575
576 fnd_message.raise_error;
577 ELSE
578 RAISE;
579 END IF;
580 END chk_event_in_collection;
581
582 -- Ths function returns a yes or no flag to identify whether a code
583 -- is in the collection or not
584 -- ----------------------------------------------------------------------------
585 -- |---------------------< chk_lvrsn_in_collection >--------------------------|
586 -- ----------------------------------------------------------------------------
587 FUNCTION chk_lvrsn_in_collection(
588 p_leave_reason IN pqp_configuration_values.pcv_information1%TYPE
589 ,p_index OUT NOCOPY NUMBER
590 )
591 RETURN VARCHAR2
592 IS
593 --
594 l_proc_name VARCHAR2(80) := g_proc_name || 'chk_lvrsn_in_collection';
595 l_proc_step PLS_INTEGER;
596 i NUMBER;
597 l_return VARCHAR2(10);
598 l_value NUMBER;
599 l_configuration_desc fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
600 l_meaning hr_lookups.meaning%TYPE;
601 --
602 BEGIN
603 --
604 IF g_debug
605 THEN
606 l_proc_step := 10;
607 debug_enter(l_proc_name);
608 DEBUG('p_leave_reason: ' || p_leave_reason);
609 END IF;
610
611 i := g_tab_lvrsn_map_cv.FIRST;
612 l_return := 'N';
613
614 WHILE i IS NOT NULL
615 LOOP
616 IF g_debug
617 THEN
618 l_proc_step := 20;
619 DEBUG(l_proc_name, l_proc_step);
620 DEBUG('i: ' || i);
621 DEBUG(
622 'g_tab_lvrsn_map_cv(i).pcv_information1: '
623 || g_tab_lvrsn_map_cv(i).pcv_information1
624 );
625 END IF;
626
627 IF g_tab_lvrsn_map_cv(i).pcv_information1 = p_leave_reason
628 THEN
629 l_return := 'Y';
630 p_index := i;
631 EXIT;
632 END IF;
633
634 i := g_tab_lvrsn_map_cv.NEXT(i);
635 END LOOP;
636
637 IF l_return = 'N' THEN
638
639 IF g_debug
640 THEN
641 DEBUG('Raise data error..Leave reason map is missing');
642 END IF;
643
644 l_meaning :=
645 hr_general.decode_lookup(
646 p_lookup_type => 'LEAV_REAS'
647 ,p_lookup_code => p_leave_reason
648 );
649
650 l_configuration_desc :=
651 get_config_type_desc(p_config_type => 'PQP_GB_PENSERVER_SER_LVRSN_MAP');
652
653 -- Raise data error
654 l_value :=
655 pqp_gb_psi_functions.raise_extract_error(
656 p_error_number => 94635
657 ,p_error_text => 'BEN_94635_EXT_PSI_MISS_LVRSN'
658 ,p_token1 => l_meaning
659 ,p_token2 => l_configuration_desc
660 );
661 END IF; -- End if of l_return = N check ...
662
663 IF g_debug
664 THEN
665 l_proc_step := 30;
666 DEBUG('l_return: ' || l_return);
667 debug_exit(l_proc_name);
668 END IF;
669
670 RETURN l_return;
671 EXCEPTION
672 WHEN OTHERS
673 THEN
674 clear_cache;
675
676 IF SQLCODE <> hr_utility.hr_error_number
677 THEN
678 debug_others(l_proc_name, l_proc_step);
679
680 IF g_debug
681 THEN
682 DEBUG('Leaving: ' || l_proc_name, -999);
683 END IF;
684
685 fnd_message.raise_error;
686 ELSE
687 RAISE;
688 END IF;
689 END chk_lvrsn_in_collection;
690
691 -- This procedure returns the assignment status details
692 -- for a given assignment status
693 -- ----------------------------------------------------------------------------
694 -- |----------------------< get_asg_status_type >-----------------------------|
695 -- ----------------------------------------------------------------------------
696 PROCEDURE get_asg_status_type(
697 p_per_system_status IN per_assignment_status_types.per_system_status%TYPE
698 ,p_rec_asg_sts_dtls OUT NOCOPY csr_get_asg_sts_dtls%ROWTYPE
699 )
700 IS
701 --
702
703 l_proc_name VARCHAR2(80)
704 := g_proc_name || 'get_asg_status_type';
705 l_proc_step PLS_INTEGER;
706 l_rec_asg_sts_dtls csr_get_asg_sts_dtls%ROWTYPE;
707 --
708 BEGIN
709 --
710 IF g_debug
711 THEN
712 l_proc_step := 10;
713 debug_enter(l_proc_name);
714 DEBUG('p_per_system_status: ' || p_per_system_status);
715 END IF;
716
717 OPEN csr_get_asg_sts_dtls(p_per_system_status);
718 FETCH csr_get_asg_sts_dtls INTO l_rec_asg_sts_dtls;
719 CLOSE csr_get_asg_sts_dtls;
720 p_rec_asg_sts_dtls := l_rec_asg_sts_dtls;
721
722 IF g_debug
723 THEN
724 DEBUG(
725 'assignment_status_type_id: '
726 || l_rec_asg_sts_dtls.assignment_status_type_id
727 );
728 DEBUG('user_status: ' || l_rec_asg_sts_dtls.user_status);
729 l_proc_step := 20;
730 debug_exit(l_proc_name);
731 END IF;
732 EXCEPTION
733 WHEN OTHERS
734 THEN
735 clear_cache;
736
737 IF SQLCODE <> hr_utility.hr_error_number
738 THEN
739 debug_others(l_proc_name, l_proc_step);
740
741 IF g_debug
742 THEN
743 DEBUG('Leaving: ' || l_proc_name, -999);
744 END IF;
745
746 fnd_message.raise_error;
747 ELSE
748 RAISE;
749 END IF;
750 END get_asg_status_type;
751
752 -- This function returns user status
753 -- for a given assignment status
754 -- ----------------------------------------------------------------------------
755 -- |----------------------< get_asg_status_type >-----------------------------|
756 -- ----------------------------------------------------------------------------
757 FUNCTION get_asg_status_type(p_asg_sts_type_id IN NUMBER)
758 RETURN per_assignment_status_types.user_status%TYPE
759 IS
760 --
761 CURSOR csr_get_asg_sts_dtls
762 IS
763 SELECT user_status
764 FROM per_assignment_status_types
765 WHERE assignment_status_type_id = p_asg_sts_type_id;
766
767 l_proc_name VARCHAR2(80) := g_proc_name || 'get_asg_status_type';
768 l_proc_step PLS_INTEGER;
769 l_user_status per_assignment_status_types.user_status%TYPE;
770 --
771 BEGIN
772 --
773 IF g_debug
774 THEN
775 l_proc_step := 10;
776 debug_enter(l_proc_name);
777 DEBUG('p_asg_sts_type_id: ' || p_asg_sts_type_id);
778 END IF;
779
780 OPEN csr_get_asg_sts_dtls;
781 FETCH csr_get_asg_sts_dtls INTO l_user_status;
782 CLOSE csr_get_asg_sts_dtls;
783
784 IF g_debug
785 THEN
786 DEBUG('user_status: ' || l_user_status);
787 l_proc_step := 20;
788 debug_exit(l_proc_name);
789 END IF;
790
791 RETURN l_user_status;
792 EXCEPTION
793 WHEN OTHERS
794 THEN
795 clear_cache;
796
797 IF SQLCODE <> hr_utility.hr_error_number
798 THEN
799 debug_others(l_proc_name, l_proc_step);
800
801 IF g_debug
802 THEN
803 DEBUG('Leaving: ' || l_proc_name, -999);
804 END IF;
805
806 fnd_message.raise_error;
807 ELSE
808 RAISE;
809 END IF;
810 END get_asg_status_type;
811
812 -- This function returns the absence type name
813 -- for a given absence type id
814 -- ----------------------------------------------------------------------------
815 -- |----------------------< get_abs_type_name >-----------------------------|
816 -- ----------------------------------------------------------------------------
817 FUNCTION get_abs_type_name(p_absence_type_id IN NUMBER)
818 RETURN per_absence_attendance_types.NAME%TYPE
819 IS
820 --
821 CURSOR csr_get_abs_type_name
822 IS
823 SELECT NAME
824 FROM per_absence_attendance_types
825 WHERE absence_attendance_type_id = p_absence_type_id;
826
827 l_proc_name VARCHAR2(80) := g_proc_name || 'get_abs_type_name';
828 l_proc_step PLS_INTEGER;
829 l_abs_type_name per_absence_attendance_types.NAME%TYPE;
830 --
831 BEGIN
832 --
833 IF g_debug
834 THEN
835 l_proc_step := 10;
836 debug_enter(l_proc_name);
837 DEBUG('p_absence_type_id: ' || p_absence_type_id);
838 END IF;
839
840 OPEN csr_get_abs_type_name;
841 FETCH csr_get_abs_type_name INTO l_abs_type_name;
842 CLOSE csr_get_abs_type_name;
843
844 IF g_debug
845 THEN
846 DEBUG('l_abs_type_name: ' || l_abs_type_name);
847 l_proc_step := 20;
848 debug_exit(l_proc_name);
849 END IF;
850
851 RETURN l_abs_type_name;
852 EXCEPTION
853 WHEN OTHERS
854 THEN
855 clear_cache;
856
857 IF SQLCODE <> hr_utility.hr_error_number
858 THEN
859 debug_others(l_proc_name, l_proc_step);
860
861 IF g_debug
862 THEN
863 DEBUG('Leaving: ' || l_proc_name, -999);
864 END IF;
865
866 fnd_message.raise_error;
867 ELSE
868 RAISE;
869 END IF;
870 END get_abs_type_name;
871
872 -- This procedures fetches the process definition configuration
873 -- for penserver
874 -- ----------------------------------------------------------------------------
875 -- |----------------------------< fetch_process_defn_cv >---------------------|
876 -- ----------------------------------------------------------------------------
877 PROCEDURE fetch_process_defn_cv(p_business_group_id IN NUMBER)
878 IS
879 --
880 l_proc_name VARCHAR2(80)
881 := g_proc_name || 'fetch_process_defn_cv';
882 l_proc_step PLS_INTEGER;
883 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
884 l_tab_config_values pqp_utilities.t_config_values;
885 i NUMBER;
886 --
887 BEGIN
888 --
889 IF g_debug
890 THEN
891 l_proc_step := 10;
892 debug_enter(l_proc_name);
893 END IF;
894
895 -- Call configuration value function to retrieve all data
896 -- for a configuration type
897 IF g_debug
898 THEN
899 l_proc_step := 20;
900 DEBUG(l_proc_name, l_proc_step);
901 DEBUG('p_business_group_id: ' || p_business_group_id);
902 END IF;
903
904 l_configuration_type := 'PQP_GB_PENSERVER_DEFINITION';
905 pqp_utilities.get_config_type_values(
906 p_configuration_type => l_configuration_type
907 ,p_business_group_id => p_business_group_id
908 ,p_legislation_code => g_legislation_code
909 ,p_tab_config_values => l_tab_config_values
910 );
911
912 IF g_debug
913 THEN
914 l_proc_step := 30;
915 DEBUG(l_proc_name, l_proc_step);
916 DEBUG('l_configuration_type: ' || l_configuration_type);
917 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
918 END IF;
919
920 -- Store the config values in the global collection
921 -- for event map
922 g_tab_prs_dfn_cv := l_tab_config_values;
923
924 -- Debug PCV_INFORMATION1
925 IF g_debug
926 THEN
927 l_proc_step := 40;
928 DEBUG(l_proc_name, l_proc_step);
929 END IF;
930
931 i := g_tab_prs_dfn_cv.FIRST;
932
933 WHILE i IS NOT NULL
934 LOOP
935 IF g_debug
936 THEN
937 DEBUG('Debug: ' || l_tab_config_values(i).pcv_information1);
938 END IF;
939
940 i := g_tab_prs_dfn_cv.NEXT(i);
941 END LOOP;
942
943 IF g_debug
944 THEN
945 l_proc_step := 50;
946 debug_exit(l_proc_name);
947 END IF;
948 EXCEPTION
949 WHEN OTHERS
950 THEN
951 clear_cache;
952
953 IF SQLCODE <> hr_utility.hr_error_number
954 THEN
955 debug_others(l_proc_name, l_proc_step);
956
957 IF g_debug
958 THEN
959 DEBUG('Leaving: ' || l_proc_name, -999);
960 END IF;
961
962 fnd_message.raise_error;
963 ELSE
964 RAISE;
965 END IF;
966 END fetch_process_defn_cv;
967
968 -- This procedure fetches event mapping configuration value
969 -- for service history
970 -- ----------------------------------------------------------------------------
971 -- |----------------------------< fetch_event_map_cv >------------------------|
972 -- ----------------------------------------------------------------------------
973 PROCEDURE fetch_event_map_cv
974 IS
975 --
976 -- Cursor to fetch values from event desc lookup
977 CURSOR csr_get_event_desc
978 IS
979 SELECT lookup_code, meaning
980 FROM hr_lookups
981 WHERE lookup_type = 'PQP_PENSERVER_EVENT_DESC'
982 AND enabled_flag = 'Y'
983 AND g_effective_date BETWEEN NVL(
984 start_date_active
985 ,g_effective_date
986 )
987 AND NVL(end_date_active
988 ,g_effective_date)
989 ORDER BY lookup_code;
990
991 l_proc_name VARCHAR2(80)
992 := g_proc_name || 'fetch_event_map_cv';
993 l_proc_step PLS_INTEGER;
994 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
995 l_tab_config_values pqp_utilities.t_config_values;
996 i NUMBER;
997 j NUMBER;
998 l_event_code hr_lookups.lookup_code%TYPE;
999 l_event_desc hr_lookups.meaning%TYPE;
1000 l_token1 VARCHAR2(2000);
1001 l_token2 VARCHAR2(2000);
1002 l_new_joiner VARCHAR2(10);
1003 l_ret_break VARCHAR2(10);
1004 l_meaning hr_lookups.meaning%TYPE;
1005 l_configuration_desc fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
1006 l_abs_type_name per_absence_attendance_types.NAME%TYPE;
1007 l_asg_status per_assignment_status_types.user_status%TYPE;
1008 l_miss_events VARCHAR2(32000);
1009 --
1010 BEGIN
1011 --
1012 IF g_debug
1013 THEN
1014 l_proc_step := 10;
1015 debug_enter(l_proc_name);
1016 END IF;
1017
1018 -- Call configuration value function to retrieve all data
1019 -- for a configuration type
1020
1021 IF g_debug
1022 THEN
1023 l_proc_step := 20;
1024 DEBUG(l_proc_name, l_proc_step);
1025 DEBUG('g_business_group_id: ' || g_business_group_id);
1026 END IF;
1027
1028 l_configuration_type := 'PQP_GB_PENSERVER_SEREVENT_INFO';
1029 l_configuration_desc :=
1030 get_config_type_desc(p_config_type => l_configuration_type);
1031 pqp_utilities.get_config_type_values(
1032 p_configuration_type => l_configuration_type
1033 ,p_business_group_id => g_business_group_id
1034 ,p_legislation_code => g_legislation_code
1035 ,p_tab_config_values => l_tab_config_values
1036 );
1037
1038 IF g_debug
1039 THEN
1040 l_proc_step := 30;
1041 DEBUG(l_proc_name, l_proc_step);
1042 DEBUG('l_configuration_type: ' || l_configuration_type);
1043 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1044 END IF;
1045
1046 -- Store the config values in the global collection
1047 -- for event map
1048 g_tab_event_map_cv := l_tab_config_values;
1049
1050 -- For bug 7145485
1051
1052 hr_api.set_legislation_context('GB');
1053
1054 -- End Bug 7145485
1055 -- Event Description PCV_INFORMATION1
1056 -- Event Source PCV_INFORMATION4
1057 -- Absence Type PCV_INFORMATION7
1058 -- Assignment Status PCV_INFORMATION8
1059 -- Employment Type PCV_INFORMATION9
1060 -- Pension Scheme PCV_INFORMATION10
1061 -- Start Reason PCV_INFORMATION11
1062 -- Scheme Category PCV_INFORMATION2
1063 -- Scheme Status PCV_INFORMATION3
1064
1065 -- Loop through the event description lookup
1066 -- and store it in the collection
1067 -- we will use this information to check atleast
1068 -- one value exist in the event map collection
1069 -- for this event description
1070 i := 1;
1071 OPEN csr_get_event_desc;
1072
1073 LOOP
1074 FETCH csr_get_event_desc INTO l_event_code, l_event_desc;
1075 EXIT WHEN csr_get_event_desc%NOTFOUND;
1076 g_tab_event_desc_lov(i).lookup_code := l_event_code;
1077 g_tab_event_desc_lov(i).meaning := l_event_desc;
1078
1079 IF g_debug
1080 THEN
1081 l_proc_step := 40;
1082 DEBUG(l_proc_name, l_proc_step);
1083 DEBUG('l_event_code: ' || l_event_code);
1084 DEBUG('l_event_desc: ' || l_event_desc);
1085 END IF;
1086
1087 i := i + 1;
1088 END LOOP;
1089
1090 CLOSE csr_get_event_desc;
1091
1092 IF g_debug
1093 THEN
1094 l_proc_step := 50;
1095 DEBUG(l_proc_name, l_proc_step);
1096 END IF;
1097
1098 i := g_tab_event_map_cv.FIRST;
1099
1100 WHILE i IS NOT NULL
1101 LOOP
1102 IF g_debug
1103 THEN
1104 DEBUG('Configuration Value ID: ' || i);
1105 DEBUG('Event Description:'
1106 || g_tab_event_map_cv(i).pcv_information1);
1107 DEBUG('Event Source: ' || g_tab_event_map_cv(i).pcv_information4);
1108 DEBUG('Absence Type: ' || g_tab_event_map_cv(i).pcv_information7);
1109 DEBUG(
1110 'Assignment Status: ' || g_tab_event_map_cv(i).pcv_information8
1111 );
1112 DEBUG('Employment Type: '
1113 || g_tab_event_map_cv(i).pcv_information9);
1114 DEBUG('Pension Scheme: '
1115 || g_tab_event_map_cv(i).pcv_information10);
1116 DEBUG('Start Reason: ' || g_tab_event_map_cv(i).pcv_information11);
1117 DEBUG('Scheme Category: '
1118 || g_tab_event_map_cv(i).pcv_information2);
1119 DEBUG('Scheme Status: ' || g_tab_event_map_cv(i).pcv_information3);
1120 END IF;
1121
1122 -- Populate assignment status and absence type global collection
1123 -- based of event source
1124 IF g_tab_event_map_cv(i).pcv_information4 = 'ABS'
1125 THEN
1126 -- Event is absence type
1127 -- populate absence type collection
1128 IF g_tab_event_map_cv(i).pcv_information7 IS NOT NULL
1129 THEN
1130 g_tab_abs_types(i) :=
1131 fnd_number.canonical_to_number(g_tab_event_map_cv(i).pcv_information7);
1132 ELSE
1133 l_meaning :=
1134 hr_general.decode_lookup(
1135 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1136 ,p_lookup_code => g_tab_event_map_cv(i).pcv_information1
1137 );
1138
1139 IF g_debug
1140 THEN
1141 DEBUG('l_meaning: ' || l_meaning);
1142 END IF;
1143
1144 -- Raise setup error
1145 pqp_gb_psi_functions.store_extract_exceptions(
1146 p_extract_type => 'SERVICE_HISTORY'
1147 ,p_error_number => 93774
1148 ,p_error_text => 'BEN_93774_EXT_PSI_NO_ABS_TYPE'
1149 ,p_token1 => l_configuration_desc
1150 ,p_token2 => l_meaning
1151 ,p_error_warning_flag => 'E'
1152 );
1153 END IF; -- End if of abs not null check ...
1154 ELSIF g_tab_event_map_cv(i).pcv_information4 = 'ASG'
1155 THEN
1156 -- Event is assignment status
1157 -- populate assignment status collection
1158 IF g_tab_event_map_cv(i).pcv_information8 IS NOT NULL
1159 THEN
1160 g_tab_asg_status(i) :=
1161 fnd_number.canonical_to_number(g_tab_event_map_cv(i).pcv_information8);
1162 ELSE
1163 l_meaning :=
1164 hr_general.decode_lookup(
1165 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1166 ,p_lookup_code => g_tab_event_map_cv(i).pcv_information1
1167 );
1168
1169 IF g_debug
1170 THEN
1171 DEBUG('l_meaning: ' || l_meaning);
1172 END IF;
1173
1174 -- Raise setup error
1175 pqp_gb_psi_functions.store_extract_exceptions(
1176 p_extract_type => 'SERVICE_HISTORY'
1177 ,p_error_number => 93776
1178 ,p_error_text => 'BEN_93776_EXT_PSI_NO_ASG_STS'
1179 ,p_token1 => l_configuration_desc
1180 ,p_token2 => l_meaning
1181 ,p_error_warning_flag => 'E'
1182 );
1183 END IF; -- End if of asg status not null check ...
1184 END IF; -- End if of event source value check ...
1185
1186 -- Delete lookup collection if an event is found
1187
1188 j := g_tab_event_desc_lov.FIRST;
1189
1190 WHILE j IS NOT NULL
1191 LOOP
1192 IF g_tab_event_desc_lov(j).lookup_code =
1193 g_tab_event_map_cv(i).pcv_information1
1194 THEN
1195 IF g_debug
1196 THEN
1197 l_proc_step := 60;
1198 DEBUG(l_proc_name, l_proc_step);
1199 DEBUG(
1200 'g_tab_event_desc_lov(j): '
1201 || g_tab_event_desc_lov(j).lookup_code
1202 );
1203 END IF;
1204
1205 g_tab_event_desc_lov.DELETE(j);
1206 EXIT; -- Exit the collection
1207 END IF;
1208
1209 j := g_tab_event_desc_lov.NEXT(j);
1210 END LOOP;
1211
1212 i := g_tab_event_map_cv.NEXT(i);
1213 END LOOP;
1214
1215 IF g_tab_event_desc_lov.COUNT <> 0
1216 THEN
1217 IF g_debug
1218 THEN
1219 l_proc_step := 70;
1220 DEBUG(l_proc_name, l_proc_step);
1221 END IF;
1222
1223 -- There are some events for which event mapping
1224 -- do not exist
1225 -- Check for new joiner events and return from
1226 -- break events not set up
1227 l_new_joiner := chk_event_in_collection(p_event_code => 'N');
1228 l_ret_break := chk_event_in_collection(p_event_code => 'RB');
1229
1230 IF l_new_joiner = 'N'
1231 THEN
1232 l_token1 := 'New Joiner';
1233 END IF;
1234
1235 IF l_ret_break = 'N'
1236 THEN
1237 l_token2 := 'Return from Break';
1238 END IF;
1239
1240 IF l_new_joiner = 'N' OR l_ret_break = 'N'
1241 THEN
1242 -- Raise a setup error
1243 pqp_gb_psi_functions.store_extract_exceptions(
1244 p_extract_type => 'SERVICE_HISTORY'
1245 ,p_error_number => 93777
1246 ,p_error_text => 'BEN_93777_EXT_PSI_SER_EVNT_MAP'
1247 ,p_token1 => l_configuration_desc
1248 ,p_token2 => l_token1
1249 ,p_token3 => l_token2
1250 ,p_error_warning_flag => 'E'
1251 );
1252 END IF;
1253
1254 -- Enhancement 5040543
1255 -- Get a list of events that are not mapped
1256 i := g_tab_event_desc_lov.FIRST;
1257 l_miss_events := NULL;
1258 WHILE i IS NOT NULL
1259 LOOP
1260
1261 IF l_miss_events IS NULL
1262 THEN
1263 l_miss_events := g_tab_event_desc_lov(i).meaning;
1264 ELSE
1265 l_miss_events := l_miss_events || ', ' || g_tab_event_desc_lov(i).meaning;
1266 END IF;
1267
1268 IF g_debug
1269 THEN
1270 debug('Event Code: '|| g_tab_event_desc_lov(i).lookup_code);
1271 debug('Event Name: '|| g_tab_event_desc_lov(i).meaning);
1272 END IF;
1273
1274 i := g_tab_event_desc_lov.NEXT(i);
1275 END LOOP;
1276
1277 IF g_debug
1278 THEN
1279 debug('Missing Events: '|| l_miss_events);
1280 END IF;
1281
1282 -- Raise a setup warning
1283 pqp_gb_psi_functions.store_extract_exceptions(
1284 p_extract_type => 'SERVICE_HISTORY'
1285 ,p_error_number => 94363
1286 ,p_error_text => 'BEN_94363_EXT_PSI_EVNT_MAP_WRN'
1287 ,p_token1 => l_configuration_desc
1288 ,p_token2 => l_miss_events
1289 ,p_error_warning_flag => 'W'
1290 );
1291
1292 -- commente for bug 8470684
1293 --g_opt_in := chk_event_in_collection(p_event_code => 'OI');
1294 --g_opt_out := chk_event_in_collection(p_event_code => 'OO');
1295
1296 -- Loop through the absence type collection
1297 -- to ensure that there are no two events used
1298 -- for same absence type
1299 IF g_debug
1300 THEN
1301 l_proc_step := 80;
1302 DEBUG(l_proc_name, l_proc_step);
1303 END IF;
1304
1305 i := g_tab_abs_types.FIRST;
1306
1307 WHILE i IS NOT NULL
1308 LOOP
1309 IF g_debug
1310 THEN
1311 l_proc_step := 90;
1312 DEBUG(l_proc_name, l_proc_step);
1313 DEBUG('Absence Type ID: ' || g_tab_abs_types(i));
1314 END IF;
1315
1316 j := g_tab_abs_types.NEXT(i);
1317
1318 WHILE j IS NOT NULL
1319 LOOP
1320 IF g_debug
1321 THEN
1322 l_proc_step := 100;
1323 DEBUG(l_proc_name, l_proc_step);
1324 DEBUG('Absence Type ID: ' || g_tab_abs_types(j));
1325 END IF;
1326
1327 IF g_tab_abs_types(i) = g_tab_abs_types(j)
1328 THEN
1329 IF g_debug
1330 THEN
1331 l_proc_step := 110;
1332 DEBUG(l_proc_name, l_proc_step);
1333 DEBUG(
1334 'first code: '
1335 || g_tab_event_map_cv(i).pcv_information1
1336 );
1337 DEBUG(
1338 'seconde code: '
1339 || g_tab_event_map_cv(j).pcv_information1
1340 );
1341 END IF;
1342
1343 -- check whether the codes are same
1344 IF g_tab_event_map_cv(i).pcv_information1 <>
1345 g_tab_event_map_cv(j).pcv_information1
1346 AND (
1347 SUBSTR(g_tab_event_map_cv(i).pcv_information1, 1
1348 ,1) NOT IN('S', 'M')
1349 OR (
1350 SUBSTR(
1351 g_tab_event_map_cv(i).pcv_information1
1352 ,1
1353 ,1
1354 ) IN('S', 'M')
1355 AND SUBSTR(
1356 g_tab_event_map_cv(i).pcv_information1
1357 ,1
1358 ,1
1359 ) <>
1360 SUBSTR(
1361 g_tab_event_map_cv(j).pcv_information1
1362 ,1
1363 ,1
1364 )
1365 )
1366 )
1367 THEN
1368 -- Get the absence type name
1369 l_abs_type_name :=
1370 get_abs_type_name(p_absence_type_id => g_tab_abs_types(i));
1371 l_meaning :=
1372 hr_general.decode_lookup(
1373 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1374 ,p_lookup_code => g_tab_event_map_cv(i).pcv_information1
1375 );
1376
1377 IF g_debug
1378 THEN
1379 DEBUG('l_meaning: ' || l_meaning);
1380 END IF;
1381
1382 l_meaning :=
1383 l_meaning
1384 || ', '
1385 || hr_general.decode_lookup(
1386 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1387 ,p_lookup_code => g_tab_event_map_cv(j).pcv_information1
1388 );
1389
1390 IF g_debug
1391 THEN
1392 DEBUG('l_meaning: ' || l_meaning);
1393 END IF;
1394
1395 pqp_gb_psi_functions.store_extract_exceptions(
1396 p_extract_type => 'SERVICE_HISTORY'
1397 ,p_error_number => 94364
1398 ,p_error_text => 'BEN_94364_EXT_PSI_DUP_EVNT_MAP'
1399 ,p_token1 => 'Absence Type'
1400 ,p_token2 => l_abs_type_name
1401 ,p_token3 => l_meaning
1402 ,p_token4 => l_configuration_desc
1403 ,p_error_warning_flag => 'E'
1404 );
1405 -- Raise error
1406 EXIT;
1407 END IF; -- End if of event codes not same check ...
1408 END IF; -- Same absence type ids ...
1409
1410 j := g_tab_abs_types.NEXT(j);
1411 END LOOP; -- j loop
1412
1413 i := g_tab_abs_types.NEXT(i);
1414 END LOOP; -- i loop
1415
1416 -- Loop through the assignment status collection
1417 -- to ensure that there are no two events used
1418 -- for same assignment status
1419
1420 IF g_debug
1421 THEN
1422 l_proc_step := 120;
1423 DEBUG(l_proc_name, l_proc_step);
1424 END IF;
1425
1426 i := g_tab_asg_status.FIRST;
1427
1428 WHILE i IS NOT NULL
1429 LOOP
1430 IF g_debug
1431 THEN
1432 l_proc_step := 130;
1433 DEBUG(l_proc_name, l_proc_step);
1434 DEBUG('Assignment Status ID: ' || g_tab_asg_status(i));
1435 END IF;
1436
1437 j := g_tab_asg_status.NEXT(i);
1438
1439 WHILE j IS NOT NULL
1440 LOOP
1441 IF g_debug
1442 THEN
1443 l_proc_step := 140;
1444 DEBUG(l_proc_name, l_proc_step);
1445 DEBUG('Assignment Status ID: ' || g_tab_asg_status(j));
1446 END IF;
1447
1448 IF g_tab_asg_status(i) = g_tab_asg_status(j)
1449 AND g_tab_asg_status(i) <> g_active_asg_sts_id
1450 THEN
1451 IF g_debug
1452 THEN
1453 l_proc_step := 150;
1454 DEBUG(l_proc_name, l_proc_step);
1455 DEBUG(
1456 'first code: '
1457 || g_tab_event_map_cv(i).pcv_information1
1458 );
1459 DEBUG(
1460 'seconde code: '
1461 || g_tab_event_map_cv(j).pcv_information1
1462 );
1463 END IF;
1464
1465 -- check whether the codes are same
1466 IF g_tab_event_map_cv(i).pcv_information1 <>
1467 g_tab_event_map_cv(j).pcv_information1
1468 THEN
1469 l_asg_status :=
1470 get_asg_status_type(p_asg_sts_type_id => g_tab_asg_status(i));
1471 l_meaning :=
1472 hr_general.decode_lookup(
1473 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1474 ,p_lookup_code => g_tab_event_map_cv(i).pcv_information1
1475 );
1476
1477 IF g_debug
1478 THEN
1479 DEBUG('l_meaning: ' || l_meaning);
1480 END IF;
1481
1482 l_meaning :=
1483 l_meaning
1484 || ', '
1485 || hr_general.decode_lookup(
1486 p_lookup_type => 'PQP_PENSERVER_EVENT_DESC'
1487 ,p_lookup_code => g_tab_event_map_cv(j).pcv_information1
1488 );
1489
1490 IF g_debug
1491 THEN
1492 DEBUG('l_meaning: ' || l_meaning);
1493 END IF;
1494
1495 pqp_gb_psi_functions.store_extract_exceptions(
1496 p_extract_type => 'SERVICE_HISTORY'
1497 ,p_error_number => 94364
1498 ,p_error_text => 'BEN_94364_EXT_PSI_DUP_EVNT_MAP'
1499 ,p_token1 => 'Assignment Status'
1500 ,p_token2 => l_asg_status
1501 ,p_token3 => l_meaning
1502 ,p_token4 => l_configuration_desc
1503 ,p_error_warning_flag => 'E'
1504 );
1505 -- Raise error
1506 EXIT;
1507 END IF; -- End if of event codes not same check ...
1508 END IF; -- Same assignment status type ids ...
1509
1510 j := g_tab_asg_status.NEXT(j);
1511 END LOOP; -- j loop
1512
1513 i := g_tab_asg_status.NEXT(i);
1514 END LOOP; -- i loop
1515 END IF; -- End if of even desc lov count <> 0 check ...
1516
1517 -- For bug 8470684
1518
1519 IF g_debug
1520 THEN
1521 DEBUG('Calling g_opt_in and g_opt_out');
1522 END IF;
1523
1524 g_opt_in := chk_event_in_collection(p_event_code => 'OI');
1525 g_opt_out := chk_event_in_collection(p_event_code => 'OO');
1526
1527 IF g_debug
1528 THEN
1529 l_proc_step := 160;
1530 DEBUG('g_opt_in: ' || g_opt_in);
1531 DEBUG('g_opt_out: ' || g_opt_out);
1532 debug_exit(l_proc_name);
1533 END IF;
1534 EXCEPTION
1535 WHEN OTHERS
1536 THEN
1537 clear_cache;
1538
1539 IF SQLCODE <> hr_utility.hr_error_number
1540 THEN
1541 debug_others(l_proc_name, l_proc_step);
1542
1543 IF g_debug
1544 THEN
1545 DEBUG('Leaving: ' || l_proc_name, -999);
1546 END IF;
1547
1548 fnd_message.raise_error;
1549 ELSE
1550 RAISE;
1551 END IF;
1552 END fetch_event_map_cv;
1553
1554 -- This procedure fetches elements mapped to civil service pension schemes
1555 -- ----------------------------------------------------------------------------
1556 -- |----------------------------< fetch_pension_scheme_map_cv >---------------|
1557 -- ----------------------------------------------------------------------------
1558 PROCEDURE fetch_pension_scheme_map_cv(
1559 p_business_group_id IN NUMBER
1560 ,p_tab_pen_sch_map_cv OUT NOCOPY pqp_utilities.t_config_values
1561 )
1562 IS
1563 --
1564 l_proc_name VARCHAR2(80)
1565 := g_proc_name || 'fetch_pension_scheme_map_cv';
1566 l_proc_step PLS_INTEGER;
1567 l_element_type_id NUMBER;
1568 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
1569 l_tab_config_values pqp_utilities.t_config_values;
1570 i NUMBER;
1571 --
1572 BEGIN
1573 --
1574 IF g_debug
1575 THEN
1576 l_proc_step := 10;
1577 debug_enter(l_proc_name);
1578 END IF;
1579
1580 --
1581 -- Call configuration value function to retrieve all data
1582 -- for a configuration type
1583
1584 IF g_debug
1585 THEN
1586 l_proc_step := 20;
1587 DEBUG(l_proc_name, l_proc_step);
1588 DEBUG('p_business_group_id: ' || p_business_group_id);
1589 END IF;
1590
1591 l_configuration_type := 'PQP_GB_PENSERV_SCHEME_MAP_INFO';
1592
1593 IF pqp_gb_psi_functions.g_pension_scheme_mapping.COUNT = 0
1594 THEN
1595 pqp_utilities.get_config_type_values(
1596 p_configuration_type => l_configuration_type
1597 ,p_business_group_id => p_business_group_id
1598 ,p_legislation_code => g_legislation_code
1599 ,p_tab_config_values => l_tab_config_values
1600 );
1601 ELSE -- get it from cached collection
1602 l_tab_config_values :=
1603 pqp_gb_psi_functions.g_pension_scheme_mapping;
1604 END IF;
1605
1606 IF g_debug
1607 THEN
1608 l_proc_step := 30;
1609 DEBUG(l_proc_name, l_proc_step);
1610 DEBUG('l_configuration_type: ' || l_configuration_type);
1611 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1612 END IF;
1613
1614 -- Return the
1615 -- collection for pension scheme elements
1616 p_tab_pen_sch_map_cv := l_tab_config_values;
1617 -- Penserver Pension Scheme PCV_INFORMATION2
1618 -- Template Pension Scheme PCV_INFORMATION1
1619
1620 i := l_tab_config_values.FIRST;
1621
1622 IF g_debug
1623 THEN
1624 l_proc_step := 40;
1625 DEBUG(l_proc_name, l_proc_step);
1626 END IF;
1627
1628 WHILE i IS NOT NULL
1629 LOOP
1630 IF g_debug
1631 THEN
1632 DEBUG(
1633 'Penserver Pension Scheme: '
1634 || l_tab_config_values(i).pcv_information2
1635 );
1636 DEBUG(
1637 'Template Pension Scheme: '
1638 || l_tab_config_values(i).pcv_information1
1639 );
1640 DEBUG('Partnership Scheme: '||
1641 l_tab_config_values(i).pcv_information3);
1642 END IF;
1643
1644 i := l_tab_config_values.NEXT(i);
1645 END LOOP;
1646
1647 IF g_debug
1648 THEN
1649 l_proc_step := 50;
1650 debug_exit(l_proc_name);
1651 END IF;
1652 EXCEPTION
1653 WHEN OTHERS
1654 THEN
1655 clear_cache;
1656
1657 IF SQLCODE <> hr_utility.hr_error_number
1658 THEN
1659 debug_others(l_proc_name, l_proc_step);
1660
1661 IF g_debug
1662 THEN
1663 DEBUG('Leaving: ' || l_proc_name, -999);
1664 END IF;
1665
1666 fnd_message.raise_error;
1667 ELSE
1668 RAISE;
1669 END IF;
1670 END fetch_pension_scheme_map_cv;
1671
1672 -- This procedure fetches leaving reason configuration mapping information
1673 -- ----------------------------------------------------------------------------
1674 -- |----------------------------< fetch_leaving_reason_map_cv >---------------|
1675 -- ----------------------------------------------------------------------------
1676 PROCEDURE fetch_leaving_reason_map_cv(
1677 p_business_group_id IN NUMBER
1678 ,p_tab_lvrsn_map_cv OUT NOCOPY pqp_utilities.t_config_values
1679 )
1680 IS
1681 --
1682 l_proc_name VARCHAR2(80)
1683 := g_proc_name || 'fetch_leaving_reason_map_cv';
1684 l_proc_step PLS_INTEGER;
1685 l_element_type_id NUMBER;
1686 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
1687 l_tab_config_values pqp_utilities.t_config_values;
1688 i NUMBER;
1689 l_configuration_desc fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
1690 --
1691 BEGIN
1692 --
1693 IF g_debug
1694 THEN
1695 l_proc_step := 10;
1696 debug_enter(l_proc_name);
1697 END IF;
1698
1699 --
1700 -- Call configuration value function to retrieve all data
1701 -- for a configuration type
1702
1703 IF g_debug
1704 THEN
1705 l_proc_step := 20;
1706 DEBUG(l_proc_name, l_proc_step);
1707 DEBUG('p_business_group_id: ' || p_business_group_id);
1708 END IF;
1709
1710 l_configuration_type := 'PQP_GB_PENSERVER_SER_LVRSN_MAP';
1711 l_configuration_desc :=
1712 get_config_type_desc(p_config_type => l_configuration_type);
1713
1714 pqp_utilities.get_config_type_values(
1715 p_configuration_type => l_configuration_type
1716 ,p_business_group_id => p_business_group_id
1717 ,p_legislation_code => g_legislation_code
1718 ,p_tab_config_values => l_tab_config_values
1719 );
1720
1721 IF g_debug
1722 THEN
1723 l_proc_step := 30;
1724 DEBUG(l_proc_name, l_proc_step);
1725 DEBUG('l_configuration_type: ' || l_configuration_type);
1726 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1727 END IF;
1728
1729 -- Return the
1730 -- collection for leaving reason map
1731 p_tab_lvrsn_map_cv := l_tab_config_values;
1732
1733 IF l_tab_config_values.COUNT = 0 THEN
1734
1735 -- Raise setup error
1736 pqp_gb_psi_functions.store_extract_exceptions(
1737 p_extract_type => 'SERVICE_HISTORY'
1738 ,p_error_number => 92799
1739 ,p_error_text => 'BEN_92799_EXT_PSI_NO_CONFIG'
1740 ,p_token1 => 'Penserver Interface'
1741 ,p_token2 => l_configuration_desc
1742 ,p_error_warning_flag => 'E'
1743 );
1744
1745 END IF; -- End if of config values count check ...
1746
1747 -- Leaving Reason PCV_INFORMATION1
1748 -- Penserver Leaving Reason Code PCV_INFORMATION2
1749
1750 i := l_tab_config_values.FIRST;
1751
1752 IF g_debug
1753 THEN
1754 l_proc_step := 40;
1755 DEBUG(l_proc_name, l_proc_step);
1756 END IF;
1757
1758 WHILE i IS NOT NULL
1759 LOOP
1760 IF g_debug
1761 THEN
1762 DEBUG(
1763 'Leaving Reason: '
1764 || l_tab_config_values(i).pcv_information1
1765 );
1766 DEBUG(
1767 'Penserver Leaving Reason Code: '
1768 || l_tab_config_values(i).pcv_information2
1769 );
1770 END IF;
1771
1772 i := l_tab_config_values.NEXT(i);
1773 END LOOP;
1774
1775 IF g_debug
1776 THEN
1777 l_proc_step := 50;
1778 debug_exit(l_proc_name);
1779 END IF;
1780 EXCEPTION
1781 WHEN OTHERS
1782 THEN
1783 clear_cache;
1784
1785 IF SQLCODE <> hr_utility.hr_error_number
1786 THEN
1787 debug_others(l_proc_name, l_proc_step);
1788
1789 IF g_debug
1790 THEN
1791 DEBUG('Leaving: ' || l_proc_name, -999);
1792 END IF;
1793
1794 fnd_message.raise_error;
1795 ELSE
1796 RAISE;
1797 END IF;
1798 END fetch_leaving_reason_map_cv;
1799
1800 -- This procedure fetches the employment type configuration values
1801 -- for penserver
1802 -- ----------------------------------------------------------------------------
1803 -- |----------------------------< fetch_empl_type_map_cv >--------------------|
1804 -- ----------------------------------------------------------------------------
1805 PROCEDURE fetch_empl_type_map_cv
1806 IS
1807 --
1808 l_proc_name VARCHAR2(80)
1809 := g_proc_name || 'fetch_empl_type_map_cv';
1810 l_proc_step PLS_INTEGER;
1811 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
1812 i NUMBER;
1813 l_tab_config_values pqp_utilities.t_config_values;
1814 --
1815 BEGIN
1816 --
1817 IF g_debug
1818 THEN
1819 l_proc_step := 10;
1820 debug_enter(l_proc_name);
1821 END IF;
1822
1823 --
1824 -- Call configuration value function to retrieve all data
1825 -- for a configuration type
1826
1827 IF g_debug
1828 THEN
1829 l_proc_step := 20;
1830 DEBUG(l_proc_name, l_proc_step);
1831 DEBUG('g_business_group_id: ' || g_business_group_id);
1832 END IF;
1833
1834 l_configuration_type := 'PQP_GB_PENSERVER_EMPLYMT_TYPE';
1835
1836 IF pqp_gb_psi_functions.g_assign_category_mapping.COUNT > 0
1837 THEN
1838 -- available from cache
1839 l_tab_config_values :=
1840 pqp_gb_psi_functions.g_assign_category_mapping;
1841 ELSE -- not available so fetch it
1842 pqp_utilities.get_config_type_values(
1843 p_configuration_type => l_configuration_type
1844 ,p_business_group_id => g_business_group_id
1845 ,p_legislation_code => g_legislation_code
1846 ,p_tab_config_values => l_tab_config_values
1847 );
1848 END IF; -- Check whether cv available from collection ...
1849
1850 IF g_debug
1851 THEN
1852 l_proc_step := 30;
1853 DEBUG(l_proc_name, l_proc_step);
1854 DEBUG('l_configuration_type: ' || l_configuration_type);
1855 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1856 END IF;
1857
1858 -- Store the collection in the global
1859 -- collection for pension scheme elements
1860 g_tab_emp_typ_map_cv := l_tab_config_values;
1861 -- Assignment Category PCV_INFORMATION1
1862 -- Penserver Employment Type PCV_INFORMATION2
1863
1864 i := l_tab_config_values.FIRST;
1865
1866 IF g_debug
1867 THEN
1868 l_proc_step := 40;
1869 DEBUG(l_proc_name, l_proc_step);
1870
1871 WHILE i IS NOT NULL
1872 LOOP
1873 DEBUG(
1874 'Assignment Category: '
1875 || l_tab_config_values(i).pcv_information1
1876 );
1877 DEBUG(
1878 'Penserver Employment Type: '
1879 || l_tab_config_values(i).pcv_information2
1880 );
1881 i := l_tab_config_values.NEXT(i);
1882 END LOOP;
1883 END IF;
1884
1885 IF g_debug
1886 THEN
1887 l_proc_step := 50;
1888 debug_exit(l_proc_name);
1889 END IF;
1890 EXCEPTION
1891 WHEN OTHERS
1892 THEN
1893 clear_cache;
1894
1895 IF SQLCODE <> hr_utility.hr_error_number
1896 THEN
1897 debug_others(l_proc_name, l_proc_step);
1898
1899 IF g_debug
1900 THEN
1901 DEBUG('Leaving: ' || l_proc_name, -999);
1902 END IF;
1903
1904 fnd_message.raise_error;
1905 ELSE
1906 RAISE;
1907 END IF;
1908 END fetch_empl_type_map_cv;
1909
1910 -- This function determines whether an extract is a periodic interface or
1911 -- cutover interface based on the data_typ_cd
1912 -- ----------------------------------------------------------------------------
1913 -- |----------------------------< get_extract_type >--------------------------|
1914 -- ----------------------------------------------------------------------------
1915 FUNCTION get_extract_type(p_ext_dfn_id IN NUMBER)
1916 RETURN VARCHAR2
1917 IS
1918 --
1919 -- F -> Full Profile
1920 -- C -> Changes Only
1921 CURSOR csr_get_ext_type
1922 IS
1923 SELECT DECODE(data_typ_cd, 'F', 'CUTOVER', 'C', 'PERIODIC')
1924 FROM ben_ext_dfn
1925 WHERE ext_dfn_id = p_ext_dfn_id;
1926
1927 l_proc_name VARCHAR2(80) := g_proc_name || 'get_extract_type';
1928 l_proc_step PLS_INTEGER;
1929 l_extract_type VARCHAR2(50);
1930 --
1931 BEGIN
1932 --
1933 IF g_debug
1934 THEN
1935 l_proc_step := 10;
1936 debug_enter(l_proc_name);
1937 DEBUG('p_ext_dfn_id: ' || p_ext_dfn_id);
1938 END IF;
1939
1940 OPEN csr_get_ext_type;
1941 FETCH csr_get_ext_type INTO l_extract_type;
1942 CLOSE csr_get_ext_type;
1943
1944 IF g_debug
1945 THEN
1946 l_proc_step := 20;
1947 DEBUG('l_extract_type: ' || l_extract_type);
1948 debug_exit(l_proc_name);
1949 END IF;
1950
1951 RETURN l_extract_type;
1952 EXCEPTION
1953 WHEN OTHERS
1954 THEN
1955 clear_cache;
1956
1957 IF SQLCODE <> hr_utility.hr_error_number
1958 THEN
1959 debug_others(l_proc_name, l_proc_step);
1960
1961 IF g_debug
1962 THEN
1963 DEBUG('Leaving: ' || l_proc_name, -999);
1964 END IF;
1965
1966 fnd_message.raise_error;
1967 ELSE
1968 RAISE;
1969 END IF;
1970 END get_extract_type;
1971
1972 -- This function returns the pension scheme membership details at a given date
1973 -- ----------------------------------------------------------------------------
1974 -- |----------------------------< get_pen_scheme_memb >-----------------------|
1975 -- ----------------------------------------------------------------------------
1976 FUNCTION get_pen_scheme_memb(
1977 p_assignment_id IN NUMBER
1978 ,p_effective_date IN DATE
1979 ,p_tab_pen_sch_map_cv IN pqp_utilities.t_config_values
1980 ,p_rec_ele_ent_details OUT NOCOPY r_ele_ent_details
1981 ,p_partnership_scheme OUT NOCOPY Varchar2
1982 )
1983 RETURN VARCHAR2
1984 IS
1985 --
1986 -- Cursor to get pension scheme element details
1987 -- for this person
1988 CURSOR csr_get_ele_ent_details(c_element_type_id NUMBER)
1989 IS
1990 SELECT pee.element_entry_id, pee.effective_start_date
1991 ,pee.effective_end_date, pel.element_type_id
1992 FROM pay_element_entries_f pee, pay_element_links_f pel
1993 WHERE pee.assignment_id = p_assignment_id
1994 AND pee.entry_type = 'E'
1995 AND pee.element_link_id = pel.element_link_id
1996 AND p_effective_date BETWEEN pee.effective_start_date
1997 AND pee.effective_end_date
1998 AND pel.element_type_id = c_element_type_id
1999 AND p_effective_date BETWEEN pel.effective_start_date
2000 AND pel.effective_end_date
2001 ORDER BY pee.effective_start_date DESC;
2002
2003 l_proc_name VARCHAR2(80)
2004 := g_proc_name || 'get_pen_scheme_memb';
2005 l_proc_step PLS_INTEGER;
2006 l_pension_category pqp_configuration_values.pcv_information1%TYPE;
2007 l_rec_ele_ent_details r_ele_ent_details;
2008 l_element_type_id NUMBER;
2009 i NUMBER;
2010 l_eff_start_date DATE := TO_DATE('01-01-0001', 'DD-MM-YYYY');
2011 l_partnership_scheme varchar2(30);
2012 --
2013 BEGIN
2014 --
2015 IF g_debug
2016 THEN
2017 l_proc_step := 10;
2018 debug_enter(l_proc_name);
2019 DEBUG('p_assignment_id: ' || p_assignment_id);
2020 DEBUG('p_effective_date: '
2021 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
2022 END IF;
2023
2024 i := g_tab_pen_sch_map_cv.FIRST;
2025
2026 WHILE i IS NOT NULL
2027 LOOP
2028 l_element_type_id :=
2029 fnd_number.canonical_to_number(p_tab_pen_sch_map_cv(i).pcv_information1);
2030
2031 IF g_debug
2032 THEN
2033 l_proc_step := 20;
2034 DEBUG(l_proc_name, l_proc_step);
2035 DEBUG('l_element_type_id: ' || l_element_type_id);
2036 END IF;
2037
2038 OPEN csr_get_ele_ent_details(l_element_type_id);
2039 FETCH csr_get_ele_ent_details INTO l_rec_ele_ent_details;
2040
2041 -- We are only interested in the latest pension scheme
2042 -- membership details
2043 IF csr_get_ele_ent_details%FOUND
2044 AND l_eff_start_date < l_rec_ele_ent_details.effective_start_date
2045 THEN
2046 l_pension_category := p_tab_pen_sch_map_cv(i).pcv_information2;
2047 l_eff_start_date :=
2048 l_rec_ele_ent_details.effective_start_date;
2049 --valid only if partnerhip scheme is partner
2050 l_partnership_scheme:= p_tab_pen_sch_map_cv(i).pcv_information3;
2051
2052 IF g_debug
2053 THEN
2054 l_proc_step := 30;
2055 DEBUG('l_pension_category: ' || l_pension_category);
2056 DEBUG('l_partnership_scheme: '||l_partnership_scheme);
2057 DEBUG(
2058 'l_eff_start_date: '
2059 || TO_CHAR(l_eff_start_date, 'DD/MON/YYYY')
2060 );
2061 DEBUG(l_proc_name, l_proc_step);
2062 END IF;
2063 END IF; -- cursor found check ...
2064
2065 CLOSE csr_get_ele_ent_details;
2066 i := p_tab_pen_sch_map_cv.NEXT(i);
2067 END LOOP;
2068
2069 IF g_debug
2070 THEN
2071 l_proc_step := 40;
2072 DEBUG('l_eff_start_date: '
2073 || TO_CHAR(l_eff_start_date, 'DD/MON/YYYY'));
2074 DEBUG('l_pension_category: ' || l_pension_category);
2075 DEBUG(
2076 'l_rec_ele_ent_details.element_entry_id: '
2077 || l_rec_ele_ent_details.element_entry_id
2078 );
2079 DEBUG(
2080 'l_rec_ele_ent_details.effective_start_date: '
2081 || l_rec_ele_ent_details.effective_start_date
2082 );
2083 DEBUG(
2084 'l_rec_ele_ent_details.effective_end_date: '
2085 || l_rec_ele_ent_details.effective_end_date
2086 );
2087 DEBUG('l_partnership_scheme: '||l_partnership_scheme);
2088 DEBUG('l_element_type_id: ' || l_element_type_id);
2089 debug_exit(l_proc_name);
2090 END IF;
2091
2092 p_rec_ele_ent_details := l_rec_ele_ent_details;
2093 p_partnership_scheme :=l_partnership_scheme;
2094 RETURN l_pension_category;
2095 EXCEPTION
2096 WHEN OTHERS
2097 THEN
2098 clear_cache;
2099
2100 IF SQLCODE <> hr_utility.hr_error_number
2101 THEN
2102 debug_others(l_proc_name, l_proc_step);
2103
2104 IF g_debug
2105 THEN
2106 DEBUG('Leaving: ' || l_proc_name, -999);
2107 END IF;
2108
2109 fnd_message.raise_error;
2110 ELSE
2111 RAISE;
2112 END IF;
2113 END get_pen_scheme_memb;
2114
2115 -- This procedure populates pay dated tables with dated table id
2116 -- and table name so that it can be used in the change event
2117 -- collection
2118 -- ----------------------------------------------------------------------------
2119 -- |----------------------------< set_dated_table_collection >----------------|
2120 -- ----------------------------------------------------------------------------
2121 PROCEDURE set_dated_table_collection
2122 IS
2123 --
2124 l_proc_name VARCHAR2(80)
2125 := g_proc_name || 'set_dated_table_collection';
2126 l_proc_step PLS_INTEGER;
2127 l_rec_dated_table csr_get_dated_table_info%ROWTYPE;
2128 l_tab_dated_table t_dated_table;
2129 i NUMBER;
2130 l_table_name t_varchar2;
2131 --
2132 BEGIN
2133 --
2134 IF g_debug
2135 THEN
2136 l_proc_step := 10;
2137 debug_enter(l_proc_name);
2138 END IF;
2139
2140 i := 1;
2141 l_table_name(i) := 'PER_ALL_ASSIGNMENTS_F';
2142 i := i + 1;
2143 l_table_name(i) := 'PAY_ELEMENT_ENTRIES_F';
2144 i := i + 1;
2145 l_table_name(i) := 'PAY_ELEMENT_ENTRY_VALUES_F';
2146 i := i + 1;
2147 l_table_name(i) := 'PER_ALL_PEOPLE_F';
2148 i := i + 1;
2149 l_table_name(i) := 'PER_ABSENCE_ATTENDANCES';
2150 i := i + 1;
2151 l_table_name(i) := 'PER_PERIODS_OF_SERVICE';
2152 i := i + 1;
2153 l_table_name(i) := 'PQP_GAP_DURATION_SUMMARY';
2154
2155 WHILE i > 0
2156 LOOP
2157 IF g_debug
2158 THEN
2159 l_proc_step := 20;
2160 DEBUG(l_proc_name, l_proc_step);
2161 DEBUG('l_table_name(' || i || '): ' || l_table_name(i));
2162 END IF;
2163
2164 OPEN csr_get_dated_table_info(l_table_name(i));
2165 FETCH csr_get_dated_table_info INTO l_rec_dated_table;
2166 CLOSE csr_get_dated_table_info;
2167 l_tab_dated_table(l_rec_dated_table.dated_table_id) :=
2168 l_rec_dated_table;
2169
2170 IF g_debug
2171 THEN
2172 l_proc_step := 30;
2173 DEBUG(l_proc_name, l_proc_step);
2174 DEBUG('dated_table_id: ' || l_rec_dated_table.dated_table_id);
2175 DEBUG('Table Name: ' || l_rec_dated_table.table_name);
2176 DEBUG('Surrogate Key Col: '
2177 || l_rec_dated_table.surrogate_key_name);
2178 END IF;
2179
2180 i := i - 1;
2181 END LOOP;
2182
2183 -- set the global
2184 g_tab_dated_table := l_tab_dated_table;
2185
2186 IF g_debug
2187 THEN
2188 l_proc_step := 40;
2189 debug_exit(l_proc_name);
2190 END IF;
2191 EXCEPTION
2192 WHEN OTHERS
2193 THEN
2194 clear_cache;
2195
2196 IF SQLCODE <> hr_utility.hr_error_number
2197 THEN
2198 debug_others(l_proc_name, l_proc_step);
2199
2200 IF g_debug
2201 THEN
2202 DEBUG('Leaving: ' || l_proc_name, -999);
2203 END IF;
2204
2205 fnd_message.raise_error;
2206 ELSE
2207 RAISE;
2208 END IF;
2209 END set_dated_table_collection;
2210
2211 --
2212
2213 -- This procedure is used to populate event groups collection
2214 -- for service history
2215 -- ----------------------------------------------------------------------------
2216 -- |----------------------------< set_event_group_collection >----------------|
2217 -- ----------------------------------------------------------------------------
2218 PROCEDURE set_event_group_collection
2219 IS
2220 --
2221 l_proc_name VARCHAR2(80)
2222 := g_proc_name || 'set_event_group_collection';
2223 l_proc_step PLS_INTEGER;
2224 l_rec_event_group csr_get_event_group_info%ROWTYPE;
2225 l_tab_event_group t_event_group;
2226 i NUMBER;
2227 l_event_group t_varchar2;
2228 --
2229 BEGIN
2230 --
2231 IF g_debug
2232 THEN
2233 l_proc_step := 10;
2234 debug_enter(l_proc_name);
2235 END IF;
2236
2237 i := 1;
2238 l_event_group(i) := 'PQP_GB_PSI_SER_ABSENCES';
2239 i := i + 1;
2240
2241 --For Bug 7034476:Removed event group
2242 /*
2243 l_event_group(i) := 'PQP_GB_PSI_ASSIGNMENT_STATUS';
2244 i := i + 1;
2245 */
2246
2247 l_event_group(i) := 'PQP_GB_PSI_SER_LEAVER';
2248 i := i + 1;
2249 l_event_group(i) := 'PQP_GB_PSI_SER_PENSIONS';
2250 i := i + 1;
2251 l_event_group(i) := 'PQP_GB_PSI_NEW_HIRE';
2252 -- i := i + 1;
2253 -- l_event_group(i) := 'PQP_GB_PSI_NI_NUMBER';
2254 -- i := i + 1;
2255 -- l_event_group(i) := 'PQP_GB_PSI_ASSIGNMENT_NUMBER';
2256 i := i + 1;
2257 l_event_group(i) := 'PQP_GB_PSI_SER_NEW_ABSENCES';
2258
2259 --For Bug 7034476:Removed event groups
2260 /*
2261 i := i + 1;
2262 l_event_group(i) := 'PQP_GB_PSI_EMP_TERMINATIONS';
2263 i := i + 1;
2264 l_event_group(i) := 'PQP_GB_PSI_SER_GAP_TRANSITION';
2265 */
2266
2267 --For Bug 5998108:Start
2268 i := i + 1;
2269 l_event_group(i) := 'PQP_GB_PSI_ASG_CATEGORY';
2270 --For Bug 5998108:End
2271
2272 WHILE i > 0
2273 LOOP
2274 IF g_debug
2275 THEN
2276 l_proc_step := 20;
2277 DEBUG(l_proc_name, l_proc_step);
2278 DEBUG('l_event_group(' || i || '): ' || l_event_group(i));
2279 END IF;
2280
2281 OPEN csr_get_event_group_info(l_event_group(i));
2282 FETCH csr_get_event_group_info INTO l_rec_event_group;
2283
2284 IF csr_get_event_group_info%NOTFOUND
2285 THEN
2286 -- Raise an error
2287 pqp_gb_psi_functions.store_extract_exceptions(
2288 p_extract_type => 'SERVICE_HISTORY'
2289 ,p_error_number => 94423
2290 ,p_error_text => 'BEN_94423_EXT_PSI_NO_EVNT_GRP'
2291 ,p_token1 => l_event_group(i)
2292 ,p_error_warning_flag => 'E'
2293 );
2294 END IF;
2295
2296 CLOSE csr_get_event_group_info;
2297 l_tab_event_group(l_rec_event_group.event_group_id) :=
2298 l_rec_event_group;
2299
2300 IF g_debug
2301 THEN
2302 l_proc_step := 30;
2303 DEBUG(l_proc_name, l_proc_step);
2304 DEBUG('event_group_id: ' || l_rec_event_group.event_group_id);
2305 DEBUG('event_group_name: ' || l_rec_event_group.event_group_name);
2306 DEBUG('event_group_type: ' || l_rec_event_group.event_group_type);
2307 END IF;
2308
2309 i := i - 1;
2310 END LOOP;
2311
2312 -- set the global
2313 g_tab_event_group := l_tab_event_group;
2314
2315 IF g_debug
2316 THEN
2317 l_proc_step := 40;
2318 debug_exit(l_proc_name);
2319 END IF;
2320 EXCEPTION
2321 WHEN OTHERS
2322 THEN
2323 clear_cache;
2324
2325 IF SQLCODE <> hr_utility.hr_error_number
2326 THEN
2327 debug_others(l_proc_name, l_proc_step);
2328
2329 IF g_debug
2330 THEN
2331 DEBUG('Leaving: ' || l_proc_name, -999);
2332 END IF;
2333
2334 fnd_message.raise_error;
2335 ELSE
2336 RAISE;
2337 END IF;
2338 END set_event_group_collection;
2339
2340 -- This function returns the last approved run date for
2341 -- periodic changes
2342 -- ----------------------------------------------------------------------------
2343 -- |----------------------------< get_last_run_date >-------------------------|
2344 -- ----------------------------------------------------------------------------
2345 FUNCTION get_last_run_date
2346 RETURN DATE
2347 IS
2348 --
2349 -- Cursor to fetch the last successful approved run date
2350 CURSOR csr_get_run_date
2351 IS
2352 SELECT MAX(eff_dt)
2353 FROM ben_ext_rslt
2354 WHERE ext_dfn_id = g_ext_dfn_id
2355 AND business_group_id = g_business_group_id
2356 AND ext_stat_cd = 'A';
2357
2358 l_proc_name VARCHAR2(80) := g_proc_name || 'get_last_run_date';
2359 l_proc_step PLS_INTEGER;
2360 l_run_date DATE;
2361 --
2362 BEGIN
2363 --
2364 IF g_debug
2365 THEN
2366 l_proc_step := 10;
2367 debug_enter(l_proc_name, l_proc_step);
2368 END IF;
2369
2370 -- Get the run date
2371 OPEN csr_get_run_date;
2372 FETCH csr_get_run_date INTO l_run_date;
2373 CLOSE csr_get_run_date;
2374
2375 IF g_debug
2376 THEN
2377 DEBUG('l_run_date: ' || TO_CHAR(l_run_date, 'DD/MON/YYYY'));
2378 END IF;
2379
2380 IF l_run_date IS NULL
2381 THEN
2382 -- Set the run date to be cutover date
2383 l_run_date := g_cutover_date;
2384 END IF; -- End if of l_run_date is null check ...
2385
2386 l_run_date := l_run_date + 1;
2387
2388 IF g_debug
2389 THEN
2390 l_proc_step := 20;
2391 DEBUG('l_run_date: ' || TO_CHAR(l_run_date, 'DD/MON/YYYY'));
2392 debug_exit(l_proc_name);
2393 END IF;
2394
2395 RETURN l_run_date;
2396 EXCEPTION
2397 WHEN OTHERS
2398 THEN
2399 clear_cache;
2400
2401 IF SQLCODE <> hr_utility.hr_error_number
2402 THEN
2403 debug_others(l_proc_name, l_proc_step);
2404
2405 IF g_debug
2406 THEN
2407 DEBUG('Leaving: ' || l_proc_name, -999);
2408 END IF;
2409
2410 fnd_message.raise_error;
2411 ELSE
2412 RAISE;
2413 END IF;
2414 END get_last_run_date;
2415
2416 -- This procedure is used to set any globals needed for this extract
2417 --
2418 -- ----------------------------------------------------------------------------
2419 -- |----------------------------< set_service_history_globals >---------------|
2420 -- ----------------------------------------------------------------------------
2421 PROCEDURE set_service_history_globals(
2422 p_business_group_id IN NUMBER
2423 ,p_effective_date IN DATE
2424 )
2425 IS
2426 --
2427 l_proc_name VARCHAR2(80)
2428 := g_proc_name || 'set_service_history_globals';
2429 l_proc_step PLS_INTEGER;
2430 l_input_value_name pay_input_values_f.NAME%TYPE;
2431 l_input_value_id NUMBER;
2432 l_element_type_id NUMBER;
2433 l_tab_config_values pqp_utilities.t_config_values;
2434 i NUMBER;
2435 l_rec_asg_sts_dtls csr_get_asg_sts_dtls%ROWTYPE;
2436 --
2437 BEGIN
2438 --
2439 IF g_debug
2440 THEN
2441 l_proc_step := 10;
2442 debug_enter(l_proc_name);
2443 DEBUG('p_business_group_id: ' || p_business_group_id);
2444 DEBUG('p_effective_date: '
2445 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
2446 END IF;
2447
2448 -- set global variables
2449 g_business_group_id := p_business_group_id;
2450 g_extract_type :=
2451 get_extract_type(p_ext_dfn_id => g_ext_dfn_id);
2452 -- IF g_extract_type = 'CUTOVER'
2453 -- THEN
2454 -- g_effective_date := g_cutover_date;
2455 -- ELSIF g_extract_type = 'PERIODIC'
2456 -- THEN
2457 -- g_effective_date := p_effective_date;
2458 -- END IF; -- End if of p_extract_type is cutover check ...
2459
2460 -- Cutover date is passed down from concurrent request
2461
2462 g_effective_date := p_effective_date;
2463
2464 IF g_debug
2465 THEN
2466 l_proc_step := 20;
2467 DEBUG(l_proc_name, l_proc_step);
2468 END IF;
2469
2470 -- Get the assignment status type id for
2471 -- active assignments
2472 get_asg_status_type(
2473 p_per_system_status => 'ACTIVE_ASSIGN'
2474 ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
2475 );
2476 g_active_asg_sts_id :=
2477 l_rec_asg_sts_dtls.assignment_status_type_id;
2478
2479 IF g_debug
2480 THEN
2481 l_proc_step := 30;
2482 DEBUG(l_proc_name, l_proc_step);
2483 END IF;
2484
2485 -- Get the assignment status type id for
2486 -- terminations
2487 get_asg_status_type(
2488 p_per_system_status => 'TERM_ASSIGN'
2489 ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
2490 );
2491 g_terminate_asg_sts_id :=
2492 l_rec_asg_sts_dtls.assignment_status_type_id;
2493
2494 IF g_debug
2495 THEN
2496 l_proc_step := 40;
2497 DEBUG(l_proc_name, l_proc_step);
2498 END IF;
2499
2500 fetch_empl_type_map_cv;
2501
2502 -- Fetch data from configuration values and store in a
2503 -- global collection
2504 -- Fetch event map configuration values
2505
2506 IF g_debug
2507 THEN
2508 l_proc_step := 50;
2509 DEBUG(l_proc_name, l_proc_step);
2510 END IF;
2511
2512 -- Fetch event map configuration values
2513 fetch_event_map_cv;
2514
2515 IF g_debug
2516 THEN
2517 l_proc_step := 75;
2518 DEBUG(l_proc_name, l_proc_step);
2519 END IF;
2520
2521 -- Fetch leaving reason configuration map
2522 fetch_leaving_reason_map_cv
2523 (p_business_group_id => p_business_group_id
2524 ,p_tab_lvrsn_map_cv => g_tab_lvrsn_map_cv
2525 );
2526
2527
2528 IF g_debug
2529 THEN
2530 l_proc_step := 60;
2531 DEBUG(l_proc_name, l_proc_step);
2532 END IF;
2533
2534 -- Fetch pension scheme configuration values
2535 fetch_pension_scheme_map_cv(
2536 p_business_group_id => p_business_group_id
2537 ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv
2538 );
2539 i := g_tab_pen_sch_map_cv.FIRST;
2540 l_input_value_name := 'Opt Out Date';
2541
2542 IF g_debug
2543 THEN
2544 l_proc_step := 70;
2545 DEBUG(l_proc_name, l_proc_step);
2546 END IF;
2547
2548 WHILE i IS NOT NULL
2549 LOOP
2550 l_element_type_id :=
2551 fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1);
2552 l_input_value_id :=
2553 get_input_value_id(
2554 p_element_type_id => l_element_type_id
2555 ,p_effective_date => g_effective_date
2556 ,p_input_value_name => l_input_value_name
2557 );
2558 g_tab_pen_ele_ids(l_element_type_id).element_type_id :=
2559 l_element_type_id;
2560 g_tab_pen_ele_ids(l_element_type_id).input_value_name :=
2561 l_input_value_name;
2562 g_tab_pen_ele_ids(l_element_type_id).input_value_id :=
2563 l_input_value_id;
2564
2565 IF g_debug
2566 THEN
2567 DEBUG(
2568 'Penserver Pension Scheme: '
2569 || g_tab_pen_sch_map_cv(i).pcv_information2
2570 );
2571 DEBUG(
2572 'Template Pension Scheme: '
2573 || g_tab_pen_sch_map_cv(i).pcv_information1
2574 );
2575 DEBUG('Element Type ID: ' || l_element_type_id);
2576 DEBUG('Input Value Name: ' || l_input_value_name);
2577 DEBUG('Input Value ID: ' || l_input_value_id);
2578 END IF;
2579
2580 i :=
2581 g_tab_pen_sch_map_cv.NEXT(i);
2582 END LOOP;
2583
2584 IF g_extract_type = 'PERIODIC'
2585 THEN
2586
2587 IF g_debug
2588 THEN
2589 l_proc_step := 80;
2590 DEBUG(l_proc_name, l_proc_step);
2591 END IF;
2592
2593 -- populated dated table ids
2594 set_dated_table_collection;
2595
2596 -- populate event group colleciton
2597 IF g_debug
2598 THEN
2599 l_proc_step := 90;
2600 DEBUG(l_proc_name, l_proc_step);
2601 END IF;
2602
2603 set_event_group_collection;
2604 END IF; -- End if of extract type = periodic check ...
2605
2606 IF g_debug
2607 THEN
2608 l_proc_step := 100;
2609 DEBUG('g_business_group_id: ' || g_business_group_id);
2610 DEBUG('g_effective_date: '
2611 || TO_CHAR(g_effective_date, 'DD/MON/YYYY'));
2612 DEBUG(
2613 'g_effective_start_date: '
2614 || TO_CHAR(g_effective_start_date, 'DD/MON/YYYY')
2615 );
2616 DEBUG(
2617 'g_effective_end_date: '
2618 || TO_CHAR(g_effective_end_date, 'DD/MON/YYYY')
2619 );
2620 DEBUG('g_extract_type: ' || g_extract_type);
2621 DEBUG('g_active_asg_sts_id: ' || g_active_asg_sts_id);
2622 DEBUG('g_terminate_asg_sts_id: ' || g_terminate_asg_sts_id);
2623 debug_exit(l_proc_name);
2624 END IF;
2625 EXCEPTION
2626 WHEN OTHERS
2627 THEN
2628 clear_cache;
2629
2630 IF SQLCODE <> hr_utility.hr_error_number
2631 THEN
2632 debug_others(l_proc_name, l_proc_step);
2633
2634 IF g_debug
2635 THEN
2636 DEBUG('Leaving: ' || l_proc_name, -999);
2637 END IF;
2638
2639 fnd_message.raise_error;
2640 ELSE
2641 RAISE;
2642 END IF;
2643 END set_service_history_globals;
2644
2645 -- This function returns the penserv category for
2646 -- a given assignment category
2647 -- ----------------------------------------------------------------------------
2648 -- |---------------------< get_psi_emp_type >---------------------------------|
2649 -- ----------------------------------------------------------------------------
2650 FUNCTION get_psi_emp_type(p_employment_category IN VARCHAR2)
2651 RETURN VARCHAR2
2652 IS
2653 --
2654 l_proc_name VARCHAR2(80) := g_proc_name || 'get_psi_emp_type';
2655 l_proc_step PLS_INTEGER;
2656 i NUMBER;
2657 l_psi_emp_type pqp_configuration_values.pcv_information1%TYPE;
2658 --
2659 BEGIN
2660 --
2661 IF g_debug
2662 THEN
2663 l_proc_step := 10;
2664 debug_enter(l_proc_name);
2665 DEBUG('p_employment_category: ' || p_employment_category);
2666 END IF;
2667
2668 i := g_tab_emp_typ_map_cv.FIRST;
2669
2670 WHILE i IS NOT NULL
2671 LOOP
2672 IF g_tab_emp_typ_map_cv(i).pcv_information1 = p_employment_category
2673 THEN
2674 l_psi_emp_type := g_tab_emp_typ_map_cv(i).pcv_information2;
2675 EXIT;
2676 END IF; -- assignment category in collection check ...
2677
2678 i := g_tab_emp_typ_map_cv.NEXT(i);
2679 END LOOP;
2680
2681 IF g_debug
2682 THEN
2683 l_proc_step := 20;
2684 DEBUG('l_psi_emp_type: ' || l_psi_emp_type);
2685 debug_exit(l_proc_name);
2686 END IF;
2687
2688 RETURN l_psi_emp_type;
2689 EXCEPTION
2690 WHEN OTHERS
2691 THEN
2692 clear_cache;
2693
2694 IF SQLCODE <> hr_utility.hr_error_number
2695 THEN
2696 debug_others(l_proc_name, l_proc_step);
2697
2698 IF g_debug
2699 THEN
2700 DEBUG('Leaving: ' || l_proc_name, -999);
2701 END IF;
2702
2703 fnd_message.raise_error;
2704 ELSE
2705 RAISE;
2706 END IF;
2707 END get_psi_emp_type;
2708
2709 -- This function returns the latest start date for a person
2710 -- ----------------------------------------------------------------------------
2711 -- |---------------------< get_per_latest_start_date >------------------------|
2712 -- ----------------------------------------------------------------------------
2713 FUNCTION get_per_latest_start_date(
2714 p_person_id IN NUMBER
2715 ,p_effective_date IN DATE
2716 )
2717 RETURN DATE
2718 IS
2719 --
2720 -- Cursor to get latest start date
2721 CURSOR csr_get_latest_date
2722 IS
2723 SELECT DECODE(per.current_employee_flag, 'Y', pps.date_start, NULL)
2724 FROM per_all_people_f per, per_periods_of_service pps
2725 WHERE per.person_id = p_person_id
2726 AND pps.person_id = p_person_id
2727 AND p_effective_date BETWEEN per.effective_start_date
2728 AND NVL(
2729 per.effective_end_date
2730 ,TO_DATE('31/12/4712', 'DD/MM/YYYY')
2731 )
2732 AND p_effective_date BETWEEN pps.date_start
2733 AND NVL(
2734 pps.actual_termination_date
2735 ,TO_DATE('31/12/4712', 'DD/MM/YYYY')
2736 );
2737
2738 l_proc_name VARCHAR2(80)
2739 := g_proc_name || 'get_per_latest_start_date';
2740 l_proc_step PLS_INTEGER;
2741 l_latest_start_date DATE;
2742 --
2743 BEGIN
2744 --
2745 IF g_debug
2746 THEN
2747 l_proc_step := 10;
2748 debug_enter(l_proc_name);
2749 DEBUG('p_person_id: ' || p_person_id);
2750 DEBUG('p_effective_date: '
2751 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
2752 END IF;
2753
2754 OPEN csr_get_latest_date;
2755 FETCH csr_get_latest_date INTO l_latest_start_date;
2756 CLOSE csr_get_latest_date;
2757
2758 IF g_debug
2759 THEN
2760 l_proc_step := 20;
2761 DEBUG(
2762 'l_latest_start_date: '
2763 || TO_CHAR(l_latest_start_date, 'DD/MON/YYYY')
2764 );
2765 debug_exit(l_proc_name);
2766 END IF;
2767
2768 RETURN l_latest_start_date;
2769 EXCEPTION
2770 WHEN OTHERS
2771 THEN
2772 clear_cache;
2773
2774 IF SQLCODE <> hr_utility.hr_error_number
2775 THEN
2776 debug_others(l_proc_name, l_proc_step);
2777
2778 IF g_debug
2779 THEN
2780 DEBUG('Leaving: ' || l_proc_name, -999);
2781 END IF;
2782
2783 fnd_message.raise_error;
2784 ELSE
2785 RAISE;
2786 END IF;
2787 END get_per_latest_start_date;
2788
2789 -- This procedure gets assignment details for a given assignment id
2790 -- ----------------------------------------------------------------------------
2791 -- |---------------------< get_asg_details >----------------------------------|
2792 -- ----------------------------------------------------------------------------
2793 PROCEDURE get_asg_details(
2794 p_assignment_id IN NUMBER
2795 ,p_effective_date IN DATE
2796 ,p_rec_asg_details OUT NOCOPY r_asg_details
2797 )
2798 IS
2799 --
2800 -- cursor to fetch assignment details for a given assignment
2801 CURSOR csr_get_asg_details
2802 IS
2803 SELECT person_id, effective_start_date, effective_end_date
2804 ,assignment_number, primary_flag, normal_hours
2805 ,assignment_status_type_id, employment_category
2806 FROM per_all_assignments_f
2807 WHERE assignment_id = p_assignment_id
2808 AND p_effective_date BETWEEN effective_start_date
2809 AND effective_end_date
2810 ORDER BY effective_start_date DESC;
2811
2812 l_proc_name VARCHAR2(80) := g_proc_name || 'get_asg_details';
2813 l_proc_step PLS_INTEGER;
2814 l_rec_asg_details r_asg_details;
2815 --
2816 BEGIN
2817 --
2818 IF g_debug
2819 THEN
2820 l_proc_step := 10;
2821 debug_enter(l_proc_name);
2822 DEBUG('p_assignment_id: ' || p_assignment_id);
2823 DEBUG('p_effective_date: '
2824 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
2825 END IF;
2826
2827 OPEN csr_get_asg_details;
2828 FETCH csr_get_asg_details INTO l_rec_asg_details;
2829 CLOSE csr_get_asg_details;
2830 p_rec_asg_details := l_rec_asg_details;
2831
2832 IF g_debug
2833 THEN
2834 l_proc_step := 20;
2835 DEBUG(l_proc_name, l_proc_step);
2836 DEBUG('Person ID: ' || l_rec_asg_details.person_id);
2837 DEBUG(
2838 'Effective Start Date: '
2839 || TO_CHAR(l_rec_asg_details.effective_start_date, 'DD/MON/YYYY')
2840 );
2841 DEBUG(
2842 'Effective End Date: '
2843 || TO_CHAR(l_rec_asg_details.effective_end_date, 'DD/MON/YYYY')
2844 );
2845 DEBUG('Assignment Number: ' || l_rec_asg_details.assignment_number);
2846 DEBUG('Primary Flag: ' || l_rec_asg_details.primary_flag);
2847 DEBUG('Normal Hours: ' || l_rec_asg_details.normal_hours);
2848 DEBUG(
2849 'Assignment Status Type ID: '
2850 || l_rec_asg_details.assignment_status_type_id
2851 );
2852 DEBUG('Assignment Category: '
2853 || l_rec_asg_details.employment_category);
2854 END IF;
2855
2856 IF g_debug
2857 THEN
2858 l_proc_step := 30;
2859 debug_exit(l_proc_name);
2860 END IF;
2861 EXCEPTION
2862 WHEN OTHERS
2863 THEN
2864 clear_cache;
2865
2866 IF SQLCODE <> hr_utility.hr_error_number
2867 THEN
2868 debug_others(l_proc_name, l_proc_step);
2869
2870 IF g_debug
2871 THEN
2872 DEBUG('Leaving: ' || l_proc_name, -999);
2873 END IF;
2874
2875 fnd_message.raise_error;
2876 ELSE
2877 RAISE;
2878 END IF;
2879 END get_asg_details;
2880
2881 -- This procedure returns the codes for a particular event from
2882 -- configuration event mappings value
2883 -- ----------------------------------------------------------------------------
2884 -- |---------------------< get_service_history_code >-------------------------|
2885 -- ----------------------------------------------------------------------------
2886 PROCEDURE get_service_history_code(
2887 p_event_desc IN VARCHAR2
2888 ,p_pension_scheme IN VARCHAR2
2889 ,p_employment_type IN VARCHAR2
2890 ,p_event_source IN VARCHAR2
2891 ,p_absence_type IN NUMBER
2892 ,p_asg_status IN NUMBER
2893 ,p_partnership_scheme IN VARCHAR2 --115.14
2894 ,p_start_reason OUT NOCOPY VARCHAR2
2895 ,p_scheme_category OUT NOCOPY VARCHAR2
2896 ,p_scheme_status OUT NOCOPY VARCHAR2
2897 )
2898 IS
2899 --
2900 l_proc_name VARCHAR2(80)
2901 := g_proc_name || 'get_service_history_code';
2902 l_proc_step PLS_INTEGER;
2903 l_start_reason pqp_configuration_values.pcv_information1%TYPE;
2904 l_scheme_category pqp_configuration_values.pcv_information1%TYPE;
2905 l_scheme_status pqp_configuration_values.pcv_information1%TYPE;
2906 l_match VARCHAR2(10);
2907 i NUMBER;
2908 l_source_pension_scheme pqp_configuration_values.pcv_information1%TYPE;
2909 l_event_description pqp_configuration_values.pcv_information1%TYPE;
2910 l_pension_scheme pqp_configuration_values.pcv_information1%TYPE;
2911 l_employment_type pqp_configuration_values.pcv_information1%TYPE;
2912 --
2913 BEGIN
2914 --
2915 IF g_debug
2916 THEN
2917 l_proc_step := 10;
2918 debug_enter(l_proc_name);
2919 DEBUG('p_event_desc: ' || p_event_desc);
2920 DEBUG('p_pension_scheme: ' || p_pension_scheme);
2921 DEBUG('p_partnership_scheme: '||p_partnership_scheme);
2922 DEBUG('p_employment_type: ' || p_employment_type);
2923 DEBUG('p_event_source: ' || p_event_source);
2924 DEBUG('p_absence_type: ' || p_absence_type);
2925 DEBUG('p_asg_status: ' || p_asg_status);
2926 END IF;
2927
2928 -- Translate all classic plus scheme to classic
2929 l_source_pension_scheme := p_pension_scheme;
2930
2931 IF p_pension_scheme = 'CLASSPLUS'
2932 THEN
2933 l_source_pension_scheme := 'PREMIUM';
2934 END IF;
2935
2936 l_match := 'N';
2937 i := g_tab_event_map_cv.FIRST;
2938
2939 WHILE i IS NOT NULL
2940 LOOP
2941 l_event_description := g_tab_event_map_cv(i).pcv_information1;
2942 l_employment_type := g_tab_event_map_cv(i).pcv_information9;
2943 l_pension_scheme := g_tab_event_map_cv(i).pcv_information10;
2944
2945 IF g_debug
2946 THEN
2947 l_proc_step := 20;
2948 DEBUG(l_proc_name, l_proc_step);
2949 DEBUG('l_source_pension_scheme: ' || l_source_pension_scheme);
2950 DEBUG('l_event_description: ' || l_event_description);
2951 DEBUG('l_employment_type: ' || l_employment_type);
2952 DEBUG('l_pension_scheme: ' || l_pension_scheme);
2953 END IF;
2954
2955 IF p_event_desc = l_event_description
2956 AND (
2957 (
2958 NVL(l_source_pension_scheme, hr_api.g_varchar2) =
2959 NVL(l_pension_scheme, hr_api.g_varchar2)
2960 )
2961 OR (
2962 l_source_pension_scheme IS NOT NULL
2963 AND l_pension_scheme = 'ANY'
2964 )
2965 OR (
2966 l_source_pension_scheme IN('CLASSIC', 'PREMIUM')
2967 AND l_pension_scheme = 'CLASSPREM'
2968 )
2969 )
2970 AND (
2971 p_employment_type = l_employment_type
2972 OR ( --115.70 5897563
2973 --ANY refers only to REGULAR and CASUAL employment types
2974 -- p_employment_type IS NOT NULL
2975 nvl( p_employment_type,hr_api.g_varchar2) in
2976 ('REGULAR','CASUAL')
2977 AND l_employment_type = 'ANY'
2978 )
2979 )
2980 THEN
2981 IF p_event_source = 'ABS'
2982 THEN
2983 IF p_absence_type = g_tab_event_map_cv(i).pcv_information7
2984 THEN
2985 l_match := 'Y';
2986 END IF; -- absence type
2987 ELSIF p_event_source = 'ASG'
2988 THEN
2989 IF p_asg_status = g_tab_event_map_cv(i).pcv_information8
2990 THEN
2991 l_match := 'Y';
2992 END IF; -- asg status
2993 ELSE -- not abs or asg
2994 l_match := 'Y';
2995 END IF; -- event source abs
2996 END IF; -- code match check ...
2997
2998 IF l_match = 'Y'
2999 THEN
3000 l_start_reason := g_tab_event_map_cv(i).pcv_information11;
3001 l_scheme_category := g_tab_event_map_cv(i).pcv_information2;
3002
3003 --115.14 Replace n in scheme category with Partnership scheme code
3004 if l_source_pension_scheme='PARTNER'
3005 and l_scheme_category in ('Qn','Nn','Sn') then
3006 l_scheme_category:=
3007 substr(l_scheme_category,1,length(l_scheme_category)-1)||p_partnership_scheme;
3008 end if;
3009
3010 l_scheme_status := g_tab_event_map_cv(i).pcv_information3;
3011 EXIT;
3012 END IF;
3013
3014 i := g_tab_event_map_cv.NEXT(i);
3015 END LOOP;
3016
3017 IF g_debug
3018 THEN
3019 l_proc_step := 30;
3020 DEBUG(l_proc_name, l_proc_step);
3021 DEBUG('l_match: ' || l_match);
3022 DEBUG('l_start_reason: ' || l_start_reason);
3023 DEBUG('l_scheme_category: ' || l_scheme_category);
3024 DEBUG('l_scheme_status: ' || l_scheme_status);
3025 END IF;
3026
3027 IF l_match = 'N'
3028 THEN
3029 -- codes does not match
3030 -- raise error
3031 NULL;
3032 END IF;
3033
3034 p_start_reason := l_start_reason;
3035 p_scheme_category := l_scheme_category;
3036 p_scheme_status := l_scheme_status;
3037
3038 IF g_debug
3039 THEN
3040 l_proc_step := 40;
3041 debug_exit(l_proc_name);
3042 END IF;
3043 EXCEPTION
3044 WHEN OTHERS
3045 THEN
3046 clear_cache;
3047
3048 IF SQLCODE <> hr_utility.hr_error_number
3049 THEN
3050 debug_others(l_proc_name, l_proc_step);
3051
3052 IF g_debug
3053 THEN
3054 DEBUG('Leaving: ' || l_proc_name, -999);
3055 END IF;
3056
3057 fnd_message.raise_error;
3058 ELSE
3059 RAISE;
3060 END IF;
3061 END get_service_history_code;
3062
3063 -- This procedure returns the absence event code for sickness and
3064 -- maternity absence for pay transitions
3065 -- ----------------------------------------------------------------------------
3066 -- |---------------------< get_gap_transition_code >--------------------------|
3067 -- ----------------------------------------------------------------------------
3068 PROCEDURE get_gap_transition_code(
3069 p_assignment_id IN NUMBER
3070 ,p_absence_attendance_id IN NUMBER
3071 ,p_effective_date IN DATE
3072 ,p_psi_event_code IN VARCHAR2
3073 ,p_absence_event_code OUT NOCOPY VARCHAR2
3074 ,p_rec_gap_details OUT NOCOPY csr_chk_pay_trans%ROWTYPE
3075 )
3076 IS
3077 --
3078
3079 l_proc_name VARCHAR2(80)
3080 := g_proc_name || 'get_gap_transition_code';
3081 l_proc_step PLS_INTEGER;
3082 l_rec_gap_details csr_chk_pay_trans%ROWTYPE;
3083 l_absence_code VARCHAR2(10);
3084 l_return VARCHAR2(10);
3085 --
3086 BEGIN
3087 --
3088 IF g_debug
3089 THEN
3090 l_proc_step := 10;
3091 debug_enter(l_proc_name);
3092 DEBUG('p_assignment_id: ' || p_assignment_id);
3093 DEBUG('p_effective_date: '
3094 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
3095 DEBUG('p_absence_attendance_id: ' || p_absence_attendance_id);
3096 DEBUG('p_psi_event_code: ' || p_psi_event_code);
3097 END IF;
3098
3099 -- Check whether there is a possible transition to half pay/no pay or
3100 -- pension rate from the OSP summary table
3101 OPEN csr_chk_pay_trans(p_assignment_id, p_effective_date
3102 ,p_absence_attendance_id);
3103
3104 LOOP
3105 FETCH csr_chk_pay_trans INTO l_rec_gap_details;
3106 EXIT WHEN csr_chk_pay_trans%NOTFOUND;
3107
3108 IF g_debug
3109 THEN
3110 l_proc_step := 20;
3111 DEBUG(l_proc_name, l_proc_step);
3112 DEBUG('p_psi_event_code: ' || p_psi_event_code);
3113 DEBUG('l_rec_gap_details.gap_level: '|| l_rec_gap_details.gap_level);
3114 END IF;
3115
3116 IF p_psi_event_code = 'S'
3117 THEN -- Sickness
3118 IF l_rec_gap_details.gap_level = 'BAND2'
3119 THEN
3120 l_absence_code := p_psi_event_code || 'H';
3121 ELSIF l_rec_gap_details.gap_level = 'NOBANDMIN'
3122 THEN
3123 --5549469 Replaced Px with P
3124 l_absence_code := p_psi_event_code || 'P';
3125 ELSIF l_rec_gap_details.gap_level = 'NOBAND'
3126 THEN
3127 --5549469 Replaced Nx with N
3128 l_absence_code := p_psi_event_code || 'N';
3129
3130 --5549469 115.16
3131 --Undid prev change
3132 --ELSE
3133 --l_return := 'N';
3134 END IF; -- End if of gap level = BAND 2 check ...
3135 ELSIF p_psi_event_code = 'M'
3136 THEN -- Maternity
3137 IF l_rec_gap_details.gap_level = 'BAND1'
3138 THEN
3139 l_absence_code := p_psi_event_code || 'F'; -- For maternity
3140 ELSIF l_rec_gap_details.gap_level = 'NOBAND'
3141 THEN
3142 l_absence_code := p_psi_event_code || 'N';
3143 --ELSE 5549489 Undid previous change.This is required only in
3144 -- function eval_gap_transition_event
3145 --l_return := 'N';
3146 END IF; -- End if of gap level = BAND1 check ...
3147 END IF; -- End if of sickness check ...
3148 -- populate the variables only if the codes are in the
3149 -- collection
3150
3151 IF g_debug
3152 THEN
3153 l_proc_step := 30;
3154 DEBUG(l_proc_name, l_proc_step);
3155 DEBUG('l_absence_code: ' || l_absence_code);
3156 END IF;
3157
3158 IF l_absence_code IS NOT NULL
3159 THEN
3160 l_return :=
3161 chk_event_in_collection(p_event_code => l_absence_code);
3162
3163 IF l_return = 'Y'
3164 THEN
3165 p_rec_gap_details := l_rec_gap_details;
3166 p_absence_event_code := l_absence_code;
3167 EXIT; -- Exit from cursor loop
3168 END IF; -- return check...
3169 END IF; -- absence code is not null check ...
3170 END LOOP; -- check pay transition cursor ...
3171
3172 CLOSE csr_chk_pay_trans;
3173
3174 IF g_debug
3175 THEN
3176 l_proc_step := 40;
3177 DEBUG(l_proc_name, l_proc_step);
3178 DEBUG('l_return: ' || l_return);
3179 DEBUG('p_absence_event_code: '||p_absence_event_code);
3180 debug_exit(l_proc_name);
3181 END IF;
3182 EXCEPTION
3183 WHEN OTHERS
3184 THEN
3185 clear_cache;
3186
3187 IF SQLCODE <> hr_utility.hr_error_number
3188 THEN
3189 debug_others(l_proc_name, l_proc_step);
3190
3191 IF g_debug
3192 THEN
3193 DEBUG('Leaving: ' || l_proc_name, -999);
3194 END IF;
3195
3196 fnd_message.raise_error;
3197 ELSE
3198 RAISE;
3199 END IF;
3200 END get_gap_transition_code;
3201
3202 -- This function is used to get service history data
3203 -- for an assignment as of a cutover date
3204 -- ----------------------------------------------------------------------------
3205 -- |---------------------< get_asg_ser_cutover_data >-------------------------|
3206 -- ----------------------------------------------------------------------------
3207 PROCEDURE get_asg_ser_cutover_data(p_assignment_id IN NUMBER)
3208 IS
3209 --
3210 CURSOR csr_get_asg_status(
3211 c_effective_start_date DATE
3212 ,c_effective_end_date DATE
3213 )
3214 IS
3215 SELECT asg1.assignment_id curr_assignment_id
3216 ,asg1.assignment_status_type_id curr_status_type_id
3217 ,asg1.effective_start_date curr_effective_start_date
3218 ,asg1.effective_end_date curr_effective_end_date
3219 ,asg2.assignment_status_type_id prev_status_type_id
3220 ,asg2.effective_start_date prev_effective_start_date
3221 ,asg2.effective_end_date prev_effective_end_date
3222 FROM per_all_assignments_f asg1, per_all_assignments_f asg2
3223 WHERE asg1.assignment_id = p_assignment_id
3224 AND (
3225 (
3226 asg1.effective_start_date BETWEEN c_effective_start_date
3227 AND c_effective_end_date
3228 )
3229 OR (
3230 asg1.effective_end_date BETWEEN c_effective_start_date
3231 AND c_effective_end_date
3232 )
3233 )
3234 AND asg2.assignment_id = asg1.assignment_id
3235 AND asg2.effective_end_date = asg1.effective_start_date - 1
3236 AND asg2.assignment_status_type_id <>
3237 asg1.assignment_status_type_id
3238 ORDER BY asg1.effective_start_date DESC;
3239
3240 -- Cursor to fetch min assignment effective start date
3241 -- for this employment category
3242 CURSOR csr_get_asg_start_date(c_employment_category VARCHAR2)
3243 IS
3244 SELECT MIN(effective_start_date)
3245 FROM per_all_assignments_f
3246 WHERE assignment_id = p_assignment_id
3247 AND employment_category = c_employment_category;
3248
3249 -- Cursor to fetch absence details for this person
3250 CURSOR csr_get_abs_details(
3251 c_person_id NUMBER
3252 ,c_effective_start_date DATE
3253 ,c_effective_end_date DATE
3254 )
3255 IS
3256 SELECT absence_attendance_type_id, absence_attendance_id
3257 ,date_start, date_end
3258 FROM per_absence_attendances
3259 WHERE person_id = c_person_id
3260 AND (
3261 (
3262 date_start BETWEEN c_effective_start_date
3263 AND c_effective_end_date
3264 )
3265 OR (
3266 ( NVL(date_end, c_effective_start_date)
3267 BETWEEN c_effective_start_date
3268 AND c_effective_end_date
3269 )
3270 AND
3271 (date_start <= c_effective_end_date)
3272 )
3273 )
3274 ORDER BY date_start DESC;
3275
3276 -- Cursor to get pension scheme element details
3277 -- for this person
3278 CURSOR csr_get_ele_ent_details(
3279 c_element_type_id NUMBER
3280 ,c_effective_start_date DATE
3281 ,c_effective_end_date DATE
3282 )
3283 IS
3284 SELECT pee.element_entry_id, pee.effective_start_date
3285 ,pee.effective_end_date, pel.element_type_id
3286 FROM pay_element_entries_f pee, pay_element_links_f pel
3287 WHERE pee.assignment_id = p_assignment_id
3288 AND pee.entry_type = 'E'
3289 AND pee.element_link_id = pel.element_link_id
3290 AND (
3291 (
3292 pee.effective_start_date BETWEEN c_effective_start_date
3293 AND c_effective_end_date
3294 )
3295 OR (
3296 pee.effective_end_date BETWEEN c_effective_start_date
3297 AND c_effective_end_date
3298 )
3299 )
3300 AND pel.element_type_id = c_element_type_id
3301 AND g_effective_date BETWEEN pel.effective_start_date
3302 AND pel.effective_end_date
3303 ORDER BY pee.effective_start_date DESC;
3304
3305 -- Cursor to fetch opt out date information
3306 -- for a given element entry id
3307 CURSOR csr_chk_opt_out_info(
3308 c_element_type_id NUMBER
3309 ,c_input_value_id NUMBER
3310 ,c_effective_start_date DATE
3311 ,c_effective_end_date DATE
3312 )
3313 IS
3314 SELECT pee.element_entry_id, pee.effective_start_date
3315 ,pee.effective_end_date
3316 FROM pay_element_entries_f pee, pay_element_links_f pel
3317 WHERE pee.assignment_id = p_assignment_id
3318 AND pee.entry_type = 'E'
3319 AND pee.element_link_id = pel.element_link_id
3320 AND (
3321 (
3322 pee.effective_start_date BETWEEN c_effective_start_date
3323 AND c_effective_end_date
3324 )
3325 OR (
3326 pee.effective_end_date BETWEEN c_effective_start_date
3327 AND c_effective_end_date
3328 )
3329 )
3330 AND pel.element_type_id = c_element_type_id
3331 AND g_effective_date BETWEEN pel.effective_start_date
3332 AND pel.effective_end_date
3333 AND EXISTS(
3334 SELECT 1
3335 FROM pay_element_entry_values_f pev
3336 WHERE pev.element_entry_id = pee.element_entry_id
3337 AND pev.effective_start_date =
3338 pee.effective_start_date
3339 AND pev.effective_end_date = pee.effective_end_date
3340 AND pev.input_value_id = c_input_value_id
3341 AND pev.screen_entry_value IS NOT NULL)
3342 ORDER BY pee.effective_start_date DESC;
3343
3344 -- Cursor to check assignment details
3345 CURSOR csr_get_asg_details(c_effective_date DATE)
3346 IS
3347 SELECT effective_end_date
3348 FROM per_all_assignments_f
3349 WHERE assignment_id = p_assignment_id
3350 AND c_effective_date BETWEEN effective_start_date
3351 AND effective_end_date
3352 ORDER BY effective_start_date;
3353
3354 -- Cursor to fetch leaving reason for non period of service
3355 -- events
3356 CURSOR csr_get_leaving_reason(c_person_id NUMBER
3357 ,c_effective_date DATE)
3358 IS
3359 SELECT pps.leaving_reason, pps.actual_termination_date
3360 FROM per_periods_of_service pps
3361 WHERE pps.person_id = c_person_id
3362 AND pps.date_start = (SELECT MAX(date_start)
3363 FROM per_periods_of_service pps1
3364 WHERE pps1.person_id = c_person_id
3365 AND pps1.date_start <= c_effective_date);
3366
3367 --For bug 7705147: Cursor to get Actual Termination Date
3368 CURSOR csr_get_atd
3369 IS
3370 SELECT pos.actual_termination_date
3371 FROM per_all_assignments_f asg,
3372 per_periods_of_service pos
3373 WHERE asg.assignment_id = p_assignment_id
3374 AND g_effective_date between asg.effective_start_date AND asg.effective_end_date
3375 AND asg.period_of_service_id = pos.period_of_service_id;
3376
3377 l_act_term_date DATE;
3378 --
3379
3380 l_proc_name VARCHAR2(80)
3381 := g_proc_name || 'get_asg_ser_cutover_data';
3382 l_proc_step PLS_INTEGER;
3383 l_rec_asg_status csr_get_asg_status%ROWTYPE;
3384 l_rec_asg_details r_asg_details;
3385 l_rec_abs_details csr_get_abs_details%ROWTYPE;
3386 l_rec_ele_ent_details r_ele_ent_details;
3387 l_rec_opt_out_info csr_chk_opt_out_info%ROWTYPE;
3388 l_rec_gap_details csr_chk_pay_trans%ROWTYPE;
3389 l_ser_start_date DATE;
3390 l_start_reason VARCHAR2(10);
3391 l_event_source VARCHAR2(20);
3392 l_asg_start_date DATE;
3393 l_psi_code VARCHAR2(10);
3394 l_element_type_id NUMBER;
3395 l_input_value_id NUMBER;
3396 l_char VARCHAR2(100);
3397 l_psi_emp_type pqp_configuration_values.pcv_information1%TYPE;
3398 l_opt_out_date DATE;
3399 l_asg_status_type_id NUMBER;
3400 l_prev_asg_status_type_id NUMBER;
3401 l_absence_type_id NUMBER;
3402 l_pension_category pqp_configuration_values.pcv_information1%TYPE;
3403 i NUMBER;
3404 j NUMBER;
3405 l_absence_event_code VARCHAR2(10);
3406 l_effective_date DATE;
3407 l_next_effective_date DATE;
3408 l_value NUMBER;
3409 l_leaver_date DATE;
3410 l_rec_leaving_reason csr_get_leaving_reason%ROWTYPE;
3411 l_return VARCHAR2(10);
3412 l_index NUMBER;
3413 l_partnership_scheme VARCHAR2(30);
3414 --
3415 BEGIN
3416 --
3417 IF g_debug
3418 THEN
3419 --
3420 l_proc_step := 10;
3421 debug_enter(l_proc_name);
3422 DEBUG('p_assignment_id: ' || p_assignment_id);
3423 END IF;
3424
3425 -- Get assignment details as of the cutover date
3426 -- To begin with, treat all employees as New Joiners
3427 -- with the start date as their latest start date
3428 -- We should get this information from basic criteria function
3429 IF g_assignment_dtl.assignment_id IS NULL
3430 THEN
3431 get_asg_details(
3432 p_assignment_id => p_assignment_id
3433 ,p_effective_date => g_effective_date
3434 ,p_rec_asg_details => l_rec_asg_details
3435 );
3436 ELSE
3437 l_rec_asg_details.person_id :=
3438 g_assignment_dtl.person_id;
3439 l_rec_asg_details.effective_start_date :=
3440 g_assignment_dtl.effective_start_date;
3441 l_rec_asg_details.effective_end_date :=
3442 g_assignment_dtl.effective_end_date;
3443 l_rec_asg_details.assignment_number :=
3444 g_assignment_dtl.assignment_number;
3445 l_rec_asg_details.primary_flag :=
3446 g_assignment_dtl.primary_flag;
3447 l_rec_asg_details.normal_hours :=
3448 g_assignment_dtl.normal_hours;
3449 l_rec_asg_details.assignment_status_type_id :=
3450 g_assignment_dtl.assignment_status_type_id;
3451 l_rec_asg_details.employment_category :=
3452 g_assignment_dtl.employment_category;
3453 END IF; -- assignment dtl global record is null check ...
3454
3455 IF g_debug
3456 THEN
3457 l_proc_step := 20;
3458 DEBUG(l_proc_name, l_proc_step);
3459 DEBUG('Person ID: ' || l_rec_asg_details.person_id);
3460 DEBUG(
3461 'Effective Start Date: '
3462 || TO_CHAR(l_rec_asg_details.effective_start_date, 'DD/MON/YYYY')
3463 );
3464 DEBUG(
3465 'Effective End Date: '
3466 || TO_CHAR(l_rec_asg_details.effective_end_date, 'DD/MON/YYYY')
3467 );
3468 DEBUG('Assignment Number: ' || l_rec_asg_details.assignment_number);
3469 DEBUG('Primary Flag: ' || l_rec_asg_details.primary_flag);
3470 DEBUG('Normal Hours: ' || l_rec_asg_details.normal_hours);
3471 DEBUG(
3472 'Assignment Status Type ID: '
3473 || l_rec_asg_details.assignment_status_type_id
3474 );
3475 DEBUG('Assignment Category: '
3476 || l_rec_asg_details.employment_category);
3477 END IF;
3478
3479 -- Assign latest start date as the service date to start with
3480 l_ser_start_date :=
3481 get_per_latest_start_date(
3482 p_person_id => l_rec_asg_details.person_id
3483 ,p_effective_date => l_rec_asg_details.effective_start_date
3484 );
3485
3486 IF g_debug
3487 THEN
3488 DEBUG('l_ser_start_date: '
3489 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
3490 END IF;
3491
3492 l_start_reason := 'N';
3493 l_event_source := 'SER';
3494 -- Get the earliest assignment effective start date when this
3495 -- person became eligible to be reported
3496 OPEN csr_get_asg_start_date(l_rec_asg_details.employment_category);
3497 FETCH csr_get_asg_start_date INTO l_asg_start_date;
3498 CLOSE csr_get_asg_start_date;
3499
3500 IF l_ser_start_date < l_asg_start_date
3501 THEN
3502 l_ser_start_date := l_asg_start_date;
3503 END IF;
3504
3505 --For bug 7705147
3506 --Commented out the old logic for checking leaver
3507 /*
3508 IF pqp_gb_psi_functions.chk_is_employee_a_leaver(
3509 p_assignment_id => p_assignment_id
3510 ,p_effective_date => g_effective_date
3511 ,p_leaver_date => l_leaver_date
3512 ) = 'Y'
3513 THEN
3514 IF l_leaver_date <= g_effective_date
3515 THEN
3516 l_ser_start_date := l_leaver_date;
3517 l_start_reason := 'ZZ'; -- Leaver
3518 l_event_source := 'ASG';
3519 END IF; -- End if of leaver date <= g_effective_date
3520 END IF; -- employee a leaver check ...
3521 */
3522 --Added new logic for leaver
3523 OPEN csr_get_atd;
3524 FETCH csr_get_atd INTO l_act_term_date;
3525
3526 IF csr_get_atd%FOUND
3527 THEN
3528
3529 DEBUG('l_act_term_date: ' || l_act_term_date);
3530 DEBUG('g_effective_date: ' || g_effective_date);
3531
3532 IF l_act_term_date IS NOT NULL
3533 AND l_act_term_date <= g_effective_date
3534 THEN
3535 l_ser_start_date := l_act_term_date;
3536 l_start_reason := 'ZZ'; -- Leaver
3537 l_event_source := 'ASG';
3538 END IF;
3539 END IF;
3540 CLOSE csr_get_atd;
3541 --For bug 7705147: Till here
3542
3543 IF g_debug
3544 THEN
3545 l_proc_step := 30;
3546 DEBUG(l_proc_name, l_proc_step);
3547 DEBUG('l_start_reason: ' || l_start_reason);
3548 DEBUG('l_event_source: ' || l_event_source);
3549 DEBUG('l_ser_start_date: '
3550 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
3551 DEBUG('l_asg_start_date: '
3552 || TO_CHAR(l_asg_start_date, 'DD/MON/YYYY'));
3553 DEBUG('Pension Event Processing: ');
3554 END IF;
3555
3556 -- Check the employee's pension scheme membership as of the cutover date
3557 -- Loop through the scheme map ele collection
3558 -- to identify the pension scheme
3559 -- Proceed only if opt in and opt out events mapping code is available
3560
3561
3562 l_psi_emp_type :=
3563 get_psi_emp_type(p_employment_category => l_rec_asg_details.employment_category);
3564
3565 IF g_debug
3566 THEN
3567 l_proc_step := 40;
3568 DEBUG(l_proc_name, l_proc_step);
3569 DEBUG('g_opt_in: ' || g_opt_in);
3570 DEBUG('g_opt_out: ' || g_opt_out);
3571 DEBUG('l_psi_emp_type: ' || l_psi_emp_type);
3572 END IF;
3573
3574 i := g_tab_pen_sch_map_cv.FIRST;
3575
3576 WHILE i IS NOT NULL AND(g_opt_in = 'Y' OR g_opt_out = 'Y')
3577 LOOP
3578 l_element_type_id :=
3579 fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1);
3580 l_input_value_id :=
3581 g_tab_pen_ele_ids(l_element_type_id).input_value_id;
3582
3583 IF g_debug
3584 THEN
3585 DEBUG('l_element_type_id: ' || l_element_type_id);
3586 DEBUG('l_input_value_id: ' || l_input_value_id);
3587 END IF;
3588
3589 OPEN csr_get_ele_ent_details(l_element_type_id, l_ser_start_date
3590 ,g_effective_date );
3591 FETCH csr_get_ele_ent_details INTO l_rec_ele_ent_details;
3592
3593 IF csr_get_ele_ent_details%FOUND
3594 THEN
3595 -- Check whether this person has opted in
3596 -- We do this check based on CS rules
3597 -- All regular/fixed term employees become member of CS from
3598 -- day one.so the effective start date should match when the
3599 -- assignment started
3600 -- apart from casuals who will be enrolled 3 months later
3601 -- Get penserver category as at the element entry effective
3602 -- start date
3603 -- Casuals can become regular
3604 -- so check the status at element entry effective date
3605 IF g_debug
3606 THEN
3607 l_proc_step := 50;
3608 DEBUG(l_proc_name, l_proc_step);
3609 END IF;
3610
3611 -- get_asg_details(p_assignment_id => p_assignment_id
3612 -- ,p_effective_date => l_rec_ele_ent_details.effective_start_date
3613 -- ,p_rec_asg_details => l_rec_asg_details
3614 -- );
3615 IF g_debug
3616 THEN
3617 l_proc_step := 60;
3618 DEBUG(l_proc_name, l_proc_step);
3619 END IF;
3620
3621 IF l_psi_emp_type <> 'CASUAL'
3622 THEN
3623 -- Regular/fixed term employees can opt into partnership schemes as well
3624 -- IF g_tab_pen_sch_map_cv(i).pcv_information1 <> 'PARTNER'
3625 -- THEN
3626 IF l_rec_ele_ent_details.effective_end_date <>
3627 hr_api.g_eot
3628 AND -- Bug 4873436: chk opt out only as of or b4 cutover date
3629 l_rec_ele_ent_details.effective_end_date <=
3630 g_effective_date
3631 AND g_opt_out = 'Y'
3632 THEN
3633 IF g_debug
3634 THEN
3635 l_proc_step := 70;
3636 DEBUG(l_proc_name, l_proc_step);
3637 END IF;
3638
3639 -- Retrieve opt out date
3640 -- Get the opt out date information
3641 l_char :=
3642 get_screen_entry_value(
3643 p_element_entry_id => l_rec_ele_ent_details.element_entry_id
3644 ,p_effective_start_date => l_rec_ele_ent_details.effective_start_date
3645 ,p_effective_end_date => l_rec_ele_ent_details.effective_end_date
3646 ,p_input_value_id => l_input_value_id
3647 );
3648 l_opt_out_date := fnd_date.canonical_to_date(l_char);
3649
3650 IF g_debug
3651 THEN
3652 l_proc_step := 80;
3653 DEBUG(l_proc_name, l_proc_step);
3654 DEBUG(
3655 'l_opt_out_date: '
3656 || TO_CHAR(l_opt_out_date, 'DD/MON/YYYY')
3657 );
3658 END IF;
3659
3660 l_ser_start_date :=
3661 LEAST(
3662 NVL(
3663 l_opt_out_date
3664 ,l_rec_ele_ent_details.effective_end_date
3665 )
3666 ,l_rec_ele_ent_details.effective_end_date
3667 );
3668 l_event_source := 'PENSION';
3669 l_start_reason := 'OO';
3670 l_pension_category :=
3671 g_tab_pen_sch_map_cv(i).pcv_information2;
3672 l_partnership_scheme :=g_tab_pen_sch_map_cv(i).pcv_information3;
3673
3674 ELSIF l_rec_ele_ent_details.effective_start_date <>
3675 l_asg_start_date
3676 THEN
3677 IF g_debug
3678 THEN
3679 l_proc_step := 90;
3680 DEBUG(l_proc_name, l_proc_step);
3681 END IF;
3682
3683 -- Person has opted in
3684 IF l_ser_start_date <
3685 l_rec_ele_ent_details.effective_start_date
3686 AND g_opt_in = 'Y'
3687 THEN
3688 -- Double check to ensure there was an opt out event
3689 -- before opting in
3690 -- Check whether this person has opted out anytime
3691 -- Check this only for classic and premium
3692 -- Can't check this as the person would have opted out on a
3693 -- different scheme
3694 -- IF g_tab_pen_sch_map_cv(i).pcv_information1 <> 'PARTNER'
3695 -- THEN
3696 -- OPEN csr_chk_opt_out_info(
3697 -- l_element_type_id
3698 -- ,l_input_value_id
3699 -- ,l_asg_start_date
3700 -- ,g_effective_date
3701 -- );
3702 -- FETCH csr_chk_opt_out_info INTO l_rec_opt_out_info;
3703 --
3704 -- IF csr_chk_opt_out_info%FOUND
3705 -- THEN
3706 IF g_debug
3707 THEN
3708 l_proc_step := 100;
3709 DEBUG(l_proc_name, l_proc_step);
3710 END IF;
3711
3712 -- Store this information
3713 -- l_ser_start_date :=
3714 -- l_rec_ele_ent_details.effective_start_date;
3715 -- l_event_source := 'PENSION';
3716 -- l_start_reason := 'OI';
3717 -- l_pension_category :=
3718 -- g_tab_pen_sch_map_cv(i).pcv_information1;
3719 -- END IF; -- End if of opt out cursor check ...
3720 --
3721 -- CLOSE csr_chk_opt_out_info;
3722 -- ELSE -- Partnership
3723 l_ser_start_date :=
3724 l_rec_ele_ent_details.effective_start_date;
3725 l_event_source := 'PENSION';
3726 l_start_reason := 'OI';
3727 l_pension_category :=
3728 g_tab_pen_sch_map_cv(i).pcv_information2;
3729 l_partnership_scheme :=g_tab_pen_sch_map_cv(i).pcv_information3;
3730 -- END IF; -- End if of partner check ...
3731 END IF; -- End if of service start date < element entry start date ...
3732 END IF;
3733 -- check element entry end date is set ...
3734 -- END IF; -- Employee member of <> partner scheme check ...
3735 ELSIF l_psi_emp_type = 'CASUAL'
3736 THEN
3737 IF g_debug
3738 THEN
3739 l_proc_step := 110;
3740 DEBUG(l_proc_name, l_proc_step);
3741 END IF;
3742
3743 -- Check whether person is a member of partnership scheme
3744 IF g_tab_pen_sch_map_cv(i).pcv_information2 = 'PARTNER'
3745 THEN
3746 IF l_rec_ele_ent_details.effective_end_date <>
3747 hr_api.g_eot
3748 AND -- Bug 4873436: chk opt out only as of or b4 cutover date
3749 l_rec_ele_ent_details.effective_end_date <=
3750 g_effective_date
3751 AND g_opt_out = 'Y'
3752 THEN
3753 -- Get the opt out date information
3754 l_char :=
3755 get_screen_entry_value(
3756 p_element_entry_id => l_rec_ele_ent_details.element_entry_id
3757 ,p_effective_start_date => l_rec_ele_ent_details.effective_start_date
3758 ,p_effective_end_date => l_rec_ele_ent_details.effective_end_date
3759 ,p_input_value_id => l_input_value_id
3760 );
3761 l_opt_out_date :=
3762 fnd_date.canonical_to_date(l_char);
3763
3764 IF g_debug
3765 THEN
3766 l_proc_step := 120;
3767 DEBUG(l_proc_name, l_proc_step);
3768 END IF;
3769
3770 l_ser_start_date :=
3771 LEAST(
3772 NVL(
3773 l_opt_out_date
3774 ,l_rec_ele_ent_details.effective_end_date
3775 )
3776 ,l_rec_ele_ent_details.effective_end_date
3777 );
3778 l_event_source := 'PENSION';
3779 l_start_reason := 'OO';
3780 l_pension_category :=
3781 g_tab_pen_sch_map_cv(i).pcv_information2;
3782 l_partnership_scheme :=
3783 g_tab_pen_sch_map_cv(i).pcv_information3;
3784 -- Remove the three month rule check for casuals
3785 ELSIF l_rec_ele_ent_details.effective_start_date <> l_asg_start_date
3786 -- ADD_MONTHS(l_asg_start_date, 3)
3787 THEN
3788 -- Person has opted in
3789 IF l_ser_start_date <
3790 l_rec_ele_ent_details.effective_start_date
3791 AND g_opt_in = 'Y'
3792 THEN
3793 -- Double check to ensure there was an opt out event
3794 -- before opting in
3795 -- Check whether this person has opted out anytime
3796 -- Can't do this check as the person could have
3797 -- enrolled into a different scheme
3798 IF g_debug
3799 THEN
3800 l_proc_step := 130;
3801 DEBUG(l_proc_name, l_proc_step);
3802 END IF;
3803
3804 -- OPEN csr_chk_opt_out_info(
3805 -- l_element_type_id
3806 -- ,l_input_value_id
3807 -- ,l_asg_start_date
3808 -- ,g_effective_date
3809 -- );
3810 -- FETCH csr_chk_opt_out_info INTO l_rec_opt_out_info;
3811 --
3812 -- IF csr_chk_opt_out_info%FOUND
3813 -- THEN
3814 -- Store this information
3815 l_ser_start_date :=
3816 l_rec_ele_ent_details.effective_start_date;
3817 l_event_source := 'PENSION';
3818 l_start_reason := 'OI';
3819 l_pension_category :=
3820 g_tab_pen_sch_map_cv(i).pcv_information2;
3821 l_partnership_scheme :=g_tab_pen_sch_map_cv(i).pcv_information3;
3822 -- END IF; -- End if of opt out info found check ...
3823 --
3824 -- CLOSE csr_chk_opt_out_info;
3825 END IF; -- Service date lesser than element entry start date ...
3826 END IF; -- check element entry end date is set ...
3827
3828 -- CLOSE csr_get_ele_ent_details;
3829 -- EXIT; -- From collection loop
3830 END IF; -- End if of partner check ...
3831 END IF; -- End if of employment type <> casual check ...
3832 END IF; -- cursor found check ...
3833
3834 CLOSE csr_get_ele_ent_details;
3835 i := g_tab_pen_sch_map_cv.NEXT(i);
3836 END LOOP;
3837
3838 IF g_debug
3839 THEN
3840 l_proc_step := 140;
3841 DEBUG('l_start_reason: ' || l_start_reason);
3842 DEBUG('l_event_source: ' || l_event_source);
3843 DEBUG('l_ser_start_date: '
3844 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
3845 DEBUG('l_pension_category: ' || l_pension_category);
3846 DEBUG('l_partnership_scheme: '||l_partnership_scheme);
3847 DEBUG(l_proc_name, l_proc_step);
3848 DEBUG('Absence Event Processing: ');
3849 END IF;
3850
3851 -- Check the employee's absence records as of the cutover date
3852 --
3853 -- Only procced if there is atleast one absence event source
3854 IF g_tab_abs_types.COUNT > 0
3855 THEN
3856 OPEN csr_get_abs_details(
3857 l_rec_asg_details.person_id
3858 ,l_ser_start_date
3859 ,g_effective_date
3860 );
3861
3862 LOOP
3863 FETCH csr_get_abs_details INTO l_rec_abs_details;
3864 EXIT WHEN csr_get_abs_details%NOTFOUND;
3865
3866 -- Loop through global absence type collection
3867 -- to check whether the fetched absence type matches
3868 IF g_debug
3869 THEN
3870 l_proc_step := 150;
3871 DEBUG(l_proc_name, l_proc_step);
3872 DEBUG(
3873 'Absence attendance id: '
3874 || l_rec_abs_details.absence_attendance_id
3875 );
3876 DEBUG(
3877 'Absence Type Id: '
3878 || l_rec_abs_details.absence_attendance_type_id
3879 );
3880 DEBUG(
3881 'Date Start: '
3882 || TO_CHAR(l_rec_abs_details.date_start, 'DD/MON/YYYY')
3883 );
3884 DEBUG(
3885 'Date End: '
3886 || TO_CHAR(l_rec_abs_details.date_end, 'DD/MON/YYYY')
3887 );
3888 END IF;
3889
3890 i := g_tab_abs_types.FIRST;
3891
3892 WHILE i IS NOT NULL
3893 LOOP
3894 IF g_tab_abs_types(i) =
3895 l_rec_abs_details.absence_attendance_type_id
3896 THEN
3897 IF l_rec_abs_details.date_end IS NOT NULL
3898 AND l_rec_abs_details.date_end <= g_effective_date
3899 THEN
3900 IF g_debug
3901 THEN
3902 l_proc_step := 155;
3903 DEBUG(l_proc_name, l_proc_step);
3904 DEBUG('g_tab_abs_types(i): ' || g_tab_abs_types(i));
3905 END IF;
3906
3907 -- Person has returned from absence
3908 -- Use RB code instead
3909 --RB for Sickness/Maternity to be returned only
3910 --if there is a PAY transition
3911 l_psi_code :=
3912 SUBSTR(g_tab_event_map_cv(i).pcv_information1, 1, 1);
3913 IF l_psi_code in ('S','M') then
3914
3915 IF g_debug
3916 THEN
3917 l_proc_step := 157;
3918 DEBUG(l_proc_name, l_proc_step);
3919 DEBUG('l_psi_code: ' || l_psi_code);
3920 END IF;
3921
3922 get_gap_transition_code(
3923 p_assignment_id => p_assignment_id
3924 ,p_absence_attendance_id =>
3925 l_rec_abs_details.absence_attendance_id
3926 ,p_effective_date =>l_rec_abs_details.date_end
3927 ,p_psi_event_code => l_psi_code
3928 ,p_absence_event_code => l_absence_event_code
3929 ,p_rec_gap_details => l_rec_gap_details
3930 );
3931 IF l_absence_event_code is not NULL
3932 THEN
3933 IF g_debug
3934 THEN
3935 l_proc_step := 160;
3936 DEBUG(l_proc_name, l_proc_step);
3937 DEBUG(
3938 'l_absence_event_code: ' || l_absence_event_code
3939 );
3940 END IF;--g_debug
3941 l_ser_start_date := l_rec_abs_details.date_end + 1;
3942 l_event_source := 'ABSBREAK';
3943 l_start_reason := 'RB';
3944 END IF;--absence_event_code is not NULL
3945 ELSE --l_psi_code in ('S','M')
3946 IF g_debug
3947 THEN
3948 l_proc_step := 163;
3949 DEBUG(l_proc_name, l_proc_step);
3950 DEBUG('l_psi_code: ' || l_psi_code);
3951 END IF;
3952 l_ser_start_date := l_rec_abs_details.date_end + 1;
3953 l_event_source := 'ABSBREAK';
3954 l_start_reason := 'RB';
3955 END IF; --l_psi_code in ('S','M')
3956 ELSE
3957 -- Add additional logic to check whether the absence type
3958 -- relates to sickness / maternity absence in which case we will have to
3959 -- add this event only if there is a sickness / maternity transition from full pay to
3960 -- half pay or to no pay or to pension rate
3961 l_psi_code :=
3962 SUBSTR(g_tab_event_map_cv(i).pcv_information1, 1, 1);
3963
3964 IF l_psi_code IN('S', 'M') -- Sickness and Maternity
3965 THEN
3966 IF g_debug
3967 THEN
3968 l_proc_step := 165;
3969 DEBUG(l_proc_name, l_proc_step);
3970 DEBUG('l_psi_code: ' || l_psi_code);
3971 END IF;
3972
3973 get_gap_transition_code(
3974 p_assignment_id => p_assignment_id
3975 ,p_absence_attendance_id => l_rec_abs_details.absence_attendance_id
3976 ,p_effective_date => g_effective_date
3977 ,p_psi_event_code => l_psi_code
3978 ,p_absence_event_code => l_absence_event_code
3979 ,p_rec_gap_details => l_rec_gap_details
3980 );
3981
3982 IF g_debug
3983 THEN
3984 l_proc_step := 170;
3985 DEBUG(l_proc_name, l_proc_step);
3986 DEBUG(
3987 'l_absence_event_code: ' || l_absence_event_code
3988 );
3989 END IF;
3990
3991 IF l_absence_event_code IS NOT NULL
3992 THEN
3993 l_ser_start_date :=
3994 l_rec_gap_details.date_start;
3995 l_event_source := 'ABS';
3996 l_start_reason := l_absence_event_code;
3997 l_absence_type_id :=
3998 l_rec_abs_details.absence_attendance_type_id;
3999 END IF; -- End if of absence event code not null check ...
4000 ELSE -- Not sickness or maternity
4001 l_ser_start_date := l_rec_abs_details.date_start;
4002 l_event_source := 'ABS';
4003 l_start_reason :=
4004 g_tab_event_map_cv(i).pcv_information11;
4005 l_absence_type_id :=
4006 l_rec_abs_details.absence_attendance_type_id;
4007 END IF; -- End if of sickess type absence check ...
4008 END IF; -- End if of date end is not null check ...
4009
4010 EXIT; -- exit from collection loop
4011 END IF; -- absence type in collection check ...
4012
4013 i := g_tab_abs_types.NEXT(i);
4014 END LOOP; -- collection loop
4015
4016 IF l_event_source IN('ABS', 'ABSBREAK')
4017 THEN
4018 EXIT; -- Exit from absence cursor loop
4019 END IF;
4020 END LOOP; -- absence cursor loop
4021
4022 CLOSE csr_get_abs_details;
4023 END IF; -- End if of atleast one absence event exists check ...
4024
4025 IF g_debug
4026 THEN
4027 l_proc_step := 180;
4028 DEBUG('l_start_reason: ' || l_start_reason);
4029 DEBUG('l_event_source: ' || l_event_source);
4030 DEBUG('l_ser_start_date: '
4031 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
4032 DEBUG('l_absence_type_id: ' || l_absence_type_id);
4033 DEBUG(l_proc_name, l_proc_step);
4034 DEBUG('Assignment Status Event Processing: ');
4035 END IF;
4036
4037 -- Check the employee's assignment status (if active ignore) as
4038 -- of cutover date
4039 -- Only proceed if there is atleast one assignment status event code
4040 IF g_tab_asg_status.COUNT > 0
4041 THEN
4042 OPEN csr_get_asg_status(l_ser_start_date, g_effective_date);
4043
4044 LOOP
4045 FETCH csr_get_asg_status INTO l_rec_asg_status;
4046 EXIT WHEN csr_get_asg_status%NOTFOUND;
4047
4048 IF g_debug
4049 THEN
4050 l_proc_step := 190;
4051 DEBUG(
4052 'l_rec_asg_status.curr_effective_start_date: '
4053 || TO_CHAR(
4054 l_rec_asg_status.curr_effective_start_date
4055 ,'DD/MON/YYYY'
4056 )
4057 );
4058 DEBUG(
4059 'Assignment Status: '
4060 || l_rec_asg_status.curr_status_type_id
4061 );
4062 DEBUG(l_proc_name, l_proc_step);
4063 END IF;
4064
4065 -- Loop through the assignment status collection
4066 -- and check whether the current status matches
4067 i := g_tab_asg_status.FIRST;
4068
4069 WHILE i IS NOT NULL
4070 LOOP
4071 l_asg_status_type_id :=
4072 fnd_number.canonical_to_number(g_tab_asg_status(i));
4073
4074 IF l_rec_asg_status.curr_status_type_id = l_asg_status_type_id
4075 THEN
4076 IF g_debug
4077 THEN
4078 l_proc_step := 200;
4079 DEBUG('l_asg_status_type_id: ' || l_asg_status_type_id);
4080 DEBUG(
4081 'l_rec_asg_status.curr_status_type_id: '
4082 || l_rec_asg_status.curr_status_type_id
4083 );
4084 DEBUG(
4085 'l_rec_asg_status.prev_status_type_id: '
4086 || l_rec_asg_status.prev_status_type_id
4087 );
4088 DEBUG(l_proc_name, l_proc_step);
4089 END IF;
4090
4091 -- Check whether this is an active status
4092 -- it could be that the person has returned from break
4093
4094 IF l_asg_status_type_id = g_active_asg_sts_id
4095 THEN
4096 -- Check whether the previous assignment status
4097 -- is in the collection to signify a suspension
4098 -- or non reckonable event
4099 j := g_tab_asg_status.FIRST;
4100
4101 WHILE j IS NOT NULL
4102 LOOP
4103 l_prev_asg_status_type_id :=
4104 fnd_number.canonical_to_number(g_tab_asg_status(j));
4105
4106 IF l_rec_asg_status.prev_status_type_id =
4107 l_prev_asg_status_type_id
4108 THEN
4109 IF g_debug
4110 THEN
4111 l_proc_step := 210;
4112 DEBUG(l_proc_name, l_proc_step);
4113 DEBUG(
4114 'g_active_asg_sts_id: '
4115 || g_active_asg_sts_id
4116 );
4117 END IF;
4118
4119 -- Can't be an active status
4120 -- so mark as return from break
4121 l_ser_start_date :=
4122 l_rec_asg_status.curr_effective_start_date;
4123 l_event_source := 'ASGBREAK';
4124 l_start_reason := 'RB'; -- Return from break
4125 EXIT; -- inner loop
4126 END IF; -- End if of prev asg status type check ...
4127
4128 j :=
4129 g_tab_asg_status.NEXT(j);
4130 END LOOP;
4131 ELSE -- not an active status
4132 IF g_debug
4133 THEN
4134 l_proc_step := 220;
4135 DEBUG(l_proc_name, l_proc_step);
4136 END IF;
4137
4138 l_ser_start_date :=
4139 l_rec_asg_status.curr_effective_start_date;
4140 l_event_source := 'ASG';
4141 l_start_reason :=
4142 g_tab_event_map_cv(i).pcv_information11;
4143 END IF; -- End if of asg status type = active ...
4144
4145 EXIT; -- Exit collection loop
4146 END IF; -- End if of status type in collection check ...
4147
4148 i := g_tab_asg_status.NEXT(i);
4149 END LOOP; -- collection loop;
4150
4151 IF l_event_source IN('ASG', 'ASGBREAK')
4152 THEN
4153 -- Exit from cursor as well
4154 EXIT;
4155 END IF;
4156 END LOOP; -- cursor loop
4157
4158 CLOSE csr_get_asg_status;
4159 END IF; -- End if of at least one asg status event exists in colleciton ...
4160
4161 IF g_debug
4162 THEN
4163 l_proc_step := 230;
4164 DEBUG('l_start_reason: ' || l_start_reason);
4165 DEBUG('l_event_source: ' || l_event_source);
4166 DEBUG('l_ser_start_date: '
4167 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
4168 DEBUG('l_asg_status_type_id: ' || l_asg_status_type_id);
4169 DEBUG(l_proc_name, l_proc_step);
4170 END IF;
4171
4172 IF g_debug
4173 THEN
4174 l_proc_step := 240;
4175 DEBUG(l_proc_name, l_proc_step);
4176 DEBUG('l_pension_category: ' || l_pension_category);
4177 END IF;
4178
4179 -- Fetch the codes for service history DE from event map cv
4180 -- Penserv Category
4181 IF l_event_source <> 'PENSION' OR l_pension_category IS NULL
4182 THEN
4183 l_pension_category :=
4184 get_pen_scheme_memb(
4185 p_assignment_id => p_assignment_id
4186 ,p_effective_date => l_ser_start_date
4187 ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv
4188 ,p_rec_ele_ent_details => l_rec_ele_ent_details
4189 ,p_partnership_scheme => l_partnership_scheme
4190 );
4191 ELSIF l_event_source = 'PENSION' AND l_start_reason = 'OO'
4192 THEN
4193 IF g_debug
4194 THEN
4195 l_proc_step := 245;
4196 DEBUG(l_proc_name, l_proc_step);
4197 END IF;
4198
4199 -- Ensure that this is not due to a termination event
4200 -- Bug Fix 4873436
4201 OPEN csr_get_asg_details(l_ser_start_date);
4202 FETCH csr_get_asg_details INTO l_effective_date;
4203 FETCH csr_get_asg_details INTO l_next_effective_date;
4204
4205 IF l_effective_date <> hr_api.g_eot
4206 AND csr_get_asg_details%NOTFOUND
4207 THEN
4208 -- This is due to termination event
4209 l_start_reason := 'ZZ';
4210 l_event_source := 'ASG';
4211 END IF; -- End if of effective date not eot
4212 END IF; -- End if of event source <> pension check ...
4213
4214 IF g_debug
4215 THEN
4216 l_proc_step := 250;
4217 DEBUG(l_proc_name, l_proc_step);
4218 END IF;
4219
4220 -- get_asg_details(p_assignment_id => p_assignment_id
4221 -- ,p_effective_date => l_ser_start_date
4222 -- ,p_rec_asg_details => l_rec_asg_details
4223 -- );
4224 -- IF g_debug THEN
4225 -- l_proc_step := 240;
4226 -- debug(l_proc_name, l_proc_step);
4227 -- END IF;
4228 --
4229 -- l_psi_emp_type := get_psi_emp_type
4230 -- (p_employment_category => l_rec_asg_details.employment_category);
4231
4232 IF g_debug
4233 THEN
4234 l_proc_step := 260;
4235 DEBUG(l_proc_name, l_proc_step);
4236 DEBUG('l_psi_emp_type: ' || l_psi_emp_type);
4237 END IF;
4238
4239 g_ser_start_date := l_ser_start_date;
4240
4241 IF l_start_reason = 'ZZ'
4242 AND NVL(l_asg_status_type_id, hr_api.g_number) <>
4243 g_terminate_asg_sts_id
4244 THEN
4245 l_asg_status_type_id := g_terminate_asg_sts_id;
4246 END IF;
4247
4248 IF l_start_reason = 'ZZ' AND
4249 g_leaving_reason IS NULL
4250 THEN
4251 -- Get the leaving reason code
4252 IF g_debug
4253 THEN
4254 l_proc_step := 270;
4255 DEBUG(l_proc_name, l_proc_step);
4256 END IF;
4257 OPEN csr_get_leaving_reason(l_rec_asg_details.person_id
4258 ,l_ser_start_date);
4259 FETCH csr_get_leaving_reason INTO l_rec_leaving_reason;
4260 CLOSE csr_get_leaving_reason;
4261
4262 -- Get the penserver leaving reason code
4263 -- for this termination event
4264 IF l_rec_leaving_reason.leaving_reason IS NOT NULL
4265 THEN
4266
4267 IF g_debug
4268 THEN
4269 l_proc_step := 280;
4270 DEBUG(l_proc_name, l_proc_step);
4271 DEBUG('l_rec_leaving_reason.leaving_reason: '
4272 || l_rec_leaving_reason.leaving_reason
4273 );
4274 END IF;
4275 l_index := NULL;
4276 l_return :=
4277 chk_lvrsn_in_collection
4278 (p_leave_reason => l_rec_leaving_reason.leaving_reason
4279 ,p_index => l_index
4280 );
4281 IF l_return = 'Y' THEN
4282 IF g_debug
4283 THEN
4284 DEBUG('g_tab_lvrsn_map_cv(l_index).pcv_information2: '
4285 || g_tab_lvrsn_map_cv(l_index).pcv_information2
4286 );
4287 END IF;
4288 g_leaving_reason := g_tab_lvrsn_map_cv(l_index).pcv_information2;
4289 END IF; -- End if of l_index is not null check ...
4290 ELSE
4291 -- Raise data error
4292 IF g_debug
4293 THEN
4294 DEBUG('Raise Data Error: Leaving Reason Missing');
4295 END IF;
4296 l_value :=
4297 pqp_gb_psi_functions.raise_extract_error(
4298 p_error_number => 94479
4299 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
4300 ,p_token1 => 'Leaving Reason'
4301 );
4302
4303 END IF; -- End if of leaving reason is not null check ...
4304 END IF; -- End if of l_start_reason = 'ZZ' check ...
4305
4306
4307 -- Enhancement 5040543
4308 -- Add a warning message when pension category is null
4309 IF l_pension_category IS NULL
4310 THEN
4311
4312 IF g_debug
4313 THEN
4314 l_proc_step := 290;
4315 DEBUG(l_proc_name, l_proc_step);
4316 DEBUG('Not a member of CS scheme');
4317 END IF;
4318
4319 l_value :=
4320 pqp_gb_psi_functions.raise_extract_warning(
4321 p_error_number => 93775
4322 ,p_error_text => 'BEN_93775_EXT_PSI_NOT_PEN_MEMB'
4323 ,p_token1 => p_assignment_id
4324 ,p_token2 => fnd_date.date_to_displaydt(g_effective_date)
4325 );
4326 END IF; -- End if of pension category is null check ...
4327
4328 get_service_history_code(
4329 p_event_desc => l_start_reason
4330 ,p_pension_scheme => l_pension_category
4331 ,p_employment_type => l_psi_emp_type
4332 ,p_event_source => l_event_source
4333 ,p_absence_type => l_absence_type_id
4334 ,p_asg_status => NVL(
4335 l_asg_status_type_id
4336 ,l_rec_asg_details.assignment_status_type_id
4337 )
4338 ,p_partnership_scheme =>l_partnership_scheme
4339 ,p_start_reason => g_start_reason
4340 ,p_scheme_category => g_scheme_category
4341 ,p_scheme_status => g_scheme_status
4342 );
4343
4344 -- Check whether the person has opted out of the pension scheme
4345 -- on the joining day (hired day)
4346 IF l_start_reason = 'OO' AND l_asg_start_date = l_ser_start_date
4347 THEN
4348 g_start_reason := 'N';
4349 END IF;
4350
4351 IF g_debug
4352 THEN
4353 l_proc_step := 300;
4354 DEBUG('l_asg_status_type_id: ' || l_asg_status_type_id);
4355 DEBUG('g_start_reason: ' || g_start_reason);
4356 DEBUG('g_scheme_category: ' || g_scheme_category);
4357 DEBUG('g_scheme_status: ' || g_scheme_status);
4358 DEBUG('g_ser_start_date: '
4359 || TO_CHAR(g_ser_start_date, 'DD/MON/YYYY'));
4360 debug_exit(l_proc_name);
4361 END IF;
4362 EXCEPTION
4363 WHEN OTHERS
4364 THEN
4365 clear_cache;
4366
4367 IF SQLCODE <> hr_utility.hr_error_number
4368 THEN
4369 debug_others(l_proc_name, l_proc_step);
4370
4371 IF g_debug
4372 THEN
4373 DEBUG('Leaving: ' || l_proc_name, -999);
4374 END IF;
4375
4376 fnd_message.raise_error;
4377 ELSE
4378 RAISE;
4379 END IF;
4380 END get_asg_ser_cutover_data;
4381
4382 -- This function evaluates assignment status events for
4383 -- service history interface
4384 -- ----------------------------------------------------------------------------
4385 -- |---------------------< eval_asg_status_event >----------------------------|
4386 -- ----------------------------------------------------------------------------
4387 FUNCTION eval_asg_status_event(
4388 p_assignment_id IN NUMBER
4389 ,p_curr_status_type_id IN NUMBER
4390 ,p_prev_status_type_id IN NUMBER
4391 ,p_start_reason OUT NOCOPY pqp_configuration_values.pcv_information1%TYPE
4392 ,p_event_source OUT NOCOPY VARCHAR2
4393 )
4394 RETURN VARCHAR2
4395 IS
4396 --
4397 l_proc_name VARCHAR2(80)
4398 := g_proc_name || 'eval_asg_status_event';
4399 l_proc_step PLS_INTEGER;
4400 l_return VARCHAR2(10);
4401 l_start_reason pqp_configuration_values.pcv_information1%TYPE;
4402 l_ser_start_date DATE;
4403 l_event_source VARCHAR2(100);
4404 l_index NUMBER;
4405 --
4406 BEGIN
4407 --
4408 IF g_debug
4409 THEN
4410 l_proc_step := 10;
4411 debug_enter(l_proc_name);
4412 DEBUG('p_assignment_id: ' || p_assignment_id);
4413 DEBUG('p_curr_status_type_id :' || p_curr_status_type_id);
4414 DEBUG('p_prev_status_type_id: ' || p_prev_status_type_id);
4415 END IF;
4416
4417 l_return := 'N';
4418
4419 -- Check whether the current status type is active
4420 IF p_curr_status_type_id = g_active_asg_sts_id
4421 THEN
4422 -- If the current status type id is active
4423 -- Check whether the previous status type id is in the collection
4424 IF g_debug
4425 THEN
4426 l_proc_step := 20;
4427 DEBUG(l_proc_name, l_proc_step);
4428 END IF;
4429
4430 IF p_prev_status_type_id <> g_terminate_asg_sts_id -- ignore rehires
4431 THEN
4432 l_return :=
4433 chk_value_in_collection(
4434 p_collection_name => g_tab_asg_status
4435 ,p_value => p_prev_status_type_id
4436 ,p_index => l_index
4437 );
4438
4439 IF l_return = 'Y'
4440 THEN
4441 IF g_debug
4442 THEN
4443 l_proc_step := 30;
4444 DEBUG(l_proc_name, l_proc_step);
4445 DEBUG('l_return: ' || l_return);
4446 END IF;
4447
4448 -- Yes is in the collection
4449 -- so this should be a return from break status
4450 l_start_reason := 'RB'; -- Return from break
4451 l_event_source := 'ASGBREAK';
4452 END IF; -- End if of value in collection check ...
4453 END IF; -- End if of prev status not in terminations ...
4454 ELSIF p_curr_status_type_id <> g_active_asg_sts_id
4455 AND p_curr_status_type_id <> g_terminate_asg_sts_id
4456 -- we are not interested in terminations
4457 THEN
4458 -- current status is not active
4459 -- check whether the current status is in the collection
4460 IF g_debug
4461 THEN
4462 l_proc_step := 40;
4463 DEBUG(l_proc_name, l_proc_step);
4464 END IF;
4465
4466 l_return :=
4467 chk_value_in_collection(
4468 p_collection_name => g_tab_asg_status
4469 ,p_value => p_curr_status_type_id
4470 ,p_index => l_index
4471 );
4472
4473 IF l_return = 'Y'
4474 THEN
4475 IF g_debug
4476 THEN
4477 l_proc_step := 50;
4478 DEBUG(l_proc_name, l_proc_step);
4479 DEBUG('l_return: ' || l_return);
4480 END IF;
4481
4482 -- We are interested in this status
4483 -- Return the codes
4484 l_start_reason := g_tab_event_map_cv(l_index).pcv_information11;
4485 l_event_source := 'ASG';
4486 END IF; -- End if of value in collection check ...
4487 END IF; -- End if of current status type is active check ...
4488
4489 p_start_reason := l_start_reason;
4490 p_event_source := l_event_source;
4491
4492 IF g_debug
4493 THEN
4494 l_proc_step := 60;
4495 DEBUG(l_proc_name, l_proc_step);
4496 DEBUG('p_start_reason: ' || l_start_reason);
4497 DEBUG('p_event_source: ' || l_event_source);
4498 DEBUG('l_return: ' || l_return);
4499 debug_exit(l_proc_name);
4500 END IF;
4501
4502 RETURN l_return;
4503 EXCEPTION
4504 WHEN OTHERS
4505 THEN
4506 clear_cache;
4507
4508 IF SQLCODE <> hr_utility.hr_error_number
4509 THEN
4510 debug_others(l_proc_name, l_proc_step);
4511
4512 IF g_debug
4513 THEN
4514 DEBUG('Leaving: ' || l_proc_name, -999);
4515 END IF;
4516
4517 fnd_message.raise_error;
4518 ELSE
4519 RAISE;
4520 END IF;
4521 END eval_asg_status_event;
4522
4523 -- This function evaluates absence events for service
4524 -- history interface
4525 -- ----------------------------------------------------------------------------
4526 -- |---------------------< eval_absence_event >-------------------------------|
4527 -- ----------------------------------------------------------------------------
4528 FUNCTION eval_absence_event(
4529 p_assignment_id IN NUMBER
4530 ,p_absence_attendance_id IN NUMBER
4531 ,p_event_group_name IN pay_event_groups.event_group_name%TYPE
4532 ,p_absence_type_id OUT NOCOPY NUMBER
4533 ,p_start_reason OUT NOCOPY pqp_configuration_values.pcv_information1%TYPE
4534 ,p_ser_start_date OUT NOCOPY DATE
4535 ,p_event_source OUT NOCOPY VARCHAR2
4536 )
4537 RETURN VARCHAR2
4538 IS
4539 --
4540 CURSOR csr_get_abs_dtls
4541 IS
4542 SELECT absence_attendance_id, absence_attendance_type_id
4543 ,date_start, date_end
4544 FROM per_absence_attendances
4545 WHERE absence_attendance_id = p_absence_attendance_id;
4546
4547 -- For Bug 5970465
4548 CURSOR csr_get_term_date
4549 IS
4550 SELECT actual_termination_date
4551 FROM per_all_assignments_f paf,
4552 per_periods_of_service pos
4553 WHERE paf.assignment_id=p_assignment_id
4554 AND paf.period_of_service_id = pos.period_of_service_id;
4555
4556
4557
4558 l_proc_name VARCHAR2(80)
4559 := g_proc_name || 'eval_absence_event';
4560 l_proc_step PLS_INTEGER;
4561 l_rec_abs_dtls csr_get_abs_dtls%ROWTYPE;
4562 l_rec_gap_details csr_chk_pay_trans%ROWTYPE;
4563 l_return VARCHAR2(10);
4564 l_value_in_collection VARCHAR2(10);
4565 l_start_reason pqp_configuration_values.pcv_information1%TYPE;
4566 l_ser_start_date DATE;
4567 l_event_source VARCHAR2(100);
4568 l_index NUMBER;
4569 l_absence_event_code VARCHAR2(10);
4570 l_psi_code VARCHAR2(10);
4571 l_absence_type_id NUMBER;
4572 l_actual_term_date DATE;
4573 --
4574
4575 BEGIN
4576 --
4577 IF g_debug
4578 THEN
4579 l_proc_step := 10;
4580 debug_enter(l_proc_name);
4581 DEBUG('p_assignment_id: ' || p_assignment_id);
4582 DEBUG('p_absence_attendance_id: ' || p_absence_attendance_id);
4583 DEBUG('p_event_group_name: ' || p_event_group_name);
4584 END IF;
4585
4586 l_return := 'N';
4587 OPEN csr_get_abs_dtls;
4588 FETCH csr_get_abs_dtls INTO l_rec_abs_dtls;
4589
4590 IF csr_get_abs_dtls%NOTFOUND
4591 THEN
4592 -- Might be a delete event
4593 IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
4594 THEN
4595 --
4596 NULL;
4597 END IF;
4598 ELSE
4599 -- Check whether absence type is in collection
4600 IF g_debug
4601 THEN
4602 l_proc_step := 20;
4603 DEBUG(l_proc_name, l_proc_step);
4604 END IF;
4605
4606 l_value_in_collection :=
4607 chk_value_in_collection(
4608 p_collection_name => g_tab_abs_types
4609 ,p_value => l_rec_abs_dtls.absence_attendance_type_id
4610 ,p_index => l_index
4611 );
4612 --115.21 Collection to be accessed only if value is in collection
4613 --Moved inside IF block=> SUBSTR(g_tab_event_map_cv(l_index)
4614
4615 IF l_value_in_collection = 'Y'
4616 THEN
4617
4618 l_psi_code :=
4619 SUBSTR(g_tab_event_map_cv(l_index).pcv_information1, 1, 1);
4620
4621 IF l_rec_abs_dtls.date_end IS NOT NULL
4622 AND l_rec_abs_dtls.date_end <=
4623 pqp_gb_psi_functions.g_effective_end_date
4624 AND p_event_group_name = 'PQP_GB_PSI_SER_ABSENCES'
4625 THEN
4626 -- For Bug 5970465
4627 OPEN csr_get_term_date;
4628 FETCH csr_get_term_date INTO l_actual_term_date;
4629 CLOSE csr_get_term_date;
4630
4631 IF l_actual_term_date is NULL
4632 or (l_actual_term_date is NOT NULL AND l_actual_term_date > l_rec_abs_dtls.date_end + 1) -- For Bug 6024703
4633 THEN
4634
4635 -- This should be a return from break
4636 -- populate service start date
4637 --115.16 RB event for Sickness,Maternity to be reported
4638 --only if a transition event exists.
4639 IF l_psi_code in ('S','M') then
4640
4641 get_gap_transition_code
4642 (p_assignment_id => p_assignment_id
4643 ,p_absence_attendance_id => p_absence_attendance_id
4644 ,p_effective_date => l_rec_abs_dtls.date_end
4645 ,p_psi_event_code => l_psi_code
4646 ,p_absence_event_code => l_absence_event_code
4647 ,p_rec_gap_details => l_rec_gap_details
4648 );
4649 IF g_debug
4650 THEN
4651 l_proc_step := 25;
4652 debug(l_proc_name, l_proc_step);
4653 debug('l_absence_event_code: '||l_absence_event_code);
4654 END IF;
4655
4656 IF l_absence_event_code is not null then
4657 l_ser_start_date := l_rec_abs_dtls.date_end + 1;
4658 l_start_reason := 'RB';
4659 l_event_source := 'ABSBREAK';
4660 l_return := 'Y';
4661 END IF;
4662
4663 ELSE
4664 --Not Sickness or maternity.Return RB without checking for
4665 --transition
4666 l_ser_start_date := l_rec_abs_dtls.date_end + 1;
4667 l_start_reason := 'RB';
4668 l_event_source := 'ABSBREAK';
4669 l_return := 'Y';
4670 IF g_debug
4671 THEN
4672 l_proc_step := 30;
4673 DEBUG(l_proc_name, l_proc_step);
4674 DEBUG(
4675 'Event Code: '
4676 || g_tab_event_map_cv(l_index).pcv_information1
4677 );
4678 END IF;
4679 END IF;--l_psi_code in('S','M')
4680 END IF; --For Bug 5970465
4681 -- ELSIF l_psi_code IN ('S','M')
4682 -- THEN
4683 -- IF g_debug
4684 -- THEN
4685 -- l_proc_step := 40;
4686 -- debug(l_proc_name, l_proc_step);
4687 -- debug('l_psi_code: '||l_psi_code);
4688 -- END IF;
4689 -- -- still an open ended absence
4690 -- -- check for gap transition code
4691 -- get_gap_transition_code
4692 -- (p_assignment_id => p_assignment_id
4693 -- ,p_absence_attendance_id => p_absence_attendance_id
4694 -- ,p_effective_date => g_effective_date
4695 -- ,p_psi_event_code => l_psi_code
4696 -- ,p_absence_event_code => l_absence_event_code
4697 -- ,p_rec_gap_details => l_rec_gap_details
4698 -- );
4699 -- IF l_absence_event_code IS NOT NULL THEN
4700 -- l_start_reason := l_absence_event_code;
4701 -- l_ser_start_date := l_rec_gap_details.date_start;
4702 -- l_event_source := 'ABS';
4703 -- END IF;
4704 ELSIF l_psi_code NOT IN('S', 'M')
4705 THEN -- Not sickness and maternity -- end date is null
4706 IF g_debug
4707 THEN
4708 l_proc_step := 50;
4709 DEBUG(l_proc_name, l_proc_step);
4710 END IF;
4711
4712 l_start_reason :=
4713 g_tab_event_map_cv(l_index).pcv_information11;
4714 l_ser_start_date := l_rec_abs_dtls.date_start;
4715 l_event_source := 'ABS';
4716 l_absence_type_id :=
4717 l_rec_abs_dtls.absence_attendance_type_id;
4718 l_return := 'Y';
4719 END IF; -- date end is not null check ...
4720 END IF; -- Return = 'Y' check ...
4721 END IF; -- End if of cursor not found check ...
4722
4723 CLOSE csr_get_abs_dtls;
4724 p_absence_type_id := l_absence_type_id;
4725 p_start_reason := l_start_reason;
4726 p_ser_start_date := l_ser_start_date;
4727 p_event_source := l_event_source;
4728
4729 IF g_debug
4730 THEN
4731 l_proc_step := 60;
4732 DEBUG(l_proc_name, l_proc_step);
4733 DEBUG('p_absence_type_id: ' || l_absence_type_id);
4734 DEBUG('p_start_reason: ' || l_start_reason);
4735 DEBUG('p_ser_start_date: '
4736 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
4737 DEBUG('p_event_source: ' || l_event_source);
4738 DEBUG('l_return: ' || l_return);
4739 debug_exit(l_proc_name);
4740 END IF;
4741
4742 RETURN l_return;
4743 EXCEPTION
4744 WHEN OTHERS
4745 THEN
4746 clear_cache;
4747
4748 IF SQLCODE <> hr_utility.hr_error_number
4749 THEN
4750 debug_others(l_proc_name, l_proc_step);
4751
4752 IF g_debug
4753 THEN
4754 DEBUG('Leaving: ' || l_proc_name, -999);
4755 END IF;
4756
4757 fnd_message.raise_error;
4758 ELSE
4759 RAISE;
4760 END IF;
4761 END eval_absence_event;
4762
4763 -- This function evaluates gap transition events for service
4764 -- history interface
4765 -- ----------------------------------------------------------------------------
4766 -- |---------------------< eval_gap_transition_event >------------------------|
4767 -- ----------------------------------------------------------------------------
4768 FUNCTION eval_gap_transition_event(
4769 p_assignment_id IN NUMBER
4770 ,p_gap_duration_summary_id IN NUMBER
4771 ,p_absence_type_id OUT NOCOPY NUMBER
4772 ,p_start_reason OUT NOCOPY pqp_configuration_values.pcv_information1%TYPE
4773 ,p_ser_start_date OUT NOCOPY DATE
4774 ,p_event_source OUT NOCOPY VARCHAR2
4775 )
4776 RETURN VARCHAR2
4777 IS
4778 --
4779 CURSOR csr_get_abs_dtls(c_absence_attendance_id NUMBER)
4780 IS
4781 SELECT absence_attendance_id, absence_attendance_type_id
4782 ,date_start, date_end
4783 FROM per_absence_attendances
4784 WHERE absence_attendance_id = c_absence_attendance_id;
4785
4786 -- Cursor to get gap duration summary details
4787 CURSOR csr_get_gap_summary_dtls
4788 IS
4789 SELECT gap.absence_attendance_id, glds.gap_absence_plan_id
4790 ,glds.gap_level, glds.date_start, glds.date_end
4791 ,glds.summary_type
4792 FROM pqp_gap_absence_plans gap, pqp_gap_duration_summary glds
4793 WHERE glds.gap_absence_plan_id = gap.gap_absence_plan_id
4794 AND glds.gap_duration_summary_id = p_gap_duration_summary_id;
4795
4796 --For Bug 6972649 from here
4797 CURSOR csr_get_act_term_date
4798 IS
4799 SELECT actual_termination_date
4800 FROM per_all_assignments_f paf,
4801 per_periods_of_service pos
4802 WHERE paf.assignment_id=p_assignment_id
4803 AND paf.period_of_service_id = pos.period_of_service_id;
4804 --For Bug 6972649 till here
4805
4806 l_proc_name VARCHAR2(80)
4807 := g_proc_name || 'eval_gap_transition_event';
4808 l_proc_step PLS_INTEGER;
4809 l_rec_abs_dtls csr_get_abs_dtls%ROWTYPE;
4810 l_rec_gap_details csr_get_gap_summary_dtls%ROWTYPE;
4811 l_return VARCHAR2(10);
4812 l_start_reason pqp_configuration_values.pcv_information1%TYPE;
4813 l_ser_start_date DATE;
4814 l_event_source VARCHAR2(100);
4815 l_index NUMBER;
4816 l_absence_code VARCHAR2(10);
4817 l_psi_code VARCHAR2(10);
4818 l_absence_type_id NUMBER;
4819 l_gap_duration_summary_id NUMBER;
4820 --For Bug 6972649 from here
4821 l_actual_term_date DATE;
4822 --For Bug 6972649 till here
4823
4824 --
4825 BEGIN
4826 --
4827 IF g_debug
4828 THEN
4829 l_proc_step := 10;
4830 debug_enter(l_proc_name);
4831 DEBUG('p_assignment_id: ' || p_assignment_id);
4832 DEBUG('p_gap_duration_summary_id: ' || p_gap_duration_summary_id);
4833 END IF;
4834
4835 l_return := 'N';
4836 -- Get gap duration summary details
4837 OPEN csr_get_gap_summary_dtls;
4838 FETCH csr_get_gap_summary_dtls INTO l_rec_gap_details;
4839
4840 IF csr_get_gap_summary_dtls%NOTFOUND
4841 THEN
4842 -- Might be a delete event
4843 IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
4844 THEN
4845 --
4846 NULL;
4847 END IF;
4848 ELSE -- row found
4849 OPEN csr_get_abs_dtls(l_rec_gap_details.absence_attendance_id);
4850 FETCH csr_get_abs_dtls INTO l_rec_abs_dtls;
4851 CLOSE csr_get_abs_dtls;
4852
4853 -- Check whether absence type is in collection
4854 IF g_debug
4855 THEN
4856 l_proc_step := 20;
4857 DEBUG(l_proc_name, l_proc_step);
4858
4859 END IF;
4860
4861 l_return :=
4862 chk_value_in_collection(
4863 p_collection_name => g_tab_abs_types
4864 ,p_value => l_rec_abs_dtls.absence_attendance_type_id
4865 ,p_index => l_index
4866 );
4867
4868 --115.21 Collection to be accessed only if value is in collection
4869 --Moved inside IF block=> SUBSTR(g_tab_event_map_cv(l_index)
4870
4871 IF l_return = 'Y'
4872 THEN
4873 l_psi_code :=
4874 SUBSTR(g_tab_event_map_cv(l_index).pcv_information1, 1, 1);
4875
4876 IF g_debug THEN
4877 DEBUG('l_psi_code '||l_psi_code);
4878 DEBUG('l_rec_gap_details.summary_type '
4879 ||l_rec_gap_details.summary_type);
4880 DEBUG(' l_rec_gap_details.gap_level '
4881 ||l_rec_gap_details.gap_level);
4882 END IF;
4883
4884
4885 IF l_rec_gap_details.summary_type = 'PAY'
4886 --115.21 gap_transitions to be checked only for S and M
4887 AND l_psi_code in ('S','M')
4888 THEN
4889 --For Bug 6972649 from here
4890 OPEN csr_get_act_term_date;
4891 FETCH csr_get_act_term_date INTO l_actual_term_date;
4892 CLOSE csr_get_act_term_date;
4893
4894 IF l_actual_term_date is NULL
4895 or (l_actual_term_date is NOT NULL
4896 AND l_actual_term_date > l_rec_gap_details.date_start)
4897 THEN
4898 DEBUG('ATD is null or it is greater than start date');
4899 DEBUG('l_actual_term_date: '||l_actual_term_date);
4900 DEBUG('l_rec_gap_details.date_start: '||l_rec_gap_details.date_start);
4901 DEBUG('l_psi_code: '||l_psi_code);
4902 DEBUG('l_rec_gap_details.gap_level: '||l_rec_gap_details.gap_level);
4903 --For Bug 6972649 till here
4904
4905 -- l_absence_code := NULL; 115.16.By default it is null.
4906 IF l_psi_code = 'S'
4907 THEN -- Sickness
4908 IF l_rec_gap_details.gap_level = 'BAND2'
4909 THEN
4910 l_absence_code := l_psi_code || 'H';
4911 ELSIF l_rec_gap_details.gap_level = 'NOBANDMIN'
4912 THEN
4913 --5549469 Replaced Px with P
4914 l_absence_code := l_psi_code || 'P';
4915 ELSIF l_rec_gap_details.gap_level = 'NOBAND'
4916 THEN
4917 --5549469 Replaced Nx with N
4918 l_absence_code := l_psi_code || 'N';
4919 ELSE
4920 --5549469.Return N if gap_level is not
4921 --what we checked for.
4922 l_return := 'N';
4923 END IF; -- End if of gap level = BAND 2 check ...
4924 ELSIF l_psi_code = 'M'
4925 THEN -- Maternity
4926 IF l_rec_gap_details.gap_level = 'BAND1'
4927 THEN
4928 l_absence_code := l_psi_code || 'F'; -- For maternity
4929 ELSIF l_rec_gap_details.gap_level = 'NOBAND'
4930 THEN
4931 l_absence_code := l_psi_code || 'N';
4932 ELSE
4933 --5549469.Return N if gap_level is not
4934 --what we checked for.
4935 l_return := 'N';
4936 END IF; -- End if of gap level = BAND1 check ...
4937 END IF; -- End if of sickness check ...
4938
4939 --For Bug 6972649 from here
4940 DEBUG('l_absence_code: '||l_absence_code);
4941 Else --actual termination date is not null and it is less than start date
4942 l_return := 'N';
4943 DEBUG('ATD is not null and it is less than start date');
4944 DEBUG('l_return: '||l_return);
4945 End IF;--actual termination date is NULL
4946 --For Bug 6972649 till here
4947
4948 -- populate the variables only if the codes are in the
4949 -- collection
4950
4951 IF g_debug
4952 THEN
4953 l_proc_step := 30;
4954 DEBUG(l_proc_name, l_proc_step);
4955 DEBUG('l_absence_code: ' || l_absence_code);
4956 END IF;
4957
4958 IF l_absence_code IS NOT NULL
4959 THEN
4960 l_return :=
4961 chk_event_in_collection(p_event_code => l_absence_code);
4962
4963 IF l_return = 'Y'
4964 THEN
4965 IF g_debug
4966 THEN
4967 l_proc_step := 50;
4968 DEBUG(l_proc_name, l_proc_step);
4969 END IF;
4970
4971 l_start_reason := l_absence_code;
4972 l_ser_start_date := l_rec_gap_details.date_start;
4973 l_event_source := 'ABS';
4974 l_absence_type_id :=
4975 l_rec_abs_dtls.absence_attendance_type_id;
4976 END IF; -- End if of l_return = Y check ...
4977 END IF; -- absence code is not null check ...
4978 ELSE
4979 --5549469 115.16
4980 --return N if l_rec_gap_details.summary_type <> 'PAY'
4981 l_return := 'N';
4982 END IF; -- Return = 'Y' check ...
4983 END IF;-- summary_type = 'PAY'and l_psi_code in ('S','M')
4984 END IF; -- End if of cursor not found check ...
4985
4986 CLOSE csr_get_gap_summary_dtls;
4987 p_absence_type_id := l_absence_type_id;
4988 p_start_reason := l_start_reason;
4989 p_ser_start_date := l_ser_start_date;
4990 p_event_source := l_event_source;
4991
4992 IF g_debug
4993 THEN
4994 l_proc_step := 60;
4995 DEBUG(l_proc_name, l_proc_step);
4996 DEBUG('p_absence_type_id: ' || l_absence_type_id);
4997 DEBUG('p_start_reason: ' || l_start_reason);
4998 DEBUG('p_ser_start_date: '
4999 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
5000 DEBUG('p_event_source: ' || l_event_source);
5001 debug_exit(l_proc_name);
5002 END IF;
5003
5004 RETURN l_return;
5005 EXCEPTION
5006 WHEN OTHERS
5007 THEN
5008 clear_cache;
5009
5010 IF SQLCODE <> hr_utility.hr_error_number
5011 THEN
5012 debug_others(l_proc_name, l_proc_step);
5013
5014 IF g_debug
5015 THEN
5016 DEBUG('Leaving: ' || l_proc_name, -999);
5017 END IF;
5018
5019 fnd_message.raise_error;
5020 ELSE
5021 RAISE;
5022 END IF;
5023 END eval_gap_transition_event;
5024
5025 -- Function to check if there is a change in pension scheme
5026 -- on the following day.(i.e same pension element is end dated or attached the previous /following day)
5027 -- For Bug: 6524143
5028 FUNCTION change_in_pension_scheme (p_assignment_id IN NUMBER,
5029 p_pension_change_date IN DATE,
5030 p_start_reason IN VARCHAR2,
5031 p_element_type_id IN NUMBER
5032 )
5033 RETURN BOOLEAN
5034 IS
5035
5036 CURSOR csr_get_opt_in_info_next_day
5037 IS
5038 SELECT element_type_id
5039 FROM pay_element_entries_f
5040 WHERE assignment_id = p_assignment_id
5041 AND element_type_id = p_element_type_id
5042 AND effective_start_date = p_pension_change_date + 1;
5043
5044 CURSOR csr_get_opt_out_info_prev_day
5045 IS
5046 SELECT element_type_id
5047 FROM pay_element_entries_f
5048 WHERE assignment_id = p_assignment_id
5049 AND element_type_id = p_element_type_id
5050 AND effective_end_date = p_pension_change_date - 1;
5051
5052 l_proc_name VARCHAR2(80):= g_proc_name || 'change_in_pension_scheme';
5053 l_proc_step PLS_INTEGER;
5054 l_return_flag BOOLEAN := FALSE;
5055 l_element_type_id NUMBER := NULL;
5056
5057 BEGIN
5058 IF g_debug
5059 THEN
5060 l_proc_step := 10;
5061 debug_enter(l_proc_name);
5062 DEBUG('p_assignment_id: ' || p_assignment_id);
5063 DEBUG('p_start_reason: ' || p_start_reason);
5064 DEBUG('p_element_type_id: ' || p_element_type_id);
5065 DEBUG('p_pension_change_date: ' || p_pension_change_date);
5066 END IF;
5067
5068 l_return_flag := FALSE;
5069
5070 IF p_start_reason = 'OI' THEN
5071 -- check old pension element on prev day.
5072 OPEN csr_get_opt_out_info_prev_day;
5073 FETCH csr_get_opt_out_info_prev_day INTO l_element_type_id;
5074 IF l_element_type_id IS NOT NULL
5075 THEN
5076 l_return_flag := TRUE;
5077 END IF;
5078 close csr_get_opt_out_info_prev_day;
5079
5080 ELSIF p_start_reason = 'OO' THEN
5081 -- check new pension element on next day.
5082 OPEN csr_get_opt_in_info_next_day;
5083 FETCH csr_get_opt_in_info_next_day INTO l_element_type_id;
5084 IF l_element_type_id IS NOT NULL
5085 THEN
5086 l_return_flag := TRUE;
5087 END IF;
5088 close csr_get_opt_in_info_next_day;
5089 END IF;
5090
5091 IF g_debug
5092 THEN
5093 l_proc_step := 20;
5094 DEBUG(l_proc_name, l_proc_step);
5095 IF l_return_flag = TRUE THEN
5096 DEBUG('l_return_flag: ' || 'TRUE');
5097 ELSE
5098 DEBUG('l_return_flag: ' || 'FALSE');
5099 END IF;
5100 debug_exit(l_proc_name);
5101 END IF;
5102
5103 RETURN l_return_flag;
5104 EXCEPTION
5105 WHEN OTHERS
5106 THEN
5107 clear_cache;
5108
5109 IF SQLCODE <> hr_utility.hr_error_number
5110 THEN
5111 debug_others(l_proc_name, l_proc_step);
5112
5113 IF g_debug
5114 THEN
5115 DEBUG('Leaving: ' || l_proc_name, -999);
5116 END IF;
5117
5118 fnd_message.raise_error;
5119 ELSE
5120 RAISE;
5121 END IF;
5122 END change_in_pension_scheme;
5123
5124 -- This function is used to evaluate pension events for
5125 -- service history periodic interface
5126 -- ----------------------------------------------------------------------------
5127 -- |--------------------------< eval_pension_event >--------------------------|
5128 -- ----------------------------------------------------------------------------
5129 FUNCTION eval_pension_event(
5130 p_assignment_id IN NUMBER
5131 ,p_table_name IN VARCHAR2
5132 ,p_surrogate_key IN NUMBER
5133 ,p_ser_start_date OUT NOCOPY DATE
5134 ,p_start_reason OUT NOCOPY VARCHAR2
5135 ,p_event_source OUT NOCOPY VARCHAR2
5136 ,p_pension_category OUT NOCOPY VARCHAR2
5137 ,p_partnership_scheme OUT NOCOPY VARCHAR2
5138 )
5139 RETURN VARCHAR2
5140 IS
5141 --
5142 -- Cursor to fetch element entry information
5143 CURSOR csr_get_ele_ent_info(c_element_entry_id NUMBER)
5144 IS
5145 SELECT pel.element_type_id, pee.effective_start_date
5146 ,pee.effective_end_date, pee.element_entry_id
5147 FROM pay_element_entries_f pee, pay_element_links_f pel
5148 WHERE pee.element_entry_id = c_element_entry_id
5149 AND g_effective_date BETWEEN pee.effective_start_date
5150 AND pee.effective_end_date
5151 AND pel.element_link_id = pee.element_link_id
5152 AND g_effective_date BETWEEN pee.effective_start_date
5153 AND pee.effective_end_date;
5154
5155 -- Cursor to fetch element entry value information
5156 CURSOR csr_get_ele_ent_val(c_element_entry_value_id NUMBER)
5157 IS
5158 SELECT input_value_id, screen_entry_value, element_entry_id
5159 FROM pay_element_entry_values_f
5160 WHERE element_entry_value_id = c_element_entry_value_id
5161 AND g_effective_date BETWEEN effective_start_date
5162 AND effective_end_date;
5163
5164 -- Cursor to fetch min assignment effective start date
5165 -- for this employment category
5166 CURSOR csr_get_asg_start_date(c_employment_category VARCHAR2)
5167 IS
5168 SELECT MIN(effective_start_date)
5169 FROM per_all_assignments_f
5170 WHERE assignment_id = p_assignment_id
5171 AND employment_category = c_employment_category;
5172
5173 -- Cursor to check assignment details
5174 -- For Bug 5930973
5175 --------------------
5176 -- Cursor to check Emp termination details
5177 -- For Bug 6836466
5178 CURSOR csr_get_asg_details
5179 IS
5180 SELECT effective_end_date
5181 FROM per_all_assignments_f paaf,
5182 per_periods_of_service pps,
5183 per_assignment_status_types past
5184 WHERE paaf.assignment_id = p_assignment_id
5185 AND paaf.assignment_status_type_id = past.assignment_status_type_id
5186 and pps.person_id = paaf.person_id
5187 and pps.period_of_service_id = paaf.period_of_service_id
5188 AND past.per_system_status = 'ACTIVE_ASSIGN'
5189 and g_effective_date <> NVL(pps.final_process_date, hr_api.g_eot)
5190 AND g_effective_date BETWEEN paaf.effective_start_date
5191 AND paaf.effective_end_date
5192 ORDER BY paaf.effective_start_date;
5193
5194 --For bug 7013325: Start here
5195 CURSOR csr_get_hire_date
5196 IS
5197 SELECT MIN(effective_start_date)
5198 FROM per_all_assignments_f
5199 WHERE assignment_id = p_assignment_id;
5200 --For bug 7013325: End here
5201
5202 --For Bug 5998108
5203 --Cursor to fetch element type id's for assignment between
5204 --start of employment_category and pension element start date
5205 CURSOR csr_get_ele_type (asg_start_date DATE,ele_start_date DATE)
5206 IS
5207 SELECT element_type_id
5208 FROM pay_element_entries_f
5209 WHERE assignment_id = p_assignment_id
5210 AND effective_start_date BETWEEN asg_start_date
5211 AND (ele_start_date-1)
5212 ORDER BY effective_start_date;
5213 --For Bug 5998108
5214
5215
5216 l_proc_name VARCHAR2(80)
5217 := g_proc_name || 'eval_pension_event';
5218 l_proc_step PLS_INTEGER;
5219 l_rec_ele_ent_info csr_get_ele_ent_info%ROWTYPE;
5220 l_rec_ele_ent_val csr_get_ele_ent_val%ROWTYPE;
5221 l_return VARCHAR2(10);
5222 l_start_reason pqp_configuration_values.pcv_information1%TYPE;
5223 l_ser_start_date DATE;
5224 l_event_source VARCHAR2(100);
5225 l_opt_out_date DATE;
5226 l_asg_start_date DATE;
5227 l_leaver_date DATE;
5228 l_psi_emp_type pqp_configuration_values.pcv_information1%TYPE;
5229 l_input_value_id NUMBER;
5230 l_char pay_element_entry_values_f.screen_entry_value%TYPE;
5231 l_pension_category pqp_configuration_values.pcv_information1%TYPE;
5232 l_partnership_scheme VARCHAR2(30);
5233 i NUMBER;
5234 l_effective_end_date DATE;
5235 l_next_effective_end_date DATE;
5236 --For Bug 5998108
5237 Flag VARCHAR2(10):= 'N';
5238 --For bug 7013325: Start here
5239 l_hire_date DATE;
5240 --For bug 7013325: End here
5241 --
5242 BEGIN
5243 --
5244 IF g_debug
5245 THEN
5246 l_proc_step := 10;
5247 debug_enter(l_proc_name);
5248 DEBUG('p_assignment_id: ' || p_assignment_id);
5249 DEBUG('p_table_name: ' || p_table_name);
5250 DEBUG('p_surrogate_key: ' || p_surrogate_key);
5251 END IF;
5252
5253 l_return := 'N';
5254
5255 IF p_table_name = 'PAY_ELEMENT_ENTRIES_F'
5256 THEN
5257 IF g_debug
5258 THEN
5259 l_proc_step := 20;
5260 DEBUG(l_proc_name, l_proc_step);
5261 END IF;
5262
5263 -- Check whether this is an element entry we are interested
5264 -- in
5265 OPEN csr_get_ele_ent_info(p_surrogate_key);
5266 FETCH csr_get_ele_ent_info INTO l_rec_ele_ent_info;
5267
5268 IF csr_get_ele_ent_info%NOTFOUND
5269 THEN
5270 -- May be a purge event
5271 IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
5272 THEN
5273 --
5274 NULL;
5275 END IF;
5276 ELSE -- row found
5277 IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'I'
5278 --Bug 9179022: Added update of EFFECTIVE_START_DATE as a valid event
5279 OR (g_tab_pay_proc_evnts(g_event_counter).update_type = 'U'
5280 AND
5281 g_tab_pay_proc_evnts(g_event_counter).column_name = 'EFFECTIVE_START_DATE')
5282 THEN
5283 -- This is an insert event
5284 -- Check whether this element type id exists in the
5285 -- pension element collection
5286 IF g_debug
5287 THEN
5288 l_proc_step := 30;
5289 DEBUG(l_proc_name, l_proc_step);
5290 DEBUG('l_rec_ele_ent_info.element_type_id : '
5291 || l_rec_ele_ent_info.element_type_id);
5292 END IF;
5293
5294 IF g_tab_pen_ele_ids.EXISTS(l_rec_ele_ent_info.element_type_id)
5295 AND g_opt_in = 'Y'
5296 THEN
5297 -- Yes this is a pension element
5298 -- We are interested in this event
5299 -- Check whether the effective start date matches with
5300 -- the assignment start date
5301 -- Get the employment type
5302 l_psi_emp_type := get_psi_emp_type(p_employment_category
5303 => g_assignment_dtl.employment_category);
5304
5305 OPEN csr_get_asg_start_date(g_assignment_dtl.employment_category);
5306 FETCH csr_get_asg_start_date INTO l_asg_start_date;
5307 CLOSE csr_get_asg_start_date;
5308
5309 --For bug 7013325: Start here
5310 OPEN csr_get_hire_date;
5311 FETCH csr_get_hire_date INTO l_hire_date;
5312 CLOSE csr_get_hire_date;
5313 --For bug 7013325: End here
5314
5315 IF g_debug
5316 THEN
5317 l_proc_step := 40;
5318 DEBUG(l_proc_name, l_proc_step);
5319 DEBUG('l_psi_emp_type: ' || l_psi_emp_type);
5320 DEBUG('l_asg_start_date: '|| TO_CHAR(l_asg_start_date, 'DD/MON/YYYY'));
5321 DEBUG('l_rec_ele_ent_info.effective_start_date: '
5322 || TO_CHAR(l_rec_ele_ent_info.effective_start_date,'DD/MON/YYYY'));
5323 END IF;
5324
5325 IF l_psi_emp_type = 'CASUAL'
5326 THEN
5327
5328 --For bug 7013325:Start
5329 -- Check if the effective start date of the pension element
5330 -- is equal to the change in asg_category
5331 IF l_rec_ele_ent_info.effective_start_date = l_asg_start_date
5332 THEN
5333 --Check if asg_cate change date is equal to hire date
5334 IF l_asg_start_date = l_hire_date
5335 THEN
5336 --Report a New Joiner
5337 l_start_reason := 'N';
5338 l_event_source := 'SER';
5339 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5340 l_return := 'Y';
5341
5342 ELSE
5343 --check if any pension element was attached to this assignmrnt before
5344 For rec_get_ele_type in csr_get_ele_type(l_hire_date,l_rec_ele_ent_info.effective_start_date)
5345 Loop
5346 IF g_tab_pen_ele_ids.EXISTS(rec_get_ele_type.element_type_id)
5347 THEN
5348 Flag := 'Y';
5349 DEBUG('Flag = '||Flag);
5350 Exit;
5351 End IF;
5352 End Loop;
5353
5354 IF Flag = 'Y'
5355 THEN
5356 --For change in pension scheme
5357 IF change_in_pension_scheme (p_assignment_id,
5358 l_rec_ele_ent_info.effective_start_date,
5359 'OI',
5360 l_rec_ele_ent_info.element_type_id )
5361 THEN
5362 --Report a New Joiner
5363 l_start_reason := 'N';
5364 l_event_source := 'SER';
5365 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5366 l_return := 'Y';
5367
5368 ELSE
5369
5370 l_start_reason := 'OI';
5371 l_event_source := 'PENSION';
5372 l_ser_start_date :=
5373 l_rec_ele_ent_info.effective_start_date;
5374 l_return := 'Y';
5375 END IF; -- END if of change_in_pension_scheme
5376
5377 ELSE
5378 --Report a New Joiner
5379 l_start_reason := 'N';
5380 l_event_source := 'SER';
5381 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5382 l_return := 'Y';
5383 END IF; --end of if Flag = 'Y'
5384 END IF; --end of if l_asg_start_date = l_hire_date
5385 END IF; --end of if pension element start date = asg_cate change date
5386 --For bug 7013325: End
5387
5388
5389 -- Check whether the effective start date of the pension element
5390 -- is 3 months later than the assignment start date
5391 -- Remove the three month rule check for casuals
5392 IF l_rec_ele_ent_info.effective_start_date > l_asg_start_date
5393 -- ADD_MONTHS(l_asg_start_date, 3)
5394 THEN
5395 IF g_debug
5396 THEN
5397 l_proc_step := 50;
5398 DEBUG(l_proc_name, l_proc_step);
5399 END IF;
5400
5401 --For Bug 5998108
5402 --For bug 7013325: Changed variable passed to open cursor to l_hire_date
5403 For rec_get_ele_type in csr_get_ele_type(l_hire_date,l_rec_ele_ent_info.effective_start_date)
5404 Loop
5405
5406 IF g_tab_pen_ele_ids.EXISTS(rec_get_ele_type.element_type_id)
5407 THEN
5408 Flag := 'Y';
5409 DEBUG('Flag = '||Flag);
5410 Exit;
5411 End IF;
5412
5413 End Loop;
5414
5415 IF (Flag = 'N')
5416 THEN
5417 --Report New Joiner
5418 /* 10174310
5419 Casual employees should be reported similar to Permanent Employees
5420 l_start_reason := 'N';
5421 l_event_source := 'SER'; */
5422 l_start_reason := 'OI';
5423 l_event_source := 'PENSION';
5424 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5425 l_return := 'Y';
5426 ELSE
5427 --For Bug 5998108
5428
5429 -- change_in_pension_scheme is called before returning the value
5430 -- to check same pension element is end dated and re-attached on following day
5431 -- if yes then assingment should not report OPT IN event
5432 IF change_in_pension_scheme (p_assignment_id,
5433 l_rec_ele_ent_info.effective_start_date,
5434 'OI',
5435 l_rec_ele_ent_info.element_type_id ) -- Bug: 6524143
5436 THEN
5437 l_return := 'N';
5438 ELSE
5439
5440 l_start_reason := 'OI';
5441 l_event_source := 'PENSION';
5442 l_ser_start_date :=
5443 l_rec_ele_ent_info.effective_start_date;
5444 l_return := 'Y';
5445 END IF; -- END if of change_in_pension_scheme
5446 END IF; --End of if Flag = N, For bug 5998108
5447 END IF; -- End if of effective start date > 3 months check ...
5448
5449 ELSIF l_psi_emp_type in ('REGULAR','FIXED') --5897563 115.19
5450 THEN
5451
5452 --For bug 7013325:Start
5453 -- Check if the effective start date of the pension element
5454 -- is equal to the change in asg_category
5455 IF l_rec_ele_ent_info.effective_start_date = l_asg_start_date
5456 THEN
5457 --Check if asg_cate change date is equal to hire date
5458 IF l_asg_start_date = l_hire_date
5459 THEN
5460 --Report a New Joiner
5461 l_start_reason := 'N';
5462 l_event_source := 'SER';
5463 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5464 l_return := 'Y';
5465
5466 ELSE
5467 --check if any pension element was attached to this assignmrnt before
5468 For rec_get_ele_type in csr_get_ele_type(l_hire_date,l_rec_ele_ent_info.effective_start_date)
5469 Loop
5470 IF g_tab_pen_ele_ids.EXISTS(rec_get_ele_type.element_type_id)
5471 THEN
5472 Flag := 'Y';
5473 DEBUG('Flag = '||Flag);
5474 Exit;
5475 End IF;
5476 End Loop;
5477
5478 IF Flag = 'Y'
5479 THEN
5480 --For change in pension scheme
5481 IF change_in_pension_scheme (p_assignment_id,
5482 l_rec_ele_ent_info.effective_start_date,
5483 'OI',
5484 l_rec_ele_ent_info.element_type_id )
5485 THEN
5486 --Report a New Joiner
5487 l_start_reason := 'N';
5488 l_event_source := 'SER';
5489 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5490 l_return := 'Y';
5491
5492 ELSE
5493
5494 l_start_reason := 'OI';
5495 l_event_source := 'PENSION';
5496 l_ser_start_date :=
5497 l_rec_ele_ent_info.effective_start_date;
5498 l_return := 'Y';
5499 END IF; -- END if of change_in_pension_scheme
5500
5501 ELSE
5502 --Report a New Joiner
5503 l_start_reason := 'N';
5504 l_event_source := 'SER';
5505 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5506 l_return := 'Y';
5507 END IF; --end of if Flag = 'Y'
5508 END IF; --end of if l_asg_start_date = l_hire_date
5509 END IF; --end of if pension element start date = asg_cate change date
5510 --For bug 7013325: End
5511
5512
5513 -- Check whether the effective start date of the pension element
5514 -- is NOT the same day as assignment start date
5515 IF l_rec_ele_ent_info.effective_start_date > l_asg_start_date
5516 THEN
5517
5518 IF g_debug
5519 THEN
5520 l_proc_step := 60;
5521 DEBUG(l_proc_name, l_proc_step);
5522 END IF;
5523
5524 -- change_in_pension_scheme is called before returning the value
5525 -- to check same pension element is end dated and re-attached on following day
5526 -- if yes then assingment should not report OPT IN event
5527 IF change_in_pension_scheme (p_assignment_id,
5528 l_rec_ele_ent_info.effective_start_date,
5529 'OI',
5530 l_rec_ele_ent_info.element_type_id ) -- Bug: 6524143
5531 THEN
5532 l_return := 'N';
5533 ELSE
5534 l_start_reason := 'OI';
5535 l_event_source := 'PENSION';
5536 l_ser_start_date := l_rec_ele_ent_info.effective_start_date;
5537 --For bug 7013325:Start
5538 l_return := 'Y';
5539 END IF; -- END if of change_in_pension_scheme
5540 END IF; -- End if of effective start date > asg start date check ...
5541 --For bug 7013325: End
5542
5543 -- Get pension category
5544 i := g_tab_pen_sch_map_cv.FIRST;
5545 WHILE i IS NOT NULL
5546 LOOP
5547 IF fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1) =
5548 l_rec_ele_ent_info.element_type_id
5549 THEN
5550 l_pension_category :=
5551 g_tab_pen_sch_map_cv(i).pcv_information2;
5552 l_partnership_scheme :=g_tab_pen_sch_map_cv(i).pcv_information3;
5553 END IF;
5554
5555 i := g_tab_pen_sch_map_cv.NEXT(i);
5556 END LOOP;
5557
5558 --For bug 7013325: Start
5559 /* l_return := 'Y';
5560 END IF; -- END if of change_in_pension_scheme
5561 END IF; -- End if of effective start date > asg start date check ... */
5562 --For bug 7013325: End
5563
5564 END IF; -- End if of employment tyep is casual check ...
5565 END IF; -- Pension element exists check ...
5566
5567 -- ELSIF g_tab_pay_proc_evnts(g_event_counter).update_type = 'U'
5568 ELSIF g_tab_pay_proc_evnts(g_event_counter).update_type
5569 in ('U','E') --115.20 5930973
5570 THEN
5571 -- Date track update
5572 -- Check whether the effective end date of the element entry
5573 -- is not end of time
5574 -- Do this check only if this is a pension element and
5575 -- ensure that this is not because of a termination event
5576 IF g_debug
5577 THEN
5578 l_proc_step := 70;
5579 DEBUG(l_proc_name, l_proc_step);
5580 DEBUG(
5581 'l_rec_ele_ent_info.effective_end_date: '
5582 || TO_CHAR(
5583 l_rec_ele_ent_info.effective_end_date
5584 ,'DD/MON/YYYY'
5585 )
5586 );
5587 END IF;
5588
5589 IF g_tab_pen_ele_ids.EXISTS(l_rec_ele_ent_info.element_type_id)
5590 AND g_opt_out = 'Y'
5591 AND l_rec_ele_ent_info.effective_end_date <> hr_api.g_eot
5592 AND l_rec_ele_ent_info.effective_end_date <=
5593 pqp_gb_psi_functions.g_effective_end_date
5594 THEN
5595 -- Yes this is an end date event
5596 IF g_debug
5597 THEN
5598 l_proc_step := 80;
5599 DEBUG(l_proc_name, l_proc_step);
5600 END IF;
5601
5602 OPEN csr_get_asg_details;
5603 FETCH csr_get_asg_details INTO l_effective_end_date;
5604 -- FETCH csr_get_asg_details INTO l_next_effective_end_date;
5605
5606 IF l_effective_end_date = hr_api.g_eot
5607 OR csr_get_asg_details%FOUND
5608 THEN
5609 -- This is NOT due to a leaver event
5610 -- might be an opt out event
5611 -- double check by getting the opt out information
5612 IF g_debug
5613 THEN
5614 l_proc_step := 90;
5615 DEBUG(l_proc_name, l_proc_step);
5616 END IF;
5617
5618 l_input_value_id :=
5619 g_tab_pen_ele_ids(l_rec_ele_ent_info.element_type_id).input_value_id;
5620
5621 l_char :=
5622 get_screen_entry_value(
5623 p_element_entry_id => l_rec_ele_ent_info.element_entry_id
5624 ,p_effective_start_date => l_rec_ele_ent_info.effective_start_date
5625 ,p_effective_end_date => l_rec_ele_ent_info.effective_end_date
5626 ,p_input_value_id => l_input_value_id
5627 );
5628 l_opt_out_date := fnd_date.canonical_to_date(l_char);
5629
5630 IF l_opt_out_date IS NULL
5631 THEN
5632 l_ser_start_date :=
5633 l_rec_ele_ent_info.effective_end_date + 1; -- For Bug 5930973
5634 ELSE
5635 l_ser_start_date :=
5636 LEAST(
5637 l_opt_out_date
5638 ,l_rec_ele_ent_info.effective_end_date + 1 -- For Bug 5930973
5639 );
5640 END IF; -- End if of opt out date is null check ...
5641
5642 -- change_in_pension_scheme is called before returning the value
5643 -- to check same pension element is end dated and re-attached on following day
5644 -- if yes then assingment should not report OPT IN event
5645 IF change_in_pension_scheme (p_assignment_id,
5646 l_rec_ele_ent_info.effective_end_date,
5647 'OO',
5648 l_rec_ele_ent_info.element_type_id ) -- Bug: 6524143
5649 THEN
5650 l_return := 'N';
5651 ELSE
5652
5653 l_start_reason := 'OO';
5654 l_event_source := 'PENSION';
5655 -- Get pension category
5656 i := g_tab_pen_sch_map_cv.FIRST;
5657
5658 WHILE i IS NOT NULL
5659 LOOP
5660 IF fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1) =
5661 l_rec_ele_ent_info.element_type_id
5662 THEN
5663 l_pension_category :=
5664 g_tab_pen_sch_map_cv(i).pcv_information2;
5665 l_partnership_scheme :=
5666 g_tab_pen_sch_map_cv(i).pcv_information3;
5667 END IF;
5668
5669 i := g_tab_pen_sch_map_cv.NEXT(i);
5670 END LOOP;
5671
5672 l_return := 'Y';
5673 END IF; --END if of change_in_pension_scheme
5674 END IF; -- End if of asg details found check ...
5675
5676 CLOSE csr_get_asg_details;
5677 END IF; -- End if of opt out exists check ...
5678 END IF; -- End if of update_type check ...
5679 END IF; -- End if of row not found check ...
5680
5681 CLOSE csr_get_ele_ent_info;
5682 ELSIF p_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5683 THEN
5684 IF g_debug
5685 THEN
5686 l_proc_step := 100;
5687 DEBUG(l_proc_name, l_proc_step);
5688 END IF;
5689
5690 -- Get the element entry value details
5691 -- to double check whether this is of pension element
5692 -- opt out date input value
5693 OPEN csr_get_ele_ent_val(p_surrogate_key);
5694 FETCH csr_get_ele_ent_val INTO l_rec_ele_ent_val;
5695
5696 IF csr_get_ele_ent_val%FOUND
5697 THEN
5698 -- Get the element entry details
5699 IF g_debug
5700 THEN
5701 l_proc_step := 110;
5702 DEBUG(l_proc_name, l_proc_step);
5703 DEBUG(
5704 'l_rec_ele_ent_val.element_entry_id: '
5705 || l_rec_ele_ent_val.element_entry_id
5706 );
5707 END IF;
5708
5709 OPEN csr_get_ele_ent_info(l_rec_ele_ent_val.element_entry_id);
5710 FETCH csr_get_ele_ent_info INTO l_rec_ele_ent_info;
5711
5712 IF csr_get_ele_ent_info%FOUND
5713 THEN
5714 -- Check whether this is a pension element
5715 IF g_debug
5716 THEN
5717 l_proc_step := 120;
5718 DEBUG(l_proc_name, l_proc_step);
5719 DEBUG(
5720 'l_rec_ele_ent_info.element_type_id: '
5721 || l_rec_ele_ent_info.element_type_id
5722 );
5723 END IF;
5724
5725 IF g_tab_pen_ele_ids.EXISTS(l_rec_ele_ent_info.element_type_id)
5726 AND g_opt_out = 'Y'
5727 THEN
5728 IF g_debug
5729 THEN
5730 l_proc_step := 130;
5731 DEBUG(l_proc_name, l_proc_step);
5732 DEBUG(
5733 'l_rec_ele_ent_val.input_value_id: '
5734 || l_rec_ele_ent_val.input_value_id
5735 );
5736 DEBUG(
5737 'l_rec_ele_ent_val.screen_entry_value: '
5738 || l_rec_ele_ent_val.screen_entry_value
5739 );
5740 END IF;
5741
5742 IF g_tab_pen_ele_ids(l_rec_ele_ent_info.element_type_id).input_value_id =
5743 l_rec_ele_ent_val.input_value_id
5744 THEN
5745 -- Yes this is the opt out date input value
5746 IF g_debug
5747 THEN
5748 l_proc_step := 140;
5749 DEBUG(l_proc_name, l_proc_step);
5750 END IF;
5751
5752 l_opt_out_date :=
5753 fnd_date.canonical_to_date(l_rec_ele_ent_val.screen_entry_value);
5754
5755 IF l_opt_out_date IS NOT NULL
5756 AND l_opt_out_date <=
5757 pqp_gb_psi_functions.g_effective_end_date
5758 THEN
5759 l_ser_start_date :=
5760 LEAST(
5761 l_opt_out_date
5762 ,l_rec_ele_ent_info.effective_end_date
5763 );
5764 l_event_source := 'PENSION';
5765 l_start_reason := 'OO';
5766 -- Get pension category
5767 i := g_tab_pen_sch_map_cv.FIRST;
5768
5769 WHILE i IS NOT NULL
5770 LOOP
5771 IF fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1) =
5772 l_rec_ele_ent_info.element_type_id
5773 THEN
5774 l_pension_category :=
5775 g_tab_pen_sch_map_cv(i).pcv_information2;
5776 l_partnership_scheme :=
5777 g_tab_pen_sch_map_cv(i).pcv_information3;
5778 END IF;
5779
5780 i := g_tab_pen_sch_map_cv.NEXT(i);
5781 END LOOP;
5782
5783 l_return := 'Y';
5784 END IF; -- End if of opt out date is not null check ...
5785 END IF; -- End if of input value check ...
5786 END IF; -- End if of pension element check ...
5787 END IF; -- End if of ele entry row found check ..
5788
5789 CLOSE csr_get_ele_ent_info;
5790 END IF; -- End if of ele entry value row found check ...
5791
5792 CLOSE csr_get_ele_ent_val;
5793 END IF; -- End if of table name check ...
5794
5795 p_start_reason := l_start_reason;
5796 p_ser_start_date := l_ser_start_date;
5797 p_event_source := l_event_source;
5798 p_pension_category := l_pension_category;
5799 p_partnership_scheme := l_partnership_scheme;
5800
5801 IF g_debug
5802 THEN
5803 l_proc_step := 150;
5804 DEBUG(l_proc_name, l_proc_step);
5805 DEBUG('p_start_reason: ' || l_start_reason);
5806 DEBUG('p_ser_start_date: '
5807 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY'));
5808 DEBUG('p_event_source: ' || l_event_source);
5809 DEBUG('p_pension_category: ' || l_pension_category);
5810 DEBUG('-_partnership_scheme: '||l_partnership_scheme);
5811 DEBUG('l_return: ' || l_return);
5812 debug_exit(l_proc_name);
5813 END IF;
5814
5815 RETURN l_return;
5816 EXCEPTION
5817 WHEN OTHERS
5818 THEN
5819 clear_cache;
5820
5821 IF SQLCODE <> hr_utility.hr_error_number
5822 THEN
5823 debug_others(l_proc_name, l_proc_step);
5824
5825 IF g_debug
5826 THEN
5827 DEBUG('Leaving: ' || l_proc_name, -999);
5828 END IF;
5829
5830 fnd_message.raise_error;
5831 ELSE
5832 RAISE;
5833 END IF;
5834 END eval_pension_event;
5835
5836 -- This procedure is used to check whether assignment
5837 -- qualifies for service history periodic changes
5838 -- ----------------------------------------------------------------------------
5839 -- |---------------------< chk_ser_periodic_criteria >------------------------|
5840 -- ----------------------------------------------------------------------------
5841 FUNCTION chk_ser_periodic_criteria(p_assignment_id IN NUMBER)
5842 RETURN VARCHAR2
5843 IS
5844 --
5845 -- Cursor to fetch min assignment effective start date
5846 -- for this employment category
5847 CURSOR csr_get_asg_start_date(c_employment_category VARCHAR2)
5848 IS
5849 SELECT MIN(effective_start_date)
5850 FROM per_all_assignments_f
5851 WHERE assignment_id = p_assignment_id
5852 AND employment_category = c_employment_category;
5853
5854 -- Cursor to fetch termination details
5855 --115.21 5945283 CURSOR csr_get_ser_details modified
5856
5857 CURSOR csr_get_ser_details IS --in 115.21 5945283
5858 SELECT leaving_reason, actual_termination_date,final_process_date
5859 FROM per_all_assignments_f paf,
5860 per_periods_of_service pos
5861 WHERE paf.assignment_id=p_assignment_id
5862 AND paf.period_of_service_id = pos.period_of_service_id;
5863 --date join not required as all rows will have same data.
5864
5865 -- Cursor to fetch leaving reason for non period of service
5866 -- events
5867 CURSOR csr_get_leaving_reason(c_person_id NUMBER
5868 ,c_effective_date DATE)
5869 IS
5870 SELECT pps.leaving_reason, pps.actual_termination_date
5871 FROM per_periods_of_service pps
5872 WHERE pps.person_id = c_person_id
5873 AND pps.date_start = (SELECT MAX(date_start)
5874 FROM per_periods_of_service pps1
5875 WHERE pps1.person_id = c_person_id
5876 AND pps1.date_start <= c_effective_date);
5877
5878 --For bug 7013325:Start
5879 --Cursor to fetch elements attached on asg_category change date
5880 CURSOR cur_get_asg_chg_dt_ele(c_asg_cate_chng_date DATE)
5881 IS
5882 SELECT element_type_id, element_entry_id
5883 FROM pay_element_entries_f
5884 WHERE assignment_id = p_assignment_id
5885 AND effective_start_date = c_asg_cate_chng_date;
5886
5887 --Cursor to check if the event on Element Entries is an Insert or Update
5888 CURSOR cur_get_ele_entry_id(c_element_entry_id NUMBER, c_asg_cate_chng_date DATE)
5889 IS
5890 SELECT element_entry_id
5891 FROM pay_element_entries_f
5892 WHERE assignment_id = p_assignment_id
5893 AND element_entry_id = c_element_entry_id
5894 AND effective_end_date = c_asg_cate_chng_date -1;
5895 --For bug 7013325: End
5896
5897 l_proc_name VARCHAR2(80)
5898 := g_proc_name || 'chk_ser_periodic_criteria';
5899 l_proc_step PLS_INTEGER;
5900 l_include_flag VARCHAR2(10);
5901 i NUMBER;
5902 l_tab_pay_proc_evnts ben_ext_person.t_detailed_output_table;
5903 l_latest_start_date DATE;
5904 l_rec_asg_details r_asg_details;
5905 l_ser_start_date DATE;
5906 l_start_reason VARCHAR2(10);
5907 l_event_source VARCHAR2(20);
5908 l_asg_start_date DATE;
5909 l_psi_code VARCHAR2(10);
5910 l_psi_emp_type pqp_configuration_values.pcv_information1%TYPE;
5911 l_absence_type_id NUMBER;
5912 l_pension_category pqp_configuration_values.pcv_information1%TYPE;
5913 l_rec_ele_ent_details r_ele_ent_details;
5914 l_asg_status_type_id NUMBER;
5915 l_event_group_id NUMBER;
5916 l_event_group_name pay_event_groups.event_group_name%TYPE;
5917 l_absence_attendance_id NUMBER;
5918 l_assignment_id NUMBER;
5919 l_rec_ser_details csr_get_ser_details%ROWTYPE;
5920 l_dated_table_id NUMBER;
5921 l_table_name pay_dated_tables.table_name%TYPE;
5922 --115.21 l_period_of_service_id NUMBER;
5923 l_curr_status_type_id NUMBER;
5924 l_prev_status_type_id NUMBER;
5925 l_return VARCHAR2(10);
5926 l_leaver_date DATE;
5927 l_surrogate_key NUMBER;
5928 l_gap_duration_summary_id NUMBER;
5929 l_process_flag VARCHAR2(10);
5930 l_value NUMBER;
5931 l_index NUMBER;
5932 l_rec_leaving_reason csr_get_leaving_reason%ROWTYPE;
5933 l_partnership_scheme VARCHAR2(30);
5934 --For bug 7013325:Start
5935 l_element_entry_id NUMBER := NULL;
5936 l_flag VARCHAR2(10):= 'N';
5937 --For bug 7013325:End
5938
5939 --For Bug 7034476: Added new variable
5940 l_column_name VARCHAR2(40);
5941
5942 --
5943 BEGIN
5944 --
5945 IF g_debug
5946 THEN
5947 l_proc_step := 10;
5948 debug_enter(l_proc_name);
5949 DEBUG('p_assignment_id: ' || p_assignment_id);
5950 END IF;
5951
5952 l_include_flag := 'N';
5953 l_tab_pay_proc_evnts := ben_ext_person.g_pay_proc_evt_tab;
5954
5955 IF l_tab_pay_proc_evnts.COUNT > 0
5956 THEN
5957 IF g_debug
5958 THEN
5959 l_proc_step := 20;
5960 DEBUG(l_proc_name, l_proc_step);
5961 END IF;
5962
5963 IF g_debug
5964 THEN
5965 l_proc_step := 30;
5966 DEBUG(l_proc_name, l_proc_step);
5967 DEBUG('g_event_counter :' || g_event_counter);
5968 DEBUG(
5969 'dated_table_id :'
5970 || l_tab_pay_proc_evnts(g_event_counter).dated_table_id
5971 );
5972 DEBUG(
5973 'datetracked_event :'
5974 || l_tab_pay_proc_evnts(g_event_counter).datetracked_event
5975 );
5976 DEBUG(
5977 'update type: '
5978 || l_tab_pay_proc_evnts(g_event_counter).update_type
5979 );
5980 DEBUG(
5981 'surrogate_key :'
5982 || l_tab_pay_proc_evnts(g_event_counter).surrogate_key
5983 );
5984 DEBUG(
5985 'column_name :'
5986 || l_tab_pay_proc_evnts(g_event_counter).column_name
5987 );
5988 DEBUG(
5989 'effective_date :'
5990 || TO_CHAR(
5991 l_tab_pay_proc_evnts(g_event_counter).effective_date
5992 ,'DD/MON/YYYY'
5993 )
5994 );
5995 DEBUG(
5996 'old_value :'
5997 || l_tab_pay_proc_evnts(g_event_counter).old_value
5998 );
5999 DEBUG(
6000 'new_value :'
6001 || l_tab_pay_proc_evnts(g_event_counter).new_value
6002 );
6003 DEBUG(
6004 'change_values :'
6005 || l_tab_pay_proc_evnts(g_event_counter).change_values
6006 );
6007 DEBUG(
6008 'proration_type :'
6009 || l_tab_pay_proc_evnts(g_event_counter).proration_type
6010 );
6011 DEBUG(
6012 'change_mode :'
6013 || l_tab_pay_proc_evnts(g_event_counter).change_mode
6014 );
6015 DEBUG(
6016 'event_group_id :'
6017 || l_tab_pay_proc_evnts(g_event_counter).event_group_id
6018 );
6019 DEBUG(
6020 'next_evt_start_date: '
6021 || TO_CHAR(
6022 l_tab_pay_proc_evnts(g_event_counter).next_evt_start_date
6023 ,'DD/MON/YYYY'
6024 )
6025 );
6026 DEBUG(
6027 'actual_date: '
6028 || TO_CHAR(
6029 l_tab_pay_proc_evnts(g_event_counter).actual_date
6030 ,'DD/MON/YYYY'
6031 )
6032 );
6033 DEBUG(
6034 'g_prev_pay_proc_evnts.dated_table_id: '
6035 || g_prev_pay_proc_evnts.dated_table_id
6036 );
6037 END IF;
6038
6039 IF g_prev_pay_proc_evnts.dated_table_id IS NOT NULL
6040 THEN
6041 --
6042 IF l_tab_pay_proc_evnts(g_event_counter).dated_table_id <>
6043 g_prev_pay_proc_evnts.dated_table_id
6044 OR l_tab_pay_proc_evnts(g_event_counter).datetracked_event <>
6045 g_prev_pay_proc_evnts.datetracked_event
6046 OR l_tab_pay_proc_evnts(g_event_counter).update_type <>
6047 g_prev_pay_proc_evnts.update_type
6048 OR l_tab_pay_proc_evnts(g_event_counter).surrogate_key <>
6049 g_prev_pay_proc_evnts.surrogate_key
6050 OR l_tab_pay_proc_evnts(g_event_counter).column_name <>
6051 g_prev_pay_proc_evnts.column_name
6052 OR l_tab_pay_proc_evnts(g_event_counter).effective_date <>
6053 g_prev_pay_proc_evnts.effective_date
6054 OR l_tab_pay_proc_evnts(g_event_counter).old_value <>
6055 g_prev_pay_proc_evnts.old_value
6056 OR l_tab_pay_proc_evnts(g_event_counter).new_value <>
6057 g_prev_pay_proc_evnts.new_value
6058 OR l_tab_pay_proc_evnts(g_event_counter).change_values <>
6059 g_prev_pay_proc_evnts.change_values
6060 OR l_tab_pay_proc_evnts(g_event_counter).proration_type <>
6061 g_prev_pay_proc_evnts.proration_type
6062 OR l_tab_pay_proc_evnts(g_event_counter).event_group_id <>
6063 g_prev_pay_proc_evnts.event_group_id
6064 OR l_tab_pay_proc_evnts(g_event_counter).actual_date <>
6065 g_prev_pay_proc_evnts.actual_date
6066 THEN
6067 l_process_flag := 'Y';
6068 ELSE
6069 l_process_flag := 'N';
6070 END IF;
6071 ELSE
6072 l_process_flag := 'Y';
6073 END IF; -- End if of dated table id not null check ...
6074
6075 IF g_debug
6076 THEN
6077 DEBUG('l_process_flag: ' || l_process_flag);
6078 END IF;
6079
6080 g_prev_pay_proc_evnts := l_tab_pay_proc_evnts(g_event_counter);
6081 g_tab_pay_proc_evnts := l_tab_pay_proc_evnts;
6082 -- Check whether we are interested in this event
6083 l_event_group_id :=
6084 l_tab_pay_proc_evnts(g_event_counter).event_group_id;
6085
6086 IF g_tab_event_group.EXISTS(l_event_group_id)
6087 AND l_process_flag = 'Y'
6088 THEN
6089 IF g_debug
6090 THEN
6091 l_proc_step := 35;
6092 DEBUG(l_proc_name, l_proc_step);
6093 END IF;
6094
6095 l_return :=
6096 pqp_gb_psi_functions.include_event(
6097 p_actual_date => l_tab_pay_proc_evnts(g_event_counter).actual_date
6098 ,p_effective_date => l_tab_pay_proc_evnts(g_event_counter).effective_date
6099 );
6100
6101 IF g_debug
6102 THEN
6103 l_proc_step := 36;
6104 DEBUG(l_proc_name, l_proc_step);
6105 DEBUG('l_return: ' || l_return);
6106 END IF;
6107
6108 IF l_return = 'Y'
6109 THEN
6110 -- We are interested in this event
6111 -- We will need pl/sql event qualifiers for assignment status and
6112 -- absence types
6113 -- Check for absence types first
6114 l_dated_table_id :=
6115 l_tab_pay_proc_evnts(g_event_counter).dated_table_id;
6116
6117 --For Bug 7034476: Start
6118 l_column_name :=
6119 l_tab_pay_proc_evnts(g_event_counter).column_name;
6120 --For Bug 7034476: End
6121
6122 l_table_name :=
6123 g_tab_dated_table(l_dated_table_id).table_name;
6124 l_event_group_name :=
6125 g_tab_event_group(l_event_group_id).event_group_name;
6126
6127 IF g_debug
6128 THEN
6129 l_proc_step := 40;
6130 DEBUG(l_proc_name, l_proc_step);
6131 DEBUG('l_event_group_name: ' || l_event_group_name);
6132 DEBUG('l_dated_table_id: ' || l_dated_table_id);
6133 DEBUG('l_table_name: ' || l_table_name);
6134 END IF;
6135
6136 -- PQP_GB_PSI_SER_ABSENCES
6137 -- PQP_GB_PSI_ASSIGNMENT_STATUS
6138 -- PQP_GB_PSI_SER_LEAVER
6139 -- PQP_GB_PSI_SER_PENSIONS
6140 -- PQP_GB_PSI_NEW_HIRE
6141 -- PQP_GB_PSI_NI_NUMBER
6142 -- PQP_GB_PSI_ASSIGNMENT_NUMBER
6143 -- PQP_GB_PSI_SER_NEW_ABSENCES
6144 -- PQP_GB_PSI_EMP_TERMINATIONS
6145 -- PQP_GB_PSI_SER_GAP_TRANSITION
6146
6147 -- Check whether event group relates to Absences
6148
6149 IF l_event_group_name = 'PQP_GB_PSI_SER_ABSENCES'
6150 OR l_event_group_name = 'PQP_GB_PSI_SER_NEW_ABSENCES'
6151 THEN
6152 -- This should be an absence event
6153 -- Evaluate absences
6154
6155 --For Bug 7034476: Start
6156 IF l_table_name = 'PER_ABSENCE_ATTENDANCES'
6157 THEN
6158 --For Bug 7034476: End
6159
6160 l_absence_attendance_id :=
6161 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
6162
6163 IF g_debug
6164 THEN
6165 l_proc_step := 50;
6166 DEBUG(l_proc_name, l_proc_step);
6167 DEBUG('l_absence_attendance_id: ' || l_absence_attendance_id);
6168 END IF;
6169
6170 l_include_flag := eval_absence_event(
6171 p_assignment_id => p_assignment_id
6172 ,p_absence_attendance_id => l_absence_attendance_id
6173 ,p_event_group_name => l_event_group_name
6174 ,p_absence_type_id => l_absence_type_id
6175 ,p_start_reason => l_start_reason
6176 ,p_ser_start_date => l_ser_start_date
6177 ,p_event_source => l_event_source
6178 );
6179
6180 --For Bug 7034476: Start
6181 ELSIF l_table_name = 'PQP_GAP_DURATION_SUMMARY'
6182 THEN
6183 -- This should be a sickness/maternity transition
6184 l_gap_duration_summary_id :=
6185 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
6186
6187 IF g_debug
6188 THEN
6189 l_proc_step := 60;
6190 DEBUG(l_proc_name, l_proc_step);
6191 DEBUG('l_gap_duration_summary_id: '
6192 || l_gap_duration_summary_id);
6193 END IF;
6194
6195 l_include_flag := eval_gap_transition_event
6196 (p_assignment_id => p_assignment_id
6197 ,p_gap_duration_summary_id => l_gap_duration_summary_id
6198 ,p_absence_type_id => l_absence_type_id
6199 ,p_start_reason => l_start_reason
6200 ,p_ser_start_date => l_ser_start_date
6201 ,p_event_source => l_event_source
6202 );
6203 END IF; --End of table name chk
6204 /*
6205 ELSIF l_event_group_name = 'PQP_GB_PSI_SER_GAP_TRANSITION'
6206 THEN
6207 -- This should be a sickness/maternity transition
6208 l_gap_duration_summary_id :=
6209 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
6210
6211 IF g_debug
6212 THEN
6213 l_proc_step := 60;
6214 DEBUG(l_proc_name, l_proc_step);
6215 DEBUG(
6216 'l_gap_duration_summary_id: '
6217 || l_gap_duration_summary_id
6218 );
6219 END IF;
6220
6221 l_include_flag :=
6222 eval_gap_transition_event(
6223 p_assignment_id => p_assignment_id
6224 ,p_gap_duration_summary_id => l_gap_duration_summary_id
6225 ,p_absence_type_id => l_absence_type_id
6226 ,p_start_reason => l_start_reason
6227 ,p_ser_start_date => l_ser_start_date
6228 ,p_event_source => l_event_source
6229 );
6230 */
6231 --For Bug 7034476: End
6232
6233
6234 --For Bug 5998108
6235 ELSIF ((l_event_group_name = 'PQP_GB_PSI_NEW_HIRE')OR(l_event_group_name = 'PQP_GB_PSI_ASG_CATEGORY'))
6236 THEN
6237 -- This is a new hire event (includes rehires)
6238 -- Evaluate new joiners
6239 -- We are only interested in primary assignments
6240 l_assignment_id :=
6241 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
6242
6243 IF g_debug
6244 THEN
6245 l_proc_step := 60;
6246 DEBUG(l_proc_name, l_proc_step);
6247 DEBUG('l_assignment_id: ' || l_assignment_id);
6248 END IF;
6249
6250 IF l_assignment_id = p_assignment_id
6251 THEN
6252 --For bug 7013325:Start
6253 --Exclude case from here
6254 IF l_event_group_name = 'PQP_GB_PSI_ASG_CATEGORY'
6255 THEN
6256
6257 DEBUG('l_tab_pay_proc_evnts(g_event_counter).effective_date = '||l_tab_pay_proc_evnts(g_event_counter).effective_date);
6258
6259 For rec_get_asg_chg_dt_ele in cur_get_asg_chg_dt_ele(l_tab_pay_proc_evnts(g_event_counter).effective_date)
6260 Loop
6261 IF g_tab_pen_ele_ids.EXISTS(rec_get_asg_chg_dt_ele.element_type_id)
6262 THEN
6263
6264 DEBUG('rec_get_asg_chg_dt_ele.element_type_id = '||rec_get_asg_chg_dt_ele.element_type_id);
6265
6266 OPEN cur_get_ele_entry_id(rec_get_asg_chg_dt_ele.element_entry_id, l_tab_pay_proc_evnts(g_event_counter).effective_date);
6267 FETCH cur_get_ele_entry_id INTO l_element_entry_id;
6268 CLOSE cur_get_ele_entry_id;
6269
6270 IF l_element_entry_id IS NULL
6271 THEN
6272 l_flag := 'Y';
6273 DEBUG('l_flag = '||l_flag);
6274 Exit;
6275 END IF;
6276 End IF;
6277 End Loop;
6278 END IF;--End of if event group is ASG_CATEGORY
6279
6280 IF l_flag = 'N'
6281 THEN
6282 l_include_flag := 'Y';
6283 l_ser_start_date :=
6284 l_tab_pay_proc_evnts(g_event_counter).effective_date;
6285 l_start_reason := 'N'; -- New Joiner
6286 l_event_source := 'SER';
6287 END IF;
6288
6289 /* l_include_flag := 'Y';
6290 l_ser_start_date :=
6291 l_tab_pay_proc_evnts(g_event_counter).effective_date;
6292 l_start_reason := 'N'; -- New Joiner
6293 l_event_source := 'SER'; */
6294 --For bug 7013325:End
6295
6296 END IF; -- Primary assignment check ...
6297 ELSIF l_event_group_name = 'PQP_GB_PSI_SER_LEAVER'
6298 THEN
6299 -- This is a leaver event
6300 -- Ensure that the assignment status represents
6301 -- Termination as of the event date
6302 IF l_table_name = 'PER_PERIODS_OF_SERVICE'
6303 THEN
6304 --115.21 5945283
6305 /* l_period_of_service_id :=
6306 fnd_number.canonical_to_number(
6307 l_tab_pay_proc_evnts(g_event_counter).surrogate_key
6308 );*/
6309
6310 IF g_debug
6311 THEN
6312 l_proc_step := 70;
6313 DEBUG(l_proc_name, l_proc_step);
6314 END IF;
6315
6316 OPEN csr_get_ser_details;
6317 FETCH csr_get_ser_details INTO l_rec_ser_details;
6318 CLOSE csr_get_ser_details;
6319
6320 IF l_rec_ser_details.actual_termination_date IS NOT NULL
6321 AND l_rec_ser_details.actual_termination_date <=
6322 pqp_gb_psi_functions.g_effective_end_date
6323 THEN
6324 IF g_debug
6325 THEN
6326 DEBUG(
6327 'l_rec_ser_details.actual_termination_date'
6328 || TO_CHAR(
6329 l_rec_ser_details.actual_termination_date
6330 ,'DD/MON/YYYY'
6331 )
6332 );
6333 END IF;
6334
6335 get_asg_details(
6336 p_assignment_id => p_assignment_id
6337 ,p_effective_date => l_rec_ser_details.actual_termination_date
6338 + 1
6339 ,p_rec_asg_details => l_rec_asg_details
6340 );
6341 l_asg_status_type_id :=
6342 l_rec_asg_details.assignment_status_type_id;
6343
6344 -- We are only interested if the status is termination
6345 IF l_asg_status_type_id = g_terminate_asg_sts_id
6346 THEN
6347 l_include_flag := 'Y';
6348 l_ser_start_date :=
6349 l_rec_ser_details.actual_termination_date;
6350 l_start_reason := 'ZZ'; -- Leaver
6351 l_event_source := 'ASG';
6352
6353 -- Get the penserver leaving reason code
6354 -- for this termination event
6355 IF l_rec_ser_details.leaving_reason IS NOT NULL
6356 THEN
6357
6358 IF g_debug
6359 THEN
6360 l_proc_step := 75;
6361 DEBUG(l_proc_name, l_proc_step);
6362 DEBUG('l_rec_ser_details.leaving_reason: '
6363 || l_rec_ser_details.leaving_reason
6364 );
6365 END IF;
6366
6367 l_index := NULL;
6368 l_return :=
6369 chk_lvrsn_in_collection
6370 (p_leave_reason => l_rec_ser_details.leaving_reason
6371 ,p_index => l_index
6372 );
6373 IF l_return = 'Y' THEN
6374 IF g_debug
6375 THEN
6376 DEBUG('g_tab_lvrsn_map_cv(l_index).pcv_information2: '
6377 || g_tab_lvrsn_map_cv(l_index).pcv_information2
6378 );
6379 END IF;
6380 g_leaving_reason := g_tab_lvrsn_map_cv(l_index).pcv_information2;
6381 END IF; -- End if of l_index is not null check ...
6382 ELSE
6383 -- Raise data error
6384 IF g_debug
6385 THEN
6386 DEBUG('Raise Data Error: Leaving Reason Missing');
6387 END IF;
6388
6389 l_value :=
6390 pqp_gb_psi_functions.raise_extract_error(
6391 p_error_number => 94479
6392 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
6393 ,p_token1 => 'Leaving Reason'
6394 );
6395
6396 END IF; -- End if of leaving reason is not null check ...
6397 END IF; -- Check for assignment status is termination
6398 END IF; -- Check whether actual termination date is not null ...
6399 ELSIF l_table_name = 'PER_ALL_ASSIGNMENTS_F'
6400 THEN
6401 -- This is an assignment status change
6402
6403 --For Bug 7034476:Start
6404 IF l_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
6405 THEN
6406 --For Bug 7034476: End
6407
6408 IF g_debug
6409 THEN
6410 l_proc_step := 76;
6411 DEBUG(l_proc_name, l_proc_step);
6412 DEBUG('g_assignment_dtl.assignment_status_type_id: '
6413 || g_assignment_dtl.assignment_status_type_id);
6414 END IF;
6415
6416 IF g_terminate_asg_sts_id =
6417 g_assignment_dtl.assignment_status_type_id
6418 THEN -- confirmed termination
6419
6420 --Added for bug 7608779: Start
6421 --this is to make sure that termination is reported only
6422 --once.
6423 OPEN csr_get_ser_details;
6424 FETCH csr_get_ser_details INTO l_rec_ser_details;
6425 CLOSE csr_get_ser_details;
6426
6427 DEBUG('l_tab_pay_proc_evnts(g_event_counter).effective_date: '
6428 ||l_tab_pay_proc_evnts(g_event_counter).effective_date);
6429 DEBUG('l_rec_ser_details.actual_termination_date: '
6430 ||l_rec_ser_details.actual_termination_date);
6431
6432 IF (l_tab_pay_proc_evnts(g_event_counter).effective_date - 1)
6433 = l_rec_ser_details.actual_termination_date
6434 THEN
6435 l_include_flag := 'Y';
6436 l_ser_start_date :=
6437 l_tab_pay_proc_evnts(g_event_counter).effective_date - 1;
6438 l_start_reason := 'ZZ'; -- Leaver
6439 l_event_source := 'ASG';
6440 END IF;
6441
6442 --For Bug 7034476:Start
6443 ELSE
6444 IF g_debug
6445 THEN
6446 l_proc_step := 90;
6447 DEBUG(l_proc_name, l_proc_step);
6448 END IF;
6449
6450 l_curr_status_type_id :=
6451 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).new_value);
6452 l_prev_status_type_id :=
6453 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).old_value);
6454 l_include_flag := eval_asg_status_event(
6455 p_assignment_id => p_assignment_id
6456 ,p_curr_status_type_id => l_curr_status_type_id
6457 ,p_prev_status_type_id => l_prev_status_type_id
6458 ,p_start_reason => l_start_reason
6459 ,p_event_source => l_event_source
6460 );
6461
6462 IF l_include_flag = 'Y'
6463 THEN
6464 l_ser_start_date :=
6465 l_tab_pay_proc_evnts(g_event_counter).effective_date;
6466 END IF;
6467 --For Bug 7034476: End
6468
6469 END IF; -- termination status check ...
6470
6471 --For Bug 7034476:Start
6472 ELSE
6473 --115.21 5945283
6474 OPEN csr_get_ser_details;
6475 FETCH csr_get_ser_details INTO l_rec_ser_details;
6476 CLOSE csr_get_ser_details;
6477
6478 IF g_debug
6479 THEN
6480 l_proc_step := 80;
6481 DEBUG(l_proc_name, l_proc_step);
6482 DEBUG('Final Process Date: '
6483 ||l_rec_ser_details.final_process_date);
6484 DEBUG('Actual Termination Date: '
6485 ||l_rec_ser_details.actual_termination_date);
6486 END IF;
6487
6488 IF l_rec_ser_details.final_process_date=
6489 l_rec_ser_details.actual_termination_date
6490 THEN
6491 --report leaver event only if fpd is same as atd
6492 IF pqp_gb_psi_functions.chk_is_employee_a_leaver(
6493 p_assignment_id => p_assignment_id
6494 ,p_effective_date => g_effective_date
6495 ,p_leaver_date => l_leaver_date
6496 ) = 'Y'
6497 THEN
6498 l_include_flag := 'Y';
6499 l_ser_start_date := l_leaver_date;
6500 l_start_reason := 'ZZ'; -- Leaver
6501 l_event_source := 'ASG';
6502 END IF; -- employee a leaver check ...
6503 END IF;---report leaver event only if fpd is same as atd
6504 END IF; --end of if l_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
6505 --For Bug 7034476: End
6506
6507 END IF; -- End if of table name is periods of service ...
6508
6509 --For Bug 7034476: Removed code for event group
6510 --PQP_GB_PSI_EMP_TERMINATIONS and PQP_GB_PSI_ASSIGNMENT_STATUS
6511 /*
6512 ELSIF l_event_group_name = 'PQP_GB_PSI_EMP_TERMINATIONS'
6513 THEN -- Terminations
6514
6515 --115.21 5945283
6516 OPEN csr_get_ser_details;
6517 FETCH csr_get_ser_details INTO l_rec_ser_details;
6518 CLOSE csr_get_ser_details;
6519
6520 IF g_debug
6521 THEN
6522 l_proc_step := 80;
6523 DEBUG(l_proc_name, l_proc_step);
6524 DEBUG('Final Process Date: '
6525 ||l_rec_ser_details.final_process_date);
6526 DEBUG('Actual Termination Date: '
6527 ||l_rec_ser_details.actual_termination_date);
6528 END IF;
6529
6530
6531 IF l_rec_ser_details.final_process_date=
6532 l_rec_ser_details.actual_termination_date THEN
6533 --report leaver event only if fpd is same as atd
6534 IF pqp_gb_psi_functions.chk_is_employee_a_leaver(
6535 p_assignment_id => p_assignment_id
6536 ,p_effective_date => g_effective_date
6537 ,p_leaver_date => l_leaver_date
6538 ) = 'Y'
6539 THEN
6540 l_include_flag := 'Y';
6541 l_ser_start_date := l_leaver_date;
6542 l_start_reason := 'ZZ'; -- Leaver
6543 l_event_source := 'ASG';
6544 END IF; -- employee a leaver check ...
6545 END IF;---report leaver event only if fpd is same as atd
6546
6547 ELSIF l_event_group_name = 'PQP_GB_PSI_ASSIGNMENT_STATUS'
6548 THEN -- Assignment status event group
6549 IF g_debug
6550 THEN
6551 l_proc_step := 90;
6552 DEBUG(l_proc_name, l_proc_step);
6553 END IF;
6554
6555 l_curr_status_type_id :=
6556 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).new_value);
6557 l_prev_status_type_id :=
6558 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).old_value);
6559 l_include_flag :=
6560 eval_asg_status_event(
6561 p_assignment_id => p_assignment_id
6562 ,p_curr_status_type_id => l_curr_status_type_id
6563 ,p_prev_status_type_id => l_prev_status_type_id
6564 ,p_start_reason => l_start_reason
6565 ,p_event_source => l_event_source
6566 );
6567
6568 IF l_include_flag = 'Y'
6569 THEN
6570 l_ser_start_date :=
6571 l_tab_pay_proc_evnts(g_event_counter).effective_date;
6572 END IF;
6573 */
6574
6575 ELSIF l_event_group_name = 'PQP_GB_PSI_SER_PENSIONS'
6576 THEN
6577 IF g_debug
6578 THEN
6579 l_proc_step := 100;
6580 DEBUG(l_proc_name, l_proc_step);
6581 END IF;
6582
6583 l_surrogate_key :=
6584 fnd_number.canonical_to_number(l_tab_pay_proc_evnts(g_event_counter).surrogate_key);
6585 l_include_flag :=
6586 eval_pension_event(
6587 p_assignment_id => p_assignment_id
6588 ,p_table_name => l_table_name
6589 ,p_surrogate_key => l_surrogate_key
6590 ,p_ser_start_date => l_ser_start_date
6591 ,p_start_reason => l_start_reason
6592 ,p_event_source => l_event_source
6593 ,p_pension_category => l_pension_category
6594 ,p_partnership_scheme => l_partnership_scheme
6595 );
6596 END IF; -- Event group name check ...
6597 END IF; -- End if of l_return = 'Y' check ...
6598 END IF; -- event group exists check ...
6599 END IF; -- Event collection count > 0 check ...
6600
6601 IF l_include_flag = 'Y'
6602 THEN
6603 IF g_debug
6604 THEN
6605 l_proc_step := 110;
6606 DEBUG(l_proc_name, l_proc_step);
6607 END IF;
6608
6609 -- Get assignment details as of the event effective date
6610 IF g_assignment_dtl.assignment_id IS NULL
6611 THEN
6612 get_asg_details(
6613 p_assignment_id => p_assignment_id
6614 ,p_effective_date => l_ser_start_date
6615 ,p_rec_asg_details => l_rec_asg_details
6616 );
6617 ELSE
6618 l_rec_asg_details.person_id :=
6619 g_assignment_dtl.person_id;
6620 l_rec_asg_details.effective_start_date :=
6621 g_assignment_dtl.effective_start_date;
6622 l_rec_asg_details.effective_end_date :=
6623 g_assignment_dtl.effective_end_date;
6624 l_rec_asg_details.assignment_number :=
6625 g_assignment_dtl.assignment_number;
6626 l_rec_asg_details.primary_flag :=
6627 g_assignment_dtl.primary_flag;
6628 l_rec_asg_details.normal_hours :=
6629 g_assignment_dtl.normal_hours;
6630 l_rec_asg_details.assignment_status_type_id :=
6631 g_assignment_dtl.assignment_status_type_id;
6632 l_rec_asg_details.employment_category :=
6633 g_assignment_dtl.employment_category;
6634 END IF; -- assignment dtl global record is null check ...
6635
6636 IF g_debug
6637 THEN
6638 l_proc_step := 120;
6639 DEBUG(l_proc_name, l_proc_step);
6640 DEBUG('Person ID: ' || l_rec_asg_details.person_id);
6641 DEBUG(
6642 'Effective Start Date: '
6643 || TO_CHAR(l_rec_asg_details.effective_start_date
6644 ,'DD/MON/YYYY')
6645 );
6646 DEBUG(
6647 'Effective End Date: '
6648 || TO_CHAR(l_rec_asg_details.effective_end_date, 'DD/MON/YYYY')
6649 );
6650 DEBUG('Assignment Number: ' || l_rec_asg_details.assignment_number);
6651 DEBUG('Primary Flag: ' || l_rec_asg_details.primary_flag);
6652 DEBUG('Normal Hours: ' || l_rec_asg_details.normal_hours);
6653 DEBUG(
6654 'Assignment Status Type ID: '
6655 || l_rec_asg_details.assignment_status_type_id
6656 );
6657 DEBUG(
6658 'Assignment Category: '
6659 || l_rec_asg_details.employment_category
6660 );
6661 END IF;
6662
6663 -- Assign latest start date as the service date to start with
6664 l_latest_start_date :=
6665 get_per_latest_start_date(
6666 p_person_id => l_rec_asg_details.person_id
6667 ,p_effective_date => l_rec_asg_details.effective_start_date
6668 );
6669
6670 IF g_debug
6671 THEN
6672 l_proc_step := 130;
6673 DEBUG(l_proc_name, l_proc_step);
6674 DEBUG(
6675 'l_latest_start_date: '
6676 || TO_CHAR(l_latest_start_date, 'DD/MON/YYYY')
6677 );
6678 END IF;
6679
6680 -- Get the earliest assignment effective start date when this
6681 -- person became eligible to be reported
6682 OPEN csr_get_asg_start_date(l_rec_asg_details.employment_category);
6683 FETCH csr_get_asg_start_date INTO l_asg_start_date;
6684 CLOSE csr_get_asg_start_date;
6685
6686 IF l_latest_start_date < l_asg_start_date
6687 THEN
6688 l_latest_start_date := l_asg_start_date;
6689 END IF;
6690
6691 IF g_debug
6692 THEN
6693 l_proc_step := 140;
6694 DEBUG(l_proc_name, l_proc_step);
6695 DEBUG('l_start_reason: ' || l_start_reason);
6696 DEBUG('l_event_source: ' || l_event_source);
6697 DEBUG(
6698 'l_latest_start_date: '
6699 || TO_CHAR(l_latest_start_date, 'DD/MON/YYYY')
6700 );
6701 DEBUG(
6702 'l_asg_start_date: '
6703 || TO_CHAR(l_asg_start_date, 'DD/MON/YYYY')
6704 );
6705 DEBUG(
6706 'l_ser_start_date: '
6707 || TO_CHAR(l_ser_start_date, 'DD/MON/YYYY')
6708 );
6709 DEBUG('l_event_source: ' || l_event_source);
6710 END IF;
6711
6712 IF l_pension_category IS NULL
6713 THEN
6714 l_pension_category :=
6715 get_pen_scheme_memb(
6716 p_assignment_id => p_assignment_id
6717 ,p_effective_date => l_ser_start_date
6718 ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv
6719 ,p_rec_ele_ent_details => l_rec_ele_ent_details
6720 ,p_partnership_scheme => l_partnership_scheme
6721 );
6722 END IF;
6723
6724 IF g_debug
6725 THEN
6726 l_proc_step := 150;
6727 DEBUG('l_pension_category: ' || l_pension_category);
6728 DEBUG('l_partnership_scheme: '||l_partnership_scheme);
6729 END IF;
6730
6731 l_psi_emp_type :=
6732 get_psi_emp_type(p_employment_category => l_rec_asg_details.employment_category);
6733
6734 IF g_debug
6735 THEN
6736 l_proc_step := 160;
6737 DEBUG(l_proc_name, l_proc_step);
6738 DEBUG('l_psi_emp_type: ' || l_psi_emp_type);
6739 END IF;
6740
6741 g_ser_start_date := l_ser_start_date;
6742
6743 IF l_start_reason = 'ZZ' AND
6744 g_leaving_reason IS NULL
6745 THEN
6746 -- Get the leaving reason code
6747 IF g_debug
6748 THEN
6749 l_proc_step := 165;
6750 DEBUG(l_proc_name, l_proc_step);
6751 END IF;
6752 OPEN csr_get_leaving_reason(l_rec_asg_details.person_id
6753 ,l_ser_start_date);
6754 FETCH csr_get_leaving_reason INTO l_rec_leaving_reason;
6755 CLOSE csr_get_leaving_reason;
6756
6757 -- Get the penserver leaving reason code
6758 -- for this termination event
6759 IF l_rec_leaving_reason.leaving_reason IS NOT NULL
6760 THEN
6761
6762 IF g_debug
6763 THEN
6764 l_proc_step := 166;
6765 DEBUG(l_proc_name, l_proc_step);
6766 DEBUG('l_rec_leaving_reason.leaving_reason: '
6767 || l_rec_leaving_reason.leaving_reason
6768 );
6769 END IF;
6770 l_index := NULL;
6771 l_return :=
6772 chk_lvrsn_in_collection
6773 (p_leave_reason => l_rec_leaving_reason.leaving_reason
6774 ,p_index => l_index
6775 );
6776 IF l_return = 'Y' THEN
6777 IF g_debug
6778 THEN
6779 DEBUG('g_tab_lvrsn_map_cv(l_index).pcv_information2: '
6780 || g_tab_lvrsn_map_cv(l_index).pcv_information2
6781 );
6782 END IF;
6783 g_leaving_reason := g_tab_lvrsn_map_cv(l_index).pcv_information2;
6784 END IF; -- End if of l_index is not null check ...
6785 ELSE
6786 -- Raise data error
6787 IF g_debug
6788 THEN
6789 DEBUG('Raise Data Error: Leaving Reason Missing');
6790 END IF;
6791 l_value :=
6792 pqp_gb_psi_functions.raise_extract_error(
6793 p_error_number => 94479
6794 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
6795 ,p_token1 => 'Leaving Reason'
6796 );
6797
6798 END IF; -- End if of leaving reason is not null check ...
6799 END IF; -- End if of l_start_reason = 'ZZ' check ...
6800
6801 IF l_start_reason = 'ZZ'
6802 AND NVL(l_asg_status_type_id, hr_api.g_number) <>
6803 g_terminate_asg_sts_id
6804 THEN
6805 l_asg_status_type_id := g_terminate_asg_sts_id;
6806 END IF;
6807
6808 -- Enhancement 5040543
6809 -- Add a warning message when pension category is null
6810 IF l_pension_category IS NULL
6811 THEN
6812
6813 IF g_debug
6814 THEN
6815 l_proc_step := 165;
6816 DEBUG(l_proc_name, l_proc_step);
6817 DEBUG('Not a member of CS scheme');
6818 END IF;
6819
6820 l_value :=
6821 pqp_gb_psi_functions.raise_extract_warning(
6822 p_error_number => 93775
6823 ,p_error_text => 'BEN_93775_EXT_PSI_NOT_PEN_MEMB'
6824 ,p_token1 => p_assignment_id
6825 ,p_token2 => fnd_date.date_to_displaydt(g_effective_date)
6826 );
6827 END IF; -- End if of pension category is null check ...
6828
6829 get_service_history_code(
6830 p_event_desc => l_start_reason
6831 ,p_pension_scheme => l_pension_category
6832 ,p_employment_type => l_psi_emp_type
6833 ,p_event_source => l_event_source
6834 ,p_absence_type => l_absence_type_id
6835 ,p_asg_status => NVL(
6836 l_asg_status_type_id
6837 ,l_rec_asg_details.assignment_status_type_id
6838 )
6839 ,p_partnership_scheme => l_partnership_scheme --115.14
6840 ,p_start_reason => g_start_reason
6841 ,p_scheme_category => g_scheme_category
6842 ,p_scheme_status => g_scheme_status
6843 );
6844
6845 -- Check whether the person has opted out of the pension scheme
6846 -- on the joining day (hired day)
6847 IF l_start_reason = 'OO' AND l_asg_start_date = l_ser_start_date
6848 THEN
6849 g_start_reason := 'N';
6850 END IF;
6851
6852 IF g_debug
6853 THEN
6854 l_proc_step := 170;
6855 DEBUG('l_asg_status_type_id: ' || l_asg_status_type_id);
6856 DEBUG('g_start_reason: ' || g_start_reason);
6857 DEBUG('g_scheme_category: ' || g_scheme_category);
6858 DEBUG('g_scheme_status: ' || g_scheme_status);
6859 DEBUG(
6860 'g_ser_start_date: '
6861 || TO_CHAR(g_ser_start_date, 'DD/MON/YYYY')
6862 );
6863 END IF;
6864 END IF; -- End if of l_include_flag = 'Y' check ...
6865
6866 -- IF l_event_group_name = 'PQP_GB_PSI_NI_NUMBER'
6867 -- OR l_event_group_name = 'PQP_GB_PSI_ASSIGNMENT_NUMBER'
6868 -- THEN
6869 -- IF g_debug
6870 -- THEN
6871 -- l_proc_step := 180;
6872 -- DEBUG(l_proc_name, l_proc_step);
6873 -- END IF;
6874 --
6875 -- -- event qualifies
6876 -- l_include_flag := 'Y';
6877 -- -- Get the service history as of the event date
6878 -- -- call cutover function to return this date
6879 -- -- g_effective_date will be set to event date
6880 -- get_asg_ser_cutover_data(p_assignment_id => p_assignment_id);
6881 -- g_ser_start_date := g_effective_date;
6882 -- END IF; -- End if of event group name check ...
6883
6884 IF g_debug
6885 THEN
6886 l_proc_step := 190;
6887 DEBUG(l_proc_name, l_proc_step);
6888 DEBUG('l_include_flag: ' || l_include_flag);
6889 DEBUG('g_ser_start_date: '
6890 || TO_CHAR(g_ser_start_date, 'DD/MON/YYYY'));
6891 debug_exit(l_proc_name);
6892 END IF;
6893
6894 RETURN l_include_flag;
6895 EXCEPTION
6896 WHEN OTHERS
6897 THEN
6898 clear_cache;
6899
6900 IF SQLCODE <> hr_utility.hr_error_number
6901 THEN
6902 debug_others(l_proc_name, l_proc_step);
6903
6904 IF g_debug
6905 THEN
6906 DEBUG('Leaving: ' || l_proc_name, -999);
6907 END IF;
6908
6909 fnd_message.raise_error;
6910 ELSE
6911 RAISE;
6912 END IF;
6913 END chk_ser_periodic_criteria;
6914
6915 -- This function is used to evaluate assignments that
6916 -- qualify for penserver service history interface
6917 -- ----------------------------------------------------------------------------
6918 -- |---------------------< chk_service_history_criteria ---------------------|
6919 -- ----------------------------------------------------------------------------
6920 FUNCTION chk_service_history_criteria(
6921 p_business_group_id IN NUMBER
6922 ,p_effective_date IN DATE
6923 ,p_assignment_id IN NUMBER
6924 )
6925 RETURN VARCHAR2
6926 IS
6927 --
6928 l_proc_name VARCHAR2(80)
6929 := g_proc_name || 'chk_service_history_criteria';
6930 l_proc_step PLS_INTEGER;
6931 l_include_flag VARCHAR2(10);
6932 l_debug VARCHAR2(10);
6933 i NUMBER;
6934 l_pension_category pqp_configuration_values.pcv_information1%TYPE;
6935 --
6936 BEGIN
6937 --
6938
6939 IF g_business_group_id IS NULL
6940 THEN
6941 -- Always clear cache before proceeding to set globals
6942 clear_cache;
6943 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
6944 -- -- set g_debug based on process definition configuration
6945 -- IF g_tab_prs_dfn_cv.COUNT = 0
6946 -- THEN
6947 -- fetch_process_defn_cv(p_business_group_id => p_business_group_id);
6948 -- i := g_tab_prs_dfn_cv.FIRST;
6949 --
6950 -- WHILE i IS NOT NULL
6951 -- LOOP
6952 -- l_debug := g_tab_prs_dfn_cv(i).pcv_information1;
6953 -- i := g_tab_prs_dfn_cv.NEXT(i);
6954 -- END LOOP;
6955 --
6956 -- IF l_debug = 'Y'
6957 -- THEN
6958 -- g_debug := TRUE;
6959 -- END IF;
6960 -- END IF; -- End if of prs dfn collection count is zero check ...
6961 END IF; -- End if of g_business_group_id is NULL check ...
6962
6963 IF g_debug
6964 THEN
6965 l_proc_step := 10;
6966 debug_enter(l_proc_name);
6967 DEBUG('p_business_group_id: ' || p_business_group_id);
6968 DEBUG('p_effective_date: '
6969 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
6970 DEBUG('p_assignment_id: ' || p_assignment_id);
6971 END IF;
6972
6973 l_include_flag := 'N';
6974
6975 IF g_business_group_id IS NULL
6976 THEN
6977 -- Call clear cache function to clear cached variables
6978 IF g_debug
6979 THEN
6980 DEBUG('g_business_group_id: ' || g_business_group_id);
6981 l_proc_step := 20;
6982 DEBUG(l_proc_name, l_proc_step);
6983 END IF;
6984
6985 -- set shared globals
6986 pqp_gb_psi_functions.set_shared_globals(
6987 p_business_group_id => p_business_group_id
6988 ,p_paypoint => g_paypoint
6989 ,p_cutover_date => g_cutover_date
6990 ,p_ext_dfn_id => g_ext_dfn_id
6991 );
6992
6993 IF g_debug
6994 THEN
6995 l_proc_step := 30;
6996 DEBUG(l_proc_name, l_proc_step);
6997 DEBUG('g_paypoint: ' || g_paypoint);
6998 DEBUG('g_cutover_date: '
6999 || TO_CHAR(g_cutover_date, 'DD/MON/YYYY'));
7000 DEBUG('g_ext_dfn_id: ' || g_ext_dfn_id);
7001 END IF;
7002
7003 -- set extract global variables
7004 set_service_history_globals(
7005 p_business_group_id => p_business_group_id
7006 ,p_effective_date => p_effective_date
7007 );
7008
7009 IF g_debug
7010 THEN
7011 l_proc_step := 40;
7012 DEBUG(l_proc_name, l_proc_step);
7013 END IF;
7014
7015 -- Raise Extract Exceptions
7016 pqp_gb_psi_functions.raise_extract_exceptions('S');
7017 END IF; -- End if of business group id is null check ...
7018
7019 IF g_extract_type = 'PERIODIC'
7020 THEN
7021 g_effective_date := p_effective_date;
7022
7023 IF g_debug
7024 THEN
7025 DEBUG(
7026 'g_effective_date: '
7027 || TO_CHAR(g_effective_date, 'DD/MON/YYYY')
7028 );
7029 END IF;
7030 END IF;
7031
7032 g_ser_start_date := NULL;
7033 g_start_reason := NULL;
7034 g_scheme_category := NULL;
7035 g_scheme_status := NULL;
7036 g_leaving_reason := NULL;
7037
7038 -- Check penserver basic criteria
7039 IF g_debug
7040 THEN
7041 l_proc_step := 50;
7042 DEBUG(l_proc_name, l_proc_step);
7043 END IF;
7044
7045 l_include_flag :=
7046 pqp_gb_psi_functions.chk_penserver_basic_criteria(
7047 p_business_group_id => g_business_group_id
7048 ,p_effective_date => g_effective_date
7049 ,p_assignment_id => p_assignment_id
7050 ,p_person_dtl => g_person_dtl
7051 ,p_assignment_dtl => g_assignment_dtl
7052 );
7053
7054 -- Initialize counter only for a different person (tested for rehires)
7055 IF NVL(g_person_id, hr_api.g_number) <> g_assignment_dtl.person_id
7056 THEN
7057 clear_per_cache;
7058 g_person_id := g_assignment_dtl.person_id;
7059 -- ELSE
7060 -- g_event_counter :=
7061 -- ben_ext_person.g_pay_proc_evt_tab.NEXT(g_event_counter);
7062 END IF;
7063
7064 IF g_debug
7065 THEN
7066 l_proc_step := 60;
7067 DEBUG(l_proc_name, l_proc_step);
7068 DEBUG('l_include_flag: ' || l_include_flag);
7069 DEBUG('g_extract_type: ' || g_extract_type);
7070 END IF;
7071
7072 IF l_include_flag = 'Y'
7073 THEN
7074 -- Check basic criteria
7075 IF g_extract_type = 'CUTOVER'
7076 THEN
7077 IF g_debug
7078 THEN
7079 l_proc_step := 70;
7080 DEBUG(l_proc_name, l_proc_step);
7081 END IF;
7082
7083 get_asg_ser_cutover_data(p_assignment_id => p_assignment_id);
7084 -- return assignment qualifies
7085 l_include_flag := 'Y';
7086 ELSIF g_extract_type = 'PERIODIC'
7087 THEN
7088 -- Set counter index to pay evt index
7089 g_event_counter := ben_ext_person.g_chg_pay_evt_index;
7090
7091 IF g_debug
7092 THEN
7093 l_proc_step := 80;
7094 DEBUG(l_proc_name, l_proc_step);
7095 DEBUG('g_event_counter: ' || g_event_counter);
7096 END IF;
7097
7098 l_include_flag :=
7099 chk_ser_periodic_criteria(p_assignment_id => p_assignment_id);
7100 -- get_asg_ser_periodic_data (p_assignment_id => p_assignment_id);
7101 -- Call process retro event for the last counter
7102 -- IF g_event_counter = ben_ext_person.g_pay_proc_evt_tab.LAST
7103 -- THEN
7104 -- IF g_debug
7105 -- THEN
7106 -- l_proc_step := 90;
7107 -- DEBUG(l_proc_name, l_proc_step);
7108 -- DEBUG('Last Counter: ' || ben_ext_person.g_pay_proc_evt_tab.LAST);
7109 -- DEBUG('g_event_counter: ' || g_event_counter);
7110 -- END IF;
7111 pqp_gb_psi_functions.process_retro_event;
7112 -- END IF; -- End if of event counter is last check ...
7113 END IF; -- End if of g_extract_type = 'CUTOVER' check ...
7114 END IF; -- End if of l_include_flag = Y check ...
7115
7116 IF g_debug
7117 THEN
7118 l_proc_step := 100;
7119 DEBUG('l_include_flag: ' || l_include_flag);
7120 debug_exit(l_proc_name);
7121 END IF;
7122
7123 RETURN l_include_flag;
7124 EXCEPTION
7125 WHEN OTHERS
7126 THEN
7127 clear_cache;
7128
7129 IF SQLCODE <> hr_utility.hr_error_number
7130 THEN
7131 debug_others(l_proc_name, l_proc_step);
7132
7133 IF g_debug
7134 THEN
7135 DEBUG('Leaving: ' || l_proc_name, -999);
7136 END IF;
7137
7138 fnd_message.raise_error;
7139 ELSE
7140 RAISE;
7141 END IF;
7142 END chk_service_history_criteria;
7143
7144 -- This function is used to get service history data
7145 -- for an assignment
7146 -- ----------------------------------------------------------------------------
7147 -- |---------------------< get_service_history_data >-------------------------|
7148 -- ----------------------------------------------------------------------------
7149 FUNCTION get_service_history_data(
7150 p_business_group_id IN NUMBER
7151 ,p_effective_date IN DATE
7152 ,p_assignment_id IN NUMBER
7153 ,p_rule_parameter IN VARCHAR2
7154 )
7155 RETURN VARCHAR2
7156 IS
7157 --
7158 l_proc_name VARCHAR2(80)
7159 := g_proc_name || 'get_service_history_data';
7160 l_proc_step PLS_INTEGER;
7161 l_return_value VARCHAR2(150);
7162 l_value NUMBER;
7163 --
7164 BEGIN
7165 --
7166 IF g_debug
7167 THEN
7168 l_proc_step := 10;
7169 debug_enter(l_proc_name);
7170 DEBUG('p_business_group_id: ' || p_business_group_id);
7171 DEBUG('p_effective_date: '
7172 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
7173 DEBUG('p_assignment_id: ' || p_assignment_id);
7174 DEBUG('p_rule_parameter: ' || p_rule_parameter);
7175 END IF;
7176
7177 -- Call local functions based on rule_parameter value
7178 IF g_debug
7179 THEN
7180 l_proc_step := 20;
7181 DEBUG(l_proc_name, l_proc_step);
7182 END IF;
7183
7184 -- Return Start Date
7185 IF p_rule_parameter = 'StartDate'
7186 THEN
7187 l_return_value := fnd_date.date_to_canonical(g_ser_start_date);
7188
7189 IF g_ser_start_date IS NULL
7190 THEN
7191 IF g_debug
7192 THEN
7193 DEBUG('Raise Data Error: Start Date Missing');
7194 END IF;
7195
7196 -- Raise data error
7197 l_value :=
7198 pqp_gb_psi_functions.raise_extract_error(
7199 p_error_number => 94479
7200 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7201 ,p_token1 => 'Start Date'
7202 );
7203 END IF;
7204 -- Return End Date
7205 ELSIF p_rule_parameter = 'EndDate'
7206 THEN
7207 IF g_start_reason = 'ZZ' THEN
7208 -- This is a termination event
7209 -- populate end date as well
7210 l_return_value := fnd_date.date_to_canonical(g_ser_start_date);
7211
7212 IF g_ser_start_date IS NULL
7213 THEN
7214 IF g_debug
7215 THEN
7216 DEBUG('Raise Data Error: End Date Missing');
7217 END IF;
7218
7219 -- Raise data error
7220 l_value :=
7221 pqp_gb_psi_functions.raise_extract_error(
7222 p_error_number => 94479
7223 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7224 ,p_token1 => 'End Date'
7225 );
7226 END IF; -- End if of start date is null check ...
7227 END IF; -- End if of start reason is ZZ ...
7228 ELSIF p_rule_parameter = 'StartReason'
7229 THEN
7230 l_return_value := TRIM(RPAD(g_start_reason, 4, ' '));
7231
7232 IF g_start_reason IS NULL
7233 THEN
7234 IF g_debug
7235 THEN
7236 DEBUG('Raise Data Error: Start Reason Missing');
7237 END IF;
7238
7239 -- Raise data error
7240 l_value :=
7241 pqp_gb_psi_functions.raise_extract_error(
7242 p_error_number => 94479
7243 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7244 ,p_token1 => 'Start Reason'
7245 );
7246 END IF;
7247 ELSIF p_rule_parameter = 'SchemeCategory'
7248 THEN
7249 l_return_value := TRIM(RPAD(g_scheme_category, 4, ' '));
7250
7251 IF g_scheme_category IS NULL
7252 THEN
7253 IF g_debug
7254 THEN
7255 DEBUG('Raise Data Error: Scheme Category Missing');
7256 END IF;
7257
7258 -- Raise data error
7259 l_value :=
7260 pqp_gb_psi_functions.raise_extract_error(
7261 p_error_number => 94479
7262 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7263 ,p_token1 => 'Scheme Category'
7264 );
7265 END IF;
7266 ELSIF p_rule_parameter = 'SchemeStatus'
7267 THEN
7268 l_return_value := TRIM(RPAD(g_scheme_status, 2, ' '));
7269
7270 IF g_scheme_category IS NULL
7271 THEN
7272 IF g_debug
7273 THEN
7274 DEBUG('Raise Data Error: Scheme Status Missing');
7275 END IF;
7276
7277 -- Raise data error
7278 l_value :=
7279 pqp_gb_psi_functions.raise_extract_error(
7280 p_error_number => 94479
7281 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7282 ,p_token1 => 'Scheme Status'
7283 );
7284 END IF;
7285 ELSIF p_rule_parameter = 'ServiceReason'
7286 THEN
7287 IF g_start_reason = 'ZZ'
7288 THEN
7289 l_return_value := TRIM(RPAD(g_leaving_reason, 2, ' '));
7290
7291 IF g_leaving_reason IS NULL THEN
7292 IF g_debug
7293 THEN
7294 DEBUG('Raise Data Error: End Reason Missing');
7295 END IF;
7296
7297 -- Raise data error
7298 l_value :=
7299 pqp_gb_psi_functions.raise_extract_error(
7300 p_error_number => 94479
7301 ,p_error_text => 'BEN_94479_EXT_PSI_REQ_FLD_MISS'
7302 ,p_token1 => 'End Reason'
7303 );
7304 END IF; -- End if of leaving reason is null check ...
7305 END IF; -- End if of start reason is ZZ check ...
7306 END IF; -- End if of rule parameter check ...
7307
7308 IF g_debug
7309 THEN
7310 l_proc_step := 30;
7311 DEBUG('l_return_value: ' || l_return_value);
7312 debug_exit(l_proc_name);
7313 END IF;
7314
7315 RETURN l_return_value;
7316 EXCEPTION
7317 WHEN OTHERS
7318 THEN
7319 clear_cache;
7320
7321 IF SQLCODE <> hr_utility.hr_error_number
7322 THEN
7323 debug_others(l_proc_name, l_proc_step);
7324
7325 IF g_debug
7326 THEN
7327 DEBUG('Leaving: ' || l_proc_name, -999);
7328 END IF;
7329
7330 fnd_message.raise_error;
7331 ELSE
7332 RAISE;
7333 END IF;
7334 END get_service_history_data;
7335
7336 -- This function is used for post processing in service history interface
7337 -- ----------------------------------------------------------------------------
7338 -- |---------------------< service_history_post_process >---------------------|
7339 -- ----------------------------------------------------------------------------
7340 FUNCTION service_history_post_process(p_ext_rslt_id IN NUMBER)
7341 RETURN VARCHAR2
7342 IS
7343 --
7344 l_proc_name VARCHAR2(80)
7345 := g_proc_name || 'service_history_post_process';
7346 l_proc_step PLS_INTEGER;
7347 l_return_value VARCHAR2(100);
7348 --
7349 BEGIN
7350 --
7351 IF g_debug
7352 THEN
7353 l_proc_step := 10;
7354 debug_enter(l_proc_name);
7355 END IF;
7356
7357 -- pqp_gb_psi_functions.raise_extract_exceptions('DE');
7358 pqp_gb_psi_functions.common_post_process(p_business_group_id => g_business_group_id);
7359
7360 IF g_debug
7361 THEN
7362 l_proc_step := 20;
7363 debug_exit(l_proc_name);
7364 END IF;
7365
7366 RETURN l_return_value;
7367 EXCEPTION
7368 WHEN OTHERS
7369 THEN
7370 clear_cache;
7371
7372 IF SQLCODE <> hr_utility.hr_error_number
7373 THEN
7374 debug_others(l_proc_name, l_proc_step);
7375
7376 IF g_debug
7377 THEN
7378 DEBUG('Leaving: ' || l_proc_name, -999);
7379 END IF;
7380
7381 fnd_message.raise_error;
7382 ELSE
7383 RAISE;
7384 END IF;
7385 END service_history_post_process;
7386 END pqp_gb_psi_service_history;