UFN_SITEID_MAPFROM_INTERACTIONID

CREATE function dbo.UFN_SITEID_MAPFROM_INTERACTIONID
(
  @INTERACTIONID uniqueidentifier
)
returns table as return
    -- Note: The logic for looking up sites for interactions is also in ConstituentGroupRecentInteractionsSummary.DataList.xml 

    -- so any updates made here need to also be made there too

  select
    coalesce(PROSPECTPLANSITE.SITEID, FUNDINGPLAN.SITEID, INTERACTIONSITE.SITEID) SITEID
  from
    dbo.INTERACTION
  left outer join
    dbo.PROSPECTPLANSITE on INTERACTION.PROSPECTPLANID = PROSPECTPLANSITE.PROSPECTPLANID  
  left outer join
    dbo.INTERACTIONSITE on INTERACTIONSITE.INTERACTIONID = INTERACTION.ID and INTERACTION.PROSPECTPLANID is null
  left outer join
    dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = INTERACTION.FUNDINGREQUESTID
  left outer join
    dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
  where
    INTERACTION.ID = @INTERACTIONID

  union all

  select
    STEWARDSHIPPLANSITE.SITEID
  from
    dbo.STEWARDSHIPPLANSTEP
  inner join
    dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
  left join
    dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.STEWARDSHIPPLANID = STEWARDSHIPPLAN.ID
  where
    STEWARDSHIPPLANSTEP.ID = @INTERACTIONID;