[Home] [Help]
PACKAGE BODY: APPS.HRI_APL_DGNSTC_OPEN_ENRT
Source
1 PACKAGE BODY hri_apl_dgnstc_open_enrt AS
2 /* $Header: hriadgoe.pkb 120.1 2005/11/16 08:53:45 nhunur noship $ */
3 --
4 FUNCTION get_pgm_for_open_le
5 RETURN VARCHAR2
6 IS
7 --
8 l_sql_stmt VARCHAR2 (32000);
9 --
10 BEGIN
11 --
12 l_sql_stmt :=
13 '
14 SELECT pgm.NAME,
15 pgm.effective_start_date,
16 pgm.effective_end_date,
17 meaning status,
18 NULL col5
19 FROM ben_pgm_f pgm, hr_lookups hl
20 WHERE hl.lookup_code = pgm.pgm_stat_cd
21 AND hl.lookup_type = ''BEN_STAT''
22 AND :p_end_date BETWEEN pgm.effective_start_date
23 AND pgm.effective_end_date
24 AND pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
25 AND EXISTS (
26 SELECT 1
27 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
28 WHERE pet.pgm_id = pgm.pgm_id
29 AND pet.enrt_typ_cycl_cd = ''O''
30 AND :p_end_date BETWEEN pet.effective_start_date
31 AND pet.effective_end_date
32 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
33 ORDER BY pgm.name
34 ';
35 --
36 RETURN l_sql_stmt;
37 --
38 END get_pgm_for_open_le;
39 --
40 FUNCTION get_actn_itm_for_open_le
41 RETURN VARCHAR2
42 IS
43 --
44 l_sql_stmt VARCHAR2 (32000);
45 --
46 BEGIN
47 --
48 l_sql_stmt :=
49 '
50 SELECT pgm.NAME pgm_name,
51 pgm.effective_start_date,
52 pgm.effective_end_date,
53 eat.NAME, NULL col5
54 FROM ben_pgm_f pgm, ben_popl_actn_typ_f pat, ben_actn_typ_tl eat
55 WHERE pgm.pgm_id = pat.pgm_id
56 AND eat.actn_typ_id = pat.actn_typ_id
57 AND eat.LANGUAGE = USERENV (''LANG'')
58 AND :p_end_date BETWEEN pgm.effective_start_date
59 AND pgm.effective_end_date
60 AND :p_end_date BETWEEN pat.effective_start_date
61 AND pat.effective_end_date
62 AND EXISTS (
63 SELECT 1
64 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
65 WHERE pet.pgm_id = pgm.pgm_id
66 AND pet.enrt_typ_cycl_cd = ''O''
67 AND :p_end_date BETWEEN pet.effective_start_date
68 AND pet.effective_end_date
69 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
70 UNION
71 SELECT pgm.NAME pgm_name,
72 pgm.effective_start_date,
73 pgm.effective_end_date,
74 eat.NAME,
75 NULL col5
76 FROM ben_pgm_f pgm,
77 ben_popl_actn_typ_f pat,
78 ben_actn_typ_tl eat,
79 ben_plip_f cpp
80 WHERE pgm.pgm_id = cpp.pgm_id
81 AND cpp.pl_id = pat.pl_id
82 AND eat.actn_typ_id = pat.actn_typ_id
83 AND :p_end_date BETWEEN pgm.effective_start_date
84 AND pgm.effective_end_date
85 AND :p_end_date BETWEEN pat.effective_start_date
86 AND pat.effective_end_date
87 AND eat.LANGUAGE = USERENV (''LANG'')
88 AND EXISTS (
89 SELECT 1
90 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
91 WHERE pet.pgm_id = pgm.pgm_id
92 AND pet.enrt_typ_cycl_cd = ''O''
93 AND :p_end_date BETWEEN pet.effective_start_date
94 AND pet.effective_end_date
95 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
96 ';
97 --
98 RETURN l_sql_stmt;
99 --
100 END get_actn_itm_for_open_le;
101 --
102 FUNCTION get_ben_user_valid_setup
103 RETURN VARCHAR2
104 IS
105 --
106 l_sql_stmt VARCHAR2 (32000);
107 --
108 BEGIN
109 --
110 l_sql_stmt :=
111 '
112 SELECT usr.user_name,
113 usr.start_date,
114 NULL col3,
115 NULL col4,
116 NULL col5
117 FROM fnd_user usr,
118 wf_user_role_assignments waur,
119 wf_local_roles wlr,
120 fnd_responsibility resp
121 WHERE resp.responsibility_id = wlr.orig_system_id
122 AND resp.responsibility_key = ''HRI_BEN_BENEFITS_MANAGER''
123 AND wlr.orig_system = ''FND_RESP''
124 AND usr.user_name = waur.user_name
125 AND waur.role_name = wlr.NAME
126 AND :p_end_date BETWEEN usr.start_date and nvl(usr.end_date,:p_end_date)
127 ORDER BY 1
128 ';
129 --
130 RETURN l_sql_stmt;
131 --
132 END get_ben_user_valid_setup;
133 --
134 FUNCTION get_pgm_witn_no_elctbl_chc
135 RETURN VARCHAR2
136 IS
137 --
138 l_sql_stmt VARCHAR2 (32000);
139 --
140 BEGIN
141 --
142 l_sql_stmt :=
143 '
144 SELECT pgm.name,
145 pgm.effective_start_Date,
146 pgm.effective_end_date,
147 NULL col4,
148 NULL col5
149 FROM ben_pgm_f pgm, ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
150 WHERE pgm.pgm_id = pet.pgm_id
151 AND pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
152 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
153 AND pet.enrt_typ_cycl_cd = ''O''
154 AND (enp.enrt_perd_id, enp.asnd_lf_evt_dt, enp.asnd_lf_evt_dt) IN (
155 SELECT enp_inn.enrt_perd_id, enp_inn.asnd_lf_evt_dt,
156 MAX (asnd_lf_evt_dt) OVER ()
157 FROM ben_enrt_perd enp_inn
158 WHERE enp_inn.strt_dt <= :p_end_date
159 AND enp_inn.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id)
160 AND :p_end_date BETWEEN pgm.effective_start_date
161 AND pgm.effective_end_date
162 AND :p_end_date BETWEEN pet.effective_start_date
163 AND pet.effective_end_date
164 AND NOT EXISTS (
165 SELECT 1
166 FROM ben_pil_elctbl_chc_popl pel, ben_elig_per_elctbl_chc epe
167 WHERE pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
168 AND epe.elctbl_flag = ''Y''
169 AND pel.PIL_ELCTBL_POPL_STAT_CD <> ''BCKDT''
170 AND pel.pgm_id = pgm.pgm_id
171 AND pel.enrt_perd_id = enp.enrt_perd_id)
172 ORDER BY PGM.NAME
173 ';
174 --
175 RETURN l_sql_stmt;
176 --
177 END get_pgm_witn_no_elctbl_chc;
178 --
179 --
180 FUNCTION get_pgm_with_no_actn_item
181 RETURN VARCHAR2
182 IS
183 l_sql_stmt VARCHAR2 (32000);
184 BEGIN
185 l_sql_stmt :=
186 '
187 SELECT pgm.name,
188 pgm.effective_start_Date,
189 pgm.effective_end_date,
190 NULL col4,
191 NULL col5
192 FROM ben_pgm_f pgm
193 WHERE NOT EXISTS (
194 SELECT 1
195 FROM ben_popl_actn_typ_f pat
196 WHERE pat.pgm_id = pgm.pgm_id
197 AND :p_end_date BETWEEN pat.effective_start_date
198 AND pat.effective_end_date)
199 AND NOT EXISTS (
200 SELECT 1
201 FROM ben_popl_actn_typ_f pat, ben_plip_f cpp
202 WHERE cpp.pgm_id = pgm.pgm_id
203 AND pat.pl_id = cpp.pl_id
204 AND :p_end_date BETWEEN pat.effective_start_date
205 AND pat.effective_end_date
206 AND :p_end_date BETWEEN cpp.effective_start_date
207 AND cpp.effective_end_date)
208 AND EXISTS (
209 SELECT 1
210 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
211 WHERE pet.pgm_id = pgm.pgm_id
212 AND pet.enrt_typ_cycl_cd = ''O''
213 AND :p_end_date BETWEEN pet.effective_start_date
214 AND pet.effective_end_date
215 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
216 AND pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
217 AND :p_end_date BETWEEN pgm.effective_start_date
218 AND pgm.effective_end_Date
219 ORDER BY pgm.name
220 ';
221 --
222 RETURN l_sql_stmt;
223 --
224 END get_pgm_with_no_actn_item;
225
226 --
227 FUNCTION get_emp_with_open_mnl_ler
228 RETURN VARCHAR2
229 IS
230 l_sql_stmt VARCHAR2 (32000);
231 BEGIN
232 l_sql_stmt :=
233 '
234 SELECT per.full_name,
235 per.employee_number,
236 ppl.lf_evt_ocrd_dt,
237 NULL col4,
238 NULL col5
239 FROM per_all_people_f per, ben_ptnl_ler_for_per ppl, ben_ler_f ler
240 WHERE per.person_id = ppl.person_id
241 AND ppl.ptnl_ler_for_per_stat_cd IN (''MNL'', ''MNLO'')
242 AND ppl.ler_id = ler.ler_id
243 AND ler.typ_cd = ''SCHEDDO''
244 AND :p_end_date BETWEEN ler.effective_start_date
245 AND ler.effective_end_date
246 AND :p_end_date BETWEEN per.effective_start_date
247 AND per.effective_end_date
248 ORDER BY per.full_name
249 ';
250 --
251 RETURN l_sql_stmt;
252 --
253 END get_emp_with_open_mnl_ler;
254
255 --
256 FUNCTION get_pln_with_no_opt
257 RETURN VARCHAR2
258 IS
259 l_sql_stmt VARCHAR2 (32000);
260 BEGIN
261 l_sql_stmt :=
262 '
263 SELECT pgm.NAME PGM_NAME,
264 pln.NAME PLN_NAME,
265 NULL col3,
266 NULL col4,
267 NULL col5
268 FROM ben_pgm_f pgm, ben_plip_f cpp, ben_pl_f pln
269 WHERE pgm.pgm_id = cpp.pgm_id
270 AND cpp.pl_id = pln.pl_id
271 AND pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
272 AND pln.invk_flx_cr_pl_flag = ''N''
273 AND pln.imptd_incm_calc_cd IS NULL
274 AND EXISTS (
275 SELECT 1
276 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
277 WHERE pet.pgm_id = pgm.pgm_id
278 AND pet.enrt_typ_cycl_cd = ''O''
279 AND :p_end_date BETWEEN pet.effective_start_date
280 AND pet.effective_end_date
281 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
282 AND NOT EXISTS (
283 SELECT 1
284 FROM ben_oipl_f cop
285 WHERE cop.pl_id = cpp.pl_id
286 AND :p_end_date BETWEEN cop.effective_start_date
287 AND cop.effective_end_date)
288 AND :p_end_date BETWEEN pgm.effective_start_date
289 AND pgm.effective_end_date
290 AND :p_end_date BETWEEN pln.effective_start_date
291 AND pln.effective_end_date
292 AND :p_end_date BETWEEN cpp.effective_start_date
293 AND cpp.effective_end_date
294 ORDER BY PGM.NAME, PLN.NAME
295 ';
296 --
297 RETURN l_sql_stmt;
298 --
299 END get_pln_with_no_opt;
300
301 --
302 FUNCTION pgm_has_pln_with_rqd_actn_item (cv_pgm_id number,
303 cv_end_date date,
304 cv_dpnt_dsgn_lvl_cd varchar2
305 )
306 RETURN VARCHAR2
307 IS
308 --
309 l_plan_found VARCHAR2 (32000);
310 l_dummy VARCHAR2 (1);
311 --
312 CURSOR c_pln
313 IS
314 SELECT null
315 FROM ben_plip_f cpp, ben_pl_f pln
316 WHERE cpp.pgm_id = cv_pgm_id
317 AND cpp.pl_id = pln.pl_id
318 AND pln.invk_flx_cr_pl_flag = 'N'
319 AND pln.imptd_incm_calc_cd IS NULL
320 AND cv_end_date BETWEEN pln.effective_start_date
321 AND pln.effective_end_date
322 AND cv_end_date BETWEEN cpp.effective_start_date
323 AND cpp.effective_end_date
324 AND ( pln.bnf_dsgn_cd = 'R' /* Designate Beneficiary */
325 OR pln.bnf_adrs_rqd_flag = 'Y' /* Beneficiary Requires Address */
326 OR pln.bnf_dob_rqd_flag = 'Y' /* Beneficiary Requires Date of Birth */
327 OR pln.bnf_legv_id_rqd_flag = 'Y' /* Beneficiary Requires Legislative Identifier */
328 OR pln.bnf_dsge_mnr_ttee_rqd_flag = 'Y'/* Beneficiary requires a Trustee */
329 OR ( pln.bnf_ctfn_rqd_flag = 'N' /* Beneficiary Requires Certification */
330 AND EXISTS (
331 SELECT 1
332 FROM ben_pl_bnf_ctfn_f pcx
333 WHERE pcx.pl_id = pln.pl_id
334 AND cv_end_date BETWEEN pcx.effective_start_date
335 AND pcx.effective_end_date)
336 )
337 OR EXISTS /* Participant / Dependent Requires Primary Care Physician */
338 (
339 SELECT 1
340 FROM ben_pl_pcp pcp
341 WHERE pcp.pl_id = pln.pl_id
342 AND ( pcp.pcp_dsgn_cd = 'R'
343 OR pcp.pcp_dpnt_dsgn_cd = 'R'
344 ))
345 OR EXISTS /* Enrollment Certification Required - Plan */
346 (
347 SELECT 1
348 FROM ben_enrt_ctfn_f ecf
349 WHERE ecf.pl_id = pln.pl_id
350 AND cv_end_date BETWEEN ecf.effective_start_date
351 AND ecf.effective_end_date)
352 OR EXISTS /* Enrollment Certification Required - Plan Life Event */
353 (
354 SELECT 1
355 FROM ben_ler_rqrs_enrt_ctfn_f lre,
356 ben_ler_enrt_ctfn_f lnc
357 WHERE lnc.ler_rqrs_enrt_ctfn_id =
358 lre.ler_rqrs_enrt_ctfn_id
359 AND cv_end_date BETWEEN lnc.effective_start_date
360 AND lnc.effective_end_date
361 AND cv_end_date BETWEEN lre.effective_start_date
362 AND lre.effective_end_date
363 AND lre.pl_id = pln.pl_id)
364 OR EXISTS /* Enrollment Certification Required - Option in Plan*/
365 (
366 SELECT 1
367 FROM ben_oipl_f cop, ben_enrt_ctfn_f ecf
368 WHERE ecf.oipl_id = cop.oipl_id
369 AND cv_end_date BETWEEN ecf.effective_start_date
370 AND ecf.effective_end_date
371 AND cv_end_date BETWEEN cop.effective_start_date
372 AND cop.effective_end_date
373 AND cop.pl_id = pln.pl_id)
374 OR EXISTS /* Enrollment Certification Required - Option In Plan Life Event */
375 (
376 SELECT 1
377 FROM ben_oipl_f cop,
378 ben_ler_rqrs_enrt_ctfn_f lre,
379 ben_ler_enrt_ctfn_f lnc
380 WHERE cop.pl_id = pln.pl_id
381 AND cop.oipl_id = lre.oipl_id
382 AND lnc.ler_rqrs_enrt_ctfn_id =
383 lre.ler_rqrs_enrt_ctfn_id
384 AND cv_end_date BETWEEN lnc.effective_start_date
385 AND lnc.effective_end_date
386 AND cv_end_date BETWEEN lre.effective_start_date
387 AND lre.effective_end_date
388 AND cv_end_date BETWEEN cop.effective_start_date
389 AND cop.effective_end_date)
390 OR ( cv_dpnt_dsgn_lvl_cd = 'PL'
391 AND ( pln.dpnt_dsgn_cd = 'R' /* Designate Dependent */
392 OR pln.dpnt_leg_id_rqd_flag = 'Y' /* Dependent Requires Legislative Identifier */
393 OR pln.dpnt_dob_rqd_flag = 'Y' /* Dependent requires Date of Birth */
394 OR pln.dpnt_adrs_rqd_flag = 'Y' /* Dependent Requires Address */
395 OR ( pln.dpnt_no_ctfn_rqd_flag = 'N' /* Dependent Requires Certification */
396 AND EXISTS (
397 SELECT 1
398 FROM ben_pl_dpnt_cvg_ctfn_f pnd
399 WHERE pnd.pl_id = pln.pl_id
400 AND cv_end_date
401 BETWEEN pnd.effective_start_date
402 AND pnd.effective_end_date)
403 )
404 )
405 )
406 );
407 --
408 BEGIN
409 --
410 l_plan_found := 'N';
411 --
412 OPEN c_pln;
413 --
414 FETCH c_pln INTO L_DUMMY;
415 --
416 IF c_pln%found
417 THEN
418 --
419 l_plan_found := 'Y';
420 --
421 ELSE
422 --
423 l_plan_found := 'N';
424 --
425 END IF;
426 --
427 CLOSE c_pln;
428 --
429 RETURN l_plan_found;
430 --
431 END pgm_has_pln_with_rqd_actn_item;
432
433 --
434 FUNCTION get_pgm_with_rqd_actn_item
435 RETURN VARCHAR2
436 IS
437 l_sql_stmt VARCHAR2 (32000);
438 BEGIN
439 l_sql_stmt :=
440 '
441 SELECT name,
442 effective_start_Date,
443 effective_end_date,
444 NULL col4,
445 NULL col5
446 FROM ben_pgm_f pgm
447 WHERE pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
448 AND :p_end_date BETWEEN pgm.effective_start_date AND pgm.effective_end_date
449 AND EXISTS (
450 SELECT 1
451 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
452 WHERE pet.pgm_id = pgm.pgm_id
453 AND pet.enrt_typ_cycl_cd = ''O''
454 AND :p_end_date BETWEEN pet.effective_start_date
455 AND pet.effective_end_date
456 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
457 AND ( ( pgm.dpnt_dsgn_lvl_cd = ''PGM''
458 AND ( pgm.dpnt_dsgn_cd = ''R'' /* Designate Dependent */
459 OR pgm.dpnt_legv_id_rqd_flag = ''Y'' /* Dependent Requires Legislative Identifier */
460 OR pgm.dpnt_dob_rqd_flag = ''Y'' /* Dependent requires Date of Birth */
461 OR pgm.dpnt_adrs_rqd_flag = ''Y'' /* Dependent Requires Address */
462 OR ( pgm.dpnt_dsgn_no_ctfn_rqd_flag = ''N''/* Dependent Requires Certification */
463 AND EXISTS (
464 SELECT 1
465 FROM ben_pgm_dpnt_cvg_ctfn_f pgc
466 WHERE pgc.pgm_id = pgm.pgm_id
467 AND :p_end_date BETWEEN pgc.effective_start_date
468 AND pgc.effective_end_date)
469 )
470 )
471 )
472 OR hri_apl_dgnstc_open_enrt.pgm_has_pln_with_rqd_actn_item (pgm.pgm_id,
473 :p_end_date ,
474 pgm.dpnt_dsgn_lvl_cd) = ''Y''
475 OR EXISTS (
476 SELECT 1
477 FROM ben_ptip_f ctp
478 WHERE ctp.pgm_id = pgm.pgm_id
479 AND :p_end_date BETWEEN ctp.effective_start_date
480 AND ctp.effective_end_date
481 AND pgm.dpnt_dsgn_lvl_cd = ''PTIP''
482 AND ( ctp.dpnt_dsgn_cd = ''R'' /* Designate Dependent */
483 OR ctp.dpnt_legv_id_rqd_flag = ''Y'' /* Dependent Requires Legislative Identifier */
484 OR ctp.dpnt_dob_rqd_flag = ''Y'' /* Dependent requires Date of Birth */
485 OR ctp.dpnt_adrs_rqd_flag = ''Y'' /* Dependent Requires Address */
486 OR ( ctp.dpnt_cvg_no_ctfn_rqd_flag = ''N'' /* Dependent Requires Certification */
487 AND EXISTS (
488 SELECT 1
489 FROM ben_ptip_dpnt_cvg_ctfn_f pyd
490 WHERE pyd.ptip_id = ctp.ptip_id
491 AND :p_end_date BETWEEN pyd.effective_start_date
492 AND pyd.effective_end_date)
493 )
494 ))
495 )
496 ORDER BY PGM.NAME
497 ';
498 --
499 RETURN l_sql_stmt;
500 --
501 END get_pgm_with_rqd_actn_item;
502
503 --
504 FUNCTION get_pgm_with_inactive_status
505 RETURN VARCHAR2
506 IS
507 l_sql_stmt VARCHAR2 (32000);
508 BEGIN
509 l_sql_stmt :=
510 '
511 SELECT pgm.NAME,
512 hl.meaning status,
513 NULL col3,
514 NULL col4,
515 NULL col5
516 FROM ben_pgm_f pgm, ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp, hr_lookups hl
517 WHERE pgm.pgm_id = pet.pgm_id
518 AND pgm.pgm_typ_cd IN (''CORE'', ''FLEX'', ''FPC'', ''OTHER'')
519 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
520 AND pet.enrt_typ_cycl_cd = ''O''
521 AND (enp.enrt_perd_id, enp.asnd_lf_evt_dt, enp.asnd_lf_evt_dt) IN (
522 SELECT enp_inn.enrt_perd_id, enp_inn.asnd_lf_evt_dt,
523 MAX (asnd_lf_evt_dt) OVER ()
524 FROM ben_enrt_perd enp_inn
525 WHERE enp_inn.strt_dt <= :p_end_date
526 AND enp_inn.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id)
527 AND enp.strt_dt BETWEEN pgm.effective_start_date AND pgm.effective_end_date
528 AND pgm.pgm_stat_cd IN (''I'', ''P'', ''C'')
529 AND :p_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
530 and hl.lookup_type = ''BEN_STAT''
531 and hl.lookup_code = pgm.pgm_stat_Cd
532 ORDER BY PGM.NAME
533 ';
534 --
535 RETURN l_sql_stmt;
536 --
537 END get_pgm_with_inactive_status;
538 --
539 END hri_apl_dgnstc_open_enrt;