USP_DATALIST_JOURNALENTRIES

CREATE procedure dbo.USP_DATALIST_JOURNALENTRIES
(
    @FINANCIALTRANSACTIONID uniqueidentifier
    ,@DETAILID uniqueidentifier = null
    ,@CURRENCYCODE tinyint = 0
    ,@POSTDATE date = null
)
as
    set nocount on;

    if @DETAILID = '00000000-0000-0000-0000-000000000000'
        set @DETAILID = null;

    --If we're coming from the order page, it sends in the order id

    if exists (select ID from dbo.SALESORDER where SALESORDER.ID = @FINANCIALTRANSACTIONID)
        select @FINANCIALTRANSACTIONID = REVENUEID from dbo.SALESORDER where SALESORDER.ID = @FINANCIALTRANSACTIONID

    declare @ORGANIZATIONCURRENCYID as uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    set @CURRENCYCODE = isnull(@CURRENCYCODE, 0);

    declare @BASECURRENCYID uniqueidentifier
    select @BASECURRENCYID = CS.BASECURRENCYID
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
    inner join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
    where FT.ID = @FINANCIALTRANSACTIONID;

    WITH DIST_CTE as (
        select 
            JE.ID
            ,LI.POSTDATE
            ,LI.POSTSTATUS
            ,LI.POSTSTATUSCODE
            ,A.ACCOUNTNUMBER
            ,A.ACCOUNTALIAS
            ,A.ACCOUNTDESCRIPTION
            ,case when JE.TRANSACTIONTYPECODE = 0 
                then case @CURRENCYCODE
                    when 0 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.TRANSACTIONAMOUNT , 0) else JE.TRANSACTIONAMOUNT end
                    when 1 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.BASEAMOUNT , 0) else JE.BASEAMOUNT end
                    else case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.ORGAMOUNT , 0) else JE.ORGAMOUNT end
                end
                else null 
            end [DEBITAMOUNT]
            ,case when JE.TRANSACTIONTYPECODE = 1
                then case @CURRENCYCODE
                    when 0 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.TRANSACTIONAMOUNT , 0) else JE.TRANSACTIONAMOUNT end
                    when 1 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.BASEAMOUNT , 0) else JE.BASEAMOUNT end
                    else case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.ORGAMOUNT , 0) else JE.ORGAMOUNT end
                end
                else null 
            end [CREDITAMOUNT]
            ,case when LI.TYPECODE = 1 then 'Reversal - ' else '' end + JE.COMMENT [COMMENT]
            ,case @CURRENCYCODE
                when 0 then JE.TRANSACTIONCURRENCYID 
                when 1 then @BASECURRENCYID
                else @ORGANIZATIONCURRENCYID end [CURRENCYID]
            ,case when LI.TYPECODE = 1 then 1 else 0 end [ISREVERSAL]
            ,case JE.TYPECODE
                when 0 then
                    case when isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) is null then 999
                    else case isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE)
                        when 0 then 1
                        when 5 then case isnull(ORIGINAL_JEX.BENEFITTYPECODE, JEX.BENEFITTYPECODE) 
                            when 1 then 5 
                            when 2 then 105 
                            when 3 then 205 
                            when 4 then 998 end
                        when 6 then case LI.TYPECODE when 5 then 6 when 1 then 6 else 106 end
                        else isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) end end
                else 997 end [TABLENAMECODE]
            ,JE.TRANSACTIONTYPECODE
            ,JE.GLACCOUNTID
            ,LI.DELETEDON
            ,LI.FINANCIALTRANSACTIONID
            ,RS.APPLICATIONCODE
            ,RS.TYPECODE RSTYPECODE
            ,FT.TYPECODE FTTYPECODE
            ,MT.DESCRIPTION MAPPINGTYPE
            ,FT.[DATE] FT_DATE
            ,case @CURRENCYCODE
                when 0 then FT.TRANSACTIONAMOUNT
                when 1 then FT.BASEAMOUNT
                else FT.ORGAMOUNT
             end [FT_AMOUNT]
            ,case @CURRENCYCODE
                when 0 then FT.TRANSACTIONCURRENCYID 
                when 1 then @BASECURRENCYID
                else @ORGANIZATIONCURRENCYID 
             end [FT_CURRENCYID]
            ,case when JEX.REVENUESPLITGIFTAIDID is not null 
                then
                    case when LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is not null
                        then FT.POSTSTATUSCODE
                        else LI.POSTSTATUSCODE
                    end
                else FT.POSTSTATUSCODE 
                end as [FT_POSTSTATUSCODE]
            ,LI.BATCHID
            ,isnull(JEX.REVENUESPLITGIFTAIDID, LI.ID) [FINANCIALTRANSACTIONLINEITEMID]
        from dbo.JOURNALENTRY JE
        inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.GLACCOUNT A on A.ID = JE.GLACCOUNTID
        left join dbo.REVENUESPLIT_EXT RS on RS.ID = isnull(LI.REVERSEDLINEITEMID, LI.ID)
        left join dbo.JOURNALENTRY_EXT ORIGINAL_JEX on JEX.REVERSEDGLTRANSACTIONID = ORIGINAL_JEX.ID
        left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MT on MT.ID = isnull(ORIGINAL_JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID, JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID)
        where LI.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and LI.POSTSTATUSCODE != 3
            and (LI.POSTDATE = isnull(@POSTDATE, LI.POSTDATE))
            and (LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = isnull(@DETAILID, LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID)
                or (LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null and @DETAILID is null)
                or (@DETAILID = @FINANCIALTRANSACTIONID and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null))

        union all

        select 
            JE.ID
            ,LI.POSTDATE
            ,LI.POSTSTATUS
            ,LI.POSTSTATUSCODE
            ,A.ACCOUNTNUMBER
            ,A.ACCOUNTALIAS
            ,A.ACCOUNTDESCRIPTION
            ,case when JE.TRANSACTIONTYPECODE = 0 
                then case @CURRENCYCODE
                    when 0 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.TRANSACTIONAMOUNT , 0) else JE.TRANSACTIONAMOUNT end
                    when 1 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.BASEAMOUNT , 0) else JE.BASEAMOUNT end
                    else case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.ORGAMOUNT , 0) else JE.ORGAMOUNT end
                end
                else null 
            end [DEBITAMOUNT]
            ,case when JE.TRANSACTIONTYPECODE = 1
                then case @CURRENCYCODE
                    when 0 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.TRANSACTIONAMOUNT , 0) else JE.TRANSACTIONAMOUNT end
                    when 1 then case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.BASEAMOUNT , 0) else JE.BASEAMOUNT end
                    else case when JE.TRANSACTIONCURRENCYID is null and JE.TYPECODE = 1 then nullif(JE.ORGAMOUNT , 0) else JE.ORGAMOUNT end
                end
                else null 
            end [CREDITAMOUNT]
            ,case when LI.TYPECODE = 1 then 'Reversal - ' else '' end + JE.COMMENT [COMMENT]
            ,case @CURRENCYCODE
                when 0 then JE.TRANSACTIONCURRENCYID 
                when 1 then @BASECURRENCYID
                else @ORGANIZATIONCURRENCYID end [CURRENCYID]
            ,case when LI.TYPECODE = 1 then 1 else 0 end [ISREVERSAL]
            ,case JE.TYPECODE
                when 0 then
                    case when isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) is null then 999
                    else case isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE)
                        when 0 then 1
                        when 5 then case isnull(ORIGINAL_JEX.BENEFITTYPECODE, JEX.BENEFITTYPECODE) 
                            when 1 then 5 
                            when 2 then 105 
                            when 3 then 205 
                            when 4 then 998 end
                        when 6 then case LI.TYPECODE when 5 then 6 when 1 then 6 else 106 end
                        else isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) end end
                else 997 end [TABLENAMECODE]
            ,JE.TRANSACTIONTYPECODE
            ,JE.GLACCOUNTID
            ,LI.DELETEDON
            ,LI.FINANCIALTRANSACTIONID
            ,RS.APPLICATIONCODE
            ,RS.TYPECODE RSTYPECODE
            ,FT.TYPECODE FTTYPECODE
            ,MT.DESCRIPTION MAPPINGTYPE
            ,FT.[DATE] FT_DATE
            ,case @CURRENCYCODE
                when 0 then FT.TRANSACTIONAMOUNT
                when 1 then FT.BASEAMOUNT
                else FT.ORGAMOUNT
             end [FT_AMOUNT]
            ,case @CURRENCYCODE
                when 0 then FT.TRANSACTIONCURRENCYID 
                when 1 then @BASECURRENCYID
                else @ORGANIZATIONCURRENCYID 
             end [FT_CURRENCYID]
            ,FT.POSTSTATUSCODE [FT_POSTSTATUSCODE]
            ,LI.BATCHID
            ,isnull(JEX.REVENUESPLITGIFTAIDID, LI.ID) [FINANCIALTRANSACTIONLINEITEMID]
        from dbo.JOURNALENTRY JE
        inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.GLACCOUNT A on A.ID = JE.GLACCOUNTID
        left join dbo.REVENUESPLIT_EXT RS on RS.ID = isnull(LI.REVERSEDLINEITEMID, LI.ID)
        left join dbo.JOURNALENTRY_EXT ORIGINAL_JEX on JEX.REVERSEDGLTRANSACTIONID = ORIGINAL_JEX.ID
        left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MT on MT.ID = isnull(ORIGINAL_JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID, JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID)
        where FT.PARENTID = @FINANCIALTRANSACTIONID and LI.POSTSTATUSCODE != 3 and FT.TYPECODE != 23
            and (LI.POSTDATE = isnull(@POSTDATE, LI.POSTDATE))
            and (LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = isnull(@DETAILID, LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID)
                or (LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null and @DETAILID is null)
                or (@DETAILID = FT.ID and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null)))

    select T.ID
        ,T.POSTDATE
        ,T.POSTSTATUS
        ,isnull(nullif(T.ACCOUNTALIAS, ''), T.ACCOUNTNUMBER) ACCOUNTNUMBER
        ,T.ACCOUNTALIAS
        ,T.ACCOUNTDESCRIPTION
        ,T.DEBITAMOUNT
        ,T.CREDITAMOUNT
        ,T.COMMENT
        ,T.CURRENCYID
        ,T.ISREVERSAL
        ,T.TABLENAMECODE
        ,case when T.DELETEDON is null then 0 else 1 end DELETED
        ,T.TABLENAMECODE ACTUAL_TABLENAMECODE
        ,T.FINANCIALTRANSACTIONID
        ,T.TRANSACTIONTYPECODE
        ,T.APPLICATIONCODE
        ,T.RSTYPECODE
        ,T.FTTYPECODE
        ,T.MAPPINGTYPE
        ,BATCH.BATCHNUMBER BATCHID
        ,T.FT_DATE
        ,T.FT_AMOUNT
        ,T.FT_CURRENCYID
        ,T.FT_POSTSTATUSCODE
        ,T.FINANCIALTRANSACTIONLINEITEMID
    from DIST_CTE T
    left join dbo.BATCH on T.BATCHID = BATCH.ID
    order by T.POSTDATE desc, T.DELETEDON, T.ISREVERSAL, T.TRANSACTIONTYPECODE, isnull(nullif(T.ACCOUNTALIAS, ''), T.ACCOUNTNUMBER), T.DEBITAMOUNT, T.CREDITAMOUNT asc