USP_POSTTOGLPROCESS_DETAILMULTICURRENCYREPORT

                create procedure dbo.USP_POSTTOGLPROCESS_DETAILMULTICURRENCYREPORT(@GLBATCHID uniqueidentifier)
                with execute as owner
                as

                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;

                with [CTE] as 
                (
                    select 
                        'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),FINANCIALTRANSACTION.ID) as REVENUEID,
                        FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as LOOKUPID,
                        REVENUEPOSTEDDETAIL.REFERENCE,
                        cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
                        FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT,
                        case 
                            when len(GLBATCH.LOOKUPID) > 0 then N'(' + GLBATCH.LOOKUPID + N')'
                            else N''
                        end as GLBATCH,
                        CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                        CURRENCYPROPERTIES.CURRENCYSYMBOL,
                        CURRENCYPROPERTIES.DECIMALDIGITS [CURRENCYDECIMALDIGITS],
                        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                        FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                        TRANSACTIONCURRENCYPROPERTIES.ISO4217 [TRANSACTIONISOCURRENCYCODE],
                        TRANSACTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [TRANSACTIONCURRENCYSYMBOL],
                        TRANSACTIONCURRENCYPROPERTIES.DECIMALDIGITS [TRANSACTIONCURRENCYDECIMALDIGITS],
                        TRANSACTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
                        FINANCIALTRANSACTION.ORGAMOUNT as ORGANIZATIONAMOUNT,
                        cast(FINANCIALTRANSACTION.POSTDATE as datetime) as POSTDATE,
                        cast(GLBATCH.POSTDATE as date) as GLBATCHDATE
                    from 
                        dbo.REVENUEPOSTEDDETAIL
                        inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUEPOSTEDDETAIL.REVENUEPOSTEDID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUEPOSTED.ID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        left join dbo.GLBATCH on GLBATCH.ID = REVENUEPOSTEDDETAIL.GLBATCHID
                        left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I with (noexpand) on FINANCIALTRANSACTION.ID = V_BASECURRENCYFORFINANCIALTRANSACTION_I.FINANCIALTRANSACTIONID
                        outer apply (
                            select
                                isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V_BASECURRENCYFORFINANCIALTRANSACTION_I.BASECURRENCYID) as BASECURRENCYID,
                                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                            where FINANCIALTRANSACTION.DELETEDON is null --otherwise, we want base and transaction currencies to be null

                        ) REVENUECURRENCY
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUECURRENCY.BASECURRENCYID) CURRENCYPROPERTIES
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUECURRENCY.TRANSACTIONCURRENCYID) TRANSACTIONCURRENCYPROPERTIES
                    where    
                        REVENUEPOSTEDDETAIL.GLBATCHID = @GLBATCHID
                )

                select
                    [CTE].REVENUEID,
                    [CTE].LOOKUPID,
                    [CTE].REFERENCE,
                    [CTE].DATE,
                    sum([CTE].AMOUNT) as [AMOUNT],
                    [CTE].GLBATCH,
                    [CTE].ISOCURRENCYCODE,
                    [CTE].CURRENCYSYMBOL,
                    [CTE].CURRENCYDECIMALDIGITS,
                    [CTE].CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    sum([CTE].TRANSACTIONAMOUNT) as [TRANSACTIONAMOUNT],
                    [CTE].TRANSACTIONISOCURRENCYCODE,
                    [CTE].TRANSACTIONCURRENCYSYMBOL,
                    [CTE].TRANSACTIONCURRENCYDECIMALDIGITS,
                    [CTE].TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    sum([CTE].ORGANIZATIONAMOUNT) as [ORGANIZATIONAMOUNT], 
                    @ORGISOCURRENCYCODE as ORGANIZATIONISOCURRENCYCODE,
                    @ORGCURRENCYSYMBOL as ORGANIZATIONCURRENCYSYMBOL,
                    @ORGDECIMALDIGITS as ORGANIZATIONCURRENCYDECIMALDIGITS,
                    @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE as ORGANIZATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    [CTE].POSTDATE,
                    [CTE].GLBATCHDATE
                from 
                    [CTE]
                group by 
                    [CTE].REVENUEID, 
                    [CTE].LOOKUPID, 
                    [CTE].REFERENCE, 
                    [CTE].DATE, 
                    [CTE].GLBATCH, 
                    [CTE].ISOCURRENCYCODE,
                    [CTE].CURRENCYSYMBOL,
                    [CTE].CURRENCYDECIMALDIGITS,
                    [CTE].CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    [CTE].TRANSACTIONISOCURRENCYCODE,
                    [CTE].TRANSACTIONCURRENCYSYMBOL,
                    [CTE].TRANSACTIONCURRENCYDECIMALDIGITS,
                    [CTE].TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    [CTE].POSTDATE,
                    [CTE].GLBATCHDATE
                order by 
                    [CTE].LOOKUPID asc, 
                    [CTE].DATE asc, 
                    [CTE].REFERENCE asc, 
                    [CTE].GLBATCH asc