[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
,p_override_validation in boolean default false
,p_defer in boolean
)
is
--
l_proc varchar2(72) := g_package||'create_Eligible_Person';
l_last_update_date ben_elig_per_f.last_update_date%TYPE;
l_last_updated_by ben_elig_per_f.last_updated_by%TYPE;
l_last_update_login ben_elig_per_f.last_update_login%TYPE;
g_pepinsplip.p_program_update_date := p_program_update_date;
,p_program_update_date => g_pepinsplip.p_program_update_date
,p_object_version_number => l_dummy_ovn
,p_effective_date => g_pepinsplip.p_effective_date
,p_override_validation => g_pepinsplip.p_override_validation
,p_defer => false
);
hr_utility.set_location('Insert: '||l_proc, 5);
insert into ben_elig_per_f
(
elig_per_id,
effective_start_date,
effective_end_date,
business_group_id,
pl_id,
pgm_id,
plip_id,
ptip_id,
ler_id,
person_id,
per_in_ler_id,
dpnt_othr_pl_cvrd_rl_flag,
prtn_ovridn_thru_dt,
pl_key_ee_flag,
pl_hghly_compd_flag,
elig_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
prtn_end_dt,
prtn_strt_dt,
wait_perd_cmpltn_dt,
wait_perd_strt_dt ,
wv_ctfn_typ_cd,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
prtn_ovridn_flag,
no_mx_prtn_ovrid_thru_flag,
prtn_ovridn_rsn_cd,
age_uom,
los_uom,
ovrid_svc_dt,
inelg_rsn_cd,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
dstr_rstcn_flag,
pct_fl_tm_val,
wv_prtn_rsn_cd,
pl_wvd_flag,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
once_r_cntug_cd,
pep_attribute_category,
pep_attribute1,
pep_attribute2,
pep_attribute3,
pep_attribute4,
pep_attribute5,
pep_attribute6,
pep_attribute7,
pep_attribute8,
pep_attribute9,
pep_attribute10,
pep_attribute11,
pep_attribute12,
pep_attribute13,
pep_attribute14,
pep_attribute15,
pep_attribute16,
pep_attribute17,
pep_attribute18,
pep_attribute19,
pep_attribute20,
pep_attribute21,
pep_attribute22,
pep_attribute23,
pep_attribute24,
pep_attribute25,
pep_attribute26,
pep_attribute27,
pep_attribute28,
pep_attribute29,
pep_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
Values
(
ben_elig_per_f_s.nextval,
l_effective_start_date,
l_effective_end_date,
p_business_group_id,
p_pl_id,
p_pgm_id,
p_plip_id,
p_ptip_id,
p_ler_id,
p_person_id,
p_per_in_ler_id,
p_dpnt_othr_pl_cvrd_rl_flag,
p_prtn_ovridn_thru_dt,
p_pl_key_ee_flag,
p_pl_hghly_compd_flag,
p_elig_flag,
p_comp_ref_amt,
p_cmbn_age_n_los_val,
p_comp_ref_uom,
p_age_val,
p_los_val,
p_prtn_end_dt,
p_prtn_strt_dt,
p_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt ,
p_wv_ctfn_typ_cd,
p_hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd,
p_prtn_ovridn_flag,
p_no_mx_prtn_ovrid_thru_flag,
p_prtn_ovridn_rsn_cd,
p_age_uom,
p_los_uom,
p_ovrid_svc_dt,
p_inelg_rsn_cd,
p_frz_los_flag,
p_frz_age_flag,
p_frz_cmp_lvl_flag,
p_frz_pct_fl_tm_flag,
p_frz_hrs_wkd_flag,
p_frz_comb_age_and_los_flag,
p_dstr_rstcn_flag,
p_pct_fl_tm_val,
p_wv_prtn_rsn_cd,
p_pl_wvd_flag,
p_rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom,
p_rt_age_val,
p_rt_los_val,
p_rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom,
p_rt_los_uom,
p_rt_pct_fl_tm_val,
p_rt_frz_los_flag,
p_rt_frz_age_flag,
p_rt_frz_cmp_lvl_flag,
p_rt_frz_pct_fl_tm_flag,
p_rt_frz_hrs_wkd_flag,
p_rt_frz_comb_age_and_los_flag,
p_once_r_cntug_cd,
p_pep_attribute_category,
p_pep_attribute1,
p_pep_attribute2,
p_pep_attribute3,
p_pep_attribute4,
p_pep_attribute5,
p_pep_attribute6,
p_pep_attribute7,
p_pep_attribute8,
p_pep_attribute9,
p_pep_attribute10,
p_pep_attribute11,
p_pep_attribute12,
p_pep_attribute13,
p_pep_attribute14,
p_pep_attribute15,
p_pep_attribute16,
p_pep_attribute17,
p_pep_attribute18,
p_pep_attribute19,
p_pep_attribute20,
p_pep_attribute21,
p_pep_attribute22,
p_pep_attribute23,
p_pep_attribute24,
p_pep_attribute25,
p_pep_attribute26,
p_pep_attribute27,
p_pep_attribute28,
p_pep_attribute29,
p_pep_attribute30,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
l_object_version_number,
l_created_by,
l_creation_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login
) RETURNING elig_per_id into l_elig_per_id;
hr_utility.set_location('Dn Insert: '||l_proc, 5);
If (p_rec.program_update_date = hr_api.g_date) then
p_rec.program_update_date :=
ben_pep_shd.g_old_rec.program_update_date;
select
elig_per_id,
effective_start_date,
effective_end_date,
business_group_id,
pl_id,
pgm_id,
plip_id,
ptip_id,
ler_id,
person_id,
per_in_ler_id,
dpnt_othr_pl_cvrd_rl_flag,
prtn_ovridn_thru_dt,
pl_key_ee_flag,
pl_hghly_compd_flag,
elig_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
prtn_end_dt,
prtn_strt_dt,
wait_perd_cmpltn_dt,
wait_perd_strt_dt,
wv_ctfn_typ_cd,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
prtn_ovridn_flag,
no_mx_prtn_ovrid_thru_flag,
prtn_ovridn_rsn_cd,
age_uom,
los_uom,
ovrid_svc_dt,
inelg_rsn_cd,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
dstr_rstcn_flag,
pct_fl_tm_val,
wv_prtn_rsn_cd,
pl_wvd_flag,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
once_r_cntug_cd,
pl_ordr_num,
plip_ordr_num,
ptip_ordr_num,
pep_attribute_category,
pep_attribute1,
pep_attribute2,
pep_attribute3,
pep_attribute4,
pep_attribute5,
pep_attribute6,
pep_attribute7,
pep_attribute8,
pep_attribute9,
pep_attribute10,
pep_attribute11,
pep_attribute12,
pep_attribute13,
pep_attribute14,
pep_attribute15,
pep_attribute16,
pep_attribute17,
pep_attribute18,
pep_attribute19,
pep_attribute20,
pep_attribute21,
pep_attribute22,
pep_attribute23,
pep_attribute24,
pep_attribute25,
pep_attribute26,
pep_attribute27,
pep_attribute28,
pep_attribute29,
pep_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number
from ben_elig_per_f
where elig_per_id = p_elig_per_id
and p_effective_date
between effective_start_date and effective_end_date;
If (p_datetrack_mode <> 'INSERT') then
--
-- We must select and lock the current row.
--
Open C_Sel1;
procedure update_perf_Eligible_Person
(p_validate in boolean default false
,p_elig_per_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_business_group_id in number default hr_api.g_number
,p_pl_id in number default hr_api.g_number
,p_pgm_id in number default hr_api.g_number
,p_plip_id in number default hr_api.g_number
,p_ptip_id in number default hr_api.g_number
,p_ler_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_dpnt_othr_pl_cvrd_rl_flag in varchar2 default hr_api.g_varchar2
,p_prtn_ovridn_thru_dt in date default hr_api.g_date
,p_pl_key_ee_flag in varchar2 default hr_api.g_varchar2
,p_pl_hghly_compd_flag in varchar2 default hr_api.g_varchar2
,p_elig_flag in varchar2 default hr_api.g_varchar2
,p_comp_ref_amt in number default hr_api.g_number
,p_cmbn_age_n_los_val in number default hr_api.g_number
,p_comp_ref_uom in varchar2 default hr_api.g_varchar2
,p_age_val in number default hr_api.g_number
,p_los_val in number default hr_api.g_number
,p_prtn_end_dt in date default hr_api.g_date
,p_prtn_strt_dt in date default hr_api.g_date
,p_wait_perd_cmpltn_dt in date default hr_api.g_date
,p_wait_perd_strt_dt in date default hr_api.g_date
,p_wv_ctfn_typ_cd in varchar2 default hr_api.g_varchar2
,p_hrs_wkd_val in number default hr_api.g_number
,p_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
,p_prtn_ovridn_flag in varchar2 default hr_api.g_varchar2
,p_no_mx_prtn_ovrid_thru_flag in varchar2 default hr_api.g_varchar2
,p_prtn_ovridn_rsn_cd in varchar2 default hr_api.g_varchar2
,p_age_uom in varchar2 default hr_api.g_varchar2
,p_los_uom in varchar2 default hr_api.g_varchar2
,p_ovrid_svc_dt in date default hr_api.g_date
,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
,p_frz_los_flag in varchar2 default hr_api.g_varchar2
,p_frz_age_flag in varchar2 default hr_api.g_varchar2
,p_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
,p_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
,p_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
,p_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
,p_dstr_rstcn_flag in varchar2 default hr_api.g_varchar2
,p_pct_fl_tm_val in number default hr_api.g_number
,p_wv_prtn_rsn_cd in varchar2 default hr_api.g_varchar2
,p_pl_wvd_flag in varchar2 default hr_api.g_varchar2
,p_rt_comp_ref_amt in number default hr_api.g_number
,p_rt_cmbn_age_n_los_val in number default hr_api.g_number
,p_rt_comp_ref_uom in varchar2 default hr_api.g_varchar2
,p_rt_age_val in number default hr_api.g_number
,p_rt_los_val in number default hr_api.g_number
,p_rt_hrs_wkd_val in number default hr_api.g_number
,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
,p_rt_age_uom in varchar2 default hr_api.g_varchar2
,p_rt_los_uom in varchar2 default hr_api.g_varchar2
,p_rt_pct_fl_tm_val in number default hr_api.g_number
,p_rt_frz_los_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_age_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
,p_once_r_cntug_cd in varchar2 default hr_api.g_varchar2
,p_pl_ordr_num in number default hr_api.g_number
,p_plip_ordr_num in number default hr_api.g_number
,p_ptip_ordr_num in number default hr_api.g_number
,p_pep_attribute_category in varchar2 default hr_api.g_varchar2
,p_pep_attribute1 in varchar2 default hr_api.g_varchar2
,p_pep_attribute2 in varchar2 default hr_api.g_varchar2
,p_pep_attribute3 in varchar2 default hr_api.g_varchar2
,p_pep_attribute4 in varchar2 default hr_api.g_varchar2
,p_pep_attribute5 in varchar2 default hr_api.g_varchar2
,p_pep_attribute6 in varchar2 default hr_api.g_varchar2
,p_pep_attribute7 in varchar2 default hr_api.g_varchar2
,p_pep_attribute8 in varchar2 default hr_api.g_varchar2
,p_pep_attribute9 in varchar2 default hr_api.g_varchar2
,p_pep_attribute10 in varchar2 default hr_api.g_varchar2
,p_pep_attribute11 in varchar2 default hr_api.g_varchar2
,p_pep_attribute12 in varchar2 default hr_api.g_varchar2
,p_pep_attribute13 in varchar2 default hr_api.g_varchar2
,p_pep_attribute14 in varchar2 default hr_api.g_varchar2
,p_pep_attribute15 in varchar2 default hr_api.g_varchar2
,p_pep_attribute16 in varchar2 default hr_api.g_varchar2
,p_pep_attribute17 in varchar2 default hr_api.g_varchar2
,p_pep_attribute18 in varchar2 default hr_api.g_varchar2
,p_pep_attribute19 in varchar2 default hr_api.g_varchar2
,p_pep_attribute20 in varchar2 default hr_api.g_varchar2
,p_pep_attribute21 in varchar2 default hr_api.g_varchar2
,p_pep_attribute22 in varchar2 default hr_api.g_varchar2
,p_pep_attribute23 in varchar2 default hr_api.g_varchar2
,p_pep_attribute24 in varchar2 default hr_api.g_varchar2
,p_pep_attribute25 in varchar2 default hr_api.g_varchar2
,p_pep_attribute26 in varchar2 default hr_api.g_varchar2
,p_pep_attribute27 in varchar2 default hr_api.g_varchar2
,p_pep_attribute28 in varchar2 default hr_api.g_varchar2
,p_pep_attribute29 in varchar2 default hr_api.g_varchar2
,p_pep_attribute30 in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_perf_Eligible_Person';
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select
elig_per_id,
effective_start_date,
effective_end_date,
business_group_id,
pl_id,
pgm_id,
plip_id,
ptip_id,
ler_id,
person_id,
per_in_ler_id,
dpnt_othr_pl_cvrd_rl_flag,
prtn_ovridn_thru_dt,
pl_key_ee_flag,
pl_hghly_compd_flag,
elig_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
prtn_end_dt,
prtn_strt_dt,
wait_perd_cmpltn_dt,
wait_perd_strt_dt,
wv_ctfn_typ_cd,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
prtn_ovridn_flag,
no_mx_prtn_ovrid_thru_flag,
prtn_ovridn_rsn_cd,
age_uom,
los_uom,
ovrid_svc_dt,
inelg_rsn_cd,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
dstr_rstcn_flag,
pct_fl_tm_val,
wv_prtn_rsn_cd,
pl_wvd_flag,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
once_r_cntug_cd,
pl_ordr_num,
plip_ordr_num,
ptip_ordr_num,
pep_attribute_category,
pep_attribute1,
pep_attribute2,
pep_attribute3,
pep_attribute4,
pep_attribute5,
pep_attribute6,
pep_attribute7,
pep_attribute8,
pep_attribute9,
pep_attribute10,
pep_attribute11,
pep_attribute12,
pep_attribute13,
pep_attribute14,
pep_attribute15,
pep_attribute16,
pep_attribute17,
pep_attribute18,
pep_attribute19,
pep_attribute20,
pep_attribute21,
pep_attribute22,
pep_attribute23,
pep_attribute24,
pep_attribute25,
pep_attribute26,
pep_attribute27,
pep_attribute28,
pep_attribute29,
pep_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number
from ben_elig_per_f
where elig_per_id = p_elig_per_id
and p_effective_date
between effective_start_date and effective_end_date
for update nowait;
select t.created_by,
t.creation_date
from ben_elig_per_f t
where t.elig_per_id = p_elig_per_id
and t.effective_start_date =
ben_pep_shd.g_old_rec.effective_start_date
and t.effective_end_date = (l_validation_start_date - 1);
select pep.effective_start_date,
pep.effective_end_date
from ben_elig_per_f pep
where pep.elig_per_id = c_pep_id
and c_eff_date
between pep.effective_start_date and pep.effective_end_date;
select nvl(max(pep.object_version_number),0) + 1
from ben_elig_per_f pep
where pep.elig_per_id = c_pep_id;
select cpp.plip_id
from ben_plip_f cpp,
ben_elig_per_f pep
where cpp.pl_id = pep.pl_id
and pep.elig_per_id = p_elig_per_id
and cpp.pgm_id = cv_pgm_id /* Bug 5098907 */
and p_effective_date
between pep.effective_start_date and pep.effective_end_date
and p_effective_date
between cpp.effective_start_date and cpp.effective_end_date;
savepoint update_perf_Eligible_Person;
,p_program_update_date => g_pepinsplip.p_program_update_date
,p_object_version_number => l_dummy_ovn
,p_effective_date => g_pepinsplip.p_effective_date
,p_override_validation => g_pepinsplip.p_override_validation
,p_defer => false
);
ben_Eligible_Person_bk2.update_Eligible_Person_b
(p_elig_per_id => p_elig_per_id
,p_business_group_id => p_business_group_id
,p_pl_id => p_pl_id
,p_pgm_id => p_pgm_id
,p_plip_id => p_plip_id
,p_ptip_id => p_ptip_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_per_in_ler_id => p_per_in_ler_id
,p_dpnt_othr_pl_cvrd_rl_flag => p_dpnt_othr_pl_cvrd_rl_flag
,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
,p_pl_key_ee_flag => p_pl_key_ee_flag
,p_pl_hghly_compd_flag => p_pl_hghly_compd_flag
,p_elig_flag => p_elig_flag
,p_comp_ref_amt => p_comp_ref_amt
,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
,p_comp_ref_uom => p_comp_ref_uom
,p_age_val => p_age_val
,p_los_val => p_los_val
,p_prtn_end_dt => p_prtn_end_dt
,p_prtn_strt_dt => p_prtn_strt_dt
,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
,p_wait_perd_strt_dt => p_wait_perd_strt_dt
,p_wv_ctfn_typ_cd => p_wv_ctfn_typ_cd
,p_hrs_wkd_val => p_hrs_wkd_val
,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
,p_prtn_ovridn_flag => p_prtn_ovridn_flag
,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
,p_age_uom => p_age_uom
,p_los_uom => p_los_uom
,p_ovrid_svc_dt => p_ovrid_svc_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_frz_los_flag => p_frz_los_flag
,p_frz_age_flag => p_frz_age_flag
,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
,p_dstr_rstcn_flag => p_dstr_rstcn_flag
,p_pct_fl_tm_val => p_pct_fl_tm_val
,p_wv_prtn_rsn_cd => p_wv_prtn_rsn_cd
,p_pl_wvd_flag => p_pl_wvd_flag
,p_rt_comp_ref_amt => p_rt_comp_ref_amt
,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
,p_rt_comp_ref_uom => p_rt_comp_ref_uom
,p_rt_age_val => p_rt_age_val
,p_rt_los_val => p_rt_los_val
,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
,p_rt_age_uom => p_rt_age_uom
,p_rt_los_uom => p_rt_los_uom
,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
,p_rt_frz_los_flag => p_rt_frz_los_flag
,p_rt_frz_age_flag => p_rt_frz_age_flag
,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
,p_once_r_cntug_cd => p_once_r_cntug_cd
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_pep_attribute_category => p_pep_attribute_category
,p_pep_attribute1 => p_pep_attribute1
,p_pep_attribute2 => p_pep_attribute2
,p_pep_attribute3 => p_pep_attribute3
,p_pep_attribute4 => p_pep_attribute4
,p_pep_attribute5 => p_pep_attribute5
,p_pep_attribute6 => p_pep_attribute6
,p_pep_attribute7 => p_pep_attribute7
,p_pep_attribute8 => p_pep_attribute8
,p_pep_attribute9 => p_pep_attribute9
,p_pep_attribute10 => p_pep_attribute10
,p_pep_attribute11 => p_pep_attribute11
,p_pep_attribute12 => p_pep_attribute12
,p_pep_attribute13 => p_pep_attribute13
,p_pep_attribute14 => p_pep_attribute14
,p_pep_attribute15 => p_pep_attribute15
,p_pep_attribute16 => p_pep_attribute16
,p_pep_attribute17 => p_pep_attribute17
,p_pep_attribute18 => p_pep_attribute18
,p_pep_attribute19 => p_pep_attribute19
,p_pep_attribute20 => p_pep_attribute20
,p_pep_attribute21 => p_pep_attribute21
,p_pep_attribute22 => p_pep_attribute22
,p_pep_attribute23 => p_pep_attribute23
,p_pep_attribute24 => p_pep_attribute24
,p_pep_attribute25 => p_pep_attribute25
,p_pep_attribute26 => p_pep_attribute26
,p_pep_attribute27 => p_pep_attribute27
,p_pep_attribute28 => p_pep_attribute28
,p_pep_attribute29 => p_pep_attribute29
,p_pep_attribute30 => p_pep_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode);
(p_module_name => 'UPDATE_Eligible_Person'
,p_hook_type => 'BP');
p_program_update_date,
p_object_version_number
);
If (p_datetrack_mode <> 'INSERT') then
--
-- We must select and lock the current row.
--
Open c_old_rec;
update ben_elig_per_f t
set t.effective_end_date = l_validation_start_date - 1,
t.object_version_number = l_object_version_number
where t.elig_per_id = p_elig_per_id
and p_effective_date
between t.effective_start_date and t.effective_end_date;
If (p_datetrack_mode = 'UPDATE_OVERRIDE') then
hr_utility.set_location(l_proc, 15);
If (p_datetrack_mode = 'DELETE_NEXT_CHANGE') then
hr_utility.set_location(l_proc, 10);
delete from ben_elig_per_f
where elig_per_id = p_elig_per_id
and effective_start_date = l_validation_start_date;
delete from ben_elig_per_f
where elig_per_id = p_elig_per_id
and effective_start_date >= l_validation_start_date;
If (p_datetrack_mode <> 'INSERT') then
hr_utility.set_location(l_proc, 10);
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
insert into ben_elig_per_f
(
elig_per_id,
effective_start_date,
effective_end_date,
business_group_id,
pl_id,
pgm_id,
plip_id,
ptip_id,
ler_id,
person_id,
per_in_ler_id,
dpnt_othr_pl_cvrd_rl_flag,
prtn_ovridn_thru_dt,
pl_key_ee_flag,
pl_hghly_compd_flag,
elig_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
prtn_end_dt,
prtn_strt_dt,
wait_perd_cmpltn_dt,
wait_perd_strt_dt ,
wv_ctfn_typ_cd,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
prtn_ovridn_flag,
no_mx_prtn_ovrid_thru_flag,
prtn_ovridn_rsn_cd,
age_uom,
los_uom,
ovrid_svc_dt,
inelg_rsn_cd,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
dstr_rstcn_flag,
pct_fl_tm_val,
wv_prtn_rsn_cd,
pl_wvd_flag,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
once_r_cntug_cd,
pl_ordr_num,
plip_ordr_num,
ptip_ordr_num,
pep_attribute_category,
pep_attribute1,
pep_attribute2,
pep_attribute3,
pep_attribute4,
pep_attribute5,
pep_attribute6,
pep_attribute7,
pep_attribute8,
pep_attribute9,
pep_attribute10,
pep_attribute11,
pep_attribute12,
pep_attribute13,
pep_attribute14,
pep_attribute15,
pep_attribute16,
pep_attribute17,
pep_attribute18,
pep_attribute19,
pep_attribute20,
pep_attribute21,
pep_attribute22,
pep_attribute23,
pep_attribute24,
pep_attribute25,
pep_attribute26,
pep_attribute27,
pep_attribute28,
pep_attribute29,
pep_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
Values
(
l_rec.elig_per_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
l_rec.business_group_id,
l_rec.pl_id,
l_rec.pgm_id,
l_rec.plip_id,
l_rec.ptip_id,
l_rec.ler_id,
l_rec.person_id,
l_rec.per_in_ler_id,
l_rec.dpnt_othr_pl_cvrd_rl_flag,
l_rec.prtn_ovridn_thru_dt,
l_rec.pl_key_ee_flag,
l_rec.pl_hghly_compd_flag,
l_rec.elig_flag,
l_rec.comp_ref_amt,
l_rec.cmbn_age_n_los_val,
l_rec.comp_ref_uom,
l_rec.age_val,
l_rec.los_val,
l_rec.prtn_end_dt,
l_rec.prtn_strt_dt,
l_rec.wait_perd_cmpltn_dt,
l_rec.wait_perd_strt_dt ,
l_rec.wv_ctfn_typ_cd,
l_rec.hrs_wkd_val,
l_rec.hrs_wkd_bndry_perd_cd,
l_rec.prtn_ovridn_flag,
l_rec.no_mx_prtn_ovrid_thru_flag,
l_rec.prtn_ovridn_rsn_cd,
l_rec.age_uom,
l_rec.los_uom,
l_rec.ovrid_svc_dt,
l_rec.inelg_rsn_cd,
l_rec.frz_los_flag,
l_rec.frz_age_flag,
l_rec.frz_cmp_lvl_flag,
l_rec.frz_pct_fl_tm_flag,
l_rec.frz_hrs_wkd_flag,
l_rec.frz_comb_age_and_los_flag,
l_rec.dstr_rstcn_flag,
l_rec.pct_fl_tm_val,
l_rec.wv_prtn_rsn_cd,
l_rec.pl_wvd_flag,
l_rec.rt_comp_ref_amt,
l_rec.rt_cmbn_age_n_los_val,
l_rec.rt_comp_ref_uom,
l_rec.rt_age_val,
l_rec.rt_los_val,
l_rec.rt_hrs_wkd_val,
l_rec.rt_hrs_wkd_bndry_perd_cd,
l_rec.rt_age_uom,
l_rec.rt_los_uom,
l_rec.rt_pct_fl_tm_val,
l_rec.rt_frz_los_flag,
l_rec.rt_frz_age_flag,
l_rec.rt_frz_cmp_lvl_flag,
l_rec.rt_frz_pct_fl_tm_flag,
l_rec.rt_frz_hrs_wkd_flag,
l_rec.rt_frz_comb_age_and_los_flag,
l_rec.once_r_cntug_cd,
l_rec.pl_ordr_num,
l_rec.plip_ordr_num,
l_rec.ptip_ordr_num,
l_rec.pep_attribute_category,
l_rec.pep_attribute1,
l_rec.pep_attribute2,
l_rec.pep_attribute3,
l_rec.pep_attribute4,
l_rec.pep_attribute5,
l_rec.pep_attribute6,
l_rec.pep_attribute7,
l_rec.pep_attribute8,
l_rec.pep_attribute9,
l_rec.pep_attribute10,
l_rec.pep_attribute11,
l_rec.pep_attribute12,
l_rec.pep_attribute13,
l_rec.pep_attribute14,
l_rec.pep_attribute15,
l_rec.pep_attribute16,
l_rec.pep_attribute17,
l_rec.pep_attribute18,
l_rec.pep_attribute19,
l_rec.pep_attribute20,
l_rec.pep_attribute21,
l_rec.pep_attribute22,
l_rec.pep_attribute23,
l_rec.pep_attribute24,
l_rec.pep_attribute25,
l_rec.pep_attribute26,
l_rec.pep_attribute27,
l_rec.pep_attribute28,
l_rec.pep_attribute29,
l_rec.pep_attribute30,
l_rec.request_id,
l_rec.program_application_id,
l_rec.program_id,
l_rec.program_update_date,
l_rec.object_version_number,
l_created_by,
l_creation_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
update ben_elig_per_f
set
elig_per_id = l_rec.elig_per_id,
business_group_id = l_rec.business_group_id,
pl_id = l_rec.pl_id,
pgm_id = l_rec.pgm_id,
plip_id = l_rec.plip_id,
ptip_id = l_rec.ptip_id,
ler_id = l_rec.ler_id,
person_id = l_rec.person_id,
per_in_ler_id = l_rec.per_in_ler_id,
dpnt_othr_pl_cvrd_rl_flag = l_rec.dpnt_othr_pl_cvrd_rl_flag,
prtn_ovridn_thru_dt = l_rec.prtn_ovridn_thru_dt,
pl_key_ee_flag = l_rec.pl_key_ee_flag,
pl_hghly_compd_flag = l_rec.pl_hghly_compd_flag,
elig_flag = l_rec.elig_flag,
comp_ref_amt = l_rec.comp_ref_amt,
cmbn_age_n_los_val = l_rec.cmbn_age_n_los_val,
comp_ref_uom = l_rec.comp_ref_uom,
age_val = l_rec.age_val,
los_val = l_rec.los_val,
prtn_end_dt = l_rec.prtn_end_dt,
prtn_strt_dt = l_rec.prtn_strt_dt,
wait_perd_cmpltn_dt = l_rec.wait_perd_cmpltn_dt,
wait_perd_strt_dt = l_rec.wait_perd_strt_dt,
wv_ctfn_typ_cd = l_rec.wv_ctfn_typ_cd,
hrs_wkd_val = l_rec.hrs_wkd_val,
hrs_wkd_bndry_perd_cd = l_rec.hrs_wkd_bndry_perd_cd,
prtn_ovridn_flag = l_rec.prtn_ovridn_flag,
no_mx_prtn_ovrid_thru_flag = l_rec.no_mx_prtn_ovrid_thru_flag,
prtn_ovridn_rsn_cd = l_rec.prtn_ovridn_rsn_cd,
age_uom = l_rec.age_uom,
los_uom = l_rec.los_uom,
ovrid_svc_dt = l_rec.ovrid_svc_dt,
inelg_rsn_cd = l_rec.inelg_rsn_cd,
frz_los_flag = l_rec.frz_los_flag,
frz_age_flag = l_rec.frz_age_flag,
frz_cmp_lvl_flag = l_rec.frz_cmp_lvl_flag,
frz_pct_fl_tm_flag = l_rec.frz_pct_fl_tm_flag,
frz_hrs_wkd_flag = l_rec.frz_hrs_wkd_flag,
frz_comb_age_and_los_flag = l_rec.frz_comb_age_and_los_flag,
dstr_rstcn_flag = l_rec.dstr_rstcn_flag,
pct_fl_tm_val = l_rec.pct_fl_tm_val,
wv_prtn_rsn_cd = l_rec.wv_prtn_rsn_cd,
pl_wvd_flag = l_rec.pl_wvd_flag,
rt_comp_ref_amt = l_rec.rt_comp_ref_amt,
rt_cmbn_age_n_los_val = l_rec.rt_cmbn_age_n_los_val,
rt_comp_ref_uom = l_rec.rt_comp_ref_uom,
rt_age_val = l_rec.rt_age_val,
rt_los_val = l_rec.rt_los_val,
rt_hrs_wkd_val = l_rec.rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd = l_rec.rt_hrs_wkd_bndry_perd_cd,
rt_age_uom = l_rec.rt_age_uom,
rt_los_uom = l_rec.rt_los_uom,
rt_pct_fl_tm_val = l_rec.rt_pct_fl_tm_val,
rt_frz_los_flag = l_rec.rt_frz_los_flag,
rt_frz_age_flag = l_rec.rt_frz_age_flag,
rt_frz_cmp_lvl_flag = l_rec.rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag = l_rec.rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag = l_rec.rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag = l_rec.rt_frz_comb_age_and_los_flag,
once_r_cntug_cd = l_rec.once_r_cntug_cd,
pl_ordr_num = l_rec.pl_ordr_num,
plip_ordr_num = l_rec.plip_ordr_num,
ptip_ordr_num = l_rec.ptip_ordr_num,
pep_attribute_category = l_rec.pep_attribute_category,
pep_attribute1 = l_rec.pep_attribute1,
pep_attribute2 = l_rec.pep_attribute2,
pep_attribute3 = l_rec.pep_attribute3,
pep_attribute4 = l_rec.pep_attribute4,
pep_attribute5 = l_rec.pep_attribute5,
pep_attribute6 = l_rec.pep_attribute6,
pep_attribute7 = l_rec.pep_attribute7,
pep_attribute8 = l_rec.pep_attribute8,
pep_attribute9 = l_rec.pep_attribute9,
pep_attribute10 = l_rec.pep_attribute10,
pep_attribute11 = l_rec.pep_attribute11,
pep_attribute12 = l_rec.pep_attribute12,
pep_attribute13 = l_rec.pep_attribute13,
pep_attribute14 = l_rec.pep_attribute14,
pep_attribute15 = l_rec.pep_attribute15,
pep_attribute16 = l_rec.pep_attribute16,
pep_attribute17 = l_rec.pep_attribute17,
pep_attribute18 = l_rec.pep_attribute18,
pep_attribute19 = l_rec.pep_attribute19,
pep_attribute20 = l_rec.pep_attribute20,
pep_attribute21 = l_rec.pep_attribute21,
pep_attribute22 = l_rec.pep_attribute22,
pep_attribute23 = l_rec.pep_attribute23,
pep_attribute24 = l_rec.pep_attribute24,
pep_attribute25 = l_rec.pep_attribute25,
pep_attribute26 = l_rec.pep_attribute26,
pep_attribute27 = l_rec.pep_attribute27,
pep_attribute28 = l_rec.pep_attribute28,
pep_attribute29 = l_rec.pep_attribute29,
pep_attribute30 = l_rec.pep_attribute30,
request_id = l_rec.request_id,
program_application_id = l_rec.program_application_id,
program_id = l_rec.program_id,
program_update_date = l_rec.program_update_date,
object_version_number = l_rec.object_version_number
where elig_per_id = l_rec.elig_per_id
and effective_start_date = l_validation_start_date
and effective_end_date = l_validation_end_date;
ben_Eligible_Person_bk2.update_Eligible_Person_a
(p_elig_per_id => p_elig_per_id
,p_effective_start_date => l_rec.effective_start_date
,p_effective_end_date => l_rec.effective_end_date
,p_business_group_id => p_business_group_id
,p_pl_id => p_pl_id
,p_pgm_id => p_pgm_id
,p_plip_id => p_plip_id
,p_ptip_id => p_ptip_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_per_in_ler_id => p_per_in_ler_id
,p_dpnt_othr_pl_cvrd_rl_flag => p_dpnt_othr_pl_cvrd_rl_flag
,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
,p_pl_key_ee_flag => p_pl_key_ee_flag
,p_pl_hghly_compd_flag => p_pl_hghly_compd_flag
,p_elig_flag => p_elig_flag
,p_comp_ref_amt => p_comp_ref_amt
,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
,p_comp_ref_uom => p_comp_ref_uom
,p_age_val => p_age_val
,p_los_val => p_los_val
,p_prtn_end_dt => p_prtn_end_dt
,p_prtn_strt_dt => p_prtn_strt_dt
,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
,p_wait_perd_strt_dt => p_wait_perd_strt_dt
,p_wv_ctfn_typ_cd => p_wv_ctfn_typ_cd
,p_hrs_wkd_val => p_hrs_wkd_val
,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
,p_prtn_ovridn_flag => p_prtn_ovridn_flag
,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
,p_age_uom => p_age_uom
,p_los_uom => p_los_uom
,p_ovrid_svc_dt => p_ovrid_svc_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_frz_los_flag => p_frz_los_flag
,p_frz_age_flag => p_frz_age_flag
,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
,p_dstr_rstcn_flag => p_dstr_rstcn_flag
,p_pct_fl_tm_val => p_pct_fl_tm_val
,p_wv_prtn_rsn_cd => p_wv_prtn_rsn_cd
,p_pl_wvd_flag => p_pl_wvd_flag
,p_rt_comp_ref_amt => p_rt_comp_ref_amt
,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
,p_rt_comp_ref_uom => p_rt_comp_ref_uom
,p_rt_age_val => p_rt_age_val
,p_rt_los_val => p_rt_los_val
,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
,p_rt_age_uom => p_rt_age_uom
,p_rt_los_uom => p_rt_los_uom
,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
,p_rt_frz_los_flag => p_rt_frz_los_flag
,p_rt_frz_age_flag => p_rt_frz_age_flag
,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
,p_once_r_cntug_cd => p_once_r_cntug_cd
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_pep_attribute_category => p_pep_attribute_category
,p_pep_attribute1 => p_pep_attribute1
,p_pep_attribute2 => p_pep_attribute2
,p_pep_attribute3 => p_pep_attribute3
,p_pep_attribute4 => p_pep_attribute4
,p_pep_attribute5 => p_pep_attribute5
,p_pep_attribute6 => p_pep_attribute6
,p_pep_attribute7 => p_pep_attribute7
,p_pep_attribute8 => p_pep_attribute8
,p_pep_attribute9 => p_pep_attribute9
,p_pep_attribute10 => p_pep_attribute10
,p_pep_attribute11 => p_pep_attribute11
,p_pep_attribute12 => p_pep_attribute12
,p_pep_attribute13 => p_pep_attribute13
,p_pep_attribute14 => p_pep_attribute14
,p_pep_attribute15 => p_pep_attribute15
,p_pep_attribute16 => p_pep_attribute16
,p_pep_attribute17 => p_pep_attribute17
,p_pep_attribute18 => p_pep_attribute18
,p_pep_attribute19 => p_pep_attribute19
,p_pep_attribute20 => p_pep_attribute20
,p_pep_attribute21 => p_pep_attribute21
,p_pep_attribute22 => p_pep_attribute22
,p_pep_attribute23 => p_pep_attribute23
,p_pep_attribute24 => p_pep_attribute24
,p_pep_attribute25 => p_pep_attribute25
,p_pep_attribute26 => p_pep_attribute26
,p_pep_attribute27 => p_pep_attribute27
,p_pep_attribute28 => p_pep_attribute28
,p_pep_attribute29 => p_pep_attribute29
,p_pep_attribute30 => p_pep_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode);
(p_module_name => 'UPDATE_Eligible_Person'
,p_hook_type => 'AP');
ROLLBACK TO update_perf_Eligible_Person;
ROLLBACK TO update_perf_Eligible_Person;
end update_perf_Eligible_Person;
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
,p_override_validation in boolean default false
)
is
--
l_proc varchar2(72) := g_package||'create_perf_Elig_Person_Option';
l_last_update_date ben_elig_per_opt_f.last_update_date%TYPE;
l_last_updated_by ben_elig_per_opt_f.last_updated_by%TYPE;
l_last_update_login ben_elig_per_opt_f.last_update_login%TYPE;
Cursor C_Sel1 is select ben_elig_per_opt_f_s.nextval from sys.dual;
hr_utility.set_location('Insert: '||l_proc, 5);
insert into ben_elig_per_opt_f
( elig_per_opt_id,
elig_per_id,
effective_start_date,
effective_end_date,
prtn_ovridn_flag,
prtn_ovridn_thru_dt,
no_mx_prtn_ovrid_thru_flag,
elig_flag,
prtn_strt_dt,
prtn_end_dt,
--wait_perd_cmpltn_dt,
wait_perd_cmpltn_date,
wait_perd_strt_dt,
prtn_ovridn_rsn_cd,
pct_fl_tm_val,
opt_id,
per_in_ler_id,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
age_uom,
los_uom,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
ovrid_svc_dt,
inelg_rsn_cd,
once_r_cntug_cd,
oipl_ordr_num,
business_group_id,
epo_attribute_category,
epo_attribute1,
epo_attribute2,
epo_attribute3,
epo_attribute4,
epo_attribute5,
epo_attribute6,
epo_attribute7,
epo_attribute8,
epo_attribute9,
epo_attribute10,
epo_attribute11,
epo_attribute12,
epo_attribute13,
epo_attribute14,
epo_attribute15,
epo_attribute16,
epo_attribute17,
epo_attribute18,
epo_attribute19,
epo_attribute20,
epo_attribute21,
epo_attribute22,
epo_attribute23,
epo_attribute24,
epo_attribute25,
epo_attribute26,
epo_attribute27,
epo_attribute28,
epo_attribute29,
epo_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
Values
( l_elig_per_opt_id,
p_elig_per_id,
l_effective_start_date,
l_effective_end_date,
p_prtn_ovridn_flag,
p_prtn_ovridn_thru_dt,
p_no_mx_prtn_ovrid_thru_flag,
p_elig_flag,
p_prtn_strt_dt,
p_prtn_end_dt,
-- p_wait_perd_cmpltn_dt,
p_wait_perd_cmpltn_date,
p_wait_perd_strt_dt,
p_prtn_ovridn_rsn_cd,
p_pct_fl_tm_val,
p_opt_id,
p_per_in_ler_id,
p_rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom,
p_rt_age_val,
p_rt_los_val,
p_rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom,
p_rt_los_uom,
p_rt_pct_fl_tm_val,
p_rt_frz_los_flag,
p_rt_frz_age_flag,
p_rt_frz_cmp_lvl_flag,
p_rt_frz_pct_fl_tm_flag,
p_rt_frz_hrs_wkd_flag,
p_rt_frz_comb_age_and_los_flag,
p_comp_ref_amt,
p_cmbn_age_n_los_val,
p_comp_ref_uom,
p_age_val,
p_los_val,
p_hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd,
p_age_uom,
p_los_uom,
p_frz_los_flag,
p_frz_age_flag,
p_frz_cmp_lvl_flag,
p_frz_pct_fl_tm_flag,
p_frz_hrs_wkd_flag,
p_frz_comb_age_and_los_flag,
p_ovrid_svc_dt,
p_inelg_rsn_cd,
p_once_r_cntug_cd,
p_oipl_ordr_num,
p_business_group_id,
p_epo_attribute_category,
p_epo_attribute1,
p_epo_attribute2,
p_epo_attribute3,
p_epo_attribute4,
p_epo_attribute5,
p_epo_attribute6,
p_epo_attribute7,
p_epo_attribute8,
p_epo_attribute9,
p_epo_attribute10,
p_epo_attribute11,
p_epo_attribute12,
p_epo_attribute13,
p_epo_attribute14,
p_epo_attribute15,
p_epo_attribute16,
p_epo_attribute17,
p_epo_attribute18,
p_epo_attribute19,
p_epo_attribute20,
p_epo_attribute21,
p_epo_attribute22,
p_epo_attribute23,
p_epo_attribute24,
p_epo_attribute25,
p_epo_attribute26,
p_epo_attribute27,
p_epo_attribute28,
p_epo_attribute29,
p_epo_attribute30,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
l_object_version_number,
l_created_by,
l_creation_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
hr_utility.set_location('Dn Insert: '||l_proc, 5);
select
elig_per_opt_id,
elig_per_id,
effective_start_date,
effective_end_date,
prtn_ovridn_flag,
prtn_ovridn_thru_dt,
no_mx_prtn_ovrid_thru_flag,
elig_flag,
prtn_strt_dt,
prtn_end_dt,
-- wait_perd_cmpltn_dt,
wait_perd_cmpltn_date,
wait_perd_strt_dt,
prtn_ovridn_rsn_cd,
pct_fl_tm_val,
opt_id,
per_in_ler_id,
rt_comp_ref_amt,
rt_cmbn_age_n_los_val,
rt_comp_ref_uom,
rt_age_val,
rt_los_val,
rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd,
rt_age_uom,
rt_los_uom,
rt_pct_fl_tm_val,
rt_frz_los_flag,
rt_frz_age_flag,
rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag,
comp_ref_amt,
cmbn_age_n_los_val,
comp_ref_uom,
age_val,
los_val,
hrs_wkd_val,
hrs_wkd_bndry_perd_cd,
age_uom,
los_uom,
frz_los_flag,
frz_age_flag,
frz_cmp_lvl_flag,
frz_pct_fl_tm_flag,
frz_hrs_wkd_flag,
frz_comb_age_and_los_flag,
ovrid_svc_dt,
inelg_rsn_cd,
once_r_cntug_cd,
oipl_ordr_num,
business_group_id,
epo_attribute_category,
epo_attribute1,
epo_attribute2,
epo_attribute3,
epo_attribute4,
epo_attribute5,
epo_attribute6,
epo_attribute7,
epo_attribute8,
epo_attribute9,
epo_attribute10,
epo_attribute11,
epo_attribute12,
epo_attribute13,
epo_attribute14,
epo_attribute15,
epo_attribute16,
epo_attribute17,
epo_attribute18,
epo_attribute19,
epo_attribute20,
epo_attribute21,
epo_attribute22,
epo_attribute23,
epo_attribute24,
epo_attribute25,
epo_attribute26,
epo_attribute27,
epo_attribute28,
epo_attribute29,
epo_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number
from ben_elig_per_opt_f
where elig_per_opt_id = p_elig_per_opt_id
and p_effective_date
between effective_start_date and effective_end_date;
If (p_datetrack_mode <> 'INSERT') then
--
-- We must select and lock the current row.
--
Open C_Sel1;
If (p_rec.program_update_date = hr_api.g_date) then
p_rec.program_update_date :=
ben_epo_shd.g_old_rec.program_update_date;
Procedure dt_pre_update
(p_rec in out nocopy ben_epo_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
)
is
--
l_proc varchar2(72) := g_package||'dt_pre_update';
If (p_datetrack_mode = 'UPDATE_OVERRIDE') then
hr_utility.set_location(l_proc, 15);
ben_epo_del.delete_dml
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
ben_epo_ins.insert_dml
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End dt_pre_update;
Procedure pre_update
(p_rec in out nocopy ben_epo_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
)
is
--
l_proc varchar2(72) := g_package||'pre_update';
dt_pre_update
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End pre_update;
Procedure dt_update_dml
(p_rec in out nocopy ben_epo_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
)
is
--
l_proc varchar2(72) := g_package||'dt_update_dml';
update ben_elig_per_opt_f
set
elig_per_opt_id = p_rec.elig_per_opt_id,
elig_per_id = p_rec.elig_per_id,
prtn_ovridn_flag = p_rec.prtn_ovridn_flag,
prtn_ovridn_thru_dt = p_rec.prtn_ovridn_thru_dt,
no_mx_prtn_ovrid_thru_flag = p_rec.no_mx_prtn_ovrid_thru_flag,
elig_flag = p_rec.elig_flag,
prtn_strt_dt = p_rec.prtn_strt_dt,
prtn_end_dt = p_rec.prtn_end_dt,
-- wait_perd_cmpltn_dt = p_rec.wait_perd_cmpltn_dt,
wait_perd_cmpltn_date = p_rec.wait_perd_cmpltn_date,
wait_perd_strt_dt = p_rec.wait_perd_strt_dt,
prtn_ovridn_rsn_cd = p_rec.prtn_ovridn_rsn_cd,
pct_fl_tm_val = p_rec.pct_fl_tm_val,
opt_id = p_rec.opt_id,
per_in_ler_id = p_rec.per_in_ler_id,
rt_comp_ref_amt = p_rec.rt_comp_ref_amt,
rt_cmbn_age_n_los_val = p_rec.rt_cmbn_age_n_los_val,
rt_comp_ref_uom = p_rec.rt_comp_ref_uom,
rt_age_val = p_rec.rt_age_val,
rt_los_val = p_rec.rt_los_val,
rt_hrs_wkd_val = p_rec.rt_hrs_wkd_val,
rt_hrs_wkd_bndry_perd_cd = p_rec.rt_hrs_wkd_bndry_perd_cd,
rt_age_uom = p_rec.rt_age_uom,
rt_los_uom = p_rec.rt_los_uom,
rt_pct_fl_tm_val = p_rec.rt_pct_fl_tm_val,
rt_frz_los_flag = p_rec.rt_frz_los_flag,
rt_frz_age_flag = p_rec.rt_frz_age_flag,
rt_frz_cmp_lvl_flag = p_rec.rt_frz_cmp_lvl_flag,
rt_frz_pct_fl_tm_flag = p_rec.rt_frz_pct_fl_tm_flag,
rt_frz_hrs_wkd_flag = p_rec.rt_frz_hrs_wkd_flag,
rt_frz_comb_age_and_los_flag = p_rec.rt_frz_comb_age_and_los_flag,
comp_ref_amt = p_rec.comp_ref_amt,
cmbn_age_n_los_val = p_rec.cmbn_age_n_los_val,
comp_ref_uom = p_rec.comp_ref_uom,
age_val = p_rec.age_val,
los_val = p_rec.los_val,
hrs_wkd_val = p_rec.hrs_wkd_val,
hrs_wkd_bndry_perd_cd = p_rec.hrs_wkd_bndry_perd_cd,
age_uom = p_rec.age_uom,
los_uom = p_rec.los_uom,
frz_los_flag = p_rec.frz_los_flag,
frz_age_flag = p_rec.frz_age_flag,
frz_cmp_lvl_flag = p_rec.frz_cmp_lvl_flag,
frz_pct_fl_tm_flag = p_rec.frz_pct_fl_tm_flag,
frz_hrs_wkd_flag = p_rec.frz_hrs_wkd_flag,
frz_comb_age_and_los_flag = p_rec.frz_comb_age_and_los_flag,
ovrid_svc_dt = p_rec.ovrid_svc_dt,
inelg_rsn_cd = p_rec.inelg_rsn_cd,
once_r_cntug_cd = p_rec.once_r_cntug_cd,
oipl_ordr_num = p_rec.oipl_ordr_num,
business_group_id = p_rec.business_group_id,
epo_attribute_category = p_rec.epo_attribute_category,
epo_attribute1 = p_rec.epo_attribute1,
epo_attribute2 = p_rec.epo_attribute2,
epo_attribute3 = p_rec.epo_attribute3,
epo_attribute4 = p_rec.epo_attribute4,
epo_attribute5 = p_rec.epo_attribute5,
epo_attribute6 = p_rec.epo_attribute6,
epo_attribute7 = p_rec.epo_attribute7,
epo_attribute8 = p_rec.epo_attribute8,
epo_attribute9 = p_rec.epo_attribute9,
epo_attribute10 = p_rec.epo_attribute10,
epo_attribute11 = p_rec.epo_attribute11,
epo_attribute12 = p_rec.epo_attribute12,
epo_attribute13 = p_rec.epo_attribute13,
epo_attribute14 = p_rec.epo_attribute14,
epo_attribute15 = p_rec.epo_attribute15,
epo_attribute16 = p_rec.epo_attribute16,
epo_attribute17 = p_rec.epo_attribute17,
epo_attribute18 = p_rec.epo_attribute18,
epo_attribute19 = p_rec.epo_attribute19,
epo_attribute20 = p_rec.epo_attribute20,
epo_attribute21 = p_rec.epo_attribute21,
epo_attribute22 = p_rec.epo_attribute22,
epo_attribute23 = p_rec.epo_attribute23,
epo_attribute24 = p_rec.epo_attribute24,
epo_attribute25 = p_rec.epo_attribute25,
epo_attribute26 = p_rec.epo_attribute26,
epo_attribute27 = p_rec.epo_attribute27,
epo_attribute28 = p_rec.epo_attribute28,
epo_attribute29 = p_rec.epo_attribute29,
epo_attribute30 = p_rec.epo_attribute30,
request_id = p_rec.request_id,
program_application_id = p_rec.program_application_id,
program_id = p_rec.program_id,
program_update_date = p_rec.program_update_date,
object_version_number = p_rec.object_version_number
where elig_per_opt_id = p_rec.elig_per_opt_id
and effective_start_date = p_validation_start_date
and effective_end_date = p_validation_end_date;
End dt_update_dml;
Procedure update_dml
(p_rec in out nocopy ben_epo_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
)
is
--
l_proc varchar2(72) := g_package||'update_dml';
dt_update_dml(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End update_dml;
nvl(p_rec.per_in_ler_id,-1) <> nvl(ben_epo_shd.g_old_rec.per_in_ler_id,-1) or -- bug 5478994 epo to be updated first-time by unrestricted run
nvl(p_rec.opt_id,-1) <> nvl(ben_epo_shd.g_old_rec.opt_id,-1) or
nvl(p_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy'))<>
nvl(ben_epo_shd.g_old_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy')) or
nvl(p_rec.elig_flag,'N') <> nvl(ben_epo_shd.g_old_rec.elig_flag,'N') or
nvl(p_rec.comp_ref_amt,-1) <> nvl(ben_epo_shd.g_old_rec.comp_ref_amt,-1) or
nvl(p_rec.cmbn_age_n_los_val, -1) <> nvl(ben_epo_shd.g_old_rec.cmbn_age_n_los_val, -1) or
nvl(p_rec.comp_ref_uom,'X') <> nvl(ben_epo_shd.g_old_rec.comp_ref_uom,'X') or
nvl(p_rec.age_val, -1) <> nvl(ben_epo_shd.g_old_rec.age_val, -1) or
nvl(p_rec.los_val, -1) <> nvl(ben_epo_shd.g_old_rec.los_val, -1) or
nvl(p_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
nvl(ben_epo_shd.g_old_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) or
nvl(p_rec.wait_perd_cmpltn_date,to_date('01-01-1001','dd-mm-yyyy')) <>
nvl(ben_epo_shd.g_old_rec.wait_perd_cmpltn_date,to_date('01-01-1001','dd-mm-yyyy')) or
nvl(p_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
nvl(ben_epo_shd.g_old_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) or
nvl(p_rec.hrs_wkd_val,-1) <> nvl(ben_epo_shd.g_old_rec.hrs_wkd_val,-1) or
nvl(p_rec.hrs_wkd_bndry_perd_cd,'X') <> nvl(ben_epo_shd.g_old_rec.hrs_wkd_bndry_perd_cd,'X') or
nvl(p_rec.prtn_ovridn_flag,'N') <> nvl(ben_epo_shd.g_old_rec.prtn_ovridn_flag,'N') or
nvl(p_rec.no_mx_prtn_ovrid_thru_flag,'N') <>
nvl(ben_epo_shd.g_old_rec.no_mx_prtn_ovrid_thru_flag,'N') or
nvl(p_rec.prtn_ovridn_rsn_cd, 'X') <>
nvl(ben_epo_shd.g_old_rec.prtn_ovridn_rsn_cd, 'X') or
nvl(p_rec.age_uom,'X') <> nvl(ben_epo_shd.g_old_rec.age_uom,'X') or
nvl(p_rec.los_uom,'X') <> nvl(ben_epo_shd.g_old_rec.los_uom,'X') or
nvl(p_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
nvl(ben_epo_shd.g_old_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) or
nvl(p_rec.inelg_rsn_cd,'X') <> nvl(ben_epo_shd.g_old_rec.inelg_rsn_cd,'X') or
nvl(p_rec.frz_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_los_flag,'N') or
nvl(p_rec.frz_age_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_age_flag,'N') or
nvl(p_rec.frz_cmp_lvl_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_cmp_lvl_flag,'N') or
nvl(p_rec.frz_pct_fl_tm_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_pct_fl_tm_flag,'N') or
nvl(p_rec.frz_hrs_wkd_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_hrs_wkd_flag,'N') or
nvl(p_rec.frz_comb_age_and_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_comb_age_and_los_flag,'N') or
nvl(p_rec.pct_fl_tm_val,-1) <> nvl(ben_epo_shd.g_old_rec.pct_fl_tm_val,-1) or
nvl(p_rec.rt_comp_ref_amt,-1) <> nvl(ben_epo_shd.g_old_rec.rt_comp_ref_amt,-1) or
nvl(p_rec.rt_cmbn_age_n_los_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_cmbn_age_n_los_val,-1) or
nvl(p_rec.rt_comp_ref_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_comp_ref_uom,'X') or
nvl(p_rec.rt_age_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_age_val,-1) or
nvl(p_rec.rt_los_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_los_val,-1) or
nvl(p_rec.rt_hrs_wkd_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_hrs_wkd_val,-1) or
nvl(p_rec.rt_hrs_wkd_bndry_perd_cd,'X') <>
nvl(ben_epo_shd.g_old_rec.rt_hrs_wkd_bndry_perd_cd,'X') or
nvl(p_rec.rt_age_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_age_uom,'X') or
nvl(p_rec.rt_los_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_los_uom,'X') or
nvl(p_rec.rt_pct_fl_tm_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_pct_fl_tm_val,-1) or
nvl(p_rec.rt_frz_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_los_flag,'N') or
nvl(p_rec.rt_frz_age_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_age_flag,'N') or
nvl(p_rec.rt_frz_cmp_lvl_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_cmp_lvl_flag,'N') or
nvl(p_rec.rt_frz_pct_fl_tm_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_pct_fl_tm_flag,'N') or
nvl(p_rec.rt_frz_hrs_wkd_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_hrs_wkd_flag,'N') or
nvl(p_rec.rt_frz_comb_age_and_los_flag,'N') <>
nvl(ben_epo_shd.g_old_rec.rt_frz_comb_age_and_los_flag,'N') or
nvl(p_rec.once_r_cntug_cd,'X') <> nvl(ben_epo_shd.g_old_rec.once_r_cntug_cd,'X') or
nvl(p_rec.oipl_ordr_num,-1) <> nvl(ben_epo_shd.g_old_rec.oipl_ordr_num,-1) or
nvl(l_new_ler_id,-1) <> nvl(l_old_ler_id,-1) -- Bug 12964414
) then
--
null;
pre_update
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);
update_dml
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);
p_program_update_date in date default hr_api.g_date,
p_object_version_number in out nocopy number,
p_effective_date in date,
p_datetrack_mode in varchar2
) is
--
l_rec ben_epo_shd.g_rec_type;
p_program_update_date,
p_object_version_number
);
procedure update_perf_Elig_Person_Option
(p_validate in boolean default false
,p_elig_per_opt_id in number
,p_elig_per_id in number default hr_api.g_number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_prtn_ovridn_flag in varchar2 default hr_api.g_varchar2
,p_prtn_ovridn_thru_dt in date default hr_api.g_date
,p_no_mx_prtn_ovrid_thru_flag in varchar2 default hr_api.g_varchar2
,p_elig_flag in varchar2 default hr_api.g_varchar2
,p_prtn_strt_dt in date default hr_api.g_date
,p_prtn_end_dt in date default hr_api.g_date
--,p_wait_perd_cmpltn_dt in date default hr_api.g_date
,p_wait_perd_cmpltn_date in date default hr_api.g_date
,p_wait_perd_strt_dt in date default hr_api.g_date
,p_prtn_ovridn_rsn_cd in varchar2 default hr_api.g_varchar2
,p_pct_fl_tm_val in number default hr_api.g_number
,p_opt_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_rt_comp_ref_amt in number default hr_api.g_number
,p_rt_cmbn_age_n_los_val in number default hr_api.g_number
,p_rt_comp_ref_uom in varchar2 default hr_api.g_varchar2
,p_rt_age_val in number default hr_api.g_number
,p_rt_los_val in number default hr_api.g_number
,p_rt_hrs_wkd_val in number default hr_api.g_number
,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
,p_rt_age_uom in varchar2 default hr_api.g_varchar2
,p_rt_los_uom in varchar2 default hr_api.g_varchar2
,p_rt_pct_fl_tm_val in number default hr_api.g_number
,p_rt_frz_los_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_age_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
,p_rt_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
,p_comp_ref_amt in number default hr_api.g_number
,p_cmbn_age_n_los_val in number default hr_api.g_number
,p_comp_ref_uom in varchar2 default hr_api.g_varchar2
,p_age_val in number default hr_api.g_number
,p_los_val in number default hr_api.g_number
,p_hrs_wkd_val in number default hr_api.g_number
,p_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
,p_age_uom in varchar2 default hr_api.g_varchar2
,p_los_uom in varchar2 default hr_api.g_varchar2
,p_frz_los_flag in varchar2 default hr_api.g_varchar2
,p_frz_age_flag in varchar2 default hr_api.g_varchar2
,p_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
,p_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
,p_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
,p_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
,p_ovrid_svc_dt in date default hr_api.g_date
,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
,p_once_r_cntug_cd in varchar2 default hr_api.g_varchar2
,p_oipl_ordr_num in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_epo_attribute_category in varchar2 default hr_api.g_varchar2
,p_epo_attribute1 in varchar2 default hr_api.g_varchar2
,p_epo_attribute2 in varchar2 default hr_api.g_varchar2
,p_epo_attribute3 in varchar2 default hr_api.g_varchar2
,p_epo_attribute4 in varchar2 default hr_api.g_varchar2
,p_epo_attribute5 in varchar2 default hr_api.g_varchar2
,p_epo_attribute6 in varchar2 default hr_api.g_varchar2
,p_epo_attribute7 in varchar2 default hr_api.g_varchar2
,p_epo_attribute8 in varchar2 default hr_api.g_varchar2
,p_epo_attribute9 in varchar2 default hr_api.g_varchar2
,p_epo_attribute10 in varchar2 default hr_api.g_varchar2
,p_epo_attribute11 in varchar2 default hr_api.g_varchar2
,p_epo_attribute12 in varchar2 default hr_api.g_varchar2
,p_epo_attribute13 in varchar2 default hr_api.g_varchar2
,p_epo_attribute14 in varchar2 default hr_api.g_varchar2
,p_epo_attribute15 in varchar2 default hr_api.g_varchar2
,p_epo_attribute16 in varchar2 default hr_api.g_varchar2
,p_epo_attribute17 in varchar2 default hr_api.g_varchar2
,p_epo_attribute18 in varchar2 default hr_api.g_varchar2
,p_epo_attribute19 in varchar2 default hr_api.g_varchar2
,p_epo_attribute20 in varchar2 default hr_api.g_varchar2
,p_epo_attribute21 in varchar2 default hr_api.g_varchar2
,p_epo_attribute22 in varchar2 default hr_api.g_varchar2
,p_epo_attribute23 in varchar2 default hr_api.g_varchar2
,p_epo_attribute24 in varchar2 default hr_api.g_varchar2
,p_epo_attribute25 in varchar2 default hr_api.g_varchar2
,p_epo_attribute26 in varchar2 default hr_api.g_varchar2
,p_epo_attribute27 in varchar2 default hr_api.g_varchar2
,p_epo_attribute28 in varchar2 default hr_api.g_varchar2
,p_epo_attribute29 in varchar2 default hr_api.g_varchar2
,p_epo_attribute30 in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_perf_Elig_Person_Option';
savepoint update_perf_Elig_Person_Option;
ben_Elig_Person_Option_bk2.update_Elig_Person_Option_b
(
p_elig_per_opt_id => p_elig_per_opt_id
,p_elig_per_id => p_elig_per_id
,p_prtn_ovridn_flag => p_prtn_ovridn_flag
,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
,p_elig_flag => p_elig_flag
,p_prtn_strt_dt => p_prtn_strt_dt
,p_prtn_end_dt => p_prtn_end_dt
--,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
,p_wait_perd_cmpltn_date => p_wait_perd_cmpltn_date
,p_wait_perd_strt_dt => p_wait_perd_strt_dt
,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
,p_pct_fl_tm_val => p_pct_fl_tm_val
,p_opt_id => p_opt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_rt_comp_ref_amt => p_rt_comp_ref_amt
,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
,p_rt_comp_ref_uom => p_rt_comp_ref_uom
,p_rt_age_val => p_rt_age_val
,p_rt_los_val => p_rt_los_val
,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
,p_rt_age_uom => p_rt_age_uom
,p_rt_los_uom => p_rt_los_uom
,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
,p_rt_frz_los_flag => p_rt_frz_los_flag
,p_rt_frz_age_flag => p_rt_frz_age_flag
,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
,p_comp_ref_amt => p_comp_ref_amt
,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
,p_comp_ref_uom => p_comp_ref_uom
,p_age_val => p_age_val
,p_los_val => p_los_val
,p_hrs_wkd_val => p_hrs_wkd_val
,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
,p_age_uom => p_age_uom
,p_los_uom => p_los_uom
,p_frz_los_flag => p_frz_los_flag
,p_frz_age_flag => p_frz_age_flag
,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
,p_ovrid_svc_dt => p_ovrid_svc_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_once_r_cntug_cd => p_once_r_cntug_cd
,p_oipl_ordr_num => p_oipl_ordr_num
,p_business_group_id => p_business_group_id
,p_epo_attribute_category => p_epo_attribute_category
,p_epo_attribute1 => p_epo_attribute1
,p_epo_attribute2 => p_epo_attribute2
,p_epo_attribute3 => p_epo_attribute3
,p_epo_attribute4 => p_epo_attribute4
,p_epo_attribute5 => p_epo_attribute5
,p_epo_attribute6 => p_epo_attribute6
,p_epo_attribute7 => p_epo_attribute7
,p_epo_attribute8 => p_epo_attribute8
,p_epo_attribute9 => p_epo_attribute9
,p_epo_attribute10 => p_epo_attribute10
,p_epo_attribute11 => p_epo_attribute11
,p_epo_attribute12 => p_epo_attribute12
,p_epo_attribute13 => p_epo_attribute13
,p_epo_attribute14 => p_epo_attribute14
,p_epo_attribute15 => p_epo_attribute15
,p_epo_attribute16 => p_epo_attribute16
,p_epo_attribute17 => p_epo_attribute17
,p_epo_attribute18 => p_epo_attribute18
,p_epo_attribute19 => p_epo_attribute19
,p_epo_attribute20 => p_epo_attribute20
,p_epo_attribute21 => p_epo_attribute21
,p_epo_attribute22 => p_epo_attribute22
,p_epo_attribute23 => p_epo_attribute23
,p_epo_attribute24 => p_epo_attribute24
,p_epo_attribute25 => p_epo_attribute25
,p_epo_attribute26 => p_epo_attribute26
,p_epo_attribute27 => p_epo_attribute27
,p_epo_attribute28 => p_epo_attribute28
,p_epo_attribute29 => p_epo_attribute29
,p_epo_attribute30 => p_epo_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_Elig_Person_Option'
,p_hook_type => 'BP'
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
ben_Elig_Person_Option_bk2.update_Elig_Person_Option_a
(
p_elig_per_opt_id => p_elig_per_opt_id
,p_elig_per_id => p_elig_per_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtn_ovridn_flag => p_prtn_ovridn_flag
,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
,p_elig_flag => p_elig_flag
,p_prtn_strt_dt => p_prtn_strt_dt
,p_prtn_end_dt => p_prtn_end_dt
--,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
,p_wait_perd_cmpltn_date => p_wait_perd_cmpltn_date
,p_wait_perd_strt_dt => p_wait_perd_Strt_dt
,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
,p_pct_fl_tm_val => p_pct_fl_tm_val
,p_opt_id => p_opt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_rt_comp_ref_amt => p_rt_comp_ref_amt
,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
,p_rt_comp_ref_uom => p_rt_comp_ref_uom
,p_rt_age_val => p_rt_age_val
,p_rt_los_val => p_rt_los_val
,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
,p_rt_age_uom => p_rt_age_uom
,p_rt_los_uom => p_rt_los_uom
,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
,p_rt_frz_los_flag => p_rt_frz_los_flag
,p_rt_frz_age_flag => p_rt_frz_age_flag
,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
,p_comp_ref_amt => p_comp_ref_amt
,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
,p_comp_ref_uom => p_comp_ref_uom
,p_age_val => p_age_val
,p_los_val => p_los_val
,p_hrs_wkd_val => p_hrs_wkd_val
,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
,p_age_uom => p_age_uom
,p_los_uom => p_los_uom
,p_frz_los_flag => p_frz_los_flag
,p_frz_age_flag => p_frz_age_flag
,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
,p_ovrid_svc_dt => p_ovrid_svc_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_once_r_cntug_cd => p_once_r_cntug_cd
,p_oipl_ordr_num => p_oipl_ordr_num
,p_business_group_id => p_business_group_id
,p_epo_attribute_category => p_epo_attribute_category
,p_epo_attribute1 => p_epo_attribute1
,p_epo_attribute2 => p_epo_attribute2
,p_epo_attribute3 => p_epo_attribute3
,p_epo_attribute4 => p_epo_attribute4
,p_epo_attribute5 => p_epo_attribute5
,p_epo_attribute6 => p_epo_attribute6
,p_epo_attribute7 => p_epo_attribute7
,p_epo_attribute8 => p_epo_attribute8
,p_epo_attribute9 => p_epo_attribute9
,p_epo_attribute10 => p_epo_attribute10
,p_epo_attribute11 => p_epo_attribute11
,p_epo_attribute12 => p_epo_attribute12
,p_epo_attribute13 => p_epo_attribute13
,p_epo_attribute14 => p_epo_attribute14
,p_epo_attribute15 => p_epo_attribute15
,p_epo_attribute16 => p_epo_attribute16
,p_epo_attribute17 => p_epo_attribute17
,p_epo_attribute18 => p_epo_attribute18
,p_epo_attribute19 => p_epo_attribute19
,p_epo_attribute20 => p_epo_attribute20
,p_epo_attribute21 => p_epo_attribute21
,p_epo_attribute22 => p_epo_attribute22
,p_epo_attribute23 => p_epo_attribute23
,p_epo_attribute24 => p_epo_attribute24
,p_epo_attribute25 => p_epo_attribute25
,p_epo_attribute26 => p_epo_attribute26
,p_epo_attribute27 => p_epo_attribute27
,p_epo_attribute28 => p_epo_attribute28
,p_epo_attribute29 => p_epo_attribute29
,p_epo_attribute30 => p_epo_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_Elig_Person_Option'
,p_hook_type => 'AP'
);
ROLLBACK TO update_perf_Elig_Person_Option;
ROLLBACK TO update_perf_Elig_Person_Option;
end update_perf_Elig_Person_Option;
select distinct p.elig_per_opt_id
from ben_elig_per_opt_f p
where p.rowid
between p_start_rowid and p_end_rowid
and p.wait_perd_cmpltn_dt||'' is not null ;
update ben_elig_per_opt_f epo
set wait_perd_cmpltn_date = fnd_date.string_to_date(wait_perd_cmpltn_dt,'DD-MON-RRRR')
where epo.elig_per_opt_id = t_epo_id(i) ;
t_epo_id.delete;