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

CREATE FUNCTION stage.fnGetNebulo (
  @lastLoad datetime
) RETURNS xml AS 
BEGIN
  DECLARE @xml xml
  SET @xml = (
    SELECT
       i.C_AZONOSITO AS intezmenyAzonosito
      ,s.C_TANEVNEV AS tanevNev
      ,s.C_NEBULOID AS nebuloId
      ,s.C_EDESANYJASZULETESICSALADINEV AS edesanyjaSzuletesiCsaladiNeve
      ,s.C_EDESANYJASZULETESIUTONEVE AS edesanyjaSzuletesiUtoneve
      ,s.C_ELOTAG AS elotag
      ,s.C_ETIKAHITERKOLCSTAN AS etikaHitErkolcstan
      ,s.C_FELVETELSTATUSZA AS felvetelStatusza
      ,s.C_NEME AS neme
      ,s.C_OKTATASIAZONOSITOSZAMA AS oktatasiAzonositoSzama
      ,s.C_SZULETESICSALADINEVE AS szuletesiCsaladiNeve
	  ,s.C_SZULETESIHELY AS szuletesiHely
	  ,s.C_SZULETESIIDO AS szuletesiIdo 
	  ,s.C_SZULETESIORSZAG AS szuletesiOrszag
	  ,s.C_SZULETESIUTONEVE AS szuletesiUtoneve
	  ,s.C_TAGOZAT AS tagozat
	  ,s.C_TANULOCSALADINEVE AS tanuloCsaladiNeve
	  ,s.C_TANULOUTONEVE AS tanuloUtoneve
	  ,s.C_AJTO AS ajto
	  ,s.C_EMELET AS emelet
	  ,s.C_HAZSZAM AS hazszam
	  ,s.C_HELYSEGNEV AS helysegNev
	  ,s.C_IRANYITOSZAM AS iranyitoszam
	  ,s.C_KOZTERULETJELLEGE AS kozteruletJellege
	  ,s.C_KOZTERULETNEV AS kozteruletnev
      ,s.C_ELETVITELSZERUOTTLAKASKORZET AS EletvitelszeruOttLakaskorzet
      ,s.C_FELADATELLATASIHELYID AS FeladatellatasiHelyId
	  ,s.C_BTMLETSZAMSULY AS BTMLetszamSuly
	  ,s.C_SNILETSZAMSULY AS SNILetszamSuly
      ,s.TOROLT AS torolt
    FROM stage.T_NEBULO_STAGE s
      INNER JOIN dbo.T_INTEZMENY i ON i.ID = s.C_INTEZMENYID
    WHERE s.LASTCHANGED > @lastLoad
    FOR XML RAW ('Nebulo'), ROOT('NebuloItems')
  );

  RETURN @xml

END;
GO
