UFN_CAMPAIGNHIERARCHY_SECURED

create function dbo.UFN_CAMPAIGNHIERARCHY_SECURED
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATURETYPE int,
  @SECURITYFEATUREID uniqueidentifier
)
returns table
as
return (
    with CAMPAIGNLIST_CTE as
    (
      select 
        ID, 
        HIERARCHYPATH, 
        NAME, 
        SEQUENCE
      from dbo.CAMPAIGN  
      where
      (
       select count(*)  
       from dbo.UFN_SITEID_MAPFROM_CAMPAIGNID(CAMPAIGN.ID) as CAMPAIGNSITE  
       where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CAMPAIGNSITE].[SITEID] or (SITEID is null and [CAMPAIGNSITE].[SITEID] is null)))  
      ) > 0  
    )
          select
              CAMPAIGN.ID,
              (    
                  select 
                      PARENTCAMPAIGN.ID 
                  from 
                      CAMPAIGNLIST_CTE [PARENTCAMPAIGN] 
                  where 
                      CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = PARENTCAMPAIGN.HIERARCHYPATH
              ) as PARENTCAMPAIGNID,
              CAMPAIGN.NAME,
              CAMPAIGN.SEQUENCE
          from
              CAMPAIGNLIST_CTE CAMPAIGN
    )