UFN_RE7INTEGRATION_GETLASTCHANGEDDATE_SYNCDATA

create function dbo.UFN_RE7INTEGRATION_GETLASTCHANGEDDATE_SYNCDATA(@IDS xml)
returns @RETURN_INFO TABLE (RPID uniqueidentifier, 
                            RE7SYSTEMRECORDID int,
                            DATECHANGED datetime, 
                            LASTSYNCDATE datetime)
with execute as caller
as begin

  declare @DATETABLE table
  (
    ID int,
    DATECHANGED datetime
  );

  declare @IDTABLE table
  (
    REID int,
    RPID uniqueidentifier
  );

  insert into @IDTABLE
  select T.c.value('(@ID)[1]','integer'), cmap.ID
  from @IDS.nodes('/CONSTITUENTS/ITEM') T(c) 
  inner join dbo.RE7INTEGRATIONCONSTITUENTMAP cmap on T.c.value('(@ID)[1]','integer') = cmap.RE7RECORDID;

  insert into @DATETABLE
  select i.REID, MAX(pg.DATECHANGED)
  from dbo.WPPHILANTHROPICGIFT pg
  inner join @IDTABLE i on i.RPID = pg.WEALTHID
  group by i.REID;

  insert into @DATETABLE
  select i.REID, MAX(pd.DATECHANGED)
  from dbo.WPPOLITICALDONATION pd
  inner join @IDTABLE i on i.RPID = pd.WEALTHID
  group by i.REID;

  insert into @DATETABLE
  select i.REID, w.DATECHANGED
  from dbo.WEALTH w
  inner join @IDTABLE i on i.RPID = w.ID;

  insert into @DATETABLE
  select i.REID, wc.DATECHANGED
  from dbo.WEALTHCAPACITY wc
  inner join @IDTABLE i on i.RPID = wc.ID;

  insert into @DATETABLE
  select i.REID, DATECHANGED
  from dbo.MODELINGANDPROPENSITY map
  inner join @IDTABLE i on i.RPID = map.ID;

  insert into @DATETABLE
  select i.REID, MAX(sf.DATECHANGED)
  from dbo.RE7INTEGRATIONSELECTEDFIELD sf
  cross join @IDTABLE i
  group by i.REID;

    insert into @RETURN_INFO 
  select cmap.ID, dt.ID, MAX(dt.DATECHANGED), cmap.LASTSYNCDATE 
  from @DATETABLE dt 
  inner join dbo.RE7INTEGRATIONCONSTITUENTMAP cmap on dt.ID = cmap.RE7RECORDID 
  group by cmap.ID, dt.ID, cmap.LASTSYNCDATE;

  return;
end