USP_APPEAL_TEAMFUNDRAISINGCOPY
Copies team fundraising teams and team fundraisers from one appeal to another.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEAPPEALID | uniqueidentifier | IN | |
| @SOURCETEAMID | uniqueidentifier | IN | |
| @DESTINATIONAPPEALID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @COPYTEAMFUNDRAISERS | bit | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @FEATUREID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_APPEAL_TEAMFUNDRAISINGCOPY
(
@SOURCEAPPEALID uniqueidentifier,
@SOURCETEAMID uniqueidentifier,
@DESTINATIONAPPEALID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@COPYTEAMFUNDRAISERS bit = 0,
@CURRENTAPPUSERID uniqueidentifier = null,
@FEATUREID uniqueidentifier = null
)
with execute as caller
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CURRENTAPPUSERID is null
raiserror('ERR_CURRENTAPPUSERID_REQUIRED', 13, 1);
if not @SOURCEAPPEALID is null
begin
if not (select dbo.UFN_APPEAL_USERHASSITEACCESS(@CURRENTAPPUSERID, @SOURCEAPPEALID)) = 1
raiserror('ERR_SECURITY_APPEALID', 13, 1);
end
if @FEATUREID is null
raiserror('ERR_FEATUREID_REQUIRED', 13, 1);
declare @DESTINATIONBASECURRENCYID uniqueidentifier;
select @DESTINATIONBASECURRENCYID = APPEAL.BASECURRENCYID
from dbo.APPEAL
where APPEAL.ID = @DESTINATIONAPPEALID;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@DESTINATIONBASECURRENCYID, @ORGANIZATIONCURRENCYID, getdate(), null, null);
declare @ISADMIN bit = 0;
declare @USERISNONRAC bit = 0;
declare @NOCONSTITUENTSECURITY bit = 0;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
if @ISADMIN <> 1
begin
set @USERISNONRAC = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
if @USERISNONRAC = 0
set @USERISNONRAC = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_NONRACROLE(@CURRENTAPPUSERID, @FEATUREID);
end
if @ISADMIN = 1 or @USERISNONRAC = 1
set @NOCONSTITUENTSECURITY = 1;
--TODO: If duplicate teams exist, use source goal, merge captains, merge members
declare @TEAMMAPPINGTABLE table
(
SOURCETEAMID uniqueidentifier,
SOURCETEAMNAME nvarchar(100),
SOURCETEAMFUNDRAISINGTEAMGROUPID uniqueidentifier,
DESTINATIONTEAMID uniqueidentifier
);
--Team names are only unique given their parent team, use a recursive CTE to map
-- destination teams by level otherwise all teams with the same name get merged during a copy
with TEAMHIERARCHY
as
(
select
SOURCETEAM.ID [SOURCETEAMID],
SOURCETEAM.NAME [SOURCETEAMNAME],
case
when SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID is null then newid()
else SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID
end [SOURCETEAMFUNDRAISINGTEAMGROUPID],
DESTINATIONTEAM.ID [DESTINATIONTEAMID],
0 [LEVEL]
from
dbo.TEAMFUNDRAISINGTEAM SOURCETEAM
inner join dbo.TEAMFUNDRAISINGTEAM DESTINATIONTEAM on
DESTINATIONTEAM.PARENTTEAMID is null
and DESTINATIONTEAM.NAME = SOURCETEAM.NAME
and @DESTINATIONAPPEALID = DESTINATIONTEAM.APPEALID
where
(
-- Either get all top-level teams from an appeal, ...
SOURCETEAM.PARENTTEAMID is null
and
SOURCETEAM.APPEALID = @SOURCEAPPEALID
)
or
(
-- ... or get the one selected team if no appeal is specified
@SOURCEAPPEALID is null
and
SOURCETEAM.ID = @SOURCETEAMID
)
union all
select
SOURCETEAM.ID,
SOURCETEAM.NAME,
case
when SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID is null then newid()
else SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID
end,
DESTINATIONTEAM.ID,
[LEVEL] + 1
from
TEAMHIERARCHY
inner join dbo.TEAMFUNDRAISINGTEAM SOURCETEAM on TEAMHIERARCHY.SOURCETEAMID = SOURCETEAM.PARENTTEAMID
inner join dbo.TEAMFUNDRAISINGTEAM DESTINATIONTEAM on
TEAMHIERARCHY.DESTINATIONTEAMID = DESTINATIONTEAM.PARENTTEAMID
and DESTINATIONTEAM.NAME = SOURCETEAM.NAME
)
insert into @TEAMMAPPINGTABLE
(
SOURCETEAMID,
SOURCETEAMNAME,
SOURCETEAMFUNDRAISINGTEAMGROUPID,
DESTINATIONTEAMID
)
select
TEAMHIERARCHY.SOURCETEAMID,
TEAMHIERARCHY.SOURCETEAMNAME,
TEAMHIERARCHY.SOURCETEAMFUNDRAISINGTEAMGROUPID,
TEAMHIERARCHY.DESTINATIONTEAMID
from
TEAMHIERARCHY
order by
TEAMHIERARCHY.[LEVEL]
option (maxrecursion 9);
-- Add all of the other teams from the selected appeal or when no
-- appeal was specified just add the subteams of the selected team
if @SOURCEAPPEALID is not null
insert into @TEAMMAPPINGTABLE
(
SOURCETEAMID,
SOURCETEAMNAME,
SOURCETEAMFUNDRAISINGTEAMGROUPID,
DESTINATIONTEAMID
)
select
SOURCETEAM.ID,
SOURCETEAM.NAME,
case
when SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID is null then newid()
else SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID
end,
newid()
from
dbo.TEAMFUNDRAISINGTEAM SOURCETEAM
where
SOURCETEAM.APPEALID = @SOURCEAPPEALID
and SOURCETEAM.ID not in (select SOURCETEAMID from @TEAMMAPPINGTABLE);
else
with TEAMHIERARCHY
as
(
select
SOURCETEAM.ID [SOURCETEAMID],
SOURCETEAM.NAME [SOURCETEAMNAME],
case
when SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID is null then newid()
else SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID
end [SOURCETEAMFUNDRAISINGTEAMGROUPID],
0 [LEVEL]
from
dbo.TEAMFUNDRAISINGTEAM SOURCETEAM
where
SOURCETEAM.ID = @SOURCETEAMID
union all
select
SOURCETEAM.ID,
SOURCETEAM.NAME,
case
when SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID is null then newid()
else SOURCETEAM.TEAMFUNDRAISINGTEAMGROUPID
end,
[LEVEL] + 1
from
TEAMHIERARCHY
inner join dbo.TEAMFUNDRAISINGTEAM SOURCETEAM on TEAMHIERARCHY.SOURCETEAMID = SOURCETEAM.PARENTTEAMID
)
insert into @TEAMMAPPINGTABLE
(
SOURCETEAMID,
SOURCETEAMNAME,
SOURCETEAMFUNDRAISINGTEAMGROUPID,
DESTINATIONTEAMID
)
select
TEAMHIERARCHY.SOURCETEAMID,
TEAMHIERARCHY.SOURCETEAMNAME,
TEAMHIERARCHY.SOURCETEAMFUNDRAISINGTEAMGROUPID,
newid()
from
TEAMHIERARCHY
where
TEAMHIERARCHY.SOURCETEAMID not in (select SOURCETEAMID from @TEAMMAPPINGTABLE);
insert into dbo.TEAMFUNDRAISINGTEAMGROUP
(
ID,
NAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
TEAMMAPPING.SOURCETEAMFUNDRAISINGTEAMGROUPID,
TEAMMAPPING.SOURCETEAMNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TEAMMAPPINGTABLE TEAMMAPPING
left join dbo.TEAMFUNDRAISINGTEAMGROUP on TEAMMAPPING.SOURCETEAMFUNDRAISINGTEAMGROUPID = TEAMFUNDRAISINGTEAMGROUP.ID
where
TEAMFUNDRAISINGTEAMGROUP.ID is null;
--Set the team group ID on the source teams if the group is new
update dbo.TEAMFUNDRAISINGTEAM set
TEAMFUNDRAISINGTEAMGROUPID = TEAMMAPPING.SOURCETEAMFUNDRAISINGTEAMGROUPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@TEAMMAPPINGTABLE TEAMMAPPING
inner join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.ID = TEAMMAPPING.SOURCETEAMID
where
TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID is null;
--Set the team group ID on the existing destination teams if they do not already have a group
update dbo.TEAMFUNDRAISINGTEAM set
TEAMFUNDRAISINGTEAMGROUPID = TEAMMAPPING.SOURCETEAMFUNDRAISINGTEAMGROUPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@TEAMMAPPINGTABLE TEAMMAPPING
inner join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.ID = TEAMMAPPING.DESTINATIONTEAMID
where
TEAMFUNDRAISINGTEAM.TEAMFUNDRAISINGTEAMGROUPID is null;
--Using recursive CTE so that teams are inserted level-by-level
with TEAMHIERARCHY
(
ID,
APPEALID,
NAME,
GOAL,
PARENTTEAMID,
LEVEL
)
as
(
select
TEAMFUNDRAISINGTEAM.ID,
TEAMFUNDRAISINGTEAM.APPEALID,
TEAMFUNDRAISINGTEAM.NAME,
TEAMFUNDRAISINGTEAM.GOAL,
TEAMFUNDRAISINGTEAM.PARENTTEAMID,
0 [LEVEL]
from
dbo.TEAMFUNDRAISINGTEAM
where
(
(
-- Either get all top-level teams from an appeal, ...
TEAMFUNDRAISINGTEAM.PARENTTEAMID is null
and
TEAMFUNDRAISINGTEAM.APPEALID = @SOURCEAPPEALID
)
or
(
-- ... or get the one selected team if no appeal is specified
@SOURCEAPPEALID is null
and
TEAMFUNDRAISINGTEAM.ID = @SOURCETEAMID
)
)
and TEAMFUNDRAISINGTEAM.ID in (select SOURCETEAMID from @TEAMMAPPINGTABLE)
union all
select
TEAMFUNDRAISINGTEAM.ID,
TEAMFUNDRAISINGTEAM.APPEALID,
TEAMFUNDRAISINGTEAM.NAME,
TEAMFUNDRAISINGTEAM.GOAL,
TEAMFUNDRAISINGTEAM.PARENTTEAMID,
TEAMHIERARCHY.LEVEL + 1 [LEVEL]
from
dbo.TEAMFUNDRAISINGTEAM
inner join TEAMHIERARCHY on TEAMFUNDRAISINGTEAM.PARENTTEAMID = TEAMHIERARCHY.ID
)
insert into dbo.TEAMFUNDRAISINGTEAM
(
ID,
APPEALID,
NAME,
GOAL,
PARENTTEAMID,
TEAMFUNDRAISINGTEAMGROUPID,
ORGANIZATIONGOAL,
ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
TEAMMAPPING.DESTINATIONTEAMID,
@DESTINATIONAPPEALID,
TEAMHIERARCHY.NAME,
TEAMHIERARCHY.GOAL,
PARENTTEAM.DESTINATIONTEAMID,
TEAMMAPPING.SOURCETEAMFUNDRAISINGTEAMGROUPID,
dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(TEAMHIERARCHY.GOAL, @ORGANIZATIONEXCHANGERATEID, null),
@ORGANIZATIONEXCHANGERATEID,
@DESTINATIONBASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
TEAMHIERARCHY
left join @TEAMMAPPINGTABLE TEAMMAPPING on TEAMHIERARCHY.ID = TEAMMAPPING.SOURCETEAMID
left join @TEAMMAPPINGTABLE PARENTTEAM on TEAMHIERARCHY.PARENTTEAMID = PARENTTEAM.SOURCETEAMID
left join dbo.TEAMFUNDRAISINGTEAM [EXISTINGDESTINATIONTEAM] on TEAMMAPPING.DESTINATIONTEAMID = [EXISTINGDESTINATIONTEAM].ID
where
[EXISTINGDESTINATIONTEAM].ID is null
order by
TEAMHIERARCHY.[LEVEL]
option (maxrecursion 9);
insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
(
TEAMFUNDRAISINGTEAMID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
TEAMMAPPING.DESTINATIONTEAMID,
TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.TEAMFUNDRAISINGTEAMCAPTAIN
left join @TEAMMAPPINGTABLE TEAMMAPPING on TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID = TEAMMAPPING.SOURCETEAMID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
where
TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID in (select SOURCETEAMID from @TEAMMAPPINGTABLE)
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null)
and
(
TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID not in
(
select
DTFTC.CONSTITUENTID
from
dbo.TEAMFUNDRAISINGTEAMCAPTAIN DTFTC
left join dbo.TEAMFUNDRAISINGTEAM DTFT on DTFT.ID = DTFTC.TEAMFUNDRAISINGTEAMID
where
DTFT.APPEALID = @DESTINATIONAPPEALID
and DTFT.ID = TEAMMAPPING.DESTINATIONTEAMID
)
and
TEAMMAPPING.DESTINATIONTEAMID not in
(
select
DTFT.ID
from
dbo.TEAMFUNDRAISINGTEAM DTFT
left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN DTFTC on DTFTC.TEAMFUNDRAISINGTEAMID = DTFT.ID
where
DTFT.APPEALID = @DESTINATIONAPPEALID
and DTFTC.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
)
);
if @COPYTEAMFUNDRAISERS = 1
begin
if @SOURCEAPPEALID is not null
update dbo.TEAMFUNDRAISER
set
GOAL = SOURCETEAMFUNDRAISER.GOAL
from
dbo.TEAMFUNDRAISER
inner join dbo.TEAMFUNDRAISER SOURCETEAMFUNDRAISER on
TEAMFUNDRAISER.CONSTITUENTID = SOURCETEAMFUNDRAISER.CONSTITUENTID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = SOURCETEAMFUNDRAISER.CONSTITUENTID
and @SOURCEAPPEALID = SOURCETEAMFUNDRAISER.APPEALID
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null)
where
TEAMFUNDRAISER.APPEALID = @DESTINATIONAPPEALID;
else
update dbo.TEAMFUNDRAISER
set
GOAL = SOURCETEAMFUNDRAISER.GOAL,
ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(SOURCETEAMFUNDRAISER.GOAL, @ORGANIZATIONEXCHANGERATEID, null),
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID = @DESTINATIONBASECURRENCYID
from
dbo.TEAMFUNDRAISER
inner join dbo.TEAMFUNDRAISER SOURCETEAMFUNDRAISER on TEAMFUNDRAISER.CONSTITUENTID = SOURCETEAMFUNDRAISER.CONSTITUENTID
left join dbo.TEAMFUNDRAISINGTEAMMEMBER on SOURCETEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = SOURCETEAMFUNDRAISER.CONSTITUENTID
where
TEAMFUNDRAISER.APPEALID = @DESTINATIONAPPEALID
and TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID in (select SOURCETEAMID from @TEAMMAPPINGTABLE)
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null);
if @SOURCEAPPEALID is not null
insert into dbo.TEAMFUNDRAISER
(
APPEALID,
CONSTITUENTID,
GOAL,
ORGANIZATIONGOAL,
ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@DESTINATIONAPPEALID,
TEAMFUNDRAISER.CONSTITUENTID,
TEAMFUNDRAISER.GOAL,
ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(TEAMFUNDRAISER.GOAL, @ORGANIZATIONEXCHANGERATEID, null),
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID = @DESTINATIONBASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.TEAMFUNDRAISER
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = TEAMFUNDRAISER.CONSTITUENTID
where
TEAMFUNDRAISER.APPEALID = @SOURCEAPPEALID
and
TEAMFUNDRAISER.CONSTITUENTID not in
(
select
DTF.CONSTITUENTID
from
dbo.TEAMFUNDRAISER DTF
where
DTF.APPEALID = @DESTINATIONAPPEALID
)
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null);
else
insert into dbo.TEAMFUNDRAISER
(
APPEALID,
CONSTITUENTID,
GOAL,
ORGANIZATIONGOAL,
ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@DESTINATIONAPPEALID,
TEAMFUNDRAISER.CONSTITUENTID,
TEAMFUNDRAISER.GOAL,
ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(TEAMFUNDRAISER.GOAL, @ORGANIZATIONEXCHANGERATEID, null),
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID = @DESTINATIONBASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.TEAMFUNDRAISER
left join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = TEAMFUNDRAISER.CONSTITUENTID
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID in (select SOURCETEAMID from @TEAMMAPPINGTABLE)
and
TEAMFUNDRAISER.CONSTITUENTID not in
(
select
DTF.CONSTITUENTID
from
dbo.TEAMFUNDRAISER DTF
where
DTF.APPEALID = @DESTINATIONAPPEALID
)
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null);
with SELECTEDTEAMFUNDRAISINGTEAMMEMBER
as
(
select
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID,
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID,
TEAMFUNDRAISER.CONSTITUENTID
from
dbo.TEAMFUNDRAISINGTEAMMEMBER
inner join dbo.TEAMFUNDRAISER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = TEAMFUNDRAISER.ID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM(@CURRENTAPPUSERID, @FEATUREID) as [RAC] on [RAC].ID = TEAMFUNDRAISER.CONSTITUENTID
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID in (select SOURCETEAMID from @TEAMMAPPINGTABLE)
or
TEAMFUNDRAISER.APPEALID = @SOURCEAPPEALID
and (@NOCONSTITUENTSECURITY = 1 or [RAC].ID is not null)
)
insert into dbo.TEAMFUNDRAISINGTEAMMEMBER
(
TEAMFUNDRAISINGTEAMID,
TEAMFUNDRAISERID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
TEAMMAPPING.DESTINATIONTEAMID,
DESTINATIONTEAMFUNDRAISER.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
SELECTEDTEAMFUNDRAISINGTEAMMEMBER
inner join dbo.TEAMFUNDRAISER DESTINATIONTEAMFUNDRAISER on
SELECTEDTEAMFUNDRAISINGTEAMMEMBER.CONSTITUENTID = DESTINATIONTEAMFUNDRAISER.CONSTITUENTID
and @DESTINATIONAPPEALID = DESTINATIONTEAMFUNDRAISER.APPEALID
left join @TEAMMAPPINGTABLE TEAMMAPPING on SELECTEDTEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMMAPPING.SOURCETEAMID
where
-- Exclude rows where the team membership already exists
(
TEAMMAPPING.DESTINATIONTEAMID is not null
and
not exists
(
select
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
from
dbo.TEAMFUNDRAISINGTEAMMEMBER
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMMAPPING.DESTINATIONTEAMID
and TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = DESTINATIONTEAMFUNDRAISER.ID
)
)
or
(
TEAMMAPPING.DESTINATIONTEAMID is null
and
not exists
(
select
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
from
dbo.TEAMFUNDRAISINGTEAMMEMBER
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID is null
and TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = DESTINATIONTEAMFUNDRAISER.ID
)
);
end
return 0;