[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure 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_input1 in varchar2 default null -- Bug 5331889
,p_input1_value in varchar2 default null
,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||'.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)
,p_param2 => p_input1
,p_param2_value => p_input1_value);
fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
End person_selection_rule;
,p_person_selection_rule_id in number default hr_api.g_number
,p_audit_log in varchar2 default hr_api.g_varchar2
,p_from_ocrd_date in date default null
,p_to_ocrd_date in date default null
,p_life_evt_typ_cd in varchar2 default null
,p_bckt_stat_cd in varchar2 default 'VOIDD'
,p_delete_life_evt in varchar2 default 'N'
,p_delete_ptnl_life_evt in varchar2 default 'N'
) is
l_proc varchar2(80) := g_package||'.print_parameters';
If (nvl(p_person_selection_rule_id,-1) <> hr_api.g_number) then
ben_batch_utils.write(p_text => 'Person Selection Rule :'||
benutils.iftrue
(p_expression => p_person_selection_rule_id is null
,p_true => 'None'
,p_false => p_person_selection_rule_id));
ben_batch_utils.write(p_text => 'Delete Life Events :'||
hr_general.decode_lookup('YES_NO',p_delete_life_evt));
,p_Person_selection_rl in number default NULL
,p_life_event_id in number default null
,p_from_ocrd_date in varchar2 default null
,p_to_ocrd_date in varchar2
,p_organization_id in number default null
,p_location_id in number default null
,p_benfts_grp_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_life_evt_typ_cd in varchar2 default null
,p_bckt_stat_cd in varchar2 default 'VOIDD'
,p_audit_log_flag in varchar2 default 'N'
,p_delete_life_evt in varchar2
,p_delete_ptnl_life_evt in varchar2
)
is
--
-- Local variable declaration.
--
l_effective_date date;
select ppf.person_id from per_all_people_f ppf
where (ppf.person_id = p_person_id or p_person_id is null)
and ppf.business_group_id = p_business_group_id
and l_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and (p_organization_id is null
or exists (select null
from per_all_assignments_f paa
where paa.person_id = ppf.person_id
and l_effective_date
between paa.effective_start_date
and paa.effective_end_date
and paa.business_group_id = ppf.business_group_id
and paa.primary_flag = 'Y'
and paa.organization_id = p_organization_id))
and (p_location_id is null
or exists (select null
from per_all_assignments_f paa
where paa.person_id = ppf.person_id
and l_effective_date
between paa.effective_start_date
and paa.effective_end_date
and paa.business_group_id = ppf.business_group_id
and paa.primary_flag = 'Y'
and paa.location_id = p_location_id))
and (p_benfts_grp_id is null
or exists (select null
from per_all_people_f pap
where pap.person_id = ppf.person_id
and pap.business_group_id = ppf.business_group_id
and l_effective_date
between pap.effective_start_date
and pap.effective_end_date
and pap.benefit_group_id = p_benfts_grp_id))
and (p_legal_entity_id is null
or exists (select null
from per_assignments_f paf,
hr_soft_coding_keyflex soft
where paf.person_id = ppf.person_id
and paf.assignment_type <> 'C'
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = ppf.business_group_id
and paf.primary_flag = 'Y'
and soft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and soft.segment1 = to_char(p_legal_entity_id)))
and (p_payroll_id is null
or exists (select null
from per_all_assignments_f paf,
pay_payrolls_f pay
where paf.person_id = ppf.person_id
and pay.payroll_id = paf.payroll_id
and paf.payroll_id = p_payroll_id
and paf.assignment_type <> 'C'
and paf.primary_flag = 'Y'
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date
and l_effective_date
between pay.effective_start_date
and pay.effective_end_date));
,p_person_selection_rl => p_person_selection_rl
,p_no_programs_flag => 'N'
,p_no_plans_flag => 'N'
,p_derivable_factors_flag => 'N'
,p_validate_flag => 'N'
,p_audit_log_flag => p_audit_log_flag
,p_ler_id => p_life_event_id
,p_date_from => l_from_ocrd_date --reuse
,p_lf_evt_ocrd_dt => l_to_ocrd_date -- reuse
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,p_benfts_grp_id => p_benfts_grp_id
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_ptnl_ler_for_per_stat_cd => p_life_evt_typ_cd --reuse
,p_elig_enrol_cd => p_bckt_stat_cd --reuse
,p_debug_messages_flag => p_delete_life_evt --reuse
,p_object_version_number => l_object_version_number
,p_effective_date => l_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);
delete from ben_batch_ranges
where benefit_action_id = l_benefit_action_id;
if p_person_selection_rl is not null then
--
person_selection_rule
(p_person_id => l_rec.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id => p_person_selection_rl
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
,argument16 => p_delete_life_evt
);
,p_person_selection_rule_id => p_person_selection_rl
);
'No person got selected with above selection criteria.');
,p_delete_life_evt => p_delete_life_evt
);
if p_delete_ptnl_life_evt = 'Y' and p_delete_life_evt = 'Y'
then
--
delete from ben_ptnl_ler_for_per
where ptnl_ler_for_per_stat_cd = 'VOIDD'
and business_group_id = p_business_group_id
and (p_life_event_id is null or ler_id = p_life_event_id)
and (p_person_id is null or person_id = p_person_id);
ben_batch_utils.write(p_text => ' No. of Voided Potentials Deleted = ' || sql%rowcount );
elsif p_delete_ptnl_life_evt = 'Y' and p_delete_life_evt = 'N'
then
If p_person_id is not null then
delete from ben_ptnl_ler_for_per p
where p.ptnl_ler_for_per_stat_cd = 'VOIDD'
and p.business_group_id = p_business_group_id
and p.person_id = p_person_id
and not exists ( select 1
from ben_per_in_ler pil
where pil.PTNL_LER_FOR_PER_ID = p.PTNL_LER_FOR_PER_ID);
delete from ben_ptnl_ler_for_per p
where p.ptnl_ler_for_per_stat_cd = 'VOIDD'
and p.business_group_id = p_business_group_id
and not exists ( select 1
from ben_per_in_ler pil
where pil.PTNL_LER_FOR_PER_ID = p.PTNL_LER_FOR_PER_ID);
ben_batch_utils.write(p_text => ' No. of Voided Potentials Deleted = ' || sql%rowcount );
delete from ben_elig_cvrd_dpnt_f t
where business_group_id = p_business_group_id
and not exists ( select 1 from ben_per_in_ler p
where p.per_in_ler_id = t.per_in_ler_id ) ;
delete from ben_pl_bnf_f t
where business_group_id = p_business_group_id
and not exists ( select 1 from ben_per_in_ler p
where p.per_in_ler_id = t.per_in_ler_id );
,p_person_selected => l_person_actn_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => l_person_actn_cnt
,p_business_group_id => p_business_group_id);
,p_delete_life_evt in varchar2
)
is
--
-- Local variable declaration
--
l_effective_date date;
select ran.range_id
,ran.starting_person_action_id
,ran.ending_person_action_id
from ben_batch_ranges ran
where ran.range_status_cd = 'U'
and ran.benefit_action_id = p_benefit_action_id
and rownum < 2
for update of ran.range_status_cd;
select ben.person_id
,ben.person_action_id
,ben.object_version_number
from ben_person_actions ben
where ben.benefit_action_id = p_benefit_action_id
and ben.action_status_cd <> 'P'
and ben.person_action_id between l_start_person_action_id
and l_end_person_action_id
order by ben.person_action_id;
select *
from ben_benefit_actions ben
where ben.benefit_action_id = p_benefit_action_id;
,p_person_selection_rule_id => l_parm.person_selection_rl
,p_location_id => l_parm.location_id
,p_ler_id => l_parm.ler_id
,p_organization_id => l_parm.organization_id
,p_benfts_grp_id => l_parm.benfts_grp_id
,p_legal_entity_id => l_parm.legal_entity_id
,p_payroll_id => l_parm.payroll_id
,p_from_ocrd_date => l_parm.date_from
,p_to_ocrd_date => l_parm.lf_evt_ocrd_dt
,p_life_evt_typ_cd => l_parm.ptnl_ler_for_per_stat_cd
,p_bckt_stat_cd => l_parm.elig_enrol_cd
,p_delete_life_evt => l_parm.debug_messages_flag
,p_audit_log => p_audit_log_flag);
update ben_batch_ranges ran
set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
hr_utility.set_location('Updated range ' || to_char(l_range_id) ||
' status code to P', 10);
g_cache_per_proc.delete;
update ben_person_actions
set action_status_cd = 'T'
where person_action_id = l_person_action_id;
,p_delete_life_evt => p_delete_life_evt
);
update ben_person_actions
set action_status_cd = 'E'
where person_action_id = g_cache_per_proc(l_cnt).person_action_id;
benutils.write(p_text => 'Table Name No. Of Rows Deleted');
select --to_char(process_date, 'YYYY/MM/DD HH24:MI:SS') process_date
fnd_date.date_to_canonical(process_date) process_date
,business_group_id
,person_id
,person_selection_rl
--,life_event_id
--,from_ocrd_date
,lf_evt_ocrd_dt -- mapped to t_ocrd_date
,organization_id
,location_id
,benfts_grp_id
,legal_entity_id
,payroll_id
,CM_TRGR_TYP_CD -- mapped to lf_evt_typ_cd
,PTNL_LER_FOR_PER_STAT_CD -- mapped to bck_stat_cd
,audit_log_flag
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
,p_Person_selection_rl => l_parameters.Person_selection_rl
--,p_life_event_id => l_parameters.life_event_id
--,p_from_ocrd_date => l_parameters.
,p_to_ocrd_date => l_parameters.lf_evt_ocrd_dt
,p_organization_id => l_parameters.organization_id
,p_location_id => l_parameters.location_id
,p_benfts_grp_id => l_parameters.benfts_grp_id
,p_legal_entity_id => l_parameters.legal_entity_id
,p_payroll_id => l_parameters.payroll_id
,p_life_evt_typ_cd => l_parameters.CM_TRGR_TYP_CD
,p_bckt_stat_cd => l_parameters.PTNL_LER_FOR_PER_STAT_CD
,p_audit_log_flag => l_parameters.audit_log_flag
);
,p_delete_life_evt in varchar2
)
is
--
l_from_ocrd_date date;
select per_in_ler_id
from ben_per_in_ler pil
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd in ('BCKDT','VOIDD')
and (p_life_event_id is null or pil.ler_id = p_life_event_id)
and pil.lf_evt_ocrd_dt between l_from_ocrd_date and p_to_ocrd_date
and (p_life_evt_typ_cd is null or
exists (select null
from ben_ler_f ler
where ler.ler_id = pil.ler_id
and ler.typ_cd = p_life_evt_typ_cd
and pil.lf_evt_ocrd_dt between ler.effective_start_date and
ler.effective_end_date));
select per_in_ler_id
from ben_per_in_ler pil
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd in ('VOIDD')
and (p_life_event_id is null or pil.ler_id = p_life_event_id)
and pil.lf_evt_ocrd_dt between l_from_ocrd_date and p_to_ocrd_date
and (p_life_evt_typ_cd is null or
exists (select null
from ben_ler_f ler
where ler.ler_id = pil.ler_id
and ler.typ_cd = p_life_evt_typ_cd
and pil.lf_evt_ocrd_dt between ler.effective_start_date and
ler.effective_end_date));
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id;
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd in ('BCKDT','VOIDD');
hr_utility.set_location('delete elig per',2);
delete from ben_elig_per_f pep
where pep.per_in_ler_id = t_per_in_ler(i);
delete from ben_elig_per_opt_f epo
where epo.per_in_ler_id = t_per_in_ler(i);
hr_utility.set_location('delete elig per',3);
delete from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = t_per_in_ler2(i);
t_elctbl_chc.delete;
t_rslt.delete; -- Added while fixing 3670708
hr_utility.set_location('delete enrt rt',1);
delete from ben_enrt_rt
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_enrt_rt
where enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = t_elctbl_chc(i));
delete from ben_enrt_prem
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_enrt_prem
where enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = t_elctbl_chc(i));
delete from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_elctbl_chc_ctfn
where enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = t_elctbl_chc(i));
delete from ben_enrt_bnft
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_pil_elctbl_chc_popl pel
where pel.pil_elctbl_chc_popl_id =
(select pil_elctbl_chc_popl_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = t_elctbl_chc(i));
delete from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_elig_dpnt
where elig_per_elctbl_chc_id = t_elctbl_chc(i);
delete from ben_prtt_rt_val
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_prtt_enrt_actn_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_prtt_prem_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_prtt_enrt_ctfn_prvdd_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_elig_cvrd_dpnt_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_pl_bnf_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_prmry_care_prvdr_f
where prtt_enrt_rslt_id = t_rslt(i);
delete from ben_le_clsn_n_rstr
where per_in_ler_id = t_per_in_ler2(i);
if p_delete_life_evt = 'Y' then
forall i in 1..t_per_in_ler2.last
delete from ben_ptnl_ler_for_per
where ptnl_ler_for_per_id = (select ptnl_ler_for_per_id
from ben_per_in_ler pil
where per_in_ler_id = t_per_in_ler2(i))
and ptnl_ler_for_per_stat_cd = 'VOIDD';
delete from ben_per_in_ler
where per_in_ler_id = t_per_in_ler2(i);