APPS.PER_CPN_BUS SQL Statements

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

Line: 20

Procedure chk_non_updateable_args(p_rec in per_cpn_shd.g_rec_type) is
--
  l_proc     varchar2(72) := g_package||'chk_non_updateable_args';

Line: 67

end chk_non_updateable_args;

Line: 117

	  select business_group_id
 	  from per_competences pc
	  where (( p_competence_id is null)
	        or (p_competence_id <> pc.competence_id)
		)
	  and competence_definition_id = p_competence_definition_id
	  and p_business_group_id is null
	  UNION
	  select business_group_id
	  from   per_competences pc
	  where  (   (p_competence_id is null)
		   or(p_competence_id <> pc.competence_id)
		 )
	  and competence_definition_id = p_competence_definition_id
          and   p_business_group_id is not null
	  and   ( business_group_id + 0  = p_business_group_id or
                  business_group_id is null);

Line: 237

    	select 	'Y'
    	from   	per_competence_elements cpe
    	where	(   nvl(cpe.effective_date_from,hr_api.g_sot) <
		    nvl(p_date_from, nvl(cpe.effective_date_from,hr_api.g_sot))
   		 or nvl(cpe.effective_date_from,hr_api.g_sot) >
		    nvl(p_date_to, nvl(cpe.effective_date_from,hr_api.g_sot))
		)
    	and	cpe.competence_id = p_competence_id
--adhunter reinstated the following check for 2533926
        and     cpe.type not in
                ('COMPETENCE_USAGE');

Line: 278

   if p_called_from = 'UPDATE'

and (        p_date_from <> per_cpn_shd.g_old_rec.date_from
     or  nvl(p_date_to,hr_api.g_date)<>nvl(per_cpn_shd.g_old_rec.date_to,hr_api.g_date)
    ) then

     open csr_check_dates_in_ele;

Line: 740

	select  business_group_id
	from	per_rating_scales
	where   rating_scale_id = p_rating_scale_id;

Line: 840

	select 'Y'
	from	per_rating_scales
	where   rating_scale_id = p_rating_scale_id
	and	upper(type)		= 'PROFICIENCY';

Line: 927

      select    'Y'
      from      per_rating_levels
      where     competence_id     = p_competence_id;

Line: 999

procedure chk_competence_rating_update
(p_competence_id             in      per_competences.competence_id%TYPE
,p_object_version_number     in      per_competences.object_version_number%TYPE
,p_rating_scale_id	     in      per_competences.rating_scale_id%TYPE
)
is
--
	l_exists	     varchar2(1);

Line: 1008

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

Line: 1016

      select    'Y'
      from      per_rating_levels rl,
                per_competence_elements ce
      where     ((rl.rating_level_id = ce.proficiency_level_id) or
		 (rl.rating_level_id = ce.high_proficiency_level_id))
      and       rl.rating_scale_id   = nvl(per_cpn_shd.g_old_rec.rating_scale_id,-9999)
      and 	ce.competence_id = p_competence_id;

Line: 1061

end chk_competence_rating_update;

Line: 1098

procedure chk_competence_delete
(p_competence_id             in      per_competences.competence_id%TYPE
,p_object_version_number     in      per_competences.object_version_number%TYPE
)
is
--
 	 l_exists  varchar2(1);

Line: 1105

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

Line: 1110

     select 'Y'
     from   per_competence_elements
     where  competence_id     = p_competence_id;

Line: 1117

     select 'Y'
     from   per_rating_levels
     where  competence_id     = p_competence_id;

Line: 1124

     select 'Y'
     from   per_competence_outcomes
     where  competence_id     = p_competence_id;

Line: 1170

end chk_competence_delete;

Line: 1200

 select	1
 from	per_rating_levels
 where	competence_id = p_competence_id;

Line: 1349

      select 1 from per_competences
      where unit_standard_id = p_unit_standard_id
      and   business_group_id = p_business_group_id
      and   p_effective_date between date_from and NVL(date_to, hr_api.g_eot);

Line: 1355

      select 1 from per_competences
      where unit_standard_id = p_unit_standard_id
      and   p_effective_date between date_from and NVL(date_to, hr_api.g_eot);

Line: 2265

Procedure insert_validate(p_rec in per_cpn_shd.g_rec_type,
			  p_effective_date in date) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 2473

End insert_validate;

Line: 2478

Procedure update_validate(p_rec in per_cpn_shd.g_rec_type
			 ,p_effective_date in date ) is
--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 2491

  chk_non_updateable_args(p_rec	=> p_rec);

Line: 2509

   ,p_called_from		=>	'UPDATE'
   );

Line: 2585

  per_cpn_bus.chk_competence_rating_update
   (p_competence_id             =>      p_rec.competence_id
   ,p_object_version_number     =>      p_rec.object_version_number
   ,p_rating_scale_id           =>      p_rec.rating_scale_id
   );

Line: 2708

End update_validate;

Line: 2713

Procedure delete_validate(p_rec in per_cpn_shd.g_rec_type) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 2726

  per_cpn_bus.chk_competence_delete
    (p_competence_id		=>	p_rec.competence_id
    ,p_object_version_number	=>	p_rec.object_version_number
    );

Line: 2732

End delete_validate;

Line: 2745

          select pbg.legislation_code, pcp.business_group_id
          from   per_business_groups pbg,
                 per_competences     pcp
          where  pcp.competence_id        = p_competence_id
            and  pbg.business_group_id(+) = pcp.business_group_id;