USP_DATALIST_BATCHEXCEPTIONCHAIN

                CREATE procedure dbo.USP_DATALIST_BATCHEXCEPTIONCHAIN
                (
                    @BATCHID uniqueidentifier
                )
                as
                    set nocount on;

                    with FULLEXCEPTIONCHAIN(ID,BATCHNUMBER,[LEVEL])
                    as
                    (
                        select 
                            [CHAINANCESTORS].ID,
                            [CHAINANCESTORS].BATCHNUMBER,
                            [CHAINANCESTORS].[LEVEL]
                        from dbo.UFN_BATCH_GETEXCEPTIONCHAINANCESTORS(@BATCHID) [CHAINANCESTORS]

                        union all

                        select 
                            @BATCHID,
                            BATCH.BATCHNUMBER,
                            0
                        from dbo.BATCH
                        where BATCH.ID = @BATCHID

                        union all

                        select 
                            [CHAINANCESTORS].ID,
                            [CHAINANCESTORS].BATCHNUMBER,
                            [CHAINANCESTORS].[LEVEL]
                        from dbo.UFN_BATCH_GETEXCEPTIONCHAINDESCENDANTS(@BATCHID) [CHAINANCESTORS]
                    )

                    select 
                        FULLEXCEPTIONCHAIN.ID,
                        FULLEXCEPTIONCHAIN.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 1 
                            from dbo.BUSINESSPROCESSOUTPUT
                            inner join dbo.BATCHSTATUS on BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
                            where BATCHSTATUS.PARAMETERSETID = BATCH.ID 
                            and BUSINESSPROCESSOUTPUT.TABLEKEY = 'CONTROL')
                            and BATCH.STATUSCODE > 0 and len(BATCHTYPECATALOG.CONTROLREPORTREPORTSPECID) > 0 and BATCH.DATECOMMITTED is not null then 1
                            else 0
                        end CONTROLREPORTVISIBLE,
                        case
                            when exists (select 1 
                            from dbo.BUSINESSPROCESSOUTPUT
                            inner join dbo.BATCHSTATUS on BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
                            where BATCHSTATUS.PARAMETERSETID = BATCH.ID
                            and BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION') 
                            and BATCH.STATUSCODE > 0 and len(BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID) > 0 and BATCH.DATECOMMITTED is not null then 1
                            else 0
                        end EXCEPTIONREPORTVISIBLE,
                        FULLEXCEPTIONCHAIN.[LEVEL],
                        BATCHTYPECATALOG.VALIDATIONREPORTID [REPORTID]
                    from FULLEXCEPTIONCHAIN
                        inner join dbo.BATCH on FULLEXCEPTIONCHAIN.ID = BATCH.ID
                        inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
                        inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
                    order by [LEVEL]