USP_POSTTOGLPROCESSMULTICURRENCY_REPORT2

            create procedure dbo.USP_POSTTOGLPROCESSMULTICURRENCY_REPORT2(@ID nvarchar(36))
            with execute as owner
            as
                set nocount on;        
                declare @TABLENAME nvarchar(255);
                declare @REPORTTYPE nvarchar(10);
                declare @GLBATCH nvarchar(100);
                declare @SQL nvarchar(4000);

                declare @BASECURRENCYID uniqueidentifier;
                declare @ISOCURRENCYCODE nvarchar(3);
                declare @CURRENCYSYMBOL nvarchar(5);
                declare @DECIMALDIGITS tinyint;
                declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

                declare @ORGISOCURRENCYCODE nvarchar(3);
                declare @ORGCURRENCYSYMBOL nvarchar(5);
                declare @ORGDECIMALDIGITS tinyint;
                declare @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

                declare @PDACCOUNTSYSTEMID nvarchar(36);

                select 
                    @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME,
                    @REPORTTYPE = case POSTTOGLPROCESS.MARKASPOSTED when 0 then 'Preview' else 'Posted' end,
                    @GLBATCH = 
                        case when len(GLBATCH.LOOKUPID) > 0 then N' - Process ID -' + GLBATCH.LOOKUPID
                             else N''
                        end,
                    @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.GLBATCH on GLBATCH.POSTPROCESSSTATUSID = BUSINESSPROCESSSTATUS.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 = 'OUTPUT';


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

                select 
                    @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE        
                from
                     dbo.UFN_CURRENCY_GETPROPERTIES(@BASECURRENCYID) CURRENCYPROPERTIES 

                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 
                                @REPORTTYPEIN as REPORTTYPE, 
                                outTable.ACCOUNTSTRING, 
                                outTable.POSTDATE as POSTDATE, 
                                outTable.DEBITCREDIT, 
                                outTable.AMOUNT, 
                                outTable.JOURNAL,
                                outTable.REFERENCE,
                                outTable.BATCH,
                                @GLBATCHIN as GLBATCH,
                                @ISOCURRENCYCODE as ISOCURRENCYCODE,
                                @CURRENCYSYMBOL as CURRENCYSYMBOL,
                                @DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                                @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                                outTable.TRANSACTIONAMOUNT, 
                                CURRENCY.ISO4217 as TRANSACTIONISOCURRENCYCODE,
                                CURRENCY.CURRENCYSYMBOL as TRANSACTIONCURRENCYSYMBOL,
                                CURRENCY.DECIMALDIGITS as TRANSACTIONCURRENCYDECIMALDIGITS,
                                CURRENCY.SYMBOLDISPLAYSETTINGCODE as TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                                outTable.ORGANIZATIONAMOUNT, 
                                @ORGISOCURRENCYCODE as ORGANIZATIONISOCURRENCYCODE,
                                @ORGCURRENCYSYMBOL as ORGANIZATIONCURRENCYSYMBOL,
                                @ORGDECIMALDIGITS as ORGANIZATIONCURRENCYDECIMALDIGITS,
                                @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE as ORGANIZATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                                ga.ACCOUNTDESCRIPTION,
                                ga.ACCOUNTALIAS

                            from dbo.'
                            + @TABLENAME 
                            +' outTable
                              left join (select GLACCOUNT.ACCOUNTDESCRIPTION
                                        ,GLACCOUNT.ACCOUNTALIAS
                                        ,GLACCOUNT.ACCOUNTNUMBER
                                        ,case when GLACCOUNT.ACCOUNTALIAS = '''' then 1 else ROW_NUMBER() over(partition by GLACCOUNT.ACCOUNTALIAS order by GLACCOUNT.ACCOUNTALIAS) end NUMBER
                                    from dbo.GLACCOUNT where GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) ga on 
                                        ga.NUMBER = 1
                                        and ((outTable.ACCOUNTSTRING = ga.ACCOUNTNUMBER and ga.ACCOUNTALIAS = '''')
                                                or (len(ga.ACCOUNTALIAS) > 0 and outTable.ACCOUNTSTRING = ga.ACCOUNTALIAS))
                                left join dbo.CURRENCY on CURRENCY.ID = outTable.TRANSACTIONCURRENCYID
                            order by
                                convert(datetime,outTable.POSTDATE),
                                outTable.REFERENCE,
                                outTable.ACCOUNTSTRING,
                                outTable.AMOUNT,
                                outTable.DEBITCREDIT ';

                    exec sp_executesql 
                        @SQL, 
                        N'@REPORTTYPEIN nvarchar(10), @GLBATCHIN nvarchar(100), @ISOCURRENCYCODE nvarchar(3), @CURRENCYSYMBOL nvarchar(5), @DECIMALDIGITS tinyint, @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint, @ORGISOCURRENCYCODE nvarchar(3), @ORGCURRENCYSYMBOL nvarchar(5), @ORGDECIMALDIGITS tinyint, @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint, @PDACCOUNTSYSTEMID nvarchar(36)', 
                        @REPORTTYPEIN = @REPORTTYPE, 
                        @GLBATCHIN = @GLBATCH,
                        @ISOCURRENCYCODE = @ISOCURRENCYCODE,
                        @CURRENCYSYMBOL = @CURRENCYSYMBOL,
                        @DECIMALDIGITS = @DECIMALDIGITS,
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        @ORGISOCURRENCYCODE = @ORGISOCURRENCYCODE,
                        @ORGCURRENCYSYMBOL = @ORGCURRENCYSYMBOL,
                        @ORGDECIMALDIGITS = @ORGDECIMALDIGITS,
                        @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE = @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE,
                        @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
                end try

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