[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_BASIC_DATA
Source
1 PACKAGE BODY PQP_GB_PSI_BASIC_DATA AS
2 -- /* $Header: pqpgbpsibas.pkb 120.5 2007/11/12 05:43:53 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 -- ----------------------------------------------------------------------------
14 -- |--------------------------------< debug >---------------------------------|
15 -- ----------------------------------------------------------------------------
16
17 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
18 IS
19
20 --
21 BEGIN
22 --
23
24 pqp_utilities.DEBUG (
25 p_trace_message => p_trace_message
26 ,p_trace_location => p_trace_location
27 );
28 --
29 END DEBUG;
30
31
32 -- This procedure is used for debug purposes
33 -- debug_enter checks the debug flag and sets the trace on/off
34 --
35 -- ----------------------------------------------------------------------------
36 -- |----------------------------< debug_enter >-------------------------------|
37 -- ----------------------------------------------------------------------------
38
39 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
40 IS
41 BEGIN
42 --
43 IF g_debug THEN
44 IF pqp_utilities.g_nested_level = 0 THEN
45 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
46 END IF;
47 pqp_utilities.debug_enter (
48 p_proc_name => p_proc_name
49 ,p_trace_on => p_trace_on
50 );
51 END IF;
52 --
53
54 END debug_enter;
55
56
57 -- This procedure is used for debug purposes
58 --
59 -- ----------------------------------------------------------------------------
60 -- |----------------------------< debug_exit >--------------------------------|
61 -- ----------------------------------------------------------------------------
62
63 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
64 IS
65 BEGIN
66 --
67 IF g_debug THEN
68 pqp_utilities.debug_exit (
69 p_proc_name => p_proc_name
70 ,p_trace_off => p_trace_off
71 );
72
73 IF pqp_utilities.g_nested_level = 0 THEN
74 hr_utility.trace_off;
75 END IF;
76 END IF;
77 --
78 END debug_exit;
79
80 -- This procedure is used for debug purposes
81 --
82 -- ----------------------------------------------------------------------------
83 -- |----------------------------< debug_others >------------------------------|
84 -- ----------------------------------------------------------------------------
85
86 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
87 IS
88 BEGIN
89 --
90 pqp_utilities.debug_others (
91 p_proc_name => p_proc_name
92 ,p_proc_step => p_proc_step
93 );
94 --
95 END debug_others;
96
97
98
99
100 -- This procedure is used to clear all cached global variables
101 --
102 -- ----------------------------------------------------------------------------
103 -- |----------------------------< clear_cache >-------------------------------|
104 -- ----------------------------------------------------------------------------
105 PROCEDURE clear_cache
106 IS
107 --
108 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
109 l_proc_step PLS_INTEGER;
110 --
111 BEGIN
112 --
113 IF g_debug
114 THEN
115 l_proc_step := 10;
116 debug_enter(l_proc_name);
117 END IF;
118
119 -- start clearing globals
120 g_business_group_id := NULL;
121 g_assignment_id := NULL;
122 g_person_id := NULL;
123 g_person_dtl := NULL;
124 g_assignment_dtl := NULL;
125 g_effective_date := NULL;
126 g_extract_type := NULL;
127
128 g_bank_detail_report_y_n := NULL;
129 --g_current_run := NULL;
130 g_altkey := NULL;
131
132 -- globals set by set_shared_globals
133 g_paypoint := NULL;
134 g_cutover_date := NULL;
135 g_ext_dfn_id := NULL;
136
137 --
138 g_marital_status_mapping.DELETE;
139
140
141 IF g_debug
142 THEN
143 debug_exit(l_proc_name);
144 END IF;
145 EXCEPTION
146 WHEN others THEN
147 IF SQLCODE <> hr_utility.hr_error_number
148 THEN
149 debug_others (l_proc_name, l_proc_step);
150 IF g_debug
151 THEN
152 DEBUG ( 'Leaving: '
153 || l_proc_name, -999);
154 END IF;
155 fnd_message.raise_error;
156 ELSE
157 RAISE;
158 END IF;
159 END clear_cache;
160
161
162
163 -- This procedure is used to show all events
164 --
165 -- ----------------------------------------------------------------------------
166 -- |----------------------------< show_events >-------------------------------|
167 -- ----------------------------------------------------------------------------
168 PROCEDURE show_events
169 IS
170 l_proc_name VARCHAR2(80) := g_proc_name || 'show_events';
171 BEGIN
172 debug_enter(l_proc_name);
173 IF g_pay_proc_evt_tab.COUNT > 0 THEN
174 debug('====== Detailed Output =======');
175 FOR i IN g_pay_proc_evt_tab.FIRST..g_pay_proc_evt_tab.LAST
176 LOOP
177 debug('----------');
178 debug('Record :'||i);
179 debug('----------');
180 debug('dated_table_id :'||g_pay_proc_evt_tab(i).dated_table_id ,20);
181 debug('datetracked_event :'||g_pay_proc_evt_tab(i).datetracked_event,20);
182 debug('surrogate_key :'||g_pay_proc_evt_tab(i).surrogate_key ,20);
183 debug('column_name :'||g_pay_proc_evt_tab(i).column_name ,20);
184 debug('update_type :'||g_pay_proc_evt_tab(i).update_type ,20);
185 debug('effective_date :'||to_char(g_pay_proc_evt_tab(i).effective_date,'DD/MM/YYYY'),20);
186 debug('old_value :'||g_pay_proc_evt_tab(i).old_value ,20);
187 debug('new_value :'||g_pay_proc_evt_tab(i).new_value ,20);
188 debug('change_values :'||g_pay_proc_evt_tab(i).change_values ,20);
189 debug('proration_type :'||g_pay_proc_evt_tab(i).proration_type ,20);
190 debug('change_mode :'||g_pay_proc_evt_tab(i).change_mode ,20);
191 END LOOP;
192 ELSE
193 debug('No Events',20);
194 END IF;
195 debug_exit(l_proc_name);
196 END show_events;
197
198
199
200 -- ----------------------------------------------------------------------------
201 -- |------------------------< set_basic_data_globals >----------------------------|
202 -- ----------------------------------------------------------------------------
203 PROCEDURE set_basic_data_globals
204 (p_business_group_id IN NUMBER
205 ,p_effective_date IN DATE
206 ,p_assignment_id IN NUMBER
207 )
208 IS
209 --
210
211 l_proc_name VARCHAR2(61):=
212 g_proc_name||'set_basic_data_globals';
213
214 l_person_id NUMBER := NULL;
215 l_bank_detail_report_y_n VARCHAR2(2) := NULL;
216 l_assignment_number VARCHAR2(30) := NULL;
217
218 -- table of records for configuration types
219
220 l_pay_point_config_value pqp_utilities.t_config_values;
221 l_cutover_date_config_value pqp_utilities.t_config_values;
222 l_bank_details_yn_config_value pqp_utilities.t_config_values;
223 l_config_value pqp_utilities.t_config_values;
224
225 i NUMBER;
226 l_index NUMBER;
227 --
228 BEGIN
229
230 debug_enter(l_proc_name);
231
232 debug('Entering set_basic_data_globals ...',10);
233 debug ('p_business_group_id:'||p_business_group_id);
234 debug ('p_effective_date:'||p_effective_date);
235 debug ('p_assignment_id:'||p_assignment_id);
236
237
238
239 -- fetch configuration value for bank_detail_reporting_y_n
240 debug('Fetching configuration value for bank details ...', 60);
241 pqp_utilities.get_config_type_values
242 ( p_configuration_type => 'PQP_GB_PENSERVER_BANKACC_DTLS'
243 ,p_business_group_id => p_business_group_id
244 ,p_legislation_code => NULL
245 ,p_tab_config_values => l_config_value
246 );
247
248 IF l_config_value.COUNT > 0 -- config value found
249 THEN
250 g_bank_detail_report_y_n := l_config_value(l_config_value.FIRST).pcv_information1; --
251 ELSE
252 debug('g_bank_detail_report_y_n was not found in the config level',65);
253 g_bank_detail_report_y_n := 'N';
254 END IF;
255
256
257 -- fetch configuration value for employment type mapping
258 debug('Fetching configuration value for marital status mapping ...', 40);
259
260 pqp_utilities.get_config_type_values
261 ( p_configuration_type => 'PQP_GB_PENSERVER_MAR_STAT_MAP'
262 ,p_business_group_id => p_business_group_id
263 ,p_legislation_code => NULL
264 ,p_tab_config_values => g_marital_status_mapping --caching in global
265 -- for future use
266 );
267
268 debug('Exiting set_basic_data_globals ...',60);
269 debug_exit(l_proc_name);
270
271 EXCEPTION
272 WHEN others THEN
273 IF SQLCODE <> hr_utility.hr_error_number
274 THEN
275 debug_others (l_proc_name, 10);
276 IF g_debug
277 THEN
278 DEBUG ( 'Leaving: '
279 || l_proc_name, -999);
280 END IF;
281 fnd_message.raise_error;
282 ELSE
283 RAISE;
284 END IF;
285
286 END set_basic_data_globals;
287
288
289
290 -- ----------------------------------------------------------------------------
291 -- |------------------------< chk_basic_data_cutover_crit >-------------------|
292 -- ----------------------------------------------------------------------------
293
294 FUNCTION chk_basic_data_cutover_crit
295 (p_business_group_id IN NUMBER
296 ,p_effective_date IN DATE
297 ,p_assignment_id IN NUMBER
298 )
299 RETURN VARCHAR2
300 IS
301 --
302 l_proc_name VARCHAR2(61):=
303 g_proc_name||'chk_basic_data_cutover_crit';
304
305 l_return VARCHAR2(1) := 'N';
306 --
307 BEGIN
308
309 -- trace
310
311 debug_enter(l_proc_name);
312
313 debug('Entering chk_basic_data_cutover_crit ...',10);
314 debug ('p_business_group_id:'||p_business_group_id);
315 debug ('p_assignment_id:'||p_assignment_id);
316
317 g_current_run := 'CUTOVER';
318
319 -- being called only once in complete extract run
320 IF g_business_group_id IS NULL THEN
321 -- clear the cached globals
322 clear_cache;
323
324 -- for trace switching ON/OFF
325 g_debug := PQP_GB_PSI_FUNCTIONS.check_debug(p_business_group_id);
326 -- setting shared globals
327 -- 1) paypoint
328 -- 2) cutover date
329 -- 3) extract def id
330 PQP_GB_PSI_FUNCTIONS.set_shared_globals
331 (p_business_group_id => p_business_group_id
332 ,p_paypoint => g_paypoint -- OUT
333 ,p_cutover_date => g_cutover_date -- OUT
334 ,p_ext_dfn_id => g_ext_dfn_id -- OUT
335 );
336
337 -- setting extract specific globals
338 -- (1) g_bank_detail_report_y_n = Y/N
339 set_basic_data_globals
340 (p_business_group_id => p_business_group_id
341 ,p_effective_date => p_effective_date
342 ,p_assignment_id => p_assignment_id
343 );
344
345 g_business_group_id := p_business_group_id;
346
347 debug('now raise setup exceptions ...',15);
348 -- raise setup errors and warnings
349 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
350
351 -- now clearing cache of assign_cat in basic criteria
352 --PQP_GB_PSI_FUNCTIONS.g_assign_category_mapping.DELETE;
353 END IF; -- shared and basic_data globals have been set
354
355
356 -- calling the basic criteria for this person assignment
357 l_return :=
358 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
359 (p_business_group_id => p_business_group_id
360 ,p_effective_date => p_effective_date
361 ,p_assignment_id => p_assignment_id
362 ,p_person_dtl => g_person_dtl
363 ,p_assignment_dtl => g_assignment_dtl
364 );
365
366 IF l_return <> 'N' -- no need to set alt_key for person not picked up
367 THEN
368 -- to ensure that this is called only once for an assignment
369 IF g_assignment_id IS NULL
370 OR
371 (
372 g_assignment_id IS NOT NULL and g_assignment_id <> p_assignment_id
373 ) THEN
374 -- put a fucntion here which is to be called only once per person
375 g_altkey :=
376 PQP_GB_PSI_FUNCTIONS.altkey;
377 --(p_assignment_number => g_assignment_dtl.assignment_number
378 --,p_paypoint => g_paypoint
379 --);
380 g_assignment_id := p_assignment_id;
381 debug('this is a new assignment, need to set globals',15);
382 ELSE
383 debug('this is the same assignment, NO need to set globals',15);
384 END IF;
385 END IF; -- l_return <> 'N'
386
387
388 debug_exit(l_proc_name);
389 return l_return;
390
391 EXCEPTION
392 WHEN others THEN
393 IF SQLCODE <> hr_utility.hr_error_number
394 THEN
395 debug_others (l_proc_name, 10);
396 IF g_debug
397 THEN
398 DEBUG ( 'Leaving: '
399 || l_proc_name, -999);
400 END IF;
401 fnd_message.raise_error;
402 ELSE
403 RAISE;
404 END IF;
405
406 END chk_basic_data_cutover_crit;
407
408
409
410
411 -- ----------------------------------------------------------------------------
412 -- |------------------------< chk_basic_data_periodic_crit >--------------------|
413 -- ----------------------------------------------------------------------------
414
415 FUNCTION chk_basic_data_periodic_crit
416 (p_business_group_id IN NUMBER
417 ,p_effective_date IN DATE
418 ,p_assignment_id IN NUMBER
419 )
420 RETURN VARCHAR2
421 IS
422
423 l_proc_name VARCHAR2(61):=
424 g_proc_name||'chk_basic_data_periodic_crit';
425
426 l_return VARCHAR2(1) := 'N';
427 l_curr_evt_index NUMBER;
428
429 BEGIN
430
431
432 debug_enter(l_proc_name);
433
434 debug('Entering chk_basic_data_periodic_crit ...',10);
435 debug ('p_business_group_id:'||p_business_group_id);
436 debug ('p_assignment_id:'||p_assignment_id);
437
438 debug ('g_business_group_id:'||g_business_group_id);
439
440 g_current_run := 'PERIODIC';
441
442 -- being called only once in complete extract run
443 IF g_business_group_id IS NULL THEN
444 -- clearing cache
445 clear_cache();
446
447 -- for switching trace ON/OFF
448 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
449 -- setting shared globals
450 -- 1) paypoint
451 -- 2) cutover date
452 -- 3) extract def id
453 PQP_GB_PSI_FUNCTIONS.set_shared_globals
454 (p_business_group_id => p_business_group_id
455 ,p_paypoint => g_paypoint
456 ,p_cutover_date => g_cutover_date
457 ,p_ext_dfn_id => g_ext_dfn_id
458 );
459
460 -- setting extract specific globals
461 -- (1) g_bank_detail_report_y_n = Y/N
462 set_basic_data_globals
463 (p_business_group_id => p_business_group_id
464 ,p_effective_date => p_effective_date
465 ,p_assignment_id => p_assignment_id
466 );
467
468 g_business_group_id := p_business_group_id;
469
470
471 debug('now raise setup exceptions ...',15);
472 -- raise setup errors and warnings
473 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions(p_extract_type => 'S');
474
475 END IF; -- shared and basic_data globals have been set
476
477
478
479
480 -- calling the basic criteria for this person assignment
481 l_return :=
482 PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
483 (p_business_group_id => p_business_group_id
484 ,p_effective_date => p_effective_date
485 ,p_assignment_id => p_assignment_id
486 ,p_person_dtl => g_person_dtl
487 ,p_assignment_dtl => g_assignment_dtl
488 );
489
490
491 -- set the global events table
492 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
493
494 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
495
496 debug('----------');
497 debug('Record :'||l_curr_evt_index);
498 debug('----------');
499 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
500 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
501 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
502 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
503 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
504 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
505 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
506 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
507 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
508 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
509 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
510 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
511
512 -- calling include_event
513 debug('Calling the common include event proc');
514
515 debug('include_event returned: '||l_return);
516
517 IF l_return = 'N' THEN
518 debug('Returning : '||l_return,20);
519 debug_exit(l_proc_name);
520 return l_return;
521 END IF; --IF l_include = 'N'
522
523 l_return := pqp_gb_psi_functions.include_event
524 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
525 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
526 );
527
528
529
530
531 IF l_return <> 'N' -- no need to set alt_key for person not picked up
532 THEN
533
534
535
536 -- to ensure that this is called only once for an assignment
537 IF g_assignment_id IS NULL
538 OR
539 (
540 g_assignment_id IS NOT NULL and g_assignment_id <> p_assignment_id
541 ) THEN
542 -- put a fucntion here which is to be called only once per person
543 g_altkey :=
544 PQP_GB_PSI_FUNCTIONS.altkey;
545 --(p_assignment_number => g_assignment_dtl.assignment_number
546 --,p_paypoint => g_paypoint
547 --);
548 g_assignment_id := p_assignment_id;
549
550 IF g_debug
551 THEN
552 -- only for debugging
553 show_events;
554 END IF;
555
556 debug('this is a new assignment, need to set globals',15);
557 ELSE
558 debug('this is the same assignment, NO need to set globals',15);
559 END IF;
560 END IF; -- l_return <> 'N'
561
562
563
564 debug('Exiting chk_basic_data_cutover_crit ...',20);
565 debug_exit(l_proc_name);
566 return l_return;
567
568
569 EXCEPTION
570 WHEN others THEN
571 IF SQLCODE <> hr_utility.hr_error_number
572 THEN
573 debug_others (l_proc_name, 10);
574 IF g_debug
575 THEN
576 DEBUG ( 'Leaving: '
577 || l_proc_name, -999);
578 END IF;
579 fnd_message.raise_error;
580 ELSE
581 RAISE;
582 END IF;
583
584 END chk_basic_data_periodic_crit;
585
586
587
588
589
590
591 -- ----------------------------------------------------------------------------
592 -- |------------------------< Location >--------------------------------------|
593 -- ----------------------------------------------------------------------------
594 FUNCTION location
595 (p_business_group_id IN NUMBER
596 ,p_effective_date IN DATE
597 ,p_assignment_id IN NUMBER
598 ,p_return IN OUT NOCOPY VARCHAR2
599 )
600 RETURN number IS
601 --
602 l_proc_name VARCHAR2(61):=
603 g_proc_name||'location';
604 l_location_code hr_location_extra_info.lei_information2%TYPE;
605 l_value NUMBER;
606 --
607 BEGIN
608
609 debug_enter(l_proc_name);
610
611 debug('Entering location ...',10);
612 debug ('p_business_group_id:'||p_business_group_id);
613 debug ('p_effective_date:'||p_effective_date);
614 debug ('p_assignment_id:'||p_assignment_id);
615
616
617 debug('Fetching location code ....',10);
618 -- check if location is present on this assignment
619 IF g_assignment_dtl.location_id IS NOT NULL
620 THEN
621 -- fetch location code for this location
622 OPEN csr_location_code
623 (p_location_id => g_assignment_dtl.location_id -- IN
624 );
625 FETCH csr_location_code into l_location_code;
626 IF csr_location_code%FOUND THEN
627 debug('l_location_code:' || l_location_code, 20);
628
629 IF NOT PQP_GB_PSI_FUNCTIONS.is_alphanumeric(p_string=> l_location_code)
630 THEN
631 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
632 (p_error_number => 94476
633 ,p_error_text => 'BEN_94476_INV_LOC_CODE'
634 ,p_token1 => p_effective_date
635 );
636 END IF;
637
638 p_return := l_location_code;
639 ELSE
640 --ERR : no location code found for this location
641 debug('ERROR!!! : no location code found for this location', 20);
642 -- store error for 'NO Location Code'
643 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
644 (p_error_number => 94475
645 ,p_error_text => 'BEN_94475_NO_LOCATION_CODE'
646 ,p_token1 => p_effective_date
647 );
648 -- p_return := ' ';
649 p_return := NULL;
650 END IF;
651 CLOSE csr_location_code;
652 ELSE -- no location on assignment
653 -- p_return := ' ';
654 p_return := NULL;
655 END IF;
656
657 -- Bugfix : 5378812
658 -- Due to the fact that the location code is stoed at the location EIT level,
659 -- there is added complication. We have the following cases now :
660 -- (1) 1 -> 1 : old_code to new_code
661 -- (2) 0 -> 1 : no_code to new_code
662 -- (3) 1 -> 0 : old_code to no_code
663 -- (4) 0 -> 0 : no_code to no_code (location event or non-location event)
664
665 -- for (1) and (2), report the new_code
666 -- for (3) and (4)
667 -- if location event, then
668 --> and case (3), report asterisk
669 --> and case (4), report spaces
670 -- if non-location event, then it is case (4), report spaces
671
672 IF p_return IS NULL
673 and
674 g_current_run = 'PERIODIC'
675 THEN -- this is case (3) + case (4)
676
677 debug('location_code:' || p_return, 30);
678
679 --
680 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_ASSIGNMENTS_F'
681 and
682 ben_ext_person.g_chg_pay_column = 'LOCATION_ID'
683 THEN -- this is checking location_event for case (3) and (4)
684
685 /*
686 ' debug('Location change event detected .. ', 40);
687 -- check if previous value was null, if yes, then spaces
688 IF ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value IS NULL
689 THEN -- previous value was null, so spaces
690 debug('Old Value : '
691 || ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value, 50);
692 p_return := ' ';
693 ELSE -- previous value was not null, report asterisk, so return NULL
694 debug('Old Value : '
695 || ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value, 60);
696 p_return := NULL;
697 END IF;
698 --
699 */
700 IF ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value IS NOT NULL
701 THEN
702 OPEN csr_location_code
703 (p_location_id
704 => ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value
705 );
706 FETCH csr_location_code into l_location_code;
707 IF csr_location_code%FOUND THEN
708 debug('Old Value : '
709 || ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value, 60);
710 p_return := NULL;
711
712 ELSE
713
714 debug('Old Value : '
715 || ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).old_value, 50);
716 p_return := ' ';
717 END IF;
718 CLOSE csr_location_code;
719 ELSE
720 p_return := ' ';
721 END IF;
722
723 ELSE -- else case implies a non-location event, report spaces
724 debug('Non-Location event .. ', 70);
725 p_return := ' ';
726 END IF;
727 --
728
729 END IF;
730
731
732 debug('p_return (location function)' || p_return, 30);
733 debug('Exiting location ...',40);
734
735 debug_exit(l_proc_name);
736 RETURN 0;
737
738 EXCEPTION
739 WHEN others THEN
740 IF SQLCODE <> hr_utility.hr_error_number
741 THEN
742 debug_others (l_proc_name, 10);
743 IF g_debug
744 THEN
745 DEBUG ( 'Leaving: '
746 || l_proc_name, -999);
747 END IF;
748 fnd_message.raise_error;
749 ELSE
750 RAISE;
751 END IF;
752
753 END location;
754
755
756
757 -- ----------------------------------------------------------------------------
758 -- |------------------------< age_verification_indicator >--------------------|
759 -- ----------------------------------------------------------------------------
760 FUNCTION age_verification_indicator
761 (p_business_group_id IN NUMBER
762 ,p_effective_date IN DATE
763 ,p_person_id IN NUMBER
764 ,p_return IN OUT NOCOPY VARCHAR2
765 )
766 RETURN number IS
767 --
768 l_proc_name VARCHAR2(61):=
769 g_proc_name||'age_verification_indicator';
770 l_age_verification_indicator per_all_people_f.date_employee_data_verified%TYPE;
771 --
772 BEGIN
773
774 debug_enter(l_proc_name);
775
776 debug('Entering age_verification_indicator ...',10);
777 debug ('p_business_group_id:'||p_business_group_id);
778 debug ('p_effective_date:'||p_effective_date);
779 debug ('p_person_id:'||p_person_id);
780
781 -- cursor to fetch employee data verification date
782 IF g_person_dtl.date_employee_data_verified IS NOT NULL
783 THEN
784 debug('verification date found :' || l_age_verification_indicator,30);
785 p_return := 'Y';
786 ELSE
787 debug('verification date not found, setting p_return to N',30);
788 p_return := 'N';
789 END IF;
790
791 debug('p_return (age_verification_indicator function)' || p_return, 40);
792 debug('Exiting age_verification_indicator ...',50);
793
794
795 debug_exit(l_proc_name);
796 RETURN 0;
797
798 EXCEPTION
799 WHEN others THEN
800 IF SQLCODE <> hr_utility.hr_error_number
801 THEN
802 debug_others (l_proc_name, 10);
803 IF g_debug
804 THEN
805 DEBUG ( 'Leaving: '
806 || l_proc_name, -999);
807 END IF;
808 fnd_message.raise_error;
809 ELSE
810 RAISE;
811 END IF;
812
813 END age_verification_indicator;
814
815
816 -- ----------------------------------------------------------------------------
817 -- |------------------------< person_decoration >-----------------------------|
818 -- ----------------------------------------------------------------------------
819 FUNCTION person_decoration
820 (p_business_group_id IN NUMBER
821 ,p_effective_date IN DATE
822 ,p_person_id IN NUMBER
823 ,p_return IN OUT NOCOPY VARCHAR2
824 )
825 RETURN number IS
826 --
827 l_proc_name VARCHAR2(61):=
828 g_proc_name||'person_decoration';
829 l_person_decoration per_all_people_f.honors%TYPE;
830 --
831 BEGIN
832
833 debug_enter(l_proc_name);
834
835 debug('Entering person_decoration ...',10);
836 debug ('p_business_group_id:'||p_business_group_id);
837 debug ('p_effective_date:'||p_effective_date);
838 debug ('p_person_id:'||p_person_id);
839
840 -- cursor to fetch person decoration (honors)
841 IF g_person_dtl.honors IS NOT NULL
842 THEN
843 debug('person decoration (honors) : ' || g_person_dtl.honors,30);
844 p_return := g_person_dtl.honors;
845 debug_exit(l_proc_name);
846 return 0;
847 ELSE
848 --ERR : no person decoration (honors) found
849 debug('person decoration (honors) not found ',30);
850 -- p_return := ' ';
851 p_return := NULL;
852 debug_exit(l_proc_name);
853 return -1;
854 END IF;
855
856 debug('p_return (person_decoration function)' || p_return, 40);
857 debug('Exiting person_decoration ...',50);
858 debug_exit(l_proc_name);
859
860 EXCEPTION
861 WHEN others THEN
862 IF SQLCODE <> hr_utility.hr_error_number
863 THEN
864 debug_others (l_proc_name, 10);
865 IF g_debug
866 THEN
867 DEBUG ( 'Leaving: '
868 || l_proc_name, -999);
869 END IF;
870 fnd_message.raise_error;
871 ELSE
872 RAISE;
873 END IF;
874
875 END person_decoration;
876
877
878
879 -- ----------------------------------------------------------------------------
880 -- |------------------------< title >-----------------------------------------|
881 -- ----------------------------------------------------------------------------
882 FUNCTION title
883 (p_business_group_id IN NUMBER
884 ,p_effective_date IN DATE
885 ,p_person_id IN NUMBER
886 ,p_return IN OUT NOCOPY VARCHAR2
887 )
888 RETURN number IS
889 --
890 l_proc_name VARCHAR2(61):=
891 g_proc_name||'title';
892 l_title VARCHAR2(30);
893 --
894 BEGIN
895
896 debug_enter(l_proc_name);
897
898 debug('Entering title ...',10);
899 debug ('p_business_group_id:'||p_business_group_id);
900 debug ('p_effective_date:'||p_effective_date);
901 debug ('p_person_id:'||p_person_id);
902
903 l_title := HR_GENERAL.DECODE_LOOKUP
904 (
905 p_lookup_type => 'TITLE'
906 ,p_lookup_code => g_person_dtl.title
907 );
908 IF l_title IS NULL THEN
909 -- p_return := '';
910 p_return := NULL;
911 ELSE
912 p_return := l_title;
913 END IF;
914
915 debug('p_return (title function)' || p_return, 40);
916 debug('Exiting title ...',50);
917 debug_exit(l_proc_name);
918 return 0;
919
920 EXCEPTION
921 WHEN others THEN
922 IF SQLCODE <> hr_utility.hr_error_number
923 THEN
924 debug_others (l_proc_name, 10);
925 IF g_debug
926 THEN
927 DEBUG ( 'Leaving: '
928 || l_proc_name, -999);
929 END IF;
930 fnd_message.raise_error;
931 ELSE
932 RAISE;
933 END IF;
934
935 END title;
936
937
938 -- ----------------------------------------------------------------------------
939 -- |------------------------< bank_account_details >--------------------------|
940 -- ----------------------------------------------------------------------------
941 FUNCTION bank_account_details
942 (p_business_group_id IN NUMBER
943 ,p_effective_date IN DATE
944 ,p_assignment_id IN NUMBER
945 ,p_rule_parameter IN VARCHAR2
946 ,p_return IN OUT NOCOPY VARCHAR2
947 )
948 RETURN number IS
949 --
950 l_proc_name VARCHAR2(61):=
951 g_proc_name||'bank_account_details';
952 --
953 BEGIN
954
955 debug_enter(l_proc_name);
956
957 debug('Entering bank_account_details ...',10);
958 debug ('p_business_group_id:'||p_business_group_id);
959 debug ('p_effective_date:'||p_effective_date);
960 debug ('p_assignment_id:'||p_assignment_id);
961 debug ('p_rule_parameter:'|| p_rule_parameter);
962
963 -- the first call to this function is for fetching sort code
964 -- we will now fetch all details, and cache them for remaining
965 -- bank detail function calls as well
966 IF g_bank_detail_report_y_n = 'Y' THEN -- details to be reported
967 debug('Configuration value based flag - Y',20);
968 IF p_rule_parameter = 'BankAccountSortCode' THEN
969
970 debug('Fetching BankAccountSortCode ... ',30);
971 OPEN csr_bank_details
972 (p_business_group_id => p_business_group_id -- IN
973 ,p_effective_date => p_effective_date -- IN
974 ,p_assignment_id => p_assignment_id -- IN
975 );
976 FETCH csr_bank_details into g_asg_bank_details;
977 IF csr_bank_details%FOUND THEN
978 g_bank_details_found := 'Y'; -- details found
979 p_return := g_asg_bank_details.segment3;
980 ELSE
981 g_bank_details_found := 'N';
982 -- p_return := ' ';
983 p_return := NULL;
984 END IF;
985 CLOSE csr_bank_details;
986
987 ELSIF p_rule_parameter = 'BankAccountNumber'
988 and g_bank_details_found = 'Y'
989 THEN
990 debug('Fetching BankAccountNumber ... ',30);
991 p_return := g_asg_bank_details.segment4;
992 ELSIF p_rule_parameter = 'BankAccountType'
993 and g_bank_details_found = 'Y'
994 THEN
995 debug('Fetching BankAccountType ... ',30);
996 p_return := g_asg_bank_details.segment6;
997 ELSIF p_rule_parameter = 'BuildingSocietyRollNumber'
998 and g_bank_details_found = 'Y'
999 THEN
1000 debug('Fetching BuildingSocietyRollNumber ... ',30);
1001 p_return := g_asg_bank_details.segment7;
1002 ELSE
1003 -- p_return := ' ';
1004 p_return := NULL;
1005 END IF;
1006
1007 ELSE
1008 -- p_return := ' ';
1009 p_return := NULL;
1010 END IF;
1011
1012 debug('p_return (bank_account_details function)' || p_return, 40);
1013 debug('BankAccountSortCode g_asg_bank_details.segment3 : ' || g_asg_bank_details.segment3,50);
1014 debug('BankAccountNumber g_asg_bank_details.segment4 : ' || g_asg_bank_details.segment4,50);
1015 debug('BankAccountType g_asg_bank_details.segment6 : ' || g_asg_bank_details.segment6,50);
1016 debug('BuildingSocietyRollNumber g_asg_bank_details.segment7 : ' || g_asg_bank_details.segment7,50);
1017
1018 debug('Exiting bank_account_details ...',10);
1019
1020 debug_exit(l_proc_name);
1021 RETURN 0;
1022
1023 EXCEPTION
1024 WHEN others THEN
1025 IF SQLCODE <> hr_utility.hr_error_number
1026 THEN
1027 debug_others (l_proc_name, 10);
1028 IF g_debug
1029 THEN
1030 DEBUG ( 'Leaving: '
1031 || l_proc_name, -999);
1032 END IF;
1033 fnd_message.raise_error;
1034 ELSE
1035 RAISE;
1036 END IF;
1037
1038 END bank_account_details;
1039
1040
1041
1042 -- ----------------------------------------------------------------------------
1043 -- |------------------------< multiple_appointment_indicator >----------------|
1044 -- ----------------------------------------------------------------------------
1045 FUNCTION multiple_appointment_indicator
1046 (p_business_group_id IN NUMBER
1047 ,p_effective_date IN DATE
1048 ,p_person_id IN NUMBER
1049 ,p_return IN OUT NOCOPY VARCHAR2
1050 )
1051 RETURN number IS
1052 --
1053
1054 l_proc_name VARCHAR2(61):=
1055 g_proc_name||'multiple_appointment_indicator';
1056 l_assignment_count NUMBER := NULL;
1057 l_assignment_count_old_run NUMBER := NULL;
1058 --
1059 BEGIN
1060
1061 debug_enter(l_proc_name);
1062
1063 debug('Entering multiple_appointment_indicator ...',10);
1064 debug ('p_business_group_id:'||p_business_group_id);
1065 debug ('p_effective_date:'||p_effective_date);
1066 debug ('p_person_id:'||p_person_id);
1067
1068
1069 /*
1070 -- find previous run's number of assignments
1071 debug('Fetching number of assignments for previous run ....',20);
1072 OPEN csr_mult_assignment_y_n
1073 (p_business_group_id => p_business_group_id -- IN
1074
1075 -- IMP !! NOTE !! DATE !!??
1076 ,p_effective_date => p_effective_date -- IN
1077 ,p_person_id => p_person_id -- IN
1078 );
1079 FETCH csr_mult_assignment_y_n into l_assignment_count_old_run;
1080 IF csr_mult_assignment_y_n%NOTFOUND THEN
1081 --ERR : could not find assignment/person_id !!
1082 debug('ERROR!!! : no data returned for this person!!', 30);
1083 p_return := ' ';
1084 l_assignment_count_old_run := 0;
1085 ELSE
1086 p_return := ' ';
1087 END IF;
1088 CLOSE csr_mult_assignment_y_n;
1089
1090 debug('l_assignment_count_old_run : '|| l_assignment_count_old_run, 40);
1091
1092 --
1093 -- find current date's mult assignment indicator
1094 debug('Fetching number of assignments for current run/person ....',50);
1095 OPEN csr_mult_assignment_y_n
1096 (p_business_group_id => p_business_group_id -- IN
1097 ,p_effective_date => p_effective_date -- IN
1098 ,p_person_id => p_person_id -- IN
1099 );
1100 FETCH csr_mult_assignment_y_n into l_assignment_count;
1101 IF csr_mult_assignment_y_n%FOUND THEN
1102 --
1103 IF l_assignment_count > 1 THEN
1104 -- check if previous run also had mult_ind = 'Y'
1105 IF l_assignment_count_old_run > 1 AND g_current_run = 'PERIODIC' THEN
1106 debug('l_assignment_count_old_run > 1 and l_assignment_count > 1 - PERIODIC',60);
1107 p_return := ' '; -- no change, leave blank
1108 ELSE
1109 debug('l_assignment_count_old_run > 1 and l_assignment_count > 1 - CUTOVER',60);
1110 p_return := 'Y';
1111 END IF;
1112 ELSE
1113 -- check if previous run also had mult_ind = 'Y'
1114 IF l_assignment_count_old_run < 2 AND l_assignment_count_old_run > 0
1115 AND g_current_run = 'PERIODIC' THEN
1116 debug('l_assignment_count_old_run < 2 and l_assignment_count < 2 - PERIODIC',60);
1117 p_return := ' '; -- no change, leave blank
1118 ELSE
1119 debug('l_assignment_count_old_run < 2 and l_assignment_count < 2 - CUTOVER',60);
1120 p_return := 'N';
1121 END IF;
1122 END IF;
1123 --
1124 ELSE
1125 --ERR : could not find assignment/person_id !!
1126 debug('could not find assignment/person_id !!',65);
1127 p_return := ' ';
1128 END IF;
1129
1130 CLOSE csr_mult_assignment_y_n;
1131 */
1132
1133
1134 --
1135 -- find current date's mult assignment indicator
1136 debug('Fetching number of assignments for current run/person ....',50);
1137 OPEN csr_mult_assignment_y_n
1138 (p_business_group_id => p_business_group_id -- IN
1139 ,p_effective_date => p_effective_date -- IN
1140 ,p_person_id => p_person_id -- IN
1141 );
1142 FETCH csr_mult_assignment_y_n into l_assignment_count;
1143 IF csr_mult_assignment_y_n%FOUND THEN
1144 --
1145 IF l_assignment_count > 1 THEN
1146 -- check if previous run also had mult_ind = 'Y'
1147 p_return := 'Y';
1148 ELSE
1149 p_return := 'N';
1150 END IF;
1151 --
1152 ELSE
1153 --ERR : could not find assignment/person_id !!
1154 debug('could not find assignment/person_id !!',65);
1155 -- p_return := ' ';
1156 p_return := NULL;
1157 END IF;
1158
1159 CLOSE csr_mult_assignment_y_n;
1160
1161 debug('p_return (multiple_appointment_indicator function)' || p_return, 70);
1162 debug('Exitng multiple_appointment_indicator ...',80);
1163
1164 debug_exit(l_proc_name);
1165 RETURN 0;
1166
1167 EXCEPTION
1168 WHEN others THEN
1169 IF SQLCODE <> hr_utility.hr_error_number
1170 THEN
1171 debug_others (l_proc_name, 10);
1172 IF g_debug
1173 THEN
1174 DEBUG ( 'Leaving: '
1175 || l_proc_name, -999);
1176 END IF;
1177 fnd_message.raise_error;
1178 ELSE
1179 RAISE;
1180 END IF;
1181
1182 END multiple_appointment_indicator;
1183
1184
1185
1186
1187 -- ----------------------------------------------------------------------------
1188 -- |------------------------< spouse_date_of_birth >---------------------------|
1189 -- ----------------------------------------------------------------------------
1190 FUNCTION spouse_date_of_birth
1191 (p_business_group_id IN NUMBER
1192 ,p_effective_date IN DATE
1193 ,p_person_id IN NUMBER
1194 ,p_return IN OUT NOCOPY VARCHAR2
1195 )
1196 RETURN number IS
1197 --
1198
1199 l_proc_name VARCHAR2(61):=
1200 g_proc_name||'spouse_date_of_birth';
1201 l_spouse_date_of_birth per_all_people_f.date_of_birth%TYPE;
1202 --
1203 BEGIN
1204
1205 debug_enter(l_proc_name);
1206
1207 debug('Entering spouse_date_of_birth ...',10);
1208 debug ('p_business_group_id:'||p_business_group_id);
1209 debug ('p_effective_date:'||p_effective_date);
1210 debug ('p_person_id:'||p_person_id);
1211
1212 -- cursor to fetch spouse_date_of_birth
1213 debug('Fetching spouse_date_of_birth ....',20);
1214 OPEN csr_spouse_dob
1215 (p_business_group_id => p_business_group_id -- IN
1216 ,p_effective_date => p_effective_date -- IN
1217 ,p_person_id => p_person_id -- IN
1218 );
1219 FETCH csr_spouse_dob into l_spouse_date_of_birth;
1220 IF csr_spouse_dob%FOUND THEN
1221 debug('l_spouse_date_of_birth : ' || l_spouse_date_of_birth,30);
1222 p_return := to_char(l_spouse_date_of_birth,'DD/MM/YYYY');
1223 ELSE
1224 --ERR : no spouse date
1225 debug('l_spouse_date_of_birth not found ',30);
1226 -- p_return := ' ';
1227 p_return := NULL;
1228 END IF;
1229 CLOSE csr_spouse_dob;
1230
1231 debug('p_return (spouse_date_of_birth function)' || p_return, 40);
1232 debug('Exiting spouse_date_of_birth ...',50);
1233
1234 debug_exit(l_proc_name);
1235 RETURN 0;
1236
1237 EXCEPTION
1238 WHEN others THEN
1239 IF SQLCODE <> hr_utility.hr_error_number
1240 THEN
1241 debug_others (l_proc_name, 10);
1242 IF g_debug
1243 THEN
1244 DEBUG ( 'Leaving: '
1245 || l_proc_name, -999);
1246 END IF;
1247 fnd_message.raise_error;
1248 ELSE
1249 RAISE;
1250 END IF;
1251
1252 END spouse_date_of_birth;
1253
1254
1255 -- ----------------------------------------------------------------------------
1256 -- |------------------------< marital_status >--------------------------------|
1257 -- ----------------------------------------------------------------------------
1258 FUNCTION marital_status
1259 (p_business_group_id IN NUMBER
1260 ,p_effective_date IN DATE
1261 ,p_person_id IN NUMBER
1262 ,p_return IN OUT NOCOPY VARCHAR2
1263 )
1264 RETURN number IS
1265 --
1266
1267 l_proc_name VARCHAR2(61):=
1268 g_proc_name||'marital_status';
1269 l_marital_status varchar2(2) := NULL;
1270 l_index NUMBER;
1271 --
1272 BEGIN
1273
1274 debug_enter(l_proc_name);
1275
1276 debug('Entering csr_marital_status ...',10);
1277 debug ('p_business_group_id:'||p_business_group_id);
1278 debug ('p_effective_date:'||p_effective_date);
1279 debug ('p_person_id:'||p_person_id);
1280
1281
1282
1283 -- check that marital_status is not null
1284 IF g_person_dtl.marital_status IS NOT NULL
1285 THEN
1286 debug('g_person_dtl.marital_status : ' || g_person_dtl.marital_status,50);
1287
1288 -- search thru the mapping for this marital status
1289 FOR i IN 1..g_marital_status_mapping.COUNT
1290 LOOP
1291
1292 IF i=1 THEN -- finding next index
1293 l_index:=g_marital_status_mapping.FIRST;
1294 ELSE
1295 l_index:=g_marital_status_mapping.NEXT(l_index);
1296 END IF;
1297
1298 debug('g_marital_status_mapping('||l_index||').pcv_information1 : '
1299 || g_marital_status_mapping(l_index).pcv_information1);
1300 debug('g_marital_status_mapping('||l_index||').pcv_information2 : '
1301 || g_marital_status_mapping(l_index).pcv_information2);
1302
1303 -- start comparing
1304 IF g_person_dtl.marital_status =
1305 g_marital_status_mapping(l_index).pcv_information1 -- match found!!
1306 THEN
1307 l_marital_status := g_marital_status_mapping(l_index).pcv_information2;
1308
1309 debug('l_marital_status : '|| l_marital_status,65);
1310 debug('g_marital_status_mapping('||l_index||').pcv_information2 : '
1311 || g_marital_status_mapping(l_index).pcv_information2,66);
1312 ELSE
1313 debug('Not a match !!',70);
1314 END IF;
1315
1316 -- is still NULL implies that no match was found
1317 IF l_marital_status IS NULL
1318 THEN
1319 l_marital_status := 'U';
1320 END IF;
1321
1322 END LOOP; -- end of FOR loop
1323
1324 ELSE -- g_person_dtl.marital_status IS NOT NULL
1325 l_marital_status := 'U';
1326 END IF;
1327
1328 p_return := l_marital_status;
1329
1330 debug('p_return (marital_status function)' || p_return, 40);
1331 debug('Exiting marital_status ...',50);
1332
1333 debug_exit(l_proc_name);
1334 RETURN 0;
1335
1336 EXCEPTION
1337 WHEN others THEN
1338 IF SQLCODE <> hr_utility.hr_error_number
1339 THEN
1340 debug_others (l_proc_name, 10);
1341 IF g_debug
1342 THEN
1343 DEBUG ( 'Leaving: '
1344 || l_proc_name, -999);
1345 END IF;
1346 fnd_message.raise_error;
1347 ELSE
1348 RAISE;
1349 END IF;
1350
1351 END marital_status;
1352
1353 -- ----------------------------------------------------------------------------
1354 -- |------------------------< basic_extract_main >----------------------------|
1355 -- ----------------------------------------------------------------------------
1356
1357 FUNCTION basic_extract_main
1358 (p_business_group_id IN NUMBER -- context
1359 ,p_effective_date IN DATE -- context
1360 ,p_assignment_id IN NUMBER -- context
1361 ,p_rule_parameter IN VARCHAR2 -- parameter
1362 ,p_output OUT NOCOPY VARCHAR2
1363 )
1364 RETURN number IS
1365 --
1366
1367 l_proc_name VARCHAR2(61):=
1368 g_proc_name||'basic_extract_main';
1369 l_value number;
1370 l_effective_date DATE;
1371 --
1372 BEGIN
1373
1374 debug_enter(l_proc_name);
1375
1376 -- switch on the trace
1377
1378 debug('Entering basic_extract_main ...',0);
1379 debug('p_business_group_id'||p_business_group_id,1);
1380 debug('p_effective_date'||p_effective_date,1);
1381 debug('p_assignment_id'|| p_assignment_id,1);
1382 debug('p_rule_parameter'||p_rule_parameter,1);
1383
1384
1385 l_effective_date := p_effective_date;
1386
1387 -- select the function call based on the parameter being passed to the rule
1388 IF p_rule_parameter = 'Location' THEN
1389 debug('About to enter location',20);
1390 l_value := location
1391 (p_business_group_id => p_business_group_id -- IN
1392 ,p_effective_date => l_effective_date -- IN
1393 ,p_assignment_id => p_assignment_id -- IN
1394 ,p_return => p_output -- OUT
1395 );
1396
1397 ELSIF p_rule_parameter = 'AgeVerificationIndicator' THEN
1398 debug('About to enter age_verification_indicator function',30);
1399 l_value := age_verification_indicator
1400 (p_business_group_id => p_business_group_id -- IN
1401 ,p_effective_date => l_effective_date -- IN
1402 ,p_person_id => g_person_dtl.person_id -- IN
1403 ,p_return => p_output -- OUT
1404 );
1405
1406 ELSIF p_rule_parameter = 'PersonDecoration' THEN
1407 debug('About to enter Person_decoration',40);
1408 l_value := person_decoration
1409 (p_business_group_id => p_business_group_id -- IN
1410 ,p_effective_date => l_effective_date -- IN
1411 ,p_person_id => g_person_dtl.person_id -- IN
1412 ,p_return => p_output -- OUT
1413 );
1414
1415 ELSIF p_rule_parameter = 'BankAccountSortCode' THEN
1416 debug('About to enter bank_account_details - BankAccountSortCode',40);
1417
1418 -- g_asg_bank_details.DELETE;
1419
1420 l_value := bank_account_details
1421 (p_business_group_id => p_business_group_id -- IN
1422 ,p_effective_date => l_effective_date -- IN
1423 ,p_assignment_id => p_assignment_id -- IN
1424 ,p_rule_parameter => p_rule_parameter -- IN
1425 ,p_return => p_output -- OUT
1426 );
1427
1428 ELSIF p_rule_parameter = 'BankAccountNumber' THEN
1429 debug('About to enter bank_account_details - BankAccountNumber',40);
1430 l_value := bank_account_details
1431 (p_business_group_id => p_business_group_id -- IN
1432 ,p_effective_date => l_effective_date -- IN
1433 ,p_assignment_id => p_assignment_id -- IN
1434 ,p_rule_parameter => p_rule_parameter -- IN
1435 ,p_return => p_output -- OUT
1436 );
1437
1438 ELSIF p_rule_parameter = 'BankAccountType' THEN
1439 debug('About to enter bank_account_details - BankAccountType',40);
1440 l_value := bank_account_details
1441 (p_business_group_id => p_business_group_id -- IN
1442 ,p_effective_date => l_effective_date -- IN
1443 ,p_assignment_id => p_assignment_id -- IN
1444 ,p_rule_parameter => p_rule_parameter -- IN
1445 ,p_return => p_output -- OUT
1446 );
1447
1448 ELSIF p_rule_parameter = 'BuildingSocietyRollNumber' THEN
1449 debug('About to enter bank_account_details - BuildingSocietyRollNumber',40);
1450 l_value := bank_account_details
1451 (p_business_group_id => p_business_group_id -- IN
1452 ,p_effective_date => l_effective_date -- IN
1453 ,p_assignment_id => p_assignment_id -- IN
1454 ,p_rule_parameter => p_rule_parameter -- IN
1455 ,p_return => p_output -- OUT
1456 );
1457
1458 ELSIF p_rule_parameter = 'MultipleAppointmentIndicator' THEN
1459 debug('About to enter Multiple_Appointment_Indicator ',40);
1460
1461 l_value := multiple_appointment_indicator
1462 (p_business_group_id => p_business_group_id -- IN
1463 ,p_effective_date => l_effective_date -- IN
1464 ,p_person_id => g_person_dtl.person_id -- IN
1465 ,p_return => p_output -- OUT
1466 );
1467
1468 ELSIF p_rule_parameter = 'SpouseDOB' THEN
1469 debug('About to enter spouse_date_of_birth',40);
1470 l_value := spouse_date_of_birth
1471 (p_business_group_id => p_business_group_id -- IN
1472 ,p_effective_date => l_effective_date -- IN
1473 ,p_person_id => g_person_dtl.person_id -- IN
1474 ,p_return => p_output -- OUT
1475 );
1476
1477 ELSIF p_rule_parameter = 'MarStatus' THEN
1478 debug('About to enter MarStatus',40);
1479 l_value := marital_status
1480 (p_business_group_id => p_business_group_id -- IN
1481 ,p_effective_date => l_effective_date -- IN
1482 ,p_person_id => g_person_dtl.person_id -- IN
1483 ,p_return => p_output -- OUT
1484 );
1485
1486 ELSIF p_rule_parameter = 'Title' THEN
1487 debug('About to enter Title',40);
1488 l_value := title
1489 (p_business_group_id => p_business_group_id -- IN
1490 ,p_effective_date => l_effective_date -- IN
1491 ,p_person_id => g_person_dtl.person_id -- IN
1492 ,p_return => p_output -- OUT
1493 );
1494
1495 ELSIF p_rule_parameter = 'PayPoint' THEN
1496 p_output := g_paypoint;
1497
1498 ELSIF p_rule_parameter = 'AltKey' THEN
1499 p_output := g_altkey;
1500
1501 ELSIF p_rule_parameter = 'FirstForeName' THEN
1502 -- check if first name is NULL
1503 -- anshghos: 115.4
1504 IF g_person_dtl.first_name IS NULL
1505 THEN
1506 -- store error for 'NO First Name'
1507 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1508 (p_error_number => 94474
1509 ,p_error_text => 'BEN_94474_NO_FIRST_NAME'
1510 );
1511 p_output := '';
1512 ELSE -- first name is not null
1513 p_output := g_person_dtl.first_name;
1514 END IF;
1515
1516 ELSIF p_rule_parameter = 'CurrentRun' THEN
1517 debug('g_current_run: '||g_current_run);
1518 -- p_output := g_current_run;
1519
1520 -- Bugfix : 5378812
1521 -- The "CurrentRun" data element decides whether asterisk (***) are to be
1522 -- reported for data elements or not. For cutover, asterisk are not to be reported.
1523 -- Also, in case of new hires, asterisk for missing data elements should not be reported.
1524 -- the logic below is to handle new_hires
1525 -- if new_hire, asterisk behaviour should be like "CUTOVER"
1526 IF g_current_run = 'PERIODIC'
1527 and
1528 ben_ext_person.g_chg_pay_table = 'PER_ALL_ASSIGNMENTS_F'
1529 and
1530 ben_ext_person.g_chg_update_type = 'I'
1531 THEN
1532 p_output := 'CUTOVER';
1533 ELSE
1534 p_output := g_current_run;
1535 END IF;
1536
1537 ELSE
1538 -- p_output := '';
1539 p_output := NULL;
1540 END IF;
1541
1542 debug('p_output: '||p_output);
1543 debug_exit(l_proc_name);
1544 RETURN 0;
1545
1546
1547 EXCEPTION
1548 WHEN others THEN
1549 IF SQLCODE <> hr_utility.hr_error_number
1550 THEN
1551 debug_others (l_proc_name, 10);
1552 IF g_debug
1553 THEN
1554 DEBUG ( 'Leaving: '
1555 || l_proc_name, -999);
1556 END IF;
1557 fnd_message.raise_error;
1558 ELSE
1559 RAISE;
1560 END IF;
1561
1562 END basic_extract_main;
1563
1564
1565
1566 -- ----------------------------------------------------------------------------
1567 -- |------------------------< basic_data_post_processing >---------------------|
1568 -- ----------------------------------------------------------------------------
1569
1570 FUNCTION basic_data_post_processing RETURN VARCHAR2
1571 IS
1572
1573 -- Variable Declaration
1574
1575 -- Rowtype Variable Declaration
1576
1577 l_proc_name VARCHAR2(61):=
1578 g_proc_name||'basic_data_post_processing';
1579
1580 BEGIN -- basic_data_post_proc_rule
1581
1582 debug_enter(l_proc_name);
1583
1584 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1585
1586 debug_exit(l_proc_name);
1587 RETURN 'Y';
1588
1589 EXCEPTION
1590 WHEN others THEN
1591 IF SQLCODE <> hr_utility.hr_error_number
1592 THEN
1593 debug_others (l_proc_name, 10);
1594 IF g_debug
1595 THEN
1596 DEBUG ( 'Leaving: '
1597 || l_proc_name, -999);
1598 END IF;
1599 fnd_message.raise_error;
1600 ELSE
1601 RAISE;
1602 END IF;
1603
1604 END basic_data_post_processing; -- basic_data_post_proc_rule
1605
1606 END PQP_GB_PSI_BASIC_DATA;