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_PER_IN_LER

Line 239: from ben_cbr_per_in_ler crp

235: --
236: cursor c_chk_init_evt
237: is
238: select null
239: from ben_cbr_per_in_ler crp
240: where crp.per_in_ler_id = p_per_in_ler_id
241: and crp.business_group_id = p_business_group_id
242: and crp.init_evt_flag = 'Y';
243: --

Line 472: ,ben_cbr_per_in_ler crp

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
476: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt

Line 540: ,ben_cbr_per_in_ler crp

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')
544: and pil.business_group_id = p_business_group_id

Line 550: from ben_cbr_per_in_ler crp2

546: and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
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

Line 569: ,ben_cbr_per_in_ler crp

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
573: and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id

Line 1423: ,ben_cbr_per_in_ler crp

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
1427: and p_lf_evt_ocrd_dt

Line 1766: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%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:
1770: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;

Line 1786: ,ben_cbr_per_in_ler crp

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
1790: and cqb.pgm_id = l_pgm_id

Line 1814: ,ben_cbr_per_in_ler crp

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
1818: and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id

Line 2037: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

2033: );
2034: --
2035: -- Write a cbr per in ler row
2036: --
2037: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2038: (p_validate => p_validate
2039: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2040: ,p_init_evt_flag => 'Y'
2041: ,p_cnt_num => 1

Line 2296: ,ben_cbr_per_in_ler crp

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'
2300: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt

Line 2321: from ben_cbr_per_in_ler crp

2317: and pdp.business_group_id = p_business_group_id;
2318: --
2319: cursor c_chk_init_evt is
2320: select crp.*
2321: from ben_cbr_per_in_ler crp
2322: where crp.per_in_ler_id = p_per_in_ler_id
2323: and crp.init_evt_flag = 'Y'
2324: and crp.business_group_id = p_business_group_id;
2325: --

Line 2526: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;

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

Line 2527: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_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;
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: --

Line 2528: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%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;
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

Line 2529: l_cqb_object_version_number ben_cbr_per_in_ler.object_version_number%type;

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

Line 2572: from ben_cbr_per_in_ler crp

2568: and ler.qualg_evt_flag = 'Y';
2569: --
2570: cursor c_check_cbr_per_in_ler is
2571: select crp.*
2572: from ben_cbr_per_in_ler crp
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

Line 2576: from ben_cbr_per_in_ler crp2

2572: from ben_cbr_per_in_ler crp
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

Line 2592: from ben_cbr_per_in_ler crp

2588: and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id;
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

Line 2603: ,ben_cbr_per_in_ler crp

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
2607: and cqb.quald_bnf_flag = 'Y'

Line 2632: ,ben_cbr_per_in_ler crp

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
2636: and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)

Line 2652: ,ben_cbr_per_in_ler crp

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'
2656: and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt

Line 2673: ,ben_cbr_per_in_ler crp

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
2677: /* and p_lf_evt_ocrd_dt

Line 2805: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

2801: if p_quald_bnf_flag = 'Y' then
2802: --
2803: -- Write COBRA per in ler row.
2804: --
2805: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2806: (p_validate => p_validate
2807: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2808: ,p_init_evt_flag => 'Y'
2809: ,p_cnt_num => 1

Line 2851: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

2847: fetch c_get_cvrd_emp_pil into l_crp2_rec;
2848: if c_get_cvrd_emp_pil%found then
2849: close c_get_cvrd_emp_pil;
2850: --
2851: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2852: (p_validate => p_validate
2853: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2854: ,p_init_evt_flag => 'Y'
2855: ,p_cnt_num => 1

Line 2975: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

2971: fetch c_get_cnt_num into l_cnt_num;
2972: close c_get_cnt_num;
2973: --
2974: hr_utility.set_location('l_cnt_num : ' || l_cnt_num, 10);
2975: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2976: (p_validate => p_validate
2977: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2978: ,p_init_evt_flag => 'N'
2979: ,p_cnt_num => l_cnt_num + 1

Line 3020: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

3016: fetch c_get_cnt_num into l_cnt_num;
3017: close c_get_cnt_num;
3018: --
3019: hr_utility.set_location('l_cnt_num : ' || l_cnt_num, 10);
3020: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
3021: (p_validate => p_validate
3022: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
3023: ,p_init_evt_flag => 'N'
3024: ,p_cnt_num => l_cnt_num + 1

Line 3062: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%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;
3066: l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;

Line 3074: ,ben_cbr_per_in_ler crp

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'
3078: and cqb.cbr_elig_perd_end_dt >= l_effective_date

Line 3093: ,ben_cbr_per_in_ler crp

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'
3097: and cqb.cbr_elig_perd_end_dt >= l_effective_date

Line 3110: from ben_cbr_per_in_ler crp

3106: -- end 12386329
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

Line 3347: l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;

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);
3351: l_proc varchar2(80) := g_package||'.end_cobra_eligibility';

Line 3348: l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;

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);
3351: l_proc varchar2(80) := g_package||'.end_cobra_eligibility';
3352: --

Line 3349: l_cnt_num ben_cbr_per_in_ler.cnt_num%type;

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);
3351: l_proc varchar2(80) := g_package||'.end_cobra_eligibility';
3352: --
3353: cursor c_get_cnt_num is

Line 3355: from ben_cbr_per_in_ler crp

3351: l_proc varchar2(80) := g_package||'.end_cobra_eligibility';
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

Line 3366: from ben_cbr_per_in_ler crp

3362:
3363: --
3364: cursor c_chk_cbr_per_in_ler is
3365: select null
3366: from ben_cbr_per_in_ler crp
3367: where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3368: and crp.per_in_ler_id = p_per_in_ler_id;
3369: --
3370: begin

Line 3388: ben_cbr_per_in_ler_api.create_cbr_per_in_ler

3384: open c_get_cnt_num;
3385: fetch c_get_cnt_num into l_cnt_num;
3386: close c_get_cnt_num;
3387: --
3388: ben_cbr_per_in_ler_api.create_cbr_per_in_ler
3389: (p_validate => p_validate
3390: ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
3391: ,p_init_evt_flag => 'N'
3392: ,p_cnt_num => l_cnt_num + 1