select (select branch_id from EIS.EIS_EMPLOYEE EIS_EMPLOYEE where /* EIS_EMPLOYEE.STATUS = 'EMPLOYEE' and This query can be used to get status for earlier appraisals too. In such case, there could be few resource who were part of earlier appraisal but now resigned, for them also we must get the branch */ EIS_EMPLOYEE.EMPLOYEE_ID = PIV_Employee_ID) Branch_id, STATE_NAME, INSTANCE_ID, PIV_Appraisal_ID Appraisal_ID, PIV_Appraisal_period Appraisal_period, PIV_Employee_ID Employee_ID, PIV_Employee_Mail Employee_Mail, PIV_Employee_Name Employee_Name, PIV_Manager_ID Manager_ID, PIV_Manager_Name Manager_Name, PIV_Manager_mail Manager_mail, PIV_Mentor_ID Mentor_ID, PIV_Mentor_Mail Mentor_Mail, PIV_Mentor_Name Mentor_Name, PIV_Period_ID Period_ID, PIV_employeeUser employeeUser, PIV_managerUser managerUser, PIV_mentorUser mentorUser, STATE_ORDER from (SELECT /*T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_APPLICATIONS.IMAGE_FILE, T_GPS_APPLICATIONS.DESCRIPTION, T_GPS_APPLICATIONS.DEFAULT_GETCONTROLLERS, T_GPS_APPLICATIONS.DEFAULT_PUTCONTROLLERS, T_GPS_APPLICATIONS.VISIBLE_FLAG, T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG, T_GPS_APPLICATIONS.APPLICATION_ORDER, T_GPS_APPLICATIONS.CREATION_DATE, T_GPS_APPLICATIONS.LAST_UPDATED_DATE, T_GPS_APPLICATIONS.LAST_UPDATED_BY, T_GPS_APPLICATIONS.CREATED_BY, T_GPS_APPLICATIONS.DISPLAYNAME, T_GPS_APPLICATIONS.APPLICATION_URL, T_GPS_APPLICATIONS.APPLICATION_URL_DISPLAY_ON, T_GPS_APPLICATIONS.APPLICATION_IMAGE, T_GPS_APPLICATIONS.IS_CLOUD_ENABLED, T_GPS_WORKFLOW_TYPES.APPLICATION_ID, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME, T_GPS_WORKFLOW_TYPES.DESCRIPTION, T_GPS_WORKFLOW_TYPES.FROZEN_FLAG, T_GPS_WORKFLOW_TYPES.CYCLE_TIME, T_GPS_WORKFLOW_TYPES.CYCLE_TIME_UNIT, T_GPS_WORKFLOW_TYPES.CYCLE_TIME_MODE, T_GPS_WORKFLOW_TYPES.SUBWORKFLOW_ID, T_GPS_WORKFLOW_TYPES.VERSION, T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG, T_GPS_WORKFLOW_TYPES.WORKFLOW_ORDER, T_GPS_WORKFLOW_TYPES.CREATED_BY, T_GPS_WORKFLOW_TYPES.LAST_UPDATED_BY, T_GPS_WORKFLOW_TYPES.CREATED_DATE, T_GPS_WORKFLOW_TYPES.LAST_UPDATED_DATE, T_GPS_WORKFLOW_TYPES.WORKFLOW_IMAGE, T_GPS_WORKFLOW_TYPES.DISPLAY_NAME, T_GPS_WORKFLOW_TYPES.DISPLAYON_PORTAL, T_GPS_WORKFLOW_TYPES.GROUP_NAME, T_GPS_WORKFLOW_TYPES.SEND_DEFAULT_MAIL, T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID, T_GPS_WORKFLOW_STATES.STATE_ID, T_GPS_WORKFLOW_STATES.STATE_NAME, T_GPS_WORKFLOW_STATES.GET_CONTROLLER_NAME, T_GPS_WORKFLOW_STATES.DESCRIPTION, T_GPS_WORKFLOW_STATES.ATTRIBUTE_ID, T_GPS_WORKFLOW_STATES.BUILDER_NAME, T_GPS_WORKFLOW_STATES.BUILD_FRAME_IMAGE, T_GPS_WORKFLOW_STATES.VIEWER_NAME, T_GPS_WORKFLOW_STATES.VIEW_FRAME_IMAGE, T_GPS_WORKFLOW_STATES.PROCESS_GET_CONTROLLER_NAME, T_GPS_WORKFLOW_STATES.EFFECTIVE_START_DATE, T_GPS_WORKFLOW_STATES.EFFECTIVE_END_DATE, T_GPS_WORKFLOW_STATES.UNDO_PUT_CONTROLLER_NAME, T_GPS_WORKFLOW_STATES.AND_STATE, T_GPS_WORKFLOW_STATES.STATE_DESCRIPTOR, T_GPS_WORKFLOW_STATES.OR_STATE, T_GPS_WORKFLOW_STATES.X, T_GPS_WORKFLOW_STATES.Y, T_GPS_WORKFLOW_STATES.SUBWORKFLOW_STATE, T_GPS_WORKFLOW_STATES.STARTNODE, T_GPS_WORKFLOW_STATES.MAIL_HANDLER_OBJECT_MAKER, T_GPS_WORKFLOW_STATES.VISIBLE_FLAG, T_GPS_WORKFLOW_STATES.STATE_ORDER, T_GPS_WORKFLOW_STATES.COLLABORATIVE_FLAG, T_GPS_WORKFLOW_STATES.FDT_ALIAS_NAME, T_GPS_WORKFLOW_STATES.FDT_FILE_NAME, T_GPS_WORKFLOW_STATES.STATE_TYPE, T_GPS_WORKFLOW_STATES.IMAGE, T_INSTANCES.APPLICATION_ID, T_INSTANCES.PRIORITY, T_INSTANCES.CREATION_DATE, T_INSTANCES.CREATED_BY, T_INSTANCES.LAST_UPDATE_DATE, T_INSTANCES.LAST_UPDATED_BY, T_INSTANCES.OWNER, T_INSTANCES.DUE_DATE, T_INSTANCES.PREDECESSOR, T_INSTANCES.ACTIVE, T_INSTANCES.PROMISED_DUE_DATE, T_INSTANCES.WORKFLOW_TYPE_ID, T_INSTANCES.CLOSED_DATE, T_GPS_INSTANCES.APPLICATION_ID, T_GPS_INSTANCES.WORKFLOW_TYPE_ID, T_GPS_INSTANCES.CURRENT_STATE_ID, --T_GPS_INSTANCES.INSTANCE_ID, T_GPS_INSTANCES.INSTANCE_PRESENT, T_GPS_INSTANCES.FROM_STATE_ID, T_GPS_INSTANCES.OWNER, T_GPS_INSTANCES.CREATED_DATE, T_GPS_INSTANCES.CYCLE_TIME, T_GPS_INSTANCES.ESCALATED, T_GPS_INSTANCES.ESCALATED_TO, T_GPS_INSTANCES.MOVED_OUT_DATE, T_GPS_INSTANCES.ACTIVE, T_GPS_INSTANCES.TIME_BASED_NOTIFICATION, T_GPS_INSTANCES.PRIORITY, T_GPS_INSTANCES.INSTANCE_PRIMARY_ID, T_GPS_INSTANCES.COMMENTS, T_GPS_INSTANCES.VISIBLE_FLAG, T_GPS_INSTANCES.MOVED_OUT_BY, T_GPS_INSTANCES.STATE_COMMENTS,*/ T_GPS_WORKFLOW_STATES.STATE_ORDER, decode(T_GPS_WORKFLOW_STATES.STATE_NAME, 'Self Appraisal', 'Self Assessment', 'Assessment based on feedback', 'Manager Assessment', 'Review & Update', 'Mentor Assessment - Mentor', 'Provide Info. to Mentor', 'Mentor Assessment - Employee', 'Workflow End', 'Completed', T_GPS_WORKFLOW_STATES.STATE_NAME) STATE_NAME, T_GPS_INSTANCE_PROPERTIES.INSTANCE_ID, -- T_INSTANCES.INSTANCE_ID, T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME, T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE /*, T_GPS_INSTANCE_PROPERTIES.PROPERTY_ID, T_GPS_INSTANCE_PROPERTIES.INSTANCE_PROPERTY_ID*/ FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, STATEGPS.T_INSTANCES T_INSTANCES, STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES, STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES WHERE T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID = T_INSTANCES.WORKFLOW_TYPE_ID AND T_INSTANCES.ACTIVE = 1 AND (T_GPS_INSTANCES.INSTANCE_PRESENT = 1 OR T_GPS_WORKFLOW_STATES.STATE_NAME = 'Workflow End') AND T_INSTANCES.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID AND T_GPS_INSTANCES.INSTANCE_ID = T_GPS_INSTANCE_PROPERTIES.INSTANCE_ID AND T_GPS_INSTANCES.CURRENT_STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID AND T_GPS_APPLICATIONS.APPLICATION_NAME LIKE 'Employee Information System' AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME LIKE 'Appraisal%' -- AND EXISTS -- (select 1 -- from STATEGPS.t_gps_instance_properties tgipp -- where tgipp.instance_id = -- T_GPS_INSTANCE_PROPERTIES.instance_id -- and tgipp.property_name = -- 'appraisalApplication.AppraisalDataBean.Appraisal_period' -- and tgipp.property_value in -- ('PD 2016-17 ANNUAL', 'HTP 2016 - ANNUAL')) -- and T_INSTANCES.INSTANCE_ID in (800322)--,800276,799965 and T_GPS_WORKFLOW_STATES.STATE_NAME NOT LIKE '%Collaborate Waiting%' and (T_GPS_WORKFLOW_STATES.STATE_NAME NOT LIKE '%Provide Info%' or not exists (select 1 from T_GPS_INSTANCES TGIN, T_GPS_WORKFLOW_STATES TGWS where TGIN.instance_id = T_GPS_INSTANCES.instance_id AND TGIN.INSTANCE_PRESENT = 1 ANd TGIN.current_state_id = tGWS.state_id and tgws.state_name like '%Review d%' )) ) pivot(max(property_value) for(property_name) in('appraisalApplication.AppraisalDataBean.Appraisal_ID' as PIV_Appraisal_ID, 'appraisalApplication.AppraisalDataBean.Appraisal_period' as PIV_Appraisal_period, 'appraisalApplication.AppraisalDataBean.Employee_ID' as PIV_Employee_ID, 'appraisalApplication.AppraisalDataBean.Employee_Mail' as PIV_Employee_Mail, 'appraisalApplication.AppraisalDataBean.Employee_Name' as PIV_Employee_Name, 'appraisalApplication.AppraisalDataBean.Manager_ID' as PIV_Manager_ID, 'appraisalApplication.AppraisalDataBean.Manager_Name' as PIV_Manager_Name, 'appraisalApplication.AppraisalDataBean.Manager_mail' as PIV_Manager_mail, 'appraisalApplication.AppraisalDataBean.Mentor_ID' as PIV_Mentor_ID, 'appraisalApplication.AppraisalDataBean.Mentor_Mail' as PIV_Mentor_Mail, 'appraisalApplication.AppraisalDataBean.Mentor_Name' as PIV_Mentor_Name, 'appraisalApplication.AppraisalDataBean.Period_ID' as PIV_Period_ID, 'appraisalApplication.AppraisalDataBean.employeeUser' as PIV_employeeUser, 'appraisalApplication.AppraisalDataBean.managerUser' as PIV_managerUser, 'appraisalApplication.AppraisalDataBean.mentorUser' as PIV_mentorUser)) order by 1, STATE_ORDER, PIV_Employee_ID