UFN_REVENUE_GENERATEWRITEOFFGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATEWRITEOFFGLDISTRIBUTION(@REVENUEID uniqueidentifier = null)
            returns table 
            as return 
            /*    Getting Pledge Write-off distributions    */
            select distinct
                REVENUE.ID as REVENUEID, 
                SPLIT.ID as REVENUESPLITID, 
                WRITEOFF.ID AS WRITEOFFID, 
                dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(205, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                tf.TRANSACTIONTYPECODE, 
                case when WRITEOFFADJUSTMENT.ID is null then WRITEOFF.POSTDATE            --if no unposted adjustment then use write-off post date

                     else WRITEOFFADJUSTMENT.POSTDATE
                end AS POSTDATE,     
                case when WRITEOFFADJUSTMENT.ID is null then WRITEOFF.POSTSTATUSCODE            --if no unposted adjustment then use write-off post status code

                     else WRITEOFFADJUSTMENT.POSTSTATUSCODE
                end AS POSTSTATUSCODE,
                tf.ACCOUNTSTRING, 
                tf.PROJECTCODE as PROJECT, 
                SPLIT.AMOUNT as AMOUNT,
                case REVENUE.TRANSACTIONTYPECODE
                    when 1 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'write-off', 'Pledge') 
                    when 6 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'write-off', 'Grant award') 
                    when 7 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'write-off', 'Auction donation')
                end as REFERENCE,
                tf.ERRORMESSAGE,
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                tf.ACCOUNTID as ACCOUNTID, 
                SPLIT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT, 
                SPLIT.ORGANIZATIONAMOUNT as ORGANIZATIONAMOUNT, 
                SPLIT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID, 
                SPLIT.BASECURRENCYID as BASECURRENCYID, 
                SPLIT.BASEEXCHANGERATEID as BASEEXCHANGERATEID, 
                SPLIT.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
                convert(varchar(max),tf.MAPPEDVALUES) as MAPPEDVALUES
            from dbo.REVENUE with (nolock)
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.WRITEOFF with (nolock) on REVENUE.ID = WRITEOFF.REVENUEID 
                inner join dbo.WRITEOFFSPLIT as SPLIT with (nolock) on WRITEOFF.ID = SPLIT.WRITEOFFID     
                left join dbo.WRITEOFFADJUSTMENT with (nolock) on WRITEOFF.ID = WRITEOFFADJUSTMENT.WRITEOFFID and WRITEOFFADJUSTMENT.POSTSTATUSCODE <> 0                        
                cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, 205, SPLIT.DESIGNATIONID) as tf 
            where    
                (REVENUE.TRANSACTIONTYPECODE in (1,6,7)) -- is a pledge or grant award or auction donation

                and (REVENUE.ID = @REVENUEID or @REVENUEID is NULL)