eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_COBRA_REQUIREMENTS dependencies on BEN_CBR_QUALD_BNF

Line 471: from ben_cbr_quald_bnf cqb

467: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
468: --
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

Line 1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

1380: l_proc varchar2(80) := g_package||
1381: '.update_cobra_elig_info';
1382: l_ptip_id ben_ptip_f.ptip_id%type;
1383: l_pgm_id ben_pgm_f.pgm_id%type;
1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1381: '.update_cobra_elig_info';
1382: l_ptip_id ben_ptip_f.ptip_id%type;
1383: l_pgm_id ben_pgm_f.pgm_id%type;
1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

Line 1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

1382: l_ptip_id ben_ptip_f.ptip_id%type;
1383: l_pgm_id ben_pgm_f.pgm_id%type;
1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1390: l_dsbld_apls boolean;

Line 1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

1383: l_pgm_id ben_pgm_f.pgm_id%type;
1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1390: l_dsbld_apls boolean;
1391: l_update boolean;

Line 1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

1384: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1390: l_dsbld_apls boolean;
1391: l_update boolean;
1392: l_arra_event boolean;

Line 1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

1385: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1390: l_dsbld_apls boolean;
1391: l_update boolean;
1392: l_arra_event boolean;
1393: --

Line 1422: from ben_cbr_quald_bnf cqb

1418: l_ler_rec c_get_ler_info%rowtype;
1419: --
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

Line 1462: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

1458: -- person is no longer a COBRA qualified beneficiary.
1459: --
1460: l_cqb_object_version_number := l_cqb_rec.object_version_number;
1461: --
1462: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
1463: (p_cbr_quald_bnf_id=> l_cqb_rec.cbr_quald_bnf_id
1464: ,p_quald_bnf_flag => 'Y'
1465: ,p_business_group_id => p_business_group_id
1466: ,p_object_version_number => l_cqb_object_version_number

Line 1757: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

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;
1761: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

Line 1758: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

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;
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;

Line 1761: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_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;
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;

Line 1762: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%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;
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;

Line 1764: l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

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;
1768: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 1765: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

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;
1768: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1769:

Line 1767: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%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;
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);

Line 1768: l_object_version_number ben_cbr_quald_bnf.object_version_number%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;
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;

Line 1785: from ben_cbr_quald_bnf cqb

1781: --
1782: cursor c_get_cbr_quald_bnf
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

Line 1813: from ben_cbr_quald_bnf cqb

1809: --
1810: cursor c_get_cvrd_emp_person_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

Line 2019: ben_cbr_quald_bnf_api.create_cbr_quald_bnf

2015: and l_quald_bnf_flag = 'N') then
2016: --
2017: -- Write a qualified beneficiary row for the dependent
2018: --
2019: ben_cbr_quald_bnf_api.create_cbr_quald_bnf
2020: (p_validate => p_validate
2021: ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2022: ,p_quald_bnf_flag => l_cvrd_emp_rec.quald_bnf_flag
2023: ,p_cbr_elig_perd_strt_dt => l_cvrd_emp_rec.cbr_elig_perd_strt_dt

Line 2280: l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;

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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

Line 2281: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;

Line 2282: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

Line 2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2287: l_enrld boolean := false;

Line 2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2287: l_enrld boolean := false;
2288: l_exists varchar2(1);

Line 2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%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;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2287: l_enrld boolean := false;
2288: l_exists varchar2(1);
2289: l_pgm_id ben_pgm_f.pgm_id%type;

Line 2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;

2282: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2283: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285: l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286: l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2287: l_enrld boolean := false;
2288: l_exists varchar2(1);
2289: l_pgm_id ben_pgm_f.pgm_id%type;
2290: l_proc varchar2(80) := g_package||'.chk_cobra_eligibility';

Line 2295: from ben_cbr_quald_bnf cqb

2291: --
2292: cursor c_get_cbr_quald_bnf(p_quald_bnf_person_id in number)
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'

Line 2519: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;

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;
2520: l_dsbld_apls boolean;
2521: l_update boolean;
2522: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2523: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

Line 2522: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;

2518: l_init_evt boolean := false;
2519: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2520: l_dsbld_apls boolean;
2521: l_update boolean;
2522: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2523: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2524: l_cvrd_emp_end_date date;
2525: l_cbr_elig_perd_end_dt date;
2526: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;

Line 2523: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

2519: l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2520: l_dsbld_apls boolean;
2521: l_update boolean;
2522: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2523: l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2524: l_cvrd_emp_end_date date;
2525: l_cbr_elig_perd_end_dt date;
2526: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2527: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;

Line 2530: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;

2526: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2527: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2528: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2529: l_cqb_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2530: l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2531: --
2532: cursor c_chk_cvrd_emp is
2533: select null
2534: from per_person_type_usages_f ptu

Line 2577: ,ben_cbr_quald_bnf cqb

2573: where (crp.per_in_ler_id = p_per_in_ler_id
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

Line 2602: from ben_cbr_quald_bnf cqb

2598: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2599: --
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

Line 2621: from ben_cbr_quald_bnf cqb

2617: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2618: --
2619: cursor c_get_cvrd_emp_end_date is
2620: select cbr_elig_perd_end_dt
2621: from ben_cbr_quald_bnf cqb
2622: where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2623: and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
2624: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2625: and cqb.business_group_id = p_business_group_id

Line 2631: from ben_cbr_quald_bnf cqb

2627: and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1);
2628: --
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

Line 2651: from ben_cbr_quald_bnf cqb

2647: -- 9294528
2648: --
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'

Line 2672: from ben_cbr_quald_bnf cqb

2668: -- 9690964
2669: --
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

Line 2785: ben_cbr_quald_bnf_api.create_cbr_quald_bnf

2781: --
2782: hr_utility.set_location('Inserting quald bnf: ' || l_proc, 10);
2783: hr_utility.set_location('l_cbr_elig_perd_end_dt: ' || l_cbr_elig_perd_end_dt, 10);
2784: --
2785: ben_cbr_quald_bnf_api.create_cbr_quald_bnf
2786: (p_validate => p_validate
2787: ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2788: ,p_quald_bnf_flag => p_quald_bnf_flag
2789: ,p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt

Line 2825: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2821: then
2822: --
2823: l_object_version_number := l_dpnt_rec.object_version_number;
2824: --
2825: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2826: (p_validate => p_validate
2827: ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2828: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2829: ,p_business_group_id => p_business_group_id

Line 2934: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2930: else
2931: l_quald_bnf_flag := p_quald_bnf_flag;
2932: end if;
2933: --
2934: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2935: (p_validate => p_validate
2936: ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
2937: ,p_quald_bnf_flag => l_quald_bnf_flag
2938: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt

Line 2954: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

2950: hr_utility.set_location('Dpnt dsbld : ' || l_cbr_elig_perd_end_dt, 10);
2951: --
2952: l_object_version_number := l_dpnt_rec.object_version_number;
2953: --
2954: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2955: (p_validate => p_validate
2956: ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2957: ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2958: ,p_business_group_id => p_business_group_id

Line 3058: l_cbr_inelg_rsn_cd ben_cbr_quald_bnf.cbr_inelg_rsn_cd%type;

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

Line 3061: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%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;
3062: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3063: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3064: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
3065: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

Line 3063: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%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;
3062: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3063: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3064: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
3065: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3066: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
3067: l_exists varchar2(1);

Line 3064: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;

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;
3062: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3063: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3064: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
3065: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3066: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
3067: l_exists varchar2(1);
3068: l_proc varchar2(80) := g_package||'.end_prtt_cobra_eligibility';

Line 3065: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;

3061: l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
3062: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3063: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3064: l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
3065: l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3066: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
3067: l_exists varchar2(1);
3068: l_proc varchar2(80) := g_package||'.end_prtt_cobra_eligibility';
3069: --

Line 3073: from ben_cbr_quald_bnf cqb

3069: --
3070: cursor c_get_cbr_quald_bnf
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'

Line 3092: from ben_cbr_quald_bnf cqb

3088: --
3089: cursor c_get_dpnt_cbr_quald_bnf(p_person_id in number)
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'

Line 3346: l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;

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;
3350: l_exists varchar2(1);

Line 3408: ben_cbr_quald_bnf_api.update_cbr_quald_bnf

3404: l_cqb_object_version_number := p_cqb_object_version_number;
3405: --
3406: hr_utility.set_location('p_cbr_elig_perd_end_dt'||p_cbr_elig_perd_end_dt, 20);
3407: hr_utility.set_location('p_quald_bnf_flag'||p_quald_bnf_flag, 30);
3408: ben_cbr_quald_bnf_api.update_cbr_quald_bnf
3409: (p_validate => p_validate
3410: ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
3411: ,p_quald_bnf_flag => p_quald_bnf_flag
3412: ,p_cbr_elig_perd_end_dt => p_cbr_elig_perd_end_dt