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_CCM_BUS SQL Statements

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

Line: 105


     select max(pen.ENRT_CVG_STRT_DT)
       from ben_prtt_enrt_rslt_f pen
      where ( pl_id = nvl(p_pl_id, -9999) OR
              oipl_id = nvl(p_oipl_id, -9999)
            )
        and pen.prtt_enrt_rslt_stat_cd is null
        and pen.business_group_id = p_business_group_id
        and pen.bnft_amt is not null
        and (pen.creation_date) >= (l_creation_date);

Line: 117


     select min(creation_date)
       from ben_cvg_amt_calc_mthd_F
      where cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id;

Line: 152


        elsif p_datetrack_mode = 'DELETE' AND
              p_effective_date < l_max_cvg_strt_dt
        then
          --
          close c_max_cvg_strt_dt;

Line: 196


cursor c_pl is select 'x'  from
    ben_acty_base_rt_f  where pl_id = p_pl_id  and
    rt_mlt_cd = 'CVG' and  entr_val_at_enrt_flag = 'Y' and
    p_effective_date between effective_start_date and effective_end_date  ;

Line: 201


cursor c_plip  is select 'x' from  ben_acty_base_rt_f a , ben_plip_f  b  where
     a.plip_id = b.plip_id  and   a.rt_mlt_cd = 'CVG' and  a.entr_val_at_enrt_flag = 'Y' and
     p_effective_date between a.effective_start_date and a.effective_end_date and
     b.pl_id = p_pl_id and p_effective_date between b.effective_start_date and b.effective_end_date;

Line: 206


cursor c_oipl is select 'x'  from
    ben_acty_base_rt_f  where oipl_id  = p_oipl_id  and
    rt_mlt_cd = 'CVG' and  entr_val_at_enrt_flag = 'Y' and
    p_effective_date between effective_start_date and effective_end_date  ;

Line: 211


cursor c_oiplip is   select 'x'  from
     ben_acty_base_rt_f a ,ben_oiplip_f b  where a.oiplip_id = b.oiplip_id and
     a.rt_mlt_cd = 'CVG' and a.entr_val_at_enrt_flag = 'Y' and
     p_effective_date between a.effective_start_date and a.effective_end_date and
     b.oipl_id = p_oipl_id and p_effective_date between b.effective_start_date and b.effective_end_date ;

Line: 298


    select null
    from   ben_comp_lvl_fctr a
    where  a.comp_lvl_fctr_id = p_comp_lvl_fctr_id;

Line: 928


    select null
    from   ff_formulas_f ff
           ,per_business_groups pbg
    where  ff.formula_id = p_val_calc_rl
    and    ff.formula_type_id = -49
    and    pbg.business_group_id = p_business_group_id
    and    nvl(ff.business_group_id, p_business_group_id) =
               p_business_group_id
    and    nvl(ff.legislation_code, pbg.legislation_code) =
               pbg.legislation_code
    and    p_effective_date
           between ff.effective_start_date
           and     ff.effective_end_date;

Line: 1092


    select null
    from   ff_formulas_f ff
           ,per_business_groups pbg
    where  ff.formula_id = p_rndg_rl
    and    ff.formula_type_id = -169
    and    pbg.business_group_id = p_business_group_id
    and    nvl(ff.business_group_id, p_business_group_id) =
               p_business_group_id
    and    nvl(ff.legislation_code, pbg.legislation_code) =
               pbg.legislation_code
    and    p_effective_date
           between ff.effective_start_date
           and     ff.effective_end_date;

Line: 1185


    select null
    from   ff_formulas_f ff
           ,per_business_groups pbg
    where  ff.formula_id = p_lwr_lmt_calc_rl
    and    ff.formula_type_id = -511
    and    pbg.business_group_id = p_business_group_id
    and    nvl(ff.business_group_id, p_business_group_id) =
               p_business_group_id
    and    nvl(ff.legislation_code, pbg.legislation_code) =
               pbg.legislation_code
    and    p_effective_date
           between ff.effective_start_date
           and     ff.effective_end_date;

Line: 1278


    select null
    from   ff_formulas_f ff
           ,per_business_groups pbg
    where  ff.formula_id = p_upr_lmt_calc_rl
    and    ff.formula_type_id = -514
    and    pbg.business_group_id = p_business_group_id
    and    nvl(ff.business_group_id, p_business_group_id) =
               p_business_group_id
    and    nvl(ff.legislation_code, pbg.legislation_code) =
               pbg.legislation_code
    and    p_effective_date
           between ff.effective_start_date
           and     ff.effective_end_date;

Line: 1760


cursor c1 is select null
             from   ben_cvg_amt_calc_mthd_f
             Where  cvg_amt_calc_mthd_id <> nvl(p_cvg_amt_calc_mthd_id,-1)
             and    oipl_id = p_oipl_id
             and    business_group_id = p_business_group_id
             and    p_effective_date between effective_start_date
                    and effective_end_date;

Line: 1818


cursor c1 is select null
             from   ben_cvg_amt_calc_mthd_f
             Where  cvg_amt_calc_mthd_id <> nvl(p_cvg_amt_calc_mthd_id,-1)
             and    pl_id = p_pl_id
             and    business_group_id = p_business_group_id
             and    p_effective_date between effective_start_date
                    and effective_end_date;

Line: 1826


cursor c2 is select 'x'
             from   ben_pl_f pl
             Where  pl.pl_id = p_pl_id
             and    pl.dflt_to_asn_pndg_ctfn_cd like '%NL%'   -- Next Lower
             and    pl.business_group_id = p_business_group_id
             and    p_effective_date between pl.effective_start_date
                    and pl.effective_end_date;

Line: 1834


cursor c3 is select 'x'
             from   ben_ler_bnft_rstrn_f lbr
             Where lbr.dflt_to_asn_pndg_ctfn_cd like '%NL%'
             and    lbr.pl_id = p_pl_id
             and    p_effective_date between lbr.effective_start_date
                    and lbr.effective_end_date;

Line: 1841


cursor c4 is select 'x'
             from   ben_plip_f pip
             Where  pip.dflt_to_asn_pndg_ctfn_cd like '%NL%'
             and    pip.pl_id = p_pl_id
             and    p_effective_date between pip.effective_start_date
                    and pip.effective_end_date;

Line: 1939


             select null
             from   ben_pl_f
             Where  pl_id = p_pl_id
	     and    svgs_pl_flag = 'Y'
             and    effective_start_date <= p_validation_end_date
	     and    effective_end_date >= p_validation_start_date;

Line: 1947


             select null
             from   ben_pl_f pln, ben_oipl_f oipl
             Where  oipl_id = p_oipl_id
	     and    pln.pl_id = oipl.pl_id
	     and    pln.svgs_pl_flag = 'Y'
	     and    pln.effective_start_date <= p_validation_end_date
	     and    pln.effective_end_date >= p_validation_start_date
             and    oipl.effective_start_date <= p_validation_end_date
	     and    oipl.effective_end_date >= p_validation_start_date;

Line: 2028


Procedure dt_update_validate
            (p_oipl_id                       in number default hr_api.g_number,
             p_pl_id                         in number default hr_api.g_number,
             p_plip_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: 2118


End dt_update_validate;

Line: 2155


Procedure dt_delete_validate
            (p_cvg_amt_calc_mthd_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: 2178


  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);

Line: 2256


End dt_delete_validate;

Line: 2585


Procedure insert_validate
	(p_rec 			 in ben_ccm_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: 2778


End insert_validate;

Line: 2783


Procedure update_validate
	(p_rec 			 in ben_ccm_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: 2949


  dt_update_validate
    (p_oipl_id                       => p_rec.oipl_id,
     p_pl_id                         => p_rec.pl_id,
     p_plip_id                       => p_rec.plip_id,
     p_datetrack_mode                => p_datetrack_mode,
     p_validation_start_date	     => p_validation_start_date,
     p_validation_end_date	     => p_validation_end_date);

Line: 2985


End update_validate;

Line: 2990


Procedure delete_validate
	(p_rec 			 in ben_ccm_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: 3004


  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_cvg_amt_calc_mthd_id		=> p_rec.cvg_amt_calc_mthd_id);

Line: 3019


End delete_validate;

Line: 3032


    select a.legislation_code
    from   per_business_groups a,
           ben_cvg_amt_calc_mthd_f b
    where b.cvg_amt_calc_mthd_id      = p_cvg_amt_calc_mthd_id
    and   a.business_group_id = b.business_group_id;