UFN_DESIGNATION_GET_BYNOEVENTASSOCIATION

CREATE function dbo.UFN_DESIGNATION_GET_BYNOEVENTASSOCIATION
(
  @CURRENTAPPUSERID uniqueidentifier,
  @EVENTID uniqueidentifier
)
returns table
as return 
  select ID, NAME 
  from (
    select D.ID, case D.VANITYNAME when '' then D.NAME else D.VANITYNAME end as NAME
    from dbo.DESIGNATION D
    join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL1ID = DL.ID         
    where D.ISACTIVE = 1 and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DL.SITEID) = 1
    and D.DESIGNATIONLEVEL1ID IS NOT NULL and D.DESIGNATIONLEVEL2ID IS NULL

    union all

    select D.ID, case D.VANITYNAME when '' then D.NAME else D.VANITYNAME end as NAME
    from dbo.DESIGNATION D
    join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL2ID = DL.ID         
    where D.ISACTIVE = 1 and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DL.SITEID) = 1
    and D.DESIGNATIONLEVEL1ID IS NOT NULL and D.DESIGNATIONLEVEL2ID IS NOT NULL
    and D.DESIGNATIONLEVEL3ID IS NULL

    union all

    select D.ID, case D.VANITYNAME when '' then D.NAME else D.VANITYNAME end as NAME
    from dbo.DESIGNATION D
    join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL3ID = DL.ID         
    where D.ISACTIVE = 1 and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DL.SITEID) = 1
    and D.DESIGNATIONLEVEL1ID IS NOT NULL and D.DESIGNATIONLEVEL2ID IS NOT NULL and D.DESIGNATIONLEVEL3ID IS NOT NULL
    and D.DESIGNATIONLEVEL4ID IS NULL

    union all

    select D.ID, case D.VANITYNAME when '' then D.NAME else D.VANITYNAME end as NAME
    from dbo.DESIGNATION D
    join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL4ID = DL.ID         
    where D.ISACTIVE = 1 and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DL.SITEID) = 1
    and D.DESIGNATIONLEVEL1ID IS NOT NULL and D.DESIGNATIONLEVEL2ID IS NOT NULL and D.DESIGNATIONLEVEL3ID IS NOT NULL and D.DESIGNATIONLEVEL4ID IS NOT NULL
    and D.DESIGNATIONLEVEL5ID IS NULL

    union all

    select D.ID, case D.VANITYNAME when '' then D.NAME else D.VANITYNAME end as NAME
    from dbo.DESIGNATION D
    join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL5ID = DL.ID         
    where D.ISACTIVE = 1 and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DL.SITEID) = 1    
    and D.DESIGNATIONLEVEL1ID IS NOT NULL and D.DESIGNATIONLEVEL2ID IS NOT NULL and D.DESIGNATIONLEVEL3ID IS NOT NULL and D.DESIGNATIONLEVEL4ID IS NOT NULL
    and D.DESIGNATIONLEVEL5ID IS NOT NULL
  ) DES   
  where not exists 
      (    
          select * from dbo.EVENT E 
          join dbo.APPEALDESIGNATION AD on E.APPEALID = AD.APPEALID and AD.ISDEFAULT = 1
          where DES.ID = AD.DESIGNATIONID and E.ID <> @EVENTID
      )