USP_HOUSEHOLDMEMBERADD
Adds a new or existing constituent to a household.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @RELATIONSHIPID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @GROUPID | uniqueidentifier | IN | |
| @MEMBERID | uniqueidentifier | INOUT | |
| @LASTNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @NICKNAME | nvarchar(50) | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @GENDERCODE | tinyint | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @COPYCONTACTINFO | bit | IN | |
| @SOURCECONSTITUENTFORCONTACTINFO | uniqueidentifier | IN | |
| @RELATEDCONSTITUENTID | uniqueidentifier | IN | |
| @ISSPOUSE | bit | IN | |
| @RECOGNIZEMEMBERFORHOUSEHOLD | bit | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @DATAFORMTEMPLATEID | uniqueidentifier | IN | |
| @SKIP_ADDING_SECURITYGROUPS | bit | IN | |
| @SKIP_ADDING_SITES | bit | IN | |
| @TITLE2CODEID | uniqueidentifier | IN | |
| @SUFFIX2CODEID | uniqueidentifier | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_HOUSEHOLDMEMBERADD
(
@ID uniqueidentifier = null output,
@RELATIONSHIPID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@GROUPID uniqueidentifier,
@MEMBERID uniqueidentifier = null output,
@LASTNAME nvarchar(100) = null,
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@NICKNAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@MAIDENNAME nvarchar(100) = '',
@GENDERCODE tinyint = 0,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@RELATIONSHIPTYPECODEID uniqueidentifier = null,
@RECIPROCALTYPECODEID uniqueidentifier = null,
@STARTDATE datetime = null,
@COPYCONTACTINFO bit = 0,
@SOURCECONSTITUENTFORCONTACTINFO uniqueidentifier = null, -- The constituent to copy primary contact info from
@RELATEDCONSTITUENTID uniqueidentifier = null, -- The constituent to setup the relationship with. If it's null, use the primary member of the household.
@ISSPOUSE bit = 0,
@RECOGNIZEMEMBERFORHOUSEHOLD bit = 0,
@CURRENTAPPUSERID uniqueidentifier = null,
@DATAFORMTEMPLATEID uniqueidentifier = null,
@SKIP_ADDING_SECURITYGROUPS bit = 0,
@SKIP_ADDING_SITES bit = 0,
@TITLE2CODEID uniqueidentifier = null,
@SUFFIX2CODEID uniqueidentifier = null,
@INFOSOURCECODEID uniqueidentifier = null,
@GENDERCODEID uniqueidentifier = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
-- Retrieve the group's primary contact
declare @PRIMARYMEMBERID uniqueidentifier;
select
@PRIMARYMEMBERID = MEMBERID
from dbo.GROUPMEMBER
where GROUPID = @GROUPID and ISPRIMARY = 1;
if @RELATEDCONSTITUENTID is null
set @RELATEDCONSTITUENTID = @PRIMARYMEMBERID;
-- Check if a new or existing constituent is being used
if @MEMBERID is null
begin
-- Create the constituent record for the new constituent
set @MEMBERID = newid()
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[ISGROUP],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[TITLE2CODEID],
[SUFFIX2CODEID],
[GENDERCODEID]
)
values
(
@MEMBERID,
0,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@TITLE2CODEID,
@SUFFIX2CODEID,
@GENDERCODEID
);
if @INFOSOURCECODEID is not null
insert into dbo.CONSTITUENTORIGINATION
(
ID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@MEMBERID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0 and @CURRENTAPPUSERID is not null and @DATAFORMTEMPLATEID is not null
begin
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID=@DATAFORMTEMPLATEID,
@CONSTITUENTID =@MEMBERID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
if coalesce(@SKIP_ADDING_SITES,0) = 0 and @CURRENTAPPUSERID is not null and @DATAFORMTEMPLATEID is not null
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID=@DATAFORMTEMPLATEID,
@CONSTITUENTID =@MEMBERID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
insert into dbo.[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
select
@MEMBERID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
from dbo.NAMEFORMATDEFAULT as NFD
where NFD.APPLYTOCODE = 0
end
-- Don't move this line below the relationship... in the case that they're trying to
-- add the primary member to the group, we would fail at either the group member add (member already in group)
-- or at the relationship (can't add a relationship to itself). The "already a member"
-- exception is prettier and makes it more consistent with what happens when they try
-- to add a non primary member to the group.
exec dbo.USP_GROUPMEMBERADD @ID output, @CHANGEAGENTID, @GROUPID, @MEMBERID, @RECOGNIZEMEMBERFORHOUSEHOLD;
-- Create a relationship between the added member and the primary contact if the primary contact exists
-- If at least one of the relationship fields is set, assume the user wants to create the relationship
if @PRIMARYMEMBERID is not null and (@RELATIONSHIPTYPECODEID is not null or @RECIPROCALTYPECODEID is not null or @STARTDATE is not null)
begin
-- Verify that a relationship doesn't already exist between the two constituents
if not exists(select 1 from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @PRIMARYMEMBERID and RECIPROCALCONSTITUENTID = @MEMBERID)
begin
if @RELATIONSHIPID is null
set @RELATIONSHIPID = newid();
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
STARTDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@RELATEDCONSTITUENTID,
@MEMBERID,
@RELATIONSHIPTYPECODEID,
@RECIPROCALTYPECODEID,
@ISSPOUSE,
@STARTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
-- Copy household's contact info to the new member if chosen
if @COPYCONTACTINFO = 1
begin
exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @SOURCECONSTITUENTFORCONTACTINFO, @MEMBERID;
end
return 0;