USP_DATAFORMTEMPLATE_ASSIGNPROSPECT_EDIT_2
The save procedure used by the edit dataform template "Fundraiser Assignment Edit Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @PROSPECTID | uniqueidentifier | IN | Prospect |
| @PROSPECTPLANID | uniqueidentifier | IN | Plan |
| @FUNDRAISERROLECODE | tinyint | IN | Fundraiser's role |
| @FUNDRAISERSTARTDATE | datetime | IN | Start date |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ASSIGNPROSPECT_EDIT_2 (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@PROSPECTID uniqueidentifier,
@PROSPECTPLANID uniqueidentifier,
@FUNDRAISERROLECODE tinyint,
@FUNDRAISERSTARTDATE datetime,
@CURRENTAPPUSERID uniqueidentifier
) as begin
set nocount on;
if not exists(select SITEID from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(@PROSPECTPLANID) SITES where dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, SITES.SITEID, '12A43662-7F1C-4391-8ABC-0D0461ECB88B', 1) = 1)
raiserror('ASSIGNPROSPECT_ERR_ACCESSDENIEDFORPROSPECTPLAN', 13, 1);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
declare @PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier;
declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier;
declare @PREVIOUSMANAGERSTARTDATE date;
declare @PREVIOUSMANAGERENDDATE date;
declare @MAKEHISTORICAL bit = 1;
select
@PREVIOUSPRIMARYFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
@PREVIOUSSECONDARYFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID,
@PREVIOUSMANAGERSTARTDATE = case when @FUNDRAISERROLECODE = 1 then PRIMARYMANAGERSTARTDATE when @FUNDRAISERROLECODE = 2 then SECONDARYMANAGERSTARTDATE else null end,
@PREVIOUSMANAGERENDDATE = case when @FUNDRAISERROLECODE = 1 then PRIMARYMANAGERENDDATE when @FUNDRAISERROLECODE = 2 then SECONDARYMANAGERENDDATE else null end
from
dbo.PROSPECTPLAN
where
ID = @PROSPECTPLANID;
if @FUNDRAISERROLECODE = 1 begin
if exists(select 1 from dbo.PROSPECTPLAN where ID = @PROSPECTPLANID and PRIMARYMANAGERFUNDRAISERID = @ID)
raiserror('ASSIGNPROSPECT_ERR_ALREADYASSIGNED', 13, 1);
if @PREVIOUSPRIMARYFUNDRAISERID is not null begin
if @FUNDRAISERSTARTDATE is null
set @FUNDRAISERSTARTDATE = getdate();
if @FUNDRAISERSTARTDATE <= @PREVIOUSMANAGERSTARTDATE
if dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) < dbo.UFN_DATE_GETEARLIESTTIME(getdate())
raiserror('ASSIGNPROSPECT_ERR_INVALIDSTARTDATE', 13, 1);
else
set @MAKEHISTORICAL = 0;
if @MAKEHISTORICAL = 1
begin
if @PREVIOUSMANAGERENDDATE is null or @PREVIOUSMANAGERENDDATE >= @FUNDRAISERSTARTDATE
set @PREVIOUSMANAGERENDDATE = dateadd(d, -1, @FUNDRAISERSTARTDATE);
update
dbo.PROSPECTPLAN
set
PRIMARYMANAGERFUNDRAISERID = null,
PRIMARYMANAGERSTARTDATE = null,
PRIMARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID
insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID, ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values(newid(), @PROSPECTPLANID, @PREVIOUSPRIMARYFUNDRAISERID, 1, @PREVIOUSMANAGERSTARTDATE, @PREVIOUSMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
end
update dbo.PROSPECTPLAN set
PRIMARYMANAGERFUNDRAISERID = @ID,
PRIMARYMANAGERSTARTDATE = @FUNDRAISERSTARTDATE,
PRIMARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
if (@@ROWCOUNT=0) raiserror('ASSIGNPROSPECT_ERR_INVALIDPROSPECTPLAN', 13, 1);
end
else if @FUNDRAISERROLECODE = 2 begin
if exists(select 1 from dbo.PROSPECTPLAN where ID=@PROSPECTPLANID and SECONDARYMANAGERFUNDRAISERID=@ID)
raiserror('ASSIGNPROSPECT_ERR_ALREADYASSIGNED', 13, 1);
if @PREVIOUSSECONDARYFUNDRAISERID is not null begin
if @FUNDRAISERSTARTDATE is null
set @FUNDRAISERSTARTDATE = getdate();
if @FUNDRAISERSTARTDATE <= @PREVIOUSMANAGERSTARTDATE
if dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) < dbo.UFN_DATE_GETEARLIESTTIME(getdate())
raiserror('ASSIGNPROSPECT_ERR_INVALIDSTARTDATE', 13, 1);
else
set @MAKEHISTORICAL = 0;
if @MAKEHISTORICAL = 1
begin
if @PREVIOUSMANAGERENDDATE is null or @PREVIOUSMANAGERENDDATE >= @FUNDRAISERSTARTDATE
set @PREVIOUSMANAGERENDDATE = dateadd(d, -1, @FUNDRAISERSTARTDATE);
update
dbo.PROSPECTPLAN
set
SECONDARYMANAGERFUNDRAISERID = null,
SECONDARYMANAGERSTARTDATE = null,
SECONDARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID
insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID, ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values(newid(), @PROSPECTPLANID, @PREVIOUSSECONDARYFUNDRAISERID, 0, @PREVIOUSMANAGERSTARTDATE, @PREVIOUSMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
end
update dbo.PROSPECTPLAN set
SECONDARYMANAGERFUNDRAISERID = @ID,
SECONDARYMANAGERSTARTDATE = @FUNDRAISERSTARTDATE,
SECONDARYMANAGERENDDATE = null,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROSPECTPLANID;
if (@@ROWCOUNT=0) raiserror('ASSIGNPROSPECT_ERR_INVALIDPROSPECTPLAN', 13, 1);
end
else if @FUNDRAISERROLECODE = 3 begin
if exists(select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=@PROSPECTPLANID and FUNDRAISERID=@ID and (DATETO is null or DATETO > @FUNDRAISERSTARTDATE))
raiserror('ASSIGNPROSPECT_ERR_ALREADYASSIGNED', 13, 1);
declare @SEQUENCE int;
select @SEQUENCE=coalesce(max(SEQUENCE)+1,0) from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=@PROSPECTPLANID;
insert into dbo.SECONDARYFUNDRAISER
(PROSPECTPLANID, FUNDRAISERID, DATEFROM, SEQUENCE, ADDEDBYID, CHANGEDBYID)
values
(@PROSPECTPLANID, @ID, @FUNDRAISERSTARTDATE, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID);
end;
exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSPRIMARYFUNDRAISERID, @PREVIOUSSECONDARYFUNDRAISERID, @PROSPECTPLANID;
update dbo.INTERACTION set
FUNDRAISERID = @ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PLANOUTLINESTEP SI
where
SI.ID = PLANOUTLINESTEPID
and SI.FUNDRAISERROLECODE = @FUNDRAISERROLECODE
and FUNDRAISERID is null
and PROSPECTPLANID = @PROSPECTPLANID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch;
return 0;
end