set nocount on
GO
set QUOTED_IDENTIFIER on

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

  @CaseWorkersXml varchar(MAX),

  @RestrictedFromDate varchar(8)

AS

BEGIN

  set nocount on;

  set QUOTED_IDENTIFIER on;

  DECLARE @SourceCaseworker INT; SET @SourceCaseworker = 1;

  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 PRIMARY KEY,

    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,

    AEVERKNR decimal(9,0) not null,

    INSNR decimal(9,0) not null,

    AKTUAL decimal(9,0) not null,

    ORGID decimal(9,0) not null,

    ORGENHNR varchar(10) not null);



  create table #Aktualserings (

    AKTUALNR DECIMAL(9,0) NOT NULL PRIMARY KEY,

    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,

    AEVERKNR decimal(9,0) not null,

    INSNR decimal(9,0) not null,

    AKTUAL decimal(9,0) not null,

    ORGID decimal(9,0) not null,

    ORGENHNR varchar(10) not null);



  create table #DecisionConnections (

    AKTUALNR DECIMAL(9,0) NOT NULL,

    DATUM VARCHAR(8) NOT NULL,

    STATUS decimal(1,0)	

  );



  insert into #TempTable

    select H.AKTUALNR,DATUM = NULL,H.URGENCY,H.PROCDATUM,

    IIF(H.AKTUALTYP = 1,ISNULL(Q.XNUM10,0),ISNULL(Q.XNUM8,0)) as USEWORKINGDAYS,

    H.ADATUM,H.NEXTMONTH,H.AVBFROMDAT,H.AVBTOMDAT,Q.XNUM6 as DEFAULTHANDLINGTIME,

    H.AEVERKNR, H.INSNR, H.AKTUAL, H.ORGID, H.ORGENHNR

    from LD0P0H H

    JOIN LD0P0A P ON H.PNR = P.PNR

    join LD0P0Q01 Q ON Q.KODTYP = 'AKTUAL' AND Q.KOD = H.AKTUAL  --LD0V0Q01

    join @CaseWorkersTable t on H.HAND = t.UserName

    WHERE H.BESLUTNR = 0 and H.AKTUALTYP = Q.XNUM1 /*AKTUALSERINGTYPE*/ and H.AKTUALTYP = @AKTUALTYP AND H.ADATUM >= @RestrictedFromDate;



  insert into #TempTable

    select H.AKTUALNR,DATUM = NULL,H.URGENCY,H.PROCDATUM,

    IIF(H.AKTUALTYP = 1,ISNULL(Q.XNUM10,0),ISNULL(Q.XNUM8,0)) as USEWORKINGDAYS,

    H.ADATUM,H.NEXTMONTH,H.AVBFROMDAT,H.AVBTOMDAT,Q.XNUM6 as DEFAULTHANDLINGTIME,

    H.AEVERKNR, H.INSNR, H.AKTUAL, H.ORGID, H.ORGENHNR

    from LD0P0H H

    join LD0P0A P ON H.PNR = P.PNR

    join LD0P0Q01 Q ON Q.KODTYP = 'AKTUAL' AND Q.KOD = H.AKTUAL  --LD0V0Q01

    join @CaseWorkersTable t on H.HAND2 = t.UserName

    WHERE H.BESLUTNR = 0 and H.AKTUALTYP = @AKTUALTYP AND Q.XNUM1 = @AKTUALTYP AND H.ADATUM >= @RestrictedFromDate

      and H.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 #DecisionConnections
	select t.AKTUALNR, e.DATUM, e.status
	from #TempTable t
	join LD0P0E e on t.AEVERKNR = e.AEVERKNR and t.AEVERKNR <> 0 
	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,

      T.AEVERKNR, T.INSNR, T.AKTUAL, T.ORGID, T.ORGENHNR

    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, @SourceCaseworker AS ACTUALIZATIONSOURCE

  from #Aktualserings H

  where EXISTS 

    (SELECT 1 FROM LD0VLC C WHERE C.AEVERKNR = H.AEVERKNR 

    union all

    SELECT 1 FROM LD0VLC20 C20 WHERE C20.INSNR = H.INSNR

    union all

    SELECT 1 FROM LD0V0AJD AJD WHERE AJD.VYNAMN = 'AKTUAL' AND H.AKTUAL >= AJD.JFRDTA1 AND H.AKTUAL <= AJD.JFRDTA2 

    AND H.ORGID = AJD.JFRDTA3 AND H.ORGENHNR LIKE AJD.JFRDTA10 AND H.AEVERKNR = 0 AND H.INSNR = 0

  );



  drop table #TempTable;

  drop table #DecisionConnections;

  drop table #Aktualserings;

END

GO

GRANT EXECUTE ON LD0SPListApplicationsByCaseworker TO LD0ROANV 

GO