USP_DATALIST_OPENREGISTRANTBATCHES

create procedure dbo.USP_DATALIST_OPENREGISTRANTBATCHES(
  @EVENTID uniqueidentifier
)
as                
    declare @DATE datetime
    set @DATE = getdate();        
        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,
        BATCHWORKFLOWSTATE.NAME 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, @DATE, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc)) < 0 then 
            1
        else 
            0
        end as OverDue,
        case when BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, @DATE, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc)) < 0 then 
            'RES:warning'
        else 
            'RES:lv_spacer'
        end as Image,
        case
            when (BATCH.ORIGINATINGBATCHID is not null) or ([CHILDBATCH].ID is not null) then 1
            else 0
        end INEXCEPTIONCHAIN,
        case when CUSTOM = 1 then '<Custom>' else BATCHTEMPLATE.NAME end as BATCHDESIGN

     from dbo.BATCH
        left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID 
        inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
        inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
        inner join dbo.EventBatch eb on BATCH.ID = eb.ID and eb.EventID = @EVENTID
        left join dbo.BATCHWORKFLOWSTATE on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
    where [BATCH].[STATUSCODE] = 0                    
    AND BATCHTYPECATALOG.CATEGORY = 'Registrant'
    order by [BATCH].[DATEADDED];