UFN_CONSTITUENTRECOGNITION_VALIDLEVEL

            CREATE function dbo.UFN_CONSTITUENTRECOGNITION_VALIDLEVEL
                (@ID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,
                @RECOGNITIONLEVELID uniqueidentifier,
                @EXPIRATIONDATE datetime = null
                )
            returns bit
            with execute as caller
            as begin
                if exists
                    (
                    select CR.ID
                    from dbo.CONSTITUENTRECOGNITION CR
                    inner join dbo.RECOGNITIONPROGRAM on CR.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
                    where CR.ID <> @ID
                        and CR.CONSTITUENTID = @CONSTITUENTID
                        and CR.RECOGNITIONLEVELID = @RECOGNITIONLEVELID
                        and CR.STATUSCODE not in (1, 3) -- not dropped or inactive (merge)

                        and (RECOGNITIONPROGRAM.TYPECODE = 1 or year(@EXPIRATIONDATE) = year(CR.EXPIRATIONDATE))
                    ) return 0;

                return 1;
            end