[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ELIGIBILITY4
Source
1 package body ben_determine_eligibility4 as
2 /* $Header: bendete4.pkb 120.0.12000000.2 2007/02/07 23:11:57 kmahendr noship $ */
3 --
4 procedure prev_elig_check
5 (p_person_id in number
6 ,p_pgm_id in number
7 ,p_pl_id in number
8 ,p_ptip_id in number
9 ,p_effective_date in date
10 ,p_mode_cd in varchar2
11 ,p_irec_asg_id in number
12 --
13 ,p_prev_eligibility out nocopy boolean
14 ,p_elig_per_id out nocopy number
15 ,p_elig_per_elig_flag out nocopy varchar2
16 ,p_prev_prtn_strt_dt out nocopy date
17 ,p_prev_prtn_end_dt out nocopy date
18 ,p_per_in_ler_id out nocopy number
19 ,p_object_version_number out nocopy number
20 ,p_prev_age_val out nocopy number
21 ,p_prev_los_val out nocopy number
22 )
23 is
24 --
25 CURSOR c_prev_elig_check
26 (c_person_id in number
27 ,c_pgm_id in number
28 ,c_pl_id in number
29 ,c_ptip_id in number
30 ,c_effective_date in date
31 ,c_mode_cd in varchar2
32 ,c_irec_asg_id in number
33 )
34 IS
35 select pep.elig_per_id,
36 pep.elig_flag,
37 pep.prtn_strt_dt,
38 pep.prtn_end_dt,
39 pep.per_in_ler_id,
40 pep.object_version_number,
41 pep.age_val,
42 pep.los_val
43 from ben_elig_per_f pep,
44 ben_per_in_ler pil
45 where pep.person_id = c_person_id
46 and nvl(pep.pgm_id,-1) = c_pgm_id
47 and nvl(pep.pl_id,-1) = c_pl_id
48 and pep.plip_id is null
49 and nvl(pep.ptip_id,-1) = c_ptip_id
50 and c_effective_date
51 between pep.effective_start_date
52 and pep.effective_end_date
53 and pil.per_in_ler_id(+)=pep.per_in_ler_id
54 and pil.business_group_id(+)=pep.business_group_id
55 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
56 or pil.per_in_ler_stat_cd is null -- outer join condition
57 )
58 and nvl(pil.assignment_id, -9999) = decode (c_mode_cd,
59 'I',
60 c_irec_asg_id,
61 nvl(pil.assignment_id, -9999));
62 --
63 l_elig_per_id number;
64 l_elig_per_elig_flag varchar2(1000);
65 l_prev_prtn_strt_dt date;
66 l_prev_prtn_end_dt date;
67 l_per_in_ler_id number;
68 l_object_version_number number;
69 l_prev_eligibility boolean;
70 l_prev_age_val number;
71 l_prev_los_val number;
72 --
73 begin
74 --
75 open c_prev_elig_check
76 (c_person_id => p_person_id
77 ,c_pgm_id => p_pgm_id
78 ,c_pl_id => p_pl_id
79 ,c_ptip_id => p_ptip_id
80 ,c_effective_date => p_effective_date
81 ,c_mode_cd => p_mode_cd
82 ,c_irec_asg_id => p_irec_asg_id
83 );
84 --
85 fetch c_prev_elig_check into l_elig_per_id,
86 l_elig_per_elig_flag,
87 l_prev_prtn_strt_dt,
88 l_prev_prtn_end_dt,
89 l_per_in_ler_id,
90 l_object_version_number,
91 l_prev_age_val,
92 l_prev_los_val;
93 if c_prev_elig_check%notfound then
94 --
95 l_prev_eligibility := false;
96 --
97 else
98 --
99 l_prev_eligibility := true;
100 --
101 end if;
102 close c_prev_elig_check;
103 --
104 p_prev_eligibility := l_prev_eligibility;
105 p_elig_per_id := l_elig_per_id;
106 p_elig_per_elig_flag := l_elig_per_elig_flag;
107 p_prev_prtn_strt_dt := l_prev_prtn_strt_dt;
108 p_prev_prtn_end_dt := l_prev_prtn_end_dt;
109 p_per_in_ler_id := l_per_in_ler_id;
110 p_object_version_number := l_object_version_number;
111 p_prev_age_val := l_prev_age_val;
112 p_prev_los_val := l_prev_los_val;
113 --
114 end prev_elig_check;
115 --
116 procedure prev_opt_elig_check
117 (p_person_id in number
118 ,p_effective_date in date
119 ,p_pl_id in number
120 ,p_opt_id in number
121 ,p_mode_cd in varchar2
122 ,p_irec_asg_id in number
123 --
124 ,p_prev_eligibility out nocopy boolean
125 ,p_elig_per_opt_id out nocopy number
126 ,p_opt_elig_flag out nocopy varchar2
127 ,p_prev_prtn_strt_dt out nocopy date
128 ,p_prev_prtn_end_dt out nocopy date
129 ,p_object_version_number_opt out nocopy number
130 ,p_elig_per_id out nocopy number
131 ,p_per_in_ler_id out nocopy number
132 ,p_elig_per_prtn_strt_dt out nocopy date
133 ,p_elig_per_prtn_end_dt out nocopy date
134 ,p_prev_age_val out nocopy number
135 ,p_prev_los_val out nocopy number
136 )
137 is
138 --
139 cursor c_prev_opt_elig_check
140 (c_person_id in number
141 ,c_effective_date in date
142 ,c_pl_id in number
143 ,c_opt_id in number
144 ,c_mode_cd in varchar2
145 ,c_irec_asg_id in number
146 )
147 is
148 select epo.elig_per_opt_id,
149 epo.elig_flag,
150 epo.prtn_strt_dt,
151 epo.prtn_end_dt,
152 epo.object_version_number,
153 pep.elig_per_id,
154 epo.per_in_ler_id,
155 pep.prtn_strt_dt,
156 pep.prtn_end_dt,
157 epo.age_val,
158 epo.los_val
159 from ben_elig_per_opt_f epo,
160 ben_per_in_ler pil,
161 ben_elig_per_f pep
162 where pep.person_id = c_person_id
163 and pep.pl_id = c_pl_id
164 and epo.opt_id = c_opt_id
165 and pep.elig_per_id = epo.elig_per_id
166 and pep.pgm_id is null
167 and c_effective_date
168 between pep.effective_start_date
169 and pep.effective_end_date
170 and c_effective_date
171 between epo.effective_start_date
172 and epo.effective_end_date
173 and pil.per_in_ler_id(+)=epo.per_in_ler_id
174 and pil.business_group_id(+)=epo.business_group_id
175 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
176 or pil.per_in_ler_stat_cd is null)
177 and nvl(pil.assignment_id,-9999) = decode ( c_mode_cd,
178 'I',
179 c_irec_asg_id,
180 nvl(pil.assignment_id, -9999) ); -- iRec : Match assignment_id for iRec
181 --
182 l_elig_per_opt_id number;
183 l_opt_elig_flag varchar2(1000);
184 l_prev_prtn_strt_dt date;
185 l_prev_prtn_end_dt date;
186 l_object_version_number_opt number;
187 l_elig_per_id number;
188 l_per_in_ler_id number;
189 l_elig_per_prtn_strt_dt date;
190 l_elig_per_prtn_end_dt date;
191 l_prev_eligibility boolean;
192 l_prev_age_val number;
193 l_prev_los_val number;
194 --
195 begin
196 --
197 open c_prev_opt_elig_check
198 (c_person_id => p_person_id
199 ,c_effective_date => p_effective_date
200 ,c_pl_id => p_pl_id
201 ,c_opt_id => p_opt_id
202 ,c_mode_cd => p_mode_cd
203 ,c_irec_asg_id => p_irec_asg_id
204 );
205 --
206 fetch c_prev_opt_elig_check into l_elig_per_opt_id,
207 l_opt_elig_flag,
208 l_prev_prtn_strt_dt,
209 l_prev_prtn_end_dt,
210 l_object_version_number_opt,
211 l_elig_per_id,
212 l_per_in_ler_id,
213 l_elig_per_prtn_strt_dt,
214 l_elig_per_prtn_end_dt,
215 l_prev_age_val,
216 l_prev_los_val;
217 if c_prev_opt_elig_check%notfound then
218 --
219 l_prev_eligibility := false;
220 --
221 else
222 --
223 l_prev_eligibility := true;
224 --
225 end if;
226 close c_prev_opt_elig_check;
227 --
228 p_prev_eligibility := l_prev_eligibility;
229 p_elig_per_opt_id := l_elig_per_opt_id;
230 p_opt_elig_flag := l_opt_elig_flag;
231 p_prev_prtn_strt_dt := l_prev_prtn_strt_dt;
232 p_prev_prtn_end_dt := l_prev_prtn_end_dt;
233 p_object_version_number_opt := l_object_version_number_opt;
234 p_elig_per_id := l_elig_per_id;
235 p_per_in_ler_id := l_per_in_ler_id;
236 p_elig_per_prtn_strt_dt := l_elig_per_prtn_strt_dt;
237 p_elig_per_prtn_end_dt := l_elig_per_prtn_end_dt;
238 p_prev_age_val := l_prev_age_val;
239 p_prev_los_val := l_prev_los_val;
240 --
241 end prev_opt_elig_check;
242 --
243 end ben_determine_eligibility4;