USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHCOMMIT
The save procedure used by the add dataform template "Constituent Batch Row Commit Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ISORGANIZATION | tinyint | IN | Constituent type |
| @KEYNAME | nvarchar(100) | IN | Last/org/group/household name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @MAIDENNAME | nvarchar(100) | IN | Maiden name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @GENDERCODE | tinyint | IN | Gender |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Web site address |
| @INDUSTRYCODEID | uniqueidentifier | IN | Industry |
| @PARENTCORPID | uniqueidentifier | IN | Parent org |
| @NUMEMPLOYEES | int | IN | No. of employees |
| @NUMSUBSIDIARIES | int | IN | No. of subsidiary orgs |
| @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 |
| @ADDRESS_DONOTMAIL | bit | IN | Do not send mail to this address |
| @PHONES | xml | IN | Phones |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @CUSTOMIDENTIFIER | nvarchar(100) | IN | Lookup ID |
| @VALIDATEONLY | bit | IN | Validate only |
| @BATCHNUMBER | nvarchar(100) | IN | |
| @ADDRESS_CART | nvarchar(10) | IN | CART |
| @ADDRESS_DPC | nvarchar(8) | IN | DPC |
| @ADDRESS_LOT | nvarchar(5) | IN | LOT |
| @ADDRESS_STARTDATE | UDT_MONTHDAY | IN | Address start date |
| @ADDRESS_ENDDATE | UDT_MONTHDAY | IN | Address end date |
| @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID | uniqueidentifier | IN | Addressee type |
| @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID | uniqueidentifier | IN | Addressee name format |
| @PRIMARYSALUTATION_NAMEFORMATTYPECODEID | uniqueidentifier | IN | Salutation type |
| @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID | uniqueidentifier | IN | Salutation name format |
| @ISDECEASED | bit | IN | Is deceased |
| @DECEASEDDATE | UDT_FUZZYDATE | IN | Deceased date |
| @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | Educational institution |
| @EDUCATIONALHISTORY_ISPRIMARYRECORD | bit | IN | Educational is primary |
| @EDUCATIONALHISTORY_EDUCATIONALHISTORYTYPECODEID | uniqueidentifier | IN | Educational type |
| @EDUCATIONALHISTORY_EDUCATIONALHISTORYSTATUSCODEID | uniqueidentifier | IN | Educational detail |
| @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE | tinyint | IN | Educational status |
| @EDUCATIONALHISTORY_CLASSOF | UDT_YEAR | IN | Class year |
| @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID | uniqueidentifier | IN | Educational degree |
| @EDUCATIONALHISTORY_GPA | decimal(3, 2) | IN | GPA |
| @EDUCATIONALHISTORY_STARTDATE | UDT_FUZZYDATE | IN | Educational start date |
| @EDUCATIONALHISTORY_ENDDATE | UDT_FUZZYDATE | IN | Educational end date |
| @EDUCATIONALHISTORY_KNOWNNAME | nvarchar(50) | IN | Known name |
| @EDUCATIONALHISTORY_FRATERNITY | nvarchar(50) | IN | Fraternity |
| @SPOUSEID | uniqueidentifier | IN | Spouse |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Spouse relationship type |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Spouse reciprocal type |
| @SPOUSE_KEYNAME | nvarchar(100) | IN | Spouse last name |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | Spouse first name |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | Spouse middle name |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | Spouse maiden name |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | Spouse nickname |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | Spouse title |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | Spouse suffix |
| @SPOUSE_GENDERCODE | tinyint | IN | Spouse gender |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | Spouse birth date |
| @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Spouse email address type |
| @SPOUSE_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Spouse email address |
| @PRIMARYBUSINESSID | uniqueidentifier | IN | Business |
| @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Business relationship type |
| @PRIMARYBUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | Business reciprocal type |
| @PRIMARYBUSINESS_KEYNAME | nvarchar(100) | IN | Business name |
| @PRIMARYBUSINESS_PHONE_PHONETYPECODEID | uniqueidentifier | IN | Business phone type |
| @PRIMARYBUSINESS_PHONE_NUMBER | nvarchar(100) | IN | Business phone number |
| @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Business email type |
| @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Business email address |
| @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Business address type |
| @PRIMARYBUSINESS_ADDRESS_COUNTRYID | uniqueidentifier | IN | Business country |
| @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Business address |
| @PRIMARYBUSINESS_ADDRESS_CITY | nvarchar(50) | IN | Business city |
| @PRIMARYBUSINESS_ADDRESS_STATEID | uniqueidentifier | IN | Business state |
| @PRIMARYBUSINESS_ADDRESS_POSTCODE | nvarchar(12) | IN | Business ZIP |
| @PRIMARYBUSINESS_ADDRESS_CART | nvarchar(10) | IN | Business CART |
| @PRIMARYBUSINESS_ADDRESS_DPC | nvarchar(8) | IN | Business DPC |
| @PRIMARYBUSINESS_ADDRESS_LOT | nvarchar(5) | IN | Business LOT |
| @PRIMARYBUSINESS_ADDRESS_DONOTMAIL | bit | IN | Business do not mail |
| @PRIMARYBUSINESS_INDUSTRYCODEID | uniqueidentifier | IN | Business industry |
| @PRIMARYBUSINESS_NUMEMPLOYEES | int | IN | Business no. of employees |
| @PRIMARYBUSINESS_NUMSUBSIDIARIES | int | IN | Business no. of subsidiary orgs |
| @PRIMARYBUSINESS_WEBADDRESS | UDT_WEBADDRESS | IN | Business web site address |
| @PRIMARYBUSINESS_PARENTCORPID | uniqueidentifier | IN | Business parent org |
| @CONSTITUENCY | xml | IN | User defined constituencies |
| @INTEREST | xml | IN | Interests |
| @EDUCATIONALMAJOR | xml | IN | Major |
| @EDUCATIONALMINOR | xml | IN | Minor |
| @SPOUSE_COPY_ADDRESS | bit | IN | Spouse copy address |
| @SPOUSE_COPY_PHONENUMBER | bit | IN | Spouse copy phone number |
| @SPOUSE_COPY_EMAILADDRESS | bit | IN | Spouse copy email address |
| @CONSTIT_SECURITY_ATTRIBUTEID | uniqueidentifier | IN | Constituent Security Attribute |
| @ADDRESS_COUNTYCODEID | uniqueidentifier | IN | County |
| @ADDRESS_OMITFROMVALIDATION | bit | IN | Omit from validation |
| @ADDRESS_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | Congressional district |
| @ADDRESS_STATEHOUSEDISTRICTCODEID | uniqueidentifier | IN | State house district |
| @ADDRESS_STATESENATEDISTRICTCODEID | uniqueidentifier | IN | State senate district |
| @ADDRESS_LOCALPRECINCTCODEID | uniqueidentifier | IN | Local precinct |
| @ADDRESS_INFOSOURCECODEID | uniqueidentifier | IN | Info source |
| @ADDRESS_REGIONCODEID | uniqueidentifier | IN | Region |
| @ADDRESS_LASTVALIDATIONATTEMPTDATE | datetime | IN | Last validation attempt date |
| @ADDRESS_VALIDATIONMESSAGE | nvarchar(200) | IN | Validation message |
| @ADDRESS_CERTIFICATIONDATA | int | IN | Certification data |
| @GROUPTYPEID | uniqueidentifier | IN | Group type |
| @GROUPSTARTDATE | datetime | IN | Group start date |
| @GROUPDESCRIPTION | nvarchar(300) | IN | Group description |
| @GROUPMEMBERS | xml | IN | Group members |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Do not mail reason |
| @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Business do not mail reason |
| @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID | uniqueidentifier | IN | Academic catalog program |
| @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID | uniqueidentifier | IN | Educational program |
| @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID | uniqueidentifier | IN | Academic catalog degree |
| @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID | uniqueidentifier | IN | Awarded |
| @EDUCATIONALHISTORY_PREFERREDCLASSYEAR | UDT_YEAR | IN | Preferred class year |
| @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID | uniqueidentifier | IN | Source |
| @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE | UDT_FUZZYDATE | IN | Source date |
| @EDUCATIONALHISTORY_COMMENT | nvarchar(500) | IN | Comment |
| @AFFILIATEDADDITIONALINFORMATION | xml | IN | Affiliated additional information |
| @UNAFFILIATEDADDITIONALINFORMATION | xml | IN | Unaffiliated additional information |
| @EDUCATIONALINVOLVEMENT | xml | IN | Involvement |
| @ALTERNATELOOKUPIDS | xml | IN | Alternate lookup IDs |
| @EDUCATIONALHISTORY_DATEGRADUATED | UDT_FUZZYDATE | IN | Date graduated |
| @EDUCATIONALHISTORY_DATELEFT | UDT_FUZZYDATE | IN | Date left |
| @EDUCATIONALHISTORY_LEVELCODEID | uniqueidentifier | IN | Level |
| @EDUCATIONALHISTORY_REASONCODEID | uniqueidentifier | IN | Reason |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHCOMMIT
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier,
@ISORGANIZATION tinyint,
@KEYNAME nvarchar(100),
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@MAIDENNAME nvarchar(100) = '',
@NICKNAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@GENDERCODE tinyint = 0,
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@INDUSTRYCODEID uniqueidentifier = null,
@PARENTCORPID uniqueidentifier = null,
@NUMEMPLOYEES int = 0,
@NUMSUBSIDIARIES int = 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) = '',
@ADDRESS_DONOTMAIL bit = 0,
@PHONES xml = null,
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@CUSTOMIDENTIFIER nvarchar(100) = '',
@VALIDATEONLY bit = 0,
@BATCHNUMBER nvarchar(100),
@ADDRESS_CART nvarchar(10) = '',
@ADDRESS_DPC nvarchar(8) = '',
@ADDRESS_LOT nvarchar(5) = '',
@ADDRESS_STARTDATE dbo.UDT_MONTHDAY = '0000',
@ADDRESS_ENDDATE dbo.UDT_MONTHDAY = '0000',
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID uniqueidentifier = null,
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID uniqueidentifier = null,
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID uniqueidentifier = null,
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID uniqueidentifier = null,
@ISDECEASED bit = 0,
@DECEASEDDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALHISTORY_ISPRIMARYRECORD bit = 0,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYTYPECODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYSTATUSCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE tinyint = 0,
@EDUCATIONALHISTORY_CLASSOF dbo.UDT_YEAR = 0,
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_GPA decimal(3,2) = 0,
@EDUCATIONALHISTORY_STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_ENDDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_KNOWNNAME nvarchar(50) = '',
@EDUCATIONALHISTORY_FRATERNITY nvarchar(50) = '',
@SPOUSEID uniqueidentifier = null,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
@SPOUSE_KEYNAME nvarchar(100) = '',
@SPOUSE_FIRSTNAME nvarchar(50) = '',
@SPOUSE_MIDDLENAME nvarchar(50) = '',
@SPOUSE_MAIDENNAME nvarchar(100) = '',
@SPOUSE_NICKNAME nvarchar(50) = '',
@SPOUSE_TITLECODEID uniqueidentifier = null,
@SPOUSE_SUFFIXCODEID uniqueidentifier = null,
@SPOUSE_GENDERCODE tinyint = 0,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@PRIMARYBUSINESSID uniqueidentifier = null,
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_KEYNAME nvarchar(100) = '',
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_PHONE_NUMBER nvarchar(100) = '',
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_COUNTRYID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@PRIMARYBUSINESS_ADDRESS_CITY nvarchar(50) = '',
@PRIMARYBUSINESS_ADDRESS_STATEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_POSTCODE nvarchar(12) = '',
@PRIMARYBUSINESS_ADDRESS_CART nvarchar(10) = '',
@PRIMARYBUSINESS_ADDRESS_DPC nvarchar(8) = '',
@PRIMARYBUSINESS_ADDRESS_LOT nvarchar(5) = '',
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL bit = 0,
@PRIMARYBUSINESS_INDUSTRYCODEID uniqueidentifier = null,
@PRIMARYBUSINESS_NUMEMPLOYEES int = 0,
@PRIMARYBUSINESS_NUMSUBSIDIARIES int = 0,
@PRIMARYBUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS = '',
@PRIMARYBUSINESS_PARENTCORPID uniqueidentifier = null,
@CONSTITUENCY xml = null,
@INTEREST xml = null,
@EDUCATIONALMAJOR xml = null,
@EDUCATIONALMINOR xml = null,
@SPOUSE_COPY_ADDRESS bit = 1,
@SPOUSE_COPY_PHONENUMBER bit = 1,
@SPOUSE_COPY_EMAILADDRESS bit = 1,
@CONSTIT_SECURITY_ATTRIBUTEID uniqueidentifier = null,
@ADDRESS_COUNTYCODEID uniqueidentifier = null,
@ADDRESS_OMITFROMVALIDATION bit = 0,
@ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_STATESENATEDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_LOCALPRECINCTCODEID uniqueidentifier = null,
@ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
@ADDRESS_REGIONCODEID uniqueidentifier = null,
@ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
@ADDRESS_VALIDATIONMESSAGE nvarchar(200) = '',
@ADDRESS_CERTIFICATIONDATA int = 0,
@GROUPTYPEID uniqueidentifier = null,
@GROUPSTARTDATE datetime = null,
@GROUPDESCRIPTION nvarchar(300) = '',
@GROUPMEMBERS xml = null,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR dbo.UDT_YEAR = 0,
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_COMMENT nvarchar(500) = '',
@AFFILIATEDADDITIONALINFORMATION xml = null,
@UNAFFILIATEDADDITIONALINFORMATION xml = null,
@EDUCATIONALINVOLVEMENT xml = null,
@ALTERNATELOOKUPIDS xml = null,
@EDUCATIONALHISTORY_DATEGRADUATED dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_DATELEFT dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_LEVELCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_REASONCODEID uniqueidentifier = null
) as
set nocount on;
declare
@CURRENTDATE datetime,
@ErrorMessage nvarchar(1000),
@InfoMsg nvarchar(100),
@ErrorSeverity int,
@ErrorState int;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
begin try
declare @ADDRESSINCLUDED bit;
set @ADDRESSINCLUDED = 0;
if (@ADDRESS_ADDRESSTYPECODEID is not null) or (@ADDRESS_COUNTRYID is not null) or
(@ADDRESS_STATEID is not null) or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or
(coalesce(@ADDRESS_CITY,'') <> '') or (coalesce(@ADDRESS_POSTCODE,'') <> '') or
(coalesce(@ADDRESS_CART,'') <> '') or (coalesce(@ADDRESS_DPC,'') <> '') or
(coalesce(@ADDRESS_LOT,'') <> '') or (coalesce(@ADDRESS_STARTDATE,'0000') <> '0000') or
(coalesce(@ADDRESS_ENDDATE,'0000') <> '0000')
set @ADDRESSINCLUDED = 1;
if @ADDRESSINCLUDED = 1 begin
if @ADDRESS_COUNTRYID is null
exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if dbo.UFN_STATE_GETCOUNTRY(@ADDRESS_STATEID, @ADDRESS_COUNTRYID) <> @ADDRESS_COUNTRYID begin
raiserror('Constituent - invalid state for country.', 13, 1);
return 1;
end
end
declare @PRIMARYBUSINESS_ADDRESSINCLUDED bit;
set @PRIMARYBUSINESS_ADDRESSINCLUDED = 0;
if (@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID is not null) or (@PRIMARYBUSINESS_ADDRESS_COUNTRYID is not null) or
(@PRIMARYBUSINESS_ADDRESS_STATEID is not null) or (coalesce(@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,'') <> '') or
(coalesce(@PRIMARYBUSINESS_ADDRESS_CITY,'') <> '') or (coalesce(@PRIMARYBUSINESS_ADDRESS_POSTCODE,'') <> '') or
(coalesce(@PRIMARYBUSINESS_ADDRESS_CART,'') <> '') or (coalesce(@PRIMARYBUSINESS_ADDRESS_DPC,'') <> '') or
(coalesce(@PRIMARYBUSINESS_ADDRESS_LOT,'') <> '')
set @PRIMARYBUSINESS_ADDRESSINCLUDED = 1;
if @PRIMARYBUSINESS_ADDRESSINCLUDED = 1 begin
if @PRIMARYBUSINESS_ADDRESS_COUNTRYID is null
exec @PRIMARYBUSINESS_ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if dbo.UFN_STATE_GETCOUNTRY(@PRIMARYBUSINESS_ADDRESS_STATEID, @PRIMARYBUSINESS_ADDRESS_COUNTRYID) <> @PRIMARYBUSINESS_ADDRESS_COUNTRYID begin
raiserror('Primary business - invalid state for country.', 13, 1);
return 1;
end
end
if (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is not null) and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is not null) and
(@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is not null) and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is not null) and
(@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID = @PRIMARYSALUTATION_NAMEFORMATTYPECODEID) and
(@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID <> @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID) begin
raiserror('Only one name format can be specified per name format type.',13,1);
return 1;
end
exec dbo.USP_PHONES_VALIDATEPHONES @PHONES;
exec dbo.USP_CONSTITUENCIES_VALIDATECONSTITUENCIES @CONSTITUENCY;
-- If the constituent is a group or household, verify only one member was set as primary
declare @GROUPMEMBERSTABLE table
(
CONSTITUENTID uniqueidentifier,
ISPRIMARY bit
)
if @ISORGANIZATION = 2 or @ISORGANIZATION = 3
begin
insert into @GROUPMEMBERSTABLE
(
CONSTITUENTID,
ISPRIMARY
)
select
CONSTITUENTID,
ISPRIMARY
from dbo.UFN_CONSTITUENTBATCH_GETGROUPMEMBERS_FROMITEMLISTXML(@GROUPMEMBERS)
declare @PRIMARYMEMBERCOUNT int
select @PRIMARYMEMBERCOUNT = count(*)
from @GROUPMEMBERSTABLE
where ISPRIMARY = 1
if @PRIMARYMEMBERCOUNT > 1
begin
raiserror('Only one constituent can be the primary member.', 13, 1)
return 1
end
if exists(select CONSTITUENTID from @GROUPMEMBERSTABLE group by CONSTITUENTID having count(*) > 1)
begin
raiserror('A constituent can only be added as a member once.', 13, 1)
return 1
end
-- If this is a household, verify the members being added aren't already in an existing household
if @ISORGANIZATION = 2
begin
if exists ( select 1 from @GROUPMEMBERSTABLE GROUPMEMBERSTABLE
inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = GROUPMEMBERSTABLE.CONSTITUENTID
inner join dbo.GROUPDATA on GROUPMEMBER.GROUPID = GROUPDATA.ID
where GROUPDATA.GROUPTYPECODE = 0)
begin
raiserror('A member already belongs to a household.', 13, 1)
return 1
end
end
end
if @VALIDATEONLY = 1 begin
if exists
(select 1
from dbo.CONSTITUENT
where @CUSTOMIDENTIFIER <> '' and CONSTITUENT.CUSTOMIDENTIFIER = @CUSTOMIDENTIFIER
) begin
raiserror('Lookup ID must be unique.', 13, 1);
return 1;
end
else if
(select count(BATCHCONSTITUENT.ID)
from dbo.BATCHCONSTITUENT
inner join dbo.BATCH on BATCH.ID = BATCHCONSTITUENT.BATCHID and BATCH.BATCHNUMBER = @BATCHNUMBER
where @CUSTOMIDENTIFIER <> '' and BATCHCONSTITUENT.CUSTOMID = @CUSTOMIDENTIFIER
) > 1 begin
raiserror('The Lookup ID has multiple occurrences in this batch.', 13, 1);
return 1;
end
if (not @EDUCATIONALHISTORY_STARTDATE is null and not @EDUCATIONALHISTORY_DATEGRADUATED is null) and @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 3 and ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@EDUCATIONALHISTORY_STARTDATE) > dbo.UFN_DATE_LATESTFROMFUZZYDATE(@EDUCATIONALHISTORY_DATEGRADUATED))) begin
raiserror('Educational date graduated must not be earlier than educational start date.',13,1);
return 1;
end
if (not @EDUCATIONALHISTORY_STARTDATE is null and not @EDUCATIONALHISTORY_DATELEFT is null) and @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 2 and ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@EDUCATIONALHISTORY_STARTDATE) > dbo.UFN_DATE_LATESTFROMFUZZYDATE(@EDUCATIONALHISTORY_DATELEFT))) begin
raiserror('Educational date left must not be earlier than educational start date.',13,1);
return 1;
end
end
else if @VALIDATEONLY = 0 begin
if @ISORGANIZATION = 0 -- individual
begin
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[BIRTHDATE],
[GENDERCODE],
[WEBADDRESS],
[ISORGANIZATION],
[CUSTOMIDENTIFIER],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@KEYNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@BIRTHDATE,
@GENDERCODE,
@WEBADDRESS,
@ISORGANIZATION,
@CUSTOMIDENTIFIER,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else if @ISORGANIZATION = 1 -- organization
begin
declare @KEYNAMEBODY nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAMEBODY output, @KEYNAMEPREFIX output;
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[KEYNAMEPREFIX],
[WEBADDRESS],
[ISORGANIZATION],
[CUSTOMIDENTIFIER],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@KEYNAMEBODY,
@KEYNAMEPREFIX,
@WEBADDRESS,
@ISORGANIZATION,
@CUSTOMIDENTIFIER,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if (@INDUSTRYCODEID is not null) or (@PARENTCORPID is not null) or
(@NUMEMPLOYEES > 0) or (@NUMSUBSIDIARIES > 0)
begin
insert into dbo.ORGANIZATIONDATA
(
[ID],
[INDUSTRYCODEID],
[PARENTCORPID],
[NUMEMPLOYEES],
[NUMSUBSIDIARIES],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@INDUSTRYCODEID,
@PARENTCORPID,
@NUMEMPLOYEES,
@NUMSUBSIDIARIES,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
else if (@ISORGANIZATION = 2 or @ISORGANIZATION = 3)
begin
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[WEBADDRESS],
[ISORGANIZATION],
[ISGROUP],
[CUSTOMIDENTIFIER],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@KEYNAME,
@WEBADDRESS,
0, -- ISORGANIZATION
1, -- ISGROUP
@CUSTOMIDENTIFIER,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.GROUPDATA
(
ID,
GROUPTYPECODE,
STARTDATE,
GROUPTYPEID,
[DESCRIPTION],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
case
when @ISORGANIZATION = 2 then 0
when @ISORGANIZATION = 3 then 1
else null
end,
@GROUPSTARTDATE,
@GROUPTYPEID,
@GROUPDESCRIPTION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @CONSTIT_SECURITY_ATTRIBUTEID is not null
insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
(
[CONSTIT_SECURITY_ATTRIBUTEID],
[CONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTIT_SECURITY_ATTRIBUTEID,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @ISDECEASED = 1 begin
insert into dbo.[DECEASEDCONSTITUENT]
(
[ID],
[DECEASEDDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@DECEASEDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @ADDRESSINCLUDED = 1 begin
declare @ADDRESSID uniqueidentifier;
set @ADDRESSID = newID();
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[DONOTMAILREASONCODEID],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[STARTDATE],
[ENDDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@ID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_DONOTMAIL,
@ADDRESS_DONOTMAILREASONCODEID,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_STARTDATE,
@ADDRESS_ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[STATEHOUSEDISTRICTCODEID],
[STATESENATEDISTRICTCODEID],
[LOCALPRECINCTCODEID],
[INFOSOURCECODEID],
[REGIONCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[OMITFROMVALIDATION],
[CERTIFICATIONDATA],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_STATEHOUSEDISTRICTCODEID,
@ADDRESS_STATESENATEDISTRICTCODEID,
@ADDRESS_LOCALPRECINCTCODEID,
@ADDRESS_INFOSOURCECODEID,
@ADDRESS_REGIONCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_CERTIFICATIONDATA,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_CONSTITUENT_GETPHONES_ADDFROMXML @ID, @PHONES, @CHANGEAGENTID;
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '') begin
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is not null) and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is not null) begin
begin try
insert into dbo.[NAMEFORMAT]
(
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID,
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID,
1,
case when @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID = @PRIMARYSALUTATION_NAMEFORMATTYPECODEID then 1 else 0 end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50001';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
if (@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is not null) and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is not null) and
((@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is null) or (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID <> @PRIMARYSALUTATION_NAMEFORMATTYPECODEID)) begin
begin try
insert into dbo.[NAMEFORMAT]
(
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID,
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID,
0,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
SELECT
@ID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE ((NFD.APPLYTOCODE = 0 and @ISORGANIZATION = 0))
and (((@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is null)
and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is null))
or ((NFD.PRIMARYADDRESSEE <> 1)
and (NFD.NAMEFORMATTYPECODEID <> @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID)))
and (((@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is null)
and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is null))
or ((NFD.PRIMARYSALUTATION <> 1)
and (NFD.NAMEFORMATTYPECODEID <> @PRIMARYSALUTATION_NAMEFORMATTYPECODEID)))
/*End name format defaults*/
if (@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID is not null) begin
declare @EDUCATIONALHISTORYID uniqueidentifier;
set @EDUCATIONALHISTORYID = newid();
declare @ISAFFILIATED bit;
select
@ISAFFILIATED = ISAFFILIATED
from dbo.EDUCATIONALINSTITUTION
where
ID = @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID;
if @ISAFFILIATED = 1
begin
insert into dbo.[EDUCATIONALHISTORY]
(
[ID],
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[ISPRIMARYRECORD],
[KNOWNNAME],
[EDUCATIONALHISTORYTYPECODEID],
[EDUCATIONALHISTORYSTATUSCODEID],
[CONSTITUENCYSTATUSCODE],
[STARTDATE],
[DATEGRADUATED],
[DATELEFT],
[GPA],
[CLASSOF],
[FRATERNITY],
[ACADEMICCATALOGPROGRAMID],
[ACADEMICCATALOGDEGREEID],
[EDUCATIONALAWARDCODEID],
[PREFERREDCLASSYEAR],
[EDUCATIONALSOURCECODEID],
[EDUCATIONALSOURCEDATE],
[COMMENT],
[EDUCATIONALHISTORYLEVELCODEID],
[EDUCATIONALHISTORYREASONCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@ID,
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
@EDUCATIONALHISTORY_ISPRIMARYRECORD,
@EDUCATIONALHISTORY_KNOWNNAME,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYTYPECODEID,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYSTATUSCODEID,
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
@EDUCATIONALHISTORY_STARTDATE,
@EDUCATIONALHISTORY_DATEGRADUATED,
@EDUCATIONALHISTORY_DATELEFT,
@EDUCATIONALHISTORY_GPA,
@EDUCATIONALHISTORY_CLASSOF,
@EDUCATIONALHISTORY_FRATERNITY,
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID,
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID,
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
@EDUCATIONALHISTORY_COMMENT,
@EDUCATIONALHISTORY_LEVELCODEID,
@EDUCATIONALHISTORY_REASONCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @EDUCATIONALHISTORYID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
end
else
begin
insert into dbo.[EDUCATIONALHISTORY]
(
[ID],
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[ISPRIMARYRECORD],
[KNOWNNAME],
[EDUCATIONALHISTORYTYPECODEID],
[EDUCATIONALHISTORYSTATUSCODEID],
[CONSTITUENCYSTATUSCODE],
[EDUCATIONALDEGREECODEID],
[STARTDATE],
[DATEGRADUATED],
[DATELEFT],
[GPA],
[CLASSOF],
[FRATERNITY],
[EDUCATIONALPROGRAMCODEID],
[EDUCATIONALAWARDCODEID],
[PREFERREDCLASSYEAR],
[EDUCATIONALSOURCECODEID],
[EDUCATIONALSOURCEDATE],
[COMMENT],
[EDUCATIONALHISTORYLEVELCODEID],
[EDUCATIONALHISTORYREASONCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@ID,
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
@EDUCATIONALHISTORY_ISPRIMARYRECORD,
@EDUCATIONALHISTORY_KNOWNNAME,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYTYPECODEID,
@EDUCATIONALHISTORY_EDUCATIONALHISTORYSTATUSCODEID,
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID,
@EDUCATIONALHISTORY_STARTDATE,
@EDUCATIONALHISTORY_DATEGRADUATED,
@EDUCATIONALHISTORY_DATELEFT,
@EDUCATIONALHISTORY_GPA,
@EDUCATIONALHISTORY_CLASSOF,
@EDUCATIONALHISTORY_FRATERNITY,
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID,
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
@EDUCATIONALHISTORY_COMMENT,
@EDUCATIONALHISTORY_LEVELCODEID,
@EDUCATIONALHISTORY_REASONCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @EDUCATIONALHISTORYID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
end
exec dbo.USP_EDUCATIONALHISTORY_GETMAJOR_ADDFROMXML @EDUCATIONALHISTORYID, @EDUCATIONALMAJOR, @CHANGEAGENTID;
exec dbo.USP_EDUCATIONALHISTORY_GETMINOR_ADDFROMXML @EDUCATIONALHISTORYID, @EDUCATIONALMINOR, @CHANGEAGENTID;
end
exec dbo.USP_CONSTITUENT_GETEDUCATIONALINVOLVEMENT_ADDFROMXML @ID, @EDUCATIONALINVOLVEMENT, @CHANGEAGENTID;
if (@SPOUSEID is null) and (coalesce(@SPOUSE_KEYNAME,'') <> '') begin
set @SPOUSEID = newid();
begin try
insert into dbo.[CONSTITUENT]
(
[ID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SPOUSEID,
@SPOUSE_KEYNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50003';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
--Bug 4965 - AdamBu - 9/23/08
-- If security on the new constituent is set and a new spouse or household
-- is created for that constituent, set them with the same security.
if @CONSTIT_SECURITY_ATTRIBUTEID is not null
insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
(
[CONSTIT_SECURITY_ATTRIBUTEID],
[CONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTIT_SECURITY_ATTRIBUTEID,
@SPOUSEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
SELECT
@SPOUSEID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE (NFD.APPLYTOCODE = 0)
/*End name format defaults*/
if (@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@SPOUSE_EMAILADDRESS_EMAILADDRESS,'') <> '') begin
begin try
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SPOUSEID,
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID,
@SPOUSE_EMAILADDRESS_EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50004';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
end
if (@SPOUSEID is not null) begin
begin try
insert into dbo.RELATIONSHIP
(
[RELATIONSHIPCONSTITUENTID],
[RECIPROCALCONSTITUENTID],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[ISSPOUSE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@SPOUSEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50005';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
declare @SPOUSE_COPY_ISPRIMARY bit;
declare @SPOUSE_COPY_EXISTINGID uniqueidentifier;
declare @SPOUSE_COPY_SEQUENCE int;
-- Only copy the address to the spouse if it is not an "empty" address
if @SPOUSE_COPY_ADDRESS = 1
and @ADDRESSINCLUDED = 1
and not
(
@ADDRESS_ADDRESSTYPECODEID is null
and coalesce(@ADDRESS_DONOTMAIL, 0) = 0
and @ADDRESS_DONOTMAILREASONCODEID is null
and @ADDRESS_STATEID is null
and coalesce(@ADDRESS_ADDRESSBLOCK, '') = ''
and coalesce(@ADDRESS_CITY, '') = ''
and coalesce(@ADDRESS_POSTCODE, '') = ''
and coalesce(@ADDRESS_CART, '') = ''
and coalesce(@ADDRESS_DPC, '') = ''
and coalesce(@ADDRESS_LOT, '') = ''
and coalesce(@ADDRESS_STARTDATE, '0000') = '0000'
and coalesce(@ADDRESS_ENDDATE, '0000') = '0000'
)
begin try
-- Remove blank address if one was created when adding the spouse (no contact info entered on the individual add form creates blank address)
delete from dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
ADDRESSTYPECODEID is null and
DONOTMAIL = 0 and
STARTDATE = '0000' and
ENDDATE = '0000' and
STATEID is null and
ADDRESSBLOCK = '' and
CITY = '' and
POSTCODE = '' and
CART = '' and
DPC = '' and
LOT = '';
if exists (select ID from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
set @SPOUSE_COPY_ISPRIMARY = 0;
else
set @SPOUSE_COPY_ISPRIMARY = 1;
select
@SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID;
set @SPOUSE_COPY_EXISTINGID = null;
select
@SPOUSE_COPY_EXISTINGID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID and
DONOTMAIL = @ADDRESS_DONOTMAIL and
DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID and
STARTDATE = @ADDRESS_STARTDATE and
ENDDATE = @ADDRESS_ENDDATE and
COUNTRYID = @ADDRESS_COUNTRYID and
STATEID = @ADDRESS_STATEID and
ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and
CITY = @ADDRESS_CITY and
POSTCODE = @ADDRESS_POSTCODE and
CART = @ADDRESS_CART and
DPC = @ADDRESS_DPC and
LOT = @ADDRESS_LOT;
if @SPOUSE_COPY_EXISTINGID is null
insert into dbo.[ADDRESS]
(
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[DONOTMAILREASONCODEID],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[STARTDATE],
[ENDDATE],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SPOUSEID,
@ADDRESS_ADDRESSTYPECODEID,
@SPOUSE_COPY_ISPRIMARY,
@ADDRESS_DONOTMAIL,
@ADDRESS_DONOTMAILREASONCODEID,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_STARTDATE,
@ADDRESS_ENDDATE,
@SPOUSE_COPY_SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
else
if @SPOUSE_COPY_ISPRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @SPOUSE_COPY_EXISTINGID
and ISPRIMARY <> 1;
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50012';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
if @SPOUSE_COPY_PHONENUMBER = 1
begin try
set @SPOUSE_COPY_EXISTINGID = null;
declare @SPOUSE_COPY_PHONEINCLUDED bit;
set @SPOUSE_COPY_PHONEINCLUDED = 0;
declare @SPOUSE_COPY_PHONETYPECODEID uniqueidentifier;
declare @SPOUSE_COPY_NUMBER nvarchar(100);
select
@SPOUSE_COPY_PHONETYPECODEID = PHONETYPECODEID,
@SPOUSE_COPY_NUMBER = NUMBER,
@SPOUSE_COPY_PHONEINCLUDED = ISPRIMARY
from
dbo.PHONE
where
CONSTITUENTID = @ID and
ISPRIMARY = 1;
if @SPOUSE_COPY_PHONEINCLUDED = 1
begin
if exists (select ID from dbo.PHONE where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
set @SPOUSE_COPY_ISPRIMARY = 0;
else
set @SPOUSE_COPY_ISPRIMARY = 1;
select
@SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
from
dbo.PHONE
where
CONSTITUENTID = @SPOUSEID;
select
@SPOUSE_COPY_EXISTINGID = ID
from
dbo.PHONE
where
CONSTITUENTID = @SPOUSEID and
PHONETYPECODEID = @SPOUSE_COPY_PHONETYPECODEID and
NUMBER = @SPOUSE_COPY_NUMBER;
if @SPOUSE_COPY_EXISTINGID is null
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSEID,
@SPOUSE_COPY_PHONETYPECODEID,
@SPOUSE_COPY_NUMBER,
@SPOUSE_COPY_ISPRIMARY,
@SPOUSE_COPY_SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50013';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
if @SPOUSE_COPY_EMAILADDRESS = 1
and
(
(@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null)
or (coalesce(@EMAILADDRESS_EMAILADDRESS, '') <> '')
)
begin try
set @SPOUSE_COPY_EXISTINGID = null;
if exists (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
set @SPOUSE_COPY_ISPRIMARY = 0;
else
set @SPOUSE_COPY_ISPRIMARY = 1;
select
@SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @SPOUSEID;
select
@SPOUSE_COPY_EXISTINGID = ID
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @SPOUSEID and
EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID and
EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;
if @SPOUSE_COPY_EXISTINGID is null
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSEID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
@SPOUSE_COPY_ISPRIMARY,
@SPOUSE_COPY_SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50014';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
declare @CURRENTDATEEARLIESTTIME date;
set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;
-- get the household of the spouse
declare @SPOUSEHOUSEHOLDID uniqueidentifier;
select
@SPOUSEHOUSEHOLDID = GM.GROUPID
from
dbo.GROUPMEMBER GM
inner join
dbo.GROUPDATA GD on GD.ID = GM.GROUPID
left join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GD.GROUPTYPECODE = 0
and
GM.MEMBERID = @SPOUSEID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME));
-- if the spouse is not in a household, create a new one and add them both
if (@SPOUSEHOUSEHOLDID is null) begin
declare @NAMEFORMATFUNCTIONID uniqueidentifier;
select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;
declare @HOUSEHOLDNAME nvarchar(154);
select @HOUSEHOLDNAME = dbo.UFN_NAMEFORMAT_FROMID(@NAMEFORMATFUNCTIONID, @ID);
declare @HOUSEHOLDID uniqueidentifier;
set @HOUSEHOLDID = newid();
-- create the household constituent/group data records
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[ISORGANIZATION],
[ISGROUP],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@HOUSEHOLDID,
@HOUSEHOLDNAME,
0, -- ISORGANIZATION
1, -- ISGROUP
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.GROUPDATA
(
ID,
GROUPTYPECODE,
STARTDATE,
GROUPTYPEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@HOUSEHOLDID,
0,
@CURRENTDATEEARLIESTTIME,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- create the primary group member record
declare @PRIMARYGROUPMEMBERID uniqueidentifier;
exec dbo.USP_GROUPMEMBERADD @PRIMARYGROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @ID;
update
dbo.GROUPMEMBER
set
ISPRIMARY = 1
where
ID = @PRIMARYGROUPMEMBERID;
-- create the spouse record
declare @SPOUSEGROUPMEMBERID uniqueidentifier;
exec dbo.USP_GROUPMEMBERADD @SPOUSEGROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @SPOUSEID;
--Bug 4965 - AdamBu - 9/23/08
-- If security on the new constituent is set and a new spouse or household
-- is created for that constituent, set them with the same security.
if @CONSTIT_SECURITY_ATTRIBUTEID is not null
insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
(
[CONSTIT_SECURITY_ATTRIBUTEID],
[CONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTIT_SECURITY_ATTRIBUTEID,
@HOUSEHOLDID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
-- if the spouse is in a household, add the new constituent to it
else begin
declare @SPOUSEHOUSEHOLDMEMBERID uniqueidentifier;
exec dbo.USP_GROUPMEMBERADD @SPOUSEHOUSEHOLDMEMBERID output, @CHANGEAGENTID, @SPOUSEHOUSEHOLDID, @ID;
end
end
if (@PRIMARYBUSINESSID is null) and (coalesce(@PRIMARYBUSINESS_KEYNAME,'') <> '') begin
set @PRIMARYBUSINESSID = newid();
begin try
insert into dbo.[CONSTITUENT]
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[WEBADDRESS],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYBUSINESSID,
-1,
@PRIMARYBUSINESS_KEYNAME,
@PRIMARYBUSINESS_WEBADDRESS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50006';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
begin try
insert into dbo.[ORGANIZATIONDATA]
(
[ID],
[INDUSTRYCODEID],
[NUMEMPLOYEES],
[NUMSUBSIDIARIES],
[PARENTCORPID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_INDUSTRYCODEID,
@PRIMARYBUSINESS_NUMEMPLOYEES,
@PRIMARYBUSINESS_NUMSUBSIDIARIES,
@PRIMARYBUSINESS_PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50007';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
if (@PRIMARYBUSINESS_PHONE_PHONETYPECODEID is not null) or (coalesce(@PRIMARYBUSINESS_PHONE_NUMBER,'') <> '') begin
begin try
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID,
@PRIMARYBUSINESS_PHONE_NUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50008';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
if (@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,'') <> '') begin
begin try
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50009';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
if @PRIMARYBUSINESS_ADDRESSINCLUDED = 1 begin
begin try
insert into dbo.[ADDRESS]
(
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[DONOTMAILREASONCODEID],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID,
1,
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL,
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID,
@PRIMARYBUSINESS_ADDRESS_COUNTRYID,
@PRIMARYBUSINESS_ADDRESS_STATEID,
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,
@PRIMARYBUSINESS_ADDRESS_CITY,
@PRIMARYBUSINESS_ADDRESS_POSTCODE,
@PRIMARYBUSINESS_ADDRESS_CART,
@PRIMARYBUSINESS_ADDRESS_DPC,
@PRIMARYBUSINESS_ADDRESS_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50010';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
end
if (@PRIMARYBUSINESSID is not null) begin
begin try
insert into dbo.RELATIONSHIP
(
[RELATIONSHIPCONSTITUENTID],
[RECIPROCALCONSTITUENTID],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[ISPRIMARYBUSINESS],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID,
@PRIMARYBUSINESS_RECIPROCALTYPECODEID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50011';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
if @GROUPMEMBERS is not null
begin
declare GROUPMEMBERCURSOR cursor local fast_forward for
select
CONSTITUENTID,
ISPRIMARY
from
@GROUPMEMBERSTABLE
order by ISPRIMARY desc;
declare @MEMBERID uniqueidentifier;
declare @ISPRIMARY bit;
declare @GROUPMEMBERID uniqueidentifier;
open GROUPMEMBERCURSOR;
fetch next from GROUPMEMBERCURSOR into @MEMBERID, @ISPRIMARY;
while @@FETCH_STATUS = 0
begin
set @GROUPMEMBERID = null;
exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @ID, @MEMBERID;
if @ISPRIMARY = 1
update dbo.GROUPMEMBER set ISPRIMARY = 1 where ID = @GROUPMEMBERID;
fetch next from GROUPMEMBERCURSOR into @MEMBERID, @ISPRIMARY;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close GROUPMEMBERCURSOR;
deallocate GROUPMEMBERCURSOR;
end
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_ADDFROMXML @ID, @CONSTITUENCY, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENT_GETINTERESTS_ADDFROMXML @ID, @INTEREST, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENT_GETALTERNATELOOKUPIDS_ADDFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;