[Home] [Help]
204: --
205: cursor c_get_lf_evt_ocrd_dt
206: is
207: select pil.lf_evt_ocrd_dt
208: from ben_per_in_ler pil
209: where pil.per_in_ler_id = p_per_in_ler_id
210: and pil.business_group_id = p_business_group_id;
211: --
212: begin
336: ,p_effective_date in date
337: ,p_business_group_id in number) return date is
338: --
339: l_proc varchar2(80) := g_package||'.get_dsblty_elig_perd_end_dt';
340: l_dsblty_ler_id ben_per_in_ler.ler_id%type;
341: l_dsblty_elig_perd_end_dt date default null;
342: --
343: cursor c_get_dsblty_ler
344: is
447: select peo.*
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
469: cursor c_get_quald_bnf is
470: select cqb.*
471: from ben_cbr_quald_bnf cqb
472: ,ben_cbr_per_in_ler crp
473: ,ben_per_in_ler pil
474: where cqb.quald_bnf_person_id = p_person_id
475: and cqb.business_group_id = p_business_group_id
476: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
477: and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
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'
533: and p_effective_date between
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
543: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
547: and crp.business_group_id = ler.business_group_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
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
572: and pil.per_in_ler_id = crp.per_in_ler_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'
1414: and p_effective_date
1420: cursor c_get_quald_bnf is
1421: select cqb.*
1422: from ben_cbr_quald_bnf cqb
1423: ,ben_cbr_per_in_ler crp
1424: ,ben_per_in_ler pil
1425: where cqb.quald_bnf_person_id = p_person_id
1426: and cqb.business_group_id = p_business_group_id
1427: and p_lf_evt_ocrd_dt
1428: between cqb.cbr_elig_perd_strt_dt
1752: ,p_effective_date in date
1753: ,p_prtt_enrt_rslt_id in number
1754: ,p_validate in boolean default false) is
1755: --
1756: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
1757: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
1758: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1759: l_dsbld_apls boolean;
1760: l_update boolean;
1759: l_dsbld_apls boolean;
1760: l_update boolean;
1761: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1762: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1763: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1764: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1765: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1766: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
1767: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1766: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
1767: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1768: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1769:
1770: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1771: l_exists varchar2(1);
1772: l_init_evt boolean := false;
1773: l_pgm_id ben_pgm_f.pgm_id%type;
1774: l_ptip_id ben_ptip_f.ptip_id%type;
1783: is
1784: select cqb.*
1785: from ben_cbr_quald_bnf cqb
1786: ,ben_cbr_per_in_ler crp
1787: ,ben_per_in_ler pil
1788: where cqb.quald_bnf_person_id = p_person_id
1789: and nvl(cqb.cbr_elig_perd_end_dt,l_lf_evt_ocrd_dt) >= l_lf_evt_ocrd_dt
1790: and cqb.pgm_id = l_pgm_id
1791: and nvl(cqb.ptip_id,l_ptip_id) = l_ptip_id
1811: is
1812: select cqb.*, crp.per_in_ler_id
1813: from ben_cbr_quald_bnf cqb
1814: ,ben_cbr_per_in_ler crp
1815: ,ben_per_in_ler pil
1816: where cqb.quald_bnf_person_id = l_enrld_person_id
1817: and cqb.cbr_elig_perd_end_dt > l_lf_evt_ocrd_dt
1818: and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
1819: and cqb.business_group_id = p_business_group_id
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'
1844: and p_effective_date
2274: ,p_business_group_id in number
2275: ,p_effective_date in date
2276: ,p_validate in boolean default false) is
2277: --
2278: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2279: l_update boolean := false;
2280: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2281: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2282: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2293: is
2294: select cqb.*
2295: from ben_cbr_quald_bnf cqb
2296: ,ben_cbr_per_in_ler crp
2297: ,ben_per_in_ler pil
2298: where cqb.quald_bnf_person_id = p_quald_bnf_person_id
2299: and cqb.quald_bnf_flag = 'Y'
2300: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2301: and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2511: ,p_ptip_id in number default null
2512: ,p_pl_typ_id in number default null
2513: ,p_validate in boolean default false) is
2514: --
2515: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2516: l_proc varchar2(80) := g_package||'.update_cobra_info';
2517: l_exists varchar2(1);
2518: l_init_evt boolean := false;
2519: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
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
2565: ler.effective_start_date and ler.effective_end_date
2574: or (crp.per_in_ler_id
2575: in (select distinct crp2.per_in_ler_id
2576: from ben_cbr_per_in_ler crp2
2577: ,ben_cbr_quald_bnf cqb
2578: ,ben_per_in_ler pil
2579: where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2580: and cqb.cbr_quald_bnf_id = crp2.cbr_quald_bnf_id
2581: and crp2.per_in_ler_id = pil.per_in_ler_id
2582: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2589: --
2590: cursor c_get_cnt_num(p_cbr_quald_bnf_id in number) is
2591: select max(crp.cnt_num)
2592: from ben_cbr_per_in_ler crp
2593: ,ben_per_in_ler pil
2594: where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
2595: and crp.business_group_id = p_business_group_id
2596: and crp.per_in_ler_id = pil.per_in_ler_id
2597: and crp.business_group_id = pil.business_group_id
2600: cursor c_get_all_quald_dpnts is
2601: select cqb.*
2602: from ben_cbr_quald_bnf cqb
2603: ,ben_cbr_per_in_ler crp
2604: ,ben_per_in_ler pil
2605: where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2606: and cqb.quald_bnf_person_id <> p_person_id
2607: and cqb.quald_bnf_flag = 'Y'
2608: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2629: cursor c_get_cvrd_emp_pil(p_cvrd_emp_person_id in number) is
2630: select crp.*
2631: from ben_cbr_quald_bnf cqb
2632: ,ben_cbr_per_in_ler crp
2633: ,ben_per_in_ler pil
2634: where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2635: and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
2636: and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
2637: and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
2649: cursor c_chk_init_evt is
2650: select null
2651: from ben_cbr_quald_bnf cqb
2652: ,ben_cbr_per_in_ler crp
2653: ,ben_per_in_ler pil
2654: where cqb.quald_bnf_person_id = p_person_id
2655: and cqb.quald_bnf_flag = 'Y'
2656: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2657: and cqb.business_group_id = p_business_group_id
2670: cursor c_get_quald_bnf is
2671: select cqb.*
2672: from ben_cbr_quald_bnf cqb
2673: ,ben_cbr_per_in_ler crp
2674: ,ben_per_in_ler pil
2675: where cqb.quald_bnf_person_id = p_person_id
2676: and cqb.business_group_id = p_business_group_id
2677: /* and p_lf_evt_ocrd_dt
2678: between cqb.cbr_elig_perd_strt_dt
3052: ,p_effective_date in date
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;
3071: is
3072: select cqb.*
3073: from ben_cbr_quald_bnf cqb
3074: ,ben_cbr_per_in_ler crp
3075: ,ben_per_in_ler pil
3076: where cqb.quald_bnf_person_id = p_person_id
3077: and cqb.quald_bnf_flag = 'Y'
3078: and cqb.cbr_elig_perd_end_dt >= l_effective_date
3079: and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
3090: is
3091: select cqb.*
3092: from ben_cbr_quald_bnf cqb
3093: ,ben_cbr_per_in_ler crp
3094: ,ben_per_in_ler pil
3095: where cqb.quald_bnf_person_id = p_person_id
3096: and cqb.quald_bnf_flag = 'Y'
3097: and cqb.cbr_elig_perd_end_dt >= l_effective_date
3098: and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
3107: --
3108: cursor c_get_cnt_num is
3109: select max(crp.cnt_num)
3110: from ben_cbr_per_in_ler crp
3111: ,ben_per_in_ler pil
3112: where crp.cbr_quald_bnf_id = l_cbr_quald_bnf_id
3113: and crp.business_group_id = p_business_group_id
3114: and crp.per_in_ler_id = pil.per_in_ler_id
3115: and crp.business_group_id = pil.business_group_id
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
3125: and ler.business_group_id = pil.business_group_id
3341: ,p_business_group_id in number
3342: ,p_effective_date in date
3343: ,p_validate in boolean default false) is
3344: --
3345: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
3346: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3347: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
3348: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3349: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
3352: --
3353: cursor c_get_cnt_num is
3354: select max(crp.cnt_num)
3355: from ben_cbr_per_in_ler crp
3356: ,ben_per_in_ler pil
3357: where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3358: and crp.business_group_id = p_business_group_id
3359: and crp.per_in_ler_id = pil.per_in_ler_id
3360: and crp.business_group_id = pil.business_group_id