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

DELETE FROM LD0P0PRO WHERE FUNKTION = 'UPPDRAG'

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


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

IF @Activation = 1
	--Activate data selection
	BEGIN
		IF @DSType = 'G'
		BEGIN
			--Create LD0V3AJD using GROUP config
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V3AJD 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 UPPDRAGIFO, 
								LD0P0SESS SU
							WHERE 	SU.SESSID = @@SPID AND
								GJD.GRUPPLNR = UPPDRAGIFO.GRUPPLNR AND
								UPPDRAGIFO.GRUPPANVID = SU.ANVID'
			EXEC sp_executesql @SQL

		END

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

		END

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V2I
						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 LD0V3AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''UPPDBEST'')
							AND I.BESTALLARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V3I
						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 LD0P0L05 L05 ON
						L05.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD 
											WHERE L05.RESTYPGRP >= AJD.JFRDTA1
											AND L05.RESTYPGRP <= AJD.JFRDTA2
											AND AJD.VYNAMN=''UPPDUTF'')
							AND I.UTFORARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V4I
						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 LD0V3AJD AJD 
											WHERE I01.ORGENHNR LIKE AJD.JFRDTA10
											AND AJD.VYNAMN=''UPPDBEST'')
							AND I.UTFORARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V2N90
						AS  SELECT * FROM LD0V0N90
							UNION
							SELECT * FROM LD0V1N90'
		EXEC sp_executesql @SQL

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

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1Q01
						AS SELECT * FROM LD0P0Q01 Q01
							WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD 
										  WHERE Q01.KOD>=AJD.JFRDTA1 
										  AND Q01.KOD<=AJD.JFRDTA2
										  AND AJD.VYNAMN=''UPPDUTF''
										  AND Q01.KODTYP=''RESTYPGRP'')
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

	IF @PersonConf = 'True'
	BEGIN 
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V3L
							AS SELECT DISTINCT L.RESURSNR, L.RESURSBEN , L.RESURSKAT , L.RESURSSTAT , L.INTEXT , L.PNR , L.ADRESS , L.POSTNR , L.ORT , L.TEL , L.ALTTEL , L.FAX , L.EPOST , L.OMRADE , L.KOMMUN , 
							L.ANNANKOMU , L.HUVUDMAN , L.HUVUDTYP , L.HAND , L.AVTAL , L.FOREST , L.KONTRAKT , L.ANTRUM , L.ANTKVM , L.VANING , L.KOK , L.MOBLERAD , L.HISS , L.VINDSUTR , L.KALLUTR , L.HYRA , 
							L.UPDDAT , L.UPDSIGN , L.COADR , L.MOBIL , L.FOLKLAN , L.FOLKDAT , L.CIVKOD , L.CIVDAT , L.MEDBSKAP , L.MEDBDAT , L.FODLAND , L.MODMAL , L.KOMSPRAK1 , L.KOMSPRAK2 , L.KOMSPRAK3 , 
							L.ADRSKYDD , L.HEMLIGTFN , L.AVREGORS , L.AVREGDAT , L.INSTNR , L.UPPDRNR , L.INSTNR_KOPPL , L.HEMSIDA , L.ORGANISATIONNR , L.EJAKTUELL , L.ANLNR , L.HEMFORS , L.TVLICENS , 
							L.MOTTAGAREID , L.MOTTAGARE , L.ANSTALLNINGSNR, L.KOKVRA, L.ORGID, L.ORGENHNR, L.EPOST2, L.SKYDDADFOLKBOKF, L.COMPANY
							FROM LD0P0L l
							JOIN LD0P0L01 L01 ON
							L.RESURSNR=L01.RESURSNR
							JOIN LD0P0Q02 Q02 ON
							Q02.KOD2=L01.RESURSTYP
							AND Q02.KODTYP=''RESTYPGRP''
							AND Q02.KODTYP2=''RESURSTYP''
							  WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE Q02.KOD>=AJD.JFRDTA1
												AND Q02.KOD<=AJD.JFRDTA2
												AND AJD.VYNAMN=''UPPDUTF'')
							AND EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
															  JOIN LD0V0ANVANDARE AJD
															  ON AJD.CSSANV=SEKR.CSSANV
															  AND SEKR.PNR=l.PNR)
									OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=l.PNR)
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1N90
							AS SELECT * FROM LD0P0N90 N90
								WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE N90.RESTYPGRP >= AJD.JFRDTA1
												AND N90.RESTYPGRP <= AJD.JFRDTA2
												AND AJD.VYNAMN=''UPPDUTF'')
								AND	(EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
															  JOIN LD0V0ANVANDARE AJD
															  ON AJD.CSSANV=SEKR.CSSANV
															  AND SEKR.PNR=N90.PNR)
														OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=N90.PNR))
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0N90
							AS SELECT * FROM LD0P0N90 N90
								WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE N90.ORGENHNR LIKE AJD.JFRDTA10
												AND AJD.VYNAMN=''UPPDBEST'')
								AND	(EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
															  JOIN LD0V0ANVANDARE AJD
															  ON AJD.CSSANV=SEKR.CSSANV
															  AND SEKR.PNR=N90.PNR)
														OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=N90.PNR))
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V4A
							AS SELECT * FROM LD0P0A A
								WHERE EXISTS (SELECT ''X'' FROM LD0V1N90 N90A
												WHERE N90A.PNR=A.PNR)'
			EXEC sp_executesql @SQL
	END

	IF @PersonConf = 'False'
	BEGIN
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V3L
							AS SELECT DISTINCT L.RESURSNR, L.RESURSBEN , L.RESURSKAT , L.RESURSSTAT , L.INTEXT , L.PNR , L.ADRESS , L.POSTNR , L.ORT , L.TEL , L.ALTTEL , L.FAX , L.EPOST , L.OMRADE , L.KOMMUN , 
							L.ANNANKOMU , L.HUVUDMAN , L.HUVUDTYP , L.HAND , L.AVTAL , L.FOREST , L.KONTRAKT , L.ANTRUM , L.ANTKVM , L.VANING , L.KOK , L.MOBLERAD , L.HISS , L.VINDSUTR , L.KALLUTR , L.HYRA , 
							L.UPDDAT , L.UPDSIGN , L.COADR , L.MOBIL , L.FOLKLAN , L.FOLKDAT , L.CIVKOD , L.CIVDAT , L.MEDBSKAP , L.MEDBDAT , L.FODLAND , L.MODMAL , L.KOMSPRAK1 , L.KOMSPRAK2 , L.KOMSPRAK3 , 
							L.ADRSKYDD , L.HEMLIGTFN , L.AVREGORS , L.AVREGDAT , L.INSTNR , L.UPPDRNR , L.INSTNR_KOPPL , L.HEMSIDA , L.ORGANISATIONNR , L.EJAKTUELL , L.ANLNR , L.HEMFORS , L.TVLICENS , 
							L.MOTTAGAREID , L.MOTTAGARE , L.ANSTALLNINGSNR, L.KOKVRA, L.ORGID, L.ORGENHNR, L.EPOST2, L.SKYDDADFOLKBOKF, L.COMPANY
							FROM LD0P0L l
							JOIN LD0P0L01 L01 ON
							L.RESURSNR=L01.RESURSNR
							JOIN LD0P0Q02 Q02 ON
							Q02.KOD2=L01.RESURSTYP
							AND Q02.KODTYP=''RESTYPGRP''
							AND Q02.KODTYP2=''RESURSTYP''
							  WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE Q02.KOD>=AJD.JFRDTA1
												AND Q02.KOD<=AJD.JFRDTA2
												AND AJD.VYNAMN=''UPPDUTF'')
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V1N90
							AS SELECT * FROM LD0P0N90 N90
								WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE N90.RESTYPGRP >= AJD.JFRDTA1
												AND N90.RESTYPGRP <= AJD.JFRDTA2
												AND AJD.VYNAMN=''UPPDUTF'')
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0N90
							AS SELECT * FROM LD0P0N90 N90
								WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
												WHERE N90.ORGENHNR LIKE AJD.JFRDTA10
												AND AJD.VYNAMN=''UPPDBEST'')
							WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V4A
							AS SELECT * FROM LD0P0A A
								WHERE EXISTS (SELECT ''X'' FROM LD0V1N90 N90A
												WHERE N90A.PNR=A.PNR)'
			EXEC sp_executesql @SQL
	END

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