[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Though iRec doesnt need multithreading, person selection etc,
* currently this concurrent program have all those capabilities
* that incase they need it in future, we can start using that.
*
* History
* Date Who Version What?
* ------- --------- ------- --------------------------------------
* 8-Sep-2004 hmani 115.0 Created
* 30-Sep-2004 hmani 115.1 Added self-service/wrapper proc
* -----------------------------------------------------------------------------
*/
/* global variables */
g_package varchar2(80) := 'ben_irec_back_out_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 pil.per_in_ler_id,
pil.person_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.business_group_id,
ler.typ_cd,
ler.ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.lf_evt_ocrd_dt = l_ocrd_date --between l_from_ocrd_date and l_to_ocrd_date
-- and pil.ler_id = p_life_event_id
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.assignment_id = p_assignment_id
and pil.person_id = p_person_id
and ler.ler_id = pil.ler_id
and ler.typ_cd = 'IREC'
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.person_id desc;
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 nvl(l_effective_date,trunc(sysdate))
between ppf.effective_start_date
and ppf.effective_end_date;
,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;
,p_person_selection_rule_id in number default null
,p_debug_messages in varchar2 default 'N'
,p_bckt_stat_cd in varchar2 default 'VOIDD'
) is
/* local variable defintions */
l_proc varchar2(80) := g_package||'.process';
l_person_selection number;
select distinct ppf.person_id, ppf.business_group_id
from per_all_people_f ppf
where 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 = l_ocrd_date
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 pil.ler_id = p_life_event_id
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.person_id = ppf.person_id
and pil.person_id = p_person_id
and pil.assignment_id = p_assignment_id
and ler.typ_cd ='IREC'
)
and exists ( select null from per_person_type_usages_f ptu,
per_person_types ppt, per_all_assignments_f apl_ass
where ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in( 'APL', 'APL_EX_APL','EMP_APL', 'EX_EMP_APL')
and ppt.business_group_id = ppf.business_group_id
and apl_ass.business_group_id = p_business_group_id
and ptu.person_id = ppf.person_id
and l_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and apl_ass.person_id = ppf.person_id
and apl_ass.assignment_id = p_assignment_id
and apl_ass.assignment_type ='A'
and l_effective_date between apl_ass.effective_start_date
and apl_ass.effective_end_date
) ;
cursor c_person_selection (cv_formula_id number
, cv_business_group_id number
, cv_effective_date date
) is
select fff.formula_id
from ff_formulas_f fff,
ff_formulas_f fff1
where fff.business_group_id = cv_business_group_id
and cv_effective_date between fff.effective_start_date
and fff.effective_end_date
and fff.formula_name = fff1.formula_name
and cv_effective_date between fff1.effective_start_date
and fff1.effective_end_date
and fff1.formula_id = cv_formula_id;
select a.ler_id
from ben_ler_f a
where a.business_group_id = p_business_group_id
and a.typ_cd = 'IREC'
and a.ler_id = p_life_event_id
and cv_effective_date between a.effective_start_date and
nvl(a.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'));
hr_utility.set_location ('p_person_selection_rule_id '||p_person_selection_rule_id,10);
,p_comp_selection_rl => null
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => p_life_event_id
,p_organization_id => null
,p_benfts_grp_id => null
,p_location_id => null
,p_pstl_zip_rng_id => p_assignment_id -- Note that we have reused
,p_rptg_grp_id => null
,p_opt_id => null
,p_eligy_prfl_id => null
,p_vrbl_rt_prfl_id => null
,p_legal_entity_id => null
,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
,p_date_from => l_ocrd_date
,p_uneai_effective_date => null);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
l_person_selection := null;
hr_utility.set_location ('next person selected is '||l_person_id,30);
If p_person_selection_rule_id is not NULL then
--
open c_person_selection (p_person_selection_rule_id, l_business_group_id, l_ocrd_date);
fetch c_person_selection into l_person_selection;
close c_person_selection;
if l_person_selection is not null then
--
--
rl_ret := ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => l_business_group_id
,p_person_selection_rule_id=> l_person_selection--p_person_selection_rule_id
,p_effective_date => l_effective_date);
else --l_person_selection is null --
skip := TRUE; --
hr_utility.set_location ('person passed selection rule '||l_person_id,35);
,p_person_selection_rule_id => p_person_selection_rule_id
,p_ler_id => p_life_event_id
,p_organization_id => null
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => null);
hr_utility.set_location('No person selected ',999);
,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
) ;
Select process_date
,mode_cd
,validate_flag
,business_group_id
,person_selection_rl
,ler_id
,debug_messages_flag
,date_from
,ptnl_ler_for_per_stat_cd
,person_id
, pstl_zip_rng_id -- using this for assignment id
-- ,pl_id
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);
select ler_id, to_char(lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS'), person_id, assignment_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.lf_evt_ocrd_dt <= p_effective_date;