[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
in rep_person_selection_rule
115.16 07-Dec-06 gsehgal bug 5663102 Query changed remove option type code
COMP.
------------------------------------------------------------------------------
*/
--
g_package varchar2(80) := ' BECNCREP - ben_conc_reports';
,comp_selection_rl number(15)
,lf_evt_ocrd_dt varchar2(30) -- date
,rptg_grp number(15)
,svc_area_id number(15)
,assgn_type varchar2(30)
,cvg_strt_dt varchar2(30) -- date
,cvg_end_dt varchar2(30) -- date
,ben_sel_flag varchar2(30) -- these 7 flags will be overloaded for other reports
,flx_sum_flag varchar2(30)
,actn_items_flag varchar2(30)
,cov_dpnt_flag varchar2(30)
,prmy_care_flag varchar2(30)
,beneficaries_flag varchar2(30)
,certifications_flag varchar2(30)
,disp_epe_flxfld_flag varchar2(30)
,disp_flex_fields varchar2(30));
g_parm.comp_selection_rl := null;
,buff => 'Person Selection Rule : ' ||
benutils.iftrue
(p_expression => g_parm.person_sel_rl is null
,p_true => 'All'
,p_false => g_parm.person_sel_rl));
,buff => 'Comp Object Selection Rule : ' ||
benutils.iftrue
(p_expression => g_parm.comp_selection_rl is null
,p_true => 'All'
,p_false => g_parm.comp_selection_rl));
buff => 'Benefits Selection : '||
g_parm.ben_sel_flag);
select 'Y'
from per_all_people_f ppf, per_person_types ppt
where ppf.person_id = p_person_id
and ppf.person_type_id = p_person_type_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id = g_parm.business_group_id
and ppt.active_flag = 'Y';
select null
from per_all_people_f ppf, per_person_types ppt
where ppf.person_id = g_parm.person_id
and ppf.person_type_id = g_parm.person_type_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id = g_parm.business_group_id
and ppt.active_flag = 'Y';
fnd_message.set_token('PER_SELECT_RL',
'person_selection_rule :'||g_parm.person_sel_rl);
procedure rep_person_selection_rule
(p_person_id in Number
,p_business_group_id in Number
,p_person_selection_rule_id in Number
,p_effective_date in Date
,p_batch_flag in Boolean default FALSE
,p_return in out nocopy varchar2
,p_err_message in out nocopy varchar2 ) as
Cursor c1 is
Select assignment_id
From per_assignments_f paf
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
And paf.primary_flag = 'Y'
And paf.business_group_id = p_business_group_id
And p_effective_date between
paf.effective_start_date and paf.effective_end_date ;
l_proc varchar2(80) := g_package||'.rep_person_selection_rule';
(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 :'||p_person_selection_rule_id);
End rep_person_selection_rule;
function comp_selection_rule
(p_person_id in number
,p_business_group_id in number
,p_pgm_id in number
,p_pl_id in number
,p_pl_typ_id in number
,p_opt_id in number
,p_ler_id in number
,p_oipl_id in number
,p_comp_selection_rule_id in number
,p_effective_date in date
) return char is
cursor c1 is
select assignment_id,organization_id
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
and paf.primary_flag = 'Y'
and paf.business_group_id = p_business_group_id
and p_effective_date between
paf.effective_start_date and paf.effective_end_date;
l_proc varchar2(80) := g_package||' .comp_selection_rule';
(p_formula_id => p_comp_selection_rule_id
,p_effective_date => p_effective_date
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_pl_typ_id => p_pl_typ_id
,p_opt_id => p_opt_id
,p_ler_id => p_ler_id
,p_business_group_id => p_business_group_id
,p_assignment_id => l_assignment_id
,p_organization_id => l_organization_id
,p_jurisdiction_code => null);
fnd_message.set_token('RL','formula_id :'||p_comp_selection_rule_id);
end comp_selection_rule;
rep_person_selection_rule
(p_person_id => p_person_id
,p_business_group_id => g_parm.business_group_id
,p_person_selection_rule_id => g_parm.person_sel_rl
,p_effective_date => g_parm.effective_date
,p_return => rl_ret
,p_err_message => l_err_message ) ;
l_actn := 'After call to person selection rule ...';
hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
-- fnd_file.put_line(fnd_file.log, 'not skip...Inserting Ben_person_actions');
fnd_file.put_line(fnd_file.log, ' persons excluded by the selection rule');
select distinct pen.person_id
from ben_prtt_enrt_rslt_f pen , ben_pl_typ_f ptyp ,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N' /* unsuspended enrollments */
and (pen.person_id = g_parm.person_id or g_parm.person_id is null)
and (pen.pl_id = g_parm.pl_nip_id or g_parm.pl_nip_id is null)
and (pen.pgm_id = g_parm.pgm_id or g_parm.pgm_id is null)
and (g_parm.cvg_strt_dt is null or pen.enrt_cvg_strt_dt >= g_parm.cvg_strt_dt )
and (g_parm.cvg_end_dt is null or pen.enrt_cvg_thru_dt <= g_parm.cvg_end_dt )
and pen.business_group_id = g_parm.business_group_id
and pen.pl_typ_id = ptyp.pl_typ_id
-- bug 5663102
-- and ptyp.opt_typ_cd not in ( 'COMP' , 'CWB' , 'GSP', 'ABS')
and ptyp.opt_typ_cd not in ('CWB' , 'GSP', 'ABS')
/* Added GSP for grade step*/
and ptyp.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ptyp.effective_start_date and ptyp.effective_end_date
and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
/* all persons within the specified organization */
and (g_parm.organization_id is null
or exists ( select '1' from
(select assignment_id,assignment_type,organization_id,person_id
from per_all_assignments_f paf
where business_group_id = g_parm.business_group_id
and paf.person_id= nvl(g_parm.person_id,paf.person_id)
and g_parm.effective_date
between nvl(effective_start_date,g_parm.effective_date )
and nvl(effective_end_date, g_parm.effective_date )
and primary_flag = 'Y'
)paf1
where paf1.organization_id = g_parm.organization_id
and paf1.assignment_type='E'
and paf1.person_id=pen.person_id
union
select '1' from
(select assignment_id,assignment_type,organization_id,person_id
from per_all_assignments_f paf
where business_group_id = g_parm.business_group_id
and paf.person_id= nvl(g_parm.person_id,paf.person_id)
and g_parm.effective_date
between nvl(effective_start_date,g_parm.effective_date )
and nvl(effective_end_date, g_parm.effective_date )
and primary_flag = 'Y'
)paf1
where paf1.organization_id = g_parm.organization_id
and paf1.person_id=pen.person_id
and (paf1.assignment_type='B' and not exists (select 1 from per_all_assignments_f paf2
where paf2.person_id = paf1.person_id
and paf2.business_group_id = g_parm.business_group_id
and g_parm.effective_date
between nvl(paf2.effective_start_date,g_parm.effective_date )
and nvl(paf2.effective_end_date, g_parm.effective_date )
and paf2.primary_flag = 'Y'
and paf2.assignment_type='E')) ))
/* person exists with specified person type */
and (g_parm.person_type_id is null
or exists (select null
from per_person_type_usages ptu
where ptu.person_id = pen.person_id
and ptu.person_type_id = g_parm.person_type_id))
/* person exists with specified assignment type */
and (g_parm.assgn_type is null
or exists (select null
from per_assignments_f asg
where asg.assignment_type = substr(g_parm.assgn_type,1,1)
and asg.person_id = pen.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pen.business_group_id
and g_parm.effective_date
between asg.effective_start_date and asg.effective_end_date))
/* person exists with specified location */
and (g_parm.location_id is null
or exists (select null
from per_assignments_f asg
where asg.location_id = g_parm.location_id
and asg.person_id = pen.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pen.business_group_id
and g_parm.effective_date
between asg.effective_start_date and asg.effective_end_date))
/* person's address has zip code specified in service area */
and (g_parm.svc_area_id is null
or exists (select null
from per_addresses addr ,
ben_svc_area_f svc ,
ben_svc_area_pstl_zip_rng_f spz ,
ben_pstl_zip_rng_f pstl
where addr.person_id = pen.person_id
and addr.primary_flag = 'Y'
and svc.svc_area_id = g_parm.svc_area_id
and svc. svc_area_id = spz.svc_area_id
and spz.pstl_zip_rng_id = pstl.pstl_zip_rng_id
and addr.postal_code between pstl.from_value and pstl.to_value
and svc.business_group_id = pen.business_group_id
and g_parm.effective_date
between addr.date_from and nvl(addr.date_to,g_parm.effective_date)
and g_parm.effective_date
between pstl.effective_start_date and pstl.effective_end_date
and g_parm.effective_date
between spz.effective_start_date and spz.effective_end_date
and g_parm.effective_date
between svc.effective_start_date and svc.effective_end_date))
and (g_parm.ler_id is null
or exists ( select null
from ben_per_in_ler pil2
where pil2.ler_id = g_parm.ler_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.per_in_ler_id = pil2.per_in_ler_id ))
and (g_parm.lf_evt_ocrd_dt is null
or exists ( select null
from ben_per_in_ler pil3
where pil3.lf_evt_ocrd_dt = g_parm.lf_evt_ocrd_dt
and pil3.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.per_in_ler_id = pil3.per_in_ler_id ))
and pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select distinct pil.person_id, pil.ler_id
from ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel,
ben_ler_f ler
where (g_parm.person_id is null or pil.person_id = g_parm.person_id)
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.business_group_id = g_parm.business_group_id
and pil.per_in_ler_id = pel.per_in_ler_id
and pil.ler_id = ler.ler_id
and g_parm.effective_date between ler.effective_start_date and ler.effective_end_date
and ler.business_group_id = pil.business_group_id
and ler.typ_cd not in ( 'GSP', 'ABS') /* added for grade step */
and pel.elcns_made_dt is null
and g_parm.effective_date between
nvl(pel.enrt_perd_strt_dt, g_parm.effective_date) and
nvl(pel.enrt_perd_end_dt, g_parm.effective_date)
and (g_parm.pgm_id is null or pel.pgm_id = g_parm.pgm_id)
and (g_parm.pl_nip_id is null or g_parm.pl_nip_id = pel.pl_id )
and (g_parm.ler_id is null or pil.ler_id = g_parm.ler_id)
and (g_parm.lf_evt_ocrd_dt is null or pil.lf_evt_ocrd_dt = g_parm.lf_evt_ocrd_dt )
/* check if the person belongs to the org or location specified */
and (g_parm.organization_id is null
or exists (select '1'
from per_all_assignments_f per
where per.person_id = pil.person_id
and per.primary_flag = 'Y'
and per.assignment_type <> 'C'
and (g_parm.organization_id is null or
per.organization_id = g_parm.organization_id)
and g_parm.effective_date between per.effective_start_date
and per.effective_end_date ))
and (g_parm.location_id is null
or exists (select '1'
from per_all_assignments_f per
where per.person_id = pil.person_id
and per.primary_flag = 'Y'
and per.assignment_type <> 'C'
and (g_parm.location_id is null or
per.location_id = g_parm.location_id)
and g_parm.effective_date between per.effective_start_date
and per.effective_end_date ))
/* person exists with specified person type */
and (g_parm.person_type_id is null
or exists (select null
from per_person_type_usages ptu
where ptu.person_id = pil.person_id
and ptu.person_type_id = g_parm.person_type_id))
/* person exists with specified assignment type */
and (g_parm.assgn_type is null
or exists (select null
from per_assignments_f asg
where asg.assignment_type = substr(g_parm.assgn_type,1,1)
and asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and g_parm.effective_date
between asg.effective_start_date and asg.effective_end_date))
/* person's address has zip code specified in service area */
and (g_parm.svc_area_id is null
or exists (select null
from per_addresses addr ,
ben_svc_area_f svc ,
ben_svc_area_pstl_zip_rng_f spz ,
ben_pstl_zip_rng_f pstl
where addr.person_id = pil.person_id
and addr.primary_flag = 'Y'
and svc.svc_area_id = g_parm.svc_area_id
and svc. svc_area_id = spz.svc_area_id
and spz.pstl_zip_rng_id = pstl.pstl_zip_rng_id
and addr.postal_code between pstl.from_value and pstl.to_value
and svc.business_group_id = pil.business_group_id
and g_parm.effective_date
between addr.date_from and nvl(addr.date_to,g_parm.effective_date)
and g_parm.effective_date
between pstl.effective_start_date and pstl.effective_end_date
and g_parm.effective_date
between spz.effective_start_date and spz.effective_end_date
and g_parm.effective_date
between svc.effective_start_date and svc.effective_end_date));
,p_person_selection_rule_id in number default null
,p_comp_selection_rule_id in number default null
,p_business_group_id in number
,p_reporting_group_id in number default null
,p_svc_area_id in number default null
,p_assignment_type in varchar2 default null
,p_cvg_strt_dt in varchar2 default null
,p_cvg_end_dt in varchar2 default null
,p_person_type_id in number default null
,p_ben_sel_flag in varchar2 default 'Y'
,p_flx_sum_flag in varchar2 default 'Y'
,p_actn_items_flag in varchar2 default 'Y'
,p_cov_dpnt_flag in varchar2 default 'Y'
,p_prmy_care_flag in varchar2 default 'Y'
,p_beneficaries_flag in varchar2 default 'Y'
,p_certifications_flag in varchar2 default 'Y'
,p_disp_epe_flxfld_flag in varchar2 default 'Y'
,p_disp_flex_fields in varchar2 default 'Y' ) is
--
l_package varchar2(80) := g_package||'.process';
g_parm.person_sel_rl := p_person_selection_rule_id;
g_parm.comp_selection_rl := p_comp_selection_rule_id;
,p_comp_selection_rl => p_comp_selection_rule_id
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,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_object_version_number => l_object_version_number
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_effective_date => l_effective_date
,p_mode_cd => 'U'
,p_derivable_factors_flag => 'N'
,p_validate_flag => 'N'
,p_debug_messages_flag => 'Y'
,p_audit_log_flag => 'N'
,p_no_programs_flag => p_plan_in_pgm_flag
,p_no_plans_flag => 'N'
,p_benfts_grp_id => null
,p_pstl_zip_rng_id => null
,p_rptg_grp_id => NULL
,p_opt_id => NULL
,p_eligy_prfl_id => NULL
,p_vrbl_rt_prfl_id => NULL
,p_legal_entity_id => null
,p_payroll_id => null
);
hr_utility.set_location('Delete rows from ben_batch_ranges',16);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
fnd_file.put_line(fnd_file.log, 'Number of persons selected : ' || g_person_cnt);
'<< No Person got selected with above selection criteria >>' );
argument11 => g_parm.comp_selection_rl,
argument12 => g_parm.business_group_id ,
argument13 => g_parm.plan_in_pgm_flag ,
argument14 => g_parm.person_type_id ,
argument15 => g_parm.rptg_grp,
argument16 => g_parm.svc_area_id,
argument17 => g_parm.assgn_type ,
argument18 => to_char(fnd_date.canonical_to_date(p_cvg_strt_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
argument19 => to_char(fnd_date.canonical_to_date(p_cvg_end_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
argument20 => g_parm.ben_sel_flag,
argument21 => g_parm.flx_sum_flag,
argument22 => g_parm.cov_dpnt_flag,
argument23 => g_parm.prmy_care_flag,
argument24 => g_parm.beneficaries_flag,
argument25 => g_parm.certifications_flag,
argument26 => g_parm.actn_items_flag
);
argument11 => g_parm.comp_selection_rl,
argument12 => g_parm.business_group_id ,
argument13 => g_parm.plan_in_pgm_flag ,
argument14 => g_parm.person_type_id ,
argument15 => g_parm.rptg_grp,
argument16 => g_parm.svc_area_id,
argument17 => g_parm.assgn_type ,
argument18 => g_parm.ben_sel_flag,
argument19 => g_parm.flx_sum_flag,
argument20 => g_parm.cov_dpnt_flag,
argument21 => g_parm.prmy_care_flag,
argument22 => g_parm.beneficaries_flag,
argument23 => g_parm.certifications_flag,
argument24 => g_parm.actn_items_flag,
argument25 => g_parm.disp_epe_flxfld_flag ,
argument26 => g_parm.disp_flex_fields);