View HLPDTEST.STATEGPS.T_TICKET_DETAILS_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
APPLICATION_NAME varchar2 255  √  null
APPLICATION_ID number 22  √  null
T_GPS_APPLICATIONS.APPLICATION_ID Implied Constraint R
WORKFLOW_TYPE_NAME varchar2 255  √  null
WORKFLOW_TYPE_ID number 22  √  null
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID Implied Constraint R
INSTANCE_ID number 22  √  null
T_GPS_WORKFLOW_INSTANCES.INSTANCE_ID Implied Constraint R
STATE_ID number 22  √  null
T_GPS_WORKFLOW_STATES.STATE_ID Implied Constraint R
REGION varchar2 2000  √  null
COUNTRY varchar2 2000  √  null
ENTITY varchar2 2000  √  null
APPLICATION_MODULE varchar2 50  √  null
ISSUE_NUMBER varchar2 100  √  null
ISSUE_TITLE varchar2 4000  √  null
ISSUE_TYPE varchar2 100  √  null
PRIORITY varchar2 255  √  null
T_PROJECT_CUSTOM_PRIORITY.PRIORITY Implied Constraint R
PRIORITY_NUMBER number 22  √  null
ATTRIBUTE1 varchar2 2000  √  null
ATTRIBUTE2 varchar2 2000  √  null
ATTRIBUTE3 varchar2 2000  √  null
ATTRIBUTE4 varchar2 2000  √  null
ATTRIBUTE5 varchar2 2000  √  null
ATTRIBUTE6 varchar2 2000  √  null
ATTRIBUTE7 varchar2 2000  √  null
ATTRIBUTE8 varchar2 2000  √  null
ATTRIBUTE9 varchar2 2000  √  null
ATTRIBUTE10 varchar2 2000  √  null
ATTRIBUTE11 varchar2 2000  √  null
ATTRIBUTE12 varchar2 2000  √  null
ATTRIBUTE13 varchar2 2000  √  null
ATTRIBUTE14 varchar2 2000  √  null
ATTRIBUTE15 varchar2 2000  √  null
USER_NAME varchar2 2000  √  null
USER_FULL_NAME varchar2 302  √  null
STATE_NAME varchar2 255  √  null
CREATED_DATE date 7  √  null
OWNER varchar2 302  √  null
CREATED_BY varchar2 255  √  null
PARENT_ISSUE_NUMBER varchar2 100  √  null
PROJECT_TASK_ID number 22  √  null
T_PROJECT_TASK.PROJECT_TASK_ID Implied Constraint R
STATUS varchar2 18  √  null
STATE_ORDER number 22  √  null
RESOURCE_NAME varchar2 2000  √  null
DUE_DATE date 7  √  null
MANAGER_COMMENTS varchar2 4000  √  null

Analyzed at Sat Jul 02 01:05 IST 2022

View Definition:
select (SELECT APPLICATION_NAME FROM T_GPS_APPLICATIONS tga WHERE tga.APPLICATION_ID = i.PROJECT_ID) APPLICATION_NAME, tga.application_id, tgwt.workflow_type_name, tgwt.workflow_type_id, ti.instance_id, tgws.state_id, ATTRIBUTE22 region, ATTRIBUTE23 country, ATTRIBUTE24 entity, i.application_module, i.issue_number, i.issue_title, i.issue_type, tpcp.priority, ti.priority priority_number, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, tpt.user_name, (SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name from tas.t_users k where k.user_name = tpt.user_name) user_full_name, tgws.state_name, i.created_date, (SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name from tas.t_users k where k.user_id = i.created_by) owner, (SELECT k.user_name from tas.t_users k where k.user_id = I.created_by) created_by, i.parent_issue_number, tpt.project_task_id, 'STARTED' STATUS, tgws.STATE_ORDER, tpt.RESOURCE_NAME, i.DUE_DATE, ( SELECT INS.STATE_COMMENTS FROM T_GPS_INSTANCES INS WHERE INS.INSTANCE_PRIMARY_ID = ( SELECT MAX(INSS.INSTANCE_PRIMARY_ID) FROM T_GPS_INSTANCES INSS, STATEGPS.T_GPS_WORKFLOW_STATES WTS WHERE WTS.STATE_ID = INSS.FROM_STATE_ID AND WTS.STATE_TYPE='Hold' --AND INSS.CURRENT_STATE_ID = TGI.CURRENT_STATE_ID AND INSS.STATE_COMMENTS IS NOT NULL AND INSS.INSTANCE_ID = TI.INSTANCE_ID ) ) MANAGER_COMMENTS from t_gps_applications tga, t_gps_app_properties tgap, t_gps_workflow_types tgwt, t_gps_workflow_states tgws, t_project_task tpt, t_instances ti, t_gps_instances tgi, tri2gps_dev.issues i, t_project_custom_priority tpcp where tga.application_id = tgwt.application_id and tga.application_id = tgap.application_id and tgwt.workflow_type_id = tgws.workflow_type_id and tpt.instance_id = ti.instance_id and ti.instance_id = i.instance_id and tgi.instance_id = i.instance_id and tgws.state_id = tpt.state_id and tgws.state_id = tgi.current_state_id and tpt.state_id = tgi.current_state_id and ti.application_id = tga.application_id and ti.workflow_type_id = tgwt.workflow_type_id and ti.priority = tpcp.start_range and tga.visible_flag = 'true' and tgwt.visible_flag = 'true' and ti.active = 1 and tgi.instance_present = 1 and tgap.property_name = 'TICKET_MANAGEMENT_APPLICATION' and tgap.property_value = 'TRUE' --and issue_number='CR00124' UNION select (SELECT APPLICATION_NAME FROM T_GPS_APPLICATIONS tga WHERE tga.APPLICATION_ID = i.PROJECT_ID) APPLICATION_NAME, TGA.application_id, TGWT.workflow_type_name, TGWT.workflow_type_id, TI.instance_id, TGWS.state_id, ATTRIBUTE22 region, ATTRIBUTE23 country, ATTRIBUTE24 entity, I.application_module, I.issue_number, I.issue_title, i.issue_type, TPCP.priority, TI.priority priority_number, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, TPT.user_name, (SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name from tas.t_users k where k.user_name = TPT.user_name) user_full_name, TGWS.state_name, I.created_date, (SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name from tas.t_users k where k.user_id = I.created_by) owner, (SELECT k.USER_NAME from tas.t_users k where k.user_id = I.created_by) created_by, I.parent_issue_number, TPT.project_task_id, DECODE(( SELECT INSTANCE_PRESENT FROM T_GPS_INSTANCES A,T_GPS_WORKFLOW_STATES B WHERE A.INSTANCE_ID = TPT.INSTANCE_ID AND A.CURRENT_STATE_ID = B.STATE_ID AND( /*(b.state_descriptor = 'Workflow End' and a.instance_present = 0)or */B.STATE_ORDER IN( SELECT MAX(STATE_ORDER) FROM T_GPS_WORKFLOW_STATES A,T_INSTANCES Y WHERE A.WORKFLOW_TYPE_ID = Y.WORKFLOW_TYPE_ID AND Y.INSTANCE_ID = TPT.INSTANCE_ID ) AND A.INSTANCE_PRESENT = 1) ),0,'PATH NOT TRAVERSED', 1,'PATH NOT TRAVERSED','NOT STARTED') STATUS, TGWS.STATE_ORDER, TPT.RESOURCE_NAME, I.DUE_DATE, NULL FROM T_GPS_APPLICATIONS TGA, T_GPS_APP_PROPERTIES TGAP, T_GPS_WORKFLOW_TYPES TGWT, T_GPS_WORKFLOW_STATES TGWS, T_PROJECT_TASK TPT, T_INSTANCES TI, TRI2GPS_DEV.ISSUES I, T_PROJECT_CUSTOM_PRIORITY TPCP WHERE TGA.APPLICATION_ID = TGWT.APPLICATION_ID AND TGA.APPLICATION_ID = TGAP.APPLICATION_ID AND TGWT.WORKFLOW_TYPE_ID = TGWS.WORKFLOW_TYPE_ID AND TPT.INSTANCE_ID = TI.INSTANCE_ID AND TI.INSTANCE_ID = I.INSTANCE_ID AND TGWS.STATE_ID = TPT.STATE_ID AND TI.APPLICATION_ID = TGA.APPLICATION_ID AND TI.WORKFLOW_TYPE_ID = TGWT.WORKFLOW_TYPE_ID AND TI.PRIORITY = TPCP.START_RANGE AND TGA.VISIBLE_FLAG = 'true' AND TGWT.VISIBLE_FLAG = 'true' AND TI.ACTIVE = 1 AND TGAP.PROPERTY_NAME = 'TICKET_MANAGEMENT_APPLICATION' -- and issue_number='CR00124' AND(TPT.DAYSREMAINING IS NULL OR TPT.DAYSREMAINING <> 0) AND NOT EXISTS( SELECT 1 FROM T_GPS_INSTANCES TGI WHERE TGI.INSTANCE_ID = TI.INSTANCE_ID AND TGI.CURRENT_STATE_ID = TPT.STATE_ID AND INSTANCE_PRESENT = 1 ) AND TGAP.PROPERTY_VALUE = 'TRUE'
 
Possibly Referenced Tables/Views:


Close relationships: