[Home] [Help]
PACKAGE BODY: APPS.HR_SE_UTILITY
Source
1 PACKAGE BODY hr_se_utility AS
2 -- $Header: hrseutil.pkb 120.2 2006/11/09 11:39:15 vetsrini noship $
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
298
299
300 --------------------------
301
302 FUNCTION get_court_order_details
303 (p_assignment_id IN NUMBER
304 ,p_effective_date IN DATE
305 ,p_reserved_amount OUT NOCOPY NUMBER
306 ,p_disdraint_amount OUT NOCOPY NUMBER
307 ,p_suspension_flag OUT NOCOPY VARCHAR2
308 ) RETURN NUMBER IS
309 --
310
311 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
312 SELECT eev1.screen_entry_value screen_entry_value
313 FROM per_all_assignments_f asg1
314 ,per_all_assignments_f asg2
315 ,per_all_people_f per
316 ,pay_element_links_f el
317 ,pay_element_types_f et
318 ,pay_input_values_f iv1
319 ,pay_element_entries_f ee
320 ,pay_element_entry_values_f eev1
321 WHERE asg1.assignment_id = p_assignment_id
322 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
323 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
324 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
325 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
326 AND per.person_id = asg1.person_id
327 AND asg2.person_id = per.person_id
328 AND asg2.primary_flag = 'Y'
329 AND et.element_name = 'Court Order Information'
330 AND et.legislation_code = 'SE'
331 AND iv1.element_type_id = et.element_type_id
332 AND iv1.name = p_input_value
333 AND el.business_group_id = per.business_group_id
334 AND el.element_type_id = et.element_type_id
335 AND ee.assignment_id = asg2.assignment_id
336 AND ee.element_link_id = el.element_link_id
337 AND eev1.element_entry_id = ee.element_entry_id
338 AND eev1.input_value_id = iv1.input_value_id;
339
340 l_rec get_details%ROWTYPE;
341 ---
342 BEGIN
343 --
344 OPEN get_details(p_assignment_id , p_effective_date ,'Reserved Amount');
345 FETCH get_details INTO l_rec;
346 p_reserved_amount := l_rec.screen_entry_value ;
347 CLOSE get_details;
348
349
350 OPEN get_details(p_assignment_id , p_effective_date , 'Distraint Amount');
351 FETCH get_details INTO l_rec;
352 p_disdraint_amount := l_rec.screen_entry_value ;
353 CLOSE get_details;
354
355
356 OPEN get_details(p_assignment_id , p_effective_date ,'Suspension Flag');
357 FETCH get_details INTO l_rec;
358
359 p_suspension_flag := l_rec.screen_entry_value ;
360
361 CLOSE get_details;
362
363
364 --
365 RETURN 1;
366 exception
367 WHEN others THEN
368 RETURN 1;
369 --
370 END get_court_order_details;
371
372 ------------Mileage
373
374 FUNCTION GET_COMPANY_MILEAGE_LIMIT
375 (p_effective_date IN DATE
376 ,p_business_group_id IN NUMBER
377 ,p_tax_unit_id IN NUMBER
378 ,p_car_type IN VARCHAR2
379 ) RETURN NUMBER IS
380
381 CURSOR c_get_details(p_business_group_id NUMBER , p_tax_unit_id NUMBER, p_effective_date DATE) IS
382 SELECT hoi2.org_information1,hoi2.org_information2,hoi2.org_information3
383 FROM hr_organization_units o1
384 , hr_organization_information hoi1
385 , hr_organization_information hoi2
386 WHERE o1.business_group_id = p_business_group_id
387 AND hoi1.organization_id = o1.organization_id
388 AND hoi1.organization_id = p_tax_unit_id
389 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
390 AND hoi1.org_information_context = 'CLASS'
391 AND o1.organization_id = hoi2.organization_id
392 AND hoi2.ORG_INFORMATION_CONTEXT ='SE_COMPANY_MILEAGE_RATES'
393 AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information4) AND
394 nvl(fnd_date.canonical_to_date(hoi2.org_information5),to_date('31/12/4712','DD/MM/YYYY')) ;
395
396 l_rec c_get_details%ROWTYPE;
397 l_return NUMBER;
398
399 --
400 BEGIN
401
402 OPEN c_get_details(p_business_group_id,p_tax_unit_id,p_effective_date);
403 FETCH c_get_details INTO l_rec;
404 CLOSE c_get_details;
405
406 IF p_car_type ='PRIVATE CAR' THEN
407 l_return := l_rec.org_information1;
408 ELSIF p_car_type ='COMPANY PETROL CAR' THEN
409 l_return := l_rec.org_information2;
410 ELSIF p_car_type ='COMPANY DIESEL CAR' THEN
411 l_return := l_rec.org_information3;
412 END IF;
413
414
415 RETURN l_return;
416
417 EXCEPTION
418 WHEN others THEN
419 RETURN NULL;
420
421 END GET_COMPANY_MILEAGE_LIMIT;
422
423 ------------------------------------------------------------------------
424 -- Function GET_MESSAGE
425 -- This function is used to obtain a message.
426 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
427 -- If you want to set the value of a token called ELEMENT to Social Ins
428 -- the token parameter would be 'ELEMENT:Social Ins.'
429 ------------------------------------------------------------------------
430 function get_message
431 (p_product in varchar2
432 ,p_message_name in varchar2
433 ,p_token1 in varchar2 default null
434 ,p_token2 in varchar2 default null
435 ,p_token3 in varchar2 default null) return varchar2
436 is
437 l_message varchar2(2000);
438 l_token_name varchar2(20);
439 l_token_value varchar2(80);
440 l_colon_position number;
441 l_proc varchar2(72) ;
442 l_product varchar2(72);
443 --
444 begin
445 --
446 l_proc:= g_package||'.get_message' ;
447 hr_utility.set_location('Entered '||l_proc,5);
448 hr_utility.set_location('. Message Name: '||p_message_name,40);
449 IF p_product in ('800','801') THEN
450 l_product :=hr_general.get_application_short_name(p_product);
451 else
452 l_product :=p_product;
453 END IF;
454 fnd_message.set_name(l_product, p_message_name);
455 if p_token1 is not null then
456 /* Obtain token 1 name and value */
457 l_colon_position := instr(p_token1,':');
458 l_token_name := substr(p_token1,1,l_colon_position-1);
459 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
460 fnd_message.set_token(l_token_name, l_token_value);
461 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
462 end if;
463 if p_token2 is not null then
464 /* Obtain token 2 name and value */
465 l_colon_position := instr(p_token2,':');
466 l_token_name := substr(p_token2,1,l_colon_position-1);
467 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
468 fnd_message.set_token(l_token_name, l_token_value);
469 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
470 end if;
471 if p_token3 is not null then
472 /* Obtain token 3 name and value */
473 l_colon_position := instr(p_token3,':');
474 l_token_name := substr(p_token3,1,l_colon_position-1);
475 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
476 fnd_message.set_token(l_token_name, l_token_value);
477 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
478 end if;
479 l_message := substr(fnd_message.get,1,254);
480 hr_utility.set_location('leaving '||l_proc,100);
481 return l_message;
482 end get_message;
483
484
485
486 --------------------------------------------------------------------------
487 -- --
488 -- Name : get_IANA_charset --
489 -- Type : Function --
490 -- Access : Public --
491 -- Description : Function to IANA charset equivalent of --
492 -- NLS_CHARACTERSET --
493 -- Parameters : --
494 -- IN : N/A --
495 -- OUT : N/A --
496 -- RETURN : VARCHAR2 --
497 -- --
498 --------------------------------------------------------------------------
499 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
500 CURSOR csr_get_iana_charset IS
501 SELECT tag
502 FROM fnd_lookup_values
503 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
504 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
505 INSTR(USERENV('LANGUAGE'), '.') + 1)
506 AND language = 'US';
507
508 lv_iana_charset fnd_lookup_values.tag%type;
509 BEGIN
510 OPEN csr_get_iana_charset;
511 FETCH csr_get_iana_charset INTO lv_iana_charset;
512 CLOSE csr_get_iana_charset;
513
514 hr_utility.trace('IANA Charset = '||lv_iana_charset);
515 RETURN (lv_iana_charset);
516 END get_IANA_charset;
517
518
519
520
521 END hr_se_utility ;