USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLEAPPUSER

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLEAPPUSER
          (
            @ID uniqueidentifier,
            @APPUSERID uniqueidentifier,
            @SECURITYMODECODE tinyint,
            @SITES xml, 
            @CHANGEAGENTID uniqueidentifier,
            @BRANCHSITEID uniqueidentifier,
            @CONSTITUENTSECURITYMODECODE tinyint,
            @CONSTITUENTSECURITYGROUPS xml
          )
          as
          begin
            set nocount on;

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

            declare @CURRENTDATE datetime;
            declare @ISPROXYUSER bit = 0;
            declare @PROXYUSERHASPERMISSION tinyint = 1;
            declare @SYSTEMROLEID uniqueidentifier = null;
            declare @PROXYCOUNT int = 0;
            declare @PROXYOWNERID uniqueidentifier = null;
            declare @ISPROXYOWNERSYSADMIN bit = 0;

            declare @ISAPPUSERSYSADMIN bit = 0;

            set @CURRENTDATE=getdate();

            if @SECURITYMODECODE <> 3 
              set @BRANCHSITEID = null

            begin try
              if @CONSTITUENTSECURITYMODECODE = 2 and @CONSTITUENTSECURITYGROUPS is null begin
                raiserror('At least one constituent security group must be selected.', 13, 1);
              end

              if @SECURITYMODECODE = 2 and @SITES is null begin
                raiserror('At least one site must be selected.', 13, 1);
              end

              select @SYSTEMROLEID = SYSTEMROLEID
              from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEAPPUSER.ID = @ID;

              select @ISPROXYUSER = isnull(dbo.UFN_APPUSER_ISPROXYUSER(@APPUSERID), 0);
              select @PROXYCOUNT = count(1) from dbo.APPUSER join dbo.SYSTEMROLEAPPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID where [PROXYOWNERID] = @APPUSERID and [SYSTEMROLEID] = @SYSTEMROLEID;
              select @ISAPPUSERSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID)

              select @PROXYOWNERID = PROXYOWNERID from dbo.APPUSER where ID = @APPUSERID;
              select @ISPROXYOWNERSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@PROXYOWNERID);

              if @ISPROXYUSER = 1
                begin

                    if @ISPROXYOWNERSYSADMIN = 0
                        begin
                            select @PROXYUSERHASPERMISSION = dbo.UFN_PROXYUSER_CHECKROLESANDPERMISSIONS(@APPUSERID,@SITES,@SYSTEMROLEID,
                                                        @SECURITYMODECODE,@BRANCHSITEID,@CONSTITUENTSECURITYMODECODE, @CONSTITUENTSECURITYGROUPS);
                        end
                    else
                        begin
                            set @PROXYUSERHASPERMISSION = 1;
                        end

                    if @PROXYUSERHASPERMISSION = 0
                        begin
                            raiserror('BBERR_PROXYUSER_PERMISSION', 13, 1);
                        end
                end

              exec dbo.USP_SYSTEMROLEAPPUSER_VALIDATESECURITYGROUPS @CONSTITUENTSECURITYGROUPS;

              update
                dbo.SYSTEMROLEAPPUSER
              set
                SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID,
                SYSTEMROLEAPPUSER.SECURITYMODECODE = @SECURITYMODECODE,
                SYSTEMROLEAPPUSER.DATECHANGED = @CURRENTDATE,
                SYSTEMROLEAPPUSER.CHANGEDBYID = @CHANGEAGENTID,
                SYSTEMROLEAPPUSER.BRANCHSITEID = @BRANCHSITEID,
                SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = @CONSTITUENTSECURITYMODECODE
              where
                SYSTEMROLEAPPUSER.ID = @ID;

              -- Clear the sites collection if 'All records' or 'Records with no site assigned' is set

              if @SECURITYMODECODE = 0 or @SECURITYMODECODE = 1
                set @SITES = null

              exec dbo.USP_SYSTEMROLEAPPUSER_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;

              -- Clear constituent security groups if 'All records' or 'Records with no security group assigned' is set

              if @CONSTITUENTSECURITYMODECODE = 0 or @CONSTITUENTSECURITYMODECODE = 1
                set @CONSTITUENTSECURITYGROUPS = null

              exec dbo.USP_SYSTEMROLEAPPUSER_GETSECURITYGROUPS_UPDATEFROMXML @ID, @CONSTITUENTSECURITYGROUPS, @CHANGEAGENTID, @CURRENTDATE;

              if @ISPROXYUSER = 0 and @PROXYCOUNT > 0 and @ISAPPUSERSYSADMIN = 0
               begin
                exec dbo.USP_APPUSER_WITHPROXY_UPDATEPROXYPERMISSIONS @APPUSERID, @SITES, @SYSTEMROLEID, @SECURITYMODECODE, @BRANCHSITEID, @CONSTITUENTSECURITYMODECODE, @CONSTITUENTSECURITYGROUPS, @PROXYCOUNT, @CHANGEAGENTID, @CURRENTDATE;
               end
            end try
            begin catch
              exec dbo.USP_RAISE_ERROR;
              return 1;
            end catch
          end