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'