USP_DATAFORMTEMPLATE_ADD_CONSTITUENTUPDATEBATCHTEMPLATE
The save procedure used by the add dataform template "Constituent Update Batch Template 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. |
| @VALIDATEONLY | bit | IN | Validate only |
| @CONSTITUENTTYPECODE | tinyint | IN | Constituent type |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @GENDERCODE | tinyint | IN | Gender |
| @GIVESANONYMOUSLY | bit | IN | Gives anonymously |
| @KEYNAME | nvarchar(100) | IN | Last name |
| @LOOKUPID | nvarchar(100) | IN | Lookup ID |
| @MAIDENNAME | nvarchar(100) | IN | Maiden name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Website |
| @ALTERNATELOOKUPIDS | xml | IN | Alternate lookup ID |
| @SECURITYATTRIBUTES | xml | IN | Security attributes |
| @DECEASED | bit | IN | Deceased |
| @DECEASEDDATE | UDT_FUZZYDATE | IN | Deceased date |
| @CONSTITUENT_SITEID | uniqueidentifier | IN | Constituent site |
| @INTERESTS | xml | IN | Interests |
| @PROSPECTMANAGERFUNDRAISERID | uniqueidentifier | IN | Prospect manager |
| @ADDRESSES | xml | IN | Addresses |
| @EMAILADDRESSES | xml | IN | Email addresses |
| @PHONES | xml | IN | Phones |
| @CONSTITUENCIES | xml | IN | Constituencies |
| @GROUPTYPEID | uniqueidentifier | IN | Group type |
| @GROUPDESCRIPTION | nvarchar(300) | IN | Group description |
| @GROUPSTARTDATE | date | IN | Group consolidation date |
| @ORG_INDUSTRYCODEID | uniqueidentifier | IN | Constituent's industry |
| @ORG_NUMEMPLOYEES | int | IN | Number of employees |
| @ORG_NUMSUBSIDIARIES | int | IN | Number of subsidiaries |
| @ORG_PARENTCORPID | uniqueidentifier | IN | Parent corporation |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | Marital status |
| @SPOUSE_ID | uniqueidentifier | IN | Spouse |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | Spouse birth date |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | Spouse first name |
| @SPOUSE_GENDERCODE | tinyint | IN | Spouse gender |
| @SPOUSE_LASTNAME | nvarchar(100) | IN | Spouse last name |
| @SPOUSE_LOOKUPID | nvarchar(100) | IN | Spouse lookup ID |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | Spouse maiden name |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | Spouse middle name |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | Spouse nickname |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | Spouse suffix |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | Spouse title |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relation to spouse |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Spouse's relation to constituent |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | IN | Business address |
| @BUSINESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Business address type |
| @BUSINESS_CART | nvarchar(10) | IN | Business CART |
| @BUSINESS_CITY | nvarchar(50) | IN | Business city |
| @BUSINESS_COUNTRYID | uniqueidentifier | IN | Business country |
| @BUSINESS_DONOTMAIL | bit | IN | Business do not mail |
| @BUSINESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Business do not mail reason |
| @BUSINESS_DPC | nvarchar(8) | IN | Business DPC |
| @BUSINESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Business email address |
| @BUSINESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Business email type |
| @BUSINESS_INDUSTRYCODEID | uniqueidentifier | IN | Industry |
| @BUSINESS_LOOKUPID | nvarchar(100) | IN | Business lookup ID |
| @BUSINESS_LOT | nvarchar(5) | IN | Business LOT |
| @BUSINESS_ID | uniqueidentifier | IN | Business |
| @BUSINESS_NAME | nvarchar(100) | IN | Business name |
| @BUSINESS_NUMEMPLOYEES | int | IN | Business number of employees |
| @BUSINESS_NUMSUBSIDIARIES | int | IN | Business number of subsidiaries |
| @BUSINESS_PARENTCORPID | uniqueidentifier | IN | Business parent organization |
| @BUSINESS_PHONENUMBER | nvarchar(100) | IN | Business phone number |
| @BUSINESS_PHONE_COUNTRYID | uniqueidentifier | IN | Business phone number country |
| @BUSINESS_PHONETYPECODEID | uniqueidentifier | IN | Business phone type |
| @BUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relation to business |
| @BUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | Business's relation to constituent |
| @BUSINESS_STATEID | uniqueidentifier | IN | Business state |
| @BUSINESS_WEBADDRESS | UDT_WEBADDRESS | IN | Business website |
| @BUSINESS_POSTCODE | nvarchar(12) | IN | Business ZIP |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST | uniqueidentifier | IN | Spouse relationship type |
| @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST | uniqueidentifier | IN | Spouse reciprocal type |
| @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST | uniqueidentifier | IN | Business relationship type |
| @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST | uniqueidentifier | IN | Business reciprocal type |
| @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | Apply recognition from business |
| @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | Recognition from business match percent |
| @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | Apply recognition to business |
| @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | Recognition to business match percent |
| @BUSINESS_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | Recognition from business type |
| @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | Recognition to business type |
| @BUSINESS_EMAILADDRESSSTARTDATE | date | IN | Business email start date |
| @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | Apply recognition from spouse |
| @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | Recognition from spouse match percent |
| @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | Apply recognition to spouse |
| @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | Recognition to spouse match percent |
| @SPOUSE_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | Recognition from spouse type |
| @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | Recognition to spouse type |
| @CONSTITUENT_SITES | xml | IN | Constituent sites |
| @BATCHOWNERID | uniqueidentifier | IN | Batch owner ID |
| @SPOUSE_ALTERNATELOOKUPIDS | xml | IN | Spouse alternate lookup IDs |
| @ROWFROMBATCHUI | bit | IN | |
| @BBNCTRANID | int | IN | |
| @REMOVESPOUSE | bit | IN | |
| @SOCIALMEDIAACCOUNTS | xml | IN | |
| @RELATIONSHIPS | xml | IN | |
| @NETCOMMUNITYTRANSACTIONPROCESSORID | uniqueidentifier | IN | |
| @NAMEFORMATS | xml | IN | |
| @REQUESTSNOEMAIL | bit | IN | |
| @SOLICITCODES | xml | IN | |
| @GENDERCODEID | uniqueidentifier | IN | |
| @SPOUSE_GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTUPDATEBATCHTEMPLATE (
@ID uniqueidentifier output,
@CHANGEAGENTID uniqueidentifier = null,
@VALIDATEONLY bit = 0,
@CONSTITUENTTYPECODE tinyint = 0,
@BIRTHDATE dbo.UDT_FUZZYDATE = null,
@FIRSTNAME nvarchar(50) = null,
@GENDERCODE tinyint = 0,
@GIVESANONYMOUSLY bit = null,
@KEYNAME nvarchar(100) = null,
@LOOKUPID nvarchar(100) = null,
@MAIDENNAME nvarchar(100) = null,
@MIDDLENAME nvarchar(50) = null,
@NICKNAME nvarchar(50) = null,
@SUFFIXCODEID uniqueidentifier = null,
@TITLECODEID uniqueidentifier = null,
@WEBADDRESS dbo.UDT_WEBADDRESS = null,
@ALTERNATELOOKUPIDS xml = null,
@SECURITYATTRIBUTES xml = null,
@DECEASED bit = null,
@DECEASEDDATE dbo.UDT_FUZZYDATE = null,
@CONSTITUENT_SITEID uniqueidentifier = null,
@INTERESTS xml = null,
@PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null,
--@EDUCATIONALINVOLVEMENTS xml,
@ADDRESSES xml = null,
@EMAILADDRESSES xml = null,
@PHONES xml = null,
@CONSTITUENCIES xml = null,
@GROUPTYPEID uniqueidentifier = null,
@GROUPDESCRIPTION nvarchar(300) = null,
@GROUPSTARTDATE date = null,
@ORG_INDUSTRYCODEID uniqueidentifier = null,
@ORG_NUMEMPLOYEES int = null,
@ORG_NUMSUBSIDIARIES int = null,
@ORG_PARENTCORPID uniqueidentifier = null,
@MARITALSTATUSCODEID uniqueidentifier = null,
@SPOUSE_ID uniqueidentifier = null,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null,
@SPOUSE_FIRSTNAME nvarchar(50) = null,
@SPOUSE_GENDERCODE tinyint = 0,
@SPOUSE_LASTNAME nvarchar(100) = null,
@SPOUSE_LOOKUPID nvarchar(100) = null,
@SPOUSE_MAIDENNAME nvarchar(100) = null,
@SPOUSE_MIDDLENAME nvarchar(50) = null,
@SPOUSE_NICKNAME nvarchar(50) = null,
@SPOUSE_SUFFIXCODEID uniqueidentifier = null,
@SPOUSE_TITLECODEID uniqueidentifier = null,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
@BUSINESS_ADDRESSBLOCK nvarchar(150) = null,
@BUSINESS_ADDRESSTYPECODEID uniqueidentifier = null,
@BUSINESS_CART nvarchar(10) = null,
@BUSINESS_CITY nvarchar(50) = null,
@BUSINESS_COUNTRYID uniqueidentifier = null,
@BUSINESS_DONOTMAIL bit = null,
@BUSINESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@BUSINESS_DPC nvarchar(8) = null,
@BUSINESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null,
@BUSINESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@BUSINESS_INDUSTRYCODEID uniqueidentifier = null,
@BUSINESS_LOOKUPID nvarchar(100) = null,
@BUSINESS_LOT nvarchar(5) = null,
@BUSINESS_ID uniqueidentifier = null,
@BUSINESS_NAME nvarchar(100) = null,
@BUSINESS_NUMEMPLOYEES int = null,
@BUSINESS_NUMSUBSIDIARIES int = null,
@BUSINESS_PARENTCORPID uniqueidentifier = null,
@BUSINESS_PHONENUMBER nvarchar(100) = null,
@BUSINESS_PHONE_COUNTRYID uniqueidentifier = null,
@BUSINESS_PHONETYPECODEID uniqueidentifier = null,
@BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@BUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null,
@BUSINESS_STATEID uniqueidentifier = null,
@BUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS = null,
@BUSINESS_POSTCODE nvarchar(12) = null,
@CURRENTAPPUSERID uniqueidentifier,
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null,
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null,
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null,
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null,
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
@BUSINESS_EMAILADDRESSSTARTDATE date = null,
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
@CONSTITUENT_SITES xml = null,
@BATCHOWNERID uniqueidentifier = null,
@SPOUSE_ALTERNATELOOKUPIDS xml = null,
@ROWFROMBATCHUI bit = 0,
@BBNCTRANID int= 0,
@REMOVESPOUSE bit = 0,
@SOCIALMEDIAACCOUNTS xml = null,
@RELATIONSHIPS xml = null,
@NETCOMMUNITYTRANSACTIONPROCESSORID uniqueidentifier = null,
@NAMEFORMATS xml = null,
@REQUESTSNOEMAIL bit = 0,
@SOLICITCODES xml = null,
@GENDERCODEID uniqueidentifier = null,
@SPOUSE_GENDERCODEID uniqueidentifier = null
) as
set nocount on;
declare
@ErrorMessage nvarchar(1000),
@InfoMsg nvarchar(100),
@ErrorSeverity int,
@ErrorState int;
if @ID is null
set @ID = newid()
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SETID uniqueidentifier = newid();
-- Perform validation
if exists (select 1 from dbo.CONSTITUENT where LOOKUPID = @LOOKUPID)
begin
raiserror('BBERR_ORIGINAL_ERROR:2627',1,11);
raiserror('UC_CONSTITUENT_LOOKUPID', 13, 1);
end
if @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR <= 0 or @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_BUSINESS_INVALIDPRIMARYMATCHFACTOR', 13, 1);
end
if @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_BUSINESS_INVALIDRECIPROCALMATCHFACTOR', 13, 1);
end
if @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_SPOUSE_INVALIDPRIMARYMATCHFACTOR', 13, 1);
end
if @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_SPOUSE_INVALIDRECIPROCALMATCHFACTOR', 13, 1);
end
if @CONSTITUENTTYPECODE = 1
begin
if @ORG_PARENTCORPID is not null
if dbo.UFN_ORGANIZATION_VALIDPARENTCORPID(@ID, @ORG_PARENTCORPID) = 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ORGANIZATIONDATA_VALIDPARENTCORPID', 13, 1);
end
if @ORG_NUMEMPLOYEES < 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ORGANIZATIONDATA_NUMEMPLOYEES', 13, 1);
end
if @ORG_NUMSUBSIDIARIES < 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ORGANIZATIONDATA_NUMSUBSIDIARIES', 13, 1);
end
end
-- Ensure group type isn't set for households and is set for custom groups.
-- CK_GROUPDATA_GROUPTYPEIDREQUIREDFORCUSTOMGROUPTYPE is being raised since
-- this is the same constraint name from the GROUPDATA table and this if-statement
-- is the equivalent to the constraint in that table.
if (@CONSTITUENTTYPECODE = 2 and @GROUPTYPEID is not null) or (@CONSTITUENTTYPECODE = 3 and @GROUPTYPEID is null)
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_GROUPDATA_GROUPTYPEIDREQUIREDFORCUSTOMGROUPTYPE', 13, 1);
end
if @SPOUSE_ID is not null or len(coalesce(@SPOUSE_LASTNAME, '')) > 0
begin
if exists (select 1 from dbo.CONSTITUENT where LOOKUPID = @SPOUSE_LOOKUPID and (@SPOUSE_ID is null or ID <> @SPOUSE_ID))
begin
raiserror('BBERR_ORIGINAL_ERROR:50002',1,11);
raiserror('UC_CONSTITUENT_LOOKUPID', 13, 1);
end
end
--Check for existing spouse relationships and existing household memberships
if @SPOUSE_ID is not null
begin
if @CONSTITUENTTYPECODE <> 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_RELATIONSHIP_OWNER_VALIDSPOUSE', 13, 1);
end
if dbo.UFN_RELATIONSHIP_VALIDSPOUSE(1, @SPOUSE_ID) = 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_RELATIONSHIP_VALIDSPOUSE', 13, 1);
end
if exists(
select
ID
from
dbo.RELATIONSHIP
where
ISSPOUSE = 1 and
RELATIONSHIPCONSTITUENTID = @SPOUSE_ID and
RECIPROCALCONSTITUENTID <> @ID) begin
raiserror('Spouse entered has an existing spouse that is different than the constituent listed in the row.',13,1);
end
if exists(
select
GD.ID
from
dbo.GROUPMEMBER GM
inner join
dbo.GROUPDATA GD on GD.ID = GM.GROUPID and GD.GROUPTYPECODE = 0
where
GM.MEMBERID = @SPOUSE_ID and
dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1) begin
raiserror('Spouse entered is part of an existing household that does not include the listed constituent.',13,1);
end
end
if @BUSINESS_ID is not null or @BUSINESS_NAME <> ''
begin
if @BUSINESS_NUMEMPLOYEES < 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ORGANIZATIONDATA_NUMEMPLOYEES', 13, 1);
end
if @BUSINESS_NUMSUBSIDIARIES < 0
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ORGANIZATIONDATA_NUMSUBSIDIARIES', 13, 1);
end
if exists (select 1 from dbo.CONSTITUENT where LOOKUPID = @BUSINESS_LOOKUPID and (@BUSINESS_ID is null or ID <> @BUSINESS_ID))
begin
raiserror('BBERR_ORIGINAL_ERROR:50003',1,11);
raiserror('UC_CONSTITUENT_LOOKUPID', 13, 1);
end
end
--Fill a table with all of the constituent's addresses
declare @ADDRESSTABLE table
(
ADDRESSBLOCK nvarchar(150),
ENDDATE dbo.UDT_MONTHDAY,
STARTDATE dbo.UDT_MONTHDAY,
HISTORICALSTARTDATE datetime,
HISTORICALENDDATE datetime,
ADDRESSTYPECODEID uniqueidentifier,
CART nvarchar(10),
CERTIFICATIONDATA int,
CITY nvarchar(50),
CONGRESSIONALDISTRICTCODEID uniqueidentifier,
COUNTRYID uniqueidentifier,
COUNTYCODEID uniqueidentifier,
DONOTMAIL bit,
DONOTMAILREASONCODEID uniqueidentifier,
DPC nvarchar(8),
INFOSOURCECODEID uniqueidentifier,
ISPRIMARY bit,
LASTVALIDATIONATTEMPTDATE date,
LOCALPRECINCTCODEID uniqueidentifier,
LOT nvarchar(5),
OMITFROMVALIDATION bit,
REGIONCODEID uniqueidentifier,
STATEID uniqueidentifier,
STATEHOUSEDISTRICTCODEID uniqueidentifier,
STATESENATEDISTRICTCODEID uniqueidentifier,
VALIDATIONMESSAGE nvarchar(200),
POSTCODE nvarchar(12),
SEQUENCE int,
UPDATEHOUSEHOLD bit,
INFOSOURCECOMMENTS nvarchar(256)
)
insert into @ADDRESSTABLE
(
ADDRESSBLOCK,
ENDDATE,
STARTDATE,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ADDRESSTYPECODEID,
CART,
CERTIFICATIONDATA,
CITY,
CONGRESSIONALDISTRICTCODEID,
COUNTRYID,
COUNTYCODEID,
DONOTMAIL,
DONOTMAILREASONCODEID,
DPC,
INFOSOURCECODEID,
ISPRIMARY,
LASTVALIDATIONATTEMPTDATE,
LOCALPRECINCTCODEID,
LOT,
OMITFROMVALIDATION,
REGIONCODEID,
STATEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
VALIDATIONMESSAGE,
POSTCODE,
SEQUENCE,
UPDATEHOUSEHOLD,
INFOSOURCECOMMENTS
)
select
ADDRESSBLOCK,
ENDDATE,
STARTDATE,
dbo.UFN_DATE_GETEARLIESTTIME(HISTORICALSTARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(HISTORICALENDDATE),
ADDRESSTYPECODEID,
CART,
CERTIFICATIONDATA,
CITY,
CONGRESSIONALDISTRICTCODEID,
COUNTRYID,
COUNTYCODEID,
case
when HISTORICALENDDATE is not null then
1
else
DONOTMAIL
end as DONOTMAIL,
case
when DONOTMAIL = 1 or HISTORICALENDDATE is not null then
DONOTMAILREASONCODEID
else
null
end as DONOTMAILREASONCODEID,
DPC,
INFOSOURCECODEID,
ISPRIMARY,
LASTVALIDATIONATTEMPTDATE,
LOCALPRECINCTCODEID,
LOT,
OMITFROMVALIDATION,
REGIONCODEID,
STATEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
VALIDATIONMESSAGE,
POSTCODE,
SEQUENCE,
UPDATEHOUSEHOLD,
INFOSOURCECOMMENTS
from dbo.UFN_CONSTITUENT_GETADDRESSES_FORUPDATEBATCH_FROMITEMLISTXML(@ADDRESSES)
if exists(select top 1 * from @ADDRESSTABLE)
begin
declare @PRIMARYADDRESSEXISTS bit
set @PRIMARYADDRESSEXISTS = 0
if exists(select ADDRESSBLOCK from @ADDRESSTABLE where ISPRIMARY = 1)
set @PRIMARYADDRESSEXISTS = 1
if @PRIMARYADDRESSEXISTS = 0 begin
raiserror('Constituent must have a primary address.', 13, 1);
return 1;
end
-- Ensure the state is valid for the country
if exists (select 1 from @ADDRESSTABLE where dbo.UFN_STATE_GETCOUNTRY(STATEID, COUNTRYID) <> COUNTRYID)
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ADDRESS_VALIDSTATEFORCOUNTRY', 13, 1);
end
-- Verify that if a start or end date is set, the corresponding date is set as well
if exists (select 1 from @ADDRESSTABLE where STARTDATE <> '0000' and ENDDATE = '0000')
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ADDRESS_VALIDENDDATEIFSTARTDATE', 13, 1);
end
if exists (select 1 from @ADDRESSTABLE where STARTDATE = '0000' and ENDDATE <> '0000')
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_ADDRESS_VALIDSTARTDATEIFENDDATE', 13, 1);
end
-- Start date must be before end date
if exists (select 1 from @ADDRESSTABLE where HISTORICALSTARTDATE > HISTORICALENDDATE)
raiserror('BBERR_ADDRESS_VALIDHISTORICALDATERANGE', 13, 1);
-- Primary addresses cannot have an end date
if exists (select 1 from @ADDRESSTABLE where ISPRIMARY = 1 and HISTORICALENDDATE is not null)
raiserror('BBERR_ADDRESS_FORMERADDRESSCANNOTBEPRIMARY', 13, 1);
-- #253763 Check to see if there are duplicate addresses. Same address type and same address
if exists ( select COUNT(*)
from @ADDRESSTABLE a1
where HISTORICALENDDATE is null
group by ADDRESSBLOCK,ADDRESSTYPECODEID,CITY,COUNTRYID,STATEID,POSTCODE
HAVING COUNT(*) > 1)
raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1);
end
--Fill a table with all of the constituent's email addresses
declare @EMAILADDRESSTABLE table
(
EMAILADDRESSTYPECODEID uniqueidentifier,
EMAILADDRESS dbo.UDT_EMAILADDRESS,
ISPRIMARY bit,
DONOTEMAIL bit,
SEQUENCE int,
UPDATEHOUSEHOLD bit,
STARTDATE datetime,
ENDDATE datetime,
INFOSOURCECODEID uniqueidentifier,
DONOTEMAILREASONCODEID uniqueidentifier,
INFOSOURCECOMMENTS nvarchar(256)
)
insert into @EMAILADDRESSTABLE
(
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
SEQUENCE,
UPDATEHOUSEHOLD,
STARTDATE,
ENDDATE,
INFOSOURCECODEID,
DONOTEMAILREASONCODEID,
INFOSOURCECOMMENTS
)
select
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
case
when ENDDATE is not null then
1
else
DONOTEMAIL
end as DONOTEMAIL,
SEQUENCE,
UPDATEHOUSEHOLD,
dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(ENDDATE),
INFOSOURCECODEID,
DONOTEMAILREASONCODEID,
ISNULL(INFOSOURCECOMMENTS,'')
from dbo.UFN_CONSTITUENT_GETEMAILADDRESSES_WITHDATES_FORUPDATEBATCH_FROMITEMLISTXML(@EMAILADDRESSES)
if exists(select top 1 * from @EMAILADDRESSTABLE)
begin
declare @PRIMARYEMAILADDRESSEXISTS bit
set @PRIMARYEMAILADDRESSEXISTS = 0
if exists(select EMAILADDRESS from @EMAILADDRESSTABLE where ISPRIMARY = 1)
set @PRIMARYEMAILADDRESSEXISTS = 1
if @PRIMARYEMAILADDRESSEXISTS = 0 begin
raiserror('Constituent must have a primary email address.', 13, 1);
return 1;
end
-- Start date must be before end date
if exists (select 1 from @EMAILADDRESSTABLE where STARTDATE > ENDDATE)
raiserror('BBERR_EMAILADDRESS_VALIDDATERANGE', 13, 1);
-- Primary email addresses cannot have an end date
if exists (select 1 from @EMAILADDRESSTABLE where ISPRIMARY = 1 and ENDDATE is not null)
raiserror('BBERR_EMAILADDRESS_FORMEREMAILADDRESSCANNOTBEPRIMARY', 13, 1);
--JamesWill WI144876 2011-03-23 Don't let email addresses have a start date in the future
if exists (select 1 from @EMAILADDRESSTABLE where STARTDATE > @CURRENTDATE)
raiserror('BBERR_EMAILADDRESS_STARTDATE_FUTURE', 13, 1);
--JamesWill WI144876 2011-03-23 Don't let email addresses have an end date in the future
if exists (select 1 from @EMAILADDRESSTABLE where ENDDATE > @CURRENTDATE)
raiserror('BBERR_EMAILADDRESS_ENDDATE_FUTURE', 13, 1);
-- #253763 Check to see if there are duplicate email addresses. Same email address type and same address
if exists ( select COUNT(*)
from @EMAILADDRESSTABLE a1
where ENDDATE is null
group by EMAILADDRESS,EMAILADDRESSTYPECODEID
HAVING COUNT(*) > 1)
raiserror('BBERR_EMAILADDRESS_DUPLICATENOTALLOWED', 13, 1);
end
--Fill a table with all of the constituent's phone numbers
declare @PHONENUMBERTABLE table
(
PHONETYPECODEID uniqueidentifier,
NUMBER nvarchar(100),
ISPRIMARY bit,
DONOTCALL bit,
SEQUENCE int,
UPDATEHOUSEHOLD bit,
COUNTRYID uniqueidentifier,
SEASONALSTARTDATE dbo.UDT_MONTHDAY,
SEASONALENDDATE dbo.UDT_MONTHDAY,
STARTTIME dbo.UDT_HOURMINUTE,
ENDTIME dbo.UDT_HOURMINUTE,
STARTDATE datetime,
ENDDATE datetime,
INFOSOURCECODEID uniqueidentifier,
DONOTTEXT bit,
INFOSOURCECOMMENTS nvarchar(256)
)
insert into @PHONENUMBERTABLE
(
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
DONOTCALL,
SEQUENCE,
UPDATEHOUSEHOLD,
COUNTRYID,
SEASONALSTARTDATE,
SEASONALENDDATE,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
INFOSOURCECODEID,
DONOTTEXT,
INFOSOURCECOMMENTS
)
select
PHONETYPECODEID,
NUMBER = dbo.UFN_PHONE_REMOVECOUNTRYCODE([PHONE].[NUMBER], [PHONE].[COUNTRYID]),
ISPRIMARY,
case
when ENDDATE is not null then
1
else
DONOTCALL
end as DONOTCALL,
SEQUENCE,
UPDATEHOUSEHOLD,
COUNTRYID,
SEASONALSTARTDATE,
SEASONALENDDATE,
STARTTIME,
ENDTIME,
dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(ENDDATE),
INFOSOURCECODEID,
ISNULL(DONOTTEXT, CAST(0 as BIT)),
ISNULL(INFOSOURCECOMMENTS,'')
from dbo.UFN_CONSTITUENT_GETPHONES_FORUPDATEBATCH_FROMITEMLISTXML(@PHONES) [PHONE]
if exists(select top 1 * from @PHONENUMBERTABLE)
begin
declare @PRIMARYPHONEEXISTS bit
set @PRIMARYPHONEEXISTS = 0
if exists(select NUMBER from @PHONENUMBERTABLE where ISPRIMARY = 1)
set @PRIMARYPHONEEXISTS = 1
if @PRIMARYPHONEEXISTS = 0 begin
raiserror('Constituent must have a primary phone.', 13, 1);
return 1;
end
-- Make sure the number is set
if exists (select 1 from @PHONENUMBERTABLE where len(coalesce(NUMBER, '')) = 0)
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_PHONE_NUMBER', 13, 1);
end
-- Start date must be before end date
if exists (select 1 from @PHONENUMBERTABLE where STARTDATE > ENDDATE)
raiserror('BBERR_PHONE_VALIDDATERANGE', 13, 1);
-- Primary email addresses cannot have an end date
if exists (select 1 from @PHONENUMBERTABLE where ISPRIMARY = 1 and ENDDATE is not null)
raiserror('BBERR_PHONE_FORMERPHONECANNOTBEPRIMARY', 13, 1);
--JamesWill WI144876 2011-03-23 Don't let phone numbers have a start date in the future
if exists (select 1 from @PHONENUMBERTABLE where STARTDATE > @CURRENTDATE)
raiserror('BBERR_PHONE_STARTDATE_FUTURE', 13, 1);
--JamesWill WI144876 2011-03-23 Don't let phone numbers have an end date in the future
if exists (select 1 from @PHONENUMBERTABLE where ENDDATE > @CURRENTDATE)
raiserror('BBERR_PHONE_ENDDATE_FUTURE', 13, 1);
-- #253763 Check to see if there are duplicate phone numbers. Same phone number type and same phone number
if exists ( select COUNT(*)
from @PHONENUMBERTABLE a1
where ENDDATE is null
group by dbo.UFN_PHONE_REMOVEFORMATTING(NUMBER),PHONETYPECODEID,COUNTRYID
HAVING COUNT(*) > 1)
raiserror('BBERR_PHONENUMBER_DUPLICATENOTALLOWED', 13, 1);
end
--Fill a table with all of the constituent's social media accounts
declare @SOCIALMEDIAACCOUNTTABLE table
(
SOCIALMEDIASERVICEID uniqueidentifier, USERID nvarchar(100),
URL dbo.UDT_WEBADDRESS, SOCIALMEDIAACCOUNTTYPECODEID uniqueidentifier,
INFOSOURCECODEID uniqueidentifier, DONOTCONTACT bit, SEQUENCE int
)
insert into @SOCIALMEDIAACCOUNTTABLE
(
SOCIALMEDIASERVICEID, USERID, URL, SOCIALMEDIAACCOUNTTYPECODEID, INFOSOURCECODEID, DONOTCONTACT, SEQUENCE
)
select
ACCOUNTS.SOCIALMEDIASERVICEID,
ACCOUNTS.USERID,
ACCOUNTS.URL,
ACCOUNTS.SOCIALMEDIAACCOUNTTYPECODEID,
ACCOUNTS.INFOSOURCECODEID,
ACCOUNTS.DONOTCONTACT,
ACCOUNTS.SEQUENCE
from
dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_FORUPDATEBATCH_FROMITEMLISTXML(@SOCIALMEDIAACCOUNTS) ACCOUNTS
--Fill a table with all of the constituent's relationships
declare @RELATIONSHIPTABLE table
(
RECIPROCALCONSTITUENTID uniqueidentifier, RELATIONSHIPTYPECODEID uniqueidentifier,
RECIPROCALTYPECODEID uniqueidentifier, STARTDATE datetime, RELATIONSHIPEXISTS bit, RELATIONSHIPSETID uniqueidentifier
)
insert into @RELATIONSHIPTABLE
(
RECIPROCALCONSTITUENTID, RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID, STARTDATE, RELATIONSHIPEXISTS, RELATIONSHIPSETID
)
select
RELATIONSHIPS.RECIPROCALCONSTITUENTID,
RELATIONSHIPS.RELATIONSHIPTYPECODEID,
RELATIONSHIPS.RECIPROCALTYPECODEID,
RELATIONSHIPS.STARTDATE,
case when RELATIONSHIP.ID is not null then 1 else 0 end as RELATIONSHIPEXISTS,
newid()
from
dbo.UFN_CONSTITUENT_GETRELATIONSHIPS_FORUPDATEBATCH_FROMITEMLISTXML(@RELATIONSHIPS) RELATIONSHIPS
left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIPS.RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
if exists (select top 1 * from @RELATIONSHIPTABLE)
begin
--Throw an error if the batch owner does not have rights to the reciprocal constituent.
if exists (
select 1
from
@RELATIONSHIPTABLE RT
outer apply dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(RT.RECIPROCALCONSTITUENTID) as CONSTITUENTSITES
where
dbo.UFN_SITEALLOWEDFORUSER(@BATCHOWNERID, CONSTITUENTSITES.SITEID) = 0
)
begin
raiserror('BBERR_RELATIONSHIPS_RECIPROCALCONSTITUENT_PERMISSIONDENIED', 13, 1);
end
end
--Fill a table with all of the constituent's constituencies
declare @CONSTITUENCYTABLE table
(
ID uniqueidentifier, CONSTITUENCYCODEID uniqueidentifier, DATEFROM datetime, DATETO datetime
)
insert into @CONSTITUENCYTABLE
(
ID, CONSTITUENCYCODEID, DATEFROM, DATETO
)
select
newid(), CONSTITUENCYCODEID, DATEFROM, DATETO
from dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FORUPDATEBATCH_FROMITEMLISTXML(@CONSTITUENCIES)
if exists(select top 1 * from @CONSTITUENCYTABLE)
begin
if exists (select 1 from @CONSTITUENCYTABLE where DATEFROM is not null and DATETO is not null and DATETO < DATEFROM)
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_CONSTITUENCY_DATETOAFTERDATEFROM', 13, 1);
end
-- Look for duplicate constituencies w/overlapping dates
if exists ( select 1
from @CONSTITUENCYTABLE C1
inner join @CONSTITUENCYTABLE C2 on
C1.ID <> C2.ID and
C1.CONSTITUENCYCODEID = C2.CONSTITUENCYCODEID and
(
( C1.DATETO between C2.DATEFROM and C2.DATETO) or
(C2.DATETO between C1.DATEFROM and C1.DATETO) or
( C1.DATEFROM between C2.DATEFROM and C2.DATETO) or
(C2.DATEFROM between C1.DATEFROM and C1.DATETO) or
( C1.DATEFROM is null and C2.DATEFROM <= C1.DATETO) or
(C2.DATEFROM is null and C1.DATEFROM <= C2.DATETO) or
( C1.DATETO is null and C2.DATETO >= C1.DATEFROM) or
(C2.DATETO is null and C1.DATETO >= C2.DATEFROM) or
(C2.DATEFROM is null and C1.DATEFROM is null) or
(C2.DATETO is null and C1.DATETO is null) or
(C2.DATEFROM is null and C2.DATETO is null) or
( C1.DATEFROM is null and C1.DATETO is null)
))
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('CK_CONSTITUENCY_ISUNIQUE', 13, 1);
end
end
--Fill a table with all of the constituent's alternate lookup IDs
declare @ALTERNATELOOKUPIDTABLE table
(
ALTERNATELOOKUPIDTYPECODEID uniqueidentifier, ALTERNATELOOKUPID nvarchar(100)
)
insert into @ALTERNATELOOKUPIDTABLE
(
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID
)
select
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID
from dbo.UFN_CONSTITUENT_GETALTERNATELOOKUPIDS_FORUPDATEBATCH_FROMITEMLISTXML(@ALTERNATELOOKUPIDS)
if exists (select 1 from @ALTERNATELOOKUPIDTABLE group by ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID having count(*) > 1)
begin
raiserror('BBERR_ORIGINAL_ERROR:2627',1,11);
raiserror('UIX_ALTERNATELOOKUPID_ALTERNATELOOKUPIDTYPECODEID_ALTERNATELOOKUPID', 13, 1);
end
--Fill a table with all of the spouse's alternate lookup IDs
declare @SPOUSEALTERNATELOOKUPIDTABLE table
(
ALTERNATELOOKUPIDTYPECODEID uniqueidentifier, ALTERNATELOOKUPID nvarchar(100)
)
insert into @SPOUSEALTERNATELOOKUPIDTABLE
(
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID
)
select
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID
from dbo.UFN_CONSTITUENT_GETSPOUSEALTERNATELOOKUPIDS_FORUPDATEBATCH_FROMITEMLISTXML(@SPOUSE_ALTERNATELOOKUPIDS)
if exists (select 1 from @SPOUSEALTERNATELOOKUPIDTABLE group by ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID having count(*) > 1)
begin
raiserror('BBERR_ORIGINAL_ERROR:2627',1,11);
raiserror('UIX_ALTERNATELOOKUPID_ALTERNATELOOKUPIDTYPECODEID_ALTERNATELOOKUPID', 13, 1);
end
if exists ( select 1 from dbo.ALTERNATELOOKUPID
inner join @SPOUSEALTERNATELOOKUPIDTABLE as NEWALTERNATELOOKUPID on
ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID = NEWALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID and
ALTERNATELOOKUPID.ALTERNATELOOKUPID = NEWALTERNATELOOKUPID.ALTERNATELOOKUPID)
begin
raiserror('BBERR_ORIGINAL_ERROR:2627',1,11);
raiserror('UIX_SPOUSEALTERNATELOOKUPID_ALTERNATELOOKUPIDTYPECODEID_ALTERNATELOOKUPID', 13, 1);
end
if @CONSTITUENT_SITEID is not null and @CONSTITUENT_SITES is null
set @CONSTITUENT_SITES =
(
select @CONSTITUENT_SITEID SITEID
for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
)
--If there are no sites and the batch owner requires site, then default on the sites of the batch owner
if @CONSTITUENT_SITES is null and dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
set @CONSTITUENT_SITES =
(
select SITEID
from dbo.UFN_SITESFORUSER(@BATCHOWNERID)
for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
)
--Fill a table with all of the constituent's sites
declare @CONSTITUENTSITESTABLE table
(
SITEID uniqueidentifier
)
insert into @CONSTITUENTSITESTABLE
(
SITEID
)
select
SITEID
from dbo.UFN_CONSTITUENT_GETSITES_FORUPDATEBATCH_FROMITEMLISTXML(@CONSTITUENT_SITES)
declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @CONSTITUENTSITECOUNT int;
select @CONSTITUENTSITECOUNT = count(SITEID) from @CONSTITUENTSITESTABLE;
if (@ISADMIN = 0 and @CONSTITUENTSITECOUNT > 0 and not exists (
select
1
from
@CONSTITUENTSITESTABLE CONSTITUENTSITES
left join dbo.UFN_SITESFORUSER(@BATCHOWNERID) SITESFORUSER on SITESFORUSER.SITEID = CONSTITUENTSITES.SITEID
where
SITESFORUSER.SITEID is not null
))
raiserror('CK_CONSTITUENTSITES_PERMISSIONDENIED', 13, 1);
-- Insert data into tables
if @VALIDATEONLY = 0
begin
begin try
declare @MATCHINGCONSTITUENTS table(CONSTITID uniqueidentifier);
-----------------------------------------------------------------
--Basic Constituent Info
--===============================================================
insert into dbo.[CONSTITUENT]
(
ID, ISORGANIZATION, ISGROUP,
BIRTHDATE, FIRSTNAME, GENDERCODE, GIVESANONYMOUSLY, KEYNAME, CUSTOMIDENTIFIER, MAIDENNAME,
MIDDLENAME, NICKNAME, SUFFIXCODEID, TITLECODEID, WEBADDRESS, MARITALSTATUSCODEID, ISCONSTITUENT,
CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED, GENDERCODEID
)
values
(
@ID, case when @CONSTITUENTTYPECODE = 1 then 1 else 0 end, case when @CONSTITUENTTYPECODE = 2 then 1 when @CONSTITUENTTYPECODE = 3 then 1 else 0 end,
@BIRTHDATE, @FIRSTNAME, @GENDERCODE, @GIVESANONYMOUSLY, @KEYNAME, @LOOKUPID, @MAIDENNAME,
@MIDDLENAME, @NICKNAME, @SUFFIXCODEID, @TITLECODEID, @WEBADDRESS, @MARITALSTATUSCODEID, 1, -- ISCONSTITUENT
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @GENDERCODEID
);
-----------------------------------------------------------------
--Name formats
--===============================================================
if @CONSTITUENTTYPECODE = 0 begin
exec dbo.USP_CONSTITUENTUPDATEBATCH_ADDUPDATENAMEFORMATS @ID, @NAMEFORMATS, @CHANGEAGENTID, @CURRENTDATE;
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
NFD.NAMEFORMATTYPECODEID not in (select NAMEFORMATTYPECODEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_FROMITEMLISTXML(@NAMEFORMATS));
end
-------------------
-- Solicit Codes --
-------------------
if @SOLICITCODES is not null
begin
declare @SOLICITCODESTABLE table
(
[CONSTITUENTSOLICITCODEID] [uniqueidentifier] NOT NULL,
[CONSTITUENTID] [uniqueidentifier] NOT NULL,
[SOLICITCODEID] [uniqueidentifier] NOT NULL,
[SEQUENCE] [int] NOT NULL,
[STARTDATE] [datetime] NULL,
[ENDDATE] [datetime] NULL,
[COMMENTS] [nvarchar](100) NOT NULL,
[CONSENTPREFERENCECODE] [tinyint] NOT NULL,
[SOURCECODEID] [uniqueidentifier] NULL,
[SOURCEFILEPATH] [nvarchar](260) NOT NULL,
[PRIVACYPOLICYFILEPATH] [nvarchar](260) NOT NULL,
[SUPPORTINGINFORMATION] [nvarchar](max) NOT NULL,
[CONSENTSTATEMENT] [nvarchar](max) NOT NULL
);
insert into @SOLICITCODESTABLE
(
CONSTITUENTSOLICITCODEID,
CONSTITUENTID,
SOLICITCODEID,
STARTDATE,
ENDDATE,
COMMENTS,
SEQUENCE,
CONSENTPREFERENCECODE,
SOURCECODEID,
SOURCEFILEPATH,
PRIVACYPOLICYFILEPATH,
SUPPORTINGINFORMATION,
CONSENTSTATEMENT
)
select
isnull(CONSTITUENTSOLICITCODEID, newid()) CONSTITUENTSOLICITCODEID,
@ID,
SOLICITCODEID,
STARTDATE,
ENDDATE,
COMMENTS,
SEQUENCE,
CONSENTPREFERENCECODE,
SOURCECODEID,
SOURCEFILEPATH,
PRIVACYPOLICYFILEPATH,
SUPPORTINGINFORMATION,
CONSENTSTATEMENT
from dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES);
update dbo.CONSTITUENTSOLICITCODE set
CONSTITUENTSOLICITCODE.CONSTITUENTID = SOLICITCODES.CONSTITUENTID,
CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODES.SOLICITCODEID,
CONSTITUENTSOLICITCODE.STARTDATE = SOLICITCODES.STARTDATE,
CONSTITUENTSOLICITCODE.ENDDATE = SOLICITCODES.ENDDATE,
CONSTITUENTSOLICITCODE.COMMENTS = SOLICITCODES.COMMENTS,
CONSTITUENTSOLICITCODE.SEQUENCE = SOLICITCODES.SEQUENCE,
CONSTITUENTSOLICITCODE.CONSENTPREFERENCECODE = SOLICITCODES.CONSENTPREFERENCECODE,
CONSTITUENTSOLICITCODE.SOURCECODEID = SOLICITCODES.SOURCECODEID,
CONSTITUENTSOLICITCODE.SOURCEFILEPATH = SOLICITCODES.SOURCEFILEPATH,
CONSTITUENTSOLICITCODE.PRIVACYPOLICYFILEPATH = SOLICITCODES.PRIVACYPOLICYFILEPATH,
CONSTITUENTSOLICITCODE.SUPPORTINGINFORMATION = SOLICITCODES.SUPPORTINGINFORMATION,
CONSTITUENTSOLICITCODE.CONSENTSTATEMENT = SOLICITCODES.CONSENTSTATEMENT,
CONSTITUENTSOLICITCODE.CHANGEDBYID = @CHANGEAGENTID,
CONSTITUENTSOLICITCODE.DATECHANGED = @CURRENTDATE
from @SOLICITCODESTABLE SOLICITCODES
where CONSTITUENTSOLICITCODE.ID = SOLICITCODES.CONSTITUENTSOLICITCODEID;
-- prune updates from table variable
delete SOLICITCODES
from @SOLICITCODESTABLE SOLICITCODES
join dbo.BATCHCONSTITUENTUPDATESOLICITCODE BSC on BSC.CONSTITUENTSOLICITCODEID = SOLICITCODES.CONSTITUENTSOLICITCODEID;
-- do inserts
insert into CONSTITUENTSOLICITCODE
(
ID,
CONSTITUENTID,
SOLICITCODEID,
STARTDATE,
ENDDATE,
COMMENTS,
SEQUENCE,
CONSENTPREFERENCECODE,
SOURCECODEID,
SOURCEFILEPATH,
PRIVACYPOLICYFILEPATH,
SUPPORTINGINFORMATION,
CONSENTSTATEMENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
isnull(CONSTITUENTSOLICITCODEID, newid()),
@ID,
SOLICITCODEID,
STARTDATE,
ENDDATE,
COMMENTS,
SEQUENCE,
CONSENTPREFERENCECODE,
SOURCECODEID,
SOURCEFILEPATH,
PRIVACYPOLICYFILEPATH,
SUPPORTINGINFORMATION,
CONSENTSTATEMENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SOLICITCODESTABLE;
end
--Update/insert the constituentid into NETCOMMUNITYCLIENTUSER & set the NETCOMMUNITYMEMBER flag
DECLARE @BBNCUSERID int, @SIGNUPTRANSACTIONDATE datetime, @SignupID int, @SEQUENCEID int, @ProfileUpdateTransactionsID int
SELECT @BBNCUSERID= BBNCUSERID, @SIGNUPTRANSACTIONDATE= DATEADDED, @NETCOMMUNITYTRANSACTIONPROCESSORID = NETCOMMUNITYTRANSACTIONPROCESSORID FROM BATCHCONSTITUENTUPDATEBBNCINFO WHERE BBNCTRANID= @BBNCTRANID
SELECT @SignupID= SignupTransactionsID FROM SignupTransactions WHERE UserID= @BBNCUSERID
SELECT @SEQUENCEID= SEQUENCEID FROM CONSTITUENT WHERE ID= @ID
SELECT @ProfileUpdateTransactionsID= ProfileUpdateTransactionsID FROM ProfileUpdateTransactions WHERE AddedByUserID= @BBNCUSERID
IF ISNULL(@BBNCUSERID, 0) <> 0
BEGIN
if ISNULL(@BBNCTRANID, 0) = 0
begin
raiserror('An exception has occurred. Please contact Blackbaud Customer Support for assistance.', 13, 1);
end
-- @BBNCUSERID will always be -1 for Acquisition transactions. Therefore we do not need to perform the IF block statements in Acquisition transaction records.
IF @BBNCUSERID > 0 AND ( SELECT dbo.fnGetUserIDFromLinkedRecordID (@SEQUENCEID, 0) ) IS NULL
begin
EXEC spTransactions_MarkSignupAsProcessed @SignupID= @SignupID, @BackOfficeID= @SEQUENCEID, @BackOfficeSysID= 0
EXEC dbo.USP_BBNC_COMMITSIGNUPCLIENTUSERADDUPDATE @ID= @ID, @BBNCUSERID= @BBNCUSERID, @SIGNUPTRANSACTIONDATE= @SIGNUPTRANSACTIONDATE, @CHANGEAGENTID= @CHANGEAGENTID, @CHANGEDATE= @CURRENTDATE
UPDATE CONSTITUENT SET NETCOMMUNITYMEMBER= 1 FROM CONSTITUENT WHERE ID= @ID
end
else
begin
IF @BBNCUSERID < 0 -- @BBNCUSERID will always be -1 for Acquisition records and update Process_date in SignupTransactions table for Acquisition Transactions.
begin
-- In Acquisition transaction, @BBNCUSERID = -1 and SignupTransactions table UserId having NULL.Therefore, we are fetching SignupTransactionID from Transactions table using BBNCTRANID.
EXEC spTransactions_MarkSignupAsProcessed @SignupID= @BBNCTRANID, @BackOfficeID= @SEQUENCEID, @BackOfficeSysID= 0;
end
else
begin
declare @OLDUSERID integer
SELECT @OLDUSERID= dbo.fnGetUserIDFromLinkedRecordID (@SEQUENCEID, 0)
declare @USERNAME varchar(100)
select @USERNAME = USERNAME from dbo.NETCOMMUNITYCLIENTUSER where BBNCUSERID = @OLDUSERID
raiserror('This constituent is already linked to an existing Blackbaud Internet Solutions member: %s', 13, 1, @USERNAME);
return 1
end
end
--Add constituent origination information
declare @INFOSOURCECODEID uniqueidentifier
select top (1)
@INFOSOURCECODEID = [INFOSOURCECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @INFOSOURCECODEID is not null and not exists (select 1 from dbo.[CONSTITUENTORIGINATION] where ID = @ID)
begin
insert into dbo.[CONSTITUENTORIGINATION]
(
ID, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end
end
IF ISNULL(@ProfileUpdateTransactionsID, 0) <> 0
EXEC spTransactions_MarkProfileUpdateAsProcessed @ProfileUpdateTransactionsID= @ProfileUpdateTransactionsID
---------------------------------------------------------------------
--Kill the constituent if deceased has been checked
if @DECEASED = 1 begin
insert into dbo.DECEASEDCONSTITUENT
(
ID,
DECEASEDDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
@DECEASEDDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
exec dbo.USP_CONSTITUENT_DECEASEFROMRULES @ID, @CHANGEAGENTID;
end
--Insert new sites into the constituent site table
insert into dbo.CONSTITUENTSITE
(
ID,
CONSTITUENTID,
SITEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(), @ID, CONSTITUENTSITESTABLE.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@CONSTITUENTSITESTABLE CONSTITUENTSITESTABLE
-----------------------------------------------------------------
--Basic group info
--===============================================================
if @CONSTITUENTTYPECODE = 2 or @CONSTITUENTTYPECODE = 3
insert into dbo.GROUPDATA
(
ID,
GROUPTYPECODE, GROUPTYPEID, DESCRIPTION,
CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED,STARTDATE
)
values
(
@ID,
coalesce(case when @CONSTITUENTTYPECODE = 2 then 0 when @CONSTITUENTTYPECODE = 3 then 1 else 0 end, 0), @GROUPTYPEID, @GROUPDESCRIPTION,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@GROUPSTARTDATE
)
-----------------------------------------------------------------
--Basic organization info
--===============================================================
if @CONSTITUENTTYPECODE = 1
begin
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
NUMEMPLOYEES,
NUMSUBSIDIARIES,
PARENTCORPID,
CHANGEDBYID,
ADDEDBYID,
DATECHANGED,
DATEADDED
)
values
(
@ID,
@ORG_INDUSTRYCODEID,
coalesce(@ORG_NUMEMPLOYEES, 0),
coalesce(@ORG_NUMSUBSIDIARIES, 0),
@ORG_PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
if @ORG_PARENTCORPID is not null and @ORG_PARENTCORPID <> '00000000-0000-0000-0000-000000000000'
exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID, @ORG_PARENTCORPID,@CURRENTDATE,@CHANGEAGENTID,@CURRENTDATE,1,null
end
-----------------------------------------------------------------
--Spouse information
--===============================================================
if @REMOVESPOUSE = 1
begin
--if this is a BBIS transaction, use the configure integration settings
if ISNULL(@BBNCUSERID, 0) <> 0
begin
declare @SPOUSEREMOVEDRELATIONSHIPTYPECODEID uniqueidentifier
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] = @CURRENTDATE
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @ID
and [RELATIONSHIP].[ISSPOUSE] = 1;
end
-- if this is a non BBIS transaction, use the batch relationship fields
if not ISNULL(@BBNCUSERID, 0) <> 0
begin
update dbo.RELATIONSHIP
set
[ISSPOUSE] = 0,
[RELATIONSHIPTYPECODEID] = @SPOUSE_RELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID] = @SPOUSE_RECIPROCALTYPECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @ID
and [RELATIONSHIP].[ISSPOUSE] = 1;
end
end
declare @NEWSPOUSE bit = 0;
declare @EXISTINGSPOUSEHOUSEHOLDID uniqueidentifier = null;
if @SPOUSE_ID is not null and @REMOVESPOUSE = 0
begin
begin try
update SPOUSE
set
SPOUSE.BIRTHDATE = coalesce(nullif(@SPOUSE_BIRTHDATE, '00000000'), SPOUSE.BIRTHDATE),
SPOUSE.FIRSTNAME = coalesce(nullif(@SPOUSE_FIRSTNAME, ''), SPOUSE.FIRSTNAME),
SPOUSE.GENDERCODE = coalesce(@SPOUSE_GENDERCODE, '0'),
SPOUSE.GENDERCODEID = @SPOUSE_GENDERCODEID,
SPOUSE.KEYNAME = coalesce(nullif(@SPOUSE_LASTNAME, ''), SPOUSE.KEYNAME),
SPOUSE.CUSTOMIDENTIFIER = @SPOUSE_LOOKUPID,
SPOUSE.MAIDENNAME = coalesce(nullif(@SPOUSE_MAIDENNAME, ''), SPOUSE.MAIDENNAME),
SPOUSE.MIDDLENAME = coalesce(nullif(@SPOUSE_MIDDLENAME, ''), SPOUSE.MIDDLENAME),
SPOUSE.NICKNAME = coalesce(nullif(@SPOUSE_NICKNAME, ''), SPOUSE.NICKNAME),
SPOUSE.SUFFIXCODEID = coalesce(nullif(@SPOUSE_SUFFIXCODEID, '00000000-0000-0000-0000-000000000000'), SPOUSE.SUFFIXCODEID),
SPOUSE.TITLECODEID = coalesce(nullif(@SPOUSE_TITLECODEID, '00000000-0000-0000-0000-000000000000'), SPOUSE.TITLECODEID),
SPOUSE.CHANGEDBYID = @CHANGEAGENTID,
SPOUSE.DATECHANGED = @CURRENTDATE
from
dbo.CONSTITUENT SPOUSE
where
SPOUSE.ID = @SPOUSE_ID
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
select
@EXISTINGSPOUSEHOUSEHOLDID = GD.ID
from
dbo.GROUPMEMBER GM
left outer join
dbo.GROUPDATA GD on GM.GROUPID = GD.ID
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.MEMBERID = @SPOUSE_ID
and GD.GROUPTYPECODE = 0
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));
end
else if @SPOUSE_LASTNAME <> '' begin
--Insert a new constituent for the spouse if an existing constituent was not chosen
set @SPOUSE_ID = newid()
set @NEWSPOUSE = 1
begin try
insert into dbo.CONSTITUENT
(
ID,
BIRTHDATE,
FIRSTNAME,
GENDERCODE,
GENDERCODEID,
KEYNAME,
CUSTOMIDENTIFIER,
MAIDENNAME,
MIDDLENAME,
NICKNAME,
SUFFIXCODEID,
TITLECODEID,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSE_ID,
coalesce(@SPOUSE_BIRTHDATE, '00000000'),
coalesce(@SPOUSE_FIRSTNAME, ''),
coalesce(@SPOUSE_GENDERCODE, 0),
@SPOUSE_GENDERCODEID,
coalesce(@SPOUSE_LASTNAME, ''),
coalesce(@SPOUSE_LOOKUPID, ''),
coalesce(@SPOUSE_MAIDENNAME, ''),
coalesce(@SPOUSE_MIDDLENAME, ''),
coalesce(@SPOUSE_NICKNAME, ''),
@SPOUSE_SUFFIXCODEID,
@SPOUSE_TITLECODEID,
dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT(),
@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
--Insert new sites into the constituent site table
insert into dbo.CONSTITUENTSITE
(
ID,
CONSTITUENTID,
SITEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(), @SPOUSE_ID, CONSTITUENTSITESTABLE.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@CONSTITUENTSITESTABLE CONSTITUENTSITESTABLE
--Add default name formats for the newly created spouse.
insert into [dbo].[NAMEFORMAT]
(
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION]
)
select
@SPOUSE_ID,
NAMEFORMATDEFAULT.NAMEFORMATTYPECODEID,
NAMEFORMATDEFAULT.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
NAMEFORMATDEFAULT.PRIMARYADDRESSEE,
NAMEFORMATDEFAULT.PRIMARYSALUTATION
from
dbo.NAMEFORMATDEFAULT
where
(NAMEFORMATDEFAULT.APPLYTOCODE = 0);
end
--Add a relationship for the new spouse and make a household
if @SPOUSE_ID is not null begin
set @SETID = newid();
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RELATIONSHIP
(
RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID, ISSPOUSE, RELATIONSHIPSETID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID, @SPOUSE_ID,
@SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, 1, @SETID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
if (not exists(select ID from dbo.DISABLEDWEALTHUPDATES where ID = @SPOUSE_ID))
insert into dbo.DISABLEDWEALTHUPDATES
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSE_ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @ID, @SPOUSE_ID,
0, @CHANGEAGENTID, @CURRENTDATE, 0,@CURRENTAPPUSERID, 1; --skip adding sites, so that we can properly default them
--If the spouse is a new constituent or the spouse is an existing constituent that did not have a household associated with it,
-- then we need to default on sites for the new household that was just created.
if @NEWSPOUSE = 1 or @EXISTINGSPOUSEHOUSEHOLDID = null
begin
declare @NEWHOUSEHOLDID uniqueidentifier;
select @NEWHOUSEHOLDID = GD.ID
from
dbo.GROUPMEMBER GM
left outer join
dbo.GROUPDATA GD on GM.GROUPID = GD.ID
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.MEMBERID = @SPOUSE_ID
and GD.GROUPTYPECODE = 0
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));
--Insert new sites into the constituent site table
insert into dbo.CONSTITUENTSITE
(
ID,
CONSTITUENTID,
SITEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(), @NEWHOUSEHOLDID, CONSTITUENTSITESTABLE.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@CONSTITUENTSITESTABLE CONSTITUENTSITESTABLE
end
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @SPOUSE_ID, @SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @SPOUSE_ID, @ID, null, null,
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID, @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID;
end
--Get the constituents who are in the household
insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_CONSTITUENT_MATCHINGHOUSEHOLDRECORDS(@ID);
if @MAIDENNAME is not null and @MAIDENNAME != ''
begin
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@ID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
if @SPOUSE_ID is not null and @SPOUSE_MAIDENNAME is not null and @SPOUSE_MAIDENNAME != ''
begin
if not exists (select ID from dbo.ALIAS
where ALIAS.CONSTITUENTID = @SPOUSE_ID
and ALIAS.KEYNAME = @SPOUSE_MAIDENNAME
and ALIAS.FIRSTNAME = @SPOUSE_FIRSTNAME
and ALIAS.MIDDLENAME = @SPOUSE_MIDDLENAME)
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@SPOUSE_ID, @SPOUSE_MAIDENNAME, @SPOUSE_FIRSTNAME, @SPOUSE_MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
--Spouse Alternate lookup IDs
if @SPOUSE_ID is not null
begin
--Insert new alternate lookup IDs into the ALTERNATELOOKUPID table
insert into dbo.ALTERNATELOOKUPID
(
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
@SPOUSE_ID,
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@SPOUSEALTERNATELOOKUPIDTABLE TEMP
where not exists(
select
ALTERNATELOOKUPIDTYPECODEID
from
ALTERNATELOOKUPID
where
ALTERNATELOOKUPIDTYPECODEID = TEMP.ALTERNATELOOKUPIDTYPECODEID and
ALTERNATELOOKUPID.CONSTITUENTID = @SPOUSE_ID)
end
-----------------------------------------------------------------
--Alternate Lookup IDs
--===============================================================
--Insert new alternate lookup IDs into the ALTERNATELOOKUPID table
insert into dbo.ALTERNATELOOKUPID
(
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
@ID,
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@ALTERNATELOOKUPIDTABLE TEMP
where not exists(
select
ALTERNATELOOKUPIDTYPECODEID
from
ALTERNATELOOKUPID
where
ALTERNATELOOKUPIDTYPECODEID = TEMP.ALTERNATELOOKUPIDTYPECODEID and
ALTERNATELOOKUPID.CONSTITUENTID = @ID)
-----------------------------------------------------------------
--Security Attributes
--===============================================================
--Fill a table with all of the constituent's security attributes
declare @SECURITYATTRIBUTETABLE table
(
CONSTIT_SECURITY_ATTRIBUTEID uniqueidentifier
)
insert into @SECURITYATTRIBUTETABLE
(
CONSTIT_SECURITY_ATTRIBUTEID
)
select
CONSTIT_SECURITY_ATTRIBUTEID
from dbo.UFN_CONSTITUENT_GETSECURITYATTRIBUTES_FORUPDATEBATCH_FROMITEMLISTXML(@SECURITYATTRIBUTES)
--Insert new security attributes into the CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT table
insert into dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT
(
CONSTITUENTID,
CONSTIT_SECURITY_ATTRIBUTEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select @ID, TEMP.CONSTIT_SECURITY_ATTRIBUTEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@SECURITYATTRIBUTETABLE TEMP
where not exists(select CSA.CONSTIT_SECURITY_ATTRIBUTEID from CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT CSA where CSA.CONSTIT_SECURITY_ATTRIBUTEID = TEMP.CONSTIT_SECURITY_ATTRIBUTEID and CSA.CONSTITUENTID = @ID)
-----------------------------------------------------------------
--Constituent Interests
--===============================================================
--Fill a table with all of the constituent's interests
declare @INTERESTTABLE table
(
CONSTITUENTINTERESTCODEID uniqueidentifier, NOTE nvarchar(255), CONSTITUENTINTERESTID uniqueidentifier
)
insert into @INTERESTTABLE
(
CONSTITUENTINTERESTCODEID, NOTE, CONSTITUENTINTERESTID
)
select
CONSTITUENTINTERESTCODEID, NOTE, CONSTITUENTINTERESTID
from dbo.UFN_CONSTITUENT_GETINTERESTS_FORUPDATEBATCH_FROMITEMLISTXML(@INTERESTS)
--to support deletion of interests
--delete from dbo.CONSTITUENTINTEREST
--where
--CONSTITUENTID = @ID
-- SHL BBIS Bug 347432;If the temporary table has an interest that the user already has, we update that interest
update OLDI
set OLDI.NOTE = NEWI.NOTE, OLDI.CHANGEDBYID = @CHANGEAGENTID, OLDI.DATECHANGED = @CURRENTDATE
from dbo.CONSTITUENTINTEREST OLDI
inner join @INTERESTTABLE NEWI on NEWI.CONSTITUENTINTERESTID = OLDI.ID
--Insert new interests into the CONSTITUENTINTEREST table
insert into dbo.CONSTITUENTINTEREST
(
CONSTITUENTID,
CONSTITUENTINTERESTCODEID,
NOTE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select @ID, CONSTITUENTINTERESTCODEID, NOTE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@INTERESTTABLE TEMP
where TEMP.CONSTITUENTINTERESTID is null
-----------------------------------------------------------------
--Educational Involvements
--===============================================================
--Fill a table with all of the constituent's educational involvements
/*declare @INVOLVEMENTTABLE table
(
EDUCATIONALINVOLVEMENTTYPECODEID uniqueidentifier, DATEFROM dbo.UDT_FUZZYDATE, DATETO dbo.UDT_FUZZYDATE,
EDUCATIONALINSTITUTIONID uniqueidentifier, COMMENT nvarchar(500), EDUCATIONALINVOLVEMENTAWARDCODEID uniqueidentifier,
EDUCATIONALINVOLVEMENTNAMEID uniqueidentifier
)
insert into @INVOLVEMENTTABLE
(
EDUCATIONALINVOLVEMENTTYPECODEID, DATEFROM, DATETO,
EDUCATIONALINSTITUTIONID, COMMENT, EDUCATIONALINVOLVEMENTAWARDCODEID,
EDUCATIONALINVOLVEMENTNAMEID
)
select
EDUCATIONALINVOLVEMENTTYPECODEID, DATEFROM, DATETO,
EDUCATIONALINSTITUTIONID, COMMENT, EDUCATIONALINVOLVEMENTAWARDCODEID,
EDUCATIONALINVOLVEMENTNAMEID
from dbo.UFN_CONSTITUENT_GETEDUCATIONALINVOLVEMENTS_FORUPDATEBATCH_FROMITEMLISTXML(@EDUCATIONALINVOLVEMENTS)
--Insert new involvements into the EDUCATIONALINVOLVEMENT table
insert into dbo.EDUCATIONALINVOLVEMENT
(
CONSTITUENTID,
EDUCATIONALINVOLVEMENTTYPECODEID, DATEFROM, DATETO,
EDUCATIONALINSTITUTIONID, COMMENT, EDUCATIONALINVOLVEMENTAWARDCODEID,
EDUCATIONALINVOLVEMENTNAMEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select @ID, EDUCATIONALINVOLVEMENTTYPECODEID, DATEFROM, DATETO,
EDUCATIONALINSTITUTIONID, COMMENT, EDUCATIONALINVOLVEMENTAWARDCODEID,
EDUCATIONALINVOLVEMENTNAMEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@INVOLVEMENTTABLE TEMP
where not exists(
select ID
from dbo.EDUCATIONALINVOLVEMENT
where
CONSTITUENTID = @ID and EDUCATIONALINVOLVEMENTTYPECODEID = TEMP.EDUCATIONALINVOLVEMENTTYPECODEID and
DATEFROM = TEMP.DATEFROM and DATETO = TEMP.DATETO and EDUCATIONALINSTITUTIONID = TEMP.EDUCATIONALINSTITUTIONID and
COMMENT = TEMP.COMMENT and EDUCATIONALINVOLVEMENTAWARDCODEID = TEMP.EDUCATIONALINVOLVEMENTAWARDCODEID and
EDUCATIONALINVOLVEMENTNAMEID = TEMP.EDUCATIONALINVOLVEMENTNAMEID)
*/
-----------------------------------------------------------------
--Addresses
--===============================================================
if exists(select top 1 * from @ADDRESSTABLE)
begin
/* WI 254227 Removing this check MMR
After quite a bit of discussion on this bug we are changing USP_ADDRESS_UPDATEMATCHINGADDRESSES
to include HISTORICAL dates as part of the comparison if two addresses match
-- If a new primary address is NOT being added to all members of the household, another household
-- has one of the addresses marked as primary, and 'Updated household address'
-- is selected, that address cannot have an end date.
if not exists (
select
1
from
@ADDRESSTABLE ADDRESSTABLE
where
ADDRESSTABLE.ISPRIMARY = 1
and ADDRESSTABLE.UPDATEHOUSEHOLD = 1
) and exists (
select
1
from
@ADDRESSTABLE ADDRESSTABLE
inner join dbo.ADDRESS on
ADDRESS.ADDRESSBLOCK = ADDRESSTABLE.ADDRESSBLOCK and
ADDRESS.CITY = ADDRESSTABLE.CITY and
(ADDRESS.COUNTRYID = ADDRESSTABLE.COUNTRYID or (ADDRESS.COUNTRYID is null and ADDRESSTABLE.COUNTRYID is null)) and
(ADDRESS.STATEID = ADDRESSTABLE.STATEID or (ADDRESS.STATEID is null and ADDRESSTABLE.STATEID is null)) and
ADDRESS.POSTCODE = ADDRESSTABLE.POSTCODE
where
ADDRESSTABLE.UPDATEHOUSEHOLD = 1
and ADDRESS.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
and ADDRESS.ISPRIMARY = 1
and ADDRESSTABLE.HISTORICALENDDATE is not null
)
raiserror('BBERR_ADDRESS_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
*/
if exists(select * from @ADDRESSTABLE where ISPRIMARY = 1) begin
update dbo.ADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @ID
end
if exists(select * from @ADDRESSTABLE where ISPRIMARY = 1 and UPDATEHOUSEHOLD = 1) begin
update dbo.ADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
end
--Update existing entries in the ADDRESS and ADDRESSVALIDATIONUPDATE table
update dbo.ADDRESS
set
ENDDATE = coalesce(nullif(TEMP.ENDDATE, '0000'), A.ENDDATE),
STARTDATE = coalesce(nullif(TEMP.STARTDATE, '0000'), A.STARTDATE),
HISTORICALSTARTDATE = coalesce(TEMP.HISTORICALSTARTDATE, A.HISTORICALSTARTDATE),
HISTORICALENDDATE = coalesce(TEMP.HISTORICALENDDATE, A.HISTORICALENDDATE),
ADDRESSTYPECODEID = coalesce(nullif(TEMP.ADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), A.ADDRESSTYPECODEID),
CART = coalesce(nullif(TEMP.CART, ''), A.CART),
DONOTMAIL = TEMP.DONOTMAIL,
DONOTMAILREASONCODEID = coalesce(nullif(TEMP.DONOTMAILREASONCODEID, '00000000-0000-0000-0000-000000000000'), A.DONOTMAILREASONCODEID),
DPC = coalesce(nullif(TEMP.DPC, ''), A.DPC),
ISPRIMARY = TEMP.ISPRIMARY,
LOT = coalesce(nullif(TEMP.LOT, ''), A.LOT),
SEQUENCE = coalesce(nullif(TEMP.SEQUENCE,''), A.SEQUENCE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS A
inner join @ADDRESSTABLE TEMP
on
A.ADDRESSBLOCK = TEMP.ADDRESSBLOCK and
A.CITY = TEMP.CITY and
(A.COUNTRYID = TEMP.COUNTRYID or (A.COUNTRYID is null and TEMP.COUNTRYID is null)) and
(A.STATEID = TEMP.STATEID or (A.STATEID is null and TEMP.STATEID is null)) and
A.POSTCODE = TEMP.POSTCODE
where
(CONSTITUENTID = @ID)
update dbo.ADDRESS
set
ENDDATE = coalesce(nullif(TEMP.ENDDATE, '0000'), A.ENDDATE),
STARTDATE = coalesce(nullif(TEMP.STARTDATE, '0000'), A.STARTDATE),
HISTORICALSTARTDATE = coalesce(TEMP.HISTORICALSTARTDATE, A.HISTORICALSTARTDATE),
HISTORICALENDDATE = coalesce(TEMP.HISTORICALENDDATE, A.HISTORICALENDDATE),
ADDRESSTYPECODEID = coalesce(nullif(TEMP.ADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), A.ADDRESSTYPECODEID),
CART = coalesce(nullif(TEMP.CART, ''), A.CART),
DONOTMAIL = TEMP.DONOTMAIL,
DONOTMAILREASONCODEID = coalesce(nullif(TEMP.DONOTMAILREASONCODEID, '00000000-0000-0000-0000-000000000000'), A.DONOTMAILREASONCODEID),
DPC = coalesce(nullif(TEMP.DPC, ''), A.DPC),
ISPRIMARY = TEMP.ISPRIMARY,
LOT = coalesce(nullif(TEMP.LOT, ''), A.LOT),
SEQUENCE = coalesce(nullif(TEMP.SEQUENCE,''), A.SEQUENCE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS A
inner join @ADDRESSTABLE TEMP
on
A.ADDRESSBLOCK = TEMP.ADDRESSBLOCK and
A.CITY = TEMP.CITY and
(A.COUNTRYID = TEMP.COUNTRYID or (A.COUNTRYID is null and TEMP.COUNTRYID is null)) and
(A.STATEID = TEMP.STATEID or (A.STATEID is null and TEMP.STATEID is null)) and
A.POSTCODE = TEMP.POSTCODE
where
((CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)) and (TEMP.UPDATEHOUSEHOLD = 1))
update dbo.ADDRESSVALIDATIONUPDATE
set
CERTIFICATIONDATA = coalesce(nullif(TEMP.CERTIFICATIONDATA, ''), AVU.CERTIFICATIONDATA),
CONGRESSIONALDISTRICTCODEID = coalesce(nullif(TEMP.CONGRESSIONALDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.CONGRESSIONALDISTRICTCODEID),
COUNTYCODEID = coalesce(nullif(TEMP.COUNTYCODEID, '00000000-0000-0000-0000-000000000000'), AVU.COUNTYCODEID),
INFOSOURCECODEID = coalesce(nullif(TEMP.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), AVU.INFOSOURCECODEID),
--LASTVALIDATIONATTEMPTDATE = coalesce(nullif(TEMP.LASTVALIDATIONATTEMPTDATE, '00000000'), AVU.LASTVALIDATIONATTEMPTDATE),
LOCALPRECINCTCODEID = coalesce(nullif(TEMP.LOCALPRECINCTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.LOCALPRECINCTCODEID),
OMITFROMVALIDATION = TEMP.OMITFROMVALIDATION,
REGIONCODEID = coalesce(nullif(TEMP.REGIONCODEID, '00000000-0000-0000-0000-000000000000'), AVU.REGIONCODEID),
STATEHOUSEDISTRICTCODEID = coalesce(nullif(TEMP.STATEHOUSEDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.STATEHOUSEDISTRICTCODEID),
STATESENATEDISTRICTCODEID = coalesce(nullif(TEMP.STATESENATEDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.STATESENATEDISTRICTCODEID),
VALIDATIONMESSAGE = coalesce(nullif(TEMP.VALIDATIONMESSAGE, ''), AVU.VALIDATIONMESSAGE),
INFOSOURCECOMMENTS = coalesce(nullif(TEMP.INFOSOURCECOMMENTS, ''), AVU.INFOSOURCECOMMENTS),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS A
inner join @ADDRESSTABLE TEMP
on
A.ADDRESSBLOCK = TEMP.ADDRESSBLOCK and
A.CITY = TEMP.CITY and
(A.COUNTRYID = TEMP.COUNTRYID or (A.COUNTRYID is null and TEMP.COUNTRYID is null)) and
(A.STATEID = TEMP.STATEID or (A.STATEID is null and TEMP.STATEID is null)) and
A.POSTCODE = TEMP.POSTCODE
inner join dbo.ADDRESSVALIDATIONUPDATE AVU on A.ID = AVU.ID
where
(A.CONSTITUENTID = @ID)
update dbo.ADDRESSVALIDATIONUPDATE
set
CERTIFICATIONDATA = coalesce(nullif(TEMP.CERTIFICATIONDATA, ''), AVU.CERTIFICATIONDATA),
CONGRESSIONALDISTRICTCODEID = coalesce(nullif(TEMP.CONGRESSIONALDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.CONGRESSIONALDISTRICTCODEID),
COUNTYCODEID = coalesce(nullif(TEMP.COUNTYCODEID, '00000000-0000-0000-0000-000000000000'), AVU.COUNTYCODEID),
INFOSOURCECODEID = coalesce(nullif(TEMP.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), AVU.INFOSOURCECODEID),
--LASTVALIDATIONATTEMPTDATE = coalesce(nullif(TEMP.LASTVALIDATIONATTEMPTDATE, '00000000'), AVU.LASTVALIDATIONATTEMPTDATE),
LOCALPRECINCTCODEID = coalesce(nullif(TEMP.LOCALPRECINCTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.LOCALPRECINCTCODEID),
OMITFROMVALIDATION = TEMP.OMITFROMVALIDATION,
REGIONCODEID = coalesce(nullif(TEMP.REGIONCODEID, '00000000-0000-0000-0000-000000000000'), AVU.REGIONCODEID),
STATEHOUSEDISTRICTCODEID = coalesce(nullif(TEMP.STATEHOUSEDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.STATEHOUSEDISTRICTCODEID),
STATESENATEDISTRICTCODEID = coalesce(nullif(TEMP.STATESENATEDISTRICTCODEID, '00000000-0000-0000-0000-000000000000'), AVU.STATESENATEDISTRICTCODEID),
VALIDATIONMESSAGE = coalesce(nullif(TEMP.VALIDATIONMESSAGE, ''), AVU.VALIDATIONMESSAGE),
INFOSOURCECOMMENTS = coalesce(nullif(TEMP.INFOSOURCECOMMENTS, ''), AVU.INFOSOURCECOMMENTS),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.ADDRESS A
inner join @ADDRESSTABLE TEMP
on
A.ADDRESSBLOCK = TEMP.ADDRESSBLOCK and
A.CITY = TEMP.CITY and
(A.COUNTRYID = TEMP.COUNTRYID or (A.COUNTRYID is null and TEMP.COUNTRYID is null)) and
(A.STATEID = TEMP.STATEID or (A.STATEID is null and TEMP.STATEID is null)) and
A.POSTCODE = TEMP.POSTCODE
inner join dbo.ADDRESSVALIDATIONUPDATE AVU on A.ID = AVU.ID
where
((A.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)) and (TEMP.UPDATEHOUSEHOLD = 1))
--Insert new addresses into the ADDRESS and ADDRESSVALIDATIONUPDATE table
insert into dbo.ADDRESS
(
CONSTITUENTID,
ADDRESSBLOCK, ENDDATE, STARTDATE, HISTORICALSTARTDATE, HISTORICALENDDATE, ADDRESSTYPECODEID, CART,
CITY, COUNTRYID, DONOTMAIL, DONOTMAILREASONCODEID, DPC, ISPRIMARY, LOT, STATEID, POSTCODE, SEQUENCE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
@ID,
ADDRESSBLOCK, ENDDATE, STARTDATE, HISTORICALSTARTDATE, HISTORICALENDDATE, ADDRESSTYPECODEID, CART,
CITY, COUNTRYID, DONOTMAIL, DONOTMAILREASONCODEID, DPC, ISPRIMARY, LOT, STATEID, POSTCODE, SEQUENCE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@ADDRESSTABLE TEMP
where not exists(
select ID
from dbo.ADDRESS
where
CONSTITUENTID = @ID and
ADDRESSBLOCK = TEMP.ADDRESSBLOCK and
CITY = TEMP.CITY and
(COUNTRYID = TEMP.COUNTRYID or (COUNTRYID is null and TEMP.COUNTRYID is null)) and
(STATEID = TEMP.STATEID or (STATEID is null and TEMP.STATEID is null)) and
POSTCODE = TEMP.POSTCODE)
insert into dbo.ADDRESS
(
CONSTITUENTID,
ADDRESSBLOCK, ENDDATE, STARTDATE, HISTORICALSTARTDATE, HISTORICALENDDATE, ADDRESSTYPECODEID, CART,
CITY, COUNTRYID, DONOTMAIL, DONOTMAILREASONCODEID, DPC, ISPRIMARY, LOT, STATEID, POSTCODE, SEQUENCE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
MC.CONSTITID,
AT.ADDRESSBLOCK, AT.ENDDATE, AT.STARTDATE, AT.HISTORICALSTARTDATE, AT.HISTORICALENDDATE, AT.ADDRESSTYPECODEID, AT.CART,
AT.CITY, AT.COUNTRYID, AT.DONOTMAIL, AT.DONOTMAILREASONCODEID, AT.DPC, AT.ISPRIMARY, AT.LOT, AT.STATEID, AT.POSTCODE, AT.SEQUENCE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@MATCHINGCONSTITUENTS MC
join @ADDRESSTABLE AT on AT.UPDATEHOUSEHOLD = 1
where
not exists(
select CONSTITUENTID from dbo.ADDRESS
where
CONSTITUENTID = MC.CONSTITID and
ADDRESSBLOCK = AT.ADDRESSBLOCK and
CITY = AT.CITY and
(COUNTRYID = AT.COUNTRYID or (COUNTRYID is null and AT.COUNTRYID is null)) and
(STATEID = AT.STATEID or (STATEID is null and AT.STATEID is null)) and
POSTCODE = AT.POSTCODE)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
CERTIFICATIONDATA, CONGRESSIONALDISTRICTCODEID, COUNTYCODEID, INFOSOURCECODEID, LASTVALIDATIONATTEMPTDATE, LOCALPRECINCTCODEID,
OMITFROMVALIDATION, REGIONCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, VALIDATIONMESSAGE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECOMMENTS
)
select
A.ID,
coalesce(nullif(AT.CERTIFICATIONDATA, ''), 0),
AT.CONGRESSIONALDISTRICTCODEID, AT.COUNTYCODEID, AT.INFOSOURCECODEID, AT.LASTVALIDATIONATTEMPTDATE, AT.LOCALPRECINCTCODEID,
AT.OMITFROMVALIDATION, AT.REGIONCODEID, AT.STATEHOUSEDISTRICTCODEID, AT.STATESENATEDISTRICTCODEID, AT.VALIDATIONMESSAGE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, AT.INFOSOURCECOMMENTS
from
@ADDRESSTABLE AT join dbo.ADDRESS A
on
A.CONSTITUENTID = @ID and
(AT.COUNTRYID = A.COUNTRYID or (AT.COUNTRYID is null and A.COUNTRYID is null)) and
AT.CITY = A.CITY and
AT.POSTCODE = A.POSTCODE and
AT.ADDRESSBLOCK = A.ADDRESSBLOCK and
(AT.STATEID = A.STATEID or (AT.STATEID is null and A.STATEID is null)) and
AT.ISPRIMARY = A.ISPRIMARY
where
not exists(select ID from ADDRESSVALIDATIONUPDATE where ADDRESSVALIDATIONUPDATE.ID = A.ID)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
CERTIFICATIONDATA, CONGRESSIONALDISTRICTCODEID, COUNTYCODEID, INFOSOURCECODEID, LASTVALIDATIONATTEMPTDATE, LOCALPRECINCTCODEID,
OMITFROMVALIDATION, REGIONCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, VALIDATIONMESSAGE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECOMMENTS
)
select
A.ID,
coalesce(nullif(AT.CERTIFICATIONDATA, ''), 0),
AT.CONGRESSIONALDISTRICTCODEID, AT.COUNTYCODEID, AT.INFOSOURCECODEID, AT.LASTVALIDATIONATTEMPTDATE, AT.LOCALPRECINCTCODEID,
AT.OMITFROMVALIDATION, AT.REGIONCODEID, AT.STATEHOUSEDISTRICTCODEID, AT.STATESENATEDISTRICTCODEID, AT.VALIDATIONMESSAGE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, AT.INFOSOURCECOMMENTS
from
@MATCHINGCONSTITUENTS MC
join dbo.ADDRESS A on A.CONSTITUENTID = MC.CONSTITID
join @ADDRESSTABLE AT on
(A.COUNTRYID = AT.COUNTRYID or (A.COUNTRYID is null and AT.COUNTRYID is null)) and
(A.STATEID = AT.STATEID or (A.STATEID is null and AT.STATEID is null)) and
A.POSTCODE = AT.POSTCODE and
A.ADDRESSBLOCK = AT.ADDRESSBLOCK and
A.CITY = AT.CITY and
AT.ISPRIMARY = A.ISPRIMARY
where
AT.UPDATEHOUSEHOLD = 1 and
not exists(select ID from dbo.ADDRESSVALIDATIONUPDATE AVU where AVU.ID = A.ID)
end
-----------------------------------------------------------------
--Email Addresses
--===============================================================
if exists(select top 1 * from @EMAILADDRESSTABLE)
begin
-- If a new primary email address is NOT being added to all members of the household, another household
-- has one of the email addresses marked as primary, and 'Updated household email addresses'
-- is selected, that email address cannot have an end date.
if not exists (
select
1
from
@EMAILADDRESSTABLE EMAILADDRESSTABLE
where
EMAILADDRESSTABLE.ISPRIMARY = 1
and EMAILADDRESSTABLE.UPDATEHOUSEHOLD = 1
) and exists (
select
1
from
@EMAILADDRESSTABLE EMAILADDRESSTABLE
inner join dbo.EMAILADDRESS on EMAILADDRESS.EMAILADDRESS = EMAILADDRESSTABLE.EMAILADDRESS
where
EMAILADDRESSTABLE.UPDATEHOUSEHOLD = 1
and EMAILADDRESS.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
and EMAILADDRESS.ISPRIMARY = 1
and EMAILADDRESSTABLE.ENDDATE is not null
)
raiserror('BBERR_EMAILADDRESS_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
if exists(select * from @EMAILADDRESSTABLE where ISPRIMARY = 1) begin
update dbo.EMAILADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @ID
end
if exists(select * from @EMAILADDRESSTABLE where ISPRIMARY = 1 and UPDATEHOUSEHOLD = 1) begin
update dbo.EMAILADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
end
--Update existing entries in the EMAILADDRESS table
update EA
set
EA.EMAILADDRESSTYPECODEID = coalesce(nullif(EAT.EMAILADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), EA.EMAILADDRESSTYPECODEID),
EA.ISPRIMARY = EAT.ISPRIMARY,
EA.DONOTEMAIL = EAT.DONOTEMAIL,
EA.SEQUENCE = coalesce(nullif(EAT.SEQUENCE,''), EA.SEQUENCE),
EA.STARTDATE = EAT.STARTDATE,
EA.ENDDATE = EAT.ENDDATE,
EA.CHANGEDBYID = @CHANGEAGENTID,
EA.DATECHANGED = @CURRENTDATE,
EA.INFOSOURCECODEID = coalesce(nullif(EAT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), EA.INFOSOURCECODEID),
EA.DONOTEMAILREASONCODEID = coalesce(nullif(EAT.DONOTEMAILREASONCODEID, '00000000-0000-0000-0000-000000000000'), EA.DONOTEMAILREASONCODEID),
EA.INFOSOURCECOMMENTS = EAT.INFOSOURCECOMMENTS
from
dbo.EMAILADDRESS EA
inner join @EMAILADDRESSTABLE EAT on EA.EMAILADDRESS = EAT.EMAILADDRESS
where
(EA.CONSTITUENTID = @ID)
update EA
set
EA.EMAILADDRESSTYPECODEID = coalesce(nullif(EAT.EMAILADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), EA.EMAILADDRESSTYPECODEID),
EA.ISPRIMARY = EAT.ISPRIMARY,
EA.DONOTEMAIL = EAT.DONOTEMAIL,
EA.SEQUENCE = coalesce(nullif(EAT.SEQUENCE,''), EA.SEQUENCE),
EA.STARTDATE = EAT.STARTDATE,
EA.ENDDATE = EAT.ENDDATE,
EA.CHANGEDBYID = @CHANGEAGENTID,
EA.DATECHANGED = @CURRENTDATE,
EA.INFOSOURCECODEID = coalesce(nullif(EAT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), EA.INFOSOURCECODEID),
EA.DONOTEMAILREASONCODEID = coalesce(nullif(EAT.DONOTEMAILREASONCODEID, '00000000-0000-0000-0000-000000000000'), EA.DONOTEMAILREASONCODEID),
EA.INFOSOURCECOMMENTS = EAT.INFOSOURCECOMMENTS
from
dbo.EMAILADDRESS EA
inner join @EMAILADDRESSTABLE EAT on EA.EMAILADDRESS = EAT.EMAILADDRESS
where
((EA.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)) and (EAT.UPDATEHOUSEHOLD = 1))
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY,
DONOTEMAIL, SEQUENCE, STARTDATE, ENDDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID, DONOTEMAILREASONCODEID, INFOSOURCECOMMENTS
)
select
@ID,
EAT.EMAILADDRESSTYPECODEID, EAT.EMAILADDRESS, EAT.ISPRIMARY,
EAT.DONOTEMAIL, EAT.SEQUENCE, EAT.STARTDATE, EAT.ENDDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, EAT.INFOSOURCECODEID, EAT.DONOTEMAILREASONCODEID, EAT.INFOSOURCECOMMENTS
from
@EMAILADDRESSTABLE EAT
where not exists(
select ID
from dbo.EMAILADDRESS
where
CONSTITUENTID = @ID and
EMAILADDRESS = EAT.EMAILADDRESS)
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY,
DONOTEMAIL, SEQUENCE, STARTDATE, ENDDATE,
CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED, INFOSOURCECODEID, DONOTEMAILREASONCODEID, INFOSOURCECOMMENTS
)
select
MC.CONSTITID,
EAT.EMAILADDRESSTYPECODEID, EAT.EMAILADDRESS, EAT.ISPRIMARY,
EAT.DONOTEMAIL, EAT.SEQUENCE, EAT.STARTDATE, EAT.ENDDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, EAT.INFOSOURCECODEID, EAT.DONOTEMAILREASONCODEID, EAT.INFOSOURCECOMMENTS
from
@MATCHINGCONSTITUENTS MC
join @EMAILADDRESSTABLE EAT on EAT.UPDATEHOUSEHOLD = 1
where
not exists(
select CONSTITUENTID from dbo.EMAILADDRESS
where
CONSTITUENTID = MC.CONSTITID and
EMAILADDRESS = EAT.EMAILADDRESS)
end
-----------------------------------------------------------------
--Phone Numbers
--===============================================================
if exists(select top 1 * from @PHONENUMBERTABLE)
begin
-- If a new primary phone number is NOT being added to all members of the household, another household
-- has one of the phone numbers marked as primary, and 'Updated household phone numbers'
-- is selected, that phone number cannot have an end date.
if not exists (
select
1
from
@PHONENUMBERTABLE PHONENUMBERTABLE
where
PHONENUMBERTABLE.ISPRIMARY = 1
and PHONENUMBERTABLE.UPDATEHOUSEHOLD = 1
) and exists (
select
1
from
@PHONENUMBERTABLE PHONENUMBERTABLE
inner join dbo.PHONE on PHONE.NUMBER = PHONENUMBERTABLE.NUMBER
where
PHONENUMBERTABLE.UPDATEHOUSEHOLD = 1
and PHONE.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
and PHONE.ISPRIMARY = 1
and PHONENUMBERTABLE.ENDDATE is not null
)
raiserror('BBERR_PHONE_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
if exists(select * from @PHONENUMBERTABLE where ISPRIMARY = 1) begin
update dbo.PHONE
set
ISPRIMARY = 0
where
CONSTITUENTID = @ID
end
if exists(select * from @PHONENUMBERTABLE where ISPRIMARY = 1 and UPDATEHOUSEHOLD = 1) begin
update dbo.PHONE
set
ISPRIMARY = 0
where
CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)
end
--Update existing entries in the PHONE table
update P
set
P.PHONETYPECODEID = coalesce(nullif(PT.PHONETYPECODEID, '00000000-0000-0000-0000-000000000000'), P.PHONETYPECODEID),
P.ISPRIMARY = PT.ISPRIMARY,
P.DONOTCALL = PT.DONOTCALL,
P.SEQUENCE = coalesce(nullif(PT.SEQUENCE,''), P.SEQUENCE),
P.COUNTRYID = PT.COUNTRYID,
P.SEASONALSTARTDATE = PT.SEASONALSTARTDATE,
P.SEASONALENDDATE=PT.SEASONALENDDATE,
P.STARTTIME=PT.STARTTIME,
P.ENDTIME=PT.ENDTIME,
P.STARTDATE = PT.STARTDATE,
P.ENDDATE = PT.ENDDATE,
P.CHANGEDBYID = @CHANGEAGENTID,
P.DATECHANGED = @CURRENTDATE,
P.INFOSOURCECODEID = PT.INFOSOURCECODEID,
P.DONOTTEXT = PT.DONOTTEXT,
P.INFOSOURCECOMMENTS = PT.INFOSOURCECOMMENTS
from
dbo.PHONE P
inner join @PHONENUMBERTABLE PT on P.NUMBER = PT.NUMBER
where
(P.CONSTITUENTID = @ID)
update P
set
P.PHONETYPECODEID = coalesce(nullif(PT.PHONETYPECODEID, '00000000-0000-0000-0000-000000000000'), P.PHONETYPECODEID),
P.ISPRIMARY = PT.ISPRIMARY,
P.DONOTCALL = PT.DONOTCALL,
P.SEQUENCE = coalesce(nullif(PT.SEQUENCE,''), P.SEQUENCE),
P.COUNTRYID = PT.COUNTRYID,
P.SEASONALSTARTDATE = PT.SEASONALSTARTDATE,
P.SEASONALENDDATE=PT.SEASONALENDDATE,
P.STARTTIME=PT.STARTTIME,
P.ENDTIME=PT.ENDTIME,
P.STARTDATE = PT.STARTDATE,
P.ENDDATE = PT.ENDDATE,
P.CHANGEDBYID = @CHANGEAGENTID,
P.DATECHANGED = @CURRENTDATE,
P.INFOSOURCECODEID = PT.INFOSOURCECODEID,
P.DONOTTEXT = PT.DONOTTEXT,
P.INFOSOURCECOMMENTS = PT.INFOSOURCECOMMENTS
from
dbo.PHONE P
inner join @PHONENUMBERTABLE PT on P.NUMBER = PT.NUMBER
where
((P.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)) and (PT.UPDATEHOUSEHOLD = 1))
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID, NUMBER, ISPRIMARY, DONOTCALL, SEQUENCE, COUNTRYID,
SEASONALSTARTDATE, SEASONALENDDATE, STARTTIME, ENDTIME, STARTDATE, ENDDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID, DONOTTEXT, INFOSOURCECOMMENTS
)
select
@ID,
PT.PHONETYPECODEID, PT.NUMBER, PT.ISPRIMARY, PT.DONOTCALL, PT.SEQUENCE, PT.COUNTRYID,
PT.SEASONALSTARTDATE, PT.SEASONALENDDATE, PT.STARTTIME, PT.ENDTIME, PT.STARTDATE, PT.ENDDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, PT.INFOSOURCECODEID, PT.DONOTTEXT, PT.INFOSOURCECOMMENTS
from
@PHONENUMBERTABLE PT
where not exists(
select ID
from dbo.PHONE
where
CONSTITUENTID = @ID and
NUMBER = PT.NUMBER)
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID, NUMBER, ISPRIMARY, DONOTCALL, SEQUENCE, COUNTRYID,
SEASONALSTARTDATE, SEASONALENDDATE, STARTTIME, ENDTIME, STARTDATE, ENDDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID, DONOTTEXT, INFOSOURCECOMMENTS
)
select
MC.CONSTITID,
PT.PHONETYPECODEID, PT.NUMBER, PT.ISPRIMARY, PT.DONOTCALL, PT.SEQUENCE, PT.COUNTRYID,
PT.SEASONALSTARTDATE, PT.SEASONALENDDATE, PT.STARTTIME, PT.ENDTIME, PT.STARTDATE, PT.ENDDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, PT.INFOSOURCECODEID, PT.DONOTTEXT, PT.INFOSOURCECOMMENTS
from
@MATCHINGCONSTITUENTS MC
join @PHONENUMBERTABLE PT on PT.UPDATEHOUSEHOLD = 1
where
not exists(
select CONSTITUENTID from dbo.PHONE
where
CONSTITUENTID = MC.CONSTITID and
NUMBER = PT.NUMBER)
end
-----------------------------------------------------------------
--Social media accounts
--===============================================================
if exists(select top 1 * from @SOCIALMEDIAACCOUNTTABLE)
begin
update
dbo.SOCIALMEDIAACCOUNT
set
SOCIALMEDIAACCOUNT.URL = ST.URL,
SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID = coalesce(nullif(ST.SOCIALMEDIAACCOUNTTYPECODEID, '00000000-0000-0000-0000-000000000000'), SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID),
SOCIALMEDIAACCOUNT.INFOSOURCECODEID = coalesce(nullif(ST.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), SOCIALMEDIAACCOUNT.INFOSOURCECODEID),
SOCIALMEDIAACCOUNT.DONOTCONTACT = ST.DONOTCONTACT,
SOCIALMEDIAACCOUNT.SEQUENCE = coalesce(nullif(ST.SEQUENCE,''), SOCIALMEDIAACCOUNT.SEQUENCE),
SOCIALMEDIAACCOUNT.CHANGEDBYID = @CHANGEAGENTID,
SOCIALMEDIAACCOUNT.DATECHANGED = @CURRENTDATE
from
dbo.SOCIALMEDIAACCOUNT
inner join @SOCIALMEDIAACCOUNTTABLE ST on SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID = ST.SOCIALMEDIASERVICEID
and SOCIALMEDIAACCOUNT.USERID = ST.USERID
where
SOCIALMEDIAACCOUNT.CONSTITUENTID = @ID;
insert into dbo.SOCIALMEDIAACCOUNT
(
CONSTITUENTID,
SOCIALMEDIASERVICEID,
USERID,
URL,
SOCIALMEDIAACCOUNTTYPECODEID,
INFOSOURCECODEID,
DONOTCONTACT,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
ST.SOCIALMEDIASERVICEID,
ST.USERID,
ST.URL,
ST.SOCIALMEDIAACCOUNTTYPECODEID,
ST.INFOSOURCECODEID,
ST.DONOTCONTACT,
ST.SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@SOCIALMEDIAACCOUNTTABLE ST
where not exists(
select
ID
from
dbo.SOCIALMEDIAACCOUNT
where
CONSTITUENTID = @ID
and SOCIALMEDIASERVICEID = ST.SOCIALMEDIASERVICEID
and USERID = ST.USERID
)
end
-----------------------------------------------------------------
--Relationships
--===============================================================
if exists(select top 1 * from @RELATIONSHIPTABLE)
begin
--Note: We don't need an update statement since we are not implementing update relationship functionality through constituent update batch.
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
RT.RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@RELATIONSHIPTABLE RT
where not exists(
select
ID
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @ID
and RECIPROCALCONSTITUENTID = RT.RECIPROCALCONSTITUENTID
and RELATIONSHIPTYPECODEID = RT.RELATIONSHIPTYPECODEID
and RECIPROCALTYPECODEID = RT.RECIPROCALTYPECODEID
)
insert into dbo.RELATIONSHIP
(
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
RELATIONSHIPSETID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
RT.RECIPROCALCONSTITUENTID,
RT.RELATIONSHIPTYPECODEID,
RT.RECIPROCALTYPECODEID,
RT.STARTDATE,
RT.RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@RELATIONSHIPTABLE RT
where not exists(
select
ID
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @ID
and RECIPROCALCONSTITUENTID = RT.RECIPROCALCONSTITUENTID
and RELATIONSHIPTYPECODEID = RT.RELATIONSHIPTYPECODEID
and RECIPROCALTYPECODEID = RT.RECIPROCALTYPECODEID
)
end
-----------------------------------------------------------------
--Constituencies
--===============================================================
declare @HASPROSPECTCONSTITUENCY bit = 0;
if exists(select top 1 * from @CONSTITUENCYTABLE)
begin
--Insert new constituencies into the CONSTITUENCY table
declare @USERDEFINEDCONSTITUENCY xml;
set @USERDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = CONSTITUENCYSYSTEMNAME.ID
where
CONSTITUENCYSYSTEMNAME.ID is null
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_ADDFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;
declare @SYSTEMDEFINEDCONSTITUENCY xml;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_BOARDMEMBER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = '6093915E-ADE9-42BE-88AE-304731754467'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_STAFF_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'D2DCA06A-BE6E-40B3-B95D-59A926181923'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_FUNDRAISER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = '00E748FB-940D-4A7D-A133-C148B29410A8'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_PROSPECT_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
if @SYSTEMDEFINEDCONSTITUENCY is not null
set @HASPROSPECTCONSTITUENCY = 1;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'E7489703-3D63-4017-A2BC-88C092563C5D'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_VOLUNTEER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
if not exists (select 1 from dbo.VOLUNTEER where ID=@ID)
insert into dbo.VOLUNTEER
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--Committee
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') CONSTITUENCY(c)
where
CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799'
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_COMMITTEE_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
if not exists (select 1 from dbo.COMMITTEE where ID=@ID)
insert into dbo.COMMITTEE
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @HASPROSPECTCONSTITUENCY = 1 or @PROSPECTMANAGERFUNDRAISERID is not null
begin
insert into dbo.PROSPECT
(
ID,
PROSPECTMANAGERFUNDRAISERID,
ADDEDBYID, CHANGEDBYID,
DATEADDED, DATECHANGED
)
values
(
@ID,
@PROSPECTMANAGERFUNDRAISERID,
@CHANGEAGENTID, @CHANGEAGENTID,
@CURRENTDATE, @CURRENTDATE
);
--If prospect was implied via PROSPECTMANAGERFUNDRAISERID need to create date range entry
if @HASPROSPECTCONSTITUENCY = 0
begin
insert into dbo.PROSPECTDATERANGE
(
CONSTITUENTID,
ADDEDBYID, CHANGEDBYID,
DATEADDED, DATECHANGED
)
values
(
@ID,
@CHANGEAGENTID, @CHANGEAGENTID,
@CURRENTDATE, @CURRENTDATE
);
end
end
-----------------------------------------------------------------
--Primary business information
--===============================================================
declare @BUSINESS_ISNEWRECORD bit = 0
if @BUSINESS_ID is not null or @BUSINESS_NAME <> '' begin
--Update info for the business
if @BUSINESS_ID is not null begin
begin try
update B
set
B.CUSTOMIDENTIFIER = coalesce(nullif(@BUSINESS_LOOKUPID, ''), B.CUSTOMIDENTIFIER),
B.KEYNAME = coalesce(nullif(@BUSINESS_NAME, ''), B.KEYNAME),
B.WEBADDRESS = coalesce(nullif(@BUSINESS_WEBADDRESS, ''), B.WEBADDRESS),
B.CHANGEDBYID = @CHANGEAGENTID,
B.DATECHANGED = @CURRENTDATE
from
dbo.CONSTITUENT B
where
B.ID = @BUSINESS_ID
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
--The business didn't have any organization data, so make some
if not exists(select ID from dbo.ORGANIZATIONDATA where ID = @BUSINESS_ID)
insert into dbo.ORGANIZATIONDATA
(
ID,
CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED
)
values
(
@BUSINESS_ID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
update OD
set
OD.INDUSTRYCODEID = coalesce(nullif(@BUSINESS_INDUSTRYCODEID, '00000000-0000-0000-0000-000000000000'), OD.INDUSTRYCODEID),
OD.NUMEMPLOYEES = coalesce(nullif(@BUSINESS_NUMEMPLOYEES, '0'), OD.NUMEMPLOYEES),
OD.NUMSUBSIDIARIES = coalesce(nullif(@BUSINESS_NUMSUBSIDIARIES, '0'), OD.NUMSUBSIDIARIES),
OD.PARENTCORPID = coalesce(nullif(@BUSINESS_PARENTCORPID, '00000000-0000-0000-0000-000000000000'), OD.PARENTCORPID),
OD.CHANGEDBYID = @CHANGEAGENTID,
OD.DATECHANGED = @CURRENTDATE
from
dbo.ORGANIZATIONDATA OD
where
OD.ID = @BUSINESS_ID
end
--Add a new business relationship if one does not currently exist
else if @BUSINESS_NAME <> '' begin
--Insert a new business into the CONSTITUENT table if an existing one wasn't chosen
set @BUSINESS_ID = newid()
set @BUSINESS_ISNEWRECORD = 1
begin try
insert into dbo.CONSTITUENT
(
ID,
CUSTOMIDENTIFIER,
KEYNAME,
WEBADDRESS,
ISORGANIZATION,
CHANGEDBYID,
ADDEDBYID,
DATECHANGED,
DATEADDED
)
values
(
@BUSINESS_ID,
coalesce(@BUSINESS_LOOKUPID, ''),
coalesce(@BUSINESS_NAME, ''),
coalesce(@BUSINESS_WEBADDRESS, ''),
1,
@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
--Insert new sites into the constituent site table
insert into dbo.CONSTITUENTSITE
(
ID,
CONSTITUENTID,
SITEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(), @BUSINESS_ID, CONSTITUENTSITESTABLE.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
@CONSTITUENTSITESTABLE CONSTITUENTSITESTABLE
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
NUMEMPLOYEES,
NUMSUBSIDIARIES,
PARENTCORPID,
CHANGEDBYID,
ADDEDBYID,
DATECHANGED,
DATEADDED
)
values
(
@BUSINESS_ID,
@BUSINESS_INDUSTRYCODEID,
coalesce(@BUSINESS_NUMEMPLOYEES, 0),
coalesce(@BUSINESS_NUMSUBSIDIARIES, 0),
@BUSINESS_PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
--Add a new relationship for the business
begin try
set @SETID = newid();
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RELATIONSHIP
(
RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID, ISPRIMARYBUSINESS, RELATIONSHIPSETID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID, @BUSINESS_ID,
@BUSINESS_RELATIONSHIPTYPECODEID, @BUSINESS_RECIPROCALTYPECODEID, 1, @SETID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end try
begin catch
exec dbo.USP_RAISE_ERROR 3;
return 1;
end catch
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @BUSINESS_ID, @BUSINESS_RELATIONSHIPTYPECODEID, @BUSINESS_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
--If a primary address still does not exist (an address was not updated) then
--insert a new one. Do the same for email and phone number.
if @BUSINESS_ISNEWRECORD = 1
begin
if @BUSINESS_COUNTRYID is not null
begin
insert into dbo.ADDRESS
(
CONSTITUENTID,
ADDRESSBLOCK,
ADDRESSTYPECODEID,
CART,
CITY,
COUNTRYID,
DONOTMAIL,
DONOTMAILREASONCODEID,
DPC,
ISPRIMARY,
LOT,
STATEID,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BUSINESS_ID,
coalesce(@BUSINESS_ADDRESSBLOCK, ''),
@BUSINESS_ADDRESSTYPECODEID,
coalesce(@BUSINESS_CART, ''),
coalesce(@BUSINESS_CITY, ''),
@BUSINESS_COUNTRYID,
@BUSINESS_DONOTMAIL,
@BUSINESS_DONOTMAILREASONCODEID,
coalesce(@BUSINESS_DPC, ''),
1,
coalesce(@BUSINESS_LOT, ''),
@BUSINESS_STATEID,
coalesce(@BUSINESS_POSTCODE, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
if @BUSINESS_EMAILADDRESS is not null
and @BUSINESS_EMAILADDRESS <> ''
begin
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESS,
EMAILADDRESSTYPECODEID,
ISPRIMARY,
STARTDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@BUSINESS_ID,
@BUSINESS_EMAILADDRESS,
@BUSINESS_EMAILADDRESSTYPECODEID,
1,
@BUSINESS_EMAILADDRESSSTARTDATE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end
if @BUSINESS_PHONENUMBER is not null
and @BUSINESS_PHONENUMBER <> ''
begin
insert into dbo.PHONE
(
CONSTITUENTID,
NUMBER, PHONETYPECODEID, ISPRIMARY, COUNTRYID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@BUSINESS_ID,
@BUSINESS_PHONENUMBER, @BUSINESS_PHONETYPECODEID, 1, @BUSINESS_PHONE_COUNTRYID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end
end
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @BUSINESS_ID, @ID, null, null,
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID, @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR, @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID;
end
----------------------------------------------------------------------------------------
-- SHL BBIS BUG 275573; Registrants should not receive emails unless otherwise specified
--======================================================================================
if @REQUESTSNOEMAIL = 1
begin
--Retrieve the ID of the solicit code being used to flag do not email.
declare @NOEMAILSOLICITCODEID uniqueidentifier
select top 1 @NOEMAILSOLICITCODEID=DONOTEMAILSOLICITCODEID
from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @NOEMAILSOLICITCODEID is not null
begin
exec dbo.USP_CONSTITUENTSOLICITCODE_ADD null, @NOEMAILSOLICITCODEID, @ID, null, null, '', @CHANGEAGENTID;
end
end
----------------------------------------------------------------------------------------
-- ADC WI 645666; SEARCHCONSTITUENT updates last to capture address data
--======================================================================================
if isnull(context_info(),0x) = 0xE2DF375A033A104382689B8EAC5165AD
begin
insert into dbo.SEARCHCONSTITUENT
(
ID,
CONSTITUENTID,
ADDRESSID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NAMETYPECODE,
TITLECODEID,
SUFFIXCODEID,
COUNTRYID,
POSTCODE,
STREETNUMBER,
STREETNAME,
ISORGANIZATION,
ISGROUP,
ALIASID
)
select
newid(),
CONSITTUENTALLNAMES.ID,
ADDRESS.ID,
CONSITTUENTALLNAMES.KEYNAME,
CONSITTUENTALLNAMES.FIRSTNAME,
CONSITTUENTALLNAMES.MIDDLENAME,
CONSITTUENTALLNAMES.NAMETYPECODE,
CONSITTUENTALLNAMES.TITLECODEID,
CONSITTUENTALLNAMES.SUFFIXCODEID,
ADDRESS.COUNTRYID,
case when ADDRESS.POSTCODE is null
then ''
when ltrim(rtrim(ADDRESS.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
then left(ltrim(ADDRESS.POSTCODE), 5)
else
ltrim(rtrim(ADDRESS.POSTCODE))
end,
isnull(PARSEDADDRESS.STREETNUMBER, ''),
coalesce(PARSEDADDRESS.STREETNAME, ab.CLEANADDRESSBLOCK,''),
CONSITTUENTALLNAMES.ISORGANIZATION,
CONSITTUENTALLNAMES.ISGROUP,
CONSITTUENTALLNAMES.ALIASID
from dbo.V_CONSTITUENTALLNAMES CONSITTUENTALLNAMES
left join dbo.ADDRESS on
ADDRESS.CONSTITUENTID = CONSITTUENTALLNAMES.ID
outer apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
ADDRESS.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),' ',' ')))) CLEANADDRESSBLOCK) ab
outer apply (select left(CLEANADDRESSBLOCK,charindex(' ', CLEANADDRESSBLOCK) -1) STREETNUMBER,
right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex(' ',CLEANADDRESSBLOCK)) STREETNAME
where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANADDRESSBLOCK) = 1
and charindex(' ', CLEANADDRESSBLOCK) <= 13) PARSEDADDRESS
where
CONSITTUENTALLNAMES.ID = @ID
and CONSITTUENTALLNAMES.KEYNAME <> '';
-- If spouse is present/added, we should keep their fields updated
if @SPOUSE_ID is not null and @REMOVESPOUSE = 0
begin
insert into dbo.SEARCHCONSTITUENT
(
ID,
CONSTITUENTID,
ADDRESSID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NAMETYPECODE,
TITLECODEID,
SUFFIXCODEID,
COUNTRYID,
POSTCODE,
STREETNUMBER,
STREETNAME,
ISORGANIZATION,
ISGROUP,
ALIASID
)
select
newid(),
CONSITTUENTALLNAMES.ID,
ADDRESS.ID,
CONSITTUENTALLNAMES.KEYNAME,
CONSITTUENTALLNAMES.FIRSTNAME,
CONSITTUENTALLNAMES.MIDDLENAME,
CONSITTUENTALLNAMES.NAMETYPECODE,
CONSITTUENTALLNAMES.TITLECODEID,
CONSITTUENTALLNAMES.SUFFIXCODEID,
ADDRESS.COUNTRYID,
case when ADDRESS.POSTCODE is null
then ''
when ltrim(rtrim(ADDRESS.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
then left(ltrim(ADDRESS.POSTCODE), 5)
else
ltrim(rtrim(ADDRESS.POSTCODE))
end,
isnull(PARSEDADDRESS.STREETNUMBER, ''),
coalesce(PARSEDADDRESS.STREETNAME, ab.CLEANADDRESSBLOCK,''),
CONSITTUENTALLNAMES.ISORGANIZATION,
CONSITTUENTALLNAMES.ISGROUP,
CONSITTUENTALLNAMES.ALIASID
from dbo.V_CONSTITUENTALLNAMES CONSITTUENTALLNAMES
left join dbo.ADDRESS on
ADDRESS.CONSTITUENTID = CONSITTUENTALLNAMES.ID
outer apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
ADDRESS.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),' ',' ')))) CLEANADDRESSBLOCK) ab
outer apply (select left(CLEANADDRESSBLOCK,charindex(' ', CLEANADDRESSBLOCK) -1) STREETNUMBER,
right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex(' ',CLEANADDRESSBLOCK)) STREETNAME
where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANADDRESSBLOCK) = 1
and charindex(' ', CLEANADDRESSBLOCK) <= 13) PARSEDADDRESS
where
CONSITTUENTALLNAMES.ID = @SPOUSE_ID
and CONSITTUENTALLNAMES.KEYNAME <> '';
end
-- If business is present/added, we should keep their fields updated
if @BUSINESS_ID is not null
begin
insert into dbo.SEARCHCONSTITUENT
(
ID,
CONSTITUENTID,
ADDRESSID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NAMETYPECODE,
TITLECODEID,
SUFFIXCODEID,
COUNTRYID,
POSTCODE,
STREETNUMBER,
STREETNAME,
ISORGANIZATION,
ISGROUP,
ALIASID
)
select
newid(),
CONSITTUENTALLNAMES.ID,
ADDRESS.ID,
CONSITTUENTALLNAMES.KEYNAME,
CONSITTUENTALLNAMES.FIRSTNAME,
CONSITTUENTALLNAMES.MIDDLENAME,
CONSITTUENTALLNAMES.NAMETYPECODE,
CONSITTUENTALLNAMES.TITLECODEID,
CONSITTUENTALLNAMES.SUFFIXCODEID,
ADDRESS.COUNTRYID,
case when ADDRESS.POSTCODE is null
then ''
when ltrim(rtrim(ADDRESS.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
then left(ltrim(ADDRESS.POSTCODE), 5)
else
ltrim(rtrim(ADDRESS.POSTCODE))
end,
isnull(PARSEDADDRESS.STREETNUMBER, ''),
coalesce(PARSEDADDRESS.STREETNAME, ab.CLEANADDRESSBLOCK,''),
CONSITTUENTALLNAMES.ISORGANIZATION,
CONSITTUENTALLNAMES.ISGROUP,
CONSITTUENTALLNAMES.ALIASID
from dbo.V_CONSTITUENTALLNAMES CONSITTUENTALLNAMES
left join dbo.ADDRESS on
ADDRESS.CONSTITUENTID = CONSITTUENTALLNAMES.ID
outer apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
ADDRESS.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),' ',' ')))) CLEANADDRESSBLOCK) ab
outer apply (select left(CLEANADDRESSBLOCK,charindex(' ', CLEANADDRESSBLOCK) -1) STREETNUMBER,
right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex(' ',CLEANADDRESSBLOCK)) STREETNAME
where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANADDRESSBLOCK) = 1
and charindex(' ', CLEANADDRESSBLOCK) <= 13) PARSEDADDRESS
where
CONSITTUENTALLNAMES.ID = @BUSINESS_ID
and CONSITTUENTALLNAMES.KEYNAME <> '';
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
end
return 0;