APPS.BEN_COBRA_REQUIREMENTS dependencies on BEN_CBR_QUALD_BNF

Line 454: from ben_cbr_quald_bnf cqb

450: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
451: --
452: cursor c_get_quald_bnf is
453: select cqb.*
454: from ben_cbr_quald_bnf cqb
455: ,ben_cbr_per_in_ler crp
456: ,ben_per_in_ler pil
457: where cqb.quald_bnf_person_id = p_person_id
458: and cqb.business_group_id = p_business_group_id

Line 1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

1362: l_proc varchar2(80) := g_package||
1363: '.update_cobra_elig_info';
1364: l_ptip_id ben_ptip_f.ptip_id%type;
1365: l_pgm_id ben_pgm_f.pgm_id%type;
1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1363: '.update_cobra_elig_info';
1364: l_ptip_id ben_ptip_f.ptip_id%type;
1365: l_pgm_id ben_pgm_f.pgm_id%type;
1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

Line 1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1364: l_ptip_id ben_ptip_f.ptip_id%type;
1365: l_pgm_id ben_pgm_f.pgm_id%type;
1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1372: l_dsbld_apls boolean;

Line 1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

1365: l_pgm_id ben_pgm_f.pgm_id%type;
1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1372: l_dsbld_apls boolean;
1373: l_update boolean;

Line 1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

1366: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1372: l_dsbld_apls boolean;
1373: l_update boolean;
1374: --

Line 1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

1367: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1368: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1369: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1370: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1371: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1372: l_dsbld_apls boolean;
1373: l_update boolean;
1374: --
1375: cursor c_get_epe is

Line 1671: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

1667: ,p_prtt_enrt_rslt_id in number
1668: ,p_validate in boolean default false) is
1669: --
1670: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
1671: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
1672: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1673: l_dsbld_apls boolean;
1674: l_update boolean;
1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

Line 1672: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

1668: ,p_validate in boolean default false) is
1669: --
1670: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
1671: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
1672: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1673: l_dsbld_apls boolean;
1674: l_update boolean;
1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

Line 1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

1671: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
1672: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1673: l_dsbld_apls boolean;
1674: l_update boolean;
1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1677: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

Line 1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1672: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1673: l_dsbld_apls boolean;
1674: l_update boolean;
1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1677: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1680: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1674: l_update boolean;
1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1677: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1680: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1681:
1682: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;

Line 1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

1675: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1677: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1680: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1681:
1682: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1683: l_exists varchar2(1);

Line 1680: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

1676: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1677: l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1678: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1679: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1680: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1681:
1682: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1683: l_exists varchar2(1);
1684: l_init_evt boolean := false;

Line 1696: from ben_cbr_quald_bnf cqb

1692: --
1693: cursor c_get_cbr_quald_bnf
1694: is
1695: select cqb.*
1696: from ben_cbr_quald_bnf cqb
1697: ,ben_cbr_per_in_ler crp
1698: ,ben_per_in_ler pil
1699: where cqb.quald_bnf_person_id = p_person_id
1700: and nvl(cqb.cbr_elig_perd_end_dt,l_lf_evt_ocrd_dt) >= l_lf_evt_ocrd_dt

Line 1724: from ben_cbr_quald_bnf cqb

1720: --
1721: cursor c_get_cvrd_emp_person_id
1722: is
1723: select cqb.cvrd_emp_person_id
1724: from ben_cbr_quald_bnf cqb
1725: ,ben_cbr_per_in_ler crp
1726: ,ben_per_in_ler pil
1727: where cqb.quald_bnf_person_id = l_enrld_person_id
1728: and cqb.cbr_elig_perd_end_dt > l_lf_evt_ocrd_dt

Line 2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

2120: ,p_validate in boolean default false) is
2121: --
2122: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2123: l_update boolean := false;
2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

Line 2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2121: --
2122: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2123: l_update boolean := false;
2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;

Line 2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2122: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2123: l_update boolean := false;
2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

Line 2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

2123: l_update boolean := false;
2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2131: l_enrld boolean := false;

Line 2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

2124: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2131: l_enrld boolean := false;
2132: l_exists varchar2(1);

Line 2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;

2125: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2131: l_enrld boolean := false;
2132: l_exists varchar2(1);
2133: l_pgm_id ben_pgm_f.pgm_id%type;

Line 2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

2126: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2127: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2128: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2129: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2130: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2131: l_enrld boolean := false;
2132: l_exists varchar2(1);
2133: l_pgm_id ben_pgm_f.pgm_id%type;
2134: l_proc varchar2(80) := g_package||'.chk_cobra_eligibility';

Line 2139: from ben_cbr_quald_bnf cqb

2135: --
2136: cursor c_get_cbr_quald_bnf(p_quald_bnf_person_id in number)
2137: is
2138: select cqb.*
2139: from ben_cbr_quald_bnf cqb
2140: ,ben_cbr_per_in_ler crp
2141: ,ben_per_in_ler pil
2142: where cqb.quald_bnf_person_id = p_quald_bnf_person_id
2143: and cqb.quald_bnf_flag = 'Y'

Line 2339: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

2335: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2336: l_proc varchar2(80) := g_package||'.update_cobra_info';
2337: l_exists varchar2(1);
2338: l_init_evt boolean := false;
2339: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2340: l_dsbld_apls boolean;
2341: l_update boolean;
2342: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2343: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

Line 2342: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

2338: l_init_evt boolean := false;
2339: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2340: l_dsbld_apls boolean;
2341: l_update boolean;
2342: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2343: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2344: l_cvrd_emp_end_date date;
2345: l_cbr_elig_perd_end_dt date;
2346: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;

Line 2343: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

2339: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2340: l_dsbld_apls boolean;
2341: l_update boolean;
2342: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2343: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2344: l_cvrd_emp_end_date date;
2345: l_cbr_elig_perd_end_dt date;
2346: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2347: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;

Line 2350: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2346: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2347: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2348: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2349: l_cqb_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2350: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2351: --
2352: cursor c_chk_cvrd_emp is
2353: select null
2354: from per_person_type_usages_f ptu

Line 2397: ,ben_cbr_quald_bnf cqb

2393: where (crp.per_in_ler_id = p_per_in_ler_id
2394: or (crp.per_in_ler_id
2395: in (select distinct crp2.per_in_ler_id
2396: from ben_cbr_per_in_ler crp2
2397: ,ben_cbr_quald_bnf cqb
2398: ,ben_per_in_ler pil
2399: where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2400: and cqb.cbr_quald_bnf_id = crp2.cbr_quald_bnf_id
2401: and crp2.per_in_ler_id = pil.per_in_ler_id

Line 2422: from ben_cbr_quald_bnf cqb

2418: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2419: --
2420: cursor c_get_all_quald_dpnts is
2421: select cqb.*
2422: from ben_cbr_quald_bnf cqb
2423: ,ben_cbr_per_in_ler crp
2424: ,ben_per_in_ler pil
2425: where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2426: and cqb.quald_bnf_person_id <> p_person_id

Line 2441: from ben_cbr_quald_bnf cqb

2437: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2438: --
2439: cursor c_get_cvrd_emp_end_date is
2440: select cbr_elig_perd_end_dt
2441: from ben_cbr_quald_bnf cqb
2442: where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2443: and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
2444: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2445: and cqb.business_group_id = p_business_group_id

Line 2451: from ben_cbr_quald_bnf cqb

2447: and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1);
2448: --
2449: cursor c_get_cvrd_emp_pil(p_cvrd_emp_person_id in number) is
2450: select crp.*
2451: from ben_cbr_quald_bnf cqb
2452: ,ben_cbr_per_in_ler crp
2453: ,ben_per_in_ler pil
2454: where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2455: and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id

Line 2526: ben_cbr_quald_bnf_api.create_cbr_quald_bnf

2522: --
2523: hr_utility.set_location('Inserting quald bnf: ' || l_proc, 10);
2524: hr_utility.set_location('l_cbr_elig_perd_end_dt: ' || l_cbr_elig_perd_end_dt, 10);
2525: --
2526: ben_cbr_quald_bnf_api.create_cbr_quald_bnf
2527: (p_validate => p_validate
2528: ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2529: ,p_quald_bnf_flag => p_quald_bnf_flag
2530: ,p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt

Line 2566: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2562: then
2563: --
2564: l_object_version_number := l_dpnt_rec.object_version_number;
2565: --
2566: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2567: (p_validate => p_validate
2568: ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2569: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2570: ,p_business_group_id => p_business_group_id

Line 2665: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2661: else
2662: l_quald_bnf_flag := p_quald_bnf_flag;
2663: end if;
2664: --
2665: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2666: (p_validate => p_validate
2667: ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
2668: ,p_quald_bnf_flag => l_quald_bnf_flag
2669: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt

Line 2685: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2681: hr_utility.set_location('Dpnt dsbld : ' || l_cbr_elig_perd_end_dt, 10);
2682: --
2683: l_object_version_number := l_dpnt_rec.object_version_number;
2684: --
2685: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2686: (p_validate => p_validate
2687: ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2688: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2689: ,p_business_group_id => p_business_group_id

Line 2789: l_cbr_inelg_rsn_cd ben_cbr_quald_bnf.cbr_inelg_rsn_cd%type;

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;
2793: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;

Line 2792: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%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;
2793: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2794: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2795: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2796: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 2794: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%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;
2793: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2794: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2795: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2796: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2797: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
2798: l_exists varchar2(1);

Line 2795: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

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;
2793: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2794: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2795: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2796: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2797: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
2798: l_exists varchar2(1);
2799: l_proc varchar2(80) := g_package||'.end_prtt_cobra_eligibility';

Line 2796: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2792: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2793: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2794: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2795: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2796: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2797: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
2798: l_exists varchar2(1);
2799: l_proc varchar2(80) := g_package||'.end_prtt_cobra_eligibility';
2800: --

Line 2804: from ben_cbr_quald_bnf cqb

2800: --
2801: cursor c_get_cbr_quald_bnf
2802: is
2803: select cqb.*
2804: from ben_cbr_quald_bnf cqb
2805: ,ben_cbr_per_in_ler crp
2806: ,ben_per_in_ler pil
2807: where cqb.quald_bnf_person_id = p_person_id
2808: and cqb.quald_bnf_flag = 'Y'

Line 2987: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2983: ,p_effective_date in date
2984: ,p_validate in boolean default false) is
2985: --
2986: l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2987: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2988: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2989: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2990: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2991: l_exists varchar2(1);

Line 3049: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

3045: l_cqb_object_version_number := p_cqb_object_version_number;
3046: --
3047: hr_utility.set_location('p_cbr_elig_perd_end_dt'||p_cbr_elig_perd_end_dt, 20);
3048: hr_utility.set_location('p_quald_bnf_flag'||p_quald_bnf_flag, 30);
3049: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
3050: (p_validate => p_validate
3051: ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
3052: ,p_quald_bnf_flag => p_quald_bnf_flag
3053: ,p_cbr_elig_perd_end_dt => p_cbr_elig_perd_end_dt