USP_DATALIST_REVENUEGLDISTRIBUTIONDEPOSIT

CREATE procedure dbo.USP_DATALIST_REVENUEGLDISTRIBUTIONDEPOSIT (
    @DEPOSITID uniqueidentifier
    ,@SYSTEMDISTRIBUTION bit = 0
) as 
    set nocount on;

    declare @TempTbl Table (
        ID uniqueidentifier
        ,TYPECODE tinyint
        ,GLDISTRIBUTIONID uniqueidentifier
        ,DESCRIPTION nvarchar(100)
        ,TRANSACTIONTYPE nvarchar(100)
        ,ACCOUNT nvarchar(100)
        ,PROJECT nvarchar(100)
        ,AMOUNT money
        ,REFERENCE nvarchar(255)
        ,TRANSACTIONAMOUNT money
        ,BASEAMOUNT money
        ,ORGANIZATIONAMOUNT money
        ,TRANSACTIONCURRENCYID uniqueidentifier
        ,BASECURRENCYID uniqueidentifier
        ,ACCOUNTALIAS nvarchar(255)
        ,ISO4217 nvarchar(3)
        ,CURRENCYSYMBOL nvarchar(5)
        ,SYMBOLDISPLAYSETTINGCODE tinyint
        ,DECIMALDIGITS tinyint
        ,TRANSACTIONDEBITAMOUNT money
        ,TRANSACTIONCREDITAMOUNT money
        ,ORGANIZATIONDEBITAMOUNT money
        ,ORGANIZATIONCREDITAMOUNT money
        ,BASEDEBITAMOUNT money
        ,BASECREDITAMOUNT money
        ,BASEISO4217 nvarchar(3)
        ,BASECURRENCYSYMBOL nvarchar(5)
        ,BASESYMBOLDISPLAYSETTINGCODE tinyint
        ,BASEDECIMALDIGITS tinyint
        ,ORGISO4217 nvarchar(3)
        ,ORGCURRENCYSYMBOL nvarchar(5)
        ,ORGSYMBOLDISPLAYSETTINGCODE tinyint
        ,ORGDECIMALDIGITS tinyint
    );

    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEISO4217 nvarchar(3);
    declare @BASECURRENCYSYMBOL nvarchar(5);
    declare @BASEDECIMALDIGITS tinyint;
    declare @BASESYMBOLDISPLAYSETTINGCODE tinyint;

    declare @ORGISO4217 nvarchar(3);
    declare @ORGCURRENCYSYMBOL nvarchar(5);
    declare @ORGDECIMALDIGITS tinyint;
    declare @ORGSYMBOLDISPLAYSETTINGCODE tinyint;

    select @BASECURRENCYID = BAT.BASECURRENCYID
    from dbo.BANKACCOUNTTRANSACTION BAT
    where BAT.ID = @DEPOSITID;

    select 
        @BASEISO4217 = CURRENCYPROPERTIES.ISO4217,
        @BASECURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @BASEDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @BASESYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE        
    from
    dbo.UFN_CURRENCY_GETPROPERTIES(@BASECURRENCYID) CURRENCYPROPERTIES 

    select 
        @ORGISO4217 = CURRENCY.ISO4217,
        @ORGCURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
        @ORGDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @ORGSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE        
        from dbo.CURRENCY
    where CURRENCY.ISORGANIZATIONCURRENCY = 1;

    insert into @TempTbl (ID, TYPECODE, GLDISTRIBUTIONID, DESCRIPTION, TRANSACTIONTYPE, ACCOUNT, PROJECT, AMOUNT, REFERENCE, TRANSACTIONAMOUNT, BASEAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, ACCOUNTALIAS,ISO4217,CURRENCYSYMBOL,SYMBOLDISPLAYSETTINGCODE,DECIMALDIGITS,TRANSACTIONDEBITAMOUNT,TRANSACTIONCREDITAMOUNT,ORGANIZATIONDEBITAMOUNT,ORGANIZATIONCREDITAMOUNT,BASEDEBITAMOUNT,BASECREDITAMOUNT,BASEISO4217,BASECURRENCYSYMBOL,BASESYMBOLDISPLAYSETTINGCODE,BASEDECIMALDIGITS,ORGISO4217,ORGCURRENCYSYMBOL,ORGSYMBOLDISPLAYSETTINGCODE,ORGDECIMALDIGITS)
    select 
        BANKACCOUNTDEPOSITPAYMENT.ID
        ,1 as [TYPECODE]
        ,isnull(JEX.DISTRIBUTIONTABLEID, JE.ID) as GLDISTRIBUTIONID
        ,MAP.DESCRIPTION
        ,JE.TRANSACTIONTYPE
        ,coalesce(GLACCOUNT.ACCOUNTNUMBER,JEX.ACCOUNT,'') ACCOUNT
        ,JEX.PROJECT
        ,JE.BASEAMOUNT AMOUNT
        ,JE.COMMENT REFERENCE
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.TRANSACTIONAMOUNT, 0) else JE.TRANSACTIONAMOUNT end
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.BASEAMOUNT, 0) else JE.BASEAMOUNT end
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.ORGAMOUNT, 0) else JE.ORGAMOUNT end
        ,JE.TRANSACTIONCURRENCYID
        ,@BASECURRENCYID
        ,GLACCOUNT.ACCOUNTALIAS
        ,CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE
        ,CURRENCYPROPERTIES.CURRENCYSYMBOL
        ,CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
        ,CURRENCYPROPERTIES.DECIMALDIGITS
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.BASEAMOUNT  else 0 end as BASEDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.BASEAMOUNT  else 0 end as BASECREDITAMOUNT     
        ,@BASEISO4217 as BASEISO4217
        ,@BASECURRENCYSYMBOL as BASECURRENCYSYMBOL
        ,@BASESYMBOLDISPLAYSETTINGCODE as BASESYMBOLDISPLAYSETTINGCODE
        ,@BASEDECIMALDIGITS as BASEDECIMALDIGITS
        ,@ORGISO4217 as ORGISO4217
        ,@ORGCURRENCYSYMBOL as ORGCURRENCYSYMBOL
        ,@ORGSYMBOLDISPLAYSETTINGCODE as ORGSYMBOLDISPLAYSETTINGCODE
        ,@ORGDECIMALDIGITS as ORGDECIMALDIGITS
    from dbo.JOURNALENTRY JE
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = LI.FINANCIALTRANSACTIONID
    inner join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
    left join dbo.JOURNALENTRY_EXT ADJX on ADJX.REVERSEDGLTRANSACTIONID = JE.ID
    left join dbo.JOURNALENTRY ADJ on ADJ.ID = ADJX.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM ADJ_LI on ADJ_LI.ID = ADJ.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(JE.TRANSACTIONCURRENCYID) CURRENCYPROPERTIES  
    where BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID
    and (JEX.OUTDATED = 0 or (JEX.OUTDATED = 1 and ADJ_LI.POSTSTATUSCODE = 3))
    and JE.TYPECODE = case @SYSTEMDISTRIBUTION when 1 then 1 else 0 end
    and JEX.TABLENAMECODE in (1, 2)

    union all

    select
        FT.ID
        ,2 as [TYPECODE]
        ,isnull(JEX.DISTRIBUTIONTABLEID, JE.ID) as GLDISTRIBUTIONID
        ,'' as [DESCRIPTION]
        ,JE.TRANSACTIONTYPE
        ,GLACCOUNT.ACCOUNTNUMBER ACCOUNT
        ,JEX.PROJECT
        ,JE.BASEAMOUNT AMOUNT
        ,JE.COMMENT REFERENCE
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.TRANSACTIONAMOUNT, 0) else JE.TRANSACTIONAMOUNT end
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.BASEAMOUNT, 0) else JE.BASEAMOUNT end
        ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.ORGAMOUNT, 0) else JE.ORGAMOUNT end
        ,JE.TRANSACTIONCURRENCYID
        ,@BASECURRENCYID
        ,GLACCOUNT.ACCOUNTALIAS
        ,CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE
        ,CURRENCYPROPERTIES.CURRENCYSYMBOL
        ,CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
        ,CURRENCYPROPERTIES.DECIMALDIGITS
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.BASEAMOUNT  else 0 end as BASEDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.BASEAMOUNT  else 0 end as BASECREDITAMOUNT     
        ,@BASEISO4217 as BASEISO4217
        ,@BASECURRENCYSYMBOL as BASECURRENCYSYMBOL
        ,@BASESYMBOLDISPLAYSETTINGCODE as BASESYMBOLDISPLAYSETTINGCODE
        ,@BASEDECIMALDIGITS as BASEDECIMALDIGITS
        ,@ORGISO4217 as ORGISO4217
        ,@ORGCURRENCYSYMBOL as ORGCURRENCYSYMBOL
        ,@ORGSYMBOLDISPLAYSETTINGCODE as ORGSYMBOLDISPLAYSETTINGCODE
        ,@ORGDECIMALDIGITS as ORGDECIMALDIGITS
    from dbo.JOURNALENTRY JE
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
    inner join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT on BANKACCOUNTDEPOSITCORRECTION_EXT.ID = FT.ID
    inner join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(JE.TRANSACTIONCURRENCYID) CURRENCYPROPERTIES
    where FT.PARENTID = @DEPOSITID and JE.TYPECODE = (case @SYSTEMDISTRIBUTION when 1 then 1 else 0 end )and FT.TYPECODE in (24,25)

    union all

    select
        CREDITITEM.CREDITID as ID
        ,3 as [TYPECODE]
        ,isnull(JEX.DISTRIBUTIONTABLEID, JE.ID) as GLDISTRIBUTIONID
        ,'' as [DESCRIPTION]
        ,JE.TRANSACTIONTYPE
        ,isnull(GLACCOUNT.ACCOUNTNUMBER,'') as ACCOUNT
        ,''
        ,JE.BASEAMOUNT AMOUNT
        ,isnull(convert(nvarchar(255),JE.COMMENT),'') REFERENCE
        ,nullif(JE.TRANSACTIONAMOUNT, 0)
        ,nullif(JE.TRANSACTIONAMOUNT, 0)
        ,nullif(JE.TRANSACTIONAMOUNT, 0)
        ,@BASECURRENCYID
        ,@BASECURRENCYID
        ,GLACCOUNT.ACCOUNTALIAS
        ,CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE
        ,CURRENCYPROPERTIES.CURRENCYSYMBOL
        ,CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
        ,CURRENCYPROPERTIES.DECIMALDIGITS 
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.TRANSACTIONAMOUNT  else 0 end as TRANSACTIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.ORGAMOUNT  else 0 end as ORGANIZATIONCREDITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 0 then JE.BASEAMOUNT  else 0 end as BASEDEBITAMOUNT
        ,case JE.TRANSACTIONTYPECODE when 1 then JE.BASEAMOUNT  else 0 end as BASECREDITAMOUNT   
        ,@BASEISO4217 as BASEISO4217
        ,@BASECURRENCYSYMBOL as BASECURRENCYSYMBOL
        ,@BASESYMBOLDISPLAYSETTINGCODE as BASESYMBOLDISPLAYSETTINGCODE
        ,@BASEDECIMALDIGITS as BASEDECIMALDIGITS
        ,@ORGISO4217 as ORGISO4217
        ,@ORGCURRENCYSYMBOL as ORGCURRENCYSYMBOL
        ,@ORGSYMBOLDISPLAYSETTINGCODE as ORGSYMBOLDISPLAYSETTINGCODE
        ,@ORGDECIMALDIGITS as ORGDECIMALDIGITS
    from dbo.JOURNALENTRY JE
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.CREDITITEM_EXT CREDITITEM on CREDITITEM.ID = LI.ID
    inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on JEX.CREDITPAYMENTID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
    inner join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(isnull(JE.TRANSACTIONCURRENCYID, @BASECURRENCYID)) CURRENCYPROPERTIES  
    where BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = @DEPOSITID and JE.TYPECODE = case @SYSTEMDISTRIBUTION when 1 then 1 else 0 end

    select ID, TYPECODE, GLDISTRIBUTIONID, DESCRIPTION, TRANSACTIONTYPE, ACCOUNT, PROJECT, AMOUNT, REFERENCE, TRANSACTIONAMOUNT, BASEAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, ACCOUNTALIAS,ISO4217,CURRENCYSYMBOL,SYMBOLDISPLAYSETTINGCODE,DECIMALDIGITS,TRANSACTIONDEBITAMOUNT,TRANSACTIONCREDITAMOUNT,ORGANIZATIONDEBITAMOUNT,ORGANIZATIONCREDITAMOUNT,BASEDEBITAMOUNT,BASECREDITAMOUNT,BASEISO4217,BASECURRENCYSYMBOL,BASESYMBOLDISPLAYSETTINGCODE,BASEDECIMALDIGITS,ORGISO4217,ORGCURRENCYSYMBOL,ORGSYMBOLDISPLAYSETTINGCODE,ORGDECIMALDIGITS
    from @TempTbl
    order by TRANSACTIONTYPE desc, ACCOUNT asc;