USP_KPI_MAJORGIVING_PLANNEDMOVESFUNDRAISER

                CREATE procedure dbo.USP_KPI_MAJORGIVING_PLANNEDMOVESFUNDRAISER
                    @VALUE int output,
                    @ASOFDATE datetime, 
                    @DATEFILTER tinyint = null,
                    @PROSPECTPLANTYPECODEID uniqueidentifier = null,
                    @PROSPECTPLANSTATUSCODEID uniqueidentifier = null,
                    @PROSPECTSTATUSCODEID uniqueidentifier = null,
                    @FUNDRAISERID uniqueidentifier = null,
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
                    @INTERACTIONTYPECODEID uniqueidentifier = null,
                    @INTERACTIONCATEGORYID uniqueidentifier = null,
                    @INTERACTIONSUBCATEGORYID uniqueidentifier = null,
                    @ONLYOWNEDINTERACTIONS bit = 1,
                    @STEPSSELECTIONID uniqueidentifier = null
                as

                    set nocount on;

                    if @DATEFILTER is null begin
                        set @DATEFILTER = 10;  -- all dates;

                    end

                    declare @STARTDATE datetime;
                    declare @ENDDATE datetime;

                    if @FUNDRAISERID is null and @ORGPOSITIONSSELECTIONID is null
                        set @ONLYOWNEDINTERACTIONS = 0;

                    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output, @ASOFDATE=@ASOFDATE;

                    declare @STEPIDS as table (ID uniqueidentifier);
                    if @STEPSSELECTIONID is not null
                        insert into @STEPIDS (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@STEPSSELECTIONID);

                    if @ORGPOSITIONSSELECTIONID is null
                        select
                            @VALUE = coalesce(count(*), 0)
                        from
                            dbo.INTERACTION I
                            inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                            inner join dbo.PROSPECT P on P.ID = PP.PROSPECTID
                        where
                            (@FUNDRAISERID is null or
                                (
                                    @FUNDRAISERID = I.FUNDRAISERID or
                                    (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID and FUNDRAISERID = @FUNDRAISERID))
                                )
                            )
                            and
                            (
                                @STEPSSELECTIONID is null
                                or exists (select ID from @STEPIDS [STEPIDS] where [STEPIDS].ID = I.ID)
                            )
                            and I.COMPLETED = 0
                            and I.DATE between @STARTDATE and @ENDDATE
                            and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                            and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                            and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                            and (@INTERACTIONTYPECODEID is null or I.INTERACTIONTYPECODEID = @INTERACTIONTYPECODEID)
                            and (@INTERACTIONSUBCATEGORYID is null or I.INTERACTIONSUBCATEGORYID = @INTERACTIONSUBCATEGORYID)
                            and (@INTERACTIONCATEGORYID is null or I.INTERACTIONSUBCATEGORYID in 
                                (select INTERACTIONSUBCATEGORY.ID from INTERACTIONSUBCATEGORY where INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = @INTERACTIONCATEGORYID))
                    else
                    begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec dbo.USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

                        select 
                            @VALUE = coalesce(count(distinct DATA.ID), 0)
                        from 
                            (
                                select
                                    I.ID
                                from
                                    dbo.INTERACTION I
                                    inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                                    inner join dbo.PROSPECT P on P.ID = PP.PROSPECTID
                                    inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                                        on OPH.CONSTITUENTID = I.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
                                    inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                                where
                                    (@FUNDRAISERID is null or
                                        (
                                            @FUNDRAISERID = I.FUNDRAISERID or
                                            (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID and FUNDRAISERID = @FUNDRAISERID))
                                        )
                                    )
                                    and I.COMPLETED = 0
                                    and I.DATE between @STARTDATE and @ENDDATE
                                    and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                                    and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                                    and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                                    and (@INTERACTIONTYPECODEID is null or I.INTERACTIONTYPECODEID = @INTERACTIONTYPECODEID)
                                    and (@INTERACTIONSUBCATEGORYID is null or I.INTERACTIONSUBCATEGORYID = @INTERACTIONSUBCATEGORYID)
                                    and (@INTERACTIONCATEGORYID is null or I.INTERACTIONSUBCATEGORYID in 
                                        (select INTERACTIONSUBCATEGORY.ID from INTERACTIONSUBCATEGORY where INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = @INTERACTIONCATEGORYID))

                                union all

                                select
                                    I.ID
                                from
                                    dbo.INTERACTION I
                                    inner join dbo.INTERACTIONADDITIONALFUNDRAISER on I.ID = INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID
                                    inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                                    inner join dbo.PROSPECT P on P.ID = PP.PROSPECTID
                                    inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
                                    inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                                where
                                    @ONLYOWNEDINTERACTIONS = 0
                                    and (@FUNDRAISERID is null or
                                        (
                                            @FUNDRAISERID = I.FUNDRAISERID or
                                            (exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID and FUNDRAISERID = @FUNDRAISERID))
                                        )
                                    )
                                    and I.COMPLETED = 0
                                    and I.DATE between @STARTDATE and @ENDDATE
                                    and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                                    and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                                    and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                                    and (@INTERACTIONTYPECODEID is null or I.INTERACTIONTYPECODEID = @INTERACTIONTYPECODEID)
                                    and (@INTERACTIONSUBCATEGORYID is null or I.INTERACTIONSUBCATEGORYID = @INTERACTIONSUBCATEGORYID)
                                    and (@INTERACTIONCATEGORYID is null or I.INTERACTIONSUBCATEGORYID in 
                                        (select INTERACTIONSUBCATEGORY.ID from INTERACTIONSUBCATEGORY where INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = @INTERACTIONCATEGORYID))

                            ) as DATA
                        where
                            (
                                @STEPSSELECTIONID is null
                                or exists (select ID from @STEPIDS [STEPIDS] where [STEPIDS].ID = DATA.ID)
                            )
                    end