USP_DATAFORMTEMPLATE_ADD_ORGANIZATIONMERGER
The save procedure used by the add dataform template "Organization Merger Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @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 procedure. |
| @PRIMARYORGID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @SECONDARYORGID | uniqueidentifier | IN | Secondary organization |
| @CREATENEWORG | bit | IN | Merge both into new primary organization |
| @NEWORGNAME | nvarchar(100) | IN | Organization name |
| @MOVECHILDREN | bit | IN | Move all child organizations |
| @MERGERDATE | datetime | IN | Merger date |
| @INACTIVEREASON | uniqueidentifier | IN | Inactive reason |
| @INACTIVEDETAIL | nvarchar(300) | IN | Inactive detail |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ORGANIZATIONMERGER
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@PRIMARYORGID uniqueidentifier,
@SECONDARYORGID uniqueidentifier,
@CREATENEWORG bit,
@NEWORGNAME nvarchar(100) = '',
@MOVECHILDREN bit = 0,
@MERGERDATE datetime,
@INACTIVEREASON uniqueidentifier,
@INACTIVEDETAIL nvarchar(300) = null
)
as
set nocount on;
declare @CONTEXTCACHE varbinary(128)
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
--ensure we aren't trying to merge an org with itself
if @PRIMARYORGID = @SECONDARYORGID
raiserror('BBERR_ORGMERGER_PRIMARYSAMEASSECONDARY', 13,1);
--ensure that neither source org is inactive
if exists(select ID from dbo.CONSTITUENT where ID in (@PRIMARYORGID, @SECONDARYORGID) and ISINACTIVE = 1)
raiserror('BBERR_ORGMERGER_INACTIVEORG', 13,1);
--ensure that the source orgs are not related with a start date after the merger date
if exists(select ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @PRIMARYORGID and RECIPROCALCONSTITUENTID = @SECONDARYORGID and STARTDATE > @MERGERDATE)
raiserror('BBERR_ORGMERGER_FUTURERELATEDORGS', 13,1);
--disable trigger so that incorrect parent history records are not created
alter table dbo.ORGANIZATIONDATA disable trigger TR_ORGANIZATIONDATA_INSERTUPDATE
declare @TEMPRELATIONSHIPSETS table
(
NEWRELATIONSHIPSETID uniqueidentifier not null,
OLDRELATIONSHIPSETID uniqueidentifier not null,
OLDRELATIONSHIPSETEXISTED bit not null,
OLDRELATIONSHIPID uniqueidentifier not null
)
declare @NEWPARENTORGID uniqueidentifier
if @CREATENEWORG = 1
begin
--create target org
if len(@NEWORGNAME) = 0
raiserror('BBERR_ORGMERGER_NEWORGNAMEREQUIRED', 13,1);
exec dbo.USP_ORGANIZATION_ADD @ID output, @CURRENTAPPUSERID, @CHANGEAGENTID, @NEWORGNAME
--copy addresses from primary to target
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ISCONFIDENTIAL
)
select
NEWID(),
@ID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
DONOTMAILREASONCODEID,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ISCONFIDENTIAL
from
dbo.ADDRESS
where
CONSTITUENTID = @PRIMARYORGID
--copy phones from primary to target
insert into dbo.PHONE
(
ID,
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTCALL,
STARTTIME,
ENDTIME,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
COUNTRYID,
STARTDATE,
ENDDATE,
DONOTCALLREASONCODEID,
ISCONFIDENTIAL,
ORIGINCODE,
SEASONALSTARTDATE,
SEASONALENDDATE
)
select
NEWID(),
@ID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
DONOTCALL,
STARTTIME,
ENDTIME,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
COUNTRYID,
STARTDATE,
ENDDATE,
DONOTCALLREASONCODEID,
ISCONFIDENTIAL,
ORIGINCODE,
SEASONALSTARTDATE,
SEASONALENDDATE
from
dbo.PHONE
where
CONSTITUENTID = @PRIMARYORGID
--copy email addresses from primary to target
insert into dbo.EMAILADDRESS
(
ID,
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTEMAIL,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
ORIGINCODE
)
select
NEWID(),
@ID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
DONOTEMAIL,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
ORIGINCODE
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @PRIMARYORGID
--copy the primary's parent org id to the target
declare @PRIMARYORGPARENTCORPID uniqueidentifier
select @PRIMARYORGPARENTCORPID = PARENTCORPID
from dbo.ORGANIZATIONDATA
where ID = @PRIMARYORGID
if @PRIMARYORGPARENTCORPID is not null
begin
update dbo.ORGANIZATIONDATA set
PARENTCORPID = @PRIMARYORGPARENTCORPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
if @@ROWCOUNT = 0
insert into dbo.ORGANIZATIONDATA
(
ID,
PARENTCORPID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
@PRIMARYORGPARENTCORPID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
--manually create the org parent history record
--since the trigger on ORGANIZATIONDATA that typically
--creates it is disabled
insert into dbo.ORGANIZATIONPARENTHISTORY
(
ID,
CHILDCORPID,
PARENTCORPID,
STARTDATE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
values
(
newid(),
@ID,
@PRIMARYORGPARENTCORPID,
@MERGERDATE,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
)
end
--copy relationships from primary to target
insert into @TEMPRELATIONSHIPSETS
(
NEWRELATIONSHIPSETID,
OLDRELATIONSHIPSETID,
OLDRELATIONSHIPID,
OLDRELATIONSHIPSETEXISTED
)
select
newid(),
case when RELATIONSHIPSETID is null then newid() else RELATIONSHIPSETID end,
ID,
case when RELATIONSHIPSETID is null then 0 else 1 end
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
and RECIPROCALCONSTITUENTID <> @SECONDARYORGID
-- Set RELATIONSHIPSETID for records that are being copied and don't have it set since the below
-- code requires it be set.
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
OLDRELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @TEMPRELATIONSHIPSETS
where OLDRELATIONSHIPSETEXISTED = 0
; with TEMPRELATIONSHIPSETSDIDNTEXIST_CTE as
(
select
OLDRELATIONSHIPID,
OLDRELATIONSHIPSETID
from @TEMPRELATIONSHIPSETS
where OLDRELATIONSHIPSETEXISTED = 0
)
update
dbo.RELATIONSHIP
set
RELATIONSHIPSETID = OLDRELATIONSHIP.OLDRELATIONSHIPSETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP
inner join
(
-- Original relationship
select
OLDRELATIONSHIPID as ID,
OLDRELATIONSHIPSETID
from TEMPRELATIONSHIPSETSDIDNTEXIST_CTE
union all
-- Reciprocal of original relationship
select
RECIPROCALRELATIONSHIP.ID,
OLDRELATIONSHIPSETID
from TEMPRELATIONSHIPSETSDIDNTEXIST_CTE as TEMPRELATIONSHIPSETS
inner join dbo.RELATIONSHIP ORIGINALRELATIONSHIP on TEMPRELATIONSHIPSETS.OLDRELATIONSHIPID = ORIGINALRELATIONSHIP.ID
inner join dbo.RELATIONSHIP RECIPROCALRELATIONSHIP on
ORIGINALRELATIONSHIP.RELATIONSHIPCONSTITUENTID = RECIPROCALRELATIONSHIP.RECIPROCALCONSTITUENTID and
ORIGINALRELATIONSHIP.RECIPROCALCONSTITUENTID = RECIPROCALRELATIONSHIP.RELATIONSHIPCONSTITUENTID and
ORIGINALRELATIONSHIP.RELATIONSHIPTYPECODEID = RECIPROCALRELATIONSHIP.RECIPROCALTYPECODEID and
ORIGINALRELATIONSHIP.RECIPROCALTYPECODEID = RECIPROCALRELATIONSHIP.RELATIONSHIPTYPECODEID
) OLDRELATIONSHIP on RELATIONSHIP.ID = OLDRELATIONSHIP.ID
-- Create copied relationships
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
NEWRELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TEMPRELATIONSHIPSETS
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ENDDATE,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
CONTACTTYPECODEID,
POSITION,
ISMATCHINGGIFTRELATIONSHIP,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ISPRIMARYCONTACT,
RELATIONSHIPSETID,
COMMENTS,
ISEMERGENCYCONTACT,
RECEIVESREPORTCARD
)
select
newid(),
@ID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ENDDATE,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
CONTACTTYPECODEID,
POSITION,
ISMATCHINGGIFTRELATIONSHIP,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
ISPRIMARYCONTACT,
TRS.NEWRELATIONSHIPSETID,
COMMENTS,
ISEMERGENCYCONTACT,
RECEIVESREPORTCARD
from
dbo.RELATIONSHIP
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIP.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
where
RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
--copy job info from primary to target
insert into dbo.RELATIONSHIPJOBINFO
(
ID,
RELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ENDDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
JOBSCHEDULECODEID,
JOBDIVISION,
JOBDEPARTMENT
)
select
newid(),
TRS.NEWRELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
JOBSCHEDULECODEID,
JOBDIVISION,
JOBDEPARTMENT
from
dbo.RELATIONSHIPJOBINFO
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
--update relationship addresses to point at target
update
ADDRESS
set
RELATIONSHIPID = RELATIONSHIP.ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS
inner join dbo.RELATIONSHIP OLDREL on ADDRESS.RELATIONSHIPID = OLDREL.ID
inner join @TEMPRELATIONSHIPSETS TRS on OLDREL.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
inner join dbo.RELATIONSHIP on TRS.NEWRELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
--update relationship phones to point at target
update
PHONE
set
RELATIONSHIPID = RELATIONSHIP.ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PHONE
inner join dbo.RELATIONSHIP OLDREL on PHONE.RELATIONSHIPID = OLDREL.ID
inner join @TEMPRELATIONSHIPSETS TRS on OLDREL.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
inner join dbo.RELATIONSHIP on TRS.NEWRELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
--update relationship email addresses to point at target
update
EMAILADDRESS
set
RELATIONSHIPID = RELATIONSHIP.ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.EMAILADDRESS
inner join dbo.RELATIONSHIP OLDREL on EMAILADDRESS.RELATIONSHIPID = OLDREL.ID
inner join @TEMPRELATIONSHIPSETS TRS on OLDREL.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
inner join dbo.RELATIONSHIP on TRS.NEWRELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
--terminate primary relationships
set @CONTEXTCACHE = CONTEXT_INFO()
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID
delete
RELATIONSHIP
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
and STARTDATE > @MERGERDATE
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE
update
RELATIONSHIP
set
ENDDATE = @MERGERDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
and (ENDDATE IS NULL or ENDDATE > @MERGERDATE)
--terminate primary job info
set @CONTEXTCACHE = CONTEXT_INFO()
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID
delete
RELATIONSHIPJOBINFO
from
dbo.RELATIONSHIPJOBINFO
inner join RELATIONSHIP on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
and RELATIONSHIPJOBINFO.STARTDATE > @MERGERDATE
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE
update
RELATIONSHIPJOBINFO
set
ENDDATE = @MERGERDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIPJOBINFO
inner join RELATIONSHIP on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PRIMARYORGID
and (RELATIONSHIPJOBINFO.ENDDATE IS NULL or RELATIONSHIPJOBINFO.ENDDATE > @MERGERDATE)
select
@NEWPARENTORGID = PARENTCORPID
from
dbo.ORGANIZATIONDATA
where
ID = @PRIMARYORGID
and @MOVECHILDREN = 0
if @NEWPARENTORGID is null
set @NEWPARENTORGID = @ID
-- end primary children's previous parent history records
update
ORGANIZATIONPARENTHISTORY
set
ENDDATE = DateAdd("d", -1, @MERGERDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ORGANIZATIONPARENTHISTORY
inner join dbo.ORGANIZATIONDATA on ORGANIZATIONPARENTHISTORY.CHILDCORPID = ORGANIZATIONDATA.ID
and ORGANIZATIONPARENTHISTORY.PARENTCORPID = ORGANIZATIONDATA.PARENTCORPID
where
ORGANIZATIONDATA.PARENTCORPID = @PRIMARYORGID
and (ORGANIZATIONPARENTHISTORY.ENDDATE >= @MERGERDATE or ORGANIZATIONPARENTHISTORY.ENDDATE IS NULL)
-- create new parent history records for primary children
insert into dbo.ORGANIZATIONPARENTHISTORY
(
ID,
CHILDCORPID,
PARENTCORPID,
STARTDATE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
select
newid(),
ORGANIZATIONDATA.ID,
@NEWPARENTORGID,
@MERGERDATE,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from
dbo.ORGANIZATIONDATA
where
PARENTCORPID = @PRIMARYORGID
--move primary child orgs
update
ORGANIZATIONDATA
set
PARENTCORPID = @NEWPARENTORGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ORGANIZATIONDATA
where
PARENTCORPID = @PRIMARYORGID
exec dbo.USP_ORGANIZATIONMERGER_MAKEORGCHILD
@CHILDORGANIZATIONID = @PRIMARYORGID,
@PARENTORGANIZATIONID = @ID,
@MERGERDATE = @MERGERDATE,
@CURRENTDATE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID
--mark primary as inactive
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTINACTIVEDETAIL @PRIMARYORGID, @CHANGEAGENTID, @INACTIVEREASON, @INACTIVEDETAIL
end
else
set @ID = @PRIMARYORGID
--cache primary contact ID
declare @PRIMARYCONTACTRELATIONSHIPID uniqueidentifier = (select ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @ID and ISPRIMARYCONTACT = 1)
--update dates on target relationships matched by secondary
update
RELATIONSHIP
set
STARTDATE = case when SECONDARYRELS.STARTDATE < RELATIONSHIP.STARTDATE then SECONDARYRELS.STARTDATE else RELATIONSHIP.STARTDATE end,
ENDDATE = case when SECONDARYRELS.ENDDATE > RELATIONSHIP.ENDDATE then SECONDARYRELS.ENDDATE else RELATIONSHIP.ENDDATE end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP
inner join dbo.RELATIONSHIP SECONDARYRELS on RELATIONSHIP.RECIPROCALCONSTITUENTID = SECONDARYRELS.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPTYPECODEID = SECONDARYRELS.RELATIONSHIPTYPECODEID
and RELATIONSHIP.RECIPROCALTYPECODEID = SECONDARYRELS.RECIPROCALTYPECODEID
and SECONDARYRELS.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
and (SECONDARYRELS.STARTDATE < RELATIONSHIP.STARTDATE or SECONDARYRELS.ENDDATE > RELATIONSHIP.ENDDATE)
--copy unmatched relationships from secondary to target
delete from @TEMPRELATIONSHIPSETS
insert into @TEMPRELATIONSHIPSETS
(
NEWRELATIONSHIPSETID,
OLDRELATIONSHIPSETID,
OLDRELATIONSHIPID,
OLDRELATIONSHIPSETEXISTED
)
select
newid(),
case when RELATIONSHIP.RELATIONSHIPSETID is null then newid() else RELATIONSHIP.RELATIONSHIPSETID end,
RELATIONSHIP.ID,
case when RELATIONSHIP.RELATIONSHIPSETID is null then 0 else 1 end
from
dbo.RELATIONSHIP
left outer join dbo.RELATIONSHIP TARGETRELS on RELATIONSHIP.RECIPROCALCONSTITUENTID = TARGETRELS.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPTYPECODEID = TARGETRELS.RELATIONSHIPTYPECODEID
and RELATIONSHIP.RECIPROCALTYPECODEID = TARGETRELS.RECIPROCALTYPECODEID
and TARGETRELS.RELATIONSHIPCONSTITUENTID = @ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and RELATIONSHIP.RECIPROCALCONSTITUENTID <> @PRIMARYORGID
and TARGETRELS.ID is null
-- Set RELATIONSHIPSETID for records that are being copied and don't have it set since the below
-- code requires it be set.
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
OLDRELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @TEMPRELATIONSHIPSETS
where OLDRELATIONSHIPSETEXISTED = 0
; with TEMPRELATIONSHIPSETSDIDNTEXIST_CTE as
(
select
OLDRELATIONSHIPID,
OLDRELATIONSHIPSETID
from @TEMPRELATIONSHIPSETS
where
OLDRELATIONSHIPSETEXISTED = 0
)
update
dbo.RELATIONSHIP
set
RELATIONSHIPSETID = OLDRELATIONSHIP.OLDRELATIONSHIPSETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP
inner join
(
-- Original relationship
select
OLDRELATIONSHIPID as ID,
OLDRELATIONSHIPSETID
from TEMPRELATIONSHIPSETSDIDNTEXIST_CTE
union all
-- Reciprocal of original relationship
select
RECIPROCALRELATIONSHIP.ID,
OLDRELATIONSHIPSETID
from TEMPRELATIONSHIPSETSDIDNTEXIST_CTE as TEMPRELATIONSHIPSETS
inner join dbo.RELATIONSHIP ORIGINALRELATIONSHIP on TEMPRELATIONSHIPSETS.OLDRELATIONSHIPID = ORIGINALRELATIONSHIP.ID
inner join dbo.RELATIONSHIP RECIPROCALRELATIONSHIP on
ORIGINALRELATIONSHIP.RELATIONSHIPCONSTITUENTID = RECIPROCALRELATIONSHIP.RECIPROCALCONSTITUENTID and
ORIGINALRELATIONSHIP.RECIPROCALCONSTITUENTID = RECIPROCALRELATIONSHIP.RELATIONSHIPCONSTITUENTID and
ORIGINALRELATIONSHIP.RELATIONSHIPTYPECODEID = RECIPROCALRELATIONSHIP.RECIPROCALTYPECODEID and
ORIGINALRELATIONSHIP.RECIPROCALTYPECODEID = RECIPROCALRELATIONSHIP.RELATIONSHIPTYPECODEID
) OLDRELATIONSHIP on RELATIONSHIP.ID = OLDRELATIONSHIP.ID
-- Create copied relationships
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
NEWRELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TEMPRELATIONSHIPSETS
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ENDDATE,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
CONTACTTYPECODEID,
POSITION,
ISMATCHINGGIFTRELATIONSHIP,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ISPRIMARYCONTACT,
RELATIONSHIPSETID,
COMMENTS,
ISEMERGENCYCONTACT,
RECEIVESREPORTCARD
)
select
newid(),
@ID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ENDDATE,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
CONTACTTYPECODEID,
POSITION,
ISMATCHINGGIFTRELATIONSHIP,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
ISPRIMARYCONTACT,
TRS.NEWRELATIONSHIPSETID,
COMMENTS,
ISEMERGENCYCONTACT,
RECEIVESREPORTCARD
from
dbo.RELATIONSHIP
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIP.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
where
RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
--restore primary contact relationship
if @PRIMARYCONTACTRELATIONSHIPID is null
select
@PRIMARYCONTACTRELATIONSHIPID = RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join dbo.RELATIONSHIP SECONDARYRELS on RELATIONSHIP.RECIPROCALCONSTITUENTID = SECONDARYRELS.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPTYPECODEID = SECONDARYRELS.RELATIONSHIPTYPECODEID
and RELATIONSHIP.RECIPROCALTYPECODEID = SECONDARYRELS.RECIPROCALTYPECODEID
and SECONDARYRELS.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
and SECONDARYRELS.ISPRIMARYCONTACT = 1
update
dbo.RELATIONSHIP
set
ISPRIMARYCONTACT = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @PRIMARYCONTACTRELATIONSHIPID
--copy job info from secondary to target for unmatched relationships
insert into dbo.RELATIONSHIPJOBINFO
(
ID,
RELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ENDDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
JOBSCHEDULECODEID,
JOBDIVISION,
JOBDEPARTMENT
)
select
newid(),
TRS.NEWRELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
JOBSCHEDULECODEID,
JOBDIVISION,
JOBDEPARTMENT
from
dbo.RELATIONSHIPJOBINFO
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = TRS.OLDRELATIONSHIPSETID
--copy job info from secondary to target for matched relationships without job info
insert into dbo.RELATIONSHIPJOBINFO
(
ID,
RELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ENDDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
JOBSCHEDULECODEID,
JOBDIVISION,
JOBDEPARTMENT
)
select
newid(),
RELATIONSHIP.RELATIONSHIPSETID,
SECONDARYJOBINFO.JOBTITLE,
SECONDARYJOBINFO.JOBCATEGORYCODEID,
SECONDARYJOBINFO.CAREERLEVELCODEID,
SECONDARYJOBINFO.STARTDATE,
SECONDARYJOBINFO.ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
SECONDARYJOBINFO.JOBSCHEDULECODEID,
SECONDARYJOBINFO.JOBDIVISION,
SECONDARYJOBINFO.JOBDEPARTMENT
from
dbo.RELATIONSHIPJOBINFO SECONDARYJOBINFO
inner join dbo.RELATIONSHIP SECONDARYRELS on SECONDARYJOBINFO.RELATIONSHIPSETID = SECONDARYRELS.RELATIONSHIPSETID
inner join dbo.RELATIONSHIP on SECONDARYRELS.RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
and SECONDARYRELS.RELATIONSHIPTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID
and SECONDARYRELS.RECIPROCALTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
left outer join dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
where
SECONDARYRELS.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and RELATIONSHIPJOBINFO.ID is null
--copy relationship addresses to contacts
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ISCONFIDENTIAL,
RELATIONSHIPID
)
select
NEWID(),
RECIPROCALCONSTITUENTID,
ADDRESSTYPECODEID,
0,
DONOTMAIL,
ADDRESS.STARTDATE,
ADDRESS.ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
DONOTMAILREASONCODEID,
RELATIONSHIP.STARTDATE,
RELATIONSHIP.ENDDATE,
ISCONFIDENTIAL,
RELATIONSHIP.ID
from
dbo.ADDRESS
inner join dbo.RELATIONSHIP on ADDRESS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIP.RELATIONSHIPSETID = TRS.NEWRELATIONSHIPSETID
where
ADDRESS.CONSTITUENTID = @ID
and ADDRESS.ISPRIMARY = 1
and RELATIONSHIP.ISCONTACT = 1
--terminate secondary relationship addresses if they aren't primary or if we've added a new relationship address
update
ADDRESS
set
HISTORICALSTARTDATE = case when ADDRESS.HISTORICALSTARTDATE > @MERGERDATE then NULL else ADDRESS.HISTORICALSTARTDATE end,
HISTORICALENDDATE = @MERGERDATE,
DONOTMAIL = 1,
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS
inner join dbo.RELATIONSHIP on ADDRESS.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and (ADDRESS.ISPRIMARY = 0 or exists (select A.ID
from dbo.ADDRESS A inner join dbo.RELATIONSHIP R on A.RELATIONSHIPID = R.ID
where R.RELATIONSHIPCONSTITUENTID = @ID and A.CONSTITUENTID = ADDRESS.CONSTITUENTID))
--set new addresses to primary if there isn't already a primary. this should only be the case if the address we terminated was previously primary.
update
ADDRESS
set
ISPRIMARY = 1,
HISTORICALENDDATE = NULL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS
inner join dbo.RELATIONSHIP on ADDRESS.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
and not exists (select A.ID
from dbo.ADDRESS A
where A.CONSTITUENTID = ADDRESS.CONSTITUENTID and A.ISPRIMARY = 1)
--copy relationship phones to contacts if number/type combo does not exist
insert into dbo.PHONE
(
ID,
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTCALL,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
COUNTRYID,
STARTDATE,
ENDDATE,
DONOTCALLREASONCODEID,
ISCONFIDENTIAL,
ORIGINCODE,
SEASONALSTARTDATE,
SEASONALENDDATE,
RELATIONSHIPID
)
select
NEWID(),
RECIPROCALCONSTITUENTID,
PHONE.PHONETYPECODEID,
PHONE.NUMBER,
0,
PHONE.SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
PHONE.DONOTCALL,
PHONE.INFOSOURCECODEID,
PHONE.INFOSOURCECOMMENTS,
PHONE.COUNTRYID,
RELATIONSHIP.STARTDATE,
RELATIONSHIP.ENDDATE,
PHONE.DONOTCALLREASONCODEID,
PHONE.ISCONFIDENTIAL,
PHONE.ORIGINCODE,
PHONE.SEASONALSTARTDATE,
PHONE.SEASONALENDDATE,
RELATIONSHIP.ID
from
dbo.PHONE
inner join dbo.RELATIONSHIP on PHONE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIP.RELATIONSHIPSETID = TRS.NEWRELATIONSHIPSETID
left outer join dbo.PHONE RELPHONES on RELATIONSHIP.RECIPROCALCONSTITUENTID = RELPHONES.CONSTITUENTID
and PHONE.PHONETYPECODEID = RELPHONES.PHONETYPECODEID
and PHONE.NUMBER = RELPHONES.NUMBER
where
PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1
and RELATIONSHIP.ISCONTACT = 1
and RELPHONES.ID IS NULL
--update secondary relationship phones to point at target relationship if the number and type matches
update
PHONE
set
RELATIONSHIPID = TARGETRELS.ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PHONE
inner join dbo.RELATIONSHIP on PHONE.RELATIONSHIPID = RELATIONSHIP.ID
inner join dbo.PHONE TARGETPHONES on PHONE.NUMBER = TARGETPHONES.NUMBER
and PHONE.PHONETYPECODEID = TARGETPHONES.PHONETYPECODEID
and PHONE.CONSTITUENTID = @ID
inner join dbo.RELATIONSHIP TARGETRELS on RELATIONSHIP.RECIPROCALCONSTITUENTID = TARGETRELS.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPTYPECODEID = TARGETRELS.RELATIONSHIPTYPECODEID
and RELATIONSHIP.RECIPROCALTYPECODEID = TARGETRELS.RECIPROCALTYPECODEID
and TARGETRELS.RELATIONSHIPCONSTITUENTID = TARGETPHONES.CONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
--terminate secondary relationship phones if they aren't primary or if we've added a new relationship phone
update
PHONE
set
STARTDATE = case when PHONE.STARTDATE > @MERGERDATE then NULL else PHONE.STARTDATE end,
ENDDATE = @MERGERDATE,
DONOTCALL = 1,
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PHONE
inner join dbo.RELATIONSHIP on PHONE.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and (PHONE.ISPRIMARY = 0 or exists (select P.ID
from dbo.PHONE P inner join dbo.RELATIONSHIP R on P.RELATIONSHIPID = R.ID
where R.RELATIONSHIPCONSTITUENTID = @ID and P.CONSTITUENTID = PHONE.CONSTITUENTID))
--set new phones to primary if there isn't already a primary. this should only be the case if the phone we terminated was previously primary.
update
PHONE
set
ISPRIMARY = 1,
ENDDATE = NULL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PHONE
inner join dbo.RELATIONSHIP on PHONE.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
and not exists (select P.ID
from dbo.PHONE P
where P.CONSTITUENTID = PHONE.CONSTITUENTID and P.ISPRIMARY = 1)
--copy relationship emails to contacts
insert into dbo.EMAILADDRESS
(
ID,
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTEMAIL,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
ORIGINCODE,
RELATIONSHIPID
)
select
NEWID(),
RECIPROCALCONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
0,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
DONOTEMAIL,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
ORIGINCODE,
RELATIONSHIP.ID
from
dbo.EMAILADDRESS
inner join dbo.RELATIONSHIP on EMAILADDRESS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
inner join @TEMPRELATIONSHIPSETS TRS on RELATIONSHIP.RELATIONSHIPSETID = TRS.NEWRELATIONSHIPSETID
where
EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1
and RELATIONSHIP.ISCONTACT = 1
--terminate secondary relationship emails if they aren't primary or if we've added a new relationship email
update
EMAILADDRESS
set
DONOTEMAIL = 1,
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.EMAILADDRESS
inner join dbo.RELATIONSHIP on EMAILADDRESS.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and (EMAILADDRESS.ISPRIMARY = 0 or exists (select E.ID
from dbo.EMAILADDRESS E inner join dbo.RELATIONSHIP R on E.RELATIONSHIPID = R.ID
where R.RELATIONSHIPCONSTITUENTID = @ID and E.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID))
--set new emails to primary if there isn't already a primary. this should only be the case if the email we terminated was previously primary.
update
EMAILADDRESS
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.EMAILADDRESS
inner join dbo.RELATIONSHIP on EMAILADDRESS.RELATIONSHIPID = RELATIONSHIP.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
and not exists (select E.ID
from dbo.EMAILADDRESS E
where E.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID and E.ISPRIMARY = 1)
--terminate secondary relationships
set @CONTEXTCACHE = CONTEXT_INFO()
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID
delete
RELATIONSHIP
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and STARTDATE > @MERGERDATE
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE
update
RELATIONSHIP
set
ENDDATE = @MERGERDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and (ENDDATE IS NULL or ENDDATE > @MERGERDATE)
--terminate secondary job info
set @CONTEXTCACHE = CONTEXT_INFO()
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID
delete
RELATIONSHIPJOBINFO
from
dbo.RELATIONSHIPJOBINFO
inner join RELATIONSHIP on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and RELATIONSHIPJOBINFO.STARTDATE > @MERGERDATE
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE
update
RELATIONSHIPJOBINFO
set
ENDDATE = @MERGERDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIPJOBINFO
inner join RELATIONSHIP on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SECONDARYORGID
and (RELATIONSHIPJOBINFO.ENDDATE IS NULL or RELATIONSHIPJOBINFO.ENDDATE > @MERGERDATE)
set @NEWPARENTORGID = null
select
@NEWPARENTORGID = PARENTCORPID
from
dbo.ORGANIZATIONDATA
where
ID = @SECONDARYORGID
and @MOVECHILDREN = 0
if @NEWPARENTORGID is null
set @NEWPARENTORGID = @ID
-- end secondary children's previous parent history records
update
ORGANIZATIONPARENTHISTORY
set
ENDDATE = DateAdd("d", -1, @MERGERDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ORGANIZATIONPARENTHISTORY
inner join dbo.ORGANIZATIONDATA on ORGANIZATIONPARENTHISTORY.CHILDCORPID = ORGANIZATIONDATA.ID
and ORGANIZATIONPARENTHISTORY.PARENTCORPID = ORGANIZATIONDATA.PARENTCORPID
where
ORGANIZATIONDATA.PARENTCORPID = @SECONDARYORGID
and (ORGANIZATIONPARENTHISTORY.ENDDATE >= @MERGERDATE or ORGANIZATIONPARENTHISTORY.ENDDATE IS NULL)
-- create new parent history records for secondary children
insert into dbo.ORGANIZATIONPARENTHISTORY
(
ID,
CHILDCORPID,
PARENTCORPID,
STARTDATE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
select
newid(),
ORGANIZATIONDATA.ID,
@NEWPARENTORGID,
@MERGERDATE,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from
dbo.ORGANIZATIONDATA
where
PARENTCORPID = @SECONDARYORGID
--move secondary child orgs
update
ORGANIZATIONDATA
set
PARENTCORPID = @NEWPARENTORGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ORGANIZATIONDATA
where
PARENTCORPID = @SECONDARYORGID
--make the secondary org a child of the merged org
exec dbo.USP_ORGANIZATIONMERGER_MAKEORGCHILD
@CHILDORGANIZATIONID = @SECONDARYORGID,
@PARENTORGANIZATIONID = @ID,
@MERGERDATE = @MERGERDATE,
@CURRENTDATE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID
--mark secondary as inactive
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTINACTIVEDETAIL @SECONDARYORGID, @CHANGEAGENTID, @INACTIVEREASON, @INACTIVEDETAIL
--insert record into merger history
insert into dbo.ORGANIZATIONMERGERHISTORY
(
ID,
SOURCEORG1ID,
SOURCEORG2ID,
NEWORGID,
MERGERDATE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
values
(
newid(),
@PRIMARYORGID,
@SECONDARYORGID,
@ID,
@MERGERDATE,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
)
end try
begin catch
exec dbo.USP_RAISE_ERROR
--reenable trigger
alter table dbo.ORGANIZATIONDATA enable trigger TR_ORGANIZATIONDATA_INSERTUPDATE
return 1
end catch
alter table dbo.ORGANIZATIONDATA enable trigger TR_ORGANIZATIONDATA_INSERTUPDATE
return 0