[Home] [Help]
PACKAGE BODY: APPS.HR_PL_UTILITY
Source
1 PACKAGE BODY hr_pl_utility as
2 /* $Header: peplutil.pkb 120.7.12010000.2 2008/11/10 12:37:41 ubhat 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 number;
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 -- Procedure per_pl_calc_periods --
520 --------------------------------------------------------------------------------
521 PROCEDURE per_pl_calc_periods(p_start_date IN DATE,
522 p_end_date IN DATE,
523 p_days IN OUT NOCOPY NUMBER,
524 p_months IN OUT NOCOPY NUMBER,
525 p_years IN OUT NOCOPY NUMBER)
526 IS
527 dStartdate Date;
528 dEnddate Date;
529 dDays number;
530 dMonths number;
531 nStartdate Date;
532 nEnddate Date;
533 dYear number:=0;
534 BEGIN
535 dStartdate:=p_start_date;
536 dEnddate:=p_end_date;
537
538 if last_day(dStartdate -1) = (dStartdate - 1) and last_day(dEnddate) = dEnddate then
539 dMonths:= months_between(dEnddate,dStartdate-1);
540 dDays:=0;
541 elsif last_day(dStartdate -1) = (dStartdate - 1) then
542 nStartdate:=dStartdate -1;
543 nEnddate:=trunc(dEnddate,'MM')-1;
544 dMonths:= months_between(nEnddate,nStartdate);
545 dDays:=to_number(to_char(dEnddate,'dd'));
546 elsif last_day(dEnddate) = dEnddate then
547 nStartdate:=last_day(dStartdate);
548 nEnddate:=dEnddate;
549 dMonths:= months_between(nEnddate,nStartdate);
550 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'));
551 elsif to_char(dStartdate,'Mon') = to_char(dEnddate,'Mon') Then
552 dMonths:= months_between(dEnddate,dStartdate);
553 if to_char(dStartdate,'dd') <= to_char(dEnddate,'dd') Then
554 dDays:= to_number(to_char(dEnddate,'dd')) - to_number(to_char(dStartdate,'dd'))+1;
555 else
556 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'))
557 +to_number(to_char(dEnddate,'dd'));
558 end if;
559 else
560 nStartdate:=last_day(dStartdate);
561 nEnddate:=trunc(dEnddate,'MM')-1;
562 dMonths:= months_between(nEnddate,nStartdate);
563 dDays:=to_number(to_char(last_day(dStartdate),'dd'))-to_number(to_char(dStartdate-1,'dd'))
564 +to_number(to_char(dEnddate,'dd'));
565 end if;
566 dMonths:= trunc(dMonths);
567 If dDays >= 30 then
568 dMonths:=dMonths+trunc(dDays/30);
569 dDays :=mod(dDays,30);
570 End If;
571
572 If dMonths >= 12 then
573 dYear := trunc(dMonths/12);
574 dMonths:= mod(dMonths,12);
575 end if;
576 p_years:= dYear;
577 p_months := dMonths;
578 p_days := dDays;
579
580 END per_pl_calc_periods;
581 -- End of per_pl_calc_periods
582
583
584
585 /************************************************************************************************/
586 /*
587 This function returns either 0 or 1. If it returns 0, then an incorrect value of
588 description for the lookup Employee Category has been specified. This is called thru the formual function
589 */
590
591 /**********************************************************************************************/
592 /*
593
594 Inputs : 1) Person id from per_all_people_f
595 2) Code of the type of Service. This code can be picked up from lookup EMPLOYEE_CATG
596
597 Outputs : 1) Number of Years
598 2) Number of Months
599 3) Number of days
600 4) Message (This message will be null if correct code has been passed else an appropriate error message
601 is thrown
602
603
604 Return : This function return 1 if successful else 0
605 */
606 /********************************************************************************************/
607
608 function GET_LENGTH_OF_SERVICE(P_PERSON_ID IN NUMBER,
609 P_TYPE_OF_SERVICE IN VARCHAR2, -- This is the code of the Category
610 l_years OUT NOCOPY NUMBER,
611 l_months OUT NOCOPY NUMBER,
612 l_days OUT NOCOPY NUMBER,
613 l_message OUT NOCOPY VARCHAR2)
614 RETURN number IS
615
616
617
618 lookup_type_val fnd_common_lookups.lookup_type%TYPE;
619 lookup_descr_val1 fnd_common_lookups.description%TYPE;
620 lookup_descr_val2 fnd_common_lookups.description%TYPE;
621
622 cursor csr_service_period_dts is
623 select ppj.start_date, ppj.end_date,
624 ppj.period_years, ppj.period_months,
625 ppj.period_days
626 from per_previous_jobs ppj, per_previous_employers ppe
627 where ppe.person_id = P_PERSON_ID
628 and ppe.previous_employer_id = ppj.previous_employer_id
629 and ppj.PJO_INFORMATION1 = P_TYPE_OF_SERVICE -- Replaced with PJO_INFORMATION1
630 and ppj.start_date IS NOT NULL
631 AND ppj.end_date IS NOT NULL
632 order by ppj.start_date, ppj.end_date;
633
634 cursor csr_service_period_ymd is
635 select ppj.start_date, ppj.end_date,
636 ppj.period_years, ppj.period_months,
637 ppj.period_days
638 from per_previous_jobs ppj, per_previous_employers ppe
639 where ppe.person_id = P_PERSON_ID
640 and ppe.previous_employer_id = ppj.previous_employer_id
641 and ppj.PJO_INFORMATION1 = P_TYPE_OF_SERVICE -- Replaced with PJO_INFORMATION1
642 and ppj.start_date IS NULL
643 AND ppj.end_date IS NULL ;
644
645 cursor csr_type_of_service is
646 select description
647 from hr_lookups -- Replaced with hr_lookups
648 where lookup_type = lookup_type_val
649 and lookup_code = P_TYPE_OF_SERVICE
650 and description in (lookup_descr_val1, lookup_descr_val2);
651
652 idx number := 0;
653 ind number := 0;
654 idx1 number := 0;
655 ind1 number := 0;
656
657 TYPE service_rec IS RECORD (
658 sdate date,
659 edate date,
660 years NUMBER,
661 months NUMBER,
662 days NUMBER);
663
664 TYPE service_table IS TABLE OF service_rec INDEX BY BINARY_INTEGER;
665
666 g_user_service_table_dts service_table;
667 g_user_service_table_ymd service_table;
668
669 total_service_years per_previous_jobs.period_years%TYPE;
670 total_service_months per_previous_jobs.period_months%TYPE;
671 total_service_days per_previous_jobs.period_days%TYPE;
672 service_type fnd_common_lookups.description%TYPE;
673
674 temp_days number := 0 ;
675 temp_months number := 0 ;
676 temp_years number := 0 ;
677
678 message varchar2(2400);
679 l_number number;
680 flag varchar2(1);
681 message_cat VARCHAR2(3);
682 message_name fnd_new_messages.message_name%TYPE;
683
684 cursor csr_error_message is
685 select fnd_message.get_string(message_cat,message_name)
686 from dual;
687
688 BEGIN
689 lookup_type_val := 'PL_TYPE_OF_SERVICE';
690 lookup_descr_val1 := '01';
691 lookup_descr_val2 := '02';
692 l_number := 1; /* Default of 1(one) means success */
693 flag := 'N';
694 message := NULL;
695 message_cat := 'PER';
696 message_name := 'HR_INVALID_ALGO_JOBTYPE_PL';
697
698
699 --
700 -- Check if a valid algorithm has been specified for the type of service
701 --
702 open csr_type_of_service;
703 fetch csr_type_of_service into service_type;
704
705 if csr_type_of_service%NOTFOUND then
706 total_service_years := 0;
707 total_service_months := 0;
708 total_service_days := 0;
709
710 open csr_error_message;
711 fetch csr_error_message into message;
712 close csr_error_message;
713
714 l_years := total_service_years;
715 l_months := total_service_months;
716 l_days := total_service_days;
717 l_message := message;
718 l_number := 0;
719 return l_number;
720 end if;
721 close csr_type_of_service;
722
723 --
724 -- Initialise the result variables
725 --
726 total_service_years := 0;
727 total_service_months := 0;
728 total_service_days := 0;
729 idx := 0;
730
731 IF service_type = '02' THEN --- for connected periods
732 FOR svcrec in csr_service_period_dts LOOP
733 -- First Record
734 IF idx = 0 THEN
735 idx := idx + 1;
736 g_user_service_table_dts(idx).sdate := svcrec.start_date;
737 g_user_service_table_dts(idx).edate := svcrec.end_date;
738 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
739 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
740 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
741
742 -- Complete Range already covered
743 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
744 AND g_user_service_table_dts(idx).edate ) AND
745 ( svcrec.end_date BETWEEN g_user_service_table_dts(idx).sdate
746 AND g_user_service_table_dts(idx).edate )
747 THEN
748 null;
749
750 -- Partial Range Covered
751 ELSIF ( svcrec.start_date BETWEEN g_user_service_table_dts(idx).sdate
752 AND g_user_service_table_dts(idx).edate ) AND
753 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
754 THEN
755 g_user_service_table_dts(idx).edate := svcrec.end_date;
756
757 -- Range Not Covered
758 ELSIF ( svcrec.start_date > g_user_service_table_dts(idx).edate ) AND
759 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
760 THEN
761 -- Is this range contigous
762 IF ( svcrec.start_Date = g_user_service_table_dts(idx).edate +1 ) THEN
763 g_user_service_table_dts(idx).edate := svcrec.end_date;
764 ELSE -- not contigous
765 idx := idx + 1;
766 g_user_service_table_dts(idx).sdate := svcrec.start_date;
767 g_user_service_table_dts(idx).edate := svcrec.end_date;
768 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
769 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
770 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
771 END IF;
772 END IF;
773
774 END LOOP;
775
776 ELSE --- else for service_type , now for separated periods
777
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 idx := idx + 1;
802 g_user_service_table_dts(idx).sdate := g_user_service_table_dts(idx-1).edate +1 ;
803 g_user_service_table_dts(idx).edate := svcrec.end_date;
804
805 -- Range Not Covered
806 ELSIF ( svcrec.start_date > g_user_service_table_dts(idx).edate ) AND
807 ( svcrec.end_date > g_user_service_table_dts(idx).edate )
808 THEN
809 -- Is this range contigous
810 IF ( svcrec.start_Date = g_user_service_table_dts(idx).edate +1 ) THEN
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 ELSE -- not contigous
815 idx := idx + 1;
816 g_user_service_table_dts(idx).sdate := svcrec.start_date;
817 g_user_service_table_dts(idx).edate := svcrec.end_date;
818 g_user_service_table_dts(idx).years := nvl(svcrec.period_years,0);
819 g_user_service_table_dts(idx).months := nvl(svcrec.period_months,0);
820 g_user_service_table_dts(idx).days := nvl(svcrec.period_days,0);
821 END IF;
822 END IF;
823 END LOOP;
824
825 END IF; -- for service_type
826
827 ind := 0 ;
828 FOR ymdrec in csr_service_period_ymd LOOP
829 ind := ind + 1;
830 g_user_service_table_ymd(ind).sdate := ymdrec.start_date;
831 g_user_service_table_ymd(ind).edate := ymdrec.end_date;
832 g_user_service_table_ymd(ind).years := nvl(ymdrec.period_years,0);
833 g_user_service_table_ymd(ind).months := nvl(ymdrec.period_months,0);
834 g_user_service_table_ymd(ind).days := nvl(ymdrec.period_days,0);
835 END LOOP;
836
837
838 --
839 -- Get the YMD from the dates , status = Automatic
840 --
841 IF idx > 0 THEN
842 FOR i in g_user_service_table_dts.FIRST .. g_user_service_table_dts.LAST LOOP
843 idx1 := idx1 + 1;
844 hr_pl_utility.per_pl_calc_periods ( g_user_service_table_dts(idx1).sdate,
845 g_user_service_table_dts(idx1).edate,
846 temp_days,
847 temp_months,
848 temp_years ) ;
849
850 total_service_years := total_service_years + temp_years ;
851 total_service_months := total_service_months + temp_months;
852 total_service_days := total_service_days + temp_days;
853 END LOOP;
854 END IF;
855
856 --
857 -- Get the YMD from the ymd , status = Manual
858 --
859 IF ind > 0 THEN
860 FOR i in g_user_service_table_ymd.FIRST .. g_user_service_table_ymd.LAST LOOP
861 ind1 := ind1 + 1;
862 total_service_years := total_service_years + g_user_service_table_ymd(ind1).years;
863 total_service_months := total_service_months + g_user_service_table_ymd(ind1).months;
864 total_service_days := total_service_days + g_user_service_table_ymd(ind1).days;
865 END LOOP;
866 END IF;
867
868
869
870 IF total_service_days > 29 THEN
871 total_service_months := total_service_months + trunc(total_service_days /30);
872 total_service_days := mod (total_service_days ,30);
873 END IF;
874 IF total_service_months > 11 THEN
875 total_service_years := total_service_years + trunc(total_service_months /12);
876 total_service_months := mod (total_service_months ,12);
877 END IF;
878
879 l_years := total_service_years;
880 l_months := total_service_months;
881 l_days := total_service_days;
882
883 return l_number;
884
885 END GET_LENGTH_OF_SERVICE;
886
887 FUNCTION CHECK_CONTRIBUTION_TYPE(P_ENTRY_VALUE VARCHAR2) return NUMBER is
888 l_out number:=0;
889 cursor csr_lookup_code is
890 select lookup_code
891 from hr_lookups
892 where LOOKUP_TYPE='PL_CONTRIBUTION_TYPE'
893 and lookup_code=P_ENTRY_VALUE;
894 l_value hr_lookups.lookup_code%TYPE;
895 begin
896 l_value := NULL;
897 open csr_lookup_code;
898 fetch csr_lookup_code into l_value;
899 close csr_lookup_code;
900 if l_value is not null then
901 l_out := 1;
902 end if;
903
904 return l_out;
905
906 end CHECK_CONTRIBUTION_TYPE;
907
908 FUNCTION GET_VEHICLE_MILEAGE(p_date_earned IN DATE,
909 p_vehicle_allocation_id IN NUMBER,
910 p_monthly_mileage_limit OUT NOCOPY NUMBER,
911 p_engine_capacity_in_cc OUT NOCOPY NUMBER,
912 p_vehicle_type OUT NOCOPY VARCHAR2) RETURN NUMBER is
913
914 Cursor csr_vehicle is select
915 puci.value,
916 pvrf.engine_capacity_in_cc,
917 pvrf.vehicle_type,
918 pvaf.val_information3
919 from
920 pqp_vehicle_allocations_f pvaf,
921 pay_user_column_instances_f puci,
922 pqp_vehicle_repository_f pvrf
923 where
924 pvaf.val_information2 = puci.user_column_instance_id and
925 pvaf.vehicle_repository_id = pvrf.vehicle_repository_id and
926 pvaf.vehicle_allocation_id = p_vehicle_allocation_id and
927 p_date_earned between pvaf.effective_start_date and pvaf.effective_end_date and
928 p_date_earned between pvrf.effective_start_date and pvrf.effective_end_date and
929 p_date_earned between puci.effective_start_date and puci.effective_end_date;
930
931 l_val_information3 number;
932
933 BEGIN
934
935 open csr_vehicle;
936 fetch csr_vehicle into p_monthly_mileage_limit,p_engine_capacity_in_cc,p_vehicle_type,l_val_information3;
937 close csr_vehicle;
938
939 If p_monthly_mileage_limit is null then
940 return 0;
941 else
942 return l_val_information3;
943 -- returning the value Monthly mileage limit by emp for bug 4576456
944 end if;
945
946 END GET_VEHICLE_MILEAGE;
947
948 /*Start of function GET_TOTAL_PERIOD_OF_SERVICE*/
949 FUNCTION GET_TOTAL_PERIOD_OF_SERVICE
950 (p_assignment_id in number,
951 p_date in date,
952 p_years OUT NOCOPY NUMBER,
953 p_months OUT NOCOPY NUMBER,
954 p_days OUT NOCOPY NUMBER) return number is
955
956 cursor csr_person_id is
957 select person_id
958 from per_all_assignments_f
959 where assignment_id=p_assignment_id;
960
961 cursor csr_per_periods_of_service(r_person_id per_all_people_f.person_id%type) is
962 select date_start start_date,nvl(ACTUAL_TERMINATION_DATE ,p_date) end_date
963 from per_periods_of_service
964 where person_id=r_person_id;
965
966 cursor csr_pemp_without_start_date(r_person_id per_all_people_f.person_id%type) is
967 select nvl(period_months,0) months,nvl(period_days,0) days,nvl(period_years,0) years
968 from PER_PREVIOUS_EMPLOYERS
969 where person_id=r_person_id
970 and employer_type='PREVIOUS'
971 and start_date is null;
972
973 cursor csr_prev_emp_with_start_date(r_person_id per_all_people_f.person_id%type) is
974 select start_date ,end_date,period_months,period_days,period_years,employer_type
975 from PER_PREVIOUS_EMPLOYERS
976 where person_id=r_person_id
977 and employer_type in ('PREVIOUS','PARALLEL')
978 and start_date is not null
979 and start_date<p_date;
980
981 TYPE prev_emp_rec IS RECORD (
982 start_date date,
983 end_date date,
984 years NUMBER,
985 months NUMBER,
986 days NUMBER,
987 active boolean
988 );
989 TYPE prev_emp_table IS TABLE OF prev_emp_rec INDEX BY BINARY_INTEGER;
990
991
992 l_prev_emp_table prev_emp_table;
993 l_key number ;
994 idx number := 0;
995 l_end_date date;
996 l_person_id per_all_people_f.person_id%type;
997
998 l_temp_period_years number;
999 l_temp_period_months number;
1000 l_temp_period_days number;
1001
1002 l_total_period_years number:=0;
1003 l_total_period_months number:=0;
1004 l_total_period_days number:=0;
1005
1006 l_proc varchar2(41);
1007
1008 begin
1009
1010 l_proc:='HR_PL_UTILITY.GET_TOTAL_PERIOD_OF_SERVICE';
1011 hr_utility.set_location(l_proc,10);
1012 open csr_person_id;
1013 fetch csr_person_id into l_person_id;
1014 close csr_person_id;
1015
1016
1017 /*start of calculation for per_periods_of_service*/
1018
1019 for i in csr_per_periods_of_service(l_person_id) loop
1020 hr_pl_utility.per_pl_calc_periods
1021 (i.start_date ,
1022 i.end_date ,
1023 l_temp_period_days ,
1024 l_temp_period_months ,
1025 l_temp_period_years );
1026
1027 if idx=0 then
1028 idx:=idx+1;
1029 l_prev_emp_table(idx).start_date:=i.start_date;
1030 l_prev_emp_table(idx).end_date :=i.end_date;
1031 l_prev_emp_table(idx).active :=true;
1032 l_prev_emp_table(idx).years :=l_temp_period_years;
1033 l_prev_emp_table(idx).months :=l_temp_period_months;
1034 l_prev_emp_table(idx).days :=l_temp_period_days;
1035 else
1036 for j in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1037 if ( i.start_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1038 and i.end_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1039 and l_prev_emp_table(j).active
1040 )then
1041
1042 l_key:=1;
1043 exit;
1044 elsif ( i.start_date < l_prev_emp_table(j).start_date
1045 and i.end_date > l_prev_emp_table(j).end_date
1046 and l_prev_emp_table(j).active
1047 ) then
1048
1049 l_prev_emp_table(j).active:=false;
1050
1051 elsif ( i.start_date < l_prev_emp_table(j).start_date
1052 and i.end_date >= l_prev_emp_table(j).start_date
1053 and l_prev_emp_table(j).active
1054 ) then
1055
1056 l_prev_emp_table(j).start_date:=i.end_date+1;
1057
1058 elsif ( i.start_date <= l_prev_emp_table(j).end_date
1059 and i.end_date > l_prev_emp_table(j).end_date
1060 and l_prev_emp_table(j).active
1061 ) then
1062
1063 l_prev_emp_table(j).end_date:=i.start_date-1;
1064 end if;
1065
1066 if l_prev_emp_table(j).start_date > l_prev_emp_table(j).end_date then
1067 l_prev_emp_table(j).active:=false;
1068 end if;
1069 end loop;
1070 if l_key<>1 then
1071 idx:=idx+1;
1072 l_prev_emp_table(idx).start_date:=i.start_date;
1073 l_prev_emp_table(idx).end_date :=i.end_date;
1074 l_prev_emp_table(idx).active :=true;
1075 l_prev_emp_table(idx).years :=l_temp_period_years;
1076 l_prev_emp_table(idx).months :=l_temp_period_months;
1077 l_prev_emp_table(idx).days :=l_temp_period_days;
1078 end if;
1079 end if;
1080 end loop;
1081 hr_utility.set_location(l_proc,20);
1082 /*End of calculation for records in per_periods_of_service*/
1083
1084
1085 /*Calculation start for per_previous_employers*/
1086
1087 /*It involves calculation for 1) (Employer Type :Parallel
1088 or Employer Type :Previous )
1089 and start_date is not null
1090 and start_date> p_date
1091 2)Employer_type='PREVIOUS' and start_date is null
1092 */
1093 hr_utility.set_location(l_proc,30);
1094 for i in csr_prev_emp_with_start_date(l_person_id) loop
1095
1096 if i.employer_type='PARALLEL' then
1097 l_end_date:=p_date;
1098 else
1099 l_end_date:= i.end_date ;
1100 end if;
1101
1102 hr_pl_utility.per_pl_calc_periods
1103 (i.start_date ,
1104 l_end_date ,
1105 l_temp_period_days ,
1106 l_temp_period_months ,
1107 l_temp_period_years );
1108 if i.employer_type='PREVIOUS' then
1109
1110
1111 l_temp_period_days :=i.period_days ;
1112 l_temp_period_months :=i.period_months ;
1113 l_temp_period_years :=i.period_years ;
1114
1115 end if;--employer_type='PREVIOUS'?
1116
1117 l_key:=0;
1118
1119 for j in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1120 if ( i.start_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1121 and l_end_date between l_prev_emp_table(j).start_date and l_prev_emp_table(j).end_date
1122 and l_prev_emp_table(j).active
1123 )then
1124 l_key:=1;
1125 exit;
1126 elsif ( i.start_date < l_prev_emp_table(j).start_date
1127 and l_end_date > l_prev_emp_table(j).end_date
1128 and l_prev_emp_table(j).active
1129 ) then
1130
1131 l_prev_emp_table(j).active:=false;
1132
1133 elsif ( i.start_date < l_prev_emp_table(j).start_date
1134 and l_end_date >= l_prev_emp_table(j).start_date
1135 and l_prev_emp_table(j).active
1136 ) then
1137
1138 l_prev_emp_table(j).start_date:=l_end_date+1;
1139
1140 elsif ( i.start_date <= l_prev_emp_table(j).end_date
1141 and l_end_date > l_prev_emp_table(j).end_date
1142 and l_prev_emp_table(j).active
1143 ) then
1144
1145 l_prev_emp_table(j).end_date:=i.start_date-1;
1146 end if;
1147
1148 if l_prev_emp_table(j).start_date > l_prev_emp_table(j).end_date then
1149 l_prev_emp_table(j).active:=false;
1150 end if;
1151 end loop;
1152 if l_key<>1 then
1153 idx:=idx+1;
1154 l_prev_emp_table(idx).start_date:=i.start_date;
1155 l_prev_emp_table(idx).end_date :=l_end_date;
1156 l_prev_emp_table(idx).active :=true;
1157 l_prev_emp_table(idx).years :=l_temp_period_years;
1158 l_prev_emp_table(idx).months :=l_temp_period_months;
1159 l_prev_emp_table(idx).days :=l_temp_period_days;
1160 end if;
1161 end loop;
1162 hr_utility.set_location(l_proc,40);
1163 for kk in l_prev_emp_table.FIRST..l_prev_emp_table.LAST loop
1164 if l_prev_emp_table(kk).active then
1165 l_total_period_years :=l_total_period_years + l_prev_emp_table(kk).years;
1166 l_total_period_months :=l_total_period_months + l_prev_emp_table(kk).months;
1167 l_total_period_days :=l_total_period_days + l_prev_emp_table(kk).days;
1168 end if;
1169 end loop;
1170
1171 hr_utility.set_location(l_proc,50);
1172 for i in csr_pemp_without_start_date(l_person_id) loop
1173 l_total_period_years :=l_total_period_years + i.years;
1174 l_total_period_months :=l_total_period_months + i.months;
1175 l_total_period_days :=l_total_period_days + i.days;
1176 end loop;
1177
1178
1179 if l_total_period_days>29 then
1180 l_total_period_months:=l_total_period_months+ trunc(l_total_period_days/30);
1181 l_total_period_days:= mod(l_total_period_days,30);
1182 end if;
1183
1184 if l_total_period_months>11 then
1185 l_total_period_years:=l_total_period_years+trunc(l_total_period_months/12);
1186 l_total_period_months:=mod(l_total_period_months,12);
1187 end if;
1188
1189 p_years := l_total_period_years;
1190 p_months := l_total_period_months;
1191 p_days := l_total_period_days;
1192 hr_utility.set_location(l_proc,60);
1193 return 1;
1194 exception
1195 when others then
1196 hr_utility.set_location(l_proc,99);
1197 hr_utility.raise_error;
1198 end GET_TOTAL_PERIOD_OF_SERVICE;
1199
1200 PROCEDURE PER_PL_CHECK_NI_UNIQUE
1201 ( p_national_identifier VARCHAR2,
1202 p_person_id NUMBER,
1203 p_business_group_id NUMBER,
1204 p_legal_employer VARCHAR2)is
1205 l_status VARCHAR2(1);
1206 l_nat_lbl VARCHAR2(2000);
1207 local_warning exception;
1208 l_prof_val varchar2(30);
1209 begin
1210 SELECT 'Y'
1211 INTO l_status
1212 FROM sys.dual
1213 WHERE exists(SELECT '1'
1214 FROM per_all_people_f pp
1215 WHERE (p_person_id IS NULL
1216 OR p_person_id <> pp.person_id)
1217 AND p_national_identifier = pp.national_identifier
1218 AND pp.business_group_id +0 = p_business_group_id
1219 AND pp.per_information7 = p_legal_employer);
1220 l_prof_val := fnd_profile.value('PL_PER_NI_UNIQUE_ERROR_WARN');
1221 fnd_message.set_name('PER','HR_NATIONAL_ID_NUMBER_PL');
1222 l_nat_lbl := fnd_message.get;
1223 l_nat_lbl := rtrim(l_nat_lbl);
1224 if l_nat_lbl = 'HR_NATIONAL_ID_NUMBER_PL' then
1225 fnd_message.set_name('PER','HR_NATIONAL_IDENTIFIER_NUMBER');
1226 l_nat_lbl := fnd_message.get;
1227 l_nat_lbl := rtrim(l_nat_lbl);
1228 end if;
1229 if l_prof_val = 'ERROR' then
1230 hr_utility.set_message(801,'HR_NI_UNIQUE_ERROR');
1231 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
1232 hr_utility.raise_error;
1233 elsif l_prof_val = 'WARNING' then
1234 hr_utility.set_message(801,'HR_NI_UNIQUE_WARNING');
1235 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
1236 raise local_warning;
1237 end if;
1238 exception
1239 when no_data_found then null;
1240 when local_warning then
1241 hr_utility.set_warning;
1242 end per_pl_check_ni_unique;
1243
1244 END hr_pl_utility;