UFN_QUERY_UNCOMMITTEDBATCHES

CREATE function dbo.UFN_QUERY_UNCOMMITTEDBATCHES
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null, 
  @SPECIFICBATCHTYPEID uniqueidentifier = null,
  @NUMDAYS int = 7,
  @ONLYEXCEPTIONBATCHES bit = 0
)
returns table
as return  (

  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,
    case when BATCH.STATUSCODE = 3 then BATCH.STATUS else BATCHWORKFLOWSTATE.NAME end as STATUS,
    BATCHTYPECATALOG.CATEGORY,
    dbo.UFN_BATCH_GETVALIDATIONREPORTID(BATCH.ID) as REPORTID,
    dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID) as TASKSAVAILABLE,
    (select ALLOWCOMMIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) as ENABLECOMMIT,
    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,
    case when BATCHTEMPLATE.CUSTOM = 1 then 1 else null end as ISCUSTOM,
    BATCH.DATEADDED,
    BATCH.STATUSCODE as BATCHSTATUSCODE,
    case when BATCH.STATUSCODE = 3 then  -- committing

      -- for batches that are committing, see if the business process is running

      case when (select top 1 BPS.STATUSCODE from dbo.BATCHSTATUS as BS inner join dbo.BUSINESSPROCESSSTATUS as BPS on BPS.ID = BS.ID where BS.PARAMETERSETID = BATCH.ID order by BPS.STARTEDON DESC) = 1
      then 1 else 0 end
    else 0 end as BUSINESSPROCESSRUNNING,
    isnull(SITE.NAME, 'All sites') as BATCHTEMPLATESITE,
    case when BATCHWORKFLOWSTATE.OVERDUEIN > 0
          and datediff(day, (select max(BATCHWORKFLOWSTATUS.DATEADDED) from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID), getdate()) >= BATCHWORKFLOWSTATE.OVERDUEIN
    then 1 else 0 end as OVERDUE,
    BATCH.DATECHANGED,
    V_BATCH_ISLOCKED.ISLOCKED as ISBATCHLOCKED

  from dbo.BATCH
  inner join dbo.V_BATCH_ISLOCKED on BATCH.ID = V_BATCH_ISLOCKED.BATCHID
  left join dbo.BATCH as CHILDBATCH on BATCH.ID = CHILDBATCH.ORIGINATINGBATCHID
  inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
  inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
  left join dbo.BATCHWORKFLOWSTATE on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
  left join dbo.SITE on SITE.ID = BATCHTEMPLATE.SITEID
  cross apply ISSYSADMIN

  where BATCH.STATUSCODE in (0, 3)  -- uncommitted, committing


  -- feature security

  and (
    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  -- current user has access to the batch somehow

      and (
        -- check site security

        BATCHTEMPLATE.SITEID is null
        or exists (select top 1 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, 'bd1b97b5-9512-4d7a-8177-3692f7cefe77', 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.DATEADDED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
  )

  -- other parameters

  and (@SPECIFICBATCHTYPEID is null or BATCHTYPECATALOG.ID = @SPECIFICBATCHTYPEID)
  and (@ONLYEXCEPTIONBATCHES = 0 or dbo.UFN_BATCH_HASBATCHEXCEPTION(BATCH.ID) = 1)
)