[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
,ben.ler_id
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 => null
,p_comp_selection_rule_id => l_parm.comp_selection_rl
,p_pgm_id => l_parm.pgm_id
,p_pl_typ_id => l_parm.pl_typ_id
,p_pl_id => l_parm.pl_id
,p_person_type_id => l_parm.person_type_id
,p_ler_id => null
,p_organization_id => null
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => null
,p_payroll_id => null
);
update ben_batch_ranges ran set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
g_cache_person_process.delete;
,p_comp_selection_rule_id in number default null
,p_debug_messages in varchar2 default 'N'
,p_first_day_of_month in varchar2
,p_mo_num in number
,p_yr_num in number
,p_threads in number
,p_chunk_size in number
,p_max_errors in number
,p_restart in boolean default FALSE ) is
--
-- Cursors declaration.
--
-- Premiums to be processed:
cursor c_prems (p_effective_date date) is
select apr.actl_prem_id, apr.oipl_id, apr.pl_id
from ben_actl_prem_f apr
where apr.prem_asnmt_cd = 'PROC' -- ENRT are dealt with in benprprm.pkb
and apr.business_group_id = p_business_group_id
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
l_actn := 'Calling ben_batch_utils.comp_obj_selection_rule...';
or p_comp_selection_rule_id is not null then
rl_ret := 'Y';
elsif rl_ret = 'Y' and p_comp_selection_rule_id is not null then
l_actn := 'found a comp object rule...';
rl_ret:=ben_maintain_designee_elig.comp_selection_rule(
p_person_id => null -- we have no person_id
,p_business_group_id => p_business_group_id
,p_pgm_id => l_pgm_id
,p_pl_id => l_pl_id
,p_pl_typ_id => l_pl_typ_id
,p_opt_id => l_opt_id
,p_oipl_id => l_prems.oipl_id
,p_ler_id => null -- we have no ler_id
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_effective_date => l_effective_date);
hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
l_actn := 'Inserting Ben_person_actions...';
select ben_person_actions_s.nextval
into l_person_action_id
from sys.dual;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number,
chunk_number,
non_person_cd)
values
(l_person_action_id,
l_prems.actl_prem_id,
0,
p_benefit_action_id,
'U',
1,
l_chunk_num,
'PREM');
l_actn := 'Inserting Ben_batch_ranges.......';
hr_utility.set_location('Inserting Ben_batch_ranges',32);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
p_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_actn := 'Inserting Final Ben_batch_ranges...';
hr_utility.set_location('Inserting Final Ben_batch_ranges',38);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
p_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_pgm_id => p_pgm_id
,p_pl_typ_id => p_pl_typ_id
,p_pl_id => p_pl_id
,p_popl_enrt_typ_cycl_id => null
,p_person_id => null
,p_person_selection_rule_id => null
,p_person_type_id => null
,p_ler_id => null
,p_organization_id => null
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => null
,p_payroll_id => null
);
'<< No Process Premiums were selected with above selection criteria >>' );
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
,p_non_person_cd => 'PREM');
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
) ;