[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 08-Jun-02 pabodla 115.10 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 17-Jun-02 vsethi 115.11 Modified the person determination
* criteria to include organization and
* legal entity
* 18-Jun-02 vsethi 115.12 Modified the sub_query in c_person cursor
* to refer the person_id of outer query and
* not p_person_id
* 30-Dec-2002 mmudigon 115.14 NOCOPY
* 07-Jan-2003 rpgupta 115.15 Removed l_return from procedure
* prem_person_selection_rule as formula was
* not being picked up
* 04-Jun-2006 swjain 115.16 Bug 5331889 - passed person_id as input param
* in prem_person_selection_rule and added input1 as
* additional param for future use
* -----------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
g_package varchar2(80) := 'Ben_premium_concurrent';
procedure prem_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_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||'.prem_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 prem_person_selection_rule;
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 => l_parm.person_selection_rl
,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 => l_parm.organization_id
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => l_parm.legal_entity_id
,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_rl => 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_object_version_number => g_cache_person_process(l_cnt).object_version_number
,p_business_group_id => p_business_group_id
,p_mo_num => p_mo_num
,p_yr_num => p_yr_num
,p_first_day_of_month => l_first_day_of_month
,p_effective_date => l_effective_date
);
Select process_date
,mode_cd
,validate_flag
,person_id
,pgm_id
,pl_typ_id
,pl_id
,business_group_id
,popl_enrt_typ_cycl_id
,person_selection_rl
,comp_selection_rl
,ler_id
,organization_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_comp_selection_rule_id => l_parameters.comp_selection_rl
,p_organization_id => l_parameters.organization_id
,p_legal_entity_id => l_parameters.legal_entity_id
,p_debug_messages => l_parameters.debug_messages_flag
);
,p_person_selection_rule_id in number default null
,p_comp_selection_rule_id in number default null
,p_organization_id in number default null
,p_legal_entity_id in number default null
,p_debug_messages in varchar2 default 'N'
) is
cursor c_person (p_effective_date date) is
select distinct pen.person_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
-- cvg starts sometime before end of next month:
and pen.enrt_cvg_strt_dt <= add_months(p_effective_date,1)
-- check criteria user entered on the submit form:
and (pen.person_id = p_person_id or p_person_id is null)
and (pen.pl_id = p_pl_id or p_pl_id is null)
and (pen.pl_typ_id = p_pl_typ_id or p_pl_typ_id is null)
and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
and pen.business_group_id+0 = p_business_group_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and (p_organization_id is null
or exists (select null from per_all_assignments_f
where person_id = pen.person_id
and business_group_id = p_business_group_id
and p_effective_date between nvl(effective_start_date,p_effective_date )
and nvl(effective_end_date, p_effective_date )
and primary_flag = 'Y'
and organization_id = p_organization_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 = pen.person_id
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = p_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_comp_selection_rl => p_comp_selection_rule_id
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => null
,p_location_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 => p_legal_entity_id
,p_payroll_id => null
,p_debug_messages_flag => p_debug_messages
,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
);
l_actn := 'Delete rows from ben_batch_ranges..';
hr_utility.set_location('Delete rows from ben_batch_ranges',16);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
If (p_person_selection_rule_id is not NULL) then
l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
/* 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
);
prem_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 => rl_ret
,p_err_message => l_err_message ) ;
hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
,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 => p_person_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_person_type_id => null
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => null
);
'<< No Person was selected for Participant Premiums with above selection criteria >>' );
if p_person_id is null and p_person_selection_rule_id is null then
-- only process comp object premiums if no person criteria was selected
ben_premium_plan_concurrent.process
(errbuf => l_errbuf
,retcode => l_retcode
,p_benefit_action_id => l_benefit_action_id
,p_effective_date => l_effective_date_char
,p_validate => p_validate
,p_pgm_id => p_pgm_id
,p_pl_typ_id => p_pl_typ_id
,p_pl_id => p_pl_id
,p_business_group_id => p_business_group_id
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_debug_messages => p_debug_messages
,p_mo_num => l_mo_num
,p_yr_num => l_yr_num
,p_first_day_of_month => l_first_day_of_month_char
,p_threads => l_threads
,p_chunk_size => l_chunk_size
,p_max_errors => g_max_errors_allowed
,p_restart => l_restart);
,p_person_selection_rule_id => p_person_selection_rule_id
,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_organization_id => p_organization_id
,p_legal_entity_id => p_legal_entity_id
,p_business_group_id => p_business_group_id
,p_mo_num => l_mo_num
,p_yr_num => l_yr_num
,p_first_day_of_month => l_first_day_of_month
,p_effective_date => l_effective_date);
,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
) ;