69 lines
2.0 KiB
Transact-SQL
69 lines
2.0 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dbo.uspGetAlkalmazottByVezetoiOraszamOk
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[uspGetAlkalmazottByVezetoiOraszamOk]
|
|
@pTanevId int
|
|
,@pNyomtatvanyNyelv int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY result.Nev, result.VezetoiOraszamOk) AS Id
|
|
,result.*
|
|
FROM
|
|
(
|
|
SELECT
|
|
f.C_NYOMTATASINEV AS Nev
|
|
,CASE @pNyomtatvanyNyelv
|
|
WHEN 2 THEN d.C_NAME_1
|
|
WHEN 1 THEN d.C_NAME_2
|
|
ELSE d.C_NAME
|
|
END AS VezetoiOraszamOk
|
|
,0 AS IsIntezmenyAdat
|
|
FROM T_MUNKAUGYIADATOK_OSSZES ma
|
|
INNER JOIN T_FELHASZNALO_OSSZES f on f.ID = ma.C_ALKALMAZOTTID
|
|
AND f.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.ID = ma.C_VEZETOIORASZAMOK
|
|
AND d.TOROLT = 'F'
|
|
AND d.C_TANEVID = ma.C_TANEVID
|
|
WHERE ma.TOROLT = 'F'
|
|
AND ma.C_TANEVID = @pTanevId
|
|
AND ma.C_VEZETOIORASZAMOK in (6572,6573,6574,7755,7757,7758)
|
|
UNION
|
|
SELECT
|
|
f.C_NYOMTATASINEV AS Nev
|
|
,CASE @pNyomtatvanyNyelv
|
|
WHEN 2 THEN d.C_NAME_1
|
|
WHEN 1 THEN d.C_NAME_2
|
|
ELSE d.C_NAME
|
|
END AS VezetoiOraszamOk
|
|
,0 AS IsIntezmenyAdat
|
|
FROM T_TOVABBIMUNKAUGYIADATOK_OSSZES ma
|
|
INNER JOIN T_FELHASZNALO_OSSZES f on f.ID = ma.C_ALKALMAZOTTID
|
|
AND f.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.ID = ma.C_VEZETOIORASZAMOK
|
|
AND d.TOROLT = 'F'
|
|
AND d.C_TANEVID = ma.C_TANEVID
|
|
WHERE ma.TOROLT = 'F'
|
|
AND ma.C_TANEVID = @pTanevId
|
|
AND ma.C_VEZETOIORASZAMOK in (6572,6573,6574,7755,7757,7758)
|
|
UNION
|
|
SELECT
|
|
ia.C_IGAZGATONEVE AS Nev
|
|
,(SELECT
|
|
CASE @pNyomtatvanyNyelv
|
|
WHEN 2 THEN d.C_NAME_1
|
|
WHEN 1 THEN d.C_NAME_2
|
|
ELSE d.C_NAME
|
|
END
|
|
FROM T_DICTIONARYITEMBASE_OSSZES d
|
|
WHERE d.TOROLT = 'F'
|
|
AND d.C_TANEVID = @pTanevId
|
|
AND d.ID = 7755) AS VezetoiOraszamOk
|
|
,1 AS IsIntezmenyAdat
|
|
FROM T_INTEZMENYADATOK_OSSZES ia
|
|
WHERE ia.TOROLT = 'F'
|
|
AND ia.C_TANEVID = @pTanevId
|
|
) result
|
|
|
|
END |