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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LD0SPListUnfinishedDecisions

  @CaseWorkersXml varchar(MAX),

  @RestrictedFromDate varchar(8)

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);

  DECLARE @STOPDAT varchar(8); SET @STOPDAT = CONVERT(varchar,GETDATE(),112);

  CREATE TABLE #unfinishedDecisions (
    DECISIONDATE varchar(8), DECISIONSTATUS decimal(1, 0), BESLTYP decimal(9, 0),
    BESLUTNR decimal(9, 0), BESFATT varchar(10), HAND varchar(10), CASEWORKERNAME varchar(180), MEDHAND varchar(10), COCASEWORKERNAME varchar(180),
    SERVICEID decimal(9, 0), INSTYP INT, INVESTIGATIONID decimal(9, 0), STOPDAT varchar(8), BESLUT decimal(9, 0),
	DECISIONLEADSTOSERVICE decimal(9, 0), DECISIONNAME varchar(180), PERSONID varchar(12),PERSONNAME varchar(180),
	PERSONKEY decimal(9, 0),UPDSIGN varchar(10), UPDDAT varchar(6), REFDECISIONID decimal(9, 0), AKTUALNR int, AKTUALTYP int, VERKNR int
  );

  INSERT INTO #unfinishedDecisions

  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER,
  U.INSTYP, U.INVESTIGATIONNUMBER,  U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, 
  U.PERSONNAME, U.PERSONKEY, U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0,-1, U.EXECUTIONID  
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_IN_EXE_ALERTS U

  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2 

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)

    AND (DECISIONDATE >= @RestrictedFromDate);
	
  INSERT INTO #unfinishedDecisions
  
  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER, 
  U.INSTYP, U.INVESTIGATIONNUMBER, U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, 
  U.PERSONNAME, U.PERSONKEY, U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0,-1, U.EXECUTIONID 
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_IN_EXE_ALERTS U

  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)

    AND (DECISIONDATE >= @RestrictedFromDate)

    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));
	
  INSERT INTO #unfinishedDecisions
  
  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER,
  U.INSTYP, U.INVESTIGATIONNUMBER, U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, 
  U.PERSONNAME, U.PERSONKEY, U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0, -1, 0  
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_NOTIN_EXE_ALERTS U

  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2 

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)

    AND (DECISIONDATE >= @RestrictedFromDate);
	
  INSERT INTO #unfinishedDecisions
  
  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER,
  U.INSTYP, U.INVESTIGATIONNUMBER, U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, 
  U.PERSONNAME, U.PERSONKEY, U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0,-1, 0  
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_NOTIN_EXE_ALERTS U

  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)

    AND (DECISIONDATE >= @RestrictedFromDate)

    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));
	
  INSERT INTO #unfinishedDecisions
  
  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER,
  0, U.INVESTIGATIONNUMBER, U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, 
  U.PERSONNAME, U.PERSONKEY, U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0, -1, 0  
  FROM LD0V0CUNFINISHEDDECISIONS_WITHOUT_SERVICE_ALERTS U

  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2 

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR (STOPDAT > @STOPDAT))

    AND (DECISIONDATE >= @RestrictedFromDate);

  INSERT INTO #unfinishedDecisions
  
  SELECT U.DECISIONDATE, U.DECISIONSTATUS, U.DECISIONTYPEID, U.DECISIONID, U.DECISIONMAKERID, U.CASEWORKERID, U.CASEWORKERNAME, U.COCASEWORKERID, U.COCASEWORKERNAME, U.SERVICENUMBER, U.INVESTIGATIONNUMBER,
  0, U.STOPDAT, U.DECISION, U.DECISIONLEADSTOSERVICE, U.DECISIONNAME, U.PERSONID, U.PERSONNAME, U.PERSONKEY, 
  U.UPDSIGN, U.UPDDAT, U.REFDECISIONID, 0, -1, 0  
  FROM LD0V0CUNFINISHEDDECISIONS_WITHOUT_SERVICE_ALERTS U

  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName

  WHERE (DECISIONSTATUS = 2

    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)

    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))

    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR (STOPDAT > @STOPDAT))

    AND (DECISIONDATE >= @RestrictedFromDate)

    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));


  select distinct DECISIONDATE, DECISIONSTATUS, BESLTYP, BESLUTNR, BESFATT, HAND, CASEWORKERNAME, MEDHAND, COCASEWORKERNAME, SERVICEID, K1.INSTYP, INVESTIGATIONID, STOPDAT, BESLUT,
  DECISIONLEADSTOSERVICE, DECISIONNAME, PERSONID, PERSONNAME,	PERSONKEY, UPDSIGN, UPDDAT, REFDECISIONID, K.AKTUALNR, K.AKTUALTYP, VERKNR 
  from #unfinishedDecisions
  OUTER APPLY (SELECT TOP 1 AKTUALNR,AKTUALTYP FROM LD0V0CAKTUALSERING WHERE AEVERKNR = #unfinishedDecisions.INVESTIGATIONID ORDER BY AKTUALNR DESC) K
  OUTER APPLY (SELECT TOP 1 INSNR,INSTYP FROM LD0V0CCASEWORKERSERVICES WHERE INSNR = #unfinishedDecisions.SERVICEID ORDER BY INSNR DESC) K1;

  drop table #unfinishedDecisions;

END
GO

GRANT ALL ON LD0SPListUnfinishedDecisions to LD0ROANV
GO
