USP_SITEPERMISSION_UPDATEPERMISSIONS

            CREATE procedure dbo.USP_SITEPERMISSION_UPDATEPERMISSIONS 
            as
                set nocount on;                

                /*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)
                ),    
                ASSIGNED_SITES_CTE as
                (
                    select 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        SITE.ID as SITEID,
                        SITE.NAME as SITENAME,
                        SITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID
                    from 
                        dbo.SYSTEMROLEAPPUSERSITE with (nolock)
                        inner join dbo.SITE with (nolock) on SITE.ID = SYSTEMROLEAPPUSERSITE.SITEID 
                        inner join dbo.SYSTEMROLEAPPUSER with (nolock) on SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID 
                        inner join dbo.SYSTEMROLE with (nolock) on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                    where
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 2 and
                        SYSTEMROLEAPPUSER.ID not in(select SYSTEMROLEAPPUSERID from dbo.SITEPERMISSION)
                union all
                    select 
                        ASSIGNED_SITES_CTE.SYSTEMROLEID, 
                        ASSIGNED_SITES_CTE.SYSTEMROLENAME,
                        SITE_HIERACHY_CTE.ID, 
                        SITE_HIERACHY_CTE.NAME,
                        SITE_HIERACHY_CTE.HIERARCHYPATH,
                        ASSIGNED_SITES_CTE.SYSTEMROLEAPPUSERID,
                        ASSIGNED_SITES_CTE.APPUSERID
                    from 
                        ASSIGNED_SITES_CTE 
                        inner join SITE_HIERACHY_CTE on SITE_HIERACHY_CTE.PARENTID = ASSIGNED_SITES_CTE.SITEID
                ),
                /*Walk down the hierarchy branch of the selected branch site*/    
                BRANCH_ASSIGNED_SITES_CTE as
                (
                    select 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        SITE.ID as SITEID,
                        SITE.NAME as SITENAME,
                        SITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID
                    from 
                        dbo.SYSTEMROLEAPPUSER with (nolock) 
                        inner join dbo.SITE with (nolock) on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID
                        inner join dbo.SYSTEMROLE with (nolock) on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                    where
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 3 and
                        SYSTEMROLEAPPUSER.ID not in(select SYSTEMROLEAPPUSERID from dbo.SITEPERMISSION)
                union all
                    select 
                        BRANCH_ASSIGNED_SITES_CTE.SYSTEMROLEID, 
                        BRANCH_ASSIGNED_SITES_CTE.SYSTEMROLENAME,
                        SITE_HIERACHY_CTE.ID, 
                        SITE_HIERACHY_CTE.NAME,
                        SITE_HIERACHY_CTE.HIERARCHYPATH,
                        BRANCH_ASSIGNED_SITES_CTE.SYSTEMROLEAPPUSERID,
                        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 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        PARENTSITE.ID as SITEID,
                        PARENTSITE.NAME as SITENAME,
                        PARENTSITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID,
                        PARENTSITE.HIERARCHYPATH.GetAncestor(1) as PARENTHIERARCHYID
                    from 
                        dbo.SYSTEMROLEAPPUSER with (nolock)  
                        inner join dbo.SITE with (nolock)  on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID 
                        inner join dbo.SYSTEMROLE with (nolock)  on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                        left outer join dbo.SITE as PARENTSITE with (nolock)  on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1) 
                    where
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 3 and
                        SITE.HIERARCHYPATH.GetAncestor(1) <> hierarchyid::GetRoot() and
                        SYSTEMROLEAPPUSER.ID not in(select SYSTEMROLEAPPUSERID from dbo.SITEPERMISSION)
                union all
                    select 
                        BRANCH_CTE.SYSTEMROLEID,
                        BRANCH_CTE.SYSTEMROLENAME,
                        SITE.ID,
                        SITE.NAME,
                        SITE.HIERARCHYPATH,
                        BRANCH_CTE.SYSTEMROLEAPPUSERID,
                        BRANCH_CTE.APPUSERID,
                        SITE.HIERARCHYPATH.GetAncestor(1)
                    from
                        BRANCH_CTE
                        inner join dbo.SITE with (nolock)  on SITE.HIERARCHYPATH = BRANCH_CTE.PARENTHIERARCHYID
                    where
                        BRANCH_CTE.PARENTHIERARCHYID <> hierarchyid::GetRoot()
                ) 
                insert into SITEPERMISSION(SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, SYSTEMROLEAPPUSERID, APPUSERID) 
                    select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, SYSTEMROLEAPPUSERID, APPUSERID from ASSIGNED_SITES_CTE
                    union
                    select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, SYSTEMROLEAPPUSERID, APPUSERID from BRANCH_ASSIGNED_SITES_CTE        
                    union
                    select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, SYSTEMROLEAPPUSERID, APPUSERID from BRANCH_CTE;