[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_procptip_list.delete;
function check_selection_rule
(p_person_selection_rule_id in number,
p_person_id in number,
p_business_group_id in number,
p_effective_date in date) return boolean is
--
l_outputs ff_exec.outputs_t;
l_package varchar2(80) := g_package||'.check_selection_rule';
if p_person_selection_rule_id is null then
--
return true;
(p_formula_id => p_person_selection_rule_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_assignment_id => l_assignment_id,
p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
p_param1_value => to_char(p_person_id));
fnd_message.set_token('RL','person_selection_rule_id');
end check_selection_rule;
select 1 ordr_num ,
leer.lee_rsn_id,
NVL(leer.defer_deenrol_flag,'N')
FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = p_ler_id
AND leer.business_group_id = p_business_group_id
AND c_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = c_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
UNION
select 2 ordr_num ,
leer.lee_rsn_id,
NVL(leer.defer_deenrol_flag,'N')
FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = p_ler_id
AND leer.business_group_id = p_business_group_id
AND c_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = c_pgm_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
order by 1 ;
select formula_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc,
ben_lee_rsn_rl_f lrr
WHERE leer.ler_id = p_ler_id
AND l_effective_date BETWEEN leer.effective_start_date -- 12715758
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = l_pgm_id
AND petc.enrt_typ_cycl_cd = 'L'
AND l_effective_date BETWEEN petc.effective_start_date -- 12715758
AND petc.effective_end_date
and l_effective_date between -- 12715758
lrr.effective_start_date and lrr.effective_end_date
and lrr.lee_rsn_id=leer.lee_rsn_id
and lrr.business_group_id=leer.business_group_id
order by ordr_to_aply_num;
select formula_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc,
ben_lee_rsn_rl_f lrr
WHERE leer.ler_id = p_ler_id
AND l_effective_date BETWEEN leer.effective_start_date -- 12715758
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = l_pl_id
AND petc.enrt_typ_cycl_cd = 'L'
AND l_effective_date BETWEEN petc.effective_start_date -- 12715758
AND petc.effective_end_date
and l_effective_date between -- 12715758
lrr.effective_start_date and lrr.effective_end_date
and lrr.lee_rsn_id=leer.lee_rsn_id
and lrr.business_group_id=leer.business_group_id
order by ordr_to_aply_num;
select formula_id
from ben_schedd_enrt_rl_f lrr,
ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp
where pet.pgm_id = l_pgm_id
and l_effective_date -- 12715758
between pet.effective_start_date
and pet.effective_end_date
and pet.popl_enrt_typ_cycl_id =
enp.popl_enrt_typ_cycl_id
-- PB : 5422 :
and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
/* 5422 : PB :and enp.strt_dt = (select enp1.strt_dt
from ben_enrt_perd enp1
where enp1.enrt_perd_id
= p_popl_enrt_typ_cycl_id) */
and enp.business_group_id =
pet.business_group_id
and l_effective_date between -- 12715758
lrr.effective_start_date and lrr.effective_end_date
and lrr.enrt_perd_id=enp.enrt_perd_id
and lrr.business_group_id=enp.business_group_id
order by ordr_to_aply_num;
select formula_id
from ben_schedd_enrt_rl_f lrr,
ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp
-- CWB changes
where pet.pl_id = l_pl_id
and l_effective_date -- 12715758
between pet.effective_start_date
and pet.effective_end_date
and pet.popl_enrt_typ_cycl_id =
enp.popl_enrt_typ_cycl_id
-- PB : 5422 :
and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
/* and enp.strt_dt = (select enp1.strt_dt
from ben_enrt_perd enp1
where enp1.enrt_perd_id
= p_popl_enrt_typ_cycl_id) */
and enp.business_group_id =
pet.business_group_id
and l_effective_date between -- 12715758
lrr.effective_start_date and lrr.effective_end_date
and lrr.enrt_perd_id=enp.enrt_perd_id
and lrr.business_group_id=enp.business_group_id
order by ordr_to_aply_num;
for l_person_selection in c_schedd_enrt_rl_pgm loop
if l_person_selection.formula_id is not null then
if check_selection_rule
(p_person_selection_rule_id =>l_person_selection.formula_id,
p_person_id =>p_person_id,
p_business_group_id =>p_business_group_id,
p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
hr_utility.set_location('Rule passed for pgm',15);
for l_person_selection in c_lee_rsn_rl_pgm loop
if l_person_selection.formula_id is not null then
if check_selection_rule
(p_person_selection_rule_id =>l_person_selection.formula_id,
p_person_id =>p_person_id,
p_business_group_id =>p_business_group_id,
p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
hr_utility.set_location('Rule passed for pgm',15);
--If the Compensation object is first ineligible or still ineligible then inserting the
--comp object into globle variables to check whether any future records are there in
-- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
if p_comp_obj_tree(l_treeele_num).oipl_id is null then
hr_utility.set_location('p_comp_obj_tree(l_treeele_num).ptip_id'||p_comp_obj_tree(l_treeele_num).ptip_id,10);
--If the Compensation object is first ineligible or still ineligible then inserting the
--comp object into globle variables to check whether any future records are there in
-- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
if p_comp_obj_tree(l_treeele_num).oipl_id is null then
ben_determine_eligibility2.g_cache_first_inelig_object.extend;
for l_person_selection in c_schedd_enrt_rl_plnip loop
if l_person_selection.formula_id is not null then
if check_selection_rule
(p_person_selection_rule_id =>l_person_selection.formula_id,
p_person_id =>p_person_id,
p_business_group_id =>p_business_group_id,
p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
hr_utility.set_location('Rule passed for pgm',15);
for l_person_selection in c_lee_rsn_rl_plnip loop
if l_person_selection.formula_id is not null then
if check_selection_rule
(p_person_selection_rule_id =>l_person_selection.formula_id,
p_person_id =>p_person_id,
p_business_group_id =>p_business_group_id,
p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
hr_utility.set_location('Rule passed for pgm',15);
--If the Compensation object is first ineligible or still ineligible then inserting the
--comp object into globle variables to check whether any future records are there in
-- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
if p_comp_obj_tree(l_treeele_num).oipl_id is null then
ben_determine_eligibility2.g_cache_first_inelig_object.extend;