HRA_OVERALL_RATINGS

Details

Schema: FUSION
Object owner: HRA
Object type: VIEW

Columns

Name
DOC_TYPE_ID DOCUMENT_TYPE_NAME TEMPLATE_DEFN_ID TEMPLATE_NAME TMPL_PERIOD_ID CUSTOMARY_NAME EVALUATION_ID BUSINESS_GROUP_ID ASSIGNMENT_ID WORKER_ID WORKER_NAME WORKER_DISPLAY_NAME MANAGER_ID MANAGER_NAME MANAGER_DISPLAY_NAME START_DATE END_DATE EVAL_PARTICIPANT_ID ROLE_TYPE_CODE STEP_COMPLETION_DATE SECTION_RATING_MODEL_ID OVERALL_RATING CALCULATED_OVERALL_RATING OVERALL_COMMENTS OVERALL_STATUS PRECEDENCE_ORDER COMP_RATING_MODEL_ID OVERALL_COMP_RATING CALCULATED_OVERALL_COMP_RATING GOAL_RATING_MODEL_ID OVERALL_GOAL_RATING CALCULATED_OVERALL_GOAL_RATING DEVGOAL_RATING_MODEL_ID OVERALL_DEVGOAL_RATING CALC_OVERALL_DEVGOAL_RATING

Name

DOC_TYPE_ID

DOCUMENT_TYPE_NAME

TEMPLATE_DEFN_ID

TEMPLATE_NAME

TMPL_PERIOD_ID

CUSTOMARY_NAME

EVALUATION_ID

BUSINESS_GROUP_ID

ASSIGNMENT_ID

WORKER_ID

WORKER_NAME

WORKER_DISPLAY_NAME

MANAGER_ID

MANAGER_NAME

MANAGER_DISPLAY_NAME

START_DATE

END_DATE

EVAL_PARTICIPANT_ID

ROLE_TYPE_CODE

STEP_COMPLETION_DATE

SECTION_RATING_MODEL_ID

OVERALL_RATING

CALCULATED_OVERALL_RATING

OVERALL_COMMENTS

OVERALL_STATUS

PRECEDENCE_ORDER

COMP_RATING_MODEL_ID

OVERALL_COMP_RATING

CALCULATED_OVERALL_COMP_RATING

GOAL_RATING_MODEL_ID

OVERALL_GOAL_RATING

CALCULATED_OVERALL_GOAL_RATING

DEVGOAL_RATING_MODEL_ID

OVERALL_DEVGOAL_RATING

CALC_OVERALL_DEVGOAL_RATING

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 FROM HRA_EVAL_SECTIONS EvalSectionCompEO, HRA_EVAL_RATINGS EvalRatingCompEO, 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.REFERENCE_SECTION_ID IS NULL OR EvalSectionCompEO.REFERENCE_SECTION_ID = (SELECT MIN(REFERENCE_SECTION_ID) FROM HRA_EVAL_SECTIONS EvalSectionEO WHERE EvalSectionEO.BUSINESS_GROUP_ID = EvalSectionCompEO.BUSINESS_GROUP_ID AND EvalSectionEO.EVALUATION_ID = EvalSectionCompEO.EVALUATION_ID AND EvalSectionEO.SECTION_TYPE_CODE = EvalSectionCompEO.SECTION_TYPE_CODE ) ) ) SELECT TemplateDefnPEO.DOC_TYPE_ID, DocumentTypePEO.NAME AS DOCUMENT_TYPE_NAME, TemplateDefnPEO.TEMPLATE_DEFN_ID, TemplateDefnPEO.NAME AS TEMPLATE_NAME, TemplatePeriodPEO.TMPL_PERIOD_ID, TemplatePeriodPEO.CUSTOMARY_NAME, EvaluationEO.EVALUATION_ID, EvaluationEO.BUSINESS_GROUP_ID, EvaluationEO.ASSIGNMENT_ID, EvaluationEO.WORKER_ID, WorkerNameDPEO.FULL_NAME AS WORKER_NAME, WorkerNameDPEO.DISPLAY_NAME AS WORKER_DISPLAY_NAME, EvaluationEO.MANAGER_ID, ManagerNameDPEO.FULL_NAME AS MANAGER_NAME, ManagerNameDPEO.DISPLAY_NAME AS MANAGER_DISPLAY_NAME, EvaluationEO.START_DATE, EvaluationEO.END_DATE, EvalParticipantEO.EVAL_PARTICIPANT_ID, EvalParticipantEO.ROLE_TYPE_CODE, EvalStepEO.STEP_COMPLETION_DATE, EvalSectionEO.SECTION_RATING_MODEL_ID, EvalRatingEO.PERFORMANCE_RATING_ID AS OVERALL_RATING, EvalRatingEO.CALCULATED_RATING AS CALCULATED_OVERALL_RATING, EvalRatingEO.COMMENT_TEXT AS OVERALL_COMMENTS, (CASE WHEN ((EvaluationEO.STATUS_CODE <> 'COMP') AND (EvalRatingEO.PERFORMANCE_RATING_ID IS NOT NULL)) THEN 'In Progress' WHEN ((EvaluationEO.STATUS_CODE = 'COMP') AND (EvalRatingEO.PERFORMANCE_RATING_ID IS NOT NULL)) THEN 'Final' END) AS OVERALL_STATUS, DENSE_RANK() OVER (PARTITION BY EvaluationEO.ASSIGNMENT_ID ORDER BY EvaluationEO.END_DATE DESC, EvaluationEO.START_DATE DESC, EvaluationEO.EVALUATION_ID DESC ) AS PRECEDENCE_ORDER, CompetencySections.SECTION_RATING_MODEL_ID AS COMP_RATING_MODEL_ID, CompetencySections.PERFORMANCE_RATING_ID AS OVERALL_COMP_RATING, CompetencySections.CALCULATED_RATING AS CALCULATED_OVERALL_COMP_RATING, PerformanceGoalSections.SECTION_RATING_MODEL_ID AS GOAL_RATING_MODEL_ID, PerformanceGoalSections.PERFORMANCE_RATING_ID AS OVERALL_GOAL_RATING, PerformanceGoalSections.CALCULATED_RATING AS CALCULATED_OVERALL_GOAL_RATING, EvalSectionDevGoalEO.SECTION_RATING_MODEL_ID AS DEVGOAL_RATING_MODEL_ID, EvalRatingDevGoalEO.PERFORMANCE_RATING_ID AS OVERALL_DEVGOAL_RATING, EvalRatingDevGoalEO.CALCULATED_RATING AS CALC_OVERALL_DEVGOAL_RATING 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, HRA_EVAL_SECTIONS EvalSectionDevGoalEO, HRA_EVAL_RATINGS EvalRatingDevGoalEO, CompetencySections, PerformanceGoalSections 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 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') OR (EvalStepEO.STEP_CODE = 'WSEVAL' AND EvalParticipantEO.ROLE_TYPE_CODE = 'WORKER')) AND EvalStepEO.STEP_STATUS = 'COMP' 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 (EvalStepEO.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID OR PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL) AND CompetencySections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID AND (EvalStepEO.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 (EvalStepEO.EVAL_PARTICIPANT_ID = EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID OR EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID IS NULL) ORDER BY EvaluationEO.ASSIGNMENT_ID, EvaluationEO.END_DATE DESC, EvaluationEO.START_DATE DESC, EvaluationEO.EVALUATION_ID DESC

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

FROM HRA_EVAL_SECTIONS EvalSectionCompEO,

HRA_EVAL_RATINGS EvalRatingCompEO,

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.REFERENCE_SECTION_ID IS NULL

OR EvalSectionCompEO.REFERENCE_SECTION_ID =

(SELECT MIN(REFERENCE_SECTION_ID)

FROM HRA_EVAL_SECTIONS EvalSectionEO

WHERE EvalSectionEO.BUSINESS_GROUP_ID = EvalSectionCompEO.BUSINESS_GROUP_ID

AND EvalSectionEO.EVALUATION_ID = EvalSectionCompEO.EVALUATION_ID

AND EvalSectionEO.SECTION_TYPE_CODE = EvalSectionCompEO.SECTION_TYPE_CODE

) )

)

SELECT TemplateDefnPEO.DOC_TYPE_ID,

DocumentTypePEO.NAME AS DOCUMENT_TYPE_NAME,

TemplateDefnPEO.TEMPLATE_DEFN_ID,

TemplateDefnPEO.NAME AS TEMPLATE_NAME,

TemplatePeriodPEO.TMPL_PERIOD_ID,

TemplatePeriodPEO.CUSTOMARY_NAME,

EvaluationEO.EVALUATION_ID,

EvaluationEO.BUSINESS_GROUP_ID,

EvaluationEO.ASSIGNMENT_ID,

EvaluationEO.WORKER_ID,

WorkerNameDPEO.FULL_NAME AS WORKER_NAME,

WorkerNameDPEO.DISPLAY_NAME AS WORKER_DISPLAY_NAME,

EvaluationEO.MANAGER_ID,

ManagerNameDPEO.FULL_NAME AS MANAGER_NAME,

ManagerNameDPEO.DISPLAY_NAME AS MANAGER_DISPLAY_NAME,

EvaluationEO.START_DATE,

EvaluationEO.END_DATE,

EvalParticipantEO.EVAL_PARTICIPANT_ID,

EvalParticipantEO.ROLE_TYPE_CODE,

EvalStepEO.STEP_COMPLETION_DATE,

EvalSectionEO.SECTION_RATING_MODEL_ID,

EvalRatingEO.PERFORMANCE_RATING_ID AS OVERALL_RATING,

EvalRatingEO.CALCULATED_RATING AS CALCULATED_OVERALL_RATING,

EvalRatingEO.COMMENT_TEXT AS OVERALL_COMMENTS,

(CASE

WHEN ((EvaluationEO.STATUS_CODE <> 'COMP')

AND (EvalRatingEO.PERFORMANCE_RATING_ID IS NOT NULL))

THEN 'In Progress'

WHEN ((EvaluationEO.STATUS_CODE = 'COMP')

AND (EvalRatingEO.PERFORMANCE_RATING_ID IS NOT NULL))

THEN 'Final'

END) AS OVERALL_STATUS,

DENSE_RANK() OVER (PARTITION BY EvaluationEO.ASSIGNMENT_ID ORDER BY EvaluationEO.END_DATE DESC, EvaluationEO.START_DATE DESC, EvaluationEO.EVALUATION_ID DESC ) AS PRECEDENCE_ORDER,

CompetencySections.SECTION_RATING_MODEL_ID AS COMP_RATING_MODEL_ID,

CompetencySections.PERFORMANCE_RATING_ID AS OVERALL_COMP_RATING,

CompetencySections.CALCULATED_RATING AS CALCULATED_OVERALL_COMP_RATING,

PerformanceGoalSections.SECTION_RATING_MODEL_ID AS GOAL_RATING_MODEL_ID,

PerformanceGoalSections.PERFORMANCE_RATING_ID AS OVERALL_GOAL_RATING,

PerformanceGoalSections.CALCULATED_RATING AS CALCULATED_OVERALL_GOAL_RATING,

EvalSectionDevGoalEO.SECTION_RATING_MODEL_ID AS DEVGOAL_RATING_MODEL_ID,

EvalRatingDevGoalEO.PERFORMANCE_RATING_ID AS OVERALL_DEVGOAL_RATING,

EvalRatingDevGoalEO.CALCULATED_RATING AS CALC_OVERALL_DEVGOAL_RATING

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,

HRA_EVAL_SECTIONS EvalSectionDevGoalEO,

HRA_EVAL_RATINGS EvalRatingDevGoalEO,

CompetencySections,

PerformanceGoalSections

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 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')

OR (EvalStepEO.STEP_CODE = 'WSEVAL' AND EvalParticipantEO.ROLE_TYPE_CODE = 'WORKER'))

AND EvalStepEO.STEP_STATUS = 'COMP'

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 (EvalStepEO.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID

OR PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL)

AND CompetencySections.EVALUATION_ID(+) = EvaluationEO.EVALUATION_ID

AND (EvalStepEO.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 (EvalStepEO.EVAL_PARTICIPANT_ID = EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID

OR EvalRatingDevGoalEO.EVAL_PARTICIPANT_ID IS NULL)

ORDER BY EvaluationEO.ASSIGNMENT_ID, EvaluationEO.END_DATE DESC, EvaluationEO.START_DATE DESC, EvaluationEO.EVALUATION_ID DESC