CREATE OR ALTER PROCEDURE LD0SPEnableDataSelectionUppdragAM
(
@Activation INT
,@DSType VARCHAR
,@PersonConf BIT
)
AS 
BEGIN

DECLARE @Schema VARCHAR(100) = (SELECT SCH.name FROM SYS.TABLES TAB JOIN SYS.schemas SCH ON TAB.schema_id=SCH.schema_id WHERE TAB.NAME = 'LD0P0A')
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @sqlQuery NVARCHAR(MAX) 


IF @DSType NOT IN ('G', 'U')
    BEGIN
        -- Raise an error with a custom message and severity
        RAISERROR ('Invalid Data selection type for Enrollment. Must be either ''G'' for Group or ''U'' for User.', 16, 1);
        RETURN;  -- Optionally stop execution after the error is raised
    END

IF @Activation NOT IN (0, 1)
    BEGIN
        -- Raise an error with a custom message and severity
        RAISERROR ('Invalid activation number. Must be either 1 for activate or 0 for deactivate data selection for Enrollment.', 16, 1);
        RETURN;  -- Optionally stop execution after the error is raised
    END

DELETE FROM LD0P0PRO WHERE FUNKTION = 'AMUPPDRAG'

IF @DSType = 'G'
	BEGIN
		INSERT INTO LD0P0PRO
			SELECT 'LD0','AMUPPDRAG',2,CONVERT(VARCHAR(8), GETDATE(), 112),CONVERT(VARCHAR(8), GETDATE(), 108)
				WHERE NOT EXISTS (SELECT 'X' FROM LD0P0PRO WHERE FUNKTION='AMUPPDRAG')
	END
IF @DSType = 'U'
	BEGIN
		INSERT INTO LD0P0PRO
			SELECT 'LD0','AMUPPDRAG',1,CONVERT(VARCHAR(8), GETDATE(), 112),CONVERT(VARCHAR(8), GETDATE(), 108)
				WHERE NOT EXISTS (SELECT 'X' FROM LD0P0PRO WHERE FUNKTION='AMUPPDRAG')
	END


IF @Activation = 0 
BEGIN
	SET @SQL =  'EXEC ' + @Schema + '.LD0SPDisableDataSelectionUppdragAM @PersonConf'
	EXEC sp_executesql @SQL, N'@PersonConf BIT', @PersonConf;
END

IF @Activation = 1
	--Activate data selection
	BEGIN
		IF @DSType = 'G'
		BEGIN
			--Create LD0V5AJD using GROUP config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5AJD
							AS SELECT	GJD.GRUPPLNR,
								GJD.VYNAMN,
								GJD.GJDLNR,
								GJD.JFRDTA1,        
								GJD.JFRDTA2,
								GJD.JFRDTA3,
								GJD.JFRDTA4,
								GJD.JFRDTA10,       
								GJD.JFRDTA11,
								GJD.JFRDTA12,
								GJD.JFRDTA13,
								GJD.UPDDAT, 
								GJD.UPDSIGN
							FROM 	LD0P0GJD GJD, 
								LD0P0DUT DUT, 
								LD0P0SESS SU
							WHERE 	SU.SESSID = @@SPID AND
								GJD.GRUPPLNR = DUT.GRUPPLNR AND
								DUT.GRUPPANVID = SU.ANVID'
			EXEC sp_executesql @SQL

		END

		IF @DSType = 'U'
		BEGIN
			--Create LD0V5AJD using USER config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5AJD
							AS SELECT 
							CSSANV,
							VYNAMN,
							BJDLNR,
							BJD.ANVID, 
							JFRDTA1,        
							JFRDTA2,
							JFRDTA3,
							JFRDTA4,
							JFRDTA10,       
							JFRDTA11,
							JFRDTA12,
							JFRDTA13,
							UPDDAT, 
							UPDSIGN
							FROM LD0P0BJD BJD, LD0P0SESS SU
									WHERE SU.SESSID = @@SPID
									AND BJD.CSSANV = SU.ANVID'
			EXEC sp_executesql @SQL

		END

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VAA0LMCASEWORKERORDERER AS 
						SELECT ORGM.HAND, ORGM.ORGID, ORGM.ORGENHNR, ORGM.FOMDAT, ORGM.TOMDAT, ORGM.UPDSIGN, 
								ORGM.UPDDAT, ORGM.ADRKOD, ORG.NAMN ORGNAMN, ORG.ANVISORGAM, ORG.HANDLORGAM, ORG.MOTTAGORGAM, CWD.BESADR, CWD.ADRESS, CWD.POSTNR,CWD.ORT 
						FROM LD0V0I HANDL 
						INNER JOIN LD0V0I01 ORGM 
						ON HANDL.HAND = ORGM.HAND 
						INNER JOIN LD0V0J ORGDAT 
						ON ORGM.ORGID = ORGDAT.ORGID 
						INNER JOIN LD0V0J01 ORG 
						ON ORG.ORGID = ORGM.ORGID 
						AND ORG.ORGENHNR = ORGM.ORGENHNR 
						LEFT  JOIN LD0V0I02 CWD 
						ON ORG.ADRHAND = CWD.ADRKOD
						WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
							WHERE AJD.VYNAMN = ''AMUPPDBEST'' AND
								ORGM.ORGENHNR LIKE AJD.JFRDTA10) AND
								ORG.INAKTIV=0 '
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U90
						AS SELECT * FROM LD0P0U90 U90
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
											WHERE U90.BESTORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDBEST'')
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1U90
						AS SELECT * FROM LD0P0U90 U90
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
											WHERE U90.MOTORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDMOT'')
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V2U90
						AS 
						SELECT * FROM LD0V0U90 U90
						UNION
						SELECT * FROM LD0V1U90 U90
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V3J01
						AS SELECT * FROM LD0P0J01 J01
						  WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
											WHERE AJD.VYNAMN=''AMUPPDBEST''
											AND J01.ORGENHNR LIKE AJD.JFRDTA10 )
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V4J01
						AS SELECT * FROM LD0P0J01 J01
						  WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
											WHERE AJD.VYNAMN=''AMUPPDMOT''
											AND J01.ORGENHNR LIKE AJD.JFRDTA10 )
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5A AS
						SELECT * FROM LD0P0A A
							WHERE (EXISTS (SELECT ''X'' FROM LD0V1U90 U90 WHERE U90.PNR=A.PNR)
							OR EXISTS (SELECT ''X'' FROM LD0V0U10 U10 WHERE U10.PERSONNR=A.PNR))'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5I
						AS SELECT DISTINCT I.HAND, I.ADRKOD, I.NAMN, I.TELE1, I.ANKN1, I.TELE2, I.ANKN2, I.TELETID, I.EPOST, I.FAX, I.TJANST, I.FOMDAT, I.TOMDAT, I.BESFATT, 
						I.UPDDAT, I.UPDSIGN, I.DELEGATION, I.TITEL, I.EXTERN, I.BESLNRAR, I.BESLNRLNR, I.BARNTMAN, I.SOCNAMND, I.FAMBESAR, I.FAMBESLNR, 
						I.BEVAKADMIN, I.KARTLAGGARE, I.ADRKODSDN, I.HANDLAGGARE, I.BARNTMANBFATT, I.YRKESGRUPP, I.ASI, I.EXPORTBOOKN, I.EXPORTBOOKNDATE, I.BESTALLARE, I.UTFORARE, I.ADAD,I.AMMOTTAGARE, I.UPPDRAGSTAGARE, I.AKTIVITETAM,
						I.VRKID, UPPDIFOINSATSHANDL, UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 I01 ON
						I01.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDBEST'')
							AND I.HANDLAGGARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V6I
						AS SELECT DISTINCT I.HAND, I.ADRKOD, I.NAMN, I.TELE1, I.ANKN1, I.TELE2, I.ANKN2, I.TELETID, I.EPOST, I.FAX, I.TJANST, I.FOMDAT, I.TOMDAT, I.BESFATT, 
						I.UPDDAT, I.UPDSIGN, I.DELEGATION, I.TITEL, I.EXTERN, I.BESLNRAR, I.BESLNRLNR, I.BARNTMAN, I.SOCNAMND, I.FAMBESAR, I.FAMBESLNR, 
						I.BEVAKADMIN, I.KARTLAGGARE, I.ADRKODSDN, I.HANDLAGGARE, I.BARNTMANBFATT, I.YRKESGRUPP, I.ASI, I.EXPORTBOOKN, I.EXPORTBOOKNDATE, I.BESTALLARE, I.UTFORARE, I.ADAD,I.AMMOTTAGARE, I.UPPDRAGSTAGARE, I.AKTIVITETAM,
						I.VRKID, UPPDIFOINSATSHANDL, UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 I01 ON
						I01.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDMOT'')
							AND I.AMMOTTAGARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V9I
						AS SELECT DISTINCT I.HAND, I.ADRKOD, I.NAMN, I.TELE1, I.ANKN1, I.TELE2, I.ANKN2, I.TELETID, I.EPOST, I.FAX, I.TJANST, I.FOMDAT, I.TOMDAT, I.BESFATT, 
						I.UPDDAT, I.UPDSIGN, I.DELEGATION, I.TITEL, I.EXTERN, I.BESLNRAR, I.BESLNRLNR, I.BARNTMAN, I.SOCNAMND, I.FAMBESAR, I.FAMBESLNR, 
						I.BEVAKADMIN, I.KARTLAGGARE, I.ADRKODSDN, I.HANDLAGGARE, I.BARNTMANBFATT, I.YRKESGRUPP, I.ASI, I.EXPORTBOOKN, I.EXPORTBOOKNDATE, I.BESTALLARE, I.UTFORARE, I.ADAD,I.AMMOTTAGARE, I.UPPDRAGSTAGARE, I.AKTIVITETAM,
						I.VRKID, UPPDIFOINSATSHANDL, UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 I01 ON
						I01.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDBEST'')
							AND I.KARTLAGGARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V11I
						AS SELECT DISTINCT I.HAND, I.ADRKOD, I.NAMN, I.TELE1, I.ANKN1, I.TELE2, I.ANKN2, I.TELETID, I.EPOST, I.FAX, I.TJANST, I.FOMDAT, I.TOMDAT, I.BESFATT, 
						I.UPDDAT, I.UPDSIGN, I.DELEGATION, I.TITEL, I.EXTERN, I.BESLNRAR, I.BESLNRLNR, I.BARNTMAN, I.SOCNAMND, I.FAMBESAR, I.FAMBESLNR, 
						I.BEVAKADMIN, I.KARTLAGGARE, I.ADRKODSDN, I.HANDLAGGARE, I.BARNTMANBFATT, I.YRKESGRUPP, I.ASI, I.EXPORTBOOKN, I.EXPORTBOOKNDATE, I.BESTALLARE, I.UTFORARE, I.ADAD,I.AMMOTTAGARE, I.UPPDRAGSTAGARE, I.AKTIVITETAM,
						I.VRKID, UPPDIFOINSATSHANDL, UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 I01 ON
						I01.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V5AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''AMUPPDBEST'')
							AND AMBESTUPPDRAG = 1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

	IF @PersonConf = 'True'
	BEGIN 
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U90
							AS SELECT * FROM LD0P0U90 U90
							 WHERE (EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
								 WHERE U90.BESTORGENHNR LIKE AJD.JFRDTA10
								 AND AJD.VYNAMN=''AMUPPDBEST''))
							AND	(EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
															  JOIN LD0V0ANVANDARE AJD
															  ON AJD.CSSANV=SEKR.CSSANV
															  AND SEKR.PNR=U90.PNR)
														OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=U90.PNR))

							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1U90
							AS SELECT * FROM LD0P0U90 U90
								WHERE (EXISTS (SELECT ''X'' FROM LD0V5AJD AJD
												WHERE U90.MOTORGENHNR LIKE AJD.JFRDTA10
												AND AJD.VYNAMN=''AMUPPDMOT''))
								AND (EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
															  JOIN LD0V0ANVANDARE AJD
															  ON AJD.CSSANV=SEKR.CSSANV
															  AND SEKR.PNR=U90.PNR
															  )
									OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=U90.PNR
															  ))
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5A AS
							SELECT * FROM LD0P0A A
								WHERE (EXISTS (SELECT ''X'' FROM LD0V1U90 U90 WHERE U90.PNR=A.PNR)
								OR EXISTS (SELECT ''X'' FROM LD0V0U10 U10 WHERE U10.PERSONNR=A.PNR))		
								AND (EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
														  JOIN LD0V0ANVANDARE AJD
														  ON AJD.CSSANV=SEKR.CSSANV
														  AND SEKR.PNR=A.PNR)
								OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=A.PNR))'
			EXEC sp_executesql @SQL
	END

	
	SET @SQL = 'EXEC ' + @Schema + '.LD0SPGrantDataSelectionUppdragIFO'
	EXEC sp_executesql @SQL
END
END
 
GO


