UFN_SITEID_MAPFROM_REVENUESPLITID

            CREATE function dbo.UFN_SITEID_MAPFROM_REVENUESPLITID
            (
                @REVENUESPLITID uniqueidentifier
            )
            returns table
            as
            return 
            (

                /*This has been purposely condensed to one sql statement so that it will be an inline function*/
                /*Note that the joins short circuit based on application code*/

                select case 
                        when REVENUESPLIT_EXT.APPLICATIONCODE=1 then EVENTSITE.SITEID
                        when REVENUESPLIT_EXT.APPLICATIONCODE=5 then MEMBERSHIPPROGRAM.SITEID
            when REVENUESPLIT_EXT.APPLICATIONCODE=3 and REVENUESPLIT_EXT.TYPECODE = 2 then  (select top 1 MEMBERSHIPPROGRAM.SITEID 
                                                                                             from dbo.RECURRINGGIFTACTIVITY 
                                                                                             inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
                                                                                             inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                                                                             inner join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                                                                                             inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                                                                                                       inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                                                                             where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = @REVENUESPLITID)
            when REVENUESPLIT_EXT.APPLICATIONCODE=18 then (select top 1 MEMBERSHIPPROGRAM.SITEID 
                                                           from dbo.MEMBERSHIPADDON
                                                                     inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPADDON.MEMBERSHIPID
                                                           inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                                                           inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                                           where MEMBERSHIPADDON.REVENUESPLITID = @REVENUESPLITID)
                        when REVENUESPLIT_EXT.APPLICATIONCODE = 19 then (select top 1 MEMBERSHIPPROGRAM.SITEID
                                                                            from dbo.INSTALLMENTSPLITPAYMENT
                                                                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                                                            inner join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                                                                            inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                                                                            inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                                                            where INSTALLMENTSPLITPAYMENT.PAYMENTID = @REVENUESPLITID)
                        when REVENUESPLIT_EXT.APPLICATIONCODE=11 then null    
                        else    
                        (    
                            -- Note: Update UFN_SITEID_MAPFROM_DESIGNATIONID if the below logic is changed

                            select top 1 coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)    
                            from dbo.DESIGNATION     
                            left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID    
                            left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID    
                            left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID    
                            left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID    
                            left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID    
                            where DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID          
                        )    
                        end as [SITEID]
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    left outer join dbo.EVENTREGISTRANTPAYMENT on REVENUESPLIT_EXT.APPLICATIONCODE = 1 and EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                    left outer join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
                    left outer join dbo.EVENTSITE on REGISTRANT.EVENTID = EVENTSITE.EVENTID
                    left outer join dbo.MEMBERSHIPTRANSACTION on REVENUESPLIT_EXT.APPLICATIONCODE = 5 and FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    left outer join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    left outer join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID

                union all

                --Return all sites assigned to the account system for misc payments.

                select S.SITEID
                from dbo.FINANCIALTRANSACTIONLINEITEM LI
                inner join dbo.REVENUESPLIT_EXT EXT on EXT.ID = LI.ID
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                inner join dbo.PDACCOUNTSYSTEMSITE S on S.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
                where EXT.APPLICATIONCODE = 11 and LI.ID = @REVENUESPLITID

            )