UFN_VENDOR_GETDEFAULTSITES

create function dbo.[UFN_VENDOR_GETDEFAULTSITES]
(  
  @APPUSERID uniqueidentifier,
  @DATAFORMTEMPLATECATALOGID uniqueidentifier
)   
returns xml
as  
  begin
      declare @XML xml;
      declare @SITEIDS table ([SITEID] uniqueidentifier null);

      set @XML = null;

      insert into @SITEIDS
        select distinct
          case [VIEW].[SITESECURITYMODE]
            when 2 then [SYSTEMROLEAPPUSERSITE].[SITEID]
            when 3 then [SYSTEMROLEAPPUSER].[BRANCHSITEID]
          end
        from dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM] as [VIEW]
        inner join dbo.[DATAFORMINSTANCECATALOG] on [DATAFORMINSTANCECATALOG].[ID] = [VIEW].[DATAFORMINSTANCECATALOGID]
        inner join dbo.[SYSTEMROLEAPPUSER] on ([SYSTEMROLEAPPUSER].[APPUSERID] = [VIEW].[APPUSERID] and [SYSTEMROLEAPPUSER].[SYSTEMROLEID] = [VIEW].[SYSTEMROLEID])
        left outer join dbo.[SYSTEMROLEAPPUSERSITE] on [SYSTEMROLEAPPUSER].[ID] = [SYSTEMROLEAPPUSERSITE].[SYSTEMROLEAPPUSERID]
        where [VIEW].[APPUSERID] = @APPUSERID
        and [DATAFORMINSTANCECATALOG].[DATAFORMTEMPLATECATALOGID] = @DATAFORMTEMPLATECATALOGID
        and dbo.[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@APPUSERID, [DATAFORMINSTANCECATALOG].[ID]) = 1
        and [VIEW].[GRANTORDENY] = 1;

      if not exists (select [SITEID] from @SITEIDS where [SITEID] is null)
        select @XML = (
          select
            null as [ID],
            [SITEID]
          from @SITEIDS
          for xml raw('ITEM'), type, elements, root('SITES'), binary base64);

     return @XML;    
  end