﻿IF OBJECT_ID('stage.fnGetTanarAdatok') IS NOT NULL BEGIN
  DROP FUNCTION stage.fnGetTanarAdatok 
END
GO

CREATE FUNCTION stage.fnGetTanarAdatok (
  @lastLoad datetime
) RETURNS xml AS 
BEGIN
  DECLARE @xml xml
  SET @xml = (
    SELECT
      i.C_AZONOSITO AS intezmenyAzonosito,
      s.C_TANEVNEV AS tanevNev,  
      s.C_TANARID AS tanarId,
      s.C_TANARNEV AS tanarNev,
      s.C_TANARSZULETESIHELY AS tanarSzuletesiHely,
      s.C_TANARSZULETESIDATUM AS tanarSzuletesiDatum,
      s.C_TANARSZULETESINEV AS tanarSzuletesiNev,
      s.C_TANARANYJANEVE AS tanarAnyjaNeve,
      s.C_TANAROMKOD AS tanarOMKod,
      s.C_KOTELEZOORASZAM AS kotelezoOraszam,
      s.C_TANARNEME AS tanarNeme,
      s.C_MUNKAKORTIPUSA AS munkakorTipusa,
      s.C_MUNKAVISZONYTIPUSA AS munkaviszonyTipusa,
      s.C_BESOROLASIFOKOZAT AS besorolasiFokozat,
      s.C_BETOLTETLENALLASHELY AS betoltetlenAllashely,
      s.C_MUNKAIDOKEDVEZMENYORASZAM AS munkaidoKedvezmenyOraszam,
      s.C_NYUGDIJAS AS nyugdijas,
      s.C_TARTOSHELYETTESITES AS tartosHelyettesites,
      s.C_PEDAGOGUSSTATUSZA AS pedagogusStatusza,
      s.C_FELADATELLATASIHELYID AS feladatellatasiHelyId,
      s.C_TANARFOKOZAT AS tanarFokozat,
      s.C_ADOSZAM AS adoszam, 
      s.C_MUNKAIDOKEDVEZMENYOKA AS munkaidoKedvezmenyOka,
      s.C_SZAKERTOIVIZSGAELNOKI AS szakertoiVizsgaelnoki,
      s.C_SZAKVIZSGA AS szakvizsga,
      s.C_TOVABBKEPZES AS tovabbkepzes,
      s.C_RESZMUNKAIDOSZAZALEK AS reszmunkaidoSzazalek,
      s.C_FOGLALKOZTATASTIPUS AS foglalkoztatasTipus,
      s.C_VEZETOIORASZAMOK AS vezetoiOraszamOk,
      s.TOROLT AS torolt
    FROM stage.T_TANARADATOK_STAGE s
      INNER JOIN dbo.T_INTEZMENY i ON i.ID = s.C_INTEZMENYID
    WHERE s.LASTCHANGED > @lastLoad
    FOR XML RAW ('TanarAdatok'), ROOT('TanarAdatokItems')
  );
  RETURN @xml
  
END;
GO
