CREATE OR ALTER PROCEDURE LD0SPEnableDataSelectionUppdragIFO
(
@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 = 'UPPDRAGIFO'

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


IF @Activation = 0 
BEGIN
	SET @SQL =  'EXEC ' + @Schema + '.LD0SPDisableDataSelectionUppdragIFO @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) +'.LD0V8I
						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, I.UPPDIFOINSATSHANDL, I.UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 UPPDRAGIFO ON
						UPPDRAGIFO.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD 
							WHERE UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
							AND AJD.VYNAMN=''UPPDIFO'')
							AND I.UPPDIFOUTFORARE=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

		SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V7I
						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, I.UPPDIFOINSATSHANDL, I.UPPDIFOUTFORARE, I.AMBESTUPPDRAG, I.UPPDIFOMOTTAGARE
						FROM LD0P0I I
						JOIN LD0P0I01 UPPDRAGIFO ON
						UPPDRAGIFO.HAND=I.HAND
							WHERE EXISTS (SELECT ''X'' FROM LD0V3AJD AJD 
							WHERE UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
							AND AJD.VYNAMN=''UPPDIFO'')
							AND I.UPPDIFOINSATSHANDL=1
						WITH CHECK OPTION'
		EXEC sp_executesql @SQL

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

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

	IF @PersonConf = 'True'
	BEGIN 
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0N900
                    AS SELECT * FROM LD0P0N900 UPPDRAGIFO
                    WHERE (EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
                      WHERE (UPPDRAGIFO.ORGID > 0
                             AND UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
                             AND AJD.VYNAMN=''UPPDIFO'')
                      OR (UPPDRAGIFO.RESTYPGRP > 0
                          AND UPPDRAGIFO.RESTYPGRP >= AJD.JFRDTA1
                          AND UPPDRAGIFO.RESTYPGRP <= AJD.JFRDTA2
                          AND AJD.VYNAMN=''UPPDUTF'')
                      OR (UPPDRAGIFO.EJBISTTYP > 0
                          AND UPPDRAGIFO.EJBISTTYP >= AJD.JFRDTA1
                          AND UPPDRAGIFO.EJBISTTYP <= AJD.JFRDTA2
                          AND AJD.VYNAMN=''EJBISTTYP'')
		                     )
                     OR EXISTS (SELECT ''X'' FROM LD0V0N901 N901
                                WHERE UPPDRAGIFO.UPPDRAGIFONR = N901.UPPDRAGIFONR)
                     )
                     AND (EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR
                       JOIN LD0V0ANVANDARE AJD
                       ON AJD.CSSANV=SEKR.CSSANV
                       AND SEKR.PNR=UPPDRAGIFO.PNR)
                       OR NOT EXISTS (SELECT ''X'' FROM LD0P0SEKR SEKR WHERE SEKR.PNR=UPPDRAGIFO.PNR))
                    WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.VIEW LD0V0N900_READ
                    AS SELECT UPPDRAGIFO.* FROM LD0P0N900 UPPDRAGIFO
                    LEFT JOIN LD0P0SEKR sek 
                      ON UPPDRAGIFO.PNR = sek.PNR
                      WHERE (sek.PNR IS NULL 
                      OR sek.CSSANV = (SELECT ANVID AS CSSANV  FROM LD0P0SESS SESS WHERE SESS.SESSID=@@SPID))
                      AND (EXISTS (SELECT ''X'' FROM LD0V3AJD AJD
                        WHERE (UPPDRAGIFO.ORGID > 0
                               AND UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
                               AND AJD.VYNAMN=''UPPDIFO'')
                        OR (UPPDRAGIFO.RESTYPGRP > 0
                            AND UPPDRAGIFO.RESTYPGRP >= AJD.JFRDTA1
                            AND UPPDRAGIFO.RESTYPGRP <= AJD.JFRDTA2
                            AND AJD.VYNAMN=''UPPDUTF'')
                        OR (UPPDRAGIFO.EJBISTTYP > 0
                            AND UPPDRAGIFO.EJBISTTYP >= AJD.JFRDTA1
                            AND UPPDRAGIFO.EJBISTTYP <= AJD.JFRDTA2
                            AND AJD.VYNAMN=''EJBISTTYP'')
		                      )
                       OR EXISTS (SELECT ''X'' FROM LD0V0N901 N901
                                  WHERE UPPDRAGIFO.UPPDRAGIFONR = N901.UPPDRAGIFONR)
                       )
                    WITH CHECK OPTION'
			EXEC sp_executesql @SQL
	END

	IF @PersonConf = 'False'
	BEGIN
			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0N900
                    AS SELECT * FROM LD0P0N900 UPPDRAGIFO
                      WHERE EXISTS 
                      (SELECT ''X'' FROM LD0V3AJD AJD
                       WHERE (UPPDRAGIFO.ORGID > 0
                              AND UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
                              AND AJD.VYNAMN=''UPPDIFO'')
                       OR (UPPDRAGIFO.RESTYPGRP > 0
                           AND UPPDRAGIFO.RESTYPGRP >= AJD.JFRDTA1
                           AND UPPDRAGIFO.RESTYPGRP <= AJD.JFRDTA2
                           AND AJD.VYNAMN=''UPPDUTF'')
                       OR (UPPDRAGIFO.EJBISTTYP > 0
                           AND UPPDRAGIFO.EJBISTTYP >= AJD.JFRDTA1
                           AND UPPDRAGIFO.EJBISTTYP <= AJD.JFRDTA2
                           AND AJD.VYNAMN=''EJBISTTYP'')
                          )
                       OR EXISTS (SELECT ''X'' FROM LD0V0N901 N901
                                  WHERE UPPDRAGIFO.UPPDRAGIFONR = N901.UPPDRAGIFONR)
                    WITH CHECK OPTION'
			EXEC sp_executesql @SQL

			SET @SQL = 'CREATE OR ALTER VIEW '+ QUOTENAME(@Schema) +'.LD0V0N900_READ
                    AS SELECT * FROM LD0P0N900 UPPDRAGIFO
                      WHERE EXISTS 
                      (SELECT ''X'' FROM LD0V3AJD AJD
                       WHERE (UPPDRAGIFO.ORGID > 0
                              AND UPPDRAGIFO.ORGENHNR LIKE AJD.JFRDTA10
                              AND AJD.VYNAMN=''UPPDIFO'')
                       OR (UPPDRAGIFO.RESTYPGRP > 0
                           AND UPPDRAGIFO.RESTYPGRP >= AJD.JFRDTA1
                           AND UPPDRAGIFO.RESTYPGRP <= AJD.JFRDTA2
                           AND AJD.VYNAMN=''UPPDUTF'')
                       OR (UPPDRAGIFO.EJBISTTYP > 0
                           AND UPPDRAGIFO.EJBISTTYP >= AJD.JFRDTA1
                           AND UPPDRAGIFO.EJBISTTYP <= AJD.JFRDTA2
                           AND AJD.VYNAMN=''EJBISTTYP'')
                          )
                       OR EXISTS (SELECT ''X'' FROM LD0V0N901 N901
                                  WHERE UPPDRAGIFO.UPPDRAGIFONR = N901.UPPDRAGIFONR)
                    WITH CHECK OPTION'
			EXEC sp_executesql @SQL

	END

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