[Home] [Help]
PACKAGE BODY: APPS.HR_ES_UTILITY
Source
1 PACKAGE BODY hr_es_utility as
2 /* $Header: peesutil.pkb 120.2.12010000.4 2009/12/20 07:50:00 rpahune ship $ */
3 --------------------------------------------------------------------------------
4 -- FUNCTION check_DNI
5 --------------------------------------------------------------------------------
6 FUNCTION check_DNI(p_identifier_value VARCHAR2) RETURN VARCHAR2 AS
7 --
8 v_dni_return VARCHAR2(30);
9 BEGIN
10 -- For Bug 3358291 did the SUBSTR of p_identifier_value
11 v_dni_return := hr_ni_chk_pkg.chk_nat_id_format(substr(p_identifier_value,1,30)
12 ,'DDDDDDDD');
13 IF (v_dni_return='0') THEN
14 RETURN 'N';
15 ELSE
16 RETURN 'Y';
17 END IF;
18 --
19 END check_DNI;
20 --
21 --------------------------------------------------------------------------------
22 -- FUNCTION check_NIF
23 --------------------------------------------------------------------------------
24 FUNCTION check_NIF(p_identifier_value VARCHAR2) RETURN VARCHAR2 AS
25 --
26 TYPE nif_tab IS TABLE OF CHAR INDEX BY BINARY_INTEGER;
27 v_nif nif_tab;
28 v_nif_num VARCHAR2(8);
29 v_nif_mod NUMBER(2);
30 v_nif_return VARCHAR2(9);
31 v_return VARCHAR2(30);
32 --
33 BEGIN
34 --
35 v_nif(1) := 'T';
36 v_nif(2) := 'R';
37 v_nif(3) := 'W';
38 v_nif(4) := 'A';
39 v_nif(5) := 'G';
40 v_nif(6) := 'M';
41 v_nif(7) := 'Y';
42 v_nif(8) := 'F';
43 v_nif(9) := 'P';
44 v_nif(10) := 'D';
45 v_nif(11) := 'X';
46 v_nif(12) := 'B';
47 v_nif(13) := 'N';
48 v_nif(14) := 'J';
49 v_nif(15) := 'Z';
50 v_nif(16) := 'S';
51 v_nif(17) := 'Q';
52 v_nif(18) := 'V';
53 v_nif(19) := 'H';
54 v_nif(20) := 'L';
55 v_nif(21) := 'C';
56 v_nif(22) := 'K';
57 v_nif(23) := 'E';
58 --
59 v_return := hr_ni_chk_pkg.chk_nat_id_format(substr(p_identifier_value,1,30)
60 ,'DDDDDDDDA');
61 IF v_return = '0' THEN
62 RETURN 'N';
63 ELSE
64 v_nif_num := substr(p_identifier_value,1,8);
65 v_nif_mod := mod(to_number(v_nif_num),23) + 1;
66 v_nif_return := v_nif_num||v_nif(v_nif_mod);
67 IF (v_nif_return=p_identifier_value) THEN
68 RETURN 'Y';
69 ELSE
70 RETURN 'N';
71 END IF;
72 END IF;
73 --
74 END check_NIF;
75 --
76 --------------------------------------------------------------------------------
77 -- FUNCTION check_NIE
78 --------------------------------------------------------------------------------
79 FUNCTION check_NIE(p_identifier_value VARCHAR2) RETURN VARCHAR2 AS
80 --
81 v_nie_return VARCHAR2(30);
82 --
83 BEGIN
84 -- Bug 7214735
85 -- Changing the valid format for NIE to ADDDDDDDDA ( like A12345678Z ),
86 -- i.e, 8 digits inplace of 7
87 -- v_nie_return := hr_ni_chk_pkg.chk_nat_id_format(substr(p_identifier_value,1,30)
88 -- ,'ADDDDDDDA');
89 v_nie_return := hr_ni_chk_pkg.chk_nat_id_format(substr(p_identifier_value,1,30)
90 ,'ADDDDDDDDA');
91
92 IF (v_nie_return='0') THEN
93 RETURN 'N';
94 ELSE
95 RETURN 'Y';
96 END IF;
97 END check_NIE;
98 --
99 --------------------------------------------------------------------------------
100 -- FUNCTION per_es_full_name
101 --------------------------------------------------------------------------------
102 FUNCTION per_es_full_name(
103 p_first_name IN VARCHAR2
104 ,p_middle_names IN VARCHAR2
105 ,p_last_name IN VARCHAR2
106 ,p_known_as IN VARCHAR2
107 ,p_title IN VARCHAR2
108 ,p_suffix IN VARCHAR2
109 ,p_pre_name_adjunct IN VARCHAR2
110 ,p_per_information1 IN VARCHAR2
111 ,p_per_information2 IN VARCHAR2
112 ,p_per_information3 IN VARCHAR2
113 ,p_per_information4 IN VARCHAR2
114 ,p_per_information5 IN VARCHAR2
115 ,p_per_information6 IN VARCHAR2
116 ,p_per_information7 IN VARCHAR2
117 ,p_per_information8 IN VARCHAR2
118 ,p_per_information9 IN VARCHAR2
119 ,p_per_information10 IN VARCHAR2
120 ,p_per_information11 IN VARCHAR2
121 ,p_per_information12 IN VARCHAR2
122 ,p_per_information13 IN VARCHAR2
123 ,p_per_information14 IN VARCHAR2
124 ,p_per_information15 IN VARCHAR2
125 ,p_per_information16 IN VARCHAR2
126 ,p_per_information17 IN VARCHAR2
127 ,p_per_information18 IN VARCHAR2
128 ,p_per_information19 IN VARCHAR2
129 ,p_per_information20 IN VARCHAR2
130 ,p_per_information21 IN VARCHAR2
131 ,p_per_information22 IN VARCHAR2
132 ,p_per_information23 IN VARCHAR2
133 ,p_per_information24 IN VARCHAR2
134 ,p_per_information25 IN VARCHAR2
135 ,p_per_information26 IN VARCHAR2
136 ,p_per_information27 IN VARCHAR2
137 ,p_per_information28 IN VARCHAR2
138 ,p_per_information29 IN VARCHAR2
139 ,p_per_information30 IN VARCHAR2)
140 RETURN VARCHAR2 IS
141 --
142 l_full_name VARCHAR2(240);
143 --
144 BEGIN
145 --
146 SELECT substr(LTRIM(RTRIM(
147 DECODE(p_last_name, NULL, '', ' ' || p_last_name)
148 ||DECODE(p_per_information1, NULL,'',' ' || p_per_information1)
149 ||DECODE(p_first_name,NULL, '', ', ' || p_first_name)
150 )), 1, 240)
151 INTO l_full_name
152 FROM dual;
153 RETURN(l_full_name);
154 --
155 END per_es_full_name;
156 --------------------------------------------------------------------------------
157 -- FUNCTION validate_identifier
158 --------------------------------------------------------------------------------
159 FUNCTION validate_identifier(p_identifier_type VARCHAR2
160 ,p_identifier_value VARCHAR2) RETURN VARCHAR2 IS
161 --
162 l_value VARCHAR2(3);
163 --
164 BEGIN
165 IF (p_identifier_type='DNI') THEN
166 l_value := check_DNI(p_identifier_value);
167 IF l_value <> 'Y' THEN
168 hr_utility.set_message(800, 'HR_ES_INVALID_DNI');
169 hr_utility.raise_error;
170 END IF;
171 ELSIF(p_identifier_type='NIE') THEN
172 l_value := check_NIE(p_identifier_value);
173 IF l_value <> 'Y' THEN
174 hr_utility.set_message(800, 'HR_ES_INVALID_NIE');
175 hr_utility.raise_error;
176 END IF;
177 ELSIF(p_identifier_type NOT IN('DNI','NIE')
178 AND p_identifier_value IS NOT NULL) THEN
179 l_value := 'Y';
180 END IF;
181 --
182 RETURN l_value;
183 --
184 END validate_identifier;
185
186 --------------------------------------------------------------------------------
187 -- FUNCTION validate Account account_no
188 --------------------------------------------------------------------------------
189 FUNCTION validate_account_no (p_bank_code VARCHAR2 default null
190 ,p_branch_code VARCHAR2 default null
191 ,p_account_number VARCHAR2 default null
192 ,p_validation_code VARCHAR2 default null
193 ,p_acc_type varchar2
194 ,p_iban_acc varchar2 default null) return number IS
195
196 l_ret number;
197 begin
198 l_ret := 0;
199 --------------------------------------------------------------------------------
200 -- If account type is normal (N) call the validate_non_IBAN_acc_no
201 --------------------------------------------------------------------------------
202 if p_acc_type = 'N' then
203 if p_account_number is null then
204 l_ret := 2;
205 return l_ret;
206 end if;
207 if ( p_bank_code is not null and p_branch_code is not null and
208 p_account_number is not null and p_validation_code is not null) then
209
210 l_ret := validate_non_IBAN_acc_no (
211 p_bank_code => p_bank_code
212 ,p_branch_code => p_branch_code
213 ,p_account_number => p_account_number
214 ,p_validation_code => p_validation_code
215 );
216 -- l_ret will have 1 if validation paased.
217 end if;
218 return l_ret;
219 end if;
220 --------------------------------------------------------------------------------
221 -- If account type is IBAN (Y) call the validate_iban_acc
222 --------------------------------------------------------------------------------
223 if p_acc_type = 'Y' then
224 if p_iban_acc is null then
225 l_ret :=2;
226 else
227 l_ret := validate_iban_acc
228 ( p_account_no => p_iban_acc
229 );
230 end if;
231 return l_ret;
232 end if;
233 --------------------------------------------------------------------------------
234 -- If account type is Combine (C) check if at least one account is not null
235 --------------------------------------------------------------------------------
236
237 IF p_acc_type = 'C' and (p_account_number is not null
238 or p_iban_acc is not null) then
239 hr_utility.set_location(' inside first if',1);
240 return l_ret;
241 end if;
242 l_ret := 3;
243 return l_ret;
244 end validate_account_no;
245
246 --------------------------------------------------------------------------------
247 -- FUNCTION validate_account_no
248 --------------------------------------------------------------------------------
249 FUNCTION validate_non_IBAN_acc_no(p_bank_code VARCHAR2
250 ,p_branch_code VARCHAR2
251 ,p_account_number VARCHAR2
252 ,p_validation_code VARCHAR2) RETURN NUMBER IS
253 --
254 X1 NUMBER;
255 X2 NUMBER;
256 X3 NUMBER;
257 X4 NUMBER;
258 X5 NUMBER;
259 X6 NUMBER;
260 X7 NUMBER;
261 X8 NUMBER;
262 X9 NUMBER;
263 X10 NUMBER;
264 first_check_digit NUMBER;
265 second_check_digit NUMBER;
266 check_digit NUMBER;
267 --
268 chk_code number;
269 BEGIN
270 --
271 -- Bug no 3516026
272 chk_code := instr(p_branch_code,'.',1,1) + instr(p_branch_code,'-',1,1) + instr(p_branch_code,'+',1,1) +
273 instr(p_account_number,'.',1,1) + instr(p_account_number,'-',1,1) + instr(p_account_number,'+',1,1) +
274 instr(p_validation_code,'.',1,1) + instr(p_validation_code,'-',1,1) + instr(p_validation_code,'+',1,1);
275 IF chk_code > 0 THEN
276 return 0;
277 END IF;
278 -- End Bug no 3516026
279
280 X1 := 0;
281 X2 := 0;
282 X3 := substr(p_bank_code,1,1);
283 X4 := substr(p_bank_code,2,1);
284 X5 := substr(p_bank_code,3,1);
285 X6 := substr(p_bank_code,4,1);
286 --
287 X7 := substr(p_branch_code,1,1);
288 X8 := substr(p_branch_code,2,1);
289 X9 := substr(p_branch_code,3,1);
290 X10 := substr(p_branch_code,4,1);
291 --
292 first_check_digit := substr(p_validation_code,1,1);
293 second_check_digit := substr(p_validation_code,2,1);
294 --
295 check_digit := (X1*1) + (X2*2) + (X3*4) + (X4*8)
296 + (X5*5) + (X6*10) + (X7*9) + (X8*7)
297 + (X9*3) + (X10*6);
298 --
299 check_digit := 11 - mod(check_digit,11);
300 --
301 -- for bug 3390728
302 IF check_digit = 10 THEN
303 check_digit := 1;
304 ELSIF check_digit = 11 THEN
305 check_digit := 0;
306 END IF;
307
308 IF check_digit <> first_check_digit THEN
309 RETURN 0;
310 END IF;
311 --
312 X1 := substr(p_account_number,1,1);
313 X2 := substr(p_account_number,2,1);
314 X3 := substr(p_account_number,3,1);
315 X4 := substr(p_account_number,4,1);
316 X5 := substr(p_account_number,5,1);
317 X6 := substr(p_account_number,6,1);
318 X7 := substr(p_account_number,7,1);
319 X8 := substr(p_account_number,8,1);
320 X9 := substr(p_account_number,9,1);
321 X10 := substr(p_account_number,10,1);
322
323 check_digit := (X1*1) + (X2*2) + (X3*4) + (X4*8)
324 + (X5*5) + (X6*10) + (X7*9) + (X8*7)
325 + (X9*3) + (X10*6);
326 --
327 check_digit := 11 - mod(check_digit,11);
328 --
329 -- for bug 3390728
330 IF check_digit = 10 THEN
331 check_digit := 1;
332 ELSIF check_digit = 11 THEN
333 check_digit := 0;
334 END IF;
335
336 IF check_digit <> second_check_digit THEN
337 RETURN 0;
338 END IF;
339 --
340 RETURN 1;
341 --
342 END validate_non_IBAN_acc_no;
343 --
344 PROCEDURE check_identifier_unique
345 ( p_identifier_type VARCHAR2,
346 p_identifier_value VARCHAR2,
347 p_person_id NUMBER,
348 p_business_group_id NUMBER)
349 is
350 --
351 l_status VARCHAR2(1);
352 l_legislation_code VARCHAR2(30);
353 l_nat_lbl VARCHAR2(2000);
354 local_warning EXCEPTION;
355
356 BEGIN
357 --
358 BEGIN
359 SELECT 'Y'
360 INTO l_status
361 FROM sys.dual
362 WHERE exists(SELECT '1'
363 FROM per_people_f pp
364 WHERE (p_person_id IS NULL OR p_person_id <> pp.person_id)
365 AND p_identifier_value = pp.per_information3
366 AND p_identifier_type = pp.per_information2
367 AND pp.business_group_id = p_business_group_id);
368 --
369 hr_utility.set_message(801,'HR_ES_NI_UNIQUE_WARNING');
370 hr_utility.set_message_token('NI_NUMBER',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_VALUE'));
371 RAISE local_warning;
372 --
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN NULL;
375 WHEN local_warning THEN
376 hr_utility.set_warning;
377 END;
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
381 hr_utility.set_message_token('PROCEDURE','CHECK_IDENTIFIER_UNIQUE');
382 hr_utility.set_message_token('STEP','1');
383 hr_utility.raise_error;
384 WHEN local_warning THEN
385 hr_utility.set_warning;
386 END check_identifier_unique;
387
388 --------------------------------------------------------------------------------
389 -- PROCEDURE validate_cif
390 --------------------------------------------------------------------------------
391 PROCEDURE validate_cif(p_org_info VARCHAR2) is
392
393 l_inputs ff_exec.inputs_t;
394 l_outputs ff_exec.outputs_t;
395 l_formula_id ff_formulas_f.formula_id%type;
396 l_formula_mesg varchar2(50);
397 l_effective_start_date ff_formulas_f.effective_start_date%type;
398
399 CURSOR get_formula_id is
400 SELECT formula_id, effective_start_date
401 FROM ff_formulas_f
402 WHERE formula_name = 'ES_CIF_VALIDATION'
403 AND business_group_id is null
404 AND legislation_code = 'ES'
405 AND sysdate BETWEEN effective_start_date AND effective_end_date;
406
407 BEGIN
408 OPEN get_formula_id;
409 FETCH get_formula_id INTO l_formula_id, l_effective_start_date;
410 CLOSE get_formula_id;
411
412 ff_exec.init_formula(l_formula_id, l_effective_start_date, l_inputs, l_outputs);
413 FOR l_in_cnt IN l_inputs.first..l_inputs.LAST LOOP
414 IF l_inputs(l_in_cnt).name = 'CIF_NUMBER' THEN
415 l_inputs(l_in_cnt).value := p_org_info;
416 END IF;
417 END LOOP;
418
419 ff_exec.run_formula(l_inputs,l_outputs);
420
421 FOR l_out_cnt IN l_outputs.first..l_outputs.LAST LOOP
422 IF l_outputs(l_out_cnt).name = 'RETURN_VALUE' THEN
423 l_formula_mesg := l_outputs(l_out_cnt).value;
424 END IF;
425 END LOOP;
426
427 IF l_formula_mesg = 'INVALID_ID' THEN
428 hr_utility.set_message(800,'HR_ES_INVALID_CIF');
429 hr_utility.raise_error;
430 END IF;
431 END validate_cif;
432 --------------------------------------------------------------------------------
433 -- FUNCTION validate_cac_lookup
434 --------------------------------------------------------------------------------
435 FUNCTION validate_cac_lookup (p_province_code VARCHAR2) RETURN NUMBER is
436
437 CURSOR get_province_code(p_province_code VARCHAR2) is
438 select 1 from hr_lookups
439 where lookup_type='ES_PROVINCE_CODES'
440 and lookup_code=p_province_code;
441
442 l_check NUMBER;
443
444 BEGIN
445
446 OPEN get_province_code(p_province_code);
447 FETCH get_province_code into l_check;
448 IF get_province_code%NOTFOUND THEN
449 RETURN 0;
450 END IF;
451 CLOSE get_province_code;
452 RETURN 1;
453 END validate_cac_lookup;
454 --------------------------------------------------------------------------------
455 -- PROCEDURE validate_cac
456 --------------------------------------------------------------------------------
457 PROCEDURE validate_cac(p_org_info VARCHAR2) is
458
459 l_inputs ff_exec.inputs_t;
460 l_outputs ff_exec.outputs_t;
461 l_formula_id ff_formulas_f.formula_id%type;
462 l_formula_mesg varchar2(50);
463 l_effective_start_date ff_formulas_f.effective_start_date%type;
464
465 CURSOR get_formula_id is
466 SELECT formula_id, effective_start_date
467 FROM ff_formulas_f
468 WHERE formula_name = 'ES_CAC_VALIDATION'
469 AND business_group_id is null
470 AND legislation_code = 'ES'
471 AND sysdate BETWEEN effective_start_date AND effective_end_date;
472
473 BEGIN
474 OPEN get_formula_id;
475 FETCH get_formula_id INTO l_formula_id, l_effective_start_date;
476 CLOSE get_formula_id;
477
478 ff_exec.init_formula(l_formula_id, l_effective_start_date, l_inputs, l_outputs);
479 FOR l_in_cnt IN l_inputs.first..l_inputs.LAST LOOP
480 IF l_inputs(l_in_cnt).name = 'CAC_NUMBER' THEN
481 l_inputs(l_in_cnt).value := p_org_info;
482 END IF;
483 END LOOP;
484
485 ff_exec.run_formula(l_inputs,l_outputs);
486
487 FOR l_out_cnt IN l_outputs.first..l_outputs.LAST LOOP
488 IF l_outputs(l_out_cnt).name = 'RETURN_VALUE' THEN
489 l_formula_mesg := l_outputs(l_out_cnt).value;
490 END IF;
491 END LOOP;
492
493 IF l_formula_mesg = 'INVALID_ID' THEN
494 hr_utility.set_message(800,'HR_ES_INVALID_CAC');
495 hr_utility.raise_error;
496 END IF;
497 END validate_cac;
498 --------------------------------------------------------------------------------
499 -- PROCEDURE unique_cac
500 --------------------------------------------------------------------------------
501 PROCEDURE unique_cac(p_org_info_id NUMBER
502 ,p_context VARCHAR2
503 ,p_org_info VARCHAR2
504 ,p_business_group_id NUMBER
505 ,p_effective_date DATE) IS
506 CURSOR get_cac_wc IS
507 SELECT 'x'
508 FROM hr_organization_information hoi,hr_all_organization_units hou
509 WHERE hoi.org_information_context = 'ES_WORK_CENTER_DETAILS'
510 AND hou.organization_id = hoi.organization_id
511 AND org_information1 = p_org_info
512 AND hou.business_group_id = p_business_group_id
513 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
514 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
515 AND exists (select 1 from hr_organization_information hoi1
516 where hoi1.org_information1 = 'ES_WORK_CENTER'
517 and hoi1.org_information_context = 'CLASS'
518 and hoi1.organization_id = hoi.organization_id
519 and hoi1.org_information2 = 'Y');
520
521 CURSOR get_cac_statutory IS
522 SELECT 'x'
523 FROM hr_organization_information hoi,hr_all_organization_units hou
524 WHERE hoi.org_information_context = 'ES_STATUTORY_INFO'
525 AND hou.organization_id = hoi.organization_id
526 AND org_information8 = p_org_info
527 AND hou.business_group_id = p_business_group_id
528 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
529 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
530 AND exists (select 1 from hr_organization_information hoi1
531 where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
532 and hoi1.org_information_context = 'CLASS'
533 and hoi1.organization_id = hoi.organization_id
534 and hoi1.org_information2 = 'Y');
535
536 l_check_cac VARCHAR(1);
537 l_check_cac1 VARCHAR(1);
538 BEGIN
539 --
540 l_check_cac := null;
541 l_check_cac1 := null;
542 --
543 -- Bug 7508536
544 -- Allow sharing of CAC numbers at the following levels -
545 -- 1) Amongst Work Centres
546 -- 2) Between Work Centre and Legal Employer.
547
548 /*
549 IF p_context in('ES_WORK_CENTER_DETAILS','ES_STATUTORY_INFO') THEN
550 OPEN get_cac_wc;
551 FETCH get_cac_wc INTO l_check_cac;
552 CLOSE get_cac_wc;
553
554 OPEN get_cac_statutory;
555 FETCH get_cac_statutory INTO l_check_cac1;
556 CLOSE get_cac_statutory;
557 END IF;
558
559 IF l_check_cac = 'x' or l_check_cac1 = 'x' THEN
560 hr_utility.set_message(800,'HR_ES_CAC_UNIQUE_ERROR');
561 hr_utility.raise_error;
562 END IF;
563 */
564
565 IF p_context in('ES_STATUTORY_INFO') THEN
566 OPEN get_cac_statutory;
567 FETCH get_cac_statutory INTO l_check_cac1;
568 CLOSE get_cac_statutory;
569 END IF;
570
571 IF l_check_cac1 = 'x' THEN
572 hr_utility.set_message(800,'HR_ES_CAC_UNIQUE_ERROR');
573 hr_utility.raise_error;
574 END IF;
575
576 --
577 END unique_cac;
578
579 --------------------------------------------------------------------------------
580 -- PROCEDURE unique_ss
581 --------------------------------------------------------------------------------
582 PROCEDURE unique_ss(p_org_info_id NUMBER
583 ,p_context VARCHAR2
584 ,p_org_info VARCHAR2
585 ,p_business_group_id NUMBER
586 ,p_effective_date DATE) IS
587 CURSOR get_ss_code IS
588 SELECT 'x'
589 FROM hr_organization_information hoi,hr_all_organization_units hou
590 WHERE hoi.org_information_context in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS')
591 AND hou.organization_id = hoi.organization_id
592 AND org_information1 = p_org_info
593 AND hou.business_group_id = p_business_group_id
594 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
595 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
596 AND exists (select 1 from hr_organization_information hoi1
597 where hoi1.org_information1 in( 'ES_SS_OFFICE_INFO','ES_SS_PROVINCE_INFO')
598 and hoi1.org_information_context = 'CLASS'
599 and hoi1.organization_id = hoi.organization_id
600 and hoi1.org_information2 = 'Y');
601
602 l_check_ss VARCHAR(1);
603 BEGIN
604 --
605 l_check_ss := NULL;
606 --
607 IF p_context in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS') THEN
608 OPEN get_ss_code;
609 FETCH get_ss_code INTO l_check_ss;
610 CLOSE get_ss_code;
611 END IF;
612
613 IF l_check_ss = 'x' THEN
614 hr_utility.set_message(800,'HR_ES_SS_UNIQUE_ERROR');
615 hr_utility.raise_error;
616 END IF;
617
618 END unique_ss;
619 --------------------------------------------------------------------------------
620 -- PROCEDURE unique_cif
621 --------------------------------------------------------------------------------
622 PROCEDURE unique_cif(p_org_info_id NUMBER
623 ,p_org_info VARCHAR2
624 ,p_business_group_id NUMBER
625 ,p_effective_date DATE) IS
626 CURSOR get_cif IS
627 SELECT 'x'
628 FROM hr_organization_information hoi,hr_all_organization_units hou
629 WHERE hoi.org_information_context = 'ES_STATUTORY_INFO'
630 AND hou.organization_id = hoi.organization_id
631 AND org_information5 = p_org_info
632 AND hou.business_group_id = p_business_group_id
633 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
634 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
635 AND exists (select 1 from hr_organization_information hoi1
636 where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
637 and hoi1.org_information_context = 'CLASS'
638 and hoi1.organization_id = hoi.organization_id
639 and hoi1.org_information2 = 'Y');
640
641 l_check_cif varchar(1):= NULL;
642 BEGIN
643 OPEN get_cif;
644 FETCH get_cif INTO l_check_cif;
645 CLOSE get_cif;
646 IF l_check_cif = 'x' THEN
647 hr_utility.set_message(800,'HR_ES_CIF_UNIQUE_ERROR');
648 hr_utility.raise_error;
649 END IF;
650 END unique_cif;
651 --------------------------------------------------------------------------------
652 -- PROCEDURE validate_wc_sec_ref
653 --------------------------------------------------------------------------------
654 PROCEDURE validate_wc_sec_ref(p_context VARCHAR2
655 ,p_org_information1 VARCHAR2
656 ,p_business_group_id NUMBER
657 ,p_effective_date DATE) IS
658
659
660 CURSOR csr_chk_wc_sec_ref IS
661 SELECT 'x'
662 FROM hr_organization_information hoi
663 ,hr_all_organization_units hou
664 WHERE hoi.org_information_context = p_context
665 AND hoi.org_information1 = p_org_information1
666 AND hoi.organization_id = hou.organization_id
667 AND hou.business_group_id = p_business_group_id
668 AND p_effective_date <= NVL(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'));
669
670 l_check_ref VARCHAR2(1);
671 BEGIN
672 --
673 l_check_ref := null;
674 --
675 OPEN csr_chk_wc_sec_ref;
676 FETCH csr_chk_wc_sec_ref into l_check_ref;
677 CLOSE csr_chk_wc_sec_ref;
678
679 IF l_check_ref = 'x' THEN
680 IF p_context = 'ES_WORK_CENTER_REF' THEN
681 hr_utility.set_message(800,'HR_ES_INVALID_REFERENCE');
682 hr_utility.set_message_token(800,'VALUE'
683 ,hr_general.decode_lookup('ES_FORM_LABELS','WORK_CENTER'));
684 hr_utility.raise_error;
685 ELSIF p_context = 'ES_SECTION_REF' THEN
686 hr_utility.set_message(800,'HR_ES_INVALID_REFERENCE');
687 hr_utility.set_message_token(800,'VALUE'
688 ,hr_general.decode_lookup('ES_FORM_LABELS','SECTION'));
689 hr_utility.raise_error;
690 END IF;
691 END IF;
692
693 END validate_wc_sec_ref;
694 --------------------------------------------------------------------------------
695 -- PROCEDURE check_leaving_reason
696 --------------------------------------------------------------------------------
697 PROCEDURE check_leaving_reason( p_leaving_reason VARCHAR2
698 ,p_business_group_id NUMBER ) IS
699 --
700 l_status varchar2(1);
701 BEGIN
702 BEGIN
703 SELECT 'Y'
704 INTO l_status
705 FROM sys.dual
706 WHERE exists(SELECT '1'
707 FROM per_shared_types pp
708 WHERE lookup_type = 'LEAV_REAS'
709 AND system_type_cd = p_leaving_reason
710 AND nvl(business_group_id,p_business_group_id)
711 = p_business_group_id
712 AND information1 IS NOT NULL);
713 --
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 hr_utility.set_message(800,'HR_ES_STAT_TERM_REASON_MISSING');
717 hr_utility.set_warning;
718 END;
719 END check_leaving_reason;
720 --------------------------------------------------------------------------------
721 -- FUNCTION check_SSI
722 --------------------------------------------------------------------------------
723 FUNCTION check_SSI(p_identifier_value VARCHAR2) RETURN VARCHAR2 AS
724
725 v_province_code VARCHAR2(2);
726 v_random_number VARCHAR2(8);
727 v_check_digit VARCHAR2(2);
728 n_check NUMBER(1);
729 BEGIN
730 --
731 IF (length(p_identifier_value) <> 12) THEN
732 RETURN 'N';
733 ELSIF (hr_ni_chk_pkg.chk_nat_id_format(p_identifier_value,'DDDDDDDDDDDD') <> p_identifier_value) THEN
734 RETURN 'N';
735 ELSE
736 v_province_code := substr(p_identifier_value,1,2);
737 --v_random_number := substr(p_identifier_value,3,8);
738 --
739 IF substr(p_identifier_value,3,1) <> '0' THEN
740 v_random_number := substr(p_identifier_value,3,8);
741 ELSE
742 v_random_number := substr(p_identifier_value,4,7);
743 END IF;
744 --
745 v_check_digit := substr(p_identifier_value,11,2);
746 n_check := 0;
747 --
748 n_check := hr_es_utility.validate_cac_lookup(v_province_code);
749 IF (n_check = 0) THEN
750 RETURN 'N';
751 ELSE
752 IF (mod(to_number(v_province_code || v_random_number),97) <> to_number(v_check_digit)) THEN
753 RETURN 'N';
754 END IF;
755 END IF;
756 END IF;
757 RETURN 'Y';
758 END check_SSI;
759 --------------------------------------------------------------------------------
760 -- FUNCTION get_disability_degree
761 --------------------------------------------------------------------------------
762 FUNCTION get_disability_degree(p_person_id NUMBER, p_session_date DATE)
763 RETURN NUMBER IS
764 --
765
766 CURSOR c_disability(p_person_id NUMBER, p_session_date DATE) IS
767 SELECT degree
768 FROM per_disabilities_f d
769 WHERE d.person_id = p_person_id
770 AND p_session_date BETWEEN d.effective_start_date
771 AND d.effective_end_date;
772
773 n_disability_degree NUMBER;
774 --
775 BEGIN
776 --
777 OPEN c_disability(p_person_id,p_session_date);
778 FETCH c_disability INTO n_disability_degree;
779 CLOSE c_disability;
780 --
781 RETURN n_disability_degree;
782 --
783 END get_disability_degree;
784 --------------------------------------------------------------------------------
785 -- FUNCTION get_ssno
786 --------------------------------------------------------------------------------
787 FUNCTION get_ssno(p_assignment_id number
788 ,p_element_type_id number
789 ,p_input_value_id number
790 ,p_effective_date date) RETURN VARCHAR2 is
791
792 CURSOR get_screen_entry_value is
793 select peevf.screen_entry_value
794 from pay_element_entries_f peef
795 ,pay_element_entry_values_f peevf
796 where peef.assignment_id=p_assignment_id
797 and peef.element_type_id=p_element_type_id
798 and peevf.input_Value_id=p_input_value_id
799 and peevf.ELEMENT_ENTRY_ID=peef.element_entry_id
800 and p_effective_date between peef.effective_start_date and peef.effective_end_date
801 and p_effective_date between peevf.effective_start_date and peevf.effective_end_date;
802
803 l_entry_value pay_element_entry_values.screen_entry_value%type;
804 BEGIN
805 OPEN get_screen_entry_value;
806 FETCH get_screen_entry_value INTO l_entry_value;
807 IF get_screen_entry_value%FOUND THEN
808 RETURN l_entry_value;
809 ELSE
810 RETURN NULL;
811 END IF;
812 END get_ssno;
813 --------------------------------------------------------------------------------
814 -- FUNCTION chk_entry_in_lookup
815 --------------------------------------------------------------------------------
816 FUNCTION chk_entry_in_lookup
817 (p_lookup_type IN hr_lookups.lookup_type%TYPE
818 ,p_entry_val IN hr_lookups.meaning%TYPE
819 ,p_effective_date IN hr_lookups.start_date_active%TYPE
820 ,p_message OUT NOCOPY VARCHAR2) RETURN VARCHAR2 AS
821 --
822 CURSOR c_entry_in_lookup IS
823 SELECT 'X'
824 FROM hr_lookups hll
825 WHERE hll.lookup_type = p_lookup_type
826 AND hll.lookup_code = p_entry_val
827 AND hll.enabled_flag = 'Y'
828 AND p_effective_date BETWEEN NVL(hll.start_date_active, p_effective_date)
829 AND NVL(hll.end_date_active, p_effective_date);
830
831 l_found_value_in_lookup VARCHAR2(1);
832 -- There is 255 character limit on the error screen
833 l_msg VARCHAR2(255);
834 --
835 BEGIN
836 --
837 l_msg := ' ';
838 -- Check if the value exists in the lookup
839 OPEN c_entry_in_lookup;
840 FETCH c_entry_in_lookup INTO l_found_value_in_lookup;
841 IF c_entry_in_lookup%FOUND THEN
842 l_found_value_in_lookup := 'Y';
843 ELSE
844 l_found_value_in_lookup := 'N';
845 END IF;
846 CLOSE c_entry_in_lookup;
847 --
848 IF p_lookup_type = 'ES_CONTRACT_TYPE' THEN
849 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_TYPE');
850 ELSIF p_lookup_type = 'ES_CONTRACT_SUB_TYPE_UDT' THEN
851 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_SUBTYPE');
852 ELSIF p_lookup_type = 'ES_CONTRACT_STATUS' THEN
853 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_STATUS');
854 ELSIF p_lookup_type = 'ES_CONTRACT_START_REASON_UDT' THEN
855 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_REASON');
856 ELSE
857 l_msg := 'You entered an invalid value. Please enter a valid value.';
858 END IF;
859 --
860 -- Setup Out variables and Return statements
861 p_message := l_msg;
862 RETURN l_found_value_in_lookup;
863 --
864 EXCEPTION
865 WHEN OTHERS THEN
866 IF c_entry_in_lookup%ISOPEN THEN
867 CLOSE c_entry_in_lookup;
868 END IF;
869 END chk_entry_in_lookup;
870 --------------------------------------------------------------------------------
871 -- FUNCTION GET_MESSAGE
872 --------------------------------------------------------------------------------
873 FUNCTION get_message(p_product IN VARCHAR2
874 ,p_message_name IN VARCHAR2
875 ,p_token1 IN VARCHAR2 DEFAULT NULL
876 ,p_token2 IN VARCHAR2 DEFAULT NULL
877 ,p_token3 IN VARCHAR2 DEFAULT NULL)
878 RETURN VARCHAR2
879 IS
880 l_message varchar2(2000);
881 l_token_name varchar2(20);
882 l_token_value varchar2(80);
883 l_colon_position number;
884 --
885 BEGIN
886 --
887 fnd_message.set_name(p_product, p_message_name);
888
889 IF p_token1 IS NOT NULL THEN
890 /* Obtain token 1 name and value */
891 l_colon_position := INSTR(p_token1,':');
892 l_token_name := SUBSTR(p_token1,1,l_colon_position-1);
893 l_token_value := SUBSTR(p_token1,l_colon_position+1,LENGTH(p_token1));
894 fnd_message.set_token(l_token_name, l_token_value);
895 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
896 END IF;
897
898 IF p_token2 is not null then
899 /* Obtain token 2 name and value */
900 l_colon_position := INSTR(p_token2,':');
901 l_token_name := SUBSTR(p_token2,1,l_colon_position-1);
902 l_token_value := SUBSTR(p_token2,l_colon_position+1,LENGTH(p_token2));
903 fnd_message.set_token(l_token_name, l_token_value);
904 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
905 END IF;
906
907 IF p_token3 is not null then
908 /* Obtain token 3 name and value */
909 l_colon_position := INSTR(p_token3,':');
910 l_token_name := SUBSTR(p_token3,1,l_colon_position-1);
911 l_token_value := SUBSTR(p_token3,l_colon_position+1,LENGTH(p_token3));
912 fnd_message.set_token(l_token_name, l_token_value);
913 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
914 END IF;
915
916 l_message := SUBSTRb(fnd_message.get,1,254);
917
918 RETURN l_message;
919 END get_message;
920 --
921 --------------------------------------------------------------------------------
922 -- GET_TABLE_VALUE
923 --------------------------------------------------------------------------------
924 FUNCTION get_table_value(bus_group_id IN NUMBER
925 ,peffective_date IN DATE
926 ,ptab_name IN VARCHAR2
927 ,pcol_name IN VARCHAR2
928 ,prow_value IN VARCHAR2)RETURN NUMBER IS
929 --
930 l_ret pay_user_column_instances_f.value%type;
931 --
932 BEGIN
933 --
934 BEGIN
935 --
936 hr_utility.trace('Inside get_table_value'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
937 l_ret:= hruserdt.get_table_value(bus_group_id
938 ,ptab_name
939 ,pcol_name
940 ,prow_value
941 ,peffective_date);
942 --
943 EXCEPTION
944 WHEN NO_DATA_FOUND THEN
945 l_ret:='0';
946 END;
947 --
948 hr_utility.trace('l_ret '||l_ret);
949 RETURN to_number(l_ret);
950 --
951 END get_table_value;
952 --
953 --------------------------------------------------------------------------------
954 -- GET_TABLE_VALUE_DATE
955 --------------------------------------------------------------------------------
956 FUNCTION get_table_value_date(bus_group_id IN NUMBER
957 ,ptab_name IN VARCHAR2
958 ,pcol_name IN VARCHAR2
959 ,prow_value IN VARCHAR2
960 ,peffective_date IN DATE)RETURN NUMBER IS
961 --
962 l_ret pay_user_column_instances_f.value%type;
963 --
964 BEGIN
965 --
966 BEGIN
967 --
968 hr_utility.trace('Inside get_table_value'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
969 l_ret:= hruserdt.get_table_value(bus_group_id
970 ,ptab_name
971 ,pcol_name
972 ,prow_value
973 ,peffective_date);
974 --
975 EXCEPTION
976 WHEN NO_DATA_FOUND THEN
977 l_ret:='0';
978 END;
979 --
980 hr_utility.trace('l_ret '||l_ret);
981 RETURN to_number(l_ret);
982 --
983 END get_table_value_date;
984 --
985 --------------------------------------------------------------------------------
986 -- GET_TABLE_VALUE_CHAR
987 --------------------------------------------------------------------------------
988 FUNCTION get_table_value_char(bus_group_id IN NUMBER
989 ,peffective_date IN DATE
990 ,ptab_name IN VARCHAR2
991 ,pcol_name IN VARCHAR2
992 ,prow_value IN VARCHAR2)RETURN VARCHAR2 IS
993 --
994 l_ret pay_user_column_instances_f.value%type;
995 --
996 BEGIN
997 --
998 BEGIN
999 --
1000 hr_utility.trace('Inside get_table_value_char'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
1001 l_ret:= hruserdt.get_table_value(bus_group_id
1002 ,ptab_name
1003 ,pcol_name
1004 ,prow_value
1005 ,peffective_date);
1006 --
1007 hr_utility.trace('l_ret '||l_ret);
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 l_ret:='-1';
1011 END;
1012 --
1013 RETURN l_ret;
1014 --
1015 END get_table_value_char;
1016 -------------------------------------------------------------------------------
1017 -- Returns the description for a lookup code of a specified type.
1018 -------------------------------------------------------------------------------
1019 FUNCTION decode_lookup_desc (p_lookup_type VARCHAR2
1020 ,p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
1021 --
1022 CURSOR csr_lookup IS
1023 SELECT description
1024 FROM hr_lookups
1025 WHERE lookup_type = p_lookup_type
1026 AND lookup_code = p_lookup_code;
1027 --
1028 v_desc VARCHAR2(250);
1029 --
1030 BEGIN
1031 --
1032 -- Only open the cursor if the parameters are going to retrieve anything
1033 --
1034 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
1035 --
1036 OPEN csr_lookup;
1037 FETCH csr_lookup INTO v_desc;
1038 CLOSE csr_lookup;
1039 --
1040 END IF;
1041 --
1042 RETURN v_desc;
1043 --
1044 END decode_lookup_desc;
1045 --
1046
1047 --------------------------------------------------------------------------------
1048 -- Function added for IBAN Validation
1049 --------------------------------------------------------------------------------
1050
1051
1052 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
1053
1054 l_iban_ret_val NUMBER;
1055 BEGIN
1056 l_iban_ret_val := IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no);
1057 hr_utility.set_location('IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) ' || l_iban_ret_val,99);
1058 return l_iban_ret_val;
1059 END validate_iban_acc;
1060
1061 END hr_es_utility;