USP_DATAFORMTEMPLATE_ADD_HOUSEHOLD
The save procedure used by the add dataform template "Household Add 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. |
| @NAME | nvarchar(100) | IN | Custom name |
| @DESCRIPTION | nvarchar(300) | IN | Description |
| @GIVESANONYMOUSLY | bit | IN | Household gives anonymously |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP/Postal code |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @EMAIL_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @EMAIL_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @PRIMARYCONTACTID | uniqueidentifier | IN | Full name |
| @PRIMARYCONTACT_KEYNAME | nvarchar(100) | IN | Last name |
| @PRIMARYCONTACT_FIRSTNAME | nvarchar(50) | IN | First name |
| @PRIMARYCONTACT_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @PRIMARYCONTACT_TITLECODEID | uniqueidentifier | IN | Title |
| @PRIMARYCONTACT_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT | bit | IN | Copy household contact information |
| @SECONDMEMBERID | uniqueidentifier | IN | Full name |
| @SECONDMEMBER_KEYNAME | nvarchar(100) | IN | Last name |
| @SECONDMEMBER_FIRSTNAME | nvarchar(50) | IN | First name |
| @SECONDMEMBER_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @SECONDMEMBER_TITLECODEID | uniqueidentifier | IN | Title |
| @SECONDMEMBER_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @SECONDMEMBER_COPYHOUSEHOLDCONTACT | bit | IN | Copy household contact information |
| @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @SECONDMEMBER_RELATIONSHIP_STARTDATE | datetime | IN | Start date |
| @SECONDMEMBER_RELATIONSHIP_ISSPOUSE | bit | IN | This is the spouse relationship |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Website |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @DONOTMAIL | bit | IN | Do not send mail to this address |
| @DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @SKIP_ADDING_SECURITYGROUPS | bit | IN | Skip adding security groups |
| @OMITFROMVALIDATION | bit | IN | Omit this address from validation |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @COUNTYCODEID | uniqueidentifier | IN | |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @CERTIFICATIONDATA | int | IN | |
| @SKIP_ADDING_SITES | bit | IN | Skip adding sites |
| @DONOTEMAIL | bit | IN | Do not send email to this address |
| @DONOTCALL | bit | IN | Do not call this phone number |
| @ADDRESS_ISCONFIDENTIAL | bit | IN | Confidential |
| @INFOSOURCECODEID | uniqueidentifier | IN | Information source |
| @PRIMARYCONTACT_TITLE2CODEID | uniqueidentifier | IN | Title 2 |
| @PRIMARYCONTACT_SUFFIX2CODEID | uniqueidentifier | IN | Suffix 2 |
| @SECONDMEMBER_TITLE2CODEID | uniqueidentifier | IN | Title 2 |
| @SECONDMEMBER_SUFFIX2CODEID | uniqueidentifier | IN | Suffix 2 |
| @DONOTCALLREASONCODEID | uniqueidentifier | IN | Reason |
| @PHONE_ISCONFIDENTIAL | bit | IN | Confidential |
| @PRIMARYCONTACT_GENDERCODE | tinyint | IN | Gender |
| @SECONDMEMBER_GENDERCODE | tinyint | IN | Gender |
| @DONOTEMAILREASONCODEID | uniqueidentifier | IN | |
| @PRIMARYCONTACT_GENDERCODEID | uniqueidentifier | IN | |
| @SECONDMEMBER_GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_HOUSEHOLD
(
@ID uniqueidentifier output,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100) = null,
@DESCRIPTION nvarchar(300) = '',
@GIVESANONYMOUSLY bit = 0,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_POSTCODE nvarchar(12) = '',
@PHONE_PHONETYPECODEID uniqueidentifier = null,
@PHONE_NUMBER nvarchar(100) = '',
@EMAIL_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAIL_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@PRIMARYCONTACTID uniqueidentifier = null,
@PRIMARYCONTACT_KEYNAME nvarchar(100) = '',
@PRIMARYCONTACT_FIRSTNAME nvarchar(50) = '',
@PRIMARYCONTACT_MIDDLENAME nvarchar(50) = '',
@PRIMARYCONTACT_TITLECODEID uniqueidentifier = null,
@PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier = null,
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit = 0,
@SECONDMEMBERID uniqueidentifier = null,
@SECONDMEMBER_KEYNAME nvarchar(100) = '',
@SECONDMEMBER_FIRSTNAME nvarchar(50) = '',
@SECONDMEMBER_MIDDLENAME nvarchar(50) = '',
@SECONDMEMBER_TITLECODEID uniqueidentifier = null,
@SECONDMEMBER_SUFFIXCODEID uniqueidentifier = null,
@SECONDMEMBER_COPYHOUSEHOLDCONTACT bit = 0,
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier = null,
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@SECONDMEMBER_RELATIONSHIP_STARTDATE datetime = null,
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit = 0,
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@CHANGEAGENTID uniqueidentifier = null,
@DONOTMAIL bit = 0,
@DONOTMAILREASONCODEID uniqueidentifier = null,
@SKIP_ADDING_SECURITYGROUPS bit=0,
@OMITFROMVALIDATION bit = 0,
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(200) = '',
@CERTIFICATIONDATA integer = 0,
@SKIP_ADDING_SITES bit=0,
@DONOTEMAIL bit = 0,
@DONOTCALL bit = 0,
@ADDRESS_ISCONFIDENTIAL bit = 0,
@INFOSOURCECODEID uniqueidentifier = null,
@PRIMARYCONTACT_TITLE2CODEID uniqueidentifier = null,
@PRIMARYCONTACT_SUFFIX2CODEID uniqueidentifier = null,
@SECONDMEMBER_TITLE2CODEID uniqueidentifier = null,
@SECONDMEMBER_SUFFIX2CODEID uniqueidentifier = null,
@DONOTCALLREASONCODEID uniqueidentifier = null,
@PHONE_ISCONFIDENTIAL bit = 0,
@PRIMARYCONTACT_GENDERCODE tinyint = 0,
@SECONDMEMBER_GENDERCODE tinyint = 0,
@DONOTEMAILREASONCODEID uniqueidentifier = null,
@PRIMARYCONTACT_GENDERCODEID uniqueidentifier = null,
@SECONDMEMBER_GENDERCODEID uniqueidentifier = null
) as
set nocount on;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
declare @APPUSER_IN_NONSITEROLE bit;
declare @APPUSER_IN_NOSITEROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
if @DONOTCALL = 0
set @DONOTCALLREASONCODEID = null
if @DONOTEMAIL = 0
set @DONOTEMAILREASONCODEID = null;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @NAMEFORMATFUNCTIONID uniqueidentifier;
begin try
-- if the user did not specify a custom name, then they must have specified a primary user.
-- we will recalculate the name of the household at the end if we are calculating it.
if @NAME is null or @NAME = ''
begin
-- We will regenerate the name later, but we need a placeholder.
-- We set the name to null to cause a DB exception when its inserted to let the user know they must pick a custom name if they make a household without members.
if @PRIMARYCONTACTID is not null or len(@PRIMARYCONTACT_KEYNAME) > 0
set @NAME = 'Household';
else
set @NAME = null;
select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;
end
else
set @NAMEFORMATFUNCTIONID = null;
-- create the constituent record for the group
insert into dbo.CONSTITUENT
(
ID,
ISGROUP,
KEYNAME,
GIVESANONYMOUSLY,
WEBADDRESS,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
1,
@NAME,
@GIVESANONYMOUSLY,
@WEBADDRESS,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
-- create the group record
insert into dbo.GROUPDATA
(
ID,
GROUPTYPECODE,
STARTDATE,
DESCRIPTION,
NAMEFORMATFUNCTIONID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
0,
null,
@DESCRIPTION,
@NAMEFORMATFUNCTIONID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
if
(
(@ADDRESS_STATEID is not null)
or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '')
or (coalesce(@ADDRESS_CITY,'') <> '')
or (coalesce(@ADDRESS_POSTCODE,'') <> '')
or (@ADDRESS_ADDRESSTYPECODEID is not null)
or (@ADDRESS_COUNTRYID is not null)
or (@DONOTMAIL = 1)
or (@DONOTMAILREASONCODEID is not null)
)
begin
-- create the group address
declare @ADDRESSID uniqueidentifier;
set @ADDRESSID = newID();
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
DONOTMAIL,
DONOTMAILREASONCODEID,
ISCONFIDENTIAL
)
values
(
@ADDRESSID,
@ID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@CART,
@DPC,
@LOT,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
@DONOTMAIL,
@DONOTMAILREASONCODEID,
@ADDRESS_ISCONFIDENTIAL
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[OMITFROMVALIDATION],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@CURRENTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@OMITFROMVALIDATION,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
declare @PHONE_COUNTRYID uniqueidentifier = @ADDRESS_COUNTRYID;
if @ADDRESS_COUNTRYID is null
exec @PHONE_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
-- create the phone record for the group
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER, '') <> '')
begin
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
COUNTRYID,
ISPRIMARY,
DONOTCALL,
INFOSOURCECODEID,
DONOTCALLREASONCODEID,
ISCONFIDENTIAL,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
@PHONE_COUNTRYID,
1,
@DONOTCALL,
@INFOSOURCECODEID,
@DONOTCALLREASONCODEID,
@PHONE_ISCONFIDENTIAL,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
if @PRIMARYCONTACTID is not null
exec dbo.USP_GROUP_COPYPHONE @ID, @PRIMARYCONTACTID, @PHONE_NUMBER, @CHANGEAGENTID;
end
-- create email address record for the group
if (@EMAIL_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAIL_EMAILADDRESS,'') <> '')
begin
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
INFOSOURCECODEID,
DONOTEMAILREASONCODEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@ID,
@EMAIL_EMAILADDRESSTYPECODEID,
@EMAIL_EMAILADDRESS,
1,
@DONOTEMAIL,
@INFOSOURCECODEID,
@DONOTEMAILREASONCODEID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
);
end
-- create the primary group contact
if (@PRIMARYCONTACTID is null) and ( coalesce(@PRIMARYCONTACT_KEYNAME,'') <> '')
begin
set @PRIMARYCONTACTID = newid();
-- create the primary contact constituent record
insert into dbo.CONSTITUENT
(
ID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
TITLECODEID,
TITLE2CODEID,
SUFFIXCODEID,
SUFFIX2CODEID,
GENDERCODE,
DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID,
GENDERCODEID
)
values
(
@PRIMARYCONTACTID,
@PRIMARYCONTACT_KEYNAME,
@PRIMARYCONTACT_FIRSTNAME,
@PRIMARYCONTACT_MIDDLENAME,
@PRIMARYCONTACT_TITLECODEID,
@PRIMARYCONTACT_TITLE2CODEID,
@PRIMARYCONTACT_SUFFIXCODEID,
@PRIMARYCONTACT_SUFFIX2CODEID,
@PRIMARYCONTACT_GENDERCODE,
@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID,
@PRIMARYCONTACT_GENDERCODEID
);
/*Start security groups*/
if COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@PRIMARYCONTACTID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end security groups*/
/*Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@PRIMARYCONTACTID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end sites*/
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION]
,[SEQUENCE])
SELECT
@PRIMARYCONTACTID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
,(select count(ID)
from dbo.NAMEFORMATDEFAULT as SUBNFD
where NFD.ID > SUBNFD.ID
and NFD.APPLYTOCODE = SUBNFD.APPLYTOCODE)
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE NFD.APPLYTOCODE = 0
/*End name format defaults*/
if (@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT = 1)
begin
if
(
(@ADDRESS_STATEID is not null)
or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '')
or (coalesce(@ADDRESS_CITY,'') <> '')
or (coalesce(@ADDRESS_POSTCODE,'') <> '')
or (@ADDRESS_ADDRESSTYPECODEID is not null)
or (@ADDRESS_COUNTRYID is not null)
or (@DONOTMAIL = 1)
or (@DONOTMAILREASONCODEID is not null)
)
begin
declare @PRIMARYCONTACTADDRESSID uniqueidentifier;
set @PRIMARYCONTACTADDRESSID = newid();
-- copy the address for the primary contact
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
DONOTMAIL,
DONOTMAILREASONCODEID
)
values
(
@PRIMARYCONTACTADDRESSID,
@PRIMARYCONTACTID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
@DONOTMAIL,
@DONOTMAILREASONCODEID
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[OMITFROMVALIDATION],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYCONTACTADDRESSID,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@CURRENTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@OMITFROMVALIDATION,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
-- copy the phone number for the primary contact
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER, '') <> '')
begin
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
COUNTRYID,
ISPRIMARY,
DONOTCALL,
INFOSOURCECODEID,
DONOTCALLREASONCODEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@PRIMARYCONTACTID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
@PHONE_COUNTRYID,
1,
@DONOTCALL,
@INFOSOURCECODEID,
@DONOTCALLREASONCODEID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
);
end
-- copy the email address for the primary contact
if (@EMAIL_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAIL_EMAILADDRESS,'') <> '')
begin
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
INFOSOURCECODEID,
DONOTEMAILREASONCODEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@PRIMARYCONTACTID,
@EMAIL_EMAILADDRESSTYPECODEID,
@EMAIL_EMAILADDRESS,
1,
@DONOTEMAIL,
@INFOSOURCECODEID,
@DONOTEMAILREASONCODEID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
);
end
end -- end copy of contact information for primary group contact
end -- end copy of create primary group contact
if @PRIMARYCONTACTID is not null
begin
if (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @PRIMARYCONTACTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, @PRIMARYCONTACTID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
begin
-- add the primary contact to the group
declare @PRIMARYCONTACTGROUPMEMBERID uniqueidentifier;
exec dbo.USP_GROUPMEMBERADD @PRIMARYCONTACTGROUPMEMBERID output, @CHANGEAGENTID, @ID, @PRIMARYCONTACTID;
-- set the primary contact bit
update
dbo.GROUPMEMBER
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @PRIMARYCONTACTGROUPMEMBERID;
end
else
begin
raiserror ('ERR_PRIMARYCONTACT_NOACCESS',13,1);
return 0;
end
end
if (@SECONDMEMBERID is null) and ( coalesce(@SECONDMEMBER_KEYNAME,'') <> '')
begin
set @SECONDMEMBERID = newid();
insert into dbo.CONSTITUENT
(
ID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
TITLECODEID,
TITLE2CODEID,
SUFFIXCODEID,
SUFFIX2CODEID,
GENDERCODE,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
GENDERCODEID
)
values
(
@SECONDMEMBERID,
@SECONDMEMBER_KEYNAME,
@SECONDMEMBER_FIRSTNAME,
@SECONDMEMBER_MIDDLENAME,
@SECONDMEMBER_TITLECODEID,
@SECONDMEMBER_TITLE2CODEID,
@SECONDMEMBER_SUFFIXCODEID,
@SECONDMEMBER_SUFFIX2CODEID,
@SECONDMEMBER_GENDERCODE,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
@SECONDMEMBER_GENDERCODEID
);
/*Start security groups*/
if COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@SECONDMEMBERID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end security groups*/
/*Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@SECONDMEMBERID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end sites*/
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION]
,[SEQUENCE])
SELECT
@SECONDMEMBERID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
,(select count(ID)
from dbo.NAMEFORMATDEFAULT as SUBNFD
where NFD.ID > SUBNFD.ID
and NFD.APPLYTOCODE = SUBNFD.APPLYTOCODE)
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE NFD.APPLYTOCODE = 0
/*End name format defaults*/
if (@SECONDMEMBER_COPYHOUSEHOLDCONTACT = 1)
begin
if
(
(@ADDRESS_STATEID is not null)
or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '')
or (coalesce(@ADDRESS_CITY,'') <> '')
or (coalesce(@ADDRESS_POSTCODE,'') <> '')
or (@ADDRESS_ADDRESSTYPECODEID is not null)
or (@ADDRESS_COUNTRYID is not null)
or (@DONOTMAIL = 1)
or (@DONOTMAILREASONCODEID is not null)
)
begin
declare @SECONDMEMBERCONTACTADDRESSID uniqueidentifier;
set @SECONDMEMBERCONTACTADDRESSID = newid();
-- copy the address
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
DONOTMAIL,
DONOTMAILREASONCODEID,
ISCONFIDENTIAL
)
values
(
@SECONDMEMBERCONTACTADDRESSID,
@SECONDMEMBERID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
@DONOTMAIL,
@DONOTMAILREASONCODEID,
@ADDRESS_ISCONFIDENTIAL
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[OMITFROMVALIDATION],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SECONDMEMBERCONTACTADDRESSID,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@CURRENTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@OMITFROMVALIDATION,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
-- copy the phone number
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER, '') <> '')
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
COUNTRYID,
ISPRIMARY,
DONOTCALL,
INFOSOURCECODEID,
DONOTCALLREASONCODEID,
ISCONFIDENTIAL,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@SECONDMEMBERID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
@PHONE_COUNTRYID,
1,
@DONOTCALL,
@INFOSOURCECODEID,
@DONOTCALLREASONCODEID,
@PHONE_ISCONFIDENTIAL,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
);
-- copy the email address for the primary contact
if (@EMAIL_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAIL_EMAILADDRESS,'') <> '')
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
INFOSOURCECODEID,
DONOTEMAILREASONCODEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@SECONDMEMBERID,
@EMAIL_EMAILADDRESSTYPECODEID,
@EMAIL_EMAILADDRESS,
1,
@DONOTEMAIL,
@INFOSOURCECODEID,
@DONOTEMAILREASONCODEID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
);
end -- end copy contact information for second member
end -- end create second member information
if @SECONDMEMBERID is not null
begin
if (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @SECONDMEMBERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, @SECONDMEMBERID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
begin
declare @MEMBER_GROUPMEMBERID uniqueidentifier;
exec dbo.USP_GROUPMEMBERADD @MEMBER_GROUPMEMBERID output, @CHANGEAGENTID, @ID, @SECONDMEMBERID;
end
else
begin
raiserror ('ERR_SECONDMEMBER_NOACCESS',13,1);
return 0;
end
end
if ((@SECONDMEMBERID is not null) and (@PRIMARYCONTACTID is not null) and
(@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID is not null) and
(@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID is not null))
begin
-- Verify a relationship between the two constituents doesn't already exist
if not exists(select 1 from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @PRIMARYCONTACTID and RECIPROCALCONSTITUENTID = @SECONDMEMBERID)
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
STARTDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
newid(),
@PRIMARYCONTACTID,
@SECONDMEMBERID,
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE,
@SECONDMEMBER_RELATIONSHIP_STARTDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @PRIMARYCONTACTID, @SECONDMEMBERID, @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID, @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit
declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2)
declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit
declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2)
set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @PRIMARYCONTACTID, @SECONDMEMBERID,
@SECONDMEMBER_RELATIONSHIP_STARTDATE, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
if @SECONDMEMBER_RELATIONSHIP_ISSPOUSE = 1
begin
exec dbo.USP_MARRIAGEOPTIONS_APPLYRULES
@PRIMARYCONTACTID,
@SECONDMEMBERID,
@CHANGEAGENTID,
@CURRENTDATE,
1;
end
end
/*Start security groups*/
if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@ID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end security groups*/
/*Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@ID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end sites*/
-- Update the household name
if @NAMEFORMATFUNCTIONID is not null
begin
declare @HOUSEHOLDNAME nvarchar(100);
select
@HOUSEHOLDNAME = left(dbo.UFN_NAMEFORMAT_FROMID(GROUPDATA.NAMEFORMATFUNCTIONID, GROUPMEMBER.MEMBERID), 100)
from dbo.GROUPDATA
inner join dbo.GROUPMEMBER on GROUPDATA.ID = GROUPMEMBER.GROUPID
where GROUPDATA.ID = @ID and GROUPDATA.NAMEFORMATFUNCTIONID is not null and GROUPMEMBER.ISPRIMARY <> 0;
if @HOUSEHOLDNAME is null or @HOUSEHOLDNAME = ''
begin
declare @FALLBACKHOUSEHOLDNAMEFORMATID uniqueidentifier
select @FALLBACKHOUSEHOLDNAMEFORMATID = ID
from dbo.NAMEFORMATFUNCTION
where FORMATSQLFUNCTION = 'UFN_NAMEFORMAT_32';
select
@HOUSEHOLDNAME = left(dbo.UFN_NAMEFORMAT_FROMID(@FALLBACKHOUSEHOLDNAMEFORMATID, GROUPMEMBER.MEMBERID), 100)
from dbo.GROUPMEMBER
where GROUPMEMBER.GROUPID = @ID and GROUPMEMBER.ISPRIMARY <> 0;
end
update dbo.CONSTITUENT
set
KEYNAME = @HOUSEHOLDNAME,
CONSTITUENT.CHANGEDBYID = @CHANGEAGENTID,
CONSTITUENT.DATECHANGED = @CURRENTDATE
where CONSTITUENT.ID = @ID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;