UFN_REVENUEUPDATEBATCH_GETMATCHINGGIFTS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETMATCHINGGIFTS(@REVENUEID uniqueidentifier)
    returns table
    as return
    (
        select
            '00000000-0000-0000-0000-000000000000' as ID, 
            SOURCEREVENUE.CONSTITUENTID as MATCHINGCONSTITUENTID, 
            MGREVENUE.CONSTITUENTID as ORGANIZATIONID, 
            REVENUEMATCHINGGIFT.RELATIONSHIPID,  
            '' as RELATIONSHIPCONTEXTID,
            REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID,
            MGREVENUE.TRANSACTIONAMOUNT as AMOUNT, 
            MGREVENUE.DATE,
            case 
                when (select count(*) from dbo.REVENUESPLIT where REVENUEID = MGREVENUE.ID) <> 1 then null
                else (select DESIGNATIONID from dbo.REVENUESPLIT where REVENUEID = MGREVENUE.ID)
            end as SINGLEDESIGNATIONID,
            (
                select
                    '00000000-0000-0000-0000-000000000000' as ID,
                    DESIGNATIONID,
                    TRANSACTIONAMOUNT as AMOUNT,
                    ROW_NUMBER() OVER(order by dateadded) as SEQUENCE,
                    ID as REVENUESPLITID,
                    TRANSACTIONCURRENCYID
                from dbo.REVENUESPLIT
                where REVENUEID = MGREVENUE.ID
                for xml raw('ITEM'),type,elements,BINARY BASE64
            ) as SPLITS,
            0 as COULDNOTDEFAULTMATCHINGGIFT,
            REVENUEMATCHINGGIFT.ID as REVENUEMATCHINGGIFTID,
            MGREVENUE.BASECURRENCYID,
            MGREVENUE.TRANSACTIONCURRENCYID,
            MGREVENUE.BASEEXCHANGERATEID,
            case
                when MGREVENUE.BASEEXCHANGERATEID is not null
                    then CURRENCYEXCHANGERATE.RATE
                when MGREVENUE.TRANSACTIONCURRENCYID = MGREVENUE.BASECURRENCYID
                    then 1
                else 0
            end EXCHANGERATE,
            SOURCEREVENUE.TRANSACTIONCURRENCYID as ORIGINALGIFTTRANSACTIONCURRENCYID,
            0 as APPLYBYPERCENT,
            case
                when exists
                    (select 1
                        from dbo.INSTALLMENTSPLITPAYMENT 
                        where
                            INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEMATCHINGGIFT.ID
                            and INSTALLMENTSPLITPAYMENT.ID is not null
                    )
                    then 1
                else 0
            end as HASPAIDMATCHINGGIFT,
      (
        select top 1
          REVENUEOPPORTUNITY.OPPORTUNITYID
        from
          dbo.REVENUEOPPORTUNITY
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUEOPPORTUNITY.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where
          FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = MGREVENUE.ID
      ) as OPPORTUNITYID
        from
            dbo.REVENUEMATCHINGGIFT
            inner join dbo.REVENUE SOURCEREVENUE on SOURCEREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
            inner join dbo.REVENUE MGREVENUE on MGREVENUE.ID = REVENUEMATCHINGGIFT.ID
            left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = MGREVENUE.BASEEXCHANGERATEID
        where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @REVENUEID
    )