[Home] [Help]
PACKAGE BODY: APPS.HR_RU_UTILITY
Source
1 PACKAGE BODY hr_ru_utility AS
2 /* $Header: peruutil.pkb 120.2.12010000.2 2009/07/07 11:31:31 parusia ship $ */
3 ---
4 ----------------------------------------------------------------------
5 -- CHECK_LOOKUP_VALUE : Check if a valid lookup value has been passed
6 ----------------------------------------------------------------------
7 PROCEDURE check_lookup_value (
8 p_argument IN VARCHAR2,
9 p_argument_value IN VARCHAR2,
10 p_lookup_type IN VARCHAR2,
11 p_effective_date IN DATE
12 )
13 IS
14 --
15 BEGIN
16 --
17 IF (hr_api.not_exists_in_hr_lookups (p_effective_date,
18 p_lookup_type,
19 p_argument_value
20 )
21 )
22 THEN
23 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
24 hr_utility.set_message_token ('ARGUMENT', p_argument);
25 hr_utility.raise_error;
26 END IF;
27 --
28 END check_lookup_value;
29
30
31 -----------------------------------------------------------------------------------------------
32 -- PER_RU_FULL_NAME : Full Name in the format Last Name First Name Middle Name
33 -----------------------------------------------------------------------------------------------
34
35 FUNCTION per_ru_full_name (
36 p_first_name IN VARCHAR2
37 ,p_middle_names IN VARCHAR2
38 ,p_last_name IN VARCHAR2
39 ,p_known_as IN VARCHAR2
40 ,p_title IN VARCHAR2
41 ,p_suffix IN VARCHAR2
42 ,p_pre_name_adjunct IN VARCHAR2
43 ,p_per_information1 IN VARCHAR2
44 ,p_per_information2 IN VARCHAR2
45 ,p_per_information3 IN VARCHAR2
46 ,p_per_information4 IN VARCHAR2
47 ,p_per_information5 IN VARCHAR2
48 ,p_per_information6 IN VARCHAR2
49 ,p_per_information7 IN VARCHAR2
50 ,p_per_information8 IN VARCHAR2
51 ,p_per_information9 IN VARCHAR2
52 ,p_per_information10 IN VARCHAR2
53 ,p_per_information11 IN VARCHAR2
54 ,p_per_information12 IN VARCHAR2
55 ,p_per_information13 IN VARCHAR2
56 ,p_per_information14 IN VARCHAR2
57 ,p_per_information15 IN VARCHAR2
58 ,p_per_information16 IN VARCHAR2
59 ,p_per_information17 IN VARCHAR2
60 ,p_per_information18 IN VARCHAR2
61 ,p_per_information19 IN VARCHAR2
62 ,p_per_information20 IN VARCHAR2
63 ,p_per_information21 IN VARCHAR2
64 ,p_per_information22 IN VARCHAR2
65 ,p_per_information23 IN VARCHAR2
66 ,p_per_information24 IN VARCHAR2
67 ,p_per_information25 IN VARCHAR2
68 ,p_per_information26 IN VARCHAR2
69 ,p_per_information27 IN VARCHAR2
70 ,p_per_information28 IN VARCHAR2
71 ,p_per_information29 IN VARCHAR2
72 ,p_per_information30 IN VARCHAR2
73 )
74 RETURN VARCHAR2
75 IS
76 --
77 l_full_name VARCHAR2 (240);
78 --
79 BEGIN
80 --
81 SELECT substr (LTRIM (RTRIM ( decode (p_last_name, NULL, '', ' ' || p_last_name)
82 || decode (p_first_name, NULL, '', ' ' || p_first_name)
83 || decode (p_middle_names, NULL, '', ' ' || p_middle_names)
84 )
85 )
86 ,1
87 ,240
88 )
89 INTO l_full_name
90 FROM dual;
91
92 RETURN l_full_name;
93 --
94 END per_ru_full_name;
95 --
96 -----------------------------------------------------------------------------------------------
97 -- PER_RU_FULL_NAME : Full Name with initials
98 -----------------------------------------------------------------------------------------------
99 FUNCTION per_ru_full_name_initials (
100 p_first_name IN VARCHAR2,
101 p_middle_names IN VARCHAR2,
102 p_last_name IN VARCHAR2,
103 p_genitive_last_name IN VARCHAR2 DEFAULT NULL,
104 p_known_as IN VARCHAR2 DEFAULT NULL,
105 p_title IN VARCHAR2 DEFAULT NULL,
106 p_suffix IN VARCHAR2 DEFAULT NULL,
107 use_genitive IN BOOLEAN DEFAULT TRUE
108 )
109 RETURN VARCHAR2
110 IS
111 --
112 l_full_name VARCHAR2 (240);
113 l_last_name per_all_people_f.last_name%TYPE;
114 --
115 BEGIN
116 --
117 IF use_genitive
118 THEN
119 l_last_name := NVL (p_genitive_last_name, p_last_name);
120 ELSE
121 l_last_name := p_last_name;
122 END IF;
123
124 SELECT SUBSTR
125 (LTRIM (RTRIM ( DECODE (l_last_name,
126 NULL, '',
127 ' ' || l_last_name
128 )
129 || DECODE
130 (p_first_name,
131 NULL, '',
132 ' '
133 || SUBSTR
134 (RTRIM (LTRIM (p_first_name)),
135 1,
136 1
137 )
138 || '.'
139 )
140 || DECODE
141 (p_middle_names,
142 NULL, '',
143 ' '
144 || SUBSTR
145 (RTRIM (LTRIM (p_middle_names)),
146 1,
147 1
148 )
149 || '.'
150 )
151 )
152 ),
153 1,
154 240
155 )
156 INTO l_full_name
157 FROM DUAL;
158
159 RETURN l_full_name;
160 --
161 END per_ru_full_name_initials;
162
163 ------------------------------------------------------------------------------------------------
164 -- CHECK_SPIF_NUMBER_UNIQUE : Uniqueness check for pension fund number across RU Business groups
165 ------------------------------------------------------------------------------------------------
166
167 ---
168 PROCEDURE check_spif_number_unique (
169 p_spifn VARCHAR2
170 ,p_person_id NUMBER
171 ,p_business_group_id NUMBER
172 )
173 IS
174 --
175 l_status VARCHAR2 (1);
176 l_legislation_code VARCHAR2 (30);
177 l_nat_lbl VARCHAR2 (2000);
178 BEGIN
179 --
180 BEGIN
181 SELECT 'Y'
182 INTO l_status
183 FROM SYS.dual
184 WHERE EXISTS (
185 SELECT '1'
186 FROM per_all_people_f pp
187 WHERE (p_person_id IS NULL OR p_person_id <> pp.person_id)
188 AND p_spifn = pp.per_information13
189 AND pp.business_group_id IN (
190 --Select all business groups having leg. code RU
191 SELECT o1.organization_id
192 FROM hr_organization_information o1, hr_organization_information o2
193 WHERE o1.org_information9 = 'RU'
194 AND o1.organization_id = o2.organization_id
195 AND o1.org_information_context = 'Business Group Information'
196 AND o2.org_information_context = 'CLASS'
197 AND o2.org_information1 = 'HR_BG'
198 AND o2.org_information2 = 'Y'));
199
200 --
201 IF l_status = 'Y'
202 THEN
203 hr_utility.set_message (801, 'HR_NI_UNIQUE_WARNING');
204 hr_utility.set_message_token ('NI_NUMBER'
205 ,hr_general.decode_lookup ('RU_FORM_LABELS', 'SPIFN')
206 );
207 hr_utility.set_warning;
208 END IF;
209 --
210 EXCEPTION
211 WHEN no_data_found
212 THEN
213 NULL;
214 END;
215 END check_spif_number_unique;
216
217 -----------------------------------------------------------------------------------------------
218 -- VALIDATE_SPIFN :Function to validate the Statutory Pension Insurance Fund Number
219 -- 1 if validation fails
220 -- 2 if Formula not present/compiled
221 -----------------------------------------------------------------------------------------------
222 FUNCTION validate_spifn (spif_number VARCHAR2, p_session_date DATE)
223 RETURN NUMBER
224 IS
225 l_formula_id NUMBER;
226 local_warning EXCEPTION;
227
228 CURSOR csr_formula
229 IS
230 SELECT formula_id
231 FROM ff_compiled_info_f
232 WHERE formula_id = l_formula_id
233 AND p_session_date BETWEEN effective_start_date AND effective_end_date;
234
235 l_inputs ff_exec.inputs_t;
236 l_outputs ff_exec.outputs_t;
237 l_effective_date DATE;
238 l_err NUMBER;
239 BEGIN
240 SELECT formula_id, effective_start_date
241 INTO l_formula_id, l_effective_date
242 FROM ff_formulas_f fo, ff_formula_types ft
243 WHERE ft.formula_type_name = 'Oracle Payroll'
244 AND fo.formula_type_id = ft.formula_type_id
245 AND fo.formula_name = 'RU_SPIFN_VALIDATION'
246 AND p_session_date BETWEEN fo.effective_start_date AND fo.effective_end_date;
247
248 OPEN csr_formula;
249
250 FETCH csr_formula
251 INTO l_formula_id;
252
253 IF csr_formula%FOUND
254 THEN
255 l_err := 0;
256 ff_exec.init_formula (l_formula_id, l_effective_date, l_inputs, l_outputs);
257
258 FOR l_in_cnt IN l_inputs.FIRST .. l_inputs.LAST
259 LOOP
260 IF (l_inputs (l_in_cnt).NAME = 'SPIF_NUMBER')
261 THEN
262 l_inputs (l_in_cnt).VALUE := spif_number;
263 END IF;
264 END LOOP;
265
266 ff_exec.run_formula (l_inputs, l_outputs);
267
268 FOR l_out_cnt IN l_outputs.FIRST .. l_outputs.LAST
269 LOOP
270 IF (l_outputs (l_out_cnt).NAME = 'RETURN_VALUE')
271 AND (l_outputs (l_out_cnt).VALUE = 'INVALID_ID')
272 THEN
273 /* hr_utility.set_message (800, 'HR_RU_INVALID_SPIF_NUMBER');
274 hr_utility.set_message_token
275 ('NUMBER',
276 hr_general.decode_lookup ('RU_FORM_LABELS',
277 'SPIFN'
278 )
279 );
280 hr_utility.raise_error;*/
281 l_err := 1;
282 END IF;
283 END LOOP;
284 ELSE
285 /*hr_utility.set_message (800, 'HR_RU_SPIFN_FORMULA_ERROR');
286 hr_utility.raise_error;*/
287 l_err := 2;
288 END IF;
289
290 CLOSE csr_formula;
291
292 RETURN l_err;
293 EXCEPTION
294 WHEN no_data_found
295 THEN
296 /*hr_utility.set_message (800, 'HR_RU_SPIFN_FORMULA_ERROR');
297 hr_utility.raise_error;*/
298 l_err := 2;
299 RETURN l_err;
300 END validate_spifn;
301
302 ---
303 -----------------------------------------------------------------------------------------------
304 -- VALIDATE_MILITARY_REG_BOARD_CODE :Function to validate the Statutory Pension Insurance Fund Number
305 -----------------------------------------------------------------------------------------------
306 PROCEDURE validate_mil_reg_board_code (p_military_reg_board_code VARCHAR2)
307 IS
308 l_tmp VARCHAR2(7);
309 l_len NUMBER;
310 BEGIN
311 l_len := LENGTH (p_military_reg_board_code);
312 IF (l_len <> 7) AND (l_len <> 6)
313 THEN
314 hr_utility.set_message (800, 'HR_RU_INVALID_MIL_REG_CODE');
315 hr_utility.set_warning;
316 ELSE
317 SELECT translate (p_military_reg_board_code, '0123456789', ' ')
318 INTO l_tmp
319 FROM dual;
320 IF (l_len =7 AND (l_tmp IS NULL OR hr_ni_chk_pkg.chk_nat_id_format(l_tmp,'A') = '0' )) OR (l_len=6 AND l_tmp IS NOT NULL)
321 THEN
322 hr_utility.set_message (800, 'HR_RU_INVALID_MIL_REG_CODE');
323 hr_utility.set_warning;
324 END IF;
325 END IF;
326 END validate_mil_reg_board_code;
327
328 --------------------------------------------------------------------------------------------
329 -- VALIDATE_TAX_NO : Function to Validate Individual Tax Number(INN)
330 --------------------------------------------------------------------------------------------
331 FUNCTION validate_tax_no (p_org_info VARCHAR2)
332 RETURN NUMBER
333 IS
334 l_tax_no VARCHAR2 (10);
335 d1 NUMBER;
336 d2 NUMBER;
337 d3 NUMBER;
338 d4 NUMBER;
339 d5 NUMBER;
340 d6 NUMBER;
341 d7 NUMBER;
342 d8 NUMBER;
343 d9 NUMBER;
344 d10 NUMBER;
345 l_cc NUMBER;
346 l_temp NUMBER;
347 l_chk_code NUMBER;
348 BEGIN
349 l_chk_code :=
350 instr (p_org_info, '.', 1, 1) + instr (p_org_info, '-', 1, 1)
351 + instr (p_org_info, '+', 1, 1);
352 IF l_chk_code > 0
353 THEN
354 RETURN 0;
355 END IF;
356 IF LENGTH (p_org_info) <> 10
357 THEN
358 RETURN 0;
359 END IF;
360 IF p_org_info = '0000000000'
361 THEN
362 RETURN 0;
363 END IF;
364
365 d1 := substr (p_org_info, 1, 1);
366 d2 := substr (p_org_info, 2, 1);
367 d3 := substr (p_org_info, 3, 1);
368 d4 := substr (p_org_info, 4, 1);
369 d5 := substr (p_org_info, 5, 1);
370 d6 := substr (p_org_info, 6, 1);
371 d7 := substr (p_org_info, 7, 1);
372 d8 := substr (p_org_info, 8, 1);
373 d9 := substr (p_org_info, 9, 1);
374 d10 := substr (p_org_info, 10, 1);
375
376 l_temp :=
377 (2 * d1)
378 + (4 * d2)
379 + (10 * d3)
380 + (3 * d4)
381 + (5 * d5)
382 + (9 * d6)
383 + (4 * d7)
384 + (6 * d8)
385 + (8 * d9);
386 l_temp := MOD (l_temp, 11);
387 l_cc := MOD (l_temp, 10);
388 IF (d10 <> l_cc)
389 THEN
390 RETURN 0;
391 ELSE
392 RETURN 1;
393 END IF;
394 END validate_tax_no;
395
396 --
397 --------------------------------------------------------------------------------------------
398 -- VALIDATE_OGRN : Function to validate Main Statutory Registration Number(OGRN)
399 --------------------------------------------------------------------------------------------
400 FUNCTION validate_ogrn (p_org_info VARCHAR2)
401 RETURN NUMBER
402 IS
403 l_ogrn_no NUMBER;
404 l_cc NUMBER;
405 l_temp NUMBER;
406 l_reg_date DATE;
407 l_chk_code NUMBER;
408 BEGIN
409 IF LENGTH (p_org_info) <> 13
410 THEN
411 RETURN 0;
412 END IF;
413 IF p_org_info = '0000000000000'
414 THEN
415 RETURN 0;
416 END IF;
417 l_chk_code :=
418 instr (p_org_info, '.', 1, 1) + instr (p_org_info, '-', 1, 1)
419 + instr (p_org_info, '+', 1, 1);
420 IF l_chk_code > 0
421 THEN
422 RETURN 0;
423 END IF;
424 IF substr (p_org_info, 1, 1) <> '1' AND substr (p_org_info, 1, 1) <> '2'
425 THEN
426 RETURN 0;
427 END IF;
428 /*l_ogrn_no := substr (p_org_info, 1, 3) || substr (p_org_info, 6, 8);
429 l_cc := MOD (l_ogrn_no, 10);
430 l_ogrn_no := substr(l_ogrn_no,1,10);
431 l_temp := MOD (l_ogrn_no, 11);
432 IF (l_cc <> l_temp)
433 THEN
434 RETURN 0;
435 END IF;*/
436 -- For bug 5191590
437 l_ogrn_no := substr (p_org_info, 1,12);
438 l_temp := substr(p_org_info,13,1);
439 l_cc := MOD(l_ogrn_no,11);
440 IF (l_cc = 10) THEN
441 l_cc := 0;
442 END IF;
443 IF l_cc <> l_temp then
444 RETURN 0;
445 END IF;
446 -- End bug 5191590
447 RETURN 1;
448
449 END validate_ogrn;
450
451 --
452 ---------------------------------------------------------------------------------------------------
453 -- CHECK_TAX_NUMBER_UNIQUE :Function to check for the Uniqueness of the Individual Tax Number(INN)
454 ---------------------------------------------------------------------------------------------------
455 FUNCTION check_tax_number_unique (p_tax_no VARCHAR2, p_org_id NUMBER, p_org_info_code VARCHAR2)
456 RETURN NUMBER
457 IS
458 --
459 l_status VARCHAR2 (1);
460
461 CURSOR c_unique_status
462 IS
463 SELECT 'Y'
464 FROM SYS.dual
465 WHERE EXISTS (
466 SELECT '1'
467 FROM hr_organization_units hou, hr_organization_information hoi
468 WHERE (p_org_id <> hoi.organization_id)
469 AND hou.organization_id = hoi.organization_id
470 AND hou.business_group_id IN (
471 SELECT o3.organization_id
472 FROM hr_organization_information o3, hr_organization_information o4
473 WHERE o3.org_information9 = 'RU'
474 AND o3.organization_id = o4.organization_id
475 AND o3.org_information_context = 'Business Group Information'
476 AND o4.org_information_context = 'CLASS'
477 AND o4.org_information1 = 'HR_BG'
478 AND o4.org_information2 = 'Y')
479 AND p_tax_no = hoi.org_information2
480 AND p_org_info_code = hoi.org_information_context);
481 BEGIN
482 --
483 BEGIN
484 OPEN c_unique_status;
485
486 FETCH c_unique_status
487 INTO l_status;
488
489 CLOSE c_unique_status;
490
491 --
492 IF l_status = 'Y'
493 THEN
494 hr_utility.set_message (800, 'HR_RU_INVALID_TAX_NO');
495 RETURN 0;
496 END IF;
497 RETURN 1;
498 END;
499 END check_tax_number_unique;
500
501 --
502 -----------------------------------------------------------------------------------------------
503 -- VALIDATE_KPP :Function to validate the Code of reason for Tax Control's Registration(KPP)
504 -----------------------------------------------------------------------------------------------
505 FUNCTION validate_kpp (p_kpp VARCHAR2)
506 RETURN NUMBER
507 IS
508 chk_code NUMBER;
509 BEGIN
510 IF LENGTH (p_kpp) <> 9
511 THEN
512 RETURN 0;
513 END IF;
514 IF hr_ni_chk_pkg.chk_nat_id_format (p_kpp, 'DDDDDDDDD') = '0'
515 THEN
516 RETURN 0;
517 END IF;
518 IF p_kpp = '000000000'
519 THEN
520 RETURN 0;
521 END IF;
522 chk_code := instr (p_kpp, '.', 1, 1) + instr (p_kpp, '-', 1, 1) + instr (p_kpp, '+', 1, 1);
523 IF chk_code > 0
524 THEN
525 RETURN 0;
526 END IF;
527 RETURN 1;
528 END validate_kpp;
529
530 --
531 -----------------------------------------------------------------------------------------------
532 -- VALIDATE_SI :Function to validate the Registration Number in the Social Insurance Fund
533 -----------------------------------------------------------------------------------------------
534 FUNCTION validate_si (p_si VARCHAR2)
535 RETURN NUMBER
536 IS
537 chk_code NUMBER;
538 BEGIN
539 IF p_si = '0000000000'
540 THEN
541 RETURN 0;
542 END IF;
543 chk_code := instr (p_si, '.', 1, 1) + instr (p_si, '-', 1, 1) + instr (p_si, '+', 1, 1);
544 IF chk_code > 0
545 THEN
546 RETURN 0;
547 END IF;
548 IF hr_ni_chk_pkg.chk_nat_id_format (p_si, 'DDDDDDDDDD') = '0'
549 THEN
550 RETURN 0;
551 END IF;
552 RETURN 1;
553 END validate_si;
554
555 --
556 -----------------------------------------------------------------------------------------------------------
557 -- VALIDATE_OKOGU :Function to validate the All-Russian Classificatory of Public Authorities and Management
558 ------------------------------------------------------------------------------------------------------------
559 FUNCTION validate_okogu (p_okogu VARCHAR2)
560 RETURN NUMBER
561 IS
562 chk_code NUMBER;
563 BEGIN
564 IF p_okogu = '00000'
565 THEN
566 RETURN 0;
567 END IF;
568 chk_code := instr (p_okogu, '.', 1, 1) + instr (p_okogu, '-', 1, 1)
569 + instr (p_okogu, '+', 1, 1);
570 IF chk_code > 0
571 THEN
572 RETURN 0;
573 END IF;
574 IF hr_ni_chk_pkg.chk_nat_id_format (p_okogu, 'DDDDD') = '0'
575 THEN
576 RETURN 0;
577 END IF;
578 RETURN 1;
579 END validate_okogu;
580
581 --
582 --------------------------------------------------------------------------------------------------------
583 -- VALIDATE_OKPO :Function to validate the All-Russian Classificatory of Enterprises and Organizations
584 --------------------------------------------------------------------------------------------------------
585 FUNCTION validate_okpo (p_okpo VARCHAR2)
586 RETURN NUMBER
587 IS
588 chk_code NUMBER;
589 BEGIN
590 IF LENGTH (p_okpo) <> 8
591 THEN
592 RETURN 0;
593 END IF;
594 IF hr_ni_chk_pkg.chk_nat_id_format (p_okpo, 'DDDDDDDD') = '0'
595 THEN
596 RETURN 0;
597 END IF;
598 IF p_okpo = '00000000'
599 THEN
600 RETURN 0;
601 END IF;
602 chk_code := instr (p_okpo, '.', 1, 1) + instr (p_okpo, '-', 1, 1) + instr (p_okpo, '+', 1, 1);
603 IF chk_code > 0
604 THEN
605 RETURN 0;
606 END IF;
607 RETURN 1;
608 END validate_okpo;
609
610 ------------------------------------------------------------------------------------------------------------
611 -- VALIDATE_ORG_SPIFN :Function to validate the Registration Number in the Obligatory Medical Insurance Fund
612 ------------------------------------------------------------------------------------------------------------
613 FUNCTION validate_org_spifn (p_spifn VARCHAR2)
614 RETURN NUMBER
615 IS
616 chk_code NUMBER;
617 BEGIN
618 IF p_spifn = '000-000-000000'
619 THEN
620 RETURN 0;
621 END IF;
622 chk_code := instr (p_spifn, '.', 1, 1) + instr (p_spifn, '+', 1, 1);
623 IF chk_code > 0
624 THEN
625 RETURN 0;
626 END IF;
627 IF hr_ni_chk_pkg.chk_nat_id_format (p_spifn, 'DDD-DDD-DDDDDD') = '0'
628 THEN
629 RETURN 0;
630 END IF;
631 IF instr (p_spifn, '-', 1) <> 4 OR instr (p_spifn, '-', 5) <> 8
632 THEN
633 RETURN 0;
634 END IF;
635 RETURN 1;
636 END validate_org_spifn;
637
638
639 --------------------------------------------------------------------------------------------------------
640 -- VALIDATE_OKVED :Function to validate the All-Russian Classificatory of Company's activities types
641 --------------------------------------------------------------------------------------------------------
642 FUNCTION validate_okved (p_okved VARCHAR2)
643 RETURN NUMBER
644 IS
645 chk_code NUMBER;
646 BEGIN
647 IF chk_id_format (p_okved, 'DD') = '0'
648 AND chk_id_format (p_okved, 'DD.D') = '0'
649 AND chk_id_format (p_okved, 'DD.DD') = '0'
650 AND chk_id_format (p_okved, 'DD.DD.D') = '0'
651 AND chk_id_format (p_okved, 'DD.DD.DD') = '0'
652 THEN
653 RETURN 0;
654 END IF;
655 chk_code := instr (p_okved, '-', 1, 1) + instr (p_okved, '+', 1, 1);
656 IF chk_code > 0
657 THEN
658 RETURN 0;
659 END IF;
660 chk_code := LENGTH (p_okved);
661 IF chk_code = 4 OR chk_code = 5
662 THEN
663 IF instr (p_okved, '.', 1) <> 3 OR instr (p_okved, '.', 4) <> 0
664 THEN
665 RETURN 0;
666 END IF;
667 ELSIF chk_code = 7 OR chk_code = 8
668 THEN
669 IF instr (p_okved, '.', 1) <> 3 OR instr (p_okved, '.', 4) <> 6 OR instr (p_okved, '.', 7) <> 0
670 THEN
671 RETURN 0;
672 END IF;
673 ELSIF chk_code <> 2
674 THEN
675 RETURN 0;
676 END IF;
677 IF p_okved = '00'
678 OR p_okved = '00.0'
679 OR p_okved = '00.00'
680 OR p_okved = '00.00.0'
681 OR p_okved = '00.00.00'
682 THEN
683 RETURN 0;
684 END IF;
685 RETURN 1;
686 END validate_okved;
687
688 -----------------------------------------------------------
689 FUNCTION validate_bik (p_bank_bik VARCHAR2)
690 RETURN NUMBER
691 IS
692 chk_code NUMBER;
693 BEGIN
694 IF LENGTH (p_bank_bik) <> 9
695 THEN
696 RETURN 0;
697 END IF;
698 IF hr_ni_chk_pkg.chk_nat_id_format (p_bank_bik, 'DDDDDDDDD') = '0'
699 THEN
700 RETURN 0;
701 END IF;
702 IF p_bank_bik = '000000000'
703 THEN
704 RETURN 0;
705 END IF;
706 chk_code :=
707 instr (p_bank_bik, '.', 1, 1) + instr (p_bank_bik, '-', 1, 1)
708 + instr (p_bank_bik, '+', 1, 1);
709 IF chk_code > 0
710 THEN
711 RETURN 0;
712 END IF;
713 RETURN 1;
714 END validate_bik;
715
716 -----------------------------------------------------------
717 FUNCTION validate_acc_no (p_bank_acc_no VARCHAR2)
718 RETURN NUMBER
719 IS
720 chk_code NUMBER;
721 BEGIN
722 IF LENGTH (p_bank_acc_no) <> 20
723 THEN
724 RETURN 0;
725 END IF;
726 IF hr_ni_chk_pkg.chk_nat_id_format (p_bank_acc_no, 'DDDDDDDDDDDDDDDDDDDD') = '0'
727 THEN
728 RETURN 0;
729 END IF;
730 IF p_bank_acc_no = '00000000000000000000'
731 THEN
732 RETURN 0;
733 END IF;
734 chk_code :=
735 instr (p_bank_acc_no, '.', 1, 1)
736 + instr (p_bank_acc_no, '-', 1, 1)
737 + instr (p_bank_acc_no, '+', 1, 1);
738 IF chk_code > 0
739 THEN
740 RETURN 0;
741 END IF;
742 RETURN 1;
743 END validate_acc_no;
744
745 ----------------------------------------------------------
746 ------------------------------------------------
747 -- Bank Code Validation Function
748 ------------------------------------------------
749 FUNCTION validate_bank_info (p_bank_inn VARCHAR2)
750 RETURN NUMBER
751 IS
752 l_flag NUMBER;
753 chk_code NUMBER;
754 BEGIN
755 chk_code :=
756 instr (p_bank_inn, '.', 1, 1)
757 + instr (p_bank_inn, '-', 1, 1)
758 + instr (p_bank_inn, '+', 1, 1);
759 IF chk_code > 0
760 THEN
761 RETURN 0;
762 END IF;
763 l_flag := validate_tax_no (p_bank_inn);
764 RETURN l_flag;
765 END validate_bank_info;
766
767 FUNCTION chk_id_format (p_id IN VARCHAR2, p_format_string IN VARCHAR2)
768 RETURN VARCHAR2
769 AS
770 l_nat_id VARCHAR2 (30);
771 l_format_mask VARCHAR2 (30);
772 l_format_string VARCHAR2 (30);
773 l_valid NUMBER;
774 l_len_format_mask NUMBER;
775 l_number_format_ch NUMBER;
776 l_no_format_nat_id VARCHAR2 (30);
777 l_no_format_string_opt VARCHAR2 (30);
778 l_no_format_string_nopt VARCHAR2 (30);
779 l_format_count NUMBER;
780 l_nat_id_count NUMBER;
781 l_lgth_string_nopt NUMBER;
782 l_lgth_string_opt NUMBER;
783 l_lgth_nat_id NUMBER;
784 --
785 BEGIN
786 --
787 --
788 l_nat_id := '0';
789 l_valid := 1;
790 /* First Derive the format mask from the format string.
791 This is defined as the remainder of the string, after
792 the format characters, namely 'ABDEX' have been removed.
793 Also generate the format mask without any kind of
794 format characters for continued use in the processing */
795 l_format_mask := translate (p_format_string, 'CABDEX', 'C');
796 l_format_string := translate (p_format_string, 'A !."$%^&*()-_+=`[]{};''#:@~<>?', 'A');
797 /* Check validity of format string */
798 IF translate (l_format_string, 'CABDEX', 'C') IS NULL
799 THEN
800 /* Check validity of format mask */
801 IF translate (upper (l_format_mask), 'A !."$%^&*()-_+=`[]{};''#:@~<>?', 'A') IS NULL
802 THEN
803 /* Check that the format string and id number are the same length */
804 /* - that is minus any optional characters */
805 l_no_format_string_opt := translate (upper (l_format_string), 'ABDEX', 'ABDEX');
806 l_no_format_string_nopt := translate (upper (l_format_string), 'ADXBE', 'ADX');
807 l_no_format_nat_id := translate (upper (p_id), 'A !."$%^&*()-_+=`[]{};''#:@~<>?', 'A');
808 l_lgth_string_nopt := LENGTH (l_no_format_string_nopt);
809 l_lgth_string_opt := LENGTH (l_no_format_string_opt);
810 l_lgth_nat_id := LENGTH (l_no_format_nat_id);
811 IF ((l_lgth_nat_id >= l_lgth_string_nopt) AND (l_lgth_nat_id <= l_lgth_string_opt))
812 THEN
813 /* If processing reaches this point, we have a valid format mask, a valid format string
814 and a format string that can be checked against the id
815 Main format validation can now preceed */
816 FOR l_char_pos IN 1 .. l_lgth_string_opt
817 LOOP
818 IF (substr (l_no_format_string_opt, l_char_pos, 1) = 'A')
819 THEN
820 IF ( substr (l_no_format_nat_id, l_char_pos, 1) < 'A'
821 OR substr (l_no_format_nat_id, l_char_pos, 1) > 'Z'
822 )
823 THEN
824 l_valid := 0;
825 END IF;
826 ELSIF (substr (l_no_format_string_opt, l_char_pos, 1) = 'B')
827 THEN
828 IF (l_lgth_nat_id >= l_char_pos)
829 THEN
830 IF ( substr (l_no_format_nat_id, l_char_pos, 1) < 'A'
831 OR substr (l_no_format_nat_id, l_char_pos, 1) > 'Z'
832 )
833 THEN
834 l_valid := 0;
835 END IF;
836 END IF;
837 ELSIF (substr (l_no_format_string_opt, l_char_pos, 1) = 'D')
838 THEN
839 IF ( substr (l_no_format_nat_id, l_char_pos, 1) < '0'
840 OR substr (l_no_format_nat_id, l_char_pos, 1) > '9'
841 )
842 THEN
843 l_valid := 0;
844 END IF;
845 ELSIF (substr (l_no_format_string_opt, l_char_pos, 1) = 'E')
846 THEN
847 IF (l_lgth_nat_id >= l_char_pos)
848 THEN
849 IF ( substr (l_no_format_nat_id, l_char_pos, 1) < '0'
850 OR substr (l_no_format_nat_id, l_char_pos, 1) > '9'
851 )
852 THEN
853 l_valid := 0;
854 END IF;
855 END IF;
856 ELSIF (substr (l_no_format_string_opt, l_char_pos, 1) = 'X')
857 THEN
858 IF ( substr (l_no_format_nat_id, l_char_pos, 1) < '0'
859 OR substr (l_no_format_nat_id, l_char_pos, 1) > '9'
860 )
861 AND ( substr (l_no_format_nat_id, l_char_pos, 1) < 'A'
862 OR substr (l_no_format_nat_id, l_char_pos, 1) > 'Z'
863 )
864 THEN
865 l_valid := 0;
866 END IF;
867 END IF;
868 EXIT WHEN l_valid = 0;
869 END LOOP;
870
871 IF l_valid = 1
872 THEN
873 /* We have a valid id - now to return it in the format mask required */
874 l_format_count := 1;
875 l_nat_id_count := 1;
876 /* Reset the id to null before adding the passed id */
877 l_nat_id := '';
878
879 FOR l_format_pos IN 1 .. LENGTH (p_format_string)
880 LOOP
881 --
882 --
883 IF (translate (substr (p_format_string, l_format_pos, 1), 'CABDEX', 'C') IS NOT NULL
884 )
885 THEN
886 /* We have a format character - add it on to the return id */
887 l_nat_id := l_nat_id || substr (p_format_string, l_format_pos, 1);
888 ELSE
889 /* We have a id character - add it on to the return variable */
890 l_nat_id := l_nat_id || substr (l_no_format_nat_id, l_nat_id_count, 1);
891 l_nat_id_count := l_nat_id_count + 1;
892 END IF;
893 END LOOP;
894 ELSE
895 /* The id is not in the valid format */
896 -- dbms_output.put_line('The format of the id is not correct');
897 NULL;
898 END IF;
899 ELSE
900 /* The format string and id are differing lengths */
901 NULL;
902 END IF;
903 END IF;
904 ELSE
905 /* The format string contains unexecpected characters - check to see if
906 the format string and the id are identical, if so,
907 then this corresponds to a special format inside the formula rather
908 than here, now that the formulae are calling this function */
909 NULL;
910 /* End format string check */
911 END IF;
912 --
913 --
914 RETURN l_nat_id;
915 END chk_id_format;
916 FUNCTION check_segment_number (entry_value IN VARCHAR2)
917 RETURN VARCHAR2
918 AS
919 return_value VARCHAR2(2000);
920 BEGIN
921 return_value := '1';
922 IF entry_value NOT IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20',
923 '21','22','23','24','25','26','27','28','29','30') THEN
924 return_value := fnd_message.get_string('PER','HR_RU_INVALID_SEGMENT_NUMBER');
925 END IF;
926 RETURN return_value;
927 END check_segment_number;
928
929 FUNCTION check_contract_number_unique (
930 p_contract_number VARCHAR2
931 ,p_assignment_id NUMBER
932 ,p_business_group_id NUMBER
933 ) RETURN VARCHAR2
934 AS
935 --
936 l_status VARCHAR2(1);
937 BEGIN
938 --
939 l_status := 'N';
940 BEGIN
941
942 -- bug 8660688
943 -- criteria updated to exclude the current assignment during checking
944 SELECT 'Y'
945 INTO l_status
946 FROM SYS.dual
947 WHERE EXISTS (
948 SELECT '1'
949 FROM per_assignments_f paaf,
950 hr_soft_coding_keyflex scl
951 WHERE (paaf.assignment_id <> p_assignment_id
952 or
953 p_assignment_id is null)
954 and p_contract_number = scl.segment3
955 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
956 AND paaf.business_group_id IN (
957 --Select all business groups having leg. code RU
958 SELECT o1.organization_id
959 FROM hr_organization_information o1, hr_organization_information o2
960 WHERE o1.org_information9 = 'RU'
961 AND o1.organization_id = o2.organization_id
962 AND o1.org_information_context = 'Business Group Information'
963 AND o2.org_information_context = 'CLASS'
964 AND o2.org_information1 = 'HR_BG'
965 AND o2.org_information2 = 'Y'
966 )
967 );
968
969
970 --
971 return l_status;
972 --
973 EXCEPTION
974 WHEN no_data_found
975 THEN
976 return('N');
977 END;
978 END check_contract_number_unique;
979
980 FUNCTION check_assign_category (
981 p_eff_start_date DATE
982 ,p_eff_end_date DATE
983 ,p_assignment_id NUMBER
984 ,p_person_id NUMBER
985 ,p_business_group_id NUMBER
986 ) RETURN VARCHAR2
987 AS
988 --
989 l_status VARCHAR2(1);
990 BEGIN
991 --
992 l_status := 'N';
993
994 BEGIN
995 SELECT 'Y'
996 INTO l_status
997 FROM SYS.dual
998 WHERE EXISTS (
999 SELECT '1'
1000 FROM per_all_assignments_f paaf,
1001 hr_soft_coding_keyflex scl
1002 WHERE (p_assignment_id IS NULL OR p_assignment_id <> paaf.assignment_id)
1003 AND (nvl(scl.segment2,'N') = 'N' OR
1004 paaf.soft_coding_keyflex_id IS NULL)
1005 and paaf.person_id = p_person_id
1006 and paaf.assignment_status_type_id = 1
1007 and paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1008 and paaf.business_group_id = p_business_group_id
1009 and (
1010 (paaf.effective_start_date
1011 between p_eff_start_date and p_eff_end_date)
1012 or
1013 (paaf.effective_end_date
1014 between p_eff_start_date and p_eff_end_date)
1015 or
1016 (p_eff_start_date
1017 between paaf.effective_start_date and paaf.effective_end_date)
1018 or
1019 (p_eff_end_date
1020 between paaf.effective_start_date and paaf.effective_end_date)
1021 )
1022 );
1023
1024
1025 --
1026 return l_status;
1027 --
1028 EXCEPTION
1029 WHEN no_data_found
1030 THEN
1031 return('N');
1032 END;
1033 END check_assign_category;
1034
1035 END hr_ru_utility;