UFN_REVENUEUPDATEBATCH_GETAPPLICATIONINFO

  CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONINFO
    (
     @ID uniqueidentifier
    )
    returns nvarchar(60)
    with execute as caller
    as
        begin

        declare @REVENUESPLITID uniqueidentifier;
        declare @APPLICATIONINFO nvarchar(60);

        select @REVENUESPLITID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1  then null
                                 else (select ID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) end
        from dbo.REVENUE R 
        where R.ID = @ID
        and R.TRANSACTIONTYPECODE = 0

        if @REVENUESPLITID is null 
            begin
                return null;
            end
        else  --look at the split table for the type of payment

            begin
                select @APPLICATIONINFO = 
                    case 
                        when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 0 then '0:0' --Donation

                        when REVENUESPLIT.APPLICATIONCODE = 4 and REVENUESPLIT.TYPECODE = 4 then '0:1' --Other

                        when REVENUESPLIT.APPLICATIONCODE = 7 and REVENUESPLIT.TYPECODE = 0 then 
                        isnull (
                            (
                                select top 1 cast(INSTALLMENTSPLITPAYMENT.PLEDGEID as varchar(36))
                                from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = REVENUESPLIT.ID
                            ), '0:2' 
                        ) --Applied/Unapplied MG Payment 

                        when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 17 then '0:3'--Sponsorship additional donation

                        when REVENUESPLIT.APPLICATIONCODE in (2, 6, 8, 13) and REVENUESPLIT.TYPECODE in (0, 3) then (
                            select top 1 cast(INSTALLMENTSPLITPAYMENT.PLEDGEID as varchar(36))
                            from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = REVENUESPLIT.ID
                        ) --Pledge, planned gift, grant award                                                                                                                          

                        when REVENUESPLIT.APPLICATIONCODE = 3 and REVENUESPLIT.TYPECODE in (0,9,17) then (
                            select cast(REVENUE.ID as varchar(36)) 
                            from dbo.REVENUE                                                                                                                              
                                inner join dbo.RECURRINGGIFTACTIVITY on REVENUE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID                                                                                                
                            where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVENUESPLIT.ID) --RG/Sponsorship RG/ Sponsorship recurring additional gift                                                                                                                

                        when REVENUESPLIT.APPLICATIONCODE = 5 and REVENUESPLIT.TYPECODE = 2 then (
                            select cast(MEMBERSHIPTRANSACTION.MEMBERSHIPID as varchar(36))
                            from dbo.MEMBERSHIPTRANSACTION 
                                inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                            where REVENUESPLITID = REVENUESPLIT.ID
                        )                                         
                        when REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 1 then (
                            select cast(EVENTREGISTRANTPAYMENT.REGISTRANTID as varchar(36))
                            from dbo.EVENTREGISTRANTPAYMENT
                            where EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
                        )                                                                                                                                                      
                    end
                from REVENUESPLIT
                where ID = @REVENUESPLITID            
            end

        return @APPLICATIONINFO    
    end