[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
03-Mar-04 rpgupta 115.4 1. Commented delete dmls and added calls to api's
19-Mar-04 pbodla 115.5 Bug 3517726 : CWB data is not getting deleted.
23-Mar-04 nhunur 115.6 removed if clause before cwb delete,added distinct
clause for person rates cursor.
25-Mar-04 rpgupta 115.7 Changed logic for person selection to work like
participation process.
26-Mar-04 pbodla 115.8 Added code to delete
BEN_CWB_PERSON_INFO,
BEN_CWB_SUMMARY,
ben_cwb_pl_dsgn
26-Mar-04 pbodla 115.9 l_ocrd_date need to be passed to
BEN_CWB_PL_DSGN_PKG.delete_pl_dsgn
27-Apr-04 rpgupta 115.13 bug 3517726 - elete records in ben_cwb_person_rates
with the given group_pl_id, ler_id and life event
ocrd date. Sometimes when one thread of benmngle
fails, theres a possibility that a few records in
person_rates exist with group_per_in_ler_id as -1
27-Apr-04 rpgupta 115.14/15 Added online backout procedure/trace calls.
01-Feb-2005 steotia 115.16 cwb_delete_routine and ben_cwb_person_info API
used to delete person_info record
11-Feb-2005 pbodla 115.17 Bug 4021004 : when group per in ler
is backed out it is not backing out the
heirarchy data and not resetting
heirarchy data for reporting employees
linked to this PIL.
23-Feb-2005 pbodla 115.18 4109090 : Removed the coun(*) statements which
are causing performance problems.
28-Feb-2005 nhunur 115.19 added code to close cursors in all conditions
13-apr-2005 nhunur 115.20 bug 4300599 - added code to handle person sel rule exceptions.
23-sep-2005 pbodla 115.21 bug 4598824 - Romove element entry if
life event backed out.
Added get_ele_dt_del_mode, backout_cwb_element
12-oct-2005 pbodla 115.22 bug 4653929 - Backout is not deleting
cwb plan design data as some life events
are not getting deleted : some cases
are - person hired and processed after
the life event occured date.
- As heirarchy data is in contention
it should be moved out of multi thread
30-Nov-2005 pbodla 115.23 Bug 4758468 : join condition is missing
in c_group_pils.
08-Mar-2006 stee 115.24 Bug 5060080 : Fix cursor to not delete
all the person rates for a plan and
life event occurred date. If there
is more than 1 life event for the
person, the person rates are also
deleted and the element entries are
not backed out.
06-Apr-2006 abparekh 115.25 Bug 5130397 : When CWB plan has options attached,
then while backing out BEN_CWB_PERSON_RATES rows
delete pay proposal only once
25-May-2006 ikasired 115.26 Bug 5240208 fix for heirarchy issue for
reassign, backout and reprocess issue
26-May-2006 maagrawa 115.27 Always run summary refresh at end
when running backout in batch.
In online mode, call delete apis
with update_summary ON.
21-aug-06 nhunur 115.28 Report any broken hierarchies, people who do not have
a level 1 manager but have a worksheet manager id
20-sep-06 nhunur 115.29 Reformat the list of people and change message.
06-Feb-07 maagrawa 115.30 When more than 1 person_rates record
exists for a group_per_in_ler_id,
pl_id, oipl_id combination, you get
a error when calling delete api for
2nd record.
* -----------------------------------------------------------------------------
*/
/* global variables */
g_package varchar2(80) := 'ben_cwb_back_out_conc';
select rowid
from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_per_in_ler_id;
l_proc varchar2(50) := g_package||'.cwb_delete_routine';
delete from ben_cwb_group_hrchy hrc
where emp_per_in_ler_id = p_per_in_ler_id;
update ben_cwb_group_hrchy set
mgr_per_in_ler_id = -1 ,
LVL_NUM = -1
where rowid = l_chr.rowid;
select rowid, emp_per_in_ler_id
from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_per_in_ler_id;
l_proc varchar2(50) := g_package||'.cwb_delete_routine';
delete from ben_cwb_group_hrchy hrc
where emp_per_in_ler_id = p_per_in_ler_id;
update ben_cwb_group_hrchy set
mgr_per_in_ler_id = -1 ,
LVL_NUM = -1
where rowid = l_chr.rowid;
delete from ben_cwb_group_hrchy
where emp_per_in_ler_id = l_chr.emp_per_in_ler_id
and LVL_NUM > -1;
l_delete_mode boolean;
l_delete_next_change_mode boolean;
l_delete_start_date date;
l_delete_end_date date;
p_delete => l_delete_mode,
p_future_change => l_future_change_mode,
p_delete_next_change => l_delete_next_change_mode); /*,
p_delete_start_date => l_delete_start_date,
p_delete_end_date => l_delete_end_date,
p_del_future_start_date => l_del_future_start_date,
p_del_future_end_date => l_del_future_end_date,
p_del_next_start_date => l_del_next_start_date,
p_del_next_end_date => l_del_next_end_date);*/
hr_utility.set_location('l_delete_start_date = ' || l_delete_start_date, 12);
hr_utility.set_location('l_delete_end_date = ' || l_delete_end_date, 12);
if l_delete_mode then
hr_utility.set_location('l_delete_mode true', 13);
if l_delete_next_change_mode then
hr_utility.set_location('l_delete_next_change_mode true', 13);
if l_delete_next_change_mode = true or l_future_change_mode = true then
l_del_mode := hr_api.g_future_change;
,p_dt_delete_mode IN VARCHAR2 default null
,p_amt in number default null ) is
--
l_proc VARCHAR2(72) := 'backout_cwb_element';
l_delete_warning BOOLEAN;
l_dt_delete_mode varchar2(80);
select min(effective_start_date),
max(effective_end_date)
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select pel.element_link_id,
pel.element_type_id,
pev.input_value_id,
pet.element_name,
pet.processing_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.element_entry_value_id = p_element_entry_value_id
and pee.element_entry_id = pev.element_entry_id
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.effective_start_date between pet.effective_start_date
and pet.effective_end_date;
select asg.assignment_id,
asg.payroll_id,
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
pee.original_entry_id,
pee.entry_type,
pee.element_link_id,
pev.screen_entry_value
from per_all_assignments_f asg,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where asg.person_id = p_person_id
and pee.assignment_id = asg.assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and pee.creator_type = 'F'
and pee.entry_type = 'E'
and p_effective_date <= pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = p_element_type_id
and pev.element_entry_id = pee.element_entry_id
and pev.input_value_id = p_input_value_id
and (p_element_entry_value_id is null or
pev.element_entry_value_id = p_element_entry_value_id)
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
order by pee.effective_start_date ;
l_dt_delete_mode := get_ele_dt_del_mode(p_effective_date, l_element_entry_id);
l_dt_delete_mode := hr_api.g_zap;
l_dt_delete_mode := hr_api.g_zap;
hr_utility.set_location('l_dt_delete_mode = ' || l_dt_delete_mode, 9);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => l_dt_delete_mode
,p_effective_date => l_effective_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning);
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
,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 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
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.group_pl_id = p_group_pl_id -- CWBGLOBAL
and ler.ler_id = pil.ler_id
and ler.typ_cd = 'COMP' -- CWBGLOBAL
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 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.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'COMP'
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;
select ppf.*
from per_all_people_f ppf
where ppf.person_id = l_person_id
order by effective_start_date desc;
,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;
hr_utility.set_location ('calling delete_cwb_data',10);
fnd_message.set_token('PROC','delete_cwb_data');
delete_cwb_data
(p_per_in_ler_id => l_ler_thread.per_in_ler_id
, p_business_group_id => l_ler_thread.business_group_id
, p_update_summary => false
) ;
delete from ben_cwb_person_rates
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = l_ocrd_date
and group_per_in_ler_id = -1 -- Bug 5060080
and person_id = l_person_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 'UNPROCD'
) 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 */
-- Looks like p_life_event_id is not passed in
and ler.typ_cd = 'COMP'
and pil.ler_id = nvl(p_life_event_id, pil.ler_id)
/* life event id made non mandatory parameter*/
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.person_id = ppf.person_id
and pil.group_pl_id = p_group_pl_id
) ;
select distinct pil.person_id, pil.business_group_id
from ben_per_in_ler pil
where pil.lf_evt_ocrd_dt = l_ocrd_date
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.group_pl_id = p_group_pl_id;
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 pil.per_in_ler_id
from ben_person_actions act,
ben_per_in_ler pil
where act.benefit_action_id = l_benefit_action_id
and act.action_status_cd = 'P'
and act.person_id = pil.person_id
and pil.lf_evt_ocrd_dt = l_ocrd_date
and pil.per_in_ler_stat_cd = 'BCKDT'
and pil.business_group_id = cv_group_bg_id
and pil.group_pl_id = p_group_pl_id;
Select pln.business_group_id
from ben_pl_f pln
where pln.pl_id = p_group_pl_id
and l_ocrd_date between pln.effective_start_date
and pln.effective_end_date;
select inf.full_name, inf.person_id
from ben_cwb_person_info inf
,ben_per_in_ler pil
where pil.group_pl_id = cv_group_pl_id
and pil.lf_evt_ocrd_dt = cv_ocrd_date
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.ws_mgr_id is not null
and pil.per_in_ler_id = inf.group_per_in_ler_id
and not exists (select 'Y'
from ben_cwb_group_hrchy hrchy
where hrchy.emp_per_in_ler_id = pil.per_in_ler_id
and hrchy.lvl_num = 1) ;
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 => 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 => 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
--
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_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
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);
BEN_CWB_PL_DSGN_PKG.delete_pl_dsgn
(p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => l_ocrd_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
) ;
procedure cwb_delete_routine
(p_routine in varchar2
,p_per_in_ler_id in number
,p_update_summary in boolean
) is
-- CWBGLOBAL
cursor c_cwb_person_task is
select task_id, object_version_number
from ben_cwb_person_tasks
where group_per_in_ler_id = p_per_in_ler_id;
select group_pl_id, group_oipl_id, object_version_number
from ben_cwb_person_groups
where group_per_in_ler_id = p_per_in_ler_id;
select distinct pl_id, oipl_id, pay_proposal_id
from ben_cwb_person_rates
where group_per_in_ler_id = p_per_in_ler_id
order by pay_proposal_id;
select ELEMENT_ENTRY_VALUE_ID,
COMP_POSTING_DATE,
person_id,
object_version_number
from ben_cwb_person_rates
where group_per_in_ler_id = p_per_in_ler_id
and pl_id = l_pl_id
and oipl_id = l_oipl_id ;
select object_version_number, business_group_id
from per_pay_proposals
where pay_proposal_id = l_pay_proposal_id;
select object_version_number
from ben_cwb_person_info
where group_per_in_ler_id = p_per_in_ler_id;
l_proc varchar2(50) := g_package||'.cwb_delete_routine';
ben_cwb_person_tasks_api.delete_person_task
( p_validate => false,
p_group_per_in_ler_id => p_per_in_ler_id,
p_task_id => l_task_id,
p_object_version_number => l_object_version_number
);
BEN_CWB_PERSON_GROUPS_API.delete_group_budget
( p_validate => false,
p_group_per_in_ler_id => p_per_in_ler_id,
p_group_pl_id => l_group_pl_id,
p_group_oipl_id => l_group_oipl_id,
p_object_version_number => l_object_version_number,
p_update_summary => p_update_summary
);
ben_cwb_person_rates_api.delete_person_rate
(p_validate => false
,p_group_per_in_ler_id => p_per_in_ler_id
,p_pl_id => l_pl_id
,p_oipl_id => l_oipl_id
,p_object_version_number => l_object_version_number
,p_update_summary => p_update_summary) ;
hr_maintain_proposal_api.delete_salary_proposal
( p_pay_proposal_id => l_pay_proposal_id
,p_business_group_id => l_business_group_id_prop
,p_object_version_number => l_object_version_number_prop
,p_validate => false
,p_salary_warning => l_salary_warning ) ;
BEN_CWB_PERSON_INFO_API.delete_person_info
( p_validate => false,
p_group_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_object_version_number
);
end cwb_delete_routine;
procedure delete_cwb_data
(p_per_in_ler_id in number
,p_business_group_id in number
,p_update_summary in boolean default false
) is
l_proc varchar2(50) := g_package||'.delete_cwb_data';
hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_TASKS', 10);
fnd_message.set_token('PROC','delete_person_tasks');
cwb_delete_routine
(p_routine => 'BEN_CWB_PERSON_TASKS'
,p_per_in_ler_id => p_per_in_ler_id
,p_update_summary => p_update_summary
);
hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_RATES', 15);
fnd_message.set_token('PROC','delete_person_rates');
cwb_delete_routine
(p_routine => 'BEN_CWB_PERSON_RATES'
,p_per_in_ler_id => p_per_in_ler_id
,p_update_summary => p_update_summary
);
fnd_message.set_token('PROC','delete_person_groups');
cwb_delete_routine
(p_routine => 'BEN_CWB_PERSON_GROUPS'
,p_per_in_ler_id => p_per_in_ler_id
,p_update_summary => p_update_summary
);
hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_INFO', 25);
fnd_message.set_token('PROC','delete_person_info');
cwb_delete_routine
(p_routine => 'BEN_CWB_PERSON_INFO'
,p_per_in_ler_id => p_per_in_ler_id
,p_update_summary => p_update_summary
);
end delete_cwb_data;
procedure delete_summary(p_group_per_in_ler_id in number) is
--
cursor csr_summary is
select rowid, s.*
from ben_cwb_summary s
where s.group_per_in_ler_id = p_group_per_in_ler_id;
select mgr_per_in_ler_id
from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_group_per_in_ler_id
and lvl_num > 0;
ben_cwb_summary_pkg.update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => summs.group_pl_id
,p_group_oipl_id => summs.group_oipl_id
,p_elig_count_all => -summs.elig_count_all
,p_emp_recv_count_all => -summs.emp_recv_count_all
,p_elig_sal_val_all => -summs.elig_sal_val_all
,p_ws_bdgt_val_all => -summs.ws_bdgt_val_all
,p_ws_bdgt_iss_val_all => -summs.ws_bdgt_iss_val_all
,p_ws_val_all => -summs.ws_val_all
,p_stat_sal_val_all => -summs.stat_sal_val_all
,p_oth_comp_val_all => -summs.oth_comp_val_all
,p_tot_comp_val_all => -summs.tot_comp_val_all
,p_rec_val_all => -summs.rec_val_all
,p_rec_mn_val_all => -summs.rec_mn_val_all
,p_rec_mx_val_all => -summs.rec_mx_val_all
,p_misc1_val_all => -summs.misc1_val_all
,p_misc2_val_all => -summs.misc2_val_all
,p_misc3_val_all => -summs.misc3_val_all);
delete ben_cwb_summary
where rowid = summs.rowid;
end delete_summary;
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 = cv_person_id
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
and pil.group_pl_id = cv_group_pl_id
and ler.ler_id = pil.ler_id
and ler.typ_cd = 'COMP'
and nvl(cv_effective_date,trunc(sysdate))
between ler.effective_start_date
and ler.effective_end_date;
delete_cwb_data
(p_per_in_ler_id => l_pil_rec.per_in_ler_id
,p_business_group_id => l_pil_rec.business_group_id
,p_update_summary => true);
delete_summary(p_group_per_in_ler_id => l_pil_rec.per_in_ler_id);
delete from ben_cwb_person_rates
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
and group_per_in_ler_id = -1 -- Bug 5060080
and person_id = p_person_id;