[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_ptnl_per_for_ler
(p_ptnl_rec IN OUT NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
,p_effective_date IN DATE) is
--
l_proc varchar2(72) := g_package||'update_ptnl_per_for_ler';
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => p_ptnl_rec.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => p_ptnl_rec.lf_evt_ocrd_dt
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_procd_dt => p_effective_date
,p_ler_id => p_ptnl_rec.ler_id
,p_person_id => p_ptnl_rec.person_id
,p_business_group_id => p_ptnl_rec.business_group_id
,p_object_version_number => p_ptnl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
end update_ptnl_per_for_ler;
select ler.ovridg_le_flag,
pfl.ler_id,
pfl.ptnl_ler_for_per_id,
pfl.lf_evt_ocrd_dt,
pfl.object_version_number,
ler.ler_eval_rl,
pfl.creation_date,
pfl.ptnl_ler_for_per_stat_cd,
pfl.ntfn_dt,
pfl.dtctd_dt,
pfl.voidd_dt,
ler.name,
pfl.trgr_table_pk_id, -- it is absence_attendance_id
ler.lf_evt_oper_cd, -- 9999 lf_evt_oper_cd,
ler.typ_cd
from ben_ptnl_ler_for_per pfl,
ben_ler_f ler
where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
and pfl.person_id = p_person_id
and pfl.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pfl.lf_evt_ocrd_dt <= p_effective_date
--
-- 9999 Do we need to filter based on effective_date, as all potentials
-- need to be processed. Also first void all potentials which are not
-- processed and are corrections.
--
and pfl.ler_id <> ben_manage_life_events.g_ler_id
and ler.typ_cd = 'ABS'
order by pfl.lf_evt_ocrd_dt asc,
decode(ler.lf_evt_oper_cd,'DELETE',3,'START',2,1) desc;
select min(ptn.lf_evt_ocrd_dt)
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ptn.person_id = p_person_id
and ptn.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'ABS'
and ptn.business_group_id = p_business_group_id
and ptn.lf_evt_ocrd_dt <= p_effective_date
and ptn.ler_id <> ben_manage_life_events.g_ler_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
select ptn.ptnl_ler_for_per_id
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ptn.person_id = p_person_id
and ptn.business_group_id = p_business_group_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'ABS'
and ptn.ler_id = ler.ler_id
and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id
and ler.lf_evt_oper_cd = 'DELETE';
select ptn.*
from ben_ptnl_ler_for_per ptn
where ptn.person_id = p_person_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id;
select ler.lf_evt_oper_cd,ptn.*
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ptn.person_id = p_person_id
and ptn.ptnl_ler_for_per_stat_cd <> 'VOIDD' -- 9999(it should work) not in ('PROCD','VOIDD')
and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id
-- not needed 9999 delete it and ptn.ptnl_ler_for_per_id <> l_potent.ptnl_ler_for_per_id
and ptn.ler_id = l_potent.ler_id
and ler.ler_id = ptn.ler_id
and p_effective_date between ler.effective_start_date and
ler.effective_end_date
order by ptn.ptnl_ler_for_per_id desc;
select 'x'
from per_absence_attendances
where absence_attendance_id = l_winning_ptnl_rec.trgr_table_pk_id
and date_end is null;
select ptn.*
from ben_ptnl_ler_for_per ptn
where ptn.person_id = p_person_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
and ptn.ler_id = l_winning_ptnl_rec.ler_id
and ptn.trgr_table_pk_id = l_winning_ptnl_rec.trgr_table_pk_id
and ptn.ptnl_ler_for_per_id <> l_winning_ptnl_rec.ptnl_ler_for_per_id;
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil.business_group_id = p_business_group_id
and pil.trgr_table_pk_id = l_potent.trgr_table_pk_id
order by pil.lf_evt_ocrd_dt asc;
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil.business_group_id = p_business_group_id
and pil.trgr_table_pk_id = l_winning_ptnl_rec.trgr_table_pk_id
and pil.ler_id = l_winning_ptnl_rec.ler_id
order by pil.lf_evt_ocrd_dt asc;
select pil.*
from ben_per_in_ler pil,
ben_ler_f ler
where pil.business_group_id = p_business_group_id
and pil.person_id = p_person_id
and pil.lf_evt_ocrd_dt >= p_min_lf_evt_ocrd_dt
and pil.ler_id <> ben_manage_life_events.g_ler_id -- 9999 what is this?
and pil.ler_id = ler.ler_id
and (pil.ler_id = p_ler_id or p_ler_id = -1)
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
-- GRADE/STEP
-- bug 6147208
-- and ler.typ_cd not in ('COMP', 'GSP', 'IREC')-- iRec
and ler.typ_cd = 'ABS'
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
order by pil.lf_evt_ocrd_dt desc,ler.lf_evt_oper_cd asc; -- most recent is the first one to backout.
SELECT object_version_number
FROM ben_per_in_ler
WHERE per_in_ler_id = cv_per_in_ler_id;
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => l_ppl_rec.lf_evt_ocrd_dt
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_procd_dt => p_effective_date
,p_ler_id => l_ppl_rec.ler_id
,p_person_id => l_ppl_rec.person_id
,p_business_group_id => l_ppl_rec.business_group_id
,p_object_version_number => l_ppl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_ppl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
end if; -- End of delete potential existence.
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_ppl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_winning_ptnl_rec.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => l_winning_ptnl_rec.lf_evt_ocrd_dt
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_procd_dt => p_effective_date
,p_ler_id => l_winning_ptnl_rec.ler_id
,p_person_id => l_winning_ptnl_rec.person_id
,p_business_group_id => l_winning_ptnl_rec.business_group_id
,p_object_version_number => l_winning_ptnl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx)
,p_bckt_per_in_ler_id => l_curr_per_in_ler_id
-- ,p_object_version_number => l_object_version_number
,p_object_version_number => pil_ovn_rec.object_version_number
,p_effective_date => p_effective_date
,P_PROCD_DT => l_date -- outputs
,P_STRTD_DT => l_date
,P_VOIDD_DT => l_date );
ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
select ptnl.*
from ben_ptnl_ler_for_per ptnl
where ptnl.ptnl_ler_for_per_id = cv_ptnl_ler_for_per_id;
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
and pil.ler_id = ler.ler_id
and pil.ler_id = p_ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'COMP'
union
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd in ('STRTD')
and pil.lf_evt_ocrd_dt < cv_lf_evt_ocrd_dt
and pil.ler_id = ler.ler_id
and pil.ler_id = p_ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'COMP'
order by 3 asc;
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
update_ptnl_per_for_ler
(p_ptnl_rec => l_ptnl_rec
,p_effective_date => p_effective_date);
select ler.ovridg_le_flag,
pfl.ler_id,
pfl.ptnl_ler_for_per_id,
pfl.lf_evt_ocrd_dt,
pfl.object_version_number,
ler.ler_eval_rl,
pfl.creation_date,
pfl.ptnl_ler_for_per_stat_cd,
pfl.ntfn_dt,
pfl.dtctd_dt,
pfl.voidd_dt,
ler.name,
pfl.person_id,
pfl.business_group_id
from ben_ptnl_ler_for_per pfl,
ben_ler_f ler
where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
and pfl.person_id = p_person_id
and pfl.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pfl.lf_evt_ocrd_dt <= p_effective_date
and pfl.ler_id <> ben_manage_life_events.g_ler_id
and ler.typ_cd = 'GSP'
order by pfl.lf_evt_ocrd_dt asc;
select null
from ben_per_in_ler pil, ben_ler_f ler
where pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between ler.effective_start_date and ler.effective_end_date
and ler.typ_cd = 'GSP';
select pil.*
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and (pil.per_in_ler_stat_cd = 'STRTD'
or
(pil.per_in_ler_stat_cd = 'PROCD'
and pil.lf_evt_ocrd_dt >= p_lf_evt_ocrd_dt)
)
and pil.ler_id <> ben_manage_life_events.g_ler_id
and pil.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'GSP'
order by per_in_ler_stat_cd desc, lf_evt_ocrd_dt desc;
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_min_ptnl.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => l_min_ptnl.lf_evt_ocrd_dt
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_procd_dt => p_effective_date
,p_ler_id => l_min_ptnl.ler_id
,p_person_id => l_min_ptnl.person_id
,p_business_group_id => l_min_ptnl.business_group_id
,p_object_version_number => l_min_ptnl.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
select ler.ovridg_le_flag,
pfl.ler_id,
pfl.ptnl_ler_for_per_id,
pfl.lf_evt_ocrd_dt,
pfl.object_version_number,
ler.ler_eval_rl,
pfl.creation_date,
pfl.ptnl_ler_for_per_stat_cd,
pfl.ntfn_dt,
pfl.dtctd_dt,
pfl.voidd_dt,
ler.name
from ben_ptnl_ler_for_per pfl,
ben_ler_f ler
where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
and pfl.person_id = p_person_id
and pfl.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pfl.lf_evt_ocrd_dt <= decode(p_mode,
'C',
pfl.lf_evt_ocrd_dt,
p_effective_date)
and pfl.ler_id <> ben_manage_life_events.g_ler_id
--
-- CWB Changes
--
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') -- iRec
order by pfl.lf_evt_ocrd_dt asc;
select pil.*
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
and pil.lf_evt_ocrd_dt >= p_lf_evt_ocrd_dt
and pil.per_in_ler_id <> p_curr_per_in_ler_id
and pil.ler_id <> ben_manage_life_events.g_ler_id
--
-- CWB Changes
--
and pil.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
--
-- CWB Changes End
--
order by lf_evt_ocrd_dt desc;
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.ler_id <> ben_manage_life_events.g_ler_id
and pil.ler_id = ler.ler_id
and cv_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
union
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd = 'PROCD'
and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
and pil.ler_id <> ben_manage_life_events.g_ler_id
and pil.ler_id = ler.ler_id
-- GRADE/STEP
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC')-- iRec
and cv_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
order by 3 asc;
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd in ( 'PROCD', 'STRTD')
and pil.ler_id <> ben_manage_life_events.g_ler_id
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.ler_id = ler.ler_id
and cv_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC');--iRec
SELECT object_version_number
FROM ben_per_in_ler
WHERE per_in_ler_id = cv_per_in_ler_id;
select typ_cd, name
from ben_ler_f ler
where ler_id = cv_ler_id;
procedure update_ptnl_per_for_ler
(p_ptnl_rec IN OUT NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
,p_effective_date IN DATE) is
--
l_proc varchar2(72) := g_package||'update_ptnl_per_for_ler';
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => p_ptnl_rec.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => p_ptnl_rec.lf_evt_ocrd_dt
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_procd_dt => p_effective_date
,p_ler_id => p_ptnl_rec.ler_id
,p_person_id => p_ptnl_rec.person_id
,p_business_group_id => p_ptnl_rec.business_group_id
,p_object_version_number => p_ptnl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
end update_ptnl_per_for_ler;
procedure insert_per_in_ler
(p_ptnl_rec IN out NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
,p_curr_per_in_ler_id out NOCOPY number
,p_effective_date IN DATE) is
--
l_per_in_ler_id NUMBER;
l_proc varchar2(72) := g_package||'insert_per_in_ler';
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
end insert_per_in_ler;
select ptn.creation_date,
ptn.ntfn_dt,
ler.tmlns_eval_cd,
ler.tmlns_perd_cd,
ler.tmlns_dys_num,
ler.tmlns_perd_rl,
ler.name,
ptn.lf_evt_ocrd_dt,
ptn.ptnl_ler_for_per_id,
ptn.ler_id,
ptn.object_version_number
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ler.ler_id = ptn.ler_id
and ler.business_group_id = p_business_group_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD', 'MNLO')
and ptn.mnlo_dt is null -- to fix the bug 1579642
and ptn.lf_evt_ocrd_dt --p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ptn.business_group_id = ler.business_group_id
and ptn.person_id = p_person_id
and ptn.ler_id <> ben_manage_life_events.g_ler_id
-- CWB Changes
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP','IREC') --iRec
and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
'SCHEDDO',
ptn.lf_evt_ocrd_dt,
'SCHEDDA',
ptn.lf_evt_ocrd_dt,
p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'MNL'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_mnl_dt => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'MNL'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_mnl_dt => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'MNL'
,p_object_version_number => l_potent.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_mnl_dt => p_effective_date);
p_program_update_date => sysdate);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
p_ler_id => l_ler_id,
p_ntfn_dt => l_ntfn_dt,
p_dtctd_dt => l_dtctd_dt,
p_voidd_dt => l_voidd_dt,
p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_mnl_dt => l_mnl_dt,
p_procd_dt => l_procd_dt,
p_unprocd_dt => l_unprocd_dt);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
p_ler_id => p_ler_id,
p_ntfn_dt => l_ntfn_dt,
p_dtctd_dt => l_dtctd_dt,
p_voidd_dt => l_voidd_dt,
p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_mnl_dt => l_mnl_dt,
p_procd_dt => l_procd_dt,
p_unprocd_dt => l_unprocd_dt);
select ler.ovridg_le_flag,
ppl.ptnl_ler_for_per_id,
ler.name
from ben_ptnl_ler_for_per ppl,
ben_ler_f ler
where ppl.person_id = p_person_id
and ppl.ler_id = ler.ler_id
and ppl.business_group_id = p_business_group_id
and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and ler.business_group_id = ppl.business_group_id
and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD')
and ppl.ler_id <> ben_manage_life_events.g_ler_id
--
-- CWB Changes
--
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select ppl.object_version_number,
ppl.ptnl_ler_for_per_id
from ben_ptnl_ler_for_per ppl,
ben_ler_f ler
where ppl.person_id = p_person_id
and ppl.business_group_id = p_business_group_id
and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and ppl.ler_id <> ben_manage_life_events.g_ler_id
and ppl.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD');
select *
from ben_ptnl_ler_for_per ptn
where ptn.ptnl_ler_for_per_id = l_search_ptnl_ler_for_per_id;
select pil.*
from ben_per_in_ler pil,
ben_ler_f ler
where pil.business_group_id = p_business_group_id
and pil.person_id = p_person_id
and (pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt -- 5727737/5677090: Need to backout all Future LEs
or (pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.ler_id <> ben_manage_life_events.g_ler_id))
and pil.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC','SCHEDDU') --iRec
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
ORDER BY pil.lf_evt_ocrd_dt DESC;
select ptnl.object_version_number
from ben_ptnl_ler_for_per ptnl
where ptnl.ptnl_ler_for_per_id = v_ptnl_ler_for_per_id
and ptnl.business_group_id = p_business_group_id;
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_object_version_number => l_ptnl.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
select min(ptn.lf_evt_ocrd_dt)
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ptn.person_id = p_person_id
and ptn.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
and ptn.business_group_id = p_business_group_id
and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
'SCHEDDO',
ptn.lf_evt_ocrd_dt,
'SCHEDDA',
ptn.lf_evt_ocrd_dt,
p_effective_date)
and ptn.ler_id <> ben_manage_life_events.g_ler_id
and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id,
p_object_version_number => l_potent.object_version_number,
p_ptnl_ler_for_per_stat_cd => 'VOIDD',
p_effective_date => p_effective_date,
p_voidd_dt => p_effective_date);
insert_per_in_ler
(p_ptnl_rec => l_ptnl_rec
,p_curr_per_in_ler_id => l_curr_per_in_ler_id
,p_effective_date => p_effective_date);
update_ptnl_per_for_ler
(p_ptnl_rec => l_ptnl_rec
,p_effective_date => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'MNL'
,p_object_version_number => l_ptnl_rec.object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_mnl_dt => p_effective_date);
insert_per_in_ler
(p_ptnl_rec => l_ptnl_rec
,p_curr_per_in_ler_id => l_curr_per_in_ler_id
,p_effective_date => p_effective_date);
update_ptnl_per_for_ler
(p_ptnl_rec => l_ptnl_rec
,p_effective_date => p_effective_date);
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx)
,p_bckt_per_in_ler_id => l_curr_per_in_ler_id
,p_object_version_number => pil_ovn_rec.object_version_number
,p_effective_date => p_effective_date
,P_PROCD_DT => l_date -- outputs
,P_STRTD_DT => l_date
,P_VOIDD_DT => l_date );
ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
select ptnl.*
from ben_ptnl_ler_for_per ptnl
where ptnl.ptnl_ler_for_per_id = cv_ptnl_ler_for_per_id;
select pil.per_in_ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.person_id,
pil.business_group_id,
pil.object_version_number,
pil.procd_dt,
pil.strtd_dt,
pil.voidd_dt,
pil.bckt_dt,
pil.clsd_dt,
pil.ntfn_dt,
pil.ptnl_ler_for_per_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = cv_person_id
and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
and pil.assignment_id = cv_assignment_id
and pil.ler_id = ler.ler_id
and pil.ler_id = cv_ler_id
and cv_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'IREC';
select pel.pil_elctbl_chc_popl_id,
pel.object_version_number
from ben_pil_elctbl_chc_popl pel,
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 pel.per_in_ler_id = pil.per_in_ler_id
and pel.business_group_id = pil.business_group_id;
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => l_pil_rec.per_in_ler_id
,p_bckt_per_in_ler_id => null
,p_per_in_ler_stat_cd => 'BCKDT'
,p_prvs_stat_cd => l_pil_rec.per_in_ler_stat_cd
,p_object_version_number => l_pil_rec.object_version_number
,p_effective_date => p_effective_date
,P_PROCD_DT => l_procd_dt -- outputs
,P_STRTD_DT => l_strtd_dt
,P_VOIDD_DT => l_voidd_dt );
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
(p_validate => false,
p_pil_elctbl_chc_popl_id => l_pel_pk_id,
p_pil_elctbl_popl_stat_cd => 'BCKDT',
p_object_version_number => l_pel_object_version_number,
p_effective_date => p_effective_date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_validate => false
,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_procd_dt => p_lf_evt_ocrd_dt
,p_person_id => l_ptnl_rec.person_id
,p_business_group_id => l_ptnl_rec.business_group_id
,p_object_version_number => l_ptnl_rec.object_version_number
,p_effective_date => p_lf_evt_ocrd_dt
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate);
p_program_update_date => sysdate);
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);