APPS.PER_APT_BUS SQL Statements

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

Line: 19

Procedure chk_non_updateable_args(p_rec in per_apt_shd.g_rec_type) is

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

Line: 56

end chk_non_updateable_args;

Line: 108

	select  business_group_id
	from	per_appraisal_templates apt
	where   (   (p_appraisal_template_id is null)
		  or(p_appraisal_template_id <> apt.appraisal_template_id)
		)
	and	apt.name = p_name and
    ( business_group_id is null or p_business_group_id is null or  business_group_id = p_business_group_id);

Line: 212

    	select 	'Y'
    	from   	per_appraisals apr
    	where	(   apr.appraisal_period_start_date < nvl(p_date_from,hr_api.g_sot)
   		 or apr.appraisal_period_end_date > nvl(p_date_to,hr_api.g_eot)
		)
    	and	apr.appraisal_template_id = p_appraisal_template_id;

Line: 332

	select  business_group_id
	from	per_assessment_types
	where   assessment_type_id = p_assessment_type_id
    and type = 'COMPETENCE';

Line: 344

	select  'Y'
	from	per_assessment_types
	where   assessment_type_id = p_assessment_type_id
	and     nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
	and     nvl(date_to,hr_api.g_eot)   >= nvl(p_date_to,hr_api.g_sot) ;

Line: 465

	select  business_group_id
	from	per_assessment_types
	where   assessment_type_id = p_objective_asmnt_type_id
    and type = 'OBJECTIVE';

Line: 477

	select  'Y'
	from	per_assessment_types
	where   assessment_type_id = p_objective_asmnt_type_id
	and     nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
	and     nvl(date_to,hr_api.g_eot)   >= nvl(p_date_to,hr_api.g_sot) ;

Line: 594

	select  business_group_id
	from	hr_questionnaires qsn
	where   qsn.questionnaire_template_id = p_questionnaire_template_id;

Line: 599

	-- can be updated

   	-- Cursor csr_question_update
        --   is
	-- select  'Y'
	-- from	per_appraisals apr
	--        ,per_assign_proposal_answers apa
	-- where   apr.appraisal_id          = apa.answer_for_key
	-- and     apa.type                  = 'APPRAISAL'
	-- and	apr.appraisal_template_id = p_appraisal_template_id;

Line: 612

        cursor csr_question_update1 is
          select 'Y'
            from hr_quest_answers hqa
           where hqa.type = 'APPRAISAL'
             and hqa.type_object_id in (select appraisal_id
                                          from per_appraisals apr
                                         where apr.appraisal_template_id
                                               = p_appraisal_template_id);

Line: 622

        cursor csr_question_update2 is
          select 'Y'
            from per_participants par
           where par.participation_in_table = 'PER_APPRAISALS'
             and par.participation_in_id in (select appraisal_id
                                               from per_appraisals apr
                                              where apr.appraisal_template_id
                                               = p_appraisal_template_id);

Line: 692

     open csr_question_update1;

Line: 693

     fetch csr_question_update1 into l_exists;

Line: 694

     if csr_question_update1%found then
        close csr_question_update1;

Line: 699

     close csr_question_update1;

Line: 701

     open csr_question_update2;

Line: 702

     fetch csr_question_update2 into l_exists;

Line: 703

     if csr_question_update2%found then
        close csr_question_update2;

Line: 708

     close csr_question_update2;

Line: 783

	select  business_group_id
	from	hr_questionnaires qsn
	where   qsn.questionnaire_template_id = p_ma_quest_template_id;

Line: 788

	-- can be updated

   	-- Cursor csr_question_update
        --   is
	-- select  'Y'
	-- from	per_appraisals apr
	--        ,per_assign_proposal_answers apa
	-- where   apr.appraisal_id          = apa.answer_for_key
	-- and     apa.type                  = 'APPRAISAL'
	-- and	apr.appraisal_template_id = p_appraisal_template_id;

Line: 801

        cursor csr_question_update1 is
          select 'Y'
            from hr_quest_answers hqa
           where hqa.type = 'APPRAISAL'
             and hqa.type_object_id in (select appraisal_id
                                          from per_appraisals apr
                                         where apr.appraisal_template_id
                                               = p_appraisal_template_id);

Line: 811

        cursor csr_question_update2 is
          select 'Y'
            from per_participants par
           where par.participation_in_table = 'PER_APPRAISALS'
             and par.participation_in_id in (select appraisal_id
                                               from per_appraisals apr
                                              where apr.appraisal_template_id
                                               = p_appraisal_template_id);

Line: 876

     open csr_question_update1;

Line: 877

     fetch csr_question_update1 into l_exists;

Line: 878

     if csr_question_update1%found then
        close csr_question_update1;

Line: 883

     close csr_question_update1;

Line: 885

     open csr_question_update2;

Line: 886

     fetch csr_question_update2 into l_exists;

Line: 887

     if csr_question_update2%found then
        close csr_question_update2;

Line: 892

     close csr_question_update2;

Line: 972

	select  business_group_id,type
	from	per_rating_scales
	where   rating_scale_id = p_rating_scale_id;

Line: 980

	Cursor csr_rating_scale_update1
          is
	select  overall_performance_level_id
	from	per_appraisals
	where	appraisal_template_id = p_appraisal_template_id;

Line: 990

   	Cursor csr_rating_scale_update2
          is
	select  'Y'
	from	per_appraisals apr
	       ,per_performance_ratings prt
	where   prt.appraisal_id	  = apr.appraisal_id
	and	apr.appraisal_template_id = p_appraisal_template_id;

Line: 1066

     open csr_rating_scale_update1;

Line: 1067

     fetch csr_rating_scale_update1 into l_overall_performance_level_id;

Line: 1068

     close csr_rating_scale_update1;

Line: 1075

     open csr_rating_scale_update2;

Line: 1076

     fetch csr_rating_scale_update2 into l_exists;

Line: 1077

     if csr_rating_scale_update2%found then
        close csr_rating_scale_update2;

Line: 1082

     close csr_rating_scale_update2;

Line: 1150

procedure chk_template_delete
(p_appraisal_template_id     in      per_appraisal_templates.appraisal_template_id%TYPE
,p_object_version_number     in	     per_appraisal_templates.object_version_number%TYPE
)
is

	l_exists	     varchar2(1);

Line: 1157

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

Line: 1163

	Cursor csr_template_delete
          is
	select  'Y'
	from	per_appraisals
	where	appraisal_template_id = p_appraisal_template_id;

Line: 1183

        open csr_template_delete;

Line: 1184

        fetch csr_template_delete into l_exists;

Line: 1185

	if csr_template_delete%found then
            close csr_template_delete;

Line: 1190

        close csr_template_delete;

Line: 1194

end chk_template_delete;

Line: 1233

Procedure chk_update_comp_profile
 (
  p_update_personal_comp_profile  in    per_appraisal_templates.update_personal_comp_profile%TYPE
 ,p_assessment_type_id		      in     per_appraisal_templates.assessment_type_id%TYPE
 ,p_effective_date		          in 	date
 ,p_object_version_number     in	     per_appraisal_templates.object_version_number%TYPE
 ,p_appraisal_template_id     in      per_appraisal_templates.appraisal_template_id%TYPE
 )
is
--
  l_proc	varchar2(72):=g_package||'chk_update_comp_profile';

Line: 1249

  hr_utility.trace('Inside the chk_update_comp_profile procedure');

Line: 1252

  if  ( p_assessment_type_id is null and p_update_personal_comp_profile is not null ) then
      hr_utility.set_location(l_proc, 2);

Line: 1277

  if ((l_api_updating and nvl(per_apt_shd.g_old_rec.update_personal_comp_profile,
				 hr_api.g_varchar2)
  		<> nvl(p_update_personal_comp_profile, hr_api.g_varchar2))
  or
    (NOT l_api_updating)) then
    --
    hr_utility.set_location(l_proc, 5);

Line: 1290

      ,p_lookup_code	=> p_update_personal_comp_profile
      ) then
      hr_utility.set_location(l_proc, 10);

Line: 1299

end chk_update_comp_profile;

Line: 1341

 ,p_update_personal_comp_profile  in    per_appraisal_templates.update_personal_comp_profile%TYPE
 ,p_effective_date		          in 	date
 ,p_object_version_number         in	per_appraisal_templates.object_version_number%TYPE
 ,p_appraisal_template_id     in      per_appraisal_templates.appraisal_template_id%TYPE
 )
is
--
  l_proc	varchar2(72):=g_package||'chk_comp_profile_source_type';

Line: 1357

  if  ( p_comp_profile_source_type is not null and ( p_update_personal_comp_profile is null or  p_update_personal_comp_profile = 'N')  ) then
      hr_utility.set_location(l_proc, 2);

Line: 1734

Procedure insert_validate(p_rec in per_apt_shd.g_rec_type
			 ,p_effective_date in date) is

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

Line: 1815

  if p_rec.update_personal_comp_profile is not null then
  per_apt_bus.chk_update_comp_profile
 (
  p_update_personal_comp_profile  =>    p_rec.update_personal_comp_profile
 ,p_assessment_type_id		      =>     p_rec.assessment_type_id
 ,p_effective_date		          => 	p_effective_date
 ,p_object_version_number     =>	     p_rec.object_version_number
 ,p_appraisal_template_id     =>      p_rec.appraisal_template_id
 );

Line: 1830

 ,p_update_personal_comp_profile  =>    p_rec.update_personal_comp_profile
 ,p_effective_date		          => 	p_effective_date
 ,p_object_version_number         =>	p_rec.object_version_number
 ,p_appraisal_template_id     =>      p_rec.appraisal_template_id
 );

Line: 1865

End insert_validate;

Line: 1870

Procedure update_validate(p_rec in per_apt_shd.g_rec_type
			 ,p_effective_date in date) is

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

Line: 1887

  chk_non_updateable_args(p_rec	=> p_rec);

Line: 1958

  if p_rec.update_personal_comp_profile is not null then
  per_apt_bus.chk_update_comp_profile
 (
  p_update_personal_comp_profile  =>    p_rec.update_personal_comp_profile
 ,p_assessment_type_id		      =>     p_rec.assessment_type_id
 ,p_effective_date		          => 	p_effective_date
 ,p_object_version_number     =>	     p_rec.object_version_number
 ,p_appraisal_template_id     =>      p_rec.appraisal_template_id
 );

Line: 1973

 ,p_update_personal_comp_profile  =>    p_rec.update_personal_comp_profile
 ,p_effective_date		          => 	p_effective_date
 ,p_object_version_number         =>	p_rec.object_version_number
 ,p_appraisal_template_id     =>      p_rec.appraisal_template_id
 );

Line: 2007

End update_validate;

Line: 2012

Procedure delete_validate(p_rec in per_apt_shd.g_rec_type) is

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

Line: 2021

  per_apt_bus.chk_template_delete
  (p_appraisal_template_id     =>	p_rec.appraisal_template_id
  ,p_object_version_number     =>	p_rec.object_version_number
  );

Line: 2027

End delete_validate;

Line: 2039

          select legislation_code
          from   per_business_groups      pbg,
                 per_appraisal_templates  pat
          where  pat.appraisal_template_id  = p_appraisal_template_id
            and  pbg.business_group_id      = pat.business_group_id;