[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.11.12020000.2 2012/07/05 02:47:04 amnaraya ship $ */
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 --
67 --------------------------------------------------------------------------
64 --------------------------------------------------------------------------
65 -- 1.0 05-Apr-04 abhjain Created this procedure --
66 -- 1.1 16-May-05 sukukuma updated this procedure --
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 --------------------------------------------------------------------------
163
160 -- 1.0 05-Apr-04 abhjain Created this procedure --
161 -- 1.1 16-May-05 sukukuma updated this procedure --
162 --------------------------------------------------------------------------
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 --
266 -- Rev# Date Userid Description --
263 -- --
264 -- Change History : --
265 --------------------------------------------------------------------------
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';
295 l_person_type(5) := 'EX_EMP_APL';
296 l_person_type(6) := 'CWK';
297
298 l_loop_count := 6;
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 -- 1.4 15/01/09 lnagaraj Added FULL HINT in csr_number --
348 --------------------------------------------------------------------------
349
350 PROCEDURE check_unique_number(p_business_group_id IN NUMBER
351 ,p_person_id IN NUMBER
352 ,p_field IN VARCHAR2
353 ,p_value IN VARCHAR2
354 ,p_message_name OUT NOCOPY VARCHAR2
355 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
356 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
357 )
358 IS
359
360 CURSOR csr_check
361 IS
362 SELECT NVL(org_information3,'Y') FROM hr_organization_information
363 WHERE organization_id = p_business_group_id
364 AND ORG_INFORMATION_CONTEXT = 'PER_IN_STAT_SETUP_DF';
365
366
367
368
369 l_count NUMBER;
370 l_check VARCHAR2(1) := 'Y';
371 l_procedure VARCHAR2(100);
372 l_message VARCHAR2(250);
373 l_qry VARCHAR2(2000):='';
374 l_inf_column VARCHAR2(50) :='';
375
376 BEGIN
377
378
379 l_procedure := g_package||'check_unique_number';
380 g_debug := hr_utility.debug_enabled;
381 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
382
383 IF g_debug THEN
387 pay_in_utils.trace('p_field ',p_field );
384 pay_in_utils.trace('**************************************************','********************');
385 pay_in_utils.trace('p_business_group_id',p_business_group_id);
386 pay_in_utils.trace('p_person_id ',p_person_id );
388 pay_in_utils.trace('p_value ',p_value );
389 pay_in_utils.trace('p_message_name ',p_message_name );
390 pay_in_utils.trace('**************************************************','********************');
391 END IF;
392
393 p_message_name := 'SUCCESS';
394
395 pay_in_utils.trace('l_qry ',l_qry);
396 OPEN csr_check;
397 FETCH csr_check INTO l_check;
398 CLOSE csr_check;
399
400 IF g_debug THEN
401 pay_in_utils.trace('**************************************************','********************');
402 pay_in_utils.trace('l_check ',l_check);
403 pay_in_utils.trace('**************************************************','********************');
404 END IF;
405
406 IF l_check = 'Y' THEN
407
408
409 select decode( p_field,'PAN','per_information4'
410 ,'PF Number','per_information8'
411 ,'ESI Number','per_information9'
412 ,'Super Annuation Number','per_information10'
413 ,'Group Insurance Number','per_information11'
414 ,'Gratuity Number','per_information12'
415 ,'Pension Number','per_information13'
416 ,'PAN Reference Number','per_information14'
417 ,'NSSN','per_information15') inf_column
418 into l_inf_column from dual;
419 IF g_debug THEN
420 pay_in_utils.trace('**************************************************','********************');
421 pay_in_utils.trace('l_inf_column ',l_inf_column);
422 pay_in_utils.trace('**************************************************','********************');
423 END IF;
424
425 l_qry:=' SELECT count(1) from dual where exists(SELECT 1 FROM per_people_f '||
426 ' WHERE business_group_id = '||p_business_group_id||
427 ' AND per_information_category = ''IN'' '||
428 ' AND (person_id <>'''|| p_person_id ||''' OR '''|| p_person_id||''' is null)'||
429 ' AND '||l_inf_column||'='''||p_value||''') ';
430
431
432 EXECUTE IMMEDIATE l_qry
433 INTO l_count;
434
435 IF g_debug THEN
436 pay_in_utils.trace('**************************************************','********************');
437 pay_in_utils.trace('l_qry ',l_qry);
438 pay_in_utils.trace('l_count ',l_count);
439 pay_in_utils.trace('**************************************************','********************');
440 END IF;
441
442
443 pay_in_utils.set_location(g_debug,l_procedure,20);
444
445 IF l_count <> 0 THEN
446 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
447 p_token_name(1) := 'NUMBER_CATEGORY';
448 p_token_value(1) := p_field;
449
450 IF g_debug THEN
451 pay_in_utils.trace('**************************************************','********************');
452 pay_in_utils.trace('p_message_name ',p_message_name);
453 pay_in_utils.trace('**************************************************','********************');
454 END IF;
455
456 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
457 RETURN;
458 END IF;
459
460 END IF ;
461
462 IF g_debug THEN
463 pay_in_utils.trace('**************************************************','********************');
464 pay_in_utils.trace('p_message_name ',p_message_name);
465 pay_in_utils.trace('**************************************************','********************');
466 END IF;
467
468 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
469
470 END check_unique_number;
471
472
473
474
475 --------------------------------------------------------------------------
476 -- --
477 -- Name : CHECK_PAN_FORMAT --
478 -- Type : PROCEDURE --
479 -- Access : Public --
480 -- Description : Checks for the validity of the format of the PAN --
481 -- --
482 -- --
483 -- --
484 -- Parameters : --
485 -- IN : p_pan VARCHAR2 --
486 -- : p_pan_af VARCHAR2 --
487 -- : p_panref_number VARCHAR2 --
488 -- : p_message_name VARCHAR2 --
489 -- OUT : p_token_name VARCHAR2 --
490 -- : p_token_value VARCHAR2 --
491 -- OUT : 3 --
492 -- RETURN : N/A --
493 -- --
494 -- Change History : --
495 --------------------------------------------------------------------------
496 -- Rev# Date Userid Description --
500 -- 1.2 27/01/06 lnagaraj Modified check for numeric part in PAN --
497 --------------------------------------------------------------------------
498 -- 1.0 16/05/05 sukukuma Created this procedure --
499 -- 1.1 19/01/06 abhjain Added p_panref_number --
501 --------------------------------------------------------------------------
502
503 PROCEDURE check_pan_format( p_pan IN VARCHAR2
504 ,p_pan_af IN VARCHAR2
505 ,p_panref_number IN VARCHAR2
506 ,p_message_name OUT NOCOPY VARCHAR2
507 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
508 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
509 )
510 IS
511
512 l_num_string NUMBER ;
513 l_char_string VARCHAR2(5);
514 l_char6_9_string VARCHAR2(4);
515 E_INVALID_FORMAT_ERR EXCEPTION;
516
517 l_procedure VARCHAR2(100);
518 l_message VARCHAR2(250);
519
520 BEGIN
521 l_procedure := g_package||'check_pan_format';
522 g_debug := hr_utility.debug_enabled;
523 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
524
525 IF g_debug THEN
526 pay_in_utils.trace('**************************************************','********************');
527 pay_in_utils.trace('p_pan ',p_pan );
528 pay_in_utils.trace('p_pan_af ',p_pan_af );
529 pay_in_utils.trace('p_panref_number',p_panref_number);
530 pay_in_utils.trace('p_message_name ',p_message_name );
531 pay_in_utils.trace('**************************************************','********************');
532 END IF;
533
534 -- PAN format - XXXXX9999X
535 -- Check for the length
536 IF NOT length(p_pan) = 10 THEN
537 p_message_name := 'PER_IN_INVALID_FORMAT';
538 p_token_name(1) := 'FIELD';
539 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
540 p_token_name(2) := 'FORMAT';
541 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
542 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
543
544 IF g_debug THEN
545 pay_in_utils.trace('**************************************************','********************');
546 pay_in_utils.trace('p_message_name ',p_message_name);
547 pay_in_utils.trace('**************************************************','********************');
548 END IF;
549
550 RETURN;
551 END IF;
552
553 -- Check for the number part
554
555 BEGIN
556 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*/
557
558 l_char6_9_string := substr(p_pan, 6, 4);
559
560 FOR l_count in 1..4 LOOP
561 -- Check for the numeric part.
562 IF NOT ascii(substr(l_char6_9_string, l_count, 1) ) BETWEEN ASCII('0') AND ASCII('9') THEN
563 p_message_name := 'PER_IN_INVALID_FORMAT';
564 p_token_name(1) := 'FIELD';
565 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
566 p_token_name(2) := 'FORMAT';
567 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
568
569 IF g_debug THEN
570 pay_in_utils.trace('**************************************************','********************');
571 pay_in_utils.trace('p_message_name ',p_message_name);
572 pay_in_utils.trace('**************************************************','********************');
573 END IF;
574
575 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
576 RETURN;
577 END IF ;
578 END LOOP ;
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 p_message_name := 'PER_IN_INVALID_FORMAT';
583 p_token_name(1) := 'FIELD';
584 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
585 p_token_name(2) := 'FORMAT';
586 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
587
588 IF g_debug THEN
589 pay_in_utils.trace('**************************************************','********************');
590 pay_in_utils.trace('p_message_name ',p_message_name);
591 pay_in_utils.trace('**************************************************','********************');
592 END IF;
593
594 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
595 RETURN;
596 END;
597 --
598 --
599 -- Check for the alphabatical part
600 l_char_string := substr(p_pan, 10, 1) ;
601 IF NOT ascii(l_char_string) BETWEEN ASCII('A') AND ASCII('Z') THEN
602 p_message_name := 'PER_IN_INVALID_FORMAT';
603 p_token_name(1) := 'FIELD';
604 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
605 p_token_name(2) := 'FORMAT';
606 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
607
608 IF g_debug THEN
609 pay_in_utils.trace('**************************************************','********************');
610 pay_in_utils.trace('p_message_name ',p_message_name);
611 pay_in_utils.trace('**************************************************','********************');
612 END IF;
613
614 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
615 RETURN;
616 END IF ;
617 --
618 --
619 l_char_string := substr(p_pan,1,5) ;
620 FOR l_count in 1..5 LOOP
624 p_token_name(1) := 'FIELD';
621 -- Check for the first 5 alphabats
622 IF NOT ascii( substr(l_char_string, l_count, 1) ) BETWEEN ASCII('A') AND ASCII('Z') THEN
623 p_message_name := 'PER_IN_INVALID_FORMAT';
625 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
626 p_token_name(2) := 'FORMAT';
627 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_FORMAT');
628
629 IF g_debug THEN
630 pay_in_utils.trace('**************************************************','********************');
631 pay_in_utils.trace('p_message_name ',p_message_name);
632 pay_in_utils.trace('**************************************************','********************');
633 END IF;
634
635 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
636 RETURN;
637 END IF ;
638 END LOOP ;
639
640 --Checks that either the PAN field or the PAN Applied For field is null.
641 IF p_pan IS NOT NULL AND p_pan_af IS NOT NULL THEN
642 p_message_name := 'PER_IN_TWO_FIELD_MISMATCH';
643 p_token_name(1) := 'FIELD1';
644 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
645 p_token_name(2) := 'FIELD2';
646 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_AF');
647
648 IF g_debug THEN
649 pay_in_utils.trace('**************************************************','********************');
650 pay_in_utils.trace('p_message_name ',p_message_name);
651 pay_in_utils.trace('**************************************************','********************');
652 END IF;
653
654 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
655 RETURN;
656 END IF;
657
658 --Checks that either the PAN field or the PAN Ref Number For field is null.
659 IF p_pan IS NOT NULL AND p_panref_number IS NOT NULL THEN
660 p_message_name := 'PER_IN_TWO_FIELD_MISMATCH';
661 p_token_name(1) := 'FIELD1';
662 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN');
663 p_token_name(2) := 'FIELD2';
664 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_REF');
665
666 IF g_debug THEN
667 pay_in_utils.trace('**************************************************','********************');
668 pay_in_utils.trace('p_message_name ',p_message_name);
669 pay_in_utils.trace('**************************************************','********************');
670 END IF;
671
672 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
673 RETURN;
674 END IF;
675 p_message_name:='SUCCESS';
676
677 IF g_debug THEN
678 pay_in_utils.trace('**************************************************','********************');
679 pay_in_utils.trace('p_message_name ',p_message_name);
680 pay_in_utils.trace('**************************************************','********************');
681 END IF;
682
683 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
684
685
686 END check_pan_format;
687
688
689
690 --------------------------------------------------------------------------
691 -- --
692 -- Name : CHECK_IN_PERSON_INT --
693 -- Type : PROCEDURE --
694 -- Access : Public --
695 -- Description : --
696 -- --
697 -- Parameters : --
698 -- IN : p_business_group_id NUMBER --
699 -- : p_person_type_id NUMBER --
700 -- : p_person_id NUMBER --
701 -- : p_effective_date DATE --
702 -- : p_pan VARCHAR2 --
703 -- : p_pan_af VARCHAR2 --
704 -- : p_military_status VARCHAR2 --
705 -- : p_resident_status VARCHAR2 --
706 -- : p_pf_number VARCHAR2 --
707 -- : p_esi_number VARCHAR2 --
708 -- : p_sa_number VARCHAR2 --
709 -- : p_group_ins_number VARCHAR2 --
710 -- : p_gratuity_number VARCHAR2 --
711 -- : p_pension_number VARCHAR2 --
712 -- : p_panref_number VARCHAR2 --
713 -- : p_NSSN VARCHAR2 --
714 -- OUT : p_message_name VARCHAR2 --
715 -- OUT : p_token_name VARCHAR2 --
716 -- OUT : p_token_value VARCHAR2 --
717 -- --
718 -- OUT : 3 --
719 -- RETURN : N/A --
720 -- --
721 -- Change History : --
722 --------------------------------------------------------------------------
723 -- Rev# Date Userid Description --
724 --------------------------------------------------------------------------
728 -- NSSN PF Monthly Returns) format --
725 -- 1.0 16/05/05 sukukuma Created this procedure --
726 -- 1.0 14/12/05 abhjain Added p_panref_number --
727 -- 1.2 10/07/07 sivanara Added parameter p_NSSN and code to check --
729 --------------------------------------------------------------------------
730
731 PROCEDURE check_in_person_int
732 (p_business_group_id IN NUMBER
733 ,p_person_type_id IN NUMBER
734 ,p_person_id IN NUMBER
735 ,p_effective_date IN DATE
736 ,p_pan IN VARCHAR2
737 ,p_pan_af IN VARCHAR2
738 ,p_military_status IN VARCHAR2
739 ,p_resident_status IN VARCHAR2
740 ,p_pf_number IN VARCHAR2
741 ,p_esi_number IN VARCHAR2
742 ,p_sa_number IN VARCHAR2
743 ,p_group_ins_number IN VARCHAR2
744 ,p_gratuity_number IN VARCHAR2
745 ,p_pension_number IN VARCHAR2
746 ,p_panref_number IN VARCHAR2
747 ,p_NSSN IN VARCHAR2
748 ,p_message_name OUT NOCOPY VARCHAR2
749 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
750 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
751 )
752 IS
753
754 CURSOR csr_pt
755 IS
756 SELECT system_person_type
757 FROM per_person_types
758 WHERE business_group_id = p_business_group_id
759 AND person_type_id = p_person_type_id;
760
761 CURSOR csr_ptu
762 IS
763 SELECT ppt.system_person_type
764 FROM per_person_type_usages_f pptu
765 ,per_person_types ppt
766 WHERE pptu.person_type_id = ppt.person_type_id
767 AND pptu.person_id = p_person_id
768 AND ppt.business_group_id = p_business_group_id
769 AND p_effective_date BETWEEN pptu.effective_start_date
770 AND pptu.effective_end_date;
771
772 l_person_type per_person_types.system_person_type%TYPE;
773 l_value hr_lookups.meaning%TYPE;
774 l_pan_af hr_lookups.lookup_code%TYPE;
775 l_military_status hr_lookups.lookup_code%TYPE;
776 l_resident_status hr_lookups.lookup_code%TYPE;
777 l_procedure VARCHAR2(100);
778 l_message VARCHAR2(250);
779
780
781 BEGIN
782
783
784
785 l_procedure := g_package||'check_in_person_int';
786 g_debug := hr_utility.debug_enabled;
787 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
788
789 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
790 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
791 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
792 RETURN;
793 END IF;
794
795 IF g_debug THEN
796 pay_in_utils.trace('**************************************************','********************');
797 pay_in_utils.trace('p_business_group_id ',p_business_group_id );
798 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
799 pay_in_utils.trace('p_person_id ',p_person_id );
800 pay_in_utils.trace('p_effective_date ',p_effective_date );
801 pay_in_utils.trace('p_pan ',p_pan );
802 pay_in_utils.trace('p_pan_af ',p_pan_af );
803 pay_in_utils.trace('p_military_status ',p_military_status );
804 pay_in_utils.trace('p_resident_status ',p_resident_status );
805 pay_in_utils.trace('p_pf_number ',p_pf_number );
806 pay_in_utils.trace('p_esi_number ',p_esi_number );
807 pay_in_utils.trace('p_sa_number ',p_sa_number );
808 pay_in_utils.trace('p_group_ins_number ',p_group_ins_number );
809 pay_in_utils.trace('p_gratuity_number ',p_gratuity_number );
810 pay_in_utils.trace('p_pension_number ',p_pension_number );
811 pay_in_utils.trace('p_panref_number ',p_panref_number );
812 pay_in_utils.trace('p_NSSN ',p_NSSN );
813 pay_in_utils.trace('p_message_name ',p_message_name );
814 pay_in_utils.trace('**************************************************','********************');
815 END IF;
816
817 --
818 -- Check for mandatory arguments
819 --
820 IF p_person_id IS NULL THEN
821 --
822 -- This means we are calling from insert
823 --
824 IF p_person_type_id IS NOT NULL THEN
825 OPEN csr_pt ;
826 FETCH csr_pt INTO l_person_type;
827 IF csr_pt%NOTFOUND THEN
828 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
829 p_token_name(1) := 'VALUE';
830 p_token_value(1) := p_person_type_id;
831 p_token_name(2) := 'FIELD';
832 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PERSON_TYPE');
833
834 IF g_debug THEN
835 pay_in_utils.trace('p_message_name ',p_message_name);
836 END IF;
837
838 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
839 RETURN;
840 END IF ;
841 END IF ;
842 ELSE
843 --
844 -- This means we are updating
845 --
846 IF p_person_type_id IS NULL THEN
847 p_message_name := 'HR_7207_API_MANDATORY_ARG';
848 p_token_name(1) := 'API_NAME';
849 p_token_value(1) := l_procedure;
850 p_token_name(2) := 'ARGUMENT';
854 pay_in_utils.trace('**************************************************','********************');
851 p_token_value(2) := 'P_PERSON_TYPE_ID';
852
853 IF g_debug THEN
855 pay_in_utils.trace('p_message_name ',p_message_name);
856 pay_in_utils.trace('**************************************************','********************');
857 END IF;
858
859 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
860 RETURN ;
861 ELSE
862 OPEN csr_ptu ;
863 FETCH csr_ptu INTO l_person_type;
864 IF csr_ptu%NOTFOUND THEN
865 CLOSE csr_ptu;
866 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
867 p_token_name(1) := 'VALUE';
868 p_token_value(1) := p_person_type_id;
869 p_token_name(2) := 'FIELD';
870 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PERSON_TYPE');
871
872 IF g_debug THEN
873 pay_in_utils.trace('**************************************************','********************');
874 pay_in_utils.trace('p_message_name ',p_message_name);
875 pay_in_utils.trace('**************************************************','********************');
876 END IF;
877
878 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
879 RETURN ;
880 ELSE
881 CLOSE csr_ptu;
882 END IF;
883 END IF ;
884 END IF;
885 --
886 -- Proceed with validations only if it is a approved person type
887 --
888 IF NOT chk_person_type (l_person_type)
889
890 THEN
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, 60);
898 RETURN ;
899 END IF;
900
901 --
902 -- Check Mandatory Arguments
903 --
904 IF p_resident_status IS NULL
905 THEN
906
907 p_message_name := 'HR_7207_API_MANDATORY_ARG';
908 p_token_name(1) := 'API_NAME';
909 p_token_value(1) := l_procedure;
910 p_token_name(2) := 'ARGUMENT';
911 p_token_value(2) := 'P_RESIDENT_STATUS';
912
913 IF g_debug THEN
914 pay_in_utils.trace('**************************************************','********************');
915 pay_in_utils.trace('p_message_name ',p_message_name);
916 pay_in_utils.trace('**************************************************','********************');
917 END IF;
918
919 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
920 RETURN;
921 END IF;
922
923 --
924 -- Check valid value from lookup
925
926 IF (p_pan_af IS NOT NULL )THEN
927 l_value := hr_general.decode_lookup('YES_NO',p_pan_af);
928 IF l_value IS NULL THEN
929
930 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
931 p_token_name(1) := 'VALUE';
932 p_token_value(1) := p_pan_af;
933 p_token_name(2) := 'FIELD';
934 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_AF');
935
936 IF g_debug THEN
937 pay_in_utils.trace('**************************************************','********************');
938 pay_in_utils.trace('p_message_name ',p_message_name);
939 pay_in_utils.trace('**************************************************','********************');
940 END IF;
941
942 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 80);
943 RETURN;
944 END IF;
945 END IF;
946
947
948
949 IF (p_military_status IS NOT NULL )THEN
950 l_value := hr_general.decode_lookup('YES_NO',p_military_status);
951 IF l_value IS NULL THEN
952
953 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
954 p_token_name(1) := 'VALUE';
955 p_token_value(1) := p_military_status;
956 p_token_name(2) := 'FIELD';
957 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','EX_SERVICE');
958
959 IF g_debug THEN
960 pay_in_utils.trace('**************************************************','********************');
961 pay_in_utils.trace('p_message_name ',p_message_name);
962 pay_in_utils.trace('**************************************************','********************');
963 END IF;
964
965 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
966 RETURN;
967 END IF;
968 END IF;
969
970
971 l_value := hr_general.decode_lookup('IN_RESIDENTIAL_STATUS',p_resident_status);
972
973 IF l_value IS NULL THEN
974 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
975 p_token_name(1) := 'VALUE';
976 p_token_value(1) := p_resident_status;
977 p_token_name(2) := 'FIELD';
978 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','RESIDENTIAL_STATUS');
979
980 IF g_debug THEN
981 pay_in_utils.trace('**************************************************','********************');
982 pay_in_utils.trace('p_message_name ',p_message_name);
983 pay_in_utils.trace('**************************************************','********************');
987 RETURN;
984 END IF;
985
986 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
988
989 END IF ;
990
991 --
992 -- Validate PAN Number
993
994 check_pan_format( p_pan => p_pan
995 ,p_pan_af => p_pan_af
996 ,p_panref_number => p_panref_number
997 ,p_message_name => p_message_name
998 ,p_token_name => p_token_name
999 ,p_token_value => p_token_value
1000 );
1001
1002 IF p_message_name <> 'SUCCESS' then
1003
1004 IF g_debug THEN
1005 pay_in_utils.trace('**************************************************','********************');
1006 pay_in_utils.trace('p_message_name ',p_message_name);
1007 pay_in_utils.trace('**************************************************','********************');
1008 END IF;
1009
1010 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 110);
1011 RETURN;
1012 END IF;
1013
1014
1015 -- Check for the NSSN Format
1016 IF NOT length(p_NSSN) = 14 THEN
1017 p_message_name := 'PER_IN_INVALID_FORMAT';
1018 p_token_name(1) := 'FIELD';
1019 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN');
1020 p_token_name(2) := 'FORMAT';
1021 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN_FORMAT');
1022 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 115);
1023
1024 IF g_debug THEN
1025 pay_in_utils.trace('**************************************************','********************');
1026 pay_in_utils.trace('p_message_name ',p_message_name);
1027 pay_in_utils.trace('**************************************************','********************');
1028 END IF;
1029
1030 RETURN;
1031 END IF;
1032 IF instr(p_NSSN,'.') > 0 THEN
1033 p_message_name := 'PER_IN_INVALID_FORMAT';
1034 p_token_name(1) := 'FIELD';
1035 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN');
1036 p_token_name(2) := 'FORMAT';
1037 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','NSSN_FORMAT');
1038 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 115);
1039
1040 IF g_debug THEN
1041 pay_in_utils.trace('**************************************************','********************');
1042 pay_in_utils.trace('p_message_name ',p_message_name);
1043 pay_in_utils.trace('**************************************************','********************');
1044 END IF;
1045
1046 RETURN;
1047 END IF;
1048 --
1049 -- Check Uniqueness
1050 --
1051
1052
1053 IF p_pan IS NOT NULL THEN
1054
1055 check_unique_number
1056 (
1057 p_business_group_id => p_business_group_id
1058 ,p_person_id => p_person_id
1059 ,p_field => 'PAN'
1060 ,p_value => p_pan
1061 ,p_message_name => p_message_name
1062 ,p_token_name => p_token_name
1063 ,p_token_value => p_token_value
1064 );
1065
1066
1067
1068 IF p_message_name <> 'SUCCESS' then
1069
1070 IF g_debug THEN
1071 pay_in_utils.trace('**************************************************','********************');
1072 pay_in_utils.trace('p_message_name ',p_message_name);
1073 pay_in_utils.trace('**************************************************','********************');
1074 END IF;
1075
1076 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 120);
1077 RETURN;
1078 END IF;
1079
1080 END IF;
1081
1082 IF p_pf_number IS NOT NULL THEN
1083 check_unique_number
1084 (
1085 p_business_group_id => p_business_group_id
1086 ,p_person_id => p_person_id
1087 ,p_field => 'PF Number'
1088 ,p_value => p_pf_number
1089 ,p_message_name => p_message_name
1090 ,p_token_name => p_token_name
1091 ,p_token_value => p_token_value
1092 );
1093
1094 IF p_message_name <> 'SUCCESS' then
1095
1096 IF g_debug THEN
1097 pay_in_utils.trace('**************************************************','********************');
1098 pay_in_utils.trace('p_message_name ',p_message_name);
1099 pay_in_utils.trace('**************************************************','********************');
1100 END IF;
1101
1102 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
1103 RETURN;
1104 END IF;
1105
1106
1107 END IF;
1108
1109 IF p_esi_number IS NOT NULL THEN
1110 check_unique_number
1111 (
1112 p_business_group_id => p_business_group_id
1113 ,p_person_id => p_person_id
1114 ,p_field => 'ESI Number'
1115 ,p_value => p_esi_number
1116 ,p_message_name => p_message_name
1117 ,p_token_name => p_token_name
1118 ,p_token_value => p_token_value
1119 );
1120
1121 IF p_message_name <> 'SUCCESS' then
1122
1123 IF g_debug THEN
1124 pay_in_utils.trace('**************************************************','********************');
1128
1125 pay_in_utils.trace('p_message_name ',p_message_name);
1126 pay_in_utils.trace('**************************************************','********************');
1127 END IF;
1129 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 140);
1130 RETURN;
1131 END IF;
1132
1133 END IF;
1134
1135 IF p_sa_number IS NOT NULL THEN
1136
1137 check_unique_number
1138 (
1139 p_business_group_id => p_business_group_id
1140 ,p_person_id => p_person_id
1141 ,p_field => 'Super Annuation Number'
1142 ,p_value => p_sa_number
1143 ,p_message_name => p_message_name
1144 ,p_token_name => p_token_name
1145 ,p_token_value => p_token_value
1146 );
1147
1148 IF p_message_name <> 'SUCCESS' then
1149
1150 IF g_debug THEN
1151 pay_in_utils.trace('**************************************************','********************');
1152 pay_in_utils.trace('p_message_name ',p_message_name);
1153 pay_in_utils.trace('**************************************************','********************');
1154 END IF;
1155
1156 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
1157 RETURN;
1158 END IF;
1159
1160 END IF;
1161
1162 IF p_group_ins_number IS NOT NULL THEN
1163
1164 check_unique_number
1165 (
1166 p_business_group_id => p_business_group_id
1167 ,p_person_id => p_person_id
1168 ,p_field => 'Group Insurance Number'
1169 ,p_value => p_group_ins_number
1170 ,p_message_name => p_message_name
1171 ,p_token_name => p_token_name
1172 ,p_token_value => p_token_value
1173 );
1174
1175 IF p_message_name <> 'SUCCESS' then
1176
1177 IF g_debug THEN
1178 pay_in_utils.trace('**************************************************','********************');
1179 pay_in_utils.trace('p_message_name ',p_message_name);
1180 pay_in_utils.trace('**************************************************','********************');
1181 END IF;
1182
1183 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
1184 RETURN;
1185 END IF;
1186
1187 END IF;
1188
1189 IF p_pension_number IS NOT NULL THEN
1190
1191 check_unique_number
1192 (
1193 p_business_group_id => p_business_group_id
1194 ,p_person_id => p_person_id
1195 ,p_field => 'Pension Number'
1196 ,p_value => p_pension_number
1197 ,p_message_name => p_message_name
1198 ,p_token_name => p_token_name
1199 ,p_token_value => p_token_value
1200 );
1201
1202 IF p_message_name <> 'SUCCESS' then
1203
1204 IF g_debug THEN
1205 pay_in_utils.trace('**************************************************','********************');
1206 pay_in_utils.trace('p_message_name ',p_message_name);
1207 pay_in_utils.trace('**************************************************','********************');
1208 END IF;
1209
1210 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 170);
1211 RETURN;
1212 END IF;
1213 END IF;
1214
1215 IF p_gratuity_number IS NOT NULL THEN
1216 check_unique_number
1217 (
1218 p_business_group_id => p_business_group_id
1219 ,p_person_id => p_person_id
1220 ,p_field => 'Gratuity Number'
1221 ,p_value => p_gratuity_number
1222 ,p_message_name => p_message_name
1223 ,p_token_name => p_token_name
1224 ,p_token_value => p_token_value
1225 );
1226
1227 IF p_message_name <> 'SUCCESS' then
1228
1229 IF g_debug THEN
1230 pay_in_utils.trace('**************************************************','********************');
1231 pay_in_utils.trace('p_message_name ',p_message_name);
1232 pay_in_utils.trace('**************************************************','********************');
1233 END IF;
1234
1235 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 180);
1236 RETURN;
1237 END IF;
1238 END IF;
1239
1240 IF p_panref_number IS NOT NULL THEN
1241 check_unique_number
1242 (
1243 p_business_group_id => p_business_group_id
1244 ,p_person_id => p_person_id
1245 ,p_field => 'PAN Reference Number'
1246 ,p_value => p_panref_number
1247 ,p_message_name => p_message_name
1248 ,p_token_name => p_token_name
1249 ,p_token_value => p_token_value
1250 );
1251
1252 IF p_message_name <> 'SUCCESS' then
1253
1254 IF g_debug THEN
1255 pay_in_utils.trace('**************************************************','********************');
1256 pay_in_utils.trace('p_message_name ',p_message_name);
1257 pay_in_utils.trace('**************************************************','********************');
1258 END IF;
1259
1260 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 190);
1261 RETURN;
1262 END IF;
1263 END IF;
1267 check_unique_number
1264
1265 IF p_NSSN IS NOT NULL THEN
1266 --Check uniqueness for the PF National Social Security Number
1268 (
1269 p_business_group_id => p_business_group_id
1270 ,p_person_id => p_person_id
1271 ,p_field => 'NSSN'
1272 ,p_value => p_NSSN
1273 ,p_message_name => p_message_name
1274 ,p_token_name => p_token_name
1275 ,p_token_value => p_token_value
1276 );
1277
1278 IF p_message_name <> 'SUCCESS' then
1279
1280 IF g_debug THEN
1281 pay_in_utils.trace('**************************************************','********************');
1282 pay_in_utils.trace('p_message_name ',p_message_name);
1283 pay_in_utils.trace('**************************************************','********************');
1284 END IF;
1285
1286 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
1287 RETURN;
1288 END IF;
1289 END IF;
1290
1291 IF g_debug THEN
1292 pay_in_utils.trace('p_message_name ',p_message_name);
1293 END IF;
1294
1295 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 200);
1296
1297
1298 END check_in_person_int;
1299
1300
1301
1302 --------------------------------------------------------------------------
1303 -- --
1304 -- Name : CHECK_IN_PERSON_INSERT --
1305 -- Type : PROCEDURE --
1306 -- Access : Public --
1307 -- Description : Checks for the uniqueness of the PAN, PF Number, --
1308 -- ESI Number, Superannuation Number, Group Insurance --
1309 -- Number, Gratuity Number and Pension Fund Number in --
1310 -- the create_employee user hook. --
1311 -- --
1312 -- Parameters : --
1313 -- IN : p_per_information_category VARCHAR2 --
1314 -- : p_business_group_id NUMBER --
1315 -- : p_person_type_id NUMBER --
1316 -- : p_hire_date DATE --
1317 -- : p_per_information4 VARCHAR2 --
1318 -- : p_per_information5 VARCHAR2 --
1319 -- : p_per_information6 VARCHAR2 --
1320 -- : p_per_information7 VARCHAR2 --
1321 -- : p_per_information8 VARCHAR2 --
1322 -- : p_per_information9 VARCHAR2 --
1323 -- : p_per_information10 VARCHAR2 --
1324 -- : p_per_information11 VARCHAR2 --
1325 -- : p_per_information12 VARCHAR2 --
1326 -- : p_per_information13 VARCHAR2 --
1327 -- : p_per_information14 VARCHAR2 --
1328 -- : p_per_information15 VARCHAR2 --
1329 -- --
1330 -- OUT : N/A --
1331 -- RETURN : N/A --
1332 -- --
1333 -- Change History : --
1334 --------------------------------------------------------------------------
1335 -- Rev# Date Userid Description --
1336 --------------------------------------------------------------------------
1337 -- 1.0 16/05/05 sukukuma Created this procedure --
1338 -- 1.1 14/12/05 abhjain Added p_per_information14 --
1339 -- 1.2 10/07/07 sivanara Added parameter p_per_information15 for --
1340 -- NSSN(PF Monthly Retunrs). --
1341 --------------------------------------------------------------------------
1342
1343 PROCEDURE check_in_person_insert
1344 (
1345 p_per_information_category IN VARCHAR2
1346 ,p_business_group_id IN NUMBER
1347 ,p_person_type_id IN NUMBER
1348 ,p_hire_date IN DATE
1349 ,p_per_information4 IN VARCHAR2
1350 ,p_per_information5 IN VARCHAR2
1351 ,p_per_information6 IN VARCHAR2
1352 ,p_per_information7 IN VARCHAR2
1353 ,p_per_information8 IN VARCHAR2
1354 ,p_per_information9 IN VARCHAR2
1355 ,p_per_information10 IN VARCHAR2
1356 ,p_per_information11 IN VARCHAR2
1357 ,p_per_information12 IN VARCHAR2
1358 ,p_per_information13 IN VARCHAR2
1359 ,p_per_information14 IN VARCHAR2
1360 ,p_per_information15 IN VARCHAR2
1361 )
1362
1363 IS
1364 l_procedure VARCHAR2(100);
1365 l_message VARCHAR2(250);
1366 p_message_name VARCHAR2(100);
1367 p_person_id NUMBER := NULL;
1368
1369
1370 BEGIN
1371
1372 l_procedure := g_package||'check_in_person_insert';
1373 g_debug := hr_utility.debug_enabled;
1374 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1375
1376 IF g_debug THEN
1377 pay_in_utils.trace('**************************************************','********************');
1381 pay_in_utils.trace('p_hire_date ',p_hire_date );
1378 pay_in_utils.trace('p_per_information_category',p_per_information_category);
1379 pay_in_utils.trace('p_business_group_id ',p_business_group_id );
1380 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
1382 pay_in_utils.trace('p_per_information4 ',p_per_information4 );
1383 pay_in_utils.trace('p_per_information5 ',p_per_information5 );
1384 pay_in_utils.trace('p_per_information6 ',p_per_information6 );
1385 pay_in_utils.trace('p_per_information7 ',p_per_information7 );
1386 pay_in_utils.trace('p_per_information8 ',p_per_information8 );
1387 pay_in_utils.trace('p_per_information9 ',p_per_information9 );
1388 pay_in_utils.trace('p_per_information10 ',p_per_information10 );
1389 pay_in_utils.trace('p_per_information11 ',p_per_information11 );
1390 pay_in_utils.trace('p_per_information12 ',p_per_information12 );
1391 pay_in_utils.trace('p_per_information13 ',p_per_information13 );
1392 pay_in_utils.trace('p_per_information14 ',p_per_information14 );
1393 pay_in_utils.trace('p_per_information15 ',p_per_information15 );
1394 pay_in_utils.trace('**************************************************','********************');
1395 END IF;
1396
1397 p_message_name := 'SUCCESS';
1398 IF p_per_information_category <> 'IN' then
1399 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1400 RETURN;
1401 END IF;
1402
1403 check_in_person_int
1404 (p_business_group_id => p_business_group_id
1405 ,p_person_type_id => p_person_type_id
1406 ,p_person_id => p_person_id
1407 ,p_effective_date => p_hire_date
1408 ,p_pan => p_per_information4
1409 ,p_pan_af => p_per_information5
1410 ,p_military_status => p_per_information6
1411 ,p_resident_status => p_per_information7
1412 ,p_pf_number => p_per_information8
1413 ,p_esi_number => p_per_information9
1414 ,p_sa_number => p_per_information10
1415 ,p_group_ins_number => p_per_information11
1416 ,p_gratuity_number => p_per_information12
1417 ,p_pension_number => p_per_information13
1418 ,p_panref_number => p_per_information14
1419 ,p_NSSN => p_per_information15
1420 ,p_message_name => p_message_name
1421 ,p_token_name => p_token_name
1422 ,p_token_value => p_token_value
1423 );
1424
1425 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1426 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
1427 pay_in_utils.raise_message(800,p_message_name, p_token_name, p_token_value);
1428 ELSE
1429 pay_in_utils.raise_message(801,p_message_name, p_token_name, p_token_value);
1430 END IF;
1431
1432 END check_in_person_insert;
1433
1434 --------------------------------------------------------------------------
1435 -- --
1436 -- Name : CHECK_IN_PERSON_UPDATE --
1437 -- Type : PROCEDURE --
1438 -- Access : Public --
1439 -- Description : Checks for the uniqueness of the PAN, PF Number, --
1440 -- ESI Number, Superannuation Number, Group Insurance --
1441 -- Number, Gratuity Number and Pension Fund Number in --
1442 -- the create_employee user hook. --
1443 -- --
1444 -- Parameters : --
1445 -- IN : p_per_information_category VARCHAR2 --
1446 -- : p_person_id NUMBER --
1447 -- : p_effective_date DATE --
1448 -- : p_per_information4 VARCHAR2 --
1449 -- : p_per_information5 VARCHAR2 --
1450 -- : p_per_information6 VARCHAR2 --
1451 -- : p_per_information7 VARCHAR2 --
1452 -- : p_per_information8 VARCHAR2 --
1453 -- : p_per_information9 VARCHAR2 --
1454 -- : p_per_information10 VARCHAR2 --
1455 -- : p_per_information11 VARCHAR2 --
1456 -- : p_per_information12 VARCHAR2 --
1457 -- : p_per_information13 VARCHAR2 --
1458 -- : p_per_information14 VARCHAR2 --
1459 -- : p_per_information15 VARCHAR2 --
1460 -- --
1461 -- OUT : N/A --
1462 -- RETURN : N/A --
1463 -- --
1464 -- Change History : --
1465 --------------------------------------------------------------------------
1466 -- Rev# Date Userid Description --
1467 --------------------------------------------------------------------------
1468 -- 1.0 16/05/05 sukukuma Created this procedure --
1469 -- 1.1 14/12/05 abhjain Added p_per_information14 --
1473
1470 -- 1.2 10/07/07 sivanara Added parameter p_per_information15 for --
1471 -- NSSN(PF Monthly Retunrs). --
1472 --------------------------------------------------------------------------
1474 PROCEDURE check_in_person_update
1475 (
1476 p_per_information_category IN VARCHAR2
1477 ,p_person_type_id IN NUMBER
1478 ,p_person_id IN NUMBER
1479 ,p_effective_date IN DATE
1480 ,p_per_information4 IN VARCHAR2
1481 ,p_per_information5 IN VARCHAR2
1482 ,p_per_information6 IN VARCHAR2
1483 ,p_per_information7 IN VARCHAR2
1484 ,p_per_information8 IN VARCHAR2
1485 ,p_per_information9 IN VARCHAR2
1486 ,p_per_information10 IN VARCHAR2
1487 ,p_per_information11 IN VARCHAR2
1488 ,p_per_information12 IN VARCHAR2
1489 ,p_per_information13 IN VARCHAR2
1490 ,p_per_information14 IN VARCHAR2
1491 ,p_per_information15 IN VARCHAR2
1492 )
1493
1494
1495 IS
1496 l_procedure VARCHAR2(100);
1497 p_message_name VARCHAR2(100);
1498 l_business_group_id per_all_people_f.business_group_id%TYPE;
1499 l_message VARCHAR2(250);
1500
1501 CURSOR csr_person IS
1502 SELECT per_information4
1503 ,per_information5
1504 ,per_information6
1505 ,per_information7
1506 ,per_information8
1507 ,per_information9
1508 ,per_information10
1509 ,per_information11
1510 ,per_information12
1511 ,per_information13
1512 ,per_information14
1513 ,per_information15
1514 ,business_group_id
1515 FROM per_people_f
1516 WHERE p_effective_date BETWEEN effective_start_date
1517 AND effective_end_date
1518 AND person_id = p_person_id;
1519
1520 l_per_information4 per_all_people_f.per_information4%TYPE;
1521 l_per_information5 per_all_people_f.per_information5%TYPE;
1522 l_per_information6 per_all_people_f.per_information6%TYPE;
1523 l_per_information7 per_all_people_f.per_information7%TYPE;
1524 l_per_information8 per_all_people_f.per_information8%TYPE;
1525 l_per_information9 per_all_people_f.per_information9%TYPE;
1526 l_per_information10 per_all_people_f.per_information10%TYPE;
1527 l_per_information11 per_all_people_f.per_information11%TYPE;
1528 l_per_information12 per_all_people_f.per_information12%TYPE;
1529 l_per_information13 per_all_people_f.per_information13%TYPE;
1530 l_per_information14 per_all_people_f.per_information14%TYPE;
1531 l_per_information15 per_all_people_f.per_information15%TYPE;
1532
1533 BEGIN
1534
1535 l_procedure := g_package||'check_in_person_update';
1536 g_debug := hr_utility.debug_enabled;
1537 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1538
1539 IF g_debug THEN
1540 pay_in_utils.trace('**************************************************','********************');
1541 pay_in_utils.trace('p_per_information_category',p_per_information_category);
1542 pay_in_utils.trace('p_person_type_id ',p_person_type_id );
1543 pay_in_utils.trace('p_person_id ',p_person_id );
1544 pay_in_utils.trace('p_effective_date ',p_effective_date );
1545 pay_in_utils.trace('p_per_information4 ',p_per_information4 );
1546 pay_in_utils.trace('p_per_information5 ',p_per_information5 );
1547 pay_in_utils.trace('p_per_information6 ',p_per_information6 );
1548 pay_in_utils.trace('p_per_information7 ',p_per_information7 );
1549 pay_in_utils.trace('p_per_information8 ',p_per_information8 );
1550 pay_in_utils.trace('p_per_information9 ',p_per_information9 );
1551 pay_in_utils.trace('p_per_information10 ',p_per_information10 );
1552 pay_in_utils.trace('p_per_information11 ',p_per_information11 );
1553 pay_in_utils.trace('p_per_information12 ',p_per_information12 );
1554 pay_in_utils.trace('p_per_information13 ',p_per_information13 );
1555 pay_in_utils.trace('p_per_information14 ',p_per_information14 );
1556 pay_in_utils.trace('p_per_information15 ',p_per_information15 );
1557 pay_in_utils.trace('**************************************************','********************');
1558 END IF;
1559
1560 p_message_name := 'SUCCESS';
1561
1562 IF p_per_information_category <> 'IN' then
1563 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1564 RETURN;
1565 END IF;
1566
1567 OPEN csr_person;
1568 FETCH csr_person
1569 INTO l_per_information4
1570 ,l_per_information5
1571 ,l_per_information6
1572 ,l_per_information7
1573 ,l_per_information8
1574 ,l_per_information9
1575 ,l_per_information10
1576 ,l_per_information11
1577 ,l_per_information12
1578 ,l_per_information13
1579 ,l_per_information14
1580 ,l_per_information15
1581 ,l_business_group_id;
1582 CLOSE csr_person;
1583
1584 IF NVL (p_per_information4,'X') <> hr_api.g_varchar2 THEN
1585 l_per_information4 := p_per_information4;
1586 END IF;
1587
1588 IF NVL(p_per_information5,'X') <> hr_api.g_varchar2 THEN
1589 l_per_information5 := p_per_information5;
1590 END IF;
1591
1592 IF p_per_information6 <> hr_api.g_varchar2 THEN
1593 l_per_information6 := p_per_information6;
1594 END IF;
1595
1596 IF p_per_information7 <> hr_api.g_varchar2 THEN
1597 l_per_information7 := p_per_information7;
1598 END IF;
1599
1600 IF p_per_information8 <> hr_api.g_varchar2 THEN
1601 l_per_information8 := p_per_information8;
1602 END IF;
1603
1604 IF p_per_information9 <> hr_api.g_varchar2 THEN
1605 l_per_information9 := p_per_information9;
1606 END IF;
1607
1608 IF p_per_information10 <> hr_api.g_varchar2 THEN
1609 l_per_information10 := p_per_information10;
1610 END IF;
1611
1612 IF p_per_information11 <> hr_api.g_varchar2 THEN
1613 l_per_information11 := p_per_information11;
1614 END IF;
1615
1616 IF p_per_information12 <> hr_api.g_varchar2 THEN
1617 l_per_information12 := p_per_information12;
1618 END IF;
1619
1620 IF p_per_information13 <> hr_api.g_varchar2 THEN
1621 l_per_information13 := p_per_information13;
1622 END IF;
1623
1624 IF NVL(p_per_information14,'X') <> hr_api.g_varchar2 THEN
1625 l_per_information14 := p_per_information14;
1626 END IF;
1627
1628 IF NVL(p_per_information15,'X') <> hr_api.g_varchar2 THEN
1629 l_per_information15 := p_per_information15;
1630 END IF;
1631
1632 check_in_person_int
1633 (p_business_group_id => l_business_group_id
1634 ,p_person_type_id => p_person_type_id
1635 ,p_person_id => p_person_id
1636 ,p_effective_date => p_effective_date
1637 ,p_pan => l_per_information4
1638 ,p_pan_af => l_per_information5
1639 ,p_military_status => l_per_information6
1640 ,p_resident_status => l_per_information7
1641 ,p_pf_number => l_per_information8
1642 ,p_esi_number => l_per_information9
1643 ,p_sa_number => l_per_information10
1644 ,p_group_ins_number => l_per_information11
1645 ,p_gratuity_number => l_per_information12
1646 ,p_pension_number => l_per_information13
1647 ,p_panref_number => l_per_information14
1648 ,p_NSSN => l_per_information15
1649 ,p_message_name => p_message_name
1650 ,p_token_name => p_token_name
1651 ,p_token_value => p_token_value
1652 );
1653
1654 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1655 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
1656 pay_in_utils.raise_message(800,p_message_name, p_token_name, p_token_value);
1657 ELSE
1658 pay_in_utils.raise_message(801,p_message_name, p_token_name, p_token_value);
1659 END IF;
1660
1661 END check_in_person_update;
1662
1663
1664 END per_in_person_leg_hook;