1 Package body ben_ss_dtct_ptnl_ler as
2 /* $Header: beptnldt.pkb 115.2 2003/02/12 10:30:52 rpgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 --
6 -- Package Variable
7
8 g_package varchar2(33) := ' ben_ss_dtct_ptnl_ler.';
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< dtct_ptnl_ler >------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This is a process which can be used by selfservice web pages( By OAB, HR and
17 -- Others ) for finding out about the newly created life events. This procedure will return
18 -- the appropriate message based on the Potential Life event status Code. This does not
19 -- necessary means that this potential life event will definitely affect their benefits
20 -- beacause we don't know unless we run benmngle. The purpose of this procedure is to simply warn.
21 --
22 --
23 -- Prerequisites:
24 --
25 --
26 -- Post Success: This procedure will return messages which are based on the Potential life event
27 -- status code.
28 -- This procedure will return null if there is no message approprita for the situation.
29 --
30 --
31
32
33
34 procedure dtct_ptnl_ler
35 (p_person_id in number ,
36 p_business_group_id in number,
37 p_effective_date in date default trunc(sysdate),
38 p_message out nocopy varchar2 ) as
39
40
41 l_proc varchar2(72) := g_package||'dtct_ptnl_ler';
42 --
43 --Declare cursors and variables
44 --
45 cursor c_ptnl_lers is
46 select ptn.ptnl_ler_for_per_id,
47 ptn.ptnl_ler_for_per_stat_cd
48 from ben_ptnl_ler_for_per ptn
49 where ptn.person_id = p_person_id
50 and ptn.business_group_id = p_business_group_id
51 and ptn.lf_evt_ocrd_dt <= p_effective_date ;
52
53 v_ptnl_lers c_ptnl_lers%rowtype;
54 v_mnl_message varchar2(2000):= null;
55 v_ler_message varchar2(2000):= null;
56
57 Begin
58 hr_utility.set_location('Entering:'|| l_proc, 10);
59 --
60 open c_ptnl_lers ;
61 fetch c_ptnl_lers into v_ptnl_lers;
62 -- if there are no potential life events return null .
63 if c_ptnl_lers%notfound then
64 p_message := null;
65 else
66 loop
67 if v_ptnl_lers.ptnl_ler_for_per_stat_cd = 'MNL' then
68 v_mnl_message := fnd_message.get_string('BEN','BEN_92657_MNL_PLE');
69 elsif v_ptnl_lers.ptnl_ler_for_per_stat_cd in ('DTCTD','MNLO','UNPROCD') then
70 v_ler_message := fnd_message.get_string('BEN','BEN_92671_OPEN_BENEFIT');
71 end if;
72 fetch c_ptnl_lers into v_ptnl_lers;
73 exit when c_ptnl_lers%notfound;
74 end loop;
75 -- if there is any potential life event which has a status code MNL then throw this message.
76 if v_mnl_message is not null then
77 p_message := v_mnl_message;
78 elsif v_ler_message is not null then
79 p_message := v_ler_message;
80 else
81 p_message := null;
82 end if;
83 end if;
84 close c_ptnl_lers;
85 --
86 hr_utility.set_location('Entering:'|| l_proc, 100);
87 end dtct_ptnl_ler;
88 --
89 end ben_ss_dtct_ptnl_ler;
[Home] [Help]