View HLPDTEST.STATEGPS.T_APPRAISAL_STATUS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Wed May 25 01:05 IST 2022 |
View Definition:
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
Possibly Referenced Tables/Views:
![]() ![]() |