HRA_MGR_OS_RATINGS

Details

Schema: FUSION
Object owner: HRA
Object type: VIEW

Columns

Name
EVALUATION_ID BUSINESS_GROUP_ID REVIEW_PERIOD_ID DOC_TYPE_ID TEMPLATE_DEFN_ID TMPL_PERIOD_ID WORKER_ID ASSIGNMENT_ID MANAGER_ID START_DATE END_DATE RATINGS_UPDATEABLE STEP_COMPLETION_DATE OVERALL_RATING_LEVEL_ID OVERALL_RATING_DESC OVERALL_RATING_SHORT_DESC OVERALL_NUMERIC_RATING CALIBRATION_STATUS CALCULATED_OVERALL_RATING OVERALL_COMMENTS OVERALL_STATUS_NUM OVERALL_STATUS

Name

EVALUATION_ID

BUSINESS_GROUP_ID

REVIEW_PERIOD_ID

DOC_TYPE_ID

TEMPLATE_DEFN_ID

TMPL_PERIOD_ID

WORKER_ID

ASSIGNMENT_ID

MANAGER_ID

START_DATE

END_DATE

RATINGS_UPDATEABLE

STEP_COMPLETION_DATE

OVERALL_RATING_LEVEL_ID

OVERALL_RATING_DESC

OVERALL_RATING_SHORT_DESC

OVERALL_NUMERIC_RATING

CALIBRATION_STATUS

CALCULATED_OVERALL_RATING

OVERALL_COMMENTS

OVERALL_STATUS_NUM

OVERALL_STATUS

Query

SQL_Statement
SELECT EVAL.EVALUATION_ID, EVAL.BUSINESS_GROUP_ID, EVAL.REVIEW_PERIOD_ID, TMPL.DOC_TYPE_ID , EVAL.TEMPLATE_DEFN_ID , EVAL.TMPL_PERIOD_ID , EVAL.WORKER_ID , EVAL.ASSIGNMENT_ID, EVAL.MANAGER_ID , EVAL.START_DATE, EVAL.END_DATE , DECODE(EVAL.USE_CALC_RATINGS_FLAG, 'Y', 'N', 'Y') AS RATINGS_UPDATEABLE, STEP.STEP_COMPLETION_DATE, EVRT.PERFORMANCE_RATING_ID AS OVERALL_RATING_LEVEL_ID, RLVL.RATING_DESCRIPTION OVERALL_RATING_DESC, RLVL.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC, RLVL.NUMERIC_RATING OVERALL_NUMERIC_RATING, EVAL.CALIBRATION_STATUS, EVRT.CALCULATED_RATING AS CALCULATED_OVERALL_RATING, EVRT.COMMENTS AS OVERALL_COMMENTS, ( CASE WHEN (EVAL.STATUS_CODE <> 'COMP') THEN DECODE(STEP.STEP_STATUS,'COMP',2,1) WHEN (EVAL.STATUS_CODE = 'COMP') THEN 3 END) AS OVERALL_STATUS_NUM , ( CASE WHEN (EVAL.STATUS_CODE <> 'COMP') THEN DECODE(STEP.STEP_STATUS,'COMP','Manager Complete','In Progress') WHEN (EVAL.STATUS_CODE = 'COMP') THEN 'Final' END) AS OVERALL_STATUS FROM HRA_EVALUATIONS EVAL , HRA_TMPL_DEFNS_B TMPL, HRA_EVAL_PARTICIPANTS PTCP, HRA_EVAL_RATINGS EVRT , HRA_EVAL_SECTIONS EVSC, HRT_RATING_LEVELS_VL RLVL , HRA_EVAL_STEPS STEP WHERE EVAL.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID AND EVAL.STATUS_CODE <> 'CANCELLED' AND EVAL.EVALUATION_ID = PTCP.EVALUATION_ID AND 'MANAGER' = PTCP.ROLE_TYPE_CODE AND PTCP.EVAL_PARTICIPANT_ID= EVRT.EVAL_PARTICIPANT_ID AND EVAL.EVALUATION_ID = EVSC.EVALUATION_ID AND EVSC.SECTION_TYPE_CODE = 'OS' AND EVSC.EVAL_SECTION_ID = EVRT.REFERENCE_ID(+) AND 'SECTION' = EVRT.REFERENCE_TYPE(+) AND EVRT.PERFORMANCE_RATING_ID = RLVL.RATING_LEVEL_ID(+) AND EVRT.BUSINESS_GROUP_ID = RLVL.BUSINESS_GROUP_ID(+) AND EVAL.EVALUATION_ID = STEP.EVALUATION_ID AND EVRT.EVAL_PARTICIPANT_ID = STEP.EVAL_PARTICIPANT_ID AND STEP.STEP_CODE = 'MGREVAL'

SQL_Statement

SELECT EVAL.EVALUATION_ID,

EVAL.BUSINESS_GROUP_ID,

EVAL.REVIEW_PERIOD_ID,

TMPL.DOC_TYPE_ID ,

EVAL.TEMPLATE_DEFN_ID ,

EVAL.TMPL_PERIOD_ID ,

EVAL.WORKER_ID ,

EVAL.ASSIGNMENT_ID,

EVAL.MANAGER_ID ,

EVAL.START_DATE,

EVAL.END_DATE ,

DECODE(EVAL.USE_CALC_RATINGS_FLAG, 'Y', 'N', 'Y') AS RATINGS_UPDATEABLE,

STEP.STEP_COMPLETION_DATE,

EVRT.PERFORMANCE_RATING_ID AS OVERALL_RATING_LEVEL_ID,

RLVL.RATING_DESCRIPTION OVERALL_RATING_DESC,

RLVL.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC,

RLVL.NUMERIC_RATING OVERALL_NUMERIC_RATING,

EVAL.CALIBRATION_STATUS,

EVRT.CALCULATED_RATING AS CALCULATED_OVERALL_RATING,

EVRT.COMMENTS AS OVERALL_COMMENTS,

(

CASE

WHEN (EVAL.STATUS_CODE <> 'COMP')

THEN DECODE(STEP.STEP_STATUS,'COMP',2,1)

WHEN (EVAL.STATUS_CODE = 'COMP')

THEN 3

END) AS OVERALL_STATUS_NUM ,

(

CASE

WHEN (EVAL.STATUS_CODE <> 'COMP')

THEN DECODE(STEP.STEP_STATUS,'COMP','Manager Complete','In Progress')

WHEN (EVAL.STATUS_CODE = 'COMP')

THEN 'Final'

END) AS OVERALL_STATUS

FROM HRA_EVALUATIONS EVAL ,

HRA_TMPL_DEFNS_B TMPL,

HRA_EVAL_PARTICIPANTS PTCP,

HRA_EVAL_RATINGS EVRT ,

HRA_EVAL_SECTIONS EVSC,

HRT_RATING_LEVELS_VL RLVL ,

HRA_EVAL_STEPS STEP

WHERE EVAL.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID

AND EVAL.STATUS_CODE <> 'CANCELLED'

AND EVAL.EVALUATION_ID = PTCP.EVALUATION_ID

AND 'MANAGER' = PTCP.ROLE_TYPE_CODE

AND PTCP.EVAL_PARTICIPANT_ID= EVRT.EVAL_PARTICIPANT_ID

AND EVAL.EVALUATION_ID = EVSC.EVALUATION_ID

AND EVSC.SECTION_TYPE_CODE = 'OS'

AND EVSC.EVAL_SECTION_ID = EVRT.REFERENCE_ID(+)

AND 'SECTION' = EVRT.REFERENCE_TYPE(+)

AND EVRT.PERFORMANCE_RATING_ID = RLVL.RATING_LEVEL_ID(+)

AND EVRT.BUSINESS_GROUP_ID = RLVL.BUSINESS_GROUP_ID(+)

AND EVAL.EVALUATION_ID = STEP.EVALUATION_ID

AND EVRT.EVAL_PARTICIPANT_ID = STEP.EVAL_PARTICIPANT_ID

AND STEP.STEP_CODE = 'MGREVAL'