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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE LD0SPServiceConnectedEvents(@SERVICEID INT)

AS

DECLARE @CONNECTEDEVENTS TABLE(CONNECTEDEVENTS VARCHAR(50))
DECLARE

@OWNERTYPEID INT,
@PROCESSMETADATAID INT,
@SERVICEFORMS INT,
@SERVICEDOCS INT,
@EHRTEMPLATE INT,
@WELFAREDOCUMENTS INT,
@JOURNALNOTE INT,
@SERVICEBASEDDECISIONS INT,
@REQUISITION INT,
@REMITTANCE INT,
@PAYMENT INT,
@EXECUTEDCLIENTWORK INT,

@EHRDOCUMENTTYPE_JOURNALNOTE INT,
@FORMTYPE_SERVICE INT,
@DOCUMENTTYPE_SERVICE INT,
@PDFDOCUMENTTYPE_SERVICE INT,
@PROCESSOWNERTYPE_SERVICE INT;

SET @OWNERTYPEID = 3; -- (Service Owner)
SET @EHRDOCUMENTTYPE_JOURNALNOTE = 2;
SET @FORMTYPE_SERVICE = 23;
SET @DOCUMENTTYPE_SERVICE = 41;
SET @PDFDOCUMENTTYPE_SERVICE = 7083;
SET @PROCESSOWNERTYPE_SERVICE = 3;

--FETCH PROCESSMETADATAID FOR SERVICE OWNED PROCESS

SELECT @PROCESSMETADATAID = ID FROM LD0V1PROCESSMETADATA M
WHERE M.OWNERTYPE = @OWNERTYPEID AND M.OWNERID = @SERVICEID


--SERVICE FORM

SELECT @SERVICEFORMS = COUNT(DOKUMENTNR) FROM LD0V0C20 SERV
LEFT JOIN LD0V0T41 LVT41 ON SERV.INSNR = LVT41.AGARNR
WHERE LVT41.AGARNR = @SERVICEID AND LVT41.AGARTYP = @FORMTYPE_SERVICE AND LVT41.DELETED = 0


--SERVICE DOCS

SELECT @SERVICEDOCS = COUNT(DOKUMENTNR) FROM LD0V0C20 SERV
LEFT JOIN LD0V0T41 LVT41 ON SERV.INSNR = LVT41.AGARNR
WHERE LVT41.AGARNR = @SERVICEID AND LVT41.AGARTYP = @DOCUMENTTYPE_SERVICE AND LVT41.DELETED = 0

IF(@SERVICEDOCS = 0)

SELECT @SERVICEDOCS = COUNT(INFOGANR) FROM LD0V0C20 SERV
LEFT JOIN LD0V0T43 LVT43 ON SERV.INSNR = LVT43.AGARNR
WHERE LVT43.AGARNR = @SERVICEID AND LVT43.AGARTYP = @PDFDOCUMENTTYPE_SERVICE AND LVT43.DELETED = 0

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

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


--JOURNAL NOTES

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

WHERE EWD.EHRDOCUMENTTYPE = @EHRDOCUMENTTYPE_JOURNALNOTE AND EWD.METADATAID = @PROCESSMETADATAID


----SERVICE BASED DECISIONS
SELECT @SERVICEBASEDDECISIONS = COUNT(BESLUTNR) FROM LD0V0E DECISION  

WHERE DECISION.INSNR = @SERVICEID 


--REQUISITION
SELECT @REQUISITION = COUNT(REKVNR) FROM LD0P0G  
WHERE LD0P0G.INSNR = @SERVICEID


--REMITTANCE / INCOME
SELECT @REMITTANCE = COUNT(BETALNR) FROM LD0V0F 
WHERE LD0V0F.INSNR = @SERVICEID AND LD0V0F.BETSATT = 90 AND STATUS != 9 AND MAKDAT = ' ' 


--PAYMENT / OUTCOME
SELECT @PAYMENT = COUNT(BETALNR) FROM LD0V0F 
WHERE LD0V0F.INSNR = @SERVICEID AND LD0V0F.BETSATT != 90 AND STATUS != 9 AND MAKDAT = ' ' 


--EXECUTED CLIENTWORK
SELECT @EXECUTEDCLIENTWORK = COUNT(KLIARBLNR) FROM LD0P0C30  
WHERE LD0P0C30.INSNR = @SERVICEID

IF(@SERVICEFORMS > 0)

	INSERT @CONNECTEDEVENTS VALUES('SERVICEFORM')
	   
IF(@SERVICEDOCS > 0)

	INSERT @CONNECTEDEVENTS VALUES('SERVICEDOCUMENT')

IF(@EHRTEMPLATE > 0)

	INSERT @CONNECTEDEVENTS VALUES('EHRFORM')

IF(@WELFAREDOCUMENTS > 0) 

	INSERT @CONNECTEDEVENTS VALUES('WELFAREDOCUMENT')

IF(@JOURNALNOTE > 0)

	INSERT @CONNECTEDEVENTS VALUES('EHRJOURNALNOTE')


IF(@SERVICEBASEDDECISIONS > 1)

	INSERT @CONNECTEDEVENTS VALUES('SERVICEBASEDDECISION')

IF(@REQUISITION > 0)

	INSERT @CONNECTEDEVENTS VALUES('REQUISITION')

IF(@REMITTANCE > 0)

	INSERT @CONNECTEDEVENTS VALUES('REMITTANCE')

IF(@PAYMENT > 0)

	INSERT @CONNECTEDEVENTS VALUES('PAYMENT')

IF(@EXECUTEDCLIENTWORK > 0)

	INSERT @CONNECTEDEVENTS VALUES('EXECUTEDCLIENTWORK')


SELECT * FROM @CONNECTEDEVENTS

GO

GRANT EXECUTE ON LD0SPServiceConnectedEvents TO LD0ROANV 

GO
