USP_POSTTOGLPROCESS_SUBREPORT

            create procedure dbo.USP_POSTTOGLPROCESS_SUBREPORT(@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;

                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;

                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    
                        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', 
                        @ISOCURRENCYCODE = @ISOCURRENCYCODE,
                        @CURRENCYSYMBOL = @CURRENCYSYMBOL,
                        @DECIMALDIGITS = @DECIMALDIGITS,
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE
                end try

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