UFN_PLANNEDGIFTADDITIONDESIGNATION_ISUNIQUE

        create function dbo.UFN_PLANNEDGIFTADDITIONDESIGNATION_ISUNIQUE
        (
            @PLANNEDGIFTADDITIONID uniqueidentifier,
            @DESIGNATIONID uniqueidentifier,
            @CATEGORYCODEID uniqueidentifier,
            @USECODEID uniqueidentifier,
            @DATE datetime
        )
        returns integer
        with execute as caller
        as begin

            declare @return integer
            declare @count integer
            set @return = 1

            /*
            backwards compatibility was broken in the release
            talked to design and they do not want to enforce uniqueness when designation is not supplied
            however when designation is required we still need to consider CATEGORYCODEID, USECODEID, and DATE
            to maintain backwards compat
            */

            if @DESIGNATIONID is not null
            begin
                select @count=count(*) 
                from PLANNEDGIFTADDITIONDESIGNATION 
                where PLANNEDGIFTADDITIONID = @PLANNEDGIFTADDITIONID
                    and coalesce(DESIGNATIONID, '00000000-0000-0000-0000-000000000000') = coalesce(@DESIGNATIONID, '00000000-0000-0000-0000-000000000000')
                    and coalesce(CATEGORYCODEID, '00000000-0000-0000-0000-000000000000') = coalesce(@CATEGORYCODEID, '00000000-0000-0000-0000-000000000000')
                    and coalesce(USECODEID, '00000000-0000-0000-0000-000000000000') = coalesce(@USECODEID, '00000000-0000-0000-0000-000000000000')
                    and ((DATE is null and @DATE is null) or (DATE = @DATE))

                if @count > 1
                    set @return = 0 ;
            end

            return @return ;                
        end