USP_REPORT_OPPORTUNITYPIPELINE

            CREATE procedure dbo.USP_REPORT_OPPORTUNITYPIPELINE
            (
                @FUNDRAISERQUERYID uniqueidentifier = null,
                @ASKSTARTDATE datetime = null,
                @ASKENDDATE datetime = null,
                @DESIGNATIONQUERYID uniqueidentifier = null,
                @PROSPECTSTATUS uniqueidentifier = null,
                @PLANSTAGE uniqueidentifier = null,
                @PLANTYPE uniqueidentifier = null,
                @INCLUDEUNQUALIFIED tinyint = null,
                @INCLUDEQUALIFIED tinyint = null,
                @INCLUDERESPONSEPENDING tinyint = null,
                @INCLUDEACCEPTED tinyint = null,
                @INCLUDEREJECTED tinyint = null,
                @INCLUDECANCELED tinyint = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as            
                set nocount on;

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                begin try
                    select
                        CONSTITUENT.ID,
                        CONSTITUENT.KEYNAME,
                        CONSTITUENT.FIRSTNAME,
                        CONSTITUENT_NF.NAME,
                        OPPORTUNITY.STATUSCODE,
                        OPPORTUNITY.STATUS,
                        count(distinct OPPORTUNITY.ID) as STATUSCOUNT
                    from dbo.OPPORTUNITY
                    inner join dbo.PROSPECTPLAN
                    on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
                    inner join dbo.PROSPECT
                    on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
                    left join dbo.PROSPECTPLANSTATUSCODE
                    on PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = PROSPECTPLANSTATUSCODE.ID
                    inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = OPPORTUNITY.ID
                    inner join dbo.CONSTITUENT 
                    on OS.FUNDRAISERID = CONSTITUENT.ID
                    left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@FUNDRAISERQUERYID) SELECTION 
                        on OS.FUNDRAISERID = SELECTION.ID
                    left join dbo.PROSPECTPLANSITE
                        on PROSPECTPLAN.ID = PROSPECTPLANSITE.PROSPECTPLANID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                    where
                        (@FUNDRAISERQUERYID is null or OS.FUNDRAISERID = SELECTION.ID) and
                        (
                            (OPPORTUNITY.ASKDATE is not null and OPPORTUNITY.ASKDATE between @ASKSTARTDATE and @ASKENDDATE) or
                            (OPPORTUNITY.ASKDATE is null and OPPORTUNITY.EXPECTEDASKDATE between @ASKSTARTDATE and @ASKENDDATE)
                        ) and
                        (
                            @DESIGNATIONQUERYID is null or 
                            exists 
                            (
                                select 
                                    OPPORTUNITYDESIGNATION.ID 
                                from dbo.OPPORTUNITYDESIGNATION 
                                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DESIGNATIONQUERYID) DESIGNATIONSELECTION
                                on OPPORTUNITYDESIGNATION.DESIGNATIONID = DESIGNATIONSELECTION.ID
                                where 
                                    OPPORTUNITYID = OPPORTUNITY.ID
                            )
                        ) and
                        (@PROSPECTSTATUS is null or PROSPECT.PROSPECTSTATUSCODEID = @PROSPECTSTATUS) and
                        (@PLANSTAGE is null or PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = @PLANSTAGE) and
                        (@PLANTYPE is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PLANTYPE) and
                        (@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0) and
                        (@INCLUDEQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 1) and
                        (@INCLUDERESPONSEPENDING = 1 or OPPORTUNITY.STATUSCODE <> 2) and
                        (@INCLUDEACCEPTED = 1 or OPPORTUNITY.STATUSCODE <> 3) and
                        (@INCLUDEREJECTED = 1 or OPPORTUNITY.STATUSCODE <> 4) and
                        (@INCLUDECANCELED = 1 or OPPORTUNITY.STATUSCODE <> 5) and
                        (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, PROSPECTPLANSITE.SITEID) = 1) and
                        (@ISADMIN = 1 or
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            )
                    group by 
                        CONSTITUENT.ID,
                        CONSTITUENT.KEYNAME,
                        CONSTITUENT.FIRSTNAME,
                        CONSTITUENT_NF.NAME,
                        OPPORTUNITY.STATUSCODE,
                        OPPORTUNITY.STATUS
                    order by 
                        CONSTITUENT.KEYNAME,
                        CONSTITUENT.FIRSTNAME,
                        OPPORTUNITY.STATUSCODE
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;