USP_PROSPECTRESEARCHREQUEST_VALIDATESITES

      CREATE procedure dbo.USP_PROSPECTRESEARCHREQUEST_VALIDATESITES
      (
        @SITES xml,
        @CURRENTAPPUSERID uniqueidentifier,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @ISEDITFORM bit = 0,
        @REQUESTEDBYID uniqueidentifier = null,
        @SUBMITTEDBYID uniqueidentifier = null
      )
      as
      set nocount on;

      declare @SITES_T table
      (
        SITEID uniqueidentifier
      );

      if not @SITES is null
      begin
        insert into @SITES_T(SITEID)
        select SITEID
        from dbo.UFN_PROSPECTRESEARCHREQUEST_GETSITES_FROMITEMLISTXML(@SITES); 
      end

      declare @SITECOUNT int = 0;
      select @SITECOUNT = count(*) from @SITES_T;

      if @SITECOUNT = 0
      begin
        if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1
          raiserror('BBERR_SITEREQUIRED', 13, 1);
      end
      else
      begin
        if not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
        begin
          if @ISEDITFORM = 1 
          begin
            if not exists(select SITEID from @SITES_T [SITES] where dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, [SITES].SITEID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1)
              raiserror('BBER_SITE_NOACCESS', 13, 1);
          end
          else
          begin
            if exists(select SITEID from @SITES_T [SITES] where not dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, [SITES].SITEID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1)
              raiserror('BBER_SITE_NOACCESS', 13, 1);
          end
        end

        declare @APPUSERID uniqueidentifier = null;

        -- Get requesting fundraiser's app user ID

        if @REQUESTEDBYID is not null
        begin
          select @APPUSERID = ID
          from dbo.APPUSER
          where CONSTITUENTID = @REQUESTEDBYID;
        end

        -- Validate requesting fundraiser has access to at least one site provided

        if @APPUSERID is not null and not exists
        (
          select top 1 1
          from dbo.UFN_SITESFORUSER(@APPUSERID) SITEFORUSER
            inner join @SITES_T SITESPROVIDED on SITEFORUSER.SITEID = SITESPROVIDED.SITEID
        )
          raiserror('BBER_SITE_REQUESTEDUSER_NOACCESS', 13, 1);

        set @APPUSERID = null;

        -- Get submitting fundraiser's app user ID

        if @SUBMITTEDBYID is not null
        begin
          select @APPUSERID = ID
          from dbo.APPUSER
          where CONSTITUENTID = @SUBMITTEDBYID;
        end

        -- Validate submitting fundraiser has access to at least one site provided

        if @APPUSERID is not null and not exists
        (
          select top 1 1
          from dbo.UFN_SITESFORUSER(@APPUSERID) SITEFORUSER
            inner join @SITES_T SITESPROVIDED on SITEFORUSER.SITEID = SITESPROVIDED.SITEID
        )
          raiserror('BBER_SITE_SUBMITTEDUSER_NOACCESS', 13, 1);
      end