[Home] [Help]
PACKAGE BODY: APPS.BEN_ENROLMENT_REQUIREMENTS2
Source
4 /*
1 PACKAGE BODY ben_enrolment_requirements2 AS
2 /* $Header: bendenr2.pkb 120.0 2006/02/16 12:24:50 kmahendr noship $ */
3 -------------------------------------------------------------------------------
5 +=============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=============================================================================+
10 --
11 History
12 Date Who Version What?
13 ---- --- ------- -----
14 24 Jan 06 mhoyes 115.0 Created.
15 */
16 -------------------------------------------------------------------------------
17 PROCEDURE get_asg_dets
18 (p_person_id IN number
19 ,p_run_mode IN varchar2
20 ,p_leodt IN date
21 ,p_effdt IN date
22 --
23 ,p_asg_id out nocopy number
24 ,p_org_id out nocopy number
25 )
26 IS
27 --
28 CURSOR c_asg
29 (c_per_id number
30 ,c_run_md varchar2
31 ,c_effdt date
32 )
33 IS
34 SELECT asg.assignment_id,
35 asg.organization_id
36 FROM per_all_assignments_f asg
37 WHERE person_id = c_per_id
38 and asg.assignment_type <> 'C'
39 AND asg.primary_flag = decode(c_run_md, 'I',asg.primary_flag, 'Y')
40 AND c_effdt
41 BETWEEN asg.effective_start_date AND asg.effective_end_date;
42 --
43 l_asg_id number;
44 l_org_id number;
45 --
46 BEGIN
47 --
48 g_debug := hr_utility.debug_enabled;
49 --
50 if g_debug then
51 hr_utility.set_location('Entering: ben_enrolment_requirements2.get_asg_dets', 10);
52 end if;
53 --
54 OPEN c_asg
55 (c_per_id => p_person_id
56 ,c_run_md => p_run_mode
57 ,c_effdt => p_effdt
58 );
59 FETCH c_asg INTO l_asg_id, l_org_id;
60 IF c_asg%NOTFOUND THEN
61 CLOSE c_asg;
62 fnd_message.set_name('BEN', 'BEN_92106_PRTT_NO_ASGN');
63 fnd_message.set_token('PROC', 'ben_enrolment_requirements2.get_asg_dets');
64 fnd_message.set_token('PERSON_ID', TO_CHAR(p_person_id));
65 fnd_message.set_token('LF_EVT_OCRD_DT', TO_CHAR(p_leodt));
66 fnd_message.set_token('EFFECTIVE_DATE', TO_CHAR(p_effdt));
67 RAISE ben_manage_life_events.g_record_error;
68 END IF;
69 CLOSE c_asg;
70 --
71 p_asg_id := l_asg_id;
72 p_org_id := l_org_id;
73 --
74 if g_debug then
75 hr_utility.set_location('Leaving: ben_enrolment_requirements2.get_asg_dets', 50);
76 end if;
77 --
78 END get_asg_dets;
79 --
80 PROCEDURE get_perpiller_dets
81 (p_person_id IN number
82 ,p_bgp_id IN number
83 ,p_ler_id IN number
84 ,p_run_mode IN varchar2
85 ,p_effdt IN date
86 ,p_irecasg_id IN number
87 --
88 ,p_pil_id out nocopy number
92 ,p_ler_esd out nocopy date
89 ,p_lertyp_cd out nocopy varchar2
90 ,p_lernm out nocopy varchar2
91 ,p_pil_leodt out nocopy date
93 ,p_ler_eed out nocopy date
94 )
95 IS
96 --
97 CURSOR c_per_in_ler_info
98 (c_per_id number
99 ,c_bgp_id number
100 ,c_ler_id number
101 ,c_run_md varchar2
102 ,c_effdt date
103 ,c_irecasg_id number
104 )
105 IS
106 SELECT pil.per_in_ler_id,
107 ler.typ_cd,
108 ler.name,
109 pil.lf_evt_ocrd_dt,
110 ler.effective_start_date,
111 ler.effective_end_date
112 FROM ben_per_in_ler pil, ben_ler_f ler
113 WHERE pil.person_id = c_per_id
114 AND pil.business_group_id = c_bgp_id
115 AND pil.ler_id = c_ler_id
116 AND pil.per_in_ler_stat_cd = 'STRTD'
117 AND ler.business_group_id = c_bgp_id
118 AND pil.ler_id = ler.ler_id
119 AND c_effdt BETWEEN ler.effective_start_date
120 AND ler.effective_end_date
121 and nvl(pil.assignment_id, -9999) = decode (c_run_md,
122 'I',
123 c_irecasg_id,
124 nvl(pil.assignment_id, -9999) );
125 --
126 l_per_in_ler_id number;
127 l_ler_typ_cd varchar2(2000);
128 l_ler_name varchar2(2000);
129 l_lf_evt_ocrd_dt_fetch date;
130 l_ler_esd date;
131 l_ler_eed date;
132 --
133 BEGIN
134 --
135 g_debug := hr_utility.debug_enabled;
136 --
137 if g_debug then
138 hr_utility.set_location('Entering: ben_enrolment_requirements2.get_perpiller_dets', 10);
139 end if;
140 --
141 OPEN c_per_in_ler_info
142 (c_per_id => p_person_id
143 ,c_bgp_id => p_bgp_id
144 ,c_ler_id => p_ler_id
145 ,c_run_md => p_run_mode
146 ,c_effdt => p_effdt
147 ,c_irecasg_id => p_irecasg_id
148 );
149 FETCH c_per_in_ler_info INTO l_per_in_ler_id,
150 l_ler_typ_cd,
151 l_ler_name,
152 l_lf_evt_ocrd_dt_fetch,
153 l_ler_esd,
154 l_ler_eed;
155 --
156 IF c_per_in_ler_info%NOTFOUND THEN
157 CLOSE c_per_in_ler_info;
158 --
159 fnd_message.set_name('BEN', 'BEN_91272_PER_IN_LER_MISSING');
160 fnd_message.set_token('PROC', 'ben_enrolment_requirements2.get_perpiller_dets');
161 fnd_message.set_token('PERSON_ID', TO_CHAR(p_person_id));
162 fnd_message.set_token('LER_ID', TO_CHAR(p_ler_id));
163 fnd_message.set_token('EFFECTIVE_DATE', TO_CHAR(p_effdt));
164 fnd_message.set_token('BG_ID', TO_CHAR(p_bgp_id));
165 RAISE ben_manage_life_events.g_record_error;
166 --
167 END IF;
168 CLOSE c_per_in_ler_info;
169 --
170 p_pil_id := l_per_in_ler_id;
171 p_lertyp_cd := l_ler_typ_cd;
172 p_lernm := l_ler_name;
173 p_pil_leodt := l_lf_evt_ocrd_dt_fetch;
174 p_ler_esd := l_ler_esd;
175 p_ler_eed := l_ler_eed;
176 --
177 if g_debug then
178 hr_utility.set_location('Leaving: ben_enrolment_requirements2.get_perpiller_dets', 50);
179 end if;
180 --
181 END get_perpiller_dets;
182 --
183 PROCEDURE get_latest_enrtdt
184 (p_person_id IN number
185 ,p_bgp_id IN number
186 --
187 ,p_pen_mxesd out nocopy date
188 )
189 IS
190 --
191 CURSOR c_get_latest_enrt_dt
192 (c_per_id number
193 ,c_bgp_id number
194 )
195 IS
196 select max(rslt.effective_start_date)
197 from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
198 where rslt.person_id = c_per_id
199 and ler.ler_id=rslt.ler_id
200 -- and rslt.prtt_enrt_rslt_stat_cd NOT IN ('BCKDT', 'VOIDD')
201 and rslt.prtt_enrt_rslt_stat_cd is null
202 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
203 and rslt.business_group_id = c_bgp_id
204 and rslt.enrt_cvg_thru_dt = hr_api.g_eot; -- Bug 4388226 - End-dated suspended enrl shudn't be picked up.
205 --
206 l_pen_mxesd date;
207 --
208 BEGIN
209 --
210 g_debug := hr_utility.debug_enabled;
211 --
212 if g_debug then
213 hr_utility.set_location('Entering: ben_enrolment_requirements2.get_latest_enrtdt', 10);
214 end if;
215 --
216 OPEN c_get_latest_enrt_dt
217 (c_per_id => p_person_id
218 ,c_bgp_id => p_bgp_id
219 );
220 FETCH c_get_latest_enrt_dt into l_pen_mxesd;
221 close c_get_latest_enrt_dt ;
222 --
223 p_pen_mxesd := l_pen_mxesd;
224 --
225 if g_debug then
226 hr_utility.set_location('Leaving: ben_enrolment_requirements2.get_latest_enrtdt', 50);
227 end if;
228 --
229 END get_latest_enrtdt;
230 --
231 PROCEDURE bckdout_ler
232 (p_person_id IN number
233 ,p_effdt IN date
234 ,p_bgp_id IN number
235 ,p_ler_id IN number
236 ,p_leodt IN date
237 --
238 ,p_pil_bcktdt out nocopy date
239 )
240 IS
241 --
242 CURSOR c_backed_out_ler
243 (c_per_id number
244 ,c_effdt date
245 ,c_bgp_id number
246 ,c_ler_id number
247 ,c_leodt date
248 )
249 IS
250 SELECT MAX(pil.bckt_dt)
251 FROM ben_per_in_ler pil
252 -- CWB changes
253 ,ben_ler_f ler
257 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
254 ,ben_ptnl_ler_for_per plr
255 WHERE pil.person_id = c_per_id
256 AND pil.ler_id = ler.ler_id
258 and c_effdt
259 between ler.effective_start_date and ler.effective_end_date
260 AND pil.business_group_id = c_bgp_id
261 AND pil.ler_id = c_ler_id
262 AND pil.lf_evt_ocrd_dt = c_leodt
263 AND pil.bckt_dt IS NOT NULL
264 and pil.per_in_ler_stat_cd = 'BCKDT'
265 and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id
266 and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD';
267 --
268 l_pil_mxbcktdt date;
269 --
270 BEGIN
271 --
272 g_debug := hr_utility.debug_enabled;
273 --
274 if g_debug then
275 hr_utility.set_location('Entering: ben_enrolment_requirements2.bckdout_ler', 10);
276 end if;
277 --
278 OPEN c_backed_out_ler
279 (c_per_id => p_person_id
280 ,c_effdt => p_effdt
281 ,c_bgp_id => p_bgp_id
282 ,c_ler_id => p_ler_id
283 ,c_leodt => p_leodt
284 );
285 FETCH c_backed_out_ler into l_pil_mxbcktdt;
286 close c_backed_out_ler;
287 --
288 p_pil_bcktdt := l_pil_mxbcktdt;
289 --
290 if g_debug then
291 hr_utility.set_location('Leaving: ben_enrolment_requirements2.bckdout_ler', 50);
292 end if;
293 --
294 END bckdout_ler;
295 --
296 PROCEDURE ptipenrt_info
297 (p_person_id IN number
298 ,p_effdt IN date
299 ,p_bgp_id IN number
300 ,p_ptip_id IN number
301 ,p_cvgthrudt IN date
302 --
303 ,p_pen_pl_id out nocopy number
304 ,p_pen_oipl_id out nocopy number
305 ,p_pen_plip_id out nocopy number
306 )
307 IS
308 --
309 CURSOR c_ptip_enrolment_info
310 (c_per_id number
311 ,c_bgp_id number
312 ,c_cvgthrudt date
313 ,c_ptip_id number
314 ,c_effdt date
315 )
316 IS
317 SELECT pen.pl_id,
318 pen.oipl_id,
319 plip.plip_id
320 FROM ben_prtt_enrt_rslt_f pen, ben_plip_f plip
321 WHERE pen.person_id = c_per_id
322 AND pen.business_group_id = c_bgp_id
323 AND pen.prtt_enrt_rslt_stat_cd IS NULL
324 --AND pen.sspndd_flag = 'N'
325 AND (pen.sspndd_flag = 'N' --CFW
326 OR (pen.sspndd_flag = 'Y' and
327 pen.enrt_cvg_thru_dt = hr_api.g_eot
328 )
329 )
330 AND pen.effective_end_date = hr_api.g_eot
331 AND c_cvgthrudt <= pen.enrt_cvg_thru_dt
332 AND pen.enrt_cvg_strt_dt < pen.effective_end_date
333 AND c_ptip_id = pen.ptip_id
334 AND plip.pgm_id = pen.pgm_id
335 AND plip.pl_id = pen.pl_id
336 AND c_effdt BETWEEN plip.effective_start_date
337 AND plip.effective_end_date;
338 --
339 l_pil_mxbcktdt date;
340 --
341 l_pen_pl_id number;
342 l_pen_oipl_id number;
343 l_pen_plip_id number;
344 --
345 BEGIN
346 --
347 g_debug := hr_utility.debug_enabled;
348 --
349 if g_debug then
350 hr_utility.set_location('Entering: ben_enrolment_requirements2.ptipenrt_info', 10);
351 end if;
352 --
353 OPEN c_ptip_enrolment_info
354 (c_per_id => p_person_id
355 ,c_bgp_id => p_bgp_id
356 ,c_cvgthrudt => p_cvgthrudt
357 ,c_ptip_id => p_ptip_id
358 ,c_effdt => p_effdt
359 );
360 FETCH c_ptip_enrolment_info into l_pen_pl_id, l_pen_oipl_id, l_pen_plip_id;
361 close c_ptip_enrolment_info;
362 --
363 p_pen_pl_id := l_pen_pl_id;
364 p_pen_oipl_id := l_pen_oipl_id;
365 p_pen_plip_id := l_pen_plip_id;
366 --
367 if g_debug then
368 hr_utility.set_location('Leaving: ben_enrolment_requirements2.ptipenrt_info', 50);
369 end if;
370 --
371 END ptipenrt_info;
372 --
373 PROCEDURE get_lerplipdfltcd
374 (p_plip_id IN number
375 ,p_ler_id IN number
376 ,p_effdt IN date
377 --
378 ,p_lep_dflt_enrt_cd out nocopy varchar2
379 ,p_lep_dflt_enrt_rl out nocopy varchar2
380 )
381 IS
382 --
383 CURSOR c_ler_plip_dflt_cd
384 (c_plip_id number
385 ,c_ler_id number
386 ,c_effdt date
387 )
388 IS
389 SELECT lep.dflt_enrt_cd,
390 lep.dflt_enrt_rl
391 FROM ben_ler_chg_plip_enrt_f lep
392 WHERE c_plip_id = lep.plip_id
393 AND c_ler_id = lep.ler_id
394 AND c_effdt BETWEEN lep.effective_start_date
395 AND lep.effective_end_date;
396 --
397 l_pil_mxbcktdt date;
398 --
399 l_dflt_enrt_cd varchar2(1000);
400 l_dflt_enrt_rl number;
401 --
402 BEGIN
403 --
404 g_debug := hr_utility.debug_enabled;
405 --
406 if g_debug then
407 hr_utility.set_location('Entering: ben_enrolment_requirements2.get_lerplipdfltcd', 10);
408 end if;
409 --
410 OPEN c_ler_plip_dflt_cd
411 (c_plip_id => p_plip_id
412 ,c_ler_id => p_ler_id
413 ,c_effdt => p_effdt
414 );
415 FETCH c_ler_plip_dflt_cd into l_dflt_enrt_cd, l_dflt_enrt_rl;
416 close c_ler_plip_dflt_cd;
417 --
418 p_lep_dflt_enrt_cd := l_dflt_enrt_cd;
419 p_lep_dflt_enrt_rl := l_dflt_enrt_rl;
420 --
421 if g_debug then
425 END get_lerplipdfltcd;
422 hr_utility.set_location('Leaving: ben_enrolment_requirements2.get_lerplipdfltcd', 50);
423 end if;
424 --
426 --
427 END ben_enrolment_requirements2;