[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_ADDDRESS
Source
4 --
1 PACKAGE BODY PQP_GB_PSI_ADDDRESS AS
2 -- /* $Header: pqpgbpsadd.pkb 120.5 2012/01/19 07:31:39 rpahune noship $ */
3 --
5 --
6 --
7 --
8 -- Exceptions
9 hr_application_error exception;
10 pragma exception_init (hr_application_error, -20001);
11
12 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
13 -- ----------------------------------------------------------------------------
14 -- |--------------------------------< debug >---------------------------------|
15 -- ----------------------------------------------------------------------------
16
17 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
21 BEGIN
18 IS
19
20 --
22 --
23 IF g_debug THEN
24 pqp_utilities.DEBUG (
25 p_trace_message => p_trace_message
26 ,p_trace_location => p_trace_location
27 );
28 END IF;
29 --
30 END DEBUG;
31
32
33 -- This procedure is used for debug purposes
34 -- debug_enter checks the debug flag and sets the trace on/off
35 --
36 -- ----------------------------------------------------------------------------
37 -- |----------------------------< debug_enter >-------------------------------|
38 -- ----------------------------------------------------------------------------
39
40 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
41 IS
42 BEGIN
43 --
44 IF g_debug THEN
45 IF pqp_utilities.g_nested_level = 0 THEN
46 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
47 END IF;
48 pqp_utilities.debug_enter (
49 p_proc_name => p_proc_name
50 ,p_trace_on => p_trace_on
51 );
52 END IF;
53 --
54
55 END debug_enter;
56
57
58 -- This procedure is used for debug purposes
59 --
60 -- ----------------------------------------------------------------------------
61 -- |----------------------------< debug_exit >--------------------------------|
62 -- ----------------------------------------------------------------------------
63
64 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
65 IS
66 BEGIN
67 --
68 IF g_debug THEN
69 pqp_utilities.debug_exit (
70 p_proc_name => p_proc_name
71 ,p_trace_off => p_trace_off
72 );
73
74 IF pqp_utilities.g_nested_level = 0 THEN
75 hr_utility.trace_off;
76 END IF;
77 END IF;
78 --
79 END debug_exit;
80
81
82 -- This procedure is used for debug purposes
83 --
84 -- ----------------------------------------------------------------------------
85 -- |----------------------------< debug_others >------------------------------|
86 -- ----------------------------------------------------------------------------
87
88 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
89 IS
90 BEGIN
91 --
92 pqp_utilities.debug_others (
93 p_proc_name => p_proc_name
94 ,p_proc_step => p_proc_step
95 );
96 --
97 END debug_others;
98
99 -- ----------------------------------------------------------------------------
100 -- |----------------------< set_address_extract_globals >---------------------|
101 -- Description: This procedure is to obtain set the extract level globals.
102 -- ----------------------------------------------------------------------------
103 PROCEDURE set_address_extract_globals
104 (
105 p_business_group_id IN NUMBER
106 ,p_assignment_id IN NUMBER
107 )
108 IS
109 l_include varchar2(1) := 'Y';
110 l_proc varchar2(72) := g_package||'.set_address_extract_globals';
111 l_config_values PQP_UTILITIES.t_config_values;
112 BEGIN
113 debug_enter(l_proc);
114
115 -- set global business group id
116 g_business_group_id := p_business_group_id;
117 g_legislation_code := 'GB';
118 g_person_id := NULL;
119 g_assignment_id := NULL;
120 g_office_address_type := NULL;
121 g_home_address_type := NULL;
122 g_office_address_id := NULL;
123 g_home_address_id := NULL;
124 g_country := NULL;
125 g_person_addresses.DELETE;
126 g_person_cutover_addresses.DELETE;
127 g_office_address_changed := 'N';
128 g_home_address_changed := 'N';
129
130 debug('g_business_group_id: '||g_business_group_id,10);
131
132 -- set the address types
133 -- fetch the configuration values for PQP_GB_PENSERVER_ADDRESS_MAP
134 -- if there is no configuration value an error will be raised at
135 -- extract level
136 PQP_UTILITIES.get_config_type_values(
137 p_configuration_type => c_configuration_type
138 ,p_business_group_id => g_business_group_id
139 ,p_legislation_code => g_legislation_code
140 ,p_tab_config_values => l_config_values
141 );
142 IF l_config_values.COUNT > 0 THEN
143 g_office_address_type := l_config_values(l_config_values.FIRST).pcv_information1;
144 g_home_address_type := l_config_values(l_config_values.FIRST).pcv_information2;
145 debug('g_office_address_type: '||g_office_address_type,20);
146 debug('g_home_address_type: '||g_home_address_type,20);
147 ELSE
148 -- No configuration for address types.
149 debug('ERROR: No configuration for address types.',20);
150 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
151 (p_extract_type => 'ADDRESS'
155 );
152 ,p_error_number => 94436
153 ,p_error_text => 'BEN_94436_NO_ADD_TYPES_CONFIG'
154 ,p_error_warning_flag => 'E'
156 END IF; --IF l_config_values.COUNT > 0
157
158 debug_exit(l_proc);
159 EXCEPTION
160 WHEN others THEN
161 IF SQLCODE <> hr_utility.hr_error_number
162 THEN
163 debug_others (l_proc, 10);
164 IF g_debug
165 THEN
166 DEBUG ( 'Leaving: '
167 || l_proc, -999);
168 END IF;
169 fnd_message.raise_error;
170 ELSE
171 RAISE;
172 END IF;
173 END set_address_extract_globals;
174 ---
175 -- ----------------------------------------------------------------------------
176 -- |----------------------< set_assignment_globals >--------------------------|
177 -- Description: This procedure is to set the assignment level globals.
178 -- ----------------------------------------------------------------------------
179 PROCEDURE set_assignment_globals
180 (
181 p_assignment_id IN NUMBER
182 ,p_effective_date IN DATE
183 )
184 IS
185 l_include varchar2(1) := 'Y';
186 l_proc varchar2(72) := g_package||'.set_assignment_globals';
187 l_address_details csr_get_addr_dtls%ROWTYPE;
188 l_address_exists boolean := false;
189 l_add_not_effective boolean := false;
190 l_no_add_type boolean := false;
191 l_add_type_not_mapped boolean := false;
192 l_error NUMBER;
193 l_errors_table pqp_gb_psi_functions.t_error_collection;
194 l_error_index NUMBER := 1;
195 BEGIN
196 debug_enter(l_proc);
197 debug('Inputs are: ',10);
198 debug('p_assignment_id: '||p_assignment_id,10);
199 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
200
201 --reset assignment level globals
202 g_person_id := NULL;
203 g_assignment_id := NULL;
204 g_office_address_id := NULL;
205 g_home_address_id := NULL;
206 g_country := NULL;
207
208 g_person_addresses.DELETE;
209 g_person_cutover_addresses.DELETE;
210
211 g_office_address_changed := 'N';
212 g_home_address_changed := 'N';
213
214 g_include_home_address := 'Y';
215 g_include_office_address := 'Y';
216 g_office_address_reported := false;
217
218 -- set global assignment_id
219 g_assignment_id := p_assignment_id;
220 debug('g_assignment_id: '||g_assignment_id,10);
221
222 -- set the global events table
223 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
224
225 -- set global person id
226 g_person_id := PQP_GB_PSI_FUNCTIONS.get_current_extract_person
227 (
228 p_assignment_id => p_assignment_id
229 );
230 debug('g_person_id: '||g_person_id,10);
231
232 -- get all the address details for the person
233 FOR l_address_details IN csr_get_addr_dtls
234 LOOP
235 debug('g_current_run: '||g_current_run,20);
236 l_address_exists := true;
237 -- if current run is cutover
238 IF g_current_run = 'CUTOVER' THEN
239 -- insert into cutover addresses table - g_person_cutover_addresses
240 debug('For Cutover extract',30);
241 -- if the current address is effective on the cutover date
242 IF p_effective_date BETWEEN l_address_details.date_from
243 AND NVL(l_address_details.date_to,c_highest_date) THEN
244 -- if the address is effective on the cutover date
245 IF NVL(l_address_details.address_type,' ') = g_office_address_type THEN
246 g_office_address_changed := 'Y';
247 g_office_address_id := l_address_details.address_id;
248 g_person_cutover_addresses(g_office_address_id) := l_address_details;
249 debug('Office address is there for cutover run',40);
250 debug('g_office_address_id: '||g_office_address_id);
251 ELSIF NVL(l_address_details.address_type,' ') = g_home_address_type THEN
252 g_home_address_changed := 'Y';
253 g_home_address_id := l_address_details.address_id;
254 g_person_cutover_addresses(g_home_address_id) := l_address_details;
255 debug('Home address is there for cutover run',40);
256 debug('g_home_address_id: '||g_home_address_id);
257 ELSE -- if the address is neither home nor office address type
258 -- if address type is null
259 IF l_address_details.address_type IS NULL THEN
260 debug('Address type is null');
261 l_no_add_type := true;
262 -- Raise Error: there are no addresses for the person
263 l_errors_table(l_error_index).error_number := 94481;
264 l_errors_table(l_error_index).error_text := 'BEN_94481_NO_ADD_TYPE';
268 debug('Address type of the Current address is not mapped');
265 l_errors_table(l_error_index).token1 := l_address_details.address_id;
266 l_error_index := l_error_index + 1;
267 ELSE
269 l_add_type_not_mapped := true;
270 -- Raise Error: there are no addresses for the person
271 l_errors_table(l_error_index).error_number := 94469;
272 l_errors_table(l_error_index).error_text := 'BEN_94469_ADD_TYPE_NOT_MAPPED';
273 l_errors_table(l_error_index).token1 := l_address_details.address_meaning;
274 l_error_index := l_error_index + 1;
275 END IF;
276 END IF; --IF l_address_details.address_type = g_office_address_type
277 ELSE -- address is not effective on cutover date
278 debug('Current address is not effective on the cutover date.');
279 l_add_not_effective := true;
280 -- Raise Error: there are no addresses for the person
281 l_errors_table(l_error_index).error_number := 94473;
282 l_errors_table(l_error_index).error_text := 'BEN_94473_ADD_NOT_EFFECTIVE';
283 l_errors_table(l_error_index).token1 := l_address_details.address_meaning;
284 l_errors_table(l_error_index).token2 := to_char(l_address_details.date_from,'dd/mm/yyyy');
285 l_errors_table(l_error_index).token3 := to_char(l_address_details.date_to,'dd/mm/yyyy');
286 l_error_index := l_error_index + 1;
287 END IF; --IF p_effective_date BETWEEN l_address_details.date_from
288
289 ELSE -- if current run is periodic
290 debug('For Periodic Changes extract',30);
291 g_person_addresses(l_address_details.address_id) := l_address_details;
292
293 END IF; --IF g_current_run = 'CUTOVER'
294
295 END LOOP;
296 IF NOT l_address_exists THEN
297 -- Raise Error: there are no addresses for the person
298 debug('ERROR: There are no addresses for the person');
299 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
300 (p_error_number => 94437
301 ,p_error_text => 'BEN_94437_NO_ADDRESSES'
302 );
303 ELSIF (g_current_run = 'CUTOVER' AND g_person_cutover_addresses.COUNT = 0) THEN
304
305 debug('raise the stored errors if there are no PenServer addresses');
306 FOR i IN l_errors_table.FIRST..l_errors_table.LAST LOOP
307 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
308 (p_error_number => l_errors_table(i).error_number
309 ,p_error_text => l_errors_table(i).error_text
310 ,p_token1 => l_errors_table(i).token1
311 ,p_token2 => l_errors_table(i).token2
312 ,p_token3 => l_errors_table(i).token3
313 ,p_token4 => l_errors_table(i).token4
314 );
315 END LOOP;
316 debug('ERROR: There are no addresses for the person');
317 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
318 (p_error_number => 94437
319 ,p_error_text => 'BEN_94437_NO_ADDRESSES'
320 );
321
322 END IF;
323 debug_exit(l_proc);
324 EXCEPTION
325 WHEN others THEN
326 IF SQLCODE <> hr_utility.hr_error_number
327 THEN
328 debug_others (l_proc, 10);
329 IF g_debug
330 THEN
331 DEBUG ( 'Leaving: '
332 || l_proc, -999);
333 END IF;
334 fnd_message.raise_error;
335 ELSE
336 RAISE;
337 END IF;
338 END set_assignment_globals;
339 ---
340 -- ----------------------------------------------------------------------------
341 -- |---------------------< address_cutover_ext_criteria >---------------------|
342 -- Description: Cutover extract criteria.
343 -- ----------------------------------------------------------------------------
344 FUNCTION address_cutover_ext_criteria
345 (
346 p_business_group_id NUMBER
347 ,p_assignment_id NUMBER
348 ,p_effective_date DATE
349 )RETURN VARCHAR2
350 IS
351 l_include varchar2(1) := 'Y';
352 l_proc varchar2(72) := g_package||'.address_cutover_ext_criteria';
353 BEGIN
354 debug_enter(l_proc);
355 debug('Inputs are: ',10);
356 debug('p_business_group_id: '||p_business_group_id,10);
357 debug('p_assignment_id: '||p_assignment_id);
358 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
359
360 -- reset salary globals
361 g_current_run := 'CUTOVER';
362
363 IF g_business_group_id IS NULL
364 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
365
366 g_business_group_id := p_business_group_id;
367 -- set the global debug value
368 g_debug := pqp_gb_psi_functions.check_debug(g_business_group_id);
369 debug_enter(l_proc);
373 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),20);
370 debug('Inputs are: ',20);
371 debug('p_business_group_id: '||p_business_group_id,20);
372 debug('p_assignment_id: '||p_assignment_id,20);
374
375 -- when business group id is null, all the globals shud be reset
376 PQP_GB_PSI_FUNCTIONS.set_shared_globals
377 (p_business_group_id => p_business_group_id
378 ,p_paypoint => g_paypoint
379 ,p_cutover_date => g_cutover_date
380 ,p_ext_dfn_id => g_ext_dfn_id
381 );
382 -- to allow the users to run cutover run for dates other than cutover date
383 -- set in the configuration values.
384 --g_effective_date := g_cutover_date;
385
386 -- set extract level globals
387 set_address_extract_globals
388 (
389 p_business_group_id => p_business_group_id
390 ,p_assignment_id => p_assignment_id
391 );
392
393 IF g_office_address_type IS NULL AND
394 g_home_address_type IS NULL THEN
395
396 l_include := 'N';
397 debug('Returning : '||l_include,30);
398 debug_exit(l_proc);
399
400 RETURN l_include;
401 END IF; --IF g_office_address_type IS NULL
402 --Raise extract exceptions which are stored while checking for the setup
403 debug('Raising the set-up errors, with input parameter as S');
404 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions('S');
405 END IF;--IF g_business_group_id IS NULL
406
407 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
408 (p_business_group_id => p_business_group_id
409 ,p_effective_date => p_effective_date
410 ,p_assignment_id => p_assignment_id
411 ,p_person_dtl => g_curr_person_dtls
412 ,p_assignment_dtl => g_curr_assg_dtls
413 );
414 IF l_include = 'N' THEN
415 debug('Returning : '||l_include,20);
416 debug_exit(l_proc);
417 return l_include;
418 END IF; --IF l_include = 'N'
419
420 debug('Person passed the basic criteria',10);
421 debug('g_assignment_id: '||g_assignment_id);
422
423 IF g_assignment_id IS NULL
424 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
425 g_effective_date := p_effective_date;
426 set_assignment_globals
427 (
428 p_assignment_id => p_assignment_id
429 ,p_effective_date => p_effective_date
430 );
431 END IF;
432
433 debug('Returning : '||l_include,10);
434 debug_exit(l_proc);
435
436 RETURN l_include;
437 EXCEPTION
438 WHEN others THEN
439 IF SQLCODE <> hr_utility.hr_error_number
440 THEN
441 debug_others (l_proc, 10);
442 IF g_debug
443 THEN
444 DEBUG ( 'Leaving: '
445 || l_proc, -999);
446 END IF;
447 fnd_message.raise_error;
448 ELSE
449 RAISE;
450 END IF;
451 END address_cutover_ext_criteria;
452 -- ----------------------------------------------------------------------------
453 -- |--------------------< address_periodic_ext_criteria >---------------------|
454 -- Description: Address Periodic extract Criteria.
455 -- ----------------------------------------------------------------------------
456 FUNCTION address_periodic_ext_criteria
457 (
458 p_business_group_id NUMBER
459 ,p_assignment_id NUMBER
460 ,p_effective_date DATE
461 )RETURN VARCHAR2
462 IS
463 l_include varchar2(1) := 'Y';
464 l_proc varchar2(72) := g_package||'.address_periodic_ext_criteria';
465 l_chg_surrogate_key VARCHAR2(30);
466 l_change_table VARCHAR2(30);
467 l_change_column VARCHAR2(30);
468 l_curr_evt_index NUMBER;
469 l_error NUMBER;
470 BEGIN
471 debug_enter(l_proc);
472 debug('Inputs are: ',10);
473 debug('p_business_group_id: '||p_business_group_id,10);
474 debug('p_assignment_id: '||p_assignment_id,10);
475
476 debug('ben_ext_person.g_chg_pay_table '||ben_ext_person.g_chg_pay_table,10);
477 debug('ben_ext_person.g_chg_pay_column '||ben_ext_person.g_chg_pay_column,10);
478 debug('ben_ext_person.g_chg_eff_dt '||ben_ext_person.g_chg_eff_dt,10);
479 debug('ben_ext_person.g_chg_update_type '||ben_ext_person.g_chg_update_type,10);
480 debug('ben_ext_person.g_chg_surrogate_key '||ben_ext_person.g_chg_surrogate_key,10);
481
482 -- reset salary globals
483 g_current_run := 'PERIODIC';
484 g_effective_date := p_effective_date;
485
486 IF g_business_group_id IS NULL
487 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
488
489 g_business_group_id := p_business_group_id;
490 -- set the global debug value
491 g_debug := pqp_gb_psi_functions.check_debug(g_business_group_id);
492 debug_enter(l_proc);
496 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
493 debug('Inputs are: ');
494 debug('p_business_group_id: '||p_business_group_id);
495 debug('p_assignment_id: '||p_assignment_id);
497
498 PQP_GB_PSI_FUNCTIONS.set_shared_globals
499 (p_business_group_id => p_business_group_id
500 ,p_paypoint => g_paypoint
501 ,p_cutover_date => g_cutover_date
502 ,p_ext_dfn_id => g_ext_dfn_id
503 );
504
505 -- set extract level globals
506 set_address_extract_globals
507 (
508 p_business_group_id => p_business_group_id
509 ,p_assignment_id => p_assignment_id
510 );
511 IF g_office_address_type IS NULL AND
512 g_home_address_type IS NULL THEN
513
514 l_include := 'N';
515 debug('Returning : '||l_include,30);
516 debug_exit(l_proc);
517 RETURN l_include;
518 END IF;
519 --Raise extract exceptions which are stored while checking for the setup
520 debug('Raising the set-up errors, with input parameter as S');
521 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions('S');
522
523 END IF;
524
525 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
526 (p_business_group_id => p_business_group_id
527 ,p_effective_date => p_effective_date
528 ,p_assignment_id => p_assignment_id
529 ,p_person_dtl => g_curr_person_dtls
530 ,p_assignment_dtl => g_curr_assg_dtls
531 );
532 IF l_include = 'N' THEN
533 debug('Returning : '||l_include,30);
534 debug_exit(l_proc);
535 return l_include;
536 END IF; --IF l_include = 'N'
537
538 IF g_assignment_id IS NULL
539 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
540
541 -- set assignment level globals
542 set_assignment_globals
543 (
544 p_assignment_id => p_assignment_id
545 ,p_effective_date => p_effective_date
546 );
547 END IF;
548 ----------- added in version 115.15
549 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
550 l_change_table := ben_ext_person.g_chg_pay_table;
551 l_change_column := ben_ext_person.g_chg_pay_column;
552 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
553
554 debug('Calling the common include event proc');
555 l_include := pqp_gb_psi_functions.include_event
556 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
557 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
558 );
559 IF l_include = 'N' THEN
560 debug('Returning : '||l_include,30);
561 debug_exit(l_proc);
562 return l_include;
563 END IF; --IF l_include = 'N'
564
565 IF UPPER(l_change_table) = 'PER_ADDRESSES' THEN
566 IF g_person_addresses.exists(l_chg_surrogate_key)
567 AND (g_person_addresses(l_chg_surrogate_key).address_type
568 NOT IN (g_office_address_type,g_home_address_type)
569 OR g_person_addresses(l_chg_surrogate_key).address_type IS NULL) THEN
570 debug('Change on a non-penserver address');
571 IF NOT chk_pen_addresses_exist(g_effective_date) THEN
572 debug('There are no penserver addresses active on the date: '||g_effective_date);
573 IF g_person_addresses(l_chg_surrogate_key).address_type IS NULL THEN
574 debug('Address type is null');
575 -- Raise Error: there are no addresses for the person
576 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
577 (p_error_number => 94481
578 ,p_error_text => 'BEN_94481_NO_ADD_TYPE'
579 ,p_token1 => l_chg_surrogate_key
580 );
581 ELSE
582 debug('Address type of the Current address is not mapped');
583 -- Raise Error: there are no addresses for the person
584 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
585 (p_error_number => 94469
586 ,p_error_text => 'BEN_94469_ADD_TYPE_NOT_MAPPED'
587 ,p_token1 => g_person_addresses(l_chg_surrogate_key).address_meaning
588 );
589 g_report_non_pen_address := true;
590 END IF; --IF g_person_addresses(l_chg_surrogate_key).address_type
591
592 END IF;--IF NOT chk_pen_addresses_exist(g_effective_date)
593
594 END IF;---IF g_person_addresses.exists(l_chg_surrogate_key)
595
596 END IF; --IF UPPER(l_change_table) = 'PER_ADDRESSES'
597 --------------------------
598
599 debug('Returning : '||l_include,10);
600 debug_exit(l_proc);
601 RETURN l_include;
605 THEN
602 EXCEPTION
603 WHEN others THEN
604 IF SQLCODE <> hr_utility.hr_error_number
606 debug_others (l_proc, 10);
607 IF g_debug
608 THEN
609 DEBUG ( 'Leaving: '
610 || l_proc, -999);
611 END IF;
612 fnd_message.raise_error;
613 ELSE
614 RAISE;
615 END IF;
616 END address_periodic_ext_criteria;
617 ---
618 -- ----------------------------------------------------------------------------
619 -- |---------------------< chk_office_address_changed >-----------------------|
620 -- Description: This procedure is to set the global g_office_address_changed,
621 -- which indicates whether
622 -- 1)there are any changes in office address for periodic extract,
623 -- 2)there is an office address active on the cutover date for
624 -- cutover extract.
625 -- ----------------------------------------------------------------------------
626 FUNCTION chk_office_address_changed RETURN VARCHAR2
627 IS
628 l_proc varchar2(72) := g_package||'.chk_office_address_changed';
629 l_error NUMBER;
630 l_chg_surrogate_key VARCHAR2(30);
631 l_change_table VARCHAR2(30);
632 l_change_column VARCHAR2(30);
633 l_index VARCHAR2(10);
634 BEGIN
635 debug_enter(l_proc);
636
637 --set global variable that the current layout is 'OFFICE' address
638 g_current_layout := 'OFFICE';
639 debug('g_current_layout: '||g_current_layout,10);
640 debug('g_current_run: '||g_current_run,10);
641 debug('g_office_address_changed: '||g_office_address_changed,10);
642 --
643 IF g_current_run = 'PERIODIC' THEN
644 g_office_address_changed := 'N';
645 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
646 l_change_table := ben_ext_person.g_chg_pay_table;
647 l_change_column := ben_ext_person.g_chg_pay_column;
648 debug('l_chg_surrogate_key: '||l_chg_surrogate_key,20);
649 debug('l_change_table: '||l_change_table,20);
650 debug('l_change_column: '||l_change_column,20);
651 debug('g_current_run: '||g_current_run,20);
652
653 IF (l_change_table = 'PER_ALL_PEOPLE_F'
654 AND (l_change_column IN ('EMAIL_ADDRESS','MAILSTOP','NATIONAL_IDENTIFIER')))
655 OR l_change_table = 'PER_ALL_ASSIGNMENTS_F'
656 THEN
657 g_office_address_id := NULL;
658 FOR i IN 1..g_person_addresses.COUNT
659 LOOP
660 IF i=1 THEN
661 l_index := g_person_addresses.FIRST;
662 ELSE
663 l_index := g_person_addresses.NEXT(l_index);
664 END IF; --IF i=1
665
666 IF (g_effective_date BETWEEN g_person_addresses(l_index).date_from
667 AND NVL(g_person_addresses(l_index).date_to,c_highest_date))
668 AND g_person_addresses(l_index).address_type = g_office_address_type THEN
669
670 g_office_address_id := g_person_addresses(l_index).address_id;
671
672 END IF;--IF (p_effective_date BETWEEN
673 END LOOP;
674 g_office_address_changed := 'Y';
675 debug('g_office_address_id: '||g_office_address_id,30);
676 debug('g_office_address_changed: '||g_office_address_changed,30);
677 debug('Returning: Y',30);
678 debug_exit(l_proc);
679 RETURN g_office_address_changed;
680 END IF;
681 IF g_person_addresses.exists(l_chg_surrogate_key) THEN
682 IF g_person_addresses(l_chg_surrogate_key).address_type = g_office_address_type THEN
683 g_office_address_id := l_chg_surrogate_key;
684 g_office_address_changed := 'Y';
685 g_office_address_reported := true;
686 debug('g_office_address_id: '||g_office_address_id,40);
687 debug('Returning: Y',40);
688 debug_exit(l_proc);
689 RETURN g_office_address_changed;
690
691 END IF; --IF g_asg_salary_ele_dtls(l_index).address_type = 'OF'
692 END IF;
693
694 END IF;--IF g_current_run = 'PERIODIC'
695 debug('Returning: '||g_office_address_changed,10);
696 debug_exit(l_proc);
697 RETURN g_office_address_changed;
698 EXCEPTION
699 WHEN others THEN
700 IF SQLCODE <> hr_utility.hr_error_number
701 THEN
702 debug_others (l_proc, 10);
703 IF g_debug
704 THEN
705 DEBUG ( 'Leaving: '
706 || l_proc, -999);
707 END IF;
708 fnd_message.raise_error;
709 ELSE
710 RAISE;
711 END IF;
712 END chk_office_address_changed;
713 ---
714 -- ----------------------------------------------------------------------------
715 -- |---------------------< chk_home_address_changed >------------------------|
719 -- 2)there is an home address active on the cutover date for
716 -- Description: This procedure is to set the global g_home_address_changed,
717 -- which indicates whether
718 -- 1)there are any changes in home address for periodic extract,
720 -- cutover extract.
721 -- ----------------------------------------------------------------------------
722 FUNCTION chk_home_address_changed RETURN VARCHAR2
723 IS
724 l_proc varchar2(72) := g_package||'.chk_home_address_changed';
725 l_error NUMBER;
726 l_chg_surrogate_key VARCHAR2(30);
727 l_change_table VARCHAR2(30);
728 l_change_column VARCHAR2(30);
729 l_index VARCHAR2(10);
730 BEGIN
731 debug_enter(l_proc);
732
733 --set global variable that the current layout is 'OFFICE' address
734 g_current_layout := 'HOME';
735 debug('g_current_layout: '||g_current_layout,10);
736 debug('g_current_run: '||g_current_run,10);
737 debug('g_home_address_changed: '||g_home_address_changed,10);
738 --
739 IF g_current_run = 'PERIODIC' THEN
740 g_home_address_changed := 'N';
741 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
742 l_change_table := ben_ext_person.g_chg_pay_table;
743 l_change_column := ben_ext_person.g_chg_pay_column;
744 debug('l_chg_surrogate_key: '||l_chg_surrogate_key,20);
745 debug('l_change_table: '||l_change_table,20);
746 debug('l_change_column: '||l_change_column,20);
747 debug('g_current_run: '||g_current_run,20);
748
749 IF (l_change_table = 'PER_ALL_PEOPLE_F'
750 AND (l_change_column IN ('EMAIL_ADDRESS','MAILSTOP','NATIONAL_IDENTIFIER')))
751 OR l_change_table = 'PER_ALL_ASSIGNMENTS_F'
752 THEN
753 g_home_address_id := NULL;
754 FOR i IN 1..g_person_addresses.COUNT
755 LOOP
756 IF i=1 THEN
757 l_index := g_person_addresses.FIRST;
758 ELSE
759 l_index := g_person_addresses.NEXT(l_index);
760 END IF; --IF i=1
761
762 IF (g_effective_date BETWEEN g_person_addresses(l_index).date_from
763 AND NVL(g_person_addresses(l_index).date_to,c_highest_date))
764 AND g_person_addresses(l_index).address_type = g_home_address_type THEN
765
766 g_home_address_id := g_person_addresses(l_index).address_id;
767
768 END IF;--IF (p_effective_date BETWEEN
769 END LOOP;
770 g_home_address_changed := 'Y';
771 debug('g_home_address_id: '||g_home_address_id,30);
772 debug('g_home_address_changed: '||g_home_address_changed,30);
773 debug('Returning: Y',30);
774 debug_exit(l_proc);
775 RETURN g_home_address_changed;
776 END IF;--IF (l_change_table = 'PER_ALL_PEOPLE_F'
777
778 IF g_person_addresses.exists(l_chg_surrogate_key) THEN
779 IF g_person_addresses(l_chg_surrogate_key).address_type = g_home_address_type THEN
780 g_home_address_id := l_chg_surrogate_key;
781 g_home_address_changed := 'Y';
782 debug('g_home_address_id: '||g_home_address_id,40);
783 debug('Returning: Y',40);
784 debug_exit(l_proc);
785 RETURN g_home_address_changed;
786 END IF; --IF g_asg_salary_ele_dtls(l_index).address_type = 'OF'
787 END IF; --IF g_person_addresses.exists
788
789 END IF;--IF g_current_run = 'PERIODIC'
790 debug('Returning: '||g_home_address_changed,10);
791 debug_exit(l_proc);
792 RETURN g_home_address_changed;
793 EXCEPTION
794 WHEN others THEN
795 IF SQLCODE <> hr_utility.hr_error_number
796 THEN
797 debug_others (l_proc, 10);
798 IF g_debug
799 THEN
800 DEBUG ( 'Leaving: '
801 || l_proc, -999);
802 END IF;
803 fnd_message.raise_error;
804 ELSE
805 RAISE;
806 END IF;
807 END chk_home_address_changed;
808 ---
809 -- ----------------------------------------------------------------------------
810 -- |---------------------< is_office_address_changed >------------------------|
811 -- Description: This process will return the value of the global variable
812 -- g_include_office_address, which indicates whether the office
813 -- address is to be picked or not. The value of this is checked
814 -- in the extra conditions on the office address record.
815 -- g_include_office_address is set to 'N' if there are any data
816 -- errors raised.
817 -- ----------------------------------------------------------------------------
818 FUNCTION is_office_address_changed RETURN VARCHAR2
819 IS
820 l_proc varchar2(72) := g_package||'.is_office_address_changed';
821 BEGIN
822 debug_enter(l_proc);
823
827 RETURN g_include_office_address;
824 debug('g_include_office_address: '||g_include_office_address,10);
825
826 debug_exit(l_proc);
828 END is_office_address_changed;
829 ---
830 -- ----------------------------------------------------------------------------
831 -- |---------------------< is_home_address_changed >------------------------|
832 -- Description: This process will return the value of the global variable
833 -- g_include_home_address, which indicates whether the home
834 -- address is to be picked or not. The value of this is checked
835 -- in the extra conditions on the home address record.
836 -- g_include_home_address is set to 'N' if there are any data
837 -- errors raised.
838 -- ----------------------------------------------------------------------------
839 FUNCTION is_home_address_changed RETURN VARCHAR2
840 IS
841 l_proc varchar2(72) := g_package||'.is_home_address_changed';
842 BEGIN
843 debug_enter(l_proc);
844
845 debug('g_include_home_address: '||g_include_home_address,10);
846
847 debug_exit(l_proc);
848 RETURN g_include_home_address;
849 END is_home_address_changed;
850 ---
851 -- ----------------------------------------------------------------------------
852 -- |---------------------< address_data_element_value >-----------------------|
853 -- Description: This is a common function used by all the data elements to fetch
854 -- thier respective values. Depending the parameter p_ext_user_value
855 -- this procedure decides which value to be returned.
856 -- ----------------------------------------------------------------------------
857 FUNCTION address_data_element_value
858 (
859 p_ext_user_value IN VARCHAR2
860 ,p_output_value OUT NOCOPY VARCHAR2
861 ) RETURN NUMBER
862 IS
863 l_proc varchar2(72) := g_package||'.address_data_element_value';
864 l_error NUMBER;
865 l_chg_surrogate_key NUMBER;
866 l_change_table VARCHAR2(30);
867 l_change_column VARCHAR2(30);
868 l_index NUMBER;
869 -- ----------------------------------------------------------------------------
870 -- |--------------------------< get_address_code >-----------------------------|
871 -- Description: This procedure is to fetch the address code, HM/OF for Home &
872 -- Office address respectively.
873 -- ----------------------------------------------------------------------------
874 FUNCTION get_address_code
875 (
876 p_address_code OUT NOCOPY VARCHAR2
877 ) RETURN NUMBER
878 IS
879 l_proc varchar2(72) := g_package||'.get_address_code';
880 BEGIN
881 debug_enter(l_proc);
882 IF g_current_layout = 'HOME' THEN
883 p_address_code := 'HM';
884 ELSE
885 p_address_code := 'OF';
886 END IF;
887 debug_exit(l_proc);
888 return 0;
889 END get_address_code;
890 ------
891 -- ----------------------------------------------------------------------------
892 -- |-------------------------< get_address_line1 >----------------------------|
893 -- Description: This procedure is to fetch address line 1 of the current address
894 -- being processed
895 -- ----------------------------------------------------------------------------
896 FUNCTION get_address_line1
897 (
898 p_address_line1 OUT NOCOPY VARCHAR2
899 ) RETURN NUMBER
900 IS
901 l_proc varchar2(72) := g_package||'.get_address_line1';
902 l_address_line1 per_addresses.address_line1%TYPE;
903 BEGIN
904 debug_enter(l_proc);
905 l_address_line1 := NULL;
906 IF g_current_run = 'CUTOVER' THEN
907 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
908 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
909
910 l_address_line1 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).address_line1,'');
911
912 END IF;
913 ELSE
914 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
915 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
916
917 l_address_line1 := NVL(g_person_addresses(l_chg_surrogate_key).address_line1,'');
918
919 END IF;
920 END IF;
921 p_address_line1 := l_address_line1;
922 IF p_address_line1 IS NULL THEN
923 -- raise error
924 debug('ERROR: No Address Line 1. This is a mandatory field',20);
925 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
926 (p_error_number => 94462
927 ,p_error_text => 'BEN_94462_NO_ADD_LINE1'
928 ,p_token1 => l_chg_surrogate_key
929 );
930 /*
934 g_include_home_address := 'N';
931 -- currently errored records are also reported
932 -- uncomment this part when they need not be reported.
933 IF g_current_layout = 'HOME' THEN
935 ELSE
936 g_include_office_address := 'N';
937 END IF; -- IF g_current_layout = 'HOME'*/
938
939 END IF;
940 debug_exit(l_proc);
941 return 0;
942 EXCEPTION
943 WHEN others THEN
944 IF SQLCODE <> hr_utility.hr_error_number
945 THEN
946 debug_others (l_proc, 10);
947 IF g_debug
948 THEN
949 DEBUG ( 'Leaving: '
950 || l_proc, -999);
951 END IF;
952 fnd_message.raise_error;
953 ELSE
954 RAISE;
955 END IF;
956 END get_address_line1;
957 ------
958 -- ----------------------------------------------------------------------------
959 -- |-------------------------< get_address_line2 >----------------------------|
960 -- Description: This procedure is to fetch address line 2 of the current address
961 -- being processed
962 -- ----------------------------------------------------------------------------
963 FUNCTION get_address_line2
964 (
965 p_address_line2 OUT NOCOPY VARCHAR2
966 ) RETURN NUMBER
967 IS
968 l_proc varchar2(72) := g_package||'.get_address_line2';
969 l_address_line2 per_addresses.address_line2%TYPE;
970 BEGIN
971 debug_enter(l_proc);
972 l_address_line2 := NULL;
973 IF g_current_run = 'CUTOVER' THEN
974 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
975 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
976
977 l_address_line2 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).address_line2,'');
978
979 END IF;
980 ELSE
981 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
982 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
983
984 l_address_line2 := NVL(g_person_addresses(l_chg_surrogate_key).address_line2,' ');
985
986 END IF;
987 END IF;
988 p_address_line2 := l_address_line2;
989 debug_exit(l_proc);
990 return 0;
991 EXCEPTION
992 WHEN others THEN
993 IF SQLCODE <> hr_utility.hr_error_number
994 THEN
995 debug_others (l_proc, 10);
996 IF g_debug
997 THEN
998 DEBUG ( 'Leaving: '
999 || l_proc, -999);
1000 END IF;
1001 fnd_message.raise_error;
1002 ELSE
1003 RAISE;
1004 END IF;
1005 END get_address_line2;
1006 ------
1007 -- ----------------------------------------------------------------------------
1008 -- |-------------------------< get_address_line3 >----------------------------|
1009 -- Description: This procedure is to fetch address line 3 of the current address
1010 -- being processed
1011 -- ----------------------------------------------------------------------------
1012 FUNCTION get_address_line3
1013 (
1014 p_address_line3 OUT NOCOPY VARCHAR2
1015 ) RETURN NUMBER
1016 IS
1017 l_proc varchar2(72) := g_package||'.get_address_line3';
1018 l_address_line3 per_addresses.address_line3%TYPE;
1019 BEGIN
1020 debug_enter(l_proc);
1021 l_address_line3 := NULL;
1022 IF g_current_run = 'CUTOVER' THEN
1023 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1024 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1025
1026 l_address_line3 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).address_line3,'');
1027
1028 END IF;
1029 ELSE
1030 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1031 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1032
1033 l_address_line3 := NVL(g_person_addresses(l_chg_surrogate_key).address_line3,' ');
1034
1035 END IF;
1036 END IF;
1037 /*-- check data type
1038 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_address_line3) THEN
1039 -- raise error
1040 debug('ERROR: Invalid Datatype.',40);
1041 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1042 (p_error_number => 94464
1043 ,p_error_text => 'BEN_94464_INVALID_ADD_LIN3'
1044 ,p_token1 => l_chg_surrogate_key
1045 );
1046
1047 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_address_line3)*/
1048
1049 p_address_line3 := l_address_line3;
1050 debug_exit(l_proc);
1054 IF SQLCODE <> hr_utility.hr_error_number
1051 return 0;
1052 EXCEPTION
1053 WHEN others THEN
1055 THEN
1056 debug_others (l_proc, 10);
1057 IF g_debug
1058 THEN
1059 DEBUG ( 'Leaving: '
1060 || l_proc, -999);
1061 END IF;
1062 fnd_message.raise_error;
1063 ELSE
1064 RAISE;
1065 END IF;
1066 END get_address_line3;
1067 ------
1068 -- ----------------------------------------------------------------------------
1069 -- |------------------------< get_address_line4 >-----------------------------|
1070 -- Description: This procedure is to fetch address line 4 of the current address
1071 -- being processed. The value of this is Town/City.
1072 -- ----------------------------------------------------------------------------
1073 FUNCTION get_address_line4
1074 (
1075 p_address_line4 OUT NOCOPY VARCHAR2
1076 ) RETURN NUMBER
1077 IS
1078 l_proc varchar2(72) := g_package||'.get_address_line4';
1079 l_address_line4 per_addresses.region_1%TYPE;
1080 BEGIN
1081 debug_enter(l_proc);
1082 l_address_line4 := NULL;
1083 IF g_current_run = 'CUTOVER' THEN
1084 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1085 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1086
1087 l_address_line4 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).address_line4,'');
1088
1089 END IF;
1090 ELSE
1091 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1092 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1093
1094 l_address_line4 := NVL(g_person_addresses(l_chg_surrogate_key).address_line4,' ');
1095
1096 END IF;
1097 END IF;
1098 /*-- check data type
1099 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_address_line4) THEN
1100 -- raise error
1101 debug('ERROR: Invalid Datatype.',40);
1102 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1103 (p_error_number => 94464
1104 ,p_error_text => 'BEN_94464_INVALID_ADD_LIN4'
1105 ,p_token1 => l_chg_surrogate_key
1106 );
1107
1108 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_address_line4)*/
1109
1110 p_address_line4 := l_address_line4;
1111 debug_exit(l_proc);
1112 return 0;
1113 EXCEPTION
1114 WHEN others THEN
1115 IF SQLCODE <> hr_utility.hr_error_number
1116 THEN
1117 debug_others (l_proc, 10);
1118 IF g_debug
1119 THEN
1120 DEBUG ( 'Leaving: '
1121 || l_proc, -999);
1122 END IF;
1123 fnd_message.raise_error;
1124 ELSE
1125 RAISE;
1126 END IF;
1127 END get_address_line4;
1128 ------
1129 -- ----------------------------------------------------------------------------
1130 -- |------------------------< get_address_line5 >-----------------------------|
1131 -- Description: This procedure is to fetch address line 5 of the current address
1132 -- being processed. The value of this is Town/City.
1133 -- ----------------------------------------------------------------------------
1134 FUNCTION get_address_line5
1135 (
1136 p_address_line5 OUT NOCOPY VARCHAR2
1137 ) RETURN NUMBER
1138 IS
1139 l_proc varchar2(72) := g_package||'.get_address_line5';
1140 l_address_line5 per_addresses.region_1%TYPE;
1141 BEGIN
1142 debug_enter(l_proc);
1143 l_address_line5 := NULL;
1144 IF g_current_run = 'CUTOVER' THEN
1145 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1146 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1147
1148 l_address_line5 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).address_line5,'');
1149
1150 END IF;
1151 ELSE
1152 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1153 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1154
1155 l_address_line5 := NVL(g_person_addresses(l_chg_surrogate_key).address_line5,' ');
1156
1157 END IF;
1158 END IF;
1159 /*-- check data type
1160 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_address_line5) THEN
1161 -- raise error
1162 debug('ERROR: Invalid Datatype.',40);
1163 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1164 (p_error_number => 94464
1165 ,p_error_text => 'BEN_94464_INVALID_ADD_LIN4'
1166 ,p_token1 => l_chg_surrogate_key
1167 );
1168
1172 debug_exit(l_proc);
1169 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_address_line5)*/
1170
1171 p_address_line5 := l_address_line5;
1173 return 0;
1174 EXCEPTION
1175 WHEN others THEN
1176 IF SQLCODE <> hr_utility.hr_error_number
1177 THEN
1178 debug_others (l_proc, 10);
1179 IF g_debug
1180 THEN
1181 DEBUG ( 'Leaving: '
1182 || l_proc, -999);
1183 END IF;
1184 fnd_message.raise_error;
1185 ELSE
1186 RAISE;
1187 END IF;
1188 END get_address_line5;
1189 ------
1190 -- ----------------------------------------------------------------------------
1191 -- |------------------------< get_address_postcode >--------------------------|
1192 -- Description: This procedure is to fetch Postcode of the current address
1193 -- being processed. The value of Postcode shuould not be null
1194 -- when Country is United Kingdom.
1195 -- ----------------------------------------------------------------------------
1196 FUNCTION get_address_postcode
1197 (
1198 p_address_postcode OUT NOCOPY VARCHAR2
1199 ) RETURN NUMBER
1200 IS
1201 l_proc varchar2(72) := g_package||'.get_address_postcode';
1202 l_postal_code per_addresses.postal_code%TYPE;
1203 l_country per_addresses.country%TYPE;
1204 BEGIN
1205 debug_enter(l_proc);
1206 l_postal_code := NULL;
1207 IF g_current_run = 'CUTOVER' THEN
1208 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1209 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1210
1211 l_postal_code := NVL(g_person_cutover_addresses(l_chg_surrogate_key).postal_code,'');
1212 l_country := NVL(g_person_cutover_addresses(l_chg_surrogate_key).country,'');
1213
1214 END IF;
1215 ELSE
1216 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1217 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1218
1219 l_postal_code := NVL(g_person_addresses(l_chg_surrogate_key).postal_code,' ');
1220 l_country := NVL(g_person_addresses(l_chg_surrogate_key).country,' ');
1221
1222 END IF;
1223 END IF;
1224 IF UPPER(l_country)='UNITED KINGDOM' AND l_postal_code IS NULL THEN
1225 -- raise error
1226 debug('ERROR: Postal Code cannot be empty for UK Addresses.',40);
1227 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1228 (p_error_number => 94438
1229 ,p_error_text => 'BEN_94438_INVALID_POST_CODE'
1230 ,p_token1 => l_chg_surrogate_key
1231 );
1232
1233 END IF;
1234 /*-- check data type
1235 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_postal_code) THEN
1236 -- raise error
1237 debug('ERROR: Invalid Datatype.',40);
1238 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1239 (p_error_number => 94464
1240 ,p_error_text => ' BEN_94438_INVALID_POSTAL_CODE'
1241 ,p_token1 => l_chg_surrogate_key
1242 );
1243
1244 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_postal_code)*/
1245
1246 p_address_postcode := l_postal_code;
1247 debug_exit(l_proc);
1248 return 0;
1249 EXCEPTION
1250 WHEN others THEN
1251 IF SQLCODE <> hr_utility.hr_error_number
1252 THEN
1253 debug_others (l_proc, 10);
1254 IF g_debug
1255 THEN
1256 DEBUG ( 'Leaving: '
1257 || l_proc, -999);
1258 END IF;
1259 fnd_message.raise_error;
1260 ELSE
1261 RAISE;
1262 END IF;
1263 END get_address_postcode;
1264 ------
1265 -- ----------------------------------------------------------------------------
1266 -- |------------------------< get_country >-----------------------------|
1267 -- Description: This procedure is to fetch country of the current address
1268 -- being processed.
1269 -- ----------------------------------------------------------------------------
1270 FUNCTION get_country
1271 (
1272 p_country OUT NOCOPY VARCHAR2
1273 ) RETURN NUMBER
1274 IS
1275 l_proc varchar2(72) := g_package||'.get_country';
1276 l_country per_addresses.country%TYPE;
1277 BEGIN
1278 debug_enter(l_proc);
1279 IF g_current_run = 'CUTOVER' THEN
1280 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1281 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1282
1283 l_country := NVL(g_person_cutover_addresses(l_chg_surrogate_key).country,'');
1284
1288 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1285 END IF;
1286 ELSE
1287 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1289
1290 l_country := NVL(g_person_addresses(l_chg_surrogate_key).country,'');
1291
1292 END IF;
1293 END IF;
1294 IF l_country IS NULL THEN
1295 debug('ERROR: Country field is mandatory if not UK',40);
1296 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1297 (p_error_number => 94439
1298 ,p_error_text => 'BEN_94439_INVALID_COUNTRY'
1299 ,p_token1 => l_chg_surrogate_key
1300 );
1301
1302 ELSIF UPPER(l_country) = 'UNITED KINGDOM' THEN
1303
1304 l_country := '';
1305
1306 END IF;
1307 /*-- check data type
1308 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_country) THEN
1309 -- raise error
1310 debug('ERROR: Invalid Datatype.',40);
1311 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1312 (p_error_number => 94439
1313 ,p_error_text => 'BEN_94439_INVALID_COUNTRY'
1314 ,p_token1 => l_chg_surrogate_key
1315 );
1316
1317 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_country)*/
1318
1319 p_country := l_country;
1320 debug_exit(l_proc);
1321 return 0;
1322 EXCEPTION
1323 WHEN others THEN
1324 IF SQLCODE <> hr_utility.hr_error_number
1325 THEN
1326 debug_others (l_proc, 10);
1327 IF g_debug
1328 THEN
1329 DEBUG ( 'Leaving: '
1330 || l_proc, -999);
1331 END IF;
1332 fnd_message.raise_error;
1333 ELSE
1334 RAISE;
1335 END IF;
1336 END get_country;
1337 ------
1338 -- ----------------------------------------------------------------------------
1339 -- |------------------------< get_address_effdate >---------------------------|
1340 -- Description: This procedure is to fetch effective start date of the current
1341 -- address being processed.
1342 -- ----------------------------------------------------------------------------
1343 FUNCTION get_address_effdate
1344 (
1345 p_address_effdate OUT NOCOPY VARCHAR2
1346 ) RETURN NUMBER
1347 IS
1348 l_proc varchar2(72) := g_package||'.get_address_effdate';
1349 l_date_from per_addresses.date_from%TYPE;
1350 BEGIN
1351 debug_enter(l_proc);
1352 l_date_from := NULL;
1353 IF g_current_run = 'CUTOVER' THEN
1354 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1355 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1356
1357 l_date_from := NVL(g_person_cutover_addresses(l_chg_surrogate_key).date_from,'');
1358
1359 END IF;
1360 ELSE
1361 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1362 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1363
1364 l_date_from := NVL(g_person_addresses(l_chg_surrogate_key).date_from,'');
1365
1366 END IF;
1367 END IF;
1368 p_address_effdate := to_char(NVL(l_date_from,''),'dd/mm/yyyy');
1369 debug_exit(l_proc);
1370 return 0;
1371 EXCEPTION
1372 WHEN others THEN
1373 IF SQLCODE <> hr_utility.hr_error_number
1374 THEN
1375 debug_others (l_proc, 10);
1376 IF g_debug
1377 THEN
1378 DEBUG ( 'Leaving: '
1379 || l_proc, -999);
1380 END IF;
1381 fnd_message.raise_error;
1382 ELSE
1383 RAISE;
1384 END IF;
1385 END get_address_effdate;
1386 ------
1387 -- ----------------------------------------------------------------------------
1388 -- |-----------------------< get_address_mailstop >---------------------------|
1389 -- Description: This procedure is to fetch MailSort code of the current Person.
1390 -- This value will be reported in Office address only.
1391 -- ----------------------------------------------------------------------------
1392 FUNCTION get_address_mailstop
1393 (
1394 p_address_mailstop OUT NOCOPY VARCHAR2
1395 ) RETURN NUMBER
1396 IS
1397 l_proc varchar2(72) := g_package||'.get_address_mailstop';
1398 l_mailstop PER_ALL_PEOPLE_F.mailstop%TYPE;
1399 BEGIN
1400 debug_enter(l_proc);
1401 l_mailstop := NULL;
1402 IF g_current_run = 'CUTOVER' THEN
1403
1404 l_mailstop := NVL(g_person_cutover_addresses(l_chg_surrogate_key).mailstop,'');
1405
1406 ELSE
1407
1411
1408 OPEN csr_get_email_mailstop;
1409 FETCH csr_get_email_mailstop INTO g_email_address,l_mailstop;
1410 CLOSE csr_get_email_mailstop;
1412 END IF;
1413 IF NOT pqp_gb_psi_functions.is_numeric(l_mailstop) THEN
1414 -- raise error
1415 debug('ERROR: Invalid Datatype.',40);
1416 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1417 (p_error_number => 94472
1418 ,p_error_text => 'BEN_94472_INVALID_MAILSTOP'
1419 ,p_token1 => l_mailstop
1420 );
1421
1422 ELSIF length(l_mailstop) > 5
1423 OR hr_number.canonical_to_number(l_mailstop) NOT BETWEEN 0 AND 99999 THEN
1424 -- raise error
1425 debug('ERROR: Invalid Length.',40);
1426 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1427 (p_error_number => 94985
1428 ,p_error_text => 'BEN_94985_INVALID_MAILSTOP_LEN'
1429 ,p_token1 => l_mailstop
1430 );
1431 l_mailstop := substr(l_mailstop,-5);
1432 ELSE
1433 debug('Apply format mask 09999 on correct mailstop',40);
1434 l_mailstop := ltrim(rtrim(to_char(hr_number.canonical_to_number(l_mailstop),'09999')));
1435 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_mailstop)
1436
1437 p_address_mailstop := l_mailstop;
1438 debug_exit(l_proc);
1439 return 0;
1440 EXCEPTION
1441 WHEN others THEN
1442 IF SQLCODE <> hr_utility.hr_error_number
1443 THEN
1444 debug_others (l_proc, 10);
1445 IF g_debug
1446 THEN
1447 DEBUG ( 'Leaving: '
1448 || l_proc, -999);
1449 END IF;
1450 fnd_message.raise_error;
1451 ELSE
1452 RAISE;
1453 END IF;
1454 END get_address_mailstop;
1455 ------
1456 -- ----------------------------------------------------------------------------
1457 -- |--------------------------< get_emailaddress >-----------------------------|
1458 -- Description: This procedure is to fetch email address of the current Person.
1459 -- This value will be reported in Office address only.
1460 -- ----------------------------------------------------------------------------
1461 FUNCTION get_emailaddress
1462 (
1463 p_emailaddress OUT NOCOPY VARCHAR2
1464 ) RETURN NUMBER
1465 IS
1466 l_proc varchar2(72) := g_package||'.get_emailaddress';
1467 l_email_address per_all_people_f.email_address%TYPE;
1468 BEGIN
1469 debug_enter(l_proc);
1470 l_email_address := NULL;
1471 IF g_current_run = 'CUTOVER' THEN
1472
1473 l_email_address := NVL(g_person_cutover_addresses(l_chg_surrogate_key).email_address,'');
1474
1475 ELSE
1476
1477 l_email_address := g_email_address;
1478
1479 END IF;
1480 /*-- check data type
1481 IF NOT pqp_gb_psi_functions.is_alphanumeric(l_email_address) THEN
1482 -- raise error
1483 debug('ERROR: Invalid Datatype.',40);
1484 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1485 (p_error_number => 94464
1486 ,p_error_text => 'BEN_94464_INVALID_EMAIL_ID'
1487 ,p_token1 => l_chg_surrogate_key
1488 ,p_token2 => l_email_address
1489 );
1490
1491 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_email_address)*/
1492 p_emailaddress := l_email_address;
1493 debug_exit(l_proc);
1494 return 0;
1495 EXCEPTION
1496 WHEN others THEN
1497 IF SQLCODE <> hr_utility.hr_error_number
1498 THEN
1499 debug_others (l_proc, 10);
1500 IF g_debug
1501 THEN
1502 DEBUG ( 'Leaving: '
1503 || l_proc, -999);
1504 END IF;
1505 fnd_message.raise_error;
1506 ELSE
1507 RAISE;
1508 END IF;
1509 END get_emailaddress;
1510 ------
1511 -- ----------------------------------------------------------------------------
1512 -- |------------------------< get_telephone_number_1 >------------------------|
1513 -- Description: This procedure is to fetch Primary telephone number of the current
1514 -- address being processed.
1515 -- ----------------------------------------------------------------------------
1516 FUNCTION get_telephone_number_1
1517 (
1518 p_telephone_number_1 OUT NOCOPY VARCHAR2
1519 ) RETURN NUMBER
1520 IS
1521 l_proc varchar2(72) := g_package||'.get_telephone_number_1';
1522 l_telephone_number_1 per_addresses.telephone_number_1%TYPE;
1523 BEGIN
1524 debug_enter(l_proc);
1525 l_telephone_number_1 := NULL;
1529
1526 IF g_current_run = 'CUTOVER' THEN
1527 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1528 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1530 l_telephone_number_1 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).telephone_number_1,'');
1531
1532 END IF;
1533 ELSE
1534 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1535 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1536
1537 l_telephone_number_1 := NVL(g_person_addresses(l_chg_surrogate_key).telephone_number_1,' ');
1538
1539 END IF;
1540 END IF;
1541 -- check data type
1542 IF NOT pqp_gb_psi_functions.is_alphanumeric_space_allowed(l_telephone_number_1) THEN
1543 -- raise error
1544 debug('ERROR: Invalid Datatype.',40);
1545 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1546 (p_error_number => 94470
1547 ,p_error_text => 'BEN_94470_INVALID_TELE_PH1'
1548 ,p_token1 => l_chg_surrogate_key
1549 ,p_token2 => l_telephone_number_1
1550 );
1551 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_telephone_number_1)
1552
1553 p_telephone_number_1 := l_telephone_number_1;
1554 debug_exit(l_proc);
1555 return 0;
1556 EXCEPTION
1557 WHEN others THEN
1558 IF SQLCODE <> hr_utility.hr_error_number
1559 THEN
1560 debug_others (l_proc, 10);
1561 IF g_debug
1562 THEN
1563 DEBUG ( 'Leaving: '
1564 || l_proc, -999);
1565 END IF;
1566 fnd_message.raise_error;
1567 ELSE
1568 RAISE;
1569 END IF;
1570 END get_telephone_number_1;
1571 ------
1572
1573 -- ----------------------------------------------------------------------------
1574 -- |-----------------------< get_telephone_number_2 >--------------------------|
1575 -- Description: This procedure is to fetch Secondary telephone number of the current
1576 -- address being processed.
1577 -- ----------------------------------------------------------------------------
1578 FUNCTION get_telephone_number_2
1579 (
1580 p_telephone_number_2 OUT NOCOPY VARCHAR2
1581 ) RETURN NUMBER
1582 IS
1583 l_proc varchar2(72) := g_package||'.get_telephone_number_2';
1584 l_telephone_number_2 per_addresses.telephone_number_2%TYPE;
1585 BEGIN
1586 debug_enter(l_proc);
1587 l_telephone_number_2 := NULL;
1588 IF g_current_run = 'CUTOVER' THEN
1589 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1590 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1591
1592 l_telephone_number_2 := NVL(g_person_cutover_addresses(l_chg_surrogate_key).telephone_number_2,'');
1593
1594 END IF;
1595 ELSE
1596 IF (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') OR
1597 (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') THEN
1598
1599 l_telephone_number_2 := NVL(g_person_addresses(l_chg_surrogate_key).telephone_number_2,' ');
1600
1601 END IF;
1602 END IF;
1603 -- check data type
1604 IF NOT pqp_gb_psi_functions.is_alphanumeric_space_allowed(l_telephone_number_2) THEN
1605 -- raise error
1606 debug('ERROR: Invalid Datatype.',40);
1607 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1608 (p_error_number => 94471
1609 ,p_error_text => 'BEN_94471_INVALID_TELE_PH2'
1610 ,p_token1 => g_person_id
1611 ,p_token2 => l_telephone_number_2
1612 );
1613 END IF; --IF NOT pqp_gb_psi_functions.is_numeric(l_telephone_number_2)
1614
1615 p_telephone_number_2 := l_telephone_number_2;
1616 debug_exit(l_proc);
1617 return 0;
1618 EXCEPTION
1619 WHEN others THEN
1620 IF SQLCODE <> hr_utility.hr_error_number
1621 THEN
1622 debug_others (l_proc, 10);
1623 IF g_debug
1624 THEN
1625 DEBUG ( 'Leaving: '
1626 || l_proc, -999);
1627 END IF;
1628 fnd_message.raise_error;
1629 ELSE
1630 RAISE;
1631 END IF;
1632 END get_telephone_number_2;
1633 ------
1634 BEGIN -- address_data_element_value
1635 --
1636 debug_enter(l_proc);
1637 IF (g_current_run = 'CUTOVER' AND g_person_cutover_addresses.COUNT = 0)
1638 OR (g_current_run = 'PERIODIC' AND g_person_addresses.COUNT = 0) THEN
1639
1640 debug('There are no penserver addresses');
1644 debug_exit(l_proc);
1641 g_include_office_address := 'N'; --For Bug 7255335:Changed flag to N
1642 g_include_home_address := 'N';
1643 debug('p_output_value: '||p_output_value,10);
1645 return l_error;
1646
1647 END IF;
1648
1649 -- check if the data element is to check whether office address
1650 -- is to be reported or not.
1651 IF p_ext_user_value = 'OFFICE' then
1652
1653 g_include_office_address := chk_office_address_changed();
1654 debug('p_output_value: '||p_output_value,10);
1655 debug_exit(l_proc);
1656 return l_error;
1657
1658 END IF;
1659 -- check if the data element is to check whether home address
1660 -- is to be reported or not
1661 IF p_ext_user_value = 'HOME' then
1662
1663 g_include_home_address := chk_home_address_changed();
1664 debug('p_output_value: '||p_output_value,10);
1665 debug_exit(l_proc);
1666 return l_error;
1667
1668 END IF;
1669
1670 -- for cutover run, the office/home address id is set while
1671 -- setting the assignment globals. and this is used as the
1672 -- surrogate key to the individual data element functions.
1673 IF g_current_run = 'CUTOVER' THEN
1674 IF g_current_layout = 'OFFICE' THEN
1675 l_chg_surrogate_key := g_office_address_id;
1676 ELSE
1677 l_chg_surrogate_key := g_home_address_id;
1678 END IF;
1679 ELSE
1680 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1681 l_change_table := ben_ext_person.g_chg_pay_table;
1682 l_change_column := ben_ext_person.g_chg_pay_column;
1683 -- added in version 115.15
1684 IF UPPER(l_change_table) = 'PER_ADDRESSES'
1685 AND g_person_addresses.exists(l_chg_surrogate_key)
1686 AND (g_person_addresses(l_chg_surrogate_key).address_type
1687 NOT IN (g_office_address_type,g_home_address_type)
1688 OR g_person_addresses(l_chg_surrogate_key).address_type IS NULL)
1689 AND g_report_non_pen_address = true THEN
1690 IF nvl(g_office_address_reported,false) THEN
1691 debug('Office address reported');
1692 debug('Including the non-penserver address details in home address');
1693 g_include_home_address := 'Y';
1694 ELSE
1695 debug('Office address not reported');
1696 debug('Including the non-penserver address details in office address');
1697 g_include_office_address := 'Y';
1698 END IF;
1699 debug('p_output_value: '||p_output_value,10);
1700 debug_exit(l_proc);
1701 return l_error;
1702 END IF;--IF UPPER(l_change_table) = 'PER_ADDRESSES'
1703 ----------
1704 END IF;--IF g_current_run = 'CUTOVER'
1705
1706 debug('l_change_table: '||l_change_table,10);
1707 debug('l_change_column: '||l_change_column,10);
1708 debug('g_current_layout: '||g_current_layout);
1709 debug('g_effective_date: '||g_effective_date);
1710 debug('g_office_address_changed: '||g_office_address_changed,10);
1711 debug('g_home_address_changed: '||g_home_address_changed,10);
1712
1713 IF (g_current_layout = 'OFFICE' AND g_office_address_changed = 'Y') OR
1714 (g_current_layout = 'HOME' AND g_home_address_changed = 'Y') THEN
1715 debug('Record changed: '||g_current_layout,20);
1716
1717 -- if the chaged values are on per_all_people_f, the surrogate key
1718 -- will be person_id. It is to be set to current address id.
1719 -- The home/office address id are picked up from the respective
1720 -- global variable. These values are set while setting the assignment
1721 -- globals for a cutover run. and during chk_xxx_address_changed function
1722 -- for periodic changes extract.
1723 IF l_change_table <> 'PER_ADDRESSES' THEN
1724 IF g_current_layout = 'OFFICE' THEN
1725 l_chg_surrogate_key := g_office_address_id;
1726 ELSE
1727 l_chg_surrogate_key := g_home_address_id;
1728 END IF;
1729
1730 END IF;
1731 debug('l_chg_surrogate_key: '||l_chg_surrogate_key,10);
1732 IF g_current_run='PERIODIC'
1733 AND NOT (g_person_addresses.exists(l_chg_surrogate_key)) THEN
1734 debug('No addresses for the surrogate key');
1735 IF g_current_layout = 'OFFICE' THEN
1736 g_include_office_address := 'N';
1737 ELSE
1738 g_include_home_address := 'N';
1739 END IF;
1740 debug('p_output_value: '||p_output_value,10);
1741 debug_exit(l_proc);
1742 return l_error;
1743 END IF;
1744 -- fetch the value from the individual data element functions
1745 -- depending on the p_ext_user_value.
1746 if p_ext_user_value = 'AddressCode' then
1747 debug('Fetching Address Code',30);
1748 l_error := get_address_code
1749 (
1753 debug('Fetching AddEffectiveDate',30);
1750 p_address_code => p_output_value
1751 );
1752 elsif p_ext_user_value = 'AddEffectiveDate' then
1754 l_error := get_address_effdate
1755 (
1756 p_address_effdate => p_output_value
1757 );
1758 elsif p_ext_user_value = 'AddressLine1' then
1759 debug('Fetching AddressLine1',30);
1760 l_error := get_address_line1
1761 (
1762 p_address_line1 => p_output_value
1763 );
1764 elsif p_ext_user_value = 'AddressLine2' then
1765 debug('Fetching AddressLine2',30);
1766 l_error := get_address_line2
1767 (
1768 p_address_line2 => p_output_value
1769 );
1770 elsif p_ext_user_value = 'AddressLine3' then
1771 debug('Fetching AddressLine3',30);
1772 l_error := get_address_line3
1773 (
1774 p_address_line3 => p_output_value
1775 );
1776 elsif p_ext_user_value = 'AddressLine4' then
1777 debug('Fetching AddressLine4',30);
1778 l_error := get_address_line4
1779 (
1780 p_address_line4 => p_output_value
1781 );
1782 elsif p_ext_user_value = 'AddressLine5' then
1783 debug('Fetching AddressLine4',30);
1784 l_error := get_address_line5
1785 (
1786 p_address_line5 => p_output_value
1787 );
1788 elsif p_ext_user_value = 'Country' then
1789 debug('Fetching Country',30);
1790 l_error := get_country
1791 (
1792 p_country => p_output_value
1793 );
1794 elsif p_ext_user_value = 'EmailAddress' then
1795 debug('Fetching EmailAddress',30);
1796 l_error := get_emailaddress
1797 (
1798 p_emailaddress => p_output_value
1799 );
1800 elsif p_ext_user_value = 'MailStop' then
1801 debug('Fetching MailStop',30);
1802 l_error := get_address_mailstop
1803 (
1804 p_address_mailstop => p_output_value
1805 );
1806 elsif p_ext_user_value = 'PostCode' then
1807 debug('Fetching PostCode',30);
1808 l_error := get_address_postcode
1809 (
1810 p_address_postcode => p_output_value
1811 );
1812 elsif p_ext_user_value = 'TelephoneNumber1' then
1813 debug('Fetching TelephoneNumber1',30);
1814 l_error := get_telephone_number_1
1815 (
1816 p_telephone_number_1 => p_output_value
1817 );
1818 elsif p_ext_user_value = 'TelephoneNumber2' then
1819 debug('Fetching TelephoneNumber2',30);
1820 l_error := get_telephone_number_2
1821 (
1822 p_telephone_number_2 => p_output_value
1823 );
1824 end if; --if p_ext_user_value
1825 ELSE
1826 debug('No changes in the record: '||g_current_layout);
1827 END IF; --IF (g_current_layout = 'PRIMARY' AND g_primary_address_changed = 'Y') OR
1828 debug('p_output_value: '||p_output_value,10);
1829 debug_exit(l_proc);
1830 return l_error;
1831 EXCEPTION
1832 WHEN others THEN
1833 IF SQLCODE <> hr_utility.hr_error_number
1834 THEN
1835 debug_others (l_proc, 10);
1836 IF g_debug
1837 THEN
1838 DEBUG ( 'Leaving: '
1839 || l_proc, -999);
1840 END IF;
1841 fnd_message.raise_error;
1842 ELSE
1843 RAISE;
1844 END IF;
1845 END address_data_element_value;
1846 ------
1847 -- ----------------------------------------------------------------------------
1848 -- |----------------------< address_post_processing >--------------------------|
1849 -- Description: This is the post-processing rule for the address layout.
1850 -- ----------------------------------------------------------------------------
1851 FUNCTION address_post_processing RETURN VARCHAR2
1852 IS
1853 l_proc varchar2(72) := g_package||'.address_post_processing';
1854 BEGIN
1855 debug_enter(l_proc);
1856
1857 --Raise extract exceptions which are stored while processing the data elements
1858 --debug('Raising the DE errors, with input parameter as S');
1859 --PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions();
1860
1861 --call the common post processing function
1862 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1863
1864 debug_exit(l_proc);
1865 return 'Y';
1866 EXCEPTION
1867 WHEN others THEN
1868 IF SQLCODE <> hr_utility.hr_error_number
1869 THEN
1870 debug_others (l_proc, 10);
1871 IF g_debug
1872 THEN
1873 DEBUG ( 'Leaving: '
1874 || l_proc, -999);
1875 END IF;
1876 fnd_message.raise_error;
1877 ELSE
1878 RAISE;
1879 END IF;
1880 END address_post_processing;
1881 ------
1882 -- ----------------------------------------------------------------------------
1883 -- |----------------------< chk_pen_addresses_exist >--------------------------|
1884 -- Description: This function is used to check if there are any perserver addresses
1885 -- active on a particular date.
1886 -- ----------------------------------------------------------------------------
1887 FUNCTION chk_pen_addresses_exist
1888 (
1889 p_effective_date DATE
1890 ) RETURN BOOLEAN
1891 IS
1892 l_proc varchar2(72) := g_package||'.chk_pen_addresses_exist';
1893 l_index NUMBER;
1894 BEGIN
1895 debug_enter(l_proc);
1896
1897 FOR i IN 1..g_person_addresses.COUNT
1898 LOOP
1899 IF i=1 THEN
1900 l_index := g_person_addresses.FIRST;
1901 ELSE
1902 l_index := g_person_addresses.NEXT(l_index);
1903 END IF; --IF i=1
1904
1905 IF (p_effective_date BETWEEN g_person_addresses(l_index).date_from
1906 AND NVL(g_person_addresses(l_index).date_to,c_highest_date))
1907 AND (g_person_addresses(l_index).address_type IN
1908 (g_office_address_type,g_home_address_type)) THEN
1909
1910 return TRUE;
1911 END IF;--IF (p_effective_date BETWEEN
1912 END LOOP;
1913
1914 debug_exit(l_proc);
1915 return FALSE;
1916 EXCEPTION
1917 WHEN others THEN
1918 IF SQLCODE <> hr_utility.hr_error_number
1919 THEN
1920 debug_others (l_proc, 10);
1921 IF g_debug
1922 THEN
1923 DEBUG ( 'Leaving: '
1924 || l_proc, -999);
1925 END IF;
1926 fnd_message.raise_error;
1927 ELSE
1928 RAISE;
1929 END IF;
1930 END chk_pen_addresses_exist;
1931 ------
1932 END PQP_GB_PSI_ADDDRESS;