UFN_SITESFORUSERONFEATURE

CREATE function dbo.UFN_SITESFORUSERONFEATURE 
(
  @CURRENTAPPUSERID uniqueidentifier,
  @FEATUREID uniqueidentifier,
  @FEATURETYPE tinyint
)
returns @T table (SITEID uniqueidentifier)
as
begin 

  --If the user is a syadamin, or a feature is not defined, return all sites the user can access.

  if (select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID) = 1
    or @FEATUREID is null
    or @FEATURETYPE is null
    or @FEATURETYPE = 0 -- SecurityFeatureType.None

    or (select DFTC.NOSECURITYREQUIRED from dbo.DATAFORMINSTANCECATALOG DFIC inner join dbo.DATAFORMTEMPLATECATALOG DFTC on DFIC.DATAFORMTEMPLATECATALOGID = DFTC.ID where DFIC.ID = @FEATUREID) = 1
  begin
    insert into @T (SITEID)
    values(null);

    insert into @T (SITEID)
      select [SITEID]
      from dbo.[UFN_SITESFORUSER](@CURRENTAPPUSERID);

    return;
  end

  --Table to hold all relevant user/role/site relationships before 

  --  removing dupes or taking security mode into consideration.

  declare @RAWSITELIST table(
    SITESECURITYMODE tinyint,
    SITEID uniqueidentifier
  );

  if @FEATURETYPE = 1  -- SecurityFeatureType.Form

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.DATAFORMINSTANCECATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 2  -- SecurityFeatureType.DataList

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.DATALISTCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 3  -- SecurityFeatureType.RecordOperation

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_RECORDOPERATION as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.RECORDOPERATIONCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 4  -- SecurityFeatureType.BusinessProcess

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BUSINESSPROCESS as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BUSINESSPROCESSCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 5  -- SecurityFeatureType.Dashboard

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_DASHBOARD as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.DASHBOARDCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 6  -- SecurityFeatureType.SearchList

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SEARCHLIST as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.SEARCHLISTCATALOGID = @FEATUREID;
  end
  -- 7 = SecurityFeatureType.SimpleDataList

  if @FEATURETYPE = 8  -- SecurityFeatureType.Task

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.TASKID = @FEATUREID;
  end
  if @FEATURETYPE = 9  -- SecurityFeatureType.SmartQuery

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.SMARTQUERYCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 10  -- SecurityFeatureType.AdHocQueryView

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.QUERYVIEWCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 11  -- SecurityFeatureType.BatchType

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BATCHTYPECATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 12  -- SecurityFeatureType.AddCodeTableEntry

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_CODETABLE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.CODETABLECATALOGID = @FEATUREID and
      PERMISSIONS.ADDPERMISSIONCODE = 1;
  end
  if @FEATURETYPE = 13  -- SecurityFeatureType.UpdateCodeTableEntry

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_CODETABLE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.CODETABLECATALOGID = @FEATUREID and
      PERMISSIONS.UPDATEPERMISSIONCODE = 1;
  end
  if @FEATURETYPE = 14  -- SecurityFeatureType.DeleteCodeTableEntry

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_CODETABLE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.CODETABLECATALOGID = @FEATUREID and
      PERMISSIONS.DELETEPERMISSIONCODE = 1;
  end
  if @FEATURETYPE = 15  -- SecurityFeatureType.Batch

  begin
    --A user has rights to a batch if granted rights to either its workflow, type, or template.

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE as PERMISSIONS
                  inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = PERMISSIONS.BATCHWORKFLOWSTATEID
      inner join dbo.BATCH on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      BATCH.ID = @FEATUREID;

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as PERMISSIONS
      inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = PERMISSIONS.BATCHTYPECATALOGID
      inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      BATCH.ID = @FEATUREID;

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as PERMISSIONS
      inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = PERMISSIONS.BATCHTEMPLATEID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      BATCH.ID = @FEATUREID;
  end
  if @FEATURETYPE = 16  -- SecurityFeatureType.BatchTemplate

  begin
    --A user has rights to a batch template if granted rights to either its template or batch type.

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BATCHTEMPLATEID = @FEATUREID;

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE,
      SITEPERMISSION.SITEID
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as PERMISSIONS inner join
      dbo.BATCHTEMPLATE on PERMISSIONS.BATCHTYPECATALOGID = BATCHTEMPLATE.BATCHTYPECATALOGID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      BATCHTEMPLATE.ID = @FEATUREID and
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID;
  end
  if @FEATURETYPE = 17  -- SecurityFeatureType.Kpi

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_KPI as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.KPICATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 18  -- SecurityFeatureType.MergeTask

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      (PERMISSIONS.DATAFORMINSTANCECATALOGID = 'a83074f2-c238-432e-979f-1a5496f1023f'
        or PERMISSIONS.DATAFORMINSTANCECATALOGID = '732ee77a-96ec-48cd-8b36-f7e7e5225a40');
  end
  if @FEATURETYPE = 19  -- SecurityFeatureType.SmartField

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
              select
          0,
          SITEPERMISSION.SITEID
          from
          SITEPERMISSION 
          where SITEPERMISSION.APPUSERID = @CURRENTAPPUSERID                   
    --select 

    --  PERMISSIONS.SITESECURITYMODE, 

    --  SITEPERMISSION.SITEID 

    --from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS

    --left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID

    --where 

    --  PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 

    --  (PERMISSIONS.DATAFORMINSTANCECATALOGID = '1441C214-BF50-4701-B4B8-5FD3EBF41577'

    --    or PERMISSIONS.DATAFORMINSTANCECATALOGID = '57C77816-505D-4010-A8C7-FF21B3759916');-->

  end
  if @FEATURETYPE = 20  -- SecurityFeatureType.GlobalChange

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      (PERMISSIONS.DATAFORMINSTANCECATALOGID = '6976F8E7-46F8-4397-BCF7-60BA896DBFFB'
        or PERMISSIONS.DATAFORMINSTANCECATALOGID = '800093A6-B727-490B-8CC4-C0C0CF2148F0');
  end
  if @FEATURETYPE = 21  -- SecurityFeatureType.ReportParameter

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_REPORT as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.REPORTCATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 22  -- SecurityFeatureType.SystemPrivilege

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.SYSTEMPRIVILEGECATALOGID = @FEATUREID;
  end
  if @FEATURETYPE = 23  -- SecurityFeatureType.ConfigurationData

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      (PERMISSIONS.DATAFORMINSTANCECATALOGID = 'B3CE5EFC-4E28-428D-BBBC-40E8BAF1A21C'
        or PERMISSIONS.DATAFORMINSTANCECATALOGID = '3C132EA3-EF68-4CA5-A10C-9CC392562254');
  end
  if @FEATURETYPE = 24  -- SecurityFeatureType.BatchTemplateCustomize

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATECUSTOMIZE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BATCHTEMPLATEID = @FEATUREID;
  end
  if @FEATURETYPE = 25  -- SecurityFeatureType.BatchProcessor

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHPROCESSOR as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BATCHTEMPLATEID = @FEATUREID;

    --WI150493: Needs to also check the security for batch workflow, batch template, and batch type.  The reason is a user can have access to add a batch

    -- but they may not have BatchProcessor rights.  So, in that case they should still be able to see the sites on that form.  This logic reflects the logic in

    -- UFN_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR.

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE as PERMISSIONS
      inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = PERMISSIONS.BATCHWORKFLOWSTATEID
      inner join dbo.BATCH on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
      inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      BATCHTEMPLATE.ID = @FEATUREID;

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.BATCHTEMPLATEID = @FEATUREID;

    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE,
      SITEPERMISSION.SITEID
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as PERMISSIONS inner join
      dbo.BATCHTEMPLATE on PERMISSIONS.BATCHTYPECATALOGID = BATCHTEMPLATE.BATCHTYPECATALOGID
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      BATCHTEMPLATE.ID = @FEATUREID and
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID;

  end
  if @FEATURETYPE = 26  -- SecurityFeatureType.Page

  begin
    declare @PAGEEXPRESSIONFORMID uniqueidentifier;
    select @PAGEEXPRESSIONFORMID=
      coalesce(
          PAGEDEFINITIONSPEC.value(
            '
            declare namespace bbspec="bb_appfx_pagedefinition";
            declare namespace c="bb_appfx_commontypes";
            /bbspec:PageDefinitionSpec[1]/@ExpressionDataFormID
            '
            ,'nvarchar(36)')
          ,'00000000-0000-0000-0000-000000000000')
    from dbo.PAGEDEFINITIONCATALOG where ID = @FEATUREID;


    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.DATAFORMINSTANCECATALOGID = @PAGEEXPRESSIONFORMID;
  end
  if @FEATURETYPE = 27  -- SecurityFeatureType.MapEntity

  begin
    insert into @RAWSITELIST(
      SITESECURITYMODE,
      SITEID
    )
    select 
      PERMISSIONS.SITESECURITYMODE, 
      SITEPERMISSION.SITEID 
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_MAPENTITY as PERMISSIONS
      left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
    where 
      PERMISSIONS.APPUSERID = @CURRENTAPPUSERID and 
      PERMISSIONS.MAPENTITYCATALOGID = @FEATUREID;
  end

  --If the raw lists contains a role assignment that allows access to all records, return all sites.

  if exists( select 1 from @RAWSITELIST where SITESECURITYMODE=0)
  begin
    insert into @T (SITEID)
    values(null);

    insert into @T (SITEID)
      select [SITEID]
      from dbo.[UFN_SITESFORUSER](@CURRENTAPPUSERID);
  end
  else --Otherwise, return the listed sites (null for role assignments for records with no sites).

  begin
    insert into @T (SITEID)
    select distinct case SITESECURITYMODE when 1 then null else SITEID end from @RAWSITELIST;
  end

  return;
end