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)))
AND AA.ASSIGNED_TO_GROUP_NAME IN (
'Publishing Technology Research',
'Publishing Technology',
'RPC Publishing Technology')
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)))
AND AA.ASSIGNED_TO_GROUP_NAME IN (
'Publishing Technology Research',
'Publishing Technology',
'RPC Publishing Technology')),
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),
ab as (select wl.item_id, sum(wl.WORK_TIME_SPENT) as WTS from inteq_work_log wl where wl.item_id in (SELECT ITEM_ID
FROM X) group by item_id)
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,
ab1.WTS
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
left outer join ab ab1
ON X1.ITEM_ID = ab1.ITEM_ID
order by x1.item_id