USP_BATCHTEMPLATE_GETDATALIST

create procedure dbo.[USP_BATCHTEMPLATE_GETDATALIST]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @CATEGORY nvarchar(100) = null,
  @INCLUDEINACTIVE bit = 0,
  @SECURITYFEATUREID uniqueidentifier,
  @SECURITYFEATURETYPE tinyint,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
as begin
  set nocount on;

  declare @ISSYSADMIN bit;
  declare @ISSITESECURITY bit;

  set @ISSYSADMIN = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);
  set @ISSITESECURITY = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('133f9bca-00f1-4007-9792-586b931340c6');

  if @CATEGORY = '' set @CATEGORY = null;

  with [INUSE] ([BATCHTEMPLATEID], [INUSE]) as (
    select distinct [BATCHTEMPLATEID], cast(1 as bit) as [INUSE]
    from dbo.[BATCH]
    where [STATUSCODE] <> 2
  )
  select
    [BATCHTEMPLATE].[ID],
    [BATCHTEMPLATE].[NAME],
    [BATCHTEMPLATE].[DESCRIPTION],
    [BATCHTYPECATALOG].[CATEGORY],
    [BATCHTYPECATALOG].[NAME] as [BATCHTYPE],
    [BATCHTEMPLATE].[ACTIVE],
    [BATCHTYPECATALOG].[SPECXML],
    isnull([SITE].[NAME], '') as [SITE],
    [BATCHWORKFLOW].[NAME] as [WORKFLOW],
    [BATCHNUMBERINGSCHEME].[NAME] as [NUMBERINGSCHEME],
    [BATCHTEMPLATE].[TEMPLATEUSECODE],
    isnull([INUSE].[INUSE], 0) as [INUSE],
    [BATCHTYPECATALOG].[ALLOWIMPORT]
  from dbo.[BATCHTEMPLATE]
  inner join dbo.[BATCHTYPECATALOG] on [BATCHTYPECATALOG].[ID] = [BATCHTEMPLATE].[BATCHTYPECATALOGID]
  left outer join dbo.[BATCHWORKFLOW] on [BATCHWORKFLOW].[ID] = [BATCHTEMPLATE].[BATCHWORKFLOWID]
  left outer join dbo.[BATCHNUMBERINGSCHEME] on [BATCHNUMBERINGSCHEME].[ID] = [BATCHTEMPLATE].[BATCHNUMBERINGSCHEMEID]
  left outer join [SITE] on [SITE].[ID] = [BATCHTEMPLATE].[SITEID]
  left outer join [INUSE] on [INUSE].[BATCHTEMPLATEID] = [BATCHTEMPLATE].[ID]
  where (@ISSYSADMIN = 1 or dbo.[UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE](@CURRENTAPPUSERID, [BATCHTEMPLATE].[ID]) = 1)
  and (@CATEGORY is null or [BATCHTYPECATALOG].[CATEGORY] = @CATEGORY)
  and ([BATCHTEMPLATE].[ACTIVE] = 1 or @INCLUDEINACTIVE = 1)
  and [BATCHTEMPLATE].[CUSTOM] = 0
  and (@ISSYSADMIN = 1 or
       @ISSITESECURITY = 0 or
       [BATCHTEMPLATE].[SITEID] is null or
       exists (select top 1 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) where [SITEID] = [BATCHTEMPLATE].[SITEID] or ([SITEID] is null and [BATCHTEMPLATE].[SITEID] is null)))
  and (@ISSITESECURITY = 0 or
       @SITEFILTERMODE = 0 or
       [BATCHTEMPLATE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [BATCHTEMPLATE].[NAME];

  return 0;
end