[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 2007/05/24 06:37:21 jvaradra noship $ */
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 BEGIN
542
543 -- trace
544
545 debug_enter(l_proc_name);
546
547 debug('Entering chk_wps_periodic_crit ...',10);
548 debug ('p_business_group_id:'||p_business_group_id);
549 debug ('p_assignment_id:'||p_assignment_id);
550 debug('p_effective_date : ' || p_effective_date);
551
552
553 -- being called only once in complete extract run
554 IF g_business_group_id IS NULL THEN
555 -- clear the cached globals
556 clear_cache;
557
558 -- for trace switching ON/OFF
559 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
560
561 -- setting shared globals
562 -- 1) paypoint
563 -- 2) cutover date
564 -- 3) extract def id
565 PQP_GB_PSI_FUNCTIONS.set_shared_globals
566 (p_business_group_id => p_business_group_id
567 ,p_paypoint => g_paypoint -- OUT
568 ,p_cutover_date => g_cutover_date -- OUT
569 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
570 );
571 --g_effective_date := p_effective_date;
572
573 -- setting extract specific globals
574 set_wps_history_globals
575 (p_business_group_id => p_business_group_id
576 ,p_assignment_id => p_assignment_id
577 ,p_effective_date => p_effective_date
578 );
579
580 g_business_group_id := p_business_group_id;
581
582 debug('now raise setup exceptions ...',15);
583 -- raise setup errors and warnings
584 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
585
586 -- now clearing cache of assign_cat in basic criteria
587 --PQP_GB_PSI_FUNCTIONS.g_assign_category_mapping.DELETE;
588 END IF; -- shared and basic_data globals have been set
589
590 g_current_run := 'PERIODIC';
591 debug('g_current_run :'||g_current_run);
592
593 debug('calling the basic criteria for this person assignment');
594 -- calling the basic criteria for this person assignment
595 l_return :=
596 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
597 (p_business_group_id => p_business_group_id
598 ,p_effective_date => p_effective_date
599 ,p_assignment_id => p_assignment_id
600 ,p_person_dtl => g_person_dtl
601 ,p_assignment_dtl => g_assignment_dtl
602 );
603 debug ('p_assignment_id:'||p_assignment_id);
604 debug('l_return: '||l_return);
605
606 IF l_return = 'Y'
607 THEN
608 debug('Calling the common include event proc');
609 -- set the global events table
610 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
611 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
612 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
613 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
614 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
615 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
616 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
617
618 debug('----------');
619 debug('Record :'||l_curr_evt_index);
620 debug('----------');
621 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
622 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
623 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
624 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
625 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
626 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
627 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
628 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
629 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
630 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
631 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
632 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
633
634
635 IF is_curr_evt_processed() THEN
636 l_return := 'N';
637 debug('Returning : '||l_return,20);
638 debug_exit(l_proc_name);
639 return l_return;
640 END IF;
641
642 l_return := pqp_gb_psi_functions.include_event
643 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
644 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
645 );
646
647
648 debug ('p_assignment_id:'||p_assignment_id);
649 debug('include_event returned: '||l_return);
650 END IF;
651
652 IF l_return = 'Y'
653 THEN
654 --setting assignment globals
655 IF (g_assignment_id IS NULL
656 OR p_assignment_id <> nvl(g_assignment_id,0))AND l_return = 'Y'
657 THEN
658 set_assignment_globals
659 (
660 p_assignment_id => p_assignment_id
661 ,p_effective_date => p_effective_date
662 );
663 END IF;
664
665 pqp_gb_psi_functions.g_effective_date := p_effective_date;
666 debug('g_effective_date: '||g_effective_date);
667 debug('p_effective_date: '||p_effective_date);
668 IF pqp_gb_psi_functions.is_today_sal_start() = 'Y' THEN -- salary start
669 g_is_terminated := 'N'; -- change termination status to N
670 IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F' AND l_update_type = 'I'
671 THEN
672 l_return := 'N'; -- this event is because of PQP_GB_PSI_NEW_HIRE event group.
673 END IF;
674 END IF;
675 IF g_is_terminated = 'Y' THEN
676 l_return := 'N';
677 END IF;
678 END IF;
679
680 --calling function to check the pension scheme of the person
681 IF l_return = 'Y'
682 THEN
683 debug('calling function to check the pension scheme of the person');
684 --calling function to check the pension scheme of the person
685 l_return := PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
686 (p_business_group_id => p_business_group_id
687 ,p_effective_date => p_effective_date
688 ,p_assignment_id => p_assignment_id
689 ,p_psi_pension_scheme => 'CLASSIC'
690 ,p_pension_element_type_id => g_pension_element_type_id
691 );
692 debug('l_return: '||l_return,25);
693
694 --****This commented loop is for 'Classic Plus' type of element.****--
695 /* IF l_return = 'N' THEN
696 l_return := PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme
697 (p_business_group_id => p_business_group_id
698 ,p_effective_date => p_effective_date
699 ,p_assignment_id => p_assignment_id
700 ,p_psi_pension_scheme => 'CLASSPLUS'
701 ,p_pension_element_type_id => g_pension_element_type_id
702 );
703 END IF;
704 debug('l_return: '||l_return,40);*/
705 END IF;
706
707
708 debug('l_chk_assignment_id : '||l_chk_assignment_id);
709 debug('p_assignment_id : '||p_assignment_id);
710
711 -- For Bug 6071527
712
713 IF (l_chk_assignment_id IS NULL OR l_chk_assignment_id <> p_assignment_id)
714 AND l_return = 'Y'
715 THEN
716 OPEN get_wps_ele_scheme_name(p_element_type_id => g_pension_element_type_id
717 );
718 FETCH get_wps_ele_scheme_name into g_pension_scheme_name;
719
720 IF get_wps_ele_scheme_name%NOTFOUND
721 THEN
722 l_return := 'N';
723 END IF;
724 CLOSE get_wps_ele_scheme_name;
725 l_chk_assignment_id := p_assignment_id;
726
727 END IF;
728
729 debug('g_pension_scheme_name : '||g_pension_scheme_name);
730
731 l_end_date_basic_ele := 'Y';
732
733 --For Bug 6071527 End
734
735 debug('g_is_terminated: '||g_is_terminated, 30);
736
737 IF l_return = 'Y'
738 THEN
739 IF l_chg_table <> 'PER_ALL_ASSIGNMENTS_F'
740 THEN
741 debug('l_return: '||l_return);
742 IF l_return = 'Y' --To check if element type is classic type.
743 THEN
744 debug('checking whether this event is of WPS type');
745 --checking whether this event is of WPS type.
746 debug('l_chg_surrogate_key '||l_chg_surrogate_key,25);
747
748 IF l_update_type = 'C'
749 THEN
750 debug('correction event');
751 OPEN csr_get_element_entry_id
752 (p_element_entry_value_id => l_chg_surrogate_key
753 );
754 FETCH csr_get_element_entry_id into l_element_entry_id;
755 IF csr_get_element_entry_id%NOTFOUND
756 THEN
757 debug('element entry id not found for this correction event');
758 l_return := 'N';
759 END IF;
760 CLOSE csr_get_element_entry_id;
761 ELSE
762 debug('not a correction event');
763 l_element_entry_id := l_chg_surrogate_key;
764 END IF;
765
766
767 IF l_return = 'Y'
768 THEN
769 debug('element entry id '||l_element_entry_id);
770 OPEN csr_get_element_type_id
771 (c_element_entry_id => l_element_entry_id
772 );
773 FETCH csr_get_element_type_id INTO l_element_type_id;
774 IF csr_get_element_type_id%NOTFOUND
775 THEN
776 debug('element_type_id not found', 10);
777 l_return := 'N';
778 ELSE
779 debug('l_element_type_id : ' || l_element_type_id, 10);
780 debug('g_pension_element_type_id : ' || g_pension_element_type_id, 20);
781
782 --For Bug 6071527
783 IF l_element_type_id <> g_pension_element_type_id
784 THEN
785
786 OPEN get_wps_byb_ele_scheme_name(p_element_type_id => l_element_type_id
787 ,p_pension_scheme_name => g_pension_scheme_name
788 );
789 FETCH get_wps_byb_ele_scheme_name INTO l_wps_byb_scheme;
790 IF get_wps_byb_ele_scheme_name%NOTFOUND
791 THEN
792 l_return := 'N';
793 ELSE
794 l_return := 'Y';
795 END IF;
796 CLOSE get_wps_byb_ele_scheme_name;
797
798 IF l_update_type = 'E' and l_return = 'Y'
799 THEN
800 l_wps_eff_end_date := p_effective_date + 1;
801
802 OPEN get_wps_eff_end_date(p_element_type_id => g_pension_element_type_id
803 ,p_assignment_id => p_assignment_id
804 ,p_effective_date => l_wps_eff_end_date);
805
806 Fetch get_wps_eff_end_date into l_wps_eff_end_date;
807 IF get_wps_eff_end_date%NOTFOUND
808 THEN
809 l_return := 'N';
810 ELSE
811 l_end_date_basic_ele :='N';
812 END IF;
813 CLOSE get_wps_eff_end_date;
814 debug('l_wps_eff_end_date : ' || l_wps_eff_end_date, 31);
815 debug('l_end_date_basic_ele : ' || l_end_date_basic_ele, 32);
816
817 IF l_return = 'Y'
818 THEN
819 OPEN get_assgn_eff_end_date(p_assignment_id => p_assignment_id
820 ,p_effective_date => p_effective_date);
821 Fetch get_assgn_eff_end_date into l_assgn_eff_end_date;
822 IF get_assgn_eff_end_date%FOUND
823 THEN
824 l_return := 'N';
825 END IF;
826 CLOSE get_assgn_eff_end_date;
827 debug('l_assgn_eff_end_date : ' || l_assgn_eff_end_date, 33);
828 END IF;
829 END IF;
830
831 ELSE
832 l_return := 'Y';
833 END IF;
834 --For Bug 6071527 End
835 debug('l_return : ' || l_return, 30);
836 END IF; -- csr_get_element_type_id%NOTFOUND
837 CLOSE csr_get_element_type_id;
838 END IF;
839 END IF;
840
841 --To check if the event is of 'override'. such events are to be discarded.
842 IF l_return = 'Y'
843 THEN
844 OPEN csr_get_entry_type
845 (p_element_entry_id => l_element_entry_id
846 ,p_effective_date => p_effective_date
847 );
848 FETCH csr_get_entry_type INTO l_entry_type;
849 IF csr_get_entry_type%NOTFOUND
850 THEN
851 debug('entry_type not found');
852 l_return := 'N';
853 ELSE
854 debug('l_entry_type : ' ||l_entry_type);
855 IF l_entry_type = 'S'
856 THEN
857 l_return := 'N';
858 END IF;
859 END IF;
860 CLOSE csr_get_entry_type;
861 END IF;
862 END IF;
863 END IF;
864
865 --Final Check -- if Opt Out Date is before the event date then reject and raise warning.
866 IF l_return = 'Y'
867 THEN
868 OPEN get_wps_percent_cont_cut
869 (p_assignment_id => p_assignment_id
870 ,p_effective_date => p_effective_date
871 ,p_element_type_id => g_pension_element_type_id
872 ,p_input_value_name=> 'Opt Out Date'
873 );
874 FETCH get_wps_percent_cont_cut INTO l_opt_out_date,l_element_entry_id;
875 IF get_wps_percent_cont_cut%NOTFOUND
876 THEN
877 l_opt_out_date := NULL;
878 debug('get_wps_percent_cont_cut NOTFOUND for opt out date');
879 END IF;
880 IF l_opt_out_date IS NOT NULL
881 THEN
882 IF p_effective_date > TO_DATE(SUBSTR(l_opt_out_date,1,10),'yyyy/mm/dd')
883 THEN
884 l_return := 'N';
885 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
886 (p_error_number => 94594
887 ,p_error_text => 'BEN_94594_OPTED_OUT_OF_SCHEME'
888 ,p_token1 => to_char(p_effective_date,'DD/MM/YYYY')
889 ,p_token2 => to_char(TO_DATE(SUBSTR(l_opt_out_date,1,10),'YYYY/MM/DD'),'DD/MM/YYYY')
890 );
891 debug('Event is after the opt out date');
892 END IF;
893 END IF;
894 CLOSE get_wps_percent_cont_cut;
895 END IF;
896
897 IF l_return = 'Y' THEN
898 pqp_gb_psi_functions.process_retro_event(p_include => l_return);
899
900 IF pqp_gb_psi_functions.is_today_sal_end() = 'Y' THEN -- salary end
901 g_is_terminated := 'Y'; -- change termination status to 'Y'
902 debug('Salary Ended Today');
903
904 -- For Bug 6033545
905 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F' or l_chg_table = 'PAY_ELEMENT_ENTRIES_F')
906 AND l_chg_column_name = 'EFFECTIVE_END_DATE'
907 THEN
908 l_return := 'N';
909 END IF;
910
911 ELSE
912 -- if the event is on assignment_status_type_id
913 -- reject the event
914 debug('Salary not Ended Today');
915 debug('l_chg_table: '||l_chg_table, 10);
916 debug('l_chg_column_name: '||l_chg_column_name, 20);
917 IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
918 AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
919 l_return := 'N';
920 END IF;
921 END IF;
922 END IF;
923 debug('g_is_terminated: '||g_is_terminated, 40);
924
925 IF l_return <> 'N' -- no need to set alt_key for person not picked up
926 THEN
927 -- to ensure that this is called only once for an assignment
928 IF g_assignment_id IS NULL
929 OR
930 (
931 g_assignment_id IS NOT NULL and g_assignment_id <> p_assignment_id
932 ) THEN
933 -- put a fucntion here which is to be called only once per person
934 g_altkey :=
935 PQP_GB_PSI_FUNCTIONS.altkey;
936 --(p_assignment_number => g_assignment_dtl.assignment_number
937 --,p_paypoint => g_paypoint
938 --);
939 g_assignment_id := p_assignment_id;
940 debug('this is a new assignment, need to set globals',15);
941 ELSE
942 debug('this is the same assignment, NO need to set globals',15);
943 END IF;
944 END IF; -- l_return <> 'N'
945
946 debug_exit(l_proc_name);
947 return l_return;
948
949 EXCEPTION
950 WHEN others THEN
951 IF SQLCODE <> hr_utility.hr_error_number
952 THEN
953 debug_others (l_proc_name, 10);
954 IF g_debug
955 THEN
956 DEBUG ( 'Leaving: '
957 || l_proc_name, -999);
958 END IF;
959 fnd_message.raise_error;
960 ELSE
961 RAISE;
962 END IF;
963
964 END chk_wps_periodic_crit;
965
966 -- ----------------------------------------------------------------------------
967 -- |------------------------< get_wpsPercent >-------------------------|
968 -- ----------------------------------------------------------------------------
969 FUNCTION get_wpsPercent
970 (p_business_group_id IN NUMBER -- context
971 ,p_effective_date IN DATE -- context
972 ,p_assignment_id IN NUMBER -- context
973 )
974 RETURN VARCHAR2 IS
975 l_proc_name VARCHAR2(61):=
976 g_proc_name||'get_wpsPercent';
977 l_value number;
978 l_effective_start_date VARCHAR2(60);
979 l_effective_end_date VARCHAR2(60);
980 l_element_entry_id NUMBER;
981 l_chg_surrogate_key NUMBER;
982 l_update_type VARCHAR2(5);
983 l_curr_evt_index NUMBER;
984 l_effective_date DATE;
985 wps_percent VARCHAR2(60);
986 l_start_date DATE;
987 l_end_date DATE;
988 l_return NUMBER;
989 l_flag BOOLEAN := TRUE; --To check l_element_id.
990 l_dated_table_id NUMBER;
991 l_chg_table VARCHAR2(61);
992
993 --For Bug 6071527
994 wps_byb_percent VARCHAR2(60) := '0';
995 l_g_effective_date DATE;
996 l_chg_column_name VARCHAR2(61);
997
998 BEGIN
999 debug_enter(l_proc_name);
1000 debug('g_current_run: '||g_current_run);
1001 IF g_current_run = 'PERIODIC'
1002 THEN
1003 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
1004 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
1005 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1006 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
1007 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
1008 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
1009
1010 --For Bug 6071527
1011 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
1012
1013 debug('l_chg_column_name :-'||l_chg_column_name );
1014 debug('l_update_type :-'||l_update_type);
1015
1016 IF l_element_type_id <> g_pension_element_type_id and l_update_type = 'E'
1017 and l_end_date_basic_ele = 'N'
1018 THEN
1019 l_g_effective_date := p_effective_date + 1;
1020 ELSE
1021 l_g_effective_date := p_effective_date;
1022 END IF;
1023 --For Bug 6071527 End
1024
1025 debug('l_chg_table :-'||l_chg_table);
1026
1027 IF l_chg_table <> 'PER_ALL_ASSIGNMENTS_F'
1028 THEN
1029 debug('l_update_type:-'||l_update_type);
1030 IF l_update_type = 'C'
1031 THEN
1032 debug('correction event');
1033 OPEN csr_get_element_entry_id
1034 (p_element_entry_value_id => l_chg_surrogate_key
1035 );
1036 FETCH csr_get_element_entry_id into l_element_entry_id;
1037 IF csr_get_element_entry_id%NOTFOUND
1038 THEN
1039 debug('element entry id not found for this correction event');
1040 l_flag := FALSE;
1041 END IF;
1042 CLOSE csr_get_element_entry_id;
1043 ELSE
1044 debug('not a correction event');
1045 l_element_entry_id := l_chg_surrogate_key;
1046 END IF;
1047 IF l_flag = TRUE
1048 THEN
1049 --For Bug 6071527
1050 OPEN get_wps_percent_cont(p_assignment_id => p_assignment_id
1051 ,p_effective_date => l_g_effective_date -- p_effective_date
1052 ,p_element_type_id => g_pension_element_type_id
1053 ,p_input_value_name=> 'Contribution Percent'
1054 );
1055 FETCH get_wps_percent_cont INTO wps_percent;
1056 IF get_wps_percent_cont%NOTFOUND
1057 THEN
1058 wps_percent := '0';
1059 END IF;
1060 CLOSE get_wps_percent_cont;
1061
1062 OPEN get_wps_byb_percent_cont(p_effective_date => l_g_effective_date --p_effective_date
1063 ,p_assignment_id => p_assignment_id
1064 ,p_input_value_name => 'Contribution Percent'
1065 ,p_scheme_name => g_pension_scheme_name
1066 );
1067 FETCH get_wps_byb_percent_cont into wps_byb_percent;
1068 IF get_wps_byb_percent_cont%NOTFOUND
1069 THEN
1070 wps_byb_percent := '0';
1071 END IF;
1072 CLOSE get_wps_byb_percent_cont;
1073
1074 debug('wps_byb_percent: '||wps_byb_percent);
1075 wps_percent := wps_percent + wps_byb_percent;
1076 --For Bug 6071527 End
1077
1078 END IF;
1079 END IF;
1080 END IF;
1081 IF g_current_run = 'CUTOVER' OR
1082 (l_chg_table = 'PER_ALL_ASSIGNMENTS_F' AND g_current_run = 'PERIODIC')
1083 THEN
1084 --For Bug 6071527
1085 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
1086 AND
1087 l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
1088 AND
1089 g_is_terminated = 'Y')
1090 THEN
1091 l_g_effective_date := p_effective_date -1;
1092 ELSE
1093 l_g_effective_date := p_effective_date;
1094 END IF;
1095 --For Bug 6071527 End
1096
1097 debug('p_effective_date: '||p_effective_date);
1098 debug('l_g_effective_date: '||l_g_effective_date);
1099 debug('g_is_terminated: '||g_is_terminated);
1100
1101 OPEN get_wps_percent_cont_cut
1102 (p_assignment_id => p_assignment_id
1103 ,p_effective_date => l_g_effective_date --p_effective_date
1104 ,p_element_type_id => g_pension_element_type_id
1105 ,p_input_value_name=> 'Contribution Percent'
1106 );
1107 FETCH get_wps_percent_cont_cut INTO wps_percent,l_element_entry_id;
1108 IF get_wps_percent_cont_cut%NOTFOUND
1109 THEN
1110 wps_percent := '0';
1111 debug('get_wps_percent_cont_cut NOTFOUND');
1112 END IF;
1113 CLOSE get_wps_percent_cont_cut;
1114
1115 debug('wps_percent: '||wps_percent);
1116
1117 --For Bug 6071527
1118
1119 OPEN get_wps_byb_percent_cont(p_effective_date => l_g_effective_date --p_effective_date
1120 ,p_assignment_id => p_assignment_id
1121 ,p_input_value_name => 'Contribution Percent'
1122 ,p_scheme_name => g_pension_scheme_name
1123 );
1124 FETCH get_wps_byb_percent_cont into wps_byb_percent;
1125 IF get_wps_byb_percent_cont%NOTFOUND
1126 THEN
1127 wps_byb_percent := '0';
1128 END IF;
1129 CLOSE get_wps_byb_percent_cont;
1130
1131 debug('wps_byb_percent: '||wps_byb_percent);
1132 wps_percent := wps_percent + wps_byb_percent;
1133 --For Bug 6071527 End
1134 END IF;
1135 debug('wps_percent: '||wps_percent);
1136 debug_exit(l_proc_name);
1137 RETURN wps_percent;
1138 END get_wpsPercent;
1139
1140 -- ----------------------------------------------------------------------------
1141 -- |------------------------< get_start_end_date >-------------------------|
1142 -- ----------------------------------------------------------------------------
1143 FUNCTION get_start_end_date
1144 (p_business_group_id IN NUMBER
1145 ,p_effective_date IN DATE
1146 ,p_assignment_id IN NUMBER
1147 ,p_effective_start_date OUT NOCOPY VARCHAR2
1148 ,p_effective_end_date OUT NOCOPY VARCHAR2
1149 )
1150 RETURN NUMBER IS
1151 l_proc_name VARCHAR2(61):=
1152 g_proc_name||'get_start_end_date';
1153 l_value number;
1154 l_effective_start_date VARCHAR2(60);
1155 l_effective_end_date VARCHAR2(60);
1156 l_element_entry_id NUMBER;
1157 l_chg_surrogate_key NUMBER;
1158 l_update_type VARCHAR2(5);
1159 l_curr_evt_index NUMBER;
1160 l_effective_date DATE;
1161 wps_percent VARCHAR2(60);
1162 opt_out_date VARCHAR2(60);
1163 l_start_date DATE;
1164 l_end_date DATE;
1165 l_return NUMBER;
1166 l_flag BOOLEAN := TRUE; --To check l_element_id.
1167
1168 -- For Bug 5998123
1169 l_dated_table_id NUMBER;
1170 l_chg_table VARCHAR2(61);
1171 l_chg_column_name VARCHAR2(61);
1172
1173 --For Bug 6071527
1174 l_g_effective_date DATE;
1175
1176 BEGIN
1177 debug_enter(l_proc_name);
1178 IF g_current_run = 'PERIODIC'
1179 THEN
1180 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
1181 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
1182 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1183 l_update_type := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
1184
1185 -- For Bug 5998123
1186 l_dated_table_id := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
1187 l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
1188 l_chg_table := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
1189
1190
1191 --debug('g_pay_proc_evt_tab: '||g_pay_proc_evt_tab);
1192 debug('l_update_type:-'||l_update_type);
1193
1194 --For Bug 6071527
1195 IF l_element_type_id <> g_pension_element_type_id and l_update_type = 'E'
1196 and l_end_date_basic_ele = 'N'
1197 THEN
1198 l_g_effective_date := p_effective_date + 1;
1199 ELSE
1200 l_g_effective_date := p_effective_date;
1201 END IF;
1202 --For Bug 6071527 End
1203
1204 p_effective_start_date := to_char(l_g_effective_date,'DD/MM/YYYY');
1205 p_effective_end_date := NULL;
1206
1207
1208 IF (l_update_type = 'E' AND l_element_type_id = g_pension_element_type_id) --For Bug 6071527
1209 or g_is_terminated = 'Y'
1210 THEN
1211 OPEN get_wps_percent_cont_per
1212 (p_element_entry_id => l_chg_surrogate_key
1213 ,p_effective_date => p_effective_date
1214 ,p_input_value_name => 'Opt Out Date'
1215 );
1216 FETCH get_wps_percent_cont_per INTO opt_out_date;
1217 IF get_wps_percent_cont_per%NOTFOUND
1218 THEN
1219 p_effective_end_date := to_char(p_effective_date,'DD/MM/YYYY');
1220 ELSIF opt_out_date IS NULL
1221 THEN
1222 p_effective_end_date := to_char(p_effective_date,'DD/MM/YYYY');
1223 ELSE
1224 p_effective_end_date := to_char(least(p_effective_date,TO_DATE(SUBSTR(opt_out_date,1,10),'yyyy/mm/dd')),'DD/MM/YYYY');
1225 END IF;
1226 CLOSE get_wps_percent_cont_per;
1227 p_effective_start_date := p_effective_end_date;
1228
1229 END IF;
1230
1231 -- For Bug 5998123
1232
1233 IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
1234 AND
1235 l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
1236 AND
1237 p_effective_start_date = p_effective_end_date
1238 AND
1239 g_is_terminated = 'Y')
1240 THEN
1241 p_effective_start_date := to_char(to_date(p_effective_start_date,'DD/MM/YYYY')-1,'DD/MM/YYYY');
1242 p_effective_end_date := p_effective_start_date;
1243
1244 END IF;
1245
1246
1247 ELSIF g_current_run = 'CUTOVER'
1248 THEN
1249 p_effective_end_date := NULL;
1250 debug('p_business_group_id'||p_business_group_id,1);
1251 debug('p_effective_date'||p_effective_date,1);
1252 debug('p_assignment_id'|| p_assignment_id,1);
1253 debug('g_pension_element_type_id'||g_pension_element_type_id,1);
1254 OPEN get_wps_percent_cont_cut
1255 (p_assignment_id => p_assignment_id
1256 ,p_effective_date => p_effective_date
1257 ,p_element_type_id => g_pension_element_type_id
1258 ,p_input_value_name=> 'Contribution Percent'
1259 );
1260 FETCH get_wps_percent_cont_cut INTO wps_percent,l_element_entry_id;
1261 IF get_wps_percent_cont_cut%NOTFOUND
1262 THEN
1263 p_effective_start_date := NULL;
1264 debug('get_wps_percent_cont_cut NOTFOUND');
1265
1266 ELSE
1267 debug('get_wps_percent_cont_cut FOUND');
1268 OPEN csr_get_start_date_cut
1269 (p_element_entry_id => l_element_entry_id
1270 );
1271 FETCH csr_get_start_date_cut INTO l_start_date;
1272 IF csr_get_start_date_cut%NOTFOUND
1273 THEN
1274 p_effective_start_date := NULL;
1275 debug('csr_get_start_date_cut NOTFOUND');
1276 ELSE
1277 p_effective_start_date := to_char(l_start_date,'DD/MM/YYYY');
1278 END IF;
1279 CLOSE csr_get_start_date_cut;
1280 END IF;
1281 CLOSE get_wps_percent_cont_cut;
1282 ELSE
1283 debug('g_current_run :'||g_current_run||'is not valid');
1284 END IF;
1285 debug_exit(l_proc_name);
1286 RETURN 0;
1287 END get_start_end_date;
1288
1289 -- ----------------------------------------------------------------------------
1290 -- |------------------------< wps_history_main >-------------------------|
1291 -- ----------------------------------------------------------------------------
1292
1293 FUNCTION wps_history_main
1294 (p_business_group_id IN NUMBER -- context
1295 ,p_effective_date IN DATE -- context
1296 ,p_assignment_id IN NUMBER -- context
1297 ,p_rule_parameter IN VARCHAR2 -- parameter
1298 ,p_output OUT NOCOPY VARCHAR2
1299 )
1300 RETURN NUMBER IS
1301 --
1302
1303 l_proc_name VARCHAR2(61):=
1304 g_proc_name||'wps_history_main';
1305 l_value number;
1306 l_effective_start_date VARCHAR2(60);
1307 l_effective_end_date VARCHAR2(60);
1308 l_element_entry_id NUMBER;
1309 l_chg_surrogate_key NUMBER;
1310 l_update_type VARCHAR2(5);
1311 l_curr_evt_index NUMBER;
1312 l_effective_date DATE;
1313 wps_percent VARCHAR2(60);
1314 l_start_date DATE;
1315 l_end_date DATE;
1316 l_return NUMBER;
1317 l_flag BOOLEAN := TRUE; --To check l_element_id.
1318 l_error NUMBER;
1319 l_element_name VARCHAR2(80);
1320 --
1321 BEGIN
1322
1323 debug_enter(l_proc_name);
1324
1325 -- switch on the trace
1326
1327 debug('Entering wps_history_main ...',0);
1328 debug('p_business_group_id'||p_business_group_id,1);
1329 debug('p_effective_date'||p_effective_date,1);
1330 debug('p_assignment_id'|| p_assignment_id,1);
1331 debug('p_rule_parameter'||p_rule_parameter,1);
1332
1333 -- select the function call based on the parameter being passed to the rule
1334 IF p_rule_parameter = 'WPSPercent'
1335 THEN
1336 debug('Fetching WPS percent Contribution',20);
1337 wps_percent := get_wpsPercent(p_business_group_id => p_business_group_id
1338 ,p_effective_date => p_effective_date
1339 ,p_assignment_id => p_assignment_id
1340 );
1341 IF wps_percent IS NULL
1342 THEN
1343 OPEN get_wps_element_name
1344 (p_element_type_id => g_pension_element_type_id
1345 );
1346 FETCH get_wps_element_name INTO l_element_name;
1347 IF get_wps_element_name%FOUND
1348 THEN
1349 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1350 (p_error_number => 94532
1351 ,p_error_text => 'BEN_94532_NO_ENTRY_VALUE'
1352 ,p_token1 => l_element_name
1353 ,p_token2 => 'CONTRIBUTION PERCENT'
1354 ,p_token3 => to_char(p_effective_date,'DD/MM/YYYY')
1355 );
1356 ELSE
1357 debug('Element Name Not Found',30);
1358 END IF;
1359 CLOSE get_wps_element_name;
1360 wps_percent := '0000000';
1361 ELSIF (fnd_number.canonical_to_number(wps_percent) > 100)
1362 THEN
1363 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1364 (p_error_number => 94533
1365 ,p_error_text => 'BEN_94533_IMPRACTICABLE_VALUE'
1366 ,p_token1 => wps_percent
1367 );
1368 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'0999D99')));
1369 ELSIF (fnd_number.canonical_to_number(wps_percent) < 0)
1370 THEN
1371 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1372 (p_error_number => 94533
1373 ,p_error_text => 'BEN_94533_IMPRACTICABLE_VALUE'
1374 ,p_token1 => wps_percent
1375 );
1376 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'099D99')));
1377 ELSE
1378 wps_percent := rtrim(ltrim(to_char(fnd_number.canonical_to_number(wps_percent),'0999D99')));
1379 END IF;
1380
1381 p_output := wps_percent;
1382 debug('p_output '||p_output);
1383
1384 ELSIF p_rule_parameter = 'WPSStartDate' THEN
1385 l_return := get_start_end_date(p_business_group_id => p_business_group_id
1386 ,p_effective_date => p_effective_date
1387 ,p_assignment_id => p_assignment_id
1388 ,p_effective_start_date => l_effective_start_date
1389 ,p_effective_end_date => l_effective_end_date
1390 );
1391 p_output := l_effective_start_date;
1392 debug('p_output '||p_output);
1393 ELSIF p_rule_parameter = 'WPSEndDate' THEN
1394 l_return := get_start_end_date(p_business_group_id => p_business_group_id
1395 ,p_effective_date => p_effective_date
1396 ,p_assignment_id => p_assignment_id
1397 ,p_effective_start_date => l_effective_start_date
1398 ,p_effective_end_date => l_effective_end_date
1399 );
1400 p_output := l_effective_end_date;
1401 debug('p_output '||p_output);
1402 ELSE
1403 p_output := ' ';
1404 debug('p_output '||p_output);
1405 END IF;
1406
1407
1408 debug_exit(l_proc_name);
1409 RETURN 0;
1410
1411
1412 EXCEPTION
1413 WHEN others THEN
1414 IF SQLCODE <> hr_utility.hr_error_number
1415 THEN
1416 debug_others (l_proc_name, 10);
1417 IF g_debug
1418 THEN
1419 DEBUG ( 'Leaving: '
1420 || l_proc_name, -999);
1421 END IF;
1422 fnd_message.raise_error;
1423 ELSE
1424 RAISE;
1425 END IF;
1426
1427 END wps_history_main;
1428
1429 -- ----------------------------------------------------------------------------
1430 -- |----------------------< wps_post_processing >--------------------------|
1431 -- Description: This is the post-processing rule for the WPS History.
1432 -- ----------------------------------------------------------------------------
1433 FUNCTION wps_post_processing RETURN VARCHAR2
1434 IS
1435 l_proc_name varchar2(72) := g_proc_name||'.wps_post_processing';
1436 BEGIN
1437 debug_enter(l_proc_name);
1438
1439 --Raise extract exceptions which are stored while processing the data elements
1440 --PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions();
1441
1442 PQP_GB_PSI_FUNCTIONS.common_post_process(p_business_group_id => g_business_group_id);
1443 debug_exit(l_proc_name);
1444 return 'Y';
1445 EXCEPTION
1446 WHEN others THEN
1447 IF SQLCODE <> hr_utility.hr_error_number
1448 THEN
1449 debug_others (l_proc_name, 10);
1450 IF g_debug
1451 THEN
1452 DEBUG ( 'Leaving: '
1453 || l_proc_name, -999);
1454 END IF;
1455 fnd_message.raise_error;
1456 ELSE
1457 RAISE;
1458 END IF;
1459 END wps_post_processing;
1460 ------
1461 ------
1462 END PQP_GB_PSI_WPS_HISTORY;