4 --
1 PACKAGE BODY hr_dk_utility AS
2 /* $Header: hrdkutil.pkb 120.4.12010000.3 2009/11/20 07:20:40 dchindar ship $ */
3 --
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 --
93 --
90 END per_dk_full_name;
91
92
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
209 ----
210 -- Function added for IBAN Validation
211 ----
212 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
213 BEGIN
214 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
215 RETURN 1;
216 else
217 RETURN 0;
218 END IF;
219 END validate_iban_acc;
220
221 ----
222 -- This function will get called from the bank keyflex field segments Bug 9127804
223 ----
224 FUNCTION validate_account_entered
225 (p_acc_no IN VARCHAR2,
226 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
227 --
228 l_ret NUMBER ;
229 begin
230 -- hr_utility.trace_on(null,'ACCVAL');
231 l_ret :=0;
232 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
233 hr_utility.set_location('p_account_number ' || p_acc_no,1);
234
235 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
236 l_ret := validate_account_number(p_acc_no);
237 hr_utility.set_location('l_ret ' || l_ret,1);
238 RETURN l_ret;
239 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
240 l_ret := validate_iban_acc(p_acc_no);
241 hr_utility.set_location('l_ret ' || l_ret,3);
242 RETURN l_ret;
243 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
244 hr_utility.set_location('Both Account Nos Null',4);
245 RETURN 1;
246 ELSE
247 hr_utility.set_location('l_ret: 3 ' ,5);
248 RETURN 3;
249 END if;
250 End validate_account_entered;
251
252
253
254 FUNCTION chk_valid_date (p_nat_id IN VARCHAR2)
255 RETURN VARCHAR2
256 IS
257
258
259 L_CENTURY NUMBER(2);
260 L_NAT_ID_YY NUMBER(2); --YY PART OF CPR NUMBER
264 BEGIN
261 L_NAT_ID_CD NUMBER(1); --DIGIT CORRESPONDING TO CENTURY IN CPR NUMBER
262 L_DATE DATE;
263
265
266
267 L_NAT_ID_YY := TO_NUMBER(SUBSTR(p_nat_id,5,2));
268 L_NAT_ID_CD := TO_NUMBER(SUBSTR(p_nat_id,8,1));
269
270 IF L_NAT_ID_CD <= 3 THEN
271 L_CENTURY := 19;
272 ELSIF L_NAT_ID_CD = 4 THEN
273 IF L_NAT_ID_YY <= 36 THEN
274 L_CENTURY := 20;
275 ELSE
276 L_CENTURY := 19;
277 END IF;
278 ELSIF L_NAT_ID_CD = 5 THEN
279 IF L_NAT_ID_YY <= 36 THEN
280 L_CENTURY := 20;
281 ELSIF L_NAT_ID_YY >=58 THEN
282 L_CENTURY := 18;
283 ELSE
284 RETURN '0';
285 END IF;
286 ELSIF L_NAT_ID_CD = 6 THEN
287 IF L_NAT_ID_YY <= 36 THEN
288 L_CENTURY := 20;
289 ELSIF L_NAT_ID_YY >=58 THEN
290 L_CENTURY := 18;
291 ELSE
292 RETURN '0';
293 END IF;
294 ELSIF L_NAT_ID_CD = 7 THEN
295 IF L_NAT_ID_YY <= 36 THEN
296 L_CENTURY := 20;
297 ELSIF L_NAT_ID_YY >=58 THEN
298 L_CENTURY := 18;
299 ELSE
300 RETURN '0';
301 END IF;
302 ELSIF L_NAT_ID_CD = 8 THEN
303 IF L_NAT_ID_YY <= 36 THEN
304 L_CENTURY := 20;
305 ELSIF L_NAT_ID_YY >=58 THEN
306 L_CENTURY := 18;
307 ELSE
308 RETURN '0';
309 END IF;
310 ELSIF L_NAT_ID_CD = 9 THEN
311 IF L_NAT_ID_YY <= 36 THEN
312 L_CENTURY := 20;
313 ELSE
314 L_CENTURY := 19;
315 END IF;
316 END IF;
317
318
319 l_date := to_date(substr(p_nat_id,1,4)||l_century||substr(p_nat_id,5,2),'DDMMYYYY');
320 RETURN TO_CHAR(L_DATE,'DDMMYYYY');
321 EXCEPTION
322 WHEN others THEN
323 RETURN '0';
324 END;
325
326
327
328
329
330
331 -- Function : get_employment_information
332 -- Parameters : assignment_id - p_assignment_id,
333 -- employment information code - l_information_code.
334 -- Description : The function returns the employment information based on the assignment id
335 -- and the information code parameters. The information is first searced for at
336 -- the assignment level through the HR_Organization level , Local Unit level ,
337 -- Legal Employer Level to the Business group level.
338 --
339 -- The values for p_emp_information_code can be
340 -- COND_OF_EMP for Condition of Employment
341 -- EMP_GROUP for Employee Group
342
343
344 FUNCTION get_employment_information (
345 p_assignment_id IN number,
346 p_emp_information_code IN varchar2 )
347 RETURN VARCHAR2 IS
348
349 -- local variables declaration --
350 l_scl_id NUMBER(5);
351 l_organization_id Number(15);
352 l_is_hr_org varchar2(150);
353 l_information varchar2(150);
354 l_legal_employer number(15);
355 l_org_id number(15);
356 l_bg_id number(15);
357 l_information_code varchar2(50);
358
359 cursor get_scl_id is
360 select SOFT_CODING_KEYFLEX_ID
361 from PER_ALL_ASSIGNMENTS_F
362 where assignment_id = p_assignment_id;
363
364 cursor get_org_id is
365 select ORGANIZATION_ID
366 from PER_ALL_ASSIGNMENTS_F
367 where assignment_id = p_assignment_id;
368
369 cursor get_info_from_scl is
370 select lookups.meaning
371 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
372 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
373 and lookups.lookup_type=l_information_code
374 and lookups.enabled_flag = 'Y'
375 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',scl.segment3,
376 'DK_EMP_GROUP',scl.segment4,NULL);
377
378 cursor get_info_from_org is
379 select lookups.meaning
380 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
381 where hou.organization_id = l_organization_id
382 and hou.organization_id = hoi.organization_id
383 and hoi.org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
384 and lookups.lookup_type = l_information_code
385 and lookups.enabled_flag = 'Y'
386 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',hoi.org_information1,
387 'DK_EMP_GROUP',hoi.org_information2,NULL);
388
389 cursor get_legal_employer is
390 select segment2
391 from hr_soft_coding_keyflex
392 where soft_coding_keyflex_id = l_scl_id;
393
394
395 cursor get_info_from_legal_employer is
396 select lookups.meaning
397 from hr_organization_information hoi , hr_lookups lookups
398 where hoi.organization_id = l_org_id
399 and hoi.org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
400 and lookups.lookup_type = l_information_code
401 and lookups.enabled_flag = 'Y'
402 and lookups.lookup_code = decode(l_information_code,'DK_COND_OF_EMP',hoi.org_information1,
403 'DK_EMP_GROUP',hoi.org_information2,NULL);
404
405 cursor get_bg_id is
406 select business_group_id
407 from hr_organization_units
408 where organization_id = l_organization_id;
409
410 cursor is_hr_org is
411 select nvl(hoi.org_information1,'NO_DATA')
412 from hr_organization_units hou , hr_organization_information hoi
413 where hou.organization_id = l_organization_id
414 and hou.organization_id = hoi.organization_id
415 and hoi.org_information_context = 'CLASS'
416 and hoi.org_information1 = 'HR_ORG';
417
418
419
420 begin
421
422 if l_information_code not in ('COND_OF_EMP','EMP_GROUP') then
423 return 'ERR_WRONG_PARAMETER';
424 end if;
425
426 l_information_code := 'DK_'||p_emp_information_code;
427
428 --------------------------------------
432 -- get scl id --
429 --Try at the Assignment Level --
430 --------------------------------------
431
433 open get_scl_id;
434 fetch get_scl_id into l_scl_id;
435 close get_scl_id;
436
437 if l_scl_id is not null then
438 -- get information at assignment level --
439 open get_info_from_scl;
440 fetch get_info_from_scl into l_information;
441 close get_info_from_scl;
442 if l_information is not null then
443 return l_information;
444 end if;
445 end if;
446
447 --------------------------------------
448 --Try at the HR_ORG Level --
449 --------------------------------------
450
451 -- get organization_id --
452 open get_org_id;
453 fetch get_org_id into l_organization_id;
454 close get_org_id;
455
456 -- organization id cannot be null --
457 -- check if the organization is HR_ORG --
458 open is_hr_org;
459 fetch is_hr_org into l_is_hr_org;
460 if is_hr_org%NOTFOUND then
461 l_is_hr_org := 'NO_INFO';
462 end if;
463
464 -- get information at the HR Organization level --
465 if l_is_hr_org <> 'NO_INFO' then
466 open get_info_from_org;
467 fetch get_info_from_org into l_information;
468 close get_info_from_org;
469
470 if l_information is not null then
471 return l_information;
472 end if;
473 end if;
474
475
476
477 -------------------------------------------
478 --Try at the Legal Employer Level --
479 --------------------------------------------
480 -- get legal employer id --
481 open get_legal_employer;
482 fetch get_legal_employer into l_legal_employer;
483 close get_legal_employer;
484
485 -- get information at local unit level --
486 l_org_id := l_legal_employer;
487 open get_info_from_legal_employer;
488 fetch get_info_from_legal_employer into l_information;
489 close get_info_from_legal_employer;
490
491 if l_information is not null then
492 return l_information;
493 end if;
494
495 ------------------------------------------
496 --Try at the Business Group Level --
497 ------------------------------------------
498 -- get bg id --
499 open get_bg_id;
500 fetch get_bg_id into l_bg_id;
501 close get_bg_id;
502
503 -- search at bg level--
504 -- the value in l_organization_id will no longer be necessary --
505 -- storing bg_id in l_organization_id --
506
507 l_organization_id := l_bg_id;
508 open get_info_from_org;
509 fetch get_info_from_org into l_information;
510 close get_info_from_org;
511
512 if l_information is not null then
513 return l_information;
514 end if;
515
516 -- return null if the emp information is not present at any level --
517 return null;
518
519
520 END get_employment_information;
521
522
523 function get_message
524 (p_product in varchar2
525 ,p_message_name in varchar2
526 ,p_token1 in varchar2 default null
527 ,p_token2 in varchar2 default null
528 ,p_token3 in varchar2 default null) return varchar2
529 is
530 l_message varchar2(2000);
531 l_token_name varchar2(20);
532 l_token_value varchar2(80);
533 l_colon_position number;
534 --l_proc varchar2(72) := g_package||'.get_message';
535 --
536 begin
537 --
538 --hr_utility.set_location('Entered '||l_proc,5);
539 hr_utility.set_location('. Message Name: '||p_message_name,40);
540 fnd_message.set_name(p_product, p_message_name);
541 if p_token1 is not null then
542 /* Obtain token 1 name and value */
543 l_colon_position := instr(p_token1,':');
544 l_token_name := substr(p_token1,1,l_colon_position-1);
545 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
546 fnd_message.set_token(l_token_name, l_token_value);
547 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
548 end if;
549 if p_token2 is not null then
550 /* Obtain token 2 name and value */
551 l_colon_position := instr(p_token2,':');
552 l_token_name := substr(p_token2,1,l_colon_position-1);
553 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
554 fnd_message.set_token(l_token_name, l_token_value);
555 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
556 end if;
557 if p_token3 is not null then
558 /* Obtain token 3 name and value */
559 l_colon_position := instr(p_token3,':');
560 l_token_name := substr(p_token3,1,l_colon_position-1);
561 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
562 fnd_message.set_token(l_token_name, l_token_value);
563 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
564 end if;
565 l_message := substr(fnd_message.get,1,254);
566 --hr_utility.set_location('leaving '||l_proc,100);
567 return l_message;
568 end get_message;
569
570 -----------------------------------------------------------
571
572 FUNCTION REPLACE_SPECIAL_CHARS(p_xml IN VARCHAR2)
573 RETURN VARCHAR2
574 IS
575 l_xml VARCHAR2(240);
576 BEGIN
577 /* Handle special charaters in data */
578
579 If p_xml is not null then
580 l_xml := '<![CDATA['||p_xml||']]>'; /*Remove the Space*/
581 end if;
582
583 RETURN l_xml;
584
585 END REPLACE_SPECIAL_CHARS;
586
587 --------------------------------------------------------------
588
589
590 --
591 -- End of the Package
592
593 END hr_dk_utility;
594
[Home] [Help]