DBA Data[Home] [Help]

PACKAGE BODY: APPS.PER_IN_CON_LEG_HOOK

Source


1 PACKAGE BODY per_in_con_leg_hook AS
2 /* $Header: peinlhco.pkb 120.6.12010000.2 2008/08/06 09:13:39 ubhat ship $ */
3 
4    g_package      CONSTANT VARCHAR2(100) := 'per_in_con_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    p_message_name VARCHAR2(50);
9 
10 
11 
12 -- -----------------------------------------------------------------------+
13 -- Name           : nominee_age_check                                   --+
14 -- Type           : Procedure                                           --+
15 -- Access         : Public                                              --+
16 -- Description    : This procedure does the age validation i.e          --+
17 --                  checks if the guardian details are entered if the   --+
18 --                  nominee's age is below 18                           --+
19 -- Parameters     :                                                     --+
20 --             IN : p_contact_relationship_id   NUMBER                  --+
21 --                                                                      --+
22 -- Change History :                                                     --+
23 --------------------------------------------------------------------------+
24 -- Rev#  Date           Userid           Description                    --+
25 --------------------------------------------------------------------------+
26 -- 1.1   16-May-2005    sukukuma        Created this procedure          --+
27 --------------------------------------------------------------------------+
28 -- vbanner, commenting out for bug 4674384.
29 -- sukukuma,uncommented out for bug 4674384
30   PROCEDURE nominee_age_check
31    ( p_contact_relationship_id  IN
32    PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
33    BEGIN
34    NULL ;
35    END nominee_age_check;
36 
37 -- -----------------------------------------------------------------------+
38 -- Name           : nomination_share_insert_check                       --+
39 -- Type           : Procedure                                           --+
40 -- Access         : Public                                              --+
41 -- Description    : This procedure checks if the sum of nomination share--+
42 --                  for a particular benifit of employee is under 100   --+
43 --                  or not.                                             --+
44 -- Parameters     :                                                     --+
45 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
46 --                  p_CEI_INFORMATION3         NUMBER                   --+
47 --                  p_effective_date           DATE                     --+
48 --                  p_contact_relationship_id  NUMBER                   --+
49 --            OUT : N/A                                                 --+
50 --         RETURN : N/A                                                 --+
51 --                                                                      --+
52 --                                                                      --+
53 --                                                                      --+
54 -- Change History :                                                     --+
55 --------------------------------------------------------------------------+
56 -- Rev#  Date           Userid           Description                    --+
57 --------------------------------------------------------------------------+
58 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
59 -- 1.1   16-May-2005    sukukuma        Updated this procedure          --+
60 --------------------------------------------------------------------------+
61 
62 PROCEDURE nomination_share_insert_check
63         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
64         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
65         ,p_effective_date          IN DATE
66         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
67         )IS
68 
69 BEGIN
70 NULL ;
71 END  nomination_share_insert_check ;
72 
73 
74 -- -----------------------------------------------------------------------+
75 -- Name           : nomination_share_update_check                       --+
76 -- Type           : Procedure                                           --+
77 -- Access         : Public                                              --+
78 -- Description    : This procedure checks if the sum of nomination share--+
79 --                  for a particular benifit of employee is under 100   --+
80 --                  or not.                                             --+
81 -- Parameters     :                                                     --+
82 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
83 --                  p_CEI_INFORMATION3         NUMBER                   --+
84 --                  p_effective_date           DATE                     --+
85 --                  p_contact_relationship_id  NUMBER                   --+
86 --                  p_contact_extra_info_id    NUMBER                   --+
87 --            OUT : N/A                                                 --+
88 --         RETURN : N/A                                                 --+
89 --                                                                      --+
90 --                                                                      --+
91 --                                                                      --+
92 -- Change History :                                                     --+
93 --------------------------------------------------------------------------+
94 -- Rev#  Date           Userid           Description                    --+
95 --------------------------------------------------------------------------+
96 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
97 -- 1.1   16-May-2005    sukukuma        Updated this procedure          --+
98 --------------------------------------------------------------------------+
99 
100 PROCEDURE nomination_share_update_check
101         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
102         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
103         ,p_effective_date          IN DATE
104         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
105         ,p_contact_extra_info_id    IN  PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE)IS
106 BEGIN
107 NULL ;
108 END nomination_share_update_check;
109 
110 
111 
112 -- -----------------------------------------------------------------------+
113 -- Name           : check_nominee_age                                --+
114 -- Type           : Procedure                                           --+
115 -- Access         : Public                                              --+
116 -- Description    : This procedure does the age validation i.e          --+
117 --                  checks if the guardian details are entered if the   --+
118 --                  nominee's age is below 18                           --+
119 -- Parameters     :                                                     --+
120 --             IN : p_contact_relationship_id   NUMBER                  --+
121 --                  p_message_name              VARCHAR2                --+
122 --                  p_token_name                VARCHAR2                --+
123 --                  p_toen_value                VARCHAR2                --+
124 --            OUT : 3                                                   --+
125 --         RETURN : N/A                                                 --+
126 -- Change History :                                                     --+
127 --------------------------------------------------------------------------+
128 -- Rev#  Date           Userid           Description                    --+
129 --------------------------------------------------------------------------+
130 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
131 -- 1.1   31-Mar-2004    gaugupta        Bug 3590036 fixed.              --+
132 -- 1.2   16-May-2005    sukukuma        updated this procedure          --+
133 -- 1.3   05-APR-2008    mdubasi         Bug 6871352 fixed.              --+
134 --------------------------------------------------------------------------+
135 --sukukuma, changed the name of this procedure from nominee_age_check
136 -- to check_nominee_age
137 --mdubasi, added Exception block to handle NO_DATA_FOUND Exception
138 
139 PROCEDURE check_nominee_age
140 ( p_contact_relationship_id  IN  PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
141  ,p_message_name             OUT NOCOPY VARCHAR2
142  ,p_token_name               OUT NOCOPY pay_in_utils.char_tab_type
143  ,p_token_value              OUT NOCOPY pay_in_utils.char_tab_type)
144 
145 IS
146   l_birth_date           DATE;
147   l_current_date         DATE;
148   l_year                 NUMBER;
149   l_month                NUMBER;
150   l_day                  NUMBER;
151   l_contact_person_id    NUMBER;
152   l_legislation_code     VARCHAR2(10);
153   l_guardian_detail_flag NUMBER;
154   l_procedure            VARCHAR2(100);
155   l_message              VARCHAR2(255);
156 
157 
158   CURSOR c_business_group_check
159          (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
160 
161          SELECT pbg.legislation_code
162            FROM per_business_groups pbg,
163                 per_contact_relationships pcr
164           WHERE pcr.contact_relationship_id = p_contact_relationship_id
165             AND pcr.business_group_id = pbg.business_group_id;
166 
167   CURSOR c_get_birth_date(l_contact_person_id IN NUMBER) IS
168 
169           SELECT DATE_OF_BIRTH from per_all_people_f
170            WHERE PERSON_ID = l_contact_person_id ;
171 
172   CURSOR c_get_contact_person_id IS
173 
174          SELECT contact_person_id from PER_CONTACT_RELATIONSHIPS
175           WHERE contact_relationship_id = p_contact_relationship_id;
176 
177   CURSOR c_check_guardian_details IS
178          SELECT 1 from per_contact_relationships
179           WHERE contact_relationship_id = p_contact_relationship_id
180             AND cont_information13 is not null
181             AND cont_information14 is not null
182             AND cont_information15 is not null
183             AND cont_information17 is not null;
184 BEGIN
185 
186    g_debug := hr_utility.debug_enabled ;
187    l_procedure := g_package ||'check_nominee_age';
188    pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
189 
190    IF g_debug THEN
191        pay_in_utils.trace('**************************************************','********************');
192        pay_in_utils.trace('p_contact_relationship_id',p_contact_relationship_id);
193        pay_in_utils.trace('**************************************************','********************');
194    END IF;
195 
196   IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
197     hr_utility.trace ('IN Legislation not installed. Not performing the validations');
198     pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
199     RETURN;
200   END IF;
201 
202   OPEN c_business_group_check(p_contact_relationship_id);
203   FETCH c_business_group_check into l_legislation_code;
204   CLOSE c_business_group_check;
205 
206   IF l_legislation_code = 'IN' THEN
207 
208     pay_in_utils.set_location(g_debug,l_procedure,30);
209 
210     OPEN c_get_contact_person_id;
211     FETCH c_get_contact_person_id into l_contact_person_id;
212     CLOSE c_get_contact_person_id;
213 
214     IF l_contact_person_id is not NULL THEN
215       --Get contact person Id--
216       pay_in_utils.set_location(g_debug,l_procedure,40);
217 
218       OPEN c_get_birth_date(l_contact_person_id);
219       FETCH c_get_birth_date into l_birth_date;
220       CLOSE c_get_birth_date;
221 
222       IF  l_birth_date is not null THEN
223      ---Get the age of nominee--
224       pay_in_utils.set_location(g_debug,l_procedure,50);
225 
226 	BEGIN
227         SELECT effective_date into l_current_date from fnd_sessions
228          WHERE session_id = userenv('SESSIONID');
229 	EXCEPTION
230 	WHEN NO_DATA_FOUND THEN
231          l_current_date := trunc(sysdate);
232 	END;
233 
234         l_year  := to_number(to_char(l_current_date , 'yyyy')) - to_number(to_char(l_birth_date , 'yyyy'));
235         l_month := to_number(to_char(l_current_date , 'mm')) - to_number(to_char(l_birth_date , 'mm'));
236         l_day   := to_number(to_char(l_current_date , 'dd')) - to_number(to_char(l_birth_date , 'dd'));
237 
238         IF (l_year < 18 or ((l_year = 18) and (l_month < 0)) or ((l_year = 18) and (l_month = 0)  and (l_day < 0))) THEN
239            l_guardian_detail_flag := 0;
240           --check the age of nominee--
241            pay_in_utils.set_location(g_debug,l_procedure,60);
242 
243            OPEN c_check_guardian_details;
244            FETCH c_check_guardian_details into l_guardian_detail_flag;
245            CLOSE c_check_guardian_details;
246 
247            IF l_guardian_detail_flag = 0 THEN
248            ---Gardian Details---
249             pay_in_utils.set_location(g_debug,l_procedure,70);
250 
251              hr_utility.set_message(800,'PER_IN_GUARDIAN_DETAILS');
252 	     hr_utility.raise_error;
253              IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
254                 fnd_log.message(LOG_LEVEL   =>FND_LOG.LEVEL_EXCEPTION
255                                ,MODULE      =>'per.plsql.hr_in_contact_extra_info_api.create_in_contact_extra_info'
256                                ,POP_MESSAGE => FALSE);
257               END IF;
258            END IF;
259         END IF;
260       ELSE
261 
262       pay_in_utils.set_location(g_debug,l_procedure,80);
263 
264         hr_utility.set_message(800,'PER_IN_GUARDIAN_DETAILS');
265 
266         IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
267           fnd_log.message(LOG_LEVEL   =>FND_LOG.LEVEL_EXCEPTION
268                          ,MODULE      =>'per.plsql.hr_in_contact_extra_info_api.create_in_contact_extra_info'
269                          ,POP_MESSAGE => FALSE);
270         END IF;
271       END IF;
272     ELSE
273 
274            pay_in_utils.set_location(g_debug,l_procedure,90);
275            p_message_name  := 'PER_IN_NO_RELATIONSHIP';
276            pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,100);
277             RETURN;
278     END IF;
279   END IF;
280  pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,110);
281 
282  END check_nominee_age;
283 
284 
285 
286 
287 -- -----------------------------------------------------------------------+
288 -- Name           : get_essential_insert_value                          --+
289 -- Type           : Procedure                                           --+
290 -- Access         : Private                                             --+
291 -- Description    : This procedure checks if the sum of nomination share--+
292 --                  for a particular benifit of employee is under 100   --+
293 --                  or not.                                             --+
294 -- Parameters     :                                                     --+
295 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
296 --                : p_CEI_INFORMATION3         NUMBER                   --+
297 --                : p_effective_date           DATE                     --+
298 --                : p_contact_relationship_id  NUMBER                   --+
299 --                  p_message_name             VARCHAR2                 --+
300 --                  p_token_name               VARCHAR2                 --+
301 --                  p_toen_value               VARCHAR2                 --+
302 --            OUT : 3                                                   --+
303 --         RETURN : N/A                                                 --+
304 --                                                                      --+
305 -- Change History :                                                     --+
306 --------------------------------------------------------------------------+
307 -- Rev#  Date           Userid           Description                    --+
308 --------------------------------------------------------------------------+
309 -- 1.0   16-May-2005    sukukuma        Created this procedure          --+
310 -- 1.1   12-Jan-2006    rpalli          Bug:4895307 - Added the check   --+
311 --                                      for nomination share less than  --+
312 --                                      100                             --+
313 --------------------------------------------------------------------------+0
314 
315 PROCEDURE get_essential_insert_value
316         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
317         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
318         ,p_effective_date          IN DATE
319         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
320         ,p_message_name            OUT NOCOPY VARCHAR2
321         ,p_token_name              OUT NOCOPY pay_in_utils.char_tab_type
322         ,p_token_value             OUT NOCOPY pay_in_utils.char_tab_type)
323 IS
324 l_nomination_share            NUMBER;
325 l_person_id                   NUMBER;
326 l_contact_relationship_id     NUMBER;
327 l_temp                        NUMBER;
328 l_legislation_code            VARCHAR2(10);
329 l_procedure                   VARCHAR2(200);
330 l_message                     VARCHAR2(255);
331 
332 
333 
334   CURSOR c_business_group_check
335          (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
336          SELECT pbg.legislation_code
337          FROM   per_business_groups pbg,
338                 per_contact_relationships pcr
339           WHERE pcr.contact_relationship_id = p_contact_relationship_id
340           AND  pcr.business_group_id = pbg.business_group_id;
341 
342 
343   CURSOR c_get_relationship_id(l_person_id IN NUMBER) IS
344          SELECT contact_relationship_id from PER_CONTACT_RELATIONSHIPS
345          WHERE person_id = l_person_id
346          AND   contact_relationship_id <> p_contact_relationship_id;
347 
348   CURSOR c_get_person_id IS
349           SELECT person_id from PER_CONTACT_RELATIONSHIPS
350           WHERE  contact_relationship_id = p_contact_relationship_id;
351 
352   CURSOR c_check_benefit IS
353          SELECT 1 from PER_CONTACT_EXTRA_INFO_F
354          WHERE contact_relationship_id = p_contact_relationship_id
355          AND   CEI_INFORMATION3 = p_CEI_INFORMATION3
356          AND   p_effective_date between effective_start_date and effective_end_date;
357 
358 BEGIN
359 
360   g_debug := hr_utility.debug_enabled ;
361   l_procedure := g_package ||'get_essential_insert_value';
362   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
363 
364   IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
365     hr_utility.trace ('IN Legislation not installed. Not performing the validations');
366     pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
367     RETURN;
368   END IF;
369 
370   OPEN c_business_group_check(p_contact_relationship_id);
371   FETCH c_business_group_check into l_legislation_code;
372   CLOSE c_business_group_check;
373 
374   pay_in_utils.set_location(g_debug,l_procedure,20);
375 
376     IF l_legislation_code  = 'IN' THEN
377         l_nomination_share := 0;
378         l_temp := 0;
379 
380     BEGIN
381         OPEN  c_check_benefit;
382         FETCH c_check_benefit into l_temp;
383         CLOSE c_check_benefit;
384         EXCEPTION
385         WHEN NO_DATA_FOUND THEN
386         NULL ;
387     END;
388 
389     IF l_temp = 0 THEN
390     --get person id--
391      pay_in_utils.set_location(g_debug,l_procedure,30);
392 
393          OPEN c_get_person_id;
394          FETCH c_get_person_id into l_person_id;
395          CLOSE c_get_person_id;
396 
397         OPEN c_get_relationship_id(l_person_id);
398         LOOP
399         FETCH c_get_relationship_id into l_contact_relationship_id;
400         EXIT WHEN c_get_relationship_id%NOTFOUND;
401         l_nomination_share := l_nomination_share + get_nomination_share(l_contact_relationship_id ,
402                                                                              p_CEI_INFORMATION3,
403                                                                              p_effective_date);
404         END LOOP;
405        CLOSE c_get_relationship_id;
406 
407        IF g_debug then
408          pay_in_utils.trace('nomination share after',l_nomination_share + to_number(p_CEI_INFORMATION2));
409        END IF;
410 
411        IF l_nomination_share + to_number(p_CEI_INFORMATION2) > 100 THEN
412        ---check for percentage--
413 	    pay_in_utils.trace('Check the value of nomination share','40');
414             p_message_name  := 'PER_IN_NOM_SHARE_MORE';
415             RETURN ;
416         ELSIF l_nomination_share + to_number(p_CEI_INFORMATION2) < 100 THEN
417        --check for percentage--
418           pay_in_utils.trace('Check the value of nomination share less','50');
419           p_message_name  := 'PER_IN_NOM_SHARE_LESS';
420             RETURN ;
421         END IF;
422        ELSE
423 
424          pay_in_utils.set_location(g_debug,l_procedure,60);
425           p_message_name   := 'PER_IN_CONT_ALREADY_NOMINATED';
426           RETURN;
427       END IF;
428 
429   END IF;
430 
431   pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
432 
433   EXCEPTION
434   WHEN NO_DATA_FOUND    THEN
435   NULL;
436 
437 
438 END get_essential_insert_value;
439 
440 
441 
442 
443 -- -----------------------------------------------------------------------+
444 -- Name           : get_essential_update_value                          --+
445 -- Type           : Procedure                                           --+
446 -- Access         : Private                                             --+
447 -- Description    : This procedure checks if the sum of nomination share--+
448 --                  for a particular benifit of employee is under 100   --+
449 --                  or not.                                             --+
450 -- Parameters     :                                                     --+
451 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
452 --                  p_CEI_INFORMATION3         NUMBER                   --+
453 --                  p_effective_date           DATE                     --+
454 --                  p_contact_relationship_id  NUMBER                   --+
455 --                  p_contact_extra_info_id    NUMBER                   --+
456 --                  p_message_name             VARCHAR2                 --+
457 --                  p_token_name               VARCHAR2                 --+
458 --                  p_toen_value               VARCHAR2                 --+
459 --            OUT : 3                                                   --+
460 --         RETURN : N/A                                                 --+
461 --                                                                      --+
462 --                                                                      --+
463 -- Change History :                                                     --+
464 --------------------------------------------------------------------------+
465 -- Rev#  Date           Userid           Description                    --+
466 --------------------------------------------------------------------------+
467 -- 1.0   16-May-2004    sukukuma      Created this procedure            --+
468 -- 1.1   12-Jan-2006    rpalli          Bug:4895307 - Added the check   --+
469 --                                      for nomination share less than  --+
470 --                                      100                             --+
471 --------------------------------------------------------------------------+0
472 
473 PROCEDURE get_essential_update_value
474         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
475         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
476         ,p_effective_date          IN DATE
477         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
478         ,p_contact_extra_info_id   IN  PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE
479         ,p_message_name            OUT NOCOPY VARCHAR2
480         ,p_token_name              OUT NOCOPY pay_in_utils.char_tab_type
481         ,p_token_value             OUT NOCOPY pay_in_utils.char_tab_type)
482 IS
483   l_nomination_share            NUMBER;
484   l_person_id                   NUMBER;
485   l_contact_relationship_id     NUMBER;
486   l_temp                        NUMBER;
487   l_legislation_code            VARCHAR2(10);
488   l_procedure                   VARCHAR2(200);
489   l_message                     VARCHAR2(255);
490 
491   CURSOR  c_check_benefit IS
492          SELECT 1 from PER_CONTACT_EXTRA_INFO_F
493          WHERE  contact_relationship_id = p_contact_relationship_id
494          AND    contact_extra_info_id <> p_contact_extra_info_id
495          AND    CEI_INFORMATION3 = p_CEI_INFORMATION3
496          AND    p_effective_date between effective_start_date and effective_end_date;
497 
498   CURSOR c_business_group_check
499          (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
500          SELECT pbg.legislation_code
501          FROM   per_business_groups pbg,
502                 per_contact_relationships pcr
503          WHERE  pcr.contact_relationship_id = p_contact_relationship_id
504          AND    pcr.business_group_id = pbg.business_group_id;
505 
506 
507   CURSOR c_get_relationship_id(l_person_id IN NUMBER) IS
508          SELECT contact_relationship_id from PER_CONTACT_RELATIONSHIPS
509          WHERE person_id = l_person_id
510          AND contact_relationship_id <> p_contact_relationship_id;
511 
512   CURSOR c_get_person_id IS
513          SELECT person_id from PER_CONTACT_RELATIONSHIPS
514          WHERE contact_relationship_id = p_contact_relationship_id;
515 
516 
517 BEGIN
518 
519   g_debug := hr_utility.debug_enabled ;
520   l_procedure := g_package ||'get_essential_update_value';
521   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
522 
523   IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
524     hr_utility.trace ('IN Legislation not installed. Not performing the validations');
525     pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
526     RETURN;
527   END IF;
528 
529   OPEN c_business_group_check(p_contact_relationship_id);
530   FETCH c_business_group_check into l_legislation_code;
531   CLOSE c_business_group_check;
532 
533    IF l_legislation_code = 'IN' THEN
534 
535    pay_in_utils.set_location(g_debug,l_procedure,30);
536 
537          l_nomination_share := 0;
538          l_temp := 0;
539       BEGIN
540          OPEN  c_check_benefit;
541          FETCH c_check_benefit into l_temp;
542          CLOSE c_check_benefit;
543 
544          EXCEPTION
545          WHEN NO_DATA_FOUND THEN
546          NULL ;
547       END;
548 
549   IF l_temp = 0 THEN
550   --Get the person Id--
551   pay_in_utils.set_location(g_debug,l_procedure,40);
552 
553 
554          OPEN  c_get_person_id;
555          FETCH c_get_person_id into l_person_id;
556          CLOSE c_get_person_id;
557 
558          OPEN c_get_relationship_id(l_person_id);
559            LOOP
560             FETCH c_get_relationship_id into l_contact_relationship_id;
561             EXIT WHEN c_get_relationship_id%NOTFOUND;
562             l_nomination_share := l_nomination_share + get_nomination_share(l_contact_relationship_id ,
563                                                                         p_CEI_INFORMATION3,
564                                                                         p_effective_date);
565            END LOOP;
566         CLOSE c_get_relationship_id;
567 
568 	 IF g_debug then
569            pay_in_utils.trace('nomination share after',l_nomination_share + to_number(p_CEI_INFORMATION2));
570          END IF;
571         IF l_nomination_share + to_number(p_CEI_INFORMATION2) > 100 THEN
572        --check for percentage--
573 	  pay_in_utils.trace('Check the value of nomination share','50');
574           p_message_name  := 'PER_IN_NOM_SHARE_MORE';
575           RETURN ;
576         ELSIF l_nomination_share + to_number(p_CEI_INFORMATION2) < 100 THEN
577        --check for percentage--
578 	  pay_in_utils.trace('Check the value of nomination share less','60');
579           p_message_name  := 'PER_IN_NOM_SHARE_LESS';
580           RETURN ;
581         END IF;
582 
583    ELSE
584      ----check for status of the nominee------
585      pay_in_utils.set_location(g_debug,l_procedure,70);
586      p_message_name   := 'PER_IN_CONT_ALREADY_NOMINATED';
587      RETURN ;
588 
589   END IF;
590 
591 END IF;
592 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
593 
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 NULL ;
597 
598 END get_essential_update_value;
599 
600 
601 
602 
603 -- -----------------------------------------------------------------------+
604 -- Name           : check_in_con_int                                    --+
605 -- Type           : Procedure                                           --+
606 -- Access         : Public                                              --+
607 -- Description    : Internal procedure which calls the appropriate      --+
608 --                : procedures for Insert and Upadte                    --+
609 -- Parameters     :                                                     --+
610 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
611 --                  p_CEI_INFORMATION3         NUMBER                   --+
612 --                  p_effective_date           DATE                     --+
613 --                  p_contact_relationship_id  NUMBER                   --+
614 --                  p_contact_extra_info_id    NUMBER                   --+
615 --                  p_message_name             VARCHAR2                 --+
616 --                  p_token_name               VARCHAR2                 --+
617 --                  p_toen_value               VARCHAR2                 --+
618 --            OUT : 3                                                   --+
619 --         RETURN : N/A                                                 --+
620 --                                                                      --+
621 --                                                                      --+
622 --                                                                      --+
623 -- Change History :                                                     --+
624 --------------------------------------------------------------------------+
625 -- Rev#  Date           Userid           Description                    --+
626 --------------------------------------------------------------------------+
627 -- 1.0   16-May-2005    sukukuma        Created this procedure          --+
628 --------------------------------------------------------------------------+0
629 PROCEDURE check_in_con_int
630         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
631         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
632         ,p_effective_date          IN DATE
633         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
634         ,p_contact_extra_info_id   IN  PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE
635         ,p_message_name            OUT NOCOPY VARCHAR2
636         ,p_token_name               OUT NOCOPY pay_in_utils.char_tab_type
637         ,p_token_value              OUT NOCOPY pay_in_utils.char_tab_type)
638 
639 IS
640  l_procedure VARCHAR2(100);
641  l_message VARCHAR2(255);
642 BEGIN
643      g_debug := hr_utility.debug_enabled;
644      l_procedure := g_package ||'check_in_con_int';
645      pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
646 
647      IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
648        hr_utility.trace ('IN Legislation not installed. Not performing the validations');
649        pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
650        RETURN;
651      END IF;
652 
653 IF p_contact_extra_info_id IS NULL THEN
654 --Insert---
655  pay_in_utils.set_location(g_debug,l_procedure,30);
656 
657  -------check age and guardian status-----------------
658 
659 check_nominee_age(p_contact_relationship_id =>p_contact_relationship_id
660                  ,p_message_name            =>p_message_name
661                  ,p_token_name              => p_token_name
662                  ,p_token_value             =>p_token_value);
663 
664     IF p_message_name <>'SUCCESS' THEN
665       pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
666       RETURN ;
667     END IF ;
668 
669 
670 get_essential_insert_value
671         (p_CEI_INFORMATION2        => p_CEI_INFORMATION2
672         ,p_CEI_INFORMATION3        => p_CEI_INFORMATION3
673         ,p_effective_date          => p_effective_date
674         ,p_contact_relationship_id => p_contact_relationship_id
675         ,p_message_name            =>p_message_name
676         ,p_token_name              => p_token_name
677         ,p_token_value             =>p_token_value);
678 
679     IF p_message_name <>'SUCCESS' THEN
680       pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
681       RETURN ;
682     END IF;
683 
684 
685 ELSE
686 --Update--
687 pay_in_utils.set_location(g_debug,l_procedure,60);
688 
689 get_essential_update_value
690         (p_CEI_INFORMATION2        => p_CEI_INFORMATION2
691         ,p_CEI_INFORMATION3        => p_CEI_INFORMATION3
692         ,p_effective_date          => p_effective_date
693         ,p_contact_relationship_id => p_contact_relationship_id
694         ,p_contact_extra_info_id   => p_contact_extra_info_id
695         ,p_message_name            => p_message_name
696         ,p_token_name              => p_token_name
697         ,p_token_value             => p_token_value);
698 
699     IF p_message_name <>'SUCCESS' THEN
700       pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
701       RETURN ;
702     END IF;
703 
704 END IF ;
705 
706    pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
707 
708 
709 
710 END  check_in_con_int;
711 
712 
713 
714 
715 
716 -- -----------------------------------------------------------------------+
717 -- Name           : check_in_con_insert                                 --+
718 -- Type           : Procedure                                           --+
719 -- Access         : Public                                              --+
720 -- Description    : This procedure checks if the sum of nomination share--+
721 --                  for a particular benifit of employee is under 100   --+
722 --                  or not.                                             --+
723 -- Parameters     :                                                     --+
724 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
725 --                  p_CEI_INFORMATION3         NUMBER                   --+
726 --                  p_effective_date           DATE                     --+
727 --                  p_contact_relationship_id  NUMBER                   --+
728 --            OUT : N/A                                                   --+
729 --         RETURN : N/A                                                 --+
730 --                                                                      --+
731 --                                                                      --+
732 --                                                                      --+
733 -- Change History :                                                     --+
734 --------------------------------------------------------------------------+
735 -- Rev#  Date           Userid           Description                    --+
736 --------------------------------------------------------------------------+
737 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
738 -- 1.1   16-May-2005    sukukuma        Updated this procedure          --+
739 -- 1.2   12-Jan-2006    rpalli          Updated the procedure to raise  --+
740 --                                      no error when nomination share  --+
741 --                                      less than 100. The message is   --+
742 --                                      handled in the library          --+
743 --------------------------------------------------------------------------+
744 
745 PROCEDURE check_in_con_insert
746         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
747         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
748         ,p_effective_date          IN DATE
749         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
750         )
751 IS
752 l_procedure VARCHAR2(100);
753 l_message   VARCHAR2(255);
754 
755 BEGIN
756 
757   g_debug := hr_utility.debug_enabled;
758   l_procedure := g_package ||'check_in_con_insert';
759   p_message_name := 'SUCCESS';
760   pay_in_utils.null_message(p_token_name, p_token_value);
761 
762   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
763 
764   check_in_con_int
765         (p_CEI_INFORMATION2        => p_CEI_INFORMATION2
766         ,p_CEI_INFORMATION3        => p_CEI_INFORMATION3
767         ,p_effective_date          => p_effective_date
768         ,p_contact_relationship_id => p_contact_relationship_id
769         ,p_contact_extra_info_id   => NULL
770         ,p_message_name            => p_message_name
771         ,p_token_name              => p_token_name
772         ,p_token_value             => p_token_value);
773 
774 
775   IF p_message_name in ('PER_IN_NOM_SHARE_LESS') THEN
776       pay_in_utils.trace('PER_IN_NOM_SHARE_LESS','20');
777   ELSIF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
778       pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
779   ELSE
780       pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
781   END IF;
782 
783  pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
784 
785 END  check_in_con_insert ;
786 
787 -- -----------------------------------------------------------------------+
788 -- Name           : check_in_con_update                                 --+
789 -- Type           : Procedure                                           --+
790 -- Access         : Public                                              --+
791 -- Description    : This procedure checks if the sum of nomination share--+
792 --                  for a particular benifit of employee is under 100   --+
793 --                  or not.                                             --+
794 -- Parameters     :                                                     --+
795 --             IN : p_CEI_INFORMATION2         NUMBER                   --+
796 --                  p_CEI_INFORMATION3         NUMBER                   --+
797 --                  p_effective_date           DATE                     --+
798 --                  p_contact_relationship_id  NUMBER                   --+
799 --                  p_contact_extra_info_id    NUMBER                   --+
800 --            OUT : N/A                                                 --+
801 --         RETURN : N/A                                                 --+
802 --                                                                      --+
803 --                                                                      --+
804 --                                                                      --+
805 -- Change History :                                                     --+
806 --------------------------------------------------------------------------+
807 -- Rev#  Date           Userid           Description                    --+
808 --------------------------------------------------------------------------+
809 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
810 -- 1.1   16-May-2005    sukukuma        Updated this procedure          --+
811 -- 1.2   12-Jan-2006    rpalli          Updated the procedure to raise  --+
812 --                                      no error when nomination share  --+
813 --                                      less than 100. The message is   --+
814 --                                      handled in the library          --+
815 --------------------------------------------------------------------------+
816 
817 PROCEDURE check_in_con_update
818         (p_CEI_INFORMATION2        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
819         ,p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
820         ,p_effective_date          IN DATE
821         ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
822         ,p_contact_extra_info_id    IN  PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE)
823 IS
824 
825 CURSOR c_nom_id IS
826       SELECT
827          CEI_INFORMATION2
828         ,CEI_INFORMATION3
829         ,contact_relationship_id
830         FROM  PER_CONTACT_EXTRA_INFO_F
831         WHERE contact_extra_info_id =p_contact_extra_info_id;
832 
833 
834     l_CEI_INFORMATION2           PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE;
835     l_CEI_INFORMATION3           PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE;
836     l_contact_relationship_id    PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE;
837     l_procedure                  VARCHAR2(100);
838     l_message                    VARCHAR2(255);
839 
840 BEGIN
841 
842 
843    g_debug := hr_utility.debug_enabled;
844    l_procedure := g_package ||'check_in_con_update';
845    p_message_name := 'SUCCESS';
846    pay_in_utils.null_message(p_token_name, p_token_value);
847 
848    pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
849 
850   OPEN  c_nom_id;
851   FETCH c_nom_id
852   INTO  l_CEI_INFORMATION2
853        ,l_CEI_INFORMATION3
854        ,l_contact_relationship_id;
855   CLOSE c_nom_id;
856 
857   pay_in_utils.set_location(g_debug,l_procedure,20);
858 
859    IF p_CEI_INFORMATION2 <> hr_api.g_varchar2 THEN
860       l_CEI_INFORMATION2 := p_CEI_INFORMATION2;
861    END IF;
862 
863    IF p_CEI_INFORMATION3 <> hr_api.g_varchar2 THEN
864        l_CEI_INFORMATION3 := p_CEI_INFORMATION3;
865    END IF;
866 
867 
868     check_in_con_int
869         (p_CEI_INFORMATION2        => l_CEI_INFORMATION2
870         ,p_CEI_INFORMATION3        => l_CEI_INFORMATION3
871         ,p_effective_date          => p_effective_date
872         ,p_contact_relationship_id => l_contact_relationship_id
873         ,p_contact_extra_info_id   => p_contact_extra_info_id
874         ,p_message_name            => p_message_name
875         ,p_token_name              => p_token_name
876         ,p_token_value             => p_token_value
877         );
878 
879   pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
880 
881   IF p_message_name in ('PER_IN_NOM_SHARE_LESS') THEN
882       hr_utility.trace('PER_IN_NOM_SHARE_LESS');
883       pay_in_utils.trace('PER_IN_NOM_SHARE_LESS','40');
884   ELSIF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
885       pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
886   ELSE
887       pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
888   END IF;
889 
890 END check_in_con_update ;
891 
892 -- -----------------------------------------------------------------------+
893 -- Name           : get_nomination_share                                --+
894 -- Type           : Function                                            --+
895 -- Access         : Public                                              --+
896 -- Description    : This function returns the nomination share for a    --+
897 --                  particular combination of contact relationship id   --+
898 --                  effecttive date and benefit type.                   --+
899 -- Parameters     :                                                     --+
900 --             IN : p_contact_relationship_id  NUMBER                   --+
901 --                  p_CEI_INFORMATION3         NUMBER                   --+
902 --                  p_effective_date           DATE                     --+
903 --            OUT : 3                                                   --+
904 --         RETURN : N/A                                                 --+
905 --                                                                      --+
906 --                                                                      --+
907 --                                                                      --+
908 -- Change History :                                                     --+
909 --------------------------------------------------------------------------+
910 -- Rev#  Date           Userid           Description                    --+
911 --------------------------------------------------------------------------+
912 -- 1.0   31-Mar-2004    gaugupta        Created this procedure          --+
913 -- 1.1   24-Jun-2004    vgsriniv        Modified the logic.(Bug:3683622)--+
914 --------------------------------------------------------------------------+
915 FUNCTION get_nomination_share(p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE,
916                               p_CEI_INFORMATION3        IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE,
917                               p_effective_date          IN DATE)
918 RETURN NUMBER IS
919 l_nomination_share NUMBER;
920 l_nom NUMBER;
921 l_procedure                  VARCHAR2(100);
922 l_message                    VARCHAR2(255);
923 
924 CURSOR c_nomination_share IS
925    SELECT CEI_INFORMATION2
926     FROM PER_CONTACT_EXTRA_INFO_F
927    WHERE contact_relationship_id = p_contact_relationship_id
928      AND CEI_INFORMATION3 = p_CEI_INFORMATION3
929      AND  effective_end_date >= p_effective_date;
930 
931 
932 BEGIN
933   g_debug := hr_utility.debug_enabled;
934   l_procedure := 'get_nomination_share';
935   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
936   l_nomination_share := 0;
937   l_nom := 0;
938 
939   OPEN c_nomination_share;
940   LOOP
941     FETCH c_nomination_share INTO l_nom;
942     pay_in_utils.trace('l_nom',l_nom);
943     EXIT WHEN c_nomination_share%NOTFOUND;
944     l_nomination_share := l_nomination_share + l_nom;
945   END LOOP;
946   CLOSE c_nomination_share;
947 
948   pay_in_utils.trace('l_nomination_share',l_nomination_share);
949   pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
950 
951   RETURN l_nomination_share;
952 
953   EXCEPTION
954     WHEN NO_DATA_FOUND  THEN
955       RETURN 0;
956 
957 
958 END get_nomination_share;
959 
960 END per_in_con_leg_hook;