UFN_QUERY_COMMITTEDBATCHES

CREATE function dbo.UFN_QUERY_COMMITTEDBATCHES
(
  @CURRENTAPPUSERID uniqueidentifier, 
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SPECIFICBATCHTYPEID uniqueidentifier = null,
  @NUMDAYS int = 7, 
  @INCLUDEDELETED bit = 0
)
returns table
as return (
  -- these batch template CTEs resolve a chain of batch templates, where each is created from the next, down to the original batch template

  with ISSYSADMIN (ISSYSADMIN) as (
    select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID
  )
  select
    BATCH.ID,
    BATCH.BATCHNUMBER,
    BATCH.DESCRIPTION,
    (select case when DISPLAYNAME = '' then USERNAME else DISPLAYNAME end from dbo.APPUSER where ID = BATCH.APPUSERID) as OWNER,
    BATCH.STATUS,
    BATCHTYPECATALOG.CATEGORY,
    BATCH.STATUSCODE,
    case when exists (select top 1 1
                      from dbo.BUSINESSPROCESSOUTPUT
                      inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = BATCH.ID
                      and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
                      and BUSINESSPROCESSOUTPUT.TABLEKEY = 'CONTROL')
              and BATCH.STATUSCODE > 0
              and len(BATCHTYPECATALOG.CONTROLREPORTREPORTSPECID) > 0
         then 1 else 0 end as CONTROLREPORTVISIBLE,
    case when exists (select top 1 1
                      from dbo.BUSINESSPROCESSOUTPUT
                      inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = BATCH.ID
                      and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
                      and BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION')
              and BATCH.STATUSCODE > 0
              and len(BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID) > 0
         then 1 else 0 end as EXCEPTIONREPORTVISIBLE,
    case when BATCH.ORIGINATINGBATCHID is not null or CHILDBATCH.ID is not null then 1 else 0 end as INEXCEPTIONCHAIN,
    case when BATCHTEMPLATE.CUSTOM = 1 then dbo.UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATENAME(BATCH.BATCHTEMPLATEID) else BATCHTEMPLATE.NAME end as BATCHDESIGN,
    BATCH.DATECOMMITTED,
    case when BATCHTEMPLATE.CUSTOM = 1 then 1 else null end as ISCUSTOM,
    isnull(SITE.NAME, 'All sites') as BATCHTEMPLATESITE,
    BATCH.DATEADDED,
    BATCH.DATECHANGED

  from dbo.BATCH
  left join dbo.BATCH as CHILDBATCH on BATCH.ID = CHILDBATCH.ORIGINATINGBATCHID
  inner join BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
  inner join BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
  left join dbo.SITE on SITE.ID = BATCHTEMPLATE.SITEID
  left join dbo.BATCHWORKFLOWSTATUS on BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID = BATCH.BATCHWORKFLOWSTATEID and BATCHWORKFLOWSTATUS.APPUSERID = @CURRENTAPPUSERID and BATCHWORKFLOWSTATUS.DATECHANGED = BATCH.DATECHANGED
  cross apply ISSYSADMIN

  where BATCH.STATUSCODE IN (1, 1 + isnull(@INCLUDEDELETED, 0))  -- committed, deleted

  and BATCH.DATECOMMITTED is not null

  -- feature security

  and (
    BATCHWORKFLOWSTATUS.BATCHID is not null  -- current user is assigned the batch; trumps everything

    or 
    BATCH.APPUSERID = @CURRENTAPPUSERID  -- current user owns the batch; trumps everything

    or
    ISSYSADMIN.ISSYSADMIN = 1
    or (
      dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID, BATCH.ID) = 1
      and (
        BATCHTEMPLATE.SITEID is null
        or exists (select top 1 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '10265778-d9b2-474e-84b5-422cfec48e57', 10) where SITEID = BATCHTEMPLATE.SITEID)
      )
    )
  )

  -- site filter

  and (
    @SITEFILTERMODE = 0
    or
    BATCHTEMPLATE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
  )

  -- time window

  and (
    @NUMDAYS is null
    -- the "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

    or (@NUMDAYS is not null and dateadd(ms, -003, dateadd(d, 1, cast(cast(BATCH.DATECOMMITTED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
  )

  -- other parameters

  and (@SPECIFICBATCHTYPEID is null or BATCHTYPECATALOG.ID = @SPECIFICBATCHTYPEID)
)