View HLPDTEST.STATEGPS.T_ISSUES_CREATED_BY_ME_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Mar 01 01:05 IST 2021 |
View Definition:
SELECT ISSUES.ISSUE_NUMBER IA_ISSUE_NUMBER,
T_GPS_INSTANCES.Instance_Primary_Id,
ISSUES.ISSUE_TITLE IA_ISSUE_TITLE,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_GPS_APPLICATIONS.APPLICATION_ID,
T_INSTANCES.OWNER,
(SELECT user_name
FROM t_project_task
WHERE state_id = T_GPS_INSTANCES.current_state_id
AND instance_id = T_INSTANCES.instance_id
AND rownum = 1) USER_NAME,
(SELECT DECODE(ws.state_descriptor,
'Workflow End',
'Issue Closed',
'AND State',
'Issue Closed',decode(ws.State_Order,
(select max(state_order)
from stategps.t_gps_workflow_states a
where a.workflow_type_id = ws.workflow_type_id group by a.workflow_type_id),
'Issue Closed',ws.state_name))
FROM t_gps_workflow_states ws
WHERE ws.state_id = T_GPS_INSTANCES.current_state_id
AND rownum = 1 ) STATE_NAME,
T_INSTANCES.CREATION_DATE START_DATE,
NVL((SELECT FINISH_DATE
FROM T_PROJECT_TASK
WHERE instance_id = T_INSTANCES.INSTANCE_ID
AND STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID
AND rownum = 1),
(SELECT MAX(MOVED_OUT_DATE)
FROM t_gps_instances
WHERE INSTANCE_ID = T_INSTANCES.INSTANCE_ID)) END_DATE,
(SELECT SUM(NVL(daysremaining, duration))
FROM t_project_task
WHERE instance_id = T_INSTANCES.instance_id
AND duration > 0
AND daysremaining > 0) DAYSREMAING,
(SELECT comments
FROM t_project_task
WHERE state_id = T_GPS_INSTANCES.current_state_id
AND instance_id = T_INSTANCES.instance_id
AND rownum = 1) COMMENTS,
NVL(T_INSTANCES.PRIORITY, 99) PRIORITY,
T_INSTANCES.INSTANCE_ID PROPERTY3,
T_INSTANCES.CREATION_DATE crtd_date,
DECODE(T_GPS_INSTANCES.active, 1, 'Active', 'Inactive') Active,
T_INSTANCES.DUE_DATE,
NULL DURATION,
T_GPS_INSTANCES.current_state_id current_state_id,
T_GPS_INSTANCES.Instance_Present,
T_GPS_WORKFLOW_STATES.state_type,
NULL FINAL_STATE_ID,
(SELECT DOCUMENT_ID
FROM t_instance_documents
WHERE instance_id = T_INSTANCES.Instance_Id
AND rownum = 1) DOCUMENT_ID,
(SELECT DOCUMENT
FROM t_instance_documents
WHERE instance_id = T_INSTANCES.Instance_Id
AND rownum = 1) DOCUMENT_PATH,
x.approver,
x.approver_comments,
x.approved_on,
(select sum(a.non_billable_hours)
from stategps.t_project_activity a, stategps.t_project_task b
where a.PROJECT_TASK_ID = b.PROJECT_TASK_ID
AND instance_id = T_INSTANCES.instance_id
group by b.INSTANCE_ID) time_spent_in_hours,
(SELECT b.feedback_answer
FROM t_gps_wf_state_feedback_form a,
t_gps_instance_feedback b,
t_gps_instances c,
t_instances d
where a.question_id = b.question_id
AND a.state_id = c.Current_State_Id
AND C.INSTANCE_ID = d.instance_id
and d.instance_id = T_INSTANCES.INSTANCE_ID
and b.feedback_answer IS NOT NULL
and UPPER(a.question) like UPPER('%Time%')
and c.from_state_id is not null
and b.instance_primary_id = c.instance_primary_id) TIME_IT_TOOK,
(SELECT b.feedback_answer
FROM t_gps_wf_state_feedback_form a,
t_gps_instance_feedback b,
t_gps_instances c,
t_instances d
where a.question_id = b.question_id
AND a.state_id = c.Current_State_Id
AND C.INSTANCE_ID = d.instance_id
and d.instance_id = T_INSTANCES.INSTANCE_ID
and b.feedback_answer IS NOT NULL
and UPPER(a.question) like UPPER('%Solution%')
and c.from_state_id is not null
and b.instance_primary_id = c.instance_primary_id) OVERALL_QUALITY,
(SELECT b.feedback_answer
FROM t_gps_wf_state_feedback_form a,
t_gps_instance_feedback b,
t_gps_instances c,
t_instances d
where a.question_id = b.question_id
AND a.state_id = c.Current_State_Id
AND C.INSTANCE_ID = d.instance_id
and d.instance_id = T_INSTANCES.INSTANCE_ID
and b.feedback_answer IS NOT NULL
and UPPER(a.question) like UPPER('%documentation%')
and c.from_state_id is not null
and b.instance_primary_id = c.instance_primary_id) QUALITY_OF_THE_DOCUMENTATION,
(SELECT b.feedback_answer
FROM t_gps_wf_state_feedback_form a,
t_gps_instance_feedback b,
t_gps_instances c,
t_instances d
where a.question_id = b.question_id
AND a.state_id = c.Current_State_Id
AND C.INSTANCE_ID = d.instance_id
and d.instance_id = T_INSTANCES.INSTANCE_ID
and b.feedback_answer IS NOT NULL
and UPPER(a.question) like UPPER('%Communication%')
and c.from_state_id is not null
and b.instance_primary_id = c.instance_primary_id) QUALITY_OF_COMMUNICATION,
T_GPS_WORKFLOW_STATES.state_name currentstate,
t_gps_instances.moved_out_date moved_out_date,
T_GPS_INSTANCES.State_Comments,
ISSUES.Parent_Issue_Number PARENT_ISSUE_NUMBER
FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_APP_PROPERTIES T_GPS_APP_PROPERTIES,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_INSTANCES T_INSTANCES,
--STATEGPS.V_INSTANCE_PROPS V_INSTANCE_PROPS,
STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES,
(SELECT DISTINCT pt.user_name approver,
pt.comments approver_comments,
gi.moved_out_date approved_on,
gi.instance_id instance_id
FROM t_gps_workflow_states ws,
t_project_task pt,
t_gps_instances gi
WHERE ws.state_id = pt.state_id
AND pt.duration > 0
AND gi.instance_id = pt.instance_id
AND gi.current_state_id = pt.state_id
AND gi.instance_present = 0
AND UPPER(ws.state_name) LIKE '%APPROV%'
AND state_order IN (SELECT MAX(state_order)
FROM t_gps_workflow_states
GROUP BY workflow_type_id)) x,
TRI2GPS_DEV.ISSUES ISSUES
WHERE T_INSTANCES.INSTANCE_ID = ISSUES.INSTANCE_ID
AND T_GPS_APPLICATIONS.APPLICATION_ID = T_INSTANCES.APPLICATION_ID
AND T_GPS_APPLICATIONS.APPLICATION_ID =
T_GPS_APP_PROPERTIES.APPLICATION_ID
AND T_GPS_APP_PROPERTIES.PROPERTY_NAME = 'PROJECTPLAN_APPLICATION'
AND T_GPS_APP_PROPERTIES.PROPERTY_VALUE = 'TRUE'
AND T_INSTANCES.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID
AND T_GPS_INSTANCES.WORKFLOW_TYPE_ID =
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND 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.STATE_ID = T_GPS_INSTANCES.CURRENT_STATE_ID
AND T_GPS_WORKFLOW_STATES.STATE_DESCRIPTOR NOT IN ('AND State')
AND (T_GPS_INSTANCES.INSTANCE_PRESENT = 1 OR
T_GPS_WORKFLOW_STATES.STATE_ORDER =
(SELECT MAX(state_order)
FROM t_gps_workflow_states
WHERE workflow_type_id = T_GPS_WORKFLOW_TYPES.workflow_type_id))
AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true'
AND T_GPS_WORKFLOW_STATES.VISIBLE_FLAG = 'true'
--AND T_INSTANCES.ACTIVE = 1
AND T_INSTANCES.INSTANCE_ID = x.instance_id(+)
AND T_GPS_INSTANCES.From_State_Id IS NOT NULL
--AND T_GPS_INSTANCES.ACTIVE = 1
GROUP BY ISSUES.ISSUE_NUMBER,
ISSUES.ISSUE_TITLE,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME,
T_INSTANCES.PRIORITY,
T_GPS_INSTANCES.CURRENT_STATE_ID,
T_GPS_INSTANCES.ACTIVE,
T_INSTANCES.CREATION_DATE,
T_GPS_APPLICATIONS.APPLICATION_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID,
T_INSTANCES.INSTANCE_ID,
T_GPS_WORKFLOW_STATES.STATE_ID,
T_INSTANCES.OWNER,
T_INSTANCES.DUE_DATE,
x.approver,
x.approver_comments,
x.approved_on,
T_GPS_INSTANCES.Instance_Primary_Id,
T_GPS_WORKFLOW_STATES.state_name,
t_gps_instances.moved_out_date,
T_GPS_INSTANCES.State_Comments,
T_GPS_INSTANCES.Instance_Present,
T_GPS_WORKFLOW_STATES.state_type,
ISSUES.Parent_Issue_Number
Possibly Referenced Tables/Views:
![]() ![]() |