USP_REPORT_POSTTOGLPROCESSEXCEPTION2

CREATE procedure dbo.USP_REPORT_POSTTOGLPROCESSEXCEPTION2(@ID nvarchar(36))
with execute as owner
as
    set nocount on;
    declare @TABLENAME nvarchar(255);
    declare @SQL nvarchar(4000);
  declare @PDACCOUNTSYSTEMID nvarchar(36);

    declare @BASECURRENCYID uniqueidentifier;
    select 
        @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME,
        @BASECURRENCYID = coalesce(CURRENCYSET.BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()),
    @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    from dbo.BUSINESSPROCESSSTATUS 
        inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
        inner join dbo.POSTTOGLPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID= POSTTOGLPROCESSSTATUS.ID
        inner join dbo.POSTTOGLPROCESS on POSTTOGLPROCESSSTATUS.PARAMETERSETID = POSTTOGLPROCESS.ID    
        left join dbo.PDACCOUNTSYSTEM ON POSTTOGLPROCESS.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        left join dbo.CURRENCYSET ON PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION';

    if @TABLENAME is null or OBJECT_ID(@TABLENAME) is null
        raiserror('Business process exception table could not be found.  The process might not have completed successfully. ',13,1)

    declare @ISOCURRENCYCODE nvarchar(3);
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @DECIMALDIGITS tinyint;
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
    select 
        @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE        
    from
         dbo.UFN_CURRENCY_GETPROPERTIES(@BASECURRENCYID) CURRENCYPROPERTIES 

    declare @ORGISOCURRENCYCODE nvarchar(3);
    declare @ORGCURRENCYSYMBOL nvarchar(5);
    declare @ORGDECIMALDIGITS tinyint;
    declare @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;        
    select 
        @ORGISOCURRENCYCODE = CURRENCY.ISO4217,
        @ORGCURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
        @ORGDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE        
    from dbo.CURRENCY
    where CURRENCY.ISORGANIZATIONCURRENCY = 1;

    begin try
        set @SQL = 'select 
                    CASE WHEN REVENUE.ID is not null then ''http://www.blackbaud.com/REVENUEID?REVENUEID='' + CONVERT(nvarchar(36), EXCEPTION.RECORDID) ELSE NULL END as REVENUEID,
                    CASE WHEN BANKACCOUNTDEPOSITCORRECTION.ID is not null then ''http://www.blackbaud.com/DEPOSITCORRECTIONID?DEPOSITCORRECTIONID='' + CONVERT(nvarchar(36), EXCEPTION.RECORDID) ELSE NULL END as DEPOSITCORRECTIONID,
                    CASE WHEN BANKACCOUNTADJUSTMENT.ID is not null then ''http://www.blackbaud.com/ADJUSTMENTID?ADJUSTMENTID='' + CONVERT(nvarchar(36), EXCEPTION.RECORDID) ELSE NULL END as ADJUSTMENTID,
                    isnull(nullif(ga.ACCOUNTALIAS,''''),EXCEPTION.ACCOUNTSTRING) as ACCOUNTSTRING, 
                    CONVERT(datetime,EXCEPTION.POSTDATE) as POSTDATE, 
                    EXCEPTION.TRANSACTIONTYPE, 
                    EXCEPTION.TRANSACTIONTYPECODE,
                    EXCEPTION.AMOUNT, 
                    EXCEPTION.REFERENCE,
                    EXCEPTION.EXCEPTIONREASON,
                    @ISOCURRENCYCODE as ISOCURRENCYCODE,
                    @CURRENCYSYMBOL as CURRENCYSYMBOL,
                    @DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    EXCEPTION.TRANSACTIONAMOUNT, 
                    CURRENCY.ISO4217 as TRANSACTIONISOCURRENCYCODE,
                    CURRENCY.CURRENCYSYMBOL as TRANSACTIONCURRENCYSYMBOL,
                    CURRENCY.DECIMALDIGITS as TRANSACTIONCURRENCYDECIMALDIGITS,
                    CURRENCY.SYMBOLDISPLAYSETTINGCODE as TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    EXCEPTION.ORGANIZATIONAMOUNT, 
                    @ORGISOCURRENCYCODE as ORGANIZATIONISOCURRENCYCODE,
                    @ORGCURRENCYSYMBOL as ORGANIZATIONCURRENCYSYMBOL,
                    @ORGDECIMALDIGITS as ORGANIZATIONCURRENCYDECIMALDIGITS,
                    @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE as ORGANIZATIONCURRENCYSYMBOLDISPLAYSETTINGCODE
                    ,case when CREDIT.ID is not null then ''http://www.blackbaud.com/CREDITID?CREDITID='' + CONVERT(nvarchar(36), EXCEPTION.RECORDID) ELSE NULL END as CREDITID
                from dbo.' + @TABLENAME + ' as EXCEPTION 
          inner join dbo.GLACCOUNT ga on ga.ACCOUNTNUMBER = EXCEPTION.ACCOUNTSTRING and ga.PDACCOUNTSYSTEMID =' + CHAR(39) + @PDACCOUNTSYSTEMID + CHAR(39) + ' 
                    left outer join dbo.REVENUE on EXCEPTION.RECORDID = REVENUE.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION on EXCEPTION.RECORDID = BANKACCOUNTDEPOSITCORRECTION.ID
                    left outer join dbo.BANKACCOUNTADJUSTMENT on EXCEPTION.RECORDID = BANKACCOUNTADJUSTMENT.ID
                    left join dbo.CURRENCY on CURRENCY.ID = EXCEPTION.TRANSACTIONCURRENCYID
                    left outer join dbo.CREDIT on CREDIT.ID = EXCEPTION.RECORDID
                order by
                    EXCEPTION.POSTDATE,
                    EXCEPTION.REFERENCE,
                    ACCOUNTSTRING,
                    EXCEPTION.AMOUNT,
                    EXCEPTION.TRANSACTIONTYPE,
                    EXCEPTION.EXCEPTIONREASON ';

        exec sp_executesql @SQL,
            N'@ISOCURRENCYCODE nvarchar(3), @CURRENCYSYMBOL nvarchar(5), @DECIMALDIGITS tinyint, @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint, @ORGISOCURRENCYCODE nvarchar(3), @ORGCURRENCYSYMBOL nvarchar(5), @ORGDECIMALDIGITS tinyint, @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
            @ISOCURRENCYCODE = @ISOCURRENCYCODE,
            @CURRENCYSYMBOL = @CURRENCYSYMBOL,
            @DECIMALDIGITS = @DECIMALDIGITS,
            @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE,
            @ORGISOCURRENCYCODE = @ORGISOCURRENCYCODE,
            @ORGCURRENCYSYMBOL = @ORGCURRENCYSYMBOL,
            @ORGDECIMALDIGITS = @ORGDECIMALDIGITS,
            @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE = @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch