USP_APPUSERSITEBRANCH_UPDATESITES

            CREATE procedure dbo.USP_APPUSERSITEBRANCH_UPDATESITES 
            (
                @APPUSERID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
                set nocount on;                

                if @CHANGEAGENTID is null begin
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
                end;

                declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO()
                set CONTEXT_INFO @CHANGEAGENTID;

                delete from dbo.APPUSERSITEBRANCH where APPUSERSITEBRANCH.APPUSERID = @APPUSERID or @APPUSERID is null;

                if @CONTEXTCACHE is not null begin
                    set CONTEXT_INFO @CONTEXTCACHE
                end;

                /*Walk down the branch for all selected sites*/
                with
                SITE_HIERACHY_CTE as
                (
                    select 
                        SITE.NAME, 
                        SITE.ID,
                        PARENTSITE.ID as PARENTID, 
                        SITE.HIERARCHYPATH 
                    from 
                        dbo.SITE with (nolock)
                        inner join dbo.SITE  as PARENTSITE with (nolock) on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1)
                ),    
                /*Walk down the hierarchy branch of the selected branch site*/    
                BRANCH_ASSIGNED_SITES_CTE as
                (
                    select                             
                        SITE.ID as SITEID,
                        1 as NODELOCATIONCODE,
                        APPUSER.ID as APPUSERID
                    from 
                        dbo.APPUSER with (nolock) 
                        inner join dbo.SITE with (nolock) on SITE.ID = APPUSER.SITEID                            
                    where
                        (APPUSER.ID = @APPUSERID or @APPUSERID is null) and
                        APPUSER.SITEID is not null
                union all
                    select 
                        SITE_HIERACHY_CTE.ID, 
                        0 as NODELOCATIONCODE,
                        BRANCH_ASSIGNED_SITES_CTE.APPUSERID
                    from 
                        BRANCH_ASSIGNED_SITES_CTE
                        inner join SITE_HIERACHY_CTE on SITE_HIERACHY_CTE.PARENTID = BRANCH_ASSIGNED_SITES_CTE.SITEID
                ),                        
                /*Walk up the hierarchy branch of the selected branch site*/
                BRANCH_CTE as 
                (
                    select 
                        PARENTSITE.ID as SITEID,
                        2 as NODELOCATIONCODE,
                        PARENTSITE.HIERARCHYPATH.GetAncestor(1) as PARENTHIERARCHYID,
                        APPUSER.ID as APPUSERID
                    from 
                        dbo.APPUSER with (nolock)  
                        inner join dbo.SITE with (nolock)  on SITE.ID = APPUSER.SITEID 
                        left outer join dbo.SITE as PARENTSITE with (nolock)  on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1) 
                    where
                        (APPUSER.ID = @APPUSERID or @APPUSERID is null) and
                        APPUSER.SITEID is not null and
                        SITE.HIERARCHYPATH.GetAncestor(1) <> hierarchyid::GetRoot()
                union all
                    select 
                        SITE.ID,
                        2 as NODELOCATIONCODE,
                        SITE.HIERARCHYPATH.GetAncestor(1),
                        BRANCH_CTE.APPUSERID
                    from
                        BRANCH_CTE
                        inner join dbo.SITE with (nolock)  on SITE.HIERARCHYPATH = BRANCH_CTE.PARENTHIERARCHYID
                    where
                        BRANCH_CTE.PARENTHIERARCHYID <> hierarchyid::GetRoot()
                ) 
                insert into APPUSERSITEBRANCH(APPUSERID, SITEID, NODELOCATIONCODE)                         
                    select distinct APPUSERID, SITEID, NODELOCATIONCODE from BRANCH_ASSIGNED_SITES_CTE        
                    union
                    select distinct APPUSERID, SITEID, NODELOCATIONCODE from BRANCH_CTE;