USP_REPORT_RECOGNITIONCREDIT

            CREATE procedure dbo.USP_REPORT_RECOGNITIONCREDIT
            (
                @DATETYPE tinyint = null,
                @DATERANGEDISPLAY nvarchar(100) = '',
                @STARTDATE datetime = null, 
                @ENDDATE datetime = null,
                @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                @CONSTITUENTQUERY uniqueidentifier = null,
                @APPEALID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null,
                @REVENUETRANSACTIONID uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @REVENUERECOGNITIONTYPECODEID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @CAMPAIGNID uniqueidentifier = null,
                @CURRENCYCODE tinyint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
            set nocount on;

                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                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);

                declare @SQLTOEXEC nvarchar(max);

                declare @REVENUEDBOBJECTNAME nvarchar(128);
                declare @REVENUEDBOBJECTTYPE smallint;
                declare @CONSTITUENTDBOBJECTNAME nvarchar(128);
                declare @CONSTITUENTDBOBJECTTYPE smallint;

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

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

                set @SQLTOEXEC = 
                    N'
                declare @CAMPAIGNHIERARCHYPATH hierarchyid
                select @CAMPAIGNHIERARCHYPATH=HIERARCHYPATH
                from CAMPAIGN
                where ID=@CAMPAIGNID;

                WITH RECOGNITIONS
                AS
                (
                    select
                        RR.ID,
                        RR.REVENUESPLITID,
                        RR.EFFECTIVEDATE,
                        RR.AMOUNT,
                        RR.ORGANIZATIONAMOUNT,
                        RR.BASECURRENCYID,
                        RR.REVENUERECOGNITIONTYPECODEID,
                        RR.CONSTITUENTID,
                        REVENUESPLIT_EXT.[DESIGNATIONID],
                        -1 as RECOGNITIONCREDITTYPECODE,
                        FINANCIALTRANSACTION.[TYPE] as [TRANSACTIONTYPE],
                        FINANCIALTRANSACTION.CONSTITUENTID as REVENUECONSTITUENTID,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as REVENUEBASECURRENCYID,
                        convert(datetime, FINANCIALTRANSACTION.DATE) as DATE,
                        REVENUE_EXT.APPEALID,
                        FINANCIALTRANSACTION.ID REVENUEID,
                        FINANCIALTRANSACTION.BASEAMOUNT as REVENUEAMOUNT,
                        FINANCIALTRANSACTION.ORGAMOUNT as REVENUEORGAMOUNT
                        from dbo.REVENUERECOGNITION as RR 
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = RR.REVENUESPLITID 
                        inner join dbo.REVENUESPLIT_EXT with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT with (nolock) on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                        left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
                        where (@REVENUERECOGNITIONTYPECODEID is null or RR.REVENUERECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0  -- Standard line items only

                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is NULL -- Non deleted line items only

                        and FINANCIALTRANSACTION.DELETEDON is NULL
                        -- Gift, Pledge, Matching Gift Claim, Planned Gift, Order, Grant award, Pledge Payment, Recurring Gift Payment, Matching Gift Payment, Auction Donation, Auction Payment and Grant Award Payment

                        and (FINANCIALTRANSACTION.TYPECODE in (1,3,4,5,6,7,8) or (FINANCIALTRANSACTION.TYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0,2,3,4,7,8,12,13) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE in (0,1)))))

                        union all

                        select
                        RECOGNITIONCREDIT.ID,
                        DONORCHALLENGEENCUMBERED.REVENUESPLITID as REVENUESPLITID, --This should be null but we still store some info on the revenue, REVENUECAMPAIGN for example

                        RECOGNITIONCREDIT.EFFECTIVEDATE,
                        RECOGNITIONCREDIT.AMOUNT,
                        RECOGNITIONCREDIT.ORGANIZATIONAMOUNT,
                        RECOGNITIONCREDIT.BASECURRENCYID,
                        RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODEID,
                        RECOGNITIONCREDIT.CONSTITUENTID,
                        RECOGNITIONCREDIT.DESIGNATIONID,
                        RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE,
                        ''Donor challenge claim - Internal sponsor'' as TRANSACTIONTYPE,
                        FINANCIALTRANSACTION.CONSTITUENTID as REVENUECONSTITUENTID,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as REVENUEBASECURRENCYID,
                        convert(datetime, FINANCIALTRANSACTION.DATE) as DATE,
                        REVENUE_EXT.APPEALID,
                        FINANCIALTRANSACTION.ID REVENUEID,
                        FINANCIALTRANSACTION.BASEAMOUNT as REVENUEAMOUNT,
                        FINANCIALTRANSACTION.ORGAMOUNT as REVENUEORGAMOUNT
                        from dbo.RECOGNITIONCREDIT
                        inner join dbo.DONORCHALLENGEENCUMBERED with (nolock) on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                        inner join dbo.REVENUESPLIT_EXT with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT with (nolock) on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                        left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
                        where (@REVENUERECOGNITIONTYPECODEID is null or RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
                        and RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
                        and FINANCIALTRANSACTION.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                )
                -- Added distinct so that recognitions tied to multiple campaigns 

                -- aren''t returned multiple times

                select distinct
                    REC_NF.NAME as RECOGNITIONNAME,
                    REC.KEYNAME as RECOGNITIONKEYNAME,
                    REC.FIRSTNAME as RECOGNITIONFIRSTNAME,
                    RRTC.DESCRIPTION as RECOGNITIONTYPE,
                    RECOGNITIONS.EFFECTIVEDATE,
                    case @CURRENCYCODE when 0 then RECOGNITIONS.AMOUNT else RECOGNITIONS.ORGANIZATIONAMOUNT end as RECOGNITIONAMOUNT,
                    RC_NF.NAME as REVENUENAME,
                    RC.KEYNAME as REVENUEKEYNAME,
                    RC.FIRSTNAME as REVENUEFIRSTNAME,
                    RECOGNITIONS.DATE,
                    RECOGNITIONS.TRANSACTIONTYPE as REVENUETYPE,
                    case @CURRENCYCODE when 0 then RECOGNITIONS.REVENUEAMOUNT else RECOGNITIONS.REVENUEORGAMOUNT end as REVENUEAMOUNT,
                    RECOGNITIONS.ID,
                    RECOGNITIONCURRENCYPROPERTIES.ISO4217 [RECOGNITIONISOCURRENCYCODE],
                    RECOGNITIONCURRENCYPROPERTIES.CURRENCYSYMBOL [RECOGNITIONCURRENCYSYMBOL],
                    RECOGNITIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [RECOGNITIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
                    RECOGNITIONCURRENCYPROPERTIES.DECIMALDIGITS [RECOGNITIONDECIMALDIGITS],
                    REVENUECURRENCYPROPERTIES.ISO4217 [REVENUEISOCURRENCYCODE],
                    REVENUECURRENCYPROPERTIES.CURRENCYSYMBOL [REVENUECURRENCYSYMBOL],
                    REVENUECURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [REVENUECURRENCYSYMBOLDISPLAYSETTINGCODE],
                    REVENUECURRENCYPROPERTIES.DECIMALDIGITS [REVENUEDECIMALDIGITS]
                from RECOGNITIONS
                inner join dbo.CONSTITUENT REC with (nolock) on RECOGNITIONS.CONSTITUENTID = REC.ID
                inner join dbo.CONSTITUENT RC with (nolock) on RECOGNITIONS.REVENUECONSTITUENTID = RC.ID
                left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = RRTC.ID
                left join REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID=RECOGNITIONS.REVENUESPLITID
                left join CAMPAIGN on CAMPAIGN.ID=REVENUESPLITCAMPAIGN.CAMPAIGNID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then RECOGNITIONS.BASECURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) RECOGNITIONCURRENCYPROPERTIES
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then RECOGNITIONS.REVENUEBASECURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) REVENUECURRENCYPROPERTIES
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REC.ID) REC_NF
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RC.ID) RC_NF
                ' + nchar(13)

                if @REVENUETRANSACTIONQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + N'inner join dbo.' + @REVENUEDBOBJECTNAME + ' as REVSELECTION on RECOGNITIONS.REVENUEID = REVSELECTION.[ID] and RECOGNITIONS.RECOGNITIONCREDITTYPECODE = -1' + nchar(13);

                if @CONSTITUENTQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + N'inner join dbo.' + @CONSTITUENTDBOBJECTNAME + ' as CONSTITSELECTION on RECOGNITIONS.CONSTITUENTID = CONSTITSELECTION.[ID]' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where (@STARTDATE is null or RECOGNITIONS.EFFECTIVEDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                    and (@ENDDATE is null or RECOGNITIONS.EFFECTIVEDATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                    and (@APPEALID is null or RECOGNITIONS.APPEALID = @APPEALID)
                    and (@DESIGNATIONID is null or RECOGNITIONS.DESIGNATIONID = @DESIGNATIONID)
                    and (@CONSTITUENTID is null or RECOGNITIONS.CONSTITUENTID = @CONSTITUENTID)
                    and (@REVENUERECOGNITIONTYPECODEID is null or RRTC.ID = @REVENUERECOGNITIONTYPECODEID)
                    and (@REVENUETRANSACTIONID is null or (RECOGNITIONS.REVENUEID = @REVENUETRANSACTIONID and RECOGNITIONS.RECOGNITIONCREDITTYPECODE = -1))
                    and (@ISADMIN = 1 or
                            @APPUSER_IN_NONRACROLE = 1 or
                                (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, RECOGNITIONS.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1 
                                and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, RECOGNITIONS.REVENUECONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1))
                    -- Check site security

                    and (exists (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RECOGNITIONS.REVENUESPLITID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''2A6F0BA3-334C-4a45-9A9E-34764C94AB47'', REVSITES.SITEID)
                            )  or 
                            exists (
                                    select 1 
                                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,''2A6F0BA3-334C-4a45-9A9E-34764C94AB47'', 21) -- 21 is the Feature Type value for report parameter

                                    where
                                    SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONS.DESIGNATIONID) or 
                                    (
                                        SITEID is null and 
                                        dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONS.DESIGNATIONID) is null
                                    )
                            ))
                    and (@CAMPAIGNID is null or CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1)
                    ' + nchar(13)

                set @SQLTOEXEC = @SQLTOEXEC + 
                    N'order by REC.KEYNAME, REC.FIRSTNAME, RECOGNITIONS.EFFECTIVEDATE, RRTC.DESCRIPTION, RECOGNITIONAMOUNT, RC.KEYNAME, RC.FIRSTNAME, RECOGNITIONS.DATE, RECOGNITIONS.TRANSACTIONTYPE, REVENUEAMOUNT'

                exec sp_executesql @SQLTOEXEC, 
                        N'@STARTDATE datetime, @ENDDATE datetime, @APPEALID uniqueidentifier, @DESIGNATIONID uniqueidentifier, @REVENUETRANSACTIONID uniqueidentifier, 
                        @REVENUERECOGNITIONTYPECODEID uniqueidentifier, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @CAMPAIGNID uniqueidentifier, @CURRENCYCODE tinyint',
                        @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @APPEALID=@APPEALID, @DESIGNATIONID=@DESIGNATIONID, @REVENUETRANSACTIONID=@REVENUETRANSACTIONID,
                        @REVENUERECOGNITIONTYPECODEID=@REVENUERECOGNITIONTYPECODEID, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, 
                        @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CAMPAIGNID=@CAMPAIGNID, @CURRENCYCODE=@CURRENCYCODE;