[Home] [Help]
325: --
326: cursor c_get_dsblty_ler
327: is
328: select ler.ler_id
329: from ben_ler_f ler
330: where ler.typ_cd = 'DSBLTY'
331: and ler.business_group_id = p_business_group_id
332: and ler.qualg_evt_flag = 'Y'
333: and p_lf_evt_ocrd_dt
420: l_dsblty_elig_perd_end_dt date;
421: l_cbr_elig_perd_end_dt date;
422: l_eligible boolean := false;
423: l_exists varchar2(1);
424: l_typ_cd ben_ler_f.typ_cd%type;
425: l_jurisdiction_code varchar2(30);
426: l_outputs ff_exec.outputs_t;
427: l_proc varchar2(72) := g_package||'determine_cobra_elig_dates';
428: --
431: ,ler.typ_cd
432: ,pil.lf_evt_ocrd_dt
433: from ben_elig_to_prte_rsn_f peo
434: ,ben_per_in_ler pil
435: ,ben_ler_f ler
436: where pil.ler_id = peo.ler_id
437: and pil.per_in_ler_id = p_per_in_ler_id
438: and pil.business_group_id = p_business_group_id
439: and peo.business_group_id = pil.business_group_id
506: and effective_end_date;
507: --
508: cursor c_get_enddsblty_ler is
509: select null
510: from ben_ler_f ler
511: ,ben_per_in_ler pil
512: where ler.ler_id = pil.ler_id
513: and pil.per_in_ler_id = p_per_in_ler_id
514: and ler.typ_cd = 'ENDDSBLTY'
516: ler.effective_start_date and ler.effective_end_date;
517: --
518: cursor c_get_prvs_elig_end_dt(p_cbr_quald_bnf_id in number) is
519: select crp.prvs_elig_perd_end_dt
520: from ben_ler_f ler
521: ,ben_per_in_ler pil
522: ,ben_cbr_per_in_ler crp
523: where ler.ler_id = pil.ler_id
524: and pil.per_in_ler_id = crp.per_in_ler_id
530: and ler.typ_cd = 'DSBLTY'
531: and crp.cnt_num = (select max(crp2.cnt_num)
532: from ben_cbr_per_in_ler crp2
533: ,ben_per_in_ler pil2
534: ,ben_ler_f ler2
535: where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
536: and crp2.business_group_id = pil2.business_group_id
537: and crp2.business_group_id = ler2.business_group_id
538: and crp2.business_group_id = p_business_group_id
545: --
546: cursor c_get_init_ler(p_cbr_quald_bnf_id in number) is
547: select peo.*
548: ,pil.lf_evt_ocrd_dt
549: from ben_ler_f ler
550: ,ben_per_in_ler pil
551: ,ben_cbr_per_in_ler crp
552: ,ben_elig_to_prte_rsn_f peo
553: where ler.ler_id = pil.ler_id
2376: and pet.system_person_type = 'PRTN';
2377: --
2378: cursor c_get_qualg_evt is
2379: select null
2380: from ben_ler_f ler
2381: ,ben_per_in_ler pil
2382: where ler.ler_id = pil.ler_id
2383: and pil.per_in_ler_id = p_per_in_ler_id
2384: and p_effective_date between
2784: ,p_validate in boolean default false) is
2785: --
2786: l_update boolean := false;
2787: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
2788: l_typ_cd ben_ler_f.typ_cd%type;
2789: l_cbr_inelg_rsn_cd ben_cbr_quald_bnf.cbr_inelg_rsn_cd%type;
2790: l_effective_date date;
2791: l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
2792: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2826: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2827: --
2828: cursor c_get_ler_type is
2829: select ler.typ_cd
2830: from ben_ler_f ler
2831: ,ben_per_in_ler pil
2832: where ler.ler_id = pil.ler_id
2833: and pil.per_in_ler_id = p_per_in_ler_id
2834: and ler.business_group_id = p_business_group_id