USP_MERGETASK_CONSTITUENTVOLUNTEERS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTVOLUNTEERS
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @CHANGEDATE datetime = getdate();
-- If the source does not have a volunteer record,
-- then there is no need to continue
if exists
(
select top(1) ID
from dbo.VOLUNTEER
where ID = @SOURCEID
)
begin
-- Next we must determine if the target already has a
-- volunteer record
if exists
(
select top(1) ID
from dbo.VOLUNTEER
where ID = @TARGETID
)
begin
-- If the Target has a volunteer record, then we will
-- merge the fields of the source and target records
-- using the rule that a target field's value will
-- be kept unless it is null or holds a default value,
-- in which case the source field's value will be copied
-- to the target's field.
declare @SPONSORID uniqueidentifier;
declare @EMERGENCYCONTACTNAME nvarchar(255);
declare @EMERGENCYCONTACTPHONE nvarchar(100);
declare @AVAILABILITYCOMMENT nvarchar(4000);
declare @UNAVAILABLEFROM datetime;
declare @UNAVAILABLETO datetime;
-- Cache the source volunteer record's field values
select
@SPONSORID = SPONSORID,
@EMERGENCYCONTACTNAME = EMERGENCYCONTACTNAME,
@EMERGENCYCONTACTPHONE = EMERGENCYCONTACTPHONE,
@AVAILABILITYCOMMENT = AVAILABILITYCOMMENT,
@UNAVAILABLEFROM = UNAVAILABLEFROM,
@UNAVAILABLETO = UNAVAILABLETO
from
dbo.VOLUNTEER
where
ID = @SOURCEID;
-- Update the target's volunteer record
update dbo.VOLUNTEER
set
SPONSORID = case when(SPONSORID is null) then @SPONSORID else SPONSORID end,
-- Evaluate emergency contact name & phone together - update both or neither
EMERGENCYCONTACTNAME = case when(EMERGENCYCONTACTNAME = '' and EMERGENCYCONTACTPHONE = '') then @EMERGENCYCONTACTNAME else EMERGENCYCONTACTNAME end,
EMERGENCYCONTACTPHONE = case when(EMERGENCYCONTACTNAME = '' and EMERGENCYCONTACTPHONE = '') then @EMERGENCYCONTACTPHONE else EMERGENCYCONTACTPHONE end,
AVAILABILITYCOMMENT = case when(AVAILABILITYCOMMENT = '') then @AVAILABILITYCOMMENT else AVAILABILITYCOMMENT end,
-- Evaluate unavailability from & to dates together - update both or neither
UNAVAILABLEFROM = case when(UNAVAILABLEFROM is null and UNAVAILABLETO is null) then @UNAVAILABLEFROM else UNAVAILABLEFROM end,
UNAVAILABLETO = case when(UNAVAILABLEFROM is null and UNAVAILABLETO is null) then @UNAVAILABLETO else UNAVAILABLETO end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID = @TARGETID;
-- Merge volunteer date range records for the target and source constituents
--Bring over any source volunteer date range records that don't have date ranges
--that overlap with existing volunteer date range records on the target
update dbo.VOLUNTEERDATERANGE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and ID not in
(
select source.ID
from dbo.VOLUNTEERDATERANGE source
cross apply dbo.VOLUNTEERDATERANGE target
where target.CONSTITUENTID = @TARGETID
and source.CONSTITUENTID = @SOURCEID
and
(
(target.DATETO between source.DATEFROM and source.DATETO) or
(source.DATETO between target.DATEFROM and target.DATETO) or
(target.DATEFROM between source.DATEFROM and source.DATETO) or
(source.DATEFROM between target.DATEFROM and target.DATETO) or
(target.DATEFROM is null and source.DATEFROM <= target.DATETO) or
(source.DATEFROM is null and target.DATEFROM <= source.DATETO) or
(target.DATETO is null and source.DATETO >= target.DATEFROM) or
(source.DATETO is null and target.DATETO >= source.DATEFROM) or
(source.DATEFROM is null and target.DATEFROM is null) or
(source.DATETO is null and target.DATETO is null) or
(source.DATEFROM is null and source.DATETO is null) or
(target.DATEFROM is null and target.DATETO is null)
)
)
--If there is still a volunteer date range record on the source that is "open"
--(i.e. it's DATETO field is null) then make sure the most
--recent volunteer date range record on the target is open. This is done
--to prevent "open" volunteer date range status of being lost due to
--overlapping date ranges.
if exists
(
select top(1) ID
from dbo.VOLUNTEERDATERANGE
where CONSTITUENTID = @SOURCEID
and DATETO is null
)
begin
-- Order By clause evaluates NULL as less than any value.
-- Since we prefer NULL to any actual date when looking for
-- the most recent record, we have to look for NULL as a
-- separate search.
declare @openVDRID uniqueidentifier;
select @openVDRID = ID
from dbo.VOLUNTEERDATERANGE
where CONSTITUENTID = @TARGETID
and DATETO is null;
if @openVDRID is null
begin
-- If no open volunteer was found for the Target,
-- then "open" the record with the most recent DATETO field
update dbo.VOLUNTEERDATERANGE
set DATETO = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID in
(
select top(1) ID
from dbo.VOLUNTEERDATERANGE
where CONSTITUENTID = @TARGETID
order by DATEFROM desc
)
end
end
-- Now delete the source's lingering date range records
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.VOLUNTEERDATERANGE where CONSTITUENTID = @SOURCEID;
if not @contextCache is null
set CONTEXT_INFO @contextCache
end
else
begin
-- Otherwise, the target does not have a volunteer record
-- so we will create one by cloning the source's record.
insert into dbo.VOLUNTEER
(ID, SPONSORID, EMERGENCYCONTACTNAME, EMERGENCYCONTACTPHONE, AVAILABILITYCOMMENT, UNAVAILABLEFROM, UNAVAILABLETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@TARGETID, SPONSORID, EMERGENCYCONTACTNAME, EMERGENCYCONTACTPHONE, AVAILABILITYCOMMENT, UNAVAILABLEFROM, UNAVAILABLETO, ADDEDBYID, @CHANGEAGENTID, DATEADDED, @CHANGEDATE
from
dbo.VOLUNTEER
where
ID = @SOURCEID;
-- update the source's volunteer date range records to
-- point to the target
update dbo.VOLUNTEERDATERANGE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID;
end
-- Migrate the source's availability records to the target
update dbo.VOLUNTEERAVAILABILITY
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID;
-- Migrate volunteer administrative records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERADMINISTRATIVE
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERADMINISTRATIVE a
cross apply dbo.VOLUNTEERADMINISTRATIVE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.ADMINISTRATIVECODEID = b.ADMINISTRATIVECODEID
);
-- For redundant volunteer administrative records,
-- make sure the target record keeps the latest
-- expiration date
with ADMINUPDATEXPIRESON as
(
select b.ID, a.EXPIRESON
from dbo.VOLUNTEERADMINISTRATIVE a
cross apply dbo.VOLUNTEERADMINISTRATIVE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.ADMINISTRATIVECODEID = b.ADMINISTRATIVECODEID
and a.EXPIRESON > b.EXPIRESON
)
update dbo.VOLUNTEERADMINISTRATIVE
set
EXPIRESON = ADMINUPDATEXPIRESON.EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERADMINISTRATIVE
inner join ADMINUPDATEXPIRESON
on VOLUNTEERADMINISTRATIVE.ID = ADMINUPDATEXPIRESON.ID;
-- Migrate volunteer certification records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERCERTIFICATION
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERCERTIFICATION a
cross apply dbo.VOLUNTEERCERTIFICATION b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.CERTIFICATIONCODEID = b.CERTIFICATIONCODEID
);
-- For redundant volunteer certification records,
-- make sure the target record keeps the latest
-- expiration date
with CERTUPDATEXPIRESON as
(
select b.ID, a.EXPIRESON
from dbo.VOLUNTEERCERTIFICATION a
cross apply dbo.VOLUNTEERCERTIFICATION b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.CERTIFICATIONCODEID = b.CERTIFICATIONCODEID
and a.EXPIRESON > b.EXPIRESON
)
update dbo.VOLUNTEERCERTIFICATION
set
EXPIRESON = CERTUPDATEXPIRESON.EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERCERTIFICATION
inner join CERTUPDATEXPIRESON
on VOLUNTEERCERTIFICATION.ID = CERTUPDATEXPIRESON.ID;
-- Migrate volunteer course records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERCOURSE
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERCOURSE a
cross apply dbo.VOLUNTEERCOURSE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.COURSECODEID = b.COURSECODEID
);
-- For redundant volunteer course records,
-- make sure the target record keeps the latest
-- expiration date
with COURSEUPDATEXPIRESON as
(
select b.ID, a.EXPIRESON
from dbo.VOLUNTEERCOURSE a
cross apply dbo.VOLUNTEERCOURSE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.COURSECODEID = b.COURSECODEID
and a.EXPIRESON > b.EXPIRESON
)
update dbo.VOLUNTEERCOURSE
set
EXPIRESON = COURSEUPDATEXPIRESON.EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERCOURSE
inner join COURSEUPDATEXPIRESON
on VOLUNTEERCOURSE.ID = COURSEUPDATEXPIRESON.ID;
-- Migrate volunteer interest records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERINTEREST
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERINTEREST a
cross apply dbo.VOLUNTEERINTEREST b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.VOLUNTEERINTERESTCODEID = b.VOLUNTEERINTERESTCODEID
);
-- Migrate volunteer license records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERLICENSE
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERLICENSE a
cross apply dbo.VOLUNTEERLICENSE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.LICENSECODEID = b.LICENSECODEID
);
-- For redundant volunteer license records,
-- make sure the target record keeps the latest
-- expiration date
with LICUPDATEXPIRESON as
(
select b.ID, a.EXPIRESON
from dbo.VOLUNTEERLICENSE a
cross apply dbo.VOLUNTEERLICENSE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.LICENSECODEID = b.LICENSECODEID
and a.EXPIRESON > b.EXPIRESON
)
update dbo.VOLUNTEERLICENSE
set
EXPIRESON = LICUPDATEXPIRESON.EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERLICENSE
inner join LICUPDATEXPIRESON
on VOLUNTEERLICENSE.ID = LICUPDATEXPIRESON.ID;
-- Migrate volunteer medical records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERMEDICAL
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERMEDICAL a
cross apply dbo.VOLUNTEERMEDICAL b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.MEDICALCODEID = b.MEDICALCODEID
);
-- For redundant volunteer medical records,
-- make sure the target record keeps the latest
-- expiration date
with MEDUPDATEXPIRESON as
(
select b.ID, a.EXPIRESON
from dbo.VOLUNTEERMEDICAL a
cross apply dbo.VOLUNTEERMEDICAL b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.MEDICALCODEID = b.MEDICALCODEID
and a.EXPIRESON > b.EXPIRESON
)
update dbo.VOLUNTEERMEDICAL
set
EXPIRESON = MEDUPDATEXPIRESON.EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERMEDICAL
inner join MEDUPDATEXPIRESON
on VOLUNTEERMEDICAL.ID = MEDUPDATEXPIRESON.ID;
-- Migrate volunteer special need records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERSPECIALNEED
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERSPECIALNEED a
cross apply dbo.VOLUNTEERSPECIALNEED b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.SPECIALNEEDCODEID = b.SPECIALNEEDCODEID
);
-- Migrate volunteer skill records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERSKILL
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERSKILL a
cross apply dbo.VOLUNTEERSKILL b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.SKILLCODEID = b.SKILLCODEID
);
-- For redundant volunteer skill records,
-- make sure the target skill record reflects
-- the highest skill level of the target and source
-- records
with UPDATSKILLLEVEL as
(
select b.ID, a.SKILLLEVELID
from dbo.VOLUNTEERSKILL a
inner join dbo.VOLUNTEERSKILLLEVEL asl
on a.SKILLLEVELID = asl.ID
cross apply dbo.VOLUNTEERSKILL b
inner join dbo.VOLUNTEERSKILLLEVEL bsl
on b.SKILLLEVELID = bsl.ID
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.SKILLCODEID = b.SKILLCODEID
and asl.SEQUENCE > bsl.SEQUENCE
)
update dbo.VOLUNTEERSKILL
set
SKILLLEVELID = UPDATSKILLLEVEL.SKILLLEVELID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.VOLUNTEERSKILL
inner join UPDATSKILLLEVEL
on UPDATSKILLLEVEL.ID = VOLUNTEERSKILL.ID;
-- Migrate volunteer assignment records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERASSIGNMENT
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERASSIGNMENT a
cross apply dbo.VOLUNTEERASSIGNMENT b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.JOBOCCURRENCEID = b.JOBOCCURRENCEID
and a.DATE = b.DATE
);
-- Merge documentation records for the source and target
-- volunteer records
update dbo.VOLUNTEERNOTE
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
update dbo.VOLUNTEERNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
update dbo.VOLUNTEERMEDIALINK
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
update dbo.VOLUNTEERMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
update dbo.VOLUNTEERATTACHMENT
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
update dbo.VOLUNTEERATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
-- Migrate volunteer screen plan records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERSCREENPLAN
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERSCREENPLAN a
cross apply dbo.VOLUNTEERSCREENPLAN b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.SCREENPLANID = b.SCREENPLANID
);
-- Update volunteer screen plan links for shared volunteer types
update a
set a.VOLUNTEERSCREENPLANID = b.VOLUNTEERSCREENPLANID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
from dbo.VOLUNTEERVOLUNTEERTYPE a
inner join dbo.VOLUNTEERVOLUNTEERTYPE b on a.VOLUNTEERTYPEID = b.VOLUNTEERTYPEID
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.VOLUNTEERSCREENPLANID is not null
and a.VOLUNTEERSCREENPLANID <> b.VOLUNTEERSCREENPLANID
--save the information for later use in screen plan link conflict check
declare @TMP_VOLUNTEERSCREENPLANLINK table(
ID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
VOLUNTEERSCREENPLANID uniqueidentifier,
VOLUNTEERID uniqueidentifier,
SCREENPLANID uniqueidentifier);
insert into @TMP_VOLUNTEERSCREENPLANLINK
select
V.ID,
V.VOLUNTEERTYPEID,
V.VOLUNTEERSCREENPLANID,
V.VOLUNTEERID,
S.SCREENPLANID
from dbo.VOLUNTEERVOLUNTEERTYPE V
inner join dbo.VOLUNTEERSCREENPLAN S on v.VOLUNTEERSCREENPLANID = S.ID
and V.VOLUNTEERID = @SOURCEID
and V.VOLUNTEERSCREENPLANID not in
(select VOLUNTEERSCREENPLANID
from dbo.VOLUNTEERVOLUNTEERTYPE
where VOLUNTEERID = @TARGETID);
-- Migrate timesheet records from the source volunteer
-- to the target volunteer
update dbo.TIMESHEET
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID;
-- Migrate volunteer type records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERVOLUNTEERTYPE
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERVOLUNTEERTYPE a
cross apply dbo.VOLUNTEERVOLUNTEERTYPE b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.VOLUNTEERTYPEID = b.VOLUNTEERTYPEID
and a.STARTDATE = b.STARTDATE
and a.ENDDATE = b.ENDDATE
);
-- Update volunteer screen plan links for new volunteer types which share their screen plans with the existing ones
update a
set a.VOLUNTEERSCREENPLANID = b.VOLUNTEERSCREENPLANID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
from dbo.VOLUNTEERVOLUNTEERTYPE a
inner join @TMP_VOLUNTEERSCREENPLANLINK T on t.ID = a.ID
inner join
(select V.VOLUNTEERSCREENPLANID, S.SCREENPLANID
from dbo.VOLUNTEERVOLUNTEERTYPE V
inner join VOLUNTEERSCREENPLAN S on v.VOLUNTEERSCREENPLANID = S.ID
and V.VOLUNTEERID = @TARGETID)
b on t.SCREENPLANID = b.SCREENPLANID
and a.VOLUNTEERSCREENPLANID <> b.VOLUNTEERSCREENPLANID
-- Migrate volunteer location records from the source
-- volunteer record to the target volunteer record
update dbo.VOLUNTEERVOLUNTEERLOCATION
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID
and ID not in
(
select a.ID
from dbo.VOLUNTEERVOLUNTEERLOCATION a
cross apply dbo.VOLUNTEERVOLUNTEERLOCATION b
where a.VOLUNTEERID = @SOURCEID
and b.VOLUNTEERID = @TARGETID
and a.VOLUNTEERLOCATIONCODEID = b.VOLUNTEERLOCATIONCODEID
);
-- don't leave orphaned award assignment records (duplicates between source and target)
delete from dbo.VOLUNTEERAWARDASSIGNMENT where ID in
(
select source.ID
from dbo.VOLUNTEERAWARDASSIGNMENT source
inner join dbo.VOLUNTEERAWARDASSIGNMENT target
on source.VOLUNTEERID = @SOURCEID
and target.VOLUNTEERID = @TARGETID
and source.AWARDID = target.AWARDID
and source.DATEAWARDED = target.DATEAWARDED
);
-- Migrate volunteer award assignments
update dbo.VOLUNTEERAWARDASSIGNMENT
set VOLUNTEERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where VOLUNTEERID = @SOURCEID;
-- Now that all the child records have been migrated to
-- the target, remove the source's redundant volunteer record
exec dbo.USP_VOLUNTEER_DELETEBYID_WITHCHANGEAGENTID @SOURCEID, @CHANGEAGENTID;
end
return 0;