eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_VEP_BUS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 189


    select null from
         BEN_VRBL_RT_ELIG_PRFL_f vep
    where
     vep.vrbl_rt_prfl_id        = p_vrbl_rt_prfl_id
     and p_effective_date between effective_start_date
     						and effective_end_date;

Line: 254


    select null from
         BEN_VRBL_RT_ELIG_PRFL_f vep
    where
      vep.eligy_prfl_id   = p_eligy_prfl_id
     and  vep.vrbl_rt_prfl_id        = p_vrbl_rt_prfl_id
     and  vep.effective_start_date > p_effective_date
     and  nvl(vep.vrbl_rt_elig_prfl_id,-1) <> p_vrbl_rt_elig_prfl_id ;

Line: 270


      hr_utility.set_location('Future record exists.Cannot insert ', 8 ) ;

Line: 320


    select 'X' from
         ben_vrbl_rt_prfl_f vpf
    where
      vpf.vrbl_rt_prfl_id   = p_vrbl_rt_prfl_id
     and p_effective_date between vpf.effective_start_date and vpf.effective_end_date
     and (
	 --  Bug 14361059
	 (rt_hrly_slrd_flag = 'Y' and exists (select '1' from BEN_HRLY_SLRD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_pstl_cd_flag = 'Y' and exists (select '1' from BEN_PSTL_ZIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

(rt_lbr_mmbr_flag = 'Y' and exists (select '1' from BEN_LBR_MMBR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_lgl_enty_flag = 'Y' and exists (select '1' from BEN_LGL_ENTY_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

(rt_benfts_grp_flag = 'Y' and exists (select '1' from BEN_BENFTS_GRP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

(rt_wk_loc_flag = 'Y' and exists (select '1' from BEN_WK_LOC_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_brgng_unit_flag = 'Y' and exists (select '1' from BEN_BRGNG_UNIT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_age_flag = 'Y' and exists (select '1' from BEN_AGE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))  or

	(rt_los_flag = 'Y' and exists (select '1' from BEN_LOS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))  or

	(rt_per_typ_flag = 'Y' and exists (select '1' from BEN_PER_TYP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))  or

	(rt_fl_tm_pt_tm_flag = 'Y' and exists (select '1' from BEN_FL_TM_PT_TM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))  or

	(rt_ee_stat_flag = 'Y' and exists (select '1' from BEN_EE_STAT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))  or

	(rt_grd_flag = 'Y' and exists (select '1' from BEN_GRADE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_pct_fl_tm_flag = 'Y' and exists (select '1' from BEN_PCT_FL_TM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_asnt_set_flag = 'Y' and exists (select '1' from BEN_ASNT_SET_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_hrs_wkd_flag = 'Y' and exists (select '1' from BEN_HRS_WKD_IN_PERD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_comp_lvl_flag = 'Y' and exists (select '1' from BEN_COMP_LVL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_org_unit_flag = 'Y' and exists (select '1' from BEN_ORG_UNIT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_loa_rsn_flag = 'Y' and exists (select '1' from BEN_LOA_RSN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_pyrl_flag = 'Y' and exists (select '1' from BEN_PYRL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_schedd_hrs_flag = 'Y' and exists (select '1' from BEN_SCHEDD_HRS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_py_bss_flag = 'Y' and exists (select '1' from BEN_PY_BSS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_prfl_rl_flag = 'Y' and exists (select '1' from BEN_VRBL_RT_PRFL_RL_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_cmbn_age_los_flag = 'Y' and exists (select '1' from BEN_CMBN_AGE_LOS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_prtt_pl_flag = 'Y') or

	(rt_svc_area_flag = 'Y' and exists (select '1' from BEN_SVC_AREA_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_ppl_grp_flag = 'Y' and exists (select '1' from BEN_PPL_GRP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dsbld_flag = 'Y' and exists (select '1' from BEN_DSBLD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_hlth_cvg_flag = 'Y') or

	(rt_poe_flag = 'Y' and exists (select '1' from BEN_POE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_ttl_cvg_vol_flag = 'Y' and exists (select '1' from BEN_TTL_CVG_VOL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_ttl_prtt_flag = 'Y' and exists (select '1' from BEN_TTL_PRTT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_gndr_flag = 'Y' and exists (select '1' from BEN_GNDR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_tbco_use_flag = 'Y' and exists (select '1' from BEN_TBCO_USE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_cntng_prtn_prfl_flag = 'Y' and exists (select '1' from BEN_CNTNG_PRTN_PRFL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_cbr_quald_bnf_flag = 'Y' and exists (select '1' from BEN_CBR_QUALD_BNF_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_optd_mdcr_flag = 'Y' and exists (select '1' from BEN_OPTD_MDCR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_lvg_rsn_flag = 'Y' and exists (select '1' from BEN_LVG_RSN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_pstn_flag = 'Y' and exists (select '1' from BEN_PSTN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_comptncy_flag = 'Y' and exists (select '1' from BEN_COMPTNCY_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_job_flag = 'Y' and exists (select '1' from BEN_JOB_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_qual_titl_flag = 'Y' and exists (select '1' from BEN_QUAL_TITL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dpnt_cvrd_pl_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dpnt_cvrd_plip_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_PLIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dpnt_cvrd_ptip_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dpnt_cvrd_pgm_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PGM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_enrld_oipl_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_OIPL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_enrld_pl_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_enrld_plip_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PLIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_enrld_ptip_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_enrld_pgm_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PGM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_prtt_anthr_pl_flag = 'Y' and exists (select '1' from BEN_PRTT_ANTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_othr_ptip_flag = 'Y' and exists (select '1' from BEN_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_no_othr_cvg_flag = 'Y' and exists (select '1' from BEN_NO_OTHR_CVG_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_dpnt_othr_ptip_flag = 'Y' and exists (select '1' from BEN_DPNT_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_qua_in_gr_flag = 'Y' and exists (select '1' from BEN_QUA_IN_GR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or

	(rt_perf_rtng_flag = 'Y' and exists (select '1' from BEN_PERF_RTNG_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
                                 and  p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))
  --Bug 14361059
	-- or rt_elig_prfl_flag = 'Y'
	);

Line: 532


    select pbg.security_group_id
      from per_business_groups pbg
         , BEN_VRBL_RT_ELIG_PRFL_f vep
     where vep.vrbl_rt_elig_prfl_id = p_vrbl_rt_elig_prfl_id
       and pbg.business_group_id = vep.business_group_id;

Line: 592


    select pbg.legislation_code
      from per_business_groups pbg
         , BEN_VRBL_RT_ELIG_PRFL_f vep
     where vep.vrbl_rt_elig_prfl_id = p_vrbl_rt_elig_prfl_id
       and pbg.business_group_id = vep.business_group_id;

Line: 860


Procedure chk_non_updateable_args
  (p_effective_date  in date
  ,p_rec             in ben_vep_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 895


End chk_non_updateable_args;

Line: 918


    select null
    from ben_elig_ttl_cvg_vol_prte_f
    where eligy_prfl_id = p_eligy_prfl_id
    and exists
      (select null
      from ben_elig_ttl_prtt_prte_f
      where eligy_prfl_id in
      	(select eligy_prfl_id
	 from ben_vrbl_rt_elig_prfl_f
	 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
	 and business_group_id = p_business_group_id
	 and p_effective_date between effective_start_date and effective_end_date)
      or eligy_prfl_id = p_eligy_prfl_id);

Line: 933


    select null
    from ben_elig_ttl_prtt_prte_f
    where eligy_prfl_id = p_eligy_prfl_id
    and exists
      (select null
       from ben_elig_ttl_cvg_vol_prte_f
       where eligy_prfl_id in
      	(select eligy_prfl_id
	 from ben_vrbl_rt_elig_prfl_f
	 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
	 and business_group_id = p_business_group_id
	 and p_effective_date between effective_start_date and effective_end_date)
      or eligy_prfl_id = p_eligy_prfl_id);

Line: 1001


  select mlt_cd, vrbl_usg_cd
  from ben_vrbl_rt_prfl_f
  where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
  and business_group_id = p_business_group_id
  and p_effective_date between effective_start_date and effective_end_date;

Line: 1008


  select null
  from ben_elig_ttl_prtt_prte_f
  where eligy_prfl_id = p_eligy_prfl_id
  and business_group_id = p_business_group_id
  and p_effective_date between effective_start_date and effective_end_date;

Line: 1015


  select null
  from ben_elig_ttl_cvg_vol_prte_f
  where eligy_prfl_id = p_eligy_prfl_id
  and business_group_id = p_business_group_id
  and p_effective_date between effective_start_date and effective_end_date;

Line: 1086


Procedure dt_update_validate
  (
  p_vrbl_rt_prfl_id               in number default hr_api.g_number
  ,p_eligy_prfl_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';

Line: 1135


End dt_update_validate;

Line: 1172


Procedure dt_delete_validate
  (p_vrbl_rt_elig_prfl_id             in 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_delete_validate';

Line: 1196


  If (p_datetrack_mode = hr_api.g_delete or
      p_datetrack_mode = hr_api.g_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
      );

Line: 1243


End dt_delete_validate;

Line: 1248


Procedure insert_validate
  (p_rec                   in ben_vep_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';

Line: 1312


End insert_validate;

Line: 1317


Procedure update_validate
  (p_rec                     in ben_vep_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';

Line: 1375


  dt_update_validate
    (
    p_vrbl_rt_prfl_id                        => p_rec.vrbl_rt_prfl_id
    ,p_eligy_prfl_id                         => p_rec.eligy_prfl_id
    ,p_datetrack_mode                 => p_datetrack_mode
    ,p_validation_start_date          => p_validation_start_date
    ,p_validation_end_date            => p_validation_end_date
    );

Line: 1385


  chk_non_updateable_args
    (p_effective_date  => p_effective_date
    ,p_rec             => p_rec
    );

Line: 1395


End update_validate;

Line: 1400


Procedure delete_validate
  (p_rec                    in ben_vep_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';

Line: 1415


  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_vrbl_rt_elig_prfl_id                         => p_rec.vrbl_rt_elig_prfl_id
    );

Line: 1423


End delete_validate;