& "SELECT j.id, NVL2(ssv.name, ssv.name, odv.name) AS object_name, j.created_date, issue_from, " _ & "CONCAT(CONCAT(su1.first_name, ' '), su1.last_name) AS created_by_name, CONCAT(CONCAT(su2.first_name, ' '), su2.last_name) AS assigned_by_name, " _ & "j.customer_case, j.issue_type_id, " _ & "CASE WHEN SUBSTR(t3.ccc, 1, 4000) LIKE '%Ticket is created%' THEN 'Open' " _ & "WHEN SUBSTR(t3.ccc, 1, 4000) LIKE 'From - to %' THEN 'Assigned' " _ & "ELSE SUBSTR(t3.ccc, 1, 4000) END AS status, t3.created_date " _ & "FROM jira_mini j " _ & "JOIN (SELECT jma.jira_mini_id, SUBSTR(jma.action_content, 1, 4000) AS ccc, jma.created_date, jma.action_type_id " _ & "FROM jira_mini_action jma " _ & "WHERE jma.created_date >= TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') " _ & "AND jma.created_date < ADD_MONTHS(TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS'), 1) " _ & "AND (jma.action_type_id IN (1, 5)) " _ & "AND jma.jira_mini_id IN (SELECT jm.id " _ & "FROM jira_mini jm " _ & "WHERE (jm.issue_from LIKE 'APEX500' OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL)) " _ & "AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) " _ & "UNION SELECT a.jira_mini_id, SUBSTR(a.action_content, 1, 4000) AS ccc, a.created_date, a.action_type_id " _ & "FROM jira_mini_action a " _ & "WHERE a.created_date = (SELECT MAX(b.created_date) " _ & "FROM jira_mini_action b " _ & "WHERE (b.action_type_id IN (1, 5)) " _ & "AND b.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') " _ & "AND a.jira_mini_id = b.jira_mini_id) " _ & "AND (a.action_type_id IN (1, 5)) " _ & "AND SUBSTR(a.action_content, 1, 4000) <> 'Close' " _ & "AND a.jira_mini_id NOT IN (SELECT jm.id " _ & "FROM data_management.jira_mini jm " _ & "JOIN (SELECT jma.jira_mini_id, MAX(jma.created_date) AS max2 " _ & "FROM data_management.jira_mini_action jma " _ & "WHERE jma.action_type_id = 1 " _ & "AND SUBSTR(jma.action_content, 1, 4000) = 'Close' " _ & "AND jma.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') " _ & "GROUP BY jma.jira_mini_id) t2 ON jm.id = t2.jira_mini_id " _ & "WHERE jm.assigned_by IS NOT NULL " _ & "AND jm.issue_status_id = 8 " _ & "AND jm.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS')) " _ & "AND a.jira_mini_id IN (SELECT jm.id " _ & "FROM jira_mini jm " _ & "WHERE (jm.issue_from LIKE 'APEX500' OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL)) " _ & "AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS'))) t3 ON j.id = t3.jira_mini_id " _ & "LEFT JOIN sources.series_dataset_v ssv ON ssv.id = j.object_id " _ & "LEFT JOIN sources.outage_dataset_v odv ON odv.id = j.object_id " _ & "LEFT JOIN safe_user su1 ON su1.id = j.created_by " _ & "LEFT JOIN safe_user su2 ON su2.id = j.assigned_by " _ & "ORDER BY j.id, t3.created_date ASC;"