APPS.HR_APPRAISALS_UTIL_SS SQL Statements

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

Line: 7

         select prl1.step_value prof_value, prl2.step_value perf_value, prl3.step_value weigh_value
         from per_competence_elements pce, per_rating_levels_vl prl1, per_rating_levels_vl prl2,
              per_rating_levels_vl prl3
         where pce.assessment_id = p_assessment_id and pce.competence_id = p_competence_id
         and pce.type='ASSESSMENT' and pce.object_name='ASSESSOR_ID'
         and pce.proficiency_level_id = prl1.rating_level_id(+)
         and pce.rating_level_id = prl2.rating_level_id (+)
         and pce.weighting_level_id = prl3.rating_level_id(+)
         and (prl1.step_value is not null or prl2.step_value is not null or
              prl3.step_value is not null)
	 and exists (select * from per_participants pp where pp.PARTICIPATION_STATUS='COMPLETED'
	 AND pp.PARTICIPATION_IN_COLUMN='APPRAISAL_ID'
	 AND pp.PARTICIPATION_IN_ID=(select APPRAISAL_ID from per_assessments
	 where assessment_id=p_assessment_id)
	 AND pp.PERSON_ID=pce.object_id );

Line: 28

         select prl.step_value perf_value, pos.weighting_percent weigh_percent
         from per_performance_ratings ppr, per_rating_levels_vl prl, per_assessments pas,
          per_objectives pos
         where pas.assessment_id = p_assessment_id
	 and ppr.objective_id = p_objective_id
	 and ppr.appraisal_id = pas.appraisal_id
         and ppr.performance_level_id = prl.rating_level_id
         and pos.appraisal_id = ppr.appraisal_id
         and pos.objective_id =  ppr.objective_id
 	 and exists (select * from per_participants pp where pp.PARTICIPATION_STATUS='COMPLETED'
	 AND pp.PARTICIPATION_IN_COLUMN='APPRAISAL_ID'
	 AND pp.PARTICIPATION_IN_ID=(select APPRAISAL_ID from per_assessments
	 where assessment_id=p_assessment_id)
	 AND pp.PERSON_ID=ppr.PERSON_ID);

Line: 50

	select pap.appraisal_id,
	       pap.assignment_id,
	       pap.assignment_business_group_id,
	       pap.assignment_organization_id,
	       pap.appraisee_person_id,
               pap.appraisal_template_id,
               pap.system_type,
               pap.type
	from per_appraisals pap,
	     per_assessments pas
	where pas.assessment_id = p_assessment_id
	and pap.appraisal_id = pas.appraisal_id;

Line: 120

  SELECT ff.formula_id, ff.formula_name
  FROM   ff_formulas_f ff
  WHERE  l_effective_date BETWEEN
         ff.effective_start_date AND ff.effective_end_date
  AND    ff.formula_id = p_line_formula_id;

Line: 268

  SELECT ff.formula_id, ff.formula_name
  FROM   ff_formulas_f ff
  WHERE  l_effective_date BETWEEN
         ff.effective_start_date AND ff.effective_end_date
  AND    ff.formula_id = p_final_formula_id;

Line: 390

          select line_score_formula, line_score_formula_id from per_assessment_types, per_assessments
          where per_assessments.assessment_id = p_assessment_id
          and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;

Line: 444

          select pat.line_score_formula,
	         pat.line_score_formula_id,
	         pas.assessment_id
	  from per_assessment_types pat,
	       per_assessments pas
          where pas.appraisal_id = p_appraisal_id
          and pas.assessment_type_id = pat.assessment_type_id
	  and pat.type = 'OBJECTIVE';

Line: 501

          select total_score_formula from per_assessment_types, per_assessments
          where per_assessments.assessment_id = p_assessment_id
          and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;

Line: 505

          select distinct pce.competence_id
          from per_competence_elements pce
          where pce.assessment_id = p_assessment_id and pce.type='ASSESSMENT'
          and pce.object_name = 'ASSESSOR_ID';

Line: 510

          select po.objective_id
          from per_objectives po,
	       per_assessments pa
          where pa.assessment_id = p_assessment_id
          and po.appraisal_id = pa.appraisal_id;

Line: 516

          select nvl(pst.type, 'COMPETENCE'),
                 pa.appraisal_id
          from per_assessment_types pst,
	       per_assessments pa
          where pa.assessment_id = p_assessment_id
          and pst.assessment_type_id = pa.assessment_type_id;

Line: 577

          select pa.assessment_id,
                 nvl(pst.type, 'COMPETENCE')
          from per_assessment_types pst,
	       per_assessments pa
          where pa.appraisal_id = p_appraisal_id
          and pst.assessment_type_id = pa.assessment_type_id;

Line: 584

      SELECT  'Y'
      FROM    dual
      WHERE   EXISTS
          (
           SELECT  'Y'
           FROM    per_competence_elements pce
           WHERE   pce.assessment_id = p_assessment_id
           AND     pce.type = 'ASSESSMENT'
           AND     pce.object_name = 'ASSESSOR_ID');

Line: 633

         SELECT wf.NAME role_name
           FROM wf_roles wf
          WHERE wf.orig_system = 'PER' AND wf.orig_system_id = person_id;

Line: 639

         SELECT NVL (GLOBAL_NAME, first_name || ', ' || last_name)
           FROM per_all_people_f
          WHERE person_id = p_person_id
            AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Line: 734

SELECT
  nvl(nvl(current_employee_flag
         ,current_npw_flag)
     ,'N')
FROM
  per_people_f
WHERE person_id = p_person_id
  AND sysdate BETWEEN effective_start_date AND effective_end_date;

Line: 762

SELECT
  nvl(nvl(current_employee_flag
         ,current_npw_flag)
     ,'N')
FROM
  per_people_f
WHERE person_id = p_person_id
  AND sysdate BETWEEN effective_start_date AND effective_end_date;

Line: 788

SELECT
  nvl(nvl(current_employee_flag
         ,current_npw_flag)
     ,'N')
FROM
  per_people_f
WHERE person_id = p_approver_id
  AND sysdate BETWEEN effective_start_date AND effective_end_date;

Line: 824

	SELECT
	  nvl(nvl(current_employee_flag
		 ,current_npw_flag)
	     ,'N')
	FROM
	  per_people_f
	WHERE person_id = p_approver_id
	  AND sysdate BETWEEN effective_start_date AND effective_end_date;

Line: 834

	 SELECT wf.orig_system_id from wf_roles wf
	 WHERE wf.orig_system = 'PER'
	 AND wf.name = p_role;

Line: 882

select substr(system_params,instr(system_params,'pItemKey=')+9) into l_itemkey from per_appraisals where appraisal_id=p_appraisal_id;