UFN_REVENUEBATCH_GETENHANCEDMATCHINGGIFTS_2

CREATE function dbo.UFN_REVENUEBATCH_GETENHANCEDMATCHINGGIFTS_2
(
    @BATCHREVENUEID uniqueidentifier
)
returns table
as 
return
(
    select
        BATCHREVENUEENHANCEDMATCHINGGIFTS.ID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGCONSTITUENTID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.ORGANIZATIONID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.RELATIONSHIPID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGGIFTCONDITIONID,        
        BATCHREVENUEENHANCEDMATCHINGGIFTS.AMOUNT,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.DATE,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.COULDNOTDEFAULTMATCHINGGIFT,
        case 
            when (select count(*) from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTSPLIT where BATCHREVENUEENHANCEDMATCHINGGIFTID = BATCHREVENUEENHANCEDMATCHINGGIFTS.ID) <> 1 
                then null
            else (select DESIGNATIONID from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTSPLIT where BATCHREVENUEENHANCEDMATCHINGGIFTID = BATCHREVENUEENHANCEDMATCHINGGIFTS.ID)
        end as SINGLEDESIGNATIONID,
        (
            select
                ID,
                DESIGNATIONID,
                AMOUNT,
                SEQUENCE,
                REVENUESPLITID
            from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTSPLIT
            where BATCHREVENUEENHANCEDMATCHINGGIFTID = BATCHREVENUEENHANCEDMATCHINGGIFTS.ID
            for xml raw('ITEM'),type,elements,BINARY BASE64
        ) as SPLITS,        
        cast(BATCHREVENUEENHANCEDMATCHINGGIFTS.ORGANIZATIONID as nvarchar(36)) + cast(BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGCONSTITUENTID as nvarchar(36)) + '1' as RELATIONSHIPCONTEXTID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.REVENUEMATCHINGGIFTID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.TRANSACTIONCURRENCYID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.BASEEXCHANGERATEID,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.EXCHANGERATE,
        BATCHREVENUEENHANCEDMATCHINGGIFTS.APPLYBYPERCENT,
        BATCHREVENUE.BASECURRENCYID,
        case
            when exists
                (select 1
                    from dbo.INSTALLMENTSPLITPAYMENT 
                    where
                        INSTALLMENTSPLITPAYMENT.PLEDGEID = BATCHREVENUEENHANCEDMATCHINGGIFTS.REVENUEMATCHINGGIFTID
                        and INSTALLMENTSPLITPAYMENT.ID is not null
                ) 
                then 1
            else 0
                 end as HASPAIDMATCHINGGIFT,
    BATCHREVENUEENHANCEDMATCHINGGIFTS.OPPORTUNITYID,
    BATCHREVENUEENHANCEDMATCHINGGIFTS.RESETSOLICITORCREDITS
    from 
        dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
        inner join dbo.BATCHREVENUE on BATCHREVENUEENHANCEDMATCHINGGIFTS.BATCHREVENUEID = BATCHREVENUE.ID
    where 
        BATCHREVENUEID = @BATCHREVENUEID
)