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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- &OWNER will be replaced with the database installation-specific schema
CREATE PROCEDURE LD0SPGetUnhandledApplicationsByPersonId
	@PersonId varchar(12),
	@ADATUM varchar(8) = NULL
AS
BEGIN
	set nocount on;

	DECLARE @TODAY varchar(8) = convert(varchar,GETDATE(),112);

	IF @ADATUM IS NULL
	SET @ADATUM = '00000000'

	-- Define the temporary table
	create table #tmpAktualserings (
		AKTUALNR DECIMAL(9,0) NOT NULL, ORGID DECIMAL(9,0), AVBORSAK DECIMAL(9,0),
		AKTUAL DECIMAL(9,0), AKTUALTYP DECIMAL(9,0), BTYP DECIMAL(1,0), ADATUM VARCHAR(8) NOT NULL, AVEM DECIMAL(9,0),
		AORSAK DECIMAL(9,0), ORGENHNR VARCHAR(10), ORGENHNR2 VARCHAR(10), PNR VARCHAR(12), HAND VARCHAR(10), HAND2 VARCHAR(10),
		ATEXT1 VARCHAR(250), ATEXT2 VARCHAR(250), ATEXT3 VARCHAR(250), ATEXT4 VARCHAR(250), AVBFROMDAT VARCHAR(8) NULL,
		AVBTOMDAT VARCHAR(8) NULL, REGTID VARCHAR(6), AVSER DECIMAL(9,0), UPDDAT VARCHAR(6), UPDSIGN VARCHAR(10), PROCDATUM VARCHAR(8) NULL,
		NEXTMONTH DECIMAL(1,0) NOT NULL, URGENCY DECIMAL(1,0) NOT NULL, URSPRUNG DECIMAL(1,0), RESPOND_DATE VARCHAR(8), AEVERKNR DECIMAL(9,0),
		INSNR DECIMAL(9,0), INVESTIGATIONSTARTDATE VARCHAR(8), INVESTIGATIONSTOPDATE VARCHAR(8), INVESTIGATIONTYPE DECIMAL(9,0),
		SERVICESTARTDATE VARCHAR(8), SERVICETYPE DECIMAL(9,0), PERSONNAME VARCHAR(180), PERSONKEY DECIMAL(9,0), TYPECODETEXT VARCHAR(180),
		AKTUALSERINGTYPE DECIMAL(9,0), DEFAULTHANDLINGTIME DECIMAL(9,0) NOT NULL, USEWORKINGDAYS DECIMAL(1,0) NOT NULL,
		SHOWINLIFECARE DECIMAL(9,0), ORGANIZATIONNAME VARCHAR(250), COORGANIZATIONNAME VARCHAR(250)
		);

	-- Retrieve the unhandled applications by a personId to a temporary table
	insert into #tmpAktualserings
	select AKTUALNR,ORGID,AVBORSAK,AKTUAL,AKTUALTYP,BTYP,A.ADATUM,AVEM,AORSAK,
		ORGENHNR,ORGENHNR2,PNR,HAND,HAND2,ATEXT1,ATEXT2,ATEXT3,ATEXT4,A.AVBFROMDAT,
		A.AVBTOMDAT,REGTID,AVSER,UPDDAT,UPDSIGN,A.PROCDATUM,A.NEXTMONTH,A.URGENCY,URSPRUNG,
		RESPOND_DATE,AEVERKNR,INSNR,INVESTIGATIONSTARTDATE,INVESTIGATIONSTOPDATE,
		INVESTIGATIONTYPE,SERVICESTARTDATE,SERVICETYPE,PERSONNAME,PERSONKEY,
		TYPECODETEXT,AKTUALSERINGTYPE,A.DEFAULTHANDLINGTIME,A.USEWORKINGDAYS,
		SHOWINLIFECARE,ORGANIZATIONNAME,COORGANIZATIONNAME
	FROM LD0V0CAKTUALSERING A
	WHERE PNR = @PersonId
	    --EfficaDefinitions.ApplicationType = 1
		--EfficaDefinitions.OtherWayType = 3
		--EfficaDefinitions.StatementType = 4
		--EfficaDefinitions.AppealType = 5
		AND AKTUALTYP IN (1,3,4,5)
		AND ADATUM >= @ADATUM
		AND ISNULL(INVESTIGATIONSTOPDATE, '99999999') >= @TODAY;

	-- Create an index for the temporary table
	create clustered index IX_tmpAktualserings ON #tmpAktualserings (ADATUM DESC, AKTUALNR DESC);

	--Remove those applications from the list which have a connected decisions (LD0V0DECISIONCONNECTION) directly with the applicationId in final status (4)
	--Refactored based on WEFI.FC.Business.Action.ApplicationAction.GetApplicationDecisionsConnectedDirectly
	DELETE FROM #tmpAktualserings
	FROM #tmpAktualserings A
	--LD0V0H03 = DecisionConnection
	INNER JOIN LD0P0H03 H3 ON (H3.AKTUALNR = A.AKTUALNR) --AKTUALNR is the identifier of an application
	--LD0V0E = Decision
	INNER JOIN LD0P0E E on (H3.BESLUTNR = E.BESLUTNR AND E.STATUS = 4); --Decision.InvestigationExecutionId = Aktualsering.InvestigationId

	--Remove those applications from the list which have a connected decisions (LD0V0DECISIONCONNECTION) indirectly via investigation in final status (4)
	--Refactored based on WEFI.FC.Business.Action.ApplicationAction.GetApplicationDecisionsConnectedThroughInvestigation
	DELETE FROM #tmpAktualserings
	FROM #tmpAktualserings a
	--LD0V0E = Decision
	INNER JOIN LD0P0E E on (a.AEVERKNR = e.AEVERKNR AND E.STATUS = 4) --Decision.InvestigationExecutionId = Aktualsering.InvestigationId
	WHERE a.AEVERKNR <> 0

	-- Execute a select for the results
	select * from #tmpAktualserings

	-- Delete the temporary table
	drop table #tmpAktualserings;

END
GO

-- Grant the user rights for the application to call the database procedure
GRANT EXECUTE ON LD0SPGetUnhandledApplicationsByPersonId TO LD0ROANV
GO