USP_REPORT_REVENUEDETAIL

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

            declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
            declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

            declare @CURRENTAPPUSERID uniqueidentifier;
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE bit;
            declare @CHECKSITESECURITY bit = 1;

            declare @BASICPROGRAMSINSTALLED bit = 0;
            if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
                set @BASICPROGRAMSINSTALLED = 1;

            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);

            if @ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1
                set @CHECKSITESECURITY = 0;
            else if not exists(select top 1 1 from dbo.[SITE])
                set @CHECKSITESECURITY = 0;

            declare @CHECKMULTICURRENCY bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency');

            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

            declare @ORIGINCODE tinyint;
            select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;
            select
                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
            from dbo.CURRENCY where CURRENCY.ID = @ORGANIZATIONCURRENCYID;

            set @SQLTOEXEC = ''
            if @CAMPAIGNID is not null
                set @SQLTOEXEC = N'
                    declare @CAMPAIGNHIERARCHYPATH hierarchyid
                    select @CAMPAIGNHIERARCHYPATH=HIERARCHYPATH
                    from dbo.CAMPAIGN
                    where ID=@CAMPAIGNID; 
                    ';

            if @BASICPROGRAMSINSTALLED = 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    with DONATIONREFUNDED_CTE as (
                        select ORDERSPLIT.ID as REVENUESPLITID, CREDITLINEITEM.BASEAMOUNT as DONATIONREFUNDED
                        from dbo.FINANCIALTRANSACTIONLINEITEM CREDITLINEITEM
                            inner join dbo.CREDITITEM_EXT on CREDITLINEITEM.ID = CREDITITEM_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLINEITEM on ORDERLINEITEM.ID = CREDITLINEITEM.SOURCELINEITEMID
                            inner join dbo.REVENUESPLIT_EXT ORDERSPLIT on ORDERLINEITEM.ID = ORDERSPLIT.ID
                        where CREDITITEM_EXT.TYPECODE = 2 and ORDERSPLIT.TYPECODE = 0 AND ORDERSPLIT.APPLICATIONCODE = 0
                    ),
                    ORDERPAYMENT_CTE as (
                        select ORDERPAYMENTLINEITEM.SOURCELINEITEMID as ID, coalesce(sum(ORDERPAYMENTLINEITEM.BASEAMOUNT), 0) as BASEAMOUNT, coalesce(sum(ORDERPAYMENTLINEITEM.TRANSACTIONAMOUNT), 0) as TRANSACTIONAMOUNT
                            from dbo.FINANCIALTRANSACTIONLINEITEM ORDERPAYMENTLINEITEM
                            inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = ORDERPAYMENTLINEITEM.FINANCIALTRANSACTIONID 
                        where ORDERPAYMENTLINEITEM.SOURCELINEITEMID is not null
                        group by ORDERPAYMENTLINEITEM.SOURCELINEITEMID
                    ),
                    ORDERITEMPAYMENT_CTE as (
                        select CONTRIBUTEDMEMBERSHIPLINEITEM.ID, isnull(ORDERPAYMENT_CTE.BASEAMOUNT, 0) as BASEAMOUNT, isnull(ORDERPAYMENT_CTE.TRANSACTIONAMOUNT, 0) as TRANSACTIONAMOUNT
                            from dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDMEMBERSHIPLINEITEM
                            inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDMEMBERSHIPSPLIT on CONTRIBUTEDMEMBERSHIPSPLIT.ID = CONTRIBUTEDMEMBERSHIPLINEITEM.ID
                            inner join dbo.SALESORDER on CONTRIBUTEDMEMBERSHIPLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
                            left join ORDERPAYMENT_CTE on CONTRIBUTEDMEMBERSHIPLINEITEM.ID = ORDERPAYMENT_CTE.ID
                        where 
                            ((CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 0 
                                and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 0 
                                and CONTRIBUTEDMEMBERSHIPLINEITEM.SOURCELINEITEMID is not null) 
                            or 
                            (CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 2 
                                and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 5 
                                and exists (select * from dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLINEITEM
                                                inner join dbo.REVENUESPLIT_EXT DONATIONSPLIT on DONATIONLINEITEM.ID = DONATIONSPLIT.ID
                                                where DONATIONSPLIT.TYPECODE = 0 and DONATIONSPLIT.APPLICATIONCODE = 0 and DONATIONLINEITEM.SOURCELINEITEMID = CONTRIBUTEDMEMBERSHIPSPLIT.ID
                            )))
                    )
                ';

            set @SQLTOEXEC = @SQLTOEXEC + N'
                select ''http://www.blackbaud.com/REVENUETRANSACTIONID?REVENUETRANSACTIONID='' + CONVERT(nvarchar(36),REVENUE.ID) as REVENUETRANSACTIONID,
                    CONSTITUENT.KEYNAME,
                    coalesce(CONSTITUENT.NAME, ''(Unidentified)'') as NAME,
                    REVENUE.DATE,
                    dbo.UDA_BUILDLIST(DISTINCT coalesce(DESIGNATION.NAME, ''None (Earned income)'')) DESIGNATIONLIST,';
            if @BASICPROGRAMSINSTALLED = 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    sum(TRANSACTIONTYPE.GIFT - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)) as GIFT,';
            else
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    sum(TRANSACTIONTYPE.GIFT) as GIFT,';

            set @SQLTOEXEC = @SQLTOEXEC + N'
                    TRANSACTIONTYPE.PLEDGEBALANCE,
                    TRANSACTIONTYPE.DISPLAYGIFTAMOUNT,
                    TRANSACTIONTYPE.[GIFTISOCURRENCYCODE],
                    TRANSACTIONTYPE.[GIFTCURRENCYSYMBOL],
                    TRANSACTIONTYPE.[GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE],
                    TRANSACTIONTYPE.[GIFTDECIMALDIGITS],
                    TRANSACTIONTYPE.[PLEDGEISOCURRENCYCODE],
                    TRANSACTIONTYPE.[PLEDGECURRENCYSYMBOL],
                    TRANSACTIONTYPE.[PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE],
                    TRANSACTIONTYPE.[PLEDGEDECIMALDIGITS]
                from dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID';
            if @DESIGNATIONID is null
            begin
                if @CHECKMULTICURRENCY = 1
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        inner join dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK
                        (
                            @ORGANIZATIONCURRENCYID,
                            @ORGANIZATIONCURRENCYID,
                            @DECIMALDIGITS,
                            @ROUNDINGTYPECODE,
                            getdate(),
                            @ORIGINCODE,
                            @CURRENCYCODE
                        ) as REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then REVENUE.BASECURRENCYID when 2 then REVENUE.TRANSACTIONCURRENCYID else @ORGANIZATIONCURRENCYID end) CURRENCYPROPERTIES';
                else
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        inner join dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK_NO_MULTICURRENCY(getdate()) as REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@ORGANIZATIONCURRENCYID) CURRENCYPROPERTIES';

            end
            else if @CHECKMULTICURRENCY = 1 and @CURRENCYCODE = 0
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    inner join (select
                        FT.ID,
                        FT.CALCULATEDDATE [DATE],
                        FT.TYPECODE TRANSACTIONTYPECODE,
                        FT.CONSTITUENTID,
                        V.BASECURRENCYID,
                        REVENUE_EXT.APPEALID
                     from dbo.FINANCIALTRANSACTION FT
                     inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
                     inner join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
                    ) as REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUE.BASECURRENCYID) CURRENCYPROPERTIES';
            else
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    inner join (select
                        FT.ID,
                        FT.CALCULATEDDATE [DATE],
                        FT.TYPECODE TRANSACTIONTYPECODE,
                        FT.CONSTITUENTID,
                        FT.TRANSACTIONCURRENCYID,
                        REVENUE_EXT.APPEALID
                     from dbo.FINANCIALTRANSACTION FT
                     inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
                    ) as REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then REVENUE.TRANSACTIONCURRENCYID when 2 then REVENUE.TRANSACTIONCURRENCYID else @ORGANIZATIONCURRENCYID end) CURRENCYPROPERTIES';
            if @BASICPROGRAMSINSTALLED = 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    left outer join ORDERITEMPAYMENT_CTE as ORDERPAYMENTFORMEMBERSHIP on ORDERPAYMENTFORMEMBERSHIP.ID = REVENUESPLIT.ID';

            if @DESIGNATIONID is not null
            begin
                if @CHECKMULTICURRENCY = 1
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        left outer join dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_2
                        (
                            @ORGANIZATIONCURRENCYID,
                            @ORGANIZATIONCURRENCYID,
                            @DECIMALDIGITS,
                            @ROUNDINGTYPECODE,
                            getdate(),
                            @ORIGINCODE,
                            @CURRENCYCODE,
                            @DESIGNATIONID,
                            @STARTDATE,
                            @ENDDATE,
                            @STARTDATEEARLIEST,
                            @ENDDATELATEST   
                        ) as PLEDGESPLIT on PLEDGESPLIT.ID = REVENUESPLIT.ID';
                else
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        left outer join dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_NO_MULTICURRENCY(getdate()) as PLEDGESPLIT on PLEDGESPLIT.ID = REVENUESPLIT.ID';
            end
            set @SQLTOEXEC = @SQLTOEXEC + N'
                outer apply
                (
                    select
                        0.00 as GIFT,';
            if @DESIGNATIONID is null
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    REVENUE.BALANCEINCURRENCY as PLEDGEBALANCE,';
            else
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    isnull(PLEDGESPLIT.BALANCE, 0) as PLEDGEBALANCE,';

            set @SQLTOEXEC = @SQLTOEXEC + N'
                        0 as DISPLAYGIFTAMOUNT,
                        null [GIFTISOCURRENCYCODE],
                        null [GIFTCURRENCYSYMBOL],
                        null [GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE],
                        null [GIFTDECIMALDIGITS],
                        CURRENCYPROPERTIES.ISO4217 [PLEDGEISOCURRENCYCODE],
                        CURRENCYPROPERTIES.CURRENCYSYMBOL [PLEDGECURRENCYSYMBOL],
                        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE],
                        CURRENCYPROPERTIES.DECIMALDIGITS [PLEDGEDECIMALDIGITS]    
                    where REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) -- PLEDGE

                    union all
                    select';
                if @BASICPROGRAMSINSTALLED = 1
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        case 
                            when REVENUESPLIT.TYPECODE = 0 and ORDERPAYMENTFORMEMBERSHIP.ID is not null 
                                then ORDERPAYMENTFORMEMBERSHIP.BASEAMOUNT 
                            else
                                case @CURRENCYCODE when 0 then REVENUESPLIT.BASEAMOUNT when 2 then REVENUESPLIT.TRANSACTIONAMOUNT else REVENUESPLIT.ORGAMOUNT end
                        end as GIFT,';
                else
                    set @SQLTOEXEC = @SQLTOEXEC + N'
                        case @CURRENCYCODE when 0 then REVENUESPLIT.BASEAMOUNT when 2 then REVENUESPLIT.TRANSACTIONAMOUNT else REVENUESPLIT.ORGAMOUNT end as GIFT,';

                set @SQLTOEXEC = @SQLTOEXEC + N'
                        0.00 as PLEDGEBALANCE,
                        1 as DISPLAYGIFTAMOUNT,
                        CURRENCYPROPERTIES.ISO4217 [GIFTISOCURRENCYCODE],
                        CURRENCYPROPERTIES.CURRENCYSYMBOL [GIFTCURRENCYSYMBOL],
                        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE],
                        CURRENCYPROPERTIES.DECIMALDIGITS [GIFTDECIMALDIGITS],
                        null [PLEDGEISOCURRENCYCODE],
                        null [PLEDGECURRENCYSYMBOL],
                        null [PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE],
                        null [PLEDGEDECIMALDIGITS]
                    where REVENUE.TRANSACTIONTYPECODE in (0,5,7) -- GIFT

                ) TRANSACTIONTYPE
                left join dbo.CONSTITUENT with (nolock) on CONSTITUENT.ID = REVENUE.CONSTITUENTID';
            if @BASICPROGRAMSINSTALLED = 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    left join DONATIONREFUNDED_CTE on DONATIONREFUNDED_CTE.REVENUESPLITID = REVENUESPLIT.ID';

            set @SQLTOEXEC = @SQLTOEXEC + N'
                left join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
            ' + nchar(13)

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

            set @SQLTOEXEC = @SQLTOEXEC + 
                N'where 
                    (REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,7,8) or
                        (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT_EXT.APPLICATIONCODE = 0) or
                        (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.DESIGNATIONID is not null) or
                        (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE in (0,2,3,4,6,7,8,12,13, 17) or
                            (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0)
                        )
                    )
                    and REVENUESPLIT.DELETEDON is null
                    and not (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE = 2 and REVENUESPLIT_EXT.APPLICATIONCODE = 3)';

            if @STARTDATE is not null and @DATETYPE != 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (REVENUE.DATE >= @STARTDATEEARLIEST)';
            if @ENDDATE is not null and @DATETYPE != 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (REVENUE.DATE <= @ENDDATELATEST)';
            if @APPEALID is not null
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (REVENUE.APPEALID = @APPEALID)';
            if @DESIGNATIONID is not null
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (DESIGNATION.ID = @DESIGNATIONID)';
            if @CONSTITUENTID is not null
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (CONSTITUENT.ID = @CONSTITUENTID)';
            if @CHECKSITESECURITY = 1
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''E886FD5F-EECF-40DE-893D-0801C059EC61'', REVSITES.SITEID)
                            ))';
            if @CAMPAIGNID is not null
                set @SQLTOEXEC = @SQLTOEXEC + N'
                    and (exists (
                            select 1
                            from dbo.REVENUESPLITCAMPAIGN 
                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
                            where REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                                and CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH) = 1
                        )
                    )
            ' + nchar(13)

            set @SQLTOEXEC = @SQLTOEXEC +
                N'group by REVENUE.ID, CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.NAME, REVENUE.DATE, TRANSACTIONTYPE.PLEDGEBALANCE, TRANSACTIONTYPE.DISPLAYGIFTAMOUNT, TRANSACTIONTYPE.GIFTISOCURRENCYCODE, TRANSACTIONTYPE.GIFTCURRENCYSYMBOL, TRANSACTIONTYPE.GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE, TRANSACTIONTYPE.GIFTDECIMALDIGITS, TRANSACTIONTYPE.PLEDGEISOCURRENCYCODE, TRANSACTIONTYPE.PLEDGECURRENCYSYMBOL, TRANSACTIONTYPE.PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE, TRANSACTIONTYPE.PLEDGEDECIMALDIGITS
                order by CONSTITUENT.KEYNAME'

            exec sp_executesql @SQLTOEXEC,                                                                                                                
                    N'@STARTDATE datetime, @ENDDATE datetime, @APPEALID uniqueidentifier, @DESIGNATIONID uniqueidentifier, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @CAMPAIGNID uniqueidentifier, @CURRENCYCODE tinyint, @CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ORIGINCODE tinyint, @STARTDATEEARLIEST datetime, @ENDDATELATEST datetime',
                    @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @APPEALID=@APPEALID, @DESIGNATIONID=@DESIGNATIONID, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CAMPAIGNID=@CAMPAIGNID, @CURRENCYCODE=@CURRENCYCODE,
                    @CURRENCYID=@ORGANIZATIONCURRENCYID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @ORIGINCODE = @ORIGINCODE, @STARTDATEEARLIEST=@STARTDATEEARLIEST, @ENDDATELATEST=@ENDDATELATEST;