[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-Jun-02 pabodla 115.34 Do not select the contingent worker
assignment when assignment data is
fetched.
19-AUg-04 kmahendr 115.35 Optional certification changes
15-nov-04 kmahendr 115.36 Unrest. enh changes
21-feb-05 kmahendr 115.37 Bug#4198774 - mode checked for ctfn
28-Feb-05 kmahendr 115.38 Bug#4175303 - certification is written only
for one level
12 Sep 05 ikasire 115.40 Added new procedure update_susp_if_ctfn_flag
*/
-----------------------------------------------------------------------------------
--
-- Globals
--
g_package varchar2(80) := 'ben_determine_chc_ctfn';
select 'x'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd
and nvl(enrt_bnft_id,-1) = nvl(p_enrt_bnft_id,-1);
ben_manage_unres_life_events.update_enrt_ctfn
(p_elctbl_chc_ctfn_id => l_elctbl_chc_ctfn_id,
p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd,
p_rqd_flag => p_rqd_flag,
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_enrt_bnft_id => p_enrt_bnft_id,
p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag,
p_ctfn_determine_cd => p_ctfn_determine_cd,
p_business_group_id => p_business_group_id,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_request_id => fnd_global.conc_request_id,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_program_update_date => sysdate);
p_program_update_date => sysdate);
select ctfn.rqd_flag,
ctfn.enrt_ctfn_typ_cd,
ctfn.ctfn_rqd_when_rl,
lre.susp_if_ctfn_not_prvd_flag,
lre.ctfn_determine_cd
from ben_ler_enrt_ctfn_f ctfn,
ben_ler_rqrs_enrt_ctfn_f lre
where ctfn.ler_rqrs_enrt_ctfn_id = p_ler_rqrs_enrt_ctfn_id
and lre.ler_rqrs_enrt_ctfn_id = ctfn.ler_rqrs_enrt_ctfn_id
and p_effective_date between
lre.effective_start_date and lre.effective_end_date
and ctfn.business_group_id = p_business_group_id
and p_effective_date between
ctfn.effective_start_date and ctfn.effective_end_date;
select rstrn.ler_bnft_rstrn_id
from ben_ler_bnft_rstrn_f rstrn,
ben_pl_f pln
where rstrn.pl_id = p_pl_id
and rstrn.ler_id = p_ler_id
and rstrn.pl_id = pln.pl_id
and pln.bnft_or_option_rstrctn_cd = 'OPT'
and rstrn.business_group_id = p_business_group_id
and p_effective_date between
rstrn.effective_start_date and rstrn.effective_end_date
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select ctfn.rqd_flag,
ctfn.enrt_ctfn_typ_cd,
ctfn.ctfn_rqd_when_rl,
lbr.susp_if_ctfn_not_prvd_flag,
lbr.ctfn_determine_cd
from ben_ler_bnft_rstrn_ctfn_f ctfn,
ben_ler_bnft_rstrn_f lbr
where ctfn.ler_bnft_rstrn_id = l_ler_bnft_rstrn_id
and lbr.ler_bnft_rstrn_id = ctfn.ler_bnft_rstrn_id
and p_effective_date between
lbr.effective_start_date and lbr.effective_end_date
and ctfn.business_group_id = p_business_group_id
and p_effective_date between
ctfn.effective_start_date and ctfn.effective_end_date;
select ctfn.rqd_flag,
ctfn.enrt_ctfn_typ_cd,
ctfn.ctfn_rqd_when_rl,
pln.susp_if_ctfn_not_prvd_flag,
pln.ctfn_determine_cd
from ben_bnft_rstrn_ctfn_f ctfn,
ben_pl_f pln
where pln.pl_id = p_pl_id
and pln.bnft_or_option_rstrctn_cd = 'OPT'
and pln.business_group_id = p_business_group_id
and pln.pl_id = ctfn.pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
ctfn.effective_start_date and ctfn.effective_end_date;
procedure update_ctfn_rqd_flag(p_elig_per_elctbl_chc_id in number,
p_ctfn_rqd_flag in varchar2,
p_object_version_number in number,
p_business_group_id in number,
p_effective_date in date) is
--
l_package varchar2(80) := g_package||'.update_ctfn_rqd_flag ';
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
(p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_ctfn_rqd_flag => l_ctfn_rqd_flag,
p_object_version_number => l_object_version_number,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
end update_ctfn_rqd_flag;
select epe.elig_per_elctbl_chc_id,
epe.object_version_number,
epe.comp_lvl_cd,
epe.pgm_id,
epe.oipl_id,
epe.pl_id,
epe.pl_typ_id,
oipl.opt_id,
epe.business_group_id,
epe.ctfn_rqd_flag,
pil.person_id,
pil.ler_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_oipl_f oipl
where pil.per_in_ler_id = epe.per_in_ler_id
and epe.crntly_enrd_flag = 'N'
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.oipl_id = oipl.oipl_id(+)
and p_effective_date between
nvl(oipl.effective_start_date, p_effective_date) and
nvl(oipl.effective_end_date, p_effective_date);
select lre.ler_rqrs_enrt_ctfn_id,
lre.ctfn_rqd_when_rl
from ben_ler_rqrs_enrt_ctfn_f lre
where lre.oipl_id = l_oipl_id
and lre.ler_id = l_ler_id
and lre.business_group_id = l_business_group_id
and p_effective_date
between lre.effective_start_date
and lre.effective_end_date;
select lre.ler_rqrs_enrt_ctfn_id,
lre.ctfn_rqd_when_rl
from ben_ler_rqrs_enrt_ctfn_f lre
where lre.pl_id = l_pl_id
and lre.ler_id = l_ler_id
and lre.business_group_id = l_business_group_id
and p_effective_date
between lre.effective_start_date
and lre.effective_end_date;
select ecf.enrt_ctfn_typ_cd,
ecf.rqd_flag,
ecf.ctfn_rqd_when_rl,
cop.susp_if_ctfn_not_prvd_flag,
cop.ctfn_determine_cd
from ben_enrt_ctfn_f ecf,
ben_oipl_f cop
where ecf.oipl_id = l_oipl_id
and cop.oipl_id = ecf.oipl_id
and ecf.business_group_id = l_business_group_id
and p_effective_date
between cop.effective_start_date
and cop.effective_end_date
and p_effective_date
between ecf.effective_start_date
and ecf.effective_end_date;
select ecf.enrt_ctfn_typ_cd,
ecf.rqd_flag,
ecf.ctfn_rqd_when_rl,
pln.susp_if_ctfn_not_prvd_flag,
pln.ctfn_determine_cd
from ben_enrt_ctfn_f ecf,
ben_pl_f pln
where ecf.pl_id = l_pl_id
and pln.pl_id = ecf.pl_id
and ecf.business_group_id = l_business_group_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date
and p_effective_date
between ecf.effective_start_date
and ecf.effective_end_date;
select asg.assignment_id,asg.organization_id,loc.region_2
from per_all_assignments_f asg,hr_locations_all loc
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id(+)
and p_effective_date
between asg.effective_start_date
and asg.effective_end_date;
update_ctfn_rqd_flag
(p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
p_ctfn_rqd_flag => l_epe.ctfn_rqd_flag,
p_object_version_number => l_epe.object_version_number,
p_business_group_id => l_epe.business_group_id,
p_effective_date => p_effective_date);
procedure update_susp_if_ctfn_flag(
p_effective_date in date,
p_lf_evt_ocrd_dt in date,
p_person_id in number,
p_per_in_ler_id in number
) IS
l_package varchar2(80) := g_package||'.update_susp_if_ctfn_flag ';
select distinct epe.pgm_id,
epe.pl_typ_id,
epe.pl_id,
ecc.enrt_ctfn_typ_cd
from ben_elctbl_chc_ctfn ecc,
ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.elctbl_flag = 'Y'
and epe.ctfn_rqd_flag = 'Y'
and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecc.enrt_bnft_id is null
and ecc.ctfn_determine_cd = 'ENRFT'
and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
and ecc.rqd_flag = 'Y'
order by 1,2,3,4;
select 'Y'
from ben_ler_rqrs_enrt_ctfn_f lre,
ben_ler_enrt_ctfn_f lec,
ben_per_in_ler pil
where lre.pl_id = p_pl_id
and pil.per_in_ler_id = p_per_in_ler_id
and lre.ler_id = pil.ler_id
and p_effective_date between lre.effective_start_date
and lre.effective_end_date
and p_effective_date between lec.effective_start_date
and lec.effective_end_date
and lec.ler_rqrs_enrt_ctfn_id = lre.ler_rqrs_enrt_ctfn_id
and lec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd
union
select 'Y'
from ben_enrt_ctfn_f ec
where ec.pl_id = p_pl_id
and p_effective_date between ec.effective_start_date
and ec.effective_end_date
and ec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd ;
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND p_cvg_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
-- AND pen.oipl_id IS NULL
AND p_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL))
;
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND p_cvg_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_oipl_id = pen.oipl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL))
;
select epe.elig_per_elctbl_chc_id,
epe.pgm_id,
epe.pl_typ_id,
epe.pl_id,
epe.oipl_id,
ecc.enrt_ctfn_typ_cd,
ecc.elctbl_chc_ctfn_id,
ecc.object_version_number,
ecc.business_group_id
from ben_elctbl_chc_ctfn ecc,
ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.pl_id = p_pl_id
and epe.elctbl_flag = 'Y'
and epe.ctfn_rqd_flag = 'Y'
and epe.oipl_id IS NOT NULL
and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecc.enrt_bnft_id is null
and ecc.ctfn_determine_cd = 'ENRFT'
and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
and ecc.rqd_flag = 'Y' ;
select epe.elig_per_elctbl_chc_id,
epe.pgm_id,
epe.pl_typ_id,
epe.pl_id,
epe.oipl_id,
ecc.enrt_ctfn_typ_cd,
ecc.elctbl_chc_ctfn_id,
ecc.object_version_number,
ecc.business_group_id
from ben_elctbl_chc_ctfn ecc,
ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.pl_id = p_pl_id
and epe.elctbl_flag = 'Y'
and epe.ctfn_rqd_flag = 'Y'
and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecc.enrt_bnft_id is null
and ecc.ctfn_determine_cd = 'ENRFT'
and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
and ecc.rqd_flag = 'Y' ;
ben_ELTBL_CHC_CTFN_api.update_ELTBL_CHC_CTFN
(p_elctbl_chc_ctfn_id => l_oipl_rec.elctbl_chc_ctfn_id
,p_susp_if_ctfn_not_prvd_flag => 'N'
,p_object_version_number => l_ecc_ovn
,p_effective_date => trunc(p_effective_date)
);
ben_ELTBL_CHC_CTFN_api.update_ELTBL_CHC_CTFN
(p_elctbl_chc_ctfn_id => l_oipl_rec.elctbl_chc_ctfn_id
,p_susp_if_ctfn_not_prvd_flag => 'N'
,p_object_version_number => l_ecc_ovn
,p_effective_date => trunc(p_effective_date)
);
end update_susp_if_ctfn_flag ;