USP_BBNC_COMMITPROFILEUPDATESPOUSE
Updates a constituent's spouse information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @REMOVESPOUSE | bit | IN | |
| @SAMESPOUSE | bit | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @CLASSOF | smallint | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @UPDATESPOUSE | bit | IN | |
| @SPOUSELINKID | uniqueidentifier | INOUT | |
| @TITLE2CODEID | uniqueidentifier | IN | |
| @SUFFIX2CODEID | uniqueidentifier | IN | |
| @GENDERCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATESPOUSE
(
@CONSTITUENTID uniqueidentifier = null,
@REMOVESPOUSE bit = 0,
@SAMESPOUSE bit = 0,
@TITLECODEID uniqueidentifier = null,
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@KEYNAME nvarchar(100) = '',
@MAIDENNAME nvarchar(100) = '',
@SUFFIXCODEID uniqueidentifier = null,
@CLASSOF smallint = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@UPDATESPOUSE bit = 0,
@SPOUSELINKID uniqueidentifier = null output,
@TITLE2CODEID uniqueidentifier = null,
@SUFFIX2CODEID uniqueidentifier = null,
@GENDERCODE tinyint = 0
) as
set nocount on;
declare @ID uniqueidentifier;
declare @SPOUSEID uniqueidentifier;
declare @RELATIONSHIPTYPEID uniqueidentifier;
declare @SPOUSEREMOVEDRELATIONSHIPTYPECODEID uniqueidentifier;
if @CONSTITUENTID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
begin try
-- Update the current spouse's record
if @SAMESPOUSE = 1 and @UPDATESPOUSE = 1
begin
update dbo.CONSTITUENT set
[TITLECODEID] = @TITLECODEID,
[TITLE2CODEID] = @TITLE2CODEID,
[FIRSTNAME] = @FIRSTNAME,
[MIDDLENAME] = @MIDDLENAME,
[KEYNAME] = @KEYNAME,
[MAIDENNAME] = @MAIDENNAME,
[SUFFIXCODEID] = @SUFFIXCODEID,
[SUFFIX2CODEID] = @SUFFIX2CODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
from dbo.CONSTITUENT
inner join dbo.RELATIONSHIP on
CONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.ISSPOUSE = 1;
if @@ROWCOUNT = 0
begin
raiserror('ERR_SPOUSE_UPDATE_NO_SPOUSE', 16, 1);
return -2;
end
-- Update the spouse's primary education record (if it exists)
if coalesce(@CLASSOF, 0) > 0
update dbo.EDUCATIONALHISTORY set
[CLASSOF] = @CLASSOF,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
from dbo.EDUCATIONALHISTORY
inner join dbo.RELATIONSHIP on
EDUCATIONALHISTORY.CONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.ISSPOUSE = 1
where EDUCATIONALHISTORY.ISPRIMARYRECORD = 1;
end
-- Update the existing spouse relationship to be a non-spousal
-- relationship if requested
if @REMOVESPOUSE = 1
begin
select top (1)
@SPOUSEREMOVEDRELATIONSHIPTYPECODEID = [SPOUSEREMOVEDRELATIONSHIPTYPECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @SPOUSEREMOVEDRELATIONSHIPTYPECODEID is null
begin
raiserror('You must define the default relationship type for removed spouses.',16,1);
return -2;
end
update dbo.RELATIONSHIP
set
[ISSPOUSE] = 0,
[RELATIONSHIPTYPECODEID] = @SPOUSEREMOVEDRELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID] = @SPOUSEREMOVEDRELATIONSHIPTYPECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @CONSTITUENTID
and [RELATIONSHIP].[ISSPOUSE] = 1;
end
declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0
declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null
declare @PRIMARYCONSTITUENTTYPE tinyint = 0
declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0
declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null
declare @RECIPROCALCONSTITUENTTYPE tinyint = 0
-- Using an existing constituent as the new spouse
-- TMV 10/17/2007 CR285482-101207 Removed "and @UPDATESPOUSE = 1" clause, linking to an existing
-- constituent should happen even if there are no bio field updates, bio fields are ignored anyway
if @SAMESPOUSE <> 1 and @SPOUSELINKID is not null
begin
select top (1) @RELATIONSHIPTYPEID = [RELATIONSHIPTYPECODEID] from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @RELATIONSHIPTYPEID is null
begin
raiserror('You must define the default relationship type for spouses.',16,1);
return -2;
end
declare @CONSTITUENTHASSPOUSE bit, @RECIRPOCALHASSPOUSE bit;
declare @CONSTITUENTSPOUSENAME nvarchar(700), @RECIPROCALSPOUSENAME nvarchar(700);
declare @CONSTITUENTHOUSEHOLDID uniqueidentifier, @RECIPROCALHOUSEHOLDID uniqueidentifier;
declare @ERROR nvarchar(800);
exec dbo.USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALSPOUSEHOUSEHOLD @CONSTITUENTID, @HASSPOUSE=@CONSTITUENTHASSPOUSE output, @SPOUSENAME=@CONSTITUENTSPOUSENAME output, @HOUSEHOLDID=@RECIPROCALHOUSEHOLDID output
exec dbo.USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALSPOUSEHOUSEHOLD @SPOUSELINKID, @HASSPOUSE=@RECIRPOCALHASSPOUSE output, @SPOUSENAME=@RECIPROCALSPOUSENAME output, @HOUSEHOLDID=@CONSTITUENTHOUSEHOLDID output
if @CONSTITUENTHASSPOUSE = 1
begin
set @ERROR = 'Individual is already married to ' + @CONSTITUENTSPOUSENAME + '.';
raiserror(@ERROR,16,1);
return -2;
end
if @RECIRPOCALHASSPOUSE = 1
begin
set @ERROR = 'Spouse entered has an existing spouse, ' + @RECIPROCALSPOUSENAME + '.';
raiserror('BBERR_RECIPROCALCONSTITUENTID_SPOUSEEXISTS',16,1);
return -2;
end
if @CONSTITUENTHOUSEHOLDID is not null and @RECIPROCALHOUSEHOLDID is not null and @CONSTITUENTHOUSEHOLDID <> @RECIPROCALHOUSEHOLDID
begin
raiserror('Individuals belong to different households.',16,1);
return -2;
end
-- If this relationship already exists, just flag it as ISSPOUSE
-- otherwise add the new spouse relationship
if exists
(
select top (1)
1
from
dbo.RELATIONSHIP
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @CONSTITUENTID
and [RELATIONSHIP].[RECIPROCALCONSTITUENTID] = @SPOUSELINKID
and [RELATIONSHIP].[RELATIONSHIPTYPECODEID] = @RELATIONSHIPTYPEID
and [RELATIONSHIP].[RECIPROCALTYPECODEID] = @RELATIONSHIPTYPEID
)
update dbo.RELATIONSHIP
set
[ISSPOUSE] = 1,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @CONSTITUENTID
and [RELATIONSHIP].[RECIPROCALCONSTITUENTID] = @SPOUSELINKID
and [RELATIONSHIP].[RELATIONSHIPTYPECODEID] = @RELATIONSHIPTYPEID
and [RELATIONSHIP].[RECIPROCALTYPECODEID] = @RELATIONSHIPTYPEID
and [RELATIONSHIP].[ISSPOUSE] = 0;
else
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@SPOUSELINKID,
@CONSTITUENTID,
@RELATIONSHIPTYPEID,
@RELATIONSHIPTYPEID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
select @PRIMARYCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @CONSTITUENTID;
select @RECIPROCALCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @SPOUSELINKID;
-- add default recognition credits if creating a new relationship
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@PRIMARYCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @SPOUSELINKID,
NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID = @CONSTITUENTID, @SPOUSEID = @SPOUSELINKID, @COPYCONTACTINFO = 0, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE;
end
-- Creating a new constituent to use as the new spouse
if @SAMESPOUSE <> 1 and @SPOUSELINKID is null and @UPDATESPOUSE = 1
begin
declare @INFOSOURCECODEID uniqueidentifier;
select top (1)
@RELATIONSHIPTYPEID = [RELATIONSHIPTYPECODEID],
@INFOSOURCECODEID = [INFOSOURCECODEID]
from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @RELATIONSHIPTYPEID is null
begin
raiserror('You must define the default relationship type for spouses.',16,1);
return -2;
end
-- Add the new spouse constituent record
set @SPOUSEID = newid();
insert into dbo.CONSTITUENT
(
[ID],
[TITLECODEID],
[TITLE2CODEID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[SUFFIXCODEID],
[SUFFIX2CODEID],
[ISORGANIZATION],
[NETCOMMUNITYMEMBER],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[GENDERCODE]
)
values
(
@SPOUSEID,
@TITLECODEID,
@TITLE2CODEID,
@KEYNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@SUFFIXCODEID,
@SUFFIX2CODEID,
0,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@GENDERCODE
);
if @INFOSOURCECODEID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @ID = @SPOUSEID,
@CHANGEAGENTID=@CHANGEAGENTID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REVENUEID = null
end
insert into dbo.[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
select
@SPOUSEID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CHANGEDATE
,@CHANGEDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
from dbo.NAMEFORMATDEFAULT as NFD
where NFD.APPLYTOCODE = 0
set @SPOUSELINKID = @SPOUSEID; --JamesWill 09/27/2007 CR284221-092707 Need to send the created spouse ID to the caller so it can (potentially) add the spouse to a security group
-- Add the new spouse relationship
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@SPOUSEID,
@CONSTITUENTID,
@RELATIONSHIPTYPEID,
@RELATIONSHIPTYPEID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
select @PRIMARYCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @CONSTITUENTID;
select @RECIPROCALCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @SPOUSEID;
-- add default recognition credits if creating a new relationship
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@PRIMARYCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @SPOUSEID,
NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID = @CONSTITUENTID, @SPOUSEID = @SPOUSEID, @COPYCONTACTINFO = 0, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;