[Home] [Help]
342: --
343: cursor c_get_dsblty_ler
344: is
345: select ler.ler_id
346: from ben_ler_f ler
347: where ler.typ_cd = 'DSBLTY'
348: and ler.business_group_id = p_business_group_id
349: and ler.qualg_evt_flag = 'Y'
350: and p_lf_evt_ocrd_dt
437: l_dsblty_elig_perd_end_dt date;
438: l_cbr_elig_perd_end_dt date;
439: l_eligible boolean := false;
440: l_exists varchar2(1);
441: l_typ_cd ben_ler_f.typ_cd%type;
442: l_jurisdiction_code varchar2(30);
443: l_outputs ff_exec.outputs_t;
444: l_proc varchar2(72) := g_package||'determine_cobra_elig_dates';
445: --
448: ,ler.typ_cd
449: ,pil.lf_evt_ocrd_dt
450: from ben_elig_to_prte_rsn_f peo
451: ,ben_per_in_ler pil
452: ,ben_ler_f ler
453: where pil.ler_id = peo.ler_id
454: and pil.per_in_ler_id = p_per_in_ler_id
455: and pil.business_group_id = p_business_group_id
456: and peo.business_group_id = pil.business_group_id
524: and effective_end_date;
525: --
526: cursor c_get_enddsblty_ler is
527: select null
528: from ben_ler_f ler
529: ,ben_per_in_ler pil
530: where ler.ler_id = pil.ler_id
531: and pil.per_in_ler_id = p_per_in_ler_id
532: and ler.typ_cd = 'ENDDSBLTY'
534: ler.effective_start_date and ler.effective_end_date;
535: --
536: cursor c_get_prvs_elig_end_dt(p_cbr_quald_bnf_id in number) is
537: select crp.prvs_elig_perd_end_dt
538: from ben_ler_f ler
539: ,ben_per_in_ler pil
540: ,ben_cbr_per_in_ler crp
541: where ler.ler_id = pil.ler_id
542: and pil.per_in_ler_id = crp.per_in_ler_id
548: and ler.typ_cd = 'DSBLTY'
549: and crp.cnt_num = (select max(crp2.cnt_num)
550: from ben_cbr_per_in_ler crp2
551: ,ben_per_in_ler pil2
552: ,ben_ler_f ler2
553: where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
554: and crp2.business_group_id = pil2.business_group_id
555: and crp2.business_group_id = ler2.business_group_id
556: and crp2.business_group_id = p_business_group_id
563: --
564: cursor c_get_init_ler(p_cbr_quald_bnf_id in number) is
565: select peo.*
566: ,pil.lf_evt_ocrd_dt
567: from ben_ler_f ler
568: ,ben_per_in_ler pil
569: ,ben_cbr_per_in_ler crp
570: ,ben_elig_to_prte_rsn_f peo
571: where ler.ler_id = pil.ler_id
1405: order by epe.pgm_id, epe.ptip_id;
1406: --
1407: cursor c_get_ler_info is
1408: select ler.*
1409: from ben_ler_f ler
1410: ,ben_per_in_ler pil
1411: where pil.per_in_ler_id = p_per_in_ler_id
1412: and pil.ler_id = ler.ler_id
1413: and ler.name = 'Assistance Eligible Individual'
1835: -- arra event
1836: --
1837: cursor c_get_ler_info is
1838: select null
1839: from ben_ler_f ler
1840: ,ben_per_in_ler pil
1841: where pil.per_in_ler_id = p_per_in_ler_id
1842: and pil.ler_id = ler.ler_id
1843: and ler.name = 'Assistance Eligible Individual'
2556: and pet.system_person_type = 'PRTN';
2557: --
2558: cursor c_get_qualg_evt is
2559: select null
2560: from ben_ler_f ler
2561: ,ben_per_in_ler pil
2562: where ler.ler_id = pil.ler_id
2563: and pil.per_in_ler_id = p_per_in_ler_id
2564: and p_effective_date between
3053: ,p_validate in boolean default false) is
3054: --
3055: l_update boolean := false;
3056: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
3057: l_typ_cd ben_ler_f.typ_cd%type;
3058: l_cbr_inelg_rsn_cd ben_cbr_quald_bnf.cbr_inelg_rsn_cd%type;
3059: l_effective_date date;
3060: l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
3061: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
3116: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3117: --
3118: cursor c_get_ler_type is
3119: select ler.typ_cd
3120: from ben_ler_f ler
3121: ,ben_per_in_ler pil
3122: where ler.ler_id = pil.ler_id
3123: and pil.per_in_ler_id = p_per_in_ler_id
3124: and ler.business_group_id = p_business_group_id