181 lines
11 KiB
Transact-SQL
181 lines
11 KiB
Transact-SQL
IF OBJECT_ID('stage.sp_MrgTanarAdatok') IS NOT NULL BEGIN
|
|
DROP PROCEDURE stage.sp_MrgTanarAdatok
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE stage.sp_MrgTanarAdatok
|
|
AS
|
|
BEGIN
|
|
MERGE stage.T_TANARADATOK_STAGE AS trg
|
|
USING (
|
|
SELECT
|
|
i.ID AS C_INTEZMENYID,
|
|
t.C_NEV AS C_TANEVNEV,
|
|
f.ID AS C_TANARID,
|
|
f.C_NYOMTATASINEV AS C_TANARNEV,
|
|
f.C_SZULETESIHELY AS C_TANARSZULETESIHELY,
|
|
f.C_SZULETESIDATUM AS C_TANARSZULETESIDATUM,
|
|
f.C_SZULETESINEV AS C_TANARSZULETESINEV,
|
|
f.C_ANYJANEVE AS C_TANARANYJANEVE,
|
|
m.C_KOTELEZOORASZAM AS C_KOTELEZOORASZAM,
|
|
f.C_NEME AS C_TANARNEME,
|
|
m.C_MUNKAKORTIPUSA AS C_MUNKAKORTIPUSA,
|
|
m.C_MUNKAVISZONYTIPUSA AS C_MUNKAVISZONYTIPUSA,
|
|
m.C_BESOROLASIFOKOZAT AS C_BESOROLASIFOKOZAT,
|
|
m.C_BETOLTETLENALLASHELY AS C_BETOLTETLENALLASHELY,
|
|
m.C_MUNKAIDOKEDVEZMENYORASZAM AS C_MUNKAIDOKEDVEZMENYORASZAM,
|
|
m.C_NYUGDIJAS AS C_NYUGDIJAS,
|
|
m.C_TARTOSHELYETTESITES AS C_TARTOSHELYETTESITES,
|
|
m.C_PEDAGOGUSSTATUSZA AS C_PEDAGOGUSSTATUSZA,
|
|
f.C_OKTATASIAZONOSITO AS C_TANAROMKOD,
|
|
m.C_FELADATELLATASIHELYID AS C_FELADATELLATASIHELYID,
|
|
(SELECT TOP(1) p.C_FOKOZAT FROM T_PEDAGOGUSELETPALYAMODELL p WHERE p.C_TANARID = f.ID ORDER BY p.C_MINOSITESDATUM DESC) AS C_TANARFOKOZAT,
|
|
f.C_ADOSZAM AS C_ADOSZAM,
|
|
m.C_MUNKAIDOKEDVEZMENYOKA AS C_MUNKAIDOKEDVEZMENYOKA,
|
|
a.C_SZAKERTOIVIZSGAELNOKI AS C_SZAKERTOIVIZSGAELNOKI,
|
|
a.C_SZAKVIZSGA AS C_SZAKVIZSGA,
|
|
a.C_TOVABBKEPZES AS C_TOVABBKEPZES,
|
|
m.C_RESZMUNKAIDOSZAZALEK AS C_RESZMUNKAIDOSZAZALEK,
|
|
m.C_FOGLALKOZTATASTIPUS AS C_FOGLALKOZTATASTIPUS,
|
|
m.C_VEZETOIORASZAMOK AS C_VEZETOIORASZAMOK,
|
|
f.TOROLT AS TOROLT
|
|
FROM T_FELHASZNALO f
|
|
INNER JOIN T_ALKALMAZOTT a ON a.ID = f.ID
|
|
INNER JOIN T_INTEZMENY i ON i.ID = f.C_INTEZMENYID
|
|
INNER JOIN T_TANEV t ON t.ID = f.C_TANEVID
|
|
INNER JOIN T_MUNKAUGYIADATOK m ON m.C_ALKALMAZOTTID = a.ID
|
|
WHERE f.TOROLT = 'F' AND m.TOROLT = 'F' AND t.TOROLT = 'F' AND i.TOROLT = 'F' AND a.TOROLT = 'F'
|
|
) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_TANARID = src.C_TANARID)
|
|
WHEN MATCHED AND
|
|
(trg.C_TANARNEV <> src.C_TANARNEV OR (trg.C_TANARNEV IS NULL AND src.C_TANARNEV IS NOT NULL) OR (trg.C_TANARNEV IS NOT NULL AND src.C_TANARNEV IS NULL))
|
|
OR (trg.C_TANARSZULETESIHELY <> src.C_TANARSZULETESIHELY OR (trg.C_TANARSZULETESIHELY IS NULL AND src.C_TANARSZULETESIHELY IS NOT NULL) OR (trg.C_TANARSZULETESIHELY IS NOT NULL AND src.C_TANARSZULETESIHELY IS NULL))
|
|
OR (trg.C_TANARSZULETESIDATUM <> src.C_TANARSZULETESIDATUM OR (trg.C_TANARSZULETESIDATUM IS NULL AND src.C_TANARSZULETESIDATUM IS NOT NULL) OR (trg.C_TANARSZULETESIDATUM IS NOT NULL AND src.C_TANARSZULETESIDATUM IS NULL))
|
|
OR (trg.C_TANARANYJANEVE <> src.C_TANARANYJANEVE OR (trg.C_TANARANYJANEVE IS NULL AND src.C_TANARANYJANEVE IS NOT NULL) OR (trg.C_TANARANYJANEVE IS NOT NULL AND src.C_TANARANYJANEVE IS NULL))
|
|
OR (trg.C_TANARSZULETESINEV <> src.C_TANARSZULETESINEV OR (trg.C_TANARSZULETESINEV IS NULL AND src.C_TANARSZULETESINEV IS NOT NULL) OR (trg.C_TANARSZULETESINEV IS NOT NULL AND src.C_TANARSZULETESINEV IS NULL))
|
|
OR (trg.C_KOTELEZOORASZAM <> src.C_KOTELEZOORASZAM OR (trg.C_KOTELEZOORASZAM IS NULL AND src.C_KOTELEZOORASZAM IS NOT NULL) OR (trg.C_KOTELEZOORASZAM IS NOT NULL AND src.C_KOTELEZOORASZAM IS NULL))
|
|
OR (trg.C_TANARNEME <> src.C_TANARNEME OR (trg.C_TANARNEME IS NULL AND src.C_TANARNEME IS NOT NULL) OR (trg.C_TANARNEME IS NOT NULL AND src.C_TANARNEME IS NULL))
|
|
OR (trg.C_MUNKAKORTIPUSA <> src.C_MUNKAKORTIPUSA OR (trg.C_MUNKAKORTIPUSA IS NULL AND src.C_MUNKAKORTIPUSA IS NOT NULL) OR (trg.C_MUNKAKORTIPUSA IS NOT NULL AND src.C_MUNKAKORTIPUSA IS NULL))
|
|
OR (trg.C_MUNKAVISZONYTIPUSA <> src.C_MUNKAVISZONYTIPUSA OR (trg.C_MUNKAVISZONYTIPUSA IS NULL AND src.C_MUNKAVISZONYTIPUSA IS NOT NULL) OR (trg.C_MUNKAVISZONYTIPUSA IS NOT NULL AND src.C_MUNKAVISZONYTIPUSA IS NULL))
|
|
OR (trg.C_BESOROLASIFOKOZAT <> src.C_BESOROLASIFOKOZAT OR (trg.C_BESOROLASIFOKOZAT IS NULL AND src.C_BESOROLASIFOKOZAT IS NOT NULL) OR (trg.C_BESOROLASIFOKOZAT IS NOT NULL AND src.C_BESOROLASIFOKOZAT IS NULL))
|
|
OR (trg.C_BETOLTETLENALLASHELY <> src.C_BETOLTETLENALLASHELY OR (trg.C_BETOLTETLENALLASHELY IS NULL AND src.C_BETOLTETLENALLASHELY IS NOT NULL) OR (trg.C_BETOLTETLENALLASHELY IS NOT NULL AND src.C_BETOLTETLENALLASHELY IS NULL))
|
|
OR (trg.C_TANAROMKOD <> src.C_TANAROMKOD OR (trg.C_TANAROMKOD IS NULL AND src.C_TANAROMKOD IS NOT NULL) OR (trg.C_TANAROMKOD IS NOT NULL AND src.C_TANAROMKOD IS NULL))
|
|
OR (trg.C_MUNKAIDOKEDVEZMENYORASZAM <> src.C_MUNKAIDOKEDVEZMENYORASZAM OR (trg.C_MUNKAIDOKEDVEZMENYORASZAM IS NULL AND src.C_MUNKAIDOKEDVEZMENYORASZAM IS NOT NULL) OR (trg.C_MUNKAIDOKEDVEZMENYORASZAM IS NOT NULL AND src.C_MUNKAIDOKEDVEZMENYORASZAM IS NULL))
|
|
OR (trg.C_NYUGDIJAS <> src.C_NYUGDIJAS OR (trg.C_NYUGDIJAS IS NULL AND src.C_NYUGDIJAS IS NOT NULL) OR (trg.C_NYUGDIJAS IS NOT NULL AND src.C_NYUGDIJAS IS NULL))
|
|
OR (trg.C_TARTOSHELYETTESITES <> src.C_TARTOSHELYETTESITES OR (trg.C_TARTOSHELYETTESITES IS NULL AND src.C_TARTOSHELYETTESITES IS NOT NULL) OR (trg.C_TARTOSHELYETTESITES IS NOT NULL AND src.C_TARTOSHELYETTESITES IS NULL))
|
|
OR (trg.C_PEDAGOGUSSTATUSZA <> src.C_PEDAGOGUSSTATUSZA OR (trg.C_PEDAGOGUSSTATUSZA IS NULL AND src.C_PEDAGOGUSSTATUSZA IS NOT NULL) OR (trg.C_PEDAGOGUSSTATUSZA IS NOT NULL AND src.C_PEDAGOGUSSTATUSZA IS NULL))
|
|
OR (trg.C_FELADATELLATASIHELYID <> src.C_FELADATELLATASIHELYID OR (trg.C_FELADATELLATASIHELYID IS NULL AND src.C_FELADATELLATASIHELYID IS NOT NULL) OR (trg.C_FELADATELLATASIHELYID IS NOT NULL AND src.C_FELADATELLATASIHELYID IS NULL))
|
|
OR (trg.C_TANARFOKOZAT <> src.C_TANARFOKOZAT OR (trg.C_TANARFOKOZAT IS NULL AND src.C_TANARFOKOZAT IS NOT NULL) OR (trg.C_TANARFOKOZAT IS NOT NULL AND src.C_TANARFOKOZAT IS NULL))
|
|
OR (trg.C_ADOSZAM <> src.C_ADOSZAM OR (trg.C_ADOSZAM IS NULL AND src.C_ADOSZAM IS NOT NULL) OR (trg.C_ADOSZAM IS NOT NULL AND src.C_ADOSZAM IS NULL))
|
|
OR (trg.C_MUNKAIDOKEDVEZMENYOKA <> src.C_MUNKAIDOKEDVEZMENYOKA OR (trg.C_MUNKAIDOKEDVEZMENYOKA IS NULL AND src.C_MUNKAIDOKEDVEZMENYOKA IS NOT NULL) OR (trg.C_MUNKAIDOKEDVEZMENYOKA IS NOT NULL AND src.C_MUNKAIDOKEDVEZMENYOKA IS NULL))
|
|
OR (trg.C_SZAKERTOIVIZSGAELNOKI <> src.C_SZAKERTOIVIZSGAELNOKI OR (trg.C_SZAKERTOIVIZSGAELNOKI IS NULL AND src.C_SZAKERTOIVIZSGAELNOKI IS NOT NULL) OR (trg.C_SZAKERTOIVIZSGAELNOKI IS NOT NULL AND src.C_SZAKERTOIVIZSGAELNOKI IS NULL))
|
|
OR (trg.C_SZAKVIZSGA <> src.C_SZAKVIZSGA OR (trg.C_SZAKVIZSGA IS NULL AND src.C_SZAKVIZSGA IS NOT NULL) OR (trg.C_SZAKVIZSGA IS NOT NULL AND src.C_SZAKVIZSGA IS NULL))
|
|
OR (trg.C_TOVABBKEPZES <> src.C_TOVABBKEPZES OR (trg.C_TOVABBKEPZES IS NULL AND src.C_TOVABBKEPZES IS NOT NULL) OR (trg.C_TOVABBKEPZES IS NOT NULL AND src.C_TOVABBKEPZES IS NULL))
|
|
OR (trg.C_RESZMUNKAIDOSZAZALEK <> src.C_RESZMUNKAIDOSZAZALEK OR (trg.C_RESZMUNKAIDOSZAZALEK IS NULL AND src.C_RESZMUNKAIDOSZAZALEK IS NOT NULL) OR (trg.C_RESZMUNKAIDOSZAZALEK IS NOT NULL AND src.C_RESZMUNKAIDOSZAZALEK IS NULL))
|
|
OR (trg.C_FOGLALKOZTATASTIPUS <> src.C_FOGLALKOZTATASTIPUS OR (trg.C_FOGLALKOZTATASTIPUS IS NULL AND src.C_FOGLALKOZTATASTIPUS IS NOT NULL) OR (trg.C_FOGLALKOZTATASTIPUS IS NOT NULL AND src.C_FOGLALKOZTATASTIPUS IS NULL))
|
|
OR (trg.C_VEZETOIORASZAMOK <> src.C_VEZETOIORASZAMOK OR (trg.C_VEZETOIORASZAMOK IS NULL AND src.C_VEZETOIORASZAMOK IS NOT NULL) OR (trg.C_VEZETOIORASZAMOK IS NOT NULL AND src.C_VEZETOIORASZAMOK IS NULL))
|
|
THEN
|
|
UPDATE SET
|
|
trg.C_TANARNEV = src.C_TANARNEV
|
|
,trg.C_TANARSZULETESIHELY = src.C_TANARSZULETESIHELY
|
|
,trg.C_TANARSZULETESIDATUM = src.C_TANARSZULETESIDATUM
|
|
,trg.C_TANARSZULETESINEV = src.C_TANARSZULETESINEV
|
|
,trg.C_TANARANYJANEVE = src.C_TANARANYJANEVE
|
|
,trg.C_KOTELEZOORASZAM = src.C_KOTELEZOORASZAM
|
|
,trg.C_TANARNEME = src.C_TANARNEME
|
|
,trg.C_MUNKAKORTIPUSA = src.C_MUNKAKORTIPUSA
|
|
,trg.C_MUNKAVISZONYTIPUSA = src.C_MUNKAVISZONYTIPUSA
|
|
,trg.C_BESOROLASIFOKOZAT = src.C_BESOROLASIFOKOZAT
|
|
,trg.C_BETOLTETLENALLASHELY = src.C_BETOLTETLENALLASHELY
|
|
,trg.C_TANAROMKOD = src.C_TANAROMKOD
|
|
,trg.C_MUNKAIDOKEDVEZMENYORASZAM = src.C_MUNKAIDOKEDVEZMENYORASZAM
|
|
,trg.C_NYUGDIJAS = src.C_NYUGDIJAS
|
|
,trg.C_TARTOSHELYETTESITES = src.C_TARTOSHELYETTESITES
|
|
,trg.C_PEDAGOGUSSTATUSZA = src.C_PEDAGOGUSSTATUSZA
|
|
,trg.C_FELADATELLATASIHELYID = src.C_FELADATELLATASIHELYID
|
|
,trg.C_TANARFOKOZAT = src.C_TANARFOKOZAT
|
|
,trg.C_ADOSZAM = src.C_ADOSZAM
|
|
,trg.C_MUNKAIDOKEDVEZMENYOKA = src.C_MUNKAIDOKEDVEZMENYOKA
|
|
,trg.C_SZAKERTOIVIZSGAELNOKI = src.C_SZAKERTOIVIZSGAELNOKI
|
|
,trg.C_SZAKVIZSGA = src.C_SZAKVIZSGA
|
|
,trg.C_TOVABBKEPZES = src.C_TOVABBKEPZES
|
|
,trg.C_RESZMUNKAIDOSZAZALEK = src.C_RESZMUNKAIDOSZAZALEK
|
|
,trg.C_FOGLALKOZTATASTIPUS = src.C_FOGLALKOZTATASTIPUS
|
|
,trg.C_VEZETOIORASZAMOK = src.C_VEZETOIORASZAMOK
|
|
,trg.TOROLT = src.TOROLT
|
|
,trg.LASTCHANGED = GETDATE()
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (
|
|
C_INTEZMENYID,
|
|
C_TANEVNEV,
|
|
C_TANARID,
|
|
C_TANARNEV,
|
|
C_TANARSZULETESIHELY,
|
|
C_TANARSZULETESIDATUM,
|
|
C_TANARSZULETESINEV,
|
|
C_TANARANYJANEVE,
|
|
C_TANAROMKOD,
|
|
C_KOTELEZOORASZAM,
|
|
C_TANARNEME,
|
|
C_MUNKAKORTIPUSA,
|
|
C_MUNKAVISZONYTIPUSA,
|
|
C_BESOROLASIFOKOZAT,
|
|
C_BETOLTETLENALLASHELY,
|
|
C_MUNKAIDOKEDVEZMENYORASZAM,
|
|
C_NYUGDIJAS,
|
|
C_TARTOSHELYETTESITES,
|
|
C_PEDAGOGUSSTATUSZA,
|
|
C_FELADATELLATASIHELYID,
|
|
C_TANARFOKOZAT,
|
|
C_ADOSZAM,
|
|
C_MUNKAIDOKEDVEZMENYOKA,
|
|
C_SZAKERTOIVIZSGAELNOKI,
|
|
C_SZAKVIZSGA,
|
|
C_TOVABBKEPZES,
|
|
C_RESZMUNKAIDOSZAZALEK,
|
|
C_FOGLALKOZTATASTIPUS,
|
|
C_VEZETOIORASZAMOK,
|
|
TOROLT,
|
|
LASTCHANGED,
|
|
CREATED
|
|
) VALUES (
|
|
src.C_INTEZMENYID,
|
|
src.C_TANEVNEV,
|
|
src.C_TANARID,
|
|
src.C_TANARNEV,
|
|
src.C_TANARSZULETESIHELY,
|
|
src.C_TANARSZULETESIDATUM,
|
|
src.C_TANARSZULETESINEV,
|
|
src.C_TANARANYJANEVE,
|
|
src.C_TANAROMKOD,
|
|
src.C_KOTELEZOORASZAM,
|
|
src.C_TANARNEME,
|
|
src.C_MUNKAKORTIPUSA,
|
|
src.C_MUNKAVISZONYTIPUSA,
|
|
src.C_BESOROLASIFOKOZAT,
|
|
src.C_BETOLTETLENALLASHELY,
|
|
src.C_MUNKAIDOKEDVEZMENYORASZAM,
|
|
src.C_NYUGDIJAS,
|
|
src.C_TARTOSHELYETTESITES,
|
|
src.C_PEDAGOGUSSTATUSZA,
|
|
src.C_FELADATELLATASIHELYID,
|
|
src.C_TANARFOKOZAT,
|
|
src.C_ADOSZAM,
|
|
src.C_MUNKAIDOKEDVEZMENYOKA,
|
|
src.C_SZAKERTOIVIZSGAELNOKI,
|
|
src.C_SZAKVIZSGA,
|
|
src.C_TOVABBKEPZES,
|
|
src.C_RESZMUNKAIDOSZAZALEK,
|
|
src.C_FOGLALKOZTATASTIPUS,
|
|
src.C_VEZETOIORASZAMOK,
|
|
'F',
|
|
GETDATE(),
|
|
GETDATE()
|
|
)
|
|
WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
|
|
UPDATE SET
|
|
trg.LASTCHANGED = GETDATE(),
|
|
trg.TOROLT = 'T'
|
|
;
|
|
END
|
|
GO
|