USP_DATALIST_OPENBATCHES

CREATE procedure dbo.USP_DATALIST_OPENBATCHES(@CURRENTAPPUSERID uniqueidentifier,@BATCHDESIGN uniqueidentifier = null,@CATEGORY nvarchar(100) = '', @OWNER uniqueidentifier = null )
with execute as owner
as                

declare @sql nvarchar(max),
        @paramlist nvarchar(4000);

select @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 PROCESSOR
                  , case
                      when [BATCH].[STATUSCODE] = 3 then [BATCH].[STATUS]
                      else BATCHWORKFLOWSTATE.NAME
                    end as STATUS
                  , [BATCHTYPECATALOG].[CATEGORY]
                  , [BATCH].[COMMENTS]
                  , [BATCH].[DATECHANGED]
                  , dbo.UFN_BATCH_GETVALIDATIONREPORTID(BATCH.ID) REPORTID
                  , dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID) TASKSAVAILABLE
                  , (select ALLOWCOMMIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) ENABLECOMMIT
                  , (select ALLOWEDIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) ENABLEEDIT
                  , coalesce(BATCHTYPECATALOG.BATCHDELETERECORDOPERATIONID, ''5345164C-DD4F-4B6E-9CF1-10E1B25E3F4F'') BATCHDELETERECORDOPERATIONID
                  , 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), @DATE) >= BATCHWORKFLOWSTATE.OVERDUEIN then 
                    1
                    else 
                    0
                    end
                  , 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), @DATE) >= BATCHWORKFLOWSTATE.OVERDUEIN then 
                    ''RES:warning''
                    else 
                    ''RES:lv_spacer''
                    end
                  , 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 BATCHTEMPLATE
                  , case when BATCHTEMPLATE.CUSTOM = 1 then 1 else null end ISCUSTOM
                  , [BATCH].[DATEADDED]
                  , [BATCH].STATUSCODE as BATCHSTATUSCODE
                  , applock_test(''public'', convert(nvarchar(36), upper(BATCH.ID)), ''Exclusive'', ''Session'') as APPLOCKFREE
                  , case when PARENTBATCH.STATUSCODE not in (1, 2) then 0 else 1 end as PARENTCOMMITTED
                from dbo.BATCH
                  left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID
                  left join dbo.BATCH PARENTBATCH on PARENTBATCH.ID = BATCH.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 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 (0, 3)
                  and (@OWNER is null or BATCH.APPUSERID = @OWNER) '

if @BATCHDESIGN is not null
  select @sql = @sql + ' and (BATCHTEMPLATE.ID = @BATCHDESIGN or xtr.ORIGINATINGTEMPLATEID = @BATCHDESIGN) '

if @CATEGORY <> '' and @CATEGORY is not null
  select @sql = @sql + ' and BATCHTYPECATALOG.CATEGORY = @CATEGORY'

select @sql = @sql + ' order by [BATCH].[DATEADDED];';

select @paramlist = '@CURRENTAPPUSERID uniqueidentifier,
                     @BATCHDESIGN uniqueidentifier = null,
                     @OWNER uniqueidentifier = null,
                     @CATEGORY nvarchar(100) = '''' '

exec sp_executesql @sql, @paramlist, @CURRENTAPPUSERID, @BATCHDESIGN, @OWNER, @CATEGORY;