UFN_DESIGNATION_HASACTIVECHILDREN

          CREATE function dbo.UFN_DESIGNATION_HASACTIVECHILDREN
          (
            @DESIGNATIONID uniqueidentifier
          )
          returns bit
          with execute as caller
          as
          begin
            declare @LEVELID1 uniqueidentifier;
            declare @LEVELID2 uniqueidentifier;
            declare @LEVELID3 uniqueidentifier;
            declare @LEVELID4 uniqueidentifier;
            declare @LEVELID5 uniqueidentifier;
      declare @EMPTY uniqueidentifier='00000000-0000-0000-0000-000000000000';

            select 
                @LEVELID1=DESIGNATIONLEVEL1ID,
                @LEVELID2=DESIGNATIONLEVEL2ID,
                @LEVELID3=DESIGNATIONLEVEL3ID,
                @LEVELID4=DESIGNATIONLEVEL4ID,
                @LEVELID5=DESIGNATIONLEVEL5ID
            from
                dbo.DESIGNATION 
            where
                ID=@DESIGNATIONID 

      if @LEVELID1 is not null
        --REVISIT technically this approach will return true if offspring OR if siblings are active

        --but this is OK right now since the system does not support siblings with the same purpose

              if exists(select ID from
                  dbo.DESIGNATION D
              where 
                  D.ISACTIVE = 1
                  and D.ID<>@DESIGNATIONID 
                  and COALESCE(D.DESIGNATIONLEVEL1ID,@EMPTY)=COALESCE(@LEVELID1,D.DESIGNATIONLEVEL1ID,@EMPTY)
                  and COALESCE(D.DESIGNATIONLEVEL2ID,@EMPTY)=COALESCE(@LEVELID2,D.DESIGNATIONLEVEL2ID,@EMPTY)
                  and COALESCE(D.DESIGNATIONLEVEL3ID,@EMPTY)=COALESCE(@LEVELID3,D.DESIGNATIONLEVEL3ID,@EMPTY)
                  and COALESCE(D.DESIGNATIONLEVEL4ID,@EMPTY)=COALESCE(@LEVELID4,D.DESIGNATIONLEVEL4ID,@EMPTY)
                  and COALESCE(D.DESIGNATIONLEVEL5ID,@EMPTY)=COALESCE(@LEVELID5,D.DESIGNATIONLEVEL5ID,@EMPTY)) return 1

            return 0;
          end