APPS.PER_CEL_BUS SQL Statements

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

Line: 20

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

Line: 195

end check_non_updateable_args;

Line: 1118

  select business_group_id,date_from,date_to
  from per_jobs_v
  where  p_job_id = job_id;

Line: 1129

  select business_group_id, date_from, date_to
  from  per_valid_grades
  where  p_valid_grade_id = valid_grade_id;

Line: 1141

  select actd.business_group_id,
         actv.start_date,
         nvl(actv.end_date, hr_api.g_eot)
  from   ota_activity_versions actv,
         ota_activity_definitions actd
  where  p_activity_version_id = actv.activity_version_id
  and    actv.activity_id      = actd.activity_id;

Line: 1157

  select business_group_id,effective_start_date, effective_end_date
  from  per_people_f
  where  p_person_id = person_id;

Line: 1169

  select min(date_of_birth)
  from  per_people_f
  where  p_person_id = person_id;

Line: 1186

  select business_group_id, date_effective, hr_general.get_position_date_end(p_position_id)
  from   hr_positions_f
  where  p_position_id = position_id;

Line: 1199

  select business_group_id, date_from, date_to
  from   per_competences
  where  p_competence_id = competence_id;

Line: 1214

  select business_group_id
  from   per_competences
  where  p_competence_id = competence_id;

Line: 1222

  select business_group_id,date_from,date_to
  from   hr_organization_units
  where  organization_id	= p_organization_id;

Line: 1232

  select business_group_id
  from   per_assessments
  where  p_assessment_id = assessment_id;

Line: 1240

  select business_group_id
  from   per_assessment_types
  where  p_assessment_type_id = assessment_type_id;

Line: 1248

  select business_group_id
  from   per_rating_levels
  where  c_level_id = rating_level_id;

Line: 1256

  select business_group_id
  from   per_competence_elements
  where  p_parent_competence_element_id =
         competence_element_id;

Line: 1265

  select qualification_type_id
  from   per_qualification_types
  where  p_qualification_type_id =
         qualification_type_id;

Line: 2083

  select party_id
  from    per_all_people_f per
    where   per.person_id = p_rec.person_id
    and     p_effective_date
    between per.effective_start_date
    and     nvl(per.effective_end_date,hr_api.g_eot);

Line: 2091

  select party_id
  from hz_parties hzp
  where hzp.party_id = p_rec.party_id;

Line: 2232

  select step_value
  --
  --  This is when the parent of the proficiency is the competence.
  --
  -- ngundura changes for pa requirements

  from   per_rating_levels
  where  p_competence_id = competence_id
  and    p_prof_id = rating_level_id
--  where  p_business_group_id  = business_group_id + 0
--  and    p_competence_id = competence_id
--  and    p_prof_id = rating_level_id

  union
  --
  -- This is when the parent of the proficiency level is the rating scale.
  --

  select ral.step_value
  from   per_rating_levels  ral,
	 per_rating_scales  ras,
	 per_competences    comp
  where  p_competence_id          = comp.competence_id
  and    ras.rating_scale_id      = comp.rating_scale_id
  and    ral.rating_scale_id      = ras.rating_scale_id
  and    p_prof_id                = ral.rating_level_id;

Line: 2488

  select ast.rating_scale_id, ast.weighting_scale_id
  from   per_assessment_types ast,
	 per_assessments      ass,
	 per_rating_levels    ral
  where  ass.assessment_id	=  p_assessment_id
  and    ass.assessment_type_id =  ast.assessment_type_id
  and    decode(c_scale_type, 'PERFORMANCE', ast.rating_scale_id,
	ast.weighting_scale_id) = ral.rating_scale_id
  and	 ral.rating_level_id    = c_rate_level_id
  and    nvl(ast.business_group_id +0,p_business_group_id)  = p_business_group_id
  and    nvl(ast.business_group_id,ass.business_group_id+0) = ass.business_group_id+0
  and	 ass.business_group_id+0  = NVL(ral.business_group_id , ass.business_group_id);

Line: 2711

   open csr_check_dates for 'select null from per_competence_elements where '
           || v_check_type || ' = :p_key_id'
           || ' and competence_id =  :p_competence_id'
           || ' and nvl(business_group_id,-999) = nvl(:p_business_group_id,-999)'
           || ' and :p_effective_date_from <= nvl(effective_date_to,:eot1)'
           || ' and nvl(:p_effective_date_to,:eot2) >= effective_date_from'
           || ' and (competence_element_id <> :p_competence_element_id1  or :p_competence_element_id2 is null)'
           || ' and decode(:p_valid_grade_null,''Y'',valid_grade_id,null) is null'
           using p_key_id,p_competence_id,p_business_group_id,p_effective_date_from,
			hr_api.g_eot,p_effective_date_to, hr_api.g_eot,
				p_competence_element_id,p_competence_element_id,p_valid_grade_null;

Line: 3092

  select ast.rating_scale_id, ast.weighting_scale_id
  from   per_rating_levels ral,
         per_assessment_types ast,

         per_rating_scales    ras
  where  ast.assessment_type_id = c_ass_type_id
--  and    p_business_group_id +0 = ast.business_group_id
--  and    p_business_group_id +0 = ral.business_group_id
--  and    p_business_group_id +0 = ras.business_group_id
  and    ral.rating_scale_id    = ras.rating_scale_id
  and    ras.rating_scale_id    = ast.rating_scale_id
  and    ras.type in ( 'PERFORMANCE', 'WEIGHTING');

Line: 3111

  select assessment_type_id
  from   per_assessments
  where  p_business_group_id + 0 = business_group_id
  and    p_assessment_id = assessment_id;

Line: 3355

select null
from   per_competence_elements
where  competence_element_id	= p_parent_competence_element_id
and    nvl(business_group_id,-1) = nvl(p_business_group_id,-1)
and    type			= 'ASSESSMENT_GROUP';

Line: 3366

  select null from per_competence_elements cel
  where cel.competence_id = p_competence_id
  and   cel.qualification_type_id = p_qualification_type_id
  and   cel.type = 'QUALIFICATION'
  and   cel.qualification_type_id = p_qualification_type_id
  and   (p_effective_date_from <= nvl(cel.effective_date_to, hr_api.g_eot)
        and NVL(p_effective_date_to, hr_api.g_eot) >= cel.effective_date_from);

Line: 3375

  select null from per_competence_elements cel
  where cel.competence_element_id <> p_competence_element_id
  and   cel.competence_id = p_competence_id
  and   cel.qualification_type_id = p_qualification_type_id
  and   cel.type = 'QUALIFICATION'
  and   cel.qualification_type_id = p_qualification_type_id
  and   (p_effective_date_from <= nvl(cel.effective_date_to, hr_api.g_eot)
        and NVL(p_effective_date_to, hr_api.g_eot) >= cel.effective_date_from);

Line: 4141

      l_sql_stmt := 'SELECT NULL '||
                    'FROM   per_competence_elements '||
                    'WHERE  business_group_id = :p_business_group_id '||
                    'AND    type = :p_type ';

Line: 4378

         select 'x'
         from per_competence_elements
         where type = 'QUALIFICATION'
	 and   competence_id = p_competence_id
         and   qualification_type_id = p_qualification_type_id
         and   p_effective_date between effective_date_from
               and nvl(effective_date_to,hr_api.g_eot);

Line: 4469

procedure chk_comp_element_delete
  ( p_competence_element_id
     in per_competence_elements.competence_element_id%TYPE
   ,p_business_group_id
     in per_competence_elements.business_group_id%TYPE
   ,p_parent_competence_element_id
     in per_competence_elements.parent_competence_element_id%TYPE
   ,p_type
     in per_competence_elements.type%TYPE
   ,p_competence_type
     in per_competence_elements.competence_type%TYPE
   ,p_group_competence_type
     in per_competence_elements.group_competence_type%TYPE
   ,p_assessment_id
     in per_competence_elements.assessment_id%TYPE
   ,p_assessment_type_id
     in per_competence_elements.assessment_type_id%TYPE
   ,p_competence_id
     in per_competence_elements.competence_id%TYPE
   ) is
--
   l_proc              varchar2(72):= g_package||'chk_comp_element_delete';

Line: 4500

select null
from per_competence_elements
where type = 'ASSESSMENT'
and   competence_id = p_competence_id
and assessment_id in
        (Select asn.assessment_id
         From per_assessments asn
         Where asn.assessment_type_id =
                (Select assessment_type_id
                 From per_competence_elements
                 Where competence_element_id =
                        (Select parent_competence_element_id
                         From per_competence_elements
                         Where competence_element_id=p_competence_element_id
                        )
               )
        )
;

Line: 4522

select null
from   per_competence_elements
where  parent_competence_element_id = p_competence_element_id
and    business_group_id	    = p_business_group_id;

Line: 4531

select null
from   per_competence_elements comp1
where  comp1.type = 'ASSESSMENT_COMPETENCE'
and    comp1.parent_competence_element_id is not null
and    comp1.business_group_id     = p_business_group_id
and    comp1.competence_id = p_competence_id
and    exists (select null
       from    per_competence_elements comp2
       where   comp2.competence_element_id =
	       comp1.parent_competence_element_id
       and     comp1.business_group_id =

	       comp2.business_group_id
       and     comp2.group_competence_type =
	       p_competence_type);

Line: 4551

   select 'x' from per_comp_element_outcomes
   where competence_element_id = p_competence_element_id;

Line: 4646

end chk_comp_element_delete;

Line: 4922

Procedure insert_validate(p_rec in out nocopy per_cel_shd.g_rec_type,
			  p_effective_date  in Date) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 5315

End insert_validate;

Line: 5320

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

Line: 5361

  per_cel_bus.check_non_updateable_args
    (p_rec              =>p_rec);

Line: 5696

End update_validate;

Line: 5701

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

Line: 5712

  chk_comp_element_delete

     (p_competence_element_id	=> per_cel_shd.g_old_rec.competence_element_id
     ,p_business_group_id	=> per_cel_shd.g_old_rec.business_group_id
     ,p_parent_competence_element_id
     			=> per_cel_shd.g_old_rec.parent_competence_element_id
     ,p_type		=> per_cel_shd.g_old_rec.type
     ,p_competence_type	=> per_cel_shd.g_old_rec.competence_type
     ,p_assessment_id	=> per_cel_shd.g_old_rec.assessment_id
     ,p_assessment_type_id	=> per_cel_shd.g_old_rec.assessment_type_id
     ,p_competence_id		=> per_cel_shd.g_old_rec.competence_id
     ,p_group_competence_type	=> per_cel_shd.g_old_rec.group_competence_type
     );

Line: 5728

End delete_validate;

Line: 5740

          select legislation_code
          from   per_business_groups     pbg,
                 per_competence_elements pce
          where  pce.competence_element_id = p_competence_element_id
            and  pbg.business_group_id     = pce.business_group_id;

Line: 5759

  Select 'Y' into l_business_group_flag
  from per_competence_elements
  where competence_element_id = p_competence_element_id
  and business_group_id is null;