HRA_MGR_SEC_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 CALIBRATION_STATUS START_DATE END_DATE RATINGS_UPDATEABLE MGR_EVAL_STEP_STATUS_CODE MGR_EVAL_STEP_COMPLETION_DATE DOCUMENT_STATUS_CODE GOAL_SEC_RATING_MODEL_ID GOAL_SEC_RATING_LEVEL_ID CALCULATED_GOAL_SEC_RATING COMP_SEC_RATING_MODEL_ID COMP_SEC_RATING_LEVEL_ID CALCULATED_COMP_SEC_RATING OVERALL_RATING_LEVEL_ID OVERALL_RATING_DESC OVERALL_RATING_SHORT_DESC OVERALL_NUMERIC_RATING CALCULATED_OVERALL_RATING OVERALL_COMMENTS OVERALL_STATUS_NUM OVERALL_STATUS DEV_GOAL_SEC_RATING_MODEL_ID DEV_GOAL_SEC_RATING_LEVEL_ID CALCULATED_DEV_GOAL_SEC_RATING COMP_TMPL_SECTION_ID

Name

EVALUATION_ID

BUSINESS_GROUP_ID

REVIEW_PERIOD_ID

DOC_TYPE_ID

TEMPLATE_DEFN_ID

TMPL_PERIOD_ID

WORKER_ID

ASSIGNMENT_ID

MANAGER_ID

CALIBRATION_STATUS

START_DATE

END_DATE

RATINGS_UPDATEABLE

MGR_EVAL_STEP_STATUS_CODE

MGR_EVAL_STEP_COMPLETION_DATE

DOCUMENT_STATUS_CODE

GOAL_SEC_RATING_MODEL_ID

GOAL_SEC_RATING_LEVEL_ID

CALCULATED_GOAL_SEC_RATING

COMP_SEC_RATING_MODEL_ID

COMP_SEC_RATING_LEVEL_ID

CALCULATED_COMP_SEC_RATING

OVERALL_RATING_LEVEL_ID

OVERALL_RATING_DESC

OVERALL_RATING_SHORT_DESC

OVERALL_NUMERIC_RATING

CALCULATED_OVERALL_RATING

OVERALL_COMMENTS

OVERALL_STATUS_NUM

OVERALL_STATUS

DEV_GOAL_SEC_RATING_MODEL_ID

DEV_GOAL_SEC_RATING_LEVEL_ID

CALCULATED_DEV_GOAL_SEC_RATING

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, EvalRatingGoalEO.ROLE_TYPE_CODE 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 (+) ) 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.CALIBRATION_STATUS, EVAL.START_DATE, EVAL.END_DATE , DECODE(EVAL.USE_CALC_RATINGS_FLAG, 'Y', 'N', 'Y') RATINGS_UPDATEABLE, STEP.STEP_STATUS MGR_EVAL_STEP_STATUS_CODE, STEP.STEP_COMPLETION_DATE MGR_EVAL_STEP_COMPLETION_DATE, EVAL.STATUS_CODE DOCUMENT_STATUS_CODE, PerformanceGoalSections.SECTION_RATING_MODEL_ID GOAL_SEC_RATING_MODEL_ID, PerformanceGoalSections.PERFORMANCE_RATING_ID GOAL_SEC_RATING_LEVEL_ID, PerformanceGoalSections.CALCULATED_RATING CALCULATED_GOAL_SEC_RATING, EVSE.SECTION_RATING_MODEL_ID COMP_SEC_RATING_MODEL_ID, EVRO.PERFORMANCE_RATING_ID COMP_SEC_RATING_LEVEL_ID, EVRO.CALCULATED_RATING CALCULATED_COMP_SEC_RATING, EVRT.PERFORMANCE_RATING_ID OVERALL_RATING_LEVEL_ID, RLVL.RATING_DESCRIPTION OVERALL_RATING_DESC, RLVL.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC, RLVL.NUMERIC_RATING OVERALL_NUMERIC_RATING, EVRT.CALCULATED_RATING CALCULATED_OVERALL_RATING, EVRT.COMMENTS 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) OVERALL_STATUS, EVSDG.SECTION_RATING_MODEL_ID DEV_GOAL_SEC_RATING_MODEL_ID, EVRDG.PERFORMANCE_RATING_ID DEV_GOAL_SEC_RATING_LEVEL_ID, EVRDG.CALCULATED_RATING CALCULATED_DEV_GOAL_SEC_RATING, EVSE.TMPL_SECTION_ID COMP_TMPL_SECTION_ID 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, PerformanceGoalSections, HRA_EVAL_SECTIONS EVSE, HRA_TMPL_SECTIONS TMSE, HRA_EVAL_RATINGS EVRO, HRA_EVAL_SECTIONS EVSDG, HRA_EVAL_RATINGS EVRDG WHERE EVAL.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID AND EVAL.business_group_id = TMPL.business_group_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' AND EVAL.EVALUATION_ID = PerformanceGoalSections.EVALUATION_ID(+) AND (PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL OR PTCP.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID ) AND 'MANAGER' = PerformanceGoalSections.ROLE_TYPE_CODE(+) AND EVAL.EVALUATION_ID = EVSE.EVALUATION_ID(+) AND 'REG' = EVSE.SECTION_TYPE_CODE(+) AND EVSE.EVAL_SECTION_ID = EVRO.REFERENCE_ID(+) AND EVSE.TMPL_SECTION_ID = TMSE.SECTION_ID(+) AND 'REG' = TMSE.SECTION_TYPE_CODE(+) AND TMPL.BUSINESS_GROUP_ID = TMSE.BUSINESS_GROUP_ID(+) AND TMPL.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 = TMPL.BUSINESS_GROUP_ID AND SectionEO.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID AND SectionEO.SECTION_TYPE_CODE = 'REG' ) ) AND 'SECTION' = EVRO.REFERENCE_TYPE(+) AND (EVRO.EVAL_PARTICIPANT_ID IS NULL OR PTCP.EVAL_PARTICIPANT_ID = EVRO.EVAL_PARTICIPANT_ID ) AND 'MANAGER' = EVRO.ROLE_TYPE_CODE(+) AND EVAL.EVALUATION_ID = EVSDG.EVALUATION_ID(+) AND 'ORA_DEVGOAL' = EVSDG.SECTION_TYPE_CODE(+) AND EVSDG.EVAL_SECTION_ID = EVRDG.REFERENCE_ID(+) AND 'SECTION' = EVRDG.REFERENCE_TYPE(+) AND (EVRDG.EVAL_PARTICIPANT_ID IS NULL OR PTCP.EVAL_PARTICIPANT_ID = EVRDG.EVAL_PARTICIPANT_ID ) AND 'MANAGER' = EVRDG.ROLE_TYPE_CODE(+)

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,

EvalRatingGoalEO.ROLE_TYPE_CODE

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 (+)

)

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.CALIBRATION_STATUS,

EVAL.START_DATE,

EVAL.END_DATE ,

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

STEP.STEP_STATUS MGR_EVAL_STEP_STATUS_CODE,

STEP.STEP_COMPLETION_DATE MGR_EVAL_STEP_COMPLETION_DATE,

EVAL.STATUS_CODE DOCUMENT_STATUS_CODE,

PerformanceGoalSections.SECTION_RATING_MODEL_ID GOAL_SEC_RATING_MODEL_ID,

PerformanceGoalSections.PERFORMANCE_RATING_ID GOAL_SEC_RATING_LEVEL_ID,

PerformanceGoalSections.CALCULATED_RATING CALCULATED_GOAL_SEC_RATING,

EVSE.SECTION_RATING_MODEL_ID COMP_SEC_RATING_MODEL_ID,

EVRO.PERFORMANCE_RATING_ID COMP_SEC_RATING_LEVEL_ID,

EVRO.CALCULATED_RATING CALCULATED_COMP_SEC_RATING,

EVRT.PERFORMANCE_RATING_ID OVERALL_RATING_LEVEL_ID,

RLVL.RATING_DESCRIPTION OVERALL_RATING_DESC,

RLVL.RATING_SHORT_DESCR OVERALL_RATING_SHORT_DESC,

RLVL.NUMERIC_RATING OVERALL_NUMERIC_RATING,

EVRT.CALCULATED_RATING CALCULATED_OVERALL_RATING,

EVRT.COMMENTS 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) OVERALL_STATUS,

EVSDG.SECTION_RATING_MODEL_ID DEV_GOAL_SEC_RATING_MODEL_ID,

EVRDG.PERFORMANCE_RATING_ID DEV_GOAL_SEC_RATING_LEVEL_ID,

EVRDG.CALCULATED_RATING CALCULATED_DEV_GOAL_SEC_RATING,

EVSE.TMPL_SECTION_ID COMP_TMPL_SECTION_ID

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,

PerformanceGoalSections,

HRA_EVAL_SECTIONS EVSE,

HRA_TMPL_SECTIONS TMSE,

HRA_EVAL_RATINGS EVRO,

HRA_EVAL_SECTIONS EVSDG,

HRA_EVAL_RATINGS EVRDG

WHERE EVAL.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID

AND EVAL.business_group_id = TMPL.business_group_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'

AND EVAL.EVALUATION_ID = PerformanceGoalSections.EVALUATION_ID(+)

AND (PerformanceGoalSections.EVAL_PARTICIPANT_ID IS NULL

OR PTCP.EVAL_PARTICIPANT_ID = PerformanceGoalSections.EVAL_PARTICIPANT_ID )

AND 'MANAGER' = PerformanceGoalSections.ROLE_TYPE_CODE(+)

AND EVAL.EVALUATION_ID = EVSE.EVALUATION_ID(+)

AND 'REG' = EVSE.SECTION_TYPE_CODE(+)

AND EVSE.EVAL_SECTION_ID = EVRO.REFERENCE_ID(+)

AND EVSE.TMPL_SECTION_ID = TMSE.SECTION_ID(+)

AND 'REG' = TMSE.SECTION_TYPE_CODE(+)

AND TMPL.BUSINESS_GROUP_ID = TMSE.BUSINESS_GROUP_ID(+)

AND TMPL.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 = TMPL.BUSINESS_GROUP_ID

AND SectionEO.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID

AND SectionEO.SECTION_TYPE_CODE = 'REG'

) )

AND 'SECTION' = EVRO.REFERENCE_TYPE(+)

AND (EVRO.EVAL_PARTICIPANT_ID IS NULL

OR PTCP.EVAL_PARTICIPANT_ID = EVRO.EVAL_PARTICIPANT_ID )

AND 'MANAGER' = EVRO.ROLE_TYPE_CODE(+)

AND EVAL.EVALUATION_ID = EVSDG.EVALUATION_ID(+)

AND 'ORA_DEVGOAL' = EVSDG.SECTION_TYPE_CODE(+)

AND EVSDG.EVAL_SECTION_ID = EVRDG.REFERENCE_ID(+)

AND 'SECTION' = EVRDG.REFERENCE_TYPE(+)

AND (EVRDG.EVAL_PARTICIPANT_ID IS NULL

OR PTCP.EVAL_PARTICIPANT_ID = EVRDG.EVAL_PARTICIPANT_ID )

AND 'MANAGER' = EVRDG.ROLE_TYPE_CODE(+)