[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_WPS_HISTORY
Source
1 PACKAGE BODY PQP_GB_PSI_WPS_HISTORY AS
2 -- /* $Header: pqpgbpsiwps.pkb 120.6.12010000.2 2009/03/25 16:23:04 jvaradra ship $ */
3
4
5
6 -- Exceptions
7 hr_application_error exception;
8 pragma exception_init (hr_application_error, -20001);
9
10
11 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
12
13 --For Bug 6071527
14 l_element_type_id NUMBER;
15 l_end_date_basic_ele VARCHAR2(1);
16
17 -- ----------------------------------------------------------------------------
18 -- |--------------------------------< debug >---------------------------------|
19 -- ----------------------------------------------------------------------------
20
21 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
22 IS
23
24 --
25 BEGIN
26 --
27
28 pqp_utilities.DEBUG (
29 p_trace_message => p_trace_message
30 ,p_trace_location => p_trace_location
31 );
32 --
33 END DEBUG;
34
35
36 -- This procedure is used for debug purposes
37 -- debug_enter checks the debug flag and sets the trace on/off
38 --
39 -- ----------------------------------------------------------------------------
40 -- |----------------------------< debug_enter >-------------------------------|
41 -- ----------------------------------------------------------------------------
42
43 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
44 IS
45 BEGIN
46 --
47 IF g_debug THEN
48 IF pqp_utilities.g_nested_level = 0 THEN
49 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
50 END IF;
51 pqp_utilities.debug_enter (
52 p_proc_name => p_proc_name
53 ,p_trace_on => p_trace_on
54 );
55 END IF;
56 --
57
58 END debug_enter;
59
60
61 -- This procedure is used for debug purposes
62 --
63 -- ----------------------------------------------------------------------------
64 -- |----------------------------< debug_exit >--------------------------------|
65 -- ----------------------------------------------------------------------------
66
67 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
68 IS
69 BEGIN
70 --
71 IF g_debug THEN
72 pqp_utilities.debug_exit (
73 p_proc_name => p_proc_name
74 ,p_trace_off => p_trace_off
75 );
76
77 IF pqp_utilities.g_nested_level = 0 THEN
78 hr_utility.trace_off;
79 END IF;
80 END IF;
81 --
82 END debug_exit;
83
84 -- This procedure is used for debug purposes
85 --
86 -- ----------------------------------------------------------------------------
87 -- |----------------------------< debug_others >------------------------------|
88 -- ----------------------------------------------------------------------------
89
90 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
91 IS
92 BEGIN
93 --
94 pqp_utilities.debug_others (
95 p_proc_name => p_proc_name
96 ,p_proc_step => p_proc_step
97 );
98 --
99 END debug_others;
100
101
102
103
104 -- This procedure is used to clear all cached global variables
105 --
106 -- ----------------------------------------------------------------------------
107 -- |----------------------------< clear_cache >-------------------------------|
108 -- ----------------------------------------------------------------------------
109 PROCEDURE clear_cache
110 IS
111 --
112 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
113 l_proc_step PLS_INTEGER;
114 --
115 BEGIN
116 --
117 IF g_debug
118 THEN
119 l_proc_step := 10;
120 debug_enter(l_proc_name);
121 END IF;
122
123 -- start clearing globals
124 g_business_group_id := NULL;
125 g_assignment_id := NULL;
126 g_person_id := NULL;
127 g_person_dtl := NULL;
128 g_assignment_dtl := NULL;
129 g_effective_date := NULL;
130 g_extract_type := NULL;
131
132 g_current_run := NULL;
133 g_altkey := NULL;
134
135 -- globals set by set_shared_globals
136 g_paypoint := NULL;
137 g_cutover_date := NULL;
138 g_ext_dfn_id := NULL;
139
140 --
141
142 IF g_debug
143 THEN
144 debug_exit(l_proc_name);
145 END IF;
146 EXCEPTION
147 WHEN others THEN
148 IF SQLCODE <> hr_utility.hr_error_number
149 THEN
150 debug_others (l_proc_name, l_proc_step);
151 IF g_debug
152 THEN
153 DEBUG ( 'Leaving: '
154 || l_proc_name, -999);
155 END IF;
156 fnd_message.raise_error;
157 ELSE
158 RAISE;
159 END IF;
160 END clear_cache;
161
162
163
164
165 -- This procedure is used to show all events
166 --
167 -- ----------------------------------------------------------------------------
168 -- |----------------------------< show_events >-------------------------------|
169 -- ----------------------------------------------------------------------------
170 PROCEDURE show_events
171 IS
172 l_proc_name VARCHAR2(80) := g_proc_name || 'show_events';
173 BEGIN
174 debug_enter(l_proc_name);
175 IF g_pay_proc_evt_tab.COUNT > 0 THEN
176 debug('====== Detailed Output =======');
177 FOR i IN g_pay_proc_evt_tab.FIRST..g_pay_proc_evt_tab.LAST
178 LOOP
179 debug('----------');
180 debug('Record :'||i);
181 debug('----------');
182 debug('dated_table_id :'||g_pay_proc_evt_tab(i).dated_table_id ,20);
183 debug('datetracked_event :'||g_pay_proc_evt_tab(i).datetracked_event,20);
184 debug('surrogate_key :'||g_pay_proc_evt_tab(i).surrogate_key ,20);
185 debug('column_name :'||g_pay_proc_evt_tab(i).column_name ,20);
186 debug('update_type :'||g_pay_proc_evt_tab(i).update_type ,20);
187 debug('effective_date :'||to_char(g_pay_proc_evt_tab(i).effective_date,'DD/MM/YYYY'),20);
188 debug('old_value :'||g_pay_proc_evt_tab(i).old_value ,20);
189 debug('new_value :'||g_pay_proc_evt_tab(i).new_value ,20);
190 debug('change_values :'||g_pay_proc_evt_tab(i).change_values ,20);
191 debug('proration_type :'||g_pay_proc_evt_tab(i).proration_type ,20);
192 debug('change_mode :'||g_pay_proc_evt_tab(i).change_mode ,20);
193 END LOOP;
194 ELSE
195 debug('No Events',20);
196 END IF;
197 debug_exit(l_proc_name);
198 END show_events;
199
200
201 -- ----------------------------------------------------------------------------
202 -- |---------------< set_wps_history_globals >-------------------|
203 -- Description:
204 -- ----------------------------------------------------------------------------
205 PROCEDURE set_wps_history_globals
206 (
207 p_business_group_id IN NUMBER
208 ,p_assignment_id IN NUMBER
209 ,p_effective_date IN DATE
210 )
211 IS
212 l_index NUMBER;
213 l_proc_name varchar2(72) := g_proc_name||'set_wps_history_globals';
214 -- l_element_type_id NUMBER := NULL;
215
216 BEGIN
217 debug_enter(l_proc_name);
218 -- set global business group id
219 g_business_group_id := p_business_group_id;
220 g_legislation_code := 'GB';
221
222
223 -- store in global, to be used in periodic criteria
224
225 debug('g_legislation_code: '||g_legislation_code,10);
226 debug('g_business_group_id: '||g_business_group_id,20);
227 debug('p_effective_date: '||p_effective_date,30);
228
229 debug_exit(l_proc_name);
230 EXCEPTION
231 WHEN others THEN
232 IF SQLCODE <> hr_utility.hr_error_number
233 THEN
234 debug_others (l_proc_name, 10);
235 IF g_debug
236 THEN
237 DEBUG ( 'Leaving: '
238 || l_proc_name, -999);
239 END IF;
240 fnd_message.raise_error;
241 ELSE
242 RAISE;
243 END IF;
244 END set_wps_history_globals;
245
246 -- ----------------------------------------------------------------------------
247 -- |-----------------------< set_assignment_globals >--------------------------|
248 -- ----------------------------------------------------------------------------
249 PROCEDURE set_assignment_globals
250 (
251 p_assignment_id IN NUMBER
252 ,p_effective_date IN DATE
253 )
254 IS
255 l_proc_name varchar2(72) := g_proc_name||'.set_assignment_globals';
256
257 BEGIN -- set_assignment_globals
258
259 debug_enter(l_proc_name);
260 debug('Inputs are: ',10);
261 debug('p_assignment_id: '||p_assignment_id,10);
262 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
263
264 -- set the global events table
265 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
266
267 -- set global assignment_id
268 g_assignment_id := p_assignment_id;
269 debug('g_assignment_id: '||g_assignment_id,20);
270
271
272 debug('now calling PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob ',30);
273 PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob();
274
275 g_is_terminated := 'N';
276 debug_exit(l_proc_name);
277 EXCEPTION
278 WHEN others THEN
279 IF SQLCODE <> hr_utility.hr_error_number
280 THEN
281 debug_others (l_proc_name, 10);
282 IF g_debug
283 THEN
284 DEBUG ( 'Leaving: '
285 || l_proc_name, -999);
286 END IF;
287 fnd_message.raise_error;
288 ELSE
289 RAISE;
290 END IF;
291 END set_assignment_globals;
292
293 -- ----------------------------------------------------------------------------
294 -- |------------------------< is_curr_evt_processed >-------------------|
295 -- ----------------------------------------------------------------------------
296
297 FUNCTION is_curr_evt_processed RETURN BOOLEAN
298 IS
299 l_proc varchar2(72) := g_proc_name||'.is_curr_evt_processed';
300 l_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
301 l_flag VARCHAR2(1);
302 BEGIN
303 debug_enter(l_proc);
304 IF g_prev_event_dtl_rec.dated_table_id IS NOT NULL THEN
305 l_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
306 l_prev_event_dtl_rec.change_mode := g_prev_event_dtl_rec.change_mode;
307 l_flag := 'Y';
308 IF l_prev_event_dtl_rec.dated_table_id <> g_prev_event_dtl_rec.dated_table_id THEN
309 l_flag := 'N';
310 ELSIF l_prev_event_dtl_rec.datetracked_event <> g_prev_event_dtl_rec.datetracked_event THEN
311 l_flag := 'N';
312 ELSIF l_prev_event_dtl_rec.update_type <> g_prev_event_dtl_rec.update_type THEN
313 l_flag := 'N';
314 ELSIF l_prev_event_dtl_rec.surrogate_key <> g_prev_event_dtl_rec.surrogate_key THEN
315 l_flag := 'N';
316 ELSIF l_prev_event_dtl_rec.column_name <> g_prev_event_dtl_rec.column_name THEN
317 l_flag := 'N';
318 ELSIF l_prev_event_dtl_rec.effective_date <> g_prev_event_dtl_rec.effective_date THEN
319 l_flag := 'N';
320 ELSIF l_prev_event_dtl_rec.old_value <> g_prev_event_dtl_rec.old_value THEN
321 l_flag := 'N';
322 ELSIF l_prev_event_dtl_rec.new_value <> g_prev_event_dtl_rec.new_value THEN
323 l_flag := 'N';
324 ELSIF l_prev_event_dtl_rec.change_values <> g_prev_event_dtl_rec.change_values THEN
325 l_flag := 'N';
326 ELSIF l_prev_event_dtl_rec.proration_type <> g_prev_event_dtl_rec.proration_type THEN
327 l_flag := 'N';
328 ELSIF l_prev_event_dtl_rec.event_group_id <> g_prev_event_dtl_rec.event_group_id THEN
329 l_flag := 'N';
330 ELSIF l_prev_event_dtl_rec.actual_date <> g_prev_event_dtl_rec.actual_date THEN
331 l_flag := 'N';
332 END IF;
333
334 IF l_flag = 'Y' THEN
335 debug('Event already processed',30);
336 debug_exit(l_proc);
337 RETURN TRUE;
338 ELSE
339 g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
340 END IF;
341 ELSE
342 debug('First event');
343 g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
344 END IF;
345
346 debug_exit(l_proc);
347 RETURN FALSE;
348 END is_curr_evt_processed;
349 ----
350
351
352 -- ----------------------------------------------------------------------------
353 -- |------------------------< chk_wps_cutover_crit >-------------------|
354 -- ----------------------------------------------------------------------------
355
356 FUNCTION chk_wps_cutover_crit
357 (p_business_group_id IN NUMBER
358 ,p_effective_date IN DATE
359 ,p_assignment_id IN NUMBER
360 )
361 RETURN VARCHAR2
362 IS
363 --
364 l_proc_name VARCHAR2(61):=
365 g_proc_name||'chk_wps_cutover_crit';
366
367 l_return VARCHAR2(1) := 'N';
368 --
369 BEGIN
370
371 -- trace
372
373 debug_enter(l_proc_name);
374
375 debug('Entering chk_wps_cutover_crit ...',10);
376 debug ('p_business_group_id:'||p_business_group_id);
377 debug ('p_assignment_id:'||p_assignment_id);
378
379 -- being called only once in complete extract run
380 IF g_business_group_id IS NULL THEN
381 -- clear the cached globals
382 clear_cache;
383
384
385 -- for trace switching ON/OFF
386 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
387 -- setting shared globals
388 -- 1) paypoint
389 -- 2) cutover date
390 -- 3) extract def id
391 PQP_GB_PSI_FUNCTIONS.set_shared_globals
392 (p_business_group_id => p_business_group_id
393 ,p_paypoint => g_paypoint -- OUT
394 ,p_cutover_date => g_cutover_date -- OUT
395 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
396 );
397
398 -- setting extract specific globals
399 set_wps_history_globals
400 (p_business_group_id => p_business_group_id
401 ,p_assignment_id => p_assignment_id
402 ,p_effective_date => p_effective_date
403 );
404
405 g_business_group_id := p_business_group_id;
406
407 debug('now raise setup exceptions ...',15);
408 -- raise setup errors and warnings
409 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
410
411 END IF; -- shared and basic_data globals have been set
412 g_current_run := 'CUTOVER';
413 debug('g_current_run :'||g_current_run);
414
415
416 -- calling the basic criteria for this person assignment
417 l_return :=
418 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
419 (p_business_group_id => p_business_group_id
420 ,p_effective_date => p_effective_date
421 ,p_assignment_id => p_assignment_id
422 ,p_person_dtl => g_person_dtl
423 ,p_assignment_dtl => g_assignment_dtl
424 );
425
426
427 --calling function to check the pension scheme of the person
428 IF l_return = 'Y'
429 THEN
430 debug('calling function to check the pension scheme of the person',20);
431 --calling function to check the pension scheme of the person
432 l_return :=PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
433 (p_business_group_id => p_business_group_id
434 ,p_effective_date => p_effective_date
435 ,p_assignment_id => p_assignment_id
436 ,p_psi_pension_scheme => 'CLASSIC'
437 ,p_pension_element_type_id => g_pension_element_type_id
438 );
439 debug('l_return: '||l_return,25);
440
441 --****This commented loop is for 'Classic Plus' type of element.****--
442 /* IF l_return = 'N' THEN
443 l_return :=PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
444 (p_business_group_id => p_business_group_id
445 ,p_effective_date => p_effective_date
446 ,p_assignment_id => p_assignment_id
447 ,p_psi_pension_scheme => 'CLASSPLUS'
448 ,p_pension_element_type_id => g_pension_element_type_id
449 );
450 END IF;
451 debug('l_return: '||l_return,30);*/
452 END IF;
453
454 --For Bug 6071527
455 IF l_return = 'Y'
456 THEN
457 OPEN get_wps_ele_scheme_name(p_element_type_id => g_pension_element_type_id
458 );
459 FETCH get_wps_ele_scheme_name into g_pension_scheme_name;
460
461 IF get_wps_ele_scheme_name%NOTFOUND
462 THEN
463 l_return := 'N';
464 END IF;
465 CLOSE get_wps_ele_scheme_name;
466 END IF;
467 --For Bug 6071527 End
468
469 IF l_return <> 'N' -- no need to set alt_key for person not picked up
470 THEN
471 -- to ensure that this is called only once for an assignment
472 IF g_assignment_id IS NULL
473 OR
474 (
475 g_assignment_id IS NOT NULL and g_assignment_id <> p_assignment_id
476 ) THEN
477 -- put a fucntion here which is to be called only once per person
478 g_assignment_id := p_assignment_id;
479 debug('this is a new assignment, need to set globals',15);
480 ELSE
481 debug('this is the same assignment, NO need to set globals',15);
482 END IF;
483 END IF; -- l_return <> 'N'
484
485
486 debug_exit(l_proc_name);
487 return l_return;
488
489 EXCEPTION
490 WHEN others THEN
491 IF SQLCODE <> hr_utility.hr_error_number
492 THEN
493 debug_others (l_proc_name, 10);
494 IF g_debug
495 THEN
496 DEBUG ( 'Leaving: '
497 || l_proc_name, -999);
498 END IF;
499 fnd_message.raise_error;
500 ELSE
501 RAISE;
502 END IF;
503
504 END chk_wps_cutover_crit;
505
506 -- ----------------------------------------------------------------------------
507 -- |------------------------< chk_wps_periodic_crit >-------------------|
508 -- ----------------------------------------------------------------------------
509
510 FUNCTION chk_wps_periodic_crit
511 (p_business_group_id IN NUMBER
512 ,p_effective_date IN DATE
513 ,p_assignment_id IN NUMBER
514 )
515 RETURN VARCHAR2
516 IS
517 --
518
519 l_proc_name VARCHAR2(61):=
520 g_proc_name||'chk_wps_periodic_crit';
521
522 l_return VARCHAR2(1) := 'N';
523 l_curr_evt_index NUMBER;
524 -- l_element_type_id NUMBER;
525 l_chg_surrogate_key NUMBER;
526 l_update_type VARCHAR2(5);
527 l_element_entry_id NUMBER;
528 l_entry_type VARCHAR2(5);
529 l_dated_table_id NUMBER;
530 l_chg_table VARCHAR2(61);
531 l_chg_column_name VARCHAR2(61);
532 l_return_01 VARCHAR2(1) := 'N';
533 l_opt_out_date VARCHAR2(61);
534 l_error NUMBER;
535 --
536 --For Bug 6071527
537 l_chk_assignment_id NUMBER;
538 l_wps_eff_end_date DATE;
539 l_assgn_eff_end_date DATE;
540 l_wps_byb_scheme VARCHAR2(60);
541
542 --Bug 7611963: Add cursor to get ele end date
543 l_surrogate_key NUMBER;
544 l_eve_effective_date DATE;
545 l_ele_end_date DATE;
546
547 CURSOR csr_get_ele_end_date (c_element_entry_id NUMBER)
548 IS
549 SELECT max(effective_end_date)
550 FROM PAY_ELEMENT_ENTRIES_F
551 WHERE element_entry_id = c_element_entry_id;
552
553 BEGIN
554
555 -- trace
556
557 debug_enter(l_proc_name);
558
559 debug('Entering chk_wps_periodic_crit ...',10);
560 debug ('p_business_group_id:'||p_business_group_id);
561 debug ('p_assignment_id:'||p_assignment_id);
562 debug('p_effective_date : ' || p_effective_date);
563
564
565 -- being called only once in complete extract run
566 IF g_business_group_id IS NULL THEN
567 -- clear the cached globals
568 clear_cache;
569
570 -- for trace switching ON/OFF
571 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
572
573 -- setting shared globals
574 -- 1) paypoint
575 -- 2) cutover date
576 -- 3) extract def id
577 PQP_GB_PSI_FUNCTIONS.set_shared_globals
578 (p_business_group_id => p_business_group_id
579 ,p_paypoint => g_paypoint -- OUT
580 ,p_cutover_date => g_cutover_date -- OUT
581 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
582 );
583 --g_effective_date := p_effective_date;
584
585 -- setting extract specific globals
586 set_wps_history_globals
587 (p_business_group_id => p_business_group_id
588 ,p_assignment_id => p_assignment_id
589 ,p_effective_date => p_effective_date
590 );
591
592 g_business_group_id := p_business_group_id;
593
594 debug('now raise setup exceptions ...',15);
595 -- raise setup errors and warnings
596 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
597
598 -- now clearing cache of assign_cat in basic criteria
599 --PQP_GB_PSI_FUNCTIONS.g_assign_category_mapping.DELETE;
600 END IF; -- shared and basic_data globals have been set
601
602 g_current_run := 'PERIODIC';
603 debug('g_current_run :'||g_current_run);
604
605 debug('calling the basic criteria for this person assignment');
606 -- calling the basic criteria for this person assignment
607 l_return :=
608 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
609 (p_business_group_id => p_business_group_id
610 ,p_effective_date => p_effective_date
611 ,p_assignment_id => p_assignment_id
612 ,p_person_dtl => g_person_dtl
613 ,p_assignment_dtl => g_assignment_dtl
614 );
615 debug ('p_assignment_id:'||p_assignment_id);
616 debug('l_return: '||l_return);
617
618 IF l_return = 'Y'
619 THEN
620 debug('Calling the common include event proc');
621 -- set the global events table
622 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
623 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
624 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
625 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
626 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
627 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
628 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
629
630 debug('----------');
631 debug('Record :'||l_curr_evt_index);
632 debug('----------');
633 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
634 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
635 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
636 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
637 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
638 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
639 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
640 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
641 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
642 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
643 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
644 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
645
646
647 IF is_curr_evt_processed() THEN
648 l_return := 'N';
649 debug('Returning : '||l_return,20);
650 debug_exit(l_proc_name);
651 return l_return;
652 END IF;
653
654 l_return := pqp_gb_psi_functions.include_event
655 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
656 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
657 );
658
659
660 debug ('p_assignment_id:'||p_assignment_id);
661 debug('include_event returned: '||l_return);
662 END IF;
663
664 IF l_return = 'Y'
665 THEN
666 --setting assignment globals
667 IF (g_assignment_id IS NULL
668 OR p_assignment_id <> nvl(g_assignment_id,0))AND l_return = 'Y'
669 THEN
670 set_assignment_globals
671 (
672 p_assignment_id => p_assignment_id
673 ,p_effective_date => p_effective_date
674 );
675 END IF;
676
677 pqp_gb_psi_functions.g_effective_date := p_effective_date;
678 debug('g_effective_date: '||g_effective_date);
679 debug('p_effective_date: '||p_effective_date);
680 IF pqp_gb_psi_functions.is_today_sal_start() = 'Y' THEN -- salary start
681 g_is_terminated := 'N'; -- change termination status to N
682 IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F' AND l_update_type = 'I'
683 THEN
684 l_return := 'N'; -- this event is because of PQP_GB_PSI_NEW_HIRE event group.
685 END IF;
686 END IF;
687 IF g_is_terminated = 'Y' THEN
688 l_return := 'N';
689 END IF;
690 END IF;
691
692 --calling function to check the pension scheme of the person
693 IF l_return = 'Y'
694 THEN
695 debug('calling function to check the pension scheme of the person');
696 --calling function to check the pension scheme of the person
697 l_return := PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
698 (p_business_group_id => p_business_group_id
699 ,p_effective_date => p_effective_date
700 ,p_assignment_id => p_assignment_id
701 ,p_psi_pension_scheme => 'CLASSIC'
702 ,p_pension_element_type_id => g_pension_element_type_id
703 );
704 debug('l_return: '||l_return,25);
705
706 --****This commented loop is for 'Classic Plus' type of element.****--
707 /* IF l_return = 'N' THEN
708 l_return := PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
709 (p_business_group_id => p_business_group_id
710 ,p_effective_date => p_effective_date
711 ,p_assignment_id => p_assignment_id
712 ,p_psi_pension_scheme => 'CLASSPLUS'
713 ,p_pension_element_type_id => g_pension_element_type_id
714 );
715 END IF;
716 debug('l_return: '||l_return,40);*/
717 END IF;
718
719
720 debug('l_chk_assignment_id : '||l_chk_assignment_id);
721 debug('p_assignment_id : '||p_assignment_id);
722
723 -- For Bug 6071527
724
725 IF (l_chk_assignment_id IS NULL OR l_chk_assignment_id <> p_assignment_id)
726 AND l_return = 'Y'
727 THEN
728 OPEN get_wps_ele_scheme_name(p_element_type_id => g_pension_element_type_id
729 );
730 FETCH get_wps_ele_scheme_name into g_pension_scheme_name;
731
732 IF get_wps_ele_scheme_name%NOTFOUND
733 THEN
734 l_return := 'N';
735 END IF;
736 CLOSE get_wps_ele_scheme_name;
737 l_chk_assignment_id := p_assignment_id;
738
739 END IF;
740
741 debug('g_pension_scheme_name : '||g_pension_scheme_name);
742
743 l_end_date_basic_ele := 'Y';
744
745 --For Bug 6071527 End
746
747 debug('g_is_terminated: '||g_is_terminated, 30);
748
749 IF l_return = 'Y'
750 THEN
751 IF l_chg_table <> 'PER_ALL_ASSIGNMENTS_F'
752 THEN
753 debug('l_return: '||l_return);
754 IF l_return = 'Y' --To check if element type is classic type.
755 THEN
756 debug('checking whether this event is of WPS type');
757 --checking whether this event is of WPS type.
758 debug('l_chg_surrogate_key '||l_chg_surrogate_key,25);
759
760 IF l_update_type = 'C'
761 THEN
762 debug('correction event');
763 OPEN csr_get_element_entry_id
764 (p_element_entry_value_id => l_chg_surrogate_key
765 );
766 FETCH csr_get_element_entry_id into l_element_entry_id;
767 IF csr_get_element_entry_id%NOTFOUND
768 THEN
769 debug('element entry id not found for this correction event');
770 l_return := 'N';
771 END IF;
772 CLOSE csr_get_element_entry_id;
773 ELSE
774 debug('not a correction event');
775 l_element_entry_id := l_chg_surrogate_key;
776 END IF;
777
778 --Bug 7611963: Add chk for reverse terminations
779 IF (l_chg_table = 'PAY_ELEMENT_ENTRIES_F'
780 AND l_chg_column_name = 'EFFECTIVE_END_DATE'
781 AND l_update_type = 'E')
782 THEN
783 l_surrogate_key := g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key;
784 debug('l_surrogate_key :'||l_surrogate_key,23);
785
786 l_eve_effective_date := g_pay_proc_evt_tab(l_curr_evt_index).effective_date;
787 debug('l_eve_effective_date :'||l_eve_effective_date,23);
788
789 OPEN csr_get_ele_end_date(l_surrogate_key);
790 FETCH csr_get_ele_end_date INTO l_ele_end_date;
791 CLOSE csr_get_ele_end_date;
792
793 debug('l_ele_end_date :'||l_ele_end_date,23);
794
795 IF l_ele_end_date <> l_eve_effective_date
796 THEN
797 l_return := 'N';
798 debug('l_return : '||l_return,23);
799 END IF;
800 END IF;
801 --Bug 7611963: End
802
803 IF l_return = 'Y'
804 THEN
805 debug('element entry id '||l_element_entry_id);
806 OPEN csr_get_element_type_id
807 (c_element_entry_id => l_element_entry_id
808 );
809 FETCH csr_get_element_type_id INTO l_element_type_id;
810 IF csr_get_element_type_id%NOTFOUND
811 THEN
812 debug('element_type_id not found', 10);
813 l_return := 'N';
814 ELSE
815 debug('l_element_type_id : ' || l_element_type_id, 10);
816 debug('g_pension_element_type_id : ' || g_pension_element_type_id, 20);
817
818 --For Bug 6071527
819 IF l_element_type_id <> g_pension_element_type_id
820 THEN
821
822 OPEN get_wps_byb_ele_scheme_name(p_element_type_id => l_element_type_id
823 ,p_pension_scheme_name => g_pension_scheme_name
824 );
825 FETCH get_wps_byb_ele_scheme_name INTO l_wps_byb_scheme;
826 IF get_wps_byb_ele_scheme_name%NOTFOUND
827 THEN
828 l_return := 'N';
829 ELSE
830 l_return := 'Y';
831 END IF;
832 CLOSE get_wps_byb_ele_scheme_name;
833
834 IF l_update_type = 'E' and l_return = 'Y'
835 THEN
836 l_wps_eff_end_date := p_effective_date + 1;
837
838 OPEN get_wps_eff_end_date(p_element_type_id => g_pension_element_type_id
839 ,p_assignment_id => p_assignment_id
840 ,p_effective_date => l_wps_eff_end_date);
841
842 Fetch get_wps_eff_end_date into l_wps_eff_end_date;
843 IF get_wps_eff_end_date%NOTFOUND
844 THEN
845 l_return := 'N';
846 ELSE
847 l_end_date_basic_ele :='N';
848 END IF;
849 CLOSE get_wps_eff_end_date;
850 debug('l_wps_eff_end_date : ' || l_wps_eff_end_date, 31);
851 debug('l_end_date_basic_ele : ' || l_end_date_basic_ele, 32);
852
853 IF l_return = 'Y'
854 THEN
855 OPEN get_assgn_eff_end_date(p_assignment_id => p_assignment_id
856 ,p_effective_date => p_effective_date);
857 Fetch get_assgn_eff_end_date into l_assgn_eff_end_date;
858 IF get_assgn_eff_end_date%FOUND
859 THEN
860 l_return := 'N';
861 END IF;
862 CLOSE get_assgn_eff_end_date;
863 debug('l_assgn_eff_end_date : ' || l_assgn_eff_end_date, 33);
864 END IF;
865 END IF;
866
867 ELSE
868 l_return := 'Y';
869 END IF;
870 --For Bug 6071527 End
871 debug('l_return : ' || l_return, 30);
872 END IF; -- csr_get_element_type_id%NOTFOUND
873 CLOSE csr_get_element_type_id;
874 END IF;
875 END IF;
876
877 --To check if the event is of 'override'. such events are to be discarded.
878 IF l_return = 'Y'
879 THEN
880 OPEN csr_get_entry_type
881 (p_element_entry_id => l_element_entry_id
882 ,p_effective_date => p_effective_date
883 );
884 FETCH csr_get_entry_type INTO l_entry_type;
885 IF csr_get_entry_type%NOTFOUND
886 THEN
887 debug('entry_type not found');
888 l_return := 'N';
889 ELSE
890 debug('l_entry_type : ' ||l_entry_type);
891 IF l_entry_type = 'S'
892 THEN
893 l_return := 'N';
894 END IF;
895 END IF;
896 CLOSE csr_get_entry_type;
897 END IF;
898 END IF;
899 END IF;
900
901 --Final Check -- if Opt Out Date is before the event date then reject and raise warning.
902 IF l_return = 'Y'
903 THEN
904 OPEN get_wps_percent_cont_cut
905 (p_assignment_id => p_assignment_id
906 ,p_effective_date => p_effective_date
907 ,p_element_type_id => g_pension_element_type_id
908 ,p_input_value_name=> 'Opt Out Date'
909 );
910 FETCH get_wps_percent_cont_cut INTO l_opt_out_date,l_element_entry_id;
911 IF get_wps_percent_cont_cut%NOTFOUND
912 THEN
913 l_opt_out_date := NULL;
914 debug('get_wps_percent_cont_cut NOTFOUND for opt out date');
915 END IF;
916 IF l_opt_out_date IS NOT NULL
917 THEN
918 IF p_effective_date > TO_DATE(SUBSTR(l_opt_out_date,1,10),'yyyy/mm/dd')
919 THEN
920 l_return := 'N';
921 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
922 (p_error_number => 94594
923 ,p_error_text => 'BEN_94594_OPTED_OUT_OF_SCHEME'
924 ,p_token1 => to_char(p_effective_date,'DD/MM/YYYY')
925 ,p_token2 => to_char(TO_DATE(SUBSTR(l_opt_out_date,1,10),'YYYY/MM/DD'),'DD/MM/YYYY')
926 );
927 debug('Event is after the opt out date');
928 END IF;
929 END IF;
930 CLOSE get_wps_percent_cont_cut;
931 END IF;
932
933 IF l_return = 'Y' THEN
934 pqp_gb_psi_functions.process_retro_event(p_include => l_return);
935
936 IF pqp_gb_psi_functions.is_today_sal_end() = 'Y' THEN -- salary end
937 g_is_terminated := 'Y'; -- change termination status to 'Y'
938 debug('Salary Ended Today');
939
940 -- For Bug 6033545
941 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F' or l_chg_table = 'PAY_ELEMENT_ENTRIES_F')
942 AND l_chg_column_name = 'EFFECTIVE_END_DATE'
943 THEN
944 l_return := 'N';
945 END IF;
946
947 ELSE
948 -- if the event is on assignment_status_type_id
949 -- reject the event
950 debug('Salary not Ended Today');
951 debug('l_chg_table: '||l_chg_table, 10);
952 debug('l_chg_column_name: '||l_chg_column_name, 20);
953 IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
954 AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
955 l_return := 'N';
956 END IF;
957 END IF;
958 END IF;
959 debug('g_is_terminated: '||g_is_terminated, 40);
960
961 IF l_return <> 'N' -- no need to set alt_key for person not picked up
962 THEN
963 -- to ensure that this is called only once for an assignment
964 IF g_assignment_id IS NULL
965 OR
966 (
967 g_assignment_id IS NOT NULL and g_assignment_id <> p_assignment_id
968 ) THEN
969 -- put a fucntion here which is to be called only once per person
970 g_altkey :=
971 PQP_GB_PSI_FUNCTIONS.altkey;
972 --(p_assignment_number => g_assignment_dtl.assignment_number
973 --,p_paypoint => g_paypoint
974 --);
975 g_assignment_id := p_assignment_id;
976 debug('this is a new assignment, need to set globals',15);
977 ELSE
978 debug('this is the same assignment, NO need to set globals',15);
979 END IF;
980 END IF; -- l_return <> 'N'
981
982 debug_exit(l_proc_name);
983 return l_return;
984
985 EXCEPTION
986 WHEN others THEN
987 IF SQLCODE <> hr_utility.hr_error_number
988 THEN
989 debug_others (l_proc_name, 10);
990 IF g_debug
991 THEN
992 DEBUG ( 'Leaving: '
993 || l_proc_name, -999);
994 END IF;
995 fnd_message.raise_error;
996 ELSE
997 RAISE;
998 END IF;
999
1000 END chk_wps_periodic_crit;
1001
1002 -- ----------------------------------------------------------------------------
1003 -- |------------------------< get_wpsPercent >-------------------------|
1004 -- ----------------------------------------------------------------------------
1005 FUNCTION get_wpsPercent
1006 (p_business_group_id IN NUMBER -- context
1007 ,p_effective_date IN DATE -- context
1008 ,p_assignment_id IN NUMBER -- context
1009 )
1010 RETURN VARCHAR2 IS
1011 l_proc_name VARCHAR2(61):=
1012 g_proc_name||'get_wpsPercent';
1013 l_value number;
1014 l_effective_start_date VARCHAR2(60);
1015 l_effective_end_date VARCHAR2(60);
1016 l_element_entry_id NUMBER;
1017 l_chg_surrogate_key NUMBER;
1018 l_update_type VARCHAR2(5);
1019 l_curr_evt_index NUMBER;
1020 l_effective_date DATE;
1021 wps_percent VARCHAR2(60);
1022 l_start_date DATE;
1023 l_end_date DATE;
1024 l_return NUMBER;
1025 l_flag BOOLEAN := TRUE; --To check l_element_id.
1026 l_dated_table_id NUMBER;
1027 l_chg_table VARCHAR2(61);
1028
1029 --For Bug 6071527
1030 wps_byb_percent VARCHAR2(60) := '0';
1031 l_g_effective_date DATE;
1032 l_chg_column_name VARCHAR2(61);
1033
1034 BEGIN
1035 debug_enter(l_proc_name);
1036 debug('g_current_run: '||g_current_run);
1037 IF g_current_run = 'PERIODIC'
1038 THEN
1039 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
1040 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
1041 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1042 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
1043 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
1044 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
1045
1046 --For Bug 6071527
1047 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
1048
1049 debug('l_chg_column_name :-'||l_chg_column_name );
1050 debug('l_update_type :-'||l_update_type);
1051
1052 IF l_element_type_id <> g_pension_element_type_id and l_update_type = 'E'
1053 and l_end_date_basic_ele = 'N'
1054 THEN
1055 l_g_effective_date := p_effective_date + 1;
1056 ELSE
1057 l_g_effective_date := p_effective_date;
1058 END IF;
1059 --For Bug 6071527 End
1060
1061 debug('l_chg_table :-'||l_chg_table);
1062
1063 IF l_chg_table <> 'PER_ALL_ASSIGNMENTS_F'
1064 THEN
1065 debug('l_update_type:-'||l_update_type);
1066 IF l_update_type = 'C'
1067 THEN
1068 debug('correction event');
1069 OPEN csr_get_element_entry_id
1070 (p_element_entry_value_id => l_chg_surrogate_key
1071 );
1072 FETCH csr_get_element_entry_id into l_element_entry_id;
1073 IF csr_get_element_entry_id%NOTFOUND
1074 THEN
1075 debug('element entry id not found for this correction event');
1076 l_flag := FALSE;
1077 END IF;
1078 CLOSE csr_get_element_entry_id;
1079 ELSE
1080 debug('not a correction event');
1081 l_element_entry_id := l_chg_surrogate_key;
1082 END IF;
1083 IF l_flag = TRUE
1084 THEN
1085 --For Bug 6071527
1086 OPEN get_wps_percent_cont(p_assignment_id => p_assignment_id
1087 ,p_effective_date => l_g_effective_date -- p_effective_date
1088 ,p_element_type_id => g_pension_element_type_id
1089 ,p_input_value_name=> 'Contribution Percent'
1090 );
1091 FETCH get_wps_percent_cont INTO wps_percent;
1092 IF get_wps_percent_cont%NOTFOUND
1093 THEN
1094 wps_percent := '0';
1095 END IF;
1096 CLOSE get_wps_percent_cont;
1097
1098 OPEN get_wps_byb_percent_cont(p_effective_date => l_g_effective_date --p_effective_date
1099 ,p_assignment_id => p_assignment_id
1100 ,p_input_value_name => 'Contribution Percent'
1101 ,p_scheme_name => g_pension_scheme_name
1102 );
1103 FETCH get_wps_byb_percent_cont into wps_byb_percent;
1104 IF get_wps_byb_percent_cont%NOTFOUND
1105 THEN
1106 wps_byb_percent := '0';
1107 END IF;
1108 CLOSE get_wps_byb_percent_cont;
1109
1110 debug('wps_byb_percent: '||wps_byb_percent);
1111 wps_percent := wps_percent + wps_byb_percent;
1112 --For Bug 6071527 End
1113
1114 END IF;
1115 END IF;
1116 END IF;
1117 IF g_current_run = 'CUTOVER' OR
1118 (l_chg_table = 'PER_ALL_ASSIGNMENTS_F' AND g_current_run = 'PERIODIC')
1119 THEN
1120 --For Bug 6071527
1121 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
1122 AND
1123 l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
1124 AND
1125 g_is_terminated = 'Y')
1126 THEN
1127 l_g_effective_date := p_effective_date -1;
1128 ELSE
1129 l_g_effective_date := p_effective_date;
1130 END IF;
1131 --For Bug 6071527 End
1132
1133 debug('p_effective_date: '||p_effective_date);
1134 debug('l_g_effective_date: '||l_g_effective_date);
1135 debug('g_is_terminated: '||g_is_terminated);
1136
1137 OPEN get_wps_percent_cont_cut
1138 (p_assignment_id => p_assignment_id
1139 ,p_effective_date => l_g_effective_date --p_effective_date
1140 ,p_element_type_id => g_pension_element_type_id
1141 ,p_input_value_name=> 'Contribution Percent'
1142 );
1143 FETCH get_wps_percent_cont_cut INTO wps_percent,l_element_entry_id;
1144 IF get_wps_percent_cont_cut%NOTFOUND
1145 THEN
1146 wps_percent := '0';
1147 debug('get_wps_percent_cont_cut NOTFOUND');
1148 END IF;
1149 CLOSE get_wps_percent_cont_cut;
1150
1151 debug('wps_percent: '||wps_percent);
1152
1153 --For Bug 6071527
1154
1155 OPEN get_wps_byb_percent_cont(p_effective_date => l_g_effective_date --p_effective_date
1156 ,p_assignment_id => p_assignment_id
1157 ,p_input_value_name => 'Contribution Percent'
1158 ,p_scheme_name => g_pension_scheme_name
1159 );
1160 FETCH get_wps_byb_percent_cont into wps_byb_percent;
1161 IF get_wps_byb_percent_cont%NOTFOUND
1162 THEN
1163 wps_byb_percent := '0';
1164 END IF;
1165 CLOSE get_wps_byb_percent_cont;
1166
1167 debug('wps_byb_percent: '||wps_byb_percent);
1168 wps_percent := wps_percent + wps_byb_percent;
1169 --For Bug 6071527 End
1170 END IF;
1171 debug('wps_percent: '||wps_percent);
1172 debug_exit(l_proc_name);
1173 RETURN wps_percent;
1174 END get_wpsPercent;
1175
1176 -- ----------------------------------------------------------------------------
1177 -- |------------------------< get_start_end_date >-------------------------|
1178 -- ----------------------------------------------------------------------------
1179 FUNCTION get_start_end_date
1180 (p_business_group_id IN NUMBER
1181 ,p_effective_date IN DATE
1182 ,p_assignment_id IN NUMBER
1183 ,p_effective_start_date OUT NOCOPY VARCHAR2
1184 ,p_effective_end_date OUT NOCOPY VARCHAR2
1185 )
1186 RETURN NUMBER IS
1187 l_proc_name VARCHAR2(61):=
1188 g_proc_name||'get_start_end_date';
1189 l_value number;
1190 l_effective_start_date VARCHAR2(60);
1191 l_effective_end_date VARCHAR2(60);
1192 l_element_entry_id NUMBER;
1193 l_chg_surrogate_key NUMBER;
1194 l_update_type VARCHAR2(5);
1195 l_curr_evt_index NUMBER;
1196 l_effective_date DATE;
1197 wps_percent VARCHAR2(60);
1198 opt_out_date VARCHAR2(60);
1199 l_start_date DATE;
1200 l_end_date DATE;
1201 l_return NUMBER;
1202 l_flag BOOLEAN := TRUE; --To check l_element_id.
1203
1204 -- For Bug 5998123
1205 l_dated_table_id NUMBER;
1206 l_chg_table VARCHAR2(61);
1207 l_chg_column_name VARCHAR2(61);
1208
1209 --For Bug 6071527
1210 l_g_effective_date DATE;
1211
1212 BEGIN
1213 debug_enter(l_proc_name);
1214 IF g_current_run = 'PERIODIC'
1215 THEN
1216 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
1217 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
1218 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1219 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
1220
1221 -- For Bug 5998123
1222 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
1223 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
1224 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
1225
1226
1227 --debug('g_pay_proc_evt_tab: '||g_pay_proc_evt_tab);
1228 debug('l_update_type:-'||l_update_type);
1229
1230 --For Bug 6071527
1231 IF l_element_type_id <> g_pension_element_type_id and l_update_type = 'E'
1232 and l_end_date_basic_ele = 'N'
1233 THEN
1234 l_g_effective_date := p_effective_date + 1;
1235 ELSE
1236 l_g_effective_date := p_effective_date;
1237 END IF;
1238 --For Bug 6071527 End
1239
1240 p_effective_start_date := to_char(l_g_effective_date,'DD/MM/YYYY');
1241 p_effective_end_date := NULL;
1242
1243
1244 IF (l_update_type = 'E' AND l_element_type_id = g_pension_element_type_id) --For Bug 6071527
1245 or g_is_terminated = 'Y'
1246 THEN
1247 OPEN get_wps_percent_cont_per
1248 (p_element_entry_id => l_chg_surrogate_key
1249 ,p_effective_date => p_effective_date
1250 ,p_input_value_name => 'Opt Out Date'
1251 );
1252 FETCH get_wps_percent_cont_per INTO opt_out_date;
1253 IF get_wps_percent_cont_per%NOTFOUND
1254 THEN
1255 p_effective_end_date := to_char(p_effective_date,'DD/MM/YYYY');
1256 ELSIF opt_out_date IS NULL
1257 THEN
1258 p_effective_end_date := to_char(p_effective_date,'DD/MM/YYYY');
1259 ELSE
1260 p_effective_end_date := to_char(least(p_effective_date,TO_DATE(SUBSTR(opt_out_date,1,10),'yyyy/mm/dd')),'DD/MM/YYYY');
1261 END IF;
1262 CLOSE get_wps_percent_cont_per;
1263 p_effective_start_date := p_effective_end_date;
1264
1265 END IF;
1266
1267 -- For Bug 5998123
1268
1269 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
1270 AND
1271 l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
1272 AND
1273 p_effective_start_date = p_effective_end_date
1274 AND
1275 g_is_terminated = 'Y')
1276 THEN
1277 p_effective_start_date := to_char(to_date(p_effective_start_date,'DD/MM/YYYY')-1,'DD/MM/YYYY');
1278 p_effective_end_date := p_effective_start_date;
1279
1280 END IF;
1281
1282
1283 ELSIF g_current_run = 'CUTOVER'
1284 THEN
1285 p_effective_end_date := NULL;
1286 debug('p_business_group_id'||p_business_group_id,1);
1287 debug('p_effective_date'||p_effective_date,1);
1288 debug('p_assignment_id'|| p_assignment_id,1);
1289 debug('g_pension_element_type_id'||g_pension_element_type_id,1);
1290 OPEN get_wps_percent_cont_cut
1291 (p_assignment_id => p_assignment_id
1292 ,p_effective_date => p_effective_date
1293 ,p_element_type_id => g_pension_element_type_id
1294 ,p_input_value_name=> 'Contribution Percent'
1295 );
1296 FETCH get_wps_percent_cont_cut INTO wps_percent,l_element_entry_id;
1297 IF get_wps_percent_cont_cut%NOTFOUND
1298 THEN
1299 p_effective_start_date := NULL;
1300 debug('get_wps_percent_cont_cut NOTFOUND');
1301
1302 ELSE
1303 debug('get_wps_percent_cont_cut FOUND');
1304 OPEN csr_get_start_date_cut
1305 (p_element_entry_id => l_element_entry_id
1306 );
1307 FETCH csr_get_start_date_cut INTO l_start_date;
1308 IF csr_get_start_date_cut%NOTFOUND
1309 THEN
1310 p_effective_start_date := NULL;
1311 debug('csr_get_start_date_cut NOTFOUND');
1312 ELSE
1313 p_effective_start_date := to_char(l_start_date,'DD/MM/YYYY');
1314 END IF;
1315 CLOSE csr_get_start_date_cut;
1316 END IF;
1317 CLOSE get_wps_percent_cont_cut;
1318 ELSE
1319 debug('g_current_run :'||g_current_run||'is not valid');
1320 END IF;
1321 debug_exit(l_proc_name);
1322 RETURN 0;
1323 END get_start_end_date;
1324
1325 -- ----------------------------------------------------------------------------
1326 -- |------------------------< wps_history_main >-------------------------|
1327 -- ----------------------------------------------------------------------------
1328
1329 FUNCTION wps_history_main
1330 (p_business_group_id IN NUMBER -- context
1331 ,p_effective_date IN DATE -- context
1332 ,p_assignment_id IN NUMBER -- context
1333 ,p_rule_parameter IN VARCHAR2 -- parameter
1334 ,p_output OUT NOCOPY VARCHAR2
1335 )
1336 RETURN NUMBER IS
1337 --
1338
1339 l_proc_name VARCHAR2(61):=
1340 g_proc_name||'wps_history_main';
1341 l_value number;
1342 l_effective_start_date VARCHAR2(60);
1343 l_effective_end_date VARCHAR2(60);
1344 l_element_entry_id NUMBER;
1345 l_chg_surrogate_key NUMBER;
1346 l_update_type VARCHAR2(5);
1347 l_curr_evt_index NUMBER;
1348 l_effective_date DATE;
1349 wps_percent VARCHAR2(60);
1350 l_start_date DATE;
1351 l_end_date DATE;
1352 l_return NUMBER;
1353 l_flag BOOLEAN := TRUE; --To check l_element_id.
1354 l_error NUMBER;
1355 l_element_name VARCHAR2(80);
1356 --
1357 BEGIN
1358
1359 debug_enter(l_proc_name);
1360
1361 -- switch on the trace
1362
1363 debug('Entering wps_history_main ...',0);
1364 debug('p_business_group_id'||p_business_group_id,1);
1365 debug('p_effective_date'||p_effective_date,1);
1366 debug('p_assignment_id'|| p_assignment_id,1);
1367 debug('p_rule_parameter'||p_rule_parameter,1);
1368
1369 -- select the function call based on the parameter being passed to the rule
1370 IF p_rule_parameter = 'WPSPercent'
1371 THEN
1372 debug('Fetching WPS percent Contribution',20);
1373 wps_percent := get_wpsPercent(p_business_group_id => p_business_group_id
1374 ,p_effective_date => p_effective_date
1375 ,p_assignment_id => p_assignment_id
1376 );
1377 IF wps_percent IS NULL
1378 THEN
1379 OPEN get_wps_element_name
1380 (p_element_type_id => g_pension_element_type_id
1381 );
1382 FETCH get_wps_element_name INTO l_element_name;
1383 IF get_wps_element_name%FOUND
1384 THEN
1385 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1386 (p_error_number => 94532
1387 ,p_error_text => 'BEN_94532_NO_ENTRY_VALUE'
1388 ,p_token1 => l_element_name
1389 ,p_token2 => 'CONTRIBUTION PERCENT'
1390 ,p_token3 => to_char(p_effective_date,'DD/MM/YYYY')
1391 );
1392 ELSE
1393 debug('Element Name Not Found',30);
1394 END IF;
1395 CLOSE get_wps_element_name;
1396 wps_percent := '0000000';
1397 ELSIF (fnd_number.canonical_to_number(wps_percent) > 100)
1398 THEN
1399 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1400 (p_error_number => 94533
1401 ,p_error_text => 'BEN_94533_IMPRACTICABLE_VALUE'
1402 ,p_token1 => wps_percent
1403 );
1404 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'0999D99')));
1405 ELSIF (fnd_number.canonical_to_number(wps_percent) < 0)
1406 THEN
1407 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1408 (p_error_number => 94533
1409 ,p_error_text => 'BEN_94533_IMPRACTICABLE_VALUE'
1410 ,p_token1 => wps_percent
1411 );
1412 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'099D99')));
1413 ELSE
1414 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'0999D99')));
1415 END IF;
1416
1417 p_output := wps_percent;
1418 debug('p_output '||p_output);
1419
1420 ELSIF p_rule_parameter = 'WPSStartDate' THEN
1421 l_return := get_start_end_date(p_business_group_id => p_business_group_id
1422 ,p_effective_date => p_effective_date
1423 ,p_assignment_id => p_assignment_id
1424 ,p_effective_start_date => l_effective_start_date
1425 ,p_effective_end_date => l_effective_end_date
1426 );
1427 p_output := l_effective_start_date;
1428 debug('p_output '||p_output);
1429 ELSIF p_rule_parameter = 'WPSEndDate' THEN
1430 l_return := get_start_end_date(p_business_group_id => p_business_group_id
1431 ,p_effective_date => p_effective_date
1432 ,p_assignment_id => p_assignment_id
1433 ,p_effective_start_date => l_effective_start_date
1434 ,p_effective_end_date => l_effective_end_date
1435 );
1436 p_output := l_effective_end_date;
1437 debug('p_output '||p_output);
1438 ELSE
1439 p_output := ' ';
1440 debug('p_output '||p_output);
1441 END IF;
1442
1443
1444 debug_exit(l_proc_name);
1445 RETURN 0;
1446
1447
1448 EXCEPTION
1449 WHEN others THEN
1450 IF SQLCODE <> hr_utility.hr_error_number
1451 THEN
1452 debug_others (l_proc_name, 10);
1453 IF g_debug
1454 THEN
1455 DEBUG ( 'Leaving: '
1456 || l_proc_name, -999);
1457 END IF;
1458 fnd_message.raise_error;
1459 ELSE
1460 RAISE;
1461 END IF;
1462
1463 END wps_history_main;
1464
1465 -- ----------------------------------------------------------------------------
1466 -- |----------------------< wps_post_processing >--------------------------|
1467 -- Description: This is the post-processing rule for the WPS History.
1468 -- ----------------------------------------------------------------------------
1469 FUNCTION wps_post_processing RETURN VARCHAR2
1470 IS
1471 l_proc_name varchar2(72) := g_proc_name||'.wps_post_processing';
1472 BEGIN
1473 debug_enter(l_proc_name);
1474
1475 --Raise extract exceptions which are stored while processing the data elements
1476 --PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions();
1477
1478 PQP_GB_PSI_FUNCTIONS.common_post_process(p_business_group_id => g_business_group_id);
1479 debug_exit(l_proc_name);
1480 return 'Y';
1481 EXCEPTION
1482 WHEN others THEN
1483 IF SQLCODE <> hr_utility.hr_error_number
1484 THEN
1485 debug_others (l_proc_name, 10);
1486 IF g_debug
1487 THEN
1488 DEBUG ( 'Leaving: '
1489 || l_proc_name, -999);
1490 END IF;
1491 fnd_message.raise_error;
1492 ELSE
1493 RAISE;
1494 END IF;
1495 END wps_post_processing;
1496 ------
1497 ------
1498 END PQP_GB_PSI_WPS_HISTORY;