[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
) is
--
-- Declare cursors and local variables
--
l_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%TYPE;
,p_program_update_date => p_program_update_date
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
)
is
--
l_proc varchar2(72) := g_package||'create_perf_ELIG_DPNT';
l_last_update_date ben_elig_dpnt.last_update_date%TYPE;
l_last_updated_by ben_elig_dpnt.last_updated_by%TYPE;
l_last_update_login ben_elig_dpnt.last_update_login%TYPE;
Cursor C_Sel1 is select ben_elig_dpnt_s.nextval from sys.dual;
hr_utility.set_location('Insert EGD: '||l_proc, 5);
insert into ben_elig_dpnt
( elig_dpnt_id,
create_dt,
elig_strt_dt,
elig_thru_dt,
ovrdn_flag,
ovrdn_thru_dt,
inelg_rsn_cd,
dpnt_inelig_flag,
elig_per_elctbl_chc_id,
per_in_ler_id,
elig_per_id,
elig_per_opt_id,
elig_cvrd_dpnt_id,
dpnt_person_id,
business_group_id,
egd_attribute_category,
egd_attribute1,
egd_attribute2,
egd_attribute3,
egd_attribute4,
egd_attribute5,
egd_attribute6,
egd_attribute7,
egd_attribute8,
egd_attribute9,
egd_attribute10,
egd_attribute11,
egd_attribute12,
egd_attribute13,
egd_attribute14,
egd_attribute15,
egd_attribute16,
egd_attribute17,
egd_attribute18,
egd_attribute19,
egd_attribute20,
egd_attribute21,
egd_attribute22,
egd_attribute23,
egd_attribute24,
egd_attribute25,
egd_attribute26,
egd_attribute27,
egd_attribute28,
egd_attribute29,
egd_attribute30,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number
)
Values
( ben_elig_dpnt_s.nextval,
p_create_dt,
p_elig_strt_dt,
p_elig_thru_dt,
p_ovrdn_flag,
p_ovrdn_thru_dt,
p_inelg_rsn_cd,
p_dpnt_inelig_flag,
p_elig_per_elctbl_chc_id,
p_per_in_ler_id,
p_elig_per_id,
p_elig_per_opt_id,
p_elig_cvrd_dpnt_id,
p_dpnt_person_id,
p_business_group_id,
p_egd_attribute_category,
p_egd_attribute1,
p_egd_attribute2,
p_egd_attribute3,
p_egd_attribute4,
p_egd_attribute5,
p_egd_attribute6,
p_egd_attribute7,
p_egd_attribute8,
p_egd_attribute9,
p_egd_attribute10,
p_egd_attribute11,
p_egd_attribute12,
p_egd_attribute13,
p_egd_attribute14,
p_egd_attribute15,
p_egd_attribute16,
p_egd_attribute17,
p_egd_attribute18,
p_egd_attribute19,
p_egd_attribute20,
p_egd_attribute21,
p_egd_attribute22,
p_egd_attribute23,
p_egd_attribute24,
p_egd_attribute25,
p_egd_attribute26,
p_egd_attribute27,
p_egd_attribute28,
p_egd_attribute29,
p_egd_attribute30,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
l_object_version_number
) RETURNING elig_dpnt_id into l_elig_dpnt_id;
hr_utility.set_location('Dn Insert: '||l_proc, 5);
procedure update_ELIG_DPNT
(p_validate in boolean default false
,p_elig_dpnt_id in number
,p_create_dt in date default hr_api.g_date
,p_elig_strt_dt in date default hr_api.g_date
,p_elig_thru_dt in date default hr_api.g_date
,p_ovrdn_flag in varchar2 default hr_api.g_varchar2
,p_ovrdn_thru_dt in date default hr_api.g_date
,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
,p_dpnt_inelig_flag in varchar2 default hr_api.g_varchar2
,p_elig_per_elctbl_chc_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_elig_per_id in number default hr_api.g_number
,p_elig_per_opt_id in number default hr_api.g_number
,p_elig_cvrd_dpnt_id in number default hr_api.g_number
,p_dpnt_person_id in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_egd_attribute_category in varchar2 default hr_api.g_varchar2
,p_egd_attribute1 in varchar2 default hr_api.g_varchar2
,p_egd_attribute2 in varchar2 default hr_api.g_varchar2
,p_egd_attribute3 in varchar2 default hr_api.g_varchar2
,p_egd_attribute4 in varchar2 default hr_api.g_varchar2
,p_egd_attribute5 in varchar2 default hr_api.g_varchar2
,p_egd_attribute6 in varchar2 default hr_api.g_varchar2
,p_egd_attribute7 in varchar2 default hr_api.g_varchar2
,p_egd_attribute8 in varchar2 default hr_api.g_varchar2
,p_egd_attribute9 in varchar2 default hr_api.g_varchar2
,p_egd_attribute10 in varchar2 default hr_api.g_varchar2
,p_egd_attribute11 in varchar2 default hr_api.g_varchar2
,p_egd_attribute12 in varchar2 default hr_api.g_varchar2
,p_egd_attribute13 in varchar2 default hr_api.g_varchar2
,p_egd_attribute14 in varchar2 default hr_api.g_varchar2
,p_egd_attribute15 in varchar2 default hr_api.g_varchar2
,p_egd_attribute16 in varchar2 default hr_api.g_varchar2
,p_egd_attribute17 in varchar2 default hr_api.g_varchar2
,p_egd_attribute18 in varchar2 default hr_api.g_varchar2
,p_egd_attribute19 in varchar2 default hr_api.g_varchar2
,p_egd_attribute20 in varchar2 default hr_api.g_varchar2
,p_egd_attribute21 in varchar2 default hr_api.g_varchar2
,p_egd_attribute22 in varchar2 default hr_api.g_varchar2
,p_egd_attribute23 in varchar2 default hr_api.g_varchar2
,p_egd_attribute24 in varchar2 default hr_api.g_varchar2
,p_egd_attribute25 in varchar2 default hr_api.g_varchar2
,p_egd_attribute26 in varchar2 default hr_api.g_varchar2
,p_egd_attribute27 in varchar2 default hr_api.g_varchar2
,p_egd_attribute28 in varchar2 default hr_api.g_varchar2
,p_egd_attribute29 in varchar2 default hr_api.g_varchar2
,p_egd_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
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_ELIG_DPNT';
savepoint update_ELIG_DPNT;
ben_ELIG_DPNT_bk2.update_ELIG_DPNT_b
(
p_elig_dpnt_id => p_elig_dpnt_id
,p_create_dt => p_create_dt
,p_elig_strt_dt => p_elig_strt_dt
,p_elig_thru_dt => p_elig_thru_dt
,p_ovrdn_flag => p_ovrdn_flag
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_dpnt_inelig_flag => p_dpnt_inelig_flag
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_per_in_ler_id => p_per_in_ler_id
,p_elig_per_id => p_elig_per_id
,p_elig_per_opt_id => p_elig_per_opt_id
,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_dpnt_person_id => p_dpnt_person_id
,p_business_group_id => p_business_group_id
,p_egd_attribute_category => p_egd_attribute_category
,p_egd_attribute1 => p_egd_attribute1
,p_egd_attribute2 => p_egd_attribute2
,p_egd_attribute3 => p_egd_attribute3
,p_egd_attribute4 => p_egd_attribute4
,p_egd_attribute5 => p_egd_attribute5
,p_egd_attribute6 => p_egd_attribute6
,p_egd_attribute7 => p_egd_attribute7
,p_egd_attribute8 => p_egd_attribute8
,p_egd_attribute9 => p_egd_attribute9
,p_egd_attribute10 => p_egd_attribute10
,p_egd_attribute11 => p_egd_attribute11
,p_egd_attribute12 => p_egd_attribute12
,p_egd_attribute13 => p_egd_attribute13
,p_egd_attribute14 => p_egd_attribute14
,p_egd_attribute15 => p_egd_attribute15
,p_egd_attribute16 => p_egd_attribute16
,p_egd_attribute17 => p_egd_attribute17
,p_egd_attribute18 => p_egd_attribute18
,p_egd_attribute19 => p_egd_attribute19
,p_egd_attribute20 => p_egd_attribute20
,p_egd_attribute21 => p_egd_attribute21
,p_egd_attribute22 => p_egd_attribute22
,p_egd_attribute23 => p_egd_attribute23
,p_egd_attribute24 => p_egd_attribute24
,p_egd_attribute25 => p_egd_attribute25
,p_egd_attribute26 => p_egd_attribute26
,p_egd_attribute27 => p_egd_attribute27
,p_egd_attribute28 => p_egd_attribute28
,p_egd_attribute29 => p_egd_attribute29
,p_egd_attribute30 => p_egd_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_module_name => 'UPDATE_ELIG_DPNT'
,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)
);
ben_ELIG_DPNT_bk2.update_ELIG_DPNT_a
(
p_elig_dpnt_id => p_elig_dpnt_id
,p_create_dt => p_create_dt
,p_elig_strt_dt => p_elig_strt_dt
,p_elig_thru_dt => p_elig_thru_dt
,p_ovrdn_flag => p_ovrdn_flag
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_inelg_rsn_cd => p_inelg_rsn_cd
,p_dpnt_inelig_flag => p_dpnt_inelig_flag
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_per_in_ler_id => p_per_in_ler_id
,p_elig_per_id => p_elig_per_id
,p_elig_per_opt_id => p_elig_per_opt_id
,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_dpnt_person_id => p_dpnt_person_id
,p_business_group_id => p_business_group_id
,p_egd_attribute_category => p_egd_attribute_category
,p_egd_attribute1 => p_egd_attribute1
,p_egd_attribute2 => p_egd_attribute2
,p_egd_attribute3 => p_egd_attribute3
,p_egd_attribute4 => p_egd_attribute4
,p_egd_attribute5 => p_egd_attribute5
,p_egd_attribute6 => p_egd_attribute6
,p_egd_attribute7 => p_egd_attribute7
,p_egd_attribute8 => p_egd_attribute8
,p_egd_attribute9 => p_egd_attribute9
,p_egd_attribute10 => p_egd_attribute10
,p_egd_attribute11 => p_egd_attribute11
,p_egd_attribute12 => p_egd_attribute12
,p_egd_attribute13 => p_egd_attribute13
,p_egd_attribute14 => p_egd_attribute14
,p_egd_attribute15 => p_egd_attribute15
,p_egd_attribute16 => p_egd_attribute16
,p_egd_attribute17 => p_egd_attribute17
,p_egd_attribute18 => p_egd_attribute18
,p_egd_attribute19 => p_egd_attribute19
,p_egd_attribute20 => p_egd_attribute20
,p_egd_attribute21 => p_egd_attribute21
,p_egd_attribute22 => p_egd_attribute22
,p_egd_attribute23 => p_egd_attribute23
,p_egd_attribute24 => p_egd_attribute24
,p_egd_attribute25 => p_egd_attribute25
,p_egd_attribute26 => p_egd_attribute26
,p_egd_attribute27 => p_egd_attribute27
,p_egd_attribute28 => p_egd_attribute28
,p_egd_attribute29 => p_egd_attribute29
,p_egd_attribute30 => p_egd_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_module_name => 'UPDATE_ELIG_DPNT'
,p_hook_type => 'AP'
);
ROLLBACK TO update_ELIG_DPNT;
ROLLBACK TO update_ELIG_DPNT;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end update_ELIG_DPNT;
procedure update_perf_ELIG_DPNT
(
p_validate in boolean default false
,p_elig_dpnt_id in number
,p_create_dt in date default hr_api.g_date
,p_elig_strt_dt in date default hr_api.g_date
,p_elig_thru_dt in date default hr_api.g_date
,p_ovrdn_flag in varchar2 default hr_api.g_varchar2
,p_ovrdn_thru_dt in date default hr_api.g_date
,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
,p_dpnt_inelig_flag in varchar2 default hr_api.g_varchar2
,p_elig_per_elctbl_chc_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_elig_per_id in number default hr_api.g_number
,p_elig_per_opt_id in number default hr_api.g_number
,p_elig_cvrd_dpnt_id in number default hr_api.g_number
,p_dpnt_person_id in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_egd_attribute_category in varchar2 default hr_api.g_varchar2
,p_egd_attribute1 in varchar2 default hr_api.g_varchar2
,p_egd_attribute2 in varchar2 default hr_api.g_varchar2
,p_egd_attribute3 in varchar2 default hr_api.g_varchar2
,p_egd_attribute4 in varchar2 default hr_api.g_varchar2
,p_egd_attribute5 in varchar2 default hr_api.g_varchar2
,p_egd_attribute6 in varchar2 default hr_api.g_varchar2
,p_egd_attribute7 in varchar2 default hr_api.g_varchar2
,p_egd_attribute8 in varchar2 default hr_api.g_varchar2
,p_egd_attribute9 in varchar2 default hr_api.g_varchar2
,p_egd_attribute10 in varchar2 default hr_api.g_varchar2
,p_egd_attribute11 in varchar2 default hr_api.g_varchar2
,p_egd_attribute12 in varchar2 default hr_api.g_varchar2
,p_egd_attribute13 in varchar2 default hr_api.g_varchar2
,p_egd_attribute14 in varchar2 default hr_api.g_varchar2
,p_egd_attribute15 in varchar2 default hr_api.g_varchar2
,p_egd_attribute16 in varchar2 default hr_api.g_varchar2
,p_egd_attribute17 in varchar2 default hr_api.g_varchar2
,p_egd_attribute18 in varchar2 default hr_api.g_varchar2
,p_egd_attribute19 in varchar2 default hr_api.g_varchar2
,p_egd_attribute20 in varchar2 default hr_api.g_varchar2
,p_egd_attribute21 in varchar2 default hr_api.g_varchar2
,p_egd_attribute22 in varchar2 default hr_api.g_varchar2
,p_egd_attribute23 in varchar2 default hr_api.g_varchar2
,p_egd_attribute24 in varchar2 default hr_api.g_varchar2
,p_egd_attribute25 in varchar2 default hr_api.g_varchar2
,p_egd_attribute26 in varchar2 default hr_api.g_varchar2
,p_egd_attribute27 in varchar2 default hr_api.g_varchar2
,p_egd_attribute28 in varchar2 default hr_api.g_varchar2
,p_egd_attribute29 in varchar2 default hr_api.g_varchar2
,p_egd_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
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_perf_ELIG_DPNT';
savepoint update_perf_ELIG_DPNT;
p_program_update_date,
p_object_version_number
);
update ben_elig_dpnt
set
elig_dpnt_id = l_rec.elig_dpnt_id,
create_dt = l_rec.create_dt,
elig_strt_dt = l_rec.elig_strt_dt,
elig_thru_dt = l_rec.elig_thru_dt,
ovrdn_flag = l_rec.ovrdn_flag,
ovrdn_thru_dt = l_rec.ovrdn_thru_dt,
inelg_rsn_cd = l_rec.inelg_rsn_cd,
dpnt_inelig_flag = l_rec.dpnt_inelig_flag,
elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id,
per_in_ler_id = l_rec.per_in_ler_id,
elig_per_id = l_rec.elig_per_id,
elig_per_opt_id = l_rec.elig_per_opt_id,
elig_cvrd_dpnt_id = l_rec.elig_cvrd_dpnt_id,
dpnt_person_id = l_rec.dpnt_person_id,
business_group_id = l_rec.business_group_id,
egd_attribute_category = l_rec.egd_attribute_category,
egd_attribute1 = l_rec.egd_attribute1,
egd_attribute2 = l_rec.egd_attribute2,
egd_attribute3 = l_rec.egd_attribute3,
egd_attribute4 = l_rec.egd_attribute4,
egd_attribute5 = l_rec.egd_attribute5,
egd_attribute6 = l_rec.egd_attribute6,
egd_attribute7 = l_rec.egd_attribute7,
egd_attribute8 = l_rec.egd_attribute8,
egd_attribute9 = l_rec.egd_attribute9,
egd_attribute10 = l_rec.egd_attribute10,
egd_attribute11 = l_rec.egd_attribute11,
egd_attribute12 = l_rec.egd_attribute12,
egd_attribute13 = l_rec.egd_attribute13,
egd_attribute14 = l_rec.egd_attribute14,
egd_attribute15 = l_rec.egd_attribute15,
egd_attribute16 = l_rec.egd_attribute16,
egd_attribute17 = l_rec.egd_attribute17,
egd_attribute18 = l_rec.egd_attribute18,
egd_attribute19 = l_rec.egd_attribute19,
egd_attribute20 = l_rec.egd_attribute20,
egd_attribute21 = l_rec.egd_attribute21,
egd_attribute22 = l_rec.egd_attribute22,
egd_attribute23 = l_rec.egd_attribute23,
egd_attribute24 = l_rec.egd_attribute24,
egd_attribute25 = l_rec.egd_attribute25,
egd_attribute26 = l_rec.egd_attribute26,
egd_attribute27 = l_rec.egd_attribute27,
egd_attribute28 = l_rec.egd_attribute28,
egd_attribute29 = l_rec.egd_attribute29,
egd_attribute30 = l_rec.egd_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_dpnt_id = l_rec.elig_dpnt_id;
ROLLBACK TO update_perf_ELIG_DPNT;
ROLLBACK TO update_perf_ELIG_DPNT;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end update_perf_ELIG_DPNT;
procedure delete_ELIG_DPNT
(p_validate in boolean default false
,p_elig_dpnt_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_ELIG_DPNT';
savepoint delete_ELIG_DPNT;
ben_ELIG_DPNT_bk3.delete_ELIG_DPNT_b
(
p_elig_dpnt_id => p_elig_dpnt_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_ELIG_DPNT'
,p_hook_type => 'BP'
);
ben_ELIG_DPNT_bk3.delete_ELIG_DPNT_a
(
p_elig_dpnt_id => p_elig_dpnt_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_ELIG_DPNT'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_ELIG_DPNT;
ROLLBACK TO delete_ELIG_DPNT;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end delete_ELIG_DPNT;
SELECT sspndd.prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f sspndd, ben_prtt_enrt_rslt_f inter
WHERE inter.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
AND sspndd.rplcs_sspndd_rslt_id = inter.prtt_enrt_rslt_id
AND sspndd.pl_id = inter.pl_id
AND NVL (sspndd.pgm_id, -1) = NVL (inter.pgm_id, -1)
AND sspndd.pl_id = inter.pl_id
AND NVL (sspndd.oipl_id, -1) = NVL (inter.oipl_id, -1)
AND sspndd.prtt_enrt_rslt_stat_cd IS NULL
AND inter.prtt_enrt_rslt_stat_cd IS NULL
AND inter.enrt_cvg_thru_dt = hr_api.g_eot
AND sspndd.enrt_cvg_thru_dt = hr_api.g_eot
and cv_effective_date between sspndd.effective_start_date
and sspndd.effective_end_date
and cv_effective_date between inter.effective_start_date
and inter.effective_end_date;
select egd.elig_dpnt_id,
egd.elig_cvrd_dpnt_id,
egd.elig_per_elctbl_chc_id,
egd.dpnt_person_id,
egd.per_in_ler_id,
epe.prtt_enrt_rslt_id,
egd.object_version_number
from ben_elig_dpnt egd,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where egd.elig_dpnt_id = p_elig_dpnt_id
and egd.business_group_id = p_business_group_id
and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and egd.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
select pdp.cvg_strt_dt,
pdp.cvg_thru_dt,
pdp.object_version_number,
pdp.effective_end_date, -------Bug 9051021
pdp.effective_start_date
from ben_elig_cvrd_dpnt_f pdp
where pdp.elig_cvrd_dpnt_id = l_egd_rec.elig_cvrd_dpnt_id
and pdp.prtt_enrt_rslt_id = l_egd_rec.prtt_enrt_rslt_id
and pdp.business_group_id = p_business_group_id
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_per_in_ler_id => l_egd_rec.per_in_ler_id,
p_cvg_thru_dt => l_cvg_thru_dt,
p_object_version_number => l_pdp_rec.object_version_number,
p_datetrack_mode => l_pdp_datetrack_mode,
p_multi_row_actn => p_multi_row_actn,
p_pdp_attribute_category => p_pdp_attribute_category,
p_pdp_attribute1 => p_pdp_attribute1,
p_pdp_attribute2 => p_pdp_attribute2,
p_pdp_attribute3 => p_pdp_attribute3,
p_pdp_attribute4 => p_pdp_attribute4,
p_pdp_attribute5 => p_pdp_attribute5,
p_pdp_attribute6 => p_pdp_attribute6,
p_pdp_attribute7 => p_pdp_attribute7,
p_pdp_attribute8 => p_pdp_attribute8,
p_pdp_attribute9 => p_pdp_attribute9,
p_pdp_attribute10 => p_pdp_attribute10,
p_pdp_attribute11 => p_pdp_attribute11,
p_pdp_attribute12 => p_pdp_attribute12,
p_pdp_attribute13 => p_pdp_attribute13,
p_pdp_attribute14 => p_pdp_attribute14,
p_pdp_attribute15 => p_pdp_attribute15,
p_pdp_attribute16 => p_pdp_attribute16,
p_pdp_attribute17 => p_pdp_attribute17,
p_pdp_attribute18 => p_pdp_attribute18,
p_pdp_attribute19 => p_pdp_attribute19,
p_pdp_attribute20 => p_pdp_attribute20,
p_pdp_attribute21 => p_pdp_attribute21,
p_pdp_attribute22 => p_pdp_attribute22,
p_pdp_attribute23 => p_pdp_attribute23,
p_pdp_attribute24 => p_pdp_attribute24,
p_pdp_attribute25 => p_pdp_attribute25,
p_pdp_attribute26 => p_pdp_attribute26,
p_pdp_attribute27 => p_pdp_attribute27,
p_pdp_attribute28 => p_pdp_attribute28,
p_pdp_attribute29 => p_pdp_attribute29,
p_pdp_attribute30 => p_pdp_attribute30,
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_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pdp_rec.object_version_number,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap,
p_multi_row_actn => true,
p_called_from => 'beegdapi' );
ben_elig_dpnt_api.update_elig_dpnt(
p_elig_dpnt_id => p_elig_dpnt_id,
p_elig_cvrd_dpnt_id => null,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_object_version_number => l_egd_rec.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);
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_per_in_ler_id => l_egd_rec.per_in_ler_id,
p_cvg_strt_dt => p_cvg_strt_dt, -- 3453213
p_cvg_thru_dt => hr_api.g_eot,
p_object_version_number => l_pdp_rec.object_version_number,
p_datetrack_mode => l_pdp_datetrack_mode,
p_multi_row_actn => p_multi_row_actn,
p_pdp_attribute_category => p_pdp_attribute_category,
p_pdp_attribute1 => p_pdp_attribute1,
p_pdp_attribute2 => p_pdp_attribute2,
p_pdp_attribute3 => p_pdp_attribute3,
p_pdp_attribute4 => p_pdp_attribute4,
p_pdp_attribute5 => p_pdp_attribute5,
p_pdp_attribute6 => p_pdp_attribute6,
p_pdp_attribute7 => p_pdp_attribute7,
p_pdp_attribute8 => p_pdp_attribute8,
p_pdp_attribute9 => p_pdp_attribute9,
p_pdp_attribute10 => p_pdp_attribute10,
p_pdp_attribute11 => p_pdp_attribute11,
p_pdp_attribute12 => p_pdp_attribute12,
p_pdp_attribute13 => p_pdp_attribute13,
p_pdp_attribute14 => p_pdp_attribute14,
p_pdp_attribute15 => p_pdp_attribute15,
p_pdp_attribute16 => p_pdp_attribute16,
p_pdp_attribute17 => p_pdp_attribute17,
p_pdp_attribute18 => p_pdp_attribute18,
p_pdp_attribute19 => p_pdp_attribute19,
p_pdp_attribute20 => p_pdp_attribute20,
p_pdp_attribute21 => p_pdp_attribute21,
p_pdp_attribute22 => p_pdp_attribute22,
p_pdp_attribute23 => p_pdp_attribute23,
p_pdp_attribute24 => p_pdp_attribute24,
p_pdp_attribute25 => p_pdp_attribute25,
p_pdp_attribute26 => p_pdp_attribute26,
p_pdp_attribute27 => p_pdp_attribute27,
p_pdp_attribute28 => p_pdp_attribute28,
p_pdp_attribute29 => p_pdp_attribute29,
p_pdp_attribute30 => p_pdp_attribute30,
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_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate);
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 => l_egd_rec.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);
/* Inserted for nocopy changes */
p_elig_cvrd_dpnt_id := null;
/* Inserted for nocopy changes */
p_elig_cvrd_dpnt_id := null;
/* Inserted for nocopy changes */
p_elig_cvrd_dpnt_id := null;
DELETE ben_online_warnings
WHERE session_id = p_person_id;
INSERT INTO ben_online_warnings(session_id, message_text)
VALUES (p_person_id, p_crt_ord_warning);
SELECT pen.person_id,
pln.pl_id,
typ.pl_typ_id,
(typ.name || ' - ' || pln.name) comp_obj_name
FROM ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_pl_typ_f typ,
ben_elig_per_elctbl_chc chc
WHERE chc.per_in_ler_id = p_per_in_ler_id
AND chc.pgm_id = p_pgm_id
AND chc.alws_dpnt_dsgn_flag = 'N'
AND pen.prtt_enrt_rslt_id = chc.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND p_effective_date between pen.effective_start_date
and pen.effective_end_date
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.pl_id = pln.pl_id
AND pln.pl_stat_cd = 'A'
AND pln.svgs_pl_flag <> 'Y'
AND pln.alws_qmcso_flag = 'Y'
AND p_effective_date between pln.effective_start_date
and pln.effective_end_date
AND pln.pl_typ_id = typ.pl_typ_id
AND p_effective_date between typ.effective_start_date
and typ.effective_end_date
AND EXISTS(SELECT null
FROM ben_crt_ordr crt,
ben_per_in_ler pil
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = pen.person_id
AND (crt.pl_id = pln.pl_id or crt.pl_typ_id = typ.pl_typ_id)
AND crt.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
AND greatest(pen.enrt_cvg_strt_dt, pil.lf_evt_ocrd_dt)
between greatest(nvl(crt.apls_perd_strtg_dt, p_effective_date)
,nvl(crt.detd_qlfd_ordr_dt, crt.apls_perd_strtg_dt)
)
and nvl(crt.apls_perd_endg_dt, pen.enrt_cvg_thru_dt)
)
AND NOT EXISTS
(SELECT 1
FROM ben_prtt_enrt_rslt_f pen2
where pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
and pen2.sspndd_flag = 'Y'
and pen2.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen2.effective_start_date and pen2.effective_end_date
and pen2.enrt_cvg_thru_dt = hr_api.g_eot);
INSERT INTO ben_online_warnings(session_id, message_text)
VALUES (p_person_id, fnd_message.get);
select egd.*
from ben_elig_dpnt egd
where egd.elig_dpnt_id = p_elig_dpnt_id;
select egd.*
from ben_elig_dpnt egd,
ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and pdp.per_in_ler_id = egd.per_in_ler_id
and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
and pdp.dpnt_person_id = egd.dpnt_person_id
and pdp.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
pdp.cvg_strt_dt and pdp.cvg_thru_dt
--- Fido fix medged by tilak # 2931919
and pdp.cvg_thru_dt <= pdp.effective_end_date ;
select egd.*
from ben_elig_dpnt egd,
ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pdp.dpnt_person_id = p_dpnt_person_id
and pdp.per_in_ler_id = egd.per_in_ler_id
and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
and pdp.dpnt_person_id = egd.dpnt_person_id
and pdp.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and p_effective_date between
pdp.cvg_strt_dt and pdp.cvg_thru_dt;
select null
from ben_elig_dpnt egd
where egd.dpnt_person_id = p_dpnt_person_id;
select egd.elig_dpnt_id
from ben_elig_dpnt egd,
ben_elig_per_opt_f epo,
ben_elig_per_f pep,
ben_per_in_ler pil,
ben_oipl_f oipl
where egd.dpnt_person_id = c_dpnt_person_id
and egd.elig_per_opt_id = epo.elig_per_opt_id
and egd.elig_per_id = epo.elig_per_id
and c_effective_date between
epo.effective_start_date and epo.effective_end_date
and epo.elig_per_id = pep.elig_per_id
and c_effective_date between
pep.effective_start_date and pep.effective_end_date
and oipl.pl_id = pep.pl_id
and c_effective_date between
oipl.effective_start_date and oipl.effective_end_date
and oipl.oipl_id = c_oipl_id
and oipl.opt_id = epo.opt_id
and nvl(pep.pgm_id, -1) = c_pgm_id
and egd.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
order by egd.create_dt desc;
select egd.elig_dpnt_id
from ben_elig_dpnt egd,
ben_per_in_ler pil,
ben_elig_per_f pep
where egd.dpnt_person_id = c_dpnt_person_id
and egd.elig_per_opt_id is null
and egd.elig_per_id = pep.elig_per_id
and pep.pl_id = c_pl_id
and nvl(pep.pgm_id, -1) = nvl(c_pgm_id, -1)
and egd.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and c_effective_date
between pep.effective_start_date and pep.effective_end_date
order by egd.create_dt desc;
select epo.elig_per_opt_id,
epo.elig_per_id
from ben_elig_per_opt_f epo,
ben_elig_per_f pep
where epo.per_in_ler_id = c_per_in_ler_id
and c_effective_date between
epo.effective_start_date and epo.effective_end_date
and epo.elig_per_id = pep.elig_per_id
and c_effective_date between
pep.effective_start_date and pep.effective_end_date
and pep.pl_id = c_pl_id
and epo.opt_id = c_opt_id
and nvl(pep.pgm_id, -1) = c_pgm_id;
select egd.elig_dpnt_id
from ben_elig_dpnt egd
where egd.elig_per_opt_id = c_elig_per_opt_id
and egd.elig_per_id = c_elig_per_id
and egd.dpnt_person_id = c_dpnt_person_id
and egd.per_in_ler_id = c_pil_id
order by egd.create_dt desc;
select egd.elig_dpnt_id
from ben_elig_dpnt egd
where egd.elig_per_id = c_elig_per_id
and egd.elig_per_opt_id is null
and egd.dpnt_person_id = c_dpnt_person_id
and egd.per_in_ler_id = c_pil_id;
select epo.elig_per_id,
epo.elig_per_opt_id
from ben_elig_per_opt_f epo,
ben_elig_per_f pep,
ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and pep.pl_id = oipl.pl_id
and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
and pep.person_id = p_person_id
and pep.business_group_id = p_business_group_id
and oipl.opt_id = epo.opt_id
and epo.elig_per_id = pep.elig_per_id
and pep.elig_flag = 'Y'
and epo.elig_flag = 'Y'
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date
and p_effective_date between
pep.effective_start_date and pep.effective_end_date
and p_effective_date between
epo.effective_start_date and epo.effective_end_date;
select pep.elig_per_id
from ben_elig_per_f pep
where pep.pl_id = p_pl_id
and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
and pep.person_id = p_person_id
and pep.business_group_id = p_business_group_id
and pep.elig_flag = 'Y'
and p_effective_date between
pep.effective_start_date and pep.effective_end_date;
is SELECT crt.CRT_ORDR_TYP_CD
FROM ben_crt_ordr crt
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = p_person_id
AND crt.pl_id = p_pl_id or crt.pl_typ_id = p_pl_typ_id;
select 'Y' from ben_elig_cvrd_dpnt_f ecd,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where ecd.dpnt_person_id = p_dpnt_person_id
and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and ecd.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
and ecd.cvg_thru_dt >= ecd.cvg_strt_dt
and ecd.cvg_strt_dt between c_rltp_start_date and c_rltp_end_date
and pen.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and ecd.business_group_id = p_business_group_id;
select * from per_contact_relationships
where contact_person_id = p_dpnt_person_id
and contact_relationship_id = p_dpnt_rltp_id;