[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select * from ben_pgm_f pgm
where pgm_id = p_pgm_id
and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
select blr.LEE_RSN_ID,pil.request_id
into l_LEE_RSN_ID, l_request_id
from ben_lee_rsn_f blr,
ben_popl_enrt_typ_cycl_f popl,
ben_per_in_ler pil
where blr.business_group_id = p_business_group_id
and blr.popl_enrt_typ_cycl_id = popl.POPL_ENRT_TYP_CYCL_ID
and blr.ler_id = pil.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and popl.business_group_id = p_business_group_id
and popl.pgm_id = p_pgm_id
and p_effective_date between popl.effective_start_date and popl.effective_end_date
and p_effective_date between blr.effective_start_date and blr.effective_end_date;
select enrt_mthd_cd
into l_posting_style
from ben_pgm_f
where pgm_id = p_pgm_id
and p_effective_date between effective_start_date and effective_end_date;
Select Yr_Perd_id
into l_yr_perd_id
From Ben_Yr_Perd
Where P_Effective_Date Between Start_Date and End_Date
and rownum = 1;
,P_LEE_RSN_ID => l_LEE_RSN_ID /* select lee_rsn_f
where pgm_id= */
,P_AUTO_ENRT_FLAG => l_posting_style /* 'Y' if Automatic Progression
'N' if Manual Progression */
,P_BUSINESS_GROUP_ID => p_business_group_id /* Business Group Id */
,P_ELIG_FLAG => 'Y' /* Y */
,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn /* out parameter */
,P_EFFECTIVE_DATE => p_effective_date
,p_request_id => l_request_id);
,P_LEE_RSN_ID => l_LEE_RSN_ID /* select lee_rsn_f
where pgm_id= */
,P_AUTO_ENRT_FLAG => l_posting_style /* 'Y' if Automatic Progression
'N' if Manual Progression */
,P_BUSINESS_GROUP_ID => p_business_group_id /* Business Group Id */
,P_ELIG_FLAG => 'N' /* Y */
,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn /* out parameter */
,P_EFFECTIVE_DATE => p_effective_date
,p_request_id => l_request_id);
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_base_Rt_f
where Pl_id = l_Pl_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_Base_rt_f
where Opt_Id = L_Opt_id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
select pil.request_id
into l_request_id
from ben_lee_rsn_f blr,
ben_popl_enrt_typ_cycl_f popl,
ben_per_in_ler pil
where blr.business_group_id = p_business_group_id
and blr.popl_enrt_typ_cycl_id = popl.POPL_ENRT_TYP_CYCL_ID
and blr.ler_id = pil.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and popl.business_group_id = p_business_group_id
and popl.pgm_id = p_pgm_id
and p_effective_date between popl.effective_start_date and popl.effective_end_date
and p_effective_date between blr.effective_start_date and blr.effective_end_date;
select ghn.Information9 to_corp_Id,
ghn.Information23 to_grade_id,
ghn.Information3 to_step_id,
pl.pl_id to_pl_id,
pgm.pgm_id to_pgm_id,
plip.plip_id to_plip_id,
pl.pl_typ_id to_pl_type_id,
ptip.ptip_id to_ptip_id,
ghn.Information10 length_of_service_type,
ghn.Information11 req_years,
ghn.Information12 req_Months,
ghn.Information13 req_days
from PER_GEN_HIERARCHY_NODES ghn,
ben_pl_f pl,
pqh_corps_definitions corp,
ben_pgm_f pgm,
ben_plip_f plip,
ben_ptip_f ptip
where ghn.Information4 = l_cur_corp_id --cur_corp_id
--and ghn.Information30 = l_cur_grade_id -- cur_pgm_id
and ghn.Entity_id = l_cur_step_id -- cur_step_id
and ghn.business_group_id = g_business_group_id
and ghn.node_type = 'CAREER_NODE'
and pl.mapping_table_name = 'PER_GRADES'
and pl.mapping_table_pk_id = ghn.Information23
and l_effective_date between pl.effective_start_date and pl.effective_end_date
and corp.corps_definition_id = ghn.Information9
and pgm.pgm_id = corp.ben_pgm_id
and l_effective_date between pgm.effective_start_date and pgm.effective_end_date
and plip.pl_id = pl.pl_id
and plip.pgm_id = pgm.pgm_id
and l_effective_date between plip.effective_start_date and plip.effective_end_date
and ptip.pgm_id = pgm.pgm_id
and ptip.pl_typ_id = pl.pl_typ_id
and l_effective_date between ptip.effective_start_date and ptip.effective_end_date
;
select
opt.opt_id to_opt_id,
oipl.oipl_id to_oipl_id
from
per_spinal_point_steps_f sps
, ben_opt_f opt
, ben_oipl_f oipl
where sps.step_id = career_path_rec.to_step_id
and opt.mapping_table_name = 'PER_SPINAL_POINTS'
and opt.mapping_table_pk_id = sps.spinal_point_id
and oipl.opt_id = opt.opt_id
and oipl.pl_id = career_path_rec.to_pl_id
and l_effective_date between sps.effective_start_date and sps.effective_end_date
and l_effective_date between opt.effective_start_date and opt.effective_end_date
and l_effective_date between oipl.effective_start_date and oipl.effective_end_date
;
Select *
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select corp.corps_definition_id cur_corp_id,
asg.grade_id cur_grade_id,
asg.assignment_id assignment_id,
spp.step_id cur_step_id,
asg.grade_ladder_pgm_id cur_pgm_id,
pl.pl_id cur_plan_id,
oipl.oipl_id cur_oipl_id
from per_all_assignments_f asg,
per_spinal_point_placements_f spp,
per_spinal_point_steps_f sps,
ben_opt_f opt,
ben_oipl_f oipl,
ben_pl_f pl,
pqh_corps_definitions corp
where asg.person_id = l_pil_info_rec.person_id
and asg.primary_flag = 'Y'
and asg.business_group_id = l_pil_info_rec.business_group_id
and asg.assignment_status_type_id = 1
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and spp.assignment_id = asg.assignment_id
and p_effective_date between spp.effective_start_date and spp.effective_end_date
and sps.step_id = spp.step_id
and p_effective_date between sps.effective_start_date and sps.effective_end_date
and opt.mapping_table_name = 'PER_SPINAL_POINTS'
and opt.mapping_table_pk_id = sps.spinal_point_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date
and pl.mapping_table_name = 'PER_GRADES'
and pl.mapping_table_pk_id = asg.grade_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and oipl.pl_id = pl.pl_id
and oipl.opt_id = opt.opt_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and corp.ben_pgm_id = asg.grade_ladder_pgm_id;