[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_lvl_rec.delete;
procedure update_count(p_lvl_name in varchar2) is
i int;
select hl.meaning
from hr_lookups hl
where hl.lookup_type = 'BEN_COMP_LVL'
and hl.lookup_code = p_lvl_name;
select 'Y'
from ben_prtt_enrt_rslt_f prtt
where prtt.business_group_id = p_business_group_id
and prtt.person_id = p_person_id
and p_effective_date between prtt.effective_start_date
and prtt.effective_end_date
and nvl(prtt.pgm_id,-1) = nvl(p_pgm_id,-1)
and prtt.enrt_cvg_thru_dt = hr_api.g_eot
and prtt.prtt_enrt_rslt_stat_cd is null
and ( (p_oipl_id is not null and prtt.oipl_id = p_oipl_id)
or (p_pl_id is not null and prtt.pl_id = p_pl_id));
select oipl.oipl_id,epo.*
from ben_elig_per_opt_f epo,
ben_elig_per_f pep,
ben_oipl_f oipl
where epo.elig_per_id = p_elig_per_rec.elig_per_id
and pep.elig_per_id = epo.elig_per_id
and oipl.pl_id = pep.pl_id
and oipl.opt_id = epo.opt_id
and pep.business_group_id = p_business_group_id
and epo.business_group_id = p_business_group_id
and oipl.business_group_id = p_business_group_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between oipl.effective_start_date
and oipl.effective_end_date;
BEN_elig_person_option_API.update_elig_person_option
(p_validate => FALSE
,p_elig_per_opt_id => l_elig_per_opt_rec.elig_per_opt_id
,p_elig_per_id => l_elig_per_opt_rec.elig_per_id
,p_business_group_id => p_business_group_id
,p_elig_flag => p_elig_flag
,p_prtn_ovridn_flag => 'Y'
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => p_dt_mode
);
update_count(p_cobj_lvl);
BEN_eligible_person_API.update_eligible_person
(p_validate => FALSE
,p_elig_per_id => p_elig_rec.elig_per_id
,p_business_group_id => p_business_group_id
,p_elig_flag => p_elig_flag
,p_prtn_ovridn_flag => 'Y'
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => p_dt_mode
);
update_count(p_cobj_lvl);
procedure update_elig_hierarchy
(p_person_id in number,
p_pgm_id in number,
p_ptip_id in number,
p_plip_id in number,
p_pl_id in number,
p_elig_per_id in number,
p_elig_flag in varchar2,
p_dt_mode in varchar2,
p_business_group_id in number,
p_per_in_ler_id in number,
p_effective_date in date,
p_out_mesg out nocopy varchar2) is
l_cobj_lvl varchar2(30) ;
select pep.*
from ben_elig_per_f pep
where pep.person_id = p_person_id
and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
and p_effective_date between pep.effective_start_date and
pep.effective_end_date
and pep.business_group_id = p_business_group_id
and ( (p_elig_per_id is not null and
pep.elig_per_id = p_elig_per_id
) or
(p_pl_id is not null and pep.plip_id is not null and exists
(select null
from ben_plip_f plip
where plip.plip_id = pep.plip_id
and plip.pl_id = p_pl_id
and plip.pgm_id = p_pgm_id
and p_effective_date between plip.effective_start_date and
plip.effective_end_date
and plip.business_group_id = p_business_group_id)
) or
(p_plip_id is not null and pep.ptip_id is not null and exists
(select null
from ben_ptip_f ptip,
ben_plip_f plip,
ben_pl_f pln
where ptip.ptip_id = pep.ptip_id
and ptip.pgm_id = p_pgm_id
and plip.plip_id = p_plip_id
and pln.pl_id = plip.pl_id
and ptip.pl_typ_id = pln.pl_typ_id
and p_effective_date between ptip.effective_start_date and
ptip.effective_end_date
and ptip.business_group_id = p_business_group_id
and p_effective_date between plip.effective_start_date and
plip.effective_end_date
and plip.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date and
pln.effective_end_date
and pln.business_group_id = p_business_group_id)
) or
(p_ptip_id is not null and pep.ptip_id is null and
pep.plip_id is null and pep.pl_id is null)
);
select pep.*
from ben_elig_per_f pep
where pep.person_id = p_person_id
and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
and p_effective_date between pep.effective_start_date and
pep.effective_end_date
and pep.business_group_id = p_business_group_id
and ( (p_pgm_id is not null and p_ptip_id is null and
p_plip_id is null and p_pl_id is null and
pep.ptip_id is not null and exists
(select null
from ben_ptip_f ptip
where ptip.ptip_id = pep.ptip_id
and ptip.pgm_id = p_pgm_id
and p_effective_date between ptip.effective_start_date and
ptip.effective_end_date
and ptip.business_group_id = p_business_group_id)
) or
(p_ptip_id is not null and pep.plip_id is not null and exists
(select null
from ben_ptip_f ptip,
ben_plip_f plip,
ben_pl_f pln
where ptip.ptip_id = p_ptip_id
and ptip.pgm_id = p_pgm_id
and ptip.pl_typ_id = pln.pl_typ_id
and plip.pl_id = pln.pl_id
and plip.plip_id = pep.plip_id
and plip.pgm_id = ptip.pgm_id
and p_effective_date between ptip.effective_start_date and
ptip.effective_end_date
and ptip.business_group_id = p_business_group_id
and p_effective_date between plip.effective_start_date and
plip.effective_end_date
and plip.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date and
pln.effective_end_date
and pln.business_group_id = p_business_group_id)
) or
(p_plip_id is not null and pep.pl_id is not null and exists
(select null
from ben_plip_f plip
where plip.plip_id = p_plip_id
and plip.pgm_id = p_pgm_id
and plip.pl_id = pep.pl_id
and p_effective_date between plip.effective_start_date and
plip.effective_end_date
and plip.business_group_id = p_business_group_id)
) or
(p_pl_id is not null and pep.pl_id = p_pl_id and exists
(select null
from ben_elig_per_opt_f epo
where epo.elig_per_id = pep.elig_per_id
and p_effective_date between epo.effective_start_date and
epo.effective_end_date
and epo.business_group_id = p_business_group_id))
);
ben_eligibility_override.update_elig_hierarchy
(p_person_id => p_person_id,
p_pgm_id => p_pgm_id,
p_ptip_id => l_elig_rec.ptip_id,
p_plip_id => l_elig_rec.plip_id,
p_pl_id => l_elig_rec.pl_id,
p_elig_per_id => null,
p_elig_flag => p_elig_flag,
p_dt_mode => p_dt_mode,
p_business_group_id => p_business_group_id,
p_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date,
p_out_mesg => p_out_mesg);
ben_eligibility_override.update_elig_hierarchy
(p_person_id => p_person_id,
p_pgm_id => p_pgm_id,
p_ptip_id => l_elig_rec.ptip_id,
p_plip_id => l_elig_rec.plip_id,
p_pl_id => l_elig_rec.pl_id,
p_elig_per_id => null,
p_elig_flag => p_elig_flag,
p_dt_mode => p_dt_mode,
p_business_group_id => p_business_group_id,
p_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date,
p_out_mesg => p_out_mesg);
end update_elig_hierarchy;
select plip.plip_id,plip.ordr_num
from ben_plip_f plip
where plip.pl_id = l_elig_per.pl_id
and plip.pgm_id = l_elig_per.pgm_id
and l_eff_dt between plip.effective_start_date
and plip.effective_end_date;
select ptip.ptip_id,ptip.ordr_num
from ben_ptip_f ptip,
ben_plip_f plip,
ben_pl_f pl
where ptip.pgm_id = l_elig_per.pgm_id
and ptip.pl_typ_id = pl.pl_typ_id
and pl.pl_id = plip.pl_id
and plip.plip_id = l_elig_per.plip_id
and l_eff_dt between ptip.effective_start_date
and ptip.effective_end_date
and l_eff_dt between plip.effective_start_date
and plip.effective_end_date
and l_eff_dt between pl.effective_start_date
and pl.effective_end_date;
select null
from ben_elig_per_f pep
where pep.pgm_id = l_elig_per.pgm_id
and nvl(pep.plip_id,-1) = nvl(l_elig_per.plip_id,-1)
and nvl(pep.ptip_id,-1) = nvl(l_elig_per.ptip_id,-1)
and nvl(pep.pl_id,-1) = nvl(l_elig_per.pl_id,-1)
and pep.person_id = l_elig_per.person_id
and (pep.per_in_ler_id is null or
exists
(select null
from ben_per_in_ler pil
where pep.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')))
and l_eff_dt between pep.effective_start_date
and pep.effective_end_date;
,p_program_update_date =>l_elig_per.program_update_date
,p_OBJECT_VERSION_NUMBER =>l_OBJECT_VERSION_NUMBER
,p_effective_date => p_effective_date
);
update_count(l_cobj_level);