UFN_REVENUEUPDATEBATCH_GETSOLICITORS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORS(@REVENUEID uniqueidentifier)
returns table
as
return
(
    select 
        case when REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE not in (0,4) then REVENUESPLIT.ID else null end as [APPLICATIONID],
        255 as [APPLICATIONTYPECODE],
        case 
            when REVENUE.TRANSACTIONTYPECODE = 0 then 
                (case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 0 then 0
                    when REVENUESPLIT.APPLICATIONCODE = 4 and REVENUESPLIT.TYPECODE = 4 then 1
                    when REVENUESPLIT.APPLICATIONCODE = 7 and REVENUESPLIT.TYPECODE = 0 then 2
                    when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 17 then 3 
                    else 255 end)
            else 0
        end as [ADDITIONALAPPLICATIONTYPECODE],
        REVENUESPLIT.DESIGNATIONID as [ADDITIONALAPPLICATIONDESIGNATIONID], 
        REVENUESPLIT.AMOUNT as [APPLICATIONAMOUNT],
        '' as [APPLICATIONDESCRIPTION],
        dbo.UFN_REVENUEUPDATEBATCH_GETCOLLECTIONDESCRIPTION(REVENUESPLIT.ID, 0) as [COLLECTIONDESCRIPTION],
        (
            select
                '00000000-0000-0000-0000-000000000000' as ID,
                CONSTITUENTID,
                AMOUNT,
                ID as REVENUESOLICITORID,
                BASECURRENCYID
            from dbo.REVENUESOLICITOR
            where REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
                for xml raw('ITEM'),type,elements,BINARY BASE64
        ) as [SOLICITORS],
        null as [ADDITIONALAPPLICATIONOPPORTUNITYID],
        REVENUESPLIT.BASECURRENCYID as [BASECURRENCYID],
        coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID, 0) as [ADDITIONALAPPLICATIONDECLINESGIFTAID],
        case 
            when  REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 17 then REVENUESPLIT.ID
            else null
        end as [ADDITIONALAPPLICATIONSPONSORSHIPID]
    from 
        dbo.REVENUE
        inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID 
        left outer join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
        left outer join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
        left outer join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
    where 
        REVENUE.ID = @REVENUEID
)