UFN_SITE_BUILDDATALISTSITEFILTER

create function BBDW.[UFN_SITE_BUILDDATALISTSITEFILTER]
    (
        @CURRENTAPPUSERID uniqueidentifier,
        @SITEFILTERMODE tinyint,
        @SITESSELECTED xml = null
    )

    returns @SITEFILTERTABLE table
  (
        [SITEID] uniqueidentifier
    )

    as begin

        if @SITEFILTERMODE = 1

          begin

              --If "My sites" was chosen, retrieve the app user's site.
              insert into @SITEFILTERTABLE([SITEID])
          select 
            a.[SITESYSTEMID]
          from BBDW.[DIM_APPUSER] a
          where a.[APPUSERSYSTEMID] =  @CURRENTAPPUSERID;

          end

        else

          begin

              if @SITEFILTERMODE = 2

                begin
                    --If "My site's branch" was chosen...
                    --Insert the app user's site and it's children
                        insert into @SITEFILTERTABLE(SITEID)
                      select 
                s.[SITESYSTEMID] as SITEID
                      from BBDW.[DIM_SITE] s
                      where s.[HIERARCHYPATH].IsDescendantOf(
                            (select 
                    ROOTSITE.HIERARCHYPATH 
                              from BBDW.[DIM_SITE] as [ROOTSITE]
                                inner join BBDW.[DIM_APPUSER] a on a.[SITESYSTEMID] = [ROOTSITE].[SITESYSTEMID]
                              where a.[APPUSERSYSTEMID] = @CURRENTAPPUSERID)
                          ) = 1;

                    --Insert the app user's site's ancestors
                    with ANCESTORS_CTE as
                    (
                        select 
                            [PARENT].[SITESYSTEMID] as [ID],
                            [PARENT].[HIERARCHYPATH]
                        from BBDW.[DIM_SITE] CHILD    
                            inner join BBDW.[DIM_SITE] as [PARENT] on [PARENT].[HIERARCHYPATH] = [CHILD].[HIERARCHYPATH].GetAncestor(1) 
                            inner join BBDW.[DIM_APPUSER] a on a.[SITESYSTEMID] = CHILD.[SITESYSTEMID]
                        where a.[APPUSERSYSTEMID] = @CURRENTAPPUSERID

                        union all

                        select 
                            [PARENT].[SITESYSTEMID],
                            [PARENT].[HIERARCHYPATH]
                        from [ANCESTORS_CTE] [SITE]
                            inner join BBDW.[DIM_SITE] as [PARENT] on [PARENT].[HIERARCHYPATH] = [SITE].[HIERARCHYPATH].GetAncestor(1)
                    )

                    insert into @SITEFILTERTABLE([SITEID])
                      select 
                [ID]
                      from [ANCESTORS_CTE];

                end

              else

                begin

                    if @SITEFILTERMODE = 3

                      begin

                      --If "Selected sites" was chosen, parse apart the given site xml.

                          insert into @SITEFILTERTABLE([SITEID])
                            select 
                                T.c.value('(SITEID)[1]','uniqueidentifier')
                            from @SITESSELECTED.nodes('/SITESSELECTED/ITEM') T(c) ;

                      end    

                end

        end

        return;

    end