USP_DATALIST_MKTMARKETINGPLANSTATUS
Returns a list of all plan items for a specific plan with revenue and expense information for each.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PLANID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @CURRENCYCODE | tinyint | IN | Currency |
Definition
Copy
CREATE procedure [dbo].[USP_DATALIST_MKTMARKETINGPLANSTATUS]
(
@PLANID uniqueidentifier = null,
@CURRENCYCODE tinyint = 1
)
as
set nocount on;
begin try
-- get number of levels in the plan
declare @LEVELS integer;
select
@LEVELS = count([ID])
from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM]
where [MARKETINGPLANID] = @PLANID;
declare @RETURNTABLE table (
[SITE] nvarchar(1024),
[CURRENCYISOCURRENCYCODE] nvarchar(6),
[CURRENCYDECIMALDIGITS] tinyint,
[CURRENCYSYMBOL] nvarchar(10),
[CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint,
[LEVELS] integer,
[LEVEL0ID] uniqueidentifier,
[LEVEL0NAME] nvarchar(100),
[LEVEL0CAPTION] nvarchar(50),
[LEVEL0EXPBUDGET] money,
[LEVEL0EXPREVENUE] money,
[LEVEL0EXPQUANTITY] integer,
[LEVEL0EXPRESPONSES] integer,
[LEVEL0EXPRESPONSERATE] float,
[LEVEL1ID] uniqueidentifier,
[LEVEL1NAME] nvarchar(100),
[LEVEL1CAPTION] nvarchar(50),
[LEVEL1EXPBUDGET] money,
[LEVEL1EXPREVENUE] money,
[LEVEL1EXPQUANTITY] integer,
[LEVEL1EXPRESPONSES] integer,
[LEVEL1EXPRESPONSERATE] float,
[LEVEL2ID] uniqueidentifier,
[LEVEL2NAME] nvarchar(100),
[LEVEL2CAPTION] nvarchar(50),
[LEVEL2EXPBUDGET] money,
[LEVEL2EXPREVENUE] money,
[LEVEL2EXPQUANTITY] integer,
[LEVEL2EXPRESPONSES] integer,
[LEVEL2EXPRESPONSERATE] float,
[LEVEL3ID] uniqueidentifier,
[LEVEL3NAME] nvarchar(100),
[LEVEL3CAPTION] nvarchar(50),
[LEVEL3EXPBUDGET] money,
[LEVEL3EXPREVENUE] money,
[LEVEL3EXPQUANTITY] integer,
[LEVEL3EXPRESPONSES] integer,
[LEVEL3EXPRESPONSERATE] float,
[LEVEL4ID] uniqueidentifier,
[LEVEL4NAME] nvarchar(100),
[LEVEL4CAPTION] nvarchar(50),
[LEVEL4EXPBUDGET] money,
[LEVEL4EXPREVENUE] money,
[LEVEL4EXPQUANTITY] integer,
[LEVEL4EXPRESPONSES] integer,
[LEVEL4EXPRESPONSERATE] float,
[OFFERS] integer,
[RESPONSES] integer,
[REVENUE] money,
[COST] money
);
declare @RESPONSETABLE table (
[OFFERS] integer,
[RESPONDERS] integer,
[RESPONSES] integer,
[TOTALGIFTAMOUNT] money,
[RESPONSERATE] decimal,
[TOTALORGANIZATIONGIFTAMOUNT] money,
[FIRSTRESPONSEDATE] datetime
);
declare @COSTTABLE table (
[COST] money
);
declare @OFFERS integer;
declare @RESPONSES integer;
declare @REVENUE money;
declare @COSTS money;
declare @MAILINGID uniqueidentifier;
---------------------------------------------------------------------------------------------------------------------
-- BEGIN LEVEL 0
---------------------------------------------------------------------------------------------------------------------
declare LEVEL0CURSOR cursor local fast_forward for
select
[MPI].[ID],
[MPI].[NAME],
[MPITI].[CAPTION],
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)
as [BUDGETAMOUNT],
case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end as [TOTALREVENUEGOAL],
[MPI].[TOTALTRANSACTIONSGOAL],
[MPI].[RESPONSERATEGOAL],
[MPI].[QUANTITY]
from dbo.[MKTMARKETINGPLANITEM] as [MPI]
inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[LEVEL] = [MPI].[LEVEL] and [MPITI].[MARKETINGPLANID] = @PLANID
where [MPI].[MARKETINGPLANID] = @PLANID
and [MPI].[LEVEL] = 0;
declare @LEVEL0ID uniqueidentifier;
declare @LEVEL0NAME nvarchar(100);
declare @LEVEL0CAPTION nvarchar(50);
declare @LEVEL0EXPBUDGET money;
declare @LEVEL0EXPREVENUE money;
declare @LEVEL0EXPRESPONSES integer;
declare @LEVEL0EXPRESPONSERATE float;
declare @LEVEL0EXPQUANTITY integer;
open LEVEL0CURSOR;
fetch next from LEVEL0CURSOR into @LEVEL0ID, @LEVEL0NAME, @LEVEL0CAPTION, @LEVEL0EXPBUDGET, @LEVEL0EXPREVENUE, @LEVEL0EXPRESPONSES, @LEVEL0EXPRESPONSERATE, @LEVEL0EXPQUANTITY;
while (@@FETCH_STATUS = 0)
begin
---------------------------------------------------------------------------------------------------------------------
-- BEGIN LEVEL 1
---------------------------------------------------------------------------------------------------------------------
declare LEVEL1CURSOR cursor local fast_forward for
select
[MPI].[ID],
[MPI].[NAME],
[MPITI].[CAPTION],
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)
as [BUDGETAMOUNT],
case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end as [TOTALREVENUEGOAL],
[MPI].[TOTALTRANSACTIONSGOAL],
[MPI].[RESPONSERATEGOAL],
[MPI].[QUANTITY]
from dbo.[MKTMARKETINGPLANITEM] as [MPI]
inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[LEVEL] = [MPI].[LEVEL] and [MPITI].[MARKETINGPLANID] = @PLANID
where [MPI].[PARENTMARKETINGPLANITEMID] = @LEVEL0ID;
declare @LEVEL1ID uniqueidentifier;
declare @LEVEL1NAME nvarchar(100);
declare @LEVEL1CAPTION nvarchar(50);
declare @LEVEL1EXPBUDGET money;
declare @LEVEL1EXPREVENUE money;
declare @LEVEL1EXPRESPONSES integer;
declare @LEVEL1EXPRESPONSERATE float;
declare @LEVEL1EXPQUANTITY integer;
open LEVEL1CURSOR;
fetch next from LEVEL1CURSOR into @LEVEL1ID, @LEVEL1NAME, @LEVEL1CAPTION, @LEVEL1EXPBUDGET, @LEVEL1EXPREVENUE, @LEVEL1EXPRESPONSES, @LEVEL1EXPRESPONSERATE, @LEVEL1EXPQUANTITY;
if @@FETCH_STATUS <> 0
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1NAME],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
'__NO_ITEM_EXISTS__',
0,
0,
0,
0
);
while (@@FETCH_STATUS = 0)
begin
if @LEVELS > 2
begin
---------------------------------------------------------------------------------------------------------------------
-- BEGIN LEVEL 2
---------------------------------------------------------------------------------------------------------------------
declare LEVEL2CURSOR cursor local fast_forward for
select
[MPI].[ID],
[MPI].[NAME],
[MPITI].[CAPTION],
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)
as [BUDGETAMOUNT],
case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end as [TOTALREVENUEGOAL],
[MPI].[TOTALTRANSACTIONSGOAL],
[MPI].[RESPONSERATEGOAL],
[MPI].[QUANTITY]
from dbo.[MKTMARKETINGPLANITEM] as [MPI]
inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[LEVEL] = [MPI].[LEVEL] and [MPITI].[MARKETINGPLANID] = @PLANID
where [MPI].[PARENTMARKETINGPLANITEMID] = @LEVEL1ID;
declare @LEVEL2ID uniqueidentifier;
declare @LEVEL2NAME nvarchar(100);
declare @LEVEL2CAPTION nvarchar(50);
declare @LEVEL2EXPBUDGET money;
declare @LEVEL2EXPREVENUE money;
declare @LEVEL2EXPRESPONSES integer;
declare @LEVEL2EXPRESPONSERATE float;
declare @LEVEL2EXPQUANTITY integer;
open LEVEL2CURSOR;
fetch next from LEVEL2CURSOR into @LEVEL2ID, @LEVEL2NAME, @LEVEL2CAPTION, @LEVEL2EXPBUDGET, @LEVEL2EXPREVENUE, @LEVEL2EXPRESPONSES, @LEVEL2EXPRESPONSERATE, @LEVEL2EXPQUANTITY;
if @@FETCH_STATUS <> 0
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2NAME],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
'__NO_ITEM_EXISTS__',
0,
0,
0,
0
);
while (@@FETCH_STATUS = 0)
begin
if @LEVELS > 3
begin
---------------------------------------------------------------------------------------------------------------------
-- BEGIN LEVEL 3
---------------------------------------------------------------------------------------------------------------------
declare LEVEL3CURSOR cursor local fast_forward for
select
[MPI].[ID],
[MPI].[NAME],
[MPITI].[CAPTION],
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)
as [BUDGETAMOUNT],
case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end as [TOTALREVENUEGOAL],
[MPI].[TOTALTRANSACTIONSGOAL],
[MPI].[RESPONSERATEGOAL],
[MPI].[QUANTITY]
from dbo.[MKTMARKETINGPLANITEM] as [MPI]
inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[LEVEL] = [MPI].[LEVEL] and [MPITI].[MARKETINGPLANID] = @PLANID
where [MPI].[PARENTMARKETINGPLANITEMID] = @LEVEL2ID;
declare @LEVEL3ID uniqueidentifier;
declare @LEVEL3NAME nvarchar(100);
declare @LEVEL3CAPTION nvarchar(50);
declare @LEVEL3EXPBUDGET money;
declare @LEVEL3EXPREVENUE money;
declare @LEVEL3EXPRESPONSES integer;
declare @LEVEL3EXPRESPONSERATE float;
declare @LEVEL3EXPQUANTITY integer;
open LEVEL3CURSOR;
fetch next from LEVEL3CURSOR into @LEVEL3ID, @LEVEL3NAME, @LEVEL3CAPTION, @LEVEL3EXPBUDGET, @LEVEL3EXPREVENUE, @LEVEL3EXPRESPONSES, @LEVEL3EXPRESPONSERATE, @LEVEL3EXPQUANTITY;
if @@FETCH_STATUS <> 0
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[LEVEL3NAME],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@LEVEL2ID,
@LEVEL2NAME,
@LEVEL2CAPTION,
@LEVEL2EXPBUDGET,
@LEVEL2EXPREVENUE,
@LEVEL2EXPQUANTITY,
@LEVEL2EXPRESPONSES,
@LEVEL2EXPRESPONSERATE,
'__NO_ITEM_EXISTS__',
0,
0,
0,
0
);
while (@@FETCH_STATUS = 0)
begin
if @LEVELS > 4
begin
---------------------------------------------------------------------------------------------------------------------
-- BEGIN LEVEL 4
---------------------------------------------------------------------------------------------------------------------
declare LEVEL4CURSOR cursor local fast_forward for
select
[MPI].[ID],
[MPI].[NAME],
[MPITI].[CAPTION],
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
coalesce((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)
as [BUDGETAMOUNT],
case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end as [TOTALREVENUEGOAL],
[MPI].[TOTALTRANSACTIONSGOAL],
[MPI].[RESPONSERATEGOAL],
[MPI].[QUANTITY]
from dbo.[MKTMARKETINGPLANITEM] as [MPI]
inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[LEVEL] = [MPI].[LEVEL] and [MPITI].[MARKETINGPLANID] = @PLANID
where [MPI].[PARENTMARKETINGPLANITEMID] = @LEVEL3ID;
declare @LEVEL4ID uniqueidentifier;
declare @LEVEL4NAME nvarchar(100);
declare @LEVEL4CAPTION nvarchar(50);
declare @LEVEL4EXPBUDGET money;
declare @LEVEL4EXPREVENUE money;
declare @LEVEL4EXPRESPONSES integer;
declare @LEVEL4EXPRESPONSERATE float;
declare @LEVEL4EXPQUANTITY integer;
open LEVEL4CURSOR;
fetch next from LEVEL4CURSOR into @LEVEL4ID, @LEVEL4NAME, @LEVEL4CAPTION, @LEVEL4EXPBUDGET, @LEVEL4EXPREVENUE, @LEVEL4EXPRESPONSES, @LEVEL4EXPRESPONSERATE, @LEVEL4EXPQUANTITY;
if @@FETCH_STATUS <> 0
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[LEVEL3ID],
[LEVEL3NAME],
[LEVEL3CAPTION],
[LEVEL3EXPBUDGET],
[LEVEL3EXPREVENUE],
[LEVEL3EXPQUANTITY],
[LEVEL3EXPRESPONSES],
[LEVEL3EXPRESPONSERATE],
[LEVEL4NAME],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@LEVEL2ID,
@LEVEL2NAME,
@LEVEL2CAPTION,
@LEVEL2EXPBUDGET,
@LEVEL2EXPREVENUE,
@LEVEL2EXPQUANTITY,
@LEVEL2EXPRESPONSES,
@LEVEL2EXPRESPONSERATE,
@LEVEL3ID,
@LEVEL3NAME,
@LEVEL3CAPTION,
@LEVEL3EXPBUDGET,
@LEVEL3EXPREVENUE,
@LEVEL3EXPQUANTITY,
@LEVEL3EXPRESPONSES,
@LEVEL3EXPRESPONSERATE,
'__NO_ITEM_EXISTS__',
0,
0,
0,
0
);
while (@@FETCH_STATUS = 0)
begin
set @MAILINGID = null;
select
@MAILINGID = [S].[ID]
from dbo.[MKTSEGMENTATION] as [S]
inner join dbo.[MKTSEGMENTATIONBUDGET] as [SB] on [SB].[ID] = [S].[ID]
where [S].[MARKETINGPLANITEMID] = @LEVEL4ID
and [S].[ACTIVE] = 1;
if @MAILINGID is not null
begin
-- multicurrency to do
insert into @RESPONSETABLE
exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @MAILINGID;
select
@OFFERS = [OFFERS],
@RESPONSES = [RESPONSES],
@REVENUE = [TOTALGIFTAMOUNT]
from @RESPONSETABLE;
delete from @RESPONSETABLE;
-- multicurrency to do
insert into @COSTTABLE
exec dbo.[USP_MKTSEGMENTATION_GETACTIVETOTALCOST] @SEGMENTATIONID = @MAILINGID;
select
@COSTS = [COST]
from @COSTTABLE;
delete from @COSTTABLE;
end
else
begin
set @OFFERS = 0;
set @RESPONSES = 0;
set @REVENUE = 0;
set @COSTS = 0;
end
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[LEVEL3ID],
[LEVEL3NAME],
[LEVEL3CAPTION],
[LEVEL3EXPBUDGET],
[LEVEL3EXPREVENUE],
[LEVEL3EXPQUANTITY],
[LEVEL3EXPRESPONSES],
[LEVEL3EXPRESPONSERATE],
[LEVEL4ID],
[LEVEL4NAME],
[LEVEL4CAPTION],
[LEVEL4EXPBUDGET],
[LEVEL4EXPREVENUE],
[LEVEL4EXPQUANTITY],
[LEVEL4EXPRESPONSES],
[LEVEL4EXPRESPONSERATE],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@LEVEL2ID,
@LEVEL2NAME,
@LEVEL2CAPTION,
@LEVEL2EXPBUDGET,
@LEVEL2EXPREVENUE,
@LEVEL2EXPQUANTITY,
@LEVEL2EXPRESPONSES,
@LEVEL2EXPRESPONSERATE,
@LEVEL3ID,
@LEVEL3NAME,
@LEVEL3CAPTION,
@LEVEL3EXPBUDGET,
@LEVEL3EXPREVENUE,
@LEVEL3EXPQUANTITY,
@LEVEL3EXPRESPONSES,
@LEVEL3EXPRESPONSERATE,
@LEVEL4ID,
@LEVEL4NAME,
@LEVEL4CAPTION,
@LEVEL4EXPBUDGET,
@LEVEL4EXPREVENUE,
@LEVEL4EXPQUANTITY,
@LEVEL4EXPRESPONSES,
@LEVEL4EXPRESPONSERATE,
@OFFERS,
@RESPONSES,
@REVENUE,
@COSTS
);
fetch next from LEVEL4CURSOR into @LEVEL4ID, @LEVEL4NAME, @LEVEL4CAPTION, @LEVEL4EXPBUDGET, @LEVEL4EXPREVENUE, @LEVEL4EXPRESPONSES, @LEVEL4EXPRESPONSERATE, @LEVEL4EXPQUANTITY;
end
close LEVEL4CURSOR;
deallocate LEVEL4CURSOR;
---------------------------------------------------------------------------------------------------------------------
-- END LEVEL 4
---------------------------------------------------------------------------------------------------------------------
end
else
begin
set @MAILINGID = null;
select
@MAILINGID = [S].[ID]
from dbo.[MKTSEGMENTATION] as [S]
inner join dbo.[MKTSEGMENTATIONBUDGET] as [SB] on [SB].[ID] = [S].[ID]
where [S].[MARKETINGPLANITEMID] = @LEVEL3ID
and [S].[ACTIVE] = 1;
if @MAILINGID is not null
begin
-- multicurrency to do
insert into @RESPONSETABLE
exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @MAILINGID;
select
@OFFERS = [OFFERS],
@RESPONSES = [RESPONSES],
@REVENUE = [TOTALGIFTAMOUNT]
from @RESPONSETABLE;
delete from @RESPONSETABLE;
-- multicurrency to do
insert into @COSTTABLE
exec dbo.[USP_MKTSEGMENTATION_GETACTIVETOTALCOST] @SEGMENTATIONID = @MAILINGID;
select
@COSTS = [COST]
from @COSTTABLE;
delete from @COSTTABLE;
end
else
begin
set @OFFERS = 0;
set @RESPONSES = 0;
set @REVENUE = 0;
set @COSTS = 0;
end
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[LEVEL3ID],
[LEVEL3NAME],
[LEVEL3CAPTION],
[LEVEL3EXPBUDGET],
[LEVEL3EXPREVENUE],
[LEVEL3EXPQUANTITY],
[LEVEL3EXPRESPONSES],
[LEVEL3EXPRESPONSERATE],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@LEVEL2ID,
@LEVEL2NAME,
@LEVEL2CAPTION,
@LEVEL2EXPBUDGET,
@LEVEL2EXPREVENUE,
@LEVEL2EXPQUANTITY,
@LEVEL2EXPRESPONSES,
@LEVEL2EXPRESPONSERATE,
@LEVEL3ID,
@LEVEL3NAME,
@LEVEL3CAPTION,
@LEVEL3EXPBUDGET,
@LEVEL3EXPREVENUE,
@LEVEL3EXPQUANTITY,
@LEVEL3EXPRESPONSES,
@LEVEL3EXPRESPONSERATE,
@OFFERS,
@RESPONSES,
@REVENUE,
@COSTS
);
end
fetch next from LEVEL3CURSOR into @LEVEL3ID, @LEVEL3NAME, @LEVEL3CAPTION, @LEVEL3EXPBUDGET, @LEVEL3EXPREVENUE, @LEVEL3EXPRESPONSES, @LEVEL3EXPRESPONSERATE, @LEVEL3EXPQUANTITY;
end
close LEVEL3CURSOR;
deallocate LEVEL3CURSOR;
---------------------------------------------------------------------------------------------------------------------
-- END LEVEL 3
---------------------------------------------------------------------------------------------------------------------
end
else
begin
set @MAILINGID = null;
select
@MAILINGID = [S].[ID]
from dbo.[MKTSEGMENTATION] as [S]
inner join dbo.[MKTSEGMENTATIONBUDGET] as [SB] on [SB].[ID] = [S].[ID]
where [S].[MARKETINGPLANITEMID] = @LEVEL2ID
and [S].[ACTIVE] = 1;
if @MAILINGID is not null
begin
-- multicurrency to do
insert into @RESPONSETABLE
exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @MAILINGID;
select
@OFFERS = [OFFERS],
@RESPONSES = [RESPONSES],
@REVENUE = [TOTALGIFTAMOUNT]
from @RESPONSETABLE;
delete from @RESPONSETABLE;
-- multicurrency to do
insert into @COSTTABLE
exec dbo.[USP_MKTSEGMENTATION_GETACTIVETOTALCOST] @SEGMENTATIONID = @MAILINGID;
select
@COSTS = [COST]
from @COSTTABLE;
delete from @COSTTABLE;
end
else
begin
set @OFFERS = 0;
set @RESPONSES = 0;
set @REVENUE = 0;
set @COSTS = 0;
end
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@LEVEL2ID,
@LEVEL2NAME,
@LEVEL2CAPTION,
@LEVEL2EXPBUDGET,
@LEVEL2EXPREVENUE,
@LEVEL2EXPQUANTITY,
@LEVEL2EXPRESPONSES,
@LEVEL2EXPRESPONSERATE,
@OFFERS,
@RESPONSES,
@REVENUE,
@COSTS
);
end
fetch next from LEVEL2CURSOR into @LEVEL2ID, @LEVEL2NAME, @LEVEL2CAPTION, @LEVEL2EXPBUDGET, @LEVEL2EXPREVENUE, @LEVEL2EXPRESPONSES, @LEVEL2EXPRESPONSERATE, @LEVEL2EXPQUANTITY;
end
close LEVEL2CURSOR;
deallocate LEVEL2CURSOR;
---------------------------------------------------------------------------------------------------------------------
-- END LEVEL 2
---------------------------------------------------------------------------------------------------------------------
end
else
begin
set @MAILINGID = null;
select
@MAILINGID = [S].[ID]
from dbo.[MKTSEGMENTATION] as [S]
inner join dbo.[MKTSEGMENTATIONBUDGET] as [SB] on [SB].[ID] = [S].[ID]
where [S].[MARKETINGPLANITEMID] = @LEVEL1ID
and [S].[ACTIVE] = 1;
if @MAILINGID is not null
begin
-- multicurrency to do
insert into @RESPONSETABLE
exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @MAILINGID;
select
@OFFERS = [OFFERS],
@RESPONSES = [RESPONSES],
@REVENUE = [TOTALGIFTAMOUNT]
from @RESPONSETABLE;
delete from @RESPONSETABLE;
-- multicurrency to do
insert into @COSTTABLE
exec dbo.[USP_MKTSEGMENTATION_GETACTIVETOTALCOST] @SEGMENTATIONID = @MAILINGID;
select
@COSTS = [COST]
from @COSTTABLE;
delete from @COSTTABLE;
end
else
begin
set @OFFERS = 0;
set @RESPONSES = 0;
set @REVENUE = 0;
set @COSTS = 0;
end
insert into @RETURNTABLE (
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
) values (
@LEVELS,
@LEVEL0ID,
@LEVEL0NAME,
@LEVEL0CAPTION,
@LEVEL0EXPBUDGET,
@LEVEL0EXPREVENUE,
@LEVEL0EXPQUANTITY,
@LEVEL0EXPRESPONSES,
@LEVEL0EXPRESPONSERATE,
@LEVEL1ID,
@LEVEL1NAME,
@LEVEL1CAPTION,
@LEVEL1EXPBUDGET,
@LEVEL1EXPREVENUE,
@LEVEL1EXPQUANTITY,
@LEVEL1EXPRESPONSES,
@LEVEL1EXPRESPONSERATE,
@OFFERS,
@RESPONSES,
@REVENUE,
@COSTS
);
end
fetch next from LEVEL1CURSOR into @LEVEL1ID, @LEVEL1NAME, @LEVEL1CAPTION, @LEVEL1EXPBUDGET, @LEVEL1EXPREVENUE, @LEVEL1EXPRESPONSES, @LEVEL1EXPRESPONSERATE, @LEVEL1EXPQUANTITY;
end
close LEVEL1CURSOR;
deallocate LEVEL1CURSOR;
---------------------------------------------------------------------------------------------------------------------
-- END LEVEL 1
---------------------------------------------------------------------------------------------------------------------
fetch next from LEVEL0CURSOR into @LEVEL0ID, @LEVEL0NAME, @LEVEL0CAPTION, @LEVEL0EXPBUDGET, @LEVEL0EXPREVENUE, @LEVEL0EXPRESPONSES, @LEVEL0EXPRESPONSERATE, @LEVEL0EXPQUANTITY;
end
close LEVEL0CURSOR;
deallocate LEVEL0CURSOR;
---------------------------------------------------------------------------------------------------------------------
-- END LEVEL 0
---------------------------------------------------------------------------------------------------------------------
declare @SITE nvarchar(100);
set @SITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME](dbo.[UFN_SITEID_MAPFROM_MARKETINGPLANID](@PLANID));
declare @CURRENCYISOCURRENCYCODE nvarchar(6);
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYSYMBOL nvarchar(10);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
select
@CURRENCYISOCURRENCYCODE = [ISO4217],
@CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
@CURRENCYSYMBOL = [CURRENCYSYMBOL],
@CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
from dbo.[CURRENCY]
inner join dbo.[MKTMARKETINGPLAN] as [MP] on case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MP].[BASECURRENCYID] end = [CURRENCY].[ID]
where [MP].[ID] = @PLANID;
update @RETURNTABLE set
[SITE] = @SITE,
[CURRENCYISOCURRENCYCODE] = @CURRENCYISOCURRENCYCODE,
[CURRENCYDECIMALDIGITS] = @CURRENCYDECIMALDIGITS,
[CURRENCYSYMBOL] = @CURRENCYSYMBOL,
[CURRENCYSYMBOLDISPLAYSETTINGCODE] = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
select
[SITE],
[CURRENCYISOCURRENCYCODE],
[CURRENCYDECIMALDIGITS],
[CURRENCYSYMBOL],
[CURRENCYSYMBOLDISPLAYSETTINGCODE],
[LEVELS],
[LEVEL0ID],
[LEVEL0NAME],
[LEVEL0CAPTION],
[LEVEL0EXPBUDGET],
[LEVEL0EXPREVENUE],
[LEVEL0EXPQUANTITY],
[LEVEL0EXPRESPONSES],
[LEVEL0EXPRESPONSERATE],
[LEVEL1ID],
[LEVEL1NAME],
[LEVEL1CAPTION],
[LEVEL1EXPBUDGET],
[LEVEL1EXPREVENUE],
[LEVEL1EXPQUANTITY],
[LEVEL1EXPRESPONSES],
[LEVEL1EXPRESPONSERATE],
[LEVEL2ID],
[LEVEL2NAME],
[LEVEL2CAPTION],
[LEVEL2EXPBUDGET],
[LEVEL2EXPREVENUE],
[LEVEL2EXPQUANTITY],
[LEVEL2EXPRESPONSES],
[LEVEL2EXPRESPONSERATE],
[LEVEL3ID],
[LEVEL3NAME],
[LEVEL3CAPTION],
[LEVEL3EXPBUDGET],
[LEVEL3EXPREVENUE],
[LEVEL3EXPQUANTITY],
[LEVEL3EXPRESPONSES],
[LEVEL3EXPRESPONSERATE],
[LEVEL4ID],
[LEVEL4NAME],
[LEVEL4CAPTION],
[LEVEL4EXPBUDGET],
[LEVEL4EXPREVENUE],
[LEVEL4EXPQUANTITY],
[LEVEL4EXPRESPONSES],
[LEVEL4EXPRESPONSERATE],
[OFFERS],
[RESPONSES],
[REVENUE],
[COST]
from
@RETURNTABLE
order by
[LEVEL0NAME];
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;