USP_MKTSEGMENTATIONACTIVATE_UPDATEFINDERNUMBERS
Updates the marketing efforts finder numbers based on reservations and check digit logic.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_UPDATEFINDERNUMBERS]
(
@SEGMENTATIONID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null
)
as
set nocount on;
declare @MAILINGTYPECODE tinyint;
declare @FINDERTABLE nvarchar(128);
declare @DATATABLE nvarchar(128);
declare @SQL nvarchar(max);
declare @AUXILIARYIDFIELD nvarchar(17);
declare @CURRENTDATE datetime;
declare @VENDORID uniqueidentifier;
declare @MIN bigint;
declare @MAX bigint;
declare @ASSIGNED bigint;
declare @QUANTITY bigint;
declare @SEEDS integer;
declare @RESERVED bigint;
declare @OFFSET bigint;
declare @CHECKDIGIT bit;
declare @WIDTH tinyint;
declare @RANGEID uniqueidentifier;
declare @LOCKNAME nvarchar(255) = 'MKTUpdateFinderNumbers';
declare @LOCKRESULT int;
begin try
if @CHANGEAGENTID is null
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
select
@MAILINGTYPECODE = [MAILINGTYPECODE],
@FINDERTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKEFINDERTABLENAME]([ID]),
@DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID])
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
select
@CHECKDIGIT = [CHECKDIGIT],
@WIDTH = [FIXEDWIDTH]
from dbo.[MKTFINDERNUMBER];
set @AUXILIARYIDFIELD = case @MAILINGTYPECODE
when 1 then '[REVENUEID], '
when 2 then '[MEMBERSHIPID], '
when 3 then '[SPONSORSHIPID], '
when 5 then '[REVENUEID], '
else ''
end;
-- synch finder number work table, cached before calculation started, with newly-calculated data
set @SQL = 'merge dbo.[##' + @FINDERTABLE + '] as target' + char(13) +
'using (select [DATA].[DONORQUERYVIEWCATALOGID], ' + @AUXILIARYIDFIELD + '[DATA].[DONORID], [DATA].[SEGMENTID], [MKTSEGMENTATIONSEGMENT].[EXCLUDE]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DATA]' + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID])' + char(13) +
' as source ([DONORQUERYVIEWCATALOGID], ' + @AUXILIARYIDFIELD + '[DONORID], [SEGMENTID], [EXCLUDE])' + char(13) +
'on (target.[DONORQUERYVIEWCATALOGID] = source.[DONORQUERYVIEWCATALOGID]' + char(13) +
' and ' + case @MAILINGTYPECODE
when 1 then 'target.[REVENUEID] = source.[REVENUEID]' + char(13) + ' and '
when 2 then 'target.[MEMBERSHIPID] = source.[MEMBERSHIPID]' + char(13) + ' and '
when 3 then 'target.[SPONSORSHIPID] = source.[SPONSORSHIPID]' + char(13) + ' and '
when 5 then 'target.[REVENUEID] = source.[REVENUEID]' + char(13) + ' and '
else ''
end +
'target.[DONORID] = source.[DONORID])' + char(13) +
'when not matched by source then' + char(13) +
' --Remove members no longer present in the data' + char(13) +
' delete' + char(13) +
'when matched and (target.[SEGMENTID] <> source.[SEGMENTID] or target.[EXCLUDE] <> source.[EXCLUDE]) then' + char(13) +
' --Make sure the segment ID for remaining members is current' + char(13) +
' update set [SEGMENTID] = source.[SEGMENTID], [EXCLUDE] = source.[EXCLUDE], [FINDERNUMBER] = case when target.[EXCLUDE] <> source.[EXCLUDE] then null else [FINDERNUMBER] end' + char(13) +
'when not matched then' + char(13) +
' --Insert any new members' + char(13) +
' insert ([SEGMENTID], [EXCLUDE], [FINDERNUMBER], [DONORQUERYVIEWCATALOGID], ' + @AUXILIARYIDFIELD + '[DONORID])' + char(13) +
' values (source.[SEGMENTID], source.[EXCLUDE], null, source.[DONORQUERYVIEWCATALOGID], ' +
case @MAILINGTYPECODE
when 1 then 'source.[REVENUEID], '
when 2 then 'source.[MEMBERSHIPID], '
when 3 then 'source.[SPONSORSHIPID], '
when 5 then 'source.[REVENUEID], '
else ''
end +
'source.[DONORID]);';
--print @SQL + char(13);
exec (@SQL);
if @CHECKDIGIT = 1
begin
-- convert to raw finder numbers (strip check digit)
set @SQL = 'update dbo.[##' + @FINDERTABLE + '] set [FINDERNUMBER] = [FINDERNUMBER] / 10 where [FINDERNUMBER] > 0;';
--print @SQL + char(13);
exec (@SQL);
end
--Create a nonclustered index on FINDERNUMBER
set @SQL = 'create nonclustered index [IX_' + @FINDERTABLE + '_FINDERNUMBER] on [dbo].[##' + @FINDERTABLE + '] ([FINDERNUMBER] asc) include ([SEGMENTID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';
--print @SQL + char(13);
exec (@SQL);
--Create a nonclustered index on ROWNUMBER
set @SQL = 'create nonclustered index [IX_' + @FINDERTABLE + '_ROWNUMBER] on [dbo].[##' + @FINDERTABLE + '] ([ROWNUMBER] asc) include ([FINDERNUMBER], [SEGMENTID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';
--print @SQL + char(13);
exec (@SQL);
--Grab an applock so the activate and segment counts process don't conflict with overlapping finder numbers.
--We need to get this lock before we start messing with finder number ranges and the MKTSEGMENTATIONFINDERNUMBER table.
exec @LOCKRESULT = sp_getapplock @Resource=@LOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=3600000;
if @LOCKRESULT < 0
raiserror('A failure or timeout occurred while requesting an app lock to update finder numbers for a marketing effort.', 13, 1);
-- work with reservations for vendor managed lists
declare VMLVENDORS cursor local fast_forward for
select
[L].[VENDORID],
sum(isnull([SSL].[EXCHANGEQUANTITY], [SL].[EXCHANGEQUANTITY]) + isnull([SSL].[RENTALQUANTITY], [SL].[RENTALQUANTITY])) as [QUANTITY]
from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
left outer join dbo.[MKTSEGMENTATIONSEGMENTLIST] as [SSL] on [SSL].[ID] = [SS].[ID]
inner join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]
inner join dbo.[MKTSEGMENTLIST] as [SL] on [SL].[ID] = [S].[CURRENTSEGMENTLISTID]
inner join dbo.[MKTLIST] as [L] on [L].[ID] = [SL].[LISTID]
where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SL].[TYPECODE] = 1
group by [L].[VENDORID];
open VMLVENDORS;
fetch next from VMLVENDORS into @VENDORID, @QUANTITY;
while (@@FETCH_STATUS = 0)
begin
if @QUANTITY > 0
begin
if exists (select top 1 1 from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [SEGMENTATIONID] = @SEGMENTATIONID and [VENDORID] = @VENDORID)
begin
update dbo.[MKTSEGMENTATIONFINDERNUMBER] set
[INUSE] = 1,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [SEGMENTATIONID] = @SEGMENTATIONID and [VENDORID] = @VENDORID;
select @RESERVED = sum([MAX] - [MIN] + 1) from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [SEGMENTATIONID] = @SEGMENTATIONID and [VENDORID] = @VENDORID;
set @QUANTITY = @QUANTITY - @RESERVED;
end
-- if there are vendor managed list segment members left over after taking into account all of the ranges set up for this vendor...
if @QUANTITY > 0
begin
select @MIN = max([MAX])
from (
select isnull(max([MAX]), 0) as [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER]
union
select isnull(max([FINDERNUMBER]), 0) as [MAX] from dbo.[MKTFINDERNUMBER] where [FINDERNUMBER] > 0
) as [NEXT];
-- does the last range for this vendor in this mailing also represent the highest range in use at the moment?
select
@RANGEID = [ID]
from dbo.[MKTSEGMENTATIONFINDERNUMBER]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [VENDORID] = @VENDORID
and [MAX] = @MIN;
if @RANGEID is null
begin
-- if not, add a new range
set @MIN = @MIN + 1;
set @MAX = @MIN + @QUANTITY - 1;
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFINDERNUMBER] @VENDORID = @VENDORID, @SEGMENTATIONID = @SEGMENTATIONID, @MIN = @MIN, @MAX = @MAX, @ISRESERVED = 0, @INUSE = 1, @CHANGEAGENTID = @CHANGEAGENTID;
end
else
begin
-- if so, simply extend the existing range
select @MIN = [MIN], @MAX = [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [ID] = @RANGEID;
set @MAX = @MAX + @QUANTITY;
exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONFINDERNUMBER] @ID = @RANGEID, @CHANGEAGENTID = @CHANGEAGENTID, @VENDORID = @VENDORID, @MIN = @MIN, @MAX = @MAX;
end
end
end
fetch next from VMLVENDORS into @VENDORID, @QUANTITY;
end
close VMLVENDORS;
deallocate VMLVENDORS;
-- work with reservations for house file / imported lists
-- use up any available numbers in any existing ranges
declare RANGES cursor local fast_forward for
select [MIN], [MAX]
from dbo.[MKTSEGMENTATIONFINDERNUMBER]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [VENDORID] is null
order by [MIN] asc;
open RANGES;
fetch next from RANGES into @MIN, @MAX;
while (@@FETCH_STATUS) = 0
begin
if @MAILINGTYPECODE = 2 -- memberships
set @SQL = 'select @ASSIGNED = count(distinct [FINDERNUMBER]) from dbo.[##' + @FINDERTABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
else
set @SQL = 'select @ASSIGNED = count([FINDERNUMBER]) from dbo.[##' + @FINDERTABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
exec sp_executesql @SQL, N'@ASSIGNED bigint output, @MIN bigint, @MAX bigint', @ASSIGNED = @ASSIGNED output, @MIN = @MIN, @MAX = @MAX;
if @ASSIGNED < (@MAX - @MIN + 1) -- there are finder numbers remaining to be used in this range
begin
-- assign ROWNUMBER to the rows, ensuring that rows without finder numbers get the lowest numbers
-- rows without finder numbers will have ROWNUMBER 1, 2, 3...
-- windowed functions like row_number() can only be used in select and order by clauses, so shenanigans are necessary
set @SQL = 'update dbo.[##' + @FINDERTABLE + '] set' + char(13) +
' [ROWNUMBER] = [N].[ROWNUMBER]' + char(13) +
'from dbo.[##' + @FINDERTABLE + '] as [WORK]' + char(13) +
'inner join (' + char(13) +
' select [F].[DONORQUERYVIEWCATALOGID], ' +
case when @MAILINGTYPECODE = 1 then '[F].[REVENUEID], ' else '' end +
case when @MAILINGTYPECODE = 2 then '[F].[MEMBERSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 3 then '[F].[SPONSORSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 5 then '[F].[REVENUEID], ' else '' end +
'[F].[DONORID], row_number() over (order by [F].[FINDERNUMBER], [F].[SEGMENTID]) as [ROWNUMBER]' + char(13) +
' from dbo.[##' + @FINDERTABLE + '] as [F]' + char(13) +
' where [F].[EXCLUDE] = 0 and [F].[FINDERNUMBER] is null' + char(13) +
') as [N] on ([N].[DONORQUERYVIEWCATALOGID] = [WORK].[DONORQUERYVIEWCATALOGID] and ' +
case when @MAILINGTYPECODE = 1 then '[N].[REVENUEID] = [WORK].[REVENUEID] and ' else '' end +
case when @MAILINGTYPECODE = 2 then '[N].[MEMBERSHIPID] = [WORK].[MEMBERSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 3 then '[N].[SPONSORSHIPID] = [WORK].[SPONSORSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 5 then '[N].[REVENUEID] = [WORK].[REVENUEID] and ' else '' end +
'[N].[DONORID] = [WORK].[DONORID])' + char(13) +
'where [WORK].[EXCLUDE] = 0 and [WORK].[FINDERNUMBER] is null;';
--print @SQL + char(13);
exec sp_executesql @SQL;
-- assign unassigned finder numbers in this range to rows that don't have them from the UFN_MKTCOMMON_NUMBERS function,
-- using ROWNUMBER as the join field
-- numbers from UFN_MKTCOMMON_NUMBERS that aren't used will get ROWNUMBER 1, 2, 3...
-- windowed functions like row_number() can only be used in select and order by clauses, so shenanigans are again necessary
set @SQL = 'update dbo.[##' + @FINDERTABLE + '] set' + char(13) +
' [FINDERNUMBER] = [AVAILABLE].[NUMBER]' + char(13) +
'from dbo.[##' + @FINDERTABLE + '] as [WORK]' + char(13) +
'inner join (' + char(13) +
' select [NUMBERS].[NUMBER], row_number() over (order by [NUMBERS].[NUMBER]) as [ROWNUMBER]' + char(13) +
' from dbo.[##' + @FINDERTABLE + '] as [F]' + char(13) +
' right outer join dbo.[UFN_MKTCOMMON_NUMBERS](@MIN, @MAX) as [NUMBERS] on [NUMBERS].[NUMBER] = [F].[FINDERNUMBER]' + char(13) +
' where [F].[FINDERNUMBER] is null' + char(13) +
') as [AVAILABLE] on [AVAILABLE].[ROWNUMBER] = [WORK].[ROWNUMBER]' + char(13) +
'where [WORK].[EXCLUDE] = 0 and [WORK].[FINDERNUMBER] is null;';
--print @SQL + char(13);
exec sp_executesql @SQL, N'@MIN bigint, @MAX bigint', @MIN = @MIN, @MAX = @MAX;
-- Assign finder numbers to constituents if they were imported in a segmented house file.
if dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID) = 1
begin
declare @SEGMENTEDHOUSEFILETABLE nvarchar(128) = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
--If they import multiple files and import the same RECORDID multiple times with different findernumbers, then
--we need to make sure we are only working with the findernumbers from the latest import file.
set @SQL = 'with [RANKEDIMPORT] ([RECORDID], [FINDERNUMBER], [RANK]) as' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [IMPORTFILE].[RECORDID],' + char(13) +
' [IMPORTFILE].[FINDERNUMBER]' + (case when @CHECKDIGIT = 1 then ' / 10' else '' end) + ',' + char(13) +
' row_number() over (partition by [IMPORTFILE].[RECORDID] order by [MKTCONSTITUENTFILEIMPORTPROCESS].[DATEADDED] desc) as [RANK]' + char(13) +
' from dbo.[' + @SEGMENTEDHOUSEFILETABLE + '] as [IMPORTFILE]' + char(13) +
' inner join dbo.[MKTCONSTITUENTFILEIMPORTPROCESS] on [MKTCONSTITUENTFILEIMPORTPROCESS].[ID] = [IMPORTFILE].[CONSTITUENTFILEIMPORTID]' + char(13) +
'),' + char(13) +
'[LATESTIMPORT] ([RECORDID], [FINDERNUMBER]) as' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [RECORDID],' + char(13) +
' [FINDERNUMBER]' + char(13) +
' from [RANKEDIMPORT]' + char(13) +
' where [RANK] = 1' + char(13) +
')' + char(13) +
'update dbo.[##' + @FINDERTABLE + ']' + char(13) +
' set [FINDERNUMBER] = [LATESTIMPORT].[FINDERNUMBER]' + char(13) +
'from dbo.[##' + @FINDERTABLE + '] as [WORK]' + char(13) +
'inner join [LATESTIMPORT] on [LATESTIMPORT].[RECORDID] = [WORK].[DONORID]' + char(13) +
'where [LATESTIMPORT].[FINDERNUMBER] is not null' + char(13) +
'and [LATESTIMPORT].[FINDERNUMBER] between @MIN and @MAX;';
--print @SQL + char(13);
exec sp_executesql @SQL, N'@MIN bigint, @MAX bigint', @MIN = @MIN, @MAX = @MAX;
end
end
fetch next from RANGES into @MIN, @MAX;
end
close RANGES;
deallocate RANGES;
-- assign finder numbers to any members who remain, and create ranges for those numbers
set @SQL = 'select @QUANTITY = count(*)' + char(13) +
'from dbo.[##' + @FINDERTABLE + ']' + char(13) +
'where [FINDERNUMBER] is null and [EXCLUDE] = 0';
exec sp_executesql @SQL, N'@QUANTITY bigint output', @QUANTITY = @QUANTITY output;
if @QUANTITY > 0
begin
select @MIN = max([MAX]) -- not +1 because the first ROWNUMBER will be 1
from (
select isnull(max([MAX]), 0) as [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER]
union
select isnull(max([FINDERNUMBER]), 0) as [MAX] from dbo.[MKTFINDERNUMBER] where [FINDERNUMBER] > 0
) as [NEXT];
set @SQL = 'update dbo.[##' + @FINDERTABLE + '] set' + char(13) +
' [FINDERNUMBER] = [N].[ROWNUMBER] + @MIN' + char(13) +
'from dbo.[##' + @FINDERTABLE + '] as [WORK]' + char(13) +
'inner join (' + char(13) +
' select [DONORQUERYVIEWCATALOGID], ' +
case when @MAILINGTYPECODE = 1 then '[REVENUEID], [SEGMENTID], ' else '' end +
case when @MAILINGTYPECODE = 2 then '[MEMBERSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 3 then '[SPONSORSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 5 then '[REVENUEID], [SEGMENTID], ' else '' end +
'[DONORID], row_number() over (order by [FINDERNUMBER]) as [ROWNUMBER]' + char(13) +
' from dbo.[##' + @FINDERTABLE + ']' + char(13) +
' where [EXCLUDE] = 0' + char(13) +
') as [N]' + char(13) +
'on ([N].[DONORQUERYVIEWCATALOGID] = [WORK].[DONORQUERYVIEWCATALOGID] and ' +
case when @MAILINGTYPECODE = 1 then '[N].[REVENUEID] = [WORK].[REVENUEID] and [N].[SEGMENTID] = [WORK].[SEGMENTID] and ' else '' end +
case when @MAILINGTYPECODE = 2 then '[N].[MEMBERSHIPID] = [WORK].[MEMBERSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 3 then '[N].[SPONSORSHIPID] = [WORK].[SPONSORSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 5 then '[N].[REVENUEID] = [WORK].[REVENUEID] and [N].[SEGMENTID] = [WORK].[SEGMENTID] and ' else '' end +
'[N].[DONORID] = [WORK].[DONORID])' + char(13) +
'where [WORK].[EXCLUDE] = 0 and [WORK].[FINDERNUMBER] is null;';
set @MAX = @MIN + @QUANTITY;
--print @SQL + char(13);
exec sp_executesql @SQL, N'@MIN bigint', @MIN = @MIN;
-- does the last range for this mailing also represent the highest range in use at the moment?
select
@RANGEID = [ID]
from dbo.[MKTSEGMENTATIONFINDERNUMBER]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [VENDORID] is null
and [MAX] = @MIN;
if @RANGEID is null
begin
-- if not, add a new range
set @MIN = @MIN + 1;
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFINDERNUMBER] @SEGMENTATIONID = @SEGMENTATIONID, @VENDORID = null, @MIN = @MIN, @MAX = @MAX, @ISRESERVED = 0, @INUSE = 1, @CHANGEAGENTID = @CHANGEAGENTID;
end
else
begin
-- if so, simply extend the existing range
select @MIN = [MIN] from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [ID] = @RANGEID;
exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONFINDERNUMBER] @ID = @RANGEID, @CHANGEAGENTID = @CHANGEAGENTID, @VENDORID = null, @MIN = @MIN, @MAX = @MAX;
end
end
-- deal with seeds
set @SEEDS = dbo.[UFN_MKTSEGMENTATIONSEED_GETSEEDCOUNT](@SEGMENTATIONID);
if @SEEDS > 0
begin
select
@QUANTITY = sum(dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETCACHEDOFFERCOUNT]([SS].[ID]))
from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
inner join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]
left outer join dbo.[MKTSEGMENTLIST] as [SL] on [SL].[ID] = [S].[CURRENTSEGMENTLISTID]
where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SS].[EXCLUDE] = 0 and ([SL].[TYPECODE] is null or [SL].[TYPECODE] <> 1)
select
@RESERVED = sum(isnull([FN].[MAX] - [FN].[MIN] + 1, 0))
from dbo.[MKTSEGMENTATIONFINDERNUMBER] as [FN]
where [FN].[SEGMENTATIONID] = @SEGMENTATIONID
and [FN].[VENDORID] is null;
if @RESERVED < (@QUANTITY + @SEEDS)
begin
select @MIN = max([MAX]) -- not +1 because the first ROWNUMBER will be 1
from (
select isnull(max([MAX]), 0) as [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER]
union
select isnull(max([FINDERNUMBER]), 0) as [MAX] from dbo.[MKTFINDERNUMBER] where [FINDERNUMBER] > 0
) as [NEXT];
set @MAX = @MIN + ((@QUANTITY + @SEEDS) - @RESERVED);
select
@RANGEID = [ID]
from dbo.[MKTSEGMENTATIONFINDERNUMBER]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [VENDORID] is null
and [MAX] = @MIN;
if @RANGEID is null
begin
-- if not, add a new range
set @MIN = @MIN + 1;
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFINDERNUMBER] @SEGMENTATIONID = @SEGMENTATIONID, @VENDORID = null, @MIN = @MIN, @MAX = @MAX, @ISRESERVED = 0, @INUSE = 1, @CHANGEAGENTID = @CHANGEAGENTID;
end
else
begin
-- if so, simply extend the existing range
select @MIN = [MIN] from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [ID] = @RANGEID;
exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONFINDERNUMBER] @ID = @RANGEID, @CHANGEAGENTID = @CHANGEAGENTID, @VENDORID = null, @MIN = @MIN, @MAX = @MAX;
end
end
end
-- update the reservations with the width and check digit information at the time of activation
update dbo.[MKTSEGMENTATIONFINDERNUMBER] set
[CHECKDIGIT] = @CHECKDIGIT,
[FIXEDWIDTH] = @WIDTH,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [SEGMENTATIONID] = @SEGMENTATIONID;
--Release the applock for finder numbers...
if @LOCKRESULT is not null and @LOCKRESULT >= 0
exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';
set @LOCKRESULT = null;
if @CHECKDIGIT = 1
begin
--Drop the two indexes first since we don't need them anymore, and it will save it from having to update the indexes also when we add back the check-digit below...
--Drop the index on ROWNUMBER...
set @SQL = 'drop index [IX_' + @FINDERTABLE + '_ROWNUMBER] on [dbo].[##' + @FINDERTABLE + '];';
--print @SQL + char(13);
exec (@SQL);
--Drop the index on FINDERNUMBER...
set @SQL = 'drop index [IX_' + @FINDERTABLE + '_FINDERNUMBER] on [dbo].[##' + @FINDERTABLE + '];';
--print @SQL + char(13);
exec (@SQL);
end
set @SQL = 'select @QUANTITY = count(*)' + char(13) +
'from dbo.[##' + @FINDERTABLE + ']' + char(13) +
'where [FINDERNUMBER] is null and [EXCLUDE] = 1';
exec sp_executesql @SQL, N'@QUANTITY bigint output', @QUANTITY = @QUANTITY output;
if @QUANTITY > 0
begin
set @SQL = 'select @OFFSET = isnull(max(abs([FINDERNUMBER])), 0)' + char(13) +
'from dbo.[##' + @FINDERTABLE + ']' + char(13) +
'where [EXCLUDE] = 1 and [FINDERNUMBER] is not null';
exec sp_executesql @SQL, N'@OFFSET bigint output', @OFFSET = @OFFSET output;
-- assign fake, negative finder numbers to excluded members
set @SQL = 'update dbo.[##' + @FINDERTABLE + '] set' + char(13) +
' [FINDERNUMBER] = -1 * ([N].[ROWNUMBER] + @OFFSET)' + char(13) +
'from dbo.[##' + @FINDERTABLE + '] as [WORK]' + char(13) +
'inner join (' + char(13) +
' select [DONORQUERYVIEWCATALOGID], ' +
case when @MAILINGTYPECODE = 1 then '[REVENUEID], [SEGMENTID], ' else '' end +
case when @MAILINGTYPECODE = 2 then '[MEMBERSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 3 then '[SPONSORSHIPID], ' else '' end +
case when @MAILINGTYPECODE = 5 then '[REVENUEID], [SEGMENTID], ' else '' end +
'[DONORID], row_number() over (order by [FINDERNUMBER]) as [ROWNUMBER]' + char(13) +
' from dbo.[##' + @FINDERTABLE + ']' + char(13) +
' where [EXCLUDE] = 1 and [FINDERNUMBER] is null' + char(13) +
') as [N]' + char(13) +
'on ([N].[DONORQUERYVIEWCATALOGID] = [WORK].[DONORQUERYVIEWCATALOGID] and ' +
case when @MAILINGTYPECODE = 1 then '[N].[REVENUEID] = [WORK].[REVENUEID] and [N].[SEGMENTID] = [WORK].[SEGMENTID] and ' else '' end +
case when @MAILINGTYPECODE = 2 then '[N].[MEMBERSHIPID] = [WORK].[MEMBERSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 3 then '[N].[SPONSORSHIPID] = [WORK].[SPONSORSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 5 then '[N].[REVENUEID] = [WORK].[REVENUEID] and [N].[SEGMENTID] = [WORK].[SEGMENTID] and ' else '' end +
'[N].[DONORID] = [WORK].[DONORID])' + char(13) +
'where [WORK].[EXCLUDE] = 1 and [WORK].[FINDERNUMBER] is null;';
exec sp_executesql @SQL, N'@OFFSET bigint', @OFFSET = @OFFSET;
end
-- transfer the finder numbers back into the data table (and add back the check digit if necessary)...
set @SQL = 'update dbo.[' + @DATATABLE + '] set ' + char(13) +
' [FINDERNUMBER] = ' + case when @CHECKDIGIT = 1 then 'case when [WORK].[FINDERNUMBER] > 0 then dbo.[UFN_MKTFINDERNUMBER_CHECKDIGIT_MOD10]([WORK].[FINDERNUMBER]) else [WORK].[FINDERNUMBER] end' else '[WORK].[FINDERNUMBER]' end + char(13) +
'from dbo.[' + @DATATABLE + '] as [DATA]' + char(13) +
'inner join dbo.[##' + @FINDERTABLE + '] as [WORK] on (' +
'[WORK].[DONORQUERYVIEWCATALOGID] = [DATA].[DONORQUERYVIEWCATALOGID] and ' +
case when @MAILINGTYPECODE = 1 then '[WORK].[REVENUEID] = [DATA].[REVENUEID] and [WORK].[SEGMENTID] = [DATA].[SEGMENTID] and ' else '' end +
case when @MAILINGTYPECODE = 2 then '[WORK].[MEMBERSHIPID] = [DATA].[MEMBERSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 3 then '[WORK].[SPONSORSHIPID] = [DATA].[SPONSORSHIPID] and ' else '' end +
case when @MAILINGTYPECODE = 5 then '[WORK].[REVENUEID] = [DATA].[REVENUEID] and [WORK].[SEGMENTID] = [DATA].[SEGMENTID] and ' else '' end +
'[WORK].[DONORID] = [DATA].[DONORID])'
--print @SQL + char(13);
exec (@SQL);
-- drop finder number work table
set @SQL = 'drop table dbo.[##' + @FINDERTABLE + '];';
--print @SQL + char(13);
exec (@SQL);
-- make finder number column not null
set @SQL = 'alter table dbo.[' + @DATATABLE + '] alter column [FINDERNUMBER] bigint not null;';
--print @SQL + char(13);
exec (@SQL);
-- add back finder number unique index
set @SQL = 'create unique nonclustered index [UIX_' + @DATATABLE + '_FINDERNUMBER] on dbo.[' + @DATATABLE + '] ([FINDERNUMBER] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when serverproperty('engineedition') = 3 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
--print @SQL + char(13);
exec (@SQL);
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
--Release the applock for finder numbers if an error occurs...
if @LOCKRESULT is not null and @LOCKRESULT >= 0
exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';
set @LOCKRESULT = null;
return 1;
end catch
return 0;