[Home] [Help]
PACKAGE BODY: APPS.HR_NL_UTILITY
Source
1 PACKAGE BODY HR_NL_UTILITY AS
2 /* $Header: hrnlutil.pkb 120.0.12010000.5 2010/03/17 11:12:33 knadhan ship $ */
3 --
4 FUNCTION per_nl_full_name(
5 p_first_name in varchar2
6 ,p_middle_names in varchar2
7 ,p_last_name in varchar2
8 ,p_known_as in varchar2
9 ,p_title in varchar2
10 ,p_suffix in varchar2
11 ,p_pre_name_adjunct in varchar2
12 ,p_per_information1 in varchar2
13 ,p_per_information2 in varchar2
14 ,p_per_information3 in varchar2
15 ,p_per_information4 in varchar2
16 ,p_per_information5 in varchar2
17 ,p_per_information6 in varchar2
18 ,p_per_information7 in varchar2
19 ,p_per_information8 in varchar2
20 ,p_per_information9 in varchar2
21 ,p_per_information10 in varchar2
22 ,p_per_information11 in varchar2
23 ,p_per_information12 in varchar2
24 ,p_per_information13 in varchar2
25 ,p_per_information14 in varchar2
26 ,p_per_information15 in varchar2
27 ,p_per_information16 in varchar2
28 ,p_per_information17 in varchar2
29 ,p_per_information18 in varchar2
30 ,p_per_information19 in varchar2
31 ,p_per_information20 in varchar2
32 ,p_per_information21 in varchar2
33 ,p_per_information22 in varchar2
34 ,p_per_information23 in varchar2
35 ,p_per_information24 in varchar2
36 ,p_per_information25 in varchar2
37 ,p_per_information26 in varchar2
38 ,p_per_information27 in varchar2
39 ,p_per_information28 in varchar2
40 ,p_per_information29 in varchar2
41 ,p_per_information30 in varchar2
42 )
43 RETURN VARCHAR2 IS
44 --
45 l_full_name varchar2(240);
46 l_title varchar2(30);
47 l_acad_title varchar2(80);
48 l_sub_acad_title varchar2(80);
49 --
50 /* 6891179 */
51 CURSOR cur_effective_date is
52 SELECT se.effective_date effective_date
53 FROM fnd_sessions se
54 WHERE se.session_id = USERENV('sessionid');
55
56 --
57 CURSOR cur_contact_person_id(c_effective_date date)
58 is
59 SELECT pcr.contact_person_id contact_person_id
60 FROM per_contact_relationships pcr
61 WHERE pcr.person_id=per_per_shd.g_old_rec.person_id
62 AND pcr.contact_type='S'
63 AND c_effective_date between nvl(pcr.date_start,to_date('01/01/0001','dd/mm/yyyy')) and nvl(pcr.date_end,to_date('31/12/4712','dd/mm/yyyy'))
64 ;
65
66 --
67 CURSOR cur_contact_person_details(c_person_id per_all_people_f.person_id%type,c_effective_date date)
68 is
69 SELECT papf.pre_name_adjunct prefix
70 ,papf.last_name last_name
71 FROM per_all_people_f papf
72 WHERE papf.person_id=c_person_id
73 AND c_effective_date between papf.effective_start_date and papf.effective_end_date
74 AND papf.person_type_id IN (
75 SELECT ppt.person_type_id
76 FROM per_person_types ppt
77 WHERE ppt.business_group_id=papf.business_group_id
78 AND ppt.seeded_person_type_key='CONTACT') ;
79
80 --
81 l_effective_date date;
82 l_contact_person_details cur_contact_person_details%ROWTYPE;
83 l_per_information5 varchar2(150);
84 l_per_information6 varchar2(150);
85 l_person_id per_all_people_f.person_id%type;
86
87 --
88 BEGIN
89
90 /* 6891179 */
91
92 hr_utility.set_location(' entered per_nl_full_name',10);
93 hr_utility.set_location(' session id :='||USERENV('sessionid'),10);
94 hr_utility.set_location(' hr_util_misc_ss.g_eff_date'||hr_util_misc_ss.g_eff_date,10000);
95
96 /* 8680292 */
97 --
98 -- hr_util_misc_ss.setEffectiveDate(hr_util_misc_ss.g_eff_date);
99 --
100
101 hr_utility.set_location(' hr_util_misc_ss.g_eff_date'||hr_util_misc_ss.g_eff_date,10);
102 hr_utility.set_location('p_per_information5 '||p_per_information5,10);
103 hr_utility.set_location(' p_per_information6 '||p_per_information6,10);
104 hr_utility.set_location(' per_per_shd.g_old_rec.person_id'||per_per_shd.g_old_rec.person_id,20);
105
106 hr_utility.set_location('Before cur_effective_date cursor ',30);
107
108 /* 8680292 */
109 /* Replaces l_effective_date by hr_util_misc_ss.g_eff_date
110 --
111 OPEN cur_effective_date;
112 FETCH cur_effective_date INTO l_effective_date;
113 CLOSE cur_effective_date;
114 hr_utility.set_location(' l_effective_Date '||l_effective_date,30);
115 */
116 --
117 open cur_contact_person_id(hr_util_misc_ss.g_eff_date);
118 fetch cur_contact_person_id into l_person_id;
119 close cur_contact_person_id;
120
121 --
122 hr_utility.set_location('l_person_id '||l_person_id,40);
123 -- hr_utility.set_location('After cur_effective_date cursor ',40);
124 -- hr_utility.set_location('l_effective_date '||l_effective_date,50);
125 hr_utility.set_location('Before cur_contact_person_details ',60);
126
127 --
128 OPEN cur_contact_person_details(l_person_id,hr_util_misc_ss.g_eff_date);
129 FETCH cur_contact_person_details INTO l_contact_person_details;
130
131 hr_utility.set_location(' contact person prename adjunct '||l_contact_person_details.prefix ,70);
132 hr_utility.set_location('contact person last name '|| l_contact_person_details.last_name,70);
133
134
135 /* the l_per_information5,l_per_information6 stores either dynamically retrieved last name , prename adjucnt(if spouse exist)
136 or the p_per_information5,p_per_information6 tat are passed to procedure */
137 IF(cur_contact_person_details%FOUND) THEN
138 l_per_information5:=l_contact_person_details.prefix;
139 l_per_information6:=l_contact_person_details.last_name;
140 hr_utility.set_location(' entered if condtion',80);
141 ELSE
142 l_per_information5:= p_per_information5;
143 l_per_information6:= p_per_information6;
144 hr_utility.set_location(' entered else condtion',90);
145 END IF;
146 CLOSE cur_contact_person_details;
147
148 --
149 l_title:=hr_general.decode_lookup('TITLE', p_title);
150 l_acad_title:=hr_general.decode_lookup('HR_NL_ACADEMIC_TITLE',
151 p_per_information10);
152 l_sub_acad_title:=hr_general.decode_lookup('HR_NL_SUB_ACADEMIC_TITLE',
153 p_per_information3);
154 if p_per_information4 is null then
155 select p_last_name || ' '
156 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
157 || decode (p_per_information1,'','',p_per_information1 || ' ')
158 || decode (l_per_information5,'','',l_per_information5 || ' ')
159 || l_per_information6
160 into l_full_name
161 from dual;
162 elsif p_per_information4 = 'FORMAT1' then
163 select decode (p_per_information1,'','',p_per_information1 || ' ')
164 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
165 || p_last_name
166 into l_full_name
167 from dual;
168 elsif p_per_information4 = 'FORMAT2' then
169 if (p_pre_name_adjunct is not null or p_last_name is not null) then
170 select decode (p_per_information1,'','',p_per_information1 || ' ')
171 || decode (l_per_information5,'','',l_per_information5 || ' ')
172 || decode (l_per_information6,'','',l_per_information6)
173 || '-'
174 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
175 || p_last_name
176 into l_full_name
177 from dual;
178 else
179 select decode (p_per_information1,'','',p_per_information1 || ' ')
180 || decode (l_per_information5,'','',l_per_information5 || ' ')
181 || decode (l_per_information6,'','',l_per_information6 || ' ')
182 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
183 || p_last_name
184 into l_full_name
185 from dual;
186 end if;
187 elsif p_per_information4 = 'FORMAT3' then
188 if (l_per_information5 is not null or l_per_information6 is not null) then
189 select decode (p_per_information1,'','',p_per_information1 || ' ')
190 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
191 || p_last_name
192 || '-'
193 || decode (l_per_information5,'','',l_per_information5 || ' ')
194 || l_per_information6
195 into l_full_name
196 from dual;
197 else
198 select decode (p_per_information1,'','',p_per_information1 || ' ')
199 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
200 || p_last_name || ' '
201 || decode (l_per_information5,'','',l_per_information5 || ' ')
202 || l_per_information6
203 into l_full_name
204 from dual;
205 end if;
206 elsif p_per_information4 = 'FORMAT4' then
207 if (p_per_information1 is not null or l_per_information5 is not null or l_per_information6 is not null) then
208 select p_last_name || ' '
209 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct)
210 || ', '
211 || decode (p_per_information1,'','',p_per_information1 || ' ')
212 || decode (l_per_information5,'','',l_per_information5 || ' ')
213 || l_per_information6
214 into l_full_name
215 from dual;
216 else
217 select p_last_name || ' '
218 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
219 || decode (p_per_information1,'','',p_per_information1 || ' ')
220 || decode (l_per_information5,'','',l_per_information5 || ' ')
221 || l_per_information6
222 into l_full_name
223 from dual;
224 end if;
225 elsif p_per_information4 = 'FORMAT5' then
226 select decode (p_per_information1,'','',p_per_information1 || ' ')
227 || decode (l_per_information5,'','',l_per_information5 || ' ')
228 || l_per_information6
229 into l_full_name
230 from dual;
231 elsif p_per_information4 = 'FORMAT6' then
232 if (p_per_information1 is not null or p_pre_name_adjunct is not null or p_last_name is not null) then
233 select decode (l_per_information6,'','',l_per_information6 || ' ')
234 || decode (l_per_information5,'','',l_per_information5)
235 || ', '
236 || decode (p_per_information1,'','',p_per_information1 || ' ')
237 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
238 || p_last_name
239 into l_full_name
240 from dual;
241 else
242 select decode (l_per_information6,'','',l_per_information6 || ' ')
243 || decode (l_per_information5,'','',l_per_information5 || ' ')
244 || decode (p_per_information1,'','',p_per_information1 || ' ')
245 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
246 || p_last_name
247 into l_full_name
248 from dual;
249 end if;
250 elsif p_per_information4 = 'FORMAT7' then
251 select p_last_name || ' '
252 || decode (p_first_name,'','',p_first_name|| ' ')
253 || p_pre_name_adjunct
254 into l_full_name
255 from dual;
256 elsif p_per_information4 = 'FORMAT8' then
257 select p_last_name || ' '
258 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct|| ' ')
259 || p_first_name
260 into l_full_name
261 from dual;
262 elsif p_per_information4 = 'FORMAT9' then
263 select decode (p_first_name,'','',p_first_name|| ' ')
264 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct|| ' ')
265 || p_last_name
266 into l_full_name
267 from dual;
268 elsif p_per_information4 = 'FORMAT10' then
269 if (p_pre_name_adjunct is not null or p_last_name is not null) then
270 select decode (p_first_name,'','',p_first_name|| ' ')
271 || decode (l_per_information5,'','',l_per_information5 || ' ')
272 || decode (l_per_information6,'','',l_per_information6)
273 || '-'
274 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
275 || p_last_name
276 into l_full_name
277 from dual;
278 else
279 select decode (p_first_name,'','',p_first_name|| ' ')
280 || decode (l_per_information5,'','',l_per_information5 || ' ')
281 || decode (l_per_information6,'','',l_per_information6 || ' ')
282 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
283 || p_last_name
284 into l_full_name
285 from dual;
286 end if;
287 elsif p_per_information4 = 'FORMAT11' then
288 select p_last_name || ' '
289 || decode (l_title,'','',l_title|| ' ')
290 || p_per_information1
291 into l_full_name
292 from dual;
293 elsif p_per_information4 = 'FORMAT12' then
294 select p_last_name || ' '
295 || decode (l_title,'','',l_title|| ' ')
296 || decode (p_per_information1,'','',p_per_information1 || ' ')
297 || p_first_name
298 into l_full_name
299 from dual;
300 elsif p_per_information4 = 'FORMAT13' then
301 select p_last_name || ' '
302 || decode (l_title,'','',l_title|| ' ')
303 || decode (p_per_information1,'','',p_per_information1 || ' ')
304 || p_known_as
305 into l_full_name
306 from dual;
307 elsif p_per_information4 = 'FORMAT14' then
308 select p_last_name || ' '
309 || decode (p_per_information1,'','',p_per_information1 || ' ')
310 || p_pre_name_adjunct
311 into l_full_name
312 from dual;
313 elsif p_per_information4 = 'FORMAT15' then
314 select decode (l_acad_title,'','',l_acad_title|| ' ')
315 || decode (p_per_information1,'','',p_per_information1 || ' ')
316 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
317 || p_last_name || ' '
318 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
319 into l_full_name
320 from dual;
321 elsif p_per_information4 = 'FORMAT16' then
322 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
323 select decode (l_acad_title,'','',l_acad_title|| ' ')
324 || decode (p_per_information1,'','',p_per_information1 || ' ')
325 || decode (l_per_information5,'','',l_per_information5 || ' ')
326 || decode (l_per_information6,'','',l_per_information6)
327 || '-'
328 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
329 || p_last_name || ' '
330 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
331 into l_full_name
332 from dual;
333 else
334 select decode (l_acad_title,'','',l_acad_title|| ' ')
335 || decode (p_per_information1,'','',p_per_information1 || ' ')
336 || decode (l_per_information5,'','',l_per_information5 || ' ')
337 || decode (l_per_information6,'','',l_per_information6 || ' ')
341 into l_full_name
338 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
339 || p_last_name || ' '
340 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
342 from dual;
343 end if;
344 elsif p_per_information4 = 'FORMAT17' then
345 if ((p_pre_name_adjunct is not null or p_last_name is not null) and (p_per_information1 is not null or l_per_information5 is not null)) then
346 select decode (l_per_information6,'','',l_per_information6)
347 || '-'
348 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
349 || p_last_name || ', '
350 || decode (p_per_information1,'','',p_per_information1 || ' ')
351 || decode (l_per_information5,'','',l_per_information5 || ' ')
352 into l_full_name
353 from dual;
354 elsif ((p_pre_name_adjunct is not null or p_last_name is not null) and (p_per_information1 is null and l_per_information5 is null)) then
355 select decode (l_per_information6,'','',l_per_information6)
356 || '-'
357 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
358 || p_last_name || ''
359 || decode (p_per_information1,'','',p_per_information1 || ' ')
360 || decode (l_per_information5,'','',l_per_information5 || ' ')
361 into l_full_name
362 from dual;
363 elsif ((p_pre_name_adjunct is null and p_last_name is null) and (p_per_information1 is not null or l_per_information5 is not null)) then
364 select decode (l_per_information6,'','',l_per_information6)
365 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct)
366 || decode (p_last_name, '','',p_last_name)
367 || ', '
368 || decode (p_per_information1,'','', ', ' || p_per_information1 || ' ')
369 || decode (l_per_information5,'','',l_per_information5 || ' ')
370 into l_full_name
371 from dual;
372 elsif (p_pre_name_adjunct is null and p_last_name is null and p_per_information1 is null and l_per_information5 is null) then
373 select decode (l_per_information6,'','',l_per_information6)
374 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
375 || decode (p_last_name, '','',p_last_name || ', ')
376 || decode (p_per_information1,'','',p_per_information1 || ' ')
377 || decode (l_per_information5,'','',l_per_information5 || ' ')
378 into l_full_name
379 from dual;
380 end if;
381 elsif p_per_information4 = 'FORMAT18' then
382 if ((l_per_information5 is not null or l_per_information6 is not null) and (p_per_information1 is not null or p_pre_name_adjunct is not null)) then
383 select p_last_name || '-'
384 || decode (l_per_information5,'','',l_per_information5 || ' ')
385 || decode (l_per_information6,'','',l_per_information6)
386 || ', '
387 || decode (p_per_information1,'','',p_per_information1 || ' ')
388 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
389 into l_full_name
390 from dual;
391 elsif((l_per_information5 is not null or l_per_information6 is not null) and (p_per_information1 is null and p_pre_name_adjunct is null)) then
392 select p_last_name || '-'
393 || decode (l_per_information5,'','',l_per_information5 || ' ')
394 || decode (l_per_information6,'','',l_per_information6 || ' ')
395 || decode (p_per_information1,'','',p_per_information1 || ' ')
396 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
397 into l_full_name
398 from dual;
399 elsif((l_per_information5 is null and l_per_information6 is null) and (p_per_information1 is not null or p_pre_name_adjunct is not null)) then
400 select p_last_name || ''
401 || decode (l_per_information5,'','',l_per_information5 || ' ')
402 || decode (l_per_information6,'','',l_per_information6)
403 || ', '
404 || decode (p_per_information1,'','',p_per_information1 || ' ')
405 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
406 into l_full_name
407 from dual;
408 elsif(l_per_information5 is null and l_per_information6 is null and p_per_information1 is null and p_pre_name_adjunct is null) then
409 select p_last_name || ''
410 || decode (l_per_information5,'','',l_per_information5 || ' ')
411 || decode (l_per_information6,'','',l_per_information6 || ', ')
412 || decode (p_per_information1,'','',p_per_information1 || ' ')
413 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
414 into l_full_name
415 from dual;
416 end if;
417 elsif p_per_information4 = 'FORMAT19' then
418 if (p_per_information1 is not null or l_per_information5 is not null) then
419 select decode (l_per_information6,'','',l_per_information6)
420 || ', '
421 || decode (p_per_information1,'','',p_per_information1 || ' ')
422 || decode (l_per_information5,'','',l_per_information5 || ' ')
423 into l_full_name
424 from dual;
425 else
426 select decode (l_per_information6,'','',l_per_information6|| ' ')
427 || decode (p_per_information1,'','',p_per_information1 || ' ')
428 || decode (l_per_information5,'','',l_per_information5 || ' ')
429 into l_full_name
430 from dual;
431 end if;
432 elsif p_per_information4 = 'FORMAT20' then
433 select decode (l_acad_title,'','',l_acad_title|| ' ')
434 || decode (l_title,'','',l_title|| ' ')
435 || decode (p_per_information1,'','',p_per_information1 || ' ')
436 || decode (l_per_information5,'','',l_per_information5 || ' ')
437 || decode (l_per_information6,'','',l_per_information6 || ' ')
438 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
439 into l_full_name
440 from dual;
441 elsif p_per_information4 = 'FORMAT21' then
442 select decode (l_acad_title,'','',l_acad_title|| ' ')
443 || decode (l_title,'','',l_title|| ' ')
444 || decode (p_per_information1,'','',p_per_information1 || ' ')
445 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
446 || p_last_name || ' '
447 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
448 into l_full_name
449 from dual;
450 elsif p_per_information4 = 'FORMAT22' then
451 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
452 select decode (l_acad_title,'','',l_acad_title|| ' ')
453 || decode (l_title,'','',l_title|| ' ')
454 || decode (p_per_information1,'','',p_per_information1 || ' ')
455 || decode (l_per_information5,'','',l_per_information5 || ' ')
456 || decode (l_per_information6,'','',l_per_information6)
457 || '-'
458 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
459 || p_last_name || ' '
460 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
461 into l_full_name
462 from dual;
463 else
464 select decode (l_acad_title,'','',l_acad_title|| ' ')
465 || decode (l_title,'','',l_title|| ' ')
466 || decode (p_per_information1,'','',p_per_information1 || ' ')
467 || decode (l_per_information5,'','',l_per_information5 || ' ')
468 || decode (l_per_information6,'','',l_per_information6 || ' ')
469 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
470 || p_last_name || ' '
471 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
472 into l_full_name
473 from dual;
474 end if;
475 elsif p_per_information4 = 'FORMAT23' then
476 select decode (l_acad_title,'','',l_acad_title|| ' ')
477 || decode (l_title,'','',l_title|| ' ')
478 || decode (p_first_name,'','',p_first_name|| ' ')
479 || decode (l_per_information5,'','',l_per_information5 || ' ')
480 || decode (l_per_information6,'','',l_per_information6 || ' ')
481 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
482 into l_full_name
483 from dual;
484 elsif p_per_information4 = 'FORMAT24' then
485 select decode (l_acad_title,'','',l_acad_title|| ' ')
486 || decode (l_title,'','',l_title|| ' ')
487 || decode (p_first_name,'','',p_first_name|| ' ')
488 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
489 || p_last_name || ' '
490 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
491 into l_full_name
492 from dual;
493 elsif p_per_information4 = 'FORMAT25' then
494 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
495 select decode (l_acad_title,'','',l_acad_title|| ' ')
496 || decode (l_title,'','',l_title|| ' ')
497 || decode (p_first_name,'','',p_first_name|| ' ')
498 || decode (l_per_information5,'','',l_per_information5 || ' ')
499 || decode (l_per_information6,'','',l_per_information6)
500 || '-'
501 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
502 || p_last_name || ' '
503 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
504 into l_full_name
505 from dual;
506 else
507 select decode (l_acad_title,'','',l_acad_title|| ' ')
508 || decode (l_title,'','',l_title|| ' ')
509 || decode (p_first_name,'','',p_first_name|| ' ')
510 || decode (l_per_information5,'','',l_per_information5 || ' ')
511 || decode (l_per_information6,'','',l_per_information6 || ' ')
512 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
513 || p_last_name || ' '
514 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
515 into l_full_name
516 from dual;
517 end if;
518 elsif p_per_information4 = 'FORMAT26' then
519 select p_last_name ||','|| ' ' /* 9346754QA */
520 || decode (p_per_information1,'','',p_per_information1 || ' ')
521 || p_pre_name_adjunct
522 into l_full_name
523 from dual;
524 else
525 select p_last_name || ' '
526 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
527 || decode (p_per_information1,'','',p_per_information1 || ' ')
528 || decode (l_per_information5,'','',l_per_information5 || ' ')
529 || l_per_information6
530 into l_full_name
531 from dual;
532 end if;
533 return rtrim(l_full_name);
534 END;
535 --
536 END HR_NL_UTILITY;