USP_REPORT_UPGRADE
Returns data for the sponsorship UPGRADE report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DIMENSION1 | nvarchar(100) | IN | |
| @DIMENSION1VALUE | nvarchar(255) | IN | |
| @DIMENSION1VALUE3 | nvarchar(255) | IN | |
| @DIMENSION2 | nvarchar(100) | IN | |
| @DIMENSION2VALUE | nvarchar(255) | IN | |
| @DIMENSION2VALUE3 | nvarchar(255) | IN | |
| @DIMENSION3 | nvarchar(100) | IN | |
| @DIMENSION3VALUE | nvarchar(255) | IN | |
| @DIMENSION3VALUE3 | nvarchar(255) | IN | |
| @REVENUEDEVELOPMENTFUNCTIONCODEID | uniqueidentifier | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @CURRENCYCODE | smallint | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_UPGRADE (
@DIMENSION1 as nvarchar(100) = null,
@DIMENSION1VALUE as nvarchar(255) = null,
@DIMENSION1VALUE3 as nvarchar(255) = null,
@DIMENSION2 as nvarchar(100) = null,
@DIMENSION2VALUE as nvarchar(255) = null,
@DIMENSION2VALUE3 as nvarchar(255) = null,
@DIMENSION3 as nvarchar(100) = null,
@DIMENSION3VALUE as nvarchar(255) = null,
@DIMENSION3VALUE3 as nvarchar(255) = null,
@REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier,
@REPORTUSERID nvarchar(128) = null,
@CURRENCYCODE smallint = null,
@ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
declare @SQLTOEXEC nvarchar(max);
declare @FROMCLAUSE nvarchar(max);
declare @COLUMNSELECT nvarchar(max);
declare @WHERECLAUSE nvarchar(max);
declare @GROUPBYCLAUSE nvarchar(max);
declare @SELECT nvarchar(max);
declare @FROM nvarchar(max);
declare @COLUMN nvarchar(max);
declare @WHERE nvarchar(max);
declare @LOCATIONCOUNT tinyint = 0;
declare @LOCATIONTYPEENUM tinyint = 2;
declare @DATECOLTEMP nvarchar(max);
declare @DIMENSIONCOLUMNS nvarchar(max) = 'DIMENSION1';
declare @RESULTSELECTION nvarchar(max);
declare @SELECTEDCURRENCYID uniqueidentifier;
declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
if @CURRENCYCODE =3
begin
if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
end
else
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
end
end
else
set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
set @FROMCLAUSE = ' from dbo.SPONSORSHIP SP' +
' inner join dbo.REVENUESPLIT SPLT on SPLT.ID = SP.REVENUESPLITID ' +
' inner join dbo.REVENUE RV on RV.ID = SPLT.REVENUEID ' +
' inner join dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED MKTSA on MKTSA.SPONSORSHIPID = SP.ID '+
--' inner join dbo.MKTSEGMENTATION SEG on SEG.ID = MKTSA.MKTSEGMENTATIONID '+
' inner join dbo.MKTSEGMENTATIONACTIVATE SEGACTIVE on SEGACTIVE.SEGMENTATIONID = MKTSA.MKTSEGMENTATIONID ';
--' left join RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY RGDFH on RGDFH.REVENUEID = RV.ID '+
--' left join REVENUEDEVELOPMENTFUNCTION RF on RF.ID = RGDFH.REVENUEDEVELOPMENTFUNCTIONID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID=@REVENUEDEVELOPMENTFUNCTIONCODEID ';
set @WHERECLAUSE = ' where SP.ID = SP.ID ';
--set @COLUMNSELECT = 'select 1 as DATECOL, 1 as YEARCOL, case when convert(date,MKTSA.DATESENT)<=RGDFH.TRANSACTIONDATE then 1 else null end as UPGRADES, ' +
set @COLUMNSELECT = 'select 1 as DATECOL, 1 as YEARCOL, SEGACTIVE.SEGMENTATIONID as SEGMENTATIONID, SPLT.REVENUEID as REVENUEID, ' +char(13)+
'(select CURRENCYSYMBOL from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID)) as CURRENCYSYMBOL, ' + char(13) +
'(select ISO4217 from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID)) as ISOCURRENCYCODE, ' + char(13) +
'(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID)) as DECIMALDIGITS, ' + char(13) +
'(select SYMBOLDISPLAYSETTINGCODE from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID)) as CURRENCYSYMBOLDISPLAYSETTINGCODE ' ;
-- count how many dimension parameters have location type specified
if @DIMENSION1 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION2 is not null and @DIMENSION2 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION3 is not null and @DIMENSION3 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION1 = 12 or @DIMENSION2 = 12 or @DIMENSION3=12
set @FROMCLAUSE = @FROMCLAUSE + ' inner join RECURRINGGIFTAMENDMENT RGDFH on RGDFH.FINANCIALTRANSACTIONID = RV.ID ';
-- only process dimension if not null and not Location Type
if @DIMENSION1 is not null and @DIMENSION1 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION1,
'@DIMENSION1VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION1'
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null';
set @GROUPBYCLAUSE = 'DIMENSION1';
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
end
-- only process dimension if it's not null and is not Location Type
if @DIMENSION2 is not null
begin
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION2';
if @DIMENSION2 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION2,
'@DIMENSION2VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION2'
-- for channel and appeal from clause is not used.
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION2VALUE3 is null';
if @GROUPBYCLAUSE is not null
begin
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
end
else
begin
set @GROUPBYCLAUSE = 'DIMENSION2';
end
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION2VALUE is null and @DIMENSION2VALUE3 is null';
end
--
if @DIMENSION3 is not null
begin
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION3';
if @DIMENSION3 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION3,
'@DIMENSION3VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION3'
-- for channel and appeal from clause is not used.
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION3VALUE3 is null';
if @GROUPBYCLAUSE is not null
begin
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION3';
end
else
begin
set @GROUPBYCLAUSE = 'DIMENSION3';
end
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION3VALUE is null and @DIMENSION3VALUE3 is null';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
set @SELECT =' count(*) CNT, (select COUNT(*) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.FINANCIALTRANSACTIONID = M.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= M.SEGMENTATIONID) AS UPGRADES, ' +
' (select sum(case when RGDFH.FREQUENCYCODE = 0 then ' +
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE, @SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/12 '+
' when RGDFH.FREQUENCYCODE = 1 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/6 '+
' when RGDFH.FREQUENCYCODE = 2 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/3 '+
' when RGDFH.FREQUENCYCODE = 3 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 4 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 5 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 6 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) / 2 '+
' when RGDFH.FREQUENCYCODE = 7 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 8 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 9 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 4 '+
' else '+
' 0 '+
' end) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.FINANCIALTRANSACTIONID =M.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= M.SEGMENTATIONID) as CHANGEMONTHLYRATE, ';
set @SELECT = @SELECT +
' (select sum(case when RGDFH.FREQUENCYCODE = 0 then ' +
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/12 '+
' when RGDFH.FREQUENCYCODE = 1 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/6 '+
' when RGDFH.FREQUENCYCODE = 2 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/3 '+
' when RGDFH.FREQUENCYCODE = 3 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 4 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 5 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 6 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) / 2 '+
' when RGDFH.FREQUENCYCODE = 7 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 8 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 9 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 4 '+
' else '+
' 0 '+
' end) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.FINANCIALTRANSACTIONID = M.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= M.SEGMENTATIONID) as MONTHLYRATE';
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
@DIMENSION3,
@COLUMNSELECT,
@FROMCLAUSE,
@WHERECLAUSE,
@GROUPBYCLAUSE,
@SQLTOEXEC output,
@SELECT,
1,
1
end
else
begin
--set @SELECT = 'select count(*) CNT, UPGRADES, avg(MONTHLYRATE) as MONTHLYRATE, avg(CHANGEMONTHLYRATE) as CHANGEMONTHLYRATE, DATECOL, YEARCOL, '+@GROUPBYCLAUSE;
set @SELECT = 'select 1 as DATECOL, 1 as YEARCOL, count(*) CNT, (select COUNT(*) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.FINANCIALTRANSACTIONID = QRY.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= QRY.SEGMENTATIONID) AS UPGRADES, ' +
' (select sum(case when RGDFH.FREQUENCYCODE = 0 then ' +
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/12 '+
' when RGDFH.FREQUENCYCODE = 1 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/6 '+
' when RGDFH.FREQUENCYCODE = 2 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE)/3 '+
' when RGDFH.FREQUENCYCODE = 3 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 4 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 5 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 6 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) / 2 '+
' when RGDFH.FREQUENCYCODE = 7 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 8 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 9 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNTCHANGE,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNTCHANGE,RGDFH.DATE) * 4 '+
' else '+
' 0 '+
' end) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.FINANCIALTRANSACTIONID = QRY.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= QRY.SEGMENTATIONID) as CHANGEMONTHLYRATE, ';
set @SELECT = @SELECT +
' (select sum(case when RGDFH.FREQUENCYCODE = 0 then ' +
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/12 '+
' when RGDFH.FREQUENCYCODE = 1 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/6 '+
' when RGDFH.FREQUENCYCODE = 2 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE)/3 '+
' when RGDFH.FREQUENCYCODE = 3 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 4 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 5 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) '+
' when RGDFH.FREQUENCYCODE = 6 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) / 2 '+
' when RGDFH.FREQUENCYCODE = 7 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 8 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 2 '+
' when RGDFH.FREQUENCYCODE = 9 then '+
' dbo.UFN_REPORT_UPGRADE_CONVERTCURRENCY(@CURRENCYCODE,RGDFH.TRANSACTIONAMOUNT,@SELECTEDCURRENCYID,RGDFH.TRANSACTIONCURRENCYID,RGDFH.TRANSACTIONAMOUNT,RGDFH.DATE) * 4 '+
' else '+
' 0 '+
' end) from dbo.RECURRINGGIFTAMENDMENT RGDFH ' +
' inner join REVENUEDEVELOPMENTFUNCTION RF on RGDFH.REVENUEDEVELOPMENTFUNCTIONID= RF.ID '+
' where RGDFH.REVENUEDEVELOPMENTFUNCTIONID is not null and RGDFH.FINANCIALTRANSACTIONID = QRY.REVENUEID and RF.REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID and RGDFH.MAILINGID= QRY.SEGMENTATIONID) as MONTHLYRATE , QRY.ISOCURRENCYCODE,QRY.DECIMALDIGITS,QRY.CURRENCYSYMBOL,QRY.CURRENCYSYMBOLDISPLAYSETTINGCODE, ' + @GROUPBYCLAUSE;
-- construct the sql query
set @SQLTOEXEC = @SELECT + ' from (' + @COLUMNSELECT +' ' +@FROMCLAUSE + ' ' + @WHERECLAUSE + ') as QRY group by DATECOL, YEARCOL, QRY.SEGMENTATIONID, QRY.REVENUEID, ' + @GROUPBYCLAUSE +',QRY.ISOCURRENCYCODE,QRY.DECIMALDIGITS,QRY.CURRENCYSYMBOL,QRY.CURRENCYSYMBOLDISPLAYSETTINGCODE';
end
-- execute the query
exec sp_executesql @SQLTOEXEC,
N'@CURRENCYCODE smallint,@SELECTEDCURRENCYID uniqueidentifier, @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier, @DIMENSION1VALUE nvarchar(255), @DIMENSION2VALUE nvarchar(255), @DIMENSION3VALUE nvarchar(255), @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255), @DIMENSION3VALUE3 nvarchar(255)',
@CURRENCYCODE=@CURRENCYCODE,@SELECTEDCURRENCYID=@SELECTEDCURRENCYID,@REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID, @DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION2VALUE=@DIMENSION2VALUE, @DIMENSION3VALUE=@DIMENSION3VALUE, @DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3, @DIMENSION3VALUE3=@DIMENSION3VALUE3;