USP_DATALIST_CAMPAIGNSNAPSHOT

                    create procedure dbo.USP_DATALIST_CAMPAIGNSNAPSHOT
                    (
                        @CAMPAIGNID uniqueidentifier
                    )
                    as
                        set nocount on

                        declare @CAMPAIGNIDSTR nvarchar(36)
                        set @CAMPAIGNIDSTR = cast(@CAMPAIGNID as nvarchar(36))

                        declare @CAMPAIGNHIERARCHYID hierarchyid
                        select @CAMPAIGNHIERARCHYID = HIERARCHYPATH
                        from dbo.CAMPAIGN where ID = @CAMPAIGNID

                        declare @PARENTCAMPAIGNIDSTR nvarchar(36)
                        if @CAMPAIGNHIERARCHYID.GetLevel() > 0
                            select
                                @PARENTCAMPAIGNIDSTR = cast(ID as nvarchar(36))
                            from dbo.CAMPAIGN 
                            where @CAMPAIGNHIERARCHYID.GetAncestor(1) = HIERARCHYPATH

                        declare @CAMPAIGNREPORTLIST table
                        (
                            REPORTID uniqueidentifier,
                            HIERARCHICAL bit not null,
                            NONHIERARCHICALCHECKPARENT bit not null,
                            REPORTIDSTR as cast(REPORTID as nvarchar(36))
                        )

                        insert into @CAMPAIGNREPORTLIST (REPORTID, HIERARCHICAL, NONHIERARCHICALCHECKPARENT)
                        select 'e8ef528a-7f8c-4ecf-839c-d021b44e028c', 1, 0 -- Hierarchical campaign summary

                        union all
                        select 'FC001871-A658-465E-90F4-5652FE9E40BD', 0, 0 -- Campaign priority

                        union all
                        select 'FA29E535-EE5E-4451-94C4-A068B1AD4757', 0, 1 -- Campaign recognition

                        union all
                        select '64A2CE1E-326F-4218-AE2D-BBA193ECFC5D', 0, 1 -- Campaign summary


                        select
                            REPORTSNAPSHOT.ID,
                            REPORTSNAPSHOT.REPORTCATALOGID,
                            REPORTSNAPSHOT.HISTORYID,
                            REPORTSNAPSHOT.DATEADDED,
                            BASEREPORT.NAME as REPORTNAME,
                            REPORTCATALOG.NAME as REPORTPARAMETERSETNAME,
                            REPORTSNAPSHOT.NAME
                        from dbo.REPORTCATALOG 
                        inner join dbo.REPORTSNAPSHOT on REPORTSNAPSHOT.REPORTCATALOGID = REPORTCATALOG.ID
                        -- Make sure the base report was a campaign report

                        inner join @CAMPAIGNREPORTLIST CAMPAIGNREPORTLIST on 
                            REPORTCATALOG.REPORTSPECXML.exist('    declare namespace ns="bb_appfx_report";
                                                                /ns:ReportSpec/ns:LinkedReport[fn:lower-case(@BaseReportID)=fn:lower-case(sql:column("CAMPAIGNREPORTLIST.REPORTIDSTR"))]') = 1
                        inner join dbo.REPORTCATALOG BASEREPORT on BASEREPORT.ID = CAMPAIGNREPORTLIST.REPORTID
                        where
                            (
                                CAMPAIGNREPORTLIST.HIERARCHICAL = 1 and
                                REPORTSNAPSHOT.PARAMETERS.exist('    declare namespace ns="bb_appfx_dataforms";
                                                                    /ns:DataFormItem/ns:Values/ns:fv[@ID="CAMPAIGNSTOINCLUDE"]/ns:Value/ns:Items/ns:DataFormItem/ns:Values/ns:fv[@ID="CAMPAIGNID"]/ns:Value/text()[fn:lower-case(.) = fn:lower-case(sql:variable("@CAMPAIGNIDSTR"))]') = 1
                            ) or
                            (
                                CAMPAIGNREPORTLIST.HIERARCHICAL = 0 and
                                REPORTSNAPSHOT.PARAMETERS.exist('    declare namespace ns="bb_appfx_dataforms";
                                                                    /ns:DataFormItem/ns:Values/ns:fv[@ID="CAMPAIGNID"]/ns:Value/text()[
                                                                        fn:lower-case(.) = fn:lower-case(sql:variable("@CAMPAIGNIDSTR")) or 
                                                                        (
                                                                            sql:column("CAMPAIGNREPORTLIST.NONHIERARCHICALCHECKPARENT") and
                                                                            not(empty(sql:variable("@PARENTCAMPAIGNIDSTR") cast as xs:string?)) and
                                                                            fn:lower-case(.) = fn:lower-case(sql:variable("@PARENTCAMPAIGNIDSTR"))
                                                                        )]') = 1

                            )
                        order by REPORTSNAPSHOT.NAME, REPORTSNAPSHOT.DATEADDED