UFN_FAFNFGCAMPAIGN_GETALLLEVELS

CREATE function dbo.UFN_FAFNFGCAMPAIGN_GETALLLEVELS()
returns table
as return

    With [All] (LEVELID, HPATH, NFGID)
    As 
    (        
        select NFGL.ID,                 
                NFGL.HIERARCHYPATH.ToString(), 
                NFGL.NFGCAMPAIGNID as NFGID
        FROM dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock)
        join dbo.FAFNFGCAMPAIGN C (nolock) on C.ID = NFGL.NFGCAMPAIGNID
        join dbo.FAFPROGRAM P (nolock) on P.ID = C.CAMPAIGNID and P.ISACTIVE = 1 
        where NFGL.HIERARCHYPATH.GetAncestor(1).ToString() <> '/'
    ),
    [Children](LEVELID, PARENTLEVELID,  NFGID)
    as
    (
        select   CC.ID,C.LEVELID, C.NFGID
            from dbo.FAFNFGCAMPAIGNLEVEL CC (nolock)
            cross apply [All] C
            where CC.HIERARCHYPATH.IsDescendantOf(C.[HPATH]) = 1
            /*
        select L.ID, A.LEVELID,  NFGID from [All] A
              join dbo.FAFNFGCAMPAIGNLEVEL L (nolock) on L.HIERARCHYPATH.ToString() like '' + A.HPATH + '%' */
    ),
    [Final] 
    As
    (
        select LEVELID as PARENTLEVELID, LEVELID,  NFGID
        from [All] 

        Union All

        select F.PARENTLEVELID, F.LEVELID,  NFGID
        from [Children] F 
    )
    select distinct F.PARENTLEVELID as ID, F.LEVELID as CHILDID, F.NFGID, FC.CAMPAIGNID from Final F 
    join dbo.FAFNFGCAMPAIGN FC (nolock) on FC.ID = F.NFGID