IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPListApplicationsByCaseworker')
	DROP PROCEDURE LD0SPListApplicationsByCaseworker
GO

set QUOTED_IDENTIFIER on
GO

CREATE PROCEDURE LD0SPListApplicationsByCaseworker
  @CaseWorkersXml varchar(MAX),
  @RestrictedFromDate varchar(8)
AS
BEGIN
  set nocount on;
  set QUOTED_IDENTIFIER on;

  DECLARE @AKTUALTYP INT; set @AKTUALTYP = 1;
  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 #TempTable (
    AKTUALNR DECIMAL(9,0) NOT NULL,
    DATUM VARCHAR(8) NULL,
    URGENCY DECIMAL(1,0) NOT NULL,
    PROCDATUM VARCHAR(8) NULL,
    USEWORKINGDAYS DECIMAL(1,0) NOT NULL,
    ADATUM VARCHAR(8) NOT NULL,
    NEXTMONTH DECIMAL(1,0) NOT NULL,
    AVBFROMDAT VARCHAR(8) NULL,
    AVBTOMDAT VARCHAR(8) NULL,
    DEFAULTHANDLINGTIME DECIMAL(9,0) NOT NULL);

  create table #Aktualserings (
    AKTUALNR DECIMAL(9,0) NOT NULL,
    DATUM VARCHAR(8) NULL,
    URGENCY DECIMAL(1,0) NOT NULL,
    PROCDATUM VARCHAR(8) NULL,
    USEWORKINGDAYS DECIMAL(1,0) NOT NULL,
    ADATUM VARCHAR(8) NOT NULL,
    NEXTMONTH DECIMAL(1,0) NOT NULL,
    AVBFROMDAT VARCHAR(8) NULL,
    AVBTOMDAT VARCHAR(8) NULL,
    DEFAULTHANDLINGTIME DECIMAL(9,0) NOT NULL);

  create table #DecisionConnections (
    AKTUALNR DECIMAL(9,0) NOT NULL,
    DATUM VARCHAR(8) NOT NULL,
    [STATUS] decimal(1,0)
  );

  insert into #TempTable
    select AKTUALNR,DATUM = NULL,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME
    FROM LD0V0CAKTUALSERING A
    join @CaseWorkersTable t on A.HAND = t.UserName
    WHERE A.AKTUALTYP = A.AKTUALSERINGTYPE and A.AKTUALTYP = @AKTUALTYP AND A.ADATUM >= @RestrictedFromDate;

  insert into #TempTable
    select AKTUALNR,DATUM = NULL,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME
    FROM LD0V0CAKTUALSERING A
    join @CaseWorkersTable t on A.HAND2 = t.UserName
    WHERE A.AKTUALTYP = @AKTUALTYP AND A.AKTUALSERINGTYPE = @AKTUALTYP AND A.ADATUM >= @RestrictedFromDate
    and AKTUALNR not in (select AKTUALNR from #TempTable);

  insert into #DecisionConnections
    select t.AKTUALNR, e.DATUM, e.[status]
    from #TempTable t
    join LD0P0H03 h03 on t.AKTUALNR = h03.AKTUALNR
    join LD0P0E e on h03.beslutnr = e.beslutnr
    where exists (
      SELECT 1 FROM LD0VLC C WHERE C.AEVERKNR = E.AEVERKNR AND E.MAKDAT = ' '
      union all
      SELECT 1 FROM LD0VLC20 C20 WHERE C20.INSNR = E.INSNR AND E.MAKDAT = ' '
    );

  insert into #Aktualserings
    select T.AKTUALNR,D.DATUM,T.URGENCY,T.PROCDATUM,T.USEWORKINGDAYS,T.ADATUM,T.NEXTMONTH,T.AVBFROMDAT,T.AVBTOMDAT,T.DEFAULTHANDLINGTIME
    from #TempTable T
    OUTER APPLY (SELECT TOP 1 DATUM FROM #DecisionConnections dct WHERE dct.AKTUALNR = T.AKTUALNR ORDER BY DATUM ASC) D
    where T.AKTUALNR NOT IN (select AKTUALNR from #DecisionConnections where [STATUS] = 4);

  select AKTUALNR,DATUM,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME
  from #Aktualserings;

  drop table #TempTable;
  drop table #DecisionConnections;
  drop table #Aktualserings;

END;
