HRA_SECTION_MGR_RATINGS

Details

Schema: FUSION
Object owner: HRA
Object type: VIEW

Columns

Name
DOC_TYPE_ID TEMPLATE_DEFN_ID TEMPLATE_NAME TMPL_PERIOD_ID CUSTOMARY_NAME EVALUATION_ID BUSINESS_GROUP_ID ASSIGNMENT_ID MANAGER_DISPLAY_NAME WORKER_DISPLAY_NAME START_DATE END_DATE STEP_COMPLETION_DATE CALIBRATION_STATUS OVERALL_RATING CALCULATED_OVERALL_RATING OVERALL_COMMENTS OVERALL_STATUS OVERALL_GOAL_RATING CALCULATED_OVERALL_GOAL_RATING OVERALL_COMP_RATING CALCULATED_OVERALL_COMP_RATING OVERALL_STATUS_NUM OVERALL_RATING_DESC OVERALL_RATING_SHORT_DESC OVERALL_NUMERIC_RATING OVERALL_RATING_GOAL_DESC OVERALL_RATING_GOAL_SHORT_DESC OVERALL_NUMERIC_GOAL_RATING OVERALL_RATING_COMP_DESC OVERALL_RATING_COMP_SHORT_DESC OVERALL_NUMERIC_COMP_RATING COMP_RATING_MODEL_ID GOAL_RATING_MODEL_ID OVERALL_RATING_MODEL_ID RATINGS_UPDATEABLE OVERALL_DEVGOAL_RATING CALC_OVERALL_DEVGOAL_RATING OVERALL_RATING_DEVGOAL_DESC OVERALL_RATING_DEVGOAL_SH_DESC OVERALL_NUMERIC_DEVGOAL_RATING DEVGOAL_RATING_MODEL_ID COMP_TMPL_SECTION_ID

Name

DOC_TYPE_ID

TEMPLATE_DEFN_ID

TEMPLATE_NAME

TMPL_PERIOD_ID

CUSTOMARY_NAME

EVALUATION_ID

BUSINESS_GROUP_ID

ASSIGNMENT_ID

MANAGER_DISPLAY_NAME

WORKER_DISPLAY_NAME

START_DATE

END_DATE

STEP_COMPLETION_DATE

CALIBRATION_STATUS

OVERALL_RATING

CALCULATED_OVERALL_RATING

OVERALL_COMMENTS

OVERALL_STATUS

OVERALL_GOAL_RATING

CALCULATED_OVERALL_GOAL_RATING

OVERALL_COMP_RATING

CALCULATED_OVERALL_COMP_RATING

OVERALL_STATUS_NUM

OVERALL_RATING_DESC

OVERALL_RATING_SHORT_DESC

OVERALL_NUMERIC_RATING

OVERALL_RATING_GOAL_DESC

OVERALL_RATING_GOAL_SHORT_DESC

OVERALL_NUMERIC_GOAL_RATING

OVERALL_RATING_COMP_DESC

OVERALL_RATING_COMP_SHORT_DESC

OVERALL_NUMERIC_COMP_RATING

COMP_RATING_MODEL_ID

GOAL_RATING_MODEL_ID

OVERALL_RATING_MODEL_ID

RATINGS_UPDATEABLE

OVERALL_DEVGOAL_RATING

CALC_OVERALL_DEVGOAL_RATING

OVERALL_RATING_DEVGOAL_DESC

OVERALL_RATING_DEVGOAL_SH_DESC

OVERALL_NUMERIC_DEVGOAL_RATING

DEVGOAL_RATING_MODEL_ID

COMP_TMPL_SECTION_ID

Query

SQL_Statement
WITH EvalGoalSectionIds AS ( SELECT EvaluationEO.EVALUATION_ID, MIN(EvalSectionGoalEO.EVAL_SECTION_ID) KEEP(DENSE_RANK FIRST ORDER BY GoalPlanEO.PRIMARY_GOAL_PLAN_FLAG DESC, TmplSectionEO.SEQUENCE_NUMBER ASC, GoalPlanEO.GOAL_PLAN_NAME ASC) EVAL_SECTION_ID, MIN(EvaluationEO.ENABLE_SYNC_FLAG) ENABLE_SYNC_FLAG FROM HRA_EVAL_SECTIONS EvalSectionGoalEO, HRA_TMPL_SECTIONS TmplSectionEO, HRG_GOAL_PLANS_VL GoalPlanEO, HRG_GOAL_PLN_ASSIGNMENTS GoalPlanAssignmentEO, HRA_EVALUATIONS EvaluationEO WHERE EvaluationEO.EVALUATION_ID = EvalSectionGoalEO.EVALUATION_ID AND EvaluationEO.ENABLE_SYNC_FLAG = 'Y' AND EvalSectionGoalEO.SECTION_TYPE_CODE = 'GOAL' AND EvalSectionGoalEO.GOAL_PLAN_ASSIGNMENT_ID = GoalPlanAssignmentEO.GOAL_PLAN_ASSIGNMENT_ID AND GoalPlanAssignmentEO.GOAL_PLAN_ID = GoalPlanEO.GOAL_PLAN_ID AND EvalSectionGoalEO.TMPL_SECTION_ID = TmplSectionEO.SECTION_ID AND EvalSectionGoalEO.SECTION_TYPE_CODE = TmplSectionEO.SECTION_TYPE_CODE AND EvalSectionGoalEO.BUSINESS_GROUP_ID = TmplSectionEO.BUSINESS_GROUP_ID GROUP BY EvaluationEO.EVALUATION_ID UNION SELECT EvaluationEO.EVALUATION_ID, EvalSectionGoalEO.EVAL_SECTION_ID, EvaluationEO.ENABLE_SYNC_FLAG FROM HRA_EVAL_SECTIONS EvalSectionGoalEO, HRA_EVALUATIONS EvaluationEO WHERE EvaluationEO.EVALUATION_ID = EvalSectionGoalEO.EVALUATION_ID AND EvaluationEO.ENABLE_SYNC_FLAG = 'N' AND EvalSectionGoalEO.SECTION_TYPE_CODE = 'GOAL' ), PerformanceGoalSections AS (SELECT EvaluationEO.BUSINESS_GROUP_ID, EvaluationEO.TMPL_PERIOD_ID, EvaluationEO.EVALUATION_ID, EvalSectionGoalEO.SECTION_RATING_MODEL_ID, EvalRatingGoalEO.PERFORMANCE_RATING_ID, EvalRatingGoalEO.CALCULATED_RATING, EvalRatingGoalEO.EVAL_PARTICIPANT_ID, EvalSectionGoalEO.REFERENCE_SECTION_ID, EvalSectionGoalEO.TMPL_SECTION_ID FROM HRA_EVAL_SECTIONS EvalSectionGoalEO, HRA_EVAL_RATINGS EvalRatingGoalEO, HRA_TMPL_SECTIONS TmplSectionEO, HRA_EVALUATIONS EvaluationEO, EvalGoalSectionIds WHERE EvalSectionGoalEO.BUSINESS_GROUP_ID (+) = EvaluationEO.BUSINESS_GROUP_ID AND EvalSectionGoalEO.EVALUATION_ID (+) = EvaluationEO.EVALUATION_ID AND EvaluationEO.EVALUATION_ID = EvalGoalSectionIds.EVALUATION_ID AND EvalSectionGoalEO.EVAL_SECTION_ID (+) = EvalGoalSectionIds.EVAL_SECTION_ID AND EvalSectionGoalEO.SECTION_TYPE_CODE (+) = 'GOAL' AND EvalRatingGoalEO.REFERENCE_ID (+) = EvalSectionGoalEO.EVAL_SECTION_ID AND EvalRatingGoalEO.REFERENCE_TYPE (+) = 'SECTION' AND EvalSectionGoalEO.TMPL_SECTION_ID = TmplSectionEO.SECTION_ID (+) AND EvalSectionGoalEO.SECTION_TYPE_CODE = TmplSectionEO.SECTION_TYPE_CODE (+) AND EvaluationEO.BUSINESS_GROUP_ID = TmplSectionEO.BUSINESS_GROUP_ID (+) AND EvaluationEO.TEMPLATE_DEFN_ID = TmplSectionEO.TEMPLATE_DEFN_ID (+) ), CompetencySections AS (SELECT EvaluationEO.BUSINESS_GROUP_ID, EvaluationEO.TMPL_PERIOD_ID, EvaluationEO.EVALUATION_ID, EvalSectionCompEO.SECTION_RATING_MODEL_ID, EvalRatingCompEO.PERFORMANCE_RATING_ID, EvalRatingCompEO.CALCULATED_RATING, EvalRatingCompEO.EVAL_PARTICIPANT_ID, EvalSectionCompEO.REFERENCE_SECTION_ID, EvalSectionCompEO.TMPL_SECTION_ID FROM HRA_EVAL_SECTIONS EvalSectionCompEO, HRA_EVAL_RATINGS EvalRatingCompEO, HRA_TMPL_SECTIONS TMSE, HRA_EVALUATIONS EvaluationEO WHERE EvalSectionCompEO.BUSINESS_GROUP_ID(+) = EvaluationEO.BUSINESS_GROUP_ID AND EvalSectionCompEO.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID AND EvalSectionCompEO.SECTION_TYPE_CODE(+) = 'REG' AND EvalRatingCompEO.REFERENCE_ID(+) = EvalSectionCompEO.EVAL_SECTION_ID AND EvalRatingCompEO.REFERENCE_TYPE(+) = 'SECTION' AND EvalSectionCompEO.TMPL_SECTION_ID = TMSE.SECTION_ID(+) AND 'REG' = TMSE.SECTION_TYPE_CODE(+) AND EvaluationEO.BUSINESS_GROUP_ID = TMSE.BUSINESS_GROUP_ID(+) AND EvaluationEO.TEMPLATE_DEFN_ID = TMSE.TEMPLATE_DEFN_ID(+) AND (TMSE.SEQUENCE_NUMBER IS NULL OR TMSE.SEQUENCE_NUMBER = (SELECT MIN(SectionEO.SEQUENCE_NUMBER) FROM HRA_TMPL_SECTIONS SectionEO WHERE SectionEO.BUSINESS_GROUP_ID = EvaluationEO.BUSINESS_GROUP_ID AND SectionEO.TEMPLATE_DEFN_ID = EvaluationEO.TEMPLATE_DEFN_ID AND SectionEO.SECTION_TYPE_CODE = 'REG' ) ) ) SELECT TemplateDefnPEO.DOC_TYPE_ID, TemplateDefnPEO.TEMPLATE_DEFN_ID, TemplateDefnPEO.NAME TEMPLATE_NAME, TemplatePeriodPEO.TMPL_PERIOD_ID, TemplatePeriodPEO.CUSTOMARY_NAME, EvaluationEO.EVALUATION_ID, EvaluationEO.BUSINESS_GROUP_ID, EvaluationEO.ASSIGNMENT_ID, ManagerNameDPEO.DISPLAY_NAME MANAGER_DISPLAY_NAME, WorkerNameDPEO.DISPLAY_NAME WORKER_DISPLAY_NAME, EvaluationEO.START_DATE, EvaluationEO.END_DATE, EvalStepEO.STEP_COMPLETION_DATE, EvaluationEO.CALIBRATION_STATUS, EvalRatingEO.PERFORMANCE_RATING_ID OVERALL_RATING, EvalRatingEO.CALCULATED_RATING CALCULATED_OVERALL_RATING, EvalRatingEO.COMMENTS OVERALL_COMMENTS, ( CASE WHEN (EvaluationEO.STATUS_CODE <> 'COMP') THEN DECODE(EvalStepEO.STEP_STATUS,'COMP','Manager Complete','In Progress') WHEN (EvaluationEO.STATUS_CODE = 'COMP') THEN 'Final' END) AS OVERALL_STATUS, PerformanceGoalSections.PERFORMANCE_RATING_ID OVERALL_GOAL_RATING, PerformanceGoalSections.CALCULATED_RATING CALCULATED_OVERALL_GOAL_RATING, CompetencySections.PERFORMANCE_RATING_ID OVERALL_COMP_RATING, CompetencySections.CALCULATED_RATING CALCULATED_OVERALL_COMP_RATING, ( CASE WHEN (EvaluationEO.STATUS_CODE <> 'COMP') THEN DECODE(EvalStepEO.STEP_STATUS,'COMP',2,1) WHEN (EvaluationEO.STATUS_CODE = 'COMP') THEN 3 END) OVERALL_STATUS_NUM , RATINGLEVELSPEO.RATING_DESCRIPTION OVERALL_RATING_DESC, RATINGLEVELSPEO.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC, RATINGLEVELSPEO.NUMERIC_RATING OVERALL_NUMERIC_RATING, RATINGLEVELSGOALPEO.RATING_DESCRIPTION OVERALL_RATING_GOAL_DESC, RATINGLEVELSGOALPEO.RATING_SHORT_DESCR OVERALL_RATING_GOAL_SHORT_DESC, RATINGLEVELSGOALPEO.NUMERIC_RATING OVERALL_NUMERIC_GOAL_RATING, RATINGLEVELSCOMPPEO.RATING_DESCRIPTION OVERALL_RATING_COMP_DESC, RATINGLEVELSCOMPPEO.RATING_SHORT_DESCR OVERALL_RATING_COMP_SHORT_DESC, RATINGLEVELSCOMPPEO.NUMERIC_RATING OVERALL_NUMERIC_COMP_RATING, CompetencySections.SECTION_RATING_MODEL_ID COMP_RATING_MODEL_ID, PerformanceGoalSections.SECTION_RATING_MODEL_ID GOAL_RATING_MODEL_ID, EvalSectionEO.SECTION_RATING_MODEL_ID AS OVERALL_RATING_MODEL_ID, DECODE(EvaluationEO.USE_CALC_RATINGS_FLAG, 'Y', 'N', 'Y') RATINGS_UPDATEABLE, EvalRatingDevGoalEO.PERFORMANCE_RATING_ID OVERALL_DEVGOAL_RATING, EvalRatingDevGoalEO.CALCULATED_RATING CALC_OVERALL_DEVGOAL_RATING, RatingLevelsDevGoalPEO.RATING_DESCRIPTION OVERALL_RATING_DEVGOAL_DESC, RatingLevelsDevGoalPEO.RATING_SHORT_DESCR OVERALL_RATING_DEVGOAL_SH_DESC, RatingLevelsDevGoalPEO.NUMERIC_RATING OVERALL_NUMERIC_DEVGOAL_RATING, EvalSectionDevGoalEO.SECTION_RATING_MODEL_ID DEVGOAL_RATING_MODEL_ID, CompetencySections.TMPL_SECTION_ID COMP_TMPL_SECTION_ID FROM HRA_DOC_TYPES_VL DocumentTypePEO, HRA_TMPL_DEFNS_VL TemplateDefnPEO, HRA_TMPL_PERIODS_VL TemplatePeriodPEO, HRA_EVALUATIONS EvaluationEO, HRA_EVAL_SECTIONS EvalSectionEO, HRA_EVAL_RATINGS EvalRatingEO, HRA_EVAL_STEPS EvalStepEO, HRA_EVAL_PARTICIPANTS EvalParticipantEO, PER_PERSON_NAMES_F_V ManagerNameDPEO, PER_PERSON_NAMES_F_V WorkerNameDPEO, PerformanceGoalSections, CompetencySections, HRT_RATING_LEVELS_VL RatingLevelsPEO, HRT_RATING_LEVELS_VL RatingLevelsGoalPEO, HRT_RATING_LEVELS_VL RatingLevelsCompPEO, HRA_EVAL_SECTIONS EvalSectionDevGoalEO, HRA_EVAL_RATINGS EvalRatingDevGoalEO, HRT_RATING_LEVELS_VL RatingLevelsDevGoalPEO WHERE TemplatePeriodPEO.TMPL_PERIOD_ID = EvaluationEO.TMPL_PERIOD_ID AND TemplatePeriodPEO.BUSINESS_GROUP_ID = EvaluationEO.BUSINESS_GROUP_ID AND TemplatePeriodPEO.TEMPLATE_DEFN_ID = TemplateDefnPEO.TEMPLATE_DEFN_ID AND TemplatePeriodPEO.BUSINESS_GROUP_ID = TemplateDefnPEO.BUSINESS_GROUP_ID AND TemplateDefnPEO.DOC_TYPE_ID = DocumentTypePEO.DOC_TYPE_ID AND TemplateDefnPEO.BUSINESS_GROUP_ID = DocumentTypePEO.BUSINESS_GROUP_ID AND EvaluationEO.EVALUATION_ID = EvalSectionEO.EVALUATION_ID AND EvalSectionEO.SECTION_TYPE_CODE = 'OS' AND EVALUATIONEO.STATUS_CODE <> 'CANCELLED' AND EvalSectionEO.EVAL_SECTION_ID = EvalRatingEO.REFERENCE_ID(+) AND EvalRatingEO.REFERENCE_TYPE(+) = 'SECTION' AND EvalRatingEO.EVAL_PARTICIPANT_ID = EvalParticipantEO.EVAL_PARTICIPANT_ID AND EvalParticipantEO.EVALUATION_ID = EvaluationEO.EVALUATION_ID AND EvaluationEO.EVALUATION_ID = EvalStepEO.EVALUATION_ID AND EvalRatingEO.EVAL_PARTICIPANT_ID = EvalStepEO.EVAL_PARTICIPANT_ID AND EvalStepEO.STEP_CODE = 'MGREVAL' AND EvalParticipantEO.ROLE_TYPE_CODE = 'MANAGER' AND EvaluationEO.MANAGER_ID = ManagerNameDPEO.PERSON_ID AND TRUNC(sysdate) BETWEEN ManagerNameDPEO.EFFECTIVE_START_DATE AND ManagerNameDPEO.EFFECTIVE_END_DATE AND EvaluationEO.WORKER_ID = WorkerNameDPEO.PERSON_ID AND TRUNC(sysdate) BETWEEN WorkerNameDPEO.EFFECTIVE_START_DATE AND WorkerNameDPEO.EFFECTIVE_END_DATE AND PerformanceGoalSections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID AND (EvalParticipantEO.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID OR PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL) AND CompetencySections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID AND (EvalParticipantEO.eval_participant_id = CompetencySections.EVAL_PARTICIPANT_ID OR CompetencySections.EVAL_PARTICIPANT_ID IS NULL) AND EvalSectionDevGoalEO.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID AND EvalSectionDevGoalEO.SECTION_TYPE_CODE(+) = 'ORA_DEVGOAL' AND EvalRatingDevGoalEO.REFERENCE_ID(+) = EvalSectionDevGoalEO.EVAL_SECTION_ID AND EvalRatingDevGoalEO.REFERENCE_TYPE(+) = 'SECTION' AND (EvalParticipantEO.EVAL_PARTICIPANT_ID = EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID OR EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID IS NULL) AND (EvalRatingEO.PERFORMANCE_RATING_ID = RatingLevelsPEO.rating_level_id(+) AND EvalRatingEO.business_group_id = RatingLevelsPEO.business_group_id(+)) AND (PerformanceGoalSections.PERFORMANCE_RATING_ID = RatingLevelsGoalPEO.rating_level_id(+) AND PerformanceGoalSections.business_group_id = RatingLevelsGoalPEO.business_group_id(+)) AND (CompetencySections.PERFORMANCE_RATING_ID = RatingLevelsCompPEO.rating_level_id(+) AND CompetencySections.BUSINESS_GROUP_ID = RatingLevelsCompPEO.BUSINESS_GROUP_ID(+)) AND (EvalRatingDevGoalEO.PERFORMANCE_RATING_ID = RatingLevelsDevGoalPEO.RATING_LEVEL_ID(+) AND EvalRatingDevGoalEO.BUSINESS_GROUP_ID = RatingLevelsDevGoalPEO.BUSINESS_GROUP_ID(+))

SQL_Statement

WITH EvalGoalSectionIds AS (

SELECT

EvaluationEO.EVALUATION_ID,

MIN(EvalSectionGoalEO.EVAL_SECTION_ID) KEEP(DENSE_RANK FIRST ORDER BY GoalPlanEO.PRIMARY_GOAL_PLAN_FLAG DESC, TmplSectionEO.SEQUENCE_NUMBER ASC, GoalPlanEO.GOAL_PLAN_NAME ASC) EVAL_SECTION_ID,

MIN(EvaluationEO.ENABLE_SYNC_FLAG) ENABLE_SYNC_FLAG

FROM

HRA_EVAL_SECTIONS EvalSectionGoalEO,

HRA_TMPL_SECTIONS TmplSectionEO,

HRG_GOAL_PLANS_VL GoalPlanEO,

HRG_GOAL_PLN_ASSIGNMENTS GoalPlanAssignmentEO,

HRA_EVALUATIONS EvaluationEO

WHERE

EvaluationEO.EVALUATION_ID = EvalSectionGoalEO.EVALUATION_ID

AND EvaluationEO.ENABLE_SYNC_FLAG = 'Y'

AND EvalSectionGoalEO.SECTION_TYPE_CODE = 'GOAL'

AND EvalSectionGoalEO.GOAL_PLAN_ASSIGNMENT_ID = GoalPlanAssignmentEO.GOAL_PLAN_ASSIGNMENT_ID

AND GoalPlanAssignmentEO.GOAL_PLAN_ID = GoalPlanEO.GOAL_PLAN_ID

AND EvalSectionGoalEO.TMPL_SECTION_ID = TmplSectionEO.SECTION_ID

AND EvalSectionGoalEO.SECTION_TYPE_CODE = TmplSectionEO.SECTION_TYPE_CODE

AND EvalSectionGoalEO.BUSINESS_GROUP_ID = TmplSectionEO.BUSINESS_GROUP_ID

GROUP BY

EvaluationEO.EVALUATION_ID

UNION

SELECT

EvaluationEO.EVALUATION_ID,

EvalSectionGoalEO.EVAL_SECTION_ID,

EvaluationEO.ENABLE_SYNC_FLAG

FROM

HRA_EVAL_SECTIONS EvalSectionGoalEO,

HRA_EVALUATIONS EvaluationEO

WHERE

EvaluationEO.EVALUATION_ID = EvalSectionGoalEO.EVALUATION_ID

AND EvaluationEO.ENABLE_SYNC_FLAG = 'N'

AND EvalSectionGoalEO.SECTION_TYPE_CODE = 'GOAL'

),

PerformanceGoalSections AS

(SELECT

EvaluationEO.BUSINESS_GROUP_ID,

EvaluationEO.TMPL_PERIOD_ID,

EvaluationEO.EVALUATION_ID,

EvalSectionGoalEO.SECTION_RATING_MODEL_ID,

EvalRatingGoalEO.PERFORMANCE_RATING_ID,

EvalRatingGoalEO.CALCULATED_RATING,

EvalRatingGoalEO.EVAL_PARTICIPANT_ID,

EvalSectionGoalEO.REFERENCE_SECTION_ID,

EvalSectionGoalEO.TMPL_SECTION_ID

FROM

HRA_EVAL_SECTIONS EvalSectionGoalEO,

HRA_EVAL_RATINGS EvalRatingGoalEO,

HRA_TMPL_SECTIONS TmplSectionEO,

HRA_EVALUATIONS EvaluationEO,

EvalGoalSectionIds

WHERE

EvalSectionGoalEO.BUSINESS_GROUP_ID (+) = EvaluationEO.BUSINESS_GROUP_ID

AND EvalSectionGoalEO.EVALUATION_ID (+) = EvaluationEO.EVALUATION_ID

AND EvaluationEO.EVALUATION_ID = EvalGoalSectionIds.EVALUATION_ID

AND EvalSectionGoalEO.EVAL_SECTION_ID (+) = EvalGoalSectionIds.EVAL_SECTION_ID

AND EvalSectionGoalEO.SECTION_TYPE_CODE (+) = 'GOAL'

AND EvalRatingGoalEO.REFERENCE_ID (+) = EvalSectionGoalEO.EVAL_SECTION_ID

AND EvalRatingGoalEO.REFERENCE_TYPE (+) = 'SECTION'

AND EvalSectionGoalEO.TMPL_SECTION_ID = TmplSectionEO.SECTION_ID (+)

AND EvalSectionGoalEO.SECTION_TYPE_CODE = TmplSectionEO.SECTION_TYPE_CODE (+)

AND EvaluationEO.BUSINESS_GROUP_ID = TmplSectionEO.BUSINESS_GROUP_ID (+)

AND EvaluationEO.TEMPLATE_DEFN_ID = TmplSectionEO.TEMPLATE_DEFN_ID (+)

),

CompetencySections AS

(SELECT EvaluationEO.BUSINESS_GROUP_ID,

EvaluationEO.TMPL_PERIOD_ID,

EvaluationEO.EVALUATION_ID,

EvalSectionCompEO.SECTION_RATING_MODEL_ID,

EvalRatingCompEO.PERFORMANCE_RATING_ID,

EvalRatingCompEO.CALCULATED_RATING,

EvalRatingCompEO.EVAL_PARTICIPANT_ID,

EvalSectionCompEO.REFERENCE_SECTION_ID,

EvalSectionCompEO.TMPL_SECTION_ID

FROM HRA_EVAL_SECTIONS EvalSectionCompEO,

HRA_EVAL_RATINGS EvalRatingCompEO,

HRA_TMPL_SECTIONS TMSE,

HRA_EVALUATIONS EvaluationEO

WHERE EvalSectionCompEO.BUSINESS_GROUP_ID(+) = EvaluationEO.BUSINESS_GROUP_ID

AND EvalSectionCompEO.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID

AND EvalSectionCompEO.SECTION_TYPE_CODE(+) = 'REG'

AND EvalRatingCompEO.REFERENCE_ID(+) = EvalSectionCompEO.EVAL_SECTION_ID

AND EvalRatingCompEO.REFERENCE_TYPE(+) = 'SECTION'

AND EvalSectionCompEO.TMPL_SECTION_ID = TMSE.SECTION_ID(+)

AND 'REG' = TMSE.SECTION_TYPE_CODE(+)

AND EvaluationEO.BUSINESS_GROUP_ID = TMSE.BUSINESS_GROUP_ID(+)

AND EvaluationEO.TEMPLATE_DEFN_ID = TMSE.TEMPLATE_DEFN_ID(+)

AND (TMSE.SEQUENCE_NUMBER IS NULL OR

TMSE.SEQUENCE_NUMBER =

(SELECT MIN(SectionEO.SEQUENCE_NUMBER)

FROM HRA_TMPL_SECTIONS SectionEO

WHERE SectionEO.BUSINESS_GROUP_ID = EvaluationEO.BUSINESS_GROUP_ID

AND SectionEO.TEMPLATE_DEFN_ID = EvaluationEO.TEMPLATE_DEFN_ID

AND SectionEO.SECTION_TYPE_CODE = 'REG'

) )

)

SELECT TemplateDefnPEO.DOC_TYPE_ID,

TemplateDefnPEO.TEMPLATE_DEFN_ID,

TemplateDefnPEO.NAME TEMPLATE_NAME,

TemplatePeriodPEO.TMPL_PERIOD_ID,

TemplatePeriodPEO.CUSTOMARY_NAME,

EvaluationEO.EVALUATION_ID,

EvaluationEO.BUSINESS_GROUP_ID,

EvaluationEO.ASSIGNMENT_ID,

ManagerNameDPEO.DISPLAY_NAME MANAGER_DISPLAY_NAME,

WorkerNameDPEO.DISPLAY_NAME WORKER_DISPLAY_NAME,

EvaluationEO.START_DATE,

EvaluationEO.END_DATE,

EvalStepEO.STEP_COMPLETION_DATE,

EvaluationEO.CALIBRATION_STATUS,

EvalRatingEO.PERFORMANCE_RATING_ID OVERALL_RATING,

EvalRatingEO.CALCULATED_RATING CALCULATED_OVERALL_RATING,

EvalRatingEO.COMMENTS OVERALL_COMMENTS,

(

CASE

WHEN (EvaluationEO.STATUS_CODE <> 'COMP')

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

WHEN (EvaluationEO.STATUS_CODE = 'COMP')

THEN 'Final'

END) AS OVERALL_STATUS,

PerformanceGoalSections.PERFORMANCE_RATING_ID OVERALL_GOAL_RATING,

PerformanceGoalSections.CALCULATED_RATING CALCULATED_OVERALL_GOAL_RATING,

CompetencySections.PERFORMANCE_RATING_ID OVERALL_COMP_RATING,

CompetencySections.CALCULATED_RATING CALCULATED_OVERALL_COMP_RATING,

(

CASE

WHEN (EvaluationEO.STATUS_CODE <> 'COMP')

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

WHEN (EvaluationEO.STATUS_CODE = 'COMP')

THEN 3

END) OVERALL_STATUS_NUM ,

RATINGLEVELSPEO.RATING_DESCRIPTION OVERALL_RATING_DESC,

RATINGLEVELSPEO.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC,

RATINGLEVELSPEO.NUMERIC_RATING OVERALL_NUMERIC_RATING,

RATINGLEVELSGOALPEO.RATING_DESCRIPTION OVERALL_RATING_GOAL_DESC,

RATINGLEVELSGOALPEO.RATING_SHORT_DESCR OVERALL_RATING_GOAL_SHORT_DESC,

RATINGLEVELSGOALPEO.NUMERIC_RATING OVERALL_NUMERIC_GOAL_RATING,

RATINGLEVELSCOMPPEO.RATING_DESCRIPTION OVERALL_RATING_COMP_DESC,

RATINGLEVELSCOMPPEO.RATING_SHORT_DESCR OVERALL_RATING_COMP_SHORT_DESC,

RATINGLEVELSCOMPPEO.NUMERIC_RATING OVERALL_NUMERIC_COMP_RATING,

CompetencySections.SECTION_RATING_MODEL_ID COMP_RATING_MODEL_ID,

PerformanceGoalSections.SECTION_RATING_MODEL_ID GOAL_RATING_MODEL_ID,

EvalSectionEO.SECTION_RATING_MODEL_ID AS OVERALL_RATING_MODEL_ID,

DECODE(EvaluationEO.USE_CALC_RATINGS_FLAG, 'Y', 'N', 'Y') RATINGS_UPDATEABLE,

EvalRatingDevGoalEO.PERFORMANCE_RATING_ID OVERALL_DEVGOAL_RATING,

EvalRatingDevGoalEO.CALCULATED_RATING CALC_OVERALL_DEVGOAL_RATING,

RatingLevelsDevGoalPEO.RATING_DESCRIPTION OVERALL_RATING_DEVGOAL_DESC,

RatingLevelsDevGoalPEO.RATING_SHORT_DESCR OVERALL_RATING_DEVGOAL_SH_DESC,

RatingLevelsDevGoalPEO.NUMERIC_RATING OVERALL_NUMERIC_DEVGOAL_RATING,

EvalSectionDevGoalEO.SECTION_RATING_MODEL_ID DEVGOAL_RATING_MODEL_ID,

CompetencySections.TMPL_SECTION_ID COMP_TMPL_SECTION_ID

FROM

HRA_DOC_TYPES_VL DocumentTypePEO,

HRA_TMPL_DEFNS_VL TemplateDefnPEO,

HRA_TMPL_PERIODS_VL TemplatePeriodPEO,

HRA_EVALUATIONS EvaluationEO,

HRA_EVAL_SECTIONS EvalSectionEO,

HRA_EVAL_RATINGS EvalRatingEO,

HRA_EVAL_STEPS EvalStepEO,

HRA_EVAL_PARTICIPANTS EvalParticipantEO,

PER_PERSON_NAMES_F_V ManagerNameDPEO,

PER_PERSON_NAMES_F_V WorkerNameDPEO,

PerformanceGoalSections,

CompetencySections,

HRT_RATING_LEVELS_VL RatingLevelsPEO,

HRT_RATING_LEVELS_VL RatingLevelsGoalPEO,

HRT_RATING_LEVELS_VL RatingLevelsCompPEO,

HRA_EVAL_SECTIONS EvalSectionDevGoalEO,

HRA_EVAL_RATINGS EvalRatingDevGoalEO,

HRT_RATING_LEVELS_VL RatingLevelsDevGoalPEO

WHERE TemplatePeriodPEO.TMPL_PERIOD_ID = EvaluationEO.TMPL_PERIOD_ID

AND TemplatePeriodPEO.BUSINESS_GROUP_ID = EvaluationEO.BUSINESS_GROUP_ID

AND TemplatePeriodPEO.TEMPLATE_DEFN_ID = TemplateDefnPEO.TEMPLATE_DEFN_ID

AND TemplatePeriodPEO.BUSINESS_GROUP_ID = TemplateDefnPEO.BUSINESS_GROUP_ID

AND TemplateDefnPEO.DOC_TYPE_ID = DocumentTypePEO.DOC_TYPE_ID

AND TemplateDefnPEO.BUSINESS_GROUP_ID = DocumentTypePEO.BUSINESS_GROUP_ID

AND EvaluationEO.EVALUATION_ID = EvalSectionEO.EVALUATION_ID

AND EvalSectionEO.SECTION_TYPE_CODE = 'OS'

AND EVALUATIONEO.STATUS_CODE <> 'CANCELLED'

AND EvalSectionEO.EVAL_SECTION_ID = EvalRatingEO.REFERENCE_ID(+)

AND EvalRatingEO.REFERENCE_TYPE(+) = 'SECTION'

AND EvalRatingEO.EVAL_PARTICIPANT_ID = EvalParticipantEO.EVAL_PARTICIPANT_ID

AND EvalParticipantEO.EVALUATION_ID = EvaluationEO.EVALUATION_ID

AND EvaluationEO.EVALUATION_ID = EvalStepEO.EVALUATION_ID

AND EvalRatingEO.EVAL_PARTICIPANT_ID = EvalStepEO.EVAL_PARTICIPANT_ID

AND EvalStepEO.STEP_CODE = 'MGREVAL'

AND EvalParticipantEO.ROLE_TYPE_CODE = 'MANAGER'

AND EvaluationEO.MANAGER_ID = ManagerNameDPEO.PERSON_ID

AND TRUNC(sysdate) BETWEEN ManagerNameDPEO.EFFECTIVE_START_DATE AND ManagerNameDPEO.EFFECTIVE_END_DATE

AND EvaluationEO.WORKER_ID = WorkerNameDPEO.PERSON_ID

AND TRUNC(sysdate) BETWEEN WorkerNameDPEO.EFFECTIVE_START_DATE AND WorkerNameDPEO.EFFECTIVE_END_DATE

AND PerformanceGoalSections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID

AND (EvalParticipantEO.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID

OR PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL)

AND CompetencySections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID

AND (EvalParticipantEO.eval_participant_id = CompetencySections.EVAL_PARTICIPANT_ID

OR CompetencySections.EVAL_PARTICIPANT_ID IS NULL)

AND EvalSectionDevGoalEO.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID

AND EvalSectionDevGoalEO.SECTION_TYPE_CODE(+) = 'ORA_DEVGOAL'

AND EvalRatingDevGoalEO.REFERENCE_ID(+) = EvalSectionDevGoalEO.EVAL_SECTION_ID

AND EvalRatingDevGoalEO.REFERENCE_TYPE(+) = 'SECTION'

AND (EvalParticipantEO.EVAL_PARTICIPANT_ID = EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID

OR EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID IS NULL)

AND (EvalRatingEO.PERFORMANCE_RATING_ID = RatingLevelsPEO.rating_level_id(+)

AND EvalRatingEO.business_group_id = RatingLevelsPEO.business_group_id(+))

AND (PerformanceGoalSections.PERFORMANCE_RATING_ID = RatingLevelsGoalPEO.rating_level_id(+)

AND PerformanceGoalSections.business_group_id = RatingLevelsGoalPEO.business_group_id(+))

AND (CompetencySections.PERFORMANCE_RATING_ID = RatingLevelsCompPEO.rating_level_id(+)

AND CompetencySections.BUSINESS_GROUP_ID = RatingLevelsCompPEO.BUSINESS_GROUP_ID(+))

AND (EvalRatingDevGoalEO.PERFORMANCE_RATING_ID = RatingLevelsDevGoalPEO.RATING_LEVEL_ID(+)

AND EvalRatingDevGoalEO.BUSINESS_GROUP_ID = RatingLevelsDevGoalPEO.BUSINESS_GROUP_ID(+))