UFN_JOURNALENTRY_GETWRITEOFFGLDISTRIBUTION

create function dbo.[UFN_JOURNALENTRY_GETWRITEOFFGLDISTRIBUTION](@WRITEOFFID uniqueidentifier)
returns table
as return
(
        select 
            JOURNALENT.FINANCIALTRANSACTIONLINEITEMID,
            JOURNALENT.ID,
            @WRITEOFFID as WRITEOFFID,
            isnull(JOURNALENT.GLPAYMENTMETHODREVENUETYPEMAPPINGID,'00000000-0000-0000-0000-000000000000') as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            JOURNALENT.PROJECT,
            JOURNALENT.REFERENCE,
            JOURNALENT.AMOUNT,
            JOURNALENT.ADDEDBYID,
            JOURNALENT.CHANGEDBYID,
            JOURNALENT.DATEADDED,
            JOURNALENT.DATECHANGED,
            JOURNALENT.TSLONG,
            isnull(coalesce(JOURNALENT.ACCOUNT,''),'') as ACCOUNT,
            isnull(JOURNALENT.REVENUEID, FINANCIALTRANS.REVENUEID) as REVENUEID,    
            JOURNALENT.TRANSACTIONTYPECODE,
            JOURNALENT.ID as GLTRANSACTIONID,
            JOURNALENT.OUTDATED,
            JOURNALENT.TRANSACTIONTYPE,
            FINANCIALTRANS.BASECURRENCYID,
            JOURNALENT.ORGANIZATIONAMOUNT as ORGANIZATIONAMOUNT,
            FINANCIALTRANS.ORGANIZATIONEXCHANGERATEID,
            JOURNALENT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT, 
            JOURNALENT.TRANSACTIONCURRENCYID,
            FINANCIALTRANS.BASEEXCHANGERATEID
        from
        (    
            select 
                FINANCIALTRANSACTIONLINEITEMID,
                JOURNALENTRY_EXT.GLTRANSACTIONID as ID,
                JOURNALENTRY_EXT.WRITEOFFID as WRITEOFFID,
                isnull(JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID,'00000000-0000-0000-0000-000000000000') as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                JOURNALENTRY_EXT.PROJECT,
                JOURNALENTRY.COMMENT as REFERENCE,
                JOURNALENTRY.BASEAMOUNT as AMOUNT,
                JOURNALENTRY_EXT.ADDEDBYID,
                JOURNALENTRY_EXT.CHANGEDBYID,
                JOURNALENTRY_EXT.DATEADDED,
                JOURNALENTRY_EXT.DATECHANGED,
                JOURNALENTRY_EXT.TSLONG,
                isnull(coalesce(JOURNALENTRY_EXT.ACCOUNT,''),'') as ACCOUNT,
                JOURNALENTRY_EXT.LOGICALREVENUEID as REVENUEID,    
                JOURNALENTRY.TRANSACTIONTYPECODE,
                JOURNALENTRY.ID as GLTRANSACTIONID,
                JOURNALENTRY_EXT.OUTDATED,
                case JOURNALENTRY.TRANSACTIONTYPECODE
                when 0 then N'Debit' 
                when 1 then N'Credit' 
                end as TRANSACTIONTYPE,
                JOURNALENTRY.ORGAMOUNT as ORGANIZATIONAMOUNT,
                JOURNALENTRY.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT, 
                JOURNALENTRY.TRANSACTIONCURRENCYID
            from dbo.JOURNALENTRY 
                inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 12
            where JOURNALENTRY_EXT.WRITEOFFID = @WRITEOFFID AND JOURNALENTRY_EXT.OUTDATED = 0
        ) JOURNALENT
        inner join
        (
            select 
                FINANCIALTRANSACTIONLINEITEM.ID as FINANCIALTRANSACTIONLINEITEMID,
                FINANCIALTRANSACTION.ID as WRITEOFFID,
                FINANCIALTRANSACTION.PARENTID as REVENUEID,    
                case when CURRENCYSET.BASECURRENCYID = '00000000-0000-0000-0000-000000000000' then null else CURRENCYSET.BASECURRENCYID end BASECURRENCYID,
                FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
                FINANCIALTRANSACTION.BASEEXCHANGERATEID
            from  dbo.FINANCIALTRANSACTION 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            where FINANCIALTRANSACTION.ID = @WRITEOFFID
        ) FINANCIALTRANS on JOURNALENT.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANS.FINANCIALTRANSACTIONLINEITEMID 
)