View HLPDTEST.STATEGPS.T_TICKET_DETAILS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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:
![]() ![]() |