[Home] [Help]
PACKAGE BODY: APPS.HR_NL_UTILITY
Source
1 PACKAGE BODY HR_NL_UTILITY AS
2 /* $Header: hrnlutil.pkb 115.9 2003/11/18 22:34:52 vigarg noship $ */
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 BEGIN
51 l_title:=hr_general.decode_lookup('TITLE', p_title);
52 l_acad_title:=hr_general.decode_lookup('HR_NL_ACADEMIC_TITLE',
53 p_per_information10);
54 l_sub_acad_title:=hr_general.decode_lookup('HR_NL_SUB_ACADEMIC_TITLE',
55 p_per_information3);
56 if p_per_information4 is null then
57 select p_last_name || ' '
58 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
59 || decode (p_per_information1,'','',p_per_information1 || ' ')
60 || decode (p_per_information5,'','',p_per_information5 || ' ')
61 || p_per_information6
62 into l_full_name
63 from dual;
64 elsif p_per_information4 = 'FORMAT1' then
65 select decode (p_per_information1,'','',p_per_information1 || ' ')
66 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
67 || p_last_name
68 into l_full_name
69 from dual;
70 elsif p_per_information4 = 'FORMAT2' then
71 if (p_pre_name_adjunct is not null or p_last_name is not null) then
72 select decode (p_per_information1,'','',p_per_information1 || ' ')
73 || decode (p_per_information5,'','',p_per_information5 || ' ')
74 || decode (p_per_information6,'','',p_per_information6)
75 || '-'
76 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
77 || p_last_name
78 into l_full_name
79 from dual;
80 else
81 select decode (p_per_information1,'','',p_per_information1 || ' ')
82 || decode (p_per_information5,'','',p_per_information5 || ' ')
83 || decode (p_per_information6,'','',p_per_information6 || ' ')
84 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
85 || p_last_name
86 into l_full_name
87 from dual;
88 end if;
89 elsif p_per_information4 = 'FORMAT3' then
90 if (p_per_information5 is not null or p_per_information6 is not null) then
91 select decode (p_per_information1,'','',p_per_information1 || ' ')
92 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
93 || p_last_name
94 || '-'
95 || decode (p_per_information5,'','',p_per_information5 || ' ')
96 || p_per_information6
97 into l_full_name
98 from dual;
99 else
100 select decode (p_per_information1,'','',p_per_information1 || ' ')
101 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
102 || p_last_name || ' '
103 || decode (p_per_information5,'','',p_per_information5 || ' ')
104 || p_per_information6
105 into l_full_name
106 from dual;
107 end if;
108 elsif p_per_information4 = 'FORMAT4' then
109 if (p_per_information1 is not null or p_per_information5 is not null or p_per_information6 is not null) then
110 select p_last_name || ' '
111 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct)
112 || ', '
113 || decode (p_per_information1,'','',p_per_information1 || ' ')
114 || decode (p_per_information5,'','',p_per_information5 || ' ')
115 || p_per_information6
116 into l_full_name
117 from dual;
118 else
119 select p_last_name || ' '
120 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
121 || decode (p_per_information1,'','',p_per_information1 || ' ')
122 || decode (p_per_information5,'','',p_per_information5 || ' ')
123 || p_per_information6
124 into l_full_name
125 from dual;
126 end if;
127 elsif p_per_information4 = 'FORMAT5' then
128 select decode (p_per_information1,'','',p_per_information1 || ' ')
129 || decode (p_per_information5,'','',p_per_information5 || ' ')
130 || p_per_information6
131 into l_full_name
132 from dual;
133 elsif p_per_information4 = 'FORMAT6' then
134 if (p_per_information1 is not null or p_pre_name_adjunct is not null or p_last_name is not null) then
135 select decode (p_per_information6,'','',p_per_information6 || ' ')
136 || decode (p_per_information5,'','',p_per_information5)
137 || ', '
138 || decode (p_per_information1,'','',p_per_information1 || ' ')
139 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
140 || p_last_name
141 into l_full_name
142 from dual;
143 else
144 select decode (p_per_information6,'','',p_per_information6 || ' ')
145 || decode (p_per_information5,'','',p_per_information5 || ' ')
146 || decode (p_per_information1,'','',p_per_information1 || ' ')
147 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
148 || p_last_name
149 into l_full_name
150 from dual;
151 end if;
152 elsif p_per_information4 = 'FORMAT7' then
153 select p_last_name || ' '
154 || decode (p_first_name,'','',p_first_name|| ' ')
155 || p_pre_name_adjunct
156 into l_full_name
157 from dual;
158 elsif p_per_information4 = 'FORMAT8' then
159 select p_last_name || ' '
160 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct|| ' ')
161 || p_first_name
162 into l_full_name
163 from dual;
164 elsif p_per_information4 = 'FORMAT9' then
165 select decode (p_first_name,'','',p_first_name|| ' ')
166 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct|| ' ')
167 || p_last_name
168 into l_full_name
169 from dual;
170 elsif p_per_information4 = 'FORMAT10' then
171 if (p_pre_name_adjunct is not null or p_last_name is not null) then
172 select decode (p_first_name,'','',p_first_name|| ' ')
173 || decode (p_per_information5,'','',p_per_information5 || ' ')
174 || decode (p_per_information6,'','',p_per_information6)
175 || '-'
176 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
177 || p_last_name
178 into l_full_name
179 from dual;
180 else
181 select decode (p_first_name,'','',p_first_name|| ' ')
182 || decode (p_per_information5,'','',p_per_information5 || ' ')
183 || decode (p_per_information6,'','',p_per_information6 || ' ')
184 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
185 || p_last_name
186 into l_full_name
187 from dual;
188 end if;
189 elsif p_per_information4 = 'FORMAT11' then
190 select p_last_name || ' '
191 || decode (l_title,'','',l_title|| ' ')
192 || p_per_information1
193 into l_full_name
194 from dual;
195 elsif p_per_information4 = 'FORMAT12' then
196 select p_last_name || ' '
197 || decode (l_title,'','',l_title|| ' ')
198 || decode (p_per_information1,'','',p_per_information1 || ' ')
199 || p_first_name
200 into l_full_name
201 from dual;
202 elsif p_per_information4 = 'FORMAT13' then
203 select p_last_name || ' '
204 || decode (l_title,'','',l_title|| ' ')
205 || decode (p_per_information1,'','',p_per_information1 || ' ')
206 || p_known_as
207 into l_full_name
208 from dual;
209 elsif p_per_information4 = 'FORMAT14' then
210 select p_last_name || ' '
211 || decode (p_per_information1,'','',p_per_information1 || ' ')
212 || p_pre_name_adjunct
213 into l_full_name
214 from dual;
215 elsif p_per_information4 = 'FORMAT15' then
216 select decode (l_acad_title,'','',l_acad_title|| ' ')
217 || decode (p_per_information1,'','',p_per_information1 || ' ')
218 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
219 || p_last_name || ' '
220 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
221 into l_full_name
222 from dual;
223 elsif p_per_information4 = 'FORMAT16' then
224 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
225 select decode (l_acad_title,'','',l_acad_title|| ' ')
226 || decode (p_per_information1,'','',p_per_information1 || ' ')
227 || decode (p_per_information5,'','',p_per_information5 || ' ')
228 || decode (p_per_information6,'','',p_per_information6)
229 || '-'
230 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
231 || p_last_name || ' '
232 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
233 into l_full_name
234 from dual;
235 else
236 select decode (l_acad_title,'','',l_acad_title|| ' ')
237 || decode (p_per_information1,'','',p_per_information1 || ' ')
238 || decode (p_per_information5,'','',p_per_information5 || ' ')
239 || decode (p_per_information6,'','',p_per_information6 || ' ')
240 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
241 || p_last_name || ' '
242 || decode (l_sub_acad_title,'','',l_sub_acad_title|| ' ')
243 into l_full_name
244 from dual;
245 end if;
246 elsif p_per_information4 = 'FORMAT17' then
247 if ((p_pre_name_adjunct is not null or p_last_name is not null) and (p_per_information1 is not null or p_per_information5 is not null)) then
248 select decode (p_per_information6,'','',p_per_information6)
249 || '-'
250 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
251 || p_last_name || ', '
252 || decode (p_per_information1,'','',p_per_information1 || ' ')
253 || decode (p_per_information5,'','',p_per_information5 || ' ')
254 into l_full_name
255 from dual;
256 elsif ((p_pre_name_adjunct is not null or p_last_name is not null) and (p_per_information1 is null and p_per_information5 is null)) then
257 select decode (p_per_information6,'','',p_per_information6)
258 || '-'
259 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
260 || p_last_name || ''
261 || decode (p_per_information1,'','',p_per_information1 || ' ')
262 || decode (p_per_information5,'','',p_per_information5 || ' ')
263 into l_full_name
264 from dual;
265 elsif ((p_pre_name_adjunct is null and p_last_name is null) and (p_per_information1 is not null or p_per_information5 is not null)) then
266 select decode (p_per_information6,'','',p_per_information6)
267 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct)
268 || decode (p_last_name, '','',p_last_name)
269 || ', '
270 || decode (p_per_information1,'','', ', ' || p_per_information1 || ' ')
271 || decode (p_per_information5,'','',p_per_information5 || ' ')
272 into l_full_name
273 from dual;
274 elsif (p_pre_name_adjunct is null and p_last_name is null and p_per_information1 is null and p_per_information5 is null) then
275 select decode (p_per_information6,'','',p_per_information6)
276 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
277 || decode (p_last_name, '','',p_last_name || ', ')
278 || decode (p_per_information1,'','',p_per_information1 || ' ')
279 || decode (p_per_information5,'','',p_per_information5 || ' ')
280 into l_full_name
281 from dual;
282 end if;
283 elsif p_per_information4 = 'FORMAT18' then
284 if ((p_per_information5 is not null or p_per_information6 is not null) and (p_per_information1 is not null or p_pre_name_adjunct is not null)) then
285 select p_last_name || '-'
286 || decode (p_per_information5,'','',p_per_information5 || ' ')
287 || decode (p_per_information6,'','',p_per_information6)
288 || ', '
289 || decode (p_per_information1,'','',p_per_information1 || ' ')
290 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
291 into l_full_name
292 from dual;
293 elsif((p_per_information5 is not null or p_per_information6 is not null) and (p_per_information1 is null and p_pre_name_adjunct is null)) then
294 select p_last_name || '-'
295 || decode (p_per_information5,'','',p_per_information5 || ' ')
296 || decode (p_per_information6,'','',p_per_information6 || ' ')
297 || decode (p_per_information1,'','',p_per_information1 || ' ')
298 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
299 into l_full_name
300 from dual;
301 elsif((p_per_information5 is null and p_per_information6 is null) and (p_per_information1 is not null or p_pre_name_adjunct is not null)) then
302 select p_last_name || ''
303 || decode (p_per_information5,'','',p_per_information5 || ' ')
304 || decode (p_per_information6,'','',p_per_information6)
305 || ', '
306 || decode (p_per_information1,'','',p_per_information1 || ' ')
307 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
308 into l_full_name
309 from dual;
310 elsif(p_per_information5 is null and p_per_information6 is null and p_per_information1 is null and p_pre_name_adjunct is null) then
311 select p_last_name || ''
312 || decode (p_per_information5,'','',p_per_information5 || ' ')
313 || decode (p_per_information6,'','',p_per_information6 || ', ')
314 || decode (p_per_information1,'','',p_per_information1 || ' ')
315 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
316 into l_full_name
317 from dual;
318 end if;
319 elsif p_per_information4 = 'FORMAT19' then
320 if (p_per_information1 is not null or p_per_information5 is not null) then
321 select decode (p_per_information6,'','',p_per_information6)
322 || ', '
323 || decode (p_per_information1,'','',p_per_information1 || ' ')
324 || decode (p_per_information5,'','',p_per_information5 || ' ')
325 into l_full_name
326 from dual;
327 else
328 select decode (p_per_information6,'','',p_per_information6|| ' ')
329 || decode (p_per_information1,'','',p_per_information1 || ' ')
330 || decode (p_per_information5,'','',p_per_information5 || ' ')
331 into l_full_name
332 from dual;
333 end if;
334 elsif p_per_information4 = 'FORMAT20' then
335 select decode (l_acad_title,'','',l_acad_title|| ' ')
336 || decode (l_title,'','',l_title|| ' ')
337 || decode (p_per_information1,'','',p_per_information1 || ' ')
338 || decode (p_per_information5,'','',p_per_information5 || ' ')
339 || decode (p_per_information6,'','',p_per_information6 || ' ')
340 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
341 into l_full_name
342 from dual;
343 elsif p_per_information4 = 'FORMAT21' then
344 select decode (l_acad_title,'','',l_acad_title|| ' ')
345 || decode (l_title,'','',l_title|| ' ')
346 || decode (p_per_information1,'','',p_per_information1 || ' ')
347 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
348 || p_last_name || ' '
349 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
350 into l_full_name
351 from dual;
352 elsif p_per_information4 = 'FORMAT22' then
353 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
354 select decode (l_acad_title,'','',l_acad_title|| ' ')
355 || decode (l_title,'','',l_title|| ' ')
356 || decode (p_per_information1,'','',p_per_information1 || ' ')
357 || decode (p_per_information5,'','',p_per_information5 || ' ')
358 || decode (p_per_information6,'','',p_per_information6)
359 || '-'
360 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
361 || p_last_name || ' '
362 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
363 into l_full_name
364 from dual;
365 else
366 select decode (l_acad_title,'','',l_acad_title|| ' ')
367 || decode (l_title,'','',l_title|| ' ')
368 || decode (p_per_information1,'','',p_per_information1 || ' ')
369 || decode (p_per_information5,'','',p_per_information5 || ' ')
370 || decode (p_per_information6,'','',p_per_information6 || ' ')
371 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
372 || p_last_name || ' '
373 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
374 into l_full_name
375 from dual;
376 end if;
377 elsif p_per_information4 = 'FORMAT23' then
378 select decode (l_acad_title,'','',l_acad_title|| ' ')
379 || decode (l_title,'','',l_title|| ' ')
380 || decode (p_first_name,'','',p_first_name|| ' ')
381 || decode (p_per_information5,'','',p_per_information5 || ' ')
382 || decode (p_per_information6,'','',p_per_information6 || ' ')
383 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
384 into l_full_name
385 from dual;
386 elsif p_per_information4 = 'FORMAT24' then
387 select decode (l_acad_title,'','',l_acad_title|| ' ')
388 || decode (l_title,'','',l_title|| ' ')
389 || decode (p_first_name,'','',p_first_name|| ' ')
390 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
391 || p_last_name || ' '
392 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
393 into l_full_name
394 from dual;
395 elsif p_per_information4 = 'FORMAT25' then
396 if (p_pre_name_adjunct is not null or p_last_name is not null or l_sub_acad_title is not null) then
397 select decode (l_acad_title,'','',l_acad_title|| ' ')
398 || decode (l_title,'','',l_title|| ' ')
399 || decode (p_first_name,'','',p_first_name|| ' ')
400 || decode (p_per_information5,'','',p_per_information5 || ' ')
401 || decode (p_per_information6,'','',p_per_information6)
402 || '-'
403 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
404 || p_last_name || ' '
405 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
406 into l_full_name
407 from dual;
408 else
409 select decode (l_acad_title,'','',l_acad_title|| ' ')
410 || decode (l_title,'','',l_title|| ' ')
411 || decode (p_first_name,'','',p_first_name|| ' ')
412 || decode (p_per_information5,'','',p_per_information5 || ' ')
413 || decode (p_per_information6,'','',p_per_information6 || ' ')
414 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
415 || p_last_name || ' '
416 || decode (l_sub_acad_title,'','',l_sub_acad_title || ' ')
417 into l_full_name
418 from dual;
419 end if;
420 else
421 select p_last_name || ' '
422 || decode (p_pre_name_adjunct,'','',p_pre_name_adjunct || ' ')
423 || decode (p_per_information1,'','',p_per_information1 || ' ')
424 || decode (p_per_information5,'','',p_per_information5 || ' ')
425 || p_per_information6
426 into l_full_name
427 from dual;
428 end if;
429 return rtrim(l_full_name);
430 END;
431 --
432 END HR_NL_UTILITY;