USP_DATAFORMTEMPLATE_EDIT_BULKASSIGNSOLICITORS_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ASSIGNMENTS | xml | IN | |
| @FUNDRAISERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BULKASSIGNSOLICITORS_2 (
@CHANGEAGENTID uniqueidentifier = null,
@ASSIGNMENTS xml,
@FUNDRAISERID uniqueidentifier
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
-- build a temporary table containing the values from the XML
declare @TempTbl table (
ID uniqueidentifier,
PROSPECTID uniqueidentifier,
TYPECODE tinyint,
PROSPECTPLANID uniqueidentifier,
SECONDARYID uniqueidentifier,
ASSIGNTOID uniqueidentifier,
PROSPECTTEAMROLECODEID uniqueidentifier,
STARTDATE datetime);
insert into @TempTbl (
ID,
PROSPECTID,
TYPECODE,
PROSPECTPLANID,
SECONDARYID,
ASSIGNTOID,
PROSPECTTEAMROLECODEID,
STARTDATE)
select
newid(),
T.c.value('(PROSPECTID)[1]','uniqueidentifier') AS 'PROSPECTID',
T.c.value('(TYPECODE)[1]','tinyint') AS 'TYPECODE',
case when len(T.c.value('(PROSPECTPLANID)[1]','nvarchar(36)')) = 36 then T.c.value('(PROSPECTPLANID)[1]','uniqueidentifier') else null end AS 'PROSPECTPLANID',
case when len(T.c.value('(SECONDARYID)[1]','nvarchar(36)')) = 36 then T.c.value('(SECONDARYID)[1]','uniqueidentifier') else null end AS 'SECONDARYID',
case when T.c.value('(ASSIGNTOID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then null else T.c.value('(ASSIGNTOID)[1]','uniqueidentifier') end AS 'ASSIGNTOID',
case when len(T.c.value('(PROSPECTTEAMROLECODEID)[1]','nvarchar(36)')) = 36 then T.c.value('(PROSPECTTEAMROLECODEID)[1]','uniqueidentifier') else null end AS 'PROSPECTTEAMROLECODEID',
dbo.UFN_DATE_GETEARLIESTTIME(T.c.value('(STARTDATE)[1]','datetime')) AS 'STARTDATE'
from
@ASSIGNMENTS.nodes('/ASSIGNMENTS/ITEM') T(c)
where
--PBI#237207 - Arun Saini - Identify changed records. Records where ASSIGNTOID is not equal to FUNDRAISERID
isnull(T.c.value('(ASSIGNTOID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000') <> isnull(@FUNDRAISERID, '00000000-0000-0000-0000-000000000000');
--Create cursor for alerts
declare PROSPECTCURSOR cursor local fast_forward for
select
PROSPECTID,
PROSPECTPLANID,
TYPECODE
from
@TempTbl;
declare @PROSPECTID uniqueidentifier;
declare @PROSPECTPLANID uniqueidentifier;
declare @TYPECODE int;
--Prospect managers
declare @PREVIOUSFUNDRAISERS table (PROSPECTID uniqueidentifier, PREVIOUSFUNDRAISERID uniqueidentifier, STARTDATE datetime, ENDDATE datetime);
insert into @PREVIOUSFUNDRAISERS (PROSPECTID, PREVIOUSFUNDRAISERID, STARTDATE, ENDDATE)
(
select
t.PROSPECTID,
PROSPECT.PROSPECTMANAGERFUNDRAISERID,
PROSPECT.PROSPECTMANAGERSTARTDATE,
case when
dbo.UFN_DATE_GETEARLIESTTIME(coalesce(PROSPECT.PROSPECTMANAGERENDDATE, t.STARTDATE, @CURRENTDATE)) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
then
isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE))
else
PROSPECT.PROSPECTMANAGERENDDATE
end
from
@TempTbl t
inner join dbo.PROSPECT on PROSPECT.ID = t.PROSPECTID
where
t.TYPECODE = 0
);
--Prospect managers
update
dbo.PROSPECT
set
PROSPECTMANAGERFUNDRAISERID = t.ASSIGNTOID,
--PBI#237207 - Arun Saini - If manager is being removed set start date to null
PROSPECTMANAGERSTARTDATE = case when t.ASSIGNTOID is null then null else isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)) end,
PROSPECTMANAGERENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PROSPECT
inner join @TempTbl t on PROSPECT.ID = t.PROSPECTID
where
t.TYPECODE = 0;
--PBI#237207 - Arun Saini - Get last created history record for all the prospects those are going to change
declare @MANAGERHISTORY table (HISTORYID uniqueidentifier, PROSPECTID uniqueidentifier, PROSPECTPLANID uniqueidentifier, FUNDRAISERID uniqueidentifier, ENDDATE datetime)
insert into @MANAGERHISTORY(HISTORYID, PROSPECTID, FUNDRAISERID, ENDDATE)
(
select
MANAGERHISTORY.ID,
MANAGERHISTORY.PROSPECTID,
MANAGERHISTORY.FUNDRAISERID,
MANAGERHISTORY.ENDDATE
from
(select
PROSPECTMANAGERHISTORY.PROSPECTID,
PROSPECTMANAGERHISTORY.ID,
PROSPECTMANAGERHISTORY.FUNDRAISERID,
PROSPECTMANAGERHISTORY.DATETO as ENDDATE,
rank() over (
partition by PROSPECTMANAGERHISTORY.PROSPECTID
order by PROSPECTMANAGERHISTORY.DATETO desc, PROSPECTMANAGERHISTORY.DATEFROM desc, PROSPECTMANAGERHISTORY.DATEADDED desc, PROSPECTMANAGERHISTORY.ID desc
) AS RANKBYLATEST
from PROSPECTMANAGERHISTORY
inner join @TempTbl t on PROSPECTMANAGERHISTORY.PROSPECTID = t.PROSPECTID
and t.TYPECODE = 0
) as MANAGERHISTORY
where MANAGERHISTORY.RANKBYLATEST = 1
)
/*Create manager history record*/
insert into dbo.PROSPECTMANAGERHISTORY
(PROSPECTID, FUNDRAISERID, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
PREVIOUSFUNDRAISERS.PROSPECTID,
PREVIOUSFUNDRAISERS.PREVIOUSFUNDRAISERID,
PREVIOUSFUNDRAISERS.STARTDATE,
PREVIOUSFUNDRAISERS.ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PREVIOUSFUNDRAISERS as PREVIOUSFUNDRAISERS
--PBI#237207 - Arun Saini - Use MANAGERHISTORY temp table to get history record corresponding to prospects
left outer join
@MANAGERHISTORY MANAGERHISTORY
on MANAGERHISTORY.PROSPECTID = PREVIOUSFUNDRAISERS.PROSPECTID
where
--PBI#237207 - Arun Saini - Its a prospect manager and no history was found for that or fundraiser is different in last history record.
-- or history is of old date we will create a new history record
PREVIOUSFUNDRAISERID is not null
and
(
MANAGERHISTORY.FUNDRAISERID is null
or
MANAGERHISTORY.FUNDRAISERID <> PREVIOUSFUNDRAISERS.PREVIOUSFUNDRAISERID
or
MANAGERHISTORY.ENDDATE < PREVIOUSFUNDRAISERS.ENDDATE
);
--PBI#237207 - Arun Saini - Update history record end date if history already exists
update
dbo.PROSPECTMANAGERHISTORY
set
DATETO = PREVIOUSFUNDRAISERS.ENDDATE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.PROSPECTMANAGERHISTORY
inner join @MANAGERHISTORY MANAGERHISTORY on PROSPECTMANAGERHISTORY.ID = MANAGERHISTORY.HISTORYID
inner join @PREVIOUSFUNDRAISERS as PREVIOUSFUNDRAISERS on MANAGERHISTORY.PROSPECTID = PREVIOUSFUNDRAISERS.PROSPECTID
and MANAGERHISTORY.FUNDRAISERID = PREVIOUSFUNDRAISERS.PREVIOUSFUNDRAISERID
where MANAGERHISTORY.ENDDATE > PREVIOUSFUNDRAISERS.ENDDATE
--Send alerts for prospect managers
declare @PREVIOUSFUNDRAISERID uniqueidentifier;
open PROSPECTCURSOR;
fetch next from PROSPECTCURSOR into @PROSPECTID, @PROSPECTPLANID, @TYPECODE;
while (@@FETCH_STATUS = 0)
begin
if @TYPECODE = 0
begin
select
@PREVIOUSFUNDRAISERID = p.PREVIOUSFUNDRAISERID
from
@PREVIOUSFUNDRAISERS p
where
p.PROSPECTID = @PROSPECTID;
exec dbo.USP_PROSPECTASSIGNEDALERT_SEND @PREVIOUSFUNDRAISERID, @PROSPECTID;
end
fetch next from PROSPECTCURSOR into @PROSPECTID, @PROSPECTPLANID, @TYPECODE;
end
close PROSPECTCURSOR;
--Team members
/* cache current context information */
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
delete PROSPECTTEAM from
dbo.PROSPECTTEAM
inner join @TempTbl as T on PROSPECTTEAM.ID =T.SECONDARYID
where
T.TYPECODE = 1
and
dbo.UFN_DATE_GETEARLIESTTIME(isnull(PROSPECTTEAM.DATEFROM, '01/01/1753')) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(T.STARTDATE,@CURRENTDATE))
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
update
dbo.PROSPECTTEAM
set
DATETO = case when
dbo.UFN_DATE_GETEARLIESTTIME(coalesce(PROSPECTTEAM.DATETO, t.STARTDATE, @CURRENTDATE)) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
then
dateadd(d, -1, isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
else
DATETO
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PROSPECTTEAM
inner join @TempTbl t on PROSPECTTEAM.ID = t.SECONDARYID
where
t.TYPECODE = 1;
insert into dbo.PROSPECTTEAM
(
PROSPECTID,
MEMBERID,
DATEFROM,
PROSPECTTEAMROLECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
PROSPECTID,
ASSIGNTOID,
isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)),
PROSPECTTEAMROLECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TempTbl t
where
TYPECODE = 1
--PBI#237207 - Arun Saini - Avoid created records for blank fundraisers
and
ASSIGNTOID is not null;
--Primary Plan managers
declare @PREVIOUSPLANMANAGERS table
(
PROSPECTPLANID uniqueidentifier,
PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier,
PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier,
PRIMARYSTARTDATE datetime,
PRIMARYENDDATE datetime,
SECONDARYSTARTDATE datetime,
SECONDARYENDDATE datetime,
TYPECODE int
);
insert into @PREVIOUSPLANMANAGERS
(PROSPECTPLANID, PREVIOUSPRIMARYFUNDRAISERID, PREVIOUSSECONDARYFUNDRAISERID, PRIMARYSTARTDATE, PRIMARYENDDATE, SECONDARYSTARTDATE, SECONDARYENDDATE, TYPECODE)
(
select
t.PROSPECTPLANID,
PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID,
PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID,
PROSPECTPLAN.PRIMARYMANAGERSTARTDATE,
case when
dbo.UFN_DATE_GETEARLIESTTIME(coalesce(PROSPECTPLAN.PRIMARYMANAGERENDDATE, t.STARTDATE, @CURRENTDATE)) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
then
dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
else
PROSPECTPLAN.PRIMARYMANAGERENDDATE
end,
PROSPECTPLAN.SECONDARYMANAGERSTARTDATE,
case when
dbo.UFN_DATE_GETEARLIESTTIME(coalesce(PROSPECTPLAN.SECONDARYMANAGERENDDATE, t.STARTDATE, @CURRENTDATE)) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
then
dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
else
PROSPECTPLAN.SECONDARYMANAGERENDDATE
end,
t.TYPECODE
from
@TempTbl t
inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = t.PROSPECTPLANID
where
(
t.TYPECODE = 2
)
or
(
t.TYPECODE = 3
)
);
update
dbo.PROSPECTPLAN
set
PRIMARYMANAGERFUNDRAISERID = t.ASSIGNTOID,
PRIMARYMANAGERSTARTDATE = case when t.ASSIGNTOID is null then null else isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)) end,
PRIMARYMANAGERENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PROSPECTPLAN
inner join @TempTbl t on PROSPECTPLAN.ID = t.PROSPECTPLANID
where
t.TYPECODE = 2;
--PBI#237207 - Arun Saini - Clear previous records and insert latest history records for primary managers
delete from @MANAGERHISTORY
insert into @MANAGERHISTORY(HISTORYID, PROSPECTPLANID, FUNDRAISERID, ENDDATE)
(
select
MANAGERHISTORY.ID,
MANAGERHISTORY.PROSPECTPLANID,
MANAGERHISTORY.FUNDRAISERID,
MANAGERHISTORY.ENDDATE
from
(select
PROSPECTPLANMANAGERHISTORY.ID,
PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID,
PROSPECTPLANMANAGERHISTORY.FUNDRAISERID,
PROSPECTPLANMANAGERHISTORY.DATETO as ENDDATE,
rank() over
(
partition by PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID
order by PROSPECTPLANMANAGERHISTORY.DATETO, PROSPECTPLANMANAGERHISTORY.DATEFROM desc, PROSPECTPLANMANAGERHISTORY.DATEADDED desc, PROSPECTPLANMANAGERHISTORY.ID desc
) AS RANKBYLATEST
from PROSPECTPLANMANAGERHISTORY
inner join @TempTbl t on PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID = t.PROSPECTPLANID
where t.TYPECODE = 2
and
PROSPECTPLANMANAGERHISTORY.ISPRIMARYMANAGER = 1
) as MANAGERHISTORY
where MANAGERHISTORY.RANKBYLATEST = 1
)
/*Create primary manager history record*/
insert into dbo.PROSPECTPLANMANAGERHISTORY
(PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
PREVIOUSPLANMANAGERS.PROSPECTPLANID,
PREVIOUSPLANMANAGERS.PREVIOUSPRIMARYFUNDRAISERID,
1,
PREVIOUSPLANMANAGERS.PRIMARYSTARTDATE,
PREVIOUSPLANMANAGERS.PRIMARYENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PREVIOUSPLANMANAGERS as PREVIOUSPLANMANAGERS
--PBI#237207 - Arun Saini - use MANAGERHISTORY temp table to get history record corresponding to prospects
left outer join @MANAGERHISTORY MANAGERHISTORY on MANAGERHISTORY.PROSPECTPLANID = PREVIOUSPLANMANAGERS.PROSPECTPLANID
where
PREVIOUSPLANMANAGERS.TYPECODE = 2
and
PREVIOUSPLANMANAGERS.PREVIOUSPRIMARYFUNDRAISERID is not null
--PBI#237207 - Arun Saini - Its a prospect plan primary manager and no history was found for that or fundraiser is different in last history record.
-- or history is of old date we will create a new history record
and (
MANAGERHISTORY.FUNDRAISERID is null
or
MANAGERHISTORY.FUNDRAISERID <> PREVIOUSPLANMANAGERS.PREVIOUSPRIMARYFUNDRAISERID
or
MANAGERHISTORY.ENDDATE < PREVIOUSPLANMANAGERS.PRIMARYENDDATE
);
--PBI#237207 - Arun Saini - Update history record end date if history already exists
update
dbo.PROSPECTPLANMANAGERHISTORY
set
DATETO = PREVIOUSPLANMANAGERS.PRIMARYENDDATE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.PROSPECTPLANMANAGERHISTORY
inner join @MANAGERHISTORY MANAGERHISTORY on PROSPECTPLANMANAGERHISTORY.ID = MANAGERHISTORY.HISTORYID
inner join @PREVIOUSPLANMANAGERS as PREVIOUSPLANMANAGERS on MANAGERHISTORY.PROSPECTPLANID = PREVIOUSPLANMANAGERS.PROSPECTPLANID
and MANAGERHISTORY.FUNDRAISERID = PREVIOUSPLANMANAGERS.PREVIOUSPRIMARYFUNDRAISERID
where
PREVIOUSPLANMANAGERS.TYPECODE = 2
and
MANAGERHISTORY.ENDDATE > PREVIOUSPLANMANAGERS.PRIMARYENDDATE
--Secondary Plan managers
update
dbo.PROSPECTPLAN
set
SECONDARYMANAGERFUNDRAISERID = t.ASSIGNTOID,
SECONDARYMANAGERSTARTDATE = case when t.ASSIGNTOID is null then null else isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)) end,
SECONDARYMANAGERENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PROSPECTPLAN
inner join @TempTbl t on PROSPECTPLAN.ID = t.PROSPECTPLANID
where
t.TYPECODE = 3;
--PBI#237207 - Arun Saini - Clear previous records and insert latest history records for secondary managers
delete from @MANAGERHISTORY
insert into @MANAGERHISTORY(HISTORYID, PROSPECTPLANID, FUNDRAISERID, ENDDATE)
(
select
MANAGERHISTORY.ID,
MANAGERHISTORY.PROSPECTPLANID,
MANAGERHISTORY.FUNDRAISERID,
MANAGERHISTORY.ENDDATE
from
(select
PROSPECTPLANMANAGERHISTORY.ID,
PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID,
PROSPECTPLANMANAGERHISTORY.FUNDRAISERID,
PROSPECTPLANMANAGERHISTORY.DATETO as ENDDATE,
rank() over
(
partition by PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID
order by PROSPECTPLANMANAGERHISTORY.DATETO desc, PROSPECTPLANMANAGERHISTORY.DATEFROM desc, PROSPECTPLANMANAGERHISTORY.DATEADDED desc, PROSPECTPLANMANAGERHISTORY.ID desc
) AS RANKBYLATEST
from PROSPECTPLANMANAGERHISTORY
inner join @TempTbl t
on PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID = t.PROSPECTPLANID
where t.TYPECODE = 3
and
PROSPECTPLANMANAGERHISTORY.ISPRIMARYMANAGER = 0
) as MANAGERHISTORY
where MANAGERHISTORY.RANKBYLATEST = 1
)
/*Create secondary manager history record*/
insert into dbo.PROSPECTPLANMANAGERHISTORY
(PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
PREVIOUSPLANMANAGERS.PROSPECTPLANID,
PREVIOUSPLANMANAGERS.PREVIOUSSECONDARYFUNDRAISERID,
0,
PREVIOUSPLANMANAGERS.SECONDARYSTARTDATE,
PREVIOUSPLANMANAGERS.SECONDARYENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PREVIOUSPLANMANAGERS as PREVIOUSPLANMANAGERS
--PBI#237207 - Arun Saini - Use MANAGERHISTORY temp table to get history record corresponding to prospects
left outer join @MANAGERHISTORY MANAGERHISTORY on MANAGERHISTORY.PROSPECTPLANID = PREVIOUSPLANMANAGERS.PROSPECTPLANID
where
PREVIOUSPLANMANAGERS.TYPECODE = 3
and
PREVIOUSPLANMANAGERS.PREVIOUSSECONDARYFUNDRAISERID is not null
--PBI#237207 - Arun Saini - Its a prospect plan secondary manager and no history was found for that or fundraiser is different in last history record.
-- or history is of old date we will create a new history record
and
(
MANAGERHISTORY.FUNDRAISERID is null
or
MANAGERHISTORY.FUNDRAISERID <> PREVIOUSPLANMANAGERS.PREVIOUSSECONDARYFUNDRAISERID
or
MANAGERHISTORY.ENDDATE < PREVIOUSPLANMANAGERS.SECONDARYENDDATE
);
--PBI#237207 - Arun Saini - Update history record end date if history already exists
update
dbo.PROSPECTPLANMANAGERHISTORY
set
DATETO = PREVIOUSPLANMANAGERS.SECONDARYENDDATE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.PROSPECTPLANMANAGERHISTORY
inner join @MANAGERHISTORY MANAGERHISTORY on PROSPECTPLANMANAGERHISTORY.ID = MANAGERHISTORY.HISTORYID
inner join @PREVIOUSPLANMANAGERS as PREVIOUSPLANMANAGERS on MANAGERHISTORY.PROSPECTPLANID = PREVIOUSPLANMANAGERS.PROSPECTPLANID
and MANAGERHISTORY.FUNDRAISERID = PREVIOUSPLANMANAGERS.PREVIOUSSECONDARYFUNDRAISERID
where PREVIOUSPLANMANAGERS.TYPECODE = 3
and
MANAGERHISTORY.ENDDATE > PREVIOUSPLANMANAGERS.SECONDARYENDDATE
--Send alerts for primary and secondary plan managers
declare @PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier;
declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier;
open PROSPECTCURSOR;
fetch next from PROSPECTCURSOR into @PROSPECTID, @PROSPECTPLANID, @TYPECODE;
while (@@FETCH_STATUS = 0)
begin
if @TYPECODE = 2 or @TYPECODE = 3
begin
select
@PREVIOUSPRIMARYFUNDRAISERID = p.PREVIOUSPRIMARYFUNDRAISERID,
@PREVIOUSSECONDARYFUNDRAISERID = p.PREVIOUSSECONDARYFUNDRAISERID
from
@PREVIOUSPLANMANAGERS p
where
p.PROSPECTPLANID = @PROSPECTPLANID;
exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSPRIMARYFUNDRAISERID, @PREVIOUSSECONDARYFUNDRAISERID, @PROSPECTPLANID;
end
fetch next from PROSPECTCURSOR into @PROSPECTID, @PROSPECTPLANID, @TYPECODE;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close PROSPECTCURSOR;
deallocate PROSPECTCURSOR;
--Secondary Fundraisers
insert into dbo.SECONDARYFUNDRAISER(PROSPECTPLANID, FUNDRAISERID, SOLICITORROLECODEID, DATEFROM, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
SECONDARYFUNDRAISER.PROSPECTPLANID,
t.ASSIGNTOID,
SECONDARYFUNDRAISER.SOLICITORROLECODEID,
isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TempTbl t
inner join dbo.SECONDARYFUNDRAISER on SECONDARYFUNDRAISER.ID = t.SECONDARYID
where
t.TYPECODE = 4
and
ASSIGNTOID is not null;
/* cache current context information */
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
delete
SECONDARYFUNDRAISER
from
dbo.SECONDARYFUNDRAISER
inner join
@TempTbl t
on SECONDARYFUNDRAISER.ID = t.SECONDARYID
where
t.TYPECODE = 4
and
dbo.UFN_DATE_GETEARLIESTTIME(isnull(SECONDARYFUNDRAISER.DATEFROM, '01/01/1753')) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE));
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
update
dbo.SECONDARYFUNDRAISER
set
DATETO = case when
dbo.UFN_DATE_GETEARLIESTTIME(coalesce(dateto, t.STARTDATE, @CURRENTDATE)) >= dbo.UFN_DATE_GETEARLIESTTIME(isnull(t.STARTDATE, @CURRENTDATE))
then
dateadd(d, -1, isnull(t.STARTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
else
DATETO
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.SECONDARYFUNDRAISER
inner join @TempTbl t on SECONDARYFUNDRAISER.ID = t.SECONDARYID
where
t.TYPECODE = 4;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;