WITH x 
     AS (SELECT AA.ccti_category, 
                AA.assigned_to_group_name, 
                AA.case_id 
                ||'-' 
                ||AA.ticket_id 
                   AS 
                    TICKET_ID, 
                AA.ticket_priority, 
                AA.severity, 
                AA.ccti_item, 
                AA.ticket_description, 
                AA.description_long, 
                AA.assignedtoindividual, 
                AA.ticket_status, 
                AA.createddatetext, 
                AA.modifieddatetext, 
                AA.modifieddatetext - AA.createddatetext 
                   AS 
                    high_diff, 
                Pending_hrs(AA.createddatetext, AA.modifieddatetext, 'D', 'OFF', 
                '24' 
                ) 
                   AS 
                nor_diff, 
                AA.ticket_reason_code, 
                AA.item_id 
         FROM   inteq_incidents AA 
         WHERE  ( AA.ticket_status = 'Closed' 
                  AND Trunc(AA.modifieddatetext, 'MONTH') = 
                      Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
                 OR ( AA.ticket_status != 'Closed' 
                      AND Trunc(AA.createddatetext, 'MONTH') <= 
                          Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
         UNION 
         SELECT AA.ccti_category, 
                AA.assigned_to_group_name, 
                AA.case_id 
                ||'-' 
                ||AA.ticket_id 
                AS 
                TICKET_ID, 
                AA.ticket_priority, 
                '' 
                AS 
                SEVERITY, 
                AA.ccti_item, 
                AA.ticket_description, 
                AA.description_long, 
                AA.assignedtoindividual, 
                AA.ticket_status, 
                AA.createddatetext, 
                AA.modifieddatetext, 
                AA.modifieddatetext - AA.createddatetext 
                AS 
                high_diff, 
                Pending_hrs(AA.createddatetext, AA.modifieddatetext, 'D', 'OFF', 
                '24' 
                ) 
                AS 
                nor_diff, 
                AA.ticket_reason_code, 
                AA.item_id 
         FROM   inteq_service_requests AA 
         WHERE  ( AA.ticket_status = 'Closed' 
                  AND Trunc(AA.modifieddatetext, 'MONTH') = 
                      Add_months(Trunc(SYSDATE, 'MONTH'), -1) ) 
                 OR ( AA.ticket_status != 'Closed' 
                      AND Trunc(AA.createddatetext, 'MONTH') <= 
                          Add_months(Trunc(SYSDATE, 'MONTH'), -1) )), 
     y 
     AS (SELECT BB.item_id, 
                Max(CASE 
                      WHEN BB.attr_name = 'Ticket Type' THEN BB.attr_value 
                    END) TICKETTYPE, 
                Max(CASE 
                      WHEN BB.attr_name = 'Ticket Cause' THEN BB.attr_value 
                    END) TICKETCAUSE, 
                Max(CASE 
                      WHEN BB.attr_name = 'HMHIT Involved' THEN BB.attr_value 
                    END) HMHITINVOLVED, 
                Max(CASE 
                      WHEN BB.attr_name = 'Work Around' THEN BB.attr_value 
                    END) WORKAROUND 
         FROM   inteq_custom_fields BB 
         WHERE  BB.item_id IN (SELECT item_id 
                               FROM   x) 
         GROUP  BY BB.item_id 
         ORDER  BY BB.item_id), 
     z 
     AS (SELECT item_id, 
                actiontakendatetext, 
                Lead(actiontakendatetext, 1, SYSDATE) 
                  over ( 
                    PARTITION BY item_id 
                    ORDER BY actiontakendatetext) AS end_date, 
                status 
         FROM   bi_prod.inteq_history 
         WHERE  Trim(status) IS NOT NULL 
                AND item_id IN (SELECT item_id 
                                FROM   x)), 
     w 
     AS (SELECT item_id, 
                SUM(Pending_hrs(actiontakendatetext, end_date, 'H', 'ON', '8')) 
                AS 
                   pend_hrs, 
                SUM(Pending_hrs(actiontakendatetext, end_date, 'D', 'ON', '8')) 
                AS 
                   pend_dys 
         FROM   z 
         WHERE  z.status = 'Pending' 
         GROUP  BY item_id), 
     v 
     AS (SELECT a.item_id, 
                b.actiontakendatetext, 
                b.act_name, 
                Row_number() 
                  over ( 
                    PARTITION BY a.item_id 
                    ORDER BY b.actiontakendatetext) AS row_num 
         FROM   x a 
                left outer join inteq_history b 
                             ON a.item_id = b.item_id 
                                AND a.assignedtoindividual = 
                                    b.assignedtoindividual 
                                AND a.assigned_to_group_name = b.group_name), 
     u 
     AS (SELECT item_id, 
                actiontakendatetext 
         FROM   v 
         WHERE  row_num = 1) 
SELECT x1.ccti_category, 
       x1.assigned_to_group_name, 
       x1. ticket_id, 
       x1.ticket_priority, 
       x1.severity, 
       x1.ccti_item, 
       x1.ticket_description, 
       x1.description_long, 
       x1.assignedtoindividual, 
       x1.ticket_status, 
       x1.createddatetext, 
       x1.modifieddatetext, 
       x1.high_diff, 
       x1.nor_diff, 
       x1.ticket_reason_code, 
       x1.item_id, 
       y1.tickettype, 
       y1.ticketcause, 
       y1.hmhitinvolved, 
       y1.workaround, 
       w1.pend_hrs, 
       w1.pend_dys, 
       u1.actiontakendatetext 
FROM   x x1 
       left outer join y y1 
                    ON x1.item_id = y1.item_id 
       left outer join w w1 
                    ON x1.item_id = w1.item_id 
       left outer join u u1 
                    ON x1.item_id = u1.item_id 
ORDER  BY x1.item_id