[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.12010000.3 2009/09/09 05:14:00 jvaradra ship $ */
3
4
5
6 -- Exceptions
7 hr_application_error exception;
8 pragma exception_init (hr_application_error, -20001);
9
10
11 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
12
13 -- ----------------------------------------------------------------------------
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 -- For Bug 8790100
551 -- Reset the global values for every new assignment.
552
553 g_title_change_exists := 'N';
554 g_honors_change_exists := 'N';
555 g_location_change_exists := 'N';
556 g_prevsur_change_exists := 'N';
557 g_midname_change_exists := 'N';
558
559 IF g_debug
560 THEN
561 -- only for debugging
562 show_events;
563 END IF;
564
565 debug('this is a new assignment, need to set globals',15);
566 ELSE
567 debug('this is the same assignment, NO need to set globals',15);
568 END IF;
569 END IF; -- l_return <> 'N'
570
571
572
573 debug('Exiting chk_basic_data_cutover_crit ...',20);
574 debug_exit(l_proc_name);
575 return l_return;
576
577
578 EXCEPTION
579 WHEN others THEN
580 IF SQLCODE <> hr_utility.hr_error_number
581 THEN
582 debug_others (l_proc_name, 10);
583 IF g_debug
584 THEN
585 DEBUG ( 'Leaving: '
586 || l_proc_name, -999);
587 END IF;
588 fnd_message.raise_error;
589 ELSE
590 RAISE;
591 END IF;
592
593 END chk_basic_data_periodic_crit;
594
595
596
597
598
599
600 -- ----------------------------------------------------------------------------
601 -- |------------------------< Location >--------------------------------------|
602 -- ----------------------------------------------------------------------------
603 FUNCTION location
604 (p_business_group_id IN NUMBER
605 ,p_effective_date IN DATE
606 ,p_assignment_id IN NUMBER
607 ,p_return IN OUT NOCOPY VARCHAR2
608 )
609 RETURN number IS
610 --
611 l_proc_name VARCHAR2(61):=
612 g_proc_name||'location';
613 l_location_code hr_location_extra_info.lei_information2%TYPE;
614 l_value NUMBER;
615 -- For bug 8790100
616 l_ret_location varchar2(30);
617 --
618 BEGIN
619
620 debug_enter(l_proc_name);
621
622 debug('Entering location ...',10);
623 debug ('p_business_group_id:'||p_business_group_id);
624 debug ('p_effective_date:'||p_effective_date);
625 debug ('p_assignment_id:'||p_assignment_id);
626 debug('Fetching location code ....',10);
627
628 --BEGIN FOR Bug 8790100
629
630 debug('g_assignment_dtl.location_id ....'||g_assignment_dtl.location_id,10);
631 -- check if location is present on this assignment
632
633 IF g_assignment_dtl.location_id IS NOT NULL
634 THEN
635
636 -- fetch location code for this location
637 OPEN csr_location_code
638 (p_location_id => g_assignment_dtl.location_id -- IN
639 );
640 FETCH csr_location_code into l_location_code;
641 IF csr_location_code%FOUND
642 THEN
643 debug('l_location_code:' || l_location_code, 20);
644
645 IF NOT PQP_GB_PSI_FUNCTIONS.is_alphanumeric(p_string=> l_location_code)
646 THEN
647 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
648 (p_error_number => 94476
649 ,p_error_text => 'BEN_94476_INV_LOC_CODE'
650 ,p_token1 => p_effective_date
651 );
652 END IF;
653
654 l_ret_location := l_location_code;
655 ELSE
656 --ERR : no location code found for this location
657 debug('ERROR!!! : no location code found for this location', 20);
658 -- store error for 'NO Location Code'
659 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
660 (p_error_number => 94475
661 ,p_error_text => 'BEN_94475_NO_LOCATION_CODE'
662 ,p_token1 => p_effective_date
663 );
664 l_ret_location := ' ';
665 END IF;
666 CLOSE csr_location_code;
667
668 ELSE -- no location on assignment
669
670 l_ret_location := ' '; --NULL;
671
672 END IF;
673
674 IF l_ret_location = ' '
675 THEN
676
677 IF g_current_run = 'PERIODIC'
678 THEN
679
680 IF g_location_change_exists = 'Y'
681 THEN
682 l_ret_location := '******';
683 ELSE
684
685 debug('location:' || l_ret_location, 30);
686 debug('ben_ext_person.g_chg_pay_table:' || ben_ext_person.g_chg_pay_table, 30);
687 debug('ben_ext_person.g_chg_pay_column:' || ben_ext_person.g_chg_pay_column, 30);
688 debug('ben_ext_person.g_chg_update_type:' || ben_ext_person.g_chg_update_type, 30);
689
690 --
691 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_ASSIGNMENTS_F'
692 AND ben_ext_person.g_chg_pay_column = 'LOCATION_ID'
693 AND ben_ext_person.g_chg_update_type <> 'I'
694 THEN -- this is checking location_event for case (3) and (4)
695
696 l_ret_location := '******';
697 g_location_change_exists := 'Y';
698
699 ELSE
700 l_ret_location := ' ';
701
702 END IF;
703
704 END IF;
705 ELSE
706 l_ret_location := '******';
707 END IF;
708 END IF;
709
710 p_return := l_ret_location;
711
712
713 debug('p_return (location function)' || p_return, 30);
714 debug('Exiting location ...',40);
715
716 debug_exit(l_proc_name);
717 RETURN 0;
718
719 EXCEPTION
720 WHEN others THEN
721 IF SQLCODE <> hr_utility.hr_error_number
722 THEN
723 debug_others (l_proc_name, 10);
724 IF g_debug
725 THEN
726 DEBUG ( 'Leaving: '
727 || l_proc_name, -999);
728 END IF;
729 fnd_message.raise_error;
730 ELSE
731 RAISE;
732 END IF;
733
734 END location;
735
736 -- ----------------------------------------------------------------------------
737 -- |------------------------< age_verification_indicator >--------------------|
738 -- ----------------------------------------------------------------------------
739 FUNCTION age_verification_indicator
740 (p_business_group_id IN NUMBER
741 ,p_effective_date IN DATE
742 ,p_person_id IN NUMBER
743 ,p_return IN OUT NOCOPY VARCHAR2
744 )
745 RETURN number IS
746 --
747 l_proc_name VARCHAR2(61):=
748 g_proc_name||'age_verification_indicator';
749 l_age_verification_indicator per_all_people_f.date_employee_data_verified%TYPE;
750 --
751 BEGIN
752
753 debug_enter(l_proc_name);
754
755 debug('Entering age_verification_indicator ...',10);
756 debug ('p_business_group_id:'||p_business_group_id);
757 debug ('p_effective_date:'||p_effective_date);
758 debug ('p_person_id:'||p_person_id);
759
760 -- cursor to fetch employee data verification date
761 IF g_person_dtl.date_employee_data_verified IS NOT NULL
762 THEN
763 debug('verification date found :' || l_age_verification_indicator,30);
764 p_return := 'Y';
765 ELSE
766 debug('verification date not found, setting p_return to N',30);
767 p_return := 'N';
768 END IF;
769
770 debug('p_return (age_verification_indicator function)' || p_return, 40);
771 debug('Exiting age_verification_indicator ...',50);
772
773
774 debug_exit(l_proc_name);
775 RETURN 0;
776
777 EXCEPTION
778 WHEN others THEN
779 IF SQLCODE <> hr_utility.hr_error_number
780 THEN
781 debug_others (l_proc_name, 10);
782 IF g_debug
783 THEN
784 DEBUG ( 'Leaving: '
785 || l_proc_name, -999);
786 END IF;
787 fnd_message.raise_error;
788 ELSE
789 RAISE;
790 END IF;
791
792 END age_verification_indicator;
793
794
795 -- ----------------------------------------------------------------------------
796 -- |------------------------< person_decoration >-----------------------------|
797 -- ----------------------------------------------------------------------------
798 FUNCTION person_decoration
799 (p_business_group_id IN NUMBER
800 ,p_effective_date IN DATE
801 ,p_person_id IN NUMBER
802 ,p_return IN OUT NOCOPY VARCHAR2
803 )
804 RETURN number IS
805 --
806 l_proc_name VARCHAR2(61):=
807 g_proc_name||'person_decoration';
808 l_person_decoration per_all_people_f.honors%TYPE;
809 -- For Bug 8790100
810 l_ret_person_decoration per_all_people_f.honors%TYPE;
811 --
812 BEGIN
813
814 debug_enter(l_proc_name);
815
816 debug('Entering person_decoration ...',10);
817 debug ('p_business_group_id:'||p_business_group_id);
818 debug ('p_effective_date:'||p_effective_date);
819 debug ('p_person_id:'||p_person_id);
820
821 -- BEGIN For Bug 8790100
822 -- cursor to fetch person decoration (honors)
823 IF g_person_dtl.honors IS NOT NULL
824 THEN
825 debug('person decoration (honors) : ' || g_person_dtl.honors,30);
826 l_ret_person_decoration := g_person_dtl.honors;
827 ELSE
828 --ERR : no person decoration (honors) found
829 debug('person decoration (honors) not found ',30);
830 l_ret_person_decoration := ' '; --NULL;
831 END IF;
832
833 IF l_ret_person_decoration = ' '
834 THEN
835
836 IF g_current_run = 'PERIODIC'
837 THEN
838
839 IF g_honors_change_exists = 'Y'
840 THEN
841 l_ret_person_decoration := '********';
842 ELSE
843
844 debug('honors:' || l_ret_person_decoration, 30);
845 debug('ben_ext_person.g_chg_pay_table:' || ben_ext_person.g_chg_pay_table, 30);
846 debug('ben_ext_person.g_chg_pay_column:' || ben_ext_person.g_chg_pay_column, 30);
847 debug('ben_ext_person.g_chg_update_type:' || ben_ext_person.g_chg_update_type, 30);
848
849 --
850 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_PEOPLE_F'
851 AND
852 ben_ext_person.g_chg_pay_column = 'HONORS'
853 AND
854 ben_ext_person.g_chg_update_type <> 'I'
855 THEN -- this is checking location_event for case (3) and (4)
856 l_ret_person_decoration := '********';
857 g_honors_change_exists := 'Y';
858 ELSE
859 l_ret_person_decoration := ' ';
860 END IF;
861
862 END IF;
863
864 ELSE
865
866 l_ret_person_decoration := '********';
867 END IF;
868 END IF;
869
870 p_return := l_ret_person_decoration;
871
872 debug('p_return (person_decoration function)' || p_return, 40);
873 debug('Exiting person_decoration ...',50);
874 debug_exit(l_proc_name);
875 return 0; -- For Bug 8790100
876
877 EXCEPTION
878 WHEN others THEN
879 IF SQLCODE <> hr_utility.hr_error_number
880 THEN
881 debug_others (l_proc_name, 10);
882 IF g_debug
883 THEN
884 DEBUG ( 'Leaving: '
885 || l_proc_name, -999);
886 END IF;
887 fnd_message.raise_error;
888 ELSE
889 RAISE;
890 END IF;
891
892 END person_decoration;
893
894
895
896 -- ----------------------------------------------------------------------------
897 -- |------------------------< title >-----------------------------------------|
898 -- ----------------------------------------------------------------------------
899 FUNCTION title
900 (p_business_group_id IN NUMBER
901 ,p_effective_date IN DATE
902 ,p_person_id IN NUMBER
903 ,p_return IN OUT NOCOPY VARCHAR2
904 )
905 RETURN number IS
906 --
907 l_proc_name VARCHAR2(61):=
908 g_proc_name||'title';
909 l_title VARCHAR2(30);
910 -- For bug 8790100
911 l_ret_title VARCHAR2(30);
912 --
913 BEGIN
914
915 debug_enter(l_proc_name);
916
917 debug('Entering title ...',10);
918 debug ('p_business_group_id:'||p_business_group_id);
919 debug ('p_effective_date:'||p_effective_date);
920 debug ('p_person_id:'||p_person_id);
921
922 l_title := HR_GENERAL.DECODE_LOOKUP
923 (
924 p_lookup_type => 'TITLE'
925 ,p_lookup_code => g_person_dtl.title
926 );
927
931 -- ELSE return space
928 -- BEGIN For bug 8790100
929 -- Firstly, check for the value, if Null then check if it is for PERIODIC/CUTOVER.
930 -- IF PERIODIC --> IF change in TITLE event then set the global to 'Y' and return asterisks
932 -- IF CUTOVER --> Return asterisks.
933
934 debug('p_return (title function)' || l_title, 40);
935
936 IF l_title IS NULL
937 THEN
938 l_ret_title := ' ';
939 ELSE
940 l_ret_title := l_title;
941 END IF;
942
943 debug('l_ret_title (title function)' || l_ret_title, 41);
944 debug('g_current_run' || g_current_run, 42);
945 debug('g_title_change_exists' || g_title_change_exists, 43);
946
947 IF l_ret_title = ' '
948 THEN
949
950 IF g_current_run = 'PERIODIC'
951 THEN
952
953 IF g_title_change_exists = 'Y'
954 THEN
955 l_ret_title := '******';
956 ELSE
957
958 debug('title:' || l_ret_title, 30);
959 debug('ben_ext_person.g_chg_pay_table:' || ben_ext_person.g_chg_pay_table, 44);
960 debug('ben_ext_person.g_chg_pay_column:' || ben_ext_person.g_chg_pay_column, 44);
961 debug('ben_ext_person.g_chg_update_type:' || ben_ext_person.g_chg_update_type, 44);
962 --
963 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_PEOPLE_F'
964 AND ben_ext_person.g_chg_pay_column = 'TITLE'
965 AND ben_ext_person.g_chg_update_type <> 'I'
966 THEN
967 l_ret_title := '******';
968 g_title_change_exists := 'Y';
969 ELSE
970 l_ret_title := ' ';
971 END IF;
972
973 END IF;
974 ELSE
975 l_ret_title := '******';
976 END IF;
977
978 END IF;
979
980 p_return := l_ret_title;
981
982 -- END For bug 8790100
983
984 debug('p_return (title function)' || p_return, 40);
985 debug('Exiting title ...',50);
986 debug_exit(l_proc_name);
987 return 0;
988
989 EXCEPTION
990 WHEN others THEN
991 IF SQLCODE <> hr_utility.hr_error_number
992 THEN
993 debug_others (l_proc_name, 10);
994 IF g_debug
995 THEN
996 DEBUG ( 'Leaving: '
997 || l_proc_name, -999);
998 END IF;
999 fnd_message.raise_error;
1000 ELSE
1001 RAISE;
1002 END IF;
1003
1004 END title;
1005
1006
1007 -- ----------------------------------------------------------------------------
1008 -- |------------------------< bank_account_details >--------------------------|
1009 -- ----------------------------------------------------------------------------
1010 FUNCTION bank_account_details
1011 (p_business_group_id IN NUMBER
1012 ,p_effective_date IN DATE
1013 ,p_assignment_id IN NUMBER
1014 ,p_rule_parameter IN VARCHAR2
1015 ,p_return IN OUT NOCOPY VARCHAR2
1016 )
1017 RETURN number IS
1018 --
1019 l_proc_name VARCHAR2(61):=
1020 g_proc_name||'bank_account_details';
1021 --
1022 BEGIN
1023
1024 debug_enter(l_proc_name);
1025
1026 debug('Entering bank_account_details ...',10);
1027 debug ('p_business_group_id:'||p_business_group_id);
1028 debug ('p_effective_date:'||p_effective_date);
1029 debug ('p_assignment_id:'||p_assignment_id);
1030 debug ('p_rule_parameter:'|| p_rule_parameter);
1031
1032 -- the first call to this function is for fetching sort code
1033 -- we will now fetch all details, and cache them for remaining
1034 -- bank detail function calls as well
1035 IF g_bank_detail_report_y_n = 'Y' THEN -- details to be reported
1036 debug('Configuration value based flag - Y',20);
1037 IF p_rule_parameter = 'BankAccountSortCode' THEN
1038
1039 debug('Fetching BankAccountSortCode ... ',30);
1040 OPEN csr_bank_details
1041 (p_business_group_id => p_business_group_id -- IN
1042 ,p_effective_date => p_effective_date -- IN
1043 ,p_assignment_id => p_assignment_id -- IN
1044 );
1045 FETCH csr_bank_details into g_asg_bank_details;
1046 IF csr_bank_details%FOUND THEN
1047 g_bank_details_found := 'Y'; -- details found
1048 p_return := g_asg_bank_details.segment3;
1049 ELSE
1050 g_bank_details_found := 'N';
1051 -- p_return := ' ';
1052 p_return := NULL;
1053 END IF;
1054 CLOSE csr_bank_details;
1055
1056 ELSIF p_rule_parameter = 'BankAccountNumber'
1057 and g_bank_details_found = 'Y'
1058 THEN
1059 debug('Fetching BankAccountNumber ... ',30);
1060 p_return := g_asg_bank_details.segment4;
1061 ELSIF p_rule_parameter = 'BankAccountType'
1062 and g_bank_details_found = 'Y'
1063 THEN
1064 debug('Fetching BankAccountType ... ',30);
1065 p_return := g_asg_bank_details.segment6;
1066 ELSIF p_rule_parameter = 'BuildingSocietyRollNumber'
1067 and g_bank_details_found = 'Y'
1068 THEN
1069 debug('Fetching BuildingSocietyRollNumber ... ',30);
1070 p_return := g_asg_bank_details.segment7;
1071 ELSE
1072 -- p_return := ' ';
1073 p_return := NULL;
1074 END IF;
1075
1076 ELSE
1077 -- p_return := ' ';
1078 p_return := NULL;
1079 END IF;
1080
1081 --Bug 8758650: This logic was added to suppress reporting the asterisks
1082 --in bank account fields when:
1083 --1)PenServer configuration for basic data has Report Bank Account
1084 -- Details option set to No.
1085 --2)Run is cutover and value is NULL
1086 --3)Run is periodic, value is NULL and it a new bank account or
1087 --the bank account has not changed
1088 IF g_bank_detail_report_y_n = 'N'
1089 THEN
1090 p_return := 'X';
1091 ELSE
1092 IF p_return IS NULL
1093 THEN
1094 IF g_current_run = 'CUTOVER'
1095 THEN
1096 p_return := 'X';
1097 ELSE --run is PERIODIC
1098 IF (ben_ext_person.g_chg_pay_table = 'PAY_PERSONAL_PAYMENT_METHODS_F'
1099 AND ben_ext_person.g_chg_update_type = 'I')
1100 OR ben_ext_person.g_chg_pay_table <> 'PAY_PERSONAL_PAYMENT_METHODS_F'
1101 THEN
1102 p_return := 'X';
1103 END IF;
1104 END IF;
1105 END IF;
1106 END IF;
1107
1108 debug('p_return (bank_account_details function)' || p_return, 40);
1109 debug('BankAccountSortCode g_asg_bank_details.segment3 : ' || g_asg_bank_details.segment3,50);
1110 debug('BankAccountNumber g_asg_bank_details.segment4 : ' || g_asg_bank_details.segment4,50);
1111 debug('BankAccountType g_asg_bank_details.segment6 : ' || g_asg_bank_details.segment6,50);
1112 debug('BuildingSocietyRollNumber g_asg_bank_details.segment7 : ' || g_asg_bank_details.segment7,50);
1113
1114 debug('Exiting bank_account_details ...',10);
1115
1116 debug_exit(l_proc_name);
1117 RETURN 0;
1118
1119 EXCEPTION
1120 WHEN others THEN
1121 IF SQLCODE <> hr_utility.hr_error_number
1122 THEN
1123 debug_others (l_proc_name, 10);
1124 IF g_debug
1125 THEN
1126 DEBUG ( 'Leaving: '
1127 || l_proc_name, -999);
1128 END IF;
1129 fnd_message.raise_error;
1130 ELSE
1131 RAISE;
1132 END IF;
1133
1134 END bank_account_details;
1135
1136
1137
1138 -- ----------------------------------------------------------------------------
1139 -- |------------------------< multiple_appointment_indicator >----------------|
1140 -- ----------------------------------------------------------------------------
1141 FUNCTION multiple_appointment_indicator
1142 (p_business_group_id IN NUMBER
1143 ,p_effective_date IN DATE
1144 ,p_person_id IN NUMBER
1145 ,p_return IN OUT NOCOPY VARCHAR2
1146 )
1147 RETURN number IS
1148 --
1149
1150 l_proc_name VARCHAR2(61):=
1151 g_proc_name||'multiple_appointment_indicator';
1152 l_assignment_count NUMBER := NULL;
1153 l_assignment_count_old_run NUMBER := NULL;
1154 --
1155 BEGIN
1156
1157 debug_enter(l_proc_name);
1158
1159 debug('Entering multiple_appointment_indicator ...',10);
1160 debug ('p_business_group_id:'||p_business_group_id);
1161 debug ('p_effective_date:'||p_effective_date);
1162 debug ('p_person_id:'||p_person_id);
1163
1164
1165 /*
1166 -- find previous run's number of assignments
1167 debug('Fetching number of assignments for previous run ....',20);
1168 OPEN csr_mult_assignment_y_n
1169 (p_business_group_id => p_business_group_id -- IN
1170
1171 -- IMP !! NOTE !! DATE !!??
1172 ,p_effective_date => p_effective_date -- IN
1173 ,p_person_id => p_person_id -- IN
1174 );
1175 FETCH csr_mult_assignment_y_n into l_assignment_count_old_run;
1176 IF csr_mult_assignment_y_n%NOTFOUND THEN
1177 --ERR : could not find assignment/person_id !!
1178 debug('ERROR!!! : no data returned for this person!!', 30);
1179 p_return := ' ';
1180 l_assignment_count_old_run := 0;
1181 ELSE
1182 p_return := ' ';
1183 END IF;
1184 CLOSE csr_mult_assignment_y_n;
1185
1186 debug('l_assignment_count_old_run : '|| l_assignment_count_old_run, 40);
1187
1188 --
1189 -- find current date's mult assignment indicator
1190 debug('Fetching number of assignments for current run/person ....',50);
1191 OPEN csr_mult_assignment_y_n
1192 (p_business_group_id => p_business_group_id -- IN
1193 ,p_effective_date => p_effective_date -- IN
1194 ,p_person_id => p_person_id -- IN
1195 );
1196 FETCH csr_mult_assignment_y_n into l_assignment_count;
1197 IF csr_mult_assignment_y_n%FOUND THEN
1198 --
1199 IF l_assignment_count > 1 THEN
1200 -- check if previous run also had mult_ind = 'Y'
1201 IF l_assignment_count_old_run > 1 AND g_current_run = 'PERIODIC' THEN
1202 debug('l_assignment_count_old_run > 1 and l_assignment_count > 1 - PERIODIC',60);
1203 p_return := ' '; -- no change, leave blank
1204 ELSE
1205 debug('l_assignment_count_old_run > 1 and l_assignment_count > 1 - CUTOVER',60);
1206 p_return := 'Y';
1207 END IF;
1208 ELSE
1209 -- check if previous run also had mult_ind = 'Y'
1210 IF l_assignment_count_old_run < 2 AND l_assignment_count_old_run > 0
1211 AND g_current_run = 'PERIODIC' THEN
1212 debug('l_assignment_count_old_run < 2 and l_assignment_count < 2 - PERIODIC',60);
1213 p_return := ' '; -- no change, leave blank
1214 ELSE
1215 debug('l_assignment_count_old_run < 2 and l_assignment_count < 2 - CUTOVER',60);
1216 p_return := 'N';
1217 END IF;
1218 END IF;
1219 --
1220 ELSE
1221 --ERR : could not find assignment/person_id !!
1222 debug('could not find assignment/person_id !!',65);
1223 p_return := ' ';
1224 END IF;
1225
1226 CLOSE csr_mult_assignment_y_n;
1227 */
1228
1229
1230 --
1231 -- find current date's mult assignment indicator
1232 debug('Fetching number of assignments for current run/person ....',50);
1233 OPEN csr_mult_assignment_y_n
1234 (p_business_group_id => p_business_group_id -- IN
1235 ,p_effective_date => p_effective_date -- IN
1236 ,p_person_id => p_person_id -- IN
1237 );
1238 FETCH csr_mult_assignment_y_n into l_assignment_count;
1239 IF csr_mult_assignment_y_n%FOUND THEN
1240 --
1241 IF l_assignment_count > 1 THEN
1242 -- check if previous run also had mult_ind = 'Y'
1243 p_return := 'Y';
1244 ELSE
1245 p_return := 'N';
1246 END IF;
1247 --
1248 ELSE
1249 --ERR : could not find assignment/person_id !!
1250 debug('could not find assignment/person_id !!',65);
1251 -- p_return := ' ';
1252 p_return := NULL;
1253 END IF;
1254
1255 CLOSE csr_mult_assignment_y_n;
1256
1257 debug('p_return (multiple_appointment_indicator function)' || p_return, 70);
1258 debug('Exitng multiple_appointment_indicator ...',80);
1259
1260 debug_exit(l_proc_name);
1261 RETURN 0;
1262
1263 EXCEPTION
1264 WHEN others THEN
1265 IF SQLCODE <> hr_utility.hr_error_number
1266 THEN
1267 debug_others (l_proc_name, 10);
1268 IF g_debug
1269 THEN
1270 DEBUG ( 'Leaving: '
1271 || l_proc_name, -999);
1272 END IF;
1273 fnd_message.raise_error;
1274 ELSE
1275 RAISE;
1276 END IF;
1277
1278 END multiple_appointment_indicator;
1279
1280
1281
1282
1283 -- ----------------------------------------------------------------------------
1284 -- |------------------------< spouse_date_of_birth >---------------------------|
1285 -- ----------------------------------------------------------------------------
1286 FUNCTION spouse_date_of_birth
1287 (p_business_group_id IN NUMBER
1288 ,p_effective_date IN DATE
1289 ,p_person_id IN NUMBER
1290 ,p_return IN OUT NOCOPY VARCHAR2
1291 )
1292 RETURN number IS
1293 --
1294
1295 l_proc_name VARCHAR2(61):=
1296 g_proc_name||'spouse_date_of_birth';
1297 l_spouse_date_of_birth per_all_people_f.date_of_birth%TYPE;
1298 --
1299 BEGIN
1300
1301 debug_enter(l_proc_name);
1302
1303 debug('Entering spouse_date_of_birth ...',10);
1304 debug ('p_business_group_id:'||p_business_group_id);
1305 debug ('p_effective_date:'||p_effective_date);
1306 debug ('p_person_id:'||p_person_id);
1307
1308 -- cursor to fetch spouse_date_of_birth
1309 debug('Fetching spouse_date_of_birth ....',20);
1310 OPEN csr_spouse_dob
1311 (p_business_group_id => p_business_group_id -- IN
1312 ,p_effective_date => p_effective_date -- IN
1313 ,p_person_id => p_person_id -- IN
1314 );
1315 FETCH csr_spouse_dob into l_spouse_date_of_birth;
1316 IF csr_spouse_dob%FOUND THEN
1317 debug('l_spouse_date_of_birth : ' || l_spouse_date_of_birth,30);
1318
1319 -- For Bug 8790100
1320 -- p_return := to_char(l_spouse_date_of_birth,'DD/MM/YYYY');
1321 p_return := to_char(l_spouse_date_of_birth,'YYYY/MM/DD');
1322 ELSE
1323 --ERR : no spouse date
1324 debug('l_spouse_date_of_birth not found ',30);
1325 -- p_return := ' ';
1326 p_return := NULL;
1327 END IF;
1328 CLOSE csr_spouse_dob;
1329
1330 debug('p_return (spouse_date_of_birth function)' || p_return, 40);
1331 debug('Exiting spouse_date_of_birth ...',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 spouse_date_of_birth;
1352
1353
1354 -- ----------------------------------------------------------------------------
1355 -- |------------------------< marital_status >--------------------------------|
1356 -- ----------------------------------------------------------------------------
1357 FUNCTION marital_status
1358 (p_business_group_id IN NUMBER
1359 ,p_effective_date IN DATE
1360 ,p_person_id IN NUMBER
1361 ,p_return IN OUT NOCOPY VARCHAR2
1362 )
1363 RETURN number IS
1364 --
1365
1366 l_proc_name VARCHAR2(61):=
1367 g_proc_name||'marital_status';
1368 l_marital_status varchar2(2) := NULL;
1369 l_index NUMBER;
1370 --
1371 BEGIN
1372
1373 debug_enter(l_proc_name);
1374
1375 debug('Entering csr_marital_status ...',10);
1376 debug ('p_business_group_id:'||p_business_group_id);
1377 debug ('p_effective_date:'||p_effective_date);
1378 debug ('p_person_id:'||p_person_id);
1379
1380
1381
1382 -- check that marital_status is not null
1383 IF g_person_dtl.marital_status IS NOT NULL
1384 THEN
1385 debug('g_person_dtl.marital_status : ' || g_person_dtl.marital_status,50);
1386
1387 -- search thru the mapping for this marital status
1388 FOR i IN 1..g_marital_status_mapping.COUNT
1389 LOOP
1390
1391 IF i=1 THEN -- finding next index
1392 l_index:=g_marital_status_mapping.FIRST;
1393 ELSE
1394 l_index:=g_marital_status_mapping.NEXT(l_index);
1395 END IF;
1396
1397 debug('g_marital_status_mapping('||l_index||').pcv_information1 : '
1398 || g_marital_status_mapping(l_index).pcv_information1);
1399 debug('g_marital_status_mapping('||l_index||').pcv_information2 : '
1400 || g_marital_status_mapping(l_index).pcv_information2);
1401
1402 -- start comparing
1403 IF g_person_dtl.marital_status =
1404 g_marital_status_mapping(l_index).pcv_information1 -- match found!!
1405 THEN
1406 l_marital_status := g_marital_status_mapping(l_index).pcv_information2;
1407
1408 debug('l_marital_status : '|| l_marital_status,65);
1409 debug('g_marital_status_mapping('||l_index||').pcv_information2 : '
1410 || g_marital_status_mapping(l_index).pcv_information2,66);
1411 ELSE
1412 debug('Not a match !!',70);
1413 END IF;
1414
1415 -- is still NULL implies that no match was found
1416 IF l_marital_status IS NULL
1417 THEN
1418 l_marital_status := 'U';
1419 END IF;
1420
1421 END LOOP; -- end of FOR loop
1422
1423 ELSE -- g_person_dtl.marital_status IS NOT NULL
1424 l_marital_status := 'U';
1425 END IF;
1426
1427 p_return := l_marital_status;
1428
1429 debug('p_return (marital_status function)' || p_return, 40);
1430 debug('Exiting marital_status ...',50);
1431
1432 debug_exit(l_proc_name);
1433 RETURN 0;
1434
1435 EXCEPTION
1436 WHEN others THEN
1437 IF SQLCODE <> hr_utility.hr_error_number
1438 THEN
1439 debug_others (l_proc_name, 10);
1440 IF g_debug
1441 THEN
1442 DEBUG ( 'Leaving: '
1443 || l_proc_name, -999);
1444 END IF;
1445 fnd_message.raise_error;
1446 ELSE
1447 RAISE;
1448 END IF;
1449
1450 END marital_status;
1451
1452 -- ----------------------------------------------------------------------------
1453 -- |------------------------< basic_extract_main >----------------------------|
1454 -- ----------------------------------------------------------------------------
1455
1456 FUNCTION basic_extract_main
1457 (p_business_group_id IN NUMBER -- context
1458 ,p_effective_date IN DATE -- context
1459 ,p_assignment_id IN NUMBER -- context
1460 ,p_rule_parameter IN VARCHAR2 -- parameter
1461 ,p_output OUT NOCOPY VARCHAR2
1462 )
1463 RETURN number IS
1464 --
1465
1466 l_proc_name VARCHAR2(61):=
1467 g_proc_name||'basic_extract_main';
1468 l_value number;
1469 l_effective_date DATE;
1470 -- For Bug 8790100
1471 l_ret_prevsur per_all_people_f.previous_last_name%type;
1472 l_ret_midname per_all_people_f.middle_names%type;
1473 --
1474 BEGIN
1475
1476 debug_enter(l_proc_name);
1477
1478 -- switch on the trace
1479
1480 debug('Entering basic_extract_main ...',0);
1481 debug('p_business_group_id'||p_business_group_id,1);
1482 debug('p_effective_date'||p_effective_date,1);
1483 debug('p_assignment_id'|| p_assignment_id,1);
1484 debug('p_rule_parameter'||p_rule_parameter,1);
1485
1486
1487 l_effective_date := p_effective_date;
1488
1489 -- select the function call based on the parameter being passed to the rule
1490 IF p_rule_parameter = 'Location' THEN
1491 debug('About to enter location',20);
1492 l_value := location
1493 (p_business_group_id => p_business_group_id -- IN
1494 ,p_effective_date => l_effective_date -- IN
1495 ,p_assignment_id => p_assignment_id -- IN
1496 ,p_return => p_output -- OUT
1497 );
1498
1499 ELSIF p_rule_parameter = 'AgeVerificationIndicator' THEN
1500 debug('About to enter age_verification_indicator function',30);
1501 l_value := age_verification_indicator
1502 (p_business_group_id => p_business_group_id -- IN
1503 ,p_effective_date => l_effective_date -- IN
1504 ,p_person_id => g_person_dtl.person_id -- IN
1505 ,p_return => p_output -- OUT
1506 );
1507
1508 ELSIF p_rule_parameter = 'PersonDecoration' THEN
1509 debug('About to enter Person_decoration',40);
1510 l_value := person_decoration
1511 (p_business_group_id => p_business_group_id -- IN
1512 ,p_effective_date => l_effective_date -- IN
1513 ,p_person_id => g_person_dtl.person_id -- IN
1514 ,p_return => p_output -- OUT
1515 );
1516
1517 ELSIF p_rule_parameter = 'BankAccountSortCode' THEN
1518 debug('About to enter bank_account_details - BankAccountSortCode',40);
1519
1520 -- g_asg_bank_details.DELETE;
1521
1522 l_value := bank_account_details
1523 (p_business_group_id => p_business_group_id -- IN
1524 ,p_effective_date => l_effective_date -- IN
1525 ,p_assignment_id => p_assignment_id -- IN
1526 ,p_rule_parameter => p_rule_parameter -- IN
1527 ,p_return => p_output -- OUT
1528 );
1529
1530 ELSIF p_rule_parameter = 'BankAccountNumber' THEN
1531 debug('About to enter bank_account_details - BankAccountNumber',40);
1532 l_value := bank_account_details
1533 (p_business_group_id => p_business_group_id -- IN
1534 ,p_effective_date => l_effective_date -- IN
1535 ,p_assignment_id => p_assignment_id -- IN
1536 ,p_rule_parameter => p_rule_parameter -- IN
1537 ,p_return => p_output -- OUT
1538 );
1539
1540 ELSIF p_rule_parameter = 'BankAccountType' THEN
1541 debug('About to enter bank_account_details - BankAccountType',40);
1542 l_value := bank_account_details
1543 (p_business_group_id => p_business_group_id -- IN
1544 ,p_effective_date => l_effective_date -- IN
1545 ,p_assignment_id => p_assignment_id -- IN
1546 ,p_rule_parameter => p_rule_parameter -- IN
1547 ,p_return => p_output -- OUT
1548 );
1549
1550 ELSIF p_rule_parameter = 'BuildingSocietyRollNumber' THEN
1551 debug('About to enter bank_account_details - BuildingSocietyRollNumber',40);
1552 l_value := bank_account_details
1553 (p_business_group_id => p_business_group_id -- IN
1554 ,p_effective_date => l_effective_date -- IN
1555 ,p_assignment_id => p_assignment_id -- IN
1556 ,p_rule_parameter => p_rule_parameter -- IN
1557 ,p_return => p_output -- OUT
1558 );
1559
1560 ELSIF p_rule_parameter = 'MultipleAppointmentIndicator' THEN
1561 debug('About to enter Multiple_Appointment_Indicator ',40);
1562
1563 l_value := multiple_appointment_indicator
1564 (p_business_group_id => p_business_group_id -- IN
1565 ,p_effective_date => l_effective_date -- IN
1566 ,p_person_id => g_person_dtl.person_id -- IN
1567 ,p_return => p_output -- OUT
1568 );
1569
1570 ELSIF p_rule_parameter = 'SpouseDOB' THEN
1571 debug('About to enter spouse_date_of_birth',40);
1572 l_value := spouse_date_of_birth
1573 (p_business_group_id => p_business_group_id -- IN
1574 ,p_effective_date => l_effective_date -- IN
1575 ,p_person_id => g_person_dtl.person_id -- IN
1576 ,p_return => p_output -- OUT
1577 );
1578
1579 ELSIF p_rule_parameter = 'MarStatus' THEN
1580 debug('About to enter MarStatus',40);
1581 l_value := marital_status
1582 (p_business_group_id => p_business_group_id -- IN
1583 ,p_effective_date => l_effective_date -- IN
1584 ,p_person_id => g_person_dtl.person_id -- IN
1585 ,p_return => p_output -- OUT
1586 );
1587
1588 ELSIF p_rule_parameter = 'Title' THEN
1589 debug('About to enter Title',40);
1590 l_value := title
1591 (p_business_group_id => p_business_group_id -- IN
1592 ,p_effective_date => l_effective_date -- IN
1593 ,p_person_id => g_person_dtl.person_id -- IN
1594 ,p_return => p_output -- OUT
1595 );
1596
1597 ELSIF p_rule_parameter = 'PayPoint' THEN
1598 p_output := g_paypoint;
1599
1600 ELSIF p_rule_parameter = 'AltKey' THEN
1601 p_output := g_altkey;
1602
1603 ELSIF p_rule_parameter = 'FirstForeName' THEN
1604 -- check if first name is NULL
1605 -- anshghos: 115.4
1606 IF g_person_dtl.first_name IS NULL
1607 THEN
1608 -- store error for 'NO First Name'
1609 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1610 (p_error_number => 94474
1611 ,p_error_text => 'BEN_94474_NO_FIRST_NAME'
1612 );
1613 p_output := '';
1614 ELSE -- first name is not null
1615 p_output := g_person_dtl.first_name;
1616 END IF;
1617
1618 -- BEGIN For Bug 8790100
1619 ELSIF p_rule_parameter = 'PreviousSurname' THEN
1620
1621 IF g_person_dtl.PREVIOUS_LAST_NAME IS NULL
1622 THEN
1623 l_ret_prevsur := ' ';
1624 ELSE
1625 l_ret_prevsur := g_person_dtl.PREVIOUS_LAST_NAME;
1626 END IF;
1627
1628 debug('p_return (previuos surname)' || l_ret_prevsur, 40);
1629
1630 IF l_ret_prevsur = ' '
1631 THEN
1632
1633 IF g_current_run = 'PERIODIC'
1634 THEN -- this is case (3) + case (4)
1635
1636 IF g_prevsur_change_exists = 'Y'
1637 THEN
1638 l_ret_prevsur := '********************';
1639 ELSE
1640 debug('prev surname:' || l_ret_prevsur, 30);
1641 debug('ben_ext_person.g_chg_pay_table:' || ben_ext_person.g_chg_pay_table, 30);
1642 debug('ben_ext_person.g_chg_pay_column:' || ben_ext_person.g_chg_pay_column, 30);
1643 debug('ben_ext_person.g_chg_update_type:' || ben_ext_person.g_chg_update_type, 30);
1644 --
1645 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_PEOPLE_F'
1646 AND ben_ext_person.g_chg_pay_column = 'PREVIOUS_LAST_NAME'
1647 AND ben_ext_person.g_chg_update_type <> 'I'
1648 THEN -- this is checking location_event for case (3) and (4)
1649 l_ret_prevsur := '********************';
1650 g_prevsur_change_exists := 'Y';
1651 ELSE
1652 l_ret_prevsur := ' ';
1653 END IF;
1654 END IF;
1655 ELSE
1656 l_ret_prevsur := '********************';
1657 END IF;
1658
1659 END IF;
1660
1661 p_output := l_ret_prevsur;
1662
1663 ELSIF p_rule_parameter = 'SecondForename' THEN
1664
1665 IF g_person_dtl.MIDDLE_NAMES IS NULL
1666 THEN
1667 l_ret_midname := ' ';
1668 ELSE
1669 l_ret_midname := g_person_dtl.MIDDLE_NAMES;
1670 END IF;
1671
1672 debug('p_return (middle name)' || l_ret_midname, 40);
1673
1674 IF l_ret_midname = ' '
1675 THEN
1676 IF g_current_run = 'PERIODIC'
1677 THEN
1678 IF g_midname_change_exists = 'Y'
1679 THEN
1680 l_ret_midname := '********************';
1681 ELSE
1682
1683 debug('middle name:' || l_ret_midname, 30);
1684 debug('ben_ext_person.g_chg_pay_table:' || ben_ext_person.g_chg_pay_table, 30);
1685 debug('ben_ext_person.g_chg_pay_column:' || ben_ext_person.g_chg_pay_column, 30);
1686 debug('ben_ext_person.g_chg_update_type:' || ben_ext_person.g_chg_update_type, 30);
1687
1688 --
1689 IF ben_ext_person.g_chg_pay_table = 'PER_ALL_PEOPLE_F'
1690 AND ben_ext_person.g_chg_pay_column = 'MIDDLE_NAMES'
1691 AND ben_ext_person.g_chg_update_type <> 'I'
1692 THEN -- this is checking location_event for case (3) and (4)
1693 l_ret_midname := '********************';
1694 g_midname_change_exists := 'Y';
1695 ELSE
1696 l_ret_midname := ' ';
1697 END IF;
1698 END IF;
1699 ELSE
1700 l_ret_midname := '********************';
1701 END IF;
1702 END IF;
1703
1704 p_output := l_ret_midname;
1705 -- Enf For bug 8790100
1706
1707 ELSIF p_rule_parameter = 'CurrentRun' THEN
1708 debug('g_current_run: '||g_current_run);
1709 -- p_output := g_current_run;
1710
1711 -- Bugfix : 5378812
1712 -- The "CurrentRun" data element decides whether asterisk (***) are to be
1713 -- reported for data elements or not. For cutover, asterisk are not to be reported.
1714 -- Also, in case of new hires, asterisk for missing data elements should not be reported.
1715 -- the logic below is to handle new_hires
1716 -- if new_hire, asterisk behaviour should be like "CUTOVER"
1717 IF g_current_run = 'PERIODIC'
1718 and
1719 ben_ext_person.g_chg_pay_table = 'PER_ALL_ASSIGNMENTS_F'
1720 and
1721 ben_ext_person.g_chg_update_type = 'I'
1722 THEN
1723 p_output := 'CUTOVER';
1724 ELSE
1725 p_output := g_current_run;
1726 END IF;
1727
1728 ELSE
1729 -- p_output := '';
1730 p_output := NULL;
1731 END IF;
1732
1733 debug('p_output: '||p_output);
1734 debug_exit(l_proc_name);
1735 RETURN 0;
1736
1737
1738 EXCEPTION
1739 WHEN others THEN
1740 IF SQLCODE <> hr_utility.hr_error_number
1741 THEN
1742 debug_others (l_proc_name, 10);
1743 IF g_debug
1744 THEN
1745 DEBUG ( 'Leaving: '
1746 || l_proc_name, -999);
1747 END IF;
1748 fnd_message.raise_error;
1749 ELSE
1750 RAISE;
1751 END IF;
1752
1753 END basic_extract_main;
1754
1755
1756
1757 -- ----------------------------------------------------------------------------
1758 -- |------------------------< basic_data_post_processing >---------------------|
1759 -- ----------------------------------------------------------------------------
1760
1761 FUNCTION basic_data_post_processing RETURN VARCHAR2
1762 IS
1763
1764 -- Variable Declaration
1765
1766 -- Rowtype Variable Declaration
1767
1768 l_proc_name VARCHAR2(61):=
1769 g_proc_name||'basic_data_post_processing';
1770
1771 BEGIN -- basic_data_post_proc_rule
1772
1773 debug_enter(l_proc_name);
1774
1775 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1776
1777 debug_exit(l_proc_name);
1778 RETURN 'Y';
1779
1780 EXCEPTION
1781 WHEN others THEN
1782 IF SQLCODE <> hr_utility.hr_error_number
1783 THEN
1784 debug_others (l_proc_name, 10);
1785 IF g_debug
1786 THEN
1787 DEBUG ( 'Leaving: '
1788 || l_proc_name, -999);
1789 END IF;
1790 fnd_message.raise_error;
1791 ELSE
1792 RAISE;
1793 END IF;
1794
1795 END basic_data_post_processing; -- basic_data_post_proc_rule
1796
1797 END PQP_GB_PSI_BASIC_DATA;