[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_ovrdn_flag in varchar2 default 'N'
,p_per_in_ler_id in number default null
,p_ovrdn_thru_dt in date default null
,p_effective_date in date
,p_multi_row_actn in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
SELECT pen.pl_id,
pen.oipl_id,
pen.person_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd is null
AND p_effective_date between pen.effective_start_date and pen.effective_end_date;
,p_program_update_date => p_program_update_date
,p_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
);
ben_cobra_requirements.update_dpnt_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_dpnt_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_validate => p_validate
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
);
procedure update_ELIG_CVRD_DPNT
(p_validate in boolean default false
,p_elig_cvrd_dpnt_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_prtt_enrt_rslt_id in number default hr_api.g_number
,p_dpnt_person_id in number default hr_api.g_number
,p_cvg_strt_dt in date default hr_api.g_date
,p_cvg_thru_dt in date default hr_api.g_date
,p_cvg_pndg_flag in varchar2 default hr_api.g_varchar2
,p_pdp_attribute_category in varchar2 default hr_api.g_varchar2
,p_pdp_attribute1 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute2 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute3 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute4 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute5 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute6 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute7 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute8 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute9 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute10 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute11 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute12 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute13 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute14 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute15 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute16 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute17 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute18 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute19 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute20 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute21 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute22 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute23 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute24 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute25 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute26 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute27 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute28 in varchar2 default hr_api.g_varchar2
,p_pdp_attribute29 in varchar2 default hr_api.g_varchar2
,p_pdp_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_ovrdn_flag in varchar2 default hr_api.g_varchar2
,p_per_in_ler_id in number default hr_api.g_number
,p_ovrdn_thru_dt in date default hr_api.g_date
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_actn in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_ELIG_CVRD_DPNT';
select bcc.cvrd_dpnt_ctfn_prvdd_id,
bcc.object_version_number
from ben_cvrd_dpnt_ctfn_prvdd_f bcc ,
ben_prtt_enrt_actn_f bpe
where bcc.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and bcc.business_group_id = p_business_group_id
and bcc.prtt_enrt_actn_id = bpe.prtt_enrt_actn_id
and bpe.per_in_ler_id = p_per_in_ler_id
and bpe.business_group_id = p_business_group_id
and p_effective_date between bcc.effective_start_date
and bcc.effective_end_date
and p_effective_date between bpe.effective_start_date
and bpe.effective_end_date
and (x_datetrack_mode=hr_api.g_delete
and p_effective_date<>bcc.effective_end_date
or x_datetrack_mode=hr_api.g_zap)
;
SELECT pdp.cvg_strt_dt,
pdp.cvg_thru_dt,
pdp.prtt_enrt_rslt_id,
pdp.per_in_ler_id,
pdp.dpnt_person_id,
pen.pl_id,
pen.oipl_id,
pen.person_id
FROM ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp
WHERE pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
AND pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pdp.effective_start_date and pdp.effective_end_date
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
AND p_effective_date between pen.effective_start_date and pen.effective_end_date;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where business_group_id = p_business_group_id
and elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
and c_pcp_effective_date between effective_start_date --3631067: Changed p_effective_date to c_pcp_effective_date
and effective_end_date
;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where pcp.business_group_id = p_business_group_id
and pcp.elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
and c_pcp_effective_date < pcp.effective_start_date ----3631067: Changed p_effective_date to c_pcp_effective_date
and NVL(pcp.effective_end_date, hr_api.g_eot) = hr_api.g_eot
;
savepoint update_ELIG_CVRD_DPNT;
(p_action => 'DELETE',
p_pl_id => l_previous_values.pl_id,
p_oipl_id => l_previous_values.oipl_id,
p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_cvg_end_dt => p_cvg_thru_dt,
p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
-- bug 1540458. line below was just prev per_in_ler_id
p_per_in_ler_id => nvl(p_per_in_ler_id,
l_previous_values.per_in_ler_id),
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_person_id => l_previous_values.person_id,
p_dpnt_person_id => l_previous_values.dpnt_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
ben_ELIG_CVRD_DPNT_bk2.update_ELIG_CVRD_DPNT_b
(
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_dpnt_person_id => p_dpnt_person_id
,p_cvg_strt_dt => p_cvg_strt_dt
,p_cvg_thru_dt => p_cvg_thru_dt
,p_cvg_pndg_flag => p_cvg_pndg_flag
,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 => 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_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_ELIG_CVRD_DPNT'
,p_hook_type => 'BP'
);
if p_datetrack_mode = 'UPDATE' then
l2_datetrack_mode := 'DELETE';
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l2_effective_start_date
,p_effective_end_date => l2_effective_end_date
,p_object_version_number => l2_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_datetrack_mode => l2_datetrack_mode
);
hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_date --3631067: Changed p_effective_date to l_pcp_effective_date
,P_DATETRACK_MODE => l2_datetrack_mode
,p_called_from => 'delete_enrollment'
);
hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp_future.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp_future.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp_future.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_start_date
,P_DATETRACK_MODE => hr_api.g_zap
,p_called_from => 'delete_enrollment'
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
ben_cobra_requirements.update_dpnt_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => l_previous_values.dpnt_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_validate => p_validate
);
ben_ELIG_CVRD_DPNT_bk2.update_ELIG_CVRD_DPNT_a
(
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_dpnt_person_id => p_dpnt_person_id
,p_cvg_strt_dt => p_cvg_strt_dt
,p_cvg_thru_dt => p_cvg_thru_dt
,p_cvg_pndg_flag => p_cvg_pndg_flag
,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 => 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_ovrdn_flag => p_ovrdn_flag
,p_per_in_ler_id => p_per_in_ler_id
,p_ovrdn_thru_dt => p_ovrdn_thru_dt
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_ELIG_CVRD_DPNT'
,p_hook_type => 'AP'
);
ROLLBACK TO update_ELIG_CVRD_DPNT;
ROLLBACK TO update_ELIG_CVRD_DPNT;
end update_ELIG_CVRD_DPNT;
select dpnt_person_id,
cvg_strt_dt,
business_group_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date;
select null
from ben_elig_cvrd_dpnt_f a,
ben_per_in_ler pil
where a.dpnt_person_id = l_dpnt_person_id
and a.elig_cvrd_dpnt_id <> p_elig_cvrd_dpnt_id
and a.cvg_strt_dt is not null
and a.cvg_thru_dt = hr_api.g_eot
and l_end_dt between a.cvg_strt_dt
and nvl(a.cvg_thru_dt, hr_api.g_date)
and p_effective_date between a.effective_start_date
and a.effective_end_date
and a.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select a.person_id
,a.person_type_usage_id
,a.object_version_number
,a.person_type_id
,a.effective_start_date
,a.effective_end_date
from per_person_type_usages_f a,
per_person_types b
where a.person_id = l_dpnt_person_id
and a.person_type_id = b.person_type_id
and b.system_person_type = 'DPNT'
and b.business_group_id = l_business_group_id
and l_end_dt between a.effective_start_date
and a.effective_end_date;
cursor delete_c is
select a.person_id
,a.person_type_usage_id
,a.object_version_number
,a.person_type_id
,a.effective_start_date
,a.effective_end_date
from per_person_type_usages_f a,
per_person_types b
where a.person_id = l_dpnt_person_id
and a.person_type_id = b.person_type_id
and b.system_person_type = 'DPNT'
and b.business_group_id = l_business_group_id
and a.effective_start_date > l_end_dt;
-- delete all future ptu's of type 'DPNT', as this one extends to end-of-time
l_datetrack_mode := hr_api.g_zap ;
for del_rec in delete_c loop
--- due to nocopy the p_effective date is nulifile because
--- the same variable sent to p_effective_start_date
--- this is fixed by sending different variabale as p_effective_date
l_effective_date := del_rec.effective_start_date ;
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => del_rec.person_type_usage_id
,p_effective_date => l_effective_date -- p_effective_date # 2744060
,p_datetrack_mode => l_datetrack_mode
,p_object_version_number => del_rec.object_version_number
,p_effective_start_date => del_rec.effective_start_date
,p_effective_end_date => del_rec.effective_end_date
);
hr_utility.set_location('Delete Person Type Usage Id : '||del_rec.person_type_usage_id , 30);
-- update table directly as this is not supported by row handler.
for cur_rec in usage_c loop
l_datetrack_mode := hr_api.g_correction;
update per_person_type_usages_f ptu
set effective_end_date = l_effective_end_date
where ptu.person_type_usage_id = cur_rec.person_type_usage_id
and ptu.effective_start_date = cur_rec.effective_start_date;
select dpnt_person_id,
cvg_strt_dt,
business_group_id,
prtt_enrt_rslt_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date;
select pea.prtt_enrt_actn_id,
pea.effective_start_date,
pea.effective_end_date,
pea.object_version_number,
pen.object_version_number rslt_object_version_number
from ben_prtt_enrt_actn_f pea,
ben_prtt_enrt_rslt_f pen
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
and p_effective_date between pen.effective_start_date
and pen.effective_end_date;
ben_PRTT_ENRT_ACTN_api.delete_PRTT_ENRT_ACTN
(p_validate => FALSE,
p_effective_date => l_effective_date,
p_business_group_id => l_business_group_id,
p_datetrack_mode => hr_api.g_future_change,
p_object_version_number => l_usage_pea.object_version_number,
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_rslt_object_version_number => l_usage_pea.rslt_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_prtt_enrt_actn_id => l_usage_pea.prtt_enrt_actn_id );
procedure delete_ELIG_CVRD_DPNT
(p_validate in boolean default false
,p_elig_cvrd_dpnt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_business_group_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_actn in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_ELIG_CVRD_DPNT';
select cvg_strt_dt,
cvg_thru_dt,
dpnt_person_id,
prtt_enrt_rslt_id,
per_in_ler_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select effective_end_date
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = cp_prtt_enrt_actn_id
and business_group_id + 0 = p_business_group_id
and cp_effective_date between effective_start_date and effective_end_date
;
select cvrd_dpnt_ctfn_prvdd_id,
prtt_enrt_actn_id,
object_version_number,
effective_start_date,
effective_end_date
from ben_cvrd_dpnt_ctfn_prvdd_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and l_child_effective_date between effective_start_date
and effective_end_date
order by cvrd_dpnt_ctfn_prvdd_id asc;
select prmry_care_prvdr_id,
object_version_number
from ben_prmry_care_prvdr_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
SELECT pen.pl_id,
pen.oipl_id,
pen.person_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pen.effective_start_date and pen.effective_end_date;
SELECT pea.object_version_number, pea.prtt_enrt_actn_id,
pea.prtt_enrt_rslt_id, pea.effective_end_date -- 5096675
FROM ben_prtt_enrt_actn_f pea
WHERE pea.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pea.effective_start_date
AND pea.effective_end_date
AND pea.actn_typ_id = l_dpnt_pcp_actn_typ_id
AND pea.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;
savepoint delete_ELIG_CVRD_DPNT;
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l2_effective_start_date
,p_effective_end_date => l2_effective_end_date
,p_object_version_number => l2_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_child_effective_date
,p_datetrack_mode => p_datetrack_mode
);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(p_validate => FALSE
,p_prmry_care_prvdr_id => pcp_rec.prmry_care_prvdr_id
,p_effective_start_date => l3_effective_start_date
,p_effective_end_date => l3_effective_end_date
,p_object_version_number => l3_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_called_from => 'delete_enrollment'
);
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_validate => FALSE,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_mode => p_datetrack_mode,
p_object_version_number => l3_object_version_number,
p_prtt_enrt_rslt_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_rslt_id,
p_rslt_object_version_number => l3_object_version_number,
p_post_rslt_flag => 'N',
p_unsuspend_enrt_flag => 'Y',
p_effective_start_date => l3_effective_start_date,
p_effective_end_date => l3_effective_end_date,
p_prtt_enrt_actn_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id
);
ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_b
(
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_ELIG_CVRD_DPNT'
,p_hook_type => 'BP'
);
(p_action => 'DELETE',
p_pl_id => l_chg_info.pl_id,
p_oipl_id => l_chg_info.oipl_id,
p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_cvg_end_dt => (l_previous_values.cvg_strt_dt-1),
p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
p_per_in_ler_id => l_previous_values.per_in_ler_id,
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_person_id => l_chg_info.person_id,
p_dpnt_person_id => l_previous_values.dpnt_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
if p_datetrack_mode = 'DELETE' then
l2_datetrack_mode := 'DELETE';
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l2_effective_start_date
,p_effective_end_date => l2_effective_end_date
,p_object_version_number => l2_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_child_effective_date
,p_datetrack_mode => p_datetrack_mode
);
ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_a
(
p_elig_cvrd_dpnt_id => p_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_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_ELIG_CVRD_DPNT'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_ELIG_CVRD_DPNT;
ROLLBACK TO delete_ELIG_CVRD_DPNT;
end delete_ELIG_CVRD_DPNT;
procedure delete_ELIG_CVRD_DPNT
(p_validate in boolean default false
,p_elig_cvrd_dpnt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_business_group_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_actn in boolean default TRUE
,p_called_from in varchar2
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_ELIG_CVRD_DPNT';
select cvg_strt_dt,
cvg_thru_dt,
dpnt_person_id,
prtt_enrt_rslt_id,
per_in_ler_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select effective_end_date
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = cp_prtt_enrt_actn_id
and business_group_id + 0 = p_business_group_id
and cp_effective_date between effective_start_date and effective_end_date
;
select cvrd_dpnt_ctfn_prvdd_id,
prtt_enrt_actn_id,
object_version_number,
effective_start_date,
effective_end_date
from ben_cvrd_dpnt_ctfn_prvdd_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and l_child_effective_date between effective_start_date
and effective_end_date
order by cvrd_dpnt_ctfn_prvdd_id asc;
select prmry_care_prvdr_id,
object_version_number
from ben_prmry_care_prvdr_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
SELECT pen.pl_id,
pen.oipl_id,
pen.person_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pen.effective_start_date and pen.effective_end_date;
SELECT pea.object_version_number, pea.prtt_enrt_actn_id,
pea.prtt_enrt_rslt_id, pea.effective_end_date -- 5096675
FROM ben_prtt_enrt_actn_f pea
WHERE pea.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pea.effective_start_date
AND pea.effective_end_date
AND pea.actn_typ_id = l_dpnt_pcp_actn_typ_id
AND pea.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;
savepoint delete_ELIG_CVRD_DPNT;
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l2_effective_start_date
,p_effective_end_date => l2_effective_end_date
,p_object_version_number => l2_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_child_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_called_from => p_called_from
);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(p_validate => FALSE
,p_prmry_care_prvdr_id => pcp_rec.prmry_care_prvdr_id
,p_effective_start_date => l3_effective_start_date
,p_effective_end_date => l3_effective_end_date
,p_object_version_number => l3_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_called_from => 'delete_enrollment'
);
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_validate => FALSE,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_mode => p_datetrack_mode,
p_object_version_number => l3_object_version_number,
p_prtt_enrt_rslt_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_rslt_id,
p_rslt_object_version_number => l3_object_version_number,
p_post_rslt_flag => 'N',
p_unsuspend_enrt_flag => 'Y',
p_effective_start_date => l3_effective_start_date,
p_effective_end_date => l3_effective_end_date,
p_prtt_enrt_actn_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id
);
ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_b
(
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_ELIG_CVRD_DPNT'
,p_hook_type => 'BP'
);
(p_action => 'DELETE',
p_pl_id => l_chg_info.pl_id,
p_oipl_id => l_chg_info.oipl_id,
p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_cvg_end_dt => (l_previous_values.cvg_strt_dt-1),
p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
p_per_in_ler_id => l_previous_values.per_in_ler_id,
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_person_id => l_chg_info.person_id,
p_dpnt_person_id => l_previous_values.dpnt_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
if p_datetrack_mode = 'DELETE' then
l2_datetrack_mode := 'DELETE';
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l2_effective_start_date
,p_effective_end_date => l2_effective_end_date
,p_object_version_number => l2_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_child_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_called_from => p_called_from
);
ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_a
(
p_elig_cvrd_dpnt_id => p_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_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_ELIG_CVRD_DPNT'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_ELIG_CVRD_DPNT;
ROLLBACK TO delete_ELIG_CVRD_DPNT;
end delete_ELIG_CVRD_DPNT;
select prtt_enrt_rslt_id,
business_group_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date;
select object_version_number,
sspndd_flag
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and business_group_id = l_business_group_id
and prtt_enrt_rslt_stat_cd is null
and p_effective_date between effective_start_date and effective_end_date
and p_effective_date < enrt_cvg_thru_dt ; -- 5173425: Need not determine dpnts for End-dated enrollments.
select dpnt_person_id,
cvg_strt_dt,
business_group_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date;
select min(per.effective_start_date)
from per_all_people_f per
where per.person_id = l_dpnt_person_id;
select person_type_id
from per_person_types
where system_person_type = 'DPNT'
and business_group_id = l_business_group_id;
select a.effective_start_date,
a.effective_end_date,
a.person_type_usage_id
from per_person_type_usages_f a
where a.person_id = l_dpnt_person_id
and a.person_type_id = l_person_type_id
-- and a.effective_start_date <= hr_api.g_date
and a.effective_end_date >= l_cvg_strt_dt
order by a.effective_start_date -- 5604361
;
update per_person_type_usages_f
set effective_end_date = hr_api.g_eot
where person_type_usage_id = l_person_type_usage_id;
update per_person_type_usages_f
set effective_start_date = l_cvg_strt_dt
where person_type_usage_id = l_person_type_usage_id;
update per_person_type_usages_f
set effective_start_date = l_cvg_strt_dt,
effective_end_date = hr_api.g_eot
where person_type_usage_id = l_person_type_usage_id;
select dpnt_person_id,
cvg_strt_dt,
business_group_id
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date;
select null
from ben_elig_cvrd_dpnt_f a,
ben_per_in_ler pil
where a.dpnt_person_id = l_dpnt_person_id
and a.elig_cvrd_dpnt_id <> p_elig_cvrd_dpnt_id
and a.cvg_strt_dt is not null
and a.cvg_thru_dt = hr_api.g_eot
-- and a.cvrd_flag = 'Y'
and l_end_dt + 1 between a.cvg_strt_dt -- Bug 5451726
and nvl(a.cvg_thru_dt, hr_api.g_date)
and p_effective_date between a.effective_start_date
and a.effective_end_date
and a.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select a.person_type_usage_id,
a.object_version_number,
a.effective_start_date,
a.effective_end_date
from per_person_type_usages_f a,
per_person_types b
where a.person_id = l_dpnt_person_id
and a.person_type_id = b.person_type_id
and b.system_person_type = 'DPNT'
and b.business_group_id = l_business_group_id
and l_end_dt between a.effective_start_date
and a.effective_end_date;
l_datetrack_mode := 'DELETE';
elsif p_datetrack_mode=hr_api.g_delete and
l_eff_end_date=l_end_dt then
close usage_c;
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => l_person_type_usage_id
,p_effective_date => l_end_dt
,p_datetrack_mode => l_datetrack_mode
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select r.person_id
,egd.dpnt_person_id
,r.pl_id
,r.oipl_id
,o.opt_id
,egd.cvg_strt_dt
,egd.cvg_thru_dt
from ben_prtt_enrt_rslt_f r,
ben_elig_cvrd_dpnt_f egd,
ben_oipl_f o ,
ben_per_in_ler pil
where r.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and r.prtt_enrt_rslt_id = egd.prtt_enrt_rslt_id
and r.business_group_id + 0 = p_business_group_id
and r.per_in_ler_id = egd.per_in_ler_id
and r.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and r.prtt_enrt_rslt_stat_cd is null
and p_effective_date between r.effective_start_date
and r.effective_end_date
and o.oipl_id(+) = r.oipl_id
and o.business_group_id(+)= p_business_group_id
and p_effective_date between o.effective_start_date(+)
and o.effective_end_date(+)
and egd.cvg_strt_dt is not null
and egd.cvg_thru_dt = hr_api.g_eot
-- bug#2151619
and egd.effective_end_date = hr_api.g_eot;
select c.contact_type
from per_contact_relationships c
where c.person_id = cv_person_id
and c.contact_person_id = cv_dpnt_person_id
-- Bug 1762932 added the personal_flag condition
and nvl(c.personal_flag, 'N') = 'Y'
and c.business_group_id = p_business_group_id
and p_effective_date between nvl(c.date_start, p_effective_date)
and nvl(c.date_end, p_effective_date)
;
select mx_dpnts_alwd_num
,no_mx_num_dfnd_flag
,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
from ben_dsgn_rqmt_f
where
((nvl(pl_id, hr_api.g_number) = cv_pl_id)
or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
and dsgn_typ_cd = 'DPNT'
and grp_rlshp_cd is null
and business_group_id + 0 = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
order by heir
;
select 's'
from ben_dsgn_rqmt_f r
where ((nvl(pl_id, hr_api.g_number) = cv_pl_id)
or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
and r.dsgn_typ_cd = 'DPNT'
and r.business_group_id = p_business_group_id
and p_effective_date between nvl(r.effective_start_date, p_effective_date)
and nvl(r.effective_end_date, p_effective_date)
;
select r.mx_dpnts_alwd_num
,r.no_mx_num_dfnd_flag
,r.dsgn_rqmt_id
,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
,r.grp_rlshp_cd
from ben_dsgn_rqmt_f r,
ben_dsgn_rqmt_rlshp_typ dr
where ((nvl(pl_id, hr_api.g_number) = cv_pl_id)
or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
and r.dsgn_typ_cd = 'DPNT'
and r.business_group_id = p_business_group_id
and p_effective_date between nvl(r.effective_start_date, p_effective_date)
and nvl(r.effective_end_date, p_effective_date)
and dr.dsgn_rqmt_id = r.dsgn_rqmt_id
and dr.rlshp_typ_cd in (select c.contact_type
from per_contact_relationships c
where c.person_id = cv_person_id
and c.contact_person_id = cv_dpnt_person_id
and nvl(c.personal_flag, 'N') = 'Y'
and c.business_group_id = p_business_group_id
and p_effective_date between nvl(c.date_start, p_effective_date)
and nvl(c.date_end, p_effective_date) )
order by heir ;
select count(elig_cvrd_dpnt_id)
from ben_elig_cvrd_dpnt_f ecd ,
ben_per_in_ler pil
where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and ecd.cvg_thru_dt = hr_api.g_eot
-- and cvrd_flag = 'Y'
and ecd.business_group_id + 0 = p_business_group_id
and p_effective_date between ecd.effective_start_date
and ecd.effective_end_date
and cv_cvg_strt_dt <= nvl(ecd.cvg_thru_dt, hr_api.g_date)
and nvl(cv_cvg_thru_dt, hr_api.g_date) >= ecd.cvg_strt_dt
and ecd.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select count(*)
from per_contact_relationships c
, ben_elig_cvrd_dpnt_f d
, ben_per_in_ler pil
where
c.person_id = cv_person_id
and c.contact_person_id = d.dpnt_person_id
and d.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and d.cvg_strt_dt is not null
and d.cvg_thru_dt = hr_api.g_eot
and cv_cvg_strt_dt <= nvl(d.cvg_thru_dt, hr_api.g_date)
and nvl(cv_cvg_thru_dt, hr_api.g_date) >= d.cvg_strt_dt
and c.business_group_id + 0 = p_business_group_id
and p_effective_date between nvl(c.date_start, p_effective_date)
and nvl(c.date_end, p_effective_date)
and d.effective_end_date = hr_api.g_eot -- bug 1237204
and d.business_group_id + 0 = p_business_group_id
and c.contact_type in
(select rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ
where dsgn_rqmt_id = l_dsgn_rqmt_id)
and d.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select p.name pl_name, o.name opt_name
from ben_pl_f p,
ben_opt_f o,
ben_oipl_f op,
ben_prtt_enrt_rslt_f en
where en.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and p.pl_id=en.pl_id
and en.oipl_id = op.oipl_id(+)
and op.opt_id=o.opt_id(+)
and en.prtt_enrt_rslt_stat_cd is null
and p_effective_date between en.effective_start_date and en.effective_end_date
and p_effective_date between p.effective_start_date and p.effective_end_date
and p_effective_date between o.effective_start_date(+) and o.effective_end_date(+)
and p_effective_date between op.effective_start_date(+) and op.effective_end_date(+)
;
select meaning
from hr_lookups
where lookup_code=cv_contact_type
and lookup_type='CONTACT'
;