USP_DATALIST_COMMITTEDBATCHES

CREATE procedure dbo.USP_DATALIST_COMMITTEDBATCHES
(
  @CURRENTAPPUSERID uniqueidentifier,
  @NUMDAYS int = 7,
  @BATCHDESIGN uniqueidentifier = null,
  @CATEGORY nvarchar(100) = '',
  @INCLUDEDELETED bit = 0
)
as
  set nocount on;

  declare @SQL nvarchar(max),
          @PARAMLIST nvarchar(4000);

  set @SQL = '
    declare @DATE datetime
    set @DATE = getdate();

    with CTE_NAME(ID, LEVELS, ORIGINATINGTEMPLATEID, ORIGINATINGTEMPLATENAME, LOOKUPID) as
    (
      select bt.ID, 0 as levels, bt.ID, bt2.NAME,  bt2.ID
      from dbo.BATCHTEMPLATE bt
      left outer join dbo.BATCHTEMPLATE bt2 on bt.ORIGINATINGTEMPLATEID = bt2.ID    
      union all
      select n.ID, n.LEVELS + 1, bt3.ID, bt3.NAME, bt3.ORIGINATINGTEMPLATEID
      from CTE_NAME n
      inner join dbo.BATCHTEMPLATE bt3 on n.LOOKUPID = bt3.ID
    )                 
    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].[DATECHANGED],
      coalesce(BATCHTYPECATALOG.BATCHDELETERECORDOPERATIONID, ''5345164C-DD4F-4B6E-9CF1-10E1B25E3F4F'') BATCHDELETERECORDOPERATIONID,
      BATCH.STATUSCODE,
      case
        when exists (select 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 CONTROLREPORTVISIBLE,
      case
        when exists (select 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 EXCEPTIONREPORTVISIBLE,
      case
        when (BATCH.ORIGINATINGBATCHID is not null) or ([CHILDBATCH].ID is not null) then 1
        else 0
      end INEXCEPTIONCHAIN,
      case when CUSTOM = 1 then xtr.ORIGINATINGTEMPLATENAME else BATCHTEMPLATE.NAME end BATCHDESIGN,
      [BATCH].[DATECOMMITTED],          
      case when BATCHTEMPLATE.CUSTOM = 1 then 1 else null end ISCUSTOM
    from dbo.BATCH
    left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID
    inner join BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    inner join BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    left outer join (select n.ID, n.ORIGINATINGTEMPLATENAME, n.ORIGINATINGTEMPLATEID
                      from CTE_NAME n
                      inner join (
                        select MAX(n.LEVELS) MAXLEVEL, n.ID
                        from CTE_NAME n
                        group by id  
                      ) m on m.ID = n.ID and m.MAXLEVEL = n.LEVELS
                    ) xtr on xtr.ID = BATCH.BATCHTEMPLATEID
    where dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID, BATCH.ID) = 1
    and BATCH.STATUSCODE IN (1, 1 + @INCLUDEDELETED)
    and not BATCH.DATECOMMITTED is null
    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()))
    )' + char(13);

  if @BATCHDESIGN is not null
    set @SQL += '    and (BATCHTEMPLATE.ID = @BATCHDESIGN or xtr.ORIGINATINGTEMPLATEID = @BATCHDESIGN)' + char(13);

  if @CATEGORY is not null and @CATEGORY <> ''
    set @SQL += '    and BATCHTYPECATALOG.CATEGORY = @CATEGORY' + char(13);

  set @SQL += '    order by BATCH.DATECOMMITTED DESC;';

  set @PARAMLIST = '@CURRENTAPPUSERID uniqueidentifier, ' +
                   '@NUMDAYS int, ' +
                   '@BATCHDESIGN uniqueidentifier = null, ' +
                   '@CATEGORY nvarchar(100) = '''', ' +
                   '@INCLUDEDELETED bit';

  exec sp_executesql @SQL, @PARAMLIST,
    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
    @NUMDAYS = @NUMDAYS,
    @BATCHDESIGN = @BATCHDESIGN,
    @CATEGORY = @CATEGORY,
    @INCLUDEDELETED = @INCLUDEDELETED;

  return 0;