View HLPDTEST.STATEGPS.T_APPRAISAL_STATUS_V Generated by
SchemaSpy
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
BRANCH_ID varchar2 20  √  null
STATE_NAME varchar2 255  √  null
INSTANCE_ID number 22
T_GPS_WORKFLOW_INSTANCES.INSTANCE_ID Implied Constraint R
APPRAISAL_ID varchar2 4000  √  null
APPRAISAL_PERIOD varchar2 4000  √  null
EMPLOYEE_ID varchar2 4000  √  null
EMPLOYEE_MAIL varchar2 4000  √  null
EMPLOYEE_NAME varchar2 4000  √  null
MANAGER_ID varchar2 4000  √  null
MANAGER_NAME varchar2 4000  √  null
MANAGER_MAIL varchar2 4000  √  null
MENTOR_ID varchar2 4000  √  null
MENTOR_MAIL varchar2 4000  √  null
MENTOR_NAME varchar2 4000  √  null
PERIOD_ID varchar2 4000  √  null
EMPLOYEEUSER varchar2 4000  √  null
MANAGERUSER varchar2 4000  √  null
MENTORUSER varchar2 4000  √  null
STATE_ORDER number 22  √  null

Analyzed at Sun Oct 18 01:05 IST 2020

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:


Close relationships: