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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

  DECLARE @AKTUALTYP INT; set @AKTUALTYP = 0;
  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 #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
  );

  insert into #Aktualserings
  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
    and A.PROCDATUM = '';

  insert into #Aktualserings
  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 #Aktualserings)
    and A.PROCDATUM = '';

  select AKTUALNR,DATUM,URGENCY,PROCDATUM,USEWORKINGDAYS,ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT,DEFAULTHANDLINGTIME,1 as [ACTUALIZATIONSOURCE]  
  from #Aktualserings;

  drop table #Aktualserings;

END
GO
GRANT EXECUTE ON LD0SPListAnnouncementsByCaseworker TO LD0ROANV
GO