[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select enrt_cvg_strt_dt,pl_typ_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_epe_id ;
select val,
bnft_typ_cd,
nnmntry_uom
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id ;
select ecr.*
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = p_epe_id
union
select ecr.*
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id is null
and ecr.enrt_bnft_id = p_enrt_bnft_id ;
select abr.*
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = c_acty_base_rt_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date ;
select epe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = c_per_in_ler_id
and epe.bnft_prvdr_pool_id is not null
and epe.elig_per_elctbl_chc_id
not in ( select ecr.elig_per_elctbl_chc_id from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecr.rt_usg_cd = 'FLXCR'
) ;
select 'x'
from ben_pgm_f pgm
where
pgm.pgm_id = c_pgm_id
and pgm.pgm_typ_cd in ('COBRAFLX','FLEX', 'FPC' )
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date ;
select pln.pl_id,plip.plip_id,ptip.ptip_id
from ben_pl_f pln,
ben_plip_f plip,
ben_ptip_f ptip
where
plip.pgm_id = p_pgm_id
and plip.pl_id = pln.pl_id
and pln.invk_flx_cr_pl_flag = 'Y'
and pln.pl_typ_id = ptip.pl_typ_id
and ptip.pgm_id = p_pgm_id
and p_effective_date between
plip.effective_start_date and plip.effective_end_date
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date
and not exists ( select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.pgm_id = p_pgm_id
and epe.pl_id = pln.pl_id
and epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANFC' );
select pln.pl_id,plip.plip_id,ptip.ptip_id
from ben_pl_f pln,
ben_plip_f plip,
ben_ptip_f ptip
where
plip.pgm_id = p_pgm_id
and plip.pl_id = pln.pl_id
and pln.imptd_incm_calc_cd is not null
and pln.pl_typ_id = ptip.pl_typ_id
and ptip.pgm_id = p_pgm_id
and p_effective_date between
plip.effective_start_date and plip.effective_end_date
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date
and not exists ( select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.pgm_id = p_pgm_id
and epe.pl_id = pln.pl_id
and epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANIMP' );
select object_version_number,per_in_ler_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
select object_version_number
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id ;
hr_utility.set_location('Calling ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC ',20);
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_object_version_number => l_epe_object_version_number
,p_effective_date => p_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
);
procedure update_elig_dpnt
( p_elig_dpnt_id in number
,p_elig_cvrd_dpnt_id in number
,p_effective_date in date
,p_business_group_id in number
,p_object_version_number in out nocopy number
) is
l_package varchar2(500) := 'Ben_Manage_Override.update_elig_dpnt' ;
ben_elig_dpnt_api.update_elig_dpnt(
p_elig_dpnt_id => p_elig_dpnt_id,
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_object_version_number => p_object_version_number,
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 prtt_rt_val_id,
object_version_number,
acty_base_rt_id,
per_in_ler_id,
business_group_id,
element_entry_value_id
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.rt_end_dt = to_date('31/12/4712','DD/MM/RRRR') ;
select input_value_id,
element_type_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = cv_acty_base_rt_id
and cv_effective_date between abr.effective_start_date
and abr.effective_end_date ;
select pdp.elig_cvrd_dpnt_id,
pdp.object_version_number,
pdp.business_group_id,
pdp.per_in_ler_id
from ben_elig_cvrd_dpnt_f pdp
where pdp.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and cv_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and pdp.cvg_thru_dt = to_date('31/12/4712','DD/MM/RRRR') ;
select ppe.prtt_prem_id,
ppe.object_version_number,
ppe.effective_start_date,
ppe.effective_end_date,
ppe.actl_prem_id
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_ppe_dt_to_use between ppe.effective_start_date and ppe.effective_end_date
UNION
select ppe1.prtt_prem_id,
ppe1.object_version_number,
ppe1.effective_start_date,
ppe1.effective_end_date,
ppe1.actl_prem_id
from ben_prtt_prem_f ppe1,
ben_per_in_ler pil
where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe1.per_in_ler_id
and pil.business_group_id=ppe1.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and ppe1.effective_start_date > p_ppe_dt_to_use
and not exists
(select 1
from ben_prtt_prem_f ppe2,
ben_per_in_ler pil
where ppe2.prtt_prem_id = ppe1.prtt_prem_id
and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe2.per_in_ler_id
and pil.business_group_id=ppe2.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_ppe_dt_to_use between
ppe2.effective_start_date and ppe2.effective_end_date);
l_delete boolean;
l_delete_next_change boolean;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_pdp.elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_pdp.business_group_id,
p_per_in_ler_id => l_pdp.per_in_ler_id,
p_cvg_thru_dt => p_enrt_cvg_thru_dt,
p_ovrdn_flag => 'Y',
p_ovrdn_thru_dt => p_enrt_cvg_thru_dt,
p_object_version_number => l_pdp.object_version_number,
p_datetrack_mode => 'CORRECTION',
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_effective_date => p_effective_date,
p_multi_row_actn => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false
,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
,p_rt_end_dt => p_enrt_cvg_thru_dt
,p_rt_ovridn_flag => 'Y'
,p_rt_ovridn_thru_dt => p_enrt_cvg_thru_dt
,p_person_id => p_person_id
,p_acty_base_rt_id => l_prv.acty_base_rt_id
,p_input_value_id => l_abr.input_value_id
,p_element_type_id => l_abr.element_type_id
,p_ended_per_in_ler_id => l_prv.per_in_ler_id
,p_business_group_id => l_prv.business_group_id
,p_object_version_number => l_prv.object_version_number
,p_effective_date => p_effective_date
);
update ben_prtt_rt_val
set rt_end_dt = p_enrt_cvg_thru_dt,
rt_ovridn_flag = 'Y',
rt_ovridn_thru_dt = p_enrt_cvg_thru_dt
where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
update ben_prtt_rt_val
set rt_end_dt = p_enrt_cvg_thru_dt,
rt_ovridn_flag = 'Y',
rt_ovridn_thru_dt = p_enrt_cvg_thru_dt
where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
p_delete => l_delete,
p_future_change => l_future_change,
p_delete_next_change => l_delete_next_change );
l_ppe_dt_mode := hr_api.g_delete ;
ben_prtt_prem_api.delete_prtt_prem
( p_validate => false,
p_prtt_prem_id => l_ppe.prtt_prem_id,
p_object_version_number => l_ppe.object_version_number,
p_effective_date => p_enrt_cvg_thru_dt,
p_effective_start_date => l_ppe.effective_end_date,
p_effective_end_date => l_ppe.effective_start_date,
p_datetrack_mode => l_ppe_dt_mode
);
select prtt_rt_val_id,
object_version_number,
acty_base_rt_id,
per_in_ler_id,
business_group_id,
element_entry_value_id
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.rt_end_dt <> to_date('31/12/4712','DD/MM/RRRR') and
not exists ( select 'x' from
ben_prtt_rt_val prv1
where prv.rowid <> prv1.rowid
and prv1.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv1.acty_base_rt_id = prv.acty_base_rt_id
and prv1.prtt_rt_val_stat_cd is null
and prv1.rt_strt_dt > prv.rt_strt_dt ) ;
select input_value_id,
element_type_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = cv_acty_base_rt_id
and cv_effective_date between abr.effective_start_date
and abr.effective_end_date ;
select pdp.elig_cvrd_dpnt_id,
pdp.object_version_number,
pdp.business_group_id,
pdp.per_in_ler_id
from ben_elig_cvrd_dpnt_f pdp
where pdp.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and cv_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and pdp.cvg_thru_dt <> to_date('31/12/4712','DD/MM/RRRR')
and not exists (
select 'x' from ben_elig_cvrd_dpnt_f pdp1
where pdp.elig_cvrd_dpnt_id = pdp1.elig_cvrd_dpnt_id
and pdp.rowid <> pdp1.rowid
and pdp1.effective_start_date > pdp.effective_start_date
and pdp1.cvg_strt_dt > pdp.cvg_thru_dt ) ;
select ppe.prtt_prem_id,
ppe.object_version_number,
ppe.effective_start_date,
ppe.effective_end_date,
ppe.actl_prem_id,
ppe.business_group_id
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_ppe_dt_to_use between ppe.effective_start_date and ppe.effective_end_date
and ppe.effective_end_date <> hr_api.g_eot
UNION
select ppe1.prtt_prem_id,
ppe1.object_version_number,
ppe1.effective_start_date,
ppe1.effective_end_date,
ppe1.actl_prem_id,
ppe1.business_group_id
from ben_prtt_prem_f ppe1,
ben_per_in_ler pil
where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe1.per_in_ler_id
and pil.business_group_id=ppe1.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and ppe1.effective_start_date > p_ppe_dt_to_use
and ppe1.effective_end_date <> hr_api.g_eot
and not exists
(select 1
from ben_prtt_prem_f ppe2,
ben_per_in_ler pil
where ppe2.prtt_prem_id = ppe1.prtt_prem_id
and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe2.per_in_ler_id
and pil.business_group_id=ppe2.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_ppe_dt_to_use between
ppe2.effective_start_date and ppe2.effective_end_date
and ppe2.effective_end_date <> hr_api.g_eot );
l_delete boolean;
l_delete_next_change boolean;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_pdp.elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_pdp.business_group_id,
p_per_in_ler_id => l_pdp.per_in_ler_id,
p_cvg_thru_dt => to_date('31/12/4712','DD/MM/RRRR'),
p_ovrdn_flag => 'Y',
p_ovrdn_thru_dt => null,
p_object_version_number => l_pdp.object_version_number,
p_datetrack_mode => 'CORRECTION',
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_effective_date => p_effective_date,
p_multi_row_actn => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false
,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
,p_rt_end_dt => to_date('31/12/4712','DD/MM/RRRR')
,p_rt_ovridn_flag => 'Y'
,p_rt_ovridn_thru_dt => null
,p_person_id => p_person_id
,p_acty_base_rt_id => l_prv.acty_base_rt_id
,p_input_value_id => l_abr.input_value_id
,p_element_type_id => l_abr.element_type_id
,p_ended_per_in_ler_id => l_prv.per_in_ler_id
,p_business_group_id => l_prv.business_group_id
,p_object_version_number => l_prv.object_version_number
,p_effective_date => p_effective_date
);
update ben_prtt_rt_val
set rt_end_dt = to_date('31/12/4712','DD/MM/RRRR'),
rt_ovridn_flag = 'Y',
rt_ovridn_thru_dt = null
where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
update ben_prtt_rt_val
set rt_end_dt = to_date('31/12/4712','DD/MM/RRRR'),
rt_ovridn_flag = 'Y',
rt_ovridn_thru_dt = null
where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
p_delete => l_delete,
p_future_change => l_future_change,
p_delete_next_change => l_delete_next_change );
ben_prtt_prem_api.delete_prtt_prem
( p_validate => false,
p_prtt_prem_id => l_ppe.prtt_prem_id,
p_object_version_number => l_ppe.object_version_number,
p_effective_date => l_ppe.effective_end_date,
p_effective_start_date => l_effective_end_date,
p_effective_end_date => l_effective_start_date,
p_datetrack_mode => hr_api.g_future_change
);
select ecr.val,
ecr.uom,
ecr.actl_prem_id,
pil.lf_evt_ocrd_dt,
pil.ler_id,
epe.elig_per_elctbl_chc_id
from ben_enrt_prem ecr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_id = p_per_in_ler_id ;
select ppe.prtt_prem_id,
ppe.std_prem_uom,
ppe.std_prem_val,
ppe.actl_prem_id,
ppe.object_version_number,
ppe.effective_start_date
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = p_actl_prem_id
and p_ppe_dt_to_use between
ppe.effective_start_date and ppe.effective_end_date
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select pel.enrt_perd_id,
pel.lee_rsn_id
from ben_pil_elctbl_chc_popl pel,
ben_elig_per_elctbl_chc epe
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
lb_update boolean;
lb_update_override boolean;
lb_update_change_insert boolean;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_ppe_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_ppe_datetrack_mode := hr_api.g_update;
ben_prtt_prem_api.update_prtt_prem
( p_validate => FALSE
,p_prtt_prem_id => l_ppe.prtt_prem_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_std_prem_uom => l_prem.uom
,p_std_prem_val => l_prem.val
,p_actl_prem_id => l_prem.actl_prem_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_ppe.object_version_number
,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_effective_date => l_ppe_dt_to_use
,p_datetrack_mode => l_ppe_datetrack_mode
);
,p_program_update_date => sysdate
,p_effective_date => l_ppe_dt_to_use
);
select *
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd IS NULL
order by pen.effective_start_date;
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date,
p_business_group_id => l_pen.business_group_id,
p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
-- p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt, This needs to be updated for the latest only
p_bnft_amt => p_bnft_amt,
p_enrt_ovridn_flag => p_enrt_ovridn_flag,
p_enrt_ovrid_thru_dt => p_enrt_ovrid_thru_dt,
p_enrt_ovrid_rsn_cd => p_enrt_ovrid_rsn_cd,
p_orgnl_enrt_dt => p_orgnl_enrt_dt,
p_object_version_number => l_object_version_number,
p_effective_date => l_pen.effective_start_date,
p_datetrack_mode => l_datetrack_mode,
p_multi_row_validate => FALSE,
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);
l_update boolean := FALSE;
l_update_override boolean := FALSE;
l_update_change_insert boolean := FALSE;
select bpl.bnft_prvdd_ldgr_id,
bpl.per_in_ler_id,
bpl.used_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=l_ecr_rec.decr_bnft_prvdr_pool_id
and bpl.business_group_id=l_epe_rec.business_group_id
and bpl.acty_base_rt_id=l_ecr_rec.acty_base_rt_id
and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and bpl.used_val is not null
and p_effective_date between bpl.effective_start_date
and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
select rt_val
from ben_prtt_rt_val
where prtt_rt_val_id=l_ecr_rec.prtt_rt_val_id;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override or l_update_change_insert then
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
elsif l_update then
l_datetrack_mode := 'UPDATE';
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => l_val
,p_bnft_prvdr_pool_id => l_ecr_rec.decr_bnft_prvdr_pool_id
,p_acty_base_rt_id => l_ecr_rec.acty_base_rt_id
,p_per_in_ler_id => l_epe_rec.per_in_ler_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_business_group_id => l_epe_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => null
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),55);
l_delete_warning boolean;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id;
select distinct
ele.element_entry_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number,
ele.creator_type,
ele.creator_id
from pay_element_entries_f ele,
pay_element_links_f elk,
pay_element_types_f elt,
per_all_assignments_f asg
where ele.effective_start_date = (select min(ele2.effective_start_date)
from pay_element_entries_f ele2
where ele2.element_entry_id
= ele.element_entry_id)
and ele.effective_start_date > p_effective_date
and asg.person_id = p_person_id
and ele.assignment_id = asg.assignment_id
and ele.effective_start_date between asg.effective_start_date
and asg.effective_end_date
and nvl(ele.creator_id,-1) <> p_prtt_enrt_rslt_id
and ele.entry_type = 'E'
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = p_element_type_id
and elt.element_type_id = elk.element_type_id
and elk.effective_start_date between elt.effective_start_date
and elt.effective_end_date
order by ele.effective_start_date desc;
select distinct
prv.prtt_rt_val_id,
prv.prtt_enrt_rslt_id,
prv.rt_val,
prv.cmcd_rt_val,
prv.ann_rt_val,
prv.rt_strt_dt,
abr.acty_base_rt_id,
abr.element_type_id,
abr.input_value_id,
prv.business_group_id,
prv.acty_ref_perd_cd,
prv.object_version_number
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr,
ben_prtt_enrt_rslt_f pen
where prv.prtt_rt_val_id <> p_prtt_rt_val_id
and prv.prtt_rt_val_stat_cd IS NULL
and prv.rt_strt_dt > p_rt_strt_dt
and abr.element_type_id = p_element_type_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and prv.rt_strt_dt between abr.effective_start_date
and abr.effective_end_date
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd IS NULL
order by prv.rt_strt_dt ;
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>hr_api.g_zap
,p_effective_date =>l_zap_future_ee.effective_end_date
,p_element_entry_id =>l_zap_future_ee.element_entry_id
,p_object_version_number =>l_zap_future_ee.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);
,p_dt_delete_mode => null
,p_acty_base_rt_id => p_acty_base_rt_id
,p_amt => p_rt_val
);
,p_dt_delete_mode => null
,p_acty_base_rt_id => p_acty_base_rt_id
,p_amt => p_rt_val
);
select object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.sspndd_flag = 'Y'
and pen.prtt_enrt_rslt_stat_cd IS NULL
and p_effective_date between pen.effective_start_date
and pen.effective_end_date;
select
pea.prtt_enrt_actn_id
,pea.actn_typ_id
,pea.rqd_flag
,pea.business_group_id
,pea.object_version_number pea_object_version_number
,pen.object_version_number pen_object_version_number
,pea.effective_start_date pea_effective_date
from ben_prtt_enrt_actn_f pea,
ben_prtt_enrt_rslt_f pen
where
pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date and
pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
and pea.rqd_flag = 'Y' ;
ben_prtt_enrt_actn_api.update_prtt_enrt_actn
(p_prtt_enrt_actn_id => l_pea.prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => l_pea.pen_object_version_number
,p_actn_typ_id => l_pea.actn_typ_id
,p_rqd_flag => 'N'
,p_effective_date => l_pea.pea_effective_date
,p_post_rslt_flag => 'N' -- 99999 p_post_rslt_flag
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_pea_effective_start_date
,p_effective_end_date => l_pea_effective_end_date
,p_object_version_number => l_pea.pea_object_version_number
,p_datetrack_mode => hr_api.g_correction
);
hr_utility.set_location('After ben_prtt_enrt_actn_api.update_prtt_enrt_actn ',20);