USP_REPORT_PREPOSTTOGLMULTICURRENCY_SUBREPORT

create procedure dbo.USP_REPORT_PREPOSTTOGLMULTICURRENCY_SUBREPORT(@ID nvarchar(36))
with execute as owner
as
begin

    set transaction isolation level read uncommitted

    set nocount on;

    declare @filterTable varchar(150)
    declare @sql nvarchar(4000);
    declare @depositFilterTable varchar(150);
    declare @adjustmentFiltertable varchar(150);
    declare @AllRevenues bit = 1
    declare @AllAdjustments bit = 1
    declare @AllDeposits bit = 1

    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEISOCURRENCYCODE nvarchar(3);
    declare @BASECURRENCYSYMBOL nvarchar(5);
    declare @BASEDECIMALDIGITS tinyint;
    declare @BASECURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

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

    select @AllRevenues = case t1.POSTINGOPTIONCODE when 3 then 0 else 1 end
    from dbo.POSTTOGLPROCESS as t1
    where t1.ID = @ID;

    select @AllAdjustments = case t1.ADJUSTMENTPOSTINGOPTIONCODE when 2 then 0 else 1 end
    from dbo.POSTTOGLPROCESS as t1
    where t1.ID = @ID;

    select @AllDeposits = case t1.DEPOSITPOSTINGOPTIONCODE when 1 then 0 else 1 end
    from dbo.POSTTOGLPROCESS as t1
    where t1.ID = @ID;

    select 
        @BASECURRENCYID = coalesce(CURRENCYSET.BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
    from
        dbo.POSTTOGLPROCESS
        left join dbo.PDACCOUNTSYSTEM ON POSTTOGLPROCESS.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        left join dbo.CURRENCYSET ON PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    where 
        POSTTOGLPROCESS.ID = @ID;

    select 
        @BASEISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @BASECURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @BASEDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @BASECURRENCYSYMBOLDISPLAYSETTINGCODE = 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;

    select 
        @filterTable= 'dbo.' + t2.DBOBJECTNAME + case t2.objecttype when 1 then '()' else '' end
    from 
        dbo.POSTTOGLPROCESS as t1
        inner join dbo.IDSETREGISTER as t2 on t1.IDSETREGISTERID=t2.ID 
    where 
        t1.ID = @ID ;

    select 
        @depositFilterTable= 'dbo.' + t2.DBOBJECTNAME + case t2.objecttype when 1 then '()' else '' end
    from 
        dbo.POSTTOGLPROCESS as t1
        inner join IDSETREGISTER as t2 on t1.DEPOSITIDSETREGISTERID=t2.ID 
    where 
        t1.ID = @ID ;

    select 
        @adjustmentFiltertable= 'dbo.' + t2.DBOBJECTNAME + case t2.objecttype when 1 then '()' else '' end
    from 
        dbo.POSTTOGLPROCESS as t1
        inner join dbo.IDSETREGISTER as t2 on t1.ADJUSTMENTIDSETREGISTERID=t2.ID 
    where 
        t1.ID = @ID;

    declare @JOURNAL nvarchar(255);    

    if exists(select CUSTOMJOURNAL from dbo.POSTTOGLPROCESSCUSTOMJOURNAL where len(replace(CUSTOMJOURNAL,' ','')) > 0)
        select @JOURNAL = CUSTOMJOURNAL from dbo.POSTTOGLPROCESSCUSTOMJOURNAL
    else
        select @JOURNAL = PRODUCT from dbo.INSTALLATIONINFO;        

    create table #FilterTable (ID uniqueidentifier)
    create table #AdjustmentTable (ID uniqueidentifier)
    create table #DepositTable (ID uniqueidentifier)

    if @filterTable is not null
        begin
        set @SQL = 'insert into #FilterTable (ID) select ID from '+@filterTable
        exec (@SQL)
        set @AllRevenues = 0
        end

    if @adjustmentFiltertable is not null
        begin
        set @SQL = 'insert into #AdjustmentTable (ID) select ID from '+@adjustmentFiltertable
        exec (@SQL)
        set @AllAdjustments = 0
        end

    if @depositFilterTable is not null
        begin
        set @SQL = 'insert into #DepositTable (ID) select ID from '+@depositFilterTable
        exec (@SQL)
        set @AllDeposits = 0
        end    

    select 
        @JOURNAL as JOURNAL, --f1.Journal, 

        t2.YEARID as YEARID, 
        t1.SEQUENCE as PERIOD, 
        sum(case TRANSACTIONTYPECODE when 0 then f1.AMOUNT else 0 end) TOTALDEBIT,
        sum(case TRANSACTIONTYPECODE when 1 then f1.AMOUNT else 0 end) TOTALCREDIT,
        @BASEISOCURRENCYCODE as ISOCURRENCYCODE,
        @BASECURRENCYSYMBOL as CURRENCYSYMBOL,
        @BASEDECIMALDIGITS as CURRENCYDECIMALDIGITS,
        @BASECURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
        sum(case TRANSACTIONTYPECODE when 0 then f1.ORGANIZATIONAMOUNT else 0 end) ORGANIZATIONTOTALDEBIT,
        sum(case TRANSACTIONTYPECODE when 1 then f1.ORGANIZATIONAMOUNT else 0 end) ORGANIZATIONTOTALCREDIT,
        @ORGISOCURRENCYCODE as ORGANIZATIONISOCURRENCYCODE,
        @ORGCURRENCYSYMBOL as ORGANIZATIONCURRENCYSYMBOL,
        @ORGDECIMALDIGITS as ORGANIZATIONCURRENCYDECIMALDIGITS,
        @ORGCURRENCYSYMBOLDISPLAYSETTINGCODE as ORGANIZATIONCURRENCYSYMBOLDISPLAYSETTINGCODE
    from 
        dbo.UFN_PREPOST_DATA(@ID) f1
        inner join GLFISCALPERIOD as t1 on f1.PERIODID = t1.ID
        inner join GLFISCALYEAR as t2 on t1.GLFISCALYEARID = t2.ID
    where 
        (f1.REVENUEIDGUID is not null and (f1.REVENUEIDGUID in (select ID from #FilterTable) or @AllRevenues = 1)  and f1.DEPOSITID is null)
        or
        (f1.ADJUSTMENTIDGUID is not null and (f1.ADJUSTMENTIDGUID in (select ID from #AdjustmentTable) or @AllAdjustments = 1))
        or
        (f1.DEPOSITID is not null and (f1.DEPOSITID in (select ID from #DepositTable) or @AllDeposits = 1))
        or
        (f1.REVENUEIDGUID is null and f1.ADJUSTMENTIDGUID is null and f1.DEPOSITID is null and @AllRevenues = 1)
    Group By t2.YEARID, t1.SEQUENCE
    Order By t2.YEARID, t1.SEQUENCE

    drop table #FilterTable 
    drop table #AdjustmentTable 
    drop table #DepositTable

end