APPS.BEN_CPP_BUS SQL Statements

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

Line: 47

   cursor c1 is select null
                  from ben_plip_f
                 where plip_id <> nvl(p_plip_id,-1)
                   and pgm_id = p_pgm_id
                   and business_group_id + 0 = p_business_group_id
                   and ordr_num = p_ordr_num
                   and p_validation_start_date <= effective_end_date
                   and p_validation_end_date >= effective_start_date;

Line: 110

cursor c1 is select null
             from   ben_plip_f
             where  pgm_id = p_pgm_id
             and    business_group_id + 0 = p_business_group_id
             and    pl_id = p_pl_id
             and    plip_id <> nvl(p_plip_id, -1)
             and    p_validation_start_date <= effective_end_date
             and    p_validation_end_date >= effective_start_date;

Line: 163

cursor c1 is select pl_cd
             from   ben_pl_f
             where  pl_id = p_pl_id
             and    p_effective_date between effective_start_date
                                         and effective_end_date
             and    business_group_id = p_business_group_id;

Line: 340

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_dflt_enrt_det_rl
    and    ff.formula_type_id = -32 /*default enrollment det */
    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: 436

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_auto_enrt_mthd_rl
    and    ff.formula_type_id = -146 /*default enrollment det */
    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: 684

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_enrt_rl
    and    ff.formula_type_id = -393 /*default enrollment det */
    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: 778

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_dflt_to_asn_pndg_ctfn_rl
    and    ff.formula_type_id = -454
    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: 874

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_mn_cvg_rl
    and    ff.formula_type_id = -164
    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: 970

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_mx_cvg_rl
    and    ff.formula_type_id = -161
    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: 1066

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_prort_prtl_yr_cvg_rstrn_rl
    and    ff.formula_type_id = -166
    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: 2405

  cursor c1 is select pgm_prvds_no_auto_enrt_flag
               from   ben_pgm_f pgm
               where  pgm.pgm_id = p_pgm_id
               and    pgm.business_group_id +0 = p_business_group_id
               and    p_effective_date between pgm.effective_start_date
                                           and pgm.effective_end_date;

Line: 2497

  cursor c1 is select pgm_prvds_no_dflt_enrt_flag
               from   ben_pgm_f pgm
               where  pgm.pgm_id = p_pgm_id
               and    pgm.business_group_id +0 = p_business_group_id
               and    p_effective_date between pgm.effective_start_date
                                           and pgm.effective_end_date;

Line: 2734

  cursor pl_is_imputed is select imptd_incm_calc_cd
                          from   ben_pl_f
                          where  pl_id = p_pl_id
                          and    p_effective_date between effective_start_date
                                 and effective_end_date
                          and    business_group_id +0 = p_business_group_id;

Line: 2741

  cursor count_plans is select count(distinct pl.pl_id)
                from   ben_pl_f pl,
                       ben_plip_f plip
                where  plip.pgm_id = p_pgm_id
                and    plip.pl_id = pl.pl_id
                and    pl.imptd_incm_calc_cd = 'PRTT'
                and    plip.plip_id <> nvl(p_plip_id, -1)
                and    pl.business_group_id +0 = p_business_group_id
                and    p_effective_date between pl.effective_start_date
                       and pl.effective_end_date
                and    p_effective_date between plip.effective_start_date
                       and plip.effective_end_date;

Line: 2825

  cursor pl_is_flex is select invk_flx_cr_pl_flag
                         from   ben_pl_f
                         where  pl_id = p_pl_id
                         and    p_effective_date between effective_start_date
                                and effective_end_date
                         and    business_group_id = p_business_group_id;

Line: 2832

  cursor c_flex_pgm is select pgm_typ_cd
        	from   ben_pgm_f
        	where  pgm_id = p_pgm_id
        	and    p_effective_date between effective_start_date and effective_end_date
                and    business_group_id = p_business_group_id;

Line: 2838

  cursor count_plans is select count(distinct pl.pl_id)
                from   ben_pl_f pl,
                       ben_plip_f plip
                where  plip.pgm_id = p_pgm_id
                and    plip.pl_id = pl.pl_id
                and    pl.invk_flx_cr_pl_flag = 'Y'
                and    plip.plip_id <> nvl(p_plip_id, -1)
                and    pl.business_group_id = p_business_group_id
                and    p_effective_date between pl.effective_start_date
                       and pl.effective_end_date
                and    p_effective_date between plip.effective_start_date
                       and plip.effective_end_date;

Line: 2932

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_enrt_cvg_strt_dt_rl
    and    ff.formula_type_id = -29
    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: 3025

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_enrt_cvg_end_dt_rl
    and    ff.formula_type_id = -30
    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: 3260

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_rt_strt_dt_rl
    and    ff.formula_type_id = -66
    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: 3426

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_rt_end_dt_rl
    and    ff.formula_type_id = -67
    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: 3521

    select null
    from   ff_formulas_f ff ,
           per_business_groups pbg
    where  ff.formula_id = p_postelcn_edit_rl
    and    ff.formula_type_id = -215
    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: 4046

    select null from BEN_CVG_AMT_CALC_MTHD_F cvg
    where nvl(cvg.pl_id,-1) = p_pl_id
    and cvg.cvg_mlt_cd = 'FLFX'
    and cvg.entr_val_at_enrt_flag = 'Y'
    and cvg.business_group_id = p_business_group_id
    and p_effective_date between cvg.effective_start_date and cvg.effective_end_date;

Line: 4122

Procedure chk_plan_delete_in_pgm( p_plip_id in number
                                 ,p_validation_start_date in date
				 ,p_validation_end_date in date
				 ,p_effective_date in date
				 ) is

l_proc    varchar2(72) := g_package||' chk_plan_delete_in_pgm ';

Line: 4132

cursor c2  is select pgm_id,pl_id
              from ben_plip_f
	      where plip_id =p_plip_id
	      and p_effective_date between effective_start_date
	      and effective_end_date;

Line: 4140

cursor c1  is select  erp.pl_id
             from
             ben_enrt_perd_for_pl_f erp
	     where
	       (
	         ( enrt_perd_id in
		      (
		       select enrt_perd_id
		       from ben_enrt_perd enp,ben_POPL_ENRT_TYP_CYCL_F pet
		       where enp.POPL_ENRT_TYP_CYCL_id = pet.POPL_ENRT_TYP_CYCL_id and
		       pet.pgm_id=l_pgm_id
		       )
                 )
		 or
                 ( lee_rsn_id in
		       (
		       select lee_rsn_id
		       from ben_lee_rsn_f len,ben_POPL_ENRT_TYP_CYCL_F pet
		       where len.POPL_ENRT_TYP_CYCL_id = pet.POPL_ENRT_TYP_CYCL_id and
		       pet.pgm_id = l_pgm_id
		       )
                 )
	       )
	       and pl_id = l_pl_id
	       and p_validation_start_date <= erp.effective_end_date
               and p_validation_end_date >= erp.effective_start_date ;

Line: 4186

       fnd_message.set_name('BEN','BEN_94109_DELETE_PL_ID');

Line: 4192

End chk_plan_delete_in_pgm;

Line: 4223

Procedure dt_update_validate
            (p_dflt_enrt_det_rl               in number ,
             p_pl_id                         in number ,
             p_pgm_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_update_validate';

Line: 4312

End dt_update_validate;

Line: 4349

Procedure dt_delete_validate
            (p_plip_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: 4372

  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: 4431

End dt_delete_validate;

Line: 4436

Procedure insert_validate
	(p_rec 			 in ben_cpp_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: 4811

End insert_validate;

Line: 4816

Procedure update_validate
	(p_rec 			 in ben_cpp_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: 5195

  dt_update_validate
    (p_dflt_enrt_det_rl              => p_rec.dflt_enrt_det_rl,
     p_pl_id                         => p_rec.pl_id,
     p_pgm_id                        => p_rec.pgm_id,
     p_datetrack_mode                => p_datetrack_mode,
     p_validation_start_date	     => p_validation_start_date,
     p_validation_end_date	     => p_validation_end_date);

Line: 5204

End update_validate;

Line: 5209

Procedure delete_validate
	(p_rec 			 in ben_cpp_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: 5223

  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_plip_id		=> p_rec.plip_id);

Line: 5230

  chk_plan_delete_in_pgm(p_plip_id               => p_rec.plip_id,
                       p_validation_start_date   => p_validation_start_date ,
                       p_validation_end_date     => p_validation_end_date,
		       p_effective_date          => p_effective_date
	          );

Line: 5239

End delete_validate;

Line: 5252

    select a.legislation_code
    from   per_business_groups a,
           ben_plip_f b
    where b.plip_id      = p_plip_id
    and   a.business_group_id = b.business_group_id;