[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* in person_selection_rule then skip the person.
* -----------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
g_package varchar2(80) := 'ben_evaluate_elig_conc';
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 *
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
select ppf.*
from per_all_people_f ppf
where ppf.person_id = l_person_id
and business_group_id = p_business_group_id
and nvl(l_effective_date,trunc(sysdate))
between ppf.effective_start_date
and ppf.effective_end_date;
select elig_obj_id
from ben_elig_obj_f
where (p_elig_obj_id is null or
elig_obj_id = p_elig_obj_id)
and l_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id
and table_name = p_elig_obj_type;
,p_person_selection_rule_id => l_parm.person_selection_rl
,p_organization_id => l_parm.organization_id
,p_benfts_grp_id => l_parm.benfts_grp_id
,p_location_id => l_parm.location_id
,p_legal_entity_id => l_parm.legal_entity_id);
update ben_batch_ranges ran
set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
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
,pl_id
,concat_segs
,business_group_id
,person_selection_rl
,los_det_to_use_cd
,organization_id
,location_id
,benfts_grp_id
,legal_entity_id
,debug_messages_flag
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_debug_messages in varchar2 default 'N') is
--
l_effective_date date;
select ppf.person_id
from per_all_people_f ppf
where ppf.business_group_id = p_business_group_id
and l_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and (p_person_id is null or
ppf.person_id = p_person_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))) ;
,p_person_selection_rl => p_person_selection_rule_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 => null
,p_rptg_grp_id => null
,p_eligy_prfl_id => null
,p_legal_entity_id => p_legal_entity_id
,p_debug_messages_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);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
If p_person_selection_rule_id is not NULL then
--
rl_ret := 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_person_selection_rule_id => p_person_selection_rule_id
,p_person_id => p_person_id
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_legal_entity_id => p_legal_entity_id);
,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
) ;