View HLPDTEST.STATEGPS.T_SLA_VIOLATION_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Tue May 24 01:05 IST 2022 |
View Definition:
SELECT application_name,
workflow_type_name,
issue_number,
created_date,
created_by,
(SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name
from tas.t_users k
where k.user_id = project_sla.created_by)created_by_name,
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,
resonse_time || 'hour(s)' resonse_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
FROM (select (diff_hrs - resonse_time ) * 60 minutes,
resonse_time,
issue_number,
created_date,
created_by,
issue_title,
application_name,
workflow_type_name,user_name,application_module,priority,sla_id
from (select 24 *
(sysdate -
to_date(to_char(a.created_date, 'MM-DD-YYYY hh24:mi'),
'MM-DD-YYYY hh24:mi')) diff_hrs,
c.resonse_time,
i.issue_number,
i.created_date,
i.created_by,
i.issue_title,i.application_module,d.priority,
application_name,
workflow_type_name,tpt.user_name,c.sla_id
from t_gps_applications tga,
t_gps_workflow_types tgwt,
t_gps_instances a,
tri2gps_dev.issues i,
t_instances y,
t_gps_workflow_states b,
T_PROJECT_SLAS c,
T_PROJECT_CUSTOM_PRIORITY d,
t_project_task tpt
where tga.application_id = y.application_id
and tga.application_id = tgwt.application_id
and tgwt.workflow_type_id = y.workflow_type_id
and a.current_state_id = b.state_id
and i.instance_id = a.instance_id
and i.instance_id = y.instance_id
and b.state_type = 'ORIGIN_STATE'
and tpt.instance_id=y.instance_id
and tpt.state_id= a.current_state_id
and a.moved_out_date is null
and (a.application_id = c.project_id)
and c.priority = d.priority
and y.priority = d.start_range
--and a.instance_id = 839981
and tga.visible_flag = 'true'
and tgwt.visible_flag = 'true'
and 24 *
(sysdate + 6 -
to_date(to_char(a.created_date, 'MM-DD-YYYY hh24:mi'),
'MM-DD-YYYY hh24:mi')) > c.resonse_time)) project_sla
union
SELECT application_name,
workflow_type_name,
issue_number,
created_date,
created_by,
(SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name
from tas.t_users k
where k.user_id = common_sla.created_by)created_by_name,
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,
resonse_time || 'hour(s)' resonse_time,(SELECT k.first_name || ' ' || k.middle_name || ' ' || k.last_name
from tas.t_users k
where k.user_name = common_sla.user_name)user_name,application_module,priority,sla_id
FROM (select (diff_hrs - resonse_time ) * 60 minutes,
resonse_time,
issue_number,
created_date,
created_by,
issue_title,
application_name,
workflow_type_name,user_name,application_module,priority,sla_id
from (select 24 *
(sysdate -
to_date(to_char(a.created_date, 'MM-DD-YYYY hh24:mi'),
'MM-DD-YYYY hh24:mi')) diff_hrs,
c.resonse_time,
i.issue_number,
i.created_date,
i.created_by,
i.issue_title,
application_name,
workflow_type_name,tpt.user_name,i.application_module,d.priority,c.sla_id
from t_gps_applications tga,
t_gps_workflow_types tgwt,
t_gps_instances a,
tri2gps_dev.issues i,
t_instances y,
t_gps_workflow_states b,
T_PROJECT_SLAS c,
T_PROJECT_CUSTOM_PRIORITY d,
t_project_task tpt
where tga.application_id = y.application_id
and tga.application_id = tgwt.application_id
and tgwt.workflow_type_id = y.workflow_type_id
and a.current_state_id = b.state_id
and i.instance_id = a.instance_id
and i.instance_id = y.instance_id
and tpt.instance_id=y.instance_id
and tpt.state_id= a.current_state_id
and b.state_type = 'ORIGIN_STATE'
and a.moved_out_date is null
--and (project_id is null)
/*and c.priority = \*'Critical'*\y.priority
*/
and c.priority = d.priority
and y.priority = d.start_range
--and a.instance_id = 839981
and tga.visible_flag = 'true'
and tgwt.visible_flag = 'true'
and (c.project_id is null and not exists
(select project_id
from T_PROJECT_SLAS a
where a.priority = d.priority
and a.project_id = a.application_id))
and 24 *
(sysdate + 6 -
to_date(to_char(a.created_date, 'MM-DD-YYYY hh24:mi'),
'MM-DD-YYYY hh24:mi')) > c.resonse_time))common_sla
Possibly Referenced Tables/Views:
![]() ![]() |