[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(pce.proficiency_level_id,'','',rtl.step_value||'-'||rtx.name) Old_Level
FROM PER_COMPETENCE_ELEMENTS pce,
PER_RATING_LEVELS rtl ,
PER_RATING_LEVELS_TL rtx,
PER_APPRAISALS apr
WHERE pce.type = 'ASSESSMENT'
and pce.object_name = 'ASSESSOR_ID'
and object_id = p_main_appraiser_id
AND pce.competence_id = p_competence_id
AND pce.proficiency_level_id = rtl.rating_level_id (+)
AND rtl.rating_level_id = rtx.rating_level_id (+)
AND rtx.language(+) = userenv('LANG')
AND (apr.appraisee_person_id, apr.appraisal_period_end_date) IN
(SELECT prv.appraisee_person_id, MAX(prv.appraisal_period_end_date)
FROM PER_APPRAISALS prv,
PER_APPRAISALS cur,
PER_COMPETENCE_ELEMENTS cpn
WHERE prv.appraisee_person_id = p_person_id
AND prv.appraisal_period_end_date < cur.appraisal_period_start_date
AND cur.appraisal_id = p_appraisal_id
AND cpn.object_name = 'APPRAISAL_ID'
AND cpn.object_id = prv.appraisal_id
GROUP BY prv.appraisee_person_id
)
AND (ASSESSMENT_ID, PCE.COMPETENCE_ID) IN
(select assessment_id, competence_id
from PER_COMPETENCE_ELEMENTS
where competence_id = p_competence_id
AND type = 'ASSESSMENT'
AND object_name = 'APPRAISAL_ID'
AND object_id = apr.appraisal_id
INTERSECT
SELECT assessment_id, COMPETENCE_ID
FROM PER_COMPETENCE_ELEMENTS
WHERE competence_id = p_competence_id
AND type = 'ASSESSMENT'
AND object_name = 'ASSESSOR_ID'
AND object_id = p_main_appraiser_id);
SELECT decode(pce.proficiency_level_id,'','',rtl.step_value||'-'||rtx.name) New_Level
FROM PER_COMPETENCE_ELEMENTS pce,
PER_RATING_LEVELS rtl ,
PER_RATING_LEVELS_TL rtx
WHERE pce.type = 'ASSESSMENT'
AND object_name = 'ASSESSOR_ID'
AND pce.competence_id = p_competence_id
AND pce.proficiency_level_id = rtl.rating_level_id (+)
AND rtl.rating_level_id = rtx.rating_level_id (+)
AND rtx.language(+) = userenv('LANG')
AND (pce.assessment_id, pce.competence_id) IN
(SELECT assessment_id, competence_id
FROM PER_COMPETENCE_ELEMENTS
WHERE competence_id = p_competence_id
AND type = 'ASSESSMENT'
AND object_name = 'APPRAISAL_ID'
AND object_id = p_appraisal_id
INTERSECT
SELECT assessment_id, competence_id
FROM PER_COMPETENCE_ELEMENTS
WHERE competence_id = p_competence_id
AND type = 'ASSESSMENT'
AND object_name = 'ASSESSOR_ID'
AND object_id = p_main_appraiser_id);
Description : Function to chech whether the given competence has been updates
by an appraisal
Preconditions : None
In Parameters : p_competence_id IN NUMBER
p_appraisal_id IN NUMBER,
p_person_id IN NUBMER
Post Sucess : Returns the 'Y' If the competence being updated by appraisal
else returns 'N'
Post Failure : 'NULL'
***************************************************************************/
--
FUNCTION get_competence_appraisal_flag(p_competence_id IN NUMBER,
p_appraisal_id IN NUMBER,
p_person_id IN NUMBER)
RETURN VARCHAR2
IS
--
Cursor c_flag(p_competence_id IN NUMBER, p_appraisal_id IN NUMBER, p_person_id IN NUMBER) is
SELECT 'Y' flag
FROM PER_COMPETENCE_ELEMENTS pce,
PER_APPRAISALS apr
WHERE apr.appraisal_id = p_appraisal_id
AND pce.competence_id = p_competence_id
AND pce.object_id = p_appraisal_id
AND apr.appraisee_person_id = p_person_id
AND PCE.object_name = 'APPRAISAL_ID'
AND APR.appraisal_system_status = 'COMPLETED';