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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LD0SPGetUnhandledDocumentsForPerson
	@PersonId varchar(12),
	@DATUM varchar(8) = NULL
AS
BEGIN
	set nocount on;

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

	if @DATUM is null set @DATUM = '00000000';

	create table #tmpDoc (
		DOKUMENTNR decimal(9,0), AGARNR decimal(9,0), AGARTYP decimal(9,0), DRUBRIK varchar(200), 
		DOKTYP decimal(9,0), ANTDOKTYP decimal(9,0), DDATUM varchar(8), DELETED decimal(1,0), 
		LOCKED decimal(1,0), LOCKDATE varchar(8), LOCKSIGN varchar(10), HANDELSEDATE varchar(8), 
		UPDDAT varchar(6), CASEWORKERID varchar(10), CASEWORKERNAME varchar(180),
		ORGANIZATIONNAME varchar(250), ORGANIZATIONID varchar(10), AKTPNR varchar(12), 
		PERSONNAME varchar(180), PERSONKEY decimal(9,0), AKTUALTYPECODE decimal(9,0), 
		ISINVESTIGATIONORIMPLEMENTATION int, LIFECARE_CREATED decimal(1,0),
		ServiceCategoryTypeText varchar(180),
		DocumentTypeText varchar(180), CanChangeOccurenceDate BIT,
		InvestigationCodeType varchar(180), InvestigationCode decimal(9,0),
		XNUM4 decimal(9,0),
		CONSTRAINT [PK_tmpDoc] PRIMARY KEY CLUSTERED ([DOKUMENTNR] ASC)
	);
	
	-- LD0V0CDOCUMENTS_AKTUALISERING (DocumentAktualiseringAction)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],T41.PERSONNR AS [AKTPNR],PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B.TYPNR AS [AKTUALTYPECODE],
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		0 AS [InvestigationCode], NULL as [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_AKTUALISERING]
	JOIN LD0V0H AS H ON H.AKTUALNR = T41.AGARNR AND T41.AGARTYP = 20 AND T41.DOKTYP > 0 
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='TYPDOK' and T41.DOKTYP = doctyp.KOD
	LEFT JOIN LD0V0C AS C ON C.AEVERKNR = H.AEVERKNR 
	LEFT JOIN LD0V0B AS B ON B.AKTNR = C.AKTNR 
	LEFT JOIN LD0V0I AS CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND 
	LEFT JOIN LD0V0A AS PERSON ON T41.PERSONNR = PERSON.PNR 
	LEFT JOIN LD0V0J01 AS ORG ON H.ORGENHNR = ORG.ORGENHNR 
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP='AKTTYP' and B.TYPNR = acttyp.KOD
	WHERE T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate

	-- LD0V0CDOCUMENTS_INVG_IMPL (DocumentInvestigationImplementationAction)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],INV.AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		INV.AEVERKTYP AS [AKTUALTYPECODE], 
		INV.TMARK AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		invtyp.KLARTEXT AS [InvestigationCodeType], invtyp.XNUM1 as [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0C INV ON INV.AEVERKNR = T41.AGARNR AND T41.AGARTYP IN (21,22) AND T41.DOKTYP > 0 
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='TYPDOK' and T41.DOKTYP = doctyp.KOD
	JOIN LD0P0Q01 invtyp on invtyp.KODTYP = 'UTREDTYP' and INV.AEVERKTYP = invtyp.KOD
	LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND 
	LEFT JOIN LD0V0A PERSON ON INV.AKTPNR = PERSON.PNR 
	LEFT JOIN LD0V0J01 ORG ON INV.ORGENHNR = ORG.ORGENHNR 
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP = 'AKTTYP' and acttyp.KOD = invtyp.XNUM1
	WHERE T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate

	-- LD0V0CDOCUMENTS_SERV_FROM_ACT (DocumentServiceFromActAction)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],C20.AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B1.TYPNR AS [AKTUALTYPECODE], 
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		'' as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		'' AS [InvestigationCodeType], 0 AS [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0C20 C20 ON C20.INSNR = T41.AGARNR AND T41.AGARTYP = 23 AND T41.DOKTYP > 0 AND C20.VERKNR <> 0 
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='TYPDOK' and T41.DOKTYP = doctyp.KOD
	LEFT JOIN LD0V0C INSATS ON INSATS.AEVERKNR = C20.VERKNR 
	LEFT JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR 
	LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND 
	LEFT JOIN LD0V0A PERSON ON C20.AKTPNR = PERSON.PNR 
	LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR 
	WHERE T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate

	-- LD0V0CDOCUMENTS_SERV_FROM_INVG (DocumentServiceFromInvestigationAction)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],C20.AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B1.TYPNR AS [AKTUALTYPECODE],
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		'' AS [InvestigationCodeType], 0 AS [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0C20 C20 ON C20.INSNR = T41.AGARNR AND T41.AGARTYP = 23 AND T41.DOKTYP > 0 AND C20.ARENDENR <> 0 
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='TYPDOK' and T41.DOKTYP = doctyp.KOD
	LEFT JOIN LD0V0C INSATS ON INSATS.AEVERKNR = C20.ARENDENR 
	LEFT JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR 
	LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND 
	LEFT JOIN LD0V0A PERSON ON C20.AKTPNR = PERSON.PNR 
	LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR 
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP = 'AKTTYP' and acttyp.KOD = B1.TYPNR
	WHERE T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate

	-- LD0V0CDOCUMENTS_ACT (DocumentActAction)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],T41.PERSONNR AS AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B.TYPNR AS [AKTUALTYPECODE], 
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		'' AS [InvestigationCodeType], 0 AS [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0B01 ACT ON ACT.HAGNR = T41.DOKUMENTNR AND ACT.HANDELSE = 859 AND T41.AGARTYP = 39 AND T41.DOKTYP > 0 AND T41.AGARNR = ACT.AKTNR  --last one needed?
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='TYPDOK' and T41.DOKTYP = doctyp.KOD
	LEFT JOIN LD0V0B B ON B.AKTNR = ACT.AKTNR 
	LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND 
	LEFT JOIN LD0V0A PERSON ON T41.PERSONNR = PERSON.PNR 
	LEFT JOIN LD0V0J01 ORG ON B.ORGENHNR = ORG.ORGENHNR 
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP = 'AKTTYP' and acttyp.KOD = B.TYPNR
	WHERE T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate

	-- LD0V0CDOCUMENTS_NOTE_41_42 (DocumentNote41And42Action)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],INV.AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B.TYPNR AS [AKTUALTYPECODE],
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		'' AS [InvestigationCodeType], 0 AS [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0C11 C11 ON C11.HAGNR = T41.DOKUMENTNR AND C11.HANDELSE = 320 AND T41.AGARTYP IN (41, 42)
	JOIN LD0V0C INV ON INV.AEVERKNR = C11.AEVERKNR
	JOIN LD0V0B B ON INV.AKTNR = B.AKTNR		
	JOIN LD0V0A PERSON ON INV.AKTPNR = PERSON.PNR
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='ANTTYP' and T41.ANTDOKTYP = doctyp.KOD
	LEFT  JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
	LEFT  JOIN LD0V0J01 ORG ON INV.ORGENHNR = ORG.ORGENHNR
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP = 'AKTTYP' and acttyp.KOD = B.TYPNR
	WHERE T41.ANTDOKTYP>0 AND T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate
	AND EXISTS (SELECT 1 FROM LD0V0AJD AJD WHERE T41.ANTDOKTYP>=AJD.JFRDTA1 AND T41.ANTDOKTYP<=AJD.JFRDTA2 AND AJD.VYNAMN='ANTTYP') 

	-- LD0V0CDOCUMENTS_NOTE_45 (DocumentNote45Action)
	insert into #tmpDoc
	SELECT T41.[DOKUMENTNR],T41.[AGARNR],T41.[AGARTYP],T41.[DRUBRIK],T41.[DOKTYP],T41.[ANTDOKTYP],T41.[DDATUM],T41.[DELETED],T41.[LOCKED],
		T41.[LOCKDATE],T41.[LOCKSIGN],T41.[HANDELSEDATE],T41.[UPDDAT],UPPER(T41.UPDSIGN) AS [CASEWORKERID],CASEWORKER.NAMN AS [CASEWORKERNAME],
		ORG.NAMN AS [ORGANIZATIONNAME],ORG.ORGENHNR AS [ORGANIZATIONID],B.AKTPNR,PERSON.NAMN AS [PERSONNAME],
		PERSON.KUNDNUMMER AS [PERSONKEY],
		B.TYPNR AS [AKTUALTYPECODE],
		0 AS [ISINVESTIGATIONORIMPLEMENTATION],T41.[LIFECARE_CREATED],
		ISNULL(acttyp.KLARTEXT,'') as [ServiceCategoryTypeText],
		doctyp.KLARTEXT as [DocumentTypeText], doctyp.XNUM28 as [CanChangeOccurenceDate],
		'' AS [InvestigationCodeType], 0 AS [InvestigationCode],
		doctyp.XNUM4
	FROM LD0P0T41 T41 --[OnPremFI_LD0].[LD0V0CDOCUMENTS_INVG_IMPL]
	JOIN LD0V0B01 NOTE ON NOTE.HAGNR = T41.DOKUMENTNR AND NOTE.HANDELSE = 320
	JOIN LD0V0B B ON B.AKTNR = NOTE.AKTNR
	JOIN LD0V0A PERSON ON B.AKTPNR = PERSON.PNR
	JOIN LD0P0Q01 doctyp on doctyp.KODTYP='ANTTYP' and T41.ANTDOKTYP = doctyp.KOD
	LEFT  JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
	LEFT  JOIN LD0V0J01 ORG ON B.ORGENHNR = ORG.ORGENHNR
	LEFT JOIN LD0P0Q01 acttyp on acttyp.KODTYP = 'AKTTYP' and acttyp.KOD = B.TYPNR
	WHERE T41.ANTDOKTYP>0 AND T41.PERSONNR = @PersonId AND T41.DELETED = 0 AND T41.LOCKED = 0 
	AND T41.DDATUM >= @DATUM -- DDATUM is CreatedDate
	AND EXISTS (SELECT 1 FROM LD0V0AJD AJD WHERE T41.ANTDOKTYP>=AJD.JFRDTA1 AND T41.ANTDOKTYP<=AJD.JFRDTA2 AND AJD.VYNAMN='ANTTYP') 

	select [DOKUMENTNR],[AGARNR],[AGARTYP],[DRUBRIK],[DOKTYP],[ANTDOKTYP],[DDATUM],[DELETED],[LOCKED],
		[LOCKDATE],[LOCKSIGN],[HANDELSEDATE],[UPDDAT],[CASEWORKERID],[CASEWORKERNAME],
		[ORGANIZATIONNAME],[ORGANIZATIONID],[AKTPNR],[PERSONNAME],
		[PERSONKEY],[AKTUALTYPECODE],
		[ISINVESTIGATIONORIMPLEMENTATION],[LIFECARE_CREATED],
		[ServiceCategoryTypeText],
		[DocumentTypeText], [CanChangeOccurenceDate],
		case 
			when AGARTYP = 20 then 1
			when AGARTYP in (21,51) then 2
			when AGARTYP in (23,41,42,45,53) then 3
			when AGARTYP in (22,52) then 4
			when AGARTYP = 39 then 5
			when AGARTYP = 40 then 6
			when AGARTYP = 50 then 7
			when AGARTYP = 25 then 9
			else 0
		end as [DOCUMENTOWNER],
		case 
			when AGARTYP in (20,21,22,23,39,40,25) then
				case when XNUM4 = 1 then 5
				when LIFECARE_CREATED = 1 then 0
				when LIFECARE_CREATED = 0 then 2
			end
			when AGARTYP in (41,42,45) then 4
			when AGARTYP in (50,51,52,53) then 3
			else 0
		end as [DOCUMENTTYPE]
			
	from #tmpDoc;
	
	drop table #tmpDoc;

END

GO

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