UFN_DESIGNATION_CANBEACTIVE

CREATE function dbo.UFN_DESIGNATION_CANBEACTIVE (@DESIGNATIONID uniqueidentifier)
returns bit
as
begin
  declare @R bit;

  set @R = 1;

  declare @DESIGNATIONLEVEL1 uniqueidentifier;
  declare @DESIGNATIONLEVEL2 uniqueidentifier;
  declare @DESIGNATIONLEVEL3 uniqueidentifier;
  declare @DESIGNATIONLEVEL4 uniqueidentifier;
  declare @DESIGNATIONLEVEL5 uniqueidentifier;
  declare @SYSTEMGENERATED bit;

  select @SYSTEMGENERATED = SYSTEMGENERATED
    ,@DESIGNATIONLEVEL1 = DESIGNATIONLEVEL1ID
    ,@DESIGNATIONLEVEL2 = DESIGNATIONLEVEL2ID
    ,@DESIGNATIONLEVEL3 = DESIGNATIONLEVEL3ID
    ,@DESIGNATIONLEVEL4 = DESIGNATIONLEVEL4ID
    ,@DESIGNATIONLEVEL5 = DESIGNATIONLEVEL5ID
  from dbo.DESIGNATION
  where ID = @DESIGNATIONID;

  if @SYSTEMGENERATED = 0
  begin
    select @R = coalesce(DESIGNATIONLEVEL.ISACCEPTINGFUNDS, 1)
    from dbo.DESIGNATIONLEVEL
    where ID = @DESIGNATIONLEVEL1;

    if @R = 1
      select @R = coalesce(DESIGNATIONLEVEL.ISACCEPTINGFUNDS, 1)
      from dbo.DESIGNATIONLEVEL
      where ID = @DESIGNATIONLEVEL2;

    if @R = 1
      select @R = coalesce(DESIGNATIONLEVEL.ISACCEPTINGFUNDS, 1)
      from dbo.DESIGNATIONLEVEL
      where ID = @DESIGNATIONLEVEL3;

    if @R = 1
      select @R = coalesce(DESIGNATIONLEVEL.ISACCEPTINGFUNDS, 1)
      from dbo.DESIGNATIONLEVEL
      where ID = @DESIGNATIONLEVEL4;

    if @R = 1
      select @R = coalesce(DESIGNATIONLEVEL.ISACCEPTINGFUNDS, 1)
      from dbo.DESIGNATIONLEVEL
      where ID = @DESIGNATIONLEVEL5;
  end

  return @R;
end