USP_DATALIST_SPONSORSHIPLOCATIONHIERARCHY

CREATE procedure dbo.USP_DATALIST_SPONSORSHIPLOCATIONHIERARCHY(
    @INCLUDEACTIVE bit = 1,
    @INCLUDEINACTIVE bit = 1,
    @INCLUDECLOSED bit = 1,
  @INCLUDEONLINEONLY bit = 0
)
as
    set nocount on;

  select
      L.ID,
      L.NAME,
      SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION TYPE,
      L.STATUS,
      L.SPONSORSHIPREASONID,
      SPONSORSHIPREASON.REASON as REASON,
      L.DESIGNATIONID,
      DESIGNATION.NAME DESIGNATION,
      (select ID from dbo.SPONSORSHIPLOCATION P where L.HIERARCHYPATH.GetAncestor(1)= P.HIERARCHYPATH) as PARENTID,
      case when SPONSORSHIPLOCATIONCLOSEPROCESS.ID is not null then 1 else 0 end SHOWPROCESSPAGE,
    case L.STATUSCODE
     when 0 then 0
     -- can't mark active if any parent is not active

     else case when exists(select 'x'
                           from dbo.SPONSORSHIPLOCATION P
                           where L.HIERARCHYPATH.IsDescendantOf(P.HIERARCHYPATH) = 1
                           and P.ID <> L.ID
                           and P.STATUSCODE <> 0) then 0 else 1 end
    end ALLOWMARKACTIVE,
    case L.STATUSCODE
     when 0 then 1
     when 1 then 0
     -- can't mark inactive if any parent is closed

     else case when exists(select 'x'
                           from dbo.SPONSORSHIPLOCATION P
                           where L.HIERARCHYPATH.IsDescendantOf(P.HIERARCHYPATH) = 1
                           and P.ID <> L.ID
                           and P.STATUSCODE = 2) then 0 else 1 end
    end ALLOWMARKINACTIVE
  from
      dbo.SPONSORSHIPLOCATION L
  inner join
      dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = L.SPONSORSHIPLOCATIONTYPECODEID
  left outer join
      dbo.DESIGNATION on DESIGNATION.ID = L.DESIGNATIONID
  left outer join
      dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = L.SPONSORSHIPREASONID
  left outer join
      dbo.SPONSORSHIPLOCATIONCLOSEPROCESS on SPONSORSHIPLOCATIONCLOSEPROCESS.ID = L.ID 
  where case STATUSCODE when 0 then @INCLUDEACTIVE
                          when 1 then @INCLUDEINACTIVE
                          when 2 then @INCLUDECLOSED end = 1
    and (@INCLUDEONLINEONLY = 0 or DISPLAYONLINE = 1)      
  order by
      dbo.UFN_SPONSORSHIPLOCATION_FULLSTRING(L.ID,'|',0,0);