USP_PROSPECT_ADD
A common stored proc for saving the prospect add forms
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PROSPECTID | uniqueidentifier | IN | |
| @PROSPECTMANAGERFUNDRAISERID | uniqueidentifier | IN | |
| @PROSPECTPLAN_PROSPECTPLANTYPECODEID | uniqueidentifier | IN | |
| @PROSPECTPLAN_PLANOUTLINEID | uniqueidentifier | IN | |
| @PROSPECTPLAN_PRIMARYMANAGERFUNDRAISERID | uniqueidentifier | IN | |
| @PROSPECTPLAN_SECONDARYMANAGERFUNDRAISERID | uniqueidentifier | IN | |
| @SECONDARYFUNDRAISERS | xml | IN | |
| @PROSPECTPLAN_NAME | nvarchar(100) | IN | |
| @PROSPECTPLAN_PARTICIPANTS | xml | IN | |
| @SITES | xml | IN | |
| @PROSPECTTEAM | xml | IN | |
| @PROSPECTPLAN_PRIMARYMANAGERDATEFROM | datetime | IN | |
| @PROSPECTPLAN_SECONDARYMANAGERDATEFROM | datetime | IN | |
| @PROSPECTMANAGERSTARTDATE | datetime | IN | |
| @PROSPECTPLAN_NARRATIVE | nvarchar(1000) | IN | |
| @PROSPECTPLAN_BASEDATE | date | IN | |
| @OVERRIDEEXISTINGPROSPECTMANAGER | bit | IN | |
| @PROSPECTPLANID | uniqueidentifier | IN | |
| @PROSPECTPLAN_BASECURRENCYID | uniqueidentifier | IN | |
| @PROSPECTPLAN_STARTDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_PROSPECT_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@PROSPECTID uniqueidentifier,
@PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null,
@PROSPECTPLAN_PROSPECTPLANTYPECODEID uniqueidentifier = null,
@PROSPECTPLAN_PLANOUTLINEID uniqueidentifier = null,
@PROSPECTPLAN_PRIMARYMANAGERFUNDRAISERID uniqueidentifier = null,
@PROSPECTPLAN_SECONDARYMANAGERFUNDRAISERID uniqueidentifier = null,
@SECONDARYFUNDRAISERS xml = null,
@PROSPECTPLAN_NAME nvarchar(100) = '',
@PROSPECTPLAN_PARTICIPANTS xml = null,
@SITES xml = null,
@PROSPECTTEAM xml = null,
@PROSPECTPLAN_PRIMARYMANAGERDATEFROM datetime = null,
@PROSPECTPLAN_SECONDARYMANAGERDATEFROM datetime = null,
@PROSPECTMANAGERSTARTDATE datetime = null,
@PROSPECTPLAN_NARRATIVE nvarchar(1000) = '',
@PROSPECTPLAN_BASEDATE date = null,
@OVERRIDEEXISTINGPROSPECTMANAGER bit = 1,
@PROSPECTPLANID uniqueidentifier = null,
@PROSPECTPLAN_BASECURRENCYID uniqueidentifier = null,
@PROSPECTPLAN_STARTDATE datetime = null
)
as begin
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
set @ID = @PROSPECTID;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @PROSPECTPLAN_BASEDATE is null
set @PROSPECTPLAN_BASEDATE = @CURRENTDATEEARLIESTTIME;
declare @PROSPECT_STARTDATE date;
if @PROSPECTPLAN_BASEDATE > @CURRENTDATEEARLIESTTIME
set @PROSPECT_STARTDATE = @CURRENTDATEEARLIESTTIME;
else
set @PROSPECT_STARTDATE = @PROSPECTPLAN_BASEDATE;
begin try
if (exists(select 1 from dbo.PROSPECT where ID=@ID)) begin
update dbo.PROSPECT set
PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
PROSPECTMANAGERSTARTDATE = case when @PROSPECTMANAGERFUNDRAISERID is not null then @PROSPECTMANAGERSTARTDATE else null end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
and PROSPECTMANAGERFUNDRAISERID != @PROSPECTMANAGERFUNDRAISERID
and @OVERRIDEEXISTINGPROSPECTMANAGER = 1;
end else begin
insert into dbo.PROSPECT (
ID,
PROSPECTMANAGERFUNDRAISERID,
PROSPECTMANAGERSTARTDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
) values (
@ID,
@PROSPECTMANAGERFUNDRAISERID,
case when @PROSPECTMANAGERFUNDRAISERID is not null then @PROSPECTMANAGERSTARTDATE else null end,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
end
exec dbo.USP_PROSPECTASSIGNEDALERT_SEND null, @ID;
declare @contextCache varbinary(128);
/* cache current context information */
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete
dbo.PROSPECTDATERANGE
where
CONSTITUENTID = @ID and
DATEFROM > @PROSPECT_STARTDATE;
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
declare @PROSPECTDATERANGEID uniqueidentifier;
select @PROSPECTDATERANGEID = ID
from dbo.PROSPECTDATERANGE
where CONSTITUENTID = @ID
and (DATEFROM <= @PROSPECT_STARTDATE or DATEFROM is null)
and (DATETO is null or DATETO >= @PROSPECT_STARTDATE)
if @PROSPECTDATERANGEID is not null
update dbo.PROSPECTDATERANGE
set DATETO = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PROSPECTDATERANGEID
and DATETO is not null;
else
insert into dbo.PROSPECTDATERANGE
(
CONSTITUENTID,
DATEFROM,
DATETO,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@PROSPECT_STARTDATE,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*Start Plan */
if @PROSPECTPLAN_NAME is not null and rtrim(ltrim(@PROSPECTPLAN_NAME)) <> ''
begin
if @PROSPECTPLANID is null
set @PROSPECTPLANID = newid();
insert into dbo.PROSPECTPLAN (
ID,
PROSPECTID,
PROSPECTPLANTYPECODEID,
PRIMARYMANAGERFUNDRAISERID,
PRIMARYMANAGERSTARTDATE,
SECONDARYMANAGERFUNDRAISERID,
SECONDARYMANAGERSTARTDATE,
NAME,
NARRATIVE,
BASECURRENCYID,
STARTDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
) values (
@PROSPECTPLANID,
@ID,
@PROSPECTPLAN_PROSPECTPLANTYPECODEID,
@PROSPECTPLAN_PRIMARYMANAGERFUNDRAISERID,
@PROSPECTPLAN_PRIMARYMANAGERDATEFROM,
@PROSPECTPLAN_SECONDARYMANAGERFUNDRAISERID,
@PROSPECTPLAN_SECONDARYMANAGERDATEFROM,
@PROSPECTPLAN_NAME,
@PROSPECTPLAN_NARRATIVE,
@PROSPECTPLAN_BASECURRENCYID,
@PROSPECTPLAN_STARTDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
exec dbo.USP_PROSPECTPLAN_GETSITES_ADDFROMXML @PROSPECTPLANID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND null, null, @PROSPECTPLANID;
exec dbo.USP_PROSPECTPLAN_SECONDARYFUNDRAISERS_ADDFROMXML @PROSPECTPLANID, @SECONDARYFUNDRAISERS, @CHANGEAGENTID, @CURRENTDATE;
declare @STEPS xml;
set @STEPS = dbo.UFN_PLANOUTLINE_STEPSFORPROSPECTEDIT_TOITEMLISTXML(@PROSPECTPLAN_PLANOUTLINEID, @PROSPECTPLANID, @PROSPECTPLAN_BASEDATE);
exec dbo.USP_PROSPECTPLAN_STEPSWITHOUTSTATUS_ADDFROMXML @PROSPECTPLANID, @STEPS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PROSPECTPLAN_PARTICIPANTS_ADDFROMXML @PROSPECTPLANID, @PROSPECTPLAN_PARTICIPANTS, @CHANGEAGENTID, @CURRENTDATE;
end
/*End Plan */
if @PROSPECTTEAM is not null
exec dbo.USP_PROSPECTTEAM_UPDATEFROMXML @PROSPECTID, @PROSPECTTEAM, @CHANGEAGENTID, @CURRENTDATE;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0
end