[Home] [Help]
PACKAGE BODY: APPS.HR_ES_UTILITY
Source
1 PACKAGE BODY hr_es_utility as
2 /* $Header: peesutil.pkb 120.2.12010000.2 2008/08/14 10:53:41 parusia 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 -- FUNCTION validate_account_no
187 --------------------------------------------------------------------------------
188 FUNCTION validate_account_no(p_bank_code VARCHAR2
189 ,p_branch_code VARCHAR2
190 ,p_account_number VARCHAR2
191 ,p_validation_code VARCHAR2) RETURN NUMBER IS
192 --
193 X1 NUMBER;
194 X2 NUMBER;
195 X3 NUMBER;
196 X4 NUMBER;
197 X5 NUMBER;
198 X6 NUMBER;
199 X7 NUMBER;
200 X8 NUMBER;
201 X9 NUMBER;
202 X10 NUMBER;
203 first_check_digit NUMBER;
204 second_check_digit NUMBER;
205 check_digit NUMBER;
206 --
207 chk_code number;
208 BEGIN
209 --
210 -- Bug no 3516026
211 chk_code := instr(p_branch_code,'.',1,1) + instr(p_branch_code,'-',1,1) + instr(p_branch_code,'+',1,1) +
212 instr(p_account_number,'.',1,1) + instr(p_account_number,'-',1,1) + instr(p_account_number,'+',1,1) +
213 instr(p_validation_code,'.',1,1) + instr(p_validation_code,'-',1,1) + instr(p_validation_code,'+',1,1);
214 IF chk_code > 0 THEN
215 return 0;
216 END IF;
217 -- End Bug no 3516026
218
219 X1 := 0;
220 X2 := 0;
221 X3 := substr(p_bank_code,1,1);
222 X4 := substr(p_bank_code,2,1);
223 X5 := substr(p_bank_code,3,1);
224 X6 := substr(p_bank_code,4,1);
225 --
226 X7 := substr(p_branch_code,1,1);
227 X8 := substr(p_branch_code,2,1);
228 X9 := substr(p_branch_code,3,1);
229 X10 := substr(p_branch_code,4,1);
230 --
231 first_check_digit := substr(p_validation_code,1,1);
232 second_check_digit := substr(p_validation_code,2,1);
233 --
234 check_digit := (X1*1) + (X2*2) + (X3*4) + (X4*8)
235 + (X5*5) + (X6*10) + (X7*9) + (X8*7)
236 + (X9*3) + (X10*6);
237 --
238 check_digit := 11 - mod(check_digit,11);
239 --
240 -- for bug 3390728
241 IF check_digit = 10 THEN
242 check_digit := 1;
243 ELSIF check_digit = 11 THEN
244 check_digit := 0;
245 END IF;
246
247 IF check_digit <> first_check_digit THEN
248 RETURN 0;
249 END IF;
250 --
251 X1 := substr(p_account_number,1,1);
252 X2 := substr(p_account_number,2,1);
253 X3 := substr(p_account_number,3,1);
254 X4 := substr(p_account_number,4,1);
255 X5 := substr(p_account_number,5,1);
256 X6 := substr(p_account_number,6,1);
257 X7 := substr(p_account_number,7,1);
258 X8 := substr(p_account_number,8,1);
259 X9 := substr(p_account_number,9,1);
260 X10 := substr(p_account_number,10,1);
261
262 check_digit := (X1*1) + (X2*2) + (X3*4) + (X4*8)
263 + (X5*5) + (X6*10) + (X7*9) + (X8*7)
264 + (X9*3) + (X10*6);
265 --
266 check_digit := 11 - mod(check_digit,11);
267 --
268 -- for bug 3390728
269 IF check_digit = 10 THEN
270 check_digit := 1;
271 ELSIF check_digit = 11 THEN
272 check_digit := 0;
273 END IF;
274
275 IF check_digit <> second_check_digit THEN
276 RETURN 0;
277 END IF;
278 --
279 RETURN 1;
280 --
281 END validate_account_no;
282 --
283 PROCEDURE check_identifier_unique
284 ( p_identifier_type VARCHAR2,
285 p_identifier_value VARCHAR2,
286 p_person_id NUMBER,
287 p_business_group_id NUMBER)
288 is
289 --
290 l_status VARCHAR2(1);
291 l_legislation_code VARCHAR2(30);
292 l_nat_lbl VARCHAR2(2000);
293 local_warning EXCEPTION;
294
295 BEGIN
296 --
297 BEGIN
298 SELECT 'Y'
299 INTO l_status
300 FROM sys.dual
301 WHERE exists(SELECT '1'
302 FROM per_people_f pp
303 WHERE (p_person_id IS NULL OR p_person_id <> pp.person_id)
304 AND p_identifier_value = pp.per_information3
305 AND p_identifier_type = pp.per_information2
306 AND pp.business_group_id = p_business_group_id);
307 --
308 hr_utility.set_message(801,'HR_ES_NI_UNIQUE_WARNING');
309 hr_utility.set_message_token('NI_NUMBER',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_VALUE'));
310 RAISE local_warning;
311 --
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN NULL;
314 WHEN local_warning THEN
315 hr_utility.set_warning;
316 END;
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
320 hr_utility.set_message_token('PROCEDURE','CHECK_IDENTIFIER_UNIQUE');
321 hr_utility.set_message_token('STEP','1');
322 hr_utility.raise_error;
323 WHEN local_warning THEN
324 hr_utility.set_warning;
325 END check_identifier_unique;
326
327 --------------------------------------------------------------------------------
328 -- PROCEDURE validate_cif
329 --------------------------------------------------------------------------------
330 PROCEDURE validate_cif(p_org_info VARCHAR2) is
331
332 l_inputs ff_exec.inputs_t;
333 l_outputs ff_exec.outputs_t;
334 l_formula_id ff_formulas_f.formula_id%type;
335 l_formula_mesg varchar2(50);
336 l_effective_start_date ff_formulas_f.effective_start_date%type;
337
338 CURSOR get_formula_id is
339 SELECT formula_id, effective_start_date
340 FROM ff_formulas_f
341 WHERE formula_name = 'ES_CIF_VALIDATION'
342 AND business_group_id is null
343 AND legislation_code = 'ES'
344 AND sysdate BETWEEN effective_start_date AND effective_end_date;
345
346 BEGIN
347 OPEN get_formula_id;
348 FETCH get_formula_id INTO l_formula_id, l_effective_start_date;
349 CLOSE get_formula_id;
350
351 ff_exec.init_formula(l_formula_id, l_effective_start_date, l_inputs, l_outputs);
352 FOR l_in_cnt IN l_inputs.first..l_inputs.LAST LOOP
353 IF l_inputs(l_in_cnt).name = 'CIF_NUMBER' THEN
354 l_inputs(l_in_cnt).value := p_org_info;
355 END IF;
356 END LOOP;
357
358 ff_exec.run_formula(l_inputs,l_outputs);
359
360 FOR l_out_cnt IN l_outputs.first..l_outputs.LAST LOOP
361 IF l_outputs(l_out_cnt).name = 'RETURN_VALUE' THEN
362 l_formula_mesg := l_outputs(l_out_cnt).value;
363 END IF;
364 END LOOP;
365
369 END IF;
366 IF l_formula_mesg = 'INVALID_ID' THEN
367 hr_utility.set_message(800,'HR_ES_INVALID_CIF');
368 hr_utility.raise_error;
370 END validate_cif;
371 --------------------------------------------------------------------------------
372 -- FUNCTION validate_cac_lookup
373 --------------------------------------------------------------------------------
374 FUNCTION validate_cac_lookup (p_province_code VARCHAR2) RETURN NUMBER is
375
376 CURSOR get_province_code(p_province_code VARCHAR2) is
377 select 1 from hr_lookups
378 where lookup_type='ES_PROVINCE_CODES'
379 and lookup_code=p_province_code;
380
381 l_check NUMBER;
382
383 BEGIN
384
385 OPEN get_province_code(p_province_code);
386 FETCH get_province_code into l_check;
387 IF get_province_code%NOTFOUND THEN
388 RETURN 0;
389 END IF;
390 CLOSE get_province_code;
391 RETURN 1;
392 END validate_cac_lookup;
393 --------------------------------------------------------------------------------
394 -- PROCEDURE validate_cac
395 --------------------------------------------------------------------------------
396 PROCEDURE validate_cac(p_org_info VARCHAR2) is
397
398 l_inputs ff_exec.inputs_t;
399 l_outputs ff_exec.outputs_t;
400 l_formula_id ff_formulas_f.formula_id%type;
401 l_formula_mesg varchar2(50);
402 l_effective_start_date ff_formulas_f.effective_start_date%type;
403
404 CURSOR get_formula_id is
405 SELECT formula_id, effective_start_date
406 FROM ff_formulas_f
407 WHERE formula_name = 'ES_CAC_VALIDATION'
408 AND business_group_id is null
409 AND legislation_code = 'ES'
410 AND sysdate BETWEEN effective_start_date AND effective_end_date;
411
412 BEGIN
413 OPEN get_formula_id;
414 FETCH get_formula_id INTO l_formula_id, l_effective_start_date;
415 CLOSE get_formula_id;
416
417 ff_exec.init_formula(l_formula_id, l_effective_start_date, l_inputs, l_outputs);
418 FOR l_in_cnt IN l_inputs.first..l_inputs.LAST LOOP
419 IF l_inputs(l_in_cnt).name = 'CAC_NUMBER' THEN
420 l_inputs(l_in_cnt).value := p_org_info;
421 END IF;
422 END LOOP;
423
424 ff_exec.run_formula(l_inputs,l_outputs);
425
426 FOR l_out_cnt IN l_outputs.first..l_outputs.LAST LOOP
427 IF l_outputs(l_out_cnt).name = 'RETURN_VALUE' THEN
428 l_formula_mesg := l_outputs(l_out_cnt).value;
429 END IF;
430 END LOOP;
431
432 IF l_formula_mesg = 'INVALID_ID' THEN
433 hr_utility.set_message(800,'HR_ES_INVALID_CAC');
434 hr_utility.raise_error;
435 END IF;
436 END validate_cac;
437 --------------------------------------------------------------------------------
438 -- PROCEDURE unique_cac
439 --------------------------------------------------------------------------------
440 PROCEDURE unique_cac(p_org_info_id NUMBER
441 ,p_context VARCHAR2
442 ,p_org_info VARCHAR2
443 ,p_business_group_id NUMBER
444 ,p_effective_date DATE) IS
445 CURSOR get_cac_wc IS
446 SELECT 'x'
447 FROM hr_organization_information hoi,hr_all_organization_units hou
448 WHERE hoi.org_information_context = 'ES_WORK_CENTER_DETAILS'
449 AND hou.organization_id = hoi.organization_id
450 AND org_information1 = p_org_info
451 AND hou.business_group_id = p_business_group_id
452 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
453 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
454 AND exists (select 1 from hr_organization_information hoi1
455 where hoi1.org_information1 = 'ES_WORK_CENTER'
459
456 and hoi1.org_information_context = 'CLASS'
457 and hoi1.organization_id = hoi.organization_id
458 and hoi1.org_information2 = 'Y');
460 CURSOR get_cac_statutory IS
461 SELECT 'x'
462 FROM hr_organization_information hoi,hr_all_organization_units hou
463 WHERE hoi.org_information_context = 'ES_STATUTORY_INFO'
464 AND hou.organization_id = hoi.organization_id
465 AND org_information8 = p_org_info
466 AND hou.business_group_id = p_business_group_id
467 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
468 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
469 AND exists (select 1 from hr_organization_information hoi1
470 where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
471 and hoi1.org_information_context = 'CLASS'
472 and hoi1.organization_id = hoi.organization_id
473 and hoi1.org_information2 = 'Y');
474
475 l_check_cac VARCHAR(1);
476 l_check_cac1 VARCHAR(1);
477 BEGIN
478 --
479 l_check_cac := null;
480 l_check_cac1 := null;
481 --
482 IF p_context in('ES_WORK_CENTER_DETAILS','ES_STATUTORY_INFO') THEN
483 OPEN get_cac_wc;
484 FETCH get_cac_wc INTO l_check_cac;
485 CLOSE get_cac_wc;
486
487 OPEN get_cac_statutory;
488 FETCH get_cac_statutory INTO l_check_cac1;
489 CLOSE get_cac_statutory;
490 END IF;
491
492 IF l_check_cac = 'x' or l_check_cac1 = 'x' THEN
493 hr_utility.set_message(800,'HR_ES_CAC_UNIQUE_ERROR');
494 hr_utility.raise_error;
495 END IF;
496 --
497 END unique_cac;
498
499 --------------------------------------------------------------------------------
500 -- PROCEDURE unique_ss
501 --------------------------------------------------------------------------------
502 PROCEDURE unique_ss(p_org_info_id NUMBER
503 ,p_context VARCHAR2
504 ,p_org_info VARCHAR2
505 ,p_business_group_id NUMBER
506 ,p_effective_date DATE) IS
507 CURSOR get_ss_code IS
508 SELECT 'x'
509 FROM hr_organization_information hoi,hr_all_organization_units hou
510 WHERE hoi.org_information_context in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS')
511 AND hou.organization_id = hoi.organization_id
512 AND org_information1 = p_org_info
513 AND hou.business_group_id = p_business_group_id
514 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
515 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
516 AND exists (select 1 from hr_organization_information hoi1
517 where hoi1.org_information1 in( 'ES_SS_OFFICE_INFO','ES_SS_PROVINCE_INFO')
518 and hoi1.org_information_context = 'CLASS'
519 and hoi1.organization_id = hoi.organization_id
520 and hoi1.org_information2 = 'Y');
521
522 l_check_ss VARCHAR(1);
523 BEGIN
524 --
525 l_check_ss := NULL;
526 --
527 IF p_context in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS') THEN
528 OPEN get_ss_code;
529 FETCH get_ss_code INTO l_check_ss;
530 CLOSE get_ss_code;
531 END IF;
532
533 IF l_check_ss = 'x' THEN
534 hr_utility.set_message(800,'HR_ES_SS_UNIQUE_ERROR');
535 hr_utility.raise_error;
536 END IF;
537
538 END unique_ss;
539 --------------------------------------------------------------------------------
540 -- PROCEDURE unique_cif
541 --------------------------------------------------------------------------------
542 PROCEDURE unique_cif(p_org_info_id NUMBER
543 ,p_org_info VARCHAR2
544 ,p_business_group_id NUMBER
545 ,p_effective_date DATE) IS
546 CURSOR get_cif IS
547 SELECT 'x'
548 FROM hr_organization_information hoi,hr_all_organization_units hou
549 WHERE hoi.org_information_context = 'ES_STATUTORY_INFO'
553 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
550 AND hou.organization_id = hoi.organization_id
551 AND org_information5 = p_org_info
552 AND hou.business_group_id = p_business_group_id
554 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
555 AND exists (select 1 from hr_organization_information hoi1
556 where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
557 and hoi1.org_information_context = 'CLASS'
558 and hoi1.organization_id = hoi.organization_id
559 and hoi1.org_information2 = 'Y');
560
561 l_check_cif varchar(1):= NULL;
562 BEGIN
563 OPEN get_cif;
564 FETCH get_cif INTO l_check_cif;
565 CLOSE get_cif;
566 IF l_check_cif = 'x' THEN
567 hr_utility.set_message(800,'HR_ES_CIF_UNIQUE_ERROR');
568 hr_utility.raise_error;
569 END IF;
570 END unique_cif;
571 --------------------------------------------------------------------------------
572 -- PROCEDURE validate_wc_sec_ref
573 --------------------------------------------------------------------------------
574 PROCEDURE validate_wc_sec_ref(p_context VARCHAR2
575 ,p_org_information1 VARCHAR2
576 ,p_business_group_id NUMBER
577 ,p_effective_date DATE) IS
578
579
580 CURSOR csr_chk_wc_sec_ref IS
581 SELECT 'x'
582 FROM hr_organization_information hoi
583 ,hr_all_organization_units hou
584 WHERE hoi.org_information_context = p_context
585 AND hoi.org_information1 = p_org_information1
586 AND hoi.organization_id = hou.organization_id
587 AND hou.business_group_id = p_business_group_id
588 AND p_effective_date <= NVL(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'));
589
590 l_check_ref VARCHAR2(1);
591 BEGIN
592 --
593 l_check_ref := null;
594 --
595 OPEN csr_chk_wc_sec_ref;
596 FETCH csr_chk_wc_sec_ref into l_check_ref;
597 CLOSE csr_chk_wc_sec_ref;
598
599 IF l_check_ref = 'x' THEN
600 IF p_context = 'ES_WORK_CENTER_REF' THEN
601 hr_utility.set_message(800,'HR_ES_INVALID_REFERENCE');
602 hr_utility.set_message_token(800,'VALUE'
603 ,hr_general.decode_lookup('ES_FORM_LABELS','WORK_CENTER'));
604 hr_utility.raise_error;
605 ELSIF p_context = 'ES_SECTION_REF' THEN
606 hr_utility.set_message(800,'HR_ES_INVALID_REFERENCE');
607 hr_utility.set_message_token(800,'VALUE'
608 ,hr_general.decode_lookup('ES_FORM_LABELS','SECTION'));
609 hr_utility.raise_error;
610 END IF;
611 END IF;
612
613 END validate_wc_sec_ref;
614 --------------------------------------------------------------------------------
615 -- PROCEDURE check_leaving_reason
616 --------------------------------------------------------------------------------
617 PROCEDURE check_leaving_reason( p_leaving_reason VARCHAR2
618 ,p_business_group_id NUMBER ) IS
619 --
620 l_status varchar2(1);
621 BEGIN
622 BEGIN
623 SELECT 'Y'
624 INTO l_status
625 FROM sys.dual
626 WHERE exists(SELECT '1'
627 FROM per_shared_types pp
628 WHERE lookup_type = 'LEAV_REAS'
629 AND system_type_cd = p_leaving_reason
630 AND nvl(business_group_id,p_business_group_id)
631 = p_business_group_id
632 AND information1 IS NOT NULL);
633 --
634 EXCEPTION
635 WHEN NO_DATA_FOUND THEN
636 hr_utility.set_message(800,'HR_ES_STAT_TERM_REASON_MISSING');
637 hr_utility.set_warning;
638 END;
639 END check_leaving_reason;
640 --------------------------------------------------------------------------------
641 -- FUNCTION check_SSI
642 --------------------------------------------------------------------------------
643 FUNCTION check_SSI(p_identifier_value VARCHAR2) RETURN VARCHAR2 AS
644
645 v_province_code VARCHAR2(2);
646 v_random_number VARCHAR2(8);
647 v_check_digit VARCHAR2(2);
648 n_check NUMBER(1);
649 BEGIN
650 --
651 IF (length(p_identifier_value) <> 12) THEN
652 RETURN 'N';
653 ELSIF (hr_ni_chk_pkg.chk_nat_id_format(p_identifier_value,'DDDDDDDDDDDD') <> p_identifier_value) THEN
654 RETURN 'N';
655 ELSE
656 v_province_code := substr(p_identifier_value,1,2);
657 --v_random_number := substr(p_identifier_value,3,8);
658 --
659 IF substr(p_identifier_value,3,1) <> '0' THEN
660 v_random_number := substr(p_identifier_value,3,8);
661 ELSE
662 v_random_number := substr(p_identifier_value,4,7);
663 END IF;
664 --
665 v_check_digit := substr(p_identifier_value,11,2);
666 n_check := 0;
667 --
668 n_check := hr_es_utility.validate_cac_lookup(v_province_code);
669 IF (n_check = 0) THEN
670 RETURN 'N';
671 ELSE
672 IF (mod(to_number(v_province_code || v_random_number),97) <> to_number(v_check_digit)) THEN
673 RETURN 'N';
674 END IF;
675 END IF;
679 --------------------------------------------------------------------------------
676 END IF;
677 RETURN 'Y';
678 END check_SSI;
680 -- FUNCTION get_disability_degree
681 --------------------------------------------------------------------------------
682 FUNCTION get_disability_degree(p_person_id NUMBER, p_session_date DATE)
683 RETURN NUMBER IS
684 --
685
686 CURSOR c_disability(p_person_id NUMBER, p_session_date DATE) IS
687 SELECT degree
688 FROM per_disabilities_f d
689 WHERE d.person_id = p_person_id
690 AND p_session_date BETWEEN d.effective_start_date
691 AND d.effective_end_date;
692
693 n_disability_degree NUMBER;
694 --
695 BEGIN
696 --
697 OPEN c_disability(p_person_id,p_session_date);
698 FETCH c_disability INTO n_disability_degree;
699 CLOSE c_disability;
700 --
701 RETURN n_disability_degree;
702 --
703 END get_disability_degree;
704 --------------------------------------------------------------------------------
705 -- FUNCTION get_ssno
706 --------------------------------------------------------------------------------
707 FUNCTION get_ssno(p_assignment_id number
708 ,p_element_type_id number
709 ,p_input_value_id number
710 ,p_effective_date date) RETURN VARCHAR2 is
711
712 CURSOR get_screen_entry_value is
713 select peevf.screen_entry_value
714 from pay_element_entries_f peef
715 ,pay_element_entry_values_f peevf
716 where peef.assignment_id=p_assignment_id
717 and peef.element_type_id=p_element_type_id
718 and peevf.input_Value_id=p_input_value_id
719 and peevf.ELEMENT_ENTRY_ID=peef.element_entry_id
720 and p_effective_date between peef.effective_start_date and peef.effective_end_date
721 and p_effective_date between peevf.effective_start_date and peevf.effective_end_date;
722
723 l_entry_value pay_element_entry_values.screen_entry_value%type;
724 BEGIN
725 OPEN get_screen_entry_value;
726 FETCH get_screen_entry_value INTO l_entry_value;
727 IF get_screen_entry_value%FOUND THEN
728 RETURN l_entry_value;
729 ELSE
730 RETURN NULL;
731 END IF;
732 END get_ssno;
733 --------------------------------------------------------------------------------
734 -- FUNCTION chk_entry_in_lookup
735 --------------------------------------------------------------------------------
736 FUNCTION chk_entry_in_lookup
737 (p_lookup_type IN hr_lookups.lookup_type%TYPE
738 ,p_entry_val IN hr_lookups.meaning%TYPE
739 ,p_effective_date IN hr_lookups.start_date_active%TYPE
740 ,p_message OUT NOCOPY VARCHAR2) RETURN VARCHAR2 AS
741 --
742 CURSOR c_entry_in_lookup IS
743 SELECT 'X'
744 FROM hr_lookups hll
745 WHERE hll.lookup_type = p_lookup_type
746 AND hll.lookup_code = p_entry_val
747 AND hll.enabled_flag = 'Y'
748 AND p_effective_date BETWEEN NVL(hll.start_date_active, p_effective_date)
749 AND NVL(hll.end_date_active, p_effective_date);
750
751 l_found_value_in_lookup VARCHAR2(1);
752 -- There is 255 character limit on the error screen
753 l_msg VARCHAR2(255);
754 --
755 BEGIN
756 --
757 l_msg := ' ';
758 -- Check if the value exists in the lookup
759 OPEN c_entry_in_lookup;
760 FETCH c_entry_in_lookup INTO l_found_value_in_lookup;
761 IF c_entry_in_lookup%FOUND THEN
762 l_found_value_in_lookup := 'Y';
763 ELSE
764 l_found_value_in_lookup := 'N';
765 END IF;
766 CLOSE c_entry_in_lookup;
767 --
768 IF p_lookup_type = 'ES_CONTRACT_TYPE' THEN
769 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_TYPE');
770 ELSIF p_lookup_type = 'ES_CONTRACT_SUB_TYPE_UDT' THEN
771 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_SUBTYPE');
772 ELSIF p_lookup_type = 'ES_CONTRACT_STATUS' THEN
773 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_STATUS');
774 ELSIF p_lookup_type = 'ES_CONTRACT_START_REASON_UDT' THEN
775 l_msg := fnd_message.get_string('PER','HR_ES_INVALID_CONTRACT_REASON');
776 ELSE
777 l_msg := 'You entered an invalid value. Please enter a valid value.';
778 END IF;
779 --
780 -- Setup Out variables and Return statements
781 p_message := l_msg;
782 RETURN l_found_value_in_lookup;
783 --
784 EXCEPTION
785 WHEN OTHERS THEN
786 IF c_entry_in_lookup%ISOPEN THEN
787 CLOSE c_entry_in_lookup;
788 END IF;
789 END chk_entry_in_lookup;
790 --------------------------------------------------------------------------------
791 -- FUNCTION GET_MESSAGE
792 --------------------------------------------------------------------------------
793 FUNCTION get_message(p_product IN VARCHAR2
794 ,p_message_name IN VARCHAR2
795 ,p_token1 IN VARCHAR2 DEFAULT NULL
796 ,p_token2 IN VARCHAR2 DEFAULT NULL
797 ,p_token3 IN VARCHAR2 DEFAULT NULL)
798 RETURN VARCHAR2
799 IS
800 l_message varchar2(2000);
801 l_token_name varchar2(20);
805 BEGIN
802 l_token_value varchar2(80);
803 l_colon_position number;
804 --
806 --
807 fnd_message.set_name(p_product, p_message_name);
808
809 IF p_token1 IS NOT NULL THEN
810 /* Obtain token 1 name and value */
811 l_colon_position := INSTR(p_token1,':');
812 l_token_name := SUBSTR(p_token1,1,l_colon_position-1);
813 l_token_value := SUBSTR(p_token1,l_colon_position+1,LENGTH(p_token1));
814 fnd_message.set_token(l_token_name, l_token_value);
815 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
816 END IF;
817
818 IF p_token2 is not null then
819 /* Obtain token 2 name and value */
820 l_colon_position := INSTR(p_token2,':');
821 l_token_name := SUBSTR(p_token2,1,l_colon_position-1);
822 l_token_value := SUBSTR(p_token2,l_colon_position+1,LENGTH(p_token2));
823 fnd_message.set_token(l_token_name, l_token_value);
824 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
825 END IF;
826
827 IF p_token3 is not null then
828 /* Obtain token 3 name and value */
829 l_colon_position := INSTR(p_token3,':');
830 l_token_name := SUBSTR(p_token3,1,l_colon_position-1);
831 l_token_value := SUBSTR(p_token3,l_colon_position+1,LENGTH(p_token3));
832 fnd_message.set_token(l_token_name, l_token_value);
833 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
834 END IF;
835
836 l_message := SUBSTRb(fnd_message.get,1,254);
837
838 RETURN l_message;
839 END get_message;
840 --
841 --------------------------------------------------------------------------------
842 -- GET_TABLE_VALUE
843 --------------------------------------------------------------------------------
844 FUNCTION get_table_value(bus_group_id IN NUMBER
845 ,peffective_date IN DATE
846 ,ptab_name IN VARCHAR2
847 ,pcol_name IN VARCHAR2
848 ,prow_value IN VARCHAR2)RETURN NUMBER IS
849 --
850 l_ret pay_user_column_instances_f.value%type;
851 --
852 BEGIN
853 --
854 BEGIN
855 --
856 hr_utility.trace('Inside get_table_value'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
857 l_ret:= hruserdt.get_table_value(bus_group_id
858 ,ptab_name
859 ,pcol_name
860 ,prow_value
861 ,peffective_date);
862 --
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 l_ret:='0';
866 END;
867 --
868 hr_utility.trace('l_ret '||l_ret);
869 RETURN to_number(l_ret);
870 --
871 END get_table_value;
872 --
873 --------------------------------------------------------------------------------
874 -- GET_TABLE_VALUE_DATE
875 --------------------------------------------------------------------------------
876 FUNCTION get_table_value_date(bus_group_id IN NUMBER
877 ,ptab_name IN VARCHAR2
878 ,pcol_name IN VARCHAR2
879 ,prow_value IN VARCHAR2
880 ,peffective_date IN DATE)RETURN NUMBER IS
881 --
882 l_ret pay_user_column_instances_f.value%type;
883 --
884 BEGIN
885 --
886 BEGIN
887 --
888 hr_utility.trace('Inside get_table_value'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
889 l_ret:= hruserdt.get_table_value(bus_group_id
890 ,ptab_name
891 ,pcol_name
892 ,prow_value
893 ,peffective_date);
894 --
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 l_ret:='0';
898 END;
899 --
900 hr_utility.trace('l_ret '||l_ret);
901 RETURN to_number(l_ret);
902 --
903 END get_table_value_date;
904 --
905 --------------------------------------------------------------------------------
906 -- GET_TABLE_VALUE_CHAR
907 --------------------------------------------------------------------------------
908 FUNCTION get_table_value_char(bus_group_id IN NUMBER
909 ,peffective_date IN DATE
910 ,ptab_name IN VARCHAR2
911 ,pcol_name IN VARCHAR2
912 ,prow_value IN VARCHAR2)RETURN VARCHAR2 IS
913 --
914 l_ret pay_user_column_instances_f.value%type;
915 --
916 BEGIN
917 --
918 BEGIN
919 --
920 hr_utility.trace('Inside get_table_value_char'||bus_group_id||' '||ptab_name||' '||pcol_name||' '||prow_value||' '||peffective_date);
921 l_ret:= hruserdt.get_table_value(bus_group_id
922 ,ptab_name
923 ,pcol_name
924 ,prow_value
925 ,peffective_date);
926 --
927 hr_utility.trace('l_ret '||l_ret);
928 EXCEPTION
929 WHEN NO_DATA_FOUND THEN
930 l_ret:='-1';
931 END;
932 --
933 RETURN l_ret;
934 --
935 END get_table_value_char;
936 -------------------------------------------------------------------------------
937 -- Returns the description for a lookup code of a specified type.
938 -------------------------------------------------------------------------------
939 FUNCTION decode_lookup_desc (p_lookup_type VARCHAR2
940 ,p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
941 --
942 CURSOR csr_lookup IS
943 SELECT description
944 FROM hr_lookups
945 WHERE lookup_type = p_lookup_type
946 AND lookup_code = p_lookup_code;
947 --
948 v_desc VARCHAR2(250);
949 --
950 BEGIN
951 --
952 -- Only open the cursor if the parameters are going to retrieve anything
953 --
954 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
955 --
956 OPEN csr_lookup;
957 FETCH csr_lookup INTO v_desc;
958 CLOSE csr_lookup;
959 --
960 END IF;
961 --
962 RETURN v_desc;
963 --
964 END decode_lookup_desc;
965 --
966 END hr_es_utility;