1 PACKAGE BODY HR_FI_UTILITY AS
2 -- $Header: hrfiutil.pkb 120.2.12010000.2 2008/08/06 08:41:08 ubhat ship $
3 --
4 g_package varchar2(30) := 'hr_fi_utility';
5 FUNCTION per_fi_full_name(
6 p_first_name in varchar2
7 ,p_middle_names in varchar2
8 ,p_last_name in varchar2
9 ,p_known_as in varchar2
10 ,p_title in varchar2
11 ,p_suffix in varchar2
12 ,p_pre_name_adjunct in varchar2
13 ,p_per_information1 in varchar2
14 ,p_per_information2 in varchar2
15 ,p_per_information3 in varchar2
16 ,p_per_information4 in varchar2
17 ,p_per_information5 in varchar2
18 ,p_per_information6 in varchar2
19 ,p_per_information7 in varchar2
20 ,p_per_information8 in varchar2
21 ,p_per_information9 in varchar2
22 ,p_per_information10 in varchar2
23 ,p_per_information11 in varchar2
24 ,p_per_information12 in varchar2
25 ,p_per_information13 in varchar2
26 ,p_per_information14 in varchar2
27 ,p_per_information15 in varchar2
28 ,p_per_information16 in varchar2
29 ,p_per_information17 in varchar2
30 ,p_per_information18 in varchar2
31 ,p_per_information19 in varchar2
32 ,p_per_information20 in varchar2
33 ,p_per_information21 in varchar2
34 ,p_per_information22 in varchar2
35 ,p_per_information23 in varchar2
36 ,p_per_information24 in varchar2
37 ,p_per_information25 in varchar2
38 ,p_per_information26 in varchar2
39 ,p_per_information27 in varchar2
40 ,p_per_information28 in varchar2
41 ,p_per_information29 in varchar2
42 ,p_per_information30 in VARCHAR2
43 )
44 RETURN VARCHAR2 AS
45 --
46 l_full_name varchar2(240);
47 l_title varchar2(240);
48
49 --
50 BEGIN
51 --
52 l_full_name := p_last_name|| ' '||p_first_name|| ' ';
53 if p_middle_names is not null then
54 l_full_name := l_full_name|| p_middle_names||' ';
55 end if;
56 if p_title is not null then
57 l_title := hr_general.decode_lookup('TITLE',p_title);
58
59 l_full_name := l_full_name|| l_title||' ';
60 end if;
61 if p_known_as is not null then
62 l_full_name := l_full_name||'('|| p_known_as ||')';
63 end if;
64
65 return (rtrim(l_full_name));
66 --
67 END;
68 --
69 --
70 FUNCTION per_fi_order_name(
71 p_first_name in varchar2
72 ,p_middle_names in varchar2
73 ,p_last_name in varchar2
74 ,p_known_as in varchar2
75 ,p_title in varchar2
76 ,p_suffix in varchar2
77 ,p_pre_name_adjunct in varchar2
78 ,p_per_information1 in varchar2
79 ,p_per_information2 in varchar2
80 ,p_per_information3 in varchar2
81 ,p_per_information4 in varchar2
82 ,p_per_information5 in varchar2
83 ,p_per_information6 in varchar2
84 ,p_per_information7 in varchar2
85 ,p_per_information8 in varchar2
86 ,p_per_information9 in varchar2
87 ,p_per_information10 in varchar2
88 ,p_per_information11 in varchar2
89 ,p_per_information12 in varchar2
90 ,p_per_information13 in varchar2
91 ,p_per_information14 in varchar2
92 ,p_per_information15 in varchar2
93 ,p_per_information16 in varchar2
94 ,p_per_information17 in varchar2
95 ,p_per_information18 in varchar2
96 ,p_per_information19 in varchar2
97 ,p_per_information20 in varchar2
98 ,p_per_information21 in varchar2
99 ,p_per_information22 in varchar2
100 ,p_per_information23 in varchar2
101 ,p_per_information24 in varchar2
102 ,p_per_information25 in varchar2
103 ,p_per_information26 in varchar2
104 ,p_per_information27 in varchar2
105 ,p_per_information28 in varchar2
106 ,p_per_information29 in varchar2
107 ,p_per_information30 in VARCHAR2
108 )
109 RETURN VARCHAR2 AS
110 --
111 l_order_name varchar2(240);
112 --
113 BEGIN
114 --
115 l_order_name := p_last_name || ' ' || p_first_name;
116 return (rtrim(l_order_name));
117 --
118 END;
119
120 --
121 -- Validates the bank account number.
122 --
123 -- The format is as follows BC-ACCX where
124 --
125 -- BC = 6 Digits representing the Branch Code
126 -- X = 1 Digit representing the Validation Code
127 -- Acc = Between 2 to 7 Digits
128
129 FUNCTION validate_account_number
130 (p_account_number IN VARCHAR2) RETURN NUMBER AS
131 l_valid NUMBER;
132 l_strlen NUMBER;
133 l_calc NUMBER;
134 l_account_number VARCHAR2(15);
135 BEGIN
136 -- Account no length should be between 9 and 15 characters.
137 --
138 IF LENGTH(p_account_number) NOT BETWEEN 9 AND 15 THEN
139 RETURN 1;
140 END IF;
141 -- Check separators exist at the correct places within the account no.
142 --
143 IF SUBSTR(p_account_number,7,1) <> '-' THEN
144 RETURN 1;
145 END IF;
146
147 -- Ensure the ABI consists only of digits.
148 --
149 l_strlen:= LENGTH(p_account_number);
150 FOR i IN 1..l_strlen
151 LOOP
152 IF i <> 7 AND (SUBSTR(p_account_number,i,1) < '0'
153 OR SUBSTR(p_account_number,i,1) > '9') then
154 l_valid :=1;
155 END IF;
156
157 END LOOP;
158 IF l_valid =1 THEN
159 RETURN 1 ;
160 END IF;
161
162 -- Ensure the Branch Code is Correct
163 --
164 IF substr(p_account_number,1,1) not in ('1','2','3','4','5','6','8') THEN
165 RETURN 1;
166 END IF;
167
168 -- Populate the Account No Upto 15 Digits
169 --
170 IF substr(p_account_number,1,1) in ('1','2','3','6','8') THEN
171 l_account_number:=substr(p_account_number,1,6)||
172 LPAD(substr(p_account_number,8,8),8,'0');
173
174 ELSE
175 l_account_number:=substr(p_account_number,1,6)||substr(p_account_number,8,1)||
176 LPAD(substr(p_account_number,9,7),7,'0');
177 END IF;
178
179 -- Calculate the Weights of the Products using the weighted coefficients of the
180 --Lunh Modulus 10
181 -- Use weights 2, 1, 2, 1, 2, 1 ? from right to left for first 13 Digits
182 --
183
184
185 l_calc :=nvl(substr((substr(l_account_number,1,1)* 2),1,1),0)
186 + nvl(substr((substr(l_account_number,1,1)* 2),2,1),0)
187 + nvl(substr((substr(l_account_number,2,1)* 1),1,1),0)
188 + nvl(substr((substr(l_account_number,2,1)* 1),2,1),0)
189 + nvl(substr((substr(l_account_number,3,1)* 2),1,1),0)
190 + nvl(substr((substr(l_account_number,3,1)* 2),2,1),0)
191 + nvl(substr((substr(l_account_number,4,1)* 1),1,1),0)
192 + nvl(substr((substr(l_account_number,4,1)* 1),2,1),0)
193 + nvl(substr((substr(l_account_number,5,1)* 2),1,1),0)
194 + nvl(substr((substr(l_account_number,5,1)* 2),2,1),0)
195 + nvl(substr((substr(l_account_number,6,1)* 1),1,1),0)
196 + nvl(substr((substr(l_account_number,6,1)* 1),2,1),0)
197 + nvl(substr((substr(l_account_number,7,1)* 2),1,1),0)
198 + nvl(substr((substr(l_account_number,7,1)* 2),2,1),0)
199 + nvl(substr((substr(l_account_number,8,1)* 1),1,1),0)
200 + nvl(substr((substr(l_account_number,8,1)* 1),2,1),0)
201 + nvl(substr((substr(l_account_number,9,1)* 2),1,1),0)
202 + nvl(substr((substr(l_account_number,9,1)* 2),2,1),0)
203 + nvl(substr((substr(l_account_number,10,1)* 1),1,1),0)
204 + nvl(substr((substr(l_account_number,10,1)* 1),2,1),0)
205 + nvl(substr((substr(l_account_number,11,1)* 2),1,1),0)
206 + nvl(substr((substr(l_account_number,11,1)* 2),2,1),0)
207 + nvl(substr((substr(l_account_number,12,1)* 1),1,1),0)
208 + nvl(substr((substr(l_account_number,12,1)* 1),2,1),0)
209 + nvl(substr((substr(l_account_number,13,1)* 2),1,1),0)
210 + nvl(substr((substr(l_account_number,13,1)* 2),2,1),0) ;
211
212 IF l_calc < 10 THEN
213 l_calc := 10 - l_calc ;
214
215 ELSIF mod(l_calc,10) = 0 THEN
216 l_calc := 0 ;
217 ELSE
218 l_calc :=l_calc + 10 - (mod(l_calc,10) + l_calc ) ;
219 END IF;
220 -- Verify the Validation Code
221 --
222
223 IF substr(l_account_number,14,1)= l_calc THEN
224 return 0;
225 ELSE
226 return 1 ;
227 END IF;
228 END ;
229
230 FUNCTION get_retirement_information
231 ( p_person_id IN NUMBER
232 , p_date IN DATE
233 , p_retire_information_code IN VARCHAR2 )
234 RETURN VARCHAR2 AS
235 l_dob Date;
236 l_retire_date VARCHAR2(150) ;
237 l_return_value VARCHAR2(150) ;
238
239 CURSOR c_retire IS
240 SELECT DATE_OF_BIRTH,PER_INFORMATION8
241 FROM PER_ALL_PEOPLE_F
242 WHERE PERSON_ID=p_person_id
243 AND p_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE ;
244 BEGIN
245 OPEN c_retire;
246 FETCH c_retire INTO l_dob,l_retire_date;
247 CLOSE c_retire;
248 IF p_retire_information_code='RD' THEN
249 l_return_value :=l_retire_date;
250 ELSIF p_retire_information_code='RA' THEN
251 l_return_value :=FLOOR((fnd_date.canonical_to_date(l_retire_date)-l_dob)/365);
252 END IF;
253 RETURN l_return_value;
254
255 END get_retirement_information;
256
257
258 -- Checks whether the input is a valid date.
259 --
260 FUNCTION chk_valid_date (p_date IN VARCHAR2,p_century IN VARCHAR2 )
261 RETURN VARCHAR2
262 AS
263 l_date DATE;
264 l_century NUMBER;
265 BEGIN
266 IF p_century='+' THEN
267 l_century:=18 ;
268 ELSIF p_century='-' THEN
269 l_century:=19 ;
270 ELSIF p_century='A' THEN
271 l_century:=20 ;
272 ELSE
273 RETURN '0' ;
274 END IF;
275 l_date:=to_date(substr(p_date,1,4)||l_century||substr(p_date,5,2),'DDMMYYYY');
276 RETURN '1';
277 exception
278 WHEN others THEN
279 RETURN '0';
280 end ;
281
282 -- Function : get_employment_information
283 -- Parameters : assignment_id - p_assignment_id,
284 -- employment information code - l_information_code.
285 -- Description : The function returns the employment information based on the assignment id
286 -- and the information code parameters. The information is first searced for at
287 -- the assignment level through the HR_Organization level , Local Unit level ,
288 -- Legal Employer Level to the Business group level.
289 --
290 -- The values for p_emp_information_code can be
291 -- FI_EMPLOYMENT_TYPE for Employment Type
292 -- FI_WORKING_TIME_TYPE for Working Time Type
293 -- FI_SHIFT_WORK_TYPE for Shift Work Type
294 -- FI_SHIFT_WORK_TYPE_DAYS for Shift Work Type Days
295 -- FI_COM_PRICE_CAT for Community Price Category
296 -- FI_EMPLOYEE_STATUS for Employee Status
297 -- FI_PERSONNEL_GRP for Personnel Group
298 -- FI_INS_OCC_GRP for Insurance Occupational Group
299 -- FI_EMPR_OCC_GRP forEmployer Union Occupational Group
300
301
302 function get_employment_information
303 (p_assignment_id IN NUMBER
304 ,p_emp_information_code IN VARCHAR2 ) RETURN VARCHAR2 IS
305 -- local variables declaration --
306 l_scl_id NUMBER(5);
307 l_information varchar2(150);
308 l_local_unit number(15);
309 l_legal_employer number(15);
310 l_org_id number(15);
311 l_bg_id number(15);
312 l_information_code varchar2(50);
313 l_organization_id number(15);
314
315 cursor get_scl_id is
316 select SOFT_CODING_KEYFLEX_ID
317 from PER_ALL_ASSIGNMENTS_F
318 where assignment_id = p_assignment_id;
319
320 cursor get_info_from_scl is
321 select lookups.meaning
322 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
323 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
324 and lookups.lookup_type=l_information_code
325 and lookups.enabled_flag = 'Y'
326 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',scl.segment3,
327 'FI_WORKING_TIME_TYPE',scl.segment4,
328 'FI_SHIFT_WORK_TYPE',scl.segment5,
329 'FI_SHIFT_WORK_TYPE_DAYS',scl.segment6,
330 'FI_COM_PRICE_CAT',scl.segment7,
331 'FI_EMPLOYEE_STATUS',scl.segment8,
332 'FI_PERSONNEL_GRP',scl.segment9,
333 'FI_INS_OCC_GRP',scl.segment10,
334 'FI_EMPR_OCC_GRP',scl.segment11,null);
335
336
337 cursor get_local_unit is
338 select segment2
339 from hr_soft_coding_keyflex
340 where soft_coding_keyflex_id = l_scl_id;
341
342
343 cursor get_info_from_local_unit is
344 select lookups.meaning
345 from hr_organization_information hoi , hr_lookups lookups
346 where hoi.organization_id = l_org_id
347 and hoi.org_information_context = 'FI_EMPLOYMENT_DEFAULTS'
348 and lookups.lookup_type = l_information_code
349 and lookups.enabled_flag = 'Y'
350 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',hoi.org_information1,
351 'FI_WORKING_TIME_TYPE',hoi.org_information2,
352 'FI_SHIFT_WORK_TYPE',hoi.org_information3,
353 'FI_SHIFT_WORK_TYPE_DAYS',hoi.org_information4,
354 'FI_COM_PRICE_CAT',hoi.org_information5,null);
355
356 cursor get_legal_employer is
357 select hoi2.organization_id
358 from hr_organization_information hoi1 , hr_organization_information hoi2
359 where hoi1.org_information1 = to_char(l_local_unit) and hoi1.org_information_context = 'FI_LOCAL_UNITS'
360 and hoi2.org_information_context = 'CLASS' and hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
361 and hoi2.organization_id = hoi1.organization_id;
362
363 cursor get_info_from_org is
364 select lookups.meaning
365 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
366 where hou.organization_id = l_organization_id
367 and hou.organization_id = hoi.organization_id
368 and hoi.org_information_context = 'FI_EMPLOYMENT_DEFAULTS'
369 and lookups.lookup_type = l_information_code
370 and lookups.enabled_flag = 'Y'
371 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',hoi.org_information1,
372 'FI_WORKING_TIME_TYPE',hoi.org_information2,
373 'FI_SHIFT_WORK_TYPE',hoi.org_information3,
374 'FI_SHIFT_WORK_TYPE_DAYS',hoi.org_information4,
375 'FI_COM_PRICE_CAT',hoi.org_information5,null);
376
377 cursor get_bg_id is
378 select business_group_id
379 from hr_organization_units
380 where organization_id = l_organization_id;
381
382
383 begin
384
385 if p_emp_information_code not in ('EMPLOYMENT_TYPE','WORKING_TIME_TYPE',
386 'SHIFT_WORK_TYPE','SHIFT_WORK_DAYS','COM_PRICE_CAT','EMPLOYEE_STATUS','PERSONNEL_GRP','INS_OCC_GRP','EMPR_OCC_GRP') then
387 return 'ERR_WRONG_PARAMETER';
388 end if;
389
390 l_information_code := 'FI_'||p_emp_information_code;
391
392 ---------------------------------------------------------------------------------
393 -- To return information other than Agreed working hours --
394 ---------------------------------------------------------------------------------
395
396
397 --------------------------------------
398 --Try at the Assignment Level --
399 --------------------------------------
400
401 -- get scl id --
402 open get_scl_id;
403 fetch get_scl_id into l_scl_id;
404 close get_scl_id;
405
406 if l_scl_id is not null then
407 -- get information at assignment level --
408 open get_info_from_scl;
409 fetch get_info_from_scl into l_information;
410 close get_info_from_scl;
411 if l_information is not null then
412 return l_information;
413 end if;
414 end if;
415
416 if p_emp_information_code in ('EMPLOYMENT_TYPE','WORKING_TIME_TYPE','SHIFT_WORK_TYPE','SHIFT_WORK_DAYS','COM_PRICE_CAT') then
417 --------------------------------------
418 --Try at the Local Unit Level --
419 --------------------------------------
420 -- get local unit id --
421 open get_local_unit;
422 fetch get_local_unit into l_local_unit;
423 close get_local_unit;
424
425 -- get information at local unit level --
426 l_org_id := l_local_unit;
427 open get_info_from_local_unit;
428 fetch get_info_from_local_unit into l_information;
429 close get_info_from_local_unit;
430
431 if l_information is not null then
432 return l_information;
433 end if;
434
435 ------------------------------------------
436 --Try at the Legal Employer Level --
437 ------------------------------------------
438 -- get legal employer id --
439 open get_legal_employer;
440 fetch get_legal_employer into l_legal_employer;
441 close get_legal_employer;
442
443 -- the cursor for local unit can be reused--
444 l_org_id := l_legal_employer;
445 open get_info_from_local_unit;
446 fetch get_info_from_local_unit into l_information;
447 close get_info_from_local_unit;
448
449 if l_information is not null then
450 return l_information;
451 end if;
452
453 ------------------------------------------
454 --Try at the Business Group Level --
455 ------------------------------------------
456 -- get bg id --
457 open get_bg_id;
458 fetch get_bg_id into l_bg_id;
459 close get_bg_id;
460
461 -- search at bg level--
462 -- the value in l_organization_id will no longer be necessary --
463 -- storing bg_id in l_organization_id --
464
465 l_organization_id := l_bg_id;
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
474 -- return null if the emp information is not present at any level --
475 return null;
476
477 end if;
478 end get_employment_information;
479
480 ------------------------------------------------------------------------
481 -- Function GET_VEHICLE_INFORMATION
482 -- This function is used to obtain vehicle information.
483 -- The input parameters are assignment id , business group and effective date.
484 -- The information being returned via out parameters are model year ,price
485 -- and engine capacity (cc).
486 ------------------------------------------------------------------------
487
488
489
490 FUNCTION get_vehicle_information
491 (p_assignment_id in number
492 ,p_business_group_id in number
493 ,p_effective_date in date
494 ,p_vehicle_allot_id in varchar2
495 ,p_model_year out nocopy number
496 ,p_price out nocopy number
497 ,p_engine_capacity_in_cc out nocopy number
498 ,p_vehicle_type out nocopy varchar2
499 ) RETURN NUMBER
500 AS
501
502 CURSOR get_vehicle_information IS
503 SELECT NVL(a.model_year,0)
504 , nvl(a.list_price,0)
505 + nvl(a.accessory_value_at_startdate,0)
506 + nvl(a.accessory_value_added_later ,0)
507 , a.engine_capacity_in_cc
508 , a.vehicle_type
509 FROM pqp_vehicle_repository_f a , pqp_vehicle_allocations_f b
510 WHERE a.VEHICLE_REPOSITORY_ID = b.VEHICLE_REPOSITORY_ID
511 AND p_effective_date between b.effective_start_date
512 AND b.effective_end_date
513 AND p_effective_date between a.effective_start_date
514 AND a.effective_end_date
515 AND b.assignment_id = p_assignment_id
516 AND b.business_group_id = p_business_group_id
517 AND b.vehicle_allocation_id = p_vehicle_allot_id;
518
519 BEGIN
520 OPEN get_vehicle_information ;
521 FETCH get_vehicle_information INTO p_model_year , p_price , p_engine_capacity_in_cc, p_vehicle_type ;
522 CLOSE get_vehicle_information ;
523
524 RETURN 1 ;
525
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 p_model_year := 0;
530 p_price := 0;
531 p_engine_capacity_in_cc := 0;
532 END;
533
534 ------------------------------------------------------------------------
535 -- Function GET_MESSAGE
536 -- This function is used to obtain a message.
537 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
538 -- If you want to set the value of a token called ELEMENT to Social Ins
539 -- the token parameter would be 'ELEMENT:Social Ins.'
540 ------------------------------------------------------------------------
541 function get_message
542 (p_product in varchar2
543 ,p_message_name in varchar2
544 ,p_token1 in varchar2 default null
545 ,p_token2 in varchar2 default null
546 ,p_token3 in varchar2 default null) return varchar2
547 is
548 l_message varchar2(2000);
549 l_token_name varchar2(20);
550 l_token_value varchar2(80);
551 l_colon_position number;
552 l_proc varchar2(72) := g_package||'.get_message';
553 --
554 begin
555 --
556 hr_utility.set_location('Entered '||l_proc,5);
557 hr_utility.set_location('. Message Name: '||p_message_name,40);
558 fnd_message.set_name(p_product, p_message_name);
559 if p_token1 is not null then
560 /* Obtain token 1 name and value */
561 l_colon_position := instr(p_token1,':');
562 l_token_name := substr(p_token1,1,l_colon_position-1);
563 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
564 fnd_message.set_token(l_token_name, l_token_value);
565 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
566 end if;
567 if p_token2 is not null then
568 /* Obtain token 2 name and value */
569 l_colon_position := instr(p_token2,':');
570 l_token_name := substr(p_token2,1,l_colon_position-1);
571 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
572 fnd_message.set_token(l_token_name, l_token_value);
573 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
574 end if;
575 if p_token3 is not null then
576 /* Obtain token 3 name and value */
577 l_colon_position := instr(p_token3,':');
578 l_token_name := substr(p_token3,1,l_colon_position-1);
579 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
580 fnd_message.set_token(l_token_name, l_token_value);
581 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
582 end if;
583 l_message := substr(fnd_message.get,1,254);
584 hr_utility.set_location('leaving '||l_proc,100);
585 return l_message;
586 end get_message;
587
588 ------------------------------------------------------------------------
589 -- Function get_dependent_number
590 -- This function is used to obtain the number of dependents of a person.
591 ------------------------------------------------------------------------
592
593 FUNCTION get_dependent_number
594 (p_assignment_id IN NUMBER
595 ,p_business_group_id IN NUMBER
596 ,p_process_date IN DATE
597 ) RETURN NUMBER IS
598
599 l_dependent_number NUMBER;
600
601 BEGIN
602
603 SELECT COUNT(distinct(c.contact_person_id))
604 INTO l_dependent_number
605 FROM per_contact_relationships c
606 WHERE c.person_id = (SELECT b.person_id from per_all_assignments_f b
607 WHERE b.assignment_id = p_assignment_id
608 AND p_process_date between b.effective_start_date AND b.effective_end_date
609 )
610 AND c.dependent_flag = 'Y'
611 AND c.business_group_id = p_business_group_id;
612
613 RETURN l_dependent_number;
614
615
616 EXCEPTION
617 WHEN others THEN
618 RETURN NULL;
619 END get_dependent_number;
620
621 ------------------------------------------------------------------------
622 -- Function Court Order Details
623 -- This function is used to obtain the number of dependents of a person.
624 ------------------------------------------------------------------------
625
626
627
628 FUNCTION get_court_order_details
629 (p_assignment_id IN NUMBER
630 ,p_effective_date IN DATE
631 ,p_dependent_number OUT NOCOPY NUMBER
632 ,p_third_party OUT NOCOPY NUMBER
633 ,p_court_order_amount OUT NOCOPY NUMBER
634 ,p_periodic_installment OUT NOCOPY NUMBER
635 ,p_number_of_installments OUT NOCOPY NUMBER
636 ,p_suspension_flag OUT NOCOPY VARCHAR2
637 ) RETURN NUMBER IS
638 --
639
640 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
641 SELECT eev1.screen_entry_value screen_entry_value
642 FROM per_all_assignments_f asg1
643 ,per_all_assignments_f asg2
644 ,per_all_people_f per
645 ,pay_element_links_f el
646 ,pay_element_types_f et
647 ,pay_input_values_f iv1
648 ,pay_element_entries_f ee
649 ,pay_element_entry_values_f eev1
650 WHERE asg1.assignment_id = p_assignment_id
651 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
652 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
653 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
654 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
655 AND per.person_id = asg1.person_id
656 AND asg2.person_id = per.person_id
657 AND asg2.primary_flag = 'Y'
658 AND et.element_name = 'Court Order Information'
659 AND et.legislation_code = 'FI'
660 AND iv1.element_type_id = et.element_type_id
661 AND iv1.name = p_input_value
662 AND el.business_group_id = per.business_group_id
663 AND el.element_type_id = et.element_type_id
664 AND ee.assignment_id = asg2.assignment_id
665 AND ee.element_link_id = el.element_link_id
666 AND eev1.element_entry_id = ee.element_entry_id
667 AND eev1.input_value_id = iv1.input_value_id;
668
669 l_rec get_details%ROWTYPE;
670 --
671 BEGIN
672 --
673 OPEN get_details(p_assignment_id , p_effective_date ,'Dependent Number');
674 FETCH get_details INTO l_rec;
675 p_dependent_number := l_rec.screen_entry_value ;
676 CLOSE get_details;
677
678
679
680 OPEN get_details(p_assignment_id , p_effective_date , 'Third Party');
681 FETCH get_details INTO l_rec;
682 p_third_party := l_rec.screen_entry_value ;
683 CLOSE get_details;
684
685
686
687 OPEN get_details(p_assignment_id , p_effective_date ,'Court Order Amount');
688 FETCH get_details INTO l_rec;
689 p_court_order_amount := l_rec.screen_entry_value ;
690 CLOSE get_details;
691
692
693
694 OPEN get_details(p_assignment_id , p_effective_date ,'Periodic Installment');
695 FETCH get_details INTO l_rec;
696 p_periodic_installment := l_rec.screen_entry_value ;
697 CLOSE get_details;
698
699
700
701 OPEN get_details(p_assignment_id , p_effective_date ,'Number Of Installments');
702 FETCH get_details INTO l_rec;
703 p_number_of_installments := l_rec.screen_entry_value ;
704 CLOSE get_details;
705
706
707
708 OPEN get_details(p_assignment_id , p_effective_date ,'Suspension Flag');
709 FETCH get_details INTO l_rec;
710
711 p_suspension_flag := l_rec.screen_entry_value ;
712
713 CLOSE get_details;
714
715
716 --
717 RETURN 1;
718 --
719 END get_court_order_details;
720
721 ------------------------------------------------------------------------
722 -- Function union details
723 -- This function is used to obtain the trade union details of a person.
724 ------------------------------------------------------------------------
725
726 FUNCTION get_union_details
727 (p_assignment_id IN NUMBER
728 ,p_effective_date IN DATE
729 ,p_fixed_union_fees OUT NOCOPY NUMBER
730 ,p_percentage_union_fees OUT NOCOPY NUMBER
731 ,p_payment_calculation_mode OUT NOCOPY VARCHAR2
732 ) RETURN NUMBER IS
733
734 CURSOR get_details(p_assignment_id NUMBER ,p_effective_date DATE ) IS
735 select hoi2.org_information2,hoi2.org_information3,hoi2.org_information4,pap1.per_information11
736 , pap1.per_information12 , pap1.per_information13
737 from HR_ORGANIZATION_UNITS o1
738 , HR_ORGANIZATION_INFORMATION hoi1
739 , HR_ORGANIZATION_INFORMATION hoi2
740 , per_all_people_f pap1
741 , per_all_assignments_f paa
742 WHERE hoi1.organization_id = o1.organization_id
743 and hoi1.org_information1 = 'FI_TRADE_UNION'
744 and hoi1.org_information_context = 'CLASS'
745 and hoi2.ORG_INFORMATION_CONTEXT='FI_TRADE_UNION_DETAILS'
746 and o1.organization_id = pap1.per_information9
747 and hoi1.organization_id = hoi2.organization_id
748 AND pap1.person_id = paa.person_id
749 AND o1.business_group_id = paa.business_group_id
750 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
751 AND p_effective_date BETWEEN nvl(fnd_date.canonical_to_date(pap1.per_information18),
752 hr_general.start_of_time) AND nvl(fnd_date.canonical_to_date(pap1.per_information19),
753 hr_general.end_of_time)
754 AND paa.assignment_id = p_assignment_id;
755
756 l_rec get_details%ROWTYPE;
757 --
758 BEGIN
759
760 OPEN get_details(p_assignment_id , p_effective_date);
761 FETCH get_details into l_rec;
762 CLOSE get_details;
763 IF l_rec.per_information11 is null THEN
764 p_fixed_union_fees := l_rec.org_information3 ;
765 p_percentage_union_fees := l_rec.org_information4 ;
766 p_payment_calculation_mode := l_rec.org_information2 ;
767 ELSE
768 p_payment_calculation_mode := l_rec.per_information11;
769 IF l_rec.per_information12 IS NULL THEN
770 p_fixed_union_fees := l_rec.org_information3 ;
771 ELSE
772
773 p_fixed_union_fees := l_rec.per_information12 ;
774 END IF;
775
776 IF l_rec.per_information13 IS NULL THEN
777 p_percentage_union_fees := l_rec.org_information4 ;
778 ELSE
779
780 p_percentage_union_fees := l_rec.per_information13 ;
781 END IF;
782
783 END IF;
784
785 IF p_payment_calculation_mode is null THEN
786 p_payment_calculation_mode :='N';
787 END IF;
788
789 RETURN 1;
790
791 EXCEPTION
792 WHEN others THEN
793 RETURN NULL;
794
795 END get_union_details;
796
797 --------------------------------------------------------------------------
798 -- --
799 -- Name : get_IANA_charset --
800 -- Type : Function --
801 -- Access : Public --
802 -- Description : Function to IANA charset equivalent of --
803 -- NLS_CHARACTERSET --
804 -- Parameters : --
805 -- IN : N/A --
806 -- OUT : N/A --
807 -- RETURN : VARCHAR2 --
808 -- --
809 --------------------------------------------------------------------------
810 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
811 CURSOR csr_get_iana_charset IS
812 SELECT tag
813 FROM fnd_lookup_values
814 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
815 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
816 INSTR(USERENV('LANGUAGE'), '.') + 1)
817 AND language = 'US';
818
819 lv_iana_charset fnd_lookup_values.tag%type;
820 BEGIN
821 OPEN csr_get_iana_charset;
822 FETCH csr_get_iana_charset INTO lv_iana_charset;
823 CLOSE csr_get_iana_charset;
824
825 hr_utility.trace('IANA Charset = '||lv_iana_charset);
826 RETURN (lv_iana_charset);
827 END get_IANA_charset;
828
829
830 END;
[Home] [Help]