IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPOngoingwork')
	DROP PROCEDURE LD0SPOngoingwork
GO
CREATE PROCEDURE LD0SPOngoingwork (@userid VARCHAR(10))    
AS    
BEGIN    
    
IF OBJECT_ID('tempdb.. #ONGOINGWORK') IS NOT NULL    
    DROP TABLE #ONGOINGWORK    
    
CREATE TABLE #ONGOINGWORK    
(    
 AEVERKNR decimal(9,0),    
 AKTPNR varchar(12),    
 STARTDAT varchar(8),    
 TMARK decimal(1,0),    
 AEVERKTYP decimal(9,0),    
 ISCASEWORKER DECIMAL(1)    
)    
    
--HAND visa ej MEDHAND i samma utredning. Finns jag som handläggare är den andra intressant.    
INSERT INTO  #ONGOINGWORK (AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,ISCASEWORKER)    
SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,CASE WHEN HAND=@userid THEN 1 ELSE 0 END    
FROM LD0V0C C    
WHERE STOPDAT = '99999999' AND (HAND = @userid OR MEDHAND = @userid)    
UNION     
--två extra fem SELECT totalt. Kan samma komma två gånger.    
SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,1    
FROM LD0V0C C    
WHERE EXISTS(    
SELECT 'X'     
FROM LD0V0C20     
WHERE ARENDENR = C.AEVERKNR     
AND TOMDAT = '99999999' AND (HAND = @userid))    
UNION    
SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,0    
FROM LD0V0C C    
WHERE EXISTS(    
SELECT 'X'     
FROM LD0V0C20     
WHERE ARENDENR = C.AEVERKNR     
AND TOMDAT = '99999999' AND (MEDHAND = @userid))    
    
    
UNION     
SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,1    
FROM LD0V0C C    
WHERE EXISTS(    
SELECT 'X'     
FROM LD0V0C20     
WHERE VERKNR = C.AEVERKNR     
AND TOMDAT = '99999999' AND (HAND = @userid))    
UNION     
SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,0    
FROM LD0V0C C    
WHERE EXISTS(    
SELECT 'X'     
FROM LD0V0C20     
WHERE VERKNR = C.AEVERKNR     
AND TOMDAT = '99999999' AND (MEDHAND = @userid))    
    
DELETE D FROM    
(SELECT AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP,ISCASEWORKER, ROW_NUMBER()OVER(PARTITION BY AEVERKNR, AKTPNR, STARTDAT, TMARK, AEVERKTYP ORDER BY ISCASEWORKER DESC) RADNR    
FROM #ONGOINGWORK) D    
WHERE RADNR > 1;    
    
CREATE INDEX #IX_ONGOINGWORK ON #ONGOINGWORK    
(    
AEVERKNR,    
AEVERKTYP    
)    
INCLUDE (AKTPNR, STARTDAT, TMARK)    
    
    
    
    
SELECT TEMP.*, A.NAMN, A.KUNDNUMMER FROM    
(    
 SELECT C11.UPDDAT, C11.UPDSIGN, C11.TID, C11.HTEXT1, C11.HTEXT2, C11.HTEXT3, C11.HTEXT4, ONGOINGWORK.*, ROW_NUMBER() OVER (PARTITION BY C11.AEVERKNR ORDER BY C11.INSNR DESC,C11.UPDDAT DESC,TID DESC) RADNUMMER  
    ,CASE WHEN C20_1.INSNR > 0 THEN 1 ELSE 0 END AS HASONGOINGSERVICES  
  FROM LD0P0C11 C11    
JOIN (    
  SELECT #ONGOINGWORK.*, Q01.KLARTEXT AS TYP_T, Q01.XNUM40 AS FLOW_T FROM #ONGOINGWORK    
  JOIN LD0V0Q01 Q01 ON #ONGOINGWORK.AEVERKTYP = Q01.KOD AND Q01.KODTYP = 'UTREDTYP'    
  WHERE TMARK = 0    
  UNION ALL    
  SELECT #ONGOINGWORK.*, Q01.KLARTEXT AS TYP_T,     
   (SELECT TOP 1 Q01_1.XNUM40     
    FROM LD0P0C20 C20     
    INNER JOIN LD0V0Q01 Q01_1     
    ON C20.INSTYP = Q01_1.KOD AND Q01_1.KODTYP = 'INSATS' AND XNUM40 > 0    
    WHERE VERKNR = #ONGOINGWORK.AEVERKNR    
    ORDER BY C20.TOMDAT DESC, C20.INSNR DESC) AS FLOW_T     
  FROM #ONGOINGWORK    
  JOIN LD0V0Q01 Q01 ON #ONGOINGWORK.AEVERKTYP = Q01.KOD AND Q01.KODTYP = 'JOURNALTYP'    
  WHERE TMARK = 1    
 ) ONGOINGWORK    
 ON ONGOINGWORK.AEVERKNR = C11.AEVERKNR   
 LEFT JOIN LD0P0C20 C20_1 ON C11.INSNR = C20_1.INSNR AND C20_1.TOMDAT = '99999999'  
) TEMP  
INNER JOIN LD0V0A A     
ON TEMP.AKTPNR = A.PNR    
WHERE RADNUMMER = 1    
ORDER BY AEVERKNR DESC    
    
END  

GO
GRANT ALL ON LD0SPOngoingwork TO LD0ROANV
GO