UFN_REVENUEUPDATEBATCH_GETADDITIONALAPPLICATIONS

            CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETADDITIONALAPPLICATIONS(@REVENUEID uniqueidentifier)
            returns table
            as
            return
            (
            select
                '00000000-0000-0000-0000-000000000000' as ID, 
                case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 0 then 0 --donation

                    when REVENUESPLIT.APPLICATIONCODE = 4 and REVENUESPLIT.TYPECODE = 4 then 1 --other

                    when REVENUESPLIT.APPLICATIONCODE = 7 and REVENUESPLIT.TYPECODE = 0 then 2 --matching gift claim

                    when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 17 then 3 --sponsorship additional gift

                end as TYPECODE,
                REVENUESPLIT.TRANSACTIONAMOUNT as APPLIED,
                DESIGNATIONID, 
                OTHERTYPECODEID,
                coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID, 0) as DECLINESGIFTAID,
                OPPORTUNITYID,
                case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 17 then 
                    REVENUESPLIT.ID
                   --WI 194341/208872 We are allowing more than one additional sponsorship donation.  I don't think the below will work

                   -- Instead we can track using the revenue split id in revenue update batch.

                    --(case when (select count(*) from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) <> 1 then null

                     --else (select ID from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) end)

                end SPONSORSHIPID,
                coalesce(REVENUESPLITGIFTAID.ISSPONSORSHIP, 0) as ISGIFTAIDSPONSORSHIP,
                REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID as CATEGORYCODEID,
                cast(1 as bit) as DIDCAMPAIGNSDEFAULT,
                (select
                    '00000000-0000-0000-0000-000000000000' as ID,
                    CAMPAIGNID,
                    CAMPAIGNSUBPRIORITYID,
                    REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID   
                from dbo.REVENUESPLITCAMPAIGN
                where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
                REVENUESPLIT.ID as REVENUESPLITID,
                REVENUESPLIT.TRANSACTIONCURRENCYID,
                case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end as GIFTAIDCOMMITTED
            from dbo.REVENUESPLIT
                inner join dbo.REVENUE 
                    on REVENUE.ID = REVENUESPLIT.REVENUEID
                left outer join dbo.REVENUECATEGORY
                    on REVENUESPLIT.ID = REVENUECATEGORY.ID
                left outer join dbo.REVENUEOPPORTUNITY 
                    on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
                left outer join dbo.REVENUESPLITOTHER 
                    on REVENUESPLITOTHER.ID = REVENUESPLIT.ID
                left outer join dbo.INSTALLMENTSPLITPAYMENT 
                    on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                left outer join dbo.REVENUESPLITGIFTAID
                    on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
            where REVENUE.ID = @REVENUEID
                    and APPLICATIONCODE in (0,4,7)
                    and TYPECODE in (0,4,17)
                    and INSTALLMENTSPLITPAYMENT.ID is null                     
        )