[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 08-Jun-02 pabodla 115.13 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 09-Jun-02 pbodla 115.14 Bug 2547536 : Backout the dummy per in
* ler's (Associated with managers)
* created in cross business group.
* 19-DEC-02 nhunur No copy.
* 27-Apr-03 mmudigon 115.16 Absences July FP enhancements
* 08-Sep-03 pbodla 115.17 When backout process errors for a
* person subsequent life events are
* not backed out for the thread.
* 09-Sep-03 rpgupta 115.18 3136058 Grade step backout
* 20-Aug-04 nhunur 115.19 3840255 - Changed person selecton rule exception handling.
* 03-Dec-04 swjain 115.20 4034201 - passed p_bckt_stat_cd as input parameter for
* p_ptnl_ler_for_per_stat_cd in create_benefit_actions call.
* 15-Jun-10 velvanop 115.21 Bug 9816446 - Fixed cursor c_latest_ler not to consider future Absence(ABS) LE's
* when backout process is run
* 22-Jul-10 velvanop 115.23 Bug 9818852 - When Back-out Life Events Process is run Rollback mode, data is commited
* 22-Nov-12 velvanop 115.24 Bug 15829093: Replaced open-fetch-close with Cursor FOR..Loop for the
cursor c_ler_thread
* -----------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
g_package varchar2(80) := 'ben_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,
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
between l_from_ocrd_date
and l_to_ocrd_date
and pil.business_group_id+0 = p_business_group_id
and ((p_abs_ler = 'N' and
pil.ler_id = p_life_event_id
and
( ( ler.typ_cd = 'GSP'
and pil.per_in_ler_stat_cd = 'STRTD'
)
or
( ler.typ_cd <> 'GSP'
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
)
)
) or
(p_abs_ler = 'Y' and
pil.ler_id in
(select ler.ler_id
from ben_ler_f ler
where ler.typ_cd = 'ABS'
and ler.lf_evt_oper_cd in ('START','END')
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and ler.business_group_id = p_business_group_id
and l_effective_date between ler.effective_start_date
and ler.effective_end_date)))
and ler.ler_id = pil.ler_id
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.person_id desc,
pil.lf_evt_ocrd_dt desc,
decode(ler.lf_evt_oper_cd,'END',2,1) desc;
select 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
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.lf_evt_ocrd_dt
between l_ler_thread.lf_evt_ocrd_dt
and l_to_ocrd_date
and pil.business_group_id+0 = p_business_group_id
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 pil.per_in_ler_id <> l_ler_thread.per_in_ler_id
and ler.lf_evt_oper_cd <> 'DEL'
order by pil.person_id desc,
pil.lf_evt_ocrd_dt desc,
decode(ler.lf_evt_oper_cd,'END',2,1) desc;
Select *
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
select pil.per_in_ler_id,
ler.name
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.business_group_id+0 = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd not in ('SCHEDDU','COMP', 'GSP', 'ABS') --Bug 9816446: Added ABS typ_cd
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
select pil.per_in_ler_id,
ler.name
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.business_group_id+0 = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd not in ('ABS','SCHEDDU','COMP', 'GSP')
and pil.lf_evt_ocrd_dt >= l_from_ocrd_date
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
select pil.per_in_ler_id,
ler.name
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.business_group_id+0 = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'COMP'
and ler.ler_id = p_life_event_id
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
select pil.per_in_ler_id,
ler.name
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = l_person_id
and pil.business_group_id+0 = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'GSP'
and ler.ler_id = p_life_event_id
and nvl(l_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date
order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
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;
commit statement(after update statement), backed out results of successfully processed employees are committed.
When Back-out Life Events Process is run Rollback mode, only ben_person_actions table update should be commited.
For a employee for whom the results are successfully backed out, results should be rolled back */
hr_utility.set_location('before rollback',10);
hr_utility.set_location('before update',10);
update ben_person_actions
set action_status_cd = 'P'
where person_id = l_person_id
and benefit_action_id = p_benefit_action_id;
hr_utility.set_location('after update',10);
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
,popl_enrt_typ_cycl_id
,person_selection_rl
,ler_id
,organization_id
,location_id
,benfts_grp_id
,legal_entity_id
,debug_messages_flag
,date_from
,uneai_effective_date
,ptnl_ler_for_per_stat_cd
,inelg_action_cd
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'
,p_bckt_stat_cd in varchar2 default 'UNPROCD'
,p_abs_ler in varchar2 default 'N') is
--
l_effective_date date;
select ppf.person_id
from per_all_people_f ppf
where -- bug 2547536 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 between l_from_ocrd_date
and l_to_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 ((p_abs_ler = 'N'
and pil.ler_id = p_life_event_id
and
( ( ler.typ_cd = 'GSP'
and pil.per_in_ler_stat_cd = 'STRTD'
)
or
( ler.typ_cd <> 'GSP'
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
)
)
)
or
(p_abs_ler = 'Y'
and ler.typ_cd = 'ABS'
and ler.lf_evt_oper_cd in ('START','END')
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
)
)
and pil.business_group_id = p_business_group_id
and pil.person_id = ppf.person_id
)
-- Bug 2547536 -- and pil.business_group_id = ppf.business_group_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))) ;
select ppf.person_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 ler.ler_id = pil.ler_id
and ler.typ_cd = 'COMP'
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
and pil.lf_evt_ocrd_dt
between l_from_ocrd_date
and l_to_ocrd_date
and pil.ler_id = p_life_event_id
and pil.person_id = ppf.person_id
and pil.business_group_id = p_business_group_id);
,p_comp_selection_rl => null
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => p_life_event_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_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
,p_date_from => l_from_ocrd_date
,p_uneai_effective_date => l_to_ocrd_date
--Bug No 4034201
,p_ptnl_ler_for_per_stat_cd => p_bckt_stat_cd);
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_selection_rule_id => p_person_selection_rule_id
,p_ler_id => p_life_event_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
) ;