USP_REPORT_GENERATETRANSACTIONS_EXCEPTION

            CREATE procedure dbo.USP_REPORT_GENERATETRANSACTIONS_EXCEPTION
            (
                @ID nvarchar(36)
            )
            with execute as owner
            as
            set nocount on;

            declare @TABLENAME nvarchar(255);
            declare @SQL nvarchar(4000);
            create table #EXCEPTIONDATA(RECORDTYPE nvarchar(100),RECORDID uniqueidentifier,REASON nvarchar(max));

            begin try
                select @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME
                from dbo.BUSINESSPROCESSSTATUS
                inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
                where BUSINESSPROCESSSTATUS.ID = @ID and BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTIONS';

                if  (not @TABLENAME is null and not OBJECT_ID(@TABLENAME) is null )
                begin
                    set @SQL='Insert into #EXCEPTIONDATA(RECORDTYPE,RECORDID,REASON)  select RECORDTYPE, RECORDID, REASON from dbo.' + @TABLENAME 
                    exec sp_executesql @SQL
                end

                if  exists (select 1 from dbo.#EXCEPTIONDATA where RECORDTYPE<>'MEMBERSHIPBATCH' and isnull(RECORDTYPE,'')<>'' )
                    begin
                        select
                            E.REASON,
                            E.RECORDID,
                            CONSTITUENT_NF.NAME,
                            FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                            FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE,
                            FINANCIALTRANSACTION.DATE,
                            'http://www.blackbaud.com?REVENUEID=' + cast(E.RECORDID as nvarchar(max)) as REVENUELINK,
                            CURRENCY.ISO4217 as ISOCURRENCYCODE,
                            CURRENCY.DECIMALDIGITS,
                            CURRENCY.CURRENCYSYMBOL,
                            CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                        from #EXCEPTIONDATA as E
                        left join dbo.FINANCIALTRANSACTION on E.RECORDID = FINANCIALTRANSACTION.ID  
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) CONSTITUENT_NF  
                        left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
                    end
                else if exists (select 1 from dbo.#EXCEPTIONDATA where RECORDTYPE='MEMBERSHIPBATCH')
                    begin
                        select
                            E.REASON,
                            E.RECORDID,
                            CONSTITUENT_NF.NAME,
                            BATCHMEMBERSHIPDUESRESTRICTED.TOTALAMOUNT as TRANSACTIONAMOUNT,
                            'Membership' as TRANSACTIONTYPE,
                            BATCHMEMBERSHIPDUESRESTRICTED.DATE,
                            '' as REVENUELINK,
                            CURRENCY.ISO4217 as ISOCURRENCYCODE,
                            CURRENCY.DECIMALDIGITS,
                            CURRENCY.CURRENCYSYMBOL,
                            CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                        from #EXCEPTIONDATA as E
                        left join dbo.BATCHMEMBERSHIPDUESRESTRICTED on E.RECORDID = BATCHMEMBERSHIPDUESRESTRICTED.BATCHMEMBERSHIPDUESID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHMEMBERSHIPDUESRESTRICTED.BILLTOCONSTITUENTID) CONSTITUENT_NF
                        left join dbo.CURRENCY on BATCHMEMBERSHIPDUESRESTRICTED.TRANSACTIONCURRENCYID = CURRENCY.ID
                    end
                else
                        select
                            ID as [REASON],
                            null as RECORDID,
                            null as NAME,
                            null as TRANSACTIONAMOUNT,
                            null as TRANSACTIONTYPE,
                            null as [DATE],
                            null as REVENUELINK,
                            null as ISOCURRENCYCODE,
                            null as DECIMALDIGITS,
                            null as CURRENCYSYMBOL,
                            null as CURRENCYSYMBOLDISPLAYSETTINGCODE
                        from dbo.BUSINESSPROCESSSTATUS
                        where 1 = 0;
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch  
            drop table if exists #EXCEPTIONDATA;
                return 0;