[Home] [Help]
PACKAGE BODY: APPS.HR_PL_UTILITY
Source
1 PACKAGE BODY hr_pl_utility as
2 /* $Header: peplutil.pkb 120.7.12010000.4 2009/12/18 10:56:25 bkeshary ship $ */
3 --------------------------------------------------------------------------------
4 -- FUNCTION per_pl_full_name
5 --------------------------------------------------------------------------------
6 FUNCTION per_pl_full_name(
7 p_first_name IN VARCHAR2
8 ,p_middle_names IN VARCHAR2
9 ,p_last_name IN VARCHAR2
10 ,p_known_as IN VARCHAR2
11 ,p_title IN VARCHAR2
12 ,p_suffix IN VARCHAR2
13 ,p_pre_name_adjunct IN VARCHAR2
14 ,p_per_information1 IN VARCHAR2
15 ,p_per_information2 IN VARCHAR2
16 ,p_per_information3 IN VARCHAR2
17 ,p_per_information4 IN VARCHAR2
18 ,p_per_information5 IN VARCHAR2
19 ,p_per_information6 IN VARCHAR2
20 ,p_per_information7 IN VARCHAR2
21 ,p_per_information8 IN VARCHAR2
22 ,p_per_information9 IN VARCHAR2
23 ,p_per_information10 IN VARCHAR2
24 ,p_per_information11 IN VARCHAR2
25 ,p_per_information12 IN VARCHAR2
26 ,p_per_information13 IN VARCHAR2
27 ,p_per_information14 IN VARCHAR2
28 ,p_per_information15 IN VARCHAR2
29 ,p_per_information16 IN VARCHAR2
30 ,p_per_information17 IN VARCHAR2
31 ,p_per_information18 IN VARCHAR2
32 ,p_per_information19 IN VARCHAR2
33 ,p_per_information20 IN VARCHAR2
34 ,p_per_information21 IN VARCHAR2
35 ,p_per_information22 IN VARCHAR2
36 ,p_per_information23 IN VARCHAR2
37 ,p_per_information24 IN VARCHAR2
38 ,p_per_information25 IN VARCHAR2
39 ,p_per_information26 IN VARCHAR2
40 ,p_per_information27 IN VARCHAR2
41 ,p_per_information28 IN VARCHAR2
42 ,p_per_information29 IN VARCHAR2
43 ,p_per_information30 IN VARCHAR2)
44 RETURN VARCHAR2 IS
45 --
46 l_full_name VARCHAR2(240);
47 --
48 BEGIN
49 --
50 SELECT SUBSTR(LTRIM(RTRIM(
51 DECODE(p_last_name, NULL, '',p_last_name)
52 ||DECODE(p_first_name,NULL, '',' ' || p_first_name)
53 ||DECODE(p_middle_names,NULL, '', ' ' || p_middle_names)
54 ||DECODE(p_title,NULL, '', ' ' || p_title)
55 )), 1, 240)
56 INTO l_full_name
57 FROM dual;
58 RETURN(l_full_name);
59 --
60 END per_pl_full_name;
61 --------------------------------------------------------------------------------
62 -- FUNCTION per_pl_order_name
63 --------------------------------------------------------------------------------
64 FUNCTION per_pl_order_name(
65 p_first_name IN VARCHAR2
66 ,p_middle_names IN VARCHAR2
67 ,p_last_name IN VARCHAR2
68 ,p_known_as IN VARCHAR2
69 ,p_title IN VARCHAR2
70 ,p_suffix IN VARCHAR2
71 ,p_pre_name_adjunct IN VARCHAR2
72 ,p_per_information1 IN VARCHAR2
73 ,p_per_information2 IN VARCHAR2
74 ,p_per_information3 IN VARCHAR2
75 ,p_per_information4 IN VARCHAR2
76 ,p_per_information5 IN VARCHAR2
77 ,p_per_information6 IN VARCHAR2
78 ,p_per_information7 IN VARCHAR2
79 ,p_per_information8 IN VARCHAR2
80 ,p_per_information9 IN VARCHAR2
81 ,p_per_information10 IN VARCHAR2
82 ,p_per_information11 IN VARCHAR2
83 ,p_per_information12 IN VARCHAR2
84 ,p_per_information13 IN VARCHAR2
85 ,p_per_information14 IN VARCHAR2
86 ,p_per_information15 IN VARCHAR2
87 ,p_per_information16 IN VARCHAR2
88 ,p_per_information17 IN VARCHAR2
89 ,p_per_information18 IN VARCHAR2
90 ,p_per_information19 IN VARCHAR2
91 ,p_per_information20 IN VARCHAR2
92 ,p_per_information21 IN VARCHAR2
93 ,p_per_information22 IN VARCHAR2
94 ,p_per_information23 IN VARCHAR2
95 ,p_per_information24 IN VARCHAR2
96 ,p_per_information25 IN VARCHAR2
97 ,p_per_information26 IN VARCHAR2
98 ,p_per_information27 IN VARCHAR2
99 ,p_per_information28 IN VARCHAR2
100 ,p_per_information29 IN VARCHAR2
101 ,p_per_information30 IN VARCHAR2)
102 RETURN VARCHAR2 IS
103 --
104 l_order_name VARCHAR2(240);
105 --
106 BEGIN
107 --
108 SELECT SUBSTR(LTRIM(RTRIM(
109 DECODE(p_last_name, NULL, '',p_last_name)
110 ||DECODE(p_first_name,NULL, '',' ' || p_first_name)
111 ||DECODE(p_middle_names,NULL, '', ' ' || p_middle_names)
112 ||DECODE(p_title,NULL, '', ' ' || p_title)
113 )), 1, 240)
114 INTO l_order_name
115 FROM dual;
116 RETURN(l_order_name);
117 --
118 END per_pl_order_name;
119
120 --------------------------------------------------------------------------------
121 -- FUNCTION per_pl_chk_valid_date
122 --------------------------------------------------------------------------------
123
124 FUNCTION per_pl_chk_valid_date (p_date IN VARCHAR2) RETURN VARCHAR2 IS
125
126 l_century number;
127 l_month number;
128 l_birth_date number;
129 l_birth_year number;
130 l_birth_month number;
131 l_birth_day varchar2(20);
132
133 BEGIN
134
135 l_month := to_number(substr(p_date,3,2));
136
137 if l_month>=1 AND l_month<=12 then
138 l_century:=1900;
139 end if;
140 if l_month>=81 AND l_month<=92 then
141 l_century:=1800;
142 l_month:=l_month-80;
143 end if;
144 if l_month>=21 AND l_month<=32 then
145 l_century:=2000;
146 l_month:=l_month-20;
147 end if;
148 if l_month>=41 AND l_month<=52 then
149 l_century:=2100;
150 l_month:=l_month-40;
151 end if;
152 if l_month>=61 AND l_month<=72 then
153 l_century:=2200 ;
154 l_month:=l_month-60;
155 end if;
156
157 l_birth_year:=to_number(substr(p_date,1,2));
158 l_birth_year:=l_century+l_birth_year;
159 l_birth_month:=l_month;
160 l_birth_date:=to_number(substr(p_date,5,2));
161 l_birth_day:=to_char(to_date(lpad(l_birth_date,2,0)||lpad(l_birth_month,2,0)||l_birth_year,'DDMMRRRR'),'DD-MON-RRRR');
162
163 return l_birth_day;
164
165 exception
166 when others then
167 return '0';
168 end per_pl_chk_valid_date;
169 --------------------------------------------------------------------------------
170 --Procedure per_pl_nip_validate
171 --------------------------------------------------------------------------------
172 procedure per_pl_nip_validate (
173 p_nip_number in varchar2,
174 p_person_id in number,
175 p_business_group_id in number,
176 p_legal_employer in varchar2,
177 p_nationality in varchar2,
178 p_citizenship in varchar2
179 ) is
180 nip_sum number;
181
182 type v_nip is table of number
183 index by binary_integer;
184
185 nip_number v_nip;
186 nip_number_dup per_all_people_f.per_information1%type;
187
188 cursor nip is
189 select per_information1
190 from per_all_people_f ppf
191 where ppf.business_group_id = p_business_group_id
192 and ppf.person_id <> nvl (p_person_id, 0)
193 and ppf.per_information1 = p_nip_number
194 and ppf.per_information7 = p_legal_employer
195 and ppf.nationality = 'PQH_PL'
196 and ppf.per_information8 = 'PL' ;
197 begin
198 nip_sum := 0;
199 /* Introduced an Additional check to ensure the NIP is not NULL*/
200 IF p_nip_number IS NOT NULL THEN
201
202 if p_nationality = 'PQH_PL' and p_citizenship = 'PL' then
203 begin
204 if length (p_nip_number) > 10 then
205 fnd_message.set_name ('PER', 'HR_NIP_INVALID_NUMBER_PL');
206 fnd_message.raise_error;
207 end if;
208
209 if to_number (p_nip_number) = 0 then
210 fnd_message.set_name ('PER', 'HR_NIP_INVALID_NUMBER_PL');
211 fnd_message.raise_error;
212 end if;
213
214 nip_number (1) := 6 * (to_number (substr (p_nip_number, 1, 1)));
215 nip_number (2) := 5 * (to_number (substr (p_nip_number, 2, 1)));
216 nip_number (3) := 7 * (to_number (substr (p_nip_number, 3, 1)));
217 nip_number (4) := 2 * (to_number (substr (p_nip_number, 4, 1)));
218 nip_number (5) := 3 * (to_number (substr (p_nip_number, 5, 1)));
219 nip_number (6) := 4 * (to_number (substr (p_nip_number, 6, 1)));
220 nip_number (7) := 5 * (to_number (substr (p_nip_number, 7, 1)));
221 nip_number (8) := 6 * (to_number (substr (p_nip_number, 8, 1)));
222 nip_number (9) := 7 * (to_number (substr (p_nip_number, 9, 1)));
223 nip_number (10) := to_number (substr (p_nip_number, 10, 1));
224 nip_sum := mod (
225 (nip_number (1) + nip_number (2) + nip_number (3)
226 + nip_number (4) + nip_number (5) + nip_number (6)
227 + nip_number (7) + nip_number (8) + nip_number (9)
228 ),
229 11
230 );
231
232 if nip_sum = 10 then
233 if nip_number (10) <> 0 then
234 fnd_message.set_name ('PER', 'HR_NIP_INVALID_NUMBER_PL');
235 fnd_message.raise_error;
236 end if;
237 elsif nip_sum <> nip_number (10) then
238 fnd_message.set_name ('PER', 'HR_NIP_INVALID_NUMBER_PL');
239 fnd_message.raise_error;
240 end if;
241
242 -- Uniqueness Check at legal employer level for Polish Persons
243 if p_nip_number is not null and p_business_group_id is not null then
244 open nip;
245 fetch nip into nip_number_dup;
246
247 if nip_number_dup = p_nip_number then
248 fnd_message.set_name ('PER', 'HR_NIP_UNIQUE_NUMBER_PL');
249 fnd_message.raise_error;
250 end if;
251
252 close nip;
253 end if;
254 exception
255 when value_error then
256 fnd_message.set_name ('PER', 'HR_NIP_INVALID_NUMBER_PL');
257 fnd_message.raise_error;
258 end;
259 elsif nvl (p_nationality, '-1') <> 'PQH_PL' or nvl (p_citizenship, '-1') <>
260 'PL' then
261 begin
262 if length (p_nip_number) <> 10 then
263 hr_utility.set_message (800, 'HR_375890_NIP_NON_POLISH_PL');
264 hr_utility.set_warning;
265 end if;
266
267 if to_number (p_nip_number) = 0 then
268 hr_utility.set_message (800, 'HR_375890_NIP_NON_POLISH_PL');
269 hr_utility.set_warning;
270 end if;
271
272 nip_number (1) := 6 * (to_number (substr (p_nip_number, 1, 1)));
273 nip_number (2) := 5 * (to_number (substr (p_nip_number, 2, 1)));
274 nip_number (3) := 7 * (to_number (substr (p_nip_number, 3, 1)));
275 nip_number (4) := 2 * (to_number (substr (p_nip_number, 4, 1)));
276 nip_number (5) := 3 * (to_number (substr (p_nip_number, 5, 1)));
277 nip_number (6) := 4 * (to_number (substr (p_nip_number, 6, 1)));
278 nip_number (7) := 5 * (to_number (substr (p_nip_number, 7, 1)));
279 nip_number (8) := 6 * (to_number (substr (p_nip_number, 8, 1)));
280 nip_number (9) := 7 * (to_number (substr (p_nip_number, 9, 1)));
281 nip_number (10) := to_number (substr (p_nip_number, 10, 1));
282 nip_sum := mod (
283 (nip_number (1) + nip_number (2) + nip_number (3)
284 + nip_number (4) + nip_number (5) + nip_number (6)
285 + nip_number (7) + nip_number (8) + nip_number (9)
286 ),
287 11
288 );
289
290 if nip_sum = 10 then
291 if nip_number (10) <> 0 then
292 hr_utility.set_message (800, 'HR_375890_NIP_NON_POLISH_PL');
293 hr_utility.set_warning;
294 end if;
295 elsif nip_sum <> nip_number (10) then
296 hr_utility.set_message (800, 'HR_375890_NIP_NON_POLISH_PL');
297 hr_utility.set_warning;
298 end if;
299
300 if length (p_nip_number) > 30 then
301 fnd_message.set_name ('PER', 'HR_375887_NIP_LENGTH_PL');
302 fnd_message.raise_error;
303 end if; -- End if of NIP Length Check
304 exception
305 when value_error then
306 hr_utility.set_message (800, 'HR_375890_NIP_NON_POLISH_PL');
307 hr_utility.set_warning;
308 end;
309 end if; -- End if of Nationality and Citizenship Check
310 End IF; -- End If for not null check
311 end per_pl_nip_validate;
312
313
314 PROCEDURE per_pl_chk_gender(nat_id varchar2,gender IN OUT NOCOPY varchar2) is
315 l_gender varchar2(20);
316 l_var number;
317 begin
318 l_gender:=substr(nat_id,10,1);
319 l_var:=mod(l_gender,2);
320 if(gender is null or gender ='U') then
321 if(l_var=0) then
322 gender:='F';
323 elsif(l_var=1) then
324 gender:='M';
325 end if;
326 else
327 if(l_var=0 and gender='F') or (l_var=1 and gender='M') then
328 null;
329 else
330 gender:='0';
331 end if;
332 end if;
333 end per_pl_chk_gender;
334
335 Procedure per_pl_validate(pesel varchar2) is
336
337 Y1 number;
338 Y2 number;
339 M3 number;
340 M4 number;
341 D5 number;
342 D6 number;
343 A7 number;
344 A8 number;
345 A9 number;
346 S10 number;
347 C11 number;
348 V1 number;
349 V2 number;
350 V3 number;
351 BEGIN
352
353 if (hr_ni_chk_pkg.chk_nat_id_format(pesel,'DDDDDDDDDDD') = 0) then
354 fnd_message.set_name('PER','HR_PL_INVALID_NATIONAL_ID');
355 fnd_message.raise_error;
356 else
357 Y1:=to_number(substr(pesel,1,1));
358 Y2:=to_number(substr(pesel,2,1));
359 M3:=to_number(substr(pesel,3,1));
360 M4:=to_number(substr(pesel,4,1));
361 D5:=to_number(substr(pesel,5,1));
362 D6:=to_number(substr(pesel,6,1));
363 A7:=to_number(substr(pesel,7,1));
364 A8:=to_number(substr(pesel,8,1));
365 A9:=to_number(substr(pesel,9,1));
366 S10:=to_number(substr(pesel,10,1));
367 C11:=to_number(substr(pesel,11,1));
368
369 V1:=((1*Y1)+(3*Y2)+(7*M3)+(9*M4)+(1*D5)+(3*D6)+(7*A7)+(9*A8)+(1*A9)+(3*S10));
370
371 V2:=MOD(V1,10);
372
373 if V2 = 0 then
374 V3:=0;
375 else
376 V3:=10-V2;
377 end if;
378
379 if C11 <> V3 then
380 fnd_message.set_name('PER','HR_PL_INVALID_NATIONAL_ID');
381 fnd_message.raise_error;
382 end if;
383 end if;
384
385 end per_pl_validate;
386
387 FUNCTION validate_bank_id(p_bank_id varchar2) RETURN NUMBER IS
388 B3 number;
389 B4 number;
390 B5 number;
391 B6 number;
392 B7 number;
393 B8 number;
394 B9 number;
395 B10 number;
396 CB10 number;
397 l_var1 number;
398
399 BEGIN
400 B3:=substr(p_bank_id,1,1);
401 B4:=substr(p_bank_id,2,1);
402 B5:=substr(p_bank_id,3,1);
403 B6:=substr(p_bank_id,4,1);
404 B7:=substr(p_bank_id,5,1);
405 B8:=substr(p_bank_id,6,1);
406 B9:=substr(p_bank_id,7,1);
407 B10:=substr(p_bank_id,8,1);
408
409 IF hr_ni_chk_pkg.chk_nat_id_format(p_bank_id,'DDDDDDDD')= '0' THEN
410 --Invalid Format
411 return 0;
412 end if;
413
414 l_var1:=(3*B3)+(9*B4)+(7*B5)+(1*B6)+(3*B7)+(9*B8)+(7*B9);
415 l_var1:=mod(l_var1,10);
416
417 if l_var1=0 then
418 CB10:=0;
419 else
420 CB10:=10-l_var1;
421 end if;
422
423 if B10 <> CB10 then -- validation of Bank ID (entered b10 with the calculated check digit b10)
424 return 0;
425 end if;
426
427 return 1;
428
429 end validate_bank_id;
430
431
432 FUNCTION validate_account_no(p_check_digit VARCHAR2
433 ,p_bank_id VARCHAR2
434 ,p_account_number VARCHAR2
435 )RETURN NUMBER IS
436 acc_no varchar2(100);
437 C1 number;
438 C2 number;
439 B3 number;
440 B4 number;
441 B5 number;
442 B6 number;
443 B7 number;
444 B8 number;
445 B9 number;
446 B10 number;
447 A11 number;
448 A12 number;
449 A13 number;
450 A14 number;
451 A15 number;
452 A16 number;
453 A17 number;
454 A18 number;
455 A19 number;
456 A20 number;
457 A21 number;
458 A22 number;
459 A23 number;
460 A24 number;
461 A25 number;
462 A26 number;
463 CB10 number;
464 CC number;
465 l_var1 number;
466
467
468 begin
469
470 C1:=substr(p_check_digit,1,1);
471 C2:=substr(p_check_digit,2,1);
472 B3:=substr(p_bank_id,1,1);
473 B4:=substr(p_bank_id,2,1);
474 B5:=substr(p_bank_id,3,1);
475 B6:=substr(p_bank_id,4,1);
476 B7:=substr(p_bank_id,5,1);
477 B8:=substr(p_bank_id,6,1);
478 B9:=substr(p_bank_id,7,1);
479 B10:=substr(p_bank_id,8,1);
480 A11:=substr(p_account_number,1,1);
481 A12:=substr(p_account_number,2,1);
482 A13:=substr(p_account_number,3,1);
483 A14:=substr(p_account_number,4,1);
484 A15:=substr(p_account_number,5,1);
485 A16:=substr(p_account_number,6,1);
486 A17:=substr(p_account_number,7,1);
487 A18:=substr(p_account_number,8,1);
488 A19:=substr(p_account_number,9,1);
489 A20:=substr(p_account_number,10,1);
490 A21:=substr(p_account_number,11,1);
491 A22:=substr(p_account_number,12,1);
492 A23:=substr(p_account_number,13,1);
493 A24:=substr(p_account_number,14,1);
494 A25:=substr(p_account_number,15,1);
495 A26:=substr(p_account_number,16,1);
496
497 acc_no:=p_check_digit||p_bank_id||p_account_number;
498
499
500 IF hr_ni_chk_pkg.chk_nat_id_format(acc_no,'DDDDDDDDDDDDDDDDDDDDDDDDDD')= '0' THEN
501 --Invalid Format
502 return 0;
503 end if;
504
505 l_var1:=(57*B3)+(93*B4)+(19*B5)+(31*B6)+(71*B7)+(75*B8)+(56*B9)+(25*B10)+(51*A11)+(73*A12)+(17*A13)+(89*A14)+(38*A15)+(62*A16)+(45*A17)+(53*A18)+(15*A19)+(50*A20)+(5*A21)+(49*A22)+(34*A23)+(81*A24)+(76*A25)+(27*A26)+(90*2)+(9*5)+(30*2)+(3*1);
506 l_var1:=98-mod(l_var1,97);
507
508 CC:=C1*10+C2;
509
510 if l_var1 <> CC then --validation of Full Bank Acc Number(entered C1C2 eith calculated C1C1)
511 return 0;
512 end if;
513
514 return 1;
515
516 end validate_account_no;
517
518 ----
519 -- Function added for IBAN Validation
520 ----
521 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
522 BEGIN
523 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
524 RETURN 1;
525 else
526 RETURN 0;
527 END IF;
528 END validate_iban_acc;
529
530 ----
531 -- This function will get called from the bank keyflex field segments
532 ----
533 FUNCTION validate_account_entered
534 (p_acc_no IN VARCHAR2,
535 p_is_iban_acc IN varchar2,
536 p_bank_chk_dig IN varchar2 DEFAULT NULL,
537 p_bank_id IN Varchar2 DEFAULT NULL) RETURN NUMBER IS
538 --
539 l_ret NUMBER ;
540 begin
541 -- hr_utility.trace_on(null,'ACCVAL');
542 l_ret :=0;
543 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
544 hr_utility.set_location('p_account_number ' || p_acc_no,1);
545
546 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
547 l_ret := validate_account_no(p_bank_chk_dig, p_bank_id, p_acc_no);
548 hr_utility.set_location('l_ret ' || l_ret,1);
549 RETURN l_ret;
550 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
551 l_ret := validate_iban_acc(p_acc_no);
552 hr_utility.set_location('l_ret ' || l_ret,3);
553 RETURN l_ret;
554 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
555 hr_utility.set_location('Both Account Nos Null',4);
556 RETURN 1;
557 ELSE
558 hr_utility.set_location('l_ret: 3 ' ,5);
559 RETURN 3;
560 END if;
561 End validate_account_entered;
562
563
564 --------------------------------------------------------------------------------
565 -- Procedure per_pl_calc_periods --
566 --------------------------------------------------------------------------------
567 PROCEDURE per_pl_calc_periods(p_start_date IN DATE,
568 p_end_date IN DATE,
569 p_days IN OUT NOCOPY NUMBER,
570 p_months IN OUT NOCOPY NUMBER,
571 p_years IN OUT NOCOPY NUMBER)
572 IS
573 dStartdate Date;
574 dEnddate Date;
575 dDays number;
576 dMonths number;
577 nStartdate Date;
578 nEnddate Date;
579 dYear number:=0;
580 BEGIN
581 dStartdate:=p_start_date;
582 dEnddate:=p_end_date;
583
584 if last_day(dStartdate -1) = (dStartdate - 1) and last_day(dEnddate) = dEnddate then
585 dMonths:= months_between(dEnddate,dStartdate-1);
586 dDays:=0;
587 elsif last_day(dStartdate -1) = (dStartdate - 1) then
588 nStartdate:=dStartdate -1;
589 nEnddate:=trunc(dEnddate,'MM')-1;
590 dMonths:= months_between(nEnddate,nStartdate);
591 dDays:=to_number(to_char(dEnddate,'dd'));
592 elsif last_day(dEnddate) = dEnddate then
593 nStartdate:=last_day(dStartdate);
594 nEnddate:=dEnddate;
595 dMonths:= months_between(nEnddate,nStartdate);
596 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'));
597 elsif to_char(dStartdate,'Mon') = to_char(dEnddate,'Mon') Then
598 dMonths:= months_between(dEnddate,dStartdate);
599 if to_char(dStartdate,'dd') <= to_char(dEnddate,'dd') Then
600 dDays:= to_number(to_char(dEnddate,'dd')) - to_number(to_char(dStartdate,'dd'))+1;
601 else
602 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'))
603 +to_number(to_char(dEnddate,'dd'));
604 end if;
605 else
606 nStartdate:=last_day(dStartdate);
607 nEnddate:=trunc(dEnddate,'MM')-1;
608 dMonths:= months_between(nEnddate,nStartdate);
609 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'))
610 +to_number(to_char(dEnddate,'dd'));
611 end if;
612 dMonths:= trunc(dMonths);
613 If dDays >= 30 then
614 dMonths:=dMonths+trunc(dDays/30);
615 dDays :=mod(dDays,30);
616 End If;
617
618 If dMonths >= 12 then
619 dYear := trunc(dMonths/12);
620 dMonths:= mod(dMonths,12);
621 end if;
622 p_years:= dYear;
623 p_months := dMonths;
624 p_days := dDays;
625
626 END per_pl_calc_periods;
627 -- End of per_pl_calc_periods
628
629
630
631 /************************************************************************************************/
632 /*
633 This function returns either 0 or 1. If it returns 0, then an incorrect value of
634 description for the lookup Employee Category has been specified. This is called thru the formual function
635 */
636
637 /**********************************************************************************************/
638 /*
639
640 Inputs : 1) Person id from per_all_people_f
641 2) Code of the type of Service. This code can be picked up from lookup EMPLOYEE_CATG
642
643 Outputs : 1) Number of Years
644 2) Number of Months
645 3) Number of days
646 4) Message (This message will be null if correct code has been passed else an appropriate error message
647 is thrown
648
649
650 Return : This function return 1 if successful else 0
651 */
652 /********************************************************************************************/
653
654 function GET_LENGTH_OF_SERVICE(P_PERSON_ID IN NUMBER,
655 P_TYPE_OF_SERVICE IN VARCHAR2, -- This is the code of the Category
656 l_years OUT NOCOPY NUMBER,
657 l_months OUT NOCOPY NUMBER,
658 l_days OUT NOCOPY NUMBER,
659 l_message OUT NOCOPY VARCHAR2)
660 RETURN number IS
661
662
663
664 lookup_type_val fnd_common_lookups.lookup_type%TYPE;
665 lookup_descr_val1 fnd_common_lookups.description%TYPE;
666 lookup_descr_val2 fnd_common_lookups.description%TYPE;
667
668 cursor csr_service_period_dts is
669 select ppj.start_date, ppj.end_date,
670 ppj.period_years, ppj.period_months,
671 ppj.period_days
672 from per_previous_jobs ppj, per_previous_employers ppe
673 where ppe.person_id = P_PERSON_ID
674 and ppe.previous_employer_id = ppj.previous_employer_id
675 and ppj.PJO_INFORMATION1 = P_TYPE_OF_SERVICE -- Replaced with PJO_INFORMATION1
676 and ppj.start_date IS NOT NULL
677 AND ppj.end_date IS NOT NULL
678 order by ppj.start_date, ppj.end_date;
679
680 cursor csr_service_period_ymd is
681 select ppj.start_date, ppj.end_date,
682 ppj.period_years, ppj.period_months,
683 ppj.period_days
684 from per_previous_jobs ppj, per_previous_employers ppe
685 where ppe.person_id = P_PERSON_ID
686 and ppe.previous_employer_id = ppj.previous_employer_id
687 and ppj.PJO_INFORMATION1 = P_TYPE_OF_SERVICE -- Replaced with PJO_INFORMATION1
688 and ppj.start_date IS NULL
689 AND ppj.end_date IS NULL ;
690
691 cursor csr_type_of_service is
692 select description
693 from hr_lookups -- Replaced with hr_lookups
694 where lookup_type = lookup_type_val
695 and lookup_code = P_TYPE_OF_SERVICE
696 and description in (lookup_descr_val1, lookup_descr_val2);
697
698 idx number := 0;
699 ind number := 0;
700 idx1 number := 0;
701 ind1 number := 0;
702
703 TYPE service_rec IS RECORD (
704 sdate date,
705 edate date,
706 years NUMBER,
707 months NUMBER,
708 days NUMBER);
709
710 TYPE service_table IS TABLE OF service_rec INDEX BY BINARY_INTEGER;
711
712 g_user_service_table_dts service_table;
713 g_user_service_table_ymd service_table;
714
715 total_service_years per_previous_jobs.period_years%TYPE;
716 total_service_months per_previous_jobs.period_months%TYPE;
717 total_service_days per_previous_jobs.period_days%TYPE;
718 service_type fnd_common_lookups.description%TYPE;
719
720 temp_days number := 0 ;
721 temp_months number := 0 ;
722 temp_years number := 0 ;
723
724 message varchar2(2400);
725 l_number number;
726 flag varchar2(1);
727 message_cat VARCHAR2(3);
728 message_name fnd_new_messages.message_name%TYPE;
729
730 cursor csr_error_message is
731 select fnd_message.get_string(message_cat,message_name)
732 from dual;
733
734 BEGIN
735 lookup_type_val := 'PL_TYPE_OF_SERVICE';
736 lookup_descr_val1 := '01';
737 lookup_descr_val2 := '02';
738 l_number := 1; /* Default of 1(one) means success */
739 flag := 'N';
740 message := NULL;
741 message_cat := 'PER';
742 message_name := 'HR_INVALID_ALGO_JOBTYPE_PL';
743
744
745 --
746 -- Check if a valid algorithm has been specified for the type of service
747 --
748 open csr_type_of_service;
749 fetch csr_type_of_service into service_type;
750
751 if csr_type_of_service%NOTFOUND then
752 total_service_years := 0;
753 total_service_months := 0;
754 total_service_days := 0;
755
756 open csr_error_message;
757 fetch csr_error_message into message;
758 close csr_error_message;
759
760 l_years := total_service_years;
761 l_months := total_service_months;
762 l_days := total_service_days;
763 l_message := message;
764 l_number := 0;
765 return l_number;
766 end if;
767 close csr_type_of_service;
768
769 --
770 -- Initialise the result variables
771 --
772 total_service_years := 0;
773 total_service_months := 0;
774 total_service_days := 0;
775 idx := 0;
776
777 IF service_type = '02' THEN --- for connected periods
778 FOR svcrec in csr_service_period_dts LOOP
779 -- First Record
780 IF idx = 0 THEN
781 idx := idx + 1;
782 g_user_service_table_dts(idx).sdate := svcrec.start_date;
783 g_user_service_table_dts(idx).edate := svcrec.end_date;
784 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
785 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
786 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
787
788 -- Complete Range already covered
789 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
790 AND g_user_service_table_dts(idx).edate ) AND
791 ( svcrec.end_date BETWEEN g_user_service_table_dts(idx).sdate
792 AND g_user_service_table_dts(idx).edate )
793 THEN
794 null;
795
796 -- Partial Range Covered
797 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
798 AND g_user_service_table_dts(idx).edate ) AND
799 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
800 THEN
801 g_user_service_table_dts(idx).edate := svcrec.end_date;
802
803 -- Range Not Covered
804 ELSIF ( svcrec.start_date > g_user_service_table_dts(idx).edate ) AND
805 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
806 THEN
807 -- Is this range contigous
808 IF ( svcrec.start_Date = g_user_service_table_dts(idx).edate +1 ) THEN
809 g_user_service_table_dts(idx).edate := svcrec.end_date;
810 ELSE -- not contigous
811 idx := idx + 1;
812 g_user_service_table_dts(idx).sdate := svcrec.start_date;
813 g_user_service_table_dts(idx).edate := svcrec.end_date;
814 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
815 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
816 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
817 END IF;
818 END IF;
819
820 END LOOP;
821
822 ELSE --- else for service_type , now for separated periods
823
824 FOR svcrec in csr_service_period_dts LOOP
825 -- First Record
826 IF idx = 0 THEN
827 idx := idx + 1;
828 g_user_service_table_dts(idx).sdate := svcrec.start_date;
829 g_user_service_table_dts(idx).edate := svcrec.end_date;
830 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
831 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
832 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
833
834 -- Complete Range already covered
835 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
836 AND g_user_service_table_dts(idx).edate ) AND
837 ( svcrec.end_date BETWEEN g_user_service_table_dts(idx).sdate
838 AND g_user_service_table_dts(idx).edate )
839 THEN
840 null;
841
842 -- Partial Range Covered
843 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
844 AND g_user_service_table_dts(idx).edate ) AND
845 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
846 THEN
847 idx := idx + 1;
848 g_user_service_table_dts(idx).sdate := g_user_service_table_dts(idx-1).edate +1 ;
849 g_user_service_table_dts(idx).edate := svcrec.end_date;
850
851 -- Range Not Covered
852 ELSIF ( svcrec.start_date > g_user_service_table_dts(idx).edate ) AND
853 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
854 THEN
855 -- Is this range contigous
856 IF ( svcrec.start_Date = g_user_service_table_dts(idx).edate +1 ) THEN
857 idx := idx + 1;
858 g_user_service_table_dts(idx).sdate := svcrec.start_date;
859 g_user_service_table_dts(idx).edate := svcrec.end_date;
860 ELSE -- not contigous
861 idx := idx + 1;
862 g_user_service_table_dts(idx).sdate := svcrec.start_date;
863 g_user_service_table_dts(idx).edate := svcrec.end_date;
864 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
865 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
866 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
867 END IF;
868 END IF;
869 END LOOP;
870
871 END IF; -- for service_type
872
873 ind := 0 ;
874 FOR ymdrec in csr_service_period_ymd LOOP
875 ind := ind + 1;
876 g_user_service_table_ymd(ind).sdate := ymdrec.start_date;
877 g_user_service_table_ymd(ind).edate := ymdrec.end_date;
878 g_user_service_table_ymd(ind).years := nvl(ymdrec.period_years,0);
879 g_user_service_table_ymd(ind).months := nvl(ymdrec.period_months,0);
880 g_user_service_table_ymd(ind).days := nvl(ymdrec.period_days,0);
881 END LOOP;
882
883
884 --
885 -- Get the YMD from the dates , status = Automatic
886 --
887 IF idx > 0 THEN
888 FOR i in g_user_service_table_dts.FIRST .. g_user_service_table_dts.LAST LOOP
889 idx1 := idx1 + 1;
890 hr_pl_utility.per_pl_calc_periods ( g_user_service_table_dts(idx1).sdate,
891 g_user_service_table_dts(idx1).edate,
892 temp_days,
893 temp_months,
894 temp_years ) ;
895
896 total_service_years := total_service_years + temp_years ;
897 total_service_months := total_service_months + temp_months;
898 total_service_days := total_service_days + temp_days;
899 END LOOP;
900 END IF;
901
902 --
903 -- Get the YMD from the ymd , status = Manual
904 --
905 IF ind > 0 THEN
906 FOR i in g_user_service_table_ymd.FIRST .. g_user_service_table_ymd.LAST LOOP
907 ind1 := ind1 + 1;
908 total_service_years := total_service_years + g_user_service_table_ymd(ind1).years;
909 total_service_months := total_service_months + g_user_service_table_ymd(ind1).months;
910 total_service_days := total_service_days + g_user_service_table_ymd(ind1).days;
911 END LOOP;
912 END IF;
913
914
915
916 IF total_service_days > 29 THEN
917 total_service_months := total_service_months + trunc(total_service_days /30);
918 total_service_days := mod (total_service_days ,30);
919 END IF;
920 IF total_service_months > 11 THEN
921 total_service_years := total_service_years + trunc(total_service_months /12);
922 total_service_months := mod (total_service_months ,12);
923 END IF;
924
925 l_years := total_service_years;
926 l_months := total_service_months;
927 l_days := total_service_days;
928
929 return l_number;
930
931 END GET_LENGTH_OF_SERVICE;
932
933 FUNCTION CHECK_CONTRIBUTION_TYPE(P_ENTRY_VALUE VARCHAR2) return NUMBER is
934 l_out number:=0;
935 cursor csr_lookup_code is
936 select lookup_code
937 from hr_lookups
938 where LOOKUP_TYPE='PL_CONTRIBUTION_TYPE'
939 and lookup_code=P_ENTRY_VALUE;
940 l_value hr_lookups.lookup_code%TYPE;
941 begin
942 l_value := NULL;
943 open csr_lookup_code;
944 fetch csr_lookup_code into l_value;
945 close csr_lookup_code;
946 if l_value is not null then
947 l_out := 1;
948 end if;
949
950 return l_out;
951
952 end CHECK_CONTRIBUTION_TYPE;
953
954 FUNCTION GET_VEHICLE_MILEAGE(p_date_earned IN DATE,
955 p_vehicle_allocation_id IN NUMBER,
956 p_monthly_mileage_limit OUT NOCOPY NUMBER,
957 p_engine_capacity_in_cc OUT NOCOPY NUMBER,
958 p_vehicle_type OUT NOCOPY VARCHAR2) RETURN NUMBER is
959
960 Cursor csr_vehicle is select
961 puci.value,
962 pvrf.engine_capacity_in_cc,
963 pvrf.vehicle_type,
964 pvaf.val_information3
965 from
966 pqp_vehicle_allocations_f pvaf,
967 pay_user_column_instances_f puci,
968 pqp_vehicle_repository_f pvrf
969 where
970 pvaf.val_information2 = puci.user_column_instance_id and
971 pvaf.vehicle_repository_id = pvrf.vehicle_repository_id and
972 pvaf.vehicle_allocation_id = p_vehicle_allocation_id and
973 p_date_earned between pvaf.effective_start_date and pvaf.effective_end_date and
974 p_date_earned between pvrf.effective_start_date and pvrf.effective_end_date and
975 p_date_earned between puci.effective_start_date and puci.effective_end_date;
976
977 l_val_information3 number;
978
979 BEGIN
980
981 open csr_vehicle;
982 fetch csr_vehicle into p_monthly_mileage_limit,p_engine_capacity_in_cc,p_vehicle_type,l_val_information3;
983 close csr_vehicle;
984
985 If p_monthly_mileage_limit is null then
986 return 0;
987 else
988 return l_val_information3;
989 -- returning the value Monthly mileage limit by emp for bug 4576456
990 end if;
991
992 END GET_VEHICLE_MILEAGE;
993
994 /*Start of function GET_TOTAL_PERIOD_OF_SERVICE*/
995 FUNCTION GET_TOTAL_PERIOD_OF_SERVICE
996 (p_assignment_id in number,
997 p_date in date,
998 p_years OUT NOCOPY NUMBER,
999 p_months OUT NOCOPY NUMBER,
1000 p_days OUT NOCOPY NUMBER) return number is
1001
1002 cursor csr_person_id is
1003 select person_id
1004 from per_all_assignments_f
1005 where assignment_id=p_assignment_id;
1006
1007 cursor csr_per_periods_of_service(r_person_id per_all_people_f.person_id%type) is
1008 select date_start start_date,nvl(ACTUAL_TERMINATION_DATE ,p_date) end_date
1009 from per_periods_of_service
1010 where person_id=r_person_id;
1011
1012 cursor csr_pemp_without_start_date(r_person_id per_all_people_f.person_id%type) is
1013 select nvl(period_months,0) months,nvl(period_days,0) days,nvl(period_years,0) years
1014 from PER_PREVIOUS_EMPLOYERS
1015 where person_id=r_person_id
1016 and employer_type='PREVIOUS'
1017 and start_date is null;
1018
1019 cursor csr_prev_emp_with_start_date(r_person_id per_all_people_f.person_id%type) is
1020 select start_date ,end_date,period_months,period_days,period_years,employer_type
1021 from PER_PREVIOUS_EMPLOYERS
1022 where person_id=r_person_id
1023 and employer_type in ('PREVIOUS','PARALLEL')
1024 and start_date is not null
1025 and start_date<p_date;
1026
1027 TYPE prev_emp_rec IS RECORD (
1028 start_date date,
1029 end_date date,
1030 years NUMBER,
1031 months NUMBER,
1032 days NUMBER,
1033 active boolean
1034 );
1035 TYPE prev_emp_table IS TABLE OF prev_emp_rec INDEX BY BINARY_INTEGER;
1036
1037
1038 l_prev_emp_table prev_emp_table;
1039 l_key number ;
1040 idx number := 0;
1041 l_end_date date;
1042 l_person_id per_all_people_f.person_id%type;
1043
1044 l_temp_period_years number;
1045 l_temp_period_months number;
1046 l_temp_period_days number;
1047
1048 l_total_period_years number:=0;
1049 l_total_period_months number:=0;
1050 l_total_period_days number:=0;
1051
1052 l_proc varchar2(41);
1053
1054 begin
1055
1056 l_proc:='HR_PL_UTILITY.GET_TOTAL_PERIOD_OF_SERVICE';
1057 hr_utility.set_location(l_proc,10);
1058 open csr_person_id;
1059 fetch csr_person_id into l_person_id;
1060 close csr_person_id;
1061
1062
1063 /*start of calculation for per_periods_of_service*/
1064
1065 for i in csr_per_periods_of_service(l_person_id) loop
1066 hr_pl_utility.per_pl_calc_periods
1067 (i.start_date ,
1068 i.end_date ,
1069 l_temp_period_days ,
1070 l_temp_period_months ,
1071 l_temp_period_years );
1072
1073 if idx=0 then
1074 idx:=idx+1;
1075 l_prev_emp_table(idx).start_date:=i.start_date;
1076 l_prev_emp_table(idx).end_date :=i.end_date;
1077 l_prev_emp_table(idx).active :=true;
1078 l_prev_emp_table(idx).years :=l_temp_period_years;
1079 l_prev_emp_table(idx).months :=l_temp_period_months;
1080 l_prev_emp_table(idx).days :=l_temp_period_days;
1081 else
1082 for j in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1083 if ( i.start_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1084 and i.end_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1085 and l_prev_emp_table(j).active
1086 )then
1087
1088 l_key:=1;
1089 exit;
1090 elsif ( i.start_date < l_prev_emp_table(j).start_date
1091 and i.end_date > l_prev_emp_table(j).end_date
1092 and l_prev_emp_table(j).active
1093 ) then
1094
1095 l_prev_emp_table(j).active:=false;
1096
1097 elsif ( i.start_date < l_prev_emp_table(j).start_date
1098 and i.end_date >= l_prev_emp_table(j).start_date
1099 and l_prev_emp_table(j).active
1100 ) then
1101
1102 l_prev_emp_table(j).start_date:=i.end_date+1;
1103
1104 elsif ( i.start_date <= l_prev_emp_table(j).end_date
1105 and i.end_date > l_prev_emp_table(j).end_date
1106 and l_prev_emp_table(j).active
1107 ) then
1108
1109 l_prev_emp_table(j).end_date:=i.start_date-1;
1110 end if;
1111
1112 if l_prev_emp_table(j).start_date > l_prev_emp_table(j).end_date then
1113 l_prev_emp_table(j).active:=false;
1114 end if;
1115 end loop;
1116 if l_key<>1 then
1117 idx:=idx+1;
1118 l_prev_emp_table(idx).start_date:=i.start_date;
1119 l_prev_emp_table(idx).end_date :=i.end_date;
1120 l_prev_emp_table(idx).active :=true;
1121 l_prev_emp_table(idx).years :=l_temp_period_years;
1122 l_prev_emp_table(idx).months :=l_temp_period_months;
1123 l_prev_emp_table(idx).days :=l_temp_period_days;
1124 end if;
1125 end if;
1126 end loop;
1127 hr_utility.set_location(l_proc,20);
1128 /*End of calculation for records in per_periods_of_service*/
1129
1130
1131 /*Calculation start for per_previous_employers*/
1132
1133 /*It involves calculation for 1) (Employer Type :Parallel
1134 or Employer Type :Previous )
1135 and start_date is not null
1136 and start_date> p_date
1137 2)Employer_type='PREVIOUS' and start_date is null
1138 */
1139 hr_utility.set_location(l_proc,30);
1140 for i in csr_prev_emp_with_start_date(l_person_id) loop
1141
1142 if i.employer_type='PARALLEL' then
1143 l_end_date:=p_date;
1144 else
1145 l_end_date:= i.end_date ;
1146 end if;
1147
1148 hr_pl_utility.per_pl_calc_periods
1149 (i.start_date ,
1150 l_end_date ,
1151 l_temp_period_days ,
1152 l_temp_period_months ,
1153 l_temp_period_years );
1154 if i.employer_type='PREVIOUS' then
1155 l_temp_period_days :=i.period_days ;
1156 l_temp_period_months :=i.period_months ;
1157 l_temp_period_years :=i.period_years ;
1158 end if;--employer_type='PREVIOUS'?
1159
1160 l_key:=0;
1161
1162 for j in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1163 if ( i.start_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1164 and l_end_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1165 and l_prev_emp_table(j).active
1166 )then
1167 l_key:=1;
1168 exit;
1169 elsif ( i.start_date < l_prev_emp_table(j).start_date
1170 and l_end_date > l_prev_emp_table(j).end_date
1171 and l_prev_emp_table(j).active
1172 ) then
1173
1174 l_prev_emp_table(j).active:=false;
1175
1176 elsif ( i.start_date < l_prev_emp_table(j).start_date
1177 and l_end_date >= l_prev_emp_table(j).start_date
1178 and l_prev_emp_table(j).active
1179 ) then
1180
1181 l_prev_emp_table(j).start_date:=l_end_date+1;
1182
1183 elsif ( i.start_date <= l_prev_emp_table(j).end_date
1184 and l_end_date > l_prev_emp_table(j).end_date
1185 and l_prev_emp_table(j).active
1186 ) then
1187
1188 l_prev_emp_table(j).end_date:=i.start_date-1;
1189 end if;
1190
1191 if l_prev_emp_table(j).start_date > l_prev_emp_table(j).end_date then
1192 l_prev_emp_table(j).active:=false;
1193 end if;
1194 end loop;
1195 if l_key<>1 then
1196 idx:=idx+1;
1197 l_prev_emp_table(idx).start_date:=i.start_date;
1198 l_prev_emp_table(idx).end_date :=l_end_date;
1199 l_prev_emp_table(idx).active :=true;
1200 l_prev_emp_table(idx).years :=l_temp_period_years;
1201 l_prev_emp_table(idx).months :=l_temp_period_months;
1202 l_prev_emp_table(idx).days :=l_temp_period_days;
1203 end if;
1204 end loop;
1205 hr_utility.set_location(l_proc,40);
1206 for kk in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1207 if l_prev_emp_table(kk).active then
1208 l_total_period_years :=l_total_period_years + l_prev_emp_table(kk).years;
1209 l_total_period_months :=l_total_period_months + l_prev_emp_table(kk).months;
1210 l_total_period_days :=l_total_period_days + l_prev_emp_table(kk).days;
1211 end if;
1212 end loop;
1213
1214 hr_utility.set_location(l_proc,50);
1215 for i in csr_pemp_without_start_date(l_person_id) loop
1216 l_total_period_years :=l_total_period_years + i.years;
1217 l_total_period_months :=l_total_period_months + i.months;
1218 l_total_period_days :=l_total_period_days + i.days;
1219 end loop;
1220
1221
1222 if l_total_period_days>29 then
1223 l_total_period_months:=l_total_period_months+ trunc(l_total_period_days/30);
1224 l_total_period_days:= mod(l_total_period_days,30);
1225 end if;
1226
1227 if l_total_period_months>11 then
1228 l_total_period_years:=l_total_period_years+trunc(l_total_period_months/12);
1229 l_total_period_months:=mod(l_total_period_months,12);
1230 end if;
1231
1232 p_years := l_total_period_years;
1233 p_months := l_total_period_months;
1234 p_days := l_total_period_days;
1235 hr_utility.set_location(l_proc,60);
1236 return 1;
1237 exception
1238 when others then
1239 hr_utility.set_location(l_proc,99);
1240 hr_utility.raise_error;
1241 end GET_TOTAL_PERIOD_OF_SERVICE;
1242
1243 PROCEDURE PER_PL_CHECK_NI_UNIQUE
1244 ( p_national_identifier VARCHAR2,
1245 p_person_id NUMBER,
1246 p_business_group_id NUMBER,
1247 p_legal_employer VARCHAR2)is
1248 l_status VARCHAR2(1);
1249 l_nat_lbl VARCHAR2(2000);
1250 local_warning exception;
1251 l_prof_val varchar2(30);
1252 begin
1253 SELECT 'Y'
1254 INTO l_status
1255 FROM sys.dual
1256 WHERE exists(SELECT '1'
1257 FROM per_all_people_f pp
1258 WHERE (p_person_id IS NULL
1259 OR p_person_id <> pp.person_id)
1260 AND p_national_identifier = pp.national_identifier
1261 AND pp.business_group_id +0 = p_business_group_id
1262 AND pp.per_information7 = p_legal_employer);
1263 l_prof_val := fnd_profile.value('PL_PER_NI_UNIQUE_ERROR_WARN');
1264 fnd_message.set_name('PER','HR_NATIONAL_ID_NUMBER_PL');
1265 l_nat_lbl := fnd_message.get;
1266 l_nat_lbl := rtrim(l_nat_lbl);
1267 if l_nat_lbl = 'HR_NATIONAL_ID_NUMBER_PL' then
1268 fnd_message.set_name('PER','HR_NATIONAL_IDENTIFIER_NUMBER');
1269 l_nat_lbl := fnd_message.get;
1270 l_nat_lbl := rtrim(l_nat_lbl);
1271 end if;
1272 if l_prof_val = 'ERROR' then
1273 hr_utility.set_message(801,'HR_NI_UNIQUE_ERROR');
1274 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
1275 hr_utility.raise_error;
1276 elsif l_prof_val = 'WARNING' then
1277 hr_utility.set_message(801,'HR_NI_UNIQUE_WARNING');
1278 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
1279 raise local_warning;
1280 end if;
1281 exception
1282 when no_data_found then null;
1283 when local_warning then
1284 hr_utility.set_warning;
1285 end per_pl_check_ni_unique;
1286
1287 END hr_pl_utility;