USP_POSTTOGLPROCESS_SUBMULTICURRENCYREPORT

            create procedure dbo.USP_POSTTOGLPROCESS_SUBMULTICURRENCYREPORT(@ID nvarchar(36))
            with execute as owner
            as
                set nocount on;        
                declare @TABLENAME nvarchar(255);
                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;

                select 
                    @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME,
                    @BASECURRENCYID = coalesce(CURRENCYSET.BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
                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 = '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 
                            Journal, 
                            T2.YEARID as YEAR, 
                            T1.SEQUENCE as PERIOD, 
                            Sum(case DEBITCREDIT when CHAR(68) then AMOUNT else 0 end) as TOTALDEBIT,
                            Sum(case DEBITCREDIT when CHAR(67) then AMOUNT else 0 end) as TOTALCREDIT,
                            @ISOCURRENCYCODE as ISOCURRENCYCODE,
                            @CURRENCYSYMBOL as CURRENCYSYMBOL,
                            @DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            Sum(case DEBITCREDIT when CHAR(68) then ORGANIZATIONAMOUNT else 0 end) as ORGANIZATIONTOTALDEBIT,
                            Sum(case DEBITCREDIT when CHAR(67) then ORGANIZATIONAMOUNT else 0 end) as ORGANIZATIONTOTALCREDIT,
                            @ORGISOCURRENCYCODE as ORGANIZATIONISOCURRENCYCODE,
                            @ORGCURRENCYSYMBOL as ORGANIZATIONCURRENCYSYMBOL,
                            @ORGDECIMALDIGITS as ORGANIZATIONCURRENCYDECIMALDIGITS,
                            @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE as ORGANIZATIONCURRENCYSYMBOLDISPLAYSETTINGCODE    
                        from 
                            dbo.' + @TABLENAME +
                            ' inner Join GLFISCALPERIOD as T1 on cast(POSTDATE as DATE) between STARTDATE and ENDDATE 
                            inner Join GLFISCALYEAR as T2 on T1.GLFISCALYEARID= T2.ID 
                        group By 
                            Journal, T2.YEARID, T1.SEQUENCE
                        order by T2.YEARID, T1.SEQUENCE asc';

                    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