View HLPDTEST.STATEGPS.T_SLA_RESOLUTION_VIOLATION_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
WORKFLOW_TYPE_NAME varchar2 255
ISSUE_NUMBER varchar2 100  √  null
ISSUE_TITLE varchar2 4000  √  null
SLA_BLEACH_TIME varchar2 152  √  null
RESOLUTION_TIME varchar2 47  √  null
USER_NAME varchar2 302  √  null
APPLICATION_MODULE varchar2 50  √  null
PRIORITY varchar2 255
T_PROJECT_CUSTOM_PRIORITY.PRIORITY Implied Constraint R
SLA_ID number 22
T_PROJECT_SLAS.SLA_ID Implied Constraint R
STATE_NAME varchar2 255  √  null
STATE_DESCRIPTOR varchar2 100  √  null

Analyzed at Wed Dec 08 01:06 IST 2021

View Definition:
SELECT application_name, workflow_type_name, issue_number, issue_title, trunc(minutes / 1440) || ' day(s), ' || trunc(mod(minutes, 1440) / 60) || ' hour(s) and ' || round(mod(minutes, 60), 0) || ' minute(s)' sla_bleach_time, resolution_time || 'hour(s)' resolution_time, (SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name from tas.t_users k where k.user_name = project_sla.user_name) user_name, application_module, priority, sla_id, state_name, state_descriptor FROM (select (total_time_taken_hrs - resolution_time) * 60 minutes, resolution_time, issue_number, issue_title, application_name, workflow_type_name, user_name, application_module, priority, sla_id, state_name, state_descriptor from (select total_time_taken_hrs, c.resolution_time, i.issue_number, i.issue_title, i.application_module, d.priority, application_name, workflow_type_name /*,tpt.user_name*/, c.sla_id, a.APPLICATION_ID, user_name, a.state_name, state_descriptor from tri2gps_dev.issues i, t_instances y, (select sum(24 * (to_date(to_char(decode(y.moved_out_date, null, decode(k.state_name, 'Issue Closed', y.created_date, sysdate), y.moved_out_date), 'MM-DD-YYYY hh24:mi'), 'MM-DD-YYYY hh24:mi') - to_date(to_char(y.created_date, 'MM-DD-YYYY hh24:mi'), 'MM-DD-YYYY hh24:mi'))) total_time_taken_hrs, (select ws.state_descriptor from t_gps_workflow_states ws where ws.state_name = k.state_name and ws.workflow_type_id = y.workflow_type_id) state_descriptor, decode((select ws.state_descriptor from t_gps_workflow_states ws where ws.state_name = k.state_name and ws.workflow_type_id = y.workflow_type_id), 'Workflow Move',max(CD.user_name),k.user_name) user_name, decode((select ws.state_descriptor from t_gps_workflow_states ws where ws.state_name = k.state_name and ws.workflow_type_id = y.workflow_type_id), 'Workflow Move',max(CD.state_name),k.state_name) state_name, instance_id, k.APPLICATION_NAME, y.WORKFLOW_TYPE_ID, ia_issue_number, k.WORKFLOW_TYPE_NAME, /* k.state_name, k.user_name,*/ k.APPLICATION_ID, y.workflow_type_id from t_gps_instances y, t_issues_created_by_me_v k,CHILD_STATE_DETAILS_V CD where k.PROPERTY3 = y.instance_id and k.ia_issue_number= cd.parent_issue_number(+) --and k.application_name='ORACLE ERP India' group by instance_id, k.APPLICATION_ID, y.WORKFLOW_TYPE_ID, k.APPLICATION_NAME, k.ia_issue_number, k.WORKFLOW_TYPE_NAME, k.state_name, k.user_name) a, T_PROJECT_SLAS c, T_PROJECT_CUSTOM_PRIORITY d where i.instance_id = a.instance_id and i.instance_id = y.instance_id and (a.application_id = c.project_id) and c.priority = d.priority and y.priority = d.start_range and i.parent_issue_number is null and total_time_taken_hrs > c.resolution_time)) project_sla
 
Possibly Referenced Tables/Views:


Close relationships: