SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPListOngoingWork')
	DROP PROCEDURE LD0SPListOngoingWork
GO
CREATE PROCEDURE LD0SPListOngoingWork
  @CaseWorkersXml VARCHAR(MAX)
AS
BEGIN
  set nocount on;
  DECLARE @XMLlist XML; SELECT @XMLList = @CaseWorkersXml;
  DECLARE @CaseWorkersTable AS TABLE ([UserName][varchar](100) NOT NULL);
  INSERT INTO @CaseWorkersTable SELECT x.y.value('.','nvarchar(100)') AS UserNames FROM @XMLList.nodes('/list/user') AS x(y);

  create table #ogw (
    AEVERKNR decimal(9,0), STARTDAT varchar(8), INVESTIGATIONTYPETEXT varchar(180),
    PERSONKEY decimal(9,0), AKTPNR varchar(12), PERSONNAME varchar(180), CASEWORKERNAME varchar(180),
    CASEWORKERID varchar(100), AKTUALNR INT, AKTUALTYP INT
  );

  insert into #ogw
  SELECT
    C.AEVERKNR, C.STARTDAT, Q.KLARTEXT AS INVESTIGATIONTYPETEXT, A.KUNDNUMMER AS PERSONKEY,
    C.AKTPNR, A.NAMN AS PERSONNAME, H.NAMN AS CASEWORKERNAME, H.HAND AS CASEWORKERID,0 ,-1
  FROM LD0V0C C
    join @CaseWorkersTable t on c.hand = t.username
    INNER JOIN LD0V0Q01 Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP 
    INNER JOIN LD0P0I H ON C.HAND = H.HAND
    LEFT OUTER JOIN LD0P0A A ON C.AKTPNR = A.PNR
  WHERE C.STOPDAT = '99999999' and C.TMARK = 0;

  insert into #ogw
  SELECT
    C.AEVERKNR, C.STARTDAT, Q.KLARTEXT AS INVESTIGATIONTYPETEXT, A.KUNDNUMMER AS PERSONKEY,
    C.AKTPNR, A.NAMN AS PERSONNAME, H.NAMN AS CASEWORKERNAME, H.HAND AS CASEWORKERID,0,-1
  FROM LD0V0C C
    join @CaseWorkersTable t on c.medhand = t.username
    INNER JOIN LD0V0Q01 Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP 
    INNER JOIN LD0P0I H ON C.HAND = H.HAND
    LEFT OUTER JOIN LD0P0A A ON C.AKTPNR = A.PNR
  WHERE C.STOPDAT = '99999999' and C.TMARK = 0;

  insert into #ogw
  SELECT
    C.AEVERKNR, C.STARTDAT, Q.KLARTEXT AS INVESTIGATIONTYPETEXT, A.KUNDNUMMER AS PERSONKEY,
    C.AKTPNR, A.NAMN AS PERSONNAME, H.NAMN AS CASEWORKERNAME, H.HAND AS CASEWORKERID,0,-1
  FROM LD0V0C C
    join LD0V0CCASEWORKERSERVICES x on c.aeverknr = x.arendenr
    join @CaseWorkersTable t on x.hand = t.username
    INNER JOIN LD0V0Q01 Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP 
    INNER JOIN LD0P0I H ON C.HAND = H.HAND
    LEFT OUTER JOIN LD0P0A A ON C.AKTPNR = A.PNR
  WHERE C.STOPDAT = '99999999' and C.TMARK = 0;

  insert into #ogw
  SELECT
    C.AEVERKNR, C.STARTDAT, Q.KLARTEXT AS INVESTIGATIONTYPETEXT, A.KUNDNUMMER AS PERSONKEY,
    C.AKTPNR, A.NAMN AS PERSONNAME, H.NAMN AS CASEWORKERNAME, H.HAND AS CASEWORKERID,0,-1
  FROM LD0V0C C
    join LD0V0CCASEWORKERSERVICES x on c.aeverknr = x.arendenr
    join @CaseWorkersTable t on x.medhand = t.username
    INNER JOIN LD0V0Q01 Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP 
    INNER JOIN LD0P0I H ON C.HAND = H.HAND
    LEFT OUTER JOIN LD0P0A A ON C.AKTPNR = A.PNR
  WHERE C.STOPDAT = '99999999' and C.TMARK = 0;


  select distinct AEVERKNR, STARTDAT, INVESTIGATIONTYPETEXT, PERSONKEY,
    AKTPNR, PERSONNAME, CASEWORKERNAME, CASEWORKERID, K.AKTUALNR,K.AKTUALTYP 
  from #ogw
  OUTER APPLY (SELECT TOP 1 AKTUALNR,AKTUALTYP FROM LD0V0CAKTUALSERING WHERE AEVERKNR = #ogw.AEVERKNR ORDER BY AKTUALNR DESC) K;

  drop table #ogw;
END
GO
GRANT EXECUTE ON LD0SPListOngoingWork TO LD0ROANV
GO