[Home] [Help]
2303: -- Cursor to retrieve the dependent designation level
2304: --
2305: cursor c_dpnt_lvl_cd (p_pgm_id number) is
2306: select pgm.dpnt_dsgn_lvl_cd
2307: from ben_pgm_f pgm
2308: where pgm.pgm_id = p_pgm_id
2309: and pgm.business_group_id = p_business_group_id
2310: and p_effective_date between
2311: pgm.effective_start_date and pgm.effective_end_date;
2313: -- Cursor to retrieve dependant required flags at the pgm level
2314: --
2315: cursor c_dpnt_pgm (p_pgm_id number) is
2316: select pgm.susp_if_ctfn_not_dpnt_flag
2317: from ben_pgm_f pgm
2318: where pgm.pgm_id = p_pgm_id
2319: and pgm.business_group_id = p_business_group_id
2320: and p_effective_date between
2321: pgm.effective_start_date and pgm.effective_end_date;
2362:
2363:
2364: l_ler_susp_if_ctfn_not_prvd varchar2(30);
2365: l_rqd_flag varchar2(30) := 'Y';
2366: l_lvl_cd ben_pgm_f.dpnt_dsgn_lvl_cd%type;
2367:
2368: --End Bug 6353069
2369:
2370:
2517:
2518: IF (p_prtt_enrt_actn_id IS NULL AND l_data_found = FALSE AND p_elig_cvrd_dpnt_id is not NULL)
2519: THEN
2520: --
2521: -- Fetch the designation level code from the ben_pgm_f table.
2522: --
2523: IF l_rslt.pgm_id IS NOT NULL
2524: THEN
2525: OPEN c_dpnt_lvl_cd (p_pgm_id => l_rslt.pgm_id);
3157: l_rslt_object_version_number number(15);
3158:
3159: --
3160: l_allws_flag varchar2(30);
3161: l_lvl_cd ben_pgm_f.dpnt_dsgn_lvl_cd%type;
3162: l_actn_typ_id number;
3163: l_rqd_data_found boolean; -- holds return value from check_xxx functions
3164: l_effective_start_date date;
3165: l_effective_end_date date;
3224: -- Cursor to retrieve the dependent designation level
3225: --
3226: cursor c_dpnt_lvl_cd (p_pgm_id number) is
3227: select pgm.dpnt_dsgn_lvl_cd
3228: from ben_pgm_f pgm
3229: where pgm.pgm_id = p_pgm_id
3230: and pgm.business_group_id = p_business_group_id
3231: and p_effective_date between
3232: pgm.effective_start_date and pgm.effective_end_date;
3239: pgm.susp_if_dpnt_adr_nt_prv_cd,
3240: pgm.susp_if_ctfn_not_dpnt_flag,
3241: pgm.dpnt_ctfn_determine_cd,
3242: pgm.dpnt_dsgn_no_ctfn_rqd_flag
3243: from ben_pgm_f pgm
3244: where pgm.pgm_id = p_pgm_id
3245: and pgm.business_group_id = p_business_group_id
3246: and p_effective_date between
3247: pgm.effective_start_date and pgm.effective_end_date;
3499: end if;
3500: */
3501:
3502: --
3503: -- Fetch the designation level code from the ben_pgm_f table.
3504: --
3505: if l_rslt.pgm_id is not null then
3506: open c_dpnt_lvl_cd(p_pgm_id => l_rslt.pgm_id);
3507: fetch c_dpnt_lvl_cd into l_lvl_cd;