USP_POSTTOGLPROCESS_DETAILREPORT

                create procedure dbo.USP_POSTTOGLPROCESS_DETAILREPORT(@GLBATCHID uniqueidentifier)
                with execute as owner
                as
                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,
                        REVENUEPOSTEDDETAIL.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],
                        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 case when FINANCIALTRANSACTION.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V_BASECURRENCYFORFINANCIALTRANSACTION_I.BASECURRENCYID) else null end as ID
                        ) BASECURRENCY
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BASECURRENCY.ID) CURRENCYPROPERTIES
                    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,
                    [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].POSTDATE,
                    [CTE].GLBATCHDATE
                order by 
                    [CTE].LOOKUPID asc, 
                    [CTE].DATE asc, 
                    [CTE].REFERENCE asc, 
                    [CTE].GLBATCH asc