USP_DATALIST_MICROSITEDONATIONDESIGNATIONS

                        CREATE procedure dbo.USP_DATALIST_MICROSITEDONATIONDESIGNATIONS
                (
                    @DESIGNATIONID uniqueidentifier,
                    @CMSONLY bit = null,
                    @LIVEONLY bit = null,
                    @NAMELIKE nvarchar(512) = null
                ) as
                    set nocount on;

                    declare @DESIGNATIONLEVELID uniqueidentifier;
                    select @DESIGNATIONLEVELID = 
                        case 
                                                        when DESIGNATIONLEVEL2ID is null then DESIGNATIONLEVEL1ID
                                                        when DESIGNATIONLEVEL3ID is null then DESIGNATIONLEVEL2ID
                                                        when DESIGNATIONLEVEL4ID is null then DESIGNATIONLEVEL3ID
                                                        when DESIGNATIONLEVEL5ID is null then DESIGNATIONLEVEL4ID
                                                        else DESIGNATIONLEVEL5ID
                                                end
                     from dbo.DESIGNATION where ID = @DESIGNATIONID;

                    declare @CURRENTDATE datetime = getdate();
                    declare @BBNCURL nvarchar(1024) = dbo.UFN_BBNC_URL();
                    set @NAMELIKE = '%' + ltrim(rtrim(@NAMELIKE)) + '%'

                    if @CMSONLY is null
                        set @CMSONLY = 0
                    if @LIVEONLY is null
                        set @LIVEONLY = 0

                    select 
                        DESIGNATION.ID,
                        DESIGNATION.VANITYNAME AS PUBLICNAME,
                        --DESIGNATIONLEVELTYPE.[DESCRIPTION] AS TYPEDESCRIPTION,

                        DESIGNATION.STARTDATE,
                        DESIGNATION.ENDDATE,
                        DESIGNATION.ISACTIVE,
                        ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0) AS ISCMS,
                        LIVESTATUS.ISLIVE,
                        DESIGNATIONLEVEL.NAME,
                        DESIGNATION.USERID AS LOOKUPID,
                        case 
                            when DESIGNATION.DESIGNATIONLEVEL2ID is null then 1
                            when DESIGNATION.DESIGNATIONLEVEL3ID is null then 2
                            when DESIGNATION.DESIGNATIONLEVEL4ID is null then 3
                            when DESIGNATION.DESIGNATIONLEVEL5ID is null then 4
                            else 5
                        end AS [LEVEL],
                        PARENTDESIGNATION.ID AS PARENTID,
                        MICROSITEPAGE.SITEPAGESID AS SITEPAGESID,
                        CASE WHEN ISNULL(CAST(MICROSITEPAGE.SITEPAGESID AS BIT), 0) = 1 THEN @BBNCURL + (SELECT TOP 1 VanityURL from dbo.VanityURL where PageID= MICROSITEPAGE.SITEPAGESID) ELSE '' END AS URL,
                        dbo.MICROSITEPAGE.PRIMARYCONTENTID AS PRIMARYCONTENTID,
                        DESIGNATION_MICROSITEEMAILTEMPLATE.ID as MICROSITEEMAILTEMPLATEID,
                        coalesce(DESIGNATION_MICROSITEEMAILTEMPLATE.ACTIVE, 0) HASACTIVEACKNOWLEDGEMENTEMAIL
                    from
                        dbo.DESIGNATION
                    inner join dbo.DESIGNATIONLEVEL 
                        on (DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID and DESIGNATION.DESIGNATIONLEVEL2ID is null)
                            or (DESIGNATION.DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL.ID and DESIGNATION.DESIGNATIONLEVEL3ID is null)
                            or (DESIGNATION.DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL.ID and DESIGNATION.DESIGNATIONLEVEL4ID is null)
                            or (DESIGNATION.DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL.ID and DESIGNATION.DESIGNATIONLEVEL5ID is null)
                            or (DESIGNATION.DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL.ID)
                    --inner join dbo.DESIGNATIONLEVELTYPE 

                    --    on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID

                    left join dbo.DESIGNATION [PARENTDESIGNATION] 
                        on DESIGNATION.DESIGNATIONLEVEL1ID = PARENTDESIGNATION.DESIGNATIONLEVEL1ID
                            and (DESIGNATION.DESIGNATIONLEVEL2ID = PARENTDESIGNATION.DESIGNATIONLEVEL2ID or (PARENTDESIGNATION.DESIGNATIONLEVEL2ID is null and DESIGNATION.DESIGNATIONLEVEL3ID is null))
                            and (DESIGNATION.DESIGNATIONLEVEL3ID = PARENTDESIGNATION.DESIGNATIONLEVEL3ID or (PARENTDESIGNATION.DESIGNATIONLEVEL3ID is null and DESIGNATION.DESIGNATIONLEVEL4ID is null))
                            and (DESIGNATION.DESIGNATIONLEVEL4ID = PARENTDESIGNATION.DESIGNATIONLEVEL4ID or (PARENTDESIGNATION.DESIGNATIONLEVEL4ID is null and DESIGNATION.DESIGNATIONLEVEL5ID is null))
                            and DESIGNATION.ID <> PARENTDESIGNATION.ID
                    --left join dbo.DonationDesignations 

                    --    on dbo.DonationDesignations.BackOfficeIDGUID = DESIGNATION.ID

                    left join dbo.MICROSITEPAGE
                            on (dbo.MICROSITEPAGE.OBJECTID = DESIGNATION.ID) and (dbo.MICROSITEPAGE.EXCLUDED = 0)
                    left outer join dbo.DESIGNATION_MICROSITEEMAILTEMPLATE
                        on DESIGNATION.ID = DESIGNATION_MICROSITEEMAILTEMPLATE.DESIGNATIONID
                    cross apply (
                        select case
                                when (ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0) = 1)--(ISNULL(CAST(dbo.DonationDesignations.ID AS BIT), 0) = 1)

                                    and (DESIGNATION.ISACTIVE = 1)
                                    and (((@CURRENTDATE >= DESIGNATION.STARTDATE) or (DESIGNATION.STARTDATE is null)) and ((@CURRENTDATE <= DESIGNATION.ENDDATE) or (DESIGNATION.ENDDATE is null)))
                                    then 1
                                else 0
                            end as [ISLIVE]
                    ) as [LIVESTATUS]
                    where ((@DESIGNATIONID = '0D47D951-8E96-4FBA-A7AA-2339B3D7B0BC') -- this well known guid is passed in as the context by the page to indicate all hierarchies

                        or (DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID)
                        or (DESIGNATION.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID)
                        or (DESIGNATION.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID)
                        or (DESIGNATION.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID)
                        or (DESIGNATION.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID)
                        or (DESIGNATIONLEVEL.ID = @DESIGNATIONLEVELID)
                        )
                    --and ((DESIGNATION.ISACTIVE = 1) or (any descendant is active))

                    and ((@CMSONLY= 0)
                        or (ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0) = 1)
                        )
                    and ((@LIVEONLY = 0)
                        or [LIVESTATUS].[ISLIVE] = 1
                        )
                    and ((@NAMELIKE is null)
                        or (DESIGNATION.VANITYNAME LIKE @NAMELIKE)
                        )
                    order by DESIGNATION.VANITYNAME, case when DESIGNATIONLEVEL.ID = @DESIGNATIONLEVELID then 1 else 0 end desc