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

CREATE PROCEDURE LD0SPActualizationConnectedEvents(@ActualizationId int, @ProcessMetadataId int)

AS

Declare @ConnectedEvents TABLE(ConnectedEvents VARCHAR(50))
Declare @JournalNote INT,

@EHRTemplate INT,
@WelfareDocuments INT,
@AktualseringDocs INT,
@Investigation INT,
@EHRDocumentType_JournalNote INT,
@DocumentType_Aktualsering INT,
@PDFDocumentType_Aktualsering INT,
@ProcessOwnerType_Investigation INT;

SET @EHRDocumentType_JournalNote = 2;
SET @DocumentType_Aktualsering = 20;
SET @PDFDocumentType_Aktualsering = 7040;
SET @ProcessOwnerType_Investigation = 2;

SELECT * INTO #EHRWelfareDocuments FROM LD0V0EHRWELFAREDOCUMENTS EWD
WHERE EWD.IsActive = 1;  

--Journal Notes

SELECT @JournalNote = COUNT(DSCID) FROM #EHRWelfareDocuments EWD 

WHERE EWD.EHRDOCUMENTTYPE = @EHRDocumentType_JournalNote AND EWD.METADATAID = @ProcessMetadataId


--EHR Template

SELECT @EHRTemplate = COUNT(DSCID) FROM #EHRWelfareDocuments EWD 

LEFT JOIN LD0V1EHRWELFAREDOCUMENTTYPES EWDT ON EWD.COMPONENTDOCUMENTTYPEMAPPINGID = EWDT.COMPONENTDOCUMENTTYPEMAPPINGID

WHERE EWDT.PROCESSCOMPONENTNAME = 'SocialCareDesktop.EHRTemplate' AND EWD.METADATAID = @ProcessMetadataId 


--Welfare Documents

SELECT @WelfareDocuments = COUNT(DSCID) FROM #EHRWelfareDocuments EWD 

LEFT JOIN LD0V1EHRWELFAREDOCUMENTTYPES EWDT ON EWD.COMPONENTDOCUMENTTYPEMAPPINGID = EWDT.COMPONENTDOCUMENTTYPEMAPPINGID

WHERE EWDT.PROCESSCOMPONENTNAME = 'SocialCareDesktop.WelfareDocuments' AND EWD.METADATAID = @ProcessMetadataId 


--Aktualsering Docs

SELECT @AktualseringDocs = COUNT(DOKUMENTNR) FROM LD0V0H ACT
LEFT JOIN LD0V0T41 LVT41 ON ACT.AKTUALNR = LVT41.AGARNR
WHERE LVT41.AGARNR = @ActualizationId AND LVT41.AGARTYP = @DocumentType_Aktualsering 

IF(@AktualseringDocs = 0)

SELECT @AktualseringDocs = COUNT(INFOGANR) FROM LD0V0H ACT
LEFT JOIN LD0V0T43 LVT43 ON ACT.AKTUALNR = LVT43.AGARNR
WHERE LVT43.AGARNR = @ActualizationId AND LVT43.AGARTYP = @PDFDocumentType_Aktualsering


--Investigation

SELECT @Investigation = COUNT(ID) FROM LD0V1PROCESSMETADATA
WHERE PREVIOUSMETADATAID = @ProcessMetadataId AND OWNERTYPE = @ProcessOwnerType_Investigation

IF(@Investigation = 0)

SELECT @Investigation = COUNT(AKTUALNR) FROM LD0V0H WHERE AKTUALNR = @ActualizationId AND AEVERKNR != 0



IF(@JournalNote > 0)

	INSERT @ConnectedEvents values('SocialCareDesktop.ProcessJournalSideTab')

IF(@EHRTemplate > 0)

	INSERT @ConnectedEvents values('SocialCareDesktop.EHRTemplate')

IF(@WelfareDocuments > 0) 

	INSERT @ConnectedEvents values('SocialCareDesktop.WelfareDocuments')

IF(@AktualseringDocs > 0)

	INSERT @ConnectedEvents values('SocialCareDesktop.AktualseringDocuments')

IF(@Investigation > 0)

	INSERT @ConnectedEvents values('SocialCareDesktop.Investigation')

SELECT * FROM @ConnectedEvents

GO
GRANT ALL ON LD0SPActualizationConnectedEvents TO LD0ROANV
GO