USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYOPPORTUNITIES

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYOPPORTUNITIES
                (
                    @CURRENTAPPUSERID uniqueidentifier = null, 
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null
                )
                as begin
                    set nocount on;

                    declare @QUALIFIEDOPPORTUNITIESCOUNT int;
                    declare @QUALIFIEDOPPORTUNITIESAMOUNT money;
                    declare @OVERDUEASKSCOUNT int;
                    declare @OVERDUEASKSAMOUNT money;
                    declare @FUZZYTODAY dbo.UDT_FUZZYDATE;

                    set @FUZZYTODAY = convert(nvarchar(8), getdate(), 112);

                    if @ORGPOSITIONSSELECTIONID is null
                    begin
                        select
                            @QUALIFIEDOPPORTUNITIESCOUNT = count(*),
                            @QUALIFIEDOPPORTUNITIESAMOUNT = coalesce(sum(AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY
                        where
                            STATUSCODE in (1,2);

                        select
                            @OVERDUEASKSCOUNT = count(*),
                            @OVERDUEASKSAMOUNT = coalesce(sum(O.AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY O
                        where
                            O.STATUSCODE=2
                            and O.RESPONSEDATE<@FUZZYTODAY;
                    end
                    else
                    begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

                        select
                            @QUALIFIEDOPPORTUNITIESCOUNT = count(*),
                            @QUALIFIEDOPPORTUNITIESAMOUNT = coalesce(sum(O.AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP
                            on O.PROSPECTPLANID = PP.ID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                            on OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID and O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE)
                        inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                        where
                            O.STATUSCODE in (1,2);

                        select
                            @OVERDUEASKSCOUNT = count(*),
                            @OVERDUEASKSAMOUNT = coalesce(sum(O.AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP
                            on O.PROSPECTPLANID = PP.ID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                            on OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID and O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE)
                        inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                        where
                            O.STATUSCODE=2
                            and O.RESPONSEDATE<@FUZZYTODAY;
                    end

                    select 
                        @QUALIFIEDOPPORTUNITIESCOUNT,
                        @QUALIFIEDOPPORTUNITIESAMOUNT,
                        @OVERDUEASKSCOUNT,
                        @OVERDUEASKSAMOUNT

                    return 0;
                end