[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select *
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
buff => 'Person Selection Rule :'||
benutils.iftrue
(p_expression => l_parm.person_selection_rl is null,
p_true => 'None',
p_false => l_parm.person_selection_rl));
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
from ben_person_actions ben
where ben.benefit_action_id = p_benefit_action_id
and ben.action_status_cd not in ('P','E')
and ben.person_action_id
between l_start_person_action_id
and l_end_person_action_id
order by ben.person_action_id;
select ppf.full_name, pil.per_in_ler_id,
pil.person_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
ler.typ_cd,
ler.ler_id,
ler.name,
pel.pil_elctbl_chc_popl_id, pel.enrt_perd_strt_dt,
pel.enrt_perd_end_dt, pel.procg_end_dt, pel.dflt_enrt_dt,
pel.object_version_number
from ben_per_in_ler pil,
ben_ler_f ler,
ben_pil_elctbl_chc_popl pel,
per_all_people_f ppf
where pil.person_id = l_person_id
and pil.lf_evt_ocrd_dt = nvl(l_lf_evt_ocrd_dt,pil.lf_evt_ocrd_dt)
and pil.business_group_id+0 = p_business_group_id
and pil.ler_id = nvl(p_ler_id,pil.ler_id)
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.typ_cd not in ('SCHEDDU','GSP','ABS','COMP')
and ler.ler_id = pil.ler_id
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
and pel.per_in_ler_id = pil.per_in_ler_id
and ((p_pl_id is null and pgm_id = nvl(p_pgm_id, pgm_id)) or
(p_pgm_id is null and pl_id = nvl(p_pl_id, pl_id)))
and pel.business_group_id = p_business_group_id
and ppf.person_id = pil.person_id
and nvl(l_effective_date,trunc(sysdate))
between ppf.effective_start_date
and ppf.effective_end_date
order by pel.pil_elctbl_chc_popl_id;
update ben_batch_ranges ran
set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
BEN_pil_elctbl_chc_popl_API.update_pil_elctbl_chc_popl
(p_validate => FALSE
,p_PIL_ELCTBL_CHC_POPL_ID => l_pel.pil_elctbl_chc_popl_id
,p_DFLT_ENRT_DT => l_new_dflt_enrt_dt
,p_ENRT_PERD_END_DT => l_new_enrt_perd_end_dt
,p_PROCG_END_DT => l_new_procg_end_dt
,p_OBJECT_VERSION_NUMBER => l_pel.object_version_number
,p_effective_date => l_effective_date
);
update ben_person_actions
set action_status_cd = 'P'
where person_id = l_person_id
and benefit_action_id = p_benefit_action_id;
update ben_person_actions
set action_status_cd = 'E'
where person_id = l_person_id
and benefit_action_id = p_benefit_action_id;
Select process_date
,mode_cd
,validate_flag
,person_id
,business_group_id
,person_selection_rl
,ler_id
,organization_id
,location_id
,benfts_grp_id
,pgm_id
,pl_id
,pstl_zip_rng_id
,rptg_grp_id
,legal_entity_id
,payroll_id
,debug_messages_flag
,object_version_number
,request_id
,program_application_id
,program_id
,program_update_date
,bft_attribute3
,bft_attribute4
,bft_attribute5
,bft_attribute6
,bft_attribute7
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
,p_person_selection_rule_id => l_parameters.person_selection_rl
,p_debug_messages => l_parameters.debug_messages_flag);
,p_person_selection_rule_id in number default null
,p_business_group_id in number
,p_pgm_id in number default null
,p_pl_id in number default null
,p_lf_evt_ocrd_dt in varchar2 default null
,p_ler_id in number default null
,p_new_enrt_perd_end_dt in varchar2 default null
,p_new_procg_end_dt in varchar2 default null
,p_new_dflt_enrt_dt in varchar2 default null
,p_no_of_days in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_audit_log_flag in varchar2 default 'N') is
--
l_effective_date date;
select ppf.person_id
from per_all_people_f ppf
where ppf.person_id = nvl(p_person_id, ppf.person_id)
and ppf.business_group_id = p_business_group_id
and l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and exists (select null
from ben_per_in_ler pil
, ben_ler_f ler
where pil.lf_evt_ocrd_dt = nvl(l_lf_evt_ocrd_dt, pil.lf_evt_ocrd_dt)
and pil.ler_id = ler.ler_id
and l_effective_date between ler.effective_start_date
and ler.effective_end_date
and ler.business_group_id = p_business_group_id
and ler.typ_cd not in ('SCHEDDU','GSP','ABS','COMP')
and pil.ler_id = nvl(p_ler_id, pil.ler_id)
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.business_group_id = p_business_group_id
and pil.person_id = ppf.person_id
and pil.business_group_id = ppf.business_group_id
and exists (select pil_elctbl_chc_popl_id
from ben_pil_elctbl_chc_popl
where per_in_ler_id = pil.per_in_ler_id
and ((p_pl_id is null and pgm_id = nvl(p_pgm_id, pgm_id)) or
(p_pgm_id is null and pl_id = nvl(p_pl_id, pl_id)))))
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 pay_payrolls_f pay,
per_all_assignments_f paf
where paf.person_id = ppf.person_id
and paf.assignment_type <> 'C'
and paf.primary_flag = 'Y'
and paf.business_group_id = ppf.business_group_id
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date
and pay.payroll_id = p_payroll_id
and pay.payroll_id = paf.payroll_id
and l_effective_date
between pay.effective_start_date
and pay.effective_end_date))
and (p_pstl_zip_rng_id is null
or exists (select null
from per_addresses pad,
ben_pstl_zip_rng_f rzr
where pad.person_id = ppf.person_id
and pad.primary_flag = 'Y'
and l_effective_date
between nvl(pad.date_from,l_effective_date)
and nvl(pad.date_to,l_effective_date)
and rzr.pstl_zip_rng_id = p_pstl_zip_rng_id
and pad.postal_code
between rzr.from_value
and rzr.to_value
and l_effective_date
between rzr.effective_start_date
and rzr.effective_end_date));
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_pstl_zip_rng_id => p_pstl_zip_rng_id
,p_rptg_grp_id => p_rptg_grp_id
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_debug_messages_flag => p_audit_log_flag
,p_no_programs_flag => 'N'
,p_no_plans_flag => 'N'
,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
,p_bft_attribute3 => p_new_enrt_perd_end_dt
,p_bft_attribute4 => p_new_procg_end_dt
,p_bft_attribute5 => p_new_dflt_enrt_dt
,p_bft_attribute6 => p_no_of_days
-- added bug 5611643
,p_bft_attribute7 => p_lf_evt_ocrd_dt
);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
If p_person_selection_rule_id is not NULL then
--
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
) ;