CREATE OR ALTER PROCEDURE LD0SPDisableDataSelectionEnrollment
(
@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) 

DELETE FROM LD0P0PRO WHERE FUNKTION='KARTURVAL'
INSERT INTO LD0P0PRO
			SELECT 'LD0','KARTURVAL',0,CONVERT(VARCHAR(8), GETDATE(), 112),CONVERT(VARCHAR(8), GETDATE(), 108)

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1AJD AS 
						SELECT BJD.CSSANV,
							   BJD.VYNAMN,
							   BJD.BJDLNR,
							   BJD.ANVID, 
							   BJD.JFRDTA1,        
							   BJD.JFRDTA2,
							   BJD.JFRDTA3,
							   BJD.JFRDTA4,
							   BJD.JFRDTA10,       
							   BJD.JFRDTA11,
							   BJD.JFRDTA12,
							   BJD.JFRDTA13,
							   BJD.UPDDAT, 
							   BJD.UPDSIGN
						FROM LD0P0BJD BJD, 
							 LD0P0SESS SU
						WHERE SU.SESSID = @@SPID
						  AND BJD.CSSANV = SU.ANVID
						  AND BJD.VYNAMN IN (''BEVAKNING'',
											 ''BEVAKTYP'',
											 ''FLYKTING'',
											 ''KARTLÄGGN'',
											 ''KLARTEXT'')'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VAA0LMEnrollmentOrg AS SELECT * FROM LD0P0J01 J01 
					  WHERE  J01.HANDLORGAM = 1
						  WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU13
						  AS SELECT * 
						  FROM LD0P0U13'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU30
						  AS SELECT * 
						  FROM LD0P0U30'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU34
						  AS SELECT * 
						  FROM LD0P0U34'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU40
						  AS SELECT * 
						  FROM LD0P0U40'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU41
						  AS SELECT * 
						  FROM LD0P0U41'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU42
						  AS SELECT * 
						  FROM LD0P0U42'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U13
						  AS SELECT * 
						  FROM LD0P0U13'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U30
						  AS SELECT * 
						  FROM LD0P0U30'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U34
						  AS SELECT * 
						  FROM LD0P0U34'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U40
						  AS SELECT * 
						  FROM LD0P0U40'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U41
						  AS SELECT * 
						  FROM LD0P0U41'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U42
						  AS SELECT * 
						  FROM LD0P0U42'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U75
						  AS SELECT * 
						  FROM LD0P0U75'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U76
						  AS SELECT * 
						  FROM LD0P0U76'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U72
						  AS SELECT * 
						  FROM LD0P0U72'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1T41
						AS SELECT * FROM LD0P0T41 T41
						WHERE EXISTS (SELECT ''X'' FROM LD0V0C C
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 21)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C C 
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 22)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C20 C20 
								WHERE C20.INSNR = T41.AGARNR AND
									  T41.AGARTYP = 23)
						   OR EXISTS (SELECT ''X'' FROM LD0V0H H 
								WHERE H.AKTUALNR = T41.AGARNR AND
									  T41.AGARTYP = 20)
						   OR EXISTS (SELECT ''X'' FROM LD0V0L L 
   								WHERE L.RESURSNR = T41.AGARNR AND
									  T41.AGARTYP = 38)
						   OR EXISTS (SELECT ''X'' FROM LD0V0L L 
								WHERE L.UPPDRNR = T41.AGARNR AND
									  T41.AGARTYP = 25)
						   OR EXISTS (SELECT ''X'' FROM LD0V0A70 A70 
								WHERE A70.FLYKTNR = T41.AGARNR AND
									  T41.AGARTYP = 40)
						   OR EXISTS (SELECT ''X'' FROM LD0V0A700 A700 
								WHERE A700.FLYKTNR = T41.AGARNR AND
									  T41.AGARTYP = 40)
						   OR EXISTS (SELECT ''X'' FROM LD0V0B B 
								WHERE B.AKTNR = T41.AGARNR AND
									  T41.AGARTYP = 39)
						   OR EXISTS (SELECT ''X'' FROM LD0V0E30 E30 
								WHERE E30.ATERLNR = T41.AGARNR AND
									  T41.AGARTYP = 44)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U10 U10 
								WHERE U10.INSKRIVNINGSNR = T41.AGARNR AND
									  T41.AGARTYP=30)'

		SET @SQL2='OR EXISTS (SELECT ''X'' FROM LD0V0U30 U30 
								WHERE U30.PLANNR = T41.AGARNR AND
									  T41.AGARTYP=32)
						   OR EXISTS (SELECT ''X'' FROM LD0V1U60 U60 
								WHERE U60.PRAKPROJNR = T41.AGARNR AND
									  T41.AGARTYP IN (33, 34, 35, 36))
						   OR EXISTS (SELECT ''X'' FROM LD0V0N90 N90 
								WHERE N90.UPPDRAGNR = T41.AGARNR AND
									  T41.AGARTYP=46)
						   OR EXISTS (SELECT ''X'' FROM LD0V0L L 
								WHERE L.RESURSNR = T41.AGARNR AND
									  T41.AGARTYP = 47)
						   OR EXISTS (SELECT ''X'' FROM LD0V2U90 U90 
								WHERE U90.LNUPPDRAGNR = T41.AGARNR AND
									  T41.AGARTYP=48)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C C
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 42)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C20 C20 
								WHERE C20.INSNR = T41.AGARNR AND
									  T41.AGARTYP = 41)
						   OR EXISTS (SELECT ''X'' FROM LD0V0B B 
								WHERE B.AKTNR = T41.AGARNR AND
									  T41.AGARTYP = 45)
						   OR EXISTS (SELECT ''X'' FROM LD0V0H H 
								WHERE H.AKTUALNR = T41.AGARNR AND
									  T41.AGARTYP = 60)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C C 
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 61)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U01 U01
								WHERE U01.KARTLAGGNR = T41.AGARNR AND
									  T41.AGARTYP = 62)
						   OR EXISTS (SELECT ''X'' FROM LD0V0B B 
								WHERE B.AKTNR = T41.AGARNR AND
									  T41.AGARTYP = 50)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C C 
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 51)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C C 
								WHERE C.AEVERKNR = T41.AGARNR AND
									  T41.AGARTYP = 52)
						   OR EXISTS (SELECT ''X'' FROM LD0V0C20 C20 
								WHERE C20.INSNR = T41.AGARNR AND
									  T41.AGARTYP = 53)
						   OR EXISTS (SELECT ''X'' FROM LD0V0N900 N900 
								WHERE N900.UPPDRAGIFONR = T41.AGARNR AND
									  T41.AGARTYP = 63)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U34 U34 
								WHERE U34.PLANAKTIVITETNR = T41.AGARNR AND
									  T41.AGARTYP IN (64,74))
						   OR EXISTS (SELECT ''X'' FROM LD0V0U70 U70 
								WHERE U70.ENKELPLATSNR = T41.AGARNR AND
									  T41.AGARTYP = 66)
						   OR EXISTS (SELECT ''X'' FROM LD0V0N900 N900 
								WHERE N900.UPPDRAGIFONR = T41.AGARNR AND
									  T41.AGARTYP = 68)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U90 U90 
								WHERE U90.LNUPPDRAGNR = T41.AGARNR AND
									  T41.AGARTYP=69)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U10 U10 
								WHERE U10.INSKRIVNINGSNR = T41.AGARNR AND
									  T41.AGARTYP=70)
						   OR EXISTS (SELECT ''X'' FROM LD0V0U35 U35 
								WHERE U35.AKTIVITETNR = T41.AGARNR AND
									  T41.AGARTYP=71)'
		SET @sqlQuery = CONCAT(@SQL, @SQL2)
		EXECUTE sp_executesql @sqlQuery
				
IF @PersonConf = 'False'
BEGIN
		--Create all the views with no dataselection and no person confidentiality!

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU10
						  AS SELECT * 
						  FROM LD0P0U10'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU05
						  AS SELECT * 
						  FROM LD0P0U05'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U10
						  AS SELECT * 
						  FROM LD0P0U10'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1U05
						  AS SELECT * 
						  FROM LD0P0U05'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U05
						  AS SELECT * 
						  FROM LD0P0U05'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V5A
						  AS SELECT * 
						  FROM LD0P0A'
		EXEC sp_executesql @SQL

END

IF @PersonConf = 'True'
BEGIN 
		--Create all the views with no dataselection but with person confidentiality!
		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU10
						  AS SELECT * 
						  FROM LD0P0U10 WHERE EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
								  JOIN LD0V0ANVANDARE AJD
								  ON AJD.CSSANV=SEKR.CSSANV
								  AND SEKR.PNR=LD0P0U10.PERSONNR)
						OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=LD0P0U10.PERSONNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0VLU05
						  AS SELECT * 
						  FROM LD0P0U05
							WHERE EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
													  JOIN LD0V0ANVANDARE AJD
													  ON AJD.CSSANV=SEKR.CSSANV
													  AND SEKR.PNR=LD0P0U05.PERSONNR)
							OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=LD0P0U05.PERSONNR)'
		EXEC sp_executesql @SQL


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

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1U05
						  AS SELECT * 
						  FROM LD0P0U05
							WHERE EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
													  JOIN LD0V0ANVANDARE AJD
													  ON AJD.CSSANV=SEKR.CSSANV
													  AND SEKR.PNR=LD0P0U05.PERSONNR)
							OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=LD0P0U05.PERSONNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0U05
						  AS SELECT * 
						  FROM LD0P0U05
							WHERE EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
													  JOIN LD0V0ANVANDARE AJD
													  ON AJD.CSSANV=SEKR.CSSANV
													  AND SEKR.PNR=LD0P0U05.PERSONNR)
							OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=LD0P0U05.PERSONNR)'
		EXEC sp_executesql @SQL

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

END
END
