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