1 PACKAGE BODY HR_FI_UTILITY AS
2 -- $Header: hrfiutil.pkb 120.2.12010000.5 2009/11/20 07:12:38 dchindar 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 ----
231 -- Function added for IBAN Validation
232 ----
233 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
234 BEGIN
235 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
236 RETURN 1;
237 else
238 RETURN 0;
239 END IF;
240 END validate_iban_acc;
241
242 ----
243 -- This function will get called from the bank keyflex field segments Bug 9127776
244 ----
245 FUNCTION validate_account_entered
246 (p_acc_no IN VARCHAR2,
247 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
248 --
249 l_ret NUMBER ;
250 begin
251 -- hr_utility.trace_on(null,'ACCVAL');
252 l_ret :=0;
253 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
254 hr_utility.set_location('p_account_number ' || p_acc_no,1);
255
256 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
257 l_ret := validate_account_number(p_acc_no);
258 hr_utility.set_location('l_ret ' || l_ret,1);
259 RETURN l_ret;
260 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
261 l_ret := validate_iban_acc(p_acc_no);
262 hr_utility.set_location('l_ret ' || l_ret,3);
263 RETURN l_ret;
264 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
265 hr_utility.set_location('Both Account Nos Null',4);
266 RETURN 1;
267 ELSE
268 hr_utility.set_location('l_ret: 3 ' ,5);
269 RETURN 3;
270 END if;
271 End validate_account_entered;
272
273
274 FUNCTION get_retirement_information
275 ( p_person_id IN NUMBER
276 , p_date IN DATE
277 , p_retire_information_code IN VARCHAR2 )
278 RETURN VARCHAR2 AS
279 l_dob Date;
280 l_retire_date VARCHAR2(150) ;
281 l_return_value VARCHAR2(150) ;
282
283 CURSOR c_retire IS
284 SELECT DATE_OF_BIRTH,PER_INFORMATION8
285 FROM PER_ALL_PEOPLE_F
286 WHERE PERSON_ID=p_person_id
287 AND p_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE ;
288 BEGIN
289 OPEN c_retire;
290 FETCH c_retire INTO l_dob,l_retire_date;
291 CLOSE c_retire;
292 IF p_retire_information_code='RD' THEN
293 l_return_value :=l_retire_date;
294 ELSIF p_retire_information_code='RA' THEN
295 l_return_value :=FLOOR((fnd_date.canonical_to_date(l_retire_date)-l_dob)/365);
296 END IF;
297 RETURN l_return_value;
298
299 END get_retirement_information;
300
301
302 -- Checks whether the input is a valid date.
303 --
304 FUNCTION chk_valid_date (p_date IN VARCHAR2,p_century IN VARCHAR2 )
305 RETURN VARCHAR2
306 AS
307 l_date DATE;
308 l_century NUMBER;
309 BEGIN
310 IF p_century='+' THEN
311 l_century:=18 ;
312 ELSIF p_century='-' THEN
313 l_century:=19 ;
314 ELSIF p_century='A' THEN
315 l_century:=20 ;
316 ELSE
317 RETURN '0' ;
318 END IF;
319 l_date:=to_date(substr(p_date,1,4)||l_century||substr(p_date,5,2),'DDMMYYYY');
320 RETURN '1';
321 exception
322 WHEN others THEN
323 RETURN '0';
324 end ;
325
326 -- Function : get_employment_information
327 -- Parameters : assignment_id - p_assignment_id,
328 -- employment information code - l_information_code.
329 -- Description : The function returns the employment information based on the assignment id
330 -- and the information code parameters. The information is first searced for at
331 -- the assignment level through the HR_Organization level , Local Unit level ,
332 -- Legal Employer Level to the Business group level.
333 --
334 -- The values for p_emp_information_code can be
335 -- FI_EMPLOYMENT_TYPE for Employment Type
336 -- FI_WORKING_TIME_TYPE for Working Time Type
337 -- FI_SHIFT_WORK_TYPE for Shift Work Type
338 -- FI_SHIFT_WORK_TYPE_DAYS for Shift Work Type Days
339 -- FI_COM_PRICE_CAT for Community Price Category
340 -- FI_EMPLOYEE_STATUS for Employee Status
341 -- FI_PERSONNEL_GRP for Personnel Group
342 -- FI_INS_OCC_GRP for Insurance Occupational Group
343 -- FI_EMPR_OCC_GRP forEmployer Union Occupational Group
344
345
346 function get_employment_information
347 (p_assignment_id IN NUMBER
348 ,p_emp_information_code IN VARCHAR2 ) RETURN VARCHAR2 IS
349 -- local variables declaration --
350 l_scl_id NUMBER(5);
351 l_information varchar2(150);
352 l_local_unit number(15);
353 l_legal_employer number(15);
354 l_org_id number(15);
355 l_bg_id number(15);
356 l_information_code varchar2(50);
357 l_organization_id number(15);
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_info_from_scl is
365 select lookups.meaning
366 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
367 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
368 and lookups.lookup_type=l_information_code
369 and lookups.enabled_flag = 'Y'
370 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',scl.segment3,
371 'FI_WORKING_TIME_TYPE',scl.segment4,
372 'FI_SHIFT_WORK_TYPE',scl.segment5,
373 'FI_SHIFT_WORK_TYPE_DAYS',scl.segment6,
374 'FI_COM_PRICE_CAT',scl.segment7,
375 'FI_EMPLOYEE_STATUS',scl.segment8,
376 'FI_PERSONNEL_GRP',scl.segment9,
377 'FI_INS_OCC_GRP',scl.segment10,
378 'FI_EMPR_OCC_GRP',scl.segment11,null);
379
380
381 cursor get_local_unit is
382 select segment2
383 from hr_soft_coding_keyflex
384 where soft_coding_keyflex_id = l_scl_id;
385
386
387 cursor get_info_from_local_unit is
388 select lookups.meaning
389 from hr_organization_information hoi , hr_lookups lookups
390 where hoi.organization_id = l_org_id
391 and hoi.org_information_context = 'FI_EMPLOYMENT_DEFAULTS'
392 and lookups.lookup_type = l_information_code
393 and lookups.enabled_flag = 'Y'
394 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',hoi.org_information1,
395 'FI_WORKING_TIME_TYPE',hoi.org_information2,
396 'FI_SHIFT_WORK_TYPE',hoi.org_information3,
397 'FI_SHIFT_WORK_TYPE_DAYS',hoi.org_information4,
398 'FI_COM_PRICE_CAT',hoi.org_information5,null);
399
400 cursor get_legal_employer is
401 select hoi2.organization_id
402 from hr_organization_information hoi1 , hr_organization_information hoi2
403 where hoi1.org_information1 = to_char(l_local_unit) and hoi1.org_information_context = 'FI_LOCAL_UNITS'
404 and hoi2.org_information_context = 'CLASS' and hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
405 and hoi2.organization_id = hoi1.organization_id;
406
407 cursor get_info_from_org is
408 select lookups.meaning
409 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
410 where hou.organization_id = l_organization_id
411 and hou.organization_id = hoi.organization_id
412 and hoi.org_information_context = 'FI_EMPLOYMENT_DEFAULTS'
413 and lookups.lookup_type = l_information_code
414 and lookups.enabled_flag = 'Y'
415 and lookups.lookup_code = decode(l_information_code,'FI_EMPLOYMENT_TYPE',hoi.org_information1,
416 'FI_WORKING_TIME_TYPE',hoi.org_information2,
417 'FI_SHIFT_WORK_TYPE',hoi.org_information3,
418 'FI_SHIFT_WORK_TYPE_DAYS',hoi.org_information4,
419 'FI_COM_PRICE_CAT',hoi.org_information5,null);
420
421 cursor get_bg_id is
422 select business_group_id
423 from hr_organization_units
424 where organization_id = l_organization_id;
425
426
427 begin
428
429 if p_emp_information_code not in ('EMPLOYMENT_TYPE','WORKING_TIME_TYPE',
430 'SHIFT_WORK_TYPE','SHIFT_WORK_DAYS','COM_PRICE_CAT','EMPLOYEE_STATUS','PERSONNEL_GRP','INS_OCC_GRP','EMPR_OCC_GRP') then
431 return 'ERR_WRONG_PARAMETER';
432 end if;
433
434 l_information_code := 'FI_'||p_emp_information_code;
435
436 ---------------------------------------------------------------------------------
437 -- To return information other than Agreed working hours --
438 ---------------------------------------------------------------------------------
439
440
441 --------------------------------------
442 --Try at the Assignment Level --
443 --------------------------------------
444
445 -- get scl id --
446 open get_scl_id;
447 fetch get_scl_id into l_scl_id;
448 close get_scl_id;
449
450 if l_scl_id is not null then
451 -- get information at assignment level --
452 open get_info_from_scl;
453 fetch get_info_from_scl into l_information;
454 close get_info_from_scl;
455 if l_information is not null then
456 return l_information;
457 end if;
458 end if;
459
460 if p_emp_information_code in ('EMPLOYMENT_TYPE','WORKING_TIME_TYPE','SHIFT_WORK_TYPE','SHIFT_WORK_DAYS','COM_PRICE_CAT') then
461 --------------------------------------
462 --Try at the Local Unit Level --
463 --------------------------------------
464 -- get local unit id --
465 open get_local_unit;
466 fetch get_local_unit into l_local_unit;
467 close get_local_unit;
468
469 -- get information at local unit level --
470 l_org_id := l_local_unit;
471 open get_info_from_local_unit;
472 fetch get_info_from_local_unit into l_information;
473 close get_info_from_local_unit;
474
475 if l_information is not null then
476 return l_information;
477 end if;
478
479 ------------------------------------------
480 --Try at the Legal Employer Level --
481 ------------------------------------------
482 -- get legal employer id --
483 open get_legal_employer;
484 fetch get_legal_employer into l_legal_employer;
485 close get_legal_employer;
486
487 -- the cursor for local unit can be reused--
488 l_org_id := l_legal_employer;
489 open get_info_from_local_unit;
490 fetch get_info_from_local_unit into l_information;
491 close get_info_from_local_unit;
492
493 if l_information is not null then
494 return l_information;
495 end if;
496
497 ------------------------------------------
498 --Try at the Business Group Level --
499 ------------------------------------------
500 -- get bg id --
501 open get_bg_id;
502 fetch get_bg_id into l_bg_id;
503 close get_bg_id;
504
505 -- search at bg level--
506 -- the value in l_organization_id will no longer be necessary --
507 -- storing bg_id in l_organization_id --
508
509 l_organization_id := l_bg_id;
510 open get_info_from_org;
511 fetch get_info_from_org into l_information;
512 close get_info_from_org;
513
514 if l_information is not null then
515 return l_information;
516 end if;
517
518 -- return null if the emp information is not present at any level --
519 return null;
520
521 end if;
522 end get_employment_information;
523
524 ------------------------------------------------------------------------
525 -- Function GET_VEHICLE_INFORMATION
526 -- This function is used to obtain vehicle information.
527 -- The input parameters are assignment id , business group and effective date.
528 -- The information being returned via out parameters are model year ,price
529 -- and engine capacity (cc).
530 ------------------------------------------------------------------------
531
532
533
534 FUNCTION get_vehicle_information
535 (p_assignment_id in number
536 ,p_business_group_id in number
537 ,p_effective_date in date
538 ,p_vehicle_allot_id in varchar2
539 ,p_model_year out nocopy number
540 ,p_price out nocopy number
541 ,p_engine_capacity_in_cc out nocopy number
542 ,p_vehicle_type out nocopy varchar2
543 ) RETURN NUMBER
544 AS
545
546 CURSOR get_vehicle_information IS
547 SELECT NVL(a.model_year,0)
548 , nvl(a.list_price,0)
549 + nvl(a.accessory_value_at_startdate,0)
550 + nvl(a.accessory_value_added_later ,0)
551 , a.engine_capacity_in_cc
552 , a.vehicle_type
553 FROM pqp_vehicle_repository_f a , pqp_vehicle_allocations_f b
554 WHERE a.VEHICLE_REPOSITORY_ID = b.VEHICLE_REPOSITORY_ID
555 AND p_effective_date between b.effective_start_date
556 AND b.effective_end_date
557 AND p_effective_date between a.effective_start_date
558 AND a.effective_end_date
559 AND b.assignment_id = p_assignment_id
560 AND b.business_group_id = p_business_group_id
561 AND b.vehicle_allocation_id = p_vehicle_allot_id;
562
563 BEGIN
564 OPEN get_vehicle_information ;
565 FETCH get_vehicle_information INTO p_model_year , p_price , p_engine_capacity_in_cc, p_vehicle_type ;
566 CLOSE get_vehicle_information ;
567
568 RETURN 1 ;
569
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 p_model_year := 0;
574 p_price := 0;
575 p_engine_capacity_in_cc := 0;
576 END;
577
578 ------------------------------------------------------------------------
579 -- Function GET_MESSAGE
580 -- This function is used to obtain a message.
581 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
582 -- If you want to set the value of a token called ELEMENT to Social Ins
583 -- the token parameter would be 'ELEMENT:Social Ins.'
584 ------------------------------------------------------------------------
585 function get_message
586 (p_product in varchar2
587 ,p_message_name in varchar2
588 ,p_token1 in varchar2 default null
589 ,p_token2 in varchar2 default null
590 ,p_token3 in varchar2 default null) return varchar2
591 is
592 l_message varchar2(2000);
593 l_token_name varchar2(20);
594 l_token_value varchar2(80);
595 l_colon_position number;
596 l_proc varchar2(72) := g_package||'.get_message';
597 --
598 begin
599 --
600 hr_utility.set_location('Entered '||l_proc,5);
601 hr_utility.set_location('. Message Name: '||p_message_name,40);
602 fnd_message.set_name(p_product, p_message_name);
603 if p_token1 is not null then
604 /* Obtain token 1 name and value */
605 l_colon_position := instr(p_token1,':');
606 l_token_name := substr(p_token1,1,l_colon_position-1);
607 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
608 fnd_message.set_token(l_token_name, l_token_value);
609 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
610 end if;
611 if p_token2 is not null then
612 /* Obtain token 2 name and value */
613 l_colon_position := instr(p_token2,':');
614 l_token_name := substr(p_token2,1,l_colon_position-1);
615 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
616 fnd_message.set_token(l_token_name, l_token_value);
617 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
618 end if;
619 if p_token3 is not null then
620 /* Obtain token 3 name and value */
621 l_colon_position := instr(p_token3,':');
622 l_token_name := substr(p_token3,1,l_colon_position-1);
623 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
624 fnd_message.set_token(l_token_name, l_token_value);
625 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
626 end if;
627 l_message := substr(fnd_message.get,1,254);
628 hr_utility.set_location('leaving '||l_proc,100);
629 return l_message;
630 end get_message;
631
632 ------------------------------------------------------------------------
633 -- Function get_dependent_number
634 -- This function is used to obtain the number of dependents of a person.
635 ------------------------------------------------------------------------
636
637 FUNCTION get_dependent_number
638 (p_assignment_id IN NUMBER
639 ,p_business_group_id IN NUMBER
640 ,p_process_date IN DATE
641 ) RETURN NUMBER IS
642
643 l_dependent_number NUMBER;
644
645 BEGIN
646
647 SELECT COUNT(distinct(c.contact_person_id))
648 INTO l_dependent_number
649 FROM per_contact_relationships c
650 WHERE c.person_id = (SELECT b.person_id from per_all_assignments_f b
651 WHERE b.assignment_id = p_assignment_id
652 AND p_process_date between b.effective_start_date AND b.effective_end_date
653 )
654 AND c.dependent_flag = 'Y'
655 AND c.business_group_id = p_business_group_id;
656
657 RETURN l_dependent_number;
658
659
660 EXCEPTION
661 WHEN others THEN
662 RETURN NULL;
663 END get_dependent_number;
664
665
666 -----------------------------------------------------------------------------------------------------------+++
667 ------------------------------------------------------------------------
668 -- Function check_Contract_Reasons
669 -- This function is used to check_Contract_Reasons
670 ------------------------------------------------------------------------
671 -- Added enhancements w.r.t Bug 8425533
672
673 FUNCTION check_Contract_Reasons
674 (p_assignment_id IN NUMBER
675 ,p_contract_type IN VARCHAR2
676 ) RETURN NUMBER IS
677
678 l_reasons_value NUMBER;
679
680 CURSOR csr_chk_contract_reasons(
681 csr_v_assignment_id NUMBER,
682 csr_v_information_type VARCHAR2
683 ) IS
684 SELECT AEI_INFORMATION1 Reason
685 FROM per_assignment_extra_info
686 WHERE assignment_id = csr_v_assignment_id
687 AND INFORMATION_TYPE = csr_v_information_type;
688
689 lr_chk_reasons csr_chk_contract_reasons%ROWTYPE;
690
691
692 CURSOR csr_soft_coded_keyflex_info (
693 csr_v_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE
694 ) IS
695 SELECT SEGMENT3 l_Employment_Type,
696 SEGMENT4 l_Working_Time_Type
697 FROM hr_soft_coding_keyflex
698 WHERE soft_coding_keyflex_id = csr_v_soft_coding_keyflex_id;
699
700 lr_soft_coded_keyflex_info csr_soft_coded_keyflex_info%ROWTYPE;
701
702
703 CURSOR csr_get_soft_coded_kf_id (
704 csr_v_assignment_id NUMBER
705 ) IS
706 SELECT max(SOFT_CODING_KEYFLEX_ID) l_soft_code_kf_id
707 FROM per_all_assignments_f
708 WHERE assignment_id = csr_v_assignment_id;
709
710 lr_soft_coded_kf_id csr_get_soft_coded_kf_id%ROWTYPE;
711
712 BEGIN
713 OPEN csr_get_soft_coded_kf_id(p_assignment_id);
714 FETCH csr_get_soft_coded_kf_id INTO lr_soft_coded_kf_id;
715 CLOSE csr_get_soft_coded_kf_id;
716
717 OPEN csr_soft_coded_keyflex_info(lr_soft_coded_kf_id.l_soft_code_kf_id);
718 FETCH csr_soft_coded_keyflex_info INTO lr_soft_coded_keyflex_info;
719 CLOSE csr_soft_coded_keyflex_info;
720
721
722
723 IF p_contract_type = 'Time-Fixed' THEN
724
725 OPEN csr_chk_contract_reasons(p_assignment_id,'FI_EMPLOYMENT_TYPE_REASON');
726 FETCH csr_chk_contract_reasons INTO lr_chk_reasons;
727 CLOSE csr_chk_contract_reasons;
728
729 IF lr_soft_coded_keyflex_info.l_Employment_Type = 2 AND lr_chk_reasons.Reason IS NULL THEN
730 l_reasons_value := 0;
731 ELSE
732 l_reasons_value := 1;
733 END IF;
734 END IF;
735
736 IF p_contract_type = 'Part-Time' THEN
737
738 OPEN csr_chk_contract_reasons(p_assignment_id,'FI_WORKING_TIME_TYPE_REASON');
739 FETCH csr_chk_contract_reasons INTO lr_chk_reasons;
740 CLOSE csr_chk_contract_reasons;
741
742 IF lr_soft_coded_keyflex_info.l_Working_Time_Type = 5 AND lr_chk_reasons.Reason IS NULL THEN
743 l_reasons_value := 0;
744 ELSE
745 l_reasons_value := 1;
746 END IF;
747 END IF;
748
749
750 RETURN l_reasons_value;
751
752 EXCEPTION
753 WHEN others THEN
754 RETURN NULL;
755 END check_Contract_Reasons;
756
757 -----------------------------------------------------------------------------------------------------------+++
758
759
760 ------------------------------------------------------------------------
761 -- Function Court Order Details
762 -- This function is used to obtain the number of dependents of a person.
763 ------------------------------------------------------------------------
764
765
766
767 FUNCTION get_court_order_details
768 (p_assignment_id IN NUMBER
769 ,p_effective_date IN DATE
770 ,p_dependent_number OUT NOCOPY NUMBER
771 ,p_third_party OUT NOCOPY NUMBER
772 ,p_court_order_amount OUT NOCOPY NUMBER
773 ,p_periodic_installment OUT NOCOPY NUMBER
774 ,p_number_of_installments OUT NOCOPY NUMBER
775 ,p_suspension_flag OUT NOCOPY VARCHAR2
776 ) RETURN NUMBER IS
777 --
778
779 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
780 SELECT eev1.screen_entry_value screen_entry_value
781 FROM per_all_assignments_f asg1
782 ,per_all_assignments_f asg2
783 ,per_all_people_f per
784 ,pay_element_links_f el
785 ,pay_element_types_f et
786 ,pay_input_values_f iv1
787 ,pay_element_entries_f ee
788 ,pay_element_entry_values_f eev1
789 WHERE asg1.assignment_id = p_assignment_id
790 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
791 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
792 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
793 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
794 AND per.person_id = asg1.person_id
795 AND asg2.person_id = per.person_id
796 AND asg2.primary_flag = 'Y'
797 AND et.element_name = 'Court Order Information'
798 AND et.legislation_code = 'FI'
799 AND iv1.element_type_id = et.element_type_id
800 AND iv1.name = p_input_value
801 AND el.business_group_id = per.business_group_id
802 AND el.element_type_id = et.element_type_id
803 AND ee.assignment_id = asg2.assignment_id
804 AND ee.element_link_id = el.element_link_id
805 AND eev1.element_entry_id = ee.element_entry_id
806 AND eev1.input_value_id = iv1.input_value_id;
807
808 l_rec get_details%ROWTYPE;
809 --
810 BEGIN
811 --
812 OPEN get_details(p_assignment_id , p_effective_date ,'Dependent Number');
813 FETCH get_details INTO l_rec;
814 p_dependent_number := l_rec.screen_entry_value ;
815 CLOSE get_details;
816
817
818
819 OPEN get_details(p_assignment_id , p_effective_date , 'Third Party');
820 FETCH get_details INTO l_rec;
821 p_third_party := l_rec.screen_entry_value ;
822 CLOSE get_details;
823
824
825
826 OPEN get_details(p_assignment_id , p_effective_date ,'Court Order Amount');
827 FETCH get_details INTO l_rec;
828 p_court_order_amount := l_rec.screen_entry_value ;
829 CLOSE get_details;
830
831
832
833 OPEN get_details(p_assignment_id , p_effective_date ,'Periodic Installment');
834 FETCH get_details INTO l_rec;
835 p_periodic_installment := l_rec.screen_entry_value ;
836 CLOSE get_details;
837
838
839
840 OPEN get_details(p_assignment_id , p_effective_date ,'Number Of Installments');
841 FETCH get_details INTO l_rec;
842 p_number_of_installments := l_rec.screen_entry_value ;
843 CLOSE get_details;
844
845
846
847 OPEN get_details(p_assignment_id , p_effective_date ,'Suspension Flag');
848 FETCH get_details INTO l_rec;
849
850 p_suspension_flag := l_rec.screen_entry_value ;
851
852 CLOSE get_details;
853
854
855 --
856 RETURN 1;
857 --
858 END get_court_order_details;
859
860 ------------------------------------------------------------------------
861 -- Function union details
862 -- This function is used to obtain the trade union details of a person.
863 ------------------------------------------------------------------------
864
865 FUNCTION get_union_details
866 (p_assignment_id IN NUMBER
867 ,p_effective_date IN DATE
868 ,p_fixed_union_fees OUT NOCOPY NUMBER
869 ,p_percentage_union_fees OUT NOCOPY NUMBER
870 ,p_payment_calculation_mode OUT NOCOPY VARCHAR2
871 ) RETURN NUMBER IS
872
873 CURSOR get_details(p_assignment_id NUMBER ,p_effective_date DATE ) IS
874 select hoi2.org_information2,hoi2.org_information3,hoi2.org_information4,pap1.per_information11
875 , pap1.per_information12 , pap1.per_information13
876 from HR_ORGANIZATION_UNITS o1
877 , HR_ORGANIZATION_INFORMATION hoi1
878 , HR_ORGANIZATION_INFORMATION hoi2
879 , per_all_people_f pap1
880 , per_all_assignments_f paa
881 WHERE hoi1.organization_id = o1.organization_id
882 and hoi1.org_information1 = 'FI_TRADE_UNION'
883 and hoi1.org_information_context = 'CLASS'
884 and hoi2.ORG_INFORMATION_CONTEXT='FI_TRADE_UNION_DETAILS'
885 and o1.organization_id = pap1.per_information9
886 and hoi1.organization_id = hoi2.organization_id
887 AND pap1.person_id = paa.person_id
888 AND o1.business_group_id = paa.business_group_id
889 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
890 AND p_effective_date BETWEEN nvl(fnd_date.canonical_to_date(pap1.per_information18),
891 hr_general.start_of_time) AND nvl(fnd_date.canonical_to_date(pap1.per_information19),
892 hr_general.end_of_time)
893 AND paa.assignment_id = p_assignment_id;
894
895 l_rec get_details%ROWTYPE;
896 --
897 BEGIN
898
899 OPEN get_details(p_assignment_id , p_effective_date);
900 FETCH get_details into l_rec;
901 CLOSE get_details;
902 IF l_rec.per_information11 is null THEN
903 p_fixed_union_fees := l_rec.org_information3 ;
904 p_percentage_union_fees := l_rec.org_information4 ;
905 p_payment_calculation_mode := l_rec.org_information2 ;
906 ELSE
907 p_payment_calculation_mode := l_rec.per_information11;
908 IF l_rec.per_information12 IS NULL THEN
909 p_fixed_union_fees := l_rec.org_information3 ;
910 ELSE
911
912 p_fixed_union_fees := l_rec.per_information12 ;
913 END IF;
914
915 IF l_rec.per_information13 IS NULL THEN
916 p_percentage_union_fees := l_rec.org_information4 ;
917 ELSE
918
919 p_percentage_union_fees := l_rec.per_information13 ;
920 END IF;
921
922 END IF;
923
924 IF p_payment_calculation_mode is null THEN
925 p_payment_calculation_mode :='N';
926 END IF;
927
928 RETURN 1;
929
930 EXCEPTION
931 WHEN others THEN
932 RETURN NULL;
933
934 END get_union_details;
935
936 --------------------------------------------------------------------------
937 -- --
938 -- Name : get_IANA_charset --
939 -- Type : Function --
940 -- Access : Public --
941 -- Description : Function to IANA charset equivalent of --
942 -- NLS_CHARACTERSET --
943 -- Parameters : --
944 -- IN : N/A --
945 -- OUT : N/A --
946 -- RETURN : VARCHAR2 --
947 -- --
948 --------------------------------------------------------------------------
949 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
950 CURSOR csr_get_iana_charset IS
951 SELECT tag
952 FROM fnd_lookup_values
953 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
954 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
955 INSTR(USERENV('LANGUAGE'), '.') + 1)
956 AND language = 'US';
957
958 lv_iana_charset fnd_lookup_values.tag%type;
959 BEGIN
960 OPEN csr_get_iana_charset;
961 FETCH csr_get_iana_charset INTO lv_iana_charset;
962 CLOSE csr_get_iana_charset;
963
964 hr_utility.trace('IANA Charset = '||lv_iana_charset);
965 RETURN (lv_iana_charset);
966 END get_IANA_charset;
967
968
969 END;
[Home] [Help]