[Home] [Help]
PACKAGE BODY: APPS.HR_SE_UTILITY
Source
1 PACKAGE BODY hr_se_utility AS
2 -- $Header: hrseutil.pkb 120.2.12010000.2 2009/11/27 10:32:18 dchindar ship $
3 g_package varchar2(30) := 'hr_se_utility';
4 FUNCTION per_se_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 AS
44 --
45 l_full_name varchar2(240);
46 --
47 BEGIN
48 --
49 l_full_name := p_last_name|| ', '||p_first_name;
50 if p_middle_names is not null then
51 l_full_name := l_full_name||', '||p_middle_names||' ';
52 end if;
53 if p_pre_name_adjunct is not null then
54 l_full_name := p_pre_name_adjunct||' '||l_full_name;
55 end if;
56
57 return (rtrim(l_full_name));
58 --
59 END;
60 --
61 --
62 FUNCTION per_se_order_name(
63 p_first_name in varchar2
64 ,p_middle_names in varchar2
65 ,p_last_name in varchar2
66 ,p_known_as in varchar2
67 ,p_title in varchar2
68 ,p_suffix in varchar2
69 ,p_pre_name_adjunct in varchar2
70 ,p_per_information1 in varchar2
71 ,p_per_information2 in varchar2
72 ,p_per_information3 in varchar2
73 ,p_per_information4 in varchar2
74 ,p_per_information5 in varchar2
75 ,p_per_information6 in varchar2
76 ,p_per_information7 in varchar2
77 ,p_per_information8 in varchar2
78 ,p_per_information9 in varchar2
79 ,p_per_information10 in varchar2
80 ,p_per_information11 in varchar2
81 ,p_per_information12 in varchar2
82 ,p_per_information13 in varchar2
83 ,p_per_information14 in varchar2
84 ,p_per_information15 in varchar2
85 ,p_per_information16 in varchar2
86 ,p_per_information17 in varchar2
87 ,p_per_information18 in varchar2
88 ,p_per_information19 in varchar2
89 ,p_per_information20 in varchar2
90 ,p_per_information21 in varchar2
91 ,p_per_information22 in varchar2
92 ,p_per_information23 in varchar2
93 ,p_per_information24 in varchar2
94 ,p_per_information25 in varchar2
95 ,p_per_information26 in varchar2
96 ,p_per_information27 in varchar2
97 ,p_per_information28 in varchar2
98 ,p_per_information29 in varchar2
99 ,p_per_information30 in VARCHAR2
100 )
101 RETURN VARCHAR2 AS
102 --
103 l_order_name varchar2(240);
104 --
105 BEGIN
106 --
107 l_order_name := p_last_name || ' ' || p_first_name;
108 return (rtrim(l_order_name));
109 --
110 END;
111
112 -- Checks whether the input is a valid date.
113 --
114 FUNCTION chk_valid_date
115 (p_date IN VARCHAR2)
116 RETURN VARCHAR2 AS
117 l_date DATE;
118 BEGIN
119 IF substr(p_date,5,2) - 60 > 0 THEN
120 l_date:=to_date(19||(p_date- 60),'YYYYMMDD');
121 ELSE
122 l_date:=to_date(19||p_date,'YYYYMMDD');
123 END IF;
124 RETURN '1';
125 exception
126 WHEN others THEN
127 RETURN '0';
128 END ;
129
130 FUNCTION validate_account_number
131 (p_account_number IN VARCHAR2,
132 p_session_id IN NUMBER,
133 p_bg_id IN NUMBER
134 ) RETURN NUMBER AS
135 l_account_type VARCHAR2(2);
136 l_modulus_type VARCHAR2(2);
137 l_no_of_digits NUMBER;
138 l_chk_format NUMBER;
139 l_i NUMBER;
140 l_rem NUMBER;
141 l_calc NUMBER;
142 l_account_number VARCHAR2(16);
143 l_effective_date DATE;
144 l_business_group_id NUMBER;
145 begin
146 --Fetching the Effective Date for the session.
147 --
148 begin
149 SELECT effective_date
150 INTO l_effective_date
151 FROM fnd_sessions
152 WHERE session_id=p_session_id;
153 exception
154 WHEN others THEN
155 null;
156 end;
157
158 begin
159 --Check for the Account Type.There are two types of Accounts ie Type 1 and Type 2.
160 --
161 l_account_type:=hruserdt.get_table_value (p_bg_id,'SE_BANK_DETAILS','ACCOUNT_TYPE',SUBSTR(p_account_number,1,4),l_effective_date);
162 IF l_account_type IS NULL THEN
163 RETURN 1;
164 END IF;
165 --Fetch the Modulus Type.There are two types of Modulus ie Modulus 10 and Modulus 11.
166 --
167 l_modulus_type:=hruserdt.get_table_value (p_bg_id,'SE_BANK_DETAILS','MODULUS_TYPE',SUBSTR(p_account_number,1,4),l_effective_date);
168
169 --Fetch the No of Digits for Validation.
170 --
171 l_no_of_digits:=hruserdt.get_table_value (p_bg_id,'SE_BANK_DETAILS','NO_OF_DIGITS',SUBSTR(p_account_number,1,4),l_effective_date);
172
173 IF l_account_type ='1' THEN
174
175 --The No of Digits should be between 11 and 16
176 --
177 IF LENGTH(p_account_number) < 11 THEN
178 RETURN 1;
179 END IF;
180 --Pad the last 7 seven Digits upto 12 spaces with 0
181 --
182 l_account_number := SUBSTR(p_account_number,1,4)||LPAD(SUBSTR(p_account_number,5,12),12,'0');
183
184 --Check for Integer Digits
185 --
186
187 l_chk_format := hr_ni_chk_pkg.chk_nat_id_format(l_account_number,'DDDDDDDDDDDDDDDD');
188
189 -- Using Modulus 11 Validation
190 --
191 l_i := 0;
192
193 IF l_no_of_digits=11 THEN
194 l_i := l_i + substr(l_account_number, 1, 1) * 1;
195 END IF;
196 l_i := l_i + substr(l_account_number, 2, 1) * 10;
197 l_i := l_i + substr(l_account_number, 3, 1) * 9;
198 l_i := l_i + substr(l_account_number, 4, 1) * 8;
199 l_i := l_i + substr(l_account_number, 10, 1) * 7;
200 l_i := l_i + substr(l_account_number, 11, 1) * 6;
201 l_i := l_i + substr(l_account_number, 12, 1) * 5;
202 l_i := l_i + substr(l_account_number, 13, 1) * 4;
203 l_i := l_i + substr(l_account_number, 14, 1) * 3;
204 l_i := l_i + substr(l_account_number, 15, 1) * 2;
205 l_i := l_i + substr(l_account_number, 16, 1) * 1;
206
207 l_rem := mod( l_i, 11 );
208 IF l_rem = 0 THEN
209 RETURN 0 ;
210 ELSE
211 RETURN 1;
212 END IF;
213 ELSE
214 --The No of Digits should be between 6 and 16
215 --
216 IF LENGTH(p_account_number)NOT BETWEEN 6 AND 16 THEN
217 RETURN 1;
218 END IF;
219 --Pad the last 7 seven Digits upto 12 spaces with 0
220 --
221 l_account_number := SUBSTR(p_account_number,1,4)||LPAD(SUBSTR(p_account_number,5,10),12,'0');
222
223 --Check for Integer Digits
224 --
225 l_chk_format := hr_ni_chk_pkg.chk_nat_id_format(l_account_number,'DDDDDDDDDDDDDDDD');
226
227 -- Using Modulus 10/11 Validation according to Type
228 --
229 IF l_modulus_type='11' THEN
230 -- Using Modulus 11 Validation
231 --
232 l_i := 0;
233
234 l_i := l_i + substr(l_account_number, 8, 1) * 9;
235 l_i := l_i + substr(l_account_number, 9, 1) * 8;
236 l_i := l_i + substr(l_account_number, 10, 1) * 7;
237 l_i := l_i + substr(l_account_number, 11, 1) * 6;
238 l_i := l_i + substr(l_account_number, 12, 1) * 5;
239 l_i := l_i + substr(l_account_number, 13, 1) * 4;
240 l_i := l_i + substr(l_account_number, 14, 1) * 3;
241 l_i := l_i + substr(l_account_number, 15, 1) * 2;
242 l_i := l_i + substr(l_account_number, 16, 1) * 1;
243
244 l_rem := mod( l_i, 11 );
245 IF l_rem = 0 THEN
246 RETURN 0 ;
247 ELSE
248 RETURN 1 ;
249 END IF;
250 ELSE
251 -- Using Modulus 10 Validation
252 --
253 l_calc :=0;
254
255 IF l_no_of_digits=12 THEN
256 l_calc :=l_calc + nvl(substr((substr(l_account_number,5,1)* 2),1,1),0)
257 + nvl(substr((substr(l_account_number,5,1)* 2),2,1),0) ;
258 l_calc :=l_calc + nvl(substr((substr(l_account_number,6,1)* 1),1,1),0)
259 + nvl(substr((substr(l_account_number,6,1)* 1),2,1),0);
260 END IF;
261 l_calc :=l_calc + nvl(substr((substr(l_account_number,7,1)* 2),1,1),0)
262 + nvl(substr((substr(l_account_number,7,1)* 2),2,1),0);
263 l_calc :=l_calc + nvl(substr((substr(l_account_number,8,1)* 1),1,1),0)
264 + nvl(substr((substr(l_account_number,8,1)* 1),2,1),0);
265 l_calc :=l_calc + nvl(substr((substr(l_account_number,9,1)* 2),1,1),0)
266 + nvl(substr((substr(l_account_number,9,1)* 2),2,1),0) ;
267 l_calc :=l_calc + nvl(substr((substr(l_account_number,10,1)* 1),1,1),0)
268 + nvl(substr((substr(l_account_number,10,1)* 1),2,1),0);
269 l_calc :=l_calc + nvl(substr((substr(l_account_number,11,1)* 2),1,1),0)
270 + nvl(substr((substr(l_account_number,11,1)* 2),2,1),0) ;
271 l_calc :=l_calc + nvl(substr((substr(l_account_number,12,1)* 1),1,1),0)
272 + nvl(substr((substr(l_account_number,12,1)* 1),2,1),0) ;
273 l_calc :=l_calc + nvl(substr((substr(l_account_number,13,1)* 2),1,1),0)
274 + nvl(substr((substr(l_account_number,13,1)* 2),2,1),0) ;
275 l_calc :=l_calc + nvl(substr((substr(l_account_number,14,1)* 1),1,1),0)
276 + nvl(substr((substr(l_account_number,14,1)* 1),2,1),0) ;
277 l_calc :=l_calc + nvl(substr((substr(l_account_number,15,1)* 2),1,1),0)
278 + nvl(substr((substr(l_account_number,15,1)* 2),2,1),0) ;
279 l_calc :=l_calc + nvl(substr((substr(l_account_number,16,1)* 1),1,1),0)
280 + nvl(substr((substr(l_account_number,16,1)* 1),2,1),0) ;
281
282 IF mod(l_calc,10)= 0 THEN
283 RETURN 0;
284 ELSE
285 RETURN 1;
286 END IF;
287 END IF;
288 END IF;
289 exception
290 WHEN others THEN
291 RETURN 1;
292 end;
293 end;
294
295
296 ----
297 -- Function added for IBAN Validation
298 ----
299 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
300 BEGIN
301 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
302 RETURN 1;
303 else
304 RETURN 0;
305 END IF;
306 END validate_iban_acc;
307
308 ----
309 -- This function will get called from the bank keyflex field segments
310 ----
311 FUNCTION validate_account_entered
312 (p_acc_no IN VARCHAR2,
313 p_is_iban_acc IN varchar2,
314 p_session_id IN NUMBER default NULL,
315 p_bg_id IN NUMBER default NULL) RETURN NUMBER IS
316 --
317 l_ret NUMBER ;
318 begin
319 -- hr_utility.trace_on(null,'ACCVAL');
320 l_ret :=0;
321 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
322 hr_utility.set_location('p_account_number ' || p_acc_no,1);
323
324 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
325 l_ret := validate_account_number(p_acc_no, p_session_id, p_bg_id);
326 hr_utility.set_location('l_ret ' || l_ret,1);
327 RETURN l_ret;
328 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
329 l_ret := validate_iban_acc(p_acc_no);
330 hr_utility.set_location('l_ret ' || l_ret,3);
331 RETURN l_ret;
332 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
333 hr_utility.set_location('Both Account Nos Null',4);
334 RETURN 1;
335 ELSE
336 hr_utility.set_location('l_ret: 3 ' ,5);
337 RETURN 3;
338 END if;
339 End validate_account_entered;
340
341
342
343
344
345 --------------------------
346
347 FUNCTION get_court_order_details
348 (p_assignment_id IN NUMBER
349 ,p_effective_date IN DATE
350 ,p_reserved_amount OUT NOCOPY NUMBER
351 ,p_disdraint_amount OUT NOCOPY NUMBER
352 ,p_suspension_flag OUT NOCOPY VARCHAR2
353 ) RETURN NUMBER IS
354 --
355
356 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
357 SELECT eev1.screen_entry_value screen_entry_value
358 FROM per_all_assignments_f asg1
359 ,per_all_assignments_f asg2
360 ,per_all_people_f per
361 ,pay_element_links_f el
362 ,pay_element_types_f et
363 ,pay_input_values_f iv1
364 ,pay_element_entries_f ee
365 ,pay_element_entry_values_f eev1
366 WHERE asg1.assignment_id = p_assignment_id
367 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
368 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
369 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
370 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
371 AND per.person_id = asg1.person_id
372 AND asg2.person_id = per.person_id
373 AND asg2.primary_flag = 'Y'
374 AND et.element_name = 'Court Order Information'
375 AND et.legislation_code = 'SE'
376 AND iv1.element_type_id = et.element_type_id
377 AND iv1.name = p_input_value
378 AND el.business_group_id = per.business_group_id
379 AND el.element_type_id = et.element_type_id
380 AND ee.assignment_id = asg2.assignment_id
381 AND ee.element_link_id = el.element_link_id
382 AND eev1.element_entry_id = ee.element_entry_id
383 AND eev1.input_value_id = iv1.input_value_id;
384
385 l_rec get_details%ROWTYPE;
386 ---
387 BEGIN
388 --
389 OPEN get_details(p_assignment_id , p_effective_date ,'Reserved Amount');
390 FETCH get_details INTO l_rec;
391 p_reserved_amount := l_rec.screen_entry_value ;
392 CLOSE get_details;
393
394
395 OPEN get_details(p_assignment_id , p_effective_date , 'Distraint Amount');
396 FETCH get_details INTO l_rec;
397 p_disdraint_amount := l_rec.screen_entry_value ;
398 CLOSE get_details;
399
400
401 OPEN get_details(p_assignment_id , p_effective_date ,'Suspension Flag');
402 FETCH get_details INTO l_rec;
403
404 p_suspension_flag := l_rec.screen_entry_value ;
405
406 CLOSE get_details;
407
408
409 --
410 RETURN 1;
411 exception
412 WHEN others THEN
413 RETURN 1;
414 --
415 END get_court_order_details;
416
417 ------------Mileage
418
419 FUNCTION GET_COMPANY_MILEAGE_LIMIT
420 (p_effective_date IN DATE
421 ,p_business_group_id IN NUMBER
422 ,p_tax_unit_id IN NUMBER
423 ,p_car_type IN VARCHAR2
424 ) RETURN NUMBER IS
425
426 CURSOR c_get_details(p_business_group_id NUMBER , p_tax_unit_id NUMBER, p_effective_date DATE) IS
427 SELECT hoi2.org_information1,hoi2.org_information2,hoi2.org_information3
428 FROM hr_organization_units o1
429 , hr_organization_information hoi1
430 , hr_organization_information hoi2
431 WHERE o1.business_group_id = p_business_group_id
432 AND hoi1.organization_id = o1.organization_id
433 AND hoi1.organization_id = p_tax_unit_id
434 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
435 AND hoi1.org_information_context = 'CLASS'
436 AND o1.organization_id = hoi2.organization_id
437 AND hoi2.ORG_INFORMATION_CONTEXT ='SE_COMPANY_MILEAGE_RATES'
438 AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information4) AND
439 nvl(fnd_date.canonical_to_date(hoi2.org_information5),to_date('31/12/4712','DD/MM/YYYY')) ;
440
441 l_rec c_get_details%ROWTYPE;
442 l_return NUMBER;
443
444 --
445 BEGIN
446
447 OPEN c_get_details(p_business_group_id,p_tax_unit_id,p_effective_date);
448 FETCH c_get_details INTO l_rec;
449 CLOSE c_get_details;
450
451 IF p_car_type ='PRIVATE CAR' THEN
452 l_return := l_rec.org_information1;
453 ELSIF p_car_type ='COMPANY PETROL CAR' THEN
454 l_return := l_rec.org_information2;
455 ELSIF p_car_type ='COMPANY DIESEL CAR' THEN
456 l_return := l_rec.org_information3;
457 END IF;
458
459
460 RETURN l_return;
461
462 EXCEPTION
463 WHEN others THEN
464 RETURN NULL;
465
466 END GET_COMPANY_MILEAGE_LIMIT;
467
468 ------------------------------------------------------------------------
469 -- Function GET_MESSAGE
470 -- This function is used to obtain a message.
471 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
472 -- If you want to set the value of a token called ELEMENT to Social Ins
473 -- the token parameter would be 'ELEMENT:Social Ins.'
474 ------------------------------------------------------------------------
475 function get_message
476 (p_product in varchar2
477 ,p_message_name in varchar2
478 ,p_token1 in varchar2 default null
479 ,p_token2 in varchar2 default null
480 ,p_token3 in varchar2 default null) return varchar2
481 is
482 l_message varchar2(2000);
483 l_token_name varchar2(20);
484 l_token_value varchar2(80);
485 l_colon_position number;
486 l_proc varchar2(72) ;
487 l_product varchar2(72);
488 --
489 begin
490 --
491 l_proc:= g_package||'.get_message' ;
492 hr_utility.set_location('Entered '||l_proc,5);
493 hr_utility.set_location('. Message Name: '||p_message_name,40);
494 IF p_product in ('800','801') THEN
495 l_product :=hr_general.get_application_short_name(p_product);
496 else
497 l_product :=p_product;
498 END IF;
499 fnd_message.set_name(l_product, p_message_name);
500 if p_token1 is not null then
501 /* Obtain token 1 name and value */
502 l_colon_position := instr(p_token1,':');
503 l_token_name := substr(p_token1,1,l_colon_position-1);
504 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
505 fnd_message.set_token(l_token_name, l_token_value);
506 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
507 end if;
508 if p_token2 is not null then
509 /* Obtain token 2 name and value */
510 l_colon_position := instr(p_token2,':');
511 l_token_name := substr(p_token2,1,l_colon_position-1);
512 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
513 fnd_message.set_token(l_token_name, l_token_value);
514 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
515 end if;
516 if p_token3 is not null then
517 /* Obtain token 3 name and value */
518 l_colon_position := instr(p_token3,':');
519 l_token_name := substr(p_token3,1,l_colon_position-1);
520 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
521 fnd_message.set_token(l_token_name, l_token_value);
522 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
523 end if;
524 l_message := substr(fnd_message.get,1,254);
525 hr_utility.set_location('leaving '||l_proc,100);
526 return l_message;
527 end get_message;
528
529
530
531 --------------------------------------------------------------------------
532 -- --
533 -- Name : get_IANA_charset --
534 -- Type : Function --
535 -- Access : Public --
536 -- Description : Function to IANA charset equivalent of --
537 -- NLS_CHARACTERSET --
538 -- Parameters : --
539 -- IN : N/A --
540 -- OUT : N/A --
541 -- RETURN : VARCHAR2 --
542 -- --
543 --------------------------------------------------------------------------
544 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
545 CURSOR csr_get_iana_charset IS
546 SELECT tag
547 FROM fnd_lookup_values
548 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
549 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
550 INSTR(USERENV('LANGUAGE'), '.') + 1)
551 AND language = 'US';
552
553 lv_iana_charset fnd_lookup_values.tag%type;
554 BEGIN
555 OPEN csr_get_iana_charset;
556 FETCH csr_get_iana_charset INTO lv_iana_charset;
557 CLOSE csr_get_iana_charset;
558
559 hr_utility.trace('IANA Charset = '||lv_iana_charset);
560 RETURN (lv_iana_charset);
561 END get_IANA_charset;
562
563
564
565
566 END hr_se_utility ;