[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_NAME
Source
1 package body hr_person_name as
2 /* $Header: pepernam.pkb 120.6 2012/02/01 09:30:31 ktithy ship $ */
3 --
4 g_package CONSTANT varchar2(30) := 'hr_person_name.';
5 --
6 g_format_name_cached hr_name_formats.format_name%TYPE := null;
7 g_format_mask_cached hr_name_formats.format_mask%TYPE := null;
8 g_user_format_choice_cached hr_name_formats.user_format_choice%TYPE := null;
9 g_legislation_code_cached hr_name_formats.legislation_code%TYPE := null;
10 g_business_group_id_cached per_all_people_f.business_group_id%TYPE := null;
11 --
12 --
13 -- ----------------------------------------------------------------------------
14 -- |------------------------< get_token_position >----------------------------|
15 -- ----------------------------------------------------------------------------
16 --
17 procedure get_token_position(p_format_mask in varchar2
18 ,p_token in varchar2
19 ,p_start_pos out nocopy number
20 ,p_end_pos out nocopy number) is
21 --
22 l_token_start_pos number;
23 l_token_end_pos number;
24 --
25 begin
26 if p_format_mask is null or p_token is null then
27 l_token_start_pos := 0;
28 l_token_end_pos := 0;
29 else
30 l_token_start_pos := instr(p_format_mask,'$'||p_token||'$');
31 l_token_end_pos := l_token_start_pos + length(p_token) + 1;
32 end if;
33 --
34 p_start_pos := l_token_start_pos;
35 p_end_pos := l_token_end_pos;
36 --
37 end get_token_position;
38 --
39 -- ----------------------------------------------------------------------------
40 -- |-----------------------< get_token_value >--------------------------------|
41 -- ----------------------------------------------------------------------------
42 --
43 procedure get_token_value(p_token in varchar2
44 ,p_person_columns in hr_person_name.t_nameColumns_Rec
45 ,p_value out nocopy varchar2) is
46 --
47 l_value varchar2(240);
48 --
49 begin
50 l_value := null;
51 if p_token = 'FIR' then l_value := p_person_columns.FIRST_NAME;
52 elsif p_token = 'MID' then l_value := p_person_columns.MIDDLE_NAMES;
53 elsif p_token = 'LAS' then l_value := p_person_columns.LAST_NAME;
54 elsif p_token = 'SUF' then l_value := p_person_columns.SUFFIX;
55 elsif p_token = 'PNADJ' then l_value := p_person_columns.PRE_NAME_ADJUNCT;
56 elsif p_token = 'TITLE' then
57 begin
58 SELECT meaning into l_value
59 FROM hr_lookups
60 WHERE lookup_type = 'TITLE'
61 AND lookup_code = p_person_columns.TITLE;
62 exception
63 when others then
64 l_value := null;
65 end;
66 elsif p_token = 'KNOWN' then l_value := p_person_columns.KNOWN_AS ;
67 elsif p_token = 'USEFIR' then l_value := nvl(p_person_columns.KNOWN_AS,p_person_columns.FIRST_NAME);
68 elsif p_token = 'EMAIL' then l_value := p_person_columns.EMAIL_ADDRESS;
69 elsif p_token = 'ENUM' then l_value := p_person_columns.EMPLOYEE_NUMBER;
70 elsif p_token = 'ANUM' then l_value := p_person_columns.APPLICANT_NUMBER;
71 elsif p_token = 'CWNUM' then l_value := p_person_columns.NPW_NUMBER;
72 elsif p_token = 'PRVLAS' then l_value := p_person_columns.PREVIOUS_LAST_NAME;
73 elsif p_token = 'MIDINIT' then l_value := Substr(p_person_columns.MIDDLE_NAMES,1,1);
74 elsif p_token = 'FIRINIT' then l_value := Substr(p_person_columns.FIRST_NAME,1,1);
75 elsif p_token = 'LASINIT' then l_value := Substr(p_person_columns.LAST_NAME,1,1);
76 elsif p_token = 'I01' then l_value := p_person_columns.PER_INFORMATION1;
77 elsif p_token = 'I02' then l_value := p_person_columns.PER_INFORMATION2;
78 elsif p_token = 'I03' then l_value := p_person_columns.PER_INFORMATION3;
79 elsif p_token = 'I04' then l_value := p_person_columns.PER_INFORMATION4;
80 elsif p_token = 'I05' then l_value := p_person_columns.PER_INFORMATION5;
81 elsif p_token = 'I06' then l_value := p_person_columns.PER_INFORMATION6;
82 elsif p_token = 'I07' then l_value := p_person_columns.PER_INFORMATION7;
83 elsif p_token = 'I08' then l_value := p_person_columns.PER_INFORMATION8;
84 elsif p_token = 'I09' then l_value := p_person_columns.PER_INFORMATION9;
85 elsif p_token = 'I10' then l_value := p_person_columns.PER_INFORMATION10;
86 elsif p_token = 'I11' then l_value := p_person_columns.PER_INFORMATION11;
87 elsif p_token = 'I12' then l_value := p_person_columns.PER_INFORMATION12;
88 elsif p_token = 'I13' then l_value := p_person_columns.PER_INFORMATION13;
89 elsif p_token = 'I14' then l_value := p_person_columns.PER_INFORMATION14;
90 elsif p_token = 'I15' then l_value := p_person_columns.PER_INFORMATION15;
91 elsif p_token = 'I16' then l_value := p_person_columns.PER_INFORMATION16;
92 elsif p_token = 'I17' then l_value := p_person_columns.PER_INFORMATION17;
93 elsif p_token = 'I18' then l_value := p_person_columns.PER_INFORMATION18;
94 elsif p_token = 'I19' then l_value := p_person_columns.PER_INFORMATION19;
95 elsif p_token = 'I20' then l_value := p_person_columns.PER_INFORMATION20;
96 elsif p_token = 'I21' then l_value := p_person_columns.PER_INFORMATION21;
97 elsif p_token = 'I22' then l_value := p_person_columns.PER_INFORMATION22;
98 elsif p_token = 'I23' then l_value := p_person_columns.PER_INFORMATION23;
99 elsif p_token = 'I24' then l_value := p_person_columns.PER_INFORMATION24;
100 elsif p_token = 'I25' then l_value := p_person_columns.PER_INFORMATION25;
101 elsif p_token = 'I26' then l_value := p_person_columns.PER_INFORMATION26;
102 elsif p_token = 'I27' then l_value := p_person_columns.PER_INFORMATION27;
103 elsif p_token = 'I28' then l_value := p_person_columns.PER_INFORMATION28;
104 elsif p_token = 'I29' then l_value := p_person_columns.PER_INFORMATION29;
105 elsif p_token = 'I30' then l_value := p_person_columns.PER_INFORMATION30;
106 elsif p_token = 'A01' then l_value := p_person_columns.ATTRIBUTE1;
107 elsif p_token = 'A02' then l_value := p_person_columns.ATTRIBUTE2;
108 elsif p_token = 'A03' then l_value := p_person_columns.ATTRIBUTE3;
109 elsif p_token = 'A04' then l_value := p_person_columns.ATTRIBUTE4;
110 elsif p_token = 'A05' then l_value := p_person_columns.ATTRIBUTE5;
111 elsif p_token = 'A06' then l_value := p_person_columns.ATTRIBUTE6;
112 elsif p_token = 'A07' then l_value := p_person_columns.ATTRIBUTE7;
113 elsif p_token = 'A08' then l_value := p_person_columns.ATTRIBUTE8;
114 elsif p_token = 'A09' then l_value := p_person_columns.ATTRIBUTE9;
115 elsif p_token = 'A10' then l_value := p_person_columns.ATTRIBUTE10;
116 elsif p_token = 'A11' then l_value := p_person_columns.ATTRIBUTE11;
117 elsif p_token = 'A12' then l_value := p_person_columns.ATTRIBUTE12;
118 elsif p_token = 'A13' then l_value := p_person_columns.ATTRIBUTE13;
119 elsif p_token = 'A14' then l_value := p_person_columns.ATTRIBUTE14;
120 elsif p_token = 'A15' then l_value := p_person_columns.ATTRIBUTE15;
121 elsif p_token = 'A16' then l_value := p_person_columns.ATTRIBUTE16;
122 elsif p_token = 'A17' then l_value := p_person_columns.ATTRIBUTE17;
123 elsif p_token = 'A18' then l_value := p_person_columns.ATTRIBUTE18;
124 elsif p_token = 'A19' then l_value := p_person_columns.ATTRIBUTE19;
125 elsif p_token = 'A20' then l_value := p_person_columns.ATTRIBUTE20;
126 elsif p_token = 'A21' then l_value := p_person_columns.ATTRIBUTE21;
127 elsif p_token = 'A22' then l_value := p_person_columns.ATTRIBUTE22;
128 elsif p_token = 'A23' then l_value := p_person_columns.ATTRIBUTE23;
129 elsif p_token = 'A24' then l_value := p_person_columns.ATTRIBUTE24;
130 elsif p_token = 'A25' then l_value := p_person_columns.ATTRIBUTE25;
131 elsif p_token = 'A26' then l_value := p_person_columns.ATTRIBUTE26;
132 elsif p_token = 'A27' then l_value := p_person_columns.ATTRIBUTE27;
133 elsif p_token = 'A28' then l_value := p_person_columns.ATTRIBUTE28;
134 elsif p_token = 'A29' then l_value := p_person_columns.ATTRIBUTE29;
135 elsif p_token = 'A30' then l_value := p_person_columns.ATTRIBUTE30;
136 elsif p_token = 'P01' then l_value := '/'; -- Added for BUG 5530099
137 end if;
138 --
139 p_value := l_value;
140 --
141 end get_token_value;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |---------------------< get_formatted_name >-------------------------------|
145 -- ----------------------------------------------------------------------------
146 --
147 procedure get_formatted_name(p_name_values in hr_person_name.t_nameColumns_Rec
148 ,p_formatted_name in out nocopy varchar2) is
149 --
150 l_formatted_name varchar2(2000);
151 l_token_start_pos number;
152 l_token_end_pos number;
153 l_token_value varchar2(240);
154 l_delimeter_start_pos number;
155 l_delimeter_end_pos number;
156 l_expr varchar2(240);
157 l_token_found boolean;
158 --
159 cursor csr_valid_tokens is
160 select lookup_code token_name
161 from hr_standard_lookups
162 where lookup_type = 'PER_FORMAT_MASK_TOKENS';
163 --
164 begin
165 l_token_start_pos := 0;
166 l_token_end_pos := 0;
167 l_token_value := null;
168 l_delimeter_start_pos := 0;
169 l_delimeter_end_pos := 0;
170 l_expr := null;
171 l_token_found := FALSE;
172 --
173 l_formatted_name := p_formatted_name;
174 if l_formatted_name is not null then
175 for l_token in csr_valid_tokens loop
176 --
177 -- check whether token is referenced by format mask
178 -- get the start/end positions in string
179 --
180 get_token_position(p_format_mask => l_formatted_name
181 ,p_token => l_token.token_name
182 ,p_start_pos => l_token_start_pos
183 ,p_end_pos => l_token_end_pos);
184 if l_token_start_pos > 0 then
185 -- found token referenced in format mask
186 get_token_value(p_token => l_token.token_name
187 ,p_person_columns => p_name_values
188 ,p_value => l_token_value);
189 --
190 -- if db column is null, then ignore token and its punctuation
191 --
192 if l_token_value is not null then
193 l_token_found := TRUE;
194 loop -- replace all ocurrences
195 l_formatted_name := REPLACE(l_formatted_name,'$'||l_token.token_name||'$',l_token_value);
196 get_token_position(p_format_mask => l_formatted_name
197 ,p_token => l_token.token_name
198 ,p_start_pos => l_token_start_pos
199 ,p_end_pos => l_token_end_pos);
200 if l_token_start_pos <= 0 then
201 l_token_found := FALSE;
202 end if;
203 exit when NOT l_token_found;
204 end loop;
205
206 else
207 --
208 -- extract the following expression: '|' [punctuation] $token$ [punctuation] '|'
209 -- this expression will be replaced by a single '|'
210 --
211 l_token_found := TRUE;
212 loop
213 l_delimeter_start_pos := instr(substr(l_formatted_name,1,l_token_start_pos-1),'|',-1);
214 l_delimeter_end_pos := instr(substr(l_formatted_name,l_delimeter_start_pos+1),'|');
215 l_expr := substr(l_formatted_name, l_delimeter_start_pos,l_delimeter_end_pos);
216 l_formatted_name := REPLACE(l_formatted_name,l_expr,'|');
217 get_token_position(p_format_mask => l_formatted_name
218 ,p_token => l_token.token_name
219 ,p_start_pos => l_token_start_pos
220 ,p_end_pos => l_token_end_pos);
221 if l_token_start_pos <= 0 then
222 l_token_found := FALSE;
223 end if;
224 exit when NOT l_token_found;
225 end loop;
226 end if;
227 end if;
228 end loop;
229 l_formatted_name := REPLACE(l_formatted_name, '|', null);
230 end if;
231 --
232 p_formatted_name := l_formatted_name;
233 --
234 end get_formatted_name;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |---------------------< get_person_name_internal >-------------------------|
238 -- ----------------------------------------------------------------------------
239 --
240 function get_person_name_internal
241 (p_rowid in rowid
242 ,p_format_mask in varchar2)
243 return varchar2 is
244 --
245 l_proc CONSTANT varchar2(80) := g_package||'get_person_name_internal';
246 --
247 l_person_names_rec hr_person_name.t_nameColumns_Rec;
248 l_formatted_name varchar2(2000);
249 --
250 cursor csr_get_name_columns is
251 select rowid row_id
252 ,FIRST_NAME
253 ,MIDDLE_NAMES
254 ,LAST_NAME
255 ,SUFFIX
256 ,PRE_NAME_ADJUNCT
257 ,TITLE
258 ,KNOWN_AS
259 ,EMAIL_ADDRESS
260 ,EMPLOYEE_NUMBER
261 ,APPLICANT_NUMBER
262 ,NPW_NUMBER
263 ,PREVIOUS_LAST_NAME
264 ,PER_INFORMATION1
265 ,PER_INFORMATION2
266 ,PER_INFORMATION3
267 ,PER_INFORMATION4
268 ,PER_INFORMATION5
269 ,PER_INFORMATION6
270 ,PER_INFORMATION7
271 ,PER_INFORMATION8
272 ,PER_INFORMATION9
273 ,PER_INFORMATION10
274 ,PER_INFORMATION11
275 ,PER_INFORMATION12
276 ,PER_INFORMATION13
277 ,PER_INFORMATION14
278 ,PER_INFORMATION15
279 ,PER_INFORMATION16
280 ,PER_INFORMATION17
281 ,PER_INFORMATION18
282 ,PER_INFORMATION19
283 ,PER_INFORMATION20
284 ,PER_INFORMATION21
285 ,PER_INFORMATION22
286 ,PER_INFORMATION23
290 ,PER_INFORMATION27
287 ,PER_INFORMATION24
288 ,PER_INFORMATION25
289 ,PER_INFORMATION26
291 ,PER_INFORMATION28
292 ,PER_INFORMATION29
293 ,PER_INFORMATION30
294 ,ATTRIBUTE1
295 ,ATTRIBUTE2
296 ,ATTRIBUTE3
297 ,ATTRIBUTE4
298 ,ATTRIBUTE5
299 ,ATTRIBUTE6
300 ,ATTRIBUTE7
301 ,ATTRIBUTE8
302 ,ATTRIBUTE9
303 ,ATTRIBUTE10
304 ,ATTRIBUTE11
305 ,ATTRIBUTE12
306 ,ATTRIBUTE13
307 ,ATTRIBUTE14
308 ,ATTRIBUTE15
309 ,ATTRIBUTE16
310 ,ATTRIBUTE17
311 ,ATTRIBUTE18
312 ,ATTRIBUTE19
313 ,ATTRIBUTE20
314 ,ATTRIBUTE21
315 ,ATTRIBUTE22
316 ,ATTRIBUTE23
317 ,ATTRIBUTE24
318 ,ATTRIBUTE25
319 ,ATTRIBUTE26
320 ,ATTRIBUTE27
321 ,ATTRIBUTE28
322 ,ATTRIBUTE29
323 ,ATTRIBUTE30
324 ,FULL_NAME
325 ,ORDER_NAME
326 ,LOCAL_NAME
327 ,GLOBAL_NAME
328 ,BUSINESS_GROUP_ID
329 from per_all_people_f
330 where rowid = p_rowid;
331 --
332 begin
333 --
334 if p_format_mask is null then
335 l_formatted_name := null;
336 else
337 l_formatted_name := p_format_mask;
338 --
339 -- retrieve all name columns for the person record
340 --
341 open csr_get_name_columns;
342 fetch csr_get_name_columns into l_person_names_rec;
343 if csr_get_name_columns%FOUND then
344 --
345 close csr_get_name_columns;
346 --
347 -- replace all tokens in format mask using name column values
348 --
349 get_formatted_name(p_name_values => l_person_names_rec
350 ,p_formatted_name => l_formatted_name);
351 --
352 l_formatted_name := substr(l_formatted_name,1,240);
353 else
354 close csr_get_name_columns;
355 --
356 end if; -- person record found
357 --
358 end if; -- format mask is null
359 RETURN l_formatted_name;
360 end get_person_name_internal;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |--------------------------< get_person_name >-----------------------------|
364 -- ----------------------------------------------------------------------------
365 --
366 function get_person_name(p_person_id in number
367 ,p_effective_date in date
368 ,p_format_name in varchar2
369 ,p_user_format_choice in varchar2
370 )
371 return varchar2 is
372 --
373 l_proc CONSTANT varchar2(80) := g_package||'get_person_name';
374 --
375 l_retrieve_flag varchar2(10);
376 l_legislation_code varchar2(30);
377 l_user_format_choice hr_name_formats.user_format_choice%TYPE;
378 l_format_mask hr_name_formats.format_mask%TYPE;
379 l_person_name varchar2(240);
380 --
381 cursor csr_get_format_mask(cp_format_name varchar2
382 ,cp_user_format_choice varchar2
383 ,cp_legislation_code varchar2) is
384 select format_mask
385 from hr_name_formats
386 where format_name = cp_format_name
387 and user_format_choice = cp_user_format_choice
388 and ((cp_legislation_code is not null
389 and legislation_code = cp_legislation_code)
390 or (cp_legislation_code is null and legislation_code is null));
391 --
392 cursor csr_get_person_details is
393 select rowid, business_group_id, full_name, order_name
394 ,local_name, global_name
395 from per_all_people_f
396 where person_id = p_person_id
397 and p_effective_date between effective_start_date and effective_end_date;
398
399 l_person_rec csr_get_person_details%ROWTYPE;
400 --
401 --
402 begin
403 l_retrieve_flag := 'N';
404 open csr_get_person_details;
405 fetch csr_get_person_details into l_person_rec;
406 if csr_get_person_details%NOTFOUND then
407 close csr_get_person_details;
408 fnd_message.set_name('PER','HR_51834_QUA_PER_ID_INV');
409 fnd_message.raise_error;
410 else
411 close csr_get_person_details;
412 if p_user_format_choice is null or p_user_format_choice not in ('G','L') then
413 l_user_format_choice := nvl(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'L');
414 else
415 l_user_format_choice := p_user_format_choice;
416 end if;
417 --
418 if l_user_format_choice <> nvl(g_user_format_choice_cached,hr_api.g_varchar2) then
419 l_retrieve_flag := 'Y';
420 g_user_format_choice_cached := l_user_format_choice;
421 end if;
422 --
423 if p_format_name = g_FULL_NAME then
424 RETURN l_person_rec.full_name;
425 elsif p_format_name = g_ORDER_NAME then
426 RETURN l_person_rec.order_name;
427 elsif p_format_name = g_LIST_NAME then
428 if g_user_format_choice_cached = 'L' then
429 RETURN l_person_rec.local_name;
430 else
431 RETURN l_person_rec.global_name;
432 end if;
433 else
434 if l_person_rec.business_group_id <> nvl(g_business_group_id_cached,hr_api.g_number) then
438 l_retrieve_flag := 'Y';
435 g_business_group_id_cached := l_person_rec.business_group_id;
436 l_legislation_code := hr_api.return_legislation_code(l_person_rec.business_group_id);
437 if l_legislation_code <> nvl(g_legislation_code_cached,hr_api.g_varchar2) then
439 g_legislation_code_cached := l_legislation_code;
440 end if;
441 end if;
442 end if;
443 --
444 if p_format_name <> nvl(g_format_name_cached,hr_api.g_varchar2) then
445 l_retrieve_flag := 'Y';
446 g_format_name_cached := p_format_name;
447 end if;
448 --
449 if l_retrieve_flag = 'Y' then
450 open csr_get_format_mask(g_format_name_cached, g_user_format_choice_cached
451 , g_legislation_code_cached);
452 fetch csr_get_format_mask into l_format_mask;
453 if csr_get_format_mask%NOTFOUND then
454 close csr_get_format_mask;
455 open csr_get_format_mask(p_format_name, l_user_format_choice, null);
456 fetch csr_get_format_mask into l_format_mask;
457 if csr_get_format_mask%NOTFOUND then
458 close csr_get_format_mask;
459 RETURN null;
460 end if;
461 end if;
462 g_format_mask_cached := l_format_mask;
463 end if;
464 -- name is already truncated to 240 characters
465 l_person_name := get_person_name_internal(l_person_rec.rowid, g_format_mask_cached);
466 RETURN l_person_name;
467 --
468 end if; --person found
469 end get_person_name;
470 --
471 --
472 -- ----------------------------------------------------------------------------
473 -- ---------------------------< is_valid_format >------------------------------
474 -- ----------------------------------------------------------------------------
475 -- Returns 'TRUE' if format exists per legislation or a seeded one exists
476 --
477 function is_valid_format(p_format_name in varchar2
478 ,p_legislation in varchar2)
479 return varchar2 is
480 --
481 l_valid varchar2(10);
482 --
483 cursor csr_validate_name is
484 select 'TRUE'
485 from hr_name_formats
486 where format_name = p_format_name
487 and ((p_legislation is not null and legislation_code = p_legislation)
488 or legislation_code is null);
489 --
490 begin
491 l_valid := 'FALSE';
492 if p_format_name is not null then
493 open csr_validate_name;
494 fetch csr_validate_name into l_valid;
495 if csr_validate_name%NOTFOUND then
496 l_valid := 'FALSE';
497 end if;
498 end if;
499 close csr_validate_name;
500 return l_valid;
501 --
502 end is_valid_format;
503 --
504 --
505 -- This routine is kept for backwards compatibility
506 -- ----------------------------------------------------------------------------
507 -- |----------------------< OLD_get_person_name >-----------------------------|
508 -- ----------------------------------------------------------------------------
509 --
510 function get_person_name(
511 p_person_id in number,
512 p_effective_date in date default null,
513 p_format in varchar2 default null) return varchar2 is
514 --
515 l_formatted_name varchar2(240);
516 l_format_name hr_name_formats.format_name%TYPE;
517 l_legislation varchar2(30);
518 --
519 cursor csr_get_leg is
520 select bg.legislation_code
521 from per_all_people_f peo
522 ,per_business_groups_perf bg
523 where peo.person_id = p_person_id
524 and p_effective_date between peo.effective_start_date and peo.effective_end_date
525 and peo.business_group_id = bg.business_group_id;
526 --
527 begin
528 l_format_name := p_format;
529 if l_format_name is null then
530 l_format_name := g_LIST_NAME;
531 else
532 open csr_get_leg;
533 fetch csr_get_leg into l_legislation;
534 close csr_get_leg;
535 if is_valid_format(l_format_name,l_legislation) = 'FALSE' then
536 l_format_name := g_LIST_NAME;
537 end if;
538 end if;
539 l_formatted_name := get_person_name
540 (p_person_id => p_person_id
541 ,p_effective_date => p_effective_date
542 ,p_format_name => l_format_name
543 ,p_user_format_choice => 'G');
544 --
545 return l_formatted_name;
546 --
547 end get_person_name;
548 -- ----------------------------------------------------------------------------
549 -- |-------------------< OBSOLETE_get_person_name >---------------------------|
550 -- ----------------------------------------------------------------------------
551 --
552 --function get_person_name(p_person_id in number,
553 -- p_effective_date in date,
554 -- p_format in varchar2) return varchar2 is
555 --
556 -- l_proc varchar2(80) := g_package||'get_person_name';
557 --cursor to select the person information.
558 --
559 -- cursor c1 is
560 -- select
561 -- first_name
562 -- , middle_names
563 -- , last_name
564 -- , pre_name_adjunct
565 -- , suffix
566 -- , hr_general.decode_lookup('TITLE',title) title
567 -- , full_name
568 -- , known_as
569 -- from per_all_people_f
570 -- where person_id = p_person_id
571 -- and trunc(nvl(p_effective_date,sysdate)) between effective_start_date
572 -- and effective_end_date;
573 --
574 -- person_rec c1%rowtype;
575 -- l_format varchar2(400);
576 --
577 --begin
578 --
582 -- fetch c1 into person_rec;
579 -- hr_utility.set_location('Entering '||l_proc,10);
580 --
581 --open c1;
583 --
584 -- if c1%notfound then --if person not found
585 --
586 -- close c1;
587 -- fnd_message.set_name('PER','HR_51834_QUA_PER_ID_INV');
588 -- fnd_message.raise_error;
589 --
590 -- else -- person found..
591 --
592 --
593 -- checking whether required format is specified
594 -- if p_format is not null then --format specified
595 --
596 -- l_format := p_format;
597 --
598 -- hr_utility.set_location('Replacing tokens '||l_proc,20);
599 -- Replacing the tokens with person name components.
600 --
601 -- l_format := replace(l_format,'$FI',person_rec.first_name);
602 -- l_format := replace(l_format,'$MI',person_rec.middle_names);
603 -- l_format := replace(l_format,'$LA',person_rec.last_name);
604 -- l_format := replace(l_format,'$PR',person_rec.pre_name_adjunct);
605 -- l_format := replace(l_format,'$SU',person_rec.suffix);
606 -- l_format := replace(l_format,'$TI',person_rec.title);
607 -- l_format := replace(l_format,'$FU',person_rec.full_name);
608 -- l_format := replace(l_format,'$KN',person_rec.known_as);
609 -- l_format := replace(l_format,'$IF',substr(ltrim(person_rec.first_name),1,1));
610 -- l_format := replace(l_format,'$IM',substr(ltrim(person_rec.middle_names),1,1));
611 --
612 -- elsif fnd_profile.value('HR_INFORMAL_NAME_FORMAT') is not null then
613 --
614 -- hr_utility.set_location('Replacing tokens '||l_proc,20);
615
616 -- l_format := fnd_profile.value('HR_INFORMAL_NAME_FORMAT');
617 -- hr_utility.trace('profile option value : ' || l_format);
618 --
619 -- Replacing the tokens with person name components.
620 --
621 -- l_format := replace(l_format,'$FI',person_rec.first_name);
622 -- l_format := replace(l_format,'$MI',person_rec.middle_names);
623 -- l_format := replace(l_format,'$LA',person_rec.last_name);
624 -- l_format := replace(l_format,'$PR',person_rec.pre_name_adjunct);
625 -- l_format := replace(l_format,'$SU',person_rec.suffix);
626 -- l_format := replace(l_format,'$TI',person_rec.title);
627 -- l_format := replace(l_format,'$FU',person_rec.full_name);
628 -- l_format := replace(l_format,'$KN',person_rec.known_as);
629 -- l_format := replace(l_format,'$IF',substr(ltrim(person_rec.first_name),1,1));
630 -- l_format := replace(l_format,'$IM',substr(ltrim(person_rec.middle_names),1,1));
631
632 -- else -- format not specified.
633 --
634 --
635 -- hr_utility.set_location('No format specified Full_Name '||l_proc,40);
636 -- in this case returning full_name.
637 --
638 -- l_format := person_rec.full_name;
639 --
640 --
641 -- end if;
642 --
643 --
644 -- hr_utility.set_location('Leaving '||l_proc,50);
645 --
646 -- return l_format;
647 --
648 --
649 -- end if;
650 --
651 -- close c1;
652 -- hr_utility.set_location('Leaving '||l_proc,60);
653 --
654 --end get_person_name; --function
655 --
656 -- ----------------------------------------------------------------------------
657 -- |-------------------< get_seeded_procedure_name >--------------------------|
658 -- ----------------------------------------------------------------------------
659 --
660 procedure get_seeded_procedure_name
661 (p_format_name IN varchar2
662 ,p_legislation_code IN varchar2
663 ,p_package_name OUT nocopy varchar2
664 ,p_procedure_name OUT nocopy varchar2 ) is
665 --
666 l_package_name VARCHAR2(50);
667 l_dummy VARCHAR2(1);
668 l_procedure_name VARCHAR2(50);
669 --
670 cursor csr_leg_pkg(cp_pkg VARCHAR2) IS
671 select '1'
672 from user_objects
673 where object_name = cp_pkg
674 and object_type = 'PACKAGE';
675 --
676 CURSOR lgsl_pkb(cp_pkg VARCHAR2) IS
677 SELECT object_name
678 FROM user_objects
679 WHERE object_type='PACKAGE BODY'
680 AND object_name = cp_pkg
681 AND length(object_name)=13
682 ORDER BY object_name;
683 --
684 begin
685 l_package_name := null;
686 l_procedure_name := null;
687 --
688 -- if hr_general.g_data_migrator_mode <> 'Y' then -- Fix For Bug # 13029361
689 if ( hr_utility.chk_product_install('Oracle Human Resources',p_legislation_code)
690 or (p_legislation_code = 'JP')) then
691 --
692 l_package_name := 'HR_'||p_legislation_code||'_UTILITY';
693 if p_format_name = g_FULL_NAME then
694 l_procedure_name := 'per_'||lower(p_legislation_code)||'_full_name';
695 elsif p_format_name = g_ORDER_NAME then
696 l_procedure_name := 'per_'||lower(p_legislation_code)||'_order_name';
697 else
698 l_procedure_name := null;
699 end if;
700 --
701 -- check package exists
702 --
703 open csr_leg_pkg(l_package_name);
704 fetch csr_leg_pkg into l_dummy;
705 if csr_leg_pkg%NOTFOUND then
706 l_package_name := null;
707 l_procedure_name := null;
708 end if;
709 close csr_leg_pkg;
710 open lgsl_pkb(l_package_name);
711 FETCH lgsl_pkb INTO l_package_name;
712 IF lgsl_pkb%NOTFOUND THEN
713 l_package_name := null;
714 l_procedure_name := null;
715 END IF;
716 CLOSE lgsl_pkb;
717
718 else
719 l_package_name := null;
720 l_procedure_name := null;
721 end if;
722 --
723 -- end if; -- Fix For Bug # 13029361
724 p_package_name := l_package_name;
725 p_procedure_name := l_procedure_name;
726 --
730 -- |------------------< derive_name_using_seeded_proc >-----------------------|
727 end get_seeded_procedure_name;
728 --
729 -- ----------------------------------------------------------------------------
731 -- ----------------------------------------------------------------------------
732 --
733 procedure derive_name_using_seeded_proc
734 (p_format_name IN varchar2
735 ,p_legislation_code IN varchar2
736 ,p_name_column_rec IN hr_person_name.t_nameColumns_Rec
737 ,p_package_name IN varchar2 default NULL
738 ,p_procedure_name IN varchar2 default NULL
739 ,p_formatted_name OUT nocopy varchar2
740 ) is
741 --
742 e_InvalidProc exception;
743 pragma exception_init(e_InvalidProc, -904);
744
745 l_formatted_name varchar2(2000);
746 l_package_name VARCHAR2(50);
747 l_procedure_name VARCHAR2(50);
748 l_proc_call VARCHAR2(4790);
749 --
750 begin
751 l_formatted_name := null;
752 l_package_name := p_package_name;
753 l_procedure_name := p_procedure_name;
754 --
755 if p_package_name is null then
756 get_seeded_procedure_name
757 (p_format_name => p_format_name
758 ,p_legislation_code => p_legislation_code
759 ,p_package_name => l_package_name
760 ,p_procedure_name => l_procedure_name);
761 end if;
762 --
763 if l_package_name is not null then
764 --
765 -- construct an anonymous block with bind variable
766 --
767 l_proc_call := 'SELECT rtrim(substrb( '|| l_package_name ||'.'||l_procedure_name||'(:p_first_name,:p_middle_names,:p_last_name,:p_known_as,:p_title,';
768
769 l_proc_call := l_proc_call||':p_suffix,:p_pre_name_adjunct,:p_per_information1,:p_per_information2,:p_per_information3,:p_per_information4,:p_per_information5,';
770
771 l_proc_call := l_proc_call||':p_per_information6,:p_per_information7,:p_per_information8,:p_per_information9,:p_per_information10,';
772
773 l_proc_call := l_proc_call||':p_per_information11,:p_per_information12,:p_per_information13,:p_per_information14,:p_per_information15,:p_per_information16,:p_per_information17,';
774
775 l_proc_call := l_proc_call||':p_per_information18,:p_per_information19,:p_per_information20,:p_per_information21,:p_per_information22,:p_per_information23,:p_per_information24,';
776
777 l_proc_call := l_proc_call||':p_per_information25,:p_per_information26,:p_per_information27,:p_per_information28,:p_per_information29,:p_per_information30),1,240)) FROM sys.dual ';
778
779 EXECUTE IMMEDIATE l_proc_call
780 INTO l_formatted_name
781 USING p_name_column_rec.first_name
782 ,p_name_column_rec.middle_names
783 ,p_name_column_rec.last_name
784 ,p_name_column_rec.known_as
785 ,p_name_column_rec.title
786 ,p_name_column_rec.suffix
787 ,p_name_column_rec.pre_name_adjunct
788 ,p_name_column_rec.per_information1
789 ,p_name_column_rec.per_information2
790 ,p_name_column_rec.per_information3
791 ,p_name_column_rec.per_information4
792 ,p_name_column_rec.per_information5
793 ,p_name_column_rec.per_information6
794 ,p_name_column_rec.per_information7
795 ,p_name_column_rec.per_information8
796 ,p_name_column_rec.per_information9
797 ,p_name_column_rec.per_information10
798 ,p_name_column_rec.per_information11
799 ,p_name_column_rec.per_information12
800 ,p_name_column_rec.per_information13
801 ,p_name_column_rec.per_information14
802 ,p_name_column_rec.per_information15
803 ,p_name_column_rec.per_information16
804 ,p_name_column_rec.per_information17
805 ,p_name_column_rec.per_information18
806 ,p_name_column_rec.per_information19
807 ,p_name_column_rec.per_information20
808 ,p_name_column_rec.per_information21
809 ,p_name_column_rec.per_information22
810 ,p_name_column_rec.per_information23
811 ,p_name_column_rec.per_information24
812 ,p_name_column_rec.per_information25
813 ,p_name_column_rec.per_information26
814 ,p_name_column_rec.per_information27
815 ,p_name_column_rec.per_information28
816 ,p_name_column_rec.per_information29
817 ,p_name_column_rec.per_information30;
818
819 p_formatted_name := substr(rtrim(l_formatted_name),1,240);
820 end if;
821 --
822 exception
823 when e_InvalidProc then -- we need to trap this error in case procedure
824 -- does not exist; that's a a valid condition
825 p_formatted_name := null;
826 --
827 end derive_name_using_seeded_proc;
828 --
829 -- ----------------------------------------------------------------------------
830 -- |------------------------< derive_person_names >---------------------------|
831 -- ----------------------------------------------------------------------------
832 --
833 procedure derive_person_names
834 (p_format_name hr_name_formats.format_name%TYPE,
835 p_business_group_id per_all_people_f.business_group_id%TYPE,
836 p_person_id per_all_people_f.person_id%TYPE,
837 p_first_name per_all_people_f.first_name%TYPE,
838 p_middle_names per_all_people_f.middle_names%TYPE,
839 p_last_name per_all_people_f.last_name%TYPE,
840 p_known_as per_all_people_f.known_as%TYPE,
841 p_title per_all_people_f.title%TYPE,
842 p_suffix per_all_people_f.suffix%TYPE,
843 p_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE,
844 p_date_of_birth per_all_people_f.date_of_birth%TYPE,
848 p_applicant_number per_all_people_f.applicant_number%TYPE DEFAULT NULL,
845 p_previous_last_name per_all_people_f.previous_last_name%TYPE DEFAULT NULL,
846 p_email_address per_all_people_f.email_address%TYPE DEFAULT NULL,
847 p_employee_number per_all_people_f.employee_number%TYPE DEFAULT NULL,
849 p_npw_number per_all_people_f.npw_number%TYPE DEFAULT NULL,
850 p_per_information1 per_all_people_f.per_information1%TYPE DEFAULT NULL,
851 p_per_information2 per_all_people_f.per_information2%TYPE DEFAULT NULL,
852 p_per_information3 per_all_people_f.per_information3%TYPE DEFAULT NULL,
853 p_per_information4 per_all_people_f.per_information4%TYPE DEFAULT NULL,
854 p_per_information5 per_all_people_f.per_information5%TYPE DEFAULT NULL,
855 p_per_information6 per_all_people_f.per_information6%TYPE DEFAULT NULL,
856 p_per_information7 per_all_people_f.per_information7%TYPE DEFAULT NULL,
857 p_per_information8 per_all_people_f.per_information8%TYPE DEFAULT NULL,
858 p_per_information9 per_all_people_f.per_information9%TYPE DEFAULT NULL,
859 p_per_information10 per_all_people_f.per_information10%TYPE DEFAULT NULL,
860 p_per_information11 per_all_people_f.per_information11%TYPE DEFAULT NULL,
861 p_per_information12 per_all_people_f.per_information12%TYPE DEFAULT NULL,
862 p_per_information13 per_all_people_f.per_information13%TYPE DEFAULT NULL,
863 p_per_information14 per_all_people_f.per_information14%TYPE DEFAULT NULL,
864 p_per_information15 per_all_people_f.per_information15%TYPE DEFAULT NULL,
865 p_per_information16 per_all_people_f.per_information16%TYPE DEFAULT NULL,
866 p_per_information17 per_all_people_f.per_information17%TYPE DEFAULT NULL,
867 p_per_information18 per_all_people_f.per_information18%TYPE DEFAULT NULL,
868 p_per_information19 per_all_people_f.per_information19%TYPE DEFAULT NULL,
869 p_per_information20 per_all_people_f.per_information20%TYPE DEFAULT NULL,
870 p_per_information21 per_all_people_f.per_information21%TYPE DEFAULT NULL,
871 p_per_information22 per_all_people_f.per_information22%TYPE DEFAULT NULL,
872 p_per_information23 per_all_people_f.per_information23%TYPE DEFAULT NULL,
873 p_per_information24 per_all_people_f.per_information24%TYPE DEFAULT NULL,
874 p_per_information25 per_all_people_f.per_information25%TYPE DEFAULT NULL,
875 p_per_information26 per_all_people_f.per_information26%TYPE DEFAULT NULL,
876 p_per_information27 per_all_people_f.per_information27%TYPE DEFAULT NULL,
877 p_per_information28 per_all_people_f.per_information28%TYPE DEFAULT NULL,
878 p_per_information29 per_all_people_f.per_information29%TYPE DEFAULT NULL,
879 p_per_information30 per_all_people_f.per_information30%TYPE DEFAULT NULL,
880 p_attribute1 per_all_people_f.attribute1%TYPE DEFAULT NULL,
881 p_attribute2 per_all_people_f.attribute2%TYPE DEFAULT NULL,
882 p_attribute3 per_all_people_f.attribute3%TYPE DEFAULT NULL,
883 p_attribute4 per_all_people_f.attribute4%TYPE DEFAULT NULL,
884 p_attribute5 per_all_people_f.attribute5%TYPE DEFAULT NULL,
885 p_attribute6 per_all_people_f.attribute6%TYPE DEFAULT NULL,
886 p_attribute7 per_all_people_f.attribute7%TYPE DEFAULT NULL,
887 p_attribute8 per_all_people_f.attribute8%TYPE DEFAULT NULL,
888 p_attribute9 per_all_people_f.attribute9%TYPE DEFAULT NULL,
889 p_attribute10 per_all_people_f.attribute10%TYPE DEFAULT NULL,
890 p_attribute11 per_all_people_f.attribute11%TYPE DEFAULT NULL,
891 p_attribute12 per_all_people_f.attribute12%TYPE DEFAULT NULL,
892 p_attribute13 per_all_people_f.attribute13%TYPE DEFAULT NULL,
893 p_attribute14 per_all_people_f.attribute14%TYPE DEFAULT NULL,
894 p_attribute15 per_all_people_f.attribute15%TYPE DEFAULT NULL,
895 p_attribute16 per_all_people_f.attribute16%TYPE DEFAULT NULL,
896 p_attribute17 per_all_people_f.attribute17%TYPE DEFAULT NULL,
897 p_attribute18 per_all_people_f.attribute18%TYPE DEFAULT NULL,
898 p_attribute19 per_all_people_f.attribute19%TYPE DEFAULT NULL,
899 p_attribute20 per_all_people_f.attribute20%TYPE DEFAULT NULL,
900 p_attribute21 per_all_people_f.attribute21%TYPE DEFAULT NULL,
901 p_attribute22 per_all_people_f.attribute22%TYPE DEFAULT NULL,
902 p_attribute23 per_all_people_f.attribute23%TYPE DEFAULT NULL,
903 p_attribute24 per_all_people_f.attribute24%TYPE DEFAULT NULL,
904 p_attribute25 per_all_people_f.attribute25%TYPE DEFAULT NULL,
905 p_attribute26 per_all_people_f.attribute26%TYPE DEFAULT NULL,
906 p_attribute27 per_all_people_f.attribute27%TYPE DEFAULT NULL,
907 p_attribute28 per_all_people_f.attribute28%TYPE DEFAULT NULL,
908 p_attribute29 per_all_people_f.attribute29%TYPE DEFAULT NULL,
909 p_attribute30 per_all_people_f.attribute30%TYPE DEFAULT NULL,
910 p_full_name OUT NOCOPY per_all_people_f.full_name%TYPE ,
911 p_order_name OUT NOCOPY per_all_people_f.order_name%TYPE,
912 p_global_name OUT NOCOPY per_all_people_f.global_name%TYPE,
913 p_local_name OUT NOCOPY per_all_people_f.local_name%TYPE,
914 p_duplicate_flag OUT NOCOPY VARCHAR2
915 ) is
916 --
917 l_proc CONSTANT varchar2(80) := g_package||'get_derive_person_names';
918 --
919 l_person_names_rec hr_person_name.t_nameColumns_Rec;
920 l_format_name hr_name_formats.format_name%TYPE;
921 l_legislation_code varchar2(30);
922 l_FULL_NAME_format_mask hr_name_formats.format_mask%TYPE;
923 l_ORDER_NAME_format_mask hr_name_formats.format_mask%TYPE;
924 l_GLOBAL_NAME_format_mask hr_name_formats.format_mask%TYPE;
925 l_LOCAL_NAME_format_mask hr_name_formats.format_mask%TYPE;
926 l_full_name_formatted varchar2(2000);
927 l_order_name_formatted varchar2(2000);
928 l_global_name_formatted varchar2(2000);
929 l_local_name_formatted varchar2(2000);
930 l_pkg_full_name VARCHAR2(50);
931 l_proc_full_name VARCHAR2(50);
932 l_pkg_order_name VARCHAR2(50);
933 l_proc_order_name VARCHAR2(50);
937 l_first_char VARCHAR2(5);
934 l_gen_all_per_cols VARCHAR2(10);
935 --
936 local_warning exception;
938 l_second_char VARCHAR2(5);
939 l_ul_check VARCHAR2(15);
940 l_lu_check VARCHAR2(15);
941 l_uu_check VARCHAR2(15);
942 l_ll_check VARCHAR2(15);
943 l_status varchar(5);
944 --
945 cursor csr_get_format_mask
946 (cp_format_name varchar2
947 ,cp_legcode varchar2
948 ,cp_user_format_choice varchar2 ) is
949 select nmf.format_mask
950 from HR_NAME_FORMATS nmf
951 where nmf.format_name = cp_format_name
952 and (cp_legcode is not null and nmf.legislation_code = cp_legcode
953 or
954 cp_legcode is null and nmf.legislation_code is null)
955 and nmf.user_format_choice = cp_user_format_choice;
956 --
957 begin
958 -- ------------------------------------------------------------------------+
959 -- Populate record with column values
960 -- ------------------------------------------------------------------------+
961 l_person_names_rec.first_name := p_first_name;
962 l_person_names_rec.middle_names := p_middle_names;
963 l_person_names_rec.last_name := p_last_name;
964 l_person_names_rec.known_as := p_known_as;
965 l_person_names_rec.title := p_title;
966 l_person_names_rec.suffix := p_suffix;
967 l_person_names_rec.pre_name_adjunct := p_pre_name_adjunct;
968 l_person_names_rec.previous_last_name := p_previous_last_name;
969 l_person_names_rec.email_address := p_email_address;
970 l_person_names_rec.employee_number := p_employee_number;
971 l_person_names_rec.applicant_number := p_applicant_number;
972 l_person_names_rec.npw_number := p_npw_number;
973 l_person_names_rec.per_information1 := p_per_information1;
974 l_person_names_rec.per_information2 := p_per_information2;
975 l_person_names_rec.per_information3 := p_per_information3;
976 l_person_names_rec.per_information4 := p_per_information4;
977 l_person_names_rec.per_information5 := p_per_information5;
978 l_person_names_rec.per_information6 := p_per_information6;
979 l_person_names_rec.per_information7 := p_per_information7;
980 l_person_names_rec.per_information8 := p_per_information8;
981 l_person_names_rec.per_information9 := p_per_information9;
982 l_person_names_rec.per_information10 := p_per_information10;
983 l_person_names_rec.per_information11 := p_per_information11;
984 l_person_names_rec.per_information12 := p_per_information12;
985 l_person_names_rec.per_information13 := p_per_information13;
986 l_person_names_rec.per_information14 := p_per_information14;
987 l_person_names_rec.per_information15 := p_per_information15;
988 l_person_names_rec.per_information16 := p_per_information16;
989 l_person_names_rec.per_information17 := p_per_information17;
990 l_person_names_rec.per_information18 := p_per_information18;
991 l_person_names_rec.per_information19 := p_per_information19;
992 l_person_names_rec.per_information20 := p_per_information20;
993 l_person_names_rec.per_information21 := p_per_information21;
994 l_person_names_rec.per_information22 := p_per_information22;
995 l_person_names_rec.per_information23 := p_per_information23;
996 l_person_names_rec.per_information24 := p_per_information24;
997 l_person_names_rec.per_information25 := p_per_information25;
998 l_person_names_rec.per_information26 := p_per_information26;
999 l_person_names_rec.per_information27 := p_per_information27;
1000 l_person_names_rec.per_information28 := p_per_information28;
1001 l_person_names_rec.per_information29 := p_per_information29;
1002 l_person_names_rec.per_information30 := p_per_information30;
1003 l_person_names_rec.attribute1 := p_attribute1;
1004 l_person_names_rec.attribute2 := p_attribute2;
1005 l_person_names_rec.attribute3 := p_attribute3;
1006 l_person_names_rec.attribute4 := p_attribute4;
1007 l_person_names_rec.attribute5 := p_attribute5;
1008 l_person_names_rec.attribute6 := p_attribute6;
1009 l_person_names_rec.attribute7 := p_attribute7;
1010 l_person_names_rec.attribute8 := p_attribute8;
1011 l_person_names_rec.attribute9 := p_attribute9;
1012 l_person_names_rec.attribute10 := p_attribute10;
1013 l_person_names_rec.attribute11 := p_attribute11;
1014 l_person_names_rec.attribute12 := p_attribute12;
1015 l_person_names_rec.attribute13 := p_attribute13;
1016 l_person_names_rec.attribute14 := p_attribute14;
1017 l_person_names_rec.attribute15 := p_attribute15;
1018 l_person_names_rec.attribute16 := p_attribute16;
1019 l_person_names_rec.attribute17 := p_attribute17;
1020 l_person_names_rec.attribute18 := p_attribute18;
1021 l_person_names_rec.attribute19 := p_attribute19;
1022 l_person_names_rec.attribute20 := p_attribute20;
1023 l_person_names_rec.attribute21 := p_attribute21;
1024 l_person_names_rec.attribute22 := p_attribute22;
1025 l_person_names_rec.attribute23 := p_attribute23;
1026 l_person_names_rec.attribute24 := p_attribute24;
1027 l_person_names_rec.attribute25 := p_attribute25;
1028 l_person_names_rec.attribute26 := p_attribute26;
1029 l_person_names_rec.attribute27 := p_attribute27;
1030 l_person_names_rec.attribute28 := p_attribute28;
1031 l_person_names_rec.attribute29 := p_attribute29;
1032 l_person_names_rec.attribute30 := p_attribute30;
1033 l_person_names_rec.full_name := null;
1034
1035
1036 -- ------------------------------------------------------------------------+
1037 -- End populate record
1038 -- ------------------------------------------------------------------------+
1039 --
1040 -- Initialize local variables
1044 l_local_name_formatted := null;
1041 --
1042 p_duplicate_flag:= 'N';
1043 l_global_name_formatted := null;
1045 l_full_name_formatted := null;
1046 l_order_name_formatted := null;
1047 l_GLOBAL_NAME_format_mask := null;
1048 l_LOCAL_NAME_format_mask := null;
1049 l_FULL_NAME_format_mask := null;
1050 l_ORDER_NAME_format_mask := null;
1051 --
1052 l_format_name := p_format_name;
1053 if p_format_name is null then
1054 l_gen_all_per_cols := 'Y';
1055 else
1056 l_gen_all_per_cols := 'N';
1057 end if;
1058 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
1059 -- -------------------------------------------------------------------+
1060 -- Derive FULL_NAME
1061 -- -------------------------------------------------------------------+
1062 if l_format_name = g_FULL_NAME or l_gen_all_per_cols = 'Y' then
1063 --
1064 open csr_get_format_mask(g_FULL_NAME, l_legislation_code,'L');
1065 fetch csr_get_format_mask into l_FULL_NAME_format_mask;
1066 if csr_get_format_mask%NOTFOUND then
1067 close csr_get_format_mask;
1068 --
1069 get_seeded_procedure_name
1070 (p_format_name => g_FULL_NAME
1071 ,p_legislation_code => l_legislation_code
1072 ,p_package_name => l_pkg_full_name
1073 ,p_procedure_name => l_proc_full_name);
1074 --
1075 if l_pkg_full_name is null then -- use seeded procedure?
1076 --
1077 -- seeded procedure does not exist, use seeded format
1078 --
1079 open csr_get_format_mask(g_FULL_NAME, null,'L');
1080 fetch csr_get_format_mask into l_FULL_NAME_format_mask;
1081 if csr_get_format_mask%NOTFOUND then
1082 l_FULL_NAME_format_mask := null;
1083 end if;
1084 close csr_get_format_mask;
1085 end if;
1086 else
1087 close csr_get_format_mask;
1088 end if;
1089 -- -------------------------------------------------------------------+
1090 -- DERIVE the name
1091 -- -------------------------------------------------------------------+
1092 if l_FULL_NAME_format_mask is not null then
1093 --
1094 -- replace all tokens in format mask using name column values
1095 --
1096 l_full_name_formatted := l_FULL_NAME_format_mask;
1097 get_formatted_name(p_name_values => l_person_names_rec
1098 ,p_formatted_name => l_full_name_formatted);
1099 --
1100 l_full_name_formatted := substr(l_full_name_formatted,1,240);
1101 --
1102 elsif l_pkg_full_name is not null then
1103 -- use seeded procedure to derive name
1104 derive_name_using_seeded_proc
1105 (p_format_name => g_FULL_NAME
1106 ,p_legislation_code => l_legislation_code
1107 ,p_name_column_rec => l_person_names_rec
1108 ,p_package_name => l_pkg_full_name
1109 ,p_procedure_name => l_proc_full_name
1110 ,p_formatted_name => l_full_name_formatted);
1111 l_full_name_formatted := rtrim(l_full_name_formatted);
1112 end if;
1113 l_person_names_rec.full_name := l_full_name_formatted;
1114 end if;
1115 -- -------------------------------------------------------------------+
1116 -- Derive ORDER_NAME
1117 -- -------------------------------------------------------------------+
1118 if l_format_name = g_ORDER_NAME or l_gen_all_per_cols = 'Y' then
1119 open csr_get_format_mask(g_ORDER_NAME, l_legislation_code,'L');
1120 fetch csr_get_format_mask into l_ORDER_NAME_format_mask;
1121 if csr_get_format_mask%NOTFOUND then
1122 close csr_get_format_mask;
1123 --
1124 get_seeded_procedure_name
1125 (p_format_name => g_ORDER_NAME
1126 ,p_legislation_code => l_legislation_code
1127 ,p_package_name => l_pkg_order_name
1128 ,p_procedure_name => l_proc_order_name);
1129 --
1130 if l_pkg_order_name is null then -- use seeded procedure?
1131 --
1132 -- seeded procedure does not exist, use seeded format
1133 --
1134 open csr_get_format_mask(g_ORDER_NAME, null,'L');
1135 fetch csr_get_format_mask into l_ORDER_NAME_format_mask;
1136 if csr_get_format_mask%NOTFOUND then
1137 l_ORDER_NAME_format_mask := null;
1138 end if;
1139 close csr_get_format_mask;
1140 end if;
1141 else
1142 close csr_get_format_mask;
1143 end if;
1144 -- -------------------------------------------------------------------+
1145 -- Derive Order Name
1146 -- -------------------------------------------------------------------+
1147 if l_ORDER_NAME_format_mask is not null then
1148 --
1149 -- replace all tokens in format mask using name column values
1150 --
1151 l_order_name_formatted := l_ORDER_NAME_format_mask;
1152 get_formatted_name(p_name_values => l_person_names_rec
1153 ,p_formatted_name => l_order_name_formatted);
1154 --
1155 l_order_name_formatted := substr(l_order_name_formatted,1,240);
1156 --
1157 elsif l_pkg_order_name is not null then
1158 -- use seeded procedure to derive name
1159 derive_name_using_seeded_proc
1160 (p_format_name => g_ORDER_NAME
1161 ,p_legislation_code => l_legislation_code
1162 ,p_name_column_rec => l_person_names_rec
1163 ,p_package_name => l_pkg_order_name
1164 ,p_procedure_name => l_proc_order_name
1165 ,p_formatted_name => l_order_name_formatted);
1169 -- -------------------------------------------------------------------+
1166 l_order_name_formatted := rtrim(l_order_name_formatted);
1167 end if;
1168 end if;
1170 -- -------------------------------------------------------------------+
1171 if l_format_name in (g_DISPLAY_NAME, g_LIST_NAME)
1172 or l_gen_all_per_cols = 'Y' then
1173 --
1174 if l_gen_all_per_cols = 'Y' then
1175 l_format_name := g_LIST_NAME;
1176 end if;
1177 --
1178 -- Get Global format mask
1179 --
1180 open csr_get_format_mask(l_format_name, l_legislation_code,'G');
1181 fetch csr_get_format_mask into l_GLOBAL_NAME_format_mask;
1182 if csr_get_format_mask%NOTFOUND then
1183 close csr_get_format_mask;
1184 -- look for non legislation specific
1185 open csr_get_format_mask(l_format_name, null,'G');
1186 fetch csr_get_format_mask into l_GLOBAL_NAME_format_mask;
1187 if csr_get_format_mask%NOTFOUND then
1188 l_GLOBAL_NAME_format_mask := null;
1189 end if;
1190 close csr_get_format_mask;
1191 else
1192 close csr_get_format_mask;
1193 end if;
1194 --
1195 -- Get Local format mask
1196 --
1197 open csr_get_format_mask(l_format_name, l_legislation_code,'L');
1198 fetch csr_get_format_mask into l_LOCAL_NAME_format_mask;
1199 if csr_get_format_mask%NOTFOUND then
1200 close csr_get_format_mask;
1201 -- look for non legislation specific
1202 open csr_get_format_mask(l_format_name, null,'L');
1203 fetch csr_get_format_mask into l_LOCAL_NAME_format_mask;
1204 if csr_get_format_mask%NOTFOUND then
1205 l_LOCAL_NAME_format_mask := null;
1206 end if;
1207 close csr_get_format_mask;
1208 else
1209 close csr_get_format_mask;
1210 end if;
1211 -- -------------------------------------------------------------------+
1212 -- Derive Global and Local Names
1213 -- -------------------------------------------------------------------+
1214 if l_GLOBAL_NAME_format_mask is not null then
1215 --
1216 -- replace all tokens in format mask using name column values
1217 --
1218 l_global_name_formatted := l_GLOBAL_NAME_format_mask;
1219 get_formatted_name(p_name_values => l_person_names_rec
1220 ,p_formatted_name => l_global_name_formatted);
1221 --
1222 l_global_name_formatted := substr(l_global_name_formatted,1,240);
1223 --
1224 end if;
1225 -- -------------------------------------------------------------------+
1226 if l_LOCAL_NAME_format_mask is not null then
1227 --
1228 -- replace all tokens in format mask using name column values
1229 --
1230 l_local_name_formatted := l_LOCAL_NAME_format_mask;
1231 get_formatted_name(p_name_values => l_person_names_rec
1232 ,p_formatted_name => l_local_name_formatted);
1233 --
1234 l_local_name_formatted := substr(l_local_name_formatted,1,240);
1235 --
1236 end if;
1237 --
1238 end if; -- Display/List Names
1239 -- -------------------------------------------------------------------+
1240 --
1241 -- Set OUT parameters
1242 --
1243 p_full_name := l_full_name_formatted;
1244 p_order_name := l_order_name_formatted;
1245 p_global_name := l_global_name_formatted;
1246 p_local_name := l_local_name_formatted;
1247 -- ----------------------------------------------------------------------- +
1248 -- Check duplicates Cross Business Groups is not enabled
1249 -- ----------------------------------------------------------------------- +
1250 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
1251 declare
1252 -- bug 3988762
1253 l_legislation_code VARCHAR2(10) := HR_API.GET_LEGISLATION_CONTEXT;
1254 begin
1255 --
1256 l_first_char := substr( p_last_name , 1 , 1 ) ;
1257 l_second_char := substr( p_last_name , 2 , 1 ) ;
1258 l_ul_check := upper(l_first_char)||lower(l_second_char)||'%';
1259 l_lu_check := lower(l_first_char)||upper(l_second_char)||'%';
1260 l_uu_check := upper(l_first_char)||upper(l_second_char)||'%';
1261 l_ll_check := lower(l_first_char)||lower(l_second_char)||'%';
1262
1263 SELECT 'Y'
1264 INTO l_status
1265 FROM sys.dual
1266 WHERE EXISTS (SELECT /*+ no_expand */ 'Duplicate Person Exists'
1267 FROM per_all_people_f pp
1268 WHERE /* Perform case insensitive check on last name */
1269 /* trying to use the index on last name */
1270 upper(pp.last_name) = upper(p_last_name)
1271 AND ( pp.last_name like l_ul_check
1272 OR pp.last_name like l_lu_check
1273 OR pp.last_name like l_uu_check
1274 OR pp.last_name like l_ll_check
1275 )
1276 AND (upper(pp.first_name) = upper(p_first_name)
1277 OR p_first_name IS NULL
1278 OR pp.first_name IS NULL)
1279 AND (pp.date_of_birth = p_date_of_birth
1280 OR p_date_of_birth IS NULL
1281 OR pp.date_of_birth IS NULL)
1282 AND ((p_person_id IS NOT NULL
1283 AND p_person_id <> pp.person_id)
1284 OR p_person_id IS NULL)
1285 AND pp.business_group_id +0 = p_business_group_id
1286 AND -- Include Kanji Name for JP Legislation
1287 (
1288 l_legislation_code <> 'JP'
1289 OR
1290 (
1291 l_legislation_code = 'JP'
1292 AND
1296 OR pp.per_information18 IS NULL
1293 (
1294 upper(pp.per_information18) = upper(p_per_information18)
1295 OR p_per_information18 IS NULL
1297 )
1298 AND
1299 (
1300 upper(pp.per_information19) = upper(p_per_information19)
1301 OR p_per_information19 IS NULL
1302 OR pp.per_information18 IS NULL
1303 )
1304 )
1305 )
1306 );
1307 --
1308 hr_utility.set_message(801,'HR_PERSON_DUPLICATE');
1309
1310 raise local_warning;
1311
1312 --
1313 exception
1314 when NO_DATA_FOUND then null ;
1315 --
1316 end;
1317 end if;
1318 --
1319 exception
1320 when local_warning then
1321 hr_utility.set_warning;
1322 p_duplicate_flag:='Y';
1323 --
1324 end derive_person_names;
1325 --
1326 -- ----------------------------------------------------------------------------
1327 -- |--------------------< derive_formatted_name >-----------------------------|
1328 -- ----------------------------------------------------------------------------
1329 -- Used within Conc Program
1330 --
1331 FUNCTION derive_formatted_name
1332 (p_person_names_rec in hr_person_name.t_nameColumns_Rec
1333 ,p_format_name in varchar2
1334 ,p_legislation_code in varchar2
1335 ,p_format_mask in varchar2
1336 ,p_seeded_pkg in varchar2 default NULL
1337 ,p_seeded_procedure in varchar2 default NULL
1338 ,p_seeded_format_mask in varchar2 default NULL) return varchar2 IS
1339 --
1340 l_formatted_name varchar2(2000);
1341 --
1342 BEGIN
1343 --
1344 l_formatted_name := p_format_mask;
1345 if p_format_name in (g_FULL_NAME, g_ORDER_NAME) then
1346 if p_format_mask is null then
1347 if p_seeded_pkg is not null and p_seeded_procedure is not null then
1348 derive_name_using_seeded_proc
1349 (p_format_name => p_format_name
1350 ,p_legislation_code => p_legislation_code
1351 ,p_name_column_rec => p_person_names_rec
1352 ,p_package_name => p_seeded_pkg
1353 ,p_procedure_name => p_seeded_procedure
1354 ,p_formatted_name => l_formatted_name
1355 );
1356 elsif p_seeded_format_mask is not null then
1357 --
1358 -- replace all tokens in format mask using name column values
1359 --
1360 l_formatted_name := p_seeded_format_mask;
1361 get_formatted_name(p_name_values => p_person_names_rec
1362 ,p_formatted_name => l_formatted_name);
1363 --
1364 else -- this should be abnormal condition
1365 if p_format_name = g_FULL_NAME then
1366 l_formatted_name := p_person_names_rec.full_name;
1367 elsif p_format_name = g_ORDER_NAME then
1368 l_formatted_name := p_person_names_rec.order_name;
1369 end if;
1370 end if;
1371 else -- localized format mask is not null
1372 --
1373 -- replace all tokens in format mask using name column values
1374 --
1375 get_formatted_name(p_name_values => p_person_names_rec
1376 ,p_formatted_name => l_formatted_name);
1377 --
1378 end if;
1379
1380 else
1381 --
1382 -- replace all tokens in format mask using name column values
1383 --
1384 if l_formatted_name is null and p_seeded_format_mask is not null then
1385 l_formatted_name := p_seeded_format_mask;
1386 end if;
1387 get_formatted_name(p_name_values => p_person_names_rec
1388 ,p_formatted_name => l_formatted_name);
1389 --
1390 end if;
1391 --
1392 l_formatted_name := substr(l_formatted_name,1,240);
1393 --
1394 RETURN(l_formatted_name);
1395 --
1396 END derive_formatted_name;
1397 --
1398 --
1399 -- ---------------------------------------------------------------------------+
1400 -- |--------------------< get_formatMask_desc >-------------------------------|
1401 -- ---------------------------------------------------------------------------+
1402 function get_formatMask_desc(p_formatMask varchar2) return varchar2 is
1403 --
1404 l_formatted_mask varchar2(2000);
1405 l_token_start_pos number;
1406 l_token_end_pos number;
1407 l_token_value varchar2(240);
1408 l_delimeter_start_pos number;
1409 l_delimeter_end_pos number;
1410 l_expr varchar2(240);
1411 --
1412 cursor csr_valid_tokens is
1413 select lookup_code token_name, meaning token_desc
1414 from hr_standard_lookups
1415 where lookup_type = 'PER_FORMAT_MASK_TOKENS';
1416 --
1417 begin
1418 l_token_start_pos := 0;
1419 l_token_end_pos := 0;
1420 l_token_value := null;
1421 l_delimeter_start_pos := 0;
1422 l_delimeter_end_pos := 0;
1423 l_expr := null;
1424 --
1425 l_formatted_mask := p_formatMask;
1426 if l_formatted_mask is not null then
1427 for l_token in csr_valid_tokens loop
1428 --
1429 -- check whether token is referenced by format mask
1430 -- get the start/end positions in string
1431 --
1432 get_token_position(p_format_mask => l_formatted_mask
1433 ,p_token => l_token.token_name
1434 ,p_start_pos => l_token_start_pos
1435 ,p_end_pos => l_token_end_pos);
1436 if l_token_start_pos > 0 then
1437 -- found token referenced in format mask
1438 --
1439 if l_token.token_desc is not null then
1443 end if;
1440 l_formatted_mask := REPLACE(l_formatted_mask,'$'||l_token.token_name||'$',l_token.token_desc);
1441 else
1442 l_formatted_mask := REPLACE(l_formatted_mask,'$'||l_token.token_name||'$',l_token.token_name);
1444 end if;
1445 end loop;
1446 l_formatted_mask := REPLACE(l_formatted_mask, '|', null);
1447 end if;
1448 --
1449 return(l_formatted_mask);
1450 --
1451 end get_formatMask_desc;
1452 --
1453 --
1454 -- ---------------------------------------------------------------------------+
1455 -- |-----------------------< get_token >-------------------------------------|
1456 -- ---------------------------------------------------------------------------+
1457 function get_token(p_format_mask in varchar2
1458 ,p_token_number in number) return varchar2 is
1459 --
1460 l_token_start_pos number;
1461 l_token_end_pos number;
1462 l_token fnd_lookup_values.lookup_code%type;
1463 --
1464 begin
1465 if p_format_mask is null or p_token_number = 0 then
1466 l_token := null;
1467 else
1468 l_token_start_pos := instr(p_format_mask,'$',1,p_token_number + (p_token_number -1));
1469 l_token_end_pos := instr(p_format_mask,'$',1,p_token_number + p_token_number);
1470 l_token := substr(p_format_mask,l_token_start_pos + 1,l_token_end_pos - l_token_start_pos - 1);
1471 end if;
1472 --
1473 return(l_token);
1474 --
1475 end get_token;
1476 --
1477 --
1478 -- ---------------------------------------------------------------------------+
1479 -- |-----------------------< get_token_desc >---------------------------------|
1480 -- ---------------------------------------------------------------------------+
1481 function get_token_desc(p_token in varchar2) return varchar2 is
1482 --
1483 l_token_desc fnd_lookup_values.meaning%type;
1484 --
1485 cursor csr_valid_tokens(cp_token varchar2) is
1486 select meaning token_desc
1487 from hr_standard_lookups
1488 where lookup_type = 'PER_FORMAT_MASK_TOKENS'
1489 and lookup_code = cp_token;
1490 begin
1491 open csr_valid_tokens(p_token);
1492 fetch csr_valid_tokens into l_token_desc;
1493 close csr_valid_tokens;
1494 --
1495 return(l_token_desc);
1496 --
1497 end get_token_desc;
1498 --
1499 --
1500 -- ---------------------------------------------------------------------------+
1501 -- |-----------------------< get_prefix >-------------------------------------|
1502 -- ---------------------------------------------------------------------------+
1503 function get_prefix(p_format_mask in varchar2
1504 ,p_token_number in number) return varchar2 is
1505 --
1506 l_token_start_pos number;
1507 l_token_end_pos number;
1508 l_delimeter_pos number;
1509 l_prefix varchar2(30);
1510 --
1511 begin
1512 if p_format_mask is null or p_token_number = 0 then
1513 l_prefix := null;
1514 else
1515 l_token_start_pos := instr(p_format_mask,'$',1,p_token_number + (p_token_number -1));
1516 l_delimeter_pos := instr(substr(p_format_mask,1,l_token_start_pos),'|',-1);
1517
1518 l_prefix := substr(p_format_mask,l_delimeter_pos+1,l_token_start_pos - l_delimeter_pos -1);
1519 end if;
1520 --
1521 return(l_prefix);
1522 --
1523 end get_prefix;
1524 --
1525 --
1526 -- ---------------------------------------------------------------------------+
1527 -- |-----------------------< get_suffix >-------------------------------------|
1528 -- ---------------------------------------------------------------------------+
1529 function get_suffix(p_format_mask in varchar2
1530 ,p_token_number in number) return varchar2 is
1531 --
1532 l_token_start_pos number;
1533 l_token_end_pos number;
1534 l_delimeter_pos number;
1535 l_suffix varchar2(30);
1536 --
1537 begin
1538 if p_format_mask is null or p_token_number = 0 then
1539 l_suffix := null;
1540 else
1541 l_token_end_pos := instr(p_format_mask,'$',1,(p_token_number + p_token_number -1) + 1);
1542 l_delimeter_pos := instr(substr(p_format_mask,l_token_end_pos),'|',1);
1543
1544 l_suffix := substr(p_format_mask,l_token_end_pos + 1, l_delimeter_pos-2);
1545 end if;
1546 --
1547 return(l_suffix);
1548 --
1549 end get_suffix;
1550 --
1551 --
1552 -- ---------------------------------------------------------------------------+
1553 -- |----------------------< get_total_tokens >--------------------------------|
1554 -- ---------------------------------------------------------------------------+
1555 function get_total_tokens(p_format_mask in varchar2) return number is
1556 --
1557 l_token_start_pos number;
1558 l_token_end_pos number;
1559 l_token fnd_lookup_values.lookup_code%type;
1560 l_mask hr_name_formats.format_mask%type;
1561 l_total number;
1562 l_count number;
1563 --
1564 begin
1565 l_total := 0;
1566 l_count := 0;
1567 if p_format_mask is not null then
1568 l_mask := p_format_mask;
1569 l_mask := REPLACE(l_mask, '|', null);
1570 while l_mask is not null loop
1571 l_token_start_pos := instr(l_mask,'$');
1572 l_token_end_pos := instr(l_mask,'$',1,2);
1573 if l_token_start_pos > 0 and l_token_end_pos > 0 then
1574 l_token := substr(l_mask,l_token_start_pos,l_token_end_pos - l_token_start_pos +1 );
1575 l_total := l_total + 1;
1576 --
1577 -- loop through the entire format mask and look for all occurrences of the token
1578 --
1579 l_count := 2; -- start with second occurrence
1580 loop
1584 else
1581 if instr(l_mask, l_token,1,l_count) > 0 then
1582 l_total := l_total + 1;
1583 l_count := l_count + 1;
1585 exit;
1586 end if;
1587 end loop;
1588 l_mask := REPLACE(l_mask, l_token, null);
1589 else
1590 l_mask := null;
1591 exit;
1592 end if;
1593 end loop;
1594 end if;
1595 --
1596 return (l_total);
1597 --
1598 end get_total_tokens;
1599 --
1600 --
1601 -- ---------------------------------------------------------------------------+
1602 -- |----------------------< get_space_before >--------------------------------|
1603 -- ---------------------------------------------------------------------------+
1604 function get_space_before(p_component varchar2) return varchar2 is
1605 --
1606 l_white_delimeter varchar2(10);
1607 --
1608 begin
1609 l_white_delimeter := 'N';
1610 if p_component is not null then
1611 if p_component = ' ' or instr(p_component,' ') = 1 then
1612 l_white_delimeter := 'Y';
1613 end if;
1614 end if;
1615 return (l_white_delimeter);
1616 end get_space_before;
1617 --
1618 -- ---------------------------------------------------------------------------+
1619 -- |----------------------< get_space_after >--------------------------------|
1620 -- ---------------------------------------------------------------------------+
1621 function get_space_after(p_component varchar2) return varchar2 is
1622 --
1623 l_white_delimeter varchar2(10);
1624 --
1625 begin
1626 l_white_delimeter := 'N';
1627 if p_component is not null then
1628 if p_component <> ' ' and instr(substr(p_component,2),' ') > 0 then
1629 l_white_delimeter := 'Y';
1630 end if;
1631 end if;
1632 return (l_white_delimeter);
1633 end get_space_after;
1634 --
1635 --
1636 -- ---------------------------------------------------------------------------+
1637 -- |----------------------< get_punctuation >--------------------------------|
1638 -- ---------------------------------------------------------------------------+
1639 function get_punctuation(p_component varchar2) return varchar2 is
1640 --
1641 l_punctuation varchar2(100);
1642 l_spaceB varchar2(10);
1643 l_spaceA varchar2(10);
1644 l_start number;
1645 l_end number;
1646
1647 --
1648 begin
1649 l_start := 1;
1650 l_end := length(p_component);
1651 if p_component is not null then
1652 if p_component = ' ' then
1653 l_punctuation := '';
1654 else
1655 l_spaceB := substr(p_component,1,1);
1656 l_spaceA := substr(p_component, l_end,1);
1657 if l_spaceB = ' ' then
1658 l_start := 2;
1659 end if;
1660 if l_spaceA = ' ' then
1661 l_end := l_end - 1;
1662 end if;
1663 l_punctuation := substr(p_component,l_start,l_end);
1664
1665 end if;
1666 end if;
1667 return (l_punctuation);
1668 end get_punctuation;
1669 --
1670 --
1671 -- ----------------------------------------------------------------------------
1672 -- |-----------------------< get_list_namne >--------------------------------|
1673 -- ----------------------------------------------------------------------------
1674 -- Description:
1675 -- This returns either a global or local name depending on the profile
1676 -- option setting. This function is to be used within the inter-operable
1677 -- views (See 4428910).
1678 --
1679 function get_list_name(p_global_name in varchar2
1680 ,p_local_name in varchar2) return varchar2 is
1681 begin
1682 if fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT') = 'G' then
1683 return p_global_name;
1684 else
1685 return p_local_name;
1686 end if;
1687 end get_list_name;
1688 --
1689 --
1690 --
1691 -- ------------------------------------------------------------------------------
1692 -- |--------------------------<derive_person_names>----------------------------|
1693 -- ----------------------------------------------------------------------------
1694 -- Description:
1695 -- This is overloaded method same as derive_person_names , but does not take
1696 -- person_id and does not perform the duplicate check.
1697 -- The procedure computes the global_name and return full_name,local_name and order_name.
1698 --
1699 --
1700 procedure derive_person_names
1701 (p_format_name hr_name_formats.format_name%TYPE,
1702 p_business_group_id per_all_people_f.business_group_id%TYPE,
1703 p_first_name per_all_people_f.first_name%TYPE,
1704 p_middle_names per_all_people_f.middle_names%TYPE,
1705 p_last_name per_all_people_f.last_name%TYPE,
1706 p_known_as per_all_people_f.known_as%TYPE,
1707 p_title per_all_people_f.title%TYPE,
1708 p_suffix per_all_people_f.suffix%TYPE,
1709 p_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE,
1710 p_date_of_birth per_all_people_f.date_of_birth%TYPE,
1711 p_previous_last_name per_all_people_f.previous_last_name%TYPE DEFAULT NULL,
1712 p_email_address per_all_people_f.email_address%TYPE DEFAULT NULL,
1713 p_employee_number per_all_people_f.employee_number%TYPE DEFAULT NULL,
1714 p_applicant_number per_all_people_f.applicant_number%TYPE DEFAULT NULL,
1715 p_npw_number per_all_people_f.npw_number%TYPE DEFAULT NULL,
1716 p_per_information1 per_all_people_f.per_information1%TYPE DEFAULT NULL,
1717 p_per_information2 per_all_people_f.per_information2%TYPE DEFAULT NULL,
1718 p_per_information3 per_all_people_f.per_information3%TYPE DEFAULT NULL,
1719 p_per_information4 per_all_people_f.per_information4%TYPE DEFAULT NULL,
1720 p_per_information5 per_all_people_f.per_information5%TYPE DEFAULT NULL,
1721 p_per_information6 per_all_people_f.per_information6%TYPE DEFAULT NULL,
1725 p_per_information10 per_all_people_f.per_information10%TYPE DEFAULT NULL,
1722 p_per_information7 per_all_people_f.per_information7%TYPE DEFAULT NULL,
1723 p_per_information8 per_all_people_f.per_information8%TYPE DEFAULT NULL,
1724 p_per_information9 per_all_people_f.per_information9%TYPE DEFAULT NULL,
1726 p_per_information11 per_all_people_f.per_information11%TYPE DEFAULT NULL,
1727 p_per_information12 per_all_people_f.per_information12%TYPE DEFAULT NULL,
1728 p_per_information13 per_all_people_f.per_information13%TYPE DEFAULT NULL,
1729 p_per_information14 per_all_people_f.per_information14%TYPE DEFAULT NULL,
1730 p_per_information15 per_all_people_f.per_information15%TYPE DEFAULT NULL,
1731 p_per_information16 per_all_people_f.per_information16%TYPE DEFAULT NULL,
1732 p_per_information17 per_all_people_f.per_information17%TYPE DEFAULT NULL,
1733 p_per_information18 per_all_people_f.per_information18%TYPE DEFAULT NULL,
1734 p_per_information19 per_all_people_f.per_information19%TYPE DEFAULT NULL,
1735 p_per_information20 per_all_people_f.per_information20%TYPE DEFAULT NULL,
1736 p_per_information21 per_all_people_f.per_information21%TYPE DEFAULT NULL,
1737 p_per_information22 per_all_people_f.per_information22%TYPE DEFAULT NULL,
1738 p_per_information23 per_all_people_f.per_information23%TYPE DEFAULT NULL,
1739 p_per_information24 per_all_people_f.per_information24%TYPE DEFAULT NULL,
1740 p_per_information25 per_all_people_f.per_information25%TYPE DEFAULT NULL,
1741 p_per_information26 per_all_people_f.per_information26%TYPE DEFAULT NULL,
1742 p_per_information27 per_all_people_f.per_information27%TYPE DEFAULT NULL,
1743 p_per_information28 per_all_people_f.per_information28%TYPE DEFAULT NULL,
1744 p_per_information29 per_all_people_f.per_information29%TYPE DEFAULT NULL,
1745 p_per_information30 per_all_people_f.per_information30%TYPE DEFAULT NULL,
1746 p_attribute1 per_all_people_f.attribute1%TYPE DEFAULT NULL,
1747 p_attribute2 per_all_people_f.attribute2%TYPE DEFAULT NULL,
1748 p_attribute3 per_all_people_f.attribute3%TYPE DEFAULT NULL,
1749 p_attribute4 per_all_people_f.attribute4%TYPE DEFAULT NULL,
1750 p_attribute5 per_all_people_f.attribute5%TYPE DEFAULT NULL,
1751 p_attribute6 per_all_people_f.attribute6%TYPE DEFAULT NULL,
1752 p_attribute7 per_all_people_f.attribute7%TYPE DEFAULT NULL,
1753 p_attribute8 per_all_people_f.attribute8%TYPE DEFAULT NULL,
1754 p_attribute9 per_all_people_f.attribute9%TYPE DEFAULT NULL,
1755 p_attribute10 per_all_people_f.attribute10%TYPE DEFAULT NULL,
1756 p_attribute11 per_all_people_f.attribute11%TYPE DEFAULT NULL,
1757 p_attribute12 per_all_people_f.attribute12%TYPE DEFAULT NULL,
1758 p_attribute13 per_all_people_f.attribute13%TYPE DEFAULT NULL,
1759 p_attribute14 per_all_people_f.attribute14%TYPE DEFAULT NULL,
1760 p_attribute15 per_all_people_f.attribute15%TYPE DEFAULT NULL,
1761 p_attribute16 per_all_people_f.attribute16%TYPE DEFAULT NULL,
1762 p_attribute17 per_all_people_f.attribute17%TYPE DEFAULT NULL,
1763 p_attribute18 per_all_people_f.attribute18%TYPE DEFAULT NULL,
1764 p_attribute19 per_all_people_f.attribute19%TYPE DEFAULT NULL,
1765 p_attribute20 per_all_people_f.attribute20%TYPE DEFAULT NULL,
1766 p_attribute21 per_all_people_f.attribute21%TYPE DEFAULT NULL,
1767 p_attribute22 per_all_people_f.attribute22%TYPE DEFAULT NULL,
1768 p_attribute23 per_all_people_f.attribute23%TYPE DEFAULT NULL,
1769 p_attribute24 per_all_people_f.attribute24%TYPE DEFAULT NULL,
1770 p_attribute25 per_all_people_f.attribute25%TYPE DEFAULT NULL,
1771 p_attribute26 per_all_people_f.attribute26%TYPE DEFAULT NULL,
1772 p_attribute27 per_all_people_f.attribute27%TYPE DEFAULT NULL,
1773 p_attribute28 per_all_people_f.attribute28%TYPE DEFAULT NULL,
1774 p_attribute29 per_all_people_f.attribute29%TYPE DEFAULT NULL,
1775 p_attribute30 per_all_people_f.attribute30%TYPE DEFAULT NULL,
1776 p_full_name OUT NOCOPY per_all_people_f.full_name%TYPE ,
1777 p_order_name OUT NOCOPY per_all_people_f.order_name%TYPE,
1778 p_global_name OUT NOCOPY per_all_people_f.global_name%TYPE,
1779 p_local_name OUT NOCOPY per_all_people_f.local_name%TYPE
1780 ) is
1781 --
1782 l_proc CONSTANT varchar2(80) := g_package||'get_derive_person_names';
1783 --
1784 l_person_names_rec hr_person_name.t_nameColumns_Rec;
1785 l_format_name hr_name_formats.format_name%TYPE;
1786 l_legislation_code varchar2(30);
1787 l_FULL_NAME_format_mask hr_name_formats.format_mask%TYPE;
1788 l_ORDER_NAME_format_mask hr_name_formats.format_mask%TYPE;
1789 l_GLOBAL_NAME_format_mask hr_name_formats.format_mask%TYPE;
1790 l_LOCAL_NAME_format_mask hr_name_formats.format_mask%TYPE;
1791 l_full_name_formatted varchar2(2000);
1792 l_order_name_formatted varchar2(2000);
1793 l_global_name_formatted varchar2(2000);
1794 l_local_name_formatted varchar2(2000);
1795 l_pkg_full_name VARCHAR2(50);
1796 l_proc_full_name VARCHAR2(50);
1797 l_pkg_order_name VARCHAR2(50);
1798 l_proc_order_name VARCHAR2(50);
1799 l_gen_all_per_cols VARCHAR2(10);
1800 --
1801 local_warning exception;
1802 l_first_char VARCHAR2(5);
1803 l_second_char VARCHAR2(5);
1804 l_ul_check VARCHAR2(15);
1805 l_lu_check VARCHAR2(15);
1806 l_uu_check VARCHAR2(15);
1807 l_ll_check VARCHAR2(15);
1808 l_status varchar(5);
1809 --
1810 cursor csr_get_format_mask
1811 (cp_format_name varchar2
1812 ,cp_legcode varchar2
1813 ,cp_user_format_choice varchar2 ) is
1814 select nmf.format_mask
1815 from HR_NAME_FORMATS nmf
1816 where nmf.format_name = cp_format_name
1817 and (cp_legcode is not null and nmf.legislation_code = cp_legcode
1818 or
1822 begin
1819 cp_legcode is null and nmf.legislation_code is null)
1820 and nmf.user_format_choice = cp_user_format_choice;
1821 --
1823 -- ------------------------------------------------------------------------+
1824 -- Populate record with column values
1825 -- ------------------------------------------------------------------------+
1826 l_person_names_rec.first_name := p_first_name;
1827 l_person_names_rec.middle_names := p_middle_names;
1828 l_person_names_rec.last_name := p_last_name;
1829 l_person_names_rec.known_as := p_known_as;
1830 l_person_names_rec.title := p_title;
1831 l_person_names_rec.suffix := p_suffix;
1832 l_person_names_rec.pre_name_adjunct := p_pre_name_adjunct;
1833 l_person_names_rec.previous_last_name := p_previous_last_name;
1834 l_person_names_rec.email_address := p_email_address;
1835 l_person_names_rec.employee_number := p_employee_number;
1836 l_person_names_rec.applicant_number := p_applicant_number;
1837 l_person_names_rec.npw_number := p_npw_number;
1838 l_person_names_rec.per_information1 := p_per_information1;
1839 l_person_names_rec.per_information2 := p_per_information2;
1840 l_person_names_rec.per_information3 := p_per_information3;
1841 l_person_names_rec.per_information4 := p_per_information4;
1842 l_person_names_rec.per_information5 := p_per_information5;
1843 l_person_names_rec.per_information6 := p_per_information6;
1844 l_person_names_rec.per_information7 := p_per_information7;
1845 l_person_names_rec.per_information8 := p_per_information8;
1846 l_person_names_rec.per_information9 := p_per_information9;
1847 l_person_names_rec.per_information10 := p_per_information10;
1848 l_person_names_rec.per_information11 := p_per_information11;
1849 l_person_names_rec.per_information12 := p_per_information12;
1850 l_person_names_rec.per_information13 := p_per_information13;
1851 l_person_names_rec.per_information14 := p_per_information14;
1852 l_person_names_rec.per_information15 := p_per_information15;
1853 l_person_names_rec.per_information16 := p_per_information16;
1854 l_person_names_rec.per_information17 := p_per_information17;
1855 l_person_names_rec.per_information18 := p_per_information18;
1856 l_person_names_rec.per_information19 := p_per_information19;
1857 l_person_names_rec.per_information20 := p_per_information20;
1858 l_person_names_rec.per_information21 := p_per_information21;
1859 l_person_names_rec.per_information22 := p_per_information22;
1860 l_person_names_rec.per_information23 := p_per_information23;
1861 l_person_names_rec.per_information24 := p_per_information24;
1862 l_person_names_rec.per_information25 := p_per_information25;
1863 l_person_names_rec.per_information26 := p_per_information26;
1864 l_person_names_rec.per_information27 := p_per_information27;
1865 l_person_names_rec.per_information28 := p_per_information28;
1866 l_person_names_rec.per_information29 := p_per_information29;
1867 l_person_names_rec.per_information30 := p_per_information30;
1868 l_person_names_rec.attribute1 := p_attribute1;
1869 l_person_names_rec.attribute2 := p_attribute2;
1870 l_person_names_rec.attribute3 := p_attribute3;
1871 l_person_names_rec.attribute4 := p_attribute4;
1872 l_person_names_rec.attribute5 := p_attribute5;
1873 l_person_names_rec.attribute6 := p_attribute6;
1874 l_person_names_rec.attribute7 := p_attribute7;
1875 l_person_names_rec.attribute8 := p_attribute8;
1876 l_person_names_rec.attribute9 := p_attribute9;
1877 l_person_names_rec.attribute10 := p_attribute10;
1878 l_person_names_rec.attribute11 := p_attribute11;
1879 l_person_names_rec.attribute12 := p_attribute12;
1880 l_person_names_rec.attribute13 := p_attribute13;
1881 l_person_names_rec.attribute14 := p_attribute14;
1882 l_person_names_rec.attribute15 := p_attribute15;
1883 l_person_names_rec.attribute16 := p_attribute16;
1884 l_person_names_rec.attribute17 := p_attribute17;
1885 l_person_names_rec.attribute18 := p_attribute18;
1886 l_person_names_rec.attribute19 := p_attribute19;
1887 l_person_names_rec.attribute20 := p_attribute20;
1888 l_person_names_rec.attribute21 := p_attribute21;
1889 l_person_names_rec.attribute22 := p_attribute22;
1890 l_person_names_rec.attribute23 := p_attribute23;
1891 l_person_names_rec.attribute24 := p_attribute24;
1892 l_person_names_rec.attribute25 := p_attribute25;
1893 l_person_names_rec.attribute26 := p_attribute26;
1894 l_person_names_rec.attribute27 := p_attribute27;
1895 l_person_names_rec.attribute28 := p_attribute28;
1896 l_person_names_rec.attribute29 := p_attribute29;
1897 l_person_names_rec.attribute30 := p_attribute30;
1898 l_person_names_rec.full_name := null;
1899
1900
1901 -- ------------------------------------------------------------------------+
1902 -- End populate record
1903 -- ------------------------------------------------------------------------+
1904 --
1905 -- Initialize local variables
1906 --
1907 l_global_name_formatted := null;
1908 l_local_name_formatted := null;
1909 l_full_name_formatted := null;
1910 l_order_name_formatted := null;
1911 l_GLOBAL_NAME_format_mask := null;
1912 l_LOCAL_NAME_format_mask := null;
1913 l_FULL_NAME_format_mask := null;
1914 l_ORDER_NAME_format_mask := null;
1915 --
1916 l_format_name := p_format_name;
1917 if p_format_name is null then
1918 l_gen_all_per_cols := 'Y';
1919 else
1920 l_gen_all_per_cols := 'N';
1921 end if;
1925 -- -------------------------------------------------------------------+
1922 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
1923 -- -------------------------------------------------------------------+
1924 -- Derive FULL_NAME
1926 if l_format_name = g_FULL_NAME or l_gen_all_per_cols = 'Y' then
1927 --
1928 open csr_get_format_mask(g_FULL_NAME, l_legislation_code,'L');
1929 fetch csr_get_format_mask into l_FULL_NAME_format_mask;
1930 if csr_get_format_mask%NOTFOUND then
1931 close csr_get_format_mask;
1932 --
1933 get_seeded_procedure_name
1934 (p_format_name => g_FULL_NAME
1935 ,p_legislation_code => l_legislation_code
1936 ,p_package_name => l_pkg_full_name
1937 ,p_procedure_name => l_proc_full_name);
1938 --
1939 if l_pkg_full_name is null then -- use seeded procedure?
1940 --
1941 -- seeded procedure does not exist, use seeded format
1942 --
1943 open csr_get_format_mask(g_FULL_NAME, null,'L');
1944 fetch csr_get_format_mask into l_FULL_NAME_format_mask;
1945 if csr_get_format_mask%NOTFOUND then
1946 l_FULL_NAME_format_mask := null;
1947 end if;
1948 close csr_get_format_mask;
1949 end if;
1950 else
1951 close csr_get_format_mask;
1952 end if;
1953 -- -------------------------------------------------------------------+
1954 -- DERIVE the name
1955 -- -------------------------------------------------------------------+
1956 if l_FULL_NAME_format_mask is not null then
1957 --
1958 -- replace all tokens in format mask using name column values
1959 --
1960 l_full_name_formatted := l_FULL_NAME_format_mask;
1961 get_formatted_name(p_name_values => l_person_names_rec
1962 ,p_formatted_name => l_full_name_formatted);
1963 --
1964 l_full_name_formatted := substr(l_full_name_formatted,1,240);
1965 --
1966 elsif l_pkg_full_name is not null then
1967 -- use seeded procedure to derive name
1968 derive_name_using_seeded_proc
1969 (p_format_name => g_FULL_NAME
1970 ,p_legislation_code => l_legislation_code
1971 ,p_name_column_rec => l_person_names_rec
1972 ,p_package_name => l_pkg_full_name
1973 ,p_procedure_name => l_proc_full_name
1974 ,p_formatted_name => l_full_name_formatted);
1975 l_full_name_formatted := rtrim(l_full_name_formatted);
1976 end if;
1977 l_person_names_rec.full_name := l_full_name_formatted;
1978 end if;
1979 -- -------------------------------------------------------------------+
1980 -- Derive ORDER_NAME
1981 -- -------------------------------------------------------------------+
1982 if l_format_name = g_ORDER_NAME or l_gen_all_per_cols = 'Y' then
1983 open csr_get_format_mask(g_ORDER_NAME, l_legislation_code,'L');
1984 fetch csr_get_format_mask into l_ORDER_NAME_format_mask;
1985 if csr_get_format_mask%NOTFOUND then
1986 close csr_get_format_mask;
1987 --
1988 get_seeded_procedure_name
1989 (p_format_name => g_ORDER_NAME
1990 ,p_legislation_code => l_legislation_code
1991 ,p_package_name => l_pkg_order_name
1992 ,p_procedure_name => l_proc_order_name);
1993 --
1994 if l_pkg_order_name is null then -- use seeded procedure?
1995 --
1996 -- seeded procedure does not exist, use seeded format
1997 --
1998 open csr_get_format_mask(g_ORDER_NAME, null,'L');
1999 fetch csr_get_format_mask into l_ORDER_NAME_format_mask;
2000 if csr_get_format_mask%NOTFOUND then
2001 l_ORDER_NAME_format_mask := null;
2002 end if;
2003 close csr_get_format_mask;
2004 end if;
2005 else
2006 close csr_get_format_mask;
2007 end if;
2008 -- -------------------------------------------------------------------+
2009 -- Derive Order Name
2010 -- -------------------------------------------------------------------+
2011 if l_ORDER_NAME_format_mask is not null then
2012 --
2013 -- replace all tokens in format mask using name column values
2014 --
2015 l_order_name_formatted := l_ORDER_NAME_format_mask;
2016 get_formatted_name(p_name_values => l_person_names_rec
2017 ,p_formatted_name => l_order_name_formatted);
2018 --
2019 l_order_name_formatted := substr(l_order_name_formatted,1,240);
2020 --
2021 elsif l_pkg_order_name is not null then
2022 -- use seeded procedure to derive name
2023 derive_name_using_seeded_proc
2024 (p_format_name => g_ORDER_NAME
2025 ,p_legislation_code => l_legislation_code
2026 ,p_name_column_rec => l_person_names_rec
2027 ,p_package_name => l_pkg_order_name
2028 ,p_procedure_name => l_proc_order_name
2029 ,p_formatted_name => l_order_name_formatted);
2030 l_order_name_formatted := rtrim(l_order_name_formatted);
2031 end if;
2032 end if;
2033 -- -------------------------------------------------------------------+
2034 -- -------------------------------------------------------------------+
2035 if l_format_name in (g_DISPLAY_NAME, g_LIST_NAME)
2036 or l_gen_all_per_cols = 'Y' then
2037 --
2038 if l_gen_all_per_cols = 'Y' then
2042 -- Get Global format mask
2039 l_format_name := g_LIST_NAME;
2040 end if;
2041 --
2043 --
2044 open csr_get_format_mask(l_format_name, l_legislation_code,'G');
2045 fetch csr_get_format_mask into l_GLOBAL_NAME_format_mask;
2046 if csr_get_format_mask%NOTFOUND then
2047 close csr_get_format_mask;
2048 -- look for non legislation specific
2049 open csr_get_format_mask(l_format_name, null,'G');
2050 fetch csr_get_format_mask into l_GLOBAL_NAME_format_mask;
2051 if csr_get_format_mask%NOTFOUND then
2052 l_GLOBAL_NAME_format_mask := null;
2053 end if;
2054 close csr_get_format_mask;
2055 else
2056 close csr_get_format_mask;
2057 end if;
2058 --
2059 -- Get Local format mask
2060 --
2061 open csr_get_format_mask(l_format_name, l_legislation_code,'L');
2062 fetch csr_get_format_mask into l_LOCAL_NAME_format_mask;
2063 if csr_get_format_mask%NOTFOUND then
2064 close csr_get_format_mask;
2065 -- look for non legislation specific
2066 open csr_get_format_mask(l_format_name, null,'L');
2067 fetch csr_get_format_mask into l_LOCAL_NAME_format_mask;
2068 if csr_get_format_mask%NOTFOUND then
2069 l_LOCAL_NAME_format_mask := null;
2070 end if;
2071 close csr_get_format_mask;
2072 else
2073 close csr_get_format_mask;
2074 end if;
2075 -- -------------------------------------------------------------------+
2076 -- Derive Global and Local Names
2077 -- -------------------------------------------------------------------+
2078 if l_GLOBAL_NAME_format_mask is not null then
2079 --
2080 -- replace all tokens in format mask using name column values
2081 --
2082 l_global_name_formatted := l_GLOBAL_NAME_format_mask;
2083 get_formatted_name(p_name_values => l_person_names_rec
2084 ,p_formatted_name => l_global_name_formatted);
2085 --
2086 l_global_name_formatted := substr(l_global_name_formatted,1,240);
2087 --
2088 end if;
2089 -- -------------------------------------------------------------------+
2090 if l_LOCAL_NAME_format_mask is not null then
2091 --
2092 -- replace all tokens in format mask using name column values
2093 --
2094 l_local_name_formatted := l_LOCAL_NAME_format_mask;
2095 get_formatted_name(p_name_values => l_person_names_rec
2096 ,p_formatted_name => l_local_name_formatted);
2097 --
2098 l_local_name_formatted := substr(l_local_name_formatted,1,240);
2099 --
2100 end if;
2101 --
2102 end if; -- Display/List Names
2103 -- -------------------------------------------------------------------+
2104 --
2105 -- Set OUT parameters
2106 --
2107 p_full_name := l_full_name_formatted;
2108 p_order_name := l_order_name_formatted;
2109 p_global_name := l_global_name_formatted;
2110 p_local_name := l_local_name_formatted;
2111 end derive_person_names;
2112 --
2113 end hr_person_name;
2114 --