HRA_EVAL_STEP_STATUS_V

Details

• Schema: FUSION

• Object owner: HRA

• Object type: VIEW

Columns

Name
EVALUATION_ID STEP_STATUS

Query

SQL_Statement

SELECT HRA_EVAL_STEPS.EVALUATION_ID, LISTAGG(HRA_ROLE_DEFNS_VL.NAME || ': ' || CASE WHEN HRA_EVAL_PARTICIPANTS.ROLE_TYPE_CODE = 'WORKER' THEN NVL(HRA_PF_TASK_ROLES_VL.WKR_TASK_NAME, HRA_EVAL_STEPS.STEP_CODE) WHEN HRA_EVAL_PARTICIPANTS.ROLE_TYPE_CODE = 'MANAGER' THEN NVL(HRA_PF_TASK_ROLES_VL.MGR_TASK_NAME, HRA_EVAL_STEPS.STEP_CODE) ELSE HRA_EVAL_STEPS.STEP_CODE END || ' ' || LOWER(FND_LOOKUP_VALUES_VL.MEANING) || '; ') WITHIN GROUP( ORDER BY HRA_EVAL_STEPS.SEQUENCE_NUMBER ) STEP_STATUS FROM HRA_EVAL_STEPS JOIN HRA_EVALUATIONS ON HRA_EVALUATIONS.EVALUATION_ID = HRA_EVAL_STEPS.EVALUATION_ID JOIN HRA_EVAL_PARTICIPANTS ON HRA_EVAL_PARTICIPANTS.EVAL_PARTICIPANT_ID = HRA_EVAL_STEPS.EVAL_PARTICIPANT_ID AND HRA_EVAL_PARTICIPANTS.EVALUATION_ID = HRA_EVAL_STEPS.EVALUATION_ID JOIN HRA_TMPL_DEFNS_B ON HRA_TMPL_DEFNS_B.TEMPLATE_DEFN_ID = HRA_EVALUATIONS.TEMPLATE_DEFN_ID JOIN HRA_PF_TASK_ROLES_VL ON HRA_PF_TASK_ROLES_VL.PROCESS_FLOW_ID = HRA_TMPL_DEFNS_B.PROCESS_FLOW_ID AND HRA_PF_TASK_ROLES_VL.TASK_CODE = HRA_EVAL_STEPS.STEP_CODE AND HRA_PF_TASK_ROLES_VL.BUSINESS_GROUP_ID = HRA_EVAL_STEPS.BUSINESS_GROUP_ID JOIN FND_LOOKUP_VALUES_VL ON FND_LOOKUP_VALUES_VL.LOOKUP_TYPE = 'HRA_EVAL_STEP_STATUS' AND FND_LOOKUP_VALUES_VL.LOOKUP_CODE = HRA_EVAL_STEPS.STEP_STATUS LEFT JOIN HRA_TMPL_ROLES ON HRA_TMPL_ROLES.TEMPLATE_DEFN_ID = HRA_TMPL_DEFNS_B.TEMPLATE_DEFN_ID AND HRA_TMPL_ROLES.ROLE_TYPE_CODE = HRA_EVAL_PARTICIPANTS.ROLE_TYPE_CODE AND HRA_TMPL_ROLES.BUSINESS_GROUP_ID = HRA_TMPL_DEFNS_B.BUSINESS_GROUP_ID LEFT JOIN HRA_ROLE_DEFNS_VL ON HRA_ROLE_DEFNS_VL.ROLE_ID = HRA_TMPL_ROLES.ROLE_ID AND HRA_ROLE_DEFNS_VL.BUSINESS_GROUP_ID = HRA_TMPL_DEFNS_B.BUSINESS_GROUP_ID WHERE HRA_EVAL_STEPS.PARENT_STEP_ID IS NOT NULL AND HRA_EVAL_STEPS.STEP_CODE <> 'MNG_PCPN_FEEDBACK' GROUP BY HRA_EVAL_STEPS.EVALUATION_ID