1 PACKAGE BODY hr_dk_utility AS
2 /* $Header: hrdkutil.pkb 120.4.12010000.2 2008/08/06 08:36:00 ubhat ship $ */
3 --
4 --
5 --
6 -- Function to Formate the Full Name for Denmark
7 --
8
9 FUNCTION per_dk_full_name(
10 p_first_name IN VARCHAR2
11 ,p_middle_name IN VARCHAR2
12 ,p_last_name IN VARCHAR2
13 ,p_known_as IN VARCHAR2
14 ,p_title IN VARCHAR2
15 ,p_suffix IN VARCHAR2
16 ,p_pre_name_adjunct IN VARCHAR2
17 ,p_per_information1 IN VARCHAR2
18 ,p_per_information2 IN VARCHAR2
19 ,p_per_information3 IN VARCHAR2
20 ,p_per_information4 IN VARCHAR2
21 ,p_per_information5 IN VARCHAR2
22 ,p_per_information6 IN VARCHAR2
23 ,p_per_information7 IN VARCHAR2
24 ,p_per_information8 IN VARCHAR2
25 ,p_per_information9 IN VARCHAR2
26 ,p_per_information10 IN VARCHAR2
27 ,p_per_information11 IN VARCHAR2
28 ,p_per_information12 IN VARCHAR2
29 ,p_per_information13 IN VARCHAR2
30 ,p_per_information14 IN VARCHAR2
31 ,p_per_information15 IN VARCHAR2
32 ,p_per_information16 IN VARCHAR2
33 ,p_per_information17 IN VARCHAR2
34 ,p_per_information18 IN VARCHAR2
35 ,p_per_information19 IN VARCHAR2
36 ,p_per_information20 IN VARCHAR2
37 ,p_per_information21 IN VARCHAR2
38 ,p_per_information22 IN VARCHAR2
39 ,p_per_information23 IN VARCHAR2
40 ,p_per_information24 IN VARCHAR2
41 ,p_per_information25 IN VARCHAR2
42 ,p_per_information26 IN VARCHAR2
43 ,p_per_information27 IN VARCHAR2
44 ,p_per_information28 IN VARCHAR2
45 ,p_per_information29 IN VARCHAR2
46 ,p_per_information30 IN VARCHAR2
47 ) RETURN VARCHAR2 is
48
49 --
50 --
51 --
52 -- Local Variable
53 --
54 l_full_name VARCHAR2(240);
55 --
56 --
57
58 BEGIN
59 --
60 --
61 -- Construct the full name which has the following format:
62 --
63 -- <last name><,> <middle name> <first name> <initials>
64 --
65 -- Modifed for bug fix 4666216
66 -- Now Construct the full name which has the following format:
67 --
68 -- <last name><,> <first name> <middle name> <initials>
69
70
71 SELECT SUBSTR(TRIM(
72 TRIM(p_last_name)
73 || ', '
74 ||TRIM(p_first_name)
75 ||' '
76 ||TRIM(p_middle_name)
77 ||' '
78 ||TRIM(p_per_information1))
79 , 1, 240)
80 INTO l_full_name
81 FROM dual;
82
83 --
84 --
85 -- Return Full name
86 --
87
88 RETURN l_full_name;
89 --
90 END per_dk_full_name;
91
92
93 --
94 --
95 --
96 -- Function to Formate the Order Name for Denmark
97 --
98 FUNCTION per_dk_order_name(
99 p_first_name IN VARCHAR2
100 ,p_middle_name IN VARCHAR2
101 ,p_last_name IN VARCHAR2
102 ,p_known_as IN VARCHAR2
103 ,p_title IN VARCHAR2
104 ,p_suffix IN VARCHAR2
105 ,p_pre_name_adjunct IN VARCHAR2
106 ,p_per_information1 IN VARCHAR2
107 ,p_per_information2 IN VARCHAR2
108 ,p_per_information3 IN VARCHAR2
109 ,p_per_information4 IN VARCHAR2
110 ,p_per_information5 IN VARCHAR2
111 ,p_per_information6 IN VARCHAR2
112 ,p_per_information7 IN VARCHAR2
113 ,p_per_information8 IN VARCHAR2
114 ,p_per_information9 IN VARCHAR2
115 ,p_per_information10 IN VARCHAR2
116 ,p_per_information11 IN VARCHAR2
117 ,p_per_information12 IN VARCHAR2
118 ,p_per_information13 IN VARCHAR2
119 ,p_per_information14 IN VARCHAR2
120 ,p_per_information15 IN VARCHAR2
121 ,p_per_information16 IN VARCHAR2
122 ,p_per_information17 IN VARCHAR2
123 ,p_per_information18 IN VARCHAR2
124 ,p_per_information19 IN VARCHAR2
125 ,p_per_information20 IN VARCHAR2
126 ,p_per_information21 IN VARCHAR2
127 ,p_per_information22 IN VARCHAR2
128 ,p_per_information23 IN VARCHAR2
129 ,p_per_information24 IN VARCHAR2
130 ,p_per_information25 IN VARCHAR2
131 ,p_per_information26 IN VARCHAR2
132 ,p_per_information27 IN VARCHAR2
133 ,p_per_information28 IN VARCHAR2
134 ,p_per_information29 IN VARCHAR2
135 ,p_per_information30 IN VARCHAR2)
136 RETURN VARCHAR2 IS
137 --
138 --
139 --
140 -- Local Variable
141 --
142 l_order_name VARCHAR2(240);
143 --
144 --
145 BEGIN
146 --
147 --
148 -- Construct the order name which has the following format:
149 --
150 -- <last name>,<first name>,<middle name>
151 --
152 --
153
154 SELECT SUBSTR(TRIM(p_last_name)||','||TRIM(p_first_name) ||
155 DECODE(TRIM(P_MIDDLE_NAME),NULL,'', ',' || TRIM(p_middle_name)), 1, 240)
156 INTO l_order_name
157 FROM dual;
158
159 --
160 --
161 -- Return the Order Name
162 --
163
164 RETURN l_order_name;
165 --
166 --
167 --
168 END per_dk_order_name;
169
170
171 -- Validates the bank account number.
172 --
173 -- The format is as follows NNNNNNNNNN
174
175
176 FUNCTION validate_account_number
177 (p_account_number IN VARCHAR2) RETURN NUMBER IS
178 l_i NUMBER;
179 l_rem NUMBER;
180 l_strlen NUMBER;
181 l_valid NUMBER;
182 l_account_number VARCHAR2(15);
183 BEGIN
184 --
185 -- Bug 4124370 , an account number less than 10 digits should be allowed
186 IF LENGTH(p_account_number) > 10 THEN
187 RETURN 1;
188 END IF;
189
190 -- Ensure the Account Number consists only of digits.
191 --
192 l_strlen:= LENGTH(p_account_number);
193 FOR i IN 1..l_strlen
194 LOOP
195 IF (SUBSTR(p_account_number,i,1) < '0' OR SUBSTR(p_account_number,i,1) > '9') then
196 l_valid :=1;
197 END IF;
198
199 END LOOP;
200 IF l_valid =1 THEN
201 RETURN 1 ;
202 ELSE
203 RETURN 0 ;
204 END IF;
205
206 END;
207
208 FUNCTION chk_valid_date (p_nat_id IN VARCHAR2)
209 RETURN VARCHAR2
210 IS
211
212
213 L_CENTURY NUMBER(2);
214 L_NAT_ID_YY NUMBER(2); --YY PART OF CPR NUMBER
215 L_NAT_ID_CD NUMBER(1); --DIGIT CORRESPONDING TO CENTURY IN CPR NUMBER
216 L_DATE DATE;
217
218 BEGIN
219
220
221 L_NAT_ID_YY := TO_NUMBER(SUBSTR(p_nat_id,5,2));
222 L_NAT_ID_CD := TO_NUMBER(SUBSTR(p_nat_id,8,1));
223
224 IF L_NAT_ID_CD <= 3 THEN
225 L_CENTURY := 19;
226 ELSIF L_NAT_ID_CD = 4 THEN
227 IF L_NAT_ID_YY <= 36 THEN
228 L_CENTURY := 20;
229 ELSE
230 L_CENTURY := 19;
231 END IF;
232 ELSIF L_NAT_ID_CD = 5 THEN
233 IF L_NAT_ID_YY <= 36 THEN
234 L_CENTURY := 20;
235 ELSIF L_NAT_ID_YY >=58 THEN
236 L_CENTURY := 18;
237 ELSE
238 RETURN '0';
239 END IF;
240 ELSIF L_NAT_ID_CD = 6 THEN
241 IF L_NAT_ID_YY <= 36 THEN
242 L_CENTURY := 20;
243 ELSIF L_NAT_ID_YY >=58 THEN
244 L_CENTURY := 18;
245 ELSE
246 RETURN '0';
247 END IF;
248 ELSIF L_NAT_ID_CD = 7 THEN
249 IF L_NAT_ID_YY <= 36 THEN
250 L_CENTURY := 20;
251 ELSIF L_NAT_ID_YY >=58 THEN
252 L_CENTURY := 18;
253 ELSE
254 RETURN '0';
255 END IF;
256 ELSIF L_NAT_ID_CD = 8 THEN
257 IF L_NAT_ID_YY <= 36 THEN
258 L_CENTURY := 20;
259 ELSIF L_NAT_ID_YY >=58 THEN
260 L_CENTURY := 18;
261 ELSE
262 RETURN '0';
263 END IF;
264 ELSIF L_NAT_ID_CD = 9 THEN
265 IF L_NAT_ID_YY <= 36 THEN
266 L_CENTURY := 20;
267 ELSE
268 L_CENTURY := 19;
269 END IF;
270 END IF;
271
272
273 l_date := to_date(substr(p_nat_id,1,4)||l_century||substr(p_nat_id,5,2),'DDMMYYYY');
274 RETURN TO_CHAR(L_DATE,'DDMMYYYY');
275 EXCEPTION
276 WHEN others THEN
277 RETURN '0';
278 END;
279
280
281
282
283
284
285 -- Function : get_employment_information
286 -- Parameters : assignment_id - p_assignment_id,
287 -- employment information code - l_information_code.
288 -- Description : The function returns the employment information based on the assignment id
289 -- and the information code parameters. The information is first searced for at
290 -- the assignment level through the HR_Organization level , Local Unit level ,
291 -- Legal Employer Level to the Business group level.
292 --
293 -- The values for p_emp_information_code can be
294 -- COND_OF_EMP for Condition of Employment
295 -- EMP_GROUP for Employee Group
296
297
298 FUNCTION get_employment_information (
299 p_assignment_id IN number,
300 p_emp_information_code IN varchar2 )
301 RETURN VARCHAR2 IS
302
303 -- local variables declaration --
304 l_scl_id NUMBER(5);
305 l_organization_id Number(15);
306 l_is_hr_org varchar2(150);
307 l_information varchar2(150);
308 l_legal_employer number(15);
309 l_org_id number(15);
310 l_bg_id number(15);
311 l_information_code varchar2(50);
312
313 cursor get_scl_id is
314 select SOFT_CODING_KEYFLEX_ID
315 from PER_ALL_ASSIGNMENTS_F
316 where assignment_id = p_assignment_id;
317
318 cursor get_org_id is
319 select ORGANIZATION_ID
320 from PER_ALL_ASSIGNMENTS_F
321 where assignment_id = p_assignment_id;
322
323 cursor get_info_from_scl is
324 select lookups.meaning
325 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
326 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
327 and lookups.lookup_type=l_information_code
328 and lookups.enabled_flag = 'Y'
329 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',scl.segment3,
330 'DK_EMP_GROUP',scl.segment4,NULL);
331
332 cursor get_info_from_org is
333 select lookups.meaning
334 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
335 where hou.organization_id = l_organization_id
336 and hou.organization_id = hoi.organization_id
337 and hoi.org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
338 and lookups.lookup_type = l_information_code
339 and lookups.enabled_flag = 'Y'
340 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',hoi.org_information1,
341 'DK_EMP_GROUP',hoi.org_information2,NULL);
342
343 cursor get_legal_employer is
344 select segment2
345 from hr_soft_coding_keyflex
346 where soft_coding_keyflex_id = l_scl_id;
347
348
349 cursor get_info_from_legal_employer is
350 select lookups.meaning
351 from hr_organization_information hoi , hr_lookups lookups
352 where hoi.organization_id = l_org_id
353 and hoi.org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
354 and lookups.lookup_type = l_information_code
355 and lookups.enabled_flag = 'Y'
356 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',hoi.org_information1,
357 'DK_EMP_GROUP',hoi.org_information2,NULL);
358
359 cursor get_bg_id is
360 select business_group_id
361 from hr_organization_units
362 where organization_id = l_organization_id;
363
364 cursor is_hr_org is
365 select nvl(hoi.org_information1,'NO_DATA')
366 from hr_organization_units hou , hr_organization_information hoi
367 where hou.organization_id = l_organization_id
368 and hou.organization_id = hoi.organization_id
369 and hoi.org_information_context = 'CLASS'
370 and hoi.org_information1 = 'HR_ORG';
371
372
373
374 begin
375
376 if l_information_code not in ('COND_OF_EMP','EMP_GROUP') then
377 return 'ERR_WRONG_PARAMETER';
378 end if;
379
380 l_information_code := 'DK_'||p_emp_information_code;
381
382 --------------------------------------
383 --Try at the Assignment Level --
384 --------------------------------------
385
386 -- get scl id --
387 open get_scl_id;
388 fetch get_scl_id into l_scl_id;
389 close get_scl_id;
390
391 if l_scl_id is not null then
392 -- get information at assignment level --
393 open get_info_from_scl;
394 fetch get_info_from_scl into l_information;
395 close get_info_from_scl;
396 if l_information is not null then
397 return l_information;
398 end if;
399 end if;
400
401 --------------------------------------
402 --Try at the HR_ORG Level --
403 --------------------------------------
404
405 -- get organization_id --
406 open get_org_id;
407 fetch get_org_id into l_organization_id;
408 close get_org_id;
409
410 -- organization id cannot be null --
411 -- check if the organization is HR_ORG --
412 open is_hr_org;
413 fetch is_hr_org into l_is_hr_org;
414 if is_hr_org%NOTFOUND then
415 l_is_hr_org := 'NO_INFO';
416 end if;
417
418 -- get information at the HR Organization level --
419 if l_is_hr_org <> 'NO_INFO' then
420 open get_info_from_org;
421 fetch get_info_from_org into l_information;
422 close get_info_from_org;
423
424 if l_information is not null then
425 return l_information;
426 end if;
427 end if;
428
429
430
431 -------------------------------------------
432 --Try at the Legal Employer Level --
433 --------------------------------------------
434 -- get legal employer id --
435 open get_legal_employer;
436 fetch get_legal_employer into l_legal_employer;
437 close get_legal_employer;
438
439 -- get information at local unit level --
440 l_org_id := l_legal_employer;
441 open get_info_from_legal_employer;
442 fetch get_info_from_legal_employer into l_information;
443 close get_info_from_legal_employer;
444
445 if l_information is not null then
446 return l_information;
447 end if;
448
449 ------------------------------------------
450 --Try at the Business Group Level --
451 ------------------------------------------
452 -- get bg id --
453 open get_bg_id;
454 fetch get_bg_id into l_bg_id;
455 close get_bg_id;
456
457 -- search at bg level--
458 -- the value in l_organization_id will no longer be necessary --
459 -- storing bg_id in l_organization_id --
460
461 l_organization_id := l_bg_id;
462 open get_info_from_org;
463 fetch get_info_from_org into l_information;
464 close get_info_from_org;
465
466 if l_information is not null then
467 return l_information;
468 end if;
469
470 -- return null if the emp information is not present at any level --
471 return null;
472
473
474 END get_employment_information;
475
476
477 function get_message
478 (p_product in varchar2
479 ,p_message_name in varchar2
480 ,p_token1 in varchar2 default null
481 ,p_token2 in varchar2 default null
482 ,p_token3 in varchar2 default null) return varchar2
483 is
484 l_message varchar2(2000);
485 l_token_name varchar2(20);
486 l_token_value varchar2(80);
487 l_colon_position number;
488 --l_proc varchar2(72) := g_package||'.get_message';
489 --
490 begin
491 --
492 --hr_utility.set_location('Entered '||l_proc,5);
493 hr_utility.set_location('. Message Name: '||p_message_name,40);
494 fnd_message.set_name(p_product, p_message_name);
495 if p_token1 is not null then
496 /* Obtain token 1 name and value */
497 l_colon_position := instr(p_token1,':');
498 l_token_name := substr(p_token1,1,l_colon_position-1);
499 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
500 fnd_message.set_token(l_token_name, l_token_value);
501 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
502 end if;
503 if p_token2 is not null then
504 /* Obtain token 2 name and value */
505 l_colon_position := instr(p_token2,':');
506 l_token_name := substr(p_token2,1,l_colon_position-1);
507 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
508 fnd_message.set_token(l_token_name, l_token_value);
509 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
510 end if;
511 if p_token3 is not null then
512 /* Obtain token 3 name and value */
513 l_colon_position := instr(p_token3,':');
514 l_token_name := substr(p_token3,1,l_colon_position-1);
515 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
516 fnd_message.set_token(l_token_name, l_token_value);
517 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
518 end if;
519 l_message := substr(fnd_message.get,1,254);
520 --hr_utility.set_location('leaving '||l_proc,100);
521 return l_message;
522 end get_message;
523
524 -----------------------------------------------------------
525
526 FUNCTION REPLACE_SPECIAL_CHARS(p_xml IN VARCHAR2)
527 RETURN VARCHAR2
528 IS
529 l_xml VARCHAR2(240);
530 BEGIN
531 /* Handle special charaters in data */
532
533 If p_xml is not null then
534 l_xml := '<![CDATA['||p_xml||']]>'; /*Remove the Space*/
535 end if;
536
537 RETURN l_xml;
538
539 END REPLACE_SPECIAL_CHARS;
540
541 --------------------------------------------------------------
542
543
544 --
545 -- End of the Package
546
547 END hr_dk_utility;
548
[Home] [Help]