USP_REPORT_NAMINGOPPORTUNITYRECOGNITION

                create procedure dbo.USP_REPORT_NAMINGOPPORTUNITYRECOGNITION
                (
                    @PURPOSEID uniqueidentifier = null,
                    @CONTACTID uniqueidentifier = null,
                    @TYPEID uniqueidentifier = null,
                    @SITEID uniqueidentifier = null,
                    @FACILITYID uniqueidentifier = null,
                    @CAMPAIGNID uniqueidentifier = null,
                    @AREAID uniqueidentifier = null,
                    @UNITID uniqueidentifier = null,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @NAMINGOPPORTUNITYQUERYID uniqueidentifier = null,
                    @NAMINGOPPORTUNITYID uniqueidentifier = null,
                    @REPORTUSERID nvarchar(128) = null,
                    @ALTREPORTUSERID nvarchar(128) = null
                )
                with execute as owner
                as
                    set nocount on;

                    -- Include recognitions without names in "All Dates" selection

                    if (@STARTDATE = '1753-1-1') and (@ENDDATE = '9999-12-30')
                    begin
                        set @STARTDATE = null;
                        set @ENDDATE = null;
                    end

                    -- Retrieve the table for the selection, if there is one.

                    declare @DBOBJECTNAME nvarchar(128);
                    declare @DBOBJECTTYPE smallint;

                    declare @CURRENTAPPUSERID uniqueidentifier;
                    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

                    if @NAMINGOPPORTUNITYQUERYID is not null begin
                        if not exists(select ID from dbo.IDSETREGISTER where ID = @NAMINGOPPORTUNITYQUERYID) raiserror('ID set does not exist in the database.', 15, 1);
                        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @NAMINGOPPORTUNITYQUERYID;
                        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @NAMINGOPPORTUNITYQUERYID) + ''')';
                    end

                    declare @SQLTOEXEC nvarchar(max);

                    set @SQLTOEXEC =
                    '
                    select
                        NAMINGOPPORTUNITY.ID as NAMINGOPPORTUNITYID,
                        NAMINGOPPORTUNITY.NAME,
                        NAMINGOPPORTUNITY.QUANTITY,
                        NAMINGOPPORTUNITY.REMAINING,
                        NAMINGOPPORTUNITY.MINIMUMAMOUNT,
                        CONTACT_NF.NAME as CONTACT,
                        CONSTITUENT_NF.NAME as CNAME,
                        NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTNAME as RECOGNITIONNAME,
                        dbo.UFN_DATE_FROMFUZZYDATE(NAMINGOPPORTUNITYRECOGNITION.STARTDATE) as RECOGNITIONDATE,
                        NAMINGOPPORTUNITYRECOGNITION.AMOUNT
                    from
                        dbo.NAMINGOPPORTUNITY
                        inner join dbo.DESIGNATIONLEVEL on DESIGNATIONLEVEL.ID = NAMINGOPPORTUNITY.PURPOSEID
                        left join dbo.SITE on SITE.ID = NAMINGOPPORTUNITY.SITEID
                        left join dbo.NAMINGOPPORTUNITYRECOGNITION on NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID = NAMINGOPPORTUNITY.ID
                        left join dbo.NAMINGOPPORTUNITYFACILITY on NAMINGOPPORTUNITYFACILITY.ID = NAMINGOPPORTUNITY.FACILITYID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITY.CONTACTID) CONTACT_NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTID) CONSTITUENT_NF
                    ' + nchar(13);

                    begin
                        if not @NAMINGOPPORTUNITYQUERYID is null
                        begin
                            set @SQLTOEXEC = @SQLTOEXEC + 
                                'inner join dbo.' + @DBOBJECTNAME + ' as NAMINGOPPORTUNITYQUERY on NAMINGOPPORTUNITY.[ID] = NAMINGOPPORTUNITYQUERY.[ID]' + nchar(13);
                        end
                    end

                    set @SQLTOEXEC = @SQLTOEXEC +
                    '
                    where
                        (@PURPOSEID is null or (DESIGNATIONLEVEL.ID = @PURPOSEID)) and
                        (@CONTACTID is null or (NAMINGOPPORTUNITY.CONTACTID = @CONTACTID)) and
                        (@TYPEID is null or (NAMINGOPPORTUNITY.NAMINGOPPORTUNITYTYPECODEID = @TYPEID)) and
                        (@SITEID is null or (SITE.ID = @SITEID)) and
                        (@FACILITYID is null or (NAMINGOPPORTUNITY.FACILITYID = @FACILITYID)) and
                        (@CAMPAIGNID is null or (NAMINGOPPORTUNITY.CAMPAIGNID = @CAMPAIGNID)) and
                        (@AREAID is null or (NAMINGOPPORTUNITYFACILITY.AREACODEID = @AREAID)) and
                        (@UNITID is null or (NAMINGOPPORTUNITYFACILITY.UNITCODEID = @UNITID)) and
                        (@NAMINGOPPORTUNITYID is null or (NAMINGOPPORTUNITY.ID = @NAMINGOPPORTUNITYID)) and
                        ((NAMINGOPPORTUNITYRECOGNITION.STARTDATE = null) or
                            ((@STARTDATE is null or (dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) <= dbo.UFN_DATE_FROMFUZZYDATE(NAMINGOPPORTUNITYRECOGNITION.STARTDATE))) and
                             (@ENDDATE is null or (dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) >= dbo.UFN_DATE_FROMFUZZYDATE(NAMINGOPPORTUNITYRECOGNITION.STARTDATE))))) and
                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, NAMINGOPPORTUNITY.SITEID) = 1
                    order by
                        NAMINGOPPORTUNITY.MINIMUMAMOUNT desc
                    ' + nchar(13);

                    exec sp_executesql @SQLTOEXEC,
                        N'@PURPOSEID uniqueidentifier, @CONTACTID uniqueidentifier, @TYPEID uniqueidentifier, @SITEID uniqueidentifier, @FACILITYID uniqueidentifier, @CAMPAIGNID uniqueidentifier,    @AREAID uniqueidentifier, @UNITID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @NAMINGOPPORTUNITYQUERYID uniqueidentifier, @NAMINGOPPORTUNITYID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier',
                        @PURPOSEID=@PURPOSEID, @CONTACTID=@CONTACTID, @TYPEID=@TYPEID, @SITEID=@SITEID, @FACILITYID=@FACILITYID, @CAMPAIGNID=@CAMPAIGNID, @AREAID=@AREAID, @UNITID=@UNITID, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @NAMINGOPPORTUNITYQUERYID=@NAMINGOPPORTUNITYQUERYID, @NAMINGOPPORTUNITYID=@NAMINGOPPORTUNITYID, @CURRENTAPPUSERID=@CURRENTAPPUSERID;