1 PACKAGE BODY ben_enrolment_requirements1 AS
2 /* $Header: bendenr1.pkb 120.2.12010000.2 2008/08/05 14:38:34 ubhat ship $ */
3 -------------------------------------------------------------------------------
4 /*
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 13-Feb-05 mhoyes 115.4 - bug5031107. Moved locally defined procedures
16 to ben_enrolment_requirements1.
17 28-Apr-05 mhoyes 115.5 - bug5152911. Added GetPenPerIDMxESD.
18 Re-wrote cursor c_getpenesd in plsql.
19 */
20 -------------------------------------------------------------------------------
21 PROCEDURE determine_ben_settings(
22 p_pl_id IN ben_ler_chg_pl_nip_enrt_f.pl_id%TYPE,
23 p_ler_id IN ben_ler_chg_pl_nip_enrt_f.ler_id%TYPE,
24 p_lf_evt_ocrd_dt IN DATE,
25 p_ptip_id IN ben_ler_chg_ptip_enrt_f.ptip_id%TYPE,
26 p_pgm_id IN ben_ler_chg_pgm_enrt_f.pgm_id%TYPE,
27 p_plip_id IN ben_ler_chg_plip_enrt_f.plip_id%TYPE,
28 p_oipl_id IN ben_ler_chg_oipl_enrt_f.oipl_id%TYPE,
29 p_just_prclds_chg_flag IN BOOLEAN DEFAULT FALSE,
30 p_enrt_cd OUT NOCOPY ben_ler_chg_oipl_enrt_f.enrt_cd%TYPE,
31 p_enrt_rl OUT NOCOPY ben_ler_chg_oipl_enrt_f.enrt_rl%TYPE,
32 p_auto_enrt_mthd_rl OUT NOCOPY ben_ler_chg_oipl_enrt_f.auto_enrt_mthd_rl%TYPE,
33 p_crnt_enrt_prclds_chg_flag OUT NOCOPY ben_ler_chg_oipl_enrt_f.crnt_enrt_prclds_chg_flag%TYPE,
34 p_dflt_flag OUT NOCOPY ben_ler_chg_oipl_enrt_f.dflt_flag%TYPE,
35 p_enrt_mthd_cd OUT NOCOPY ben_ler_chg_pgm_enrt_f.enrt_mthd_cd%TYPE,
36 p_stl_elig_cant_chg_flag OUT NOCOPY ben_ler_chg_oipl_enrt_f.stl_elig_cant_chg_flag%TYPE,
37 p_tco_chg_enrt_cd OUT NOCOPY ben_ler_chg_ptip_enrt_f.tco_chg_enrt_cd%TYPE,
38 p_ler_chg_oipl_found_flag OUT NOCOPY VARCHAR2,
39 p_ler_chg_found_flag OUT NOCOPY VARCHAR2,
40 p_enrt_cd_level OUT NOCOPY VARCHAR2 ) IS
41 -- ========================
42 -- define the local cursors
43 -- ========================
44 CURSOR csr_oipl IS
45 SELECT oipl.auto_enrt_flag,
46 oipl.auto_enrt_mthd_rl,
47 oipl.crnt_enrt_prclds_chg_flag,
48 oipl.dflt_flag,
49 oipl.enrt_cd,
50 oipl.enrt_rl,
51 oipl.ler_chg_oipl_enrt_id,
52 oipl.stl_elig_cant_chg_flag
53 FROM ben_ler_chg_oipl_enrt_f oipl
54 WHERE oipl.oipl_id = p_oipl_id
55 AND oipl.ler_id = p_ler_id
56 AND p_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
57 AND oipl.effective_end_date;
58 --
59 CURSOR csr_pgm IS
60 SELECT pgm.auto_enrt_mthd_rl,
61 pgm.crnt_enrt_prclds_chg_flag,
62 pgm.enrt_cd,
63 pgm.enrt_mthd_cd,
64 pgm.enrt_rl,
65 pgm.ler_chg_pgm_enrt_id,
66 pgm.stl_elig_cant_chg_flag
67 FROM ben_ler_chg_pgm_enrt_f pgm
68 WHERE pgm.pgm_id = p_pgm_id
69 AND pgm.ler_id = p_ler_id
70 AND p_lf_evt_ocrd_dt BETWEEN pgm.effective_start_date
71 AND pgm.effective_end_date;
72 --
73 CURSOR csr_ptip IS
74 SELECT ptip.crnt_enrt_prclds_chg_flag,
75 ptip.enrt_cd,
76 ptip.enrt_mthd_cd,
77 ptip.enrt_rl,
78 ptip.ler_chg_ptip_enrt_id,
79 ptip.stl_elig_cant_chg_flag,
80 ptip.tco_chg_enrt_cd
81 FROM ben_ler_chg_ptip_enrt_f ptip
82 WHERE ptip.ptip_id = p_ptip_id
83 AND ptip.ler_id = p_ler_id
84 AND p_lf_evt_ocrd_dt BETWEEN ptip.effective_start_date
85 AND ptip.effective_end_date;
86 --
87 CURSOR csr_plip IS
88 SELECT plip.auto_enrt_mthd_rl,
89 plip.crnt_enrt_prclds_chg_flag,
90 plip.dflt_flag,
91 plip.enrt_cd,
92 plip.enrt_mthd_cd,
93 plip.enrt_rl,
94 plip.ler_chg_plip_enrt_id,
95 plip.stl_elig_cant_chg_flag,
96 plip.tco_chg_enrt_cd
97 FROM ben_ler_chg_plip_enrt_f plip
98 WHERE plip.plip_id = p_plip_id
99 AND plip.ler_id = p_ler_id
100 AND p_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
101 AND plip.effective_end_date;
102 --
103 CURSOR csr_pl_nip IS
104 SELECT pl_nip.auto_enrt_mthd_rl,
105 pl_nip.crnt_enrt_prclds_chg_flag,
106 pl_nip.dflt_flag,
107 pl_nip.enrt_cd,
108 pl_nip.enrt_mthd_cd,
109 pl_nip.enrt_rl,
110 pl_nip.ler_chg_pl_nip_enrt_id,
111 pl_nip.stl_elig_cant_chg_flag,
112 pl_nip.tco_chg_enrt_cd
113 FROM ben_ler_chg_pl_nip_enrt_f pl_nip
114 WHERE pl_nip.pl_id = p_pl_id
115 AND pl_nip.ler_id = p_ler_id
116 AND p_lf_evt_ocrd_dt BETWEEN pl_nip.effective_start_date
117 AND pl_nip.effective_end_date;
118 -- ======================
119 -- define local variables
120 -- ======================
121 oipl_auto_enrt_flag ben_ler_chg_oipl_enrt_f.auto_enrt_flag%TYPE;
122 oipl_auto_enrt_mthd_rl ben_ler_chg_oipl_enrt_f.auto_enrt_mthd_rl%TYPE;
123 oipl_crnt_enrt_prclds_chg_flag ben_ler_chg_oipl_enrt_f.crnt_enrt_prclds_chg_flag%TYPE;
124 oipl_dflt_flag ben_ler_chg_oipl_enrt_f.dflt_flag%TYPE;
125 oipl_enrt_cd ben_ler_chg_oipl_enrt_f.enrt_cd%TYPE;
126 oipl_enrt_rl ben_ler_chg_oipl_enrt_f.enrt_rl%TYPE;
127 oipl_ler_chg_oipl_enrt_id ben_ler_chg_oipl_enrt_f.ler_chg_oipl_enrt_id%TYPE;
128 oipl_stl_elig_cant_chg_flag ben_ler_chg_oipl_enrt_f.stl_elig_cant_chg_flag%TYPE;
129 pgm_auto_enrt_mthd_rl ben_ler_chg_pgm_enrt_f.auto_enrt_mthd_rl%TYPE;
130 pgm_crnt_enrt_prclds_chg_flag ben_ler_chg_pgm_enrt_f.crnt_enrt_prclds_chg_flag%TYPE;
131 pgm_enrt_cd ben_ler_chg_pgm_enrt_f.enrt_cd%TYPE;
132 pgm_enrt_mthd_cd ben_ler_chg_pgm_enrt_f.enrt_mthd_cd%TYPE;
133 pgm_enrt_rl ben_ler_chg_pgm_enrt_f.enrt_rl%TYPE;
134 pgm_ler_chg_pgm_enrt_id ben_ler_chg_pgm_enrt_f.ler_chg_pgm_enrt_id%TYPE;
135 pgm_stl_elig_cant_chg_flag ben_ler_chg_pgm_enrt_f.stl_elig_cant_chg_flag%TYPE;
136 pnip_auto_enrt_mthd_rl ben_ler_chg_pl_nip_enrt_f.auto_enrt_mthd_rl%TYPE;
137 pnip_crnt_enrt_prclds_chg_flag ben_ler_chg_pl_nip_enrt_f.crnt_enrt_prclds_chg_flag%TYPE;
138 pnip_dflt_flag ben_ler_chg_pl_nip_enrt_f.dflt_flag%TYPE;
139 pnip_enrt_cd ben_ler_chg_pl_nip_enrt_f.enrt_cd%TYPE;
140 pnip_enrt_mthd_cd ben_ler_chg_pl_nip_enrt_f.enrt_mthd_cd%TYPE;
141 pnip_enrt_rl ben_ler_chg_pl_nip_enrt_f.enrt_rl%TYPE;
142 pnip_ler_chg_pnip_enrt_id ben_ler_chg_pl_nip_enrt_f.ler_chg_pl_nip_enrt_id%TYPE;
143 pnip_stl_elig_cant_chg_flag ben_ler_chg_pl_nip_enrt_f.stl_elig_cant_chg_flag%TYPE;
144 pnip_tco_chg_enrt_cd ben_ler_chg_pl_nip_enrt_f.tco_chg_enrt_cd%TYPE;
145 plip_auto_enrt_mthd_rl ben_ler_chg_plip_enrt_f.auto_enrt_mthd_rl%TYPE;
146 plip_crnt_enrt_prclds_chg_flag ben_ler_chg_plip_enrt_f.crnt_enrt_prclds_chg_flag%TYPE;
147 plip_dflt_flag ben_ler_chg_plip_enrt_f.dflt_flag%TYPE;
148 plip_enrt_cd ben_ler_chg_plip_enrt_f.enrt_cd%TYPE;
149 plip_enrt_mthd_cd ben_ler_chg_plip_enrt_f.enrt_mthd_cd%TYPE;
150 plip_enrt_rl ben_ler_chg_plip_enrt_f.enrt_rl%TYPE;
151 plip_ler_chg_plip_enrt_id ben_ler_chg_plip_enrt_f.ler_chg_plip_enrt_id%TYPE;
152 plip_stl_elig_cant_chg_flag ben_ler_chg_plip_enrt_f.stl_elig_cant_chg_flag%TYPE;
153 plip_tco_chg_enrt_cd ben_ler_chg_plip_enrt_f.tco_chg_enrt_cd%TYPE;
154 ptip_crnt_enrt_prclds_chg_flag ben_ler_chg_ptip_enrt_f.crnt_enrt_prclds_chg_flag%TYPE;
155 ptip_enrt_cd ben_ler_chg_ptip_enrt_f.enrt_cd%TYPE;
156 ptip_enrt_mthd_cd ben_ler_chg_ptip_enrt_f.enrt_mthd_cd%TYPE;
157 ptip_enrt_rl ben_ler_chg_ptip_enrt_f.enrt_rl%TYPE;
158 ptip_ler_chg_ptip_enrt_id ben_ler_chg_ptip_enrt_f.ler_chg_ptip_enrt_id%TYPE;
159 ptip_stl_elig_cant_chg_flag ben_ler_chg_ptip_enrt_f.stl_elig_cant_chg_flag%TYPE;
160 ptip_tco_chg_enrt_cd ben_ler_chg_ptip_enrt_f.tco_chg_enrt_cd%TYPE;
161 BEGIN
162 --
163 g_debug := hr_utility.debug_enabled;
164 --
165 -- ============================================
166 -- open,fetch and close each cursor if required
167 -- ============================================
168 IF p_ler_id IS NOT NULL THEN
169 IF p_oipl_id IS NOT NULL THEN
170 OPEN csr_oipl;
171 FETCH csr_oipl INTO oipl_auto_enrt_flag,
172 oipl_auto_enrt_mthd_rl,
173 oipl_crnt_enrt_prclds_chg_flag,
174 oipl_dflt_flag,
175 oipl_enrt_cd,
176 oipl_enrt_rl,
177 oipl_ler_chg_oipl_enrt_id,
178 oipl_stl_elig_cant_chg_flag;
179 CLOSE csr_oipl;
180 END IF;
181 IF p_pl_id IS NOT NULL THEN
182 OPEN csr_pl_nip;
183 FETCH csr_pl_nip INTO pnip_auto_enrt_mthd_rl,
184 pnip_crnt_enrt_prclds_chg_flag,
185 pnip_dflt_flag,
186 pnip_enrt_cd,
187 pnip_enrt_mthd_cd,
188 pnip_enrt_rl,
189 pnip_ler_chg_pnip_enrt_id,
190 pnip_stl_elig_cant_chg_flag,
191 pnip_tco_chg_enrt_cd;
192 CLOSE csr_pl_nip;
193 END IF;
194 IF p_plip_id IS NOT NULL THEN
195 OPEN csr_plip;
196 FETCH csr_plip INTO plip_auto_enrt_mthd_rl,
197 plip_crnt_enrt_prclds_chg_flag,
198 plip_dflt_flag,
199 plip_enrt_cd,
200 plip_enrt_mthd_cd,
201 plip_enrt_rl,
202 plip_ler_chg_plip_enrt_id,
203 plip_stl_elig_cant_chg_flag,
204 plip_tco_chg_enrt_cd;
205 CLOSE csr_plip;
206 END IF;
207 IF p_ptip_id IS NOT NULL THEN
208 OPEN csr_ptip;
209 FETCH csr_ptip INTO ptip_crnt_enrt_prclds_chg_flag,
210 ptip_enrt_cd,
211 ptip_enrt_mthd_cd,
212 ptip_enrt_rl,
213 ptip_ler_chg_ptip_enrt_id,
214 ptip_stl_elig_cant_chg_flag,
215 ptip_tco_chg_enrt_cd;
216 CLOSE csr_ptip;
217 END IF;
218 IF p_pgm_id IS NOT NULL THEN
219 OPEN csr_pgm;
220 FETCH csr_pgm INTO pgm_auto_enrt_mthd_rl,
221 pgm_crnt_enrt_prclds_chg_flag,
222 pgm_enrt_cd,
223 pgm_enrt_mthd_cd,
224 pgm_enrt_rl,
225 pgm_ler_chg_pgm_enrt_id,
226 pgm_stl_elig_cant_chg_flag;
227 CLOSE csr_pgm;
228 END IF;
229 -- ==========================================
230 -- determine and SET the OUT parameter values
231 -- ==========================================
232 -- --------------------------------
233 -- set: p_crnt_enrt_prclds_chg_flag
234 -- --------------------------------
235 IF oipl_crnt_enrt_prclds_chg_flag IS NULL THEN
236 IF pnip_crnt_enrt_prclds_chg_flag IS NULL THEN
237 IF plip_crnt_enrt_prclds_chg_flag IS NULL THEN
238 IF ptip_crnt_enrt_prclds_chg_flag IS NULL THEN
239 p_crnt_enrt_prclds_chg_flag := pgm_crnt_enrt_prclds_chg_flag;
240 ELSE
241 p_crnt_enrt_prclds_chg_flag := ptip_crnt_enrt_prclds_chg_flag;
242 END IF;
243 ELSE
244 p_crnt_enrt_prclds_chg_flag := plip_crnt_enrt_prclds_chg_flag;
245 END IF;
246 ELSE
247 p_crnt_enrt_prclds_chg_flag := pnip_crnt_enrt_prclds_chg_flag;
248 END IF;
249 ELSE
250 p_crnt_enrt_prclds_chg_flag := oipl_crnt_enrt_prclds_chg_flag;
251 END IF;
252 -- test to see if only the p_crnt_enrt_prclds_chg_flag is required
253 --IF p_just_prclds_chg_flag THEN
254 -- RETURN;
255 --END IF;
256 -- ----------------------------
257 -- set: p_enrt_cd and p_enrt_rl
258 -- ----------------------------
259 --
260 if g_debug then
261 hr_utility.set_location( 'oipl' || oipl_enrt_cd , 10) ;
262 hr_utility.set_location( 'pl' || pnip_enrt_cd , 10) ;
263 hr_utility.set_location( 'plip' || plip_enrt_cd , 10);
264 hr_utility.set_location( 'ptip' || ptip_enrt_cd , 10) ;
265 hr_utility.set_location( 'oipl id ' || p_oipl_id , 10) ;
266 hr_utility.set_location( 'ptip id ' || p_ptip_id , 10) ;
267 hr_utility.set_location( 'plip id ' || p_plip_id , 10) ;
268 end if;
269 --
270 IF oipl_enrt_cd IS NULL THEN
271 IF pnip_enrt_cd IS NULL THEN
272 IF plip_enrt_cd IS NULL THEN
273 IF ptip_enrt_cd IS NULL THEN
274 p_enrt_cd := pgm_enrt_cd;
275 p_enrt_rl := pgm_enrt_rl;
276 p_enrt_cd_level:= 'PGM' ;
277 ELSE
278 p_enrt_cd := ptip_enrt_cd;
279 p_enrt_rl := ptip_enrt_rl;
280 p_enrt_cd_level := 'PTIP' ;
281 END IF;
282 ELSE
283 p_enrt_cd := plip_enrt_cd;
284 p_enrt_rl := plip_enrt_rl;
285 p_enrt_cd_level := 'PLIP' ;
286 END IF;
287 ELSE
288 p_enrt_cd := pnip_enrt_cd;
289 p_enrt_rl := pnip_enrt_rl;
290 p_enrt_cd_level := 'PL' ;
291 END IF;
292 ELSE
293 p_enrt_cd := oipl_enrt_cd;
294 p_enrt_rl := oipl_enrt_rl;
295 p_enrt_cd_level := 'OIPL' ;
296 END IF;
297 --
298 if g_debug then
299 hr_utility.set_location( 'p_enrt_cd_level ' || p_enrt_cd_level , 10) ;
300 end if;
301 --
302 IF p_just_prclds_chg_flag THEN
303 RETURN;
304 END IF;
305
306 -- ------------------------
307 -- set: p_auto_enrt_mthd_rl
308 -- ------------------------
309 IF oipl_auto_enrt_mthd_rl IS NULL THEN
310 IF pnip_auto_enrt_mthd_rl IS NULL THEN
311 IF plip_auto_enrt_mthd_rl IS NULL THEN
312 p_auto_enrt_mthd_rl := pgm_auto_enrt_mthd_rl;
313 ELSE
314 p_auto_enrt_mthd_rl := plip_auto_enrt_mthd_rl;
315 END IF;
316 ELSE
317 p_auto_enrt_mthd_rl := pnip_auto_enrt_mthd_rl;
318 END IF;
319 ELSE
320 p_auto_enrt_mthd_rl := oipl_auto_enrt_mthd_rl;
321 END IF;
322 -- ----------------
323 -- set: p_dflt_flag
324 -- ----------------
325 IF oipl_dflt_flag IS NULL THEN
326 IF pnip_dflt_flag IS NULL THEN
327 p_dflt_flag := plip_dflt_flag;
328 ELSE
329 p_dflt_flag := pnip_dflt_flag;
330 END IF;
331 ELSE
332 p_dflt_flag := oipl_dflt_flag;
333 END IF;
334 -- -------------------
335 -- set: p_enrt_mthd_cd
336 -- -------------------
337 IF oipl_auto_enrt_flag = 'Y' THEN
338 p_enrt_mthd_cd := 'A';
339 ELSIF oipl_auto_enrt_flag = 'N' THEN
340 p_enrt_mthd_cd := 'E';
341 ELSE
342 IF pnip_enrt_mthd_cd IS NULL THEN
343 IF plip_enrt_mthd_cd IS NULL THEN
344 IF ptip_enrt_mthd_cd IS NULL THEN
345 p_enrt_mthd_cd := pgm_enrt_mthd_cd;
346 ELSE
350 p_enrt_mthd_cd := plip_enrt_mthd_cd;
347 p_enrt_mthd_cd := ptip_enrt_mthd_cd;
348 END IF;
349 ELSE
351 END IF;
352 ELSE
353 p_enrt_mthd_cd := pnip_enrt_mthd_cd;
354 END IF;
355 END IF;
356 -- -----------------------------
357 -- set: p_stl_elig_cant_chg_flag
358 -- -----------------------------
359 IF oipl_stl_elig_cant_chg_flag IS NULL THEN
360 IF pnip_stl_elig_cant_chg_flag IS NULL THEN
361 IF plip_stl_elig_cant_chg_flag IS NULL THEN
362 IF ptip_stl_elig_cant_chg_flag IS NULL THEN
363 p_stl_elig_cant_chg_flag := pgm_stl_elig_cant_chg_flag;
364 ELSE
365 p_stl_elig_cant_chg_flag := ptip_stl_elig_cant_chg_flag;
366 END IF;
367 ELSE
368 p_stl_elig_cant_chg_flag := plip_stl_elig_cant_chg_flag;
369 END IF;
370 ELSE
371 p_stl_elig_cant_chg_flag := pnip_stl_elig_cant_chg_flag;
372 END IF;
373 ELSE
374 p_stl_elig_cant_chg_flag := oipl_stl_elig_cant_chg_flag;
375 END IF;
376 -- ----------------------
377 -- set: p_tco_chg_enrt_cd
378 -- ----------------------
379 IF pnip_tco_chg_enrt_cd IS NULL THEN
380 IF plip_tco_chg_enrt_cd IS NULL THEN
381 p_tco_chg_enrt_cd := ptip_tco_chg_enrt_cd;
382 ELSE
383 p_tco_chg_enrt_cd := plip_tco_chg_enrt_cd;
384 END IF;
385 ELSE
386 p_tco_chg_enrt_cd := pnip_tco_chg_enrt_cd;
387 END IF;
388 -- -------------------------------------------------------
389 -- set: p_ler_chg_oipl_found_flag and p_ler_chg_found_flag
390 -- -------------------------------------------------------
391 IF oipl_ler_chg_oipl_enrt_id IS NULL THEN
392 p_ler_chg_oipl_found_flag := 'N';
393 IF plip_ler_chg_plip_enrt_id IS NULL
394 AND ptip_ler_chg_ptip_enrt_id IS NULL
395 AND pnip_ler_chg_pnip_enrt_id IS NULL
396 AND pgm_ler_chg_pgm_enrt_id IS NULL THEN
397 p_ler_chg_found_flag := 'N';
398 ELSE
399 p_ler_chg_found_flag := 'Y';
400 END IF;
401 ELSE
402 p_ler_chg_oipl_found_flag := 'Y';
403 p_ler_chg_found_flag := 'Y';
404 END IF;
405 END IF;
406 --
407 if g_debug then
408 hr_utility.set_location( 'p_enrt_cd_level ' || p_enrt_cd_level , 10) ;
409 end if;
410 --
411 exception
412 --
413 when others then
414 --
415 p_enrt_cd := null;
416 p_enrt_rl := null;
417 p_auto_enrt_mthd_rl := null;
418 p_crnt_enrt_prclds_chg_flag := null;
419 p_dflt_flag := null;
420 p_enrt_mthd_cd := null;
421 p_stl_elig_cant_chg_flag := null;
422 p_tco_chg_enrt_cd := null;
423 p_ler_chg_oipl_found_flag := null;
424 p_ler_chg_found_flag := null;
425 raise;
426 --
427 END determine_ben_settings;
428 --
429 procedure GetPenPerIDMxESD
430 (p_person_id number
431 ,p_business_group_id number
432 --
433 ,p_penmxesd out nocopy date
434 )
435 is
436 --
437 type g_date_table is varray(10000) of date;
438 --
439 l_penesd_va g_date_table := g_date_table();
440 l_penectdt_va g_date_table := g_date_table();
441 --
442 cursor c_getpenesd
443 (c_bgp_id number
444 ,c_per_id number
445 )
446 is
447 select /*+ c_getpenesd ben_enrolment_requirements1 */
448 rslt.effective_start_date,
449 rslt.enrt_cvg_thru_dt
450 from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
451 where rslt.person_id = c_per_id
452 and ler.ler_id=rslt.ler_id
453 and rslt.prtt_enrt_rslt_stat_cd is null
454 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
455 and rslt.business_group_id = c_bgp_id;
456 --
457 l_mxpenesd date;
458 l_lertyp_cd varchar2(100);
459 l_match boolean;
460 --
461 begin
462 --
463 open c_getpenesd
464 (c_bgp_id => p_business_group_id
465 ,c_per_id => p_person_id
466 );
467 fetch c_getpenesd BULK COLLECT INTO l_penesd_va, l_penectdt_va;
468 close c_getpenesd;
469 --
470 if l_penesd_va.count > 0
471 then
472 --
473 l_mxpenesd := hr_api.g_sot;
474 --
475 for vaen in l_penesd_va.first..l_penesd_va.last
476 loop
477 --
478 if nvl(l_penectdt_va(vaen),hr_api.g_sot) = hr_api.g_eot
479 then
480 --
481 if nvl(l_penesd_va(vaen),hr_api.g_sot) > l_mxpenesd
482 then
483 --
484 l_mxpenesd := l_penesd_va(vaen);
485 --
486 end if;
487 --
488 end if;
489 --
490 end loop;
491 --
492 else
493 --
494 l_mxpenesd := null;
495 --
496 end if;
497 --
498 p_penmxesd := l_mxpenesd;
499 --
500 end GetPenPerIDMxESD;
501 --
502 procedure enrt_perd_strt_dt
503 (p_person_id in number
504 ,p_lf_evt_ocrd_dt in date
505 ,p_enrt_perd_det_ovrlp_bckdt_cd in varchar2
509 ,p_rec_enrt_perd_strt_dt in out nocopy date
506 ,p_business_group_id in number
507 ,p_ler_id in number
508 ,p_effective_date in date
510 )
511 IS
512 -- local variables
513 l_proc varchar2 (72) := 'ben_enrolment_requirements1.enrt_perd_strt_dt';
514 l_latest_procd_dt date;
515 l_backed_out_date date;
516 l_latest_enrt_dt date;
517 l_lf_evt_ocrd_dt date := NVL(p_lf_evt_ocrd_dt, p_effective_date);
518 -- store sysdate sans the time component into a local variable for once
519 l_sysdate date := trunc(sysdate);
520 -- define cursors
521 CURSOR c_get_latest_procd_dt IS
522 SELECT MAX(pil.procd_dt)
523 FROM ben_per_in_ler pil
524 -- CWB changes
525 ,ben_ler_f ler
526 WHERE pil.person_id = p_person_id
527 AND pil.ler_id = ler.ler_id
528 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
529 and l_lf_evt_ocrd_dt between
530 ler.effective_start_date and ler.effective_end_date
531 AND pil.business_group_id = p_business_group_id
532 AND pil.per_in_ler_stat_cd NOT IN ('BCKDT', 'VOIDD')
533 AND pil.procd_dt IS NOT NULL;
534 --
535 CURSOR c_backed_out_ler IS
536 SELECT MAX(pil.bckt_dt)
537 FROM ben_per_in_ler pil
538 -- CWB changes
539 ,ben_ler_f ler
540 ,ben_ptnl_ler_for_per plr
541 WHERE pil.person_id = p_person_id
542 AND pil.ler_id = ler.ler_id
543 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
544 and l_lf_evt_ocrd_dt between
545 ler.effective_start_date and ler.effective_end_date
546 AND pil.business_group_id = p_business_group_id
547 AND pil.ler_id = p_ler_id
548 AND pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
549 AND pil.bckt_dt IS NOT NULL
550 and pil.per_in_ler_stat_cd = 'BCKDT' -- 3063867
551 and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id --3248770
552 and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD' ;
553 --
554
555 -- 2746865
556 -- cursor to select a person's maximum enrollment start date
557 -- Changed the following cursor for bug 3137519 to exclude GSP/ABS/COMP ler types.
558 -- Also included status no in backdt/voidd clause
559 --bug#3697378 - discussed with Phil why we add + 1 to the latest enrollment
560 --however he wanted this to be removed so that self service open enrollment
561 --will not be impacted and asked find ways to show history on enrollment results later
562 cursor c_get_latest_enrt_dt is
563 select max(rslt.effective_start_date)
564 from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
565 where rslt.person_id = p_person_id
566 and ler.ler_id=rslt.ler_id
567 -- and rslt.prtt_enrt_rslt_stat_cd NOT IN ('BCKDT', 'VOIDD')
568 and rslt.prtt_enrt_rslt_stat_cd is null
569 and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
570 and rslt.business_group_id = p_business_group_id
571 and rslt.enrt_cvg_thru_dt = hr_api.g_eot; -- Bug 4388226 - End-dated suspended enrl shudn't be picked up.
572
573 --
574
575 begin
576
577 -- following are the 4 codes used for enrt. period determination
578 -------------------------------------------------------------------------
579 -- L_EPSD_PEPD - Later of Enrollment period start date and
580 -- prior event processed date
581 -- L_EPSD_PEESD - Later of Enrollment period start date and
582 -- One day after prior event elections start date
583 -- L_EPSD_PEESD_BCKDT - Later of Enrollment period start date and One
584 -- day after prior event elections start date and
585 -- current events backed out date
586 -- L_EPSD_PEESD_SYSDT - Later of Enrollment period start date and One
587 -- day after prior event elections start date
588 -- and system date
589 -------------------------------------------------------------------------
590 -- if cd is L_EPSD_PEPD, use the old logic
591 --
592 if g_debug then
593 hr_utility.set_location(' Entering '||l_proc, 10);
594 --
595 -- remove all these debug messages
596 hr_utility.set_location(' p_enrt_perd_det_ovrlp_bckdt_cd is '||p_enrt_perd_det_ovrlp_bckdt_cd, 987);
597 hr_utility.set_location(' p_person_id '||p_person_id, 10);
598 hr_utility.set_location(' p_lf_evt_ocrd_dt '||p_lf_evt_ocrd_dt, 10);
599 hr_utility.set_location(' p_ler_id '||p_ler_id, 10);
600 hr_utility.set_location(' p_effective_date '||p_effective_date, 10);
601 hr_utility.set_location(' p_rec_enrt_perd_strt_dt '||p_rec_enrt_perd_strt_dt, 10);
602 end if;
603 --
604
605 IF nvl(p_enrt_perd_det_ovrlp_bckdt_cd, 'L_EPSD_PEPD') = 'L_EPSD_PEPD' THEN
606 --
607 hr_utility.set_location(' L_EPSD_PEPD', 987);
608 OPEN c_get_latest_procd_dt;
609 FETCH c_get_latest_procd_dt INTO l_latest_procd_dt;
610 -- new epsd is greater of epsd or latest_procd_dt
611 -- IF c_get_latest_procd_dt%FOUND THEN
612 IF l_latest_procd_dt IS NOT NULL THEN
613 hr_utility.set_location(' c_get_latest_procd_dt%found', 987);
614 hr_utility.set_location('l_latest_procd_dt is '||l_latest_procd_dt, 987);
615 -- jcarpent 1/4/2001 bug 1568555, removed +1 from line below
616 IF p_rec_enrt_perd_strt_dt < l_latest_procd_dt THEN
617 hr_utility.set_location('l_latest_procd_dt made enrt strt dt ', 987);
618 -- jcarpent 1/4/2001 bug 1568555, removed +1 from line below
619 p_rec_enrt_perd_strt_dt := l_latest_procd_dt;
620 -- if the enrollment exist for the previous LE
621 -- start the window latest_procd_dt + 1
622 -- or the previous enrollment will be updated in correction mode
623 -- and backout of this LE will remove the previous LE results
624 --the changes are backedout 3086161
625 --
626 --Bugs 3972973 and 3978745 fixes.
627 --If the enrollment starts after the processed date we need to consider the
628 --latest enrollment date.
629 --
630 End IF;
631 --bug#4478186 - enrl start date should always be equal or greater to latest
632 --enrt dt
633 --
634 -- 5152911
635 --
636 ben_enrolment_requirements1.GetPenPerIDMxESD
637 (p_person_id => p_person_id
638 ,p_business_group_id => p_business_group_id
639 --
640 ,p_penmxesd => l_latest_enrt_dt
641 );
642 /* 5152911
643 OPEN c_get_latest_enrt_dt;
644 FETCH c_get_latest_enrt_dt into l_latest_enrt_dt;
645 close c_get_latest_enrt_dt ;
646 */
647 --
648 if l_latest_enrt_dt is not null and l_latest_enrt_dt > p_rec_enrt_perd_strt_dt then
649 p_rec_enrt_perd_strt_dt := l_latest_enrt_dt ;
650 end if ;
651 --
652 END IF;
653 CLOSE c_get_latest_procd_dt;
654 -- 4 is new epsd <= p_lf_evt_ocrd_dt?
655 IF p_rec_enrt_perd_strt_dt <= p_lf_evt_ocrd_dt THEN
656 -- 5 is there a backed out le for the current ler and ...
657 OPEN c_backed_out_ler;
658 FETCH c_backed_out_ler INTO l_backed_out_date;
659 --IF c_backed_out_ler%FOUND THEN
660 IF l_backed_out_date is NOT NULL THEN
661 hr_utility.set_location(' c_backed_out_ler%found', 987);
662 hr_utility.set_location('l_backed_out_date is '||l_backed_out_date, 987);
663 -- 5a ... and is the backed-out date > than the new epsd?
664 IF l_backed_out_date > p_rec_enrt_perd_strt_dt THEN
665 hr_utility.set_location('l_backed_out_date made enrt strt dt ', 987);
666 -- 6 it is the new epsd.
667 p_rec_enrt_perd_strt_dt := l_backed_out_date;
668 END IF;
669 END IF;
670 CLOSE c_backed_out_ler;
671 END IF;
672 -- 2746865
673 -- if cd is L_EPSD_PEESD%, use the new logic
674 ELSIF p_enrt_perd_det_ovrlp_bckdt_cd like 'L_EPSD_PEESD%' THEN
675 hr_utility.set_location(' L_EPSD_PEESD%', 987);
676 -- get the person's latest enrollment start date +1
677 OPEN c_get_latest_enrt_dt;
678 FETCH c_get_latest_enrt_dt into l_latest_enrt_dt;
679 -- IF c_get_latest_enrt_dt%FOUND THEN --changed as its always found
680 IF l_latest_enrt_dt is not null THEN
681 hr_utility.set_location(' c_get_latest_enrt_dt%FOUND', 987);
682 hr_utility.set_location('l_latest_enrt_dt is '||l_latest_enrt_dt, 987);
683 -- if latest enrt dt is greater than epsd, make it the epsd
684 IF l_latest_enrt_dt > p_rec_enrt_perd_strt_dt THEN
685 p_rec_enrt_perd_strt_dt := l_latest_enrt_dt;
686 hr_utility.set_location('l_latest_enrt_dt substituted', 987);
687 END IF;
688 END IF;
689 CLOSE c_get_latest_enrt_dt;
690 -- cd is 2 find the bckdt out dt
691 IF p_enrt_perd_det_ovrlp_bckdt_cd = 'L_EPSD_PEESD_BCKDT' THEN
692 hr_utility.set_location('L_EPSD_PEESD_BCKDT entered', 987);
693 -- get the backed out date
694 OPEN c_backed_out_ler;
695 FETCH c_backed_out_ler INTO l_backed_out_date;
696 hr_utility.set_location('l_backed_out_date is '||l_backed_out_date, 987);
697 --IF c_backed_out_ler%FOUND THEN -- changed as its of no use
698 IF l_backed_out_date is not null THEN
699 hr_utility.set_location('bckdt%found', 987);
700 -- if bckdt out dt is greater than epsd, make it the epsd
701 IF l_backed_out_date > p_rec_enrt_perd_strt_dt THEN
702 p_rec_enrt_perd_strt_dt := l_backed_out_date;
703 hr_utility.set_location('l_backed_out_date substituted', 987);
704 END IF;
705 END IF;
706 CLOSE c_backed_out_ler;
707 -- if cd is 4, compare epsd with sysdate
708 ELSIF p_enrt_perd_det_ovrlp_bckdt_cd = 'L_EPSD_PEESD_SYSDT' THEN
709 hr_utility.set_location('L_EPSD_PEESD_SYSDT entered', 987);
710 -- if sysdate is lis greater than epsd, make it the epsd
711 IF l_sysdate > p_rec_enrt_perd_strt_dt THEN
712 p_rec_enrt_perd_strt_dt := l_sysdate;
713 --
714 if g_debug then
715 hr_utility.set_location('sysdate substituted', 987);
716 end if;
717 END IF;
718 END IF;
719 -- end 2746865
720 END IF;
721 end enrt_perd_strt_dt;
722 END ben_enrolment_requirements1;
[Home] [Help]