[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from fnd_currencies_tl
where currency_code = p_uom ;
SELECT lf_evt_ocrd_dt
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = p_per_in_ler_id;
SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
cvr.person_id, bpl.NAME, crt.crt_ordr_typ_cd
FROM ben_crt_ordr crt,
ben_crt_ordr_cvrd_per cvr,
per_all_people_f per,
per_contact_relationships con,
hr_lookups lkp,
ben_pl_f bpl
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = p_person_id
AND crt.pl_id = p_pl_id
AND crt.crt_ordr_id = cvr.crt_ordr_id
AND cvr.person_id = per.person_id
AND cvr.person_id = con.contact_person_id
AND con.contact_type = lkp.lookup_code
AND lkp.lookup_type = 'CONTACT'
AND p_effective_date BETWEEN NVL (lkp.start_date_active,
p_effective_date
)
AND NVL (lkp.end_date_active,
p_effective_date
)
AND GREATEST(l_lf_evt_ocrd_dt, p_enrt_cvg_strt_dt)
BETWEEN GREATEST (NVL (apls_perd_strtg_dt,
p_effective_date
),
NVL (detd_qlfd_ordr_dt,
apls_perd_strtg_dt
)
)
AND NVL (apls_perd_endg_dt,
p_enrt_cvg_thru_dt
)
AND crt.business_group_id = p_business_group_id
AND cvr.business_group_id = p_business_group_id
AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
AND NVL (con.date_end, p_effective_date)
AND con.business_group_id = p_business_group_id
AND bpl.pl_id = p_pl_id
AND p_effective_date BETWEEN NVL (bpl.effective_start_date,
p_effective_date
)
AND NVL (bpl.effective_end_date,
p_effective_date
)
UNION
SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
cvr.person_id, bpt.NAME, crt.crt_ordr_typ_cd
FROM ben_crt_ordr crt,
ben_crt_ordr_cvrd_per cvr,
per_all_people_f per,
per_contact_relationships con,
hr_lookups lkp,
ben_pl_typ_f bpt
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = p_person_id
AND crt.pl_typ_id = l_pl_rec.pl_typ_id
AND crt.crt_ordr_id = cvr.crt_ordr_id
AND cvr.person_id = per.person_id
AND cvr.person_id = con.contact_person_id
AND con.contact_type = lkp.lookup_code
AND lkp.lookup_type = 'CONTACT'
AND p_effective_date BETWEEN NVL (lkp.start_date_active,
p_effective_date
)
AND NVL (lkp.end_date_active,
p_effective_date
)
AND GREATEST(l_lf_evt_ocrd_dt, p_enrt_cvg_strt_dt)
BETWEEN GREATEST (NVL (apls_perd_strtg_dt,
p_effective_date
),
NVL (detd_qlfd_ordr_dt,
apls_perd_strtg_dt
)
)
AND NVL (apls_perd_endg_dt,
p_enrt_cvg_thru_dt
)
AND crt.business_group_id = p_business_group_id
AND cvr.business_group_id = p_business_group_id
AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
AND NVL (con.date_end, p_effective_date)
AND con.business_group_id = p_business_group_id
AND bpt.pl_typ_id = l_pl_rec.pl_typ_id
AND p_effective_date BETWEEN NVL (bpt.effective_start_date,
p_effective_date
)
AND NVL (bpt.effective_end_date,
p_effective_date
);
SELECT NULL
FROM ben_elig_cvrd_dpnt_f pdp, ben_per_in_ler pil
WHERE pdp.dpnt_person_id = l_dpnt_id
AND pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND p_effective_date BETWEEN pdp.effective_start_date
AND pdp.effective_end_date
AND GREATEST (pil.lf_evt_ocrd_dt, p_enrt_cvg_strt_dt) BETWEEN cvg_strt_dt
AND cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pil.business_group_id = p_business_group_id
AND pdp.per_in_ler_id = pil.per_in_ler_id
AND pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select nvl(sum(pen.bnft_amt),0)
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pl_id = p_pl_id
and pil.person_id = p_person_id
and nvl(pen.per_in_ler_id, pil.per_in_ler_id) <> pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_strt_dt < pen.effective_end_date
and (p_effective_date-1) between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.effective_end_date = ( select max(pen1.effective_start_date - 1)
from ben_prtt_enrt_rslt_f pen1
where pen1.per_in_ler_id = pil.per_in_ler_id
and pen1.prtt_enrt_rslt_stat_cd is NULL
and pen1.pl_id = p_pl_id
and pen1.enrt_cvg_thru_dt = hr_api.g_eot
and pen1.person_id = p_person_id ) ;
select pil.ler_id
from ben_per_in_ler pil,
ben_prtt_enrt_rslt_f pen
where pil.person_id = p_person_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.per_in_ler_id = pen.per_in_ler_id
and pen.person_id = p_person_id
and pen.pl_id = cv_pl_id
and pen.prtt_enrt_rslt_stat_cd is null ;
select rstrn.pl_id,
rstrn.mx_cvg_wcfn_amt,
rstrn.mx_cvg_incr_alwd_amt,
rstrn.mx_cvg_incr_wcf_alwd_amt,
rstrn.mn_cvg_amt mn_cvg_rqd_amt,
rstrn.mx_cvg_alwd_amt,
rstrn.mx_cvg_rl,
rstrn.mn_cvg_rl
from ben_ler_bnft_rstrn_f rstrn
where rstrn.ler_id = cv_ler_id
and rstrn.pl_id = cv_pl_id
and p_effective_date
between rstrn.effective_start_date
and rstrn.effective_end_date;
select pen.pgm_id,
pen.ptip_id,
pen.pl_typ_id,
NVL(epe.plip_id, cpp.plip_id) plip_id,
pen.pl_id,
pen.oipl_id,
pen.enrt_cvg_strt_dt,
pen.enrt_cvg_thru_dt,
pen.prtt_enrt_rslt_id,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
'N' interim_flag,
pen.person_id,
0 Calc_interm,
nvl(pen.bnft_amt,0) bnft_amt,
pen.uom,
epe.elig_per_elctbl_chc_id,
epe.MUST_ENRL_ANTHR_PL_ID,
'N' dpnt_cvd_by_othr_apls_flag,
-9999999999999999999999999999999999999 opt_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_plip_f cpp,
ben_oipl_f cop,
ben_pl_f pln
where pen.person_id = c_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pgm_id <> c_pgm_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
and pen.per_in_ler_id = epe.per_in_ler_id (+)
and pen.comp_lvl_cd not in ('PLANFC','PLANIMP')
and exists (select null
from ben_per_in_ler pil
where pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
and cpp.pgm_id = pen.pgm_id
and cpp.pl_id = pen.pl_id
and cpp.business_group_id = c_business_group_id
and c_effective_date between cpp.effective_start_date and cpp.effective_end_date
and pen.oipl_id = cop.oipl_id (+)
and c_effective_date
between cop.effective_start_date (+) and cop.effective_end_date (+)
and pen.pl_id = pln.pl_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date
order by 1,2,3,4,5,6,7;
l_interim_tbl.delete;
g_enrt_tbl.delete;
g_pl_tbl.delete;
g_pl_typ_tbl.delete;
g_ptip_tbl.delete;
l_dup_ptip_id_list.delete;
l_dup_pl_typ_id_list.delete;
l_dup_pl_id_list.delete;
l_enrt_tbl.delete;
l_enrt_tbl.delete;
l_enrt_tbl.delete;
select loc.region_2, asg.assignment_id, asg.organization_id
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id(+) = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and asg.business_group_id = p_business_group_id
order by DECODE(asg.assignment_type,'E',1,'B',2,3); -- 5303252 Order by asg_type
select null
from ben_elig_per_elctbl_chc
where business_group_id = p_business_group_id
and nvl(pgm_id,hr_api.g_number) = nvl(p_pgm_id, hr_api.g_number)
and MNDTRY_FLAG = 'Y'
and pl_id in -- bug 1207161 removed 'not'
(Select distinct pen.pl_id
From ben_prtt_enrt_rslt_f pen
Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and pen.person_id = p_person_id
and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null)
and oipl_id not in -- bug 1207161 added this.
(Select distinct nvl(pen.oipl_id, -1)
From ben_prtt_enrt_rslt_f pen
Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and pen.person_id = p_person_id
and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null)
and per_in_ler_id in (select per_in_ler_id
From ben_per_in_ler
Where person_id = p_person_id
and per_in_ler_stat_cd = 'STRTD'
and business_group_id = p_business_group_id
)
;
select distinct Enrt_Cvg_Strt_Dt CSD
,nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) CED
,'N' Match
from ben_prtt_enrt_rslt_f
where person_id = p_person_id
and effective_end_date >= enrt_cvg_strt_dt
and enrt_cvg_strt_dt <= nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) --bug 5257798
and prtt_enrt_rslt_stat_cd is null
and sspndd_flag = 'N'
and effective_end_date = hr_api.g_eot -- Bug 2130842 added this clause
order by 1 asc, 2 desc;
select a.person_type_usage_id
,a.person_type_id
,a.effective_start_date ESD
,a.effective_end_date EED
,a.object_version_number OVN
,'N' Match
from per_person_type_usages_f a
where a.Person_id = p_person_id
and a.person_type_id = l_person_type_id
order by a.effective_start_date asc, a.effective_end_date desc;
select person_type_id
from per_person_types
where system_person_type = 'PRTN'
and business_group_id = p_business_group_id;
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => l_ptu(j).person_type_usage_id
,p_effective_date => l_ptu(j).ESD
,p_datetrack_mode => hr_api.g_zap
,p_object_version_number => l_ptu(j).OVN
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed);
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => l_ptu(j).person_type_usage_id
,p_effective_date => l_ptu(j).ESD
,p_datetrack_mode => hr_api.g_zap
,p_object_version_number => l_ptu(j).OVN
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed);
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_pen(i).ced
,p_datetrack_mode => hr_api.g_delete
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed);
select distinct b.per_in_ler_id, b.ler_id, b.pl_id
From ben_prtt_enrt_rslt_f b
,ben_pl_f c
Where b.person_id = p_person_id
And nvl(b.pgm_id,-1) = nvl(p_pgm_id,-1)
And p_effective_date between
b.effective_start_date and b.effective_end_date
And b.business_group_id=p_business_group_id
And b.enrt_cvg_strt_dt < nvl(b.effective_end_date,hr_api.g_eot)
And b.oipl_id is NULL
and b.prtt_enrt_rslt_stat_cd is null
And b.sspndd_flag = 'Y'
And b.pl_id = c.pl_id
And p_effective_date between
c.effective_start_date and c.effective_end_date
And c.enrt_pl_opt_flag = 'Y'
;
Select a.prtt_enrt_rslt_id
,a.effective_start_date
,a.effective_end_date
,a.object_version_number
From ben_prtt_enrt_rslt_f a
Where a.person_id = p_person_id
And nvl(a.pgm_id,-1) = nvl(p_pgm_id,-1)
And a.business_group_id=p_business_group_id
And p_effective_date between
a.effective_start_date and a.effective_end_date
And a.enrt_cvg_strt_dt < nvl(a.effective_end_date, hr_api.g_eot)
And a.oipl_id is not null
and a.prtt_enrt_rslt_stat_cd is null
And a.sspndd_flag = 'N'
And a.pl_id = c_pl_id
;
l_datetrack_mode := hr_api.g_update;
BEN_PRTT_ENRT_RESULT_API.Update_PRTT_ENRT_RESULT
(p_validate => FALSE
,p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id
,p_per_in_ler_id => l_rec1.per_in_ler_id
,p_ler_id => l_rec1.ler_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_object_version_number => l_rec.object_version_number
,p_datetrack_mode => l_datetrack_mode
,p_sspndd_flag => 'Y'
,p_effective_date => p_effective_date
);
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and assignment_type <> 'C'
and primary_flag = 'Y'
and p_effective_date between
effective_start_date and effective_end_date
and business_group_id = p_business_group_id
order by assignment_type desc, effective_start_date desc ; -- bug 4124110
select pln.name
from ben_pl_f pln
where pln.pl_id = cv_pl_id
and pln.business_group_id = p_business_group_id;
select ' : ' || opt.name
from ben_oipl_f oipl,
ben_opt_f opt
where oipl.oipl_id = cv_oipl_id
and opt.opt_id = oipl.opt_id --Bug 2390734
and opt.business_group_id = p_business_group_id
and oipl.business_group_id = p_business_group_id;
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft enb
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd <> 'PLANIMP'
and pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
and pen.pgm_id = p_pgm_id
and pen.pl_typ_id = p_pl_typ_id
and enb.cvg_mlt_cd = 'ERL'
and pen.effective_end_date = hr_api.g_eot;
Procedure dt_update_validate
(p_pgm_id in number default hr_api.g_number,
p_oipl_id in number default hr_api.g_number,
p_per_in_ler_id in number default hr_api.g_number,
p_pl_id in number default hr_api.g_number,
p_pl_typ_id in number default hr_api.g_number,
p_prtt_enrt_rslt_id in number default hr_api.g_number,
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_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_prtt_enrt_rslt_id in number,
p_datetrack_mode in varchar2,
p_effective_date in date,
p_validation_start_date in date,
p_validation_end_date in date) Is
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
End dt_delete_validate;
Procedure insert_validate
(p_rec in ben_pen_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||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in ben_pen_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_validate';
dt_update_validate
(p_pgm_id => p_rec.pgm_id,
p_oipl_id => p_rec.oipl_id,
p_pl_id => p_rec.pl_id,
p_per_in_ler_id => p_rec.per_in_ler_id,
p_pl_typ_id => p_rec.pl_typ_id,
p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End update_validate;
Procedure delete_validate
(p_rec in ben_pen_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||'delete_validate';
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_effective_date => p_effective_date, -- Added for bug 3646239
p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_prtt_enrt_rslt_f b
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and a.business_group_id = b.business_group_id;