APPS.BEN_ICD_PLAN_DESIGN_SETUP SQL Statements

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

Line: 30

     select typ.element_type_id
           ,typ.effective_start_date
           ,typ.effective_end_date
           ,nvl(typtl.description,typtl.element_name) use_name
           ,typ.input_currency_code
           ,typ.processing_type
     from pay_element_types_f typ
         ,pay_element_types_f_tl typtl
     where typ.element_type_id = p_element_type_id
     and   p_effective_date between
           typ.effective_start_date and typ.effective_end_date
     and   typ.element_type_id = typtl.element_type_id
     and   typtl.language (+) = userenv('lang');

Line: 46

     select opt.opt_id
     from   ben_opt_f opt
     where  opt.business_group_id = p_business_group_id
     and    p_effective_date between
            opt.effective_start_date and opt.effective_end_date
     and    opt.name = v_name;

Line: 54

     select pon.pl_typ_opt_typ_id
     from   ben_pl_typ_opt_typ_f pon
     where  pon.opt_id = v_opt_id
     and    pon.pl_typ_id = v_pl_typ_id
     and    p_effective_date between
            pon.effective_start_date and pon.effective_end_date;

Line: 62

     select max(oipl.ordr_num)
     from   ben_oipl_f oipl
     where  oipl.pl_id = l_pl_id
     and    p_effective_date between
            oipl.effective_start_date and oipl.effective_end_date;

Line: 260

procedure delete_elig(p_prtn_elig_id in number,
                      p_effective_date in date,
                      p_object_version_number in out nocopy number) is
  cursor c_prfl is
     select prfl.prtn_elig_prfl_id
           ,prfl.effective_start_date
           ,prfl.effective_end_date
           ,prfl.object_version_number
     from  ben_prtn_elig_prfl_f prfl
     where prfl.prtn_elig_id = p_prtn_elig_id
     and   p_effective_date between
           prfl.effective_start_date and prfl.effective_end_date;

Line: 276

    ben_prtn_elig_prfl_api.delete_prtn_elig_prfl
       (p_validate => false,
        p_prtn_elig_prfl_id => l_prfl.prtn_elig_prfl_id,
        p_effective_start_date => l_esd,
        p_effective_end_date => l_eed,
        p_object_version_number => l_prfl.object_version_number,
        p_effective_date => p_effective_date,
        p_datetrack_mode => hr_api.g_zap);

Line: 286

  ben_participation_elig_api.delete_participation_elig
     (p_validate => false,
     p_prtn_elig_id => p_prtn_elig_id,
     p_effective_start_date => l_esd,
     p_effective_end_date => l_eed,
     p_object_version_number => p_object_version_number,
     p_effective_date => p_effective_date,
     p_datetrack_mode => hr_api.g_zap);

Line: 295

end delete_elig;

Line: 297

procedure delete_setup(p_element_type_id in number
                      ,p_business_group_id in number
                      ,p_effective_date in date) is

  l_esd date;

Line: 305

     select abr.acty_base_rt_id
           ,abr.object_version_number
           ,abr.context_pl_id pl_id
           ,abr.oipl_id
           ,pln.pl_typ_id
     from   ben_acty_base_rt_f abr
           ,ben_pl_f pln
           ,ben_pl_typ_f typ
     where  abr.element_type_id = p_element_type_id
     and    abr.business_group_id = p_business_group_id
     and    p_effective_date between
            abr.effective_start_date and abr.effective_end_date
     and    abr.context_pl_id = pln.pl_id
     and    p_effective_date between
            pln.effective_start_date and pln.effective_end_date
     and    pln.pl_typ_id = typ.pl_typ_id
     and    p_effective_date between
            typ.effective_start_date and typ.effective_end_date
     and    typ.opt_typ_cd = 'ICM';

Line: 326

     select prtn.prtn_elig_id
           ,prtn.object_version_number
     from ben_prtn_elig_f prtn
     where prtn.oipl_id = v_oipl_id
     and   p_effective_date between
           prtn.effective_start_date and prtn.effective_end_date;

Line: 334

     select prtn.prtn_elig_id
           ,prtn.object_version_number
     from ben_prtn_elig_f prtn
     where prtn.pl_id = v_pl_id
     and   p_effective_date between
           prtn.effective_start_date and prtn.effective_end_date;

Line: 342

       select oipl.object_version_number
             ,oipl.opt_id
       from ben_oipl_f oipl
       where oipl.oipl_id = v_oipl_id
       and   p_effective_date between
             oipl.effective_start_date and oipl.effective_end_date;

Line: 351

       select pon.pl_typ_opt_typ_id
             ,pon.object_version_number
       from ben_pl_typ_opt_typ_f pon
       where pon.opt_id = v_opt_id
       and   pon.pl_typ_id = v_pl_typ_id
       and   p_effective_date between
             pon.effective_start_date and pon.effective_end_date
       and not exists
          (select 'Y'
           from ben_oipl_f oipl
               ,ben_pl_f pln
           where oipl.opt_id = v_opt_id
           and   oipl.pl_id = pln.pl_id
           and   pln.pl_typ_id = v_pl_typ_id);

Line: 367

       select opt.object_version_number
       from ben_opt_f opt
       where opt.opt_id = v_opt_id
       and   p_effective_date between
             opt.effective_start_date and opt.effective_end_date
       and not exists
          (select 'Y'
           from ben_pl_typ_opt_typ_f pon
           where pon.opt_id = v_opt_id);

Line: 378

       select pln.object_version_number
       from ben_pl_f pln
       where pln.pl_id = v_pl_id
       and   p_effective_date between
             pln.effective_start_date and pln.effective_end_date
       and not exists
            (select 'Y'
             from  ben_oipl_f oipl
             where oipl.pl_id = v_pl_id);

Line: 389

       select typ.object_version_number
       from ben_pl_typ_f typ
       where typ.pl_typ_id = v_pl_typ_id
       and   p_effective_date between
             typ.effective_start_date and typ.effective_end_date
       and not exists
            (select 'Y'
             from  ben_pl_f pln
             where pln.pl_typ_id = v_pl_typ_id);

Line: 400

     select abr.acty_base_rt_id
     from   ben_acty_base_rt_f abr
           ,ben_pl_f pln
           ,ben_pl_typ_f typ
     where  abr.element_type_id = p_element_type_id
     and    abr.context_pl_id = pln.pl_id
     and    pln.pl_typ_id = typ.pl_typ_id
     and    typ.opt_typ_cd = 'ICM';

Line: 414

    ben_acty_base_rate_api.delete_acty_base_rate
     (p_validate => false,
     p_acty_base_rt_id => l_abr.acty_base_rt_id,
     p_effective_start_date => l_esd,
     p_effective_end_date => l_eed,
     p_object_version_number => l_abr.object_version_number,
     p_effective_date => p_effective_date,
     p_datetrack_mode => hr_api.g_zap);

Line: 425

        delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
                    p_effective_date => p_effective_date,
                    p_object_version_number => l_prtn_elig.object_version_number);

Line: 431

        ben_option_in_plan_api.delete_option_in_plan
          (p_validate => false,
           p_oipl_id => l_abr.oipl_id,
           p_effective_start_date => l_esd,
           p_effective_end_date => l_eed,
           p_object_version_number => l_oipl.object_version_number,
           p_effective_date => p_effective_date,
           p_datetrack_mode => hr_api.g_zap);

Line: 441

          ben_plan_type_option_type_api.delete_plan_type_option_type
             (p_validate => false,
              p_pl_typ_opt_typ_id => l_pon.pl_typ_opt_typ_id,
              p_effective_start_date => l_esd,
              p_effective_end_date => l_eed,
              p_object_version_number => l_pon.object_version_number,
              p_effective_date => p_effective_date,
              p_datetrack_mode => hr_api.g_zap);

Line: 452

          ben_option_definition_api.delete_option_definition
             (p_validate => false,
              p_opt_id => l_oipl.opt_id,
              p_effective_start_date => l_esd,
              p_effective_end_date => l_eed,
              p_object_version_number => l_opt.object_version_number,
              p_effective_date => p_effective_date,
              p_datetrack_mode => hr_api.g_zap);

Line: 465

        delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
                    p_effective_date => p_effective_date,
                    p_object_version_number => l_prtn_elig.object_version_number);

Line: 472

       ben_plan_api.delete_plan
          (p_validate => false,
           p_pl_id => l_abr.pl_id,
           p_effective_start_date => l_esd,
           p_effective_end_date => l_eed,
           p_object_version_number => l_pln.object_version_number,
           p_effective_date => p_effective_date,
           p_datetrack_mode => hr_api.g_zap);

Line: 483

       ben_plan_type_api.delete_plan_type
          (p_validate => false,
           p_pl_typ_id => l_abr.pl_typ_id,
           p_effective_start_date => l_esd,
           p_effective_end_date => l_eed,
           p_object_version_number => l_typ.object_version_number,
           p_effective_date => p_effective_date,
           p_datetrack_mode => hr_api.g_zap);

Line: 502

    ben_icd_flex_field_setup.delete_icd_config
    (p_element_type_id => p_element_type_id,
     p_effective_date => p_effective_date);

Line: 511

end delete_setup;