USP_REPORT_REVENUERECOGNITIONCREDIT

                CREATE procedure dbo.USP_REPORT_REVENUERECOGNITIONCREDIT
                (
                    @DATETYPE tinyint = null,
                    @DATERANGEDISPLAY nvarchar(100) = '',
                    @STARTDATE datetime = null, 
                    @ENDDATE datetime = null,
                    @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                    @APPEALID uniqueidentifier = null,
                    @DESIGNATIONID uniqueidentifier = null,
                    @REVENUETRANSACTIONID 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 @DBOBJECTNAME nvarchar(128);
                declare @DBOBJECTTYPE 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 @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
                    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
                end

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

                    select
                        ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36), C.ID) as CONSTITUENTID,
                        ''http://www.blackbaud.com/REVENUETRANSACTIONID?REVENUETRANSACTIONID='' + CONVERT(nvarchar(36), R.ID) as REVENUETRANSACTIONID,
                        R.ID,
                        C_NF.NAME as CONSTITUENTNAME,
                        C.KEYNAME as CONSTITUENTKEYNAME,
                        C.FIRSTNAME as CONSTITUENTFIRSTNAME,
                        R.DATE,
                        R.TRANSACTIONTYPE as TYPE,
                        case @CURRENCYCODE when 0 then R.AMOUNT else R.ORGANIZATIONAMOUNT end as REVENUEAMOUNT,
                        RECOGNITIONS.NAME as RECOGNITIONCONSTITUENTNAME,
                        RECOGNITIONS.KEYNAME as RECOGNITIONCONSTITUENTKEYNAME,
                        RECOGNITIONS.FIRSTNAME as RECOGNITIONCONSTITUENTFIRSTNAME,
                        RECOGNITIONS.DESCRIPTION as RECOGNITIONTYPE,
                        RECOGNITIONS.EFFECTIVEDATE,
                        case @CURRENCYCODE when 0 then RECOGNITIONS.AMOUNT else RECOGNITIONS.ORGANIZATIONAMOUNT end as RECOGNITIONAMOUNT,
                        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 dbo.REVENUE R with (nolock)
                        inner join dbo.CONSTITUENT C with (nolock) on R.CONSTITUENTID = C.ID
                        inner join REVENUESPLIT on REVENUESPLIT.REVENUEID = R.ID
                        left join REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID=REVENUESPLIT.ID
                        left join CAMPAIGN on CAMPAIGN.ID=REVENUESPLITCAMPAIGN.CAMPAIGNID
                        left join
                            (
                                select
                                    RR.ID,
                                    RR.REVENUESPLITID,
                                    RRC_NF.NAME,
                                    RRC.KEYNAME,
                                    RRC.FIRSTNAME,
                                    RRTC.DESCRIPTION,
                                    RR.EFFECTIVEDATE,
                                    RR.AMOUNT,
                                    RR.ORGANIZATIONAMOUNT,
                                    RR.BASECURRENCYID
                                from dbo.REVENUERECOGNITION RR
                                    inner join dbo.CONSTITUENT RRC with (nolock) on RR.CONSTITUENTID = RRC.ID
                                    left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RR.REVENUERECOGNITIONTYPECODEID = RRTC.ID
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RRC.ID) RRC_NF
                                where (@REVENUERECOGNITIONTYPECODEID is null or RR.REVENUERECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
                            ) as RECOGNITIONS on RECOGNITIONS.REVENUESPLITID = REVENUESPLIT.ID
                        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 R.BASECURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) REVENUECURRENCYPROPERTIES
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF
                ' + nchar(13)

                if @REVENUETRANSACTIONQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on R.[ID] = SELECTION.[ID]' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where 
                        -- (Gift, Pledge, Matching Gift Claim, Planned Gift, Order, Grant award, Donor challenge), (Pledge Payment, Recurring Gift Payment, Matching Gift Payment, Grant award payment, and Donor challenge payment)

                        (R.TRANSACTIONTYPECODE in (1,3,4,5,6,7,8) or (R.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,2,3,7,8,12,13) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))))
                        and (@STARTDATE is null or R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                        and (@ENDDATE is null or R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                        and (@APPEALID is null or R.APPEALID = @APPEALID)
                        and (@REVENUETRANSACTIONID is null or R.ID = @REVENUETRANSACTIONID)                        
                        --and not (R.AMOUNT = 0 and R.TRANSACTIONAMOUNT > 0)

                        and (@ISADMIN = 1 or
                                @APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                        and exists(
                                select ID
                                from dbo.REVENUESPLIT
                                where REVENUEID=R.ID
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''000390A2-007A-441f-A7F5-C9CE92B5648B'', REVSITES.SITEID)
                            ))
                        and (@DESIGNATIONID is null or REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID)
                        and (@CAMPAIGNID is null or CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1)
                    ' + nchar(13)

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'group by C.ID, R.ID, C_NF.NAME, C.KEYNAME, C.FIRSTNAME, R.DATE, R.TRANSACTIONTYPE, R.AMOUNT, R.ORGANIZATIONAMOUNT, RECOGNITIONS.NAME, RECOGNITIONS.KEYNAME, RECOGNITIONS.FIRSTNAME, RECOGNITIONS.DESCRIPTION, RECOGNITIONS.EFFECTIVEDATE, RECOGNITIONS.AMOUNT, RECOGNITIONS.ORGANIZATIONAMOUNT, RECOGNITIONS.ID, RECOGNITIONCURRENCYPROPERTIES.ISO4217, RECOGNITIONCURRENCYPROPERTIES.CURRENCYSYMBOL,    RECOGNITIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,    RECOGNITIONCURRENCYPROPERTIES.DECIMALDIGITS, REVENUECURRENCYPROPERTIES.ISO4217, REVENUECURRENCYPROPERTIES.CURRENCYSYMBOL, REVENUECURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, REVENUECURRENCYPROPERTIES.DECIMALDIGITS
                    order by C.KEYNAME, C.FIRSTNAME, R.DATE, R.TRANSACTIONTYPE, REVENUEAMOUNT, RECOGNITIONS.KEYNAME, RECOGNITIONS.FIRSTNAME, RECOGNITIONS.DESCRIPTION, RECOGNITIONS.EFFECTIVEDATE, RECOGNITIONAMOUNT;';

                exec sp_executesql @SQLTOEXEC, 
                        N'@STARTDATE datetime, @ENDDATE datetime, @APPEALID uniqueidentifier, @DESIGNATIONID uniqueidentifier, @REVENUETRANSACTIONID uniqueidentifier, @REVENUERECOGNITIONTYPECODEID 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, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CAMPAIGNID=@CAMPAIGNID, @CURRENCYCODE=@CURRENCYCODE;