IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPListApplicationsByOrganization')
	DROP PROCEDURE LD0SPListApplicationsByOrganization
GO
set nocount ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LD0SPListApplicationsByOrganization
  @CaseWorkersXml varchar(MAX),
  @RestrictedFromDate varchar(8),
  @TeamId int
AS
BEGIN
  set nocount on;
  SET QUOTED_IDENTIFIER on;

  DECLARE @AKTUALTYP INT; set @AKTUALTYP = 1;
  DECLARE @XMLlist XML; SELECT @XMLList = @CaseWorkersXml;
  DECLARE @OrganizationsTable TABLE ([ORGENHNR][varchar](10) NOT NULL);
  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,
	AEVERKNR 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
  );

  -- Fill @OrganizationsTable with distinct organization unit ids for given caseworkers.
  IF @TeamId = 0
  BEGIN
    insert into @OrganizationsTable
    select distinct M.ORGENHNR
    FROM LD0VAACASEWORKERORGMEMBER M
    join @CaseWorkersTable t on M.HAND = t.UserName
    WHERE M.FOMDAT <= CONVERT(VARCHAR,GETDATE(),112) 
      AND M.TOMDAT >= CONVERT(VARCHAR,GETDATE(),112);
  END;

  -- Get all aktualiserings for OrganizationUnitId (A.ORGENHNR).
  IF @TeamId = 0
  BEGIN
    insert into #TempTable
    select AKTUALNR,DATUM = NULL,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME,AEVERKNR
    FROM LD0V0CAKTUALSERING A
    JOIN @OrganizationsTable O ON A.ORGENHNR = O.ORGENHNR
    WHERE (A.AKTUALSERINGTYPE = A.AKTUALTYP and A.AKTUALTYP = @AKTUALTYP)
      and (A.ADATUM >= @RestrictedFromDate)
      and (A.HAND = '' and A.HAND2 = '');
  END;

  -- Get all aktualiserings for CoOrganizationUnitId (A.ORGENHNR2).
  IF @TeamId = 0
  BEGIN
    insert into #TempTable
    select AKTUALNR,DATUM = NULL,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME,AEVERKNR	
    FROM LD0V0CAKTUALSERING A
    JOIN @OrganizationsTable O ON A.ORGENHNR2 = O.ORGENHNR
    WHERE (A.AKTUALSERINGTYPE = A.AKTUALTYP and A.AKTUALTYP = @AKTUALTYP)
      and (A.ADATUM >= @RestrictedFromDate)
      and (A.HAND = '' and A.HAND2 = '')
      and (A.AKTUALNR not in (select AKTUALNR from #TempTable));
  END;

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

  insert into #DecisionConnectionsTable
    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;

  insert into #DecisionConnectionsTable
	select t.AKTUALNR, e.DATUM, e.[status]
	from #TempTable t
	join LD0P0E e on t.AEVERKNR = e.AEVERKNR and t.AEVERKNR <> 0;	

  -- Select applications which do not have a finalized decision
  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 #DecisionConnectionsTable dct WHERE dct.AKTUALNR = T.AKTUALNR ORDER BY DATUM ASC) D
    where T.AKTUALNR NOT IN (select AKTUALNR from #DecisionConnectionsTable where [STATUS] = 4);

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

  drop table #DecisionConnectionsTable;
  drop table #TempTable;
  drop table #Aktualserings;

END
GO
GRANT EXECUTE ON LD0SPListApplicationsByOrganization TO LD0ROANV
GO
;