USP_PROSPECTASSIGNMENTREQUEST_ACCEPT
Executes the "Accept Prospect Assignment Request" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being updated. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the update. |
Definition
Copy
CREATE procedure dbo.USP_PROSPECTASSIGNMENTREQUEST_ACCEPT
(
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
if exists(select 1 from dbo.PROSPECTPLANREQUEST where ID = @ID) -- Prospect Plan Request
begin
declare @PLANPROSPECTID uniqueidentifier;
declare @PROSPECTPLANTYPECODEID uniqueidentifier;
declare @NARRATIVE nvarchar(1000);
declare @PROSPECTPLAN_NAME nvarchar(100);
declare @PROSPECTPLAN_PARTICIPANTS xml;
declare @SECONDARYFUNDRAISERS xml;
declare @PRIMARYMANAGERFUNDRAISERID uniqueidentifier;
declare @SECONDARYMANAGERFUNDRAISERID uniqueidentifier;
declare @SITES xml;
declare @PRIMARYMANAGERDATEFROM datetime;
declare @SECONDARYMANAGERDATEFROM datetime;
select
@PLANPROSPECTID = PROSPECTID,
@PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODEID,
@NARRATIVE = NARRATIVE,
@PROSPECTPLAN_NAME = NAME,
@PROSPECTPLAN_PARTICIPANTS = PROSPECTPLAN_PARTICIPANTS,
@SECONDARYFUNDRAISERS = SECONDARYFUNDRAISERS,
@PRIMARYMANAGERFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
@SECONDARYMANAGERFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID,
@SITES = SITES,
@PRIMARYMANAGERDATEFROM = PRIMARYMANAGERSTARTDATE,
@SECONDARYMANAGERDATEFROM = SECONDARYMANAGERSTARTDATE
from dbo.PROSPECTPLANREQUEST
where ID = @ID
exec dbo.USP_DATAFORMTEMPLATE_PROSPECTPLAN_ADD
null,
@CHANGEAGENTID,
@PLANPROSPECTID,
@PROSPECTPLANTYPECODEID,
@NARRATIVE,
null,
@PROSPECTPLAN_NAME,
@PROSPECTPLAN_PARTICIPANTS,
@SECONDARYFUNDRAISERS,
@PRIMARYMANAGERFUNDRAISERID,
@SECONDARYMANAGERFUNDRAISERID,
@SITES,
@CURRENTAPPUSERID,
@PRIMARYMANAGERDATEFROM,
@SECONDARYMANAGERDATEFROM,
@CURRENTDATE;
update dbo.PROSPECTPLANREQUEST set
STATUSCODE = 1,
REVIEWDATE = @CURRENTDATE,
REVIEWEDBYID = @CURRENTAPPUSERID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID;
end
else -- Prospect Assignment Request
begin
declare @TYPECODE int;
declare @NEWFUNDRAISERID uniqueidentifier;
declare @PROSPECTID uniqueidentifier;
declare @PROSPECTPLANID uniqueidentifier;
declare @SECONDARYFUNDRAISERID uniqueidentifier;
declare @SOLICITORROLECODEID uniqueidentifier;
declare @STARTDATE datetime;
-- Get TYPE
select
@TYPECODE = TYPECODE,
@NEWFUNDRAISERID = NEWFUNDRAISERID,
@PROSPECTID = PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID,
@PROSPECTPLANID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID,
@SECONDARYFUNDRAISERID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SECONDARYFUNDRAISERID,
@SOLICITORROLECODEID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SOLICITORROLECODEID,
@STARTDATE = coalesce(PROSPECTASSIGNMENTREQUEST.STARTDATE, getdate())
from
dbo.PROSPECTASSIGNMENTREQUEST
left outer join PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUESTPROSPECT.ID = PROSPECTASSIGNMENTREQUEST.ID
left outer join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID = PROSPECTASSIGNMENTREQUEST.ID
where
PROSPECTASSIGNMENTREQUEST.ID = @ID;
--Prospect manager
if @TYPECODE = 0 begin
declare @PREVIOUSFUNDRAISERID uniqueidentifier;
declare @PREVIOUSSTARTDATE datetime;
declare @PREVIOUSENDDATE datetime;
select
@PREVIOUSFUNDRAISERID = PROSPECTMANAGERFUNDRAISERID,
@PREVIOUSSTARTDATE = PROSPECTMANAGERSTARTDATE,
@PREVIOUSENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(isnull(PROSPECTMANAGERENDDATE, dateadd(d, -1, @STARTDATE)))
from
dbo.PROSPECT
where
ID = @PROSPECTID;
if @PREVIOUSFUNDRAISERID is not null begin
--Clear out record to prevent constraint violations.
update dbo.PROSPECT set
PROSPECTMANAGERFUNDRAISERID = null,
PROSPECTMANAGERSTARTDATE = null,
PROSPECTMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTID
--Only add to the history if the fundraiser actually started in this capacity. If the start date is in the future then they never actually worked in this capacity and same record doesn't already exists.
if (coalesce(@PREVIOUSSTARTDATE,'01/01/1753') <= @CURRENTDATE
and not exists (select 1 from dbo.PROSPECTMANAGERHISTORY where PROSPECTID = @PROSPECTID and FUNDRAISERID = @PREVIOUSFUNDRAISERID and DATEFROM = @PREVIOUSSTARTDATE and DATETO = @PREVIOUSENDDATE))
begin
/*Create manager history record*/
insert into dbo.PROSPECTMANAGERHISTORY
(PROSPECTID, FUNDRAISERID, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@PROSPECTID, @PREVIOUSFUNDRAISERID, @PREVIOUSSTARTDATE, @PREVIOUSENDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
if @NEWFUNDRAISERID is not null
update dbo.PROSPECT set
PROSPECTMANAGERFUNDRAISERID = @NEWFUNDRAISERID,
PROSPECTMANAGERSTARTDATE = case when @NEWFUNDRAISERID is not null then @STARTDATE else null end,
PROSPECTMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTID;
exec dbo.USP_PROSPECTASSIGNEDALERT_SEND @PREVIOUSFUNDRAISERID, @PROSPECTID;
end
declare @PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier;
declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier;
--Primary manager
if @TYPECODE = 2 begin
select
@PREVIOUSPRIMARYFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
@PREVIOUSSTARTDATE = PRIMARYMANAGERSTARTDATE,
@PREVIOUSENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(isnull(PRIMARYMANAGERENDDATE, dateadd(d, -1, @STARTDATE))),
@PREVIOUSSECONDARYFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID
from
dbo.PROSPECTPLAN
where
ID = @PROSPECTPLANID;
if @PREVIOUSPRIMARYFUNDRAISERID is not null begin
--Clear out record to prevent constraint violations.
update dbo.PROSPECTPLAN set
PRIMARYMANAGERFUNDRAISERID = null,
PRIMARYMANAGERSTARTDATE = null,
PRIMARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
--Only add to the history if the fundraiser actually started in this capacity. If the start date is in the future then they never actually worked in this capacity and same record doesn't already exists.
if (coalesce(@PREVIOUSSTARTDATE,'01/01/1753') <= @CURRENTDATE
and not exists (select 1 from dbo.PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID = @PROSPECTPLANID and FUNDRAISERID = @PREVIOUSPRIMARYFUNDRAISERID and ISPRIMARYMANAGER = 1 and DATEFROM = @PREVIOUSSTARTDATE and DATETO = @PREVIOUSENDDATE))
begin
/*Create primary manager history record*/
insert into dbo.PROSPECTPLANMANAGERHISTORY
(PROSPECTPLANID, FUNDRAISERID, ISPRIMARYMANAGER, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@PROSPECTPLANID, @PREVIOUSPRIMARYFUNDRAISERID, 1, @PREVIOUSSTARTDATE, @PREVIOUSENDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
if @NEWFUNDRAISERID is not null
update dbo.PROSPECTPLAN set
PRIMARYMANAGERFUNDRAISERID = @NEWFUNDRAISERID,
PRIMARYMANAGERSTARTDATE = case when @NEWFUNDRAISERID is not null then @STARTDATE else null end,
PRIMARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSPRIMARYFUNDRAISERID, @PREVIOUSSECONDARYFUNDRAISERID, @PROSPECTPLANID;
end
--Secondary manager
if @TYPECODE = 3 begin
select
@PREVIOUSPRIMARYFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
@PREVIOUSSECONDARYFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID,
@PREVIOUSSTARTDATE = SECONDARYMANAGERSTARTDATE,
@PREVIOUSENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(isnull(SECONDARYMANAGERENDDATE, dateadd(d, -1, @STARTDATE)))
from
dbo.PROSPECTPLAN
where
ID = @PROSPECTPLANID;
if @PREVIOUSSECONDARYFUNDRAISERID is not null begin
--Clear out record to prevent constraint violations.
update dbo.PROSPECTPLAN set
SECONDARYMANAGERFUNDRAISERID = null,
SECONDARYMANAGERSTARTDATE = null,
SECONDARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
--Only add to the history if the fundraiser actually started in this capacity. If the start date is in the future then they never actually worked in this capacity and same record doesn't already exists.
if (coalesce(@PREVIOUSSTARTDATE,'01/01/1753') <= @CURRENTDATE
and not exists (select 1 from dbo.PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID = @PROSPECTPLANID and FUNDRAISERID = @PREVIOUSSECONDARYFUNDRAISERID and ISPRIMARYMANAGER = 0 and DATEFROM = @PREVIOUSSTARTDATE and DATETO = @PREVIOUSENDDATE))
begin
/*Create primary manager history record*/
insert into dbo.PROSPECTPLANMANAGERHISTORY
(PROSPECTPLANID, FUNDRAISERID, ISPRIMARYMANAGER, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@PROSPECTPLANID, @PREVIOUSSECONDARYFUNDRAISERID, 0, @PREVIOUSSTARTDATE, @PREVIOUSENDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
if @NEWFUNDRAISERID is not null
update dbo.PROSPECTPLAN set
SECONDARYMANAGERFUNDRAISERID = @NEWFUNDRAISERID,
SECONDARYMANAGERSTARTDATE = case when @NEWFUNDRAISERID is not null then @STARTDATE else null end,
SECONDARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSPRIMARYFUNDRAISERID, @PREVIOUSSECONDARYFUNDRAISERID, @PROSPECTPLANID;
end
--Secondary fundraiser
if @TYPECODE = 4 begin
declare @SEQUENCE int;
select
@SEQUENCE = isnull(max(SEQUENCE)+1, 0)
from
dbo.SECONDARYFUNDRAISER
where
PROSPECTPLANID = @PROSPECTPLANID;
--New secondary fundraiser
if @SECONDARYFUNDRAISERID is null and @NEWFUNDRAISERID is not null begin
insert into dbo.SECONDARYFUNDRAISER
(PROSPECTPLANID, FUNDRAISERID, SOLICITORROLECODEID, SEQUENCE, DATEFROM, ADDEDBYID, CHANGEDBYID)
values
(@PROSPECTPLANID, @NEWFUNDRAISERID, @SOLICITORROLECODEID, @SEQUENCE, @STARTDATE, @CHANGEAGENTID, @CHANGEAGENTID);
end
--Existing
if @SECONDARYFUNDRAISERID is not null begin
--remove fundraiser
if @NEWFUNDRAISERID is null
begin
if (select dbo.UFN_DATE_GETEARLIESTTIME(coalesce(DATEFROM,'01/01/1753')) from dbo.SECONDARYFUNDRAISER where ID = @SECONDARYFUNDRAISERID) >= @CURRENTDATE
begin
exec USP_PROSPECTASSIGNMENTREQUEST_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
exec USP_SECONDARYFUNDRAISER_DELETEBYID_WITHCHANGEAGENTID @SECONDARYFUNDRAISERID, @CHANGEAGENTID;
end
else
begin
update
dbo.SECONDARYFUNDRAISER
set
DATETO = case when
dbo.UFN_DATE_GETEARLIESTTIME(isnull(DATETO, @CURRENTDATE)) >= @CURRENTDATE
then
dateadd(d, -1, @CURRENTDATE)
else
SECONDARYFUNDRAISER.DATETO
end
from
dbo.SECONDARYFUNDRAISER
where
SECONDARYFUNDRAISER.ID = @SECONDARYFUNDRAISERID;
end
end
else
begin
--Add the new fundraiser
insert into dbo.SECONDARYFUNDRAISER
(PROSPECTPLANID, FUNDRAISERID, SOLICITORROLECODEID, SEQUENCE, DATEFROM, ADDEDBYID, CHANGEDBYID)
values
(@PROSPECTPLANID, @NEWFUNDRAISERID, @SOLICITORROLECODEID, @SEQUENCE, @STARTDATE, @CHANGEAGENTID, @CHANGEAGENTID);
--If the fundraiser has not actually started we should delete their record so they don't appear in the history
if (select dbo.UFN_DATE_GETEARLIESTTIME(coalesce(DATEFROM,'01/01/1753')) from dbo.SECONDARYFUNDRAISER where ID = @SECONDARYFUNDRAISERID) >= @CURRENTDATE
begin
exec USP_PROSPECTASSIGNMENTREQUEST_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
exec USP_SECONDARYFUNDRAISER_DELETEBYID_WITHCHANGEAGENTID @SECONDARYFUNDRAISERID, @CHANGEAGENTID;
end
else
begin
update
dbo.SECONDARYFUNDRAISER
set
DATETO = case when
dbo.UFN_DATE_GETEARLIESTTIME(isnull(DATETO, @CURRENTDATE)) >= @CURRENTDATE
then
dateadd(d, -1, @CURRENTDATE )
else
DATETO
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.SECONDARYFUNDRAISER
where
ID = @SECONDARYFUNDRAISERID;
end
end
end
end
update dbo.PROSPECTASSIGNMENTREQUEST set
STATUSCODE = 1,
REVIEWDATE = @CURRENTDATE,
REVIEWEDBYID = @CURRENTAPPUSERID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID;
end
return 0;
end