USP_REPORT_GENERATEBATCH_EXCEPTION

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

declare @TABLENAME nvarchar(255);
declare @SQL nvarchar(4000);

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
    set @SQL = '
      select 
        T.REASON, 
              T.RECORDID, 
              CONSTITUENT.NAME, 
                FINANCIALTRANSACTION.TRANSACTIONAMOUNT, 
                FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE, 
                FINANCIALTRANSACTION.DATE,
                ''http://www.blackbaud.com/REVENUELINK?REVENUEID='' + cast(T.RECORDID as nvarchar(max)) as REVENUELINK,
                CURRENCY.ISO4217 as ISOCURRENCYCODE,
                CURRENCY.DECIMALDIGITS,
                CURRENCY.CURRENCYSYMBOL,
                CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
            from ' + @TABLENAME + ' as T 
            left join dbo.FINANCIALTRANSACTION on T.RECORDID = FINANCIALTRANSACTION.ID
            left join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID';
  else
    set @SQL = '
      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 BUSINESSPROCESSSTATUS 
            where 1 = 0';


  exec sp_executesql @SQL;
end try
begin catch
  exec dbo.USP_RAISE_ERROR;
  return 1;
end catch
return 0;