UFN_SITEID_MAPFROM_DESIGNATIONID

        CREATE function dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID uniqueidentifier) 
        returns uniqueidentifier
        as
        begin
            -- Note: Update UFN_SITEID_MAPFROM_REVENUESPLITID if the below logic is changed


            declare @ID uniqueidentifier;

            select @ID = 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 = @DESIGNATIONID

            return @ID;

        end