UFN_PDACCOUNT_GETSITEID_3

            CREATE function dbo.UFN_PDACCOUNT_GETSITEID_3
            (                
                @REVENUESPLITID uniqueidentifier,
                @APPLICATIONCODE tinyint,
                @REVENUESPLITINFORMATION xml
            ) returns uniqueidentifier
            with execute as caller
            as begin

                declare @RESULT as uniqueidentifier = null;

                if @APPLICATIONCODE = 1 -- Event Registration        

                    select TOP 1 @RESULT =  EVENTSITE.SITEID 
                    from dbo.UFN_REVENUESPLITINFORMATION_FROMITEMLISTXML(@REVENUESPLITINFORMATION) as REVENUESPLITINFORMATION
                        inner join dbo.EVENTSITE
                            on REVENUESPLITINFORMATION.EVENTID = EVENTSITE.EVENTID
                    where REVENUESPLITINFORMATION.ID = @REVENUESPLITID
                    order by EVENTSITE.DATEADDED desc;

                else if @APPLICATIONCODE in (5, 19)    -- Membership    

                    select @RESULT = MEMBERSHIPPROGRAM.SITEID 
                    from dbo.UFN_REVENUESPLITINFORMATION_FROMITEMLISTXML(@REVENUESPLITINFORMATION) as REVENUESPLITINFORMATION
                        inner join MEMBERSHIPLEVEL
                            on REVENUESPLITINFORMATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                        inner join MEMBERSHIPPROGRAM
                            on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID 
                    where REVENUESPLITINFORMATION.ID =  @REVENUESPLITID;

                if (@RESULT is null)
                begin
                    select @RESULT = isnull(DESIGNATIONLEVEL5.SITEID, 
                                        isnull(DESIGNATIONLEVEL4.SITEID, 
                                            isnull(DESIGNATIONLEVEL3.SITEID, 
                                                isnull(DESIGNATIONLEVEL2.SITEID, DESIGNATIONLEVEL1.SITEID))))
                    from dbo.UFN_REVENUESPLITINFORMATION_FROMITEMLISTXML(@REVENUESPLITINFORMATION) as REVENUESPLITINFORMATION
                        inner join DESIGNATION
                            on DESIGNATION.ID = REVENUESPLITINFORMATION.DESIGNATIONID
                        left outer join DESIGNATIONLEVEL as DESIGNATIONLEVEL1
                            on DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL1.ID
                        left outer join DESIGNATIONLEVEL as DESIGNATIONLEVEL2
                            on DESIGNATION.DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL2.ID
                        left outer join DESIGNATIONLEVEL as DESIGNATIONLEVEL3
                            on DESIGNATION.DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL3.ID
                        left outer join DESIGNATIONLEVEL as DESIGNATIONLEVEL4
                            on DESIGNATION.DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL4.ID
                        left outer join DESIGNATIONLEVEL as DESIGNATIONLEVEL5
                            on DESIGNATION.DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL5.ID
                    where REVENUESPLITINFORMATION.ID = @REVENUESPLITID;    
                end

                return @RESULT;
            end