UFN_SITEID_MAPFROM_STEPID

create function dbo.UFN_SITEID_MAPFROM_STEPID
(
  @STEPID uniqueidentifier
)
returns table as return

  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 dbo.UFN_INTERACTION_ISPLANSTEP(INTERACTION.ID) = 0
  left outer join
    dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = INTERACTION.FUNDINGREQUESTID
  left outer join
    dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
  where
    INTERACTION.ID = @STEPID

  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 = @STEPID;