USP_REPORT_PREPOSTTOGL

Create procedure [dbo].[USP_REPORT_PREPOSTTOGL](@ID nvarchar(36)) 
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 @ISOCURRENCYCODE nvarchar(3);
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @DECIMALDIGITS tinyint;
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

    declare @PDACCOUNTSYSTEMID nvarchar(36);
    set @PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.POSTTOGLPROCESS p where p.ID = @ID);  

    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 
        @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE        
    from
         dbo.UFN_CURRENCY_GETPROPERTIES(@BASECURRENCYID) CURRENCYPROPERTIES;

    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 dbo.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 IDSETREGISTER as t2 on t1.ADJUSTMENTIDSETREGISTERID=t2.ID 
    where 
        t1.ID = @ID ;

    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 
        f1.ID, 
        f1.REVENUEID, 
        f1.DEPOSITCORRECTIONID, 
        f1.ADJUSTMENTID, 
        f1.ACCOUNT as ACCOUNT2,
        isnull(nullif(ga.ACCOUNTALIAS,''),f1.ACCOUNT) as ACCOUNT, 
        f1.LOOKUPID, 
        f1.POSTDATE, 
        f1.TRANSACTIONTYPECODE, 
        f1.AMOUNT, 
        f1.JOURNAL, 
        f1.REFERENCE, 
        f1.DEPOSITID, 
        f1.DEPOSITLINKID, 
        f1.DEPOSITNUMBER, 
        f1.PERIODID, 
        f1.SUMMARYDATE, 
        f1.GROUPBY, 
        f1.ISEXCEPTION, 
        f1.EXCEPTIONREASON,
        @ISOCURRENCYCODE as ISOCURRENCYCODE,
        @CURRENCYSYMBOL as CURRENCYSYMBOL,
        @DECIMALDIGITS as CURRENCYDECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
        f1.CREDITID,
        ga.ACCOUNTDESCRIPTION,
        ga.ACCOUNTALIAS
    from 
        dbo.UFN_PREPOST_DATA(@ID) as f1 
    inner join dbo.GLACCOUNT ga on ga.ACCOUNTNUMBER = f1.ACCOUNT and ga.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
    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)

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

end