USP_REPORT_DEPOSITACCOUNTDISTRIBUTION

create procedure dbo.USP_REPORT_DEPOSITACCOUNTDISTRIBUTION
(
    @DEPOSITID as uniqueidentifier
)
with execute as owner

as
begin
    set nocount on;

    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;

    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 (
        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 [TRANSACTIONAMOUNT]
            ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.BASEAMOUNT, 0) else JE.BASEAMOUNT end [BASEAMOUNT]
            ,case when JE.TRANSACTIONCURRENCYID is null then nullif(JE.ORGAMOUNT, 0) else JE.ORGAMOUNT end [ORGANIZATIONAMOUNT]
            ,JE.TRANSACTIONCURRENCYID
            ,@BASECURRENCYID [BASECURRENCYID]
            ,GLACCOUNT.ACCOUNTALIAS
            ,CURRENCYPROPERTIES.ISO4217
            ,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.BANKACCOUNTDEPOSITPAYMENT 
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on BANKACCOUNTDEPOSITPAYMENT.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.JOURNALENTRY JE on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
        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 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.BANKACCOUNTDEPOSITCORRECTION_EXT
        inner join dbo.FINANCIALTRANSACTION FT on BANKACCOUNTDEPOSITCORRECTION_EXT.ID = FT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.JOURNALENTRY JE on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
        inner join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(JE.TRANSACTIONCURRENCYID) CURRENCYPROPERTIES
        where FT.PARENTID = @DEPOSITID 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.CREDITITEM_EXT CREDITITEM
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on CREDITITEM.ID = LI.ID
        inner join dbo.JOURNALENTRY JE on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.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) T
    order by TRANSACTIONTYPE desc, ACCOUNT asc;
          end;