[Home] [Help]
PACKAGE BODY: APPS.PER_IN_PERSON_LEG_HOOK
Source
1 PACKAGE BODY PER_IN_PERSON_LEG_HOOK AS
2 /* $Header: peinlhpe.pkb 120.10 2007/11/19 12:17:47 rsaharay noship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'per_in_person_leg_hook.';
5 g_debug BOOLEAN;
6 p_token_name pay_in_utils.char_tab_type;
7 p_token_value pay_in_utils.char_tab_type;
8
9
10 --------------------------------------------------------------------------
11 -- --
12 -- Name : VALIDATE_PAN_FORMAT --
13 -- Type : PROCEDURE --
14 -- Access : Public --
15 -- Description : Checks for the validity of the format of the PAN --
16 -- --
17 -- --
18 -- --
19 -- Parameters : --
20 -- IN : p_per_information4 VARCHAR2 --
21 -- : p_per_information_category VARCHAR2 --
22 -- OUT : N/A --
23 -- RETURN : N/A --
24 -- --
25 -- Change History : --
26 --------------------------------------------------------------------------
27 -- Rev# Date Userid Description --
28 --------------------------------------------------------------------------
29 -- 1.0 05-Apr-04 abhjain Created this procedure --
30 -- 1.1 16-May-05 sukukuma updated this procedure --
31 --------------------------------------------------------------------------
32
33 PROCEDURE validate_pan_format(
34 p_per_information_category IN VARCHAR2
35 ,p_per_information4 IN VARCHAR2
36 ) IS
37 BEGIN
38
39 NULL ;
40
41 END validate_pan_format;
42
43
44
45 --------------------------------------------------------------------------
46 -- --
47 -- Name : CHECK_PAN_AND_PAN_AF --
48 -- Type : PROCEDURE --
49 -- Access : Public --
50 -- Description : Checks that either the PAN field or the PAN Applied --
51 -- For field is null. --
52 -- --
53 -- --
54 -- Parameters : --
55 -- IN : p_per_information_category VARCHAR2 --
56 -- p_per_information4 VARCHAR2 --
57 -- : p_per_information5 VARCHAR2 --
58 -- OUT : N/A --
59 -- RETURN : N/A --
60 -- --
61 -- Change History : --
62 --------------------------------------------------------------------------
63 -- Rev# Date Userid Description --
64 --------------------------------------------------------------------------
65 -- 1.0 05-Apr-04 abhjain Created this procedure --
66 -- 1.1 16-May-05 sukukuma updated this procedure --
67 --------------------------------------------------------------------------
68
69 PROCEDURE check_pan_and_pan_af(
70 p_per_information_category IN VARCHAR2
71 ,p_per_information4 IN VARCHAR2 DEFAULT NULL
72 ,p_per_information5 IN VARCHAR2 DEFAULT NULL
73 ) IS
74 BEGIN
75
76 NULL;
77
78 END check_pan_and_pan_af;
79
80
81 --------------------------------------------------------------------------
82 -- --
83 -- Name : CHECK_UNIQUE_NUMBER_INSERT --
84 -- Type : PROCEDURE --
85 -- Access : Public --
86 -- Description : Checks for the uniqueness of the PAN, PF Number, --
87 -- ESI Number, Superannuation Number, Group Insurance --
88 -- Number, Gratuity Number and Pension Fund Number in --
89 -- the create_employee user hook. --
90 -- --
91 -- Parameters : --
92 -- IN : p_per_information_category VARCHAR2 --
93 -- : p_business_group_id NUMBER --
94 -- : p_per_information4 VARCHAR2 --
95 -- : p_per_information8 VARCHAR2 --
96 -- : p_per_information9 VARCHAR2 --
97 -- : p_per_information10 VARCHAR2 --
98 -- : p_per_information11 VARCHAR2 --
99 -- : p_per_information12 VARCHAR2 --
100 -- : p_per_information13 VARCHAR2 --
101 -- --
102 -- OUT : N/A --
103 -- RETURN : N/A --
104 -- --
105 -- Change History : --
106 --------------------------------------------------------------------------
107 -- Rev# Date Userid Description --
108 --------------------------------------------------------------------------
109 -- 1.0 05-Apr-04 abhjain Created this procedure --
110 -- 1.1 16-May-05 sukukuma updated this procedure --
111 --------------------------------------------------------------------------
112
113
114 PROCEDURE check_unique_number_insert(
115 p_per_information_category IN VARCHAR2
116 ,p_business_group_id IN NUMBER
117 ,p_per_information4 IN VARCHAR2 DEFAULT NULL
118 ,p_per_information8 IN VARCHAR2 DEFAULT NULL
119 ,p_per_information9 IN VARCHAR2 DEFAULT NULL
120 ,p_per_information10 IN VARCHAR2 DEFAULT NULL
121 ,p_per_information11 IN VARCHAR2 DEFAULT NULL
122 ,p_per_information12 IN VARCHAR2 DEFAULT NULL
123 ,p_per_information13 IN VARCHAR2 DEFAULT NULL
124 ) IS
125 BEGIN
126
127 NULL ;
128
129 END check_unique_number_insert;
130
131 --------------------------------------------------------------------------
132 -- --
133 -- Name : CHECK_UNIQUE_NUMBER_UPDATE --
134 -- Type : PROCEDURE --
135 -- Access : Public --
136 -- Description : Checks for the uniqueness of the PAN, PF Number, --
137 -- ESI Number, Superannuation Number, Group Insurance --
138 -- Number, Gratuity Number and Pension Fund Number in --
139 -- the update_person user hook. --
140 -- --
141 -- Parameters : --
142 -- IN : p_effective_date DATE --
143 -- : p_per_information_category VARCHAR2 --
144 -- : p_person_id NUMBER --
145 -- : p_per_information4 VARCHAR2 --
146 -- : p_per_information8 VARCHAR2 --
147 -- : p_per_information9 VARCHAR2 --
148 -- : p_per_information10 VARCHAR2 --
149 -- : p_per_information11 VARCHAR2 --
150 -- : p_per_information12 VARCHAR2 --
151 -- : p_per_information13 VARCHAR2 --
152 -- --
153 -- OUT : N/A --
154 -- RETURN : N/A --
155 -- --
156 -- Change History : --
157 --------------------------------------------------------------------------
158 -- Rev# Date Userid Description --
159 --------------------------------------------------------------------------
160 -- 1.0 05-Apr-04 abhjain Created this procedure --
161 -- 1.1 16-May-05 sukukuma updated this procedure --
162 --------------------------------------------------------------------------
163
164 PROCEDURE check_unique_number_update(
165 p_effective_date IN DATE
166 ,p_per_information_category IN VARCHAR2
167 ,p_person_id IN NUMBER
168 ,p_per_information4 IN VARCHAR2 DEFAULT NULL
169 ,p_per_information8 IN VARCHAR2 DEFAULT NULL
170 ,p_per_information9 IN VARCHAR2 DEFAULT NULL
171 ,p_per_information10 IN VARCHAR2 DEFAULT NULL
172 ,p_per_information11 IN VARCHAR2 DEFAULT NULL
173 ,p_per_information12 IN VARCHAR2 DEFAULT NULL
174 ,p_per_information13 IN VARCHAR2 DEFAULT NULL
175 ) IS
176 BEGIN
177
178 NULL ;
179
180 END check_unique_number_update;
181
182 --------------------------------------------------------------------------
183 -- --
184 -- Name : CHECK_EMPLOYEE --
185 -- Type : PROCEDURE --
186 -- Access : Public --
187 -- Description : Checks for
188 -- --
189 -- Parameters : --
190 -- IN : p_person_type_id NUMBER --
191 -- : p_per_information_category VARCHAR2 --
192 -- : p_per_information7 VARCHAR2 --
193 -- : p_hire_date DATE --
194 -- OUT : N/A --
195 -- RETURN : N/A --
196 -- --
197 -- Change History : --
198 --------------------------------------------------------------------------
199 -- Rev# Date Userid Description --
200 --------------------------------------------------------------------------
201 -- 1.0 04-Feb-05 lnagaraj Created this procedure --
202 -- 1.1 16-May-05 sukukuma updated this procedure --
203 --------------------------------------------------------------------------
204 PROCEDURE check_employee(p_person_type_id IN NUMBER
205 ,p_per_information_category IN VARCHAR2
206 ,p_per_information7 IN VARCHAR2
207 ,p_hire_date IN DATE
208 ) IS
209 BEGIN
210
211 NULL ;
212
213 END check_employee;
214
215 --------------------------------------------------------------------------
216 -- --
217 -- Name : CHECK_PERSON --
218 -- Type : PROCEDURE --
219 -- Access : Public --
220 -- Description : Checks for
221 -- --
222 -- Parameters : --
223 -- IN : p_person_type_id NUMBER --
224 -- p_person_id NUMBER --
225 -- : p_per_information_category VARCHAR2 --
226 -- : p_per_information7 VARCHAR2 --
227 -- : p_effective_date DATE --
228 -- OUT : N/A --
229 -- RETURN : N/A --
230 -- --
231 -- Change History : --
232 --------------------------------------------------------------------------
233 -- Rev# Date Userid Description --
234 --------------------------------------------------------------------------
235 -- 1.0 04-Feb-05 lnagaraj Created this procedure --
236 -- 1.1 16-May-05 sukukuma updated this procedure --
237 --------------------------------------------------------------------------
238 PROCEDURE check_person(p_person_id IN NUMBER
239 ,p_person_type_id IN NUMBER
240 ,p_per_information_category IN VARCHAR2
241 ,p_per_information7 IN VARCHAR2
242 ,p_effective_date IN DATE
243 ) IS
244 BEGIN
245
246 NULL;
247
248 END check_person;
249
250
251
252
253 --------------------------------------------------------------------------
254 -- --
255 -- Name : CHK_PERSON_TYPE --
256 -- Type : Function --
257 -- Access : Public --
258 -- Description : Returns true/false IF p_code is a valid Person Type --
259 -- Parameters : --
260 -- IN : p_code VARCHAR2 --
261 -- OUT : N/A --
262 -- RETURN : VARCHAR2 --
263 -- --
264 -- Change History : --
265 --------------------------------------------------------------------------
266 -- Rev# Date Userid Description --
267 --------------------------------------------------------------------------
268 -- 1.0 16/05/05 sukukuma Created this function --
269 --------------------------------------------------------------------------
270 FUNCTION chk_person_type (p_code in VARCHAR2)
271 RETURN BOOLEAN
272 IS
273 TYPE t_pt_tbl IS TABLE OF HR_LOOKUPS.LOOKUP_CODE%TYPE index by binary_integer;
274 l_person_type t_pt_tbl;
275 l_loop_count NUMBER ;
276 l_procedure VARCHAR2(100);
277 l_message VARCHAR2(250);
278
279 BEGIN
280
281 l_procedure := g_package||'chk_person_type';
282 g_debug := hr_utility.debug_enabled;
283 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
284
285 IF g_debug THEN
286 pay_in_utils.trace('p_code',p_code);
287 END IF;
288
289 -- Change here in case any new PTs to be included
290
291 l_person_type(1) := 'EMP';
292 l_person_type(2) := 'EX_EMP';
293 l_person_type(3) := 'APL_EX_EMP';
294 l_person_type(4) := 'EMP_APL';
298 l_loop_count := 6;
295 l_person_type(5) := 'EX_EMP_APL';
296 l_person_type(6) := 'CWK';
297
299
300 -- Changes above this only.
301
302 FOR i IN 1..l_loop_count
303 LOOP
304 IF l_person_type(i) = p_code then
305 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
306 RETURN TRUE;
307 END IF;
308 END LOOP;
309 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
310 RETURN FALSE;
311
312 END chk_person_type;
313
314
315
316 --------------------------------------------------------------------------
317 -- --
318 -- Name : CHECK_UNIQUE_NUMBER --
319 -- Type : PROCEDURE --
320 -- Access : Private --
321 -- Description : Checks for the uniqueness of the PAN, PF Number, --
322 -- ESI Number, Superannuation Number, Group Insurance --
323 -- Number, Gratuity Number and Pension Fund Number --
324 -- --
325 -- Parameters --
326 -- IN : p_business_group_id NUMBER --
327 -- IN : p_person_id NUMBER --
328 -- IN : p_field VARCHAR2 --
329 -- IN : p_value VARCHAR2 --
330 -- OUT : P_message_name VARCHAR2 --
331 -- OUT : p_token_name VARCHAR2 --
332 -- OUT : p_token_value VARCHAR2 --
333 -- --
334 -- --
335 -- OUT : 3 --
336 -- RETURN : N/A --
337 -- --
338 -- Change History : --
339 --------------------------------------------------------------------------
340 -- Rev# Date Userid Description --
341 --------------------------------------------------------------------------
342 -- 1.0 16/05/05 sukukuma Created this procedure --
343 -- 1.1 19/01/06 abhjain Added check for PAN Ref Number --
344 -- 1.2 10/07/07 sivanara Added check for NSSN(PF Monthly Returns) --
345 -- 1.3 16/11/07 rsaharay Added check to check the identifier --
346 -- uniqueness --
347 --------------------------------------------------------------------------
348
349 PROCEDURE check_unique_number(p_business_group_id IN NUMBER
350 ,p_person_id IN NUMBER
351 ,p_field IN VARCHAR2
352 ,p_value IN VARCHAR2
353 ,p_message_name OUT NOCOPY VARCHAR2
354 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
355 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
356 )
357 IS
358
359 CURSOR csr_check
360 IS
361 SELECT NVL(org_information3,'Y') FROM hr_organization_information
362 WHERE organization_id = p_business_group_id
363 AND ORG_INFORMATION_CONTEXT = 'PER_IN_STAT_SETUP_DF';
364
365 CURSOR csr_number
366 IS
367 SELECT 1 FROM per_people_f
368 WHERE business_group_id = p_business_group_id
369 AND per_information_category = 'IN'
370 AND (person_id <> p_person_id OR p_person_id is null)
371 AND decode(p_field,'PAN',per_information4
372 ,'PF Number',per_information8
373 ,'ESI Number',per_information9
374 ,'Super Annuation Number',per_information10
375 ,'Group Insurance Number',per_information11
376 ,'Gratuity Number',per_information12
377 ,'Pension Number',per_information13
378 ,'PAN Reference Number',per_information14
379 ,'NSSN',per_information15) = p_value;
380
381
382 l_count NUMBER;
383 l_check VARCHAR2(1) := 'Y';
384 l_procedure VARCHAR2(100);
385 l_message VARCHAR2(250);
386
387 BEGIN
388
389 l_procedure := g_package||'check_unique_number';
390 g_debug := hr_utility.debug_enabled;
391 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
392
393 IF g_debug THEN
394 pay_in_utils.trace('**************************************************','********************');
395 pay_in_utils.trace('p_business_group_id',p_business_group_id);
396 pay_in_utils.trace('p_person_id ',p_person_id );
397 pay_in_utils.trace('p_field ',p_field );
398 pay_in_utils.trace('p_value ',p_value );
399 pay_in_utils.trace('p_message_name ',p_message_name );
400 pay_in_utils.trace('**************************************************','********************');
401 END IF;
402
406 FETCH csr_check INTO l_check;
403 p_message_name := 'SUCCESS';
404
405 OPEN csr_check;
407 CLOSE csr_check;
408
409 IF g_debug THEN
410 pay_in_utils.trace('**************************************************','********************');
411 pay_in_utils.trace('l_check ',l_check);
412 pay_in_utils.trace('**************************************************','********************');
413 END IF;
414
415 IF l_check = 'Y' THEN
416
417 OPEN csr_number;
418 FETCH csr_number
419 INTO l_count;
420 CLOSE csr_number;
421 pay_in_utils.set_location(g_debug,l_procedure,20);
422
423 IF l_count <> 0 THEN
424 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
425 p_token_name(1) := 'NUMBER_CATEGORY';
426 p_token_value(1) := p_field;
427
428 IF g_debug THEN
429 pay_in_utils.trace('**************************************************','********************');
430 pay_in_utils.trace('p_message_name ',p_message_name);
431 pay_in_utils.trace('**************************************************','********************');
432 END IF;
433
434 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
435 RETURN;
436 END IF;
437
438 END IF ;
439
440 IF g_debug THEN
441 pay_in_utils.trace('**************************************************','********************');
442 pay_in_utils.trace('p_message_name ',p_message_name);
443 pay_in_utils.trace('**************************************************','********************');
444 END IF;
445
446 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
447
448 END check_unique_number;
449
450
451
452
453 --------------------------------------------------------------------------
454 -- --
455 -- Name : CHECK_PAN_FORMAT --
456 -- Type : PROCEDURE --
457 -- Access : Public --
458 -- Description : Checks for the validity of the format of the PAN --
459 -- --
460 -- --
461 -- --
462 -- Parameters : --
463 -- IN : p_pan VARCHAR2 --
464 -- : p_pan_af VARCHAR2 --
465 -- : p_panref_number VARCHAR2 --
466 -- : p_message_name VARCHAR2 --
467 -- OUT : p_token_name VARCHAR2 --
468 -- : p_token_value VARCHAR2 --
469 -- OUT : 3 --
470 -- RETURN : N/A --
471 -- --
472 -- Change History : --
473 --------------------------------------------------------------------------
474 -- Rev# Date Userid Description --
475 --------------------------------------------------------------------------
476 -- 1.0 16/05/05 sukukuma Created this procedure --
477 -- 1.1 19/01/06 abhjain Added p_panref_number --
478 -- 1.2 27/01/06 lnagaraj Modified check for numeric part in PAN --
479 --------------------------------------------------------------------------
480
481 PROCEDURE check_pan_format( p_pan IN VARCHAR2
482 ,p_pan_af IN VARCHAR2
483 ,p_panref_number IN VARCHAR2
484 ,p_message_name OUT NOCOPY VARCHAR2
485 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
486 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
487 )
488 IS
489
490 l_num_string NUMBER ;
491 l_char_string VARCHAR2(5);
492 l_char6_9_string VARCHAR2(4);
493 E_INVALID_FORMAT_ERR EXCEPTION;
494
495 l_procedure VARCHAR2(100);
496 l_message VARCHAR2(250);
497
498 BEGIN
499 l_procedure := g_package||'check_pan_format';
500 g_debug := hr_utility.debug_enabled;
501 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
502
503 IF g_debug THEN
504 pay_in_utils.trace('**************************************************','********************');
505 pay_in_utils.trace('p_pan ',p_pan );
506 pay_in_utils.trace('p_pan_af ',p_pan_af );
507 pay_in_utils.trace('p_panref_number',p_panref_number);
508 pay_in_utils.trace('p_message_name ',p_message_name );
509 pay_in_utils.trace('**************************************************','********************');
510 END IF;
511
512 -- PAN format - XXXXX9999X
513 -- Check for the length
514 IF NOT length(p_pan) = 10 THEN
515 p_message_name := 'PER_IN_INVALID_FORMAT';
516 p_token_name(1) := 'FIELD';
517 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
521
518 p_token_name(2) := 'FORMAT';
519 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
520 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
522 IF g_debug THEN
523 pay_in_utils.trace('**************************************************','********************');
524 pay_in_utils.trace('p_message_name ',p_message_name);
525 pay_in_utils.trace('**************************************************','********************');
526 END IF;
527
528 RETURN;
529 END IF;
530
531 -- Check for the number part
532
533 BEGIN
534 l_num_string := substr(p_pan, 6, 4); /*decimal numbers in format '12.3' will still be considered valid, but this shouldn't be the case*/
535
536 l_char6_9_string := substr(p_pan, 6, 4);
537
538 FOR l_count in 1..4 LOOP
539 -- Check for the numeric part.
540 IF NOT ascii(substr(l_char6_9_string, l_count, 1) ) BETWEEN ASCII('0') AND ASCII('9') THEN
541 p_message_name := 'PER_IN_INVALID_FORMAT';
542 p_token_name(1) := 'FIELD';
543 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
544 p_token_name(2) := 'FORMAT';
545 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
546
547 IF g_debug THEN
548 pay_in_utils.trace('**************************************************','********************');
549 pay_in_utils.trace('p_message_name ',p_message_name);
550 pay_in_utils.trace('**************************************************','********************');
551 END IF;
552
553 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
554 RETURN;
555 END IF ;
556 END LOOP ;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 p_message_name := 'PER_IN_INVALID_FORMAT';
561 p_token_name(1) := 'FIELD';
562 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
563 p_token_name(2) := 'FORMAT';
564 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
565
566 IF g_debug THEN
567 pay_in_utils.trace('**************************************************','********************');
568 pay_in_utils.trace('p_message_name ',p_message_name);
569 pay_in_utils.trace('**************************************************','********************');
570 END IF;
571
572 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
573 RETURN;
574 END;
575 --
576 --
577 -- Check for the alphabatical part
578 l_char_string := substr(p_pan, 10, 1) ;
579 IF NOT ascii(l_char_string) BETWEEN ASCII('A') AND ASCII('Z') THEN
580 p_message_name := 'PER_IN_INVALID_FORMAT';
581 p_token_name(1) := 'FIELD';
582 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
583 p_token_name(2) := 'FORMAT';
584 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
585
586 IF g_debug THEN
587 pay_in_utils.trace('**************************************************','********************');
588 pay_in_utils.trace('p_message_name ',p_message_name);
589 pay_in_utils.trace('**************************************************','********************');
590 END IF;
591
592 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
593 RETURN;
594 END IF ;
595 --
596 --
597 l_char_string := substr(p_pan,1,5) ;
598 FOR l_count in 1..5 LOOP
599 -- Check for the first 5 alphabats
600 IF NOT ascii( substr(l_char_string, l_count, 1) ) BETWEEN ASCII('A') AND ASCII('Z') THEN
601 p_message_name := 'PER_IN_INVALID_FORMAT';
602 p_token_name(1) := 'FIELD';
603 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
604 p_token_name(2) := 'FORMAT';
605 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
606
607 IF g_debug THEN
608 pay_in_utils.trace('**************************************************','********************');
609 pay_in_utils.trace('p_message_name ',p_message_name);
610 pay_in_utils.trace('**************************************************','********************');
611 END IF;
612
613 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
614 RETURN;
615 END IF ;
616 END LOOP ;
617
618 --Checks that either the PAN field or the PAN Applied For field is null.
619 IF p_pan IS NOT NULL AND p_pan_af IS NOT NULL THEN
620 p_message_name := 'PER_IN_TWO_FIELD_MISMATCH';
621 p_token_name(1) := 'FIELD1';
622 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
623 p_token_name(2) := 'FIELD2';
624 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_AF');
625
626 IF g_debug THEN
627 pay_in_utils.trace('**************************************************','********************');
628 pay_in_utils.trace('p_message_name ',p_message_name);
629 pay_in_utils.trace('**************************************************','********************');
630 END IF;
631
632 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
633 RETURN;
634 END IF;
635
639 p_token_name(1) := 'FIELD1';
636 --Checks that either the PAN field or the PAN Ref Number For field is null.
637 IF p_pan IS NOT NULL AND p_panref_number IS NOT NULL THEN
638 p_message_name := 'PER_IN_TWO_FIELD_MISMATCH';
640 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
641 p_token_name(2) := 'FIELD2';
642 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_REF');
643
644 IF g_debug THEN
645 pay_in_utils.trace('**************************************************','********************');
646 pay_in_utils.trace('p_message_name ',p_message_name);
647 pay_in_utils.trace('**************************************************','********************');
648 END IF;
649
650 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
651 RETURN;
652 END IF;
653 p_message_name:='SUCCESS';
654
655 IF g_debug THEN
656 pay_in_utils.trace('**************************************************','********************');
657 pay_in_utils.trace('p_message_name ',p_message_name);
658 pay_in_utils.trace('**************************************************','********************');
659 END IF;
660
661 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
662
663
664 END check_pan_format;
665
666
667
668 --------------------------------------------------------------------------
669 -- --
670 -- Name : CHECK_IN_PERSON_INT --
671 -- Type : PROCEDURE --
672 -- Access : Public --
673 -- Description : --
674 -- --
675 -- Parameters : --
676 -- IN : p_business_group_id NUMBER --
677 -- : p_person_type_id NUMBER --
678 -- : p_person_id NUMBER --
679 -- : p_effective_date DATE --
680 -- : p_pan VARCHAR2 --
681 -- : p_pan_af VARCHAR2 --
682 -- : p_military_status VARCHAR2 --
683 -- : p_resident_status VARCHAR2 --
684 -- : p_pf_number VARCHAR2 --
685 -- : p_esi_number VARCHAR2 --
686 -- : p_sa_number VARCHAR2 --
687 -- : p_group_ins_number VARCHAR2 --
688 -- : p_gratuity_number VARCHAR2 --
689 -- : p_pension_number VARCHAR2 --
690 -- : p_panref_number VARCHAR2 --
691 -- : p_NSSN VARCHAR2 --
692 -- OUT : p_message_name VARCHAR2 --
693 -- OUT : p_token_name VARCHAR2 --
694 -- OUT : p_token_value VARCHAR2 --
695 -- --
696 -- OUT : 3 --
697 -- RETURN : N/A --
698 -- --
699 -- Change History : --
700 --------------------------------------------------------------------------
701 -- Rev# Date Userid Description --
702 --------------------------------------------------------------------------
703 -- 1.0 16/05/05 sukukuma Created this procedure --
704 -- 1.0 14/12/05 abhjain Added p_panref_number --
705 -- 1.2 10/07/07 sivanara Added parameter p_NSSN and code to check --
706 -- NSSN PF Monthly Returns) format --
707 --------------------------------------------------------------------------
708
709 PROCEDURE check_in_person_int
710 (p_business_group_id IN NUMBER
711 ,p_person_type_id IN NUMBER
712 ,p_person_id IN NUMBER
713 ,p_effective_date IN DATE
714 ,p_pan IN VARCHAR2
715 ,p_pan_af IN VARCHAR2
716 ,p_military_status IN VARCHAR2
717 ,p_resident_status IN VARCHAR2
718 ,p_pf_number IN VARCHAR2
719 ,p_esi_number IN VARCHAR2
720 ,p_sa_number IN VARCHAR2
721 ,p_group_ins_number IN VARCHAR2
722 ,p_gratuity_number IN VARCHAR2
723 ,p_pension_number IN VARCHAR2
724 ,p_panref_number IN VARCHAR2
725 ,p_NSSN IN VARCHAR2
726 ,p_message_name OUT NOCOPY VARCHAR2
727 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
728 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
729 )
730 IS
731
732 CURSOR csr_pt
733 IS
734 SELECT system_person_type
738
735 FROM per_person_types
736 WHERE business_group_id = p_business_group_id
737 AND person_type_id = p_person_type_id;
739 CURSOR csr_ptu
740 IS
741 SELECT ppt.system_person_type
742 FROM per_person_type_usages_f pptu
743 ,per_person_types ppt
744 WHERE pptu.person_type_id = ppt.person_type_id
745 AND pptu.person_id = p_person_id
746 AND ppt.business_group_id = p_business_group_id
747 AND p_effective_date BETWEEN pptu.effective_start_date
748 AND pptu.effective_end_date;
749
750 l_person_type per_person_types.system_person_type%TYPE;
751 l_value hr_lookups.meaning%TYPE;
752 l_pan_af hr_lookups.lookup_code%TYPE;
753 l_military_status hr_lookups.lookup_code%TYPE;
754 l_resident_status hr_lookups.lookup_code%TYPE;
755 l_procedure VARCHAR2(100);
756 l_message VARCHAR2(250);
757
758
759 BEGIN
760
761
762
763 l_procedure := g_package||'check_in_person_int';
764 g_debug := hr_utility.debug_enabled;
765 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
766
767 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
768 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
769 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
770 RETURN;
771 END IF;
772
773 IF g_debug THEN
774 pay_in_utils.trace('**************************************************','********************');
775 pay_in_utils.trace('p_business_group_id ',p_business_group_id );
776 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
777 pay_in_utils.trace('p_person_id ',p_person_id );
778 pay_in_utils.trace('p_effective_date ',p_effective_date );
779 pay_in_utils.trace('p_pan ',p_pan );
780 pay_in_utils.trace('p_pan_af ',p_pan_af );
781 pay_in_utils.trace('p_military_status ',p_military_status );
782 pay_in_utils.trace('p_resident_status ',p_resident_status );
783 pay_in_utils.trace('p_pf_number ',p_pf_number );
784 pay_in_utils.trace('p_esi_number ',p_esi_number );
785 pay_in_utils.trace('p_sa_number ',p_sa_number );
786 pay_in_utils.trace('p_group_ins_number ',p_group_ins_number );
787 pay_in_utils.trace('p_gratuity_number ',p_gratuity_number );
788 pay_in_utils.trace('p_pension_number ',p_pension_number );
789 pay_in_utils.trace('p_panref_number ',p_panref_number );
790 pay_in_utils.trace('p_NSSN ',p_NSSN );
791 pay_in_utils.trace('p_message_name ',p_message_name );
792 pay_in_utils.trace('**************************************************','********************');
793 END IF;
794
795 --
796 -- Check for mandatory arguments
797 --
798 IF p_person_id IS NULL THEN
799 --
800 -- This means we are calling from insert
801 --
802 IF p_person_type_id IS NOT NULL THEN
803 OPEN csr_pt ;
804 FETCH csr_pt INTO l_person_type;
805 IF csr_pt%NOTFOUND THEN
806 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
807 p_token_name(1) := 'VALUE';
808 p_token_value(1) := p_person_type_id;
809 p_token_name(2) := 'FIELD';
810 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PERSON_TYPE');
811
812 IF g_debug THEN
813 pay_in_utils.trace('p_message_name ',p_message_name);
814 END IF;
815
816 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
817 RETURN;
818 END IF ;
819 END IF ;
820 ELSE
821 --
822 -- This means we are updating
823 --
824 IF p_person_type_id IS NULL THEN
825 p_message_name := 'HR_7207_API_MANDATORY_ARG';
826 p_token_name(1) := 'API_NAME';
827 p_token_value(1) := l_procedure;
828 p_token_name(2) := 'ARGUMENT';
829 p_token_value(2) := 'P_PERSON_TYPE_ID';
830
831 IF g_debug THEN
832 pay_in_utils.trace('**************************************************','********************');
833 pay_in_utils.trace('p_message_name ',p_message_name);
834 pay_in_utils.trace('**************************************************','********************');
835 END IF;
836
837 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
838 RETURN ;
839 ELSE
840 OPEN csr_ptu ;
841 FETCH csr_ptu INTO l_person_type;
842 IF csr_ptu%NOTFOUND THEN
843 CLOSE csr_ptu;
844 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
845 p_token_name(1) := 'VALUE';
846 p_token_value(1) := p_person_type_id;
847 p_token_name(2) := 'FIELD';
848 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PERSON_TYPE');
849
850 IF g_debug THEN
851 pay_in_utils.trace('**************************************************','********************');
852 pay_in_utils.trace('p_message_name ',p_message_name);
853 pay_in_utils.trace('**************************************************','********************');
854 END IF;
855
856 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
857 RETURN ;
861 END IF ;
858 ELSE
859 CLOSE csr_ptu;
860 END IF;
862 END IF;
863 --
864 -- Proceed with validations only if it is a approved person type
865 --
866 IF NOT chk_person_type (l_person_type)
867
868 THEN
869 IF g_debug THEN
870 pay_in_utils.trace('**************************************************','********************');
871 pay_in_utils.trace('p_message_name ',p_message_name);
872 pay_in_utils.trace('**************************************************','********************');
873 END IF;
874
875 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
876 RETURN ;
877 END IF;
878
879 --
880 -- Check Mandatory Arguments
881 --
882 IF p_resident_status IS NULL
883 THEN
884
885 p_message_name := 'HR_7207_API_MANDATORY_ARG';
886 p_token_name(1) := 'API_NAME';
887 p_token_value(1) := l_procedure;
888 p_token_name(2) := 'ARGUMENT';
889 p_token_value(2) := 'P_RESIDENT_STATUS';
890
891 IF g_debug THEN
892 pay_in_utils.trace('**************************************************','********************');
893 pay_in_utils.trace('p_message_name ',p_message_name);
894 pay_in_utils.trace('**************************************************','********************');
895 END IF;
896
897 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
898 RETURN;
899 END IF;
900
901 --
902 -- Check valid value from lookup
903
904 IF (p_pan_af IS NOT NULL )THEN
905 l_value := hr_general.decode_lookup('YES_NO',p_pan_af);
906 IF l_value IS NULL THEN
907
908 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
909 p_token_name(1) := 'VALUE';
910 p_token_value(1) := p_pan_af;
911 p_token_name(2) := 'FIELD';
912 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_AF');
913
914 IF g_debug THEN
915 pay_in_utils.trace('**************************************************','********************');
916 pay_in_utils.trace('p_message_name ',p_message_name);
917 pay_in_utils.trace('**************************************************','********************');
918 END IF;
919
920 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 80);
921 RETURN;
922 END IF;
923 END IF;
924
925
926
927 IF (p_military_status IS NOT NULL )THEN
928 l_value := hr_general.decode_lookup('YES_NO',p_military_status);
929 IF l_value IS NULL THEN
930
931 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
932 p_token_name(1) := 'VALUE';
933 p_token_value(1) := p_military_status;
934 p_token_name(2) := 'FIELD';
935 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','EX_SERVICE');
936
937 IF g_debug THEN
938 pay_in_utils.trace('**************************************************','********************');
939 pay_in_utils.trace('p_message_name ',p_message_name);
940 pay_in_utils.trace('**************************************************','********************');
941 END IF;
942
943 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
944 RETURN;
945 END IF;
946 END IF;
947
948
949 l_value := hr_general.decode_lookup('IN_RESIDENTIAL_STATUS',p_resident_status);
950
951 IF l_value IS NULL THEN
952 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
953 p_token_name(1) := 'VALUE';
954 p_token_value(1) := p_resident_status;
955 p_token_name(2) := 'FIELD';
956 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','RESIDENTIAL_STATUS');
957
958 IF g_debug THEN
959 pay_in_utils.trace('**************************************************','********************');
960 pay_in_utils.trace('p_message_name ',p_message_name);
961 pay_in_utils.trace('**************************************************','********************');
962 END IF;
963
964 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
965 RETURN;
966
967 END IF ;
968
969 --
970 -- Validate PAN Number
971
972 check_pan_format( p_pan => p_pan
973 ,p_pan_af => p_pan_af
974 ,p_panref_number => p_panref_number
975 ,p_message_name => p_message_name
976 ,p_token_name => p_token_name
977 ,p_token_value => p_token_value
978 );
979
980 IF p_message_name <> 'SUCCESS' then
981
982 IF g_debug THEN
983 pay_in_utils.trace('**************************************************','********************');
984 pay_in_utils.trace('p_message_name ',p_message_name);
985 pay_in_utils.trace('**************************************************','********************');
986 END IF;
987
988 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 110);
989 RETURN;
990 END IF;
991
992
993 -- Check for the NSSN Format
994 IF NOT length(p_NSSN) = 14 THEN
998 p_token_name(2) := 'FORMAT';
995 p_message_name := 'PER_IN_INVALID_FORMAT';
996 p_token_name(1) := 'FIELD';
997 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN');
999 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN_FORMAT');
1000 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 115);
1001
1002 IF g_debug THEN
1003 pay_in_utils.trace('**************************************************','********************');
1004 pay_in_utils.trace('p_message_name ',p_message_name);
1005 pay_in_utils.trace('**************************************************','********************');
1006 END IF;
1007
1008 RETURN;
1009 END IF;
1010 IF instr(p_NSSN,'.') > 0 THEN
1011 p_message_name := 'PER_IN_INVALID_FORMAT';
1012 p_token_name(1) := 'FIELD';
1013 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN');
1014 p_token_name(2) := 'FORMAT';
1015 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN_FORMAT');
1016 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 115);
1017
1018 IF g_debug THEN
1019 pay_in_utils.trace('**************************************************','********************');
1020 pay_in_utils.trace('p_message_name ',p_message_name);
1021 pay_in_utils.trace('**************************************************','********************');
1022 END IF;
1023
1024 RETURN;
1025 END IF;
1026 --
1027 -- Check Uniqueness
1028 --
1029
1030
1031 -- IF p_pan IS NOT NULL THEN
1032
1033 check_unique_number
1034 (
1035 p_business_group_id => p_business_group_id
1036 ,p_person_id => p_person_id
1037 ,p_field => 'PAN'
1038 ,p_value => p_pan
1039 ,p_message_name => p_message_name
1040 ,p_token_name => p_token_name
1041 ,p_token_value => p_token_value
1042 );
1043
1044 -- END IF;
1045
1046 IF p_message_name <> 'SUCCESS' then
1047
1048 IF g_debug THEN
1049 pay_in_utils.trace('**************************************************','********************');
1050 pay_in_utils.trace('p_message_name ',p_message_name);
1051 pay_in_utils.trace('**************************************************','********************');
1052 END IF;
1053
1054 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 120);
1055 RETURN;
1056 END IF;
1057
1058 check_unique_number
1059 (
1060 p_business_group_id => p_business_group_id
1061 ,p_person_id => p_person_id
1062 ,p_field => 'PF Number'
1063 ,p_value => p_pf_number
1064 ,p_message_name => p_message_name
1065 ,p_token_name => p_token_name
1066 ,p_token_value => p_token_value
1067 );
1068
1069 IF p_message_name <> 'SUCCESS' then
1070
1071 IF g_debug THEN
1072 pay_in_utils.trace('**************************************************','********************');
1073 pay_in_utils.trace('p_message_name ',p_message_name);
1074 pay_in_utils.trace('**************************************************','********************');
1075 END IF;
1076
1077 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
1078 RETURN;
1079 END IF;
1080
1081
1082 check_unique_number
1083 (
1084 p_business_group_id => p_business_group_id
1085 ,p_person_id => p_person_id
1086 ,p_field => 'ESI Number'
1087 ,p_value => p_esi_number
1088 ,p_message_name => p_message_name
1089 ,p_token_name => p_token_name
1090 ,p_token_value => p_token_value
1091 );
1092
1093 IF p_message_name <> 'SUCCESS' then
1094
1095 IF g_debug THEN
1096 pay_in_utils.trace('**************************************************','********************');
1097 pay_in_utils.trace('p_message_name ',p_message_name);
1098 pay_in_utils.trace('**************************************************','********************');
1099 END IF;
1100
1101 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 140);
1102 RETURN;
1103 END IF;
1104
1105 check_unique_number
1106 (
1107 p_business_group_id => p_business_group_id
1108 ,p_person_id => p_person_id
1109 ,p_field => 'Super Annuation Number'
1110 ,p_value => p_sa_number
1111 ,p_message_name => p_message_name
1112 ,p_token_name => p_token_name
1113 ,p_token_value => p_token_value
1114 );
1115
1116 IF p_message_name <> 'SUCCESS' then
1117
1118 IF g_debug THEN
1119 pay_in_utils.trace('**************************************************','********************');
1120 pay_in_utils.trace('p_message_name ',p_message_name);
1121 pay_in_utils.trace('**************************************************','********************');
1122 END IF;
1123
1127
1124 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
1125 RETURN;
1126 END IF;
1128 check_unique_number
1129 (
1130 p_business_group_id => p_business_group_id
1131 ,p_person_id => p_person_id
1132 ,p_field => 'Group Insurance Number'
1133 ,p_value => p_group_ins_number
1134 ,p_message_name => p_message_name
1135 ,p_token_name => p_token_name
1136 ,p_token_value => p_token_value
1137 );
1138
1139 IF p_message_name <> 'SUCCESS' then
1140
1141 IF g_debug THEN
1142 pay_in_utils.trace('**************************************************','********************');
1143 pay_in_utils.trace('p_message_name ',p_message_name);
1144 pay_in_utils.trace('**************************************************','********************');
1145 END IF;
1146
1147 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
1148 RETURN;
1149 END IF;
1150
1151 check_unique_number
1152 (
1153 p_business_group_id => p_business_group_id
1154 ,p_person_id => p_person_id
1155 ,p_field => 'Pension Number'
1156 ,p_value => p_pension_number
1157 ,p_message_name => p_message_name
1158 ,p_token_name => p_token_name
1159 ,p_token_value => p_token_value
1160 );
1161
1162 IF p_message_name <> 'SUCCESS' then
1163
1164 IF g_debug THEN
1165 pay_in_utils.trace('**************************************************','********************');
1166 pay_in_utils.trace('p_message_name ',p_message_name);
1167 pay_in_utils.trace('**************************************************','********************');
1168 END IF;
1169
1170 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 170);
1171 RETURN;
1172 END IF;
1173
1174 check_unique_number
1175 (
1176 p_business_group_id => p_business_group_id
1177 ,p_person_id => p_person_id
1178 ,p_field => 'Gratuity Number'
1179 ,p_value => p_gratuity_number
1180 ,p_message_name => p_message_name
1181 ,p_token_name => p_token_name
1182 ,p_token_value => p_token_value
1183 );
1184
1185 IF p_message_name <> 'SUCCESS' then
1186
1187 IF g_debug THEN
1188 pay_in_utils.trace('**************************************************','********************');
1189 pay_in_utils.trace('p_message_name ',p_message_name);
1190 pay_in_utils.trace('**************************************************','********************');
1191 END IF;
1192
1193 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 180);
1194 RETURN;
1195 END IF;
1196
1197 check_unique_number
1198 (
1199 p_business_group_id => p_business_group_id
1200 ,p_person_id => p_person_id
1201 ,p_field => 'PAN Reference Number'
1202 ,p_value => p_panref_number
1203 ,p_message_name => p_message_name
1204 ,p_token_name => p_token_name
1205 ,p_token_value => p_token_value
1206 );
1207
1208 IF p_message_name <> 'SUCCESS' then
1209
1210 IF g_debug THEN
1211 pay_in_utils.trace('**************************************************','********************');
1212 pay_in_utils.trace('p_message_name ',p_message_name);
1213 pay_in_utils.trace('**************************************************','********************');
1214 END IF;
1215
1216 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 190);
1217 RETURN;
1218 END IF;
1219
1220 --Check uniqueness for the PF National Social Security Number
1221 check_unique_number
1222 (
1223 p_business_group_id => p_business_group_id
1224 ,p_person_id => p_person_id
1225 ,p_field => 'NSSN'
1226 ,p_value => p_NSSN
1227 ,p_message_name => p_message_name
1228 ,p_token_name => p_token_name
1229 ,p_token_value => p_token_value
1230 );
1231
1232 IF p_message_name <> 'SUCCESS' then
1233
1234 IF g_debug THEN
1235 pay_in_utils.trace('**************************************************','********************');
1236 pay_in_utils.trace('p_message_name ',p_message_name);
1237 pay_in_utils.trace('**************************************************','********************');
1238 END IF;
1239
1240 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
1241 RETURN;
1242 END IF;
1243
1244 IF g_debug THEN
1245 pay_in_utils.trace('p_message_name ',p_message_name);
1246 END IF;
1247
1248 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 200);
1249
1250
1251 END check_in_person_int;
1252
1253
1254
1255 --------------------------------------------------------------------------
1259 -- Access : Public --
1256 -- --
1257 -- Name : CHECK_IN_PERSON_INSERT --
1258 -- Type : PROCEDURE --
1260 -- Description : Checks for the uniqueness of the PAN, PF Number, --
1261 -- ESI Number, Superannuation Number, Group Insurance --
1262 -- Number, Gratuity Number and Pension Fund Number in --
1263 -- the create_employee user hook. --
1264 -- --
1265 -- Parameters : --
1266 -- IN : p_per_information_category VARCHAR2 --
1267 -- : p_business_group_id NUMBER --
1268 -- : p_person_type_id NUMBER --
1269 -- : p_hire_date DATE --
1270 -- : p_per_information4 VARCHAR2 --
1271 -- : p_per_information5 VARCHAR2 --
1272 -- : p_per_information6 VARCHAR2 --
1273 -- : p_per_information7 VARCHAR2 --
1274 -- : p_per_information8 VARCHAR2 --
1275 -- : p_per_information9 VARCHAR2 --
1276 -- : p_per_information10 VARCHAR2 --
1277 -- : p_per_information11 VARCHAR2 --
1278 -- : p_per_information12 VARCHAR2 --
1279 -- : p_per_information13 VARCHAR2 --
1280 -- : p_per_information14 VARCHAR2 --
1281 -- : p_per_information15 VARCHAR2 --
1282 -- --
1283 -- OUT : N/A --
1284 -- RETURN : N/A --
1285 -- --
1286 -- Change History : --
1287 --------------------------------------------------------------------------
1288 -- Rev# Date Userid Description --
1289 --------------------------------------------------------------------------
1290 -- 1.0 16/05/05 sukukuma Created this procedure --
1291 -- 1.1 14/12/05 abhjain Added p_per_information14 --
1292 -- 1.2 10/07/07 sivanara Added parameter p_per_information15 for --
1293 -- NSSN(PF Monthly Retunrs). --
1294 --------------------------------------------------------------------------
1295
1296 PROCEDURE check_in_person_insert
1297 (
1298 p_per_information_category IN VARCHAR2
1299 ,p_business_group_id IN NUMBER
1300 ,p_person_type_id IN NUMBER
1301 ,p_hire_date IN DATE
1302 ,p_per_information4 IN VARCHAR2
1303 ,p_per_information5 IN VARCHAR2
1304 ,p_per_information6 IN VARCHAR2
1305 ,p_per_information7 IN VARCHAR2
1306 ,p_per_information8 IN VARCHAR2
1307 ,p_per_information9 IN VARCHAR2
1308 ,p_per_information10 IN VARCHAR2
1309 ,p_per_information11 IN VARCHAR2
1310 ,p_per_information12 IN VARCHAR2
1311 ,p_per_information13 IN VARCHAR2
1312 ,p_per_information14 IN VARCHAR2
1313 ,p_per_information15 IN VARCHAR2
1314 )
1315
1316 IS
1317 l_procedure VARCHAR2(100);
1318 l_message VARCHAR2(250);
1319 p_message_name VARCHAR2(100);
1320 p_person_id NUMBER := NULL;
1321
1322
1323 BEGIN
1324
1325 l_procedure := g_package||'check_in_person_insert';
1326 g_debug := hr_utility.debug_enabled;
1327 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1328
1329 IF g_debug THEN
1330 pay_in_utils.trace('**************************************************','********************');
1331 pay_in_utils.trace('p_per_information_category',p_per_information_category);
1332 pay_in_utils.trace('p_business_group_id ',p_business_group_id );
1333 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
1334 pay_in_utils.trace('p_hire_date ',p_hire_date );
1335 pay_in_utils.trace('p_per_information4 ',p_per_information4 );
1336 pay_in_utils.trace('p_per_information5 ',p_per_information5 );
1337 pay_in_utils.trace('p_per_information6 ',p_per_information6 );
1338 pay_in_utils.trace('p_per_information7 ',p_per_information7 );
1339 pay_in_utils.trace('p_per_information8 ',p_per_information8 );
1340 pay_in_utils.trace('p_per_information9 ',p_per_information9 );
1341 pay_in_utils.trace('p_per_information10 ',p_per_information10 );
1342 pay_in_utils.trace('p_per_information11 ',p_per_information11 );
1343 pay_in_utils.trace('p_per_information12 ',p_per_information12 );
1344 pay_in_utils.trace('p_per_information13 ',p_per_information13 );
1345 pay_in_utils.trace('p_per_information14 ',p_per_information14 );
1346 pay_in_utils.trace('p_per_information15 ',p_per_information15 );
1350 p_message_name := 'SUCCESS';
1347 pay_in_utils.trace('**************************************************','********************');
1348 END IF;
1349
1351 IF p_per_information_category <> 'IN' then
1352 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1353 RETURN;
1354 END IF;
1355
1356 check_in_person_int
1357 (p_business_group_id => p_business_group_id
1358 ,p_person_type_id => p_person_type_id
1359 ,p_person_id => p_person_id
1360 ,p_effective_date => p_hire_date
1361 ,p_pan => p_per_information4
1362 ,p_pan_af => p_per_information5
1363 ,p_military_status => p_per_information6
1364 ,p_resident_status => p_per_information7
1365 ,p_pf_number => p_per_information8
1366 ,p_esi_number => p_per_information9
1367 ,p_sa_number => p_per_information10
1368 ,p_group_ins_number => p_per_information11
1369 ,p_gratuity_number => p_per_information12
1370 ,p_pension_number => p_per_information13
1371 ,p_panref_number => p_per_information14
1372 ,p_NSSN => p_per_information15
1373 ,p_message_name => p_message_name
1374 ,p_token_name => p_token_name
1375 ,p_token_value => p_token_value
1376 );
1377
1378 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1379 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
1380 pay_in_utils.raise_message(800,p_message_name, p_token_name, p_token_value);
1381 ELSE
1382 pay_in_utils.raise_message(801,p_message_name, p_token_name, p_token_value);
1383 END IF;
1384
1385 END check_in_person_insert;
1386
1387 --------------------------------------------------------------------------
1388 -- --
1389 -- Name : CHECK_IN_PERSON_UPDATE --
1390 -- Type : PROCEDURE --
1391 -- Access : Public --
1392 -- Description : Checks for the uniqueness of the PAN, PF Number, --
1393 -- ESI Number, Superannuation Number, Group Insurance --
1394 -- Number, Gratuity Number and Pension Fund Number in --
1395 -- the create_employee user hook. --
1396 -- --
1397 -- Parameters : --
1398 -- IN : p_per_information_category VARCHAR2 --
1399 -- : p_person_id NUMBER --
1400 -- : p_effective_date DATE --
1401 -- : p_per_information4 VARCHAR2 --
1402 -- : p_per_information5 VARCHAR2 --
1403 -- : p_per_information6 VARCHAR2 --
1404 -- : p_per_information7 VARCHAR2 --
1405 -- : p_per_information8 VARCHAR2 --
1406 -- : p_per_information9 VARCHAR2 --
1407 -- : p_per_information10 VARCHAR2 --
1408 -- : p_per_information11 VARCHAR2 --
1409 -- : p_per_information12 VARCHAR2 --
1410 -- : p_per_information13 VARCHAR2 --
1411 -- : p_per_information14 VARCHAR2 --
1412 -- : p_per_information15 VARCHAR2 --
1413 -- --
1414 -- OUT : N/A --
1415 -- RETURN : N/A --
1416 -- --
1417 -- Change History : --
1418 --------------------------------------------------------------------------
1419 -- Rev# Date Userid Description --
1420 --------------------------------------------------------------------------
1421 -- 1.0 16/05/05 sukukuma Created this procedure --
1422 -- 1.1 14/12/05 abhjain Added p_per_information14 --
1423 -- 1.2 10/07/07 sivanara Added parameter p_per_information15 for --
1424 -- NSSN(PF Monthly Retunrs). --
1425 --------------------------------------------------------------------------
1426
1427 PROCEDURE check_in_person_update
1428 (
1429 p_per_information_category IN VARCHAR2
1430 ,p_person_type_id IN NUMBER
1431 ,p_person_id IN NUMBER
1432 ,p_effective_date IN DATE
1433 ,p_per_information4 IN VARCHAR2
1434 ,p_per_information5 IN VARCHAR2
1435 ,p_per_information6 IN VARCHAR2
1436 ,p_per_information7 IN VARCHAR2
1437 ,p_per_information8 IN VARCHAR2
1438 ,p_per_information9 IN VARCHAR2
1439 ,p_per_information10 IN VARCHAR2
1440 ,p_per_information11 IN VARCHAR2
1441 ,p_per_information12 IN VARCHAR2
1442 ,p_per_information13 IN VARCHAR2
1443 ,p_per_information14 IN VARCHAR2
1444 ,p_per_information15 IN VARCHAR2
1445 )
1446
1447
1451 l_business_group_id per_all_people_f.business_group_id%TYPE;
1448 IS
1449 l_procedure VARCHAR2(100);
1450 p_message_name VARCHAR2(100);
1452 l_message VARCHAR2(250);
1453
1454 CURSOR csr_person IS
1455 SELECT per_information4
1456 ,per_information5
1457 ,per_information6
1458 ,per_information7
1459 ,per_information8
1460 ,per_information9
1461 ,per_information10
1462 ,per_information11
1463 ,per_information12
1464 ,per_information13
1465 ,per_information14
1466 ,per_information15
1467 ,business_group_id
1468 FROM per_people_f
1469 WHERE p_effective_date BETWEEN effective_start_date
1470 AND effective_end_date
1471 AND person_id = p_person_id;
1472
1473 l_per_information4 per_all_people_f.per_information4%TYPE;
1474 l_per_information5 per_all_people_f.per_information5%TYPE;
1475 l_per_information6 per_all_people_f.per_information6%TYPE;
1476 l_per_information7 per_all_people_f.per_information7%TYPE;
1477 l_per_information8 per_all_people_f.per_information8%TYPE;
1478 l_per_information9 per_all_people_f.per_information9%TYPE;
1479 l_per_information10 per_all_people_f.per_information10%TYPE;
1480 l_per_information11 per_all_people_f.per_information11%TYPE;
1481 l_per_information12 per_all_people_f.per_information12%TYPE;
1482 l_per_information13 per_all_people_f.per_information13%TYPE;
1483 l_per_information14 per_all_people_f.per_information14%TYPE;
1484 l_per_information15 per_all_people_f.per_information15%TYPE;
1485
1486 BEGIN
1487
1488 l_procedure := g_package||'check_in_person_update';
1489 g_debug := hr_utility.debug_enabled;
1490 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1491
1492 IF g_debug THEN
1493 pay_in_utils.trace('**************************************************','********************');
1494 pay_in_utils.trace('p_per_information_category',p_per_information_category);
1495 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
1496 pay_in_utils.trace('p_person_id ',p_person_id );
1497 pay_in_utils.trace('p_effective_date ',p_effective_date );
1498 pay_in_utils.trace('p_per_information4 ',p_per_information4 );
1499 pay_in_utils.trace('p_per_information5 ',p_per_information5 );
1500 pay_in_utils.trace('p_per_information6 ',p_per_information6 );
1501 pay_in_utils.trace('p_per_information7 ',p_per_information7 );
1502 pay_in_utils.trace('p_per_information8 ',p_per_information8 );
1503 pay_in_utils.trace('p_per_information9 ',p_per_information9 );
1504 pay_in_utils.trace('p_per_information10 ',p_per_information10 );
1505 pay_in_utils.trace('p_per_information11 ',p_per_information11 );
1506 pay_in_utils.trace('p_per_information12 ',p_per_information12 );
1507 pay_in_utils.trace('p_per_information13 ',p_per_information13 );
1508 pay_in_utils.trace('p_per_information14 ',p_per_information14 );
1509 pay_in_utils.trace('p_per_information15 ',p_per_information15 );
1510 pay_in_utils.trace('**************************************************','********************');
1511 END IF;
1512
1513 p_message_name := 'SUCCESS';
1514
1515 IF p_per_information_category <> 'IN' then
1516 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1517 RETURN;
1518 END IF;
1519
1520 OPEN csr_person;
1521 FETCH csr_person
1522 INTO l_per_information4
1523 ,l_per_information5
1524 ,l_per_information6
1525 ,l_per_information7
1526 ,l_per_information8
1527 ,l_per_information9
1528 ,l_per_information10
1529 ,l_per_information11
1530 ,l_per_information12
1531 ,l_per_information13
1532 ,l_per_information14
1533 ,l_per_information15
1534 ,l_business_group_id;
1535 CLOSE csr_person;
1536
1537 IF NVL (p_per_information4,'X') <> hr_api.g_varchar2 THEN
1538 l_per_information4 := p_per_information4;
1539 END IF;
1540
1541 IF NVL(p_per_information5,'X') <> hr_api.g_varchar2 THEN
1542 l_per_information5 := p_per_information5;
1543 END IF;
1544
1545 IF p_per_information6 <> hr_api.g_varchar2 THEN
1546 l_per_information6 := p_per_information6;
1547 END IF;
1548
1549 IF p_per_information7 <> hr_api.g_varchar2 THEN
1550 l_per_information7 := p_per_information7;
1551 END IF;
1552
1553 IF p_per_information8 <> hr_api.g_varchar2 THEN
1554 l_per_information8 := p_per_information8;
1555 END IF;
1556
1557 IF p_per_information9 <> hr_api.g_varchar2 THEN
1558 l_per_information9 := p_per_information9;
1559 END IF;
1560
1561 IF p_per_information10 <> hr_api.g_varchar2 THEN
1562 l_per_information10 := p_per_information10;
1563 END IF;
1564
1565 IF p_per_information11 <> hr_api.g_varchar2 THEN
1566 l_per_information11 := p_per_information11;
1567 END IF;
1568
1569 IF p_per_information12 <> hr_api.g_varchar2 THEN
1570 l_per_information12 := p_per_information12;
1571 END IF;
1572
1573 IF p_per_information13 <> hr_api.g_varchar2 THEN
1577 IF NVL(p_per_information14,'X') <> hr_api.g_varchar2 THEN
1574 l_per_information13 := p_per_information13;
1575 END IF;
1576
1578 l_per_information14 := p_per_information14;
1579 END IF;
1580
1581 IF NVL(p_per_information15,'X') <> hr_api.g_varchar2 THEN
1582 l_per_information15 := p_per_information15;
1583 END IF;
1584
1585 check_in_person_int
1586 (p_business_group_id => l_business_group_id
1587 ,p_person_type_id => p_person_type_id
1588 ,p_person_id => p_person_id
1589 ,p_effective_date => p_effective_date
1590 ,p_pan => l_per_information4
1591 ,p_pan_af => l_per_information5
1592 ,p_military_status => l_per_information6
1593 ,p_resident_status => l_per_information7
1594 ,p_pf_number => l_per_information8
1595 ,p_esi_number => l_per_information9
1596 ,p_sa_number => l_per_information10
1597 ,p_group_ins_number => l_per_information11
1598 ,p_gratuity_number => l_per_information12
1599 ,p_pension_number => l_per_information13
1600 ,p_panref_number => l_per_information14
1601 ,p_NSSN => l_per_information15
1602 ,p_message_name => p_message_name
1603 ,p_token_name => p_token_name
1604 ,p_token_value => p_token_value
1605 );
1606
1607 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1608 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
1609 pay_in_utils.raise_message(800,p_message_name, p_token_name, p_token_value);
1610 ELSE
1611 pay_in_utils.raise_message(801,p_message_name, p_token_name, p_token_value);
1612 END IF;
1613
1614 END check_in_person_update;
1615
1616
1617 END per_in_person_leg_hook;