USP_REPORT_CAMPAIGNSUMMARY

                create procedure dbo.USP_REPORT_CAMPAIGNSUMMARY
                (
                    @CAMPAIGNID uniqueidentifier = null,
                    @CAMPAIGNHIERARCHYGOALID uniqueidentifier = null,
                    @GROUPBY tinyint = null,
                    @CURRENCYCODE tinyint = null --0 = Record base, (null, 1) = Organization

                )
                as begin

                    --Retrieve campaign goal information we'll need later

                    declare @STARTDATE datetime;
                    declare @ENDDATE datetime;
                    select 
                        @STARTDATE=STARTDATE,
                        @ENDDATE=ENDDATE
                    from CAMPAIGNHIERARCHYGOAL
                    where ID = @CAMPAIGNHIERARCHYGOALID;

          declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    declare @SELECTEDCURRENCYID uniqueidentifier;
                    if coalesce(@CURRENCYCODE, 1) = 1
                    begin
                        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    end

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

                    if coalesce(@GROUPBY, 0) = 0
                    begin
                        -- Handle no grouping

                        with CAMPAIGNGOAL_CTE as (
                            -- pull back campaign hierarchy goal amounts for the root campaign

                            select
                                CAMPAIGNHIERARCHYGOAL.CAMPAIGNID,
                                case @CURRENCYCODE
                                    when 0 then CAMPAIGNHIERARCHYGOAL.AMOUNT
                                    else CAMPAIGNHIERARCHYGOAL.ORGANIZATIONAMOUNT
                                end [AMOUNT]
                            from
                                dbo.CAMPAIGNHIERARCHYGOAL
                            where
                                CAMPAIGNHIERARCHYGOAL.ID = @CAMPAIGNHIERARCHYGOALID

                            union all

                            -- pull back campaign goal amounts for subcampaigns

                            select
                                CAMPAIGNGOAL.CAMPAIGNID,
                                case @CURRENCYCODE
                                    when 0 then CAMPAIGNGOAL.AMOUNT
                                    else CAMPAIGNGOAL.ORGANIZATIONAMOUNT
                                end
                            from
                                dbo.CAMPAIGNGOAL
                            where
                                CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = @CAMPAIGNHIERARCHYGOALID
                        )
                        select
                            CAMPAIGN.NAME,
                            CAMPAIGNGOAL_CTE.AMOUNT [GOAL],
                            TOTALRECEIVED.TOTALRECEIVED,
                            PLEDGEBALANCE.PLEDGEBALANCE [TOTALEXPECTED],
                            TOTALRECEIVED.TOTALRECEIVED as [OVERALLTOTALRECEIVED],
                            PLEDGEBALANCE.PLEDGEBALANCE as [OVERALLTOTALEXPECTED],
                            case when CAMPAIGN.ID=@CAMPAIGNID then 1 else 0 end [ROOT],
                            '' as PERIOD,
                            PLANNEDGIFTS.TOTALPLANNEDGIFTS as [TOTALPLANNEDGIFTS],
                            PLANNEDGIFTS.TOTALPLANNEDGIFTS as [OVERALLTOTALPLANNEDGIFTS],
                            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                            CURRENCYPROPERTIES.CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE]
                        from dbo.CAMPAIGN
                        left outer join
                            CAMPAIGNGOAL_CTE on CAMPAIGN.ID = CAMPAIGNGOAL_CTE.CAMPAIGNID
                        outer apply
                            dbo.UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALRECEIVED
                        outer apply
                            dbo.UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) PLEDGEBALANCE
                        outer apply
                            dbo.UFN_CAMPAIGN_GETTOTALPLANNEDGIFTSINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) PLANNEDGIFTS
                        outer apply
                            dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID)) CURRENCYPROPERTIES
                        where
                            (CAMPAIGN.ID = @CAMPAIGNID or CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH)
                        order by 
                            ROOT desc, PERIOD;
                    end
                    else
                    begin
                        -- Handle grouping by quarter and year

                        with CAMPAIGNGOAL_CTE as (
                            -- pull back campaign hierarchy goal amounts for the root campaign

                            select
                                CAMPAIGNHIERARCHYGOAL.CAMPAIGNID,
                                case @CURRENCYCODE
                                    when 0 then CAMPAIGNHIERARCHYGOAL.AMOUNT
                                    else CAMPAIGNHIERARCHYGOAL.ORGANIZATIONAMOUNT
                                end [AMOUNT]
                            from
                                dbo.CAMPAIGNHIERARCHYGOAL
                            where
                                CAMPAIGNHIERARCHYGOAL.ID = @CAMPAIGNHIERARCHYGOALID

                            union all

                            -- pull back campaign goal amounts for subcampaigns

                            select
                                CAMPAIGNGOAL.CAMPAIGNID,
                                case @CURRENCYCODE
                                    when 0 then CAMPAIGNGOAL.AMOUNT
                                    else CAMPAIGNGOAL.ORGANIZATIONAMOUNT
                                end
                            from
                                dbo.CAMPAIGNGOAL
                            where
                                CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = @CAMPAIGNHIERARCHYGOALID
                        )
                        select
                            CAMPAIGN.NAME,
                            CAMPAIGNGOAL_CTE.AMOUNT [GOAL],
                            TOTALRECEIVED.TOTALRECEIVED,
                            PLEDGEBALANCE.PLEDGEBALANCE [TOTALEXPECTED],
                            OVERALLTOTALRECEIVED.TOTALRECEIVED as [OVERALLTOTALRECEIVED],
                            OVERALLPLEDGEBALANCE.PLEDGEBALANCE as [OVERALLTOTALEXPECTED],
                            case when CAMPAIGN.ID=@CAMPAIGNID then 1 else 0 end [ROOT],
                            case @GROUPBY 
                                when 2 
                                    then 'Q' + convert(nvarchar(10),PERIODS.SEQUENCE) + ', ' 
                                        + convert(nvarchar(10),PERIODS.PERIODYEAR)
                                when 3
                                    then convert(nvarchar(10),PERIODS.PERIODYEAR)
                                else '' end PERIOD,
                            PLANNEDGIFTS.TOTALPLANNEDGIFTS as [TOTALPLANNEDGIFTS],
                            OVERALLPLANNEDGIFTS.TOTALPLANNEDGIFTS as [OVERALLTOTALPLANNEDGIFTS],
                            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                            CURRENCYPROPERTIES.CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE]
                        from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@STARTDATE, @ENDDATE, @GROUPBY) PERIODS
                        cross join
                            dbo.CAMPAIGN
                        left outer join
                            CAMPAIGNGOAL_CTE on CAMPAIGN.ID = CAMPAIGNGOAL_CTE.CAMPAIGNID
                        outer apply
                            dbo.UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, PERIODS.STARTDATE, PERIODS.ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALRECEIVED
                        outer apply
                            dbo.UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY(CAMPAIGN.HIERARCHYPATH, PERIODS.STARTDATE, PERIODS.ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) PLEDGEBALANCE
                        outer apply
                            dbo.UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) OVERALLTOTALRECEIVED
                        outer apply
                            dbo.UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) OVERALLPLEDGEBALANCE
                        outer apply 
                            dbo.UFN_CAMPAIGN_GETTOTALPLANNEDGIFTSINCURRENCY(CAMPAIGN.HIERARCHYPATH, PERIODS.STARTDATE, PERIODS.ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) PLANNEDGIFTS
                        outer apply 
                            dbo.UFN_CAMPAIGN_GETTOTALPLANNEDGIFTSINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) OVERALLPLANNEDGIFTS
                        outer apply
                            dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID)) CURRENCYPROPERTIES
                        where
                            (CAMPAIGN.ID = @CAMPAIGNID or CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH)
                        order by 
                            ROOT desc, PERIOD;
                    end

                end