You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
708 lines
19 KiB
708 lines
19 KiB
ALTER TABLE [dbo].[Application] ADD
|
|
[Tag_Externe_MA] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
|
|
update application set tag_externe_ma='TGE;Vitruv';
|
|
GO
|
|
ALTER TABLE [dbo].[Personal] ADD
|
|
[VerantwTKBMA] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
|
|
[NextControlDate] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
|
|
GO
|
|
update personal set veranttkbma=''
|
|
update personal set nextcontroldate=''
|
|
go
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Insert] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Personal_Insert]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Update] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Personal_Update]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Delete] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Personal_Delete]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_SelectOne] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Personal_SelectOne]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_SelectAll] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Personal_SelectAll]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Insert] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Application_Insert]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Update] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Application_Update]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Delete] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Application_Delete]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_SelectOne] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Application_SelectOne]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_SelectAll] Script Date: 16.01.2021 15:27:34 ******/
|
|
DROP PROCEDURE [dbo].[pr_Application_SelectAll]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_SelectAll] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'Application'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Application_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[applikationsnr],
|
|
[version],
|
|
[showlogin],
|
|
[mandantnr],
|
|
[aktiv],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[tmp_filepath],
|
|
[Tag_Externe_MA]
|
|
FROM [dbo].[Application]
|
|
ORDER BY
|
|
[applikationsnr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_SelectOne] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'Application'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iapplikationsnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Application_SelectOne]
|
|
@iapplikationsnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[applikationsnr],
|
|
[version],
|
|
[showlogin],
|
|
[mandantnr],
|
|
[aktiv],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[tmp_filepath],
|
|
[Tag_Externe_MA]
|
|
FROM [dbo].[Application]
|
|
WHERE
|
|
[applikationsnr] = @iapplikationsnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Delete] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'Application'
|
|
-- using the Primary Key.
|
|
-- Gets: @iapplikationsnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Application_Delete]
|
|
@iapplikationsnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[Application]
|
|
WHERE
|
|
[applikationsnr] = @iapplikationsnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Update] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'Application'
|
|
-- Gets: @iapplikationsnr int
|
|
-- Gets: @sversion varchar(4)
|
|
-- Gets: @bshowlogin bit
|
|
-- Gets: @imandantnr int
|
|
-- Gets: @baktiv bit
|
|
-- Gets: @daerstellt_am datetime
|
|
-- Gets: @damutiert_am datetime
|
|
-- Gets: @imutierer int
|
|
-- Gets: @stmp_filepath varchar(255)
|
|
-- Gets: @sTag_Externe_MA varchar(255)
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Application_Update]
|
|
@iapplikationsnr int,
|
|
@sversion varchar(4),
|
|
@bshowlogin bit,
|
|
@imandantnr int,
|
|
@baktiv bit,
|
|
@daerstellt_am datetime,
|
|
@damutiert_am datetime,
|
|
@imutierer int,
|
|
@stmp_filepath varchar(255),
|
|
@sTag_Externe_MA varchar(255),
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[Application]
|
|
SET
|
|
[version] = @sversion,
|
|
[showlogin] = @bshowlogin,
|
|
[mandantnr] = @imandantnr,
|
|
[aktiv] = @baktiv,
|
|
[erstellt_am] = @daerstellt_am,
|
|
[mutiert_am] = @damutiert_am,
|
|
[mutierer] = @imutierer,
|
|
[tmp_filepath] = @stmp_filepath,
|
|
[Tag_Externe_MA] = @sTag_Externe_MA
|
|
WHERE
|
|
[applikationsnr] = @iapplikationsnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Application_Insert] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'Application'
|
|
-- Gets: @iapplikationsnr int
|
|
-- Gets: @sversion varchar(4)
|
|
-- Gets: @bshowlogin bit
|
|
-- Gets: @imandantnr int
|
|
-- Gets: @baktiv bit
|
|
-- Gets: @daerstellt_am datetime
|
|
-- Gets: @damutiert_am datetime
|
|
-- Gets: @imutierer int
|
|
-- Gets: @stmp_filepath varchar(255)
|
|
-- Gets: @sTag_Externe_MA varchar(255)
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Application_Insert]
|
|
@iapplikationsnr int,
|
|
@sversion varchar(4),
|
|
@bshowlogin bit,
|
|
@imandantnr int,
|
|
@baktiv bit,
|
|
@daerstellt_am datetime,
|
|
@damutiert_am datetime,
|
|
@imutierer int,
|
|
@stmp_filepath varchar(255),
|
|
@sTag_Externe_MA varchar(255),
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[Application]
|
|
(
|
|
[applikationsnr],
|
|
[version],
|
|
[showlogin],
|
|
[mandantnr],
|
|
[aktiv],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[tmp_filepath],
|
|
[Tag_Externe_MA]
|
|
)
|
|
VALUES
|
|
(
|
|
@iapplikationsnr,
|
|
@sversion,
|
|
@bshowlogin,
|
|
@imandantnr,
|
|
@baktiv,
|
|
@daerstellt_am,
|
|
@damutiert_am,
|
|
@imutierer,
|
|
@stmp_filepath,
|
|
@sTag_Externe_MA
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_SelectAll] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'Personal'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Personal_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[Personalnr],
|
|
[ParentID],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[TGNummer],
|
|
[Name],
|
|
[Sequenz],
|
|
[Aktiv],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[VerantwTKBMA],
|
|
[NextControlDate]
|
|
FROM [dbo].[Personal]
|
|
ORDER BY
|
|
[Personalnr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_SelectOne] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'Personal'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iPersonalnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Personal_SelectOne]
|
|
@iPersonalnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[Personalnr],
|
|
[ParentID],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[TGNummer],
|
|
[Name],
|
|
[Sequenz],
|
|
[Aktiv],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[VerantwTKBMA],
|
|
[NextControlDate]
|
|
FROM [dbo].[Personal]
|
|
WHERE
|
|
[Personalnr] = @iPersonalnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Delete] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'Personal'
|
|
-- using the Primary Key.
|
|
-- Gets: @iPersonalnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Personal_Delete]
|
|
@iPersonalnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[Personal]
|
|
WHERE
|
|
[Personalnr] = @iPersonalnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Update] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'Personal'
|
|
-- Gets: @iPersonalnr int
|
|
-- Gets: @iParentID int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @sTGNummer varchar(255)
|
|
-- Gets: @sName varchar(255)
|
|
-- Gets: @iSequenz int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @sVerantwTKBMA varchar(255)
|
|
-- Gets: @sNextControlDate varchar(255)
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Personal_Update]
|
|
@iPersonalnr int,
|
|
@iParentID int,
|
|
@sBezeichnung varchar(255),
|
|
@sBeschreibung varchar(255),
|
|
@sTGNummer varchar(255),
|
|
@sName varchar(255),
|
|
@iSequenz int,
|
|
@bAktiv bit,
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@sVerantwTKBMA varchar(255),
|
|
@sNextControlDate varchar(255),
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[Personal]
|
|
SET
|
|
[ParentID] = @iParentID,
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[TGNummer] = @sTGNummer,
|
|
[Name] = @sName,
|
|
[Sequenz] = @iSequenz,
|
|
[Aktiv] = @bAktiv,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[VerantwTKBMA] = @sVerantwTKBMA,
|
|
[NextControlDate] = @sNextControlDate
|
|
WHERE
|
|
[Personalnr] = @iPersonalnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_Personal_Insert] Script Date: 16.01.2021 15:27:34 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'Personal'
|
|
-- Gets: @iPersonalnr int
|
|
-- Gets: @iParentID int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @sTGNummer varchar(255)
|
|
-- Gets: @sName varchar(255)
|
|
-- Gets: @iSequenz int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @sVerantwTKBMA varchar(255)
|
|
-- Gets: @sNextControlDate varchar(255)
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_Personal_Insert]
|
|
@iPersonalnr INT,
|
|
@iParentID INT,
|
|
@sBezeichnung VARCHAR(255),
|
|
@sBeschreibung VARCHAR(255),
|
|
@sTGNummer VARCHAR(255),
|
|
@sName VARCHAR(255),
|
|
@iSequenz INT,
|
|
@bAktiv BIT,
|
|
@daErstellt_am DATETIME,
|
|
@daMutiert_am DATETIME,
|
|
@iMutierer INT,
|
|
@sVerantwTKBMA VARCHAR(255),
|
|
@sNextControlDate VARCHAR(255),
|
|
@iErrorCode INT OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[Personal]
|
|
(
|
|
[Personalnr],
|
|
[ParentID],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[TGNummer],
|
|
[Name],
|
|
[Sequenz],
|
|
[Aktiv],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[VerantwTKBMA],
|
|
[NextControlDate]
|
|
)
|
|
VALUES
|
|
(
|
|
@iPersonalnr,
|
|
@iParentID,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@sTGNummer,
|
|
@sName,
|
|
@iSequenz,
|
|
@bAktiv,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@sVerantwTKBMA,
|
|
@sNextControlDate
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_tgnummer_fnktstelle] Script Date: 16.01.2021 15:28:56 ******/
|
|
DROP PROCEDURE [dbo].[sp_rpt_tgnummer_fnktstelle]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_tgnummer] Script Date: 16.01.2021 15:28:56 ******/
|
|
DROP PROCEDURE [dbo].[sp_rpt_tgnummer]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_tgnummer] Script Date: 16.01.2021 15:28:56 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_rpt_tgnummer] @sqlwhere VARCHAR(255)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @fstelle VARCHAR(255)
|
|
DECLARE @fnr INT
|
|
DECLARE @fbezeichnung VARCHAR(255)
|
|
DECLARE @fnummer VARCHAR(255)
|
|
DECLARE @parent INT
|
|
|
|
DECLARE @tgtmp1 TABLE
|
|
(
|
|
ID INT ,
|
|
Bezeichnung VARCHAR(255) ,
|
|
Beschreibung VARCHAR(255) ,
|
|
Sequenz INT ,
|
|
aktiv BIT ,
|
|
erstellt_am DATETIME ,
|
|
mutiert_am DATETIME ,
|
|
mutierer INT ,
|
|
typ VARCHAR(255) ,
|
|
MKey INT,
|
|
Berechtigungstyp VARCHAR(255)
|
|
)
|
|
|
|
DECLARE @tgt1 TABLE
|
|
(
|
|
funktionsnummer VARCHAR(255) ,
|
|
funktionsbezeichnung VARCHAR(255) ,
|
|
plattform VARCHAR(255) ,
|
|
verwaltungmit VARCHAR(255) ,
|
|
verwaltungdurch VARCHAR(255) ,
|
|
berechtigungIn VARCHAR(255) ,
|
|
berechtigungfuer VARCHAR(255) ,
|
|
eigentuemer VARCHAR(255),
|
|
sequenz int,
|
|
Beschreibung varchar(255)
|
|
--,
|
|
--s1 INT,
|
|
--s2 INT,
|
|
--s3 INT,
|
|
--s4 int
|
|
)
|
|
|
|
DECLARE @sql VARCHAR(4096)
|
|
SET @sql = 'SELECT dbo.Funktionstelle.Funktionsstelle '
|
|
SET @sql = @sql + ' FROM dbo.Personal INNER JOIN '
|
|
SET @sql = @sql + ' dbo.Personal_FunktionStelle ON dbo.Personal.Personalnr = dbo.Personal_FunktionStelle.PersonalNr INNER JOIN '
|
|
SET @sql = @sql + ' dbo.Funktionstelle ON dbo.Personal_FunktionStelle.FunktionStelleNr = dbo.Funktionstelle.FunktionstelleNr '
|
|
SET @sql = @sql + ' WHERE funktionstelle.aktiv=1 and personal_funktionstelle.aktiv=1 and ' + @sqlwhere
|
|
|
|
DECLARE @tgtmp TABLE ( fstelle VARCHAR(255) )
|
|
|
|
INSERT @tgtmp
|
|
EXEC ( @sql
|
|
)
|
|
|
|
|
|
DECLARE xx CURSOR
|
|
FOR
|
|
SELECT fstelle
|
|
FROM @tgtmp
|
|
OPEN xx
|
|
FETCH NEXT FROM xx INTO @fnummer
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @fnr = funktionstellenr
|
|
FROM funktionstelle
|
|
WHERE funktionsstelle = @fnummer AND aktiv=1
|
|
|
|
INSERT @tgt1 SELECT DISTINCT * FROM dbo.fnkt_get_Report_Data(@fnr)
|
|
FETCH NEXT FROM xx INTO @fnummer
|
|
END
|
|
CLOSE xx
|
|
DEALLOCATE xx
|
|
DECLARE @tg VARCHAR(255)
|
|
DECLARE @name VARCHAR(255)
|
|
|
|
DECLARE @tgtg TABLE
|
|
(
|
|
tgnummer VARCHAR(255) ,
|
|
NAME VARCHAR(255)
|
|
)
|
|
|
|
SET @sql = 'SELECT dbo.Personal.TGNummer, dbo.personal.name '
|
|
SET @sql = @sql + ' FROM dbo.Personal '
|
|
SET @sql = @sql + ' WHERE ' + @sqlwhere
|
|
|
|
INSERT @tgtg
|
|
EXECUTE ( @sql
|
|
)
|
|
UPDATE @tgt1
|
|
SET funktionsnummer = tgnummer, funktionsbezeichnung = name
|
|
FROM @tgtg
|
|
|
|
SELECT DISTINCT funktionsnummer, funktionsbezeichnung, plattform, verwaltungmit, verwaltungdurch, berechtigungIn, berechtigungfuer,
|
|
eigentuemer, CONVERT(varchar, sequenz)+' '+berechtigungfuer AS sortstring, Beschreibung
|
|
FROM @tgt1
|
|
where beschreibung is not null
|
|
--ORDER BY verwaltungdurch, plattform, verwaltungmit
|
|
ORDER BY plattform, verwaltungmit, berechtigungin, berechtigungfuer
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_tgnummer_fnktstelle] Script Date: 16.01.2021 15:28:56 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_rpt_tgnummer_fnktstelle]
|
|
@sqlwhere VARCHAR(1024)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT TOP (100) PERCENT dbo.Personal.TGNummer AS UserID, dbo.Personal.Name,dbo.Funktionstelle.Funktionsstelle AS Funktionsstelle, dbo.Funktionstelle.Bezeichnung, dbo.Personal_FunktionStelle.Beschreibung
|
|
INTO #tmp1
|
|
FROM dbo.Personal INNER JOIN
|
|
dbo.Personal_FunktionStelle ON dbo.Personal.Personalnr = dbo.Personal_FunktionStelle.PersonalNr INNER JOIN
|
|
dbo.Funktionstelle ON dbo.Personal_FunktionStelle.FunktionStelleNr = dbo.Funktionstelle.FunktionstelleNr
|
|
WHERE (dbo.Personal.TGNummer <> '') AND (dbo.Personal.Aktiv = 1) AND Personal_FunktionStelle.Aktiv=1 AND dbo.Funktionstelle.Aktiv=1
|
|
ORDER BY UserID, dbo.Funktionstelle.Funktionsstelle, dbo.Funktionstelle.Bezeichnung
|
|
|
|
UPDATE #tmp1 SET funktionsstelle=NULL WHERE ISNUMERIC(funktionsstelle)=0
|
|
--select userid, name, CAST(funktionsstelle as Numeric(17,0)) as Funktionsstelle,bezeichnung from #tmp1
|
|
SELECT Userid, name,CAST(Funktionsstelle AS NUMERIC(17,0)) AS Funktionsstelle,Bezeichnung, Beschreibung FROM #tmp1 ORDER BY userid
|
|
--select userid, name, Funktionsstelle,bezeichnung from #tmp1
|
|
DROP TABLE #tmp1
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|