1 PACKAGE BODY Hr_General2 AS
2 /* $Header: hrgenrl2.pkb 120.16.12020000.6 2012/12/13 11:58:57 jkvallab ship $ */
3 ------------------------------------------------------------------------------
4 /*
5 +==========================================================================+
6 | Copyright (c) 1994 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==========================================================================+
10 Name
11 General2 HR utilities
12 Purpose
13 To provide widely used functions in a single shared area
14 History
15 stlocke 08-FEB-2001 115.00 Created.
16 ekim 20-JUN-2001 115.1 Added mask_characters function.
17 asahay 18-JUL-2001 115.3 Added is_person_type function.
18 wstallar 28-SEP-2001 115.4 Added functions to support duplicate
19 person checking
20 wstalar 30-SEP-2001 115.5 Added funciotn to derive full name for
21 display in duplicate checks
22 wstallar 03-OCT-2001 115.6 Fixed GET_DUP_NO_SECURITY_STATUS
23 bug 2028926
24 wstallar 26-OCT-2001 115.7 Fixed bug 2059163 - handle fact
25 TCA records null name as '**********'
26 gperry 08-JAN-2002 115.8 Fixed bug 2163957
27 gperry 09-JAN-2002 115.9 Added dbdrv commands.
28 acowan 27-FEB-2002 115.10 Added functions to return
29 assignments status usages
30 dcasemor 19-MAR-2002 115.13 Added chk_utf8_col_length.
31 dcasemor 22-MAR-2002 115.14 Added address_line1, 2 and 3 to
32 chk_utf8_col_length because
33 per_addresses has no underscore.
34 dcasemor 26-MAR-2002 115.15 Scaled down use of
35 chk_utf8_col_length because this
36 has been replaced by
37 hr_utf8_triggers.
38 dcasemor 15-MAR-2002 115.16 Removed chk_utf8_col_length. This
39 now resides in a separate package
40 (hr_utf8_triggers).
41 acowan 16-MAY-2002 115.17 Added validate_upload procedure
42 to handle date checking during
43 ldt upload.
44 acowan 24-MAY-2002 115.18 Removed debug lines.
45 acowan 24-MAY-2002 115.19 Added error handling.
46 dcasemor 06-JUN-2002 115.20 Consolidated is_person_type
47 functions by calling the function
48 in the PTU utility package instead
49 of having duplicated code.
50 skota 20-AUG-2002 115.21 GSCC Changes
51 skota 16-SEP-2002 115.22 Moved validate_upload function code to
52 pespt01t.pkb. validate_upload now
53 refers to the function in pespt01t.pkb
54 pkakar 15-OCT-2002 115.24 Added is_bg function for checking
55 the business_group_id is valid for
56 a specific legislation_code
57 (same as 115.23)
58 pkakar 16-OCT-2002 115.26 Added is_legislation_install for
59 checking to see if a certain
60 legislation has been installed
61 (same as 115.25)
62 prsundar 28-NOV-2002 115.27 Added overloaded procedure for
63 init_fndload
64 gperry 10-DEC-2002 115.28 Fixed WWBUG 2687564.
65 mbocutt 16-Dec-2002 115.29 Fixed bug 2690302. Add join to
66 HZ_PARTIES so the person_last_name
67 index can be used when duplicate
68 checking.
69 dharris 06-Jan-2003 115.30 Added the PRIVATE global var
70 g_oracle_version and PUBLIC
71 function get_oracle_db_version
72 Also added the g_debug and
73 g_package private globals
74 pattwood 17-JAN-2003 115.31 Bug 2651140. Added set_ovn procedure
75 for populating the
76 object_version_number column value
77 when this column has been added to
78 an existing table. Initial version
79 of code is based on hrsetovn.sql
80 version 115.2.
81 fsheikh 30-JAN-2003 115.32 Bug 2706637. Added 3 JP legislation
82 specific parameter to retieve full
83 name as per JP format.
84 dharris 30-Jan-2003 115.33 Modified get_oracle_db_version to
85 place a format model on the
86 returning number for wwbug 2772209.
87 Also removed the raising of an
88 error and replaced by returning
89 NULL.
90 divicker 07-APR-2003 115.36 Defaulted get_dup_full_name 3 JP param
91 divicker 07-APR-2003 115.37 Changed from default to overload
92 defaulting compiles forms but forms
93 that call with 5 params fail at RT
94 fsheikh 01-Sep-2003 115.38 Wrapped first and last name with
95 upper function so as to make duplicate
96 person search case insensitive.
97 ASahay 09-Sep-2003 115.39 Added function is_location_legal_adr
98 sgudiwad 25-Sep-2003 115.40 3136986 Added function decode_vendor for Refresh
99 attributes
100 njaladi 30-dec-2003 115.41 3257115 Added overloaded function is_duplicate_person
101 for JP legislation
102 dcasemor 01-Mar-2004 115.42 3346940 Added supervisor_assignments_in_use.
103 dcasemor 11-Mar-2004 115.43 3346940 Changed supervisor_assignments_in_use
104 so it references a new profile option.
105 alogue 31-Mar-2004 115.44 3544109 Changed all_triggers to user_triggers.
106 kramajey 13-Jun-2005 115.45 4341787 Changed the join in the sql statement
107 in is_duplicate_person procedure
108 for performance in JP Legislation.
109 sgelvi 31-May-2006 115.46 Added hrms_efc_column function
110 risgupta 13-OCT-2006 115.47 5599043 modified is_duplicate_person and removed logic
111 written for JP localization. Modified the other
112 SQL to join HZ_PARTIES so that index on last_name
113 can be used.
114 risgupta 27-NOV-2006 115.51 3988762 Added two overloaded function for is_duplicate_person
115 and also defined a global PL/SQL table to hold
116 duplicate records for the fix of enh duplicate person.
117 pchowdav 14-MAR-2007 115.52 5930576 Modified the procedures is_duplicate_person to
118 perform duplicate check when the profile option
119 HR:Cross BG Duplicate Person Check is null.
120 pchowdav 15-MAR-2007 115.53 5923547 Modified the procedures is_duplicate_person to
121 perform duplicate check and return duplicate records
122 for SSHR ,when the profile option
123 HR:Cross Business Group is set to No.
124 pchowdav 16-MAR-2007 115.54 5933115 Modified the alias of business group id
125 from "BgId" to "BusinessGroupId" in cursor rc
126 in procedure is_duplicate_person.
127 pchowdav 21-MAR-2007 115.55 5946126 Modified the procedures is_duplicate_person
128 to exclude displaying of tca records when
129 the profile 'HR:Cross business group' is set to 'No'
130 in sshr duplicate page.
131 ande 24-MAY-2007 115.56 Modified the size of the varchar2 local variables
132 in is_duplicate_person.
133 pchowdav 23-JAN-2008 115.57 6748256 Modified the cursor c_duplicate_people in
134 function is_duplicate_person and
135 cursor fetching duplicate persons in
136 procedure is_duplicate_person.
137 ktithy 17-APR-2008 115.58 6961892 Added new procedure
138 SERVER_SIDE_PROFILE_PUT
139 which assigns a value to a profile
140 at Server Side.
141 sgundoju 09-FEB-2010 115.59 7609125 Changed the condition of global_name to
142 upper(global_name)
143 ktithy 17-MAR-2011 115.60 10408501 Using rtrim to trim the extra spaces for the p_global_name
144 used in the is_duplicate_person function.
145 karthmoh 05-DEC-2011 115.61 12774028 Added new function reporting_user
146 which returns true if the user is
147 a reporting user.
148 sclakkar 11-OCT-2012 120.16.12020000.3 modifed for Bug#14664920
149 nvankadh 28-OCT-2012 120.16.12020000.5 modified few cursor definitions to refer to dba_tab_columns
150 instead of all_tab_columns, for Bug#15882261
151 jkvallab 13-DEC-2012 120.16.12020000.6 modified cursor for regression in
152 previous revision
153 --------------------------------------------------------------------------
154 */
155
156 g_userenv_lang VARCHAR2(4):=NULL;
157 g_dup_external_name VARCHAR2(2000):=NULL;
158 g_dup_no_match VARCHAR2(2000):=NULL;
159 g_dup_no_security_char VARCHAR2(2000):=NULL;
160 g_oracle_version NUMBER; -- holds the oracle version number and is
161 -- set by get_oracle_db_version
162 g_package VARCHAR2(12) := 'hr_general2.';
163 g_debug BOOLEAN;
164 -- --------------------------------------------------------------------------
165 -- |----------------------< init_fndload procedure >------------------------|
166 -- --------------------------------------------------------------------------
167
168 PROCEDURE init_fndload(
169 p_resp_appl_id IN NUMBER
170 ) IS
171
172 BEGIN
173 Fnd_Global.apps_initialize
174 (user_id => 1
175 ,resp_id => 0
176 ,resp_appl_id => p_resp_appl_id );
177
178 END init_fndload;
179
180
181 --Overloaded procedure for init_fndload
182 PROCEDURE init_fndload(p_resp_appl_id IN NUMBER
183 ,p_user_id IN NUMBER
184 ) IS
185 BEGIN
186 Fnd_Global.apps_initialize
187 (user_id => p_user_id
188 ,resp_id => 0
189 ,resp_appl_id => p_resp_appl_id );
190
191 END init_fndload;
192
193
194 -- --------------------------------------------------------------------------
195 -- |----------------------< mask_characters function >----------------------|
196 -- --------------------------------------------------------------------------
197
198 FUNCTION mask_characters(p_number IN VARCHAR2) RETURN VARCHAR2 IS
199
200 l_mask VARCHAR2(50) := '';
201 l_mask_size NUMBER;
202 l_length_number NUMBER := LENGTH(p_number);
203 l_show_digit NUMBER;
204
205 BEGIN
206 --
207 IF (p_number IS NOT NULL) THEN
208
209 l_mask_size := Fnd_Profile.value_wnps('HR_MASK_CHARACTERS');
210
211 IF (ABS(l_mask_size) > LENGTH(p_number)) THEN
212 l_mask_size := LENGTH(p_number);
213 END IF;
214
215 l_show_digit := -1 * l_mask_size;
216
217 IF l_mask_size > 0 THEN
218
219 l_mask := LPAD(SUBSTR(p_number, l_show_digit),
220 LENGTH(p_number),
221 'X');
222 ELSIF l_mask_size < 0 THEN
223
224 l_mask := RPAD(SUBSTR(p_number, 1, l_show_digit),
225 LENGTH(p_number),
226 'X');
227 END IF;
228
229 ELSE
230 NULL;
231 -- If no account number exists, do nothing.
232 END IF;
233
234 RETURN l_mask;
235 END mask_characters;
236
237 -- --------------------------------------------------------------------------
238 -- |------------------------< is_person_type function >-----------------------|
239 -- --------------------------------------------------------------------------
240
241 FUNCTION is_person_type
242 (p_person_id IN NUMBER,
243 p_person_type IN VARCHAR2,
244 p_effective_date IN DATE
245 )
246 RETURN BOOLEAN IS
247
248 l_exists BOOLEAN;
249
250 BEGIN
251
252 --
253 -- Call the PTU consolidated function.
254 --
255 l_exists := hr_person_type_usage_info.is_person_of_type
256 (p_effective_date => p_effective_date
257 ,p_person_id => p_person_id
258 ,p_system_person_type => p_person_type);
259
260 RETURN l_exists;
261
262 END is_person_type;
263 --Added for fix 0f #3257115 start
264 -- --------------------------------------------------------------------------
265 -- |--------------< is_duplicate_person function (Legislation)>--------------|
266 -- --------------------------------------------------------------------------
267
268 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
269 ,p_last_name IN VARCHAR2
270 ,p_national_identifier IN VARCHAR2
271 ,p_date_of_birth IN DATE
272 ,p_leg_code IN VARCHAR2
273 ,p_first_name_phonetic IN VARCHAR2
274 ,p_last_name_phonetic IN VARCHAR2)
275 RETURN BOOLEAN
276 IS
277 l_duplicate_found BOOLEAN:=FALSE;
278 l_session_date DATE;
279 l_dummy varchar2(1);
280 BEGIN
281 --
282 hr_utility.set_location('11',10);
283 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
284 return l_duplicate_found;
285 end if;
286 -- Get the session date
287 SELECT se.effective_date
288 INTO l_session_date
289 FROM fnd_sessions se
290 WHERE se.session_id =USERENV('sessionid');
291
292 -- Query to detect duplicate person
293 -- Use where exists for performance reasons
294 hr_utility.set_location('12',10);
295 -- commented for bug 5530099
296 /* if p_leg_code = 'JP' then
297 BEGIN
298 SELECT null
299 INTO l_dummy
300 FROM dual
301 WHERE EXISTS
302 (SELECT 1
303 FROM per_all_people_f per
304 WHERE l_session_date BETWEEN per.effective_start_date
305 AND per.effective_end_date
306 AND per.national_identifier=p_national_identifier)
307 or EXISTS
308 (SELECT 1
309 from hz_parties hzp,hz_person_profiles pro
310 where UPPER(hzp.person_last_name_phonetic)=UPPER(p_last_name_phonetic)
311 AND hzp.party_id = pro.party_id
312 AND pro.effective_end_date is NULL
313 AND
314 ( UPPER(pro.person_first_name)=UPPER(p_first_name)
315 OR pro.person_first_name is null
316 OR p_first_name IS NULL)
317 AND
318 ( UPPER(pro.Person_first_name_phonetic)=UPPER(p_first_name_phonetic)
319 OR pro.person_first_name_phonetic is null
320 OR p_first_name_phonetic IS NULL)
321 AND
322 ( UPPER(pro.person_last_name)=UPPER(p_last_name)
323 OR pro.person_last_name is null
324 OR p_last_name IS NULL)
325 AND
326 ( pro.date_of_birth=p_date_of_birth
327 OR pro.date_of_birth IS NULL
328 OR p_date_of_birth IS NULL)
329 );
330 --Row returned, duplicate exists
331 hr_utility.set_location('13',10);
332 l_duplicate_found:=TRUE;
333 EXCEPTION
334 WHEN NO_DATA_FOUND THEN
335 --No row returned, no duplicate exists
336 hr_utility.set_location('15',10);
337 l_duplicate_found:=FALSE;
338 END;
339 ELSE */
340 BEGIN
341 SELECT null
342 INTO l_dummy
343 FROM dual
344 WHERE EXISTS
345 (SELECT 1
346 FROM per_all_people_f per
347 WHERE l_session_date BETWEEN per.effective_start_date
348 AND per.effective_end_date
349 AND per.national_identifier=p_national_identifier)
350 or EXISTS
351 (SELECT 1
352 from hz_person_profiles pro,
353 hz_parties pty
354 where UPPER(pty.person_last_name)=UPPER(p_last_name)
355 AND pty.party_id = pro.party_id
356 AND pro.effective_end_date is NULL
357 AND
358 ( UPPER(pro.Person_first_name)=UPPER(p_first_name)
359 OR pro.person_first_name is null
360 OR p_first_name IS NULL)
361 AND
362 ( pro.date_of_birth=p_date_of_birth
363 OR pro.date_of_birth IS NULL
364 OR p_date_of_birth IS NULL)
365 );
366 --Row returned, duplicate exists
367 hr_utility.set_location('23',10);
368 l_duplicate_found:=TRUE;
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 --No row returned, no duplicate exists
372 hr_utility.set_location('25',10);
373 l_duplicate_found:=FALSE;
374 END;
375 -- END IF;
376 hr_utility.set_location('14',10);
377 RETURN l_duplicate_found;
378 END;
379 --Added for fix 0f #3257115 end
380
381 -- --------------------------------------------------------------------------
382 -- |--------------------< is_duplicate_person function >---------------------|
383 -- --------------------------------------------------------------------------
384 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
385 ,p_last_name IN VARCHAR2
386 ,p_national_identifier IN VARCHAR2
387 ,p_date_of_birth IN DATE)
388 RETURN BOOLEAN
389 IS
390 l_duplicate_found BOOLEAN:=FALSE;
391 l_session_date DATE;
392 l_dummy varchar2(1);
393 BEGIN
394 --
395 hr_utility.set_location('1',10);
396 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
397 return l_duplicate_found;
398 end if;
399 -- Get the session date
400 SELECT se.effective_date
401 INTO l_session_date
402 FROM fnd_sessions se
403 WHERE se.session_id =USERENV('sessionid');
404
405 -- Query to detect duplicate person
406 -- Use where exists for performance reasons
407 hr_utility.set_location('2',10);
408 --
409 -- Fix for 2687564.
410 -- Join was wrong before.
411 --
412 BEGIN
413 SELECT null
414 INTO l_dummy
415 FROM dual
416 WHERE EXISTS
417 (SELECT 1
418 FROM per_all_people_f per
419 WHERE l_session_date BETWEEN per.effective_start_date
420 AND per.effective_end_date
421 AND per.national_identifier=p_national_identifier)
422 or EXISTS
423 (SELECT 1
424 from hz_person_profiles pro,
425 hz_parties pty
426 where UPPER(pty.person_last_name)=UPPER(p_last_name)
427 AND pty.party_id = pro.party_id
428 AND pro.effective_end_date is NULL
429 AND
430 ( UPPER(pro.Person_first_name)=UPPER(p_first_name)
431 OR pro.person_first_name is null
432 OR p_first_name IS NULL)
433 AND
434 ( pro.date_of_birth=p_date_of_birth
435 OR pro.date_of_birth IS NULL
436 OR p_date_of_birth IS NULL)
437 );
438 --Row returned, duplicate exists
439 hr_utility.set_location('3',10);
440 l_duplicate_found:=TRUE;
441 EXCEPTION
442 WHEN NO_DATA_FOUND THEN
443 --No row returned, no duplicate exists
444 hr_utility.set_location('5',10);
445 l_duplicate_found:=FALSE;
446 END;
447 hr_utility.set_location('4',10);
448 RETURN l_duplicate_found;
449 END;
450 -- --------------------------------------------------------------------------
451 -- |-------------------< get_dup_external_name function >--------------------|
452 -- --------------------------------------------------------------------------
453 FUNCTION get_dup_external_name
454 RETURN VARCHAR2
455 IS
456 BEGIN
457 IF (g_dup_external_name IS NULL OR g_userenv_lang<>USERENV('LANG')) THEN
458 g_dup_external_name:=Fnd_Message.get_string('PER','PER_289350_DUP_PER_EXT_NAME');
459 g_userenv_lang:=USERENV('LANG');
460 END IF;
461 RETURN g_dup_external_name;
462 END;
463
464 -- --------------------------------------------------------------------------
465 -- |---------------------< get_dup_no_match function >-----------------------|
466 -- --------------------------------------------------------------------------
467 FUNCTION get_dup_no_match
468 RETURN VARCHAR2
469 IS
470 BEGIN
471 IF (g_dup_no_match IS NULL OR g_userenv_lang<>USERENV('LANG')) THEN
472 g_dup_no_match:=Fnd_Message.get_string('PER','PER_289352_DUP_PER_NO_MATCH');
473 END IF;
474 RETURN g_dup_no_match;
475 END;
476
477 -- --------------------------------------------------------------------------
478 -- |------------------< get_dup_no_security_char function >------------------|
479 -- --------------------------------------------------------------------------
480 FUNCTION get_dup_no_security_char
481 RETURN VARCHAR2
482 IS
483 BEGIN
484 IF (g_dup_no_security_char IS NULL )THEN
485 g_dup_no_security_char:='*';
486 END IF;
487 RETURN g_dup_no_security_char;
488 END;
489
490 -- --------------------------------------------------------------------------
491 -- |-----------------< get_dup_no_security_status function >-----------------|
492 -- --------------------------------------------------------------------------
493 FUNCTION get_dup_security_status(p_party_id IN NUMBER
494 ,p_business_group_id IN NUMBER
495 ,p_person_id IN NUMBER DEFAULT NULL) -- modified for Bug14664920
496 RETURN VARCHAR2
497 IS
498
499 return_value VARCHAR2(2000):=NULL;
500 dummy_value NUMBER;
501 l_sys_pers_type VARCHAR2(2000):=NULL; -- modified for Bug14664920
502
503 BEGIN
504
505 BEGIN -- modified for Bug14664920
506
507 IF p_person_id IS NOT NULL THEN
508
509 BEGIN
510 SELECT system_person_type
511 INTO l_sys_pers_type
512 FROM per_person_type_usages_f pptu
513 ,per_person_types ppt
514 WHERE pptu.person_id = p_person_id
515 AND pptu.person_type_id = ppt.person_type_id;
516
517 EXCEPTION
518 WHEN NO_DATA_FOUND THEN
519 l_sys_pers_type:=null;
520 END;
521
522 END IF;
523
524 IF l_sys_pers_type = 'OTHER' THEN
525 return_value:=get_dup_no_security_char;
526 ELSE
527 BEGIN
528 SELECT 1
529 INTO dummy_value
530 FROM dual
531 WHERE EXISTS (SELECT 1
532 FROM PER_ALL_PEOPLE_F per
533 WHERE per.party_id=p_party_id
534 AND per.business_group_id=Hr_General.get_business_Group_id);
535
536 return_value:=get_dup_no_security_char;
537
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 return_value:=null;
541 END;
542 END IF;
543
544 END;
545
546
547
548 /*
549 BEGIN
550 SELECT per.party_id
551 INTO dummy_value
552 FROM PER_PEOPLE_F per
553 WHERE per.party_id=p_party_id;
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 return_value:=get_dup_no_security_char;
557 END;
558 */
559
560 RETURN return_value;
561 END;
562
563
564 -- --------------------------------------------------------------------------
565 -- |---------------------< get_dup_full_name function >----------------------|
566 -- --------------------------------------------------------------------------
567 -- 5 parameter version
568 FUNCTION get_dup_full_name(p_title IN VARCHAR2
569 ,p_first_name in VARCHAR2
570 ,p_middle_name in VARCHAR2
571 ,p_last_name in VARCHAR2
572 ,p_suffix in VARCHAR2)
573 RETURN VARCHAR2
574 IS
575 l_full_name hz_person_profiles.person_name%TYPE;
576 BEGIN
577 if p_title is not null
578 and p_title<>fnd_api.g_miss_char then
579 l_full_name:=l_full_name||p_title;
580 end if;
581
582 if p_first_name is not null
583 and p_first_name<>fnd_api.g_miss_char then
584 l_full_name:=l_full_name||' '||p_first_name;
585 end if;
586
587 if p_middle_name is not null
588 and p_middle_name<>fnd_api.g_miss_char then
589 l_full_name:=l_full_name||' '||p_middle_name;
590 end if;
591
592 if p_last_name is not null
593 and p_last_name<>fnd_api.g_miss_char then
594 l_full_name:=l_full_name||' '||p_last_name;
595 end if;
596
597 if p_suffix is not null
598 and p_suffix<>fnd_api.g_miss_char then
599 l_full_name:=l_full_name||' '||p_suffix;
600 end if;
601
602 return l_full_name;
603 end;
604
605 -- 8 parameter version
606 FUNCTION get_dup_full_name(p_title IN VARCHAR2
607 ,p_first_name in VARCHAR2
608 ,p_middle_name in VARCHAR2
609 ,p_last_name in VARCHAR2
610 ,p_suffix in VARCHAR2
611 ,p_leg_code in varchar2
612 ,p_jp_fname varchar2
613 ,p_jp_lname varchar2)
614 RETURN VARCHAR2
615 IS
616 l_full_name hz_person_profiles.person_name%TYPE;
617 BEGIN
618 IF (p_leg_code = 'JP') THEN
619 IF (p_last_name is not null
620 and p_last_name<>fnd_api.g_miss_char) THEN
621 l_full_name := l_full_name || p_last_name ;
622 END IF;
623
624 if (p_first_name is not null
625 and p_first_name<>fnd_api.g_miss_char) then
626 l_full_name:=l_full_name||' '||p_first_name;
627 end if;
628
629 IF (p_last_name is not null ) or (p_first_name is not null) THEN
630 l_full_name := l_full_name ||' /';
631 END IF;
632
633 IF (p_jp_lname is not null
634 and p_jp_lname <> fnd_api.g_miss_char) THEN
635 l_full_name := l_full_name ||' '|| p_jp_lname ;
636 END IF;
637
638 IF (p_jp_fname is not null
639 and p_jp_fname <> fnd_api.g_miss_char) THEN
640 l_full_name := l_full_name ||' '|| p_jp_fname ;
641 END IF;
642
643
644 ELSE
645
646 if p_title is not null
647 and p_title<>fnd_api.g_miss_char then
648 l_full_name:=l_full_name||p_title;
649 end if;
650
651 if p_first_name is not null
652 and p_first_name<>fnd_api.g_miss_char then
653 l_full_name:=l_full_name||' '||p_first_name;
654 end if;
655
656 if p_middle_name is not null
657 and p_middle_name<>fnd_api.g_miss_char then
658 l_full_name:=l_full_name||' '||p_middle_name;
659 end if;
660
661 if p_last_name is not null
662 and p_last_name<>fnd_api.g_miss_char then
663 l_full_name:=l_full_name||' '||p_last_name;
664 end if;
665
666 if p_suffix is not null
667 and p_suffix<>fnd_api.g_miss_char then
668 l_full_name:=l_full_name||' '||p_suffix;
669 end if;
670
671 END IF;
672 return l_full_name;
673 end;
674 ------------------------------------------------------------------------------
675
676 procedure return_status_assignment_type
677 (p_status in varchar2
678 ,p_Current_flag out nocopy varchar2
679 ,p_past_flag out nocopy varchar2
680 ,p_cwk_flag out nocopy varchar2
681 ,p_emp_flag out nocopy varchar2
682 ,p_apl_flag out nocopy varchar2) is
683 begin
684 p_Current_flag :='N';
685 p_past_flag :='N';
686 p_cwk_flag :='N';
687 p_emp_flag :='N';
688 p_apl_flag :='N';
689
690 if p_status = 'END' then
691 p_past_flag := 'Y';
692 p_cwk_flag :='Y';
693 p_emp_flag :='Y';
694 p_apl_flag :='Y';
695 elsif p_status IN ('ACCEPTED','ACTIVE_APL','INTERVIEW1','INTERVIEW2','OFFER') then
696 p_apl_flag :='Y';
697 p_current_flag :='Y';
698 elsif p_status IN ('TERM_APL', 'END') then
699 p_apl_flag :='Y';
700 p_past_flag :='Y';
701 elsif p_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') then
702 p_emp_flag :='Y';
703 p_current_flag :='Y';
704 elsif p_status IN ('TERM_ASSIGN', 'END') then
705 p_emp_flag :='Y';
706 p_past_flag :='Y';
707 elsif p_status IN ('ACTIVE_CWK','SUSP_CWK_ASG') then
708 p_cwk_flag :='Y';
709 p_current_flag :='Y';
710 elsif p_status IN ('TERM_CWK', 'END') then
711 p_cwk_flag :='Y';
712 p_past_flag :='Y';
713 end if;
714 end return_status_assignment_type;
715
716 function return_status_types(p_show_emp_flag in varchar2
717 ,p_show_apl_flag in varchar2
718 ,p_show_cwk_flag in varchar2
719 ,p_show_current_flag in varchar2)
720 RETURN varchar2
721 is
722 cursor csr_status_types is
723 select distinct per_system_status from per_assignment_status_Types
724 where active_flag ='Y';
725 in_stmt varchar2(3000);
726 begin
727 in_stmt := '(''''';
728 for c_rec in csr_status_types loop
729 if show_status_type(p_status => c_rec.per_system_status
730 ,p_show_emp_flag => p_show_emp_flag
731 ,p_show_apl_flag => p_show_apl_flag
732 ,p_show_cwk_flag => p_show_cwk_flag
733 ,p_show_current_flag => p_show_current_flag) then
734 if length(in_stmt) >1 then
735 in_stmt:=in_stmt||',';
736 end if;
737 in_stmt :=in_stmt||''''||c_rec.per_system_status||'''';
738 end if;
739 end loop;
740 in_stmt :=in_stmt||')';
741 return in_stmt;
742
743 end;
744
745 function return_assignment_type_text(p_status in varchar2)
746 return varchar2
747 is
748 out_text Varchar2(60);
749 cwk_flag varchar2(1);
750 emp_flag varchar2(1);
751 apl_flag varchar2(1);
752 current_flag varchar2(1);
753 past_flag varchar2(1);
754 begin
755 return_status_assignment_type(p_status => p_status
756 ,p_Current_flag =>current_flag
757 ,p_past_flag =>past_flag
758 ,p_cwk_flag => cwk_flag
759 ,p_emp_flag => emp_flag
760 ,p_apl_flag => apl_flag);
761
762 if cwk_flag = 'Y' then
763 out_text := out_text||'Contingent Worker';
764 end if;
765 if emp_flag = 'Y' then
766 if length(out_text) > 0 then
767 out_text := out_text||'.';
768 end if;
769 out_text := out_text||'Employee';
770 end if;
771 if apl_flag = 'Y' then
772 if length(out_text) > 0 then
773 out_text := out_text||'.';
774 end if;
775 out_text := out_text||'Applicant';
776 end if;
777 return out_text;
778 end;
779
780 function show_status_type(p_status IN Varchar2
781 ,p_show_emp_flag in varchar2
782 ,p_show_apl_flag in varchar2
783 ,p_show_cwk_flag in varchar2
784 ,p_show_current_flag in varchar2)
785 RETURN Boolean
786 is
787 ass_types varchar2(10);
788 cwk_flag varchar2(1);
789 emp_flag varchar2(1);
790 apl_flag varchar2(1);
791 current_flag varchar2(1);
792 past_flag varchar2(1);
793 begin
794
795 return_status_assignment_type(p_status => p_status
796 ,p_Current_flag =>current_flag
797 ,p_past_flag =>past_flag
798 ,p_cwk_flag => cwk_flag
799 ,p_emp_flag => emp_flag
800 ,p_apl_flag => apl_flag);
801
802
803 if ((p_show_emp_flag = 'Y' and emp_flag ='Y')
804 or (p_show_apl_flag = 'Y' and apl_flag ='Y')
805 or (p_show_cwk_flag = 'Y' and cwk_flag ='Y'))
806 and (p_show_current_flag = 'A' or
807 (p_show_current_flag ='Y' and current_flag ='Y')
808 or (p_show_current_flag ='N' and past_flag='Y')) then
809 Return true;
810 else
811 return false;
812 end if;
813 end show_status_type;
814 --
815 -----------------------------------------------------------------------------
816 --
817 -----------------------------------------------------------------------------*
818 -----------------------VALIDATE_UPLOAD---------------------------------------*
819 -----------------------------------------------------------------------------*
820 -- This procedure returns true or false whether an entity should be uploaded
821 -- (Called from UPLOAD_ROW in table handlers)
822 -- In Parameters:
823 -- Upload_mode: if this is force then always returns true
824 -- Table Name: The base table for this entity type
825 -- Table_key_name: The column that holds the key (defined in the lct)
826 -- Table_key_value: The value identifying this entity.
827 --
828 function validate_upload (
829 p_Upload_mode in varchar2,
830 p_Table_name in varchar2,
831 p_new_row_updated_by in varchar2,
832 p_new_row_update_date in date,
833 p_Table_key_name in varchar2,
834 p_table_key_value in varchar2)
835 return boolean
836 is
837 l_result boolean;
838 begin
839 l_result := PER_STARTUP_PERSON_TYPES_PKG.validate_upload(
840 p_Upload_mode => p_Upload_mode,
841 p_Table_name => p_Table_name,
842 p_new_row_updated_by => p_new_row_updated_by,
843 p_new_row_update_date => p_new_row_update_date,
844 p_Table_key_name => p_Table_key_name,
845 p_table_key_value => p_table_key_value);
846 return l_result;
847 end;
848
849 -- --------------------------------------------------------------------------
850 -- |----------------------< IS_BG FUNCTION >--------------------------------|
851 -- --------------------------------------------------------------------------
852
853 --
854 -- This function checks to see if the business_group_id given is a valid id
855 -- for the legislation code. If it is valid, then true is returned
856 --
857
858 FUNCTION is_bg(
859 p_business_group_id in number,
860 p_legislation_code in varchar2)
861 return boolean
862 is
863
864 cursor csr_bg is
865 select 'Y'
866 from per_business_groups pbg
867 where pbg.business_group_id = p_business_group_id
868 and pbg.legislation_code = p_legislation_code;
869
870 l_exists varchar2(1);
871
872 BEGIN
873 --
874 open csr_bg;
875 --
876 fetch csr_bg into l_exists;
877 --
878 if csr_bg%notfound then
879 return false;
880 else
881 return true;
882 end if;
883 --
884 end;
885
886 -- --------------------------------------------------------------------------
887 -- |---------------< IS_LEGISLATION_INTSALL FUNCTION >----------------------|
888 -- --------------------------------------------------------------------------
889
890 --
891 -- This function checks to see if the legislation_code given has been
892 -- installed on the application
893 --
894
895 FUNCTION is_legislation_install(
896 p_application_short_name in varchar2,
897 p_legislation_code in varchar2)
898 return boolean
899 is
900
901 cursor csr_legislation_install is
902 select 'Y'
903 from hr_legislation_installations hli
904 where hli.application_short_name = p_application_short_name
905 and hli.legislation_code = p_legislation_code
906 and hli.status = 'I';
907
908 l_exists varchar2(1);
909
910 BEGIN
911 --
912 open csr_legislation_install;
913 --
914 fetch csr_legislation_install into l_exists;
915 --
916 if csr_legislation_install%notfound then
917 return false;
918 else
919 return true;
920 end if;
921 --
922 end;
923 -- --------------------------------------------------------------------------
924 -- |-------------------< get_oracle_db_version >----------------------------|
925 -- --------------------------------------------------------------------------
926 -- This function returns the current (major) ORACLE version number in the
927 -- format x.x (where x is a number):
928 -- e.g. 8.0, 8.1, 9.0, 9.1
929 -- If for any reason the version number cannot be identified, NULL is
930 -- returned
931 FUNCTION get_oracle_db_version RETURN NUMBER IS
932 l_proc VARCHAR2(72);
933 l_version VARCHAR2(30);
934 l_compatibility VARCHAR2(30);
935 BEGIN
936 g_debug := hr_utility.debug_enabled;
937 IF g_debug THEN
938 l_proc := g_package||'get_oracle_db_version';
939 hr_utility.set_location('Entering:'||l_proc, 5);
940 END IF;
941 -- check to see if the g_oracle_version already exists
942 IF g_oracle_version IS NULL THEN
943 -- get the current ORACLE version and compatibility values
944 dbms_utility.db_version(l_version, l_compatibility);
945 -- the oracle version number is held in the format:
946 -- x.x.x.x.x
947 -- set the version number to the first decimal position
948 -- e.g. 9.1.2.0.0 returns 9.1
949 -- 9.0.1.2.0 returns 9.0
950 -- 8.1.7.3.0 returns 8.1
951 -- 8.0.2.1.0 returns 8.0
952 --
953 -- modified line below to include a NUMBER format model to get
954 -- around, numeric format problems which have been identified
955 -- in wwbug 2772209
956 -- note: an important assumption is made here; the oracle
957 -- version is always returned with a period '.' as a seperator
958 -- regardless of NLS.
959 g_oracle_version :=
960 TO_NUMBER(SUBSTRB(l_version,1,INSTRB(l_version,'.',1,2)-1),'99.99');
961 END IF;
962 IF g_debug THEN
963 hr_utility.set_location('Leaving:'||l_proc, 10);
964 END IF;
965 -- return the value
966 RETURN(g_oracle_version);
967 EXCEPTION
968 WHEN OTHERS THEN
969 -- an unexpected error was raised and is most probably caused by
970 -- the TO_NUMBER conversion. Because of this, return NULL
971 -- indicating that the Oracle Version number could NOT be assertained
972 IF g_debug THEN
973 hr_utility.set_location('Leaving:'||l_proc, 15);
974 END IF;
975 RETURN(NULL);
976 END get_oracle_db_version;
977 --
978 -- --------------------------------------------------------------------------
979 -- |---------------------------------< set_ovn >----------------------------|
980 -- --------------------------------------------------------------------------
981 --
982 procedure set_ovn
983 (p_account_owner in varchar2
984 ,p_table_name in varchar2
985 ) is
986 -- Bug 15914681
987
988 -- Bug 15983018 : Modified cursor definition to refer dba_tab_columns instead of
989 -- all_tab_columns
990 cursor cur_tab_sel (p_owner VARCHAR2, p_table VARCHAR2) is
991 select distinct atc.table_name table_name
992 from dba_tab_columns atc
993 , user_synonyms usy
994 where atc.column_name = 'OBJECT_VERSION_NUMBER'
995 and atc.nullable = 'Y'
996 and ( ( substr(usy.synonym_name,1,3) in
997 ('BEN','DT_','FF_','PER','PAY','HR_'
998 ,'OTA', 'SSP', 'GHR', 'HXT')
999 and substr(usy.synonym_name,1,5) <> 'HR_S_'
1000 and p_table = 'ALL' )
1001 or ( usy.synonym_name = p_table and p_table <> 'ALL' )
1002 )
1003 and atc.owner = p_owner
1004 and atc.table_name = usy.table_name
1005 and atc.owner = usy.table_owner
1006 and not exists
1007 (select 1
1008 from all_views uv
1009 where uv.view_name = atc.table_name
1010 and uv.owner = p_owner)
1011 order by 1;
1012
1013
1014
1015
1016 --
1017 l_ovn_trigger all_triggers.trigger_name%TYPE := null;
1018 l_proc varchar2(72);
1019 --
1020 -- Local function establishes for a single database table
1021 -- if an OVN trigger exists and the full trigger name.
1022 --
1023 function get_ovn_trigger
1024 (p_table_name in varchar2)
1025 return varchar2 is
1026 l_ovn_trigger_name all_triggers.trigger_name%TYPE := null;
1027 begin
1028 select alt.trigger_name
1029 into l_ovn_trigger_name
1030 from user_triggers alt
1031 where alt.table_name = p_table_name
1032 and alt.trigger_name like '%_OVN';
1033 --
1034 return l_ovn_trigger_name;
1035 exception
1036 when no_data_found then
1037 return null;
1038 end get_ovn_trigger;
1039 --
1040 begin
1041 g_debug := hr_utility.debug_enabled;
1042 if g_debug then
1043 l_proc := g_package||'set_ovn';
1044 hr_utility.set_location('Entering:'||l_proc, 10);
1045 end if;
1046 --
1047 -- Loop for all tables which match the given parameter criteria
1048 --
1049 for trec in cur_tab_sel(upper(p_account_owner)
1050 ,upper(p_table_name)
1051 ) Loop
1052 --
1053 -- Establish if an _OVN trigger exists and it's name.
1054 --
1055 l_ovn_trigger := get_ovn_trigger(trec.table_name);
1056 --
1057 -- If an _OVN trigger exists then disable it.
1058 --
1059 if l_ovn_trigger is not null then
1060 execute immediate 'alter trigger ' || l_ovn_trigger || ' disable';
1061 end if;
1062 --
1063 -- Update existing rows where the object_version_number
1064 -- is currently null.
1065 --
1066 execute immediate 'update ' || trec.table_name || ' set ' ||
1067 'object_version_number = 1 where ' ||
1068 'object_version_number is null';
1069 --
1070 -- If an _OVN trigger exists then re-enable it.
1071 --
1072 if l_ovn_trigger is not null then
1073 execute immediate 'alter trigger ' || l_ovn_trigger || ' enable';
1074 end if;
1075 End Loop;
1076 if g_debug then
1077 hr_utility.set_location(' Leaving:'||l_proc, 20);
1078 end if;
1079 end set_ovn;
1080 --
1081 -- --------------------------------------------------------------------------
1082 -- |----------------< IS_LOCATION_LEGAL_ADR FUNCTION >----------------------|
1083 -- --------------------------------------------------------------------------
1084 --
1085 -- This function checks to see if the location is defined as a legal address
1086 --
1087
1088 FUNCTION is_location_legal_adr(
1089 p_location_id in NUMBER)
1090 return boolean
1091 is
1092
1093 cursor csr_location_legal_adr is
1094 select 'Y'
1095 from hr_locations_all loc
1096 where loc.location_id = p_location_id
1097 and nvl(loc.legal_address_flag,'N') = 'Y';
1098
1099 l_exists varchar2(1);
1100
1101 BEGIN
1102 --
1103 open csr_location_legal_adr;
1104 --
1105 fetch csr_location_legal_adr into l_exists;
1106 --
1107 if csr_location_legal_adr%NOTFOUND then
1108 return false;
1109 else
1110 return true;
1111 end if;
1112 --
1113 end is_location_legal_adr;
1114
1115
1116 -- newly added vendor_id attribute decode function and fix for Bug#3136986
1117 -----------------------------------------------------------------------
1118 function DECODE_VENDOR (
1119
1120 --
1121 p_vendor_id number) return varchar2 is
1122 --
1123 cursor csr_lookup is
1124 select vendor_name
1125 from po_vendors
1126 where vendor_id = p_vendor_id;
1127 --
1128 v_meaning po_vendors.vendor_name%TYPE := null;
1129 --
1130 begin
1131 --
1132 -- Only open the cursor if the parameter is going to retrieve anything
1133 --
1134 if p_vendor_id is not null then
1135 --
1136 open csr_lookup;
1137 fetch csr_lookup into v_meaning;
1138 close csr_lookup;
1139 --
1140 end if;
1141 --
1142 return v_meaning;
1143 end DECODE_VENDOR;
1144
1145 -- --------------------------------------------------------------------------
1146 -- |---------------< SUPERVISOR_ASSIGNMENTS_IN_USE >------------------------|
1147 -- --------------------------------------------------------------------------
1148
1149 function supervisor_assignments_in_use
1150 return VARCHAR2 is
1151
1152 begin
1153
1154 --
1155 -- If the profile restricts by assignment-based supervisor hierarchies,
1156 -- return true.
1157 --
1158 IF NVL(fnd_profile.value('HR_SUPERVISOR_HIERARCHY_USAGE'), 'P') = 'A' THEN
1159 RETURN 'TRUE';
1160 ELSE
1161 RETURN 'FALSE';
1162 END IF;
1163
1164 end supervisor_assignments_in_use;
1165
1166 -- --------------------------------------------------------------------------
1167 -- |----------------------< HRMS_EFC_COLUMN >-------------------------------|
1168 -- --------------------------------------------------------------------------
1169 --
1170 -- This function determines whether the column sent as parameter
1171 -- is a candidate for EFC
1172 --
1173 function hrms_efc_column (p_table_name IN VARCHAR2,p_column_name in varchar2 )
1174 return varchar2 is
1175 --
1176 l_efc_flag VARCHAR2(10);
1177 cursor efc_col(table_name IN VARCHAR2, column_name IN VARCHAR2) is
1178 select 'TRUE' from sys.dual where
1179 (column_name like '%AMOUNT%' or column_name like '%AMT%' or
1180 column_name like '%VAL%' or column_name like '%COST%' or
1181 column_name like '%PRICE%' or column_name like '%FEE%' or
1182 column_name like '%MIN%' or column_name like '%MAX%' or
1183 column_name like '%COMPENSATION%' or column_name like '%COMPNSTN%' or
1184 column_name like '%CURRENCY%' or column_name like '%PAY%' or
1185 column_name like '%SAL%' or column_name like '%RATE%' or
1186 column_name like '%LIMIT') and
1187 (column_name not like '%CD' and column_name not like '%ID' and
1188 column_name not like '%ID_' and
1189 column_name not like '%RL' and column_name not like '%FLAG' and
1190 column_name not like '%APPROVAL%' and
1191 column_name not like '%DATE%' and column_name not like '%NUMBER' and
1192 column_name not like '%NAME' and
1193 column_name not like '%REASON%' and column_name not like '%TERMINATION%' and
1194 column_name not like '%FROM' and
1195 column_name not like '%TO' and column_name not like '%DESCRIPTION%' and
1196 column_name not like '%COMMENTS%' and
1197 column_name not like '%FORMULA%' and column_name not like '%PERIOD' and
1198 column_name not like '%PERIODS' and
1199 column_name not like '%FREQUENCY' and column_name not like '%APPRAISAL%' and
1200 column_name not like 'AGE%' and
1201 column_name not like '%\_AGE%' ESCAPE '\' and column_name not like '% AGE' and
1202 column_name not like '%TYPE' and
1203 column_name not like '%STATUS%' and column_name not like '%INDICATOR' and
1204 column_name not like '%IDENTIFIER' and
1205 column_name not like '%DAYS' and column_name not like '%WEEKS' and
1206 column_name not like '%WEEK' and
1207 column_name not like '%VALUE_SET' and column_name not like '%VALUESET' and
1208 column_name not like '%VALUE SET' and
1209 column_name not like '%CATEGORY' and column_name not like '%KEY%' and
1210 column_name not like '%PARAMETER%' and
1211 column_name not like '%MULTIPLE' and column_name not like '%METHOD' and
1212 column_name not like '%CENTER' and
1213 column_name not like '%PLAN' and column_name not like '%DURATION%' and
1214 column_name not like '%YEARS' and
1215 column_name not like '%YEAR' and column_name not like '%IDENTIFICATION') and
1216 ((instr(column_name,'CODE')>0 and column_name like '%CURRENCY%CODE') or
1217 (instr(column_name,'CODE')=0));
1218
1219 cursor efc_col_ben(table_name IN VARCHAR2, column_name IN VARCHAR2) is
1220 select 'TRUE' from sys.dual where
1221 (column_name like '%AMOUNT%' or column_name like '%AMT%' or
1222 column_name like '%VAL%' or column_name like '%COST%' or
1223 column_name like '%PRICE%' or column_name like '%FEE%' or
1224 column_name like '%MIN%' or column_name like '%MAX%' or
1225 column_name like '%COMPENSATION%' or column_name like '%COMPNSTN%' or
1226 column_name like '%CURRENCY%' or column_name like '%UOM%' or
1227 column_name like '%CRN%' or column_name like '%PAY%' or
1228 column_name like '%SAL%' or column_name like '%RATE%' or
1229 column_name like 'MX%' or column_name like 'MN%' or
1230 column_name like '%LIMIT') and
1231 (column_name not like '%CD' and column_name not like '%ID' and
1232 column_name not like '%ID_' and column_name not like '%RL' and
1233 column_name not like '%FLAG' and column_name not like '%APPROVAL%' and
1234 column_name not like '%DATE%' and column_name not like '%NUMBER' and
1235 column_name not like '%NAME' and column_name not like '%REASON%' and
1236 column_name not like '%TERMINATION%' and column_name not like '%FROM' and
1237 column_name not like '%TO' and column_name not like '%DESCRIPTION%' and
1238 column_name not like '%COMMENTS%' and column_name not like '%FORMULA%' and
1239 column_name not like '%PERIOD' and column_name not like '%PERIODS' and
1240 column_name not like '%FREQUENCY' and column_name not like '%APPRAISAL%' and
1241 column_name not like 'AGE%' and column_name not like '%\_AGE%' ESCAPE '\' and
1242 column_name not like '% AGE' and column_name not like '%TYPE' and
1243 column_name not like '%STATUS%' and column_name not like '%INDICATOR' and
1244 column_name not like '%IDENTIFIER' and column_name not like '%DAYS' and
1245 column_name not like '%WEEKS' and column_name not like '%WEEK' and
1246 column_name not like '%VALUE_SET' and column_name not like '%VALUESET' and
1247 column_name not like '%VALUE SET' and column_name not like '%CATEGORY' and
1248 column_name not like '%KEY%' and column_name not like '%PARAMETER%' and
1249 column_name not like '%MULTIPLE' and column_name not like '%METHOD' and
1250 column_name not like '%PLAN' and column_name not like '%CENTER'and
1251 column_name not like '%DURATION%' and column_name not like '%YEARS' and
1252 column_name not like '%YEAR' and column_name not like '%IDENTIFICATION' and
1253 column_name not like '%\_NUM' ESCAPE '\') and
1254 ((instr(column_name,'CODE')>0 and column_name like '%CURRENCY%CODE')
1255 or (instr(column_name,'CODE')=0));
1256 begin
1257
1258 if(instr(p_table_name,'BEN') = 1)then
1259 open efc_col_ben(p_table_name, p_column_name);
1260 fetch efc_col_ben into l_efc_flag;
1261 close efc_col_ben;
1262 else
1263 open efc_col(p_table_name,p_column_name);
1264 fetch efc_col into l_efc_flag;
1265 close efc_col;
1266 end if;
1267
1268 if(l_efc_flag = 'TRUE') then
1269 return l_efc_flag;
1270 else
1271 return NULL;
1272 end if;
1273 end hrms_efc_column;
1274
1275 -- --------------------------------------------------------------------------
1276 -- |--------------------< is_duplicate_person function ( uses global_name>--|
1277 -- --------------------------------------------------------------------------
1278 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
1279 ,p_last_name IN VARCHAR2
1280 ,p_national_identifier IN VARCHAR2
1281 ,p_date_of_birth IN DATE
1282 ,p_global_name IN VARCHAR2
1283 ,p_dup_tbl OUT nocopy hr_general2.party_id_tbl
1284 )
1285 RETURN BOOLEAN
1286 IS
1287 l_session_date date;
1288 l_duplicate_found BOOLEAN:=FALSE;
1289 l_ul_check varchar2(30);
1290 l_lu_check varchar2(30);
1291 l_uu_check varchar2(30);
1292 l_ll_check varchar2(30);
1293 l_first_char VARCHAR2(10);
1294 l_second_char varchar2(10);
1295
1296 -- Bug 3988762
1297 -- cursor c_duplicate_people introduced for duplicate person check enhancement
1298 -- this will be used in is_duplicate_person overloaded function
1299 -- 1) For PUI the cursor will be fetched in a PL/SQL table
1300 -- 2) For SSHR the cursor will be feteched and returned as XML CLOB
1301 -- NOTE : Any changes made to the below cursor MUST also be made to the cursor used for SSHR
1302 cursor c_duplicate_people ( p_session_date date,p_ni varchar2,p_global_name varchar2,p_dob date,p_last_name varchar2
1303 ,p_first_name varchar2,l_ul_check varchar2,l_lu_check varchar2,l_uu_check varchar2
1304 ,l_ll_check varchar2 ) is
1305 SELECT per.party_id party_id
1306 ,per.person_id person_id
1307 ,hr_general2.get_dup_security_status ( per.party_id
1308 ,per.business_group_id
1309 ,per.person_id) security_status -- modified for Bug14664920
1310 ,per.global_name person_name
1311 ,nvl(bg.name,hr_general2.get_dup_external_name) bg_name
1312 ,loc.location_code location_code
1313 ,org.NAME org_name
1314 ,adr.postal_code postal_code
1315 ,SUBSTR(per.national_identifier,-4,4) national_identifier
1316 ,per.business_group_id bg_id
1317 FROM per_all_people_f per
1318 ,per_all_assignments_f ass
1319 ,per_business_groups_perf bg
1320 ,hr_locations_all loc
1321 ,hr_all_organization_units org
1322 ,per_addresses adr
1323 WHERE ass.person_id(+) = per.person_id
1324 AND NVL(ass.primary_flag(+),'Y') = 'Y'
1325 AND NVL(ass.assignment_type(+),'E') = 'E'
1326 AND p_session_date BETWEEN NVL(ass.effective_start_date(+),p_session_date) AND NVL(ass.effective_end_date(+),p_session_date)
1327 AND bg.business_group_id = per.business_Group_id
1328 AND loc.location_id(+) = ass.location_id
1329 AND org.organization_id(+) = ass.organization_id
1330 AND adr.person_id(+) = per.person_id
1331 AND NVL(adr.primary_flag(+),'Y') = 'Y'
1332 AND p_session_date BETWEEN NVL(adr.date_from(+),p_session_date) AND NVL(adr.date_to(+),p_session_date)--fix for bug 6748256.
1333 AND p_session_date BETWEEN NVL(per.effective_start_date,p_session_date) AND NVL(per.effective_end_date,p_session_date)
1334 AND (
1335 per.national_identifier = p_ni
1336 OR
1337 (
1338 global_name = p_global_name
1339 -- added conditions to use index
1340 AND
1341 (
1342 global_name like l_ul_check OR
1343 global_name like l_lu_check OR
1344 global_name like l_uu_check OR
1345 global_name like l_ll_check
1346 )
1347 AND
1348 (
1349 per.date_of_birth = p_dob
1350 OR per.date_of_birth IS NULL OR p_dob IS NULL
1351 )
1352 )
1353 )
1354 UNION
1355 SELECT pty.party_id party_id
1356 ,to_number(NULL) person_id
1357 ,NULL security_status
1358 ,hr_general2.get_dup_full_name
1359 (pty.person_title
1360 ,pty.person_first_name
1361 ,pty.person_middle_name
1362 ,pty.person_last_name
1363 ,pty.person_name_suffix
1364 ) person_name
1365 ,hr_general2.get_dup_external_name bg_name
1366 ,NULL location_code
1367 ,NULL org_name
1368 ,NULL postal_code
1369 ,NULL national_identifier
1370 ,NULL bg_id
1371 FROM hz_person_profiles pty,
1372 hz_parties par
1373 WHERE pty.party_id = par.party_id
1374 AND par.orig_system_reference NOT LIKE 'PER%'
1375 AND par.party_type = 'PERSON'
1376 AND pty.effective_end_date is NULL
1377 AND (
1378 (
1379 (
1380 UPPER(par.person_last_name) = UPPER(p_last_name)
1381 --OR par.person_last_name is null
1382 )
1383 AND
1384 (
1385 UPPER(par.person_first_name) = UPPER(p_first_name)
1386 OR par.person_first_name IS NULL OR p_first_name IS NULL
1387 )
1388 AND
1389 (
1390 pty.date_of_birth = p_dob
1391 OR pty.date_of_birth IS NULL OR p_dob IS NULL
1392 )
1393 )
1394 );
1395
1396 begin
1397
1398 hr_utility.set_location('1',10);
1399
1400 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
1401 hr_utility.set_location('2',10);
1402 return l_duplicate_found;
1403 else
1404 if nvl(fnd_profile.value('HR_DUPLICATE_PERSON_CHECK'),'Y') = 'Y' then --fix for bug 5930576
1405 hr_utility.set_location('3',10);
1406 -- Get the session date
1407 SELECT se.effective_date
1408 INTO l_session_date
1409 FROM fnd_sessions se
1410 WHERE se.session_id =USERENV('sessionid');
1411
1412 -- Query to detect duplicate person
1413 -- Use where exists for performance reasons
1414 hr_utility.set_location('4',10);
1415
1416 -- Global name variable for index usage
1417 l_first_char := substr( p_global_name , 1 , 1 ) ;
1418 l_second_char := substr( p_global_name , 2 , 1 ) ;
1419
1420 l_ul_check := upper(l_first_char)||lower(l_second_char)||'%';
1421 l_lu_check := lower(l_first_char)||upper(l_second_char)||'%';
1422 l_uu_check := upper(l_first_char)||upper(l_second_char)||'%';
1423 l_ll_check := lower(l_first_char)||lower(l_second_char)||'%';
1424
1425 hr_utility.set_location('5',10);
1426
1427 -- Open, fetch and close cursor c_duplicate_people. -- Fix For Bug # 10408501
1428 open c_duplicate_people
1429 ( l_session_date, p_national_identifier, rtrim(p_global_name), p_date_of_birth, p_last_name, p_first_name,l_ul_check,l_lu_check,l_uu_check,l_ll_check );
1430
1431 fetch c_duplicate_people bulk collect
1432 into p_dup_tbl;
1433
1434 close c_duplicate_people;
1435
1436 l_duplicate_found := TRUE;
1437 hr_utility.set_location('5',10);
1438 else
1439 l_duplicate_found := FALSE;
1440 end if;
1441 end if;
1442 RETURN l_duplicate_found;
1443 end is_duplicate_person;
1444
1445 -- --------------------------------------------------------------------------
1446 -- |---------< is_duplicate_person function ( FOR SSHR returns XML CLOB) >--|
1447 -- --------------------------------------------------------------------------
1448
1449 procedure is_duplicate_person(
1450 p_business_group_id in per_all_people_f.business_group_id%TYPE
1451 ,p_first_name IN VARCHAR2
1452 ,p_last_name IN VARCHAR2
1453 ,p_national_identifier IN VARCHAR2
1454 ,p_date_of_birth IN DATE
1455 ,p_per_information1 VARCHAR2 DEFAULT NULL
1456 ,p_per_information2 VARCHAR2 DEFAULT NULL
1457 ,p_per_information3 VARCHAR2 DEFAULT NULL
1458 ,p_per_information4 VARCHAR2 DEFAULT NULL
1459 ,p_per_information5 VARCHAR2 DEFAULT NULL
1460 ,p_per_information6 VARCHAR2 DEFAULT NULL
1461 ,p_per_information7 VARCHAR2 DEFAULT NULL
1462 ,p_per_information8 VARCHAR2 DEFAULT NULL
1463 ,p_per_information9 VARCHAR2 DEFAULT NULL
1464 ,p_per_information10 VARCHAR2 DEFAULT NULL
1465 ,p_per_information11 VARCHAR2 DEFAULT NULL
1466 ,p_per_information12 VARCHAR2 DEFAULT NULL
1467 ,p_per_information13 VARCHAR2 DEFAULT NULL
1468 ,p_per_information14 VARCHAR2 DEFAULT NULL
1469 ,p_per_information15 VARCHAR2 DEFAULT NULL
1470 ,p_per_information16 VARCHAR2 DEFAULT NULL
1471 ,p_per_information17 VARCHAR2 DEFAULT NULL
1472 ,p_per_information18 VARCHAR2 DEFAULT NULL
1473 ,p_per_information19 VARCHAR2 DEFAULT NULL
1474 ,p_per_information20 VARCHAR2 DEFAULT NULL
1475 ,p_per_information21 VARCHAR2 DEFAULT NULL
1476 ,p_per_information22 VARCHAR2 DEFAULT NULL
1477 ,p_per_information23 VARCHAR2 DEFAULT NULL
1478 ,p_per_information24 VARCHAR2 DEFAULT NULL
1479 ,p_per_information25 VARCHAR2 DEFAULT NULL
1480 ,p_per_information26 VARCHAR2 DEFAULT NULL
1481 ,p_per_information27 VARCHAR2 DEFAULT NULL
1482 ,p_per_information28 VARCHAR2 DEFAULT NULL
1483 ,p_per_information29 VARCHAR2 DEFAULT NULL
1484 ,p_per_information30 VARCHAR2 DEFAULT NULL
1485 ,p_duplicate_exists out nocopy integer
1486 ,p_dup_clob OUT nocopy CLOB
1487 )
1488 IS
1489 l_session_date date;
1490 l_duplicate_found BOOLEAN:=FALSE;
1491 l_ul_check varchar2(30);
1492 l_lu_check varchar2(30);
1493 l_uu_check varchar2(30);
1494 l_ll_check varchar2(30);
1495 l_first_char VARCHAR2(10);
1496 l_second_char varchar2(10);
1497 qryCtx DBMS_XMLGEN.ctxHandle;
1498
1499 rc SYS_REFCURSOR;
1500
1501 l_full_name varchar2(2000);
1502 l_order_name varchar2(2000);
1503 l_global_name varchar2(2000);
1504 l_local_name varchar2(2000);
1505 l_duplicate_name varchar2(1);
1506
1507 begin
1508
1509 -- get the global name
1510 hr_person_name.derive_person_names
1511 (p_format_name => 'LIST_NAME'
1512 ,p_business_group_id => p_business_group_id
1513 ,p_first_name => p_first_name
1514 ,p_middle_names => null
1515 ,p_last_name => p_last_name
1516 ,p_known_as => null
1517 ,p_title => null
1518 ,p_suffix => null
1519 ,p_pre_name_adjunct => null
1520 ,p_date_of_birth => p_date_of_birth
1521 ,p_previous_last_name => null
1522 ,p_email_address => null
1523 ,p_employee_number => null
1524 ,p_applicant_number => null
1525 ,p_npw_number => null
1526 ,p_per_information1 => p_per_information1
1527 ,p_per_information2 => p_per_information2
1528 ,p_per_information3 => p_per_information3
1529 ,p_per_information4 => p_per_information4
1530 ,p_per_information5 => p_per_information5
1531 ,p_per_information6 => p_per_information6
1532 ,p_per_information7 => p_per_information7
1533 ,p_per_information8 => p_per_information8
1534 ,p_per_information9 => p_per_information9
1535 ,p_per_information10 => p_per_information10
1536 ,p_per_information11 => p_per_information11
1537 ,p_per_information12 => p_per_information12
1538 ,p_per_information13 => p_per_information13
1539 ,p_per_information14 => p_per_information14
1540 ,p_per_information15 => p_per_information15
1541 ,p_per_information16 => p_per_information16
1542 ,p_per_information17 => p_per_information17
1543 ,p_per_information18 => p_per_information18
1544 ,p_per_information19 => p_per_information19
1545 ,p_per_information20 => p_per_information20
1546 ,p_per_information21 => p_per_information21
1547 ,p_per_information22 => p_per_information22
1548 ,p_per_information23 => p_per_information23
1549 ,p_per_information24 => p_per_information24
1550 ,p_per_information25 => p_per_information25
1551 ,p_per_information26 => p_per_information26
1552 ,p_per_information27 => p_per_information27
1553 ,p_per_information28 => p_per_information28
1554 ,p_per_information29 => p_per_information29
1555 ,p_per_information30 => p_per_information30
1556 ,p_full_name => l_full_name
1557 ,p_order_name => l_order_name
1558 ,p_global_name => l_global_name
1559 ,p_local_name => l_local_name
1560 );
1561 hr_utility.set_location(l_global_name || ' SSHR',11);
1562
1563
1564 -- Get the session date
1565 SELECT se.effective_date
1566 INTO l_session_date
1567 FROM fnd_sessions se
1568 WHERE se.session_id = USERENV('sessionid');
1569
1570
1571 hr_utility.set_location('SSHR',10);
1572
1573 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
1574 hr_utility.set_location('SSHR',10.1);
1575 --fix for bug 5923547 starts here.
1576 hr_person_name.derive_person_names
1577 (p_format_name => null
1578 ,p_business_group_id => p_business_group_id
1579 ,p_person_id => null
1580 ,p_first_name => p_first_name
1581 ,p_middle_names => null
1582 ,p_last_name => p_last_name
1583 ,p_known_as => null
1584 ,p_title => null
1585 ,p_suffix => null
1586 ,p_pre_name_adjunct => null
1587 ,p_date_of_birth => p_date_of_birth
1588 ,p_previous_last_name=> null
1589 ,p_email_address => null
1590 ,p_employee_number => null
1591 ,p_applicant_number => null
1592 ,p_npw_number => null
1593 ,p_per_information1 => p_per_information1
1594 ,p_per_information2 => p_per_information2
1595 ,p_per_information3 => p_per_information3
1596 ,p_per_information4 => p_per_information4
1597 ,p_per_information5 => p_per_information5
1598 ,p_per_information6 => p_per_information6
1599 ,p_per_information7 => p_per_information7
1600 ,p_per_information8 => p_per_information8
1601 ,p_per_information9 => p_per_information9
1602 ,p_per_information10 => p_per_information10
1603 ,p_per_information11 => p_per_information11
1604 ,p_per_information12 => p_per_information12
1605 ,p_per_information13 => p_per_information13
1606 ,p_per_information14 => p_per_information14
1607 ,p_per_information15 => p_per_information15
1608 ,p_per_information16 => p_per_information16
1609 ,p_per_information17 => p_per_information17
1610 ,p_per_information18 => p_per_information18
1611 ,p_per_information19 => p_per_information19
1612 ,p_per_information20 => p_per_information20
1613 ,p_per_information21 => p_per_information21
1614 ,p_per_information22 => p_per_information22
1615 ,p_per_information23 => p_per_information23
1616 ,p_per_information24 => p_per_information24
1617 ,p_per_information25 => p_per_information25
1618 ,p_per_information26 => p_per_information26
1619 ,p_per_information27 => p_per_information27
1620 ,p_per_information28 => p_per_information28
1621 ,p_per_information29 => p_per_information29
1622 ,p_per_information30 => p_per_information30
1623 ,p_full_name => l_full_name
1624 ,p_order_name => l_order_name
1625 ,p_global_name => l_global_name
1626 ,p_local_name => l_local_name
1627 ,p_duplicate_flag => l_duplicate_name
1628 );
1629
1630 hr_utility.set_location('SSHR',10.2);
1631 if l_duplicate_name = 'Y' then
1632 open rc for
1633 select per.party_id "PartyId",
1634 per.person_id "PersonId",
1635 hr_general2.get_dup_security_status(per.party_id,per.business_group_id
1636 ,per.person_id) "SecurityStatus", -- modified for Bug14664920
1637 hr_general2.get_dup_full_name(hr_general.decode_lookup('TITLE',per.title),per.first_name, per.middle_names,per.last_name,per.suffix,hr_api.return_legislation_code(per.business_group_id),per.per_information19,per.per_information18) "PersonName",
1638 NVL(bg.name,hr_general2.get_dup_external_name) "BgName",
1639 loc.location_code "LocationCode",
1640 org.name "OrgName",
1641 adr.postal_code "PostalCode",
1642 SUBSTR(per.national_identifier,-4,4) "NationalIdentifier",
1643 per.business_group_id "BusinessGroupId"
1644 from per_all_people_f per
1645 ,per_all_assignments_f ass
1646 ,hr_all_organization_units_tl bg
1647 ,hr_locations_all_tl loc
1648 ,hr_all_organization_units_tl org
1649 ,per_addresses adr
1650 where ass.person_id(+) = per.person_id
1651 and ass.primary_flag(+) = 'Y'
1652 and ass.assignment_type(+) = 'E'
1653 and per.business_group_id = p_business_group_id
1654 and l_session_date between nvl(ass.effective_start_date(+),l_session_date) and nvl(ass.effective_end_date(+),l_session_date)
1655 and bg.organization_id = per.business_Group_id
1656 and bg.language = userenv('LANG')
1657 and loc.location_id (+) = ass.location_id
1658 and loc.language (+) = userenv('LANG')
1659 and org.organization_id(+) = ass.organization_id
1660 and org.language(+) = userenv('LANG')
1661 and adr.person_id(+) = per.person_id
1662 and nvl(adr.primary_flag(+),'Y')='Y'
1663 and l_session_date between nvl(adr.date_from(+),l_session_date) and nvl(adr.date_to(+),l_session_date)
1664 and l_session_date between nvl(per.effective_start_date,l_session_date) and nvl(per.effective_end_date,l_session_date)
1665 and ((per.national_identifier=p_national_identifier)
1666 or ((per.last_name=p_last_name) and
1667 (per.first_name=p_first_name or per.first_name is null or p_first_name is null)
1668 and (per.date_of_birth=p_date_of_birth or per.date_of_birth is null or p_date_of_birth is null)));
1669 --fix for bug 5946126.
1670 /*union
1671 select pty.party_id "PartyId"
1672 ,to_number(null) "PersonId"
1673 ,null "SecurityStatus"
1674 ,hr_general2.get_dup_full_name(pty.person_title
1675 ,pty.person_first_name,pty.person_middle_name ,pty.person_last_name,pty.person_name_suffix,null,null,null) "PersonName"
1676 ,hr_general2.get_dup_external_name "BgName"
1677 ,null "LocationCode"
1678 ,null "OrgName"
1679 ,null "PostalCode"
1680 ,null "NationalIdentifier",
1681 to_number(null) "BusinessGroupId"
1682 from hz_person_profiles pty
1683 ,hz_parties p
1684 where p.party_id = pty.party_id
1685 and p.person_last_name=p_last_name
1686 and (p.person_first_name=p_first_name or p.person_first_name is null or p_first_name is null)
1687 and (pty.date_of_birth=p_date_of_birth or pty.date_of_birth is null or p_date_of_birth is null)
1688 and l_session_date between nvl(pty.effective_start_date,l_session_date) and nvl(pty.effective_end_date,l_session_date)
1689 and not exists (select 'x' from per_all_people_f per
1690 where per.party_id = p.party_id
1691 and l_session_date between nvl(per.effective_start_date,l_session_date) and nvl(per.effective_end_date,l_session_date) );*/
1692
1693 qryCtx := dbms_xmlgen.newContext(rc);
1694 DBMS_XMLGEN.setRowTag(qryCtx, 'MatchingPersonListVORow');
1695 DBMS_XMLGEN.setRowSetTag(qryCtx, 'MatchingPersonListVO');
1696
1697 hr_utility.set_location('SSHR',10.3);
1698
1699 p_dup_clob:= DBMS_XMLGEN.getXML( qryCtx );
1700
1701 hr_utility.set_location('SSHR',10.4);
1702
1703 DBMS_XMLGEN.closeContext( qryCtx );
1704
1705 hr_utility.set_location('SSHR',10.5);
1706
1707 l_duplicate_found := TRUE;
1708
1709 else
1710 l_duplicate_found := FALSE;
1711 end if;
1712 --fix for bug 5923547 ends here.
1713 else
1714 if nvl(fnd_profile.value('HR_DUPLICATE_PERSON_CHECK'),'Y') = 'Y' then --fix for bug 5930576
1715 hr_utility.set_location('SSHR',20);
1716
1717
1718 -- Query to detect duplicate person
1719 -- Use where exists for performance reasons
1720 hr_utility.set_location('SSHR',30);
1721
1722 -- Global name variable for index usage
1723 l_first_char := substr( l_global_name , 1 , 1 ) ;
1724 l_second_char := substr( l_global_name , 2 , 1 ) ;
1725
1726 l_ul_check := upper(l_first_char)||lower(l_second_char)||'%';
1727 l_lu_check := lower(l_first_char)||upper(l_second_char)||'%';
1728 l_uu_check := upper(l_first_char)||upper(l_second_char)||'%';
1729 l_ll_check := lower(l_first_char)||lower(l_second_char)||'%';
1730
1731 hr_utility.set_location('SSHR',40);
1732 -- NOTE : Any changes made to the below cursor MUST also be made to the cursor used for PUI
1733 open rc for
1734 SELECT per.party_id "PartyId"
1735 ,per.person_id "PersonId"
1736 ,hr_general2.get_dup_security_status ( per.party_id
1737 ,per.business_group_id
1738 ,per.person_id) "SecurityStatus" -- modified for Bug14664920
1739 ,per.global_name "PersonName"
1740 ,nvl(bg.name,hr_general2.get_dup_external_name) "BgName"
1741 ,loc.location_code "LocationCode"
1742 ,org.NAME "OrgName"
1743 ,adr.postal_code "PostalCode"
1744 ,SUBSTR(per.national_identifier,-4,4) "NationalIdentifier"
1745 ,per.business_group_id "BusinessGroupId"
1746 FROM per_all_people_f per
1747 ,per_all_assignments_f ass
1748 ,per_business_groups_perf bg
1749 ,hr_locations_all loc
1750 ,hr_all_organization_units org
1751 ,per_addresses adr
1752 WHERE ass.person_id(+) = per.person_id
1753 AND NVL(ass.primary_flag(+),'Y') = 'Y'
1754 AND NVL(ass.assignment_type(+),'E') = 'E'
1755 AND l_session_date BETWEEN NVL(ass.effective_start_date(+),l_session_date) AND NVL(ass.effective_end_date(+),l_session_date)
1756 AND bg.business_group_id = per.business_Group_id
1757 AND loc.location_id(+) = ass.location_id
1758 AND org.organization_id(+) = ass.organization_id
1759 AND adr.person_id(+) = per.person_id
1760 AND NVL(adr.primary_flag(+),'Y') = 'Y'
1761 AND l_session_date BETWEEN NVL(adr.date_from(+),l_session_date) AND NVL(adr.date_to(+),l_session_date)--fix for bug6748256
1762 AND l_session_date BETWEEN NVL(per.effective_start_date,l_session_date) AND NVL(per.effective_end_date,l_session_date)
1763 AND (
1764 per.national_identifier = p_national_identifier
1765 OR
1766 (
1767 --global_name = l_global_name
1768 upper(global_name) = upper(l_global_name) --Added for Bug 7609125
1769 -- added conditions to use index
1770 AND
1771 (
1772 global_name like l_ul_check OR
1773 global_name like l_lu_check OR
1774 global_name like l_uu_check OR
1775 global_name like l_ll_check
1776 )
1777 AND
1778 (
1779 per.date_of_birth = p_date_of_birth
1780 OR per.date_of_birth IS NULL OR p_date_of_birth IS NULL
1781 )
1782 )
1783 )
1784 UNION
1785 SELECT pty.party_id "PartyId"
1786 ,to_number(NULL) "PersonId"
1787 ,NULL "SecurityStatus"
1788 ,hr_general2.get_dup_full_name
1789 (pty.person_title
1790 ,pty.person_first_name
1791 ,pty.person_middle_name
1792 ,pty.person_last_name
1793 ,pty.person_name_suffix
1794 ) "PersonName"
1795 ,hr_general2.get_dup_external_name "BgName"
1796 ,NULL "LocationCode"
1797 ,NULL "OrgName"
1798 ,NULL "PostalCode"
1799 ,NULL "NationalIdentifier"
1800 ,NULL "BusinessGroupId"
1801 FROM hz_person_profiles pty,
1802 hz_parties par
1803 WHERE pty.party_id = par.party_id
1804 AND par.orig_system_reference NOT LIKE 'PER%'
1805 AND par.party_type = 'PERSON'
1806 AND pty.effective_end_date is NULL
1807 AND (
1808 (
1809 (
1810 UPPER(par.person_last_name) = UPPER(p_last_name)
1811 --OR par.person_last_name is null
1812 )
1813 AND
1814 (
1815 UPPER(par.person_first_name) = UPPER(p_first_name)
1816 OR par.person_first_name IS NULL OR p_first_name IS NULL
1817 )
1818 AND
1819 (
1820 pty.date_of_birth = p_date_of_birth
1821 OR pty.date_of_birth IS NULL OR p_date_of_birth IS NULL
1822 )
1823 )
1824 );
1825
1826 qryCtx := dbms_xmlgen.newContext(rc);
1827 DBMS_XMLGEN.setRowTag(qryCtx, 'MatchingPersonListVORow');
1828 DBMS_XMLGEN.setRowSetTag(qryCtx, 'MatchingPersonListVO');
1829
1830 hr_utility.set_location('SSHR',50);
1831
1832 p_dup_clob:= DBMS_XMLGEN.getXML( qryCtx );
1833
1834 hr_utility.set_location('SSHR',60);
1835
1836 DBMS_XMLGEN.closeContext( qryCtx );
1837
1838 hr_utility.set_location('SSHR',70);
1839
1840 l_duplicate_found := TRUE;
1841 else
1842 l_duplicate_found := FALSE;
1843 end if;
1844 end if;
1845
1846 p_duplicate_exists := hr_java_conv_util_ss.get_number(l_duplicate_found);
1847
1848 end is_duplicate_person;
1849
1850
1851
1852 -- --------------------------------------------------------------------------
1853 -- |----------------------< SERVER_SIDE_PROFILE_PUT >----------------------|
1854 -- --------------------------------------------------------------------------
1855
1856
1857 procedure SERVER_SIDE_PROFILE_PUT(
1858 NAME in varchar2,
1859 VAL in varchar2)
1860 is
1861
1862 begin
1863
1864 hr_utility.set_location('Before: FND PROFILE CALL', 13163);
1865
1866 fnd_profile.put(NAME,VAL);
1867
1868 hr_utility.set_location('After: FND PROFILE CALL', 13163);
1869
1870 end SERVER_SIDE_PROFILE_PUT;
1871
1872
1873 --Added for the bug#12774028
1874
1875 function reporting_user
1876 return boolean is
1877
1878 CURSOR get_reporting_id
1879 IS
1880 SELECT security_profile_id
1881 FROM per_security_profiles
1882 WHERE reporting_oracle_username = USER;
1883 l_security_profile_id number := -1;
1884 begin
1885
1886 OPEN get_reporting_id;
1887 FETCH get_reporting_id INTO l_security_profile_id;
1888 IF get_reporting_id%FOUND THEN
1889 CLOSE get_reporting_id;
1890 return TRUE;
1891 else
1892 CLOSE get_reporting_id;
1893 return FALSE;
1894 end if;
1895
1896 end reporting_user;
1897
1898 END Hr_General2;
[Home] [Help]