CREATE OR ALTER PROCEDURE LD0SPEnableDataSelectionRefugee
(
@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 refugee. 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 refugee.', 16, 1);
        RETURN;  -- Optionally stop execution after the error is raised
    END

DELETE FROM LD0P0PRO WHERE FUNKTION = 'FLYKTURVAL'

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


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

IF @Activation = 1
	--Activate data selection
	BEGIN
		IF @DSType = 'G'
		BEGIN
			--Create LD0V1AJD using GROUP config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1AJD 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
							  AND GJD.VYNAMN IN (''BEVAKNING'',
												 ''BEVAKTYP'',
												 ''FLYKTING'',
												 ''KARTLÄGGN'',
												 ''KLARTEXT'')'
			EXEC sp_executesql @SQL

			--Create LD0V1AJD using GROUP config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V7AJD 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
							  AND GJD.VYNAMN = ''FLYKTING'''
			EXEC sp_executesql @SQL
		END

		IF @DSType = 'U'
		BEGIN
			--Create LD0V1AJD using USER config
			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

			--Create LD0V1AJD using USER config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V7AJD 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 = ''FLYKTING'''
			EXEC sp_executesql @SQL
		END

		IF @PersonConf = 'True'
		BEGIN 
			--Create all the views based on LD0V1AJD/LD0V7AJDs appearance with person confidentiality!
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A70
						  AS SELECT * 
						  FROM LD0P0A70 A70
						  WHERE EXISTS(SELECT ''X''
								FROM LD0V1AJD AJD
								WHERE AJD.VYNAMN = ''FLYKTING''
								AND A70.ORGID = AJD.JFRDTA3
								AND A70.ORGENHNR LIKE AJD.JFRDTA10)
							AND	(EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
													  JOIN LD0V0ANVANDARE AJD
													  ON AJD.CSSANV=SEKR.CSSANV
													  AND SEKR.PNR=A70.PNR)
												OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=A70.PNR))
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL
	
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A700
							  AS SELECT * 
							  FROM LD0P0A700 A700
							  WHERE EXISTS(SELECT ''X''
									FROM LD0V1AJD AJD
									WHERE AJD.VYNAMN = ''FLYKTING''
									AND A700.ORGID = AJD.JFRDTA3
									AND A700.ORGENHNR LIKE AJD.JFRDTA10)
						AND	(EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
														  JOIN LD0V0ANVANDARE AJD
														  ON AJD.CSSANV=SEKR.CSSANV
														  AND SEKR.PNR=A700.PNR)
													OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=A700.PNR))
						WITH CHECK OPTION'
			EXEC sp_executesql @SQL		
		END

		IF @PersonConf = 'False'
		BEGIN
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A70
						  AS SELECT * 
						  FROM LD0P0A70 A70
						  WHERE EXISTS(SELECT ''X''
								FROM LD0V1AJD AJD
								WHERE AJD.VYNAMN = ''FLYKTING''
								AND A70.ORGID = AJD.JFRDTA3
								AND A70.ORGENHNR LIKE AJD.JFRDTA10)'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A700
							  AS SELECT * 
							  FROM LD0P0A700 A700
							  WHERE EXISTS(SELECT ''X''
									FROM LD0V7AJD AJD
									WHERE AJD.VYNAMN = ''FLYKTING''
									AND A700.ORGID = AJD.JFRDTA3
									AND A700.ORGENHNR LIKE AJD.JFRDTA10)
									WITH CHECK OPTION'
			EXEC sp_executesql @SQL

		END

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A80 AS
						SELECT * 
							  FROM LD0P0A80 A80
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A80.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A81 AS
						SELECT * 
							  FROM LD0P0A81 A81
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A81.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A82 AS
						SELECT * 
							  FROM LD0P0A82 A82
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A82.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A83 AS
						SELECT * 
							  FROM LD0P0A83 A83
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A83.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A84 AS
						SELECT * 
							  FROM LD0P0A84 A84
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A84.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A85 AS
						SELECT * 
							  FROM LD0P0A85 A85
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A85.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A86 AS
						SELECT * 
							  FROM LD0P0A86 A86
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A86.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A87 AS
						SELECT * 
							  FROM LD0P0A87 A87
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A87.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A90 AS
						SELECT * 
							  FROM LD0P0A90 A90
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A90.FLYKTNR)'
		EXEC sp_executesql @SQL


		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A770 AS
						SELECT * 
							  FROM LD0P0A770 A770
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A770.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A780 AS
						SELECT * 
							  FROM LD0P0A780 A780
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A780.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A820 AS
						SELECT * 
							  FROM LD0P0A820 A820
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A820.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A880 AS
						SELECT * 
							  FROM LD0P0A880 A880
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A880.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A901 AS
						SELECT * 
							  FROM LD0P0A901 A901
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A901.FLYKTNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A902 AS
						SELECT * 
							  FROM LD0P0A902 A902
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A820 A820
									WHERE A820.SCHABLNR = A902.SCHABLNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A903 AS
						SELECT * 
							  FROM LD0P0A903 A903
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A880 A880
									WHERE A880.ATERSOKTILLFNR = A903.ATERSOKTILLFNR)'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0A71 AS
						SELECT * 
							  FROM LD0P0A71 A71
							  WHERE EXISTS (SELECT ''X''
									FROM LD0V0A70 A70
									WHERE A70.FLYKTNR = A71.FLYKTNR)
							  OR EXISTS (SELECT ''X''
									FROM LD0V0A700 A700
									WHERE A700.FLYKTNR = A71.FLYKTNR)'
		EXEC sp_executesql @SQL

	END
	
	SET @SQL = 'EXEC ' + @Schema + '.LD0SPGrantDataSelectionRefugee'
	EXEC sp_executesql @SQL

END
 
