USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHTEMPLATE_15
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @VALIDATEONLY | bit | IN | |
| @CONSTITUENTTYPECODE | tinyint | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @GENDERCODE | tinyint | IN | |
| @GIVESANONYMOUSLY | bit | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @LOOKUPID | nvarchar(100) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @NICKNAME | nvarchar(50) | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @WEBADDRESS | UDT_WEBADDRESS | IN | |
| @ALTERNATELOOKUPIDS | xml | IN | |
| @SECURITYATTRIBUTES | xml | IN | |
| @DECEASED | bit | IN | |
| @DECEASEDDATE | UDT_FUZZYDATE | IN | |
| @CONSTITUENT_SITEID | uniqueidentifier | IN | |
| @INTERESTS | xml | IN | |
| @PROSPECTMANAGERFUNDRAISERID | uniqueidentifier | IN | |
| @ADDRESSES | xml | IN | |
| @EMAILADDRESSES | xml | IN | |
| @PHONES | xml | IN | |
| @CONSTITUENCIES | xml | IN | |
| @GROUPTYPEID | uniqueidentifier | IN | |
| @GROUPDESCRIPTION | nvarchar(300) | IN | |
| @GROUPSTARTDATE | date | IN | |
| @ORG_INDUSTRYCODEID | uniqueidentifier | IN | |
| @ORG_NUMEMPLOYEES | int | IN | |
| @ORG_NUMSUBSIDIARIES | int | IN | |
| @ORG_PARENTCORPID | uniqueidentifier | IN | |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | |
| @SPOUSE_ID | uniqueidentifier | IN | |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | |
| @SPOUSE_GENDERCODE | tinyint | IN | |
| @SPOUSE_LASTNAME | nvarchar(100) | IN | |
| @SPOUSE_LOOKUPID | nvarchar(100) | IN | |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | IN | |
| @BUSINESS_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_CART | nvarchar(10) | IN | |
| @BUSINESS_CITY | nvarchar(50) | IN | |
| @BUSINESS_COUNTRYID | uniqueidentifier | IN | |
| @BUSINESS_DONOTMAIL | bit | IN | |
| @BUSINESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @BUSINESS_DPC | nvarchar(8) | IN | |
| @BUSINESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @BUSINESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_INDUSTRYCODEID | uniqueidentifier | IN | |
| @BUSINESS_LOOKUPID | nvarchar(100) | IN | |
| @BUSINESS_LOT | nvarchar(5) | IN | |
| @BUSINESS_ID | uniqueidentifier | IN | |
| @BUSINESS_NAME | nvarchar(100) | IN | |
| @BUSINESS_NUMEMPLOYEES | int | IN | |
| @BUSINESS_NUMSUBSIDIARIES | int | IN | |
| @BUSINESS_PARENTCORPID | uniqueidentifier | IN | |
| @BUSINESS_PHONENUMBER | nvarchar(100) | IN | |
| @BUSINESS_PHONE_COUNTRYID | uniqueidentifier | IN | |
| @BUSINESS_PHONETYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_STATEID | uniqueidentifier | IN | |
| @BUSINESS_WEBADDRESS | UDT_WEBADDRESS | IN | |
| @BUSINESS_POSTCODE | nvarchar(12) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST | uniqueidentifier | IN | |
| @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST | uniqueidentifier | IN | |
| @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST | uniqueidentifier | IN | |
| @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST | uniqueidentifier | IN | |
| @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @BUSINESS_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_EMAILADDRESSSTARTDATE | date | IN | |
| @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @SPOUSE_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @CONSTITUENT_SITES | xml | IN | |
| @BATCHOWNERID | uniqueidentifier | IN | |
| @BATCHROWID | uniqueidentifier | IN | |
| @SPOUSE_ALTERNATELOOKUPIDS | xml | IN | |
| @SPOUSE_IMPORTLOOKUPID | nvarchar(100) | IN | |
| @ROWFROMBATCHUI | bit | IN | |
| @BBNCTRANID | int | IN | |
| @REMOVESPOUSE | bit | IN | |
| @SOCIALMEDIAACCOUNTS | xml | IN | |
| @RELATIONSHIPS | xml | IN | |
| @NETCOMMUNITYTRANSACTIONPROCESSORID | uniqueidentifier | IN | |
| @NEWADDRESSENDDATECODE | tinyint | IN | |
| @CREATEHISTORICALNAMECODE | tinyint | IN | |
| @NEWPHONEENDDATECODE | tinyint | IN | |
| @NEWEMAILENDDATECODE | tinyint | IN | |
| @NAMEFORMATS | xml | IN | |
| @REQUESTSNOEMAIL | bit | IN | |
| @SOLICITCODES | xml | IN | |
| @GENDERCODEID | uniqueidentifier | IN | |
| @SPOUSE_GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHTEMPLATE_15
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@VALIDATEONLY bit,
@CONSTITUENTTYPECODE tinyint,
@BIRTHDATE dbo.UDT_FUZZYDATE,
@FIRSTNAME nvarchar(50),
@GENDERCODE tinyint,
@GIVESANONYMOUSLY bit,
@KEYNAME nvarchar(100),
@LOOKUPID nvarchar(100),
@MAIDENNAME nvarchar(100),
@MIDDLENAME nvarchar(50),
@NICKNAME nvarchar(50),
@SUFFIXCODEID uniqueidentifier,
@TITLECODEID uniqueidentifier,
@WEBADDRESS dbo.UDT_WEBADDRESS,
@ALTERNATELOOKUPIDS xml,
@SECURITYATTRIBUTES xml,
@DECEASED bit,
@DECEASEDDATE dbo.UDT_FUZZYDATE,
@CONSTITUENT_SITEID uniqueidentifier,
@INTERESTS xml,
@PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
--@EDUCATIONALINVOLVEMENTS xml,
@ADDRESSES xml,
@EMAILADDRESSES xml,
@PHONES xml,
@CONSTITUENCIES xml,
@GROUPTYPEID uniqueidentifier,
@GROUPDESCRIPTION nvarchar(300),
@GROUPSTARTDATE date,
@ORG_INDUSTRYCODEID uniqueidentifier,
@ORG_NUMEMPLOYEES int,
@ORG_NUMSUBSIDIARIES int,
@ORG_PARENTCORPID uniqueidentifier,
@MARITALSTATUSCODEID uniqueidentifier,
@SPOUSE_ID uniqueidentifier,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
@SPOUSE_FIRSTNAME nvarchar(50),
@SPOUSE_GENDERCODE tinyint,
@SPOUSE_LASTNAME nvarchar(100),
@SPOUSE_LOOKUPID nvarchar(100),
@SPOUSE_MAIDENNAME nvarchar(100),
@SPOUSE_MIDDLENAME nvarchar(50),
@SPOUSE_NICKNAME nvarchar(50),
@SPOUSE_SUFFIXCODEID uniqueidentifier,
@SPOUSE_TITLECODEID uniqueidentifier,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
@BUSINESS_ADDRESSBLOCK nvarchar(150),
@BUSINESS_ADDRESSTYPECODEID uniqueidentifier,
@BUSINESS_CART nvarchar(10),
@BUSINESS_CITY nvarchar(50),
@BUSINESS_COUNTRYID uniqueidentifier,
@BUSINESS_DONOTMAIL bit,
@BUSINESS_DONOTMAILREASONCODEID uniqueidentifier,
@BUSINESS_DPC nvarchar(8),
@BUSINESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@BUSINESS_EMAILADDRESSTYPECODEID uniqueidentifier,
@BUSINESS_INDUSTRYCODEID uniqueidentifier,
@BUSINESS_LOOKUPID nvarchar(100),
@BUSINESS_LOT nvarchar(5),
@BUSINESS_ID uniqueidentifier,
@BUSINESS_NAME nvarchar(100),
@BUSINESS_NUMEMPLOYEES int,
@BUSINESS_NUMSUBSIDIARIES int,
@BUSINESS_PARENTCORPID uniqueidentifier,
@BUSINESS_PHONENUMBER nvarchar(100),
@BUSINESS_PHONE_COUNTRYID uniqueidentifier,
@BUSINESS_PHONETYPECODEID uniqueidentifier,
@BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,
@BUSINESS_RECIPROCALTYPECODEID uniqueidentifier,
@BUSINESS_STATEID uniqueidentifier,
@BUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS,
@BUSINESS_POSTCODE nvarchar(12),
@CURRENTAPPUSERID uniqueidentifier,
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
@BUSINESS_EMAILADDRESSSTARTDATE date,
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
@CONSTITUENT_SITES xml,
@BATCHOWNERID uniqueidentifier,
@BATCHROWID uniqueidentifier,
@SPOUSE_ALTERNATELOOKUPIDS xml,
@SPOUSE_IMPORTLOOKUPID nvarchar(100),
@ROWFROMBATCHUI bit,
@BBNCTRANID int,
@REMOVESPOUSE bit,
@SOCIALMEDIAACCOUNTS xml,
@RELATIONSHIPS xml,
@NETCOMMUNITYTRANSACTIONPROCESSORID uniqueidentifier,
@NEWADDRESSENDDATECODE tinyint,
@CREATEHISTORICALNAMECODE tinyint,
@NEWPHONEENDDATECODE tinyint,
@NEWEMAILENDDATECODE tinyint,
@NAMEFORMATS xml,
@REQUESTSNOEMAIL bit,
@SOLICITCODES xml,
@GENDERCODEID uniqueidentifier,
@SPOUSE_GENDERCODEID uniqueidentifier
) as
set nocount on;
declare
@ErrorMessage nvarchar(1000),
@InfoMsg nvarchar(100),
@ErrorSeverity int,
@ErrorState int;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SETID uniqueidentifier = newid();
declare @CURRENTCONSTITUENTTYPECODE tinyint;
declare @NULLVALUE uniqueidentifier = newid();
declare @NULLDATE DATE = '9999-01-01';
declare @dupflag tinyint = 0;
select @CURRENTCONSTITUENTTYPECODE = case
when C.ISORGANIZATION = 1 then 1
when (C.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0) then 2
when (C.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 1) then 3
else 0
end
from dbo.CONSTITUENT C
left join dbo.GROUPDATA on C.ID = GROUPDATA.ID
where C.ID= @ID
if @CURRENTCONSTITUENTTYPECODE <> @CONSTITUENTTYPECODE
begin
raiserror('BBERR_ORIGINAL_ERROR:547',1,11);
raiserror('BBERR_CONSTITUENTTYPE', 13, 1);
end
-- Verify the solicit codes are valid
if @SOLICITCODES is not null
begin
-- address the auto end date issues for consent based SC
exec USP_CONSTITUENTUPDATEBATCH_ADJUSTSOLICITCODEDATERANGES @ID, @SOLICITCODES, @CHANGEAGENTID;
-- reload from table to reflect changes made above
select @SOLICITCODES = dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_TOITEMLISTXML(@BATCHROWID);
-- validate the SC
declare @SOLICITCODESTABLEALIDATION table
(
ID uniqueidentifier,
STARTDATE datetime,
ENDDATE datetime,
SOLICITCODEID uniqueidentifier
)
insert into @SOLICITCODESTABLEALIDATION
(
ID,
STARTDATE,
ENDDATE,
SOLICITCODEID
)
select
case when ID is null then newid() else ID end,
STARTDATE,
ENDDATE,
SOLICITCODEID
from dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES)
-- Check for solicit codes whose end date is before the start date
if exists ( select 1
from @SOLICITCODESTABLEALIDATION
where
STARTDATE is not null and
STARTDATE > ENDDATE)
raiserror('BBERR_SOLICITCODES_STARTENDDATE', 13, 1)
-- Check for records with the same solicit code and overlapping dates
if exists ( select 1
from @SOLICITCODESTABLEALIDATION SOLICITCODEONE
inner join @SOLICITCODESTABLEALIDATION SOLICITCODETWO on
SOLICITCODEONE.ID <> SOLICITCODETWO.ID and
SOLICITCODEONE.SOLICITCODEID = SOLICITCODETWO.SOLICITCODEID and
dbo.UFN_DATES_AREDATESOVERLAPPING(SOLICITCODEONE.STARTDATE, SOLICITCODEONE.ENDDATE, SOLICITCODETWO.STARTDATE, SOLICITCODETWO.ENDDATE) = 1)
raiserror('BBERR_SOLICITCODES_NOOVERLAPPINGDATES', 13, 1)
end
-----------------------------------------------------------------
--Get current Household/spouse information
--===============================================================
declare @CURRENTSPOUSE uniqueidentifier = null;
declare @CURRENTHOUSEHOLD uniqueidentifier = null;
select
@CURRENTSPOUSE = R.RECIPROCALCONSTITUENTID
from
dbo.RELATIONSHIP R
where
R.ISSPOUSE = 1 and
R.RELATIONSHIPCONSTITUENTID = @ID;
select
@CURRENTHOUSEHOLD = GD.ID
from
dbo.GROUPMEMBER GM
inner join
dbo.GROUPDATA GD on GD.ID = GM.GROUPID and GD.GROUPTYPECODE = 0
where
GM.MEMBERID = @ID
and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1;
-----------------------------------------------------------------
--Get current Business information
--===============================================================
declare @OLD_BUSINESS_ID uniqueidentifier
declare @OLD_BUSINESS_NAME nvarchar(100)
declare @OLD_BUSINESS_LOOKUPID nvarchar(100)
select
@OLD_BUSINESS_ID = R.RECIPROCALCONSTITUENTID,
@OLD_BUSINESS_NAME = B.KEYNAME,
@OLD_BUSINESS_LOOKUPID = B.LOOKUPID
from
dbo.RELATIONSHIP R
join dbo.CONSTITUENT B on B.ID = R.RECIPROCALCONSTITUENTID
where
R.RELATIONSHIPCONSTITUENTID = @ID and ISPRIMARYBUSINESS = 1
--If no business was specified but the lookupids match use the old business id
if (@BUSINESS_ID is null and @OLD_BUSINESS_LOOKUPID = @BUSINESS_LOOKUPID)
set @BUSINESS_ID = @OLD_BUSINESS_ID
-- Perform validation
if exists (select 1 from dbo.CONSTITUENT where LOOKUPID = @LOOKUPID and ID <> @ID)
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_PRIMARYSOFTCREDITMATCHFACTOR is null
set @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = 100.0;
if @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_BUSINESS_INVALIDRECIPROCALMATCHFACTOR', 13, 1);
end
if @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR is null
set @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = 100.0;
if @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_SPOUSE_INVALIDPRIMARYMATCHFACTOR', 13, 1);
end
if @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR is null
set @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = 100.0;
if @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
begin
if @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR > 100
raiserror('BBERR_SPOUSE_INVALIDRECIPROCALMATCHFACTOR', 13, 1);
end
if @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR is null
set @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = 100.0;
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
if @CURRENTSPOUSE is not null and (@CURRENTSPOUSE <> @SPOUSE_ID or (@SPOUSE_LASTNAME <> '' and @SPOUSE_ID is null)) begin
raiserror('Constituent entered has an existing spouse that is different than the spouse entered in the row.',13,1);
end
if @SPOUSE_ID is not null and (@CURRENTSPOUSE is null or @CURRENTSPOUSE <> @SPOUSE_ID)
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
-- Check for existing spouse relationships and existing household memberships
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 @CURRENTHOUSEHOLD is not null and not exists(
select
GM.ID
from
dbo.GROUPMEMBER GM
where
GM.GROUPID = @CURRENTHOUSEHOLD and
GM.MEMBERID = @SPOUSE_ID) begin
raiserror('Constituent entered is part of an existing household that does not include the listed spouse.',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 and
not exists(
select
ID
from
dbo.GROUPMEMBER
where
GROUPID = GD.ID and
MEMBERID = @ID)) 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
declare @MAIDENNAMECHANGED bit = 0;
select @MAIDENNAMECHANGED = case when @MAIDENNAME = CONSTITUENT.MAIDENNAME then 0 else 1 end
from dbo.CONSTITUENT
where CONSTITUENT.ID = @ID;
declare @SPOUSE_MAIDENNAMECHANGED bit = 0;
select @SPOUSE_MAIDENNAMECHANGED = case when @SPOUSE_MAIDENNAME = CONSTITUENT.MAIDENNAME then 0 else 1 end
from dbo.CONSTITUENT
where CONSTITUENT.ID = @SPOUSE_ID;
--WI 196521/204149 If either the Addresses, Phones or Email addresses fields aren't available in this batch, do not
--try to update non existent update fields.
declare @ISADDRESSES bit = 1
declare @ISPHONES bit = 1
declare @ISEMAILADDRESSES bit = 1
declare @ISSOCIALMEDIAACCOUNTS bit = 1
declare @ISRELATIONSHIPS bit = 0;
declare @FORMDEFINITION XML
declare @BATCHTYPEID uniqueidentifier
--Interrogate batch form definition to see if the fields are in batch.
select @FORMDEFINITION = BATCH.FORMDEFINITIONXML,
@BATCHTYPEID = BATCHTEMPLATE.BATCHTYPECATALOGID
from dbo.BATCHCONSTITUENTUPDATE
inner join dbo.BATCH on BATCH.ID = BATCHCONSTITUENTUPDATE.BATCHID
inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
where BATCHCONSTITUENTUPDATE.ID = @BATCHROWID;
-- PBI 237406: ERB now saves edits of existing constituents to CUB and calls this procedure to commit them.
-- These CUB rows are associated with an ERB template, not a CUB template, and thus won't have these fields.
-- Unless we're calling from a CUB batch and these checks are valid, go ahead and process the collections.
if @BATCHTYPEID = '877DBC83-98F9-4008-98A0-902FDB35E819' -- Constituent Update Batch
begin
SELECT @ISADDRESSES = @FORMDEFINITION.exist('declare namespace pz="bb_appfx_commontypes";
/pz:FormMetaData/pz:FormFields/pz:FormField[@FieldID="ADDRESSES"]');
SELECT @ISPHONES = @FORMDEFINITION.exist('declare namespace pz="bb_appfx_commontypes";
/pz:FormMetaData/pz:FormFields/pz:FormField[@FieldID="PHONES"]');
SELECT @ISEMAILADDRESSES = @FORMDEFINITION.exist('declare namespace pz="bb_appfx_commontypes";
/pz:FormMetaData/pz:FormFields/pz:FormField[@FieldID="EMAILADDRESSES"]');
select @ISSOCIALMEDIAACCOUNTS = @FORMDEFINITION.exist('declare namespace pz="bb_appfx_commontypes";
/pz:FormMetaData/pz:FormFields/pz:FormField[@FieldID="SOCIALMEDIAACCOUNTS"]');
select @ISRELATIONSHIPS = @FORMDEFINITION.exist('declare namespace pz="bb_appfx_commontypes";
/pz:FormMetaData/pz:FormFields/pz:FormField[@FieldID="RELATIONSHIPS"]');
end
--Fill a table with all of the constituent's addresses
if @ISADDRESSES = 1
begin
declare @ADDRESSTABLE table
(
ADDRESSID uniqueidentifier,
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,
ROWNUMBER smallint,
INFOSOURCECOMMENTS nvarchar(256),ID uniqueidentifier
);
with ADDS as (
select *
from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_FROMITEMLISTXML(@ADDRESSES)
)
insert into @ADDRESSTABLE
(
ADDRESSID,
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,
ROWNUMBER,
INFOSOURCECOMMENTS,ID
)
select
case
when adds.ADDRESSID is not null then adds.ADDRESSID
else ( select top 1 ID
from dbo.ADDRESS a
where a.CONSTITUENTID = @ID
and not exists(select 'x' from ADDS where ADDRESSID = a.ID)
and coalesce(A.ADDRESSBLOCK, '') = coalesce(ADDS.ADDRESSBLOCK, '') and
coalesce(A.ADDRESSTYPECODEID, @NULLVALUE) = coalesce(ADDS.ADDRESSTYPECODEID, @NULLVALUE) and
coalesce(A.CITY, '') = coalesce(ADDS.CITY, '') and
coalesce(A.COUNTRYID, @NULLVALUE) = coalesce(ADDS.COUNTRYID,@NULLVALUE) and
coalesce(A.STATEID, @NULLVALUE) = coalesce(ADDS.STATEID, @NULLVALUE) and
coalesce(A.POSTCODE, '') = coalesce(ADDS.POSTCODE, '')) end,
ADDS.ADDRESSBLOCK,
ADDS.ENDDATE,
ADDS.STARTDATE,
dbo.UFN_DATE_GETEARLIESTTIME(ADDS.HISTORICALSTARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(ADDS.HISTORICALENDDATE),
ADDS.ADDRESSTYPECODEID,
ADDS.CART,
ADDS.CERTIFICATIONDATA,
ADDS.CITY,
ADDS.CONGRESSIONALDISTRICTCODEID,
ADDS.COUNTRYID,
ADDS.COUNTYCODEID,
case
when ADDS.HISTORICALENDDATE is not null then
1
else
ADDS.DONOTMAIL
end as DONOTMAIL,
case
when ADDS.DONOTMAIL = 1 or ADDS.HISTORICALENDDATE is not null then
ADDS.DONOTMAILREASONCODEID
else
null
end as DONOTMAILREASONCODEID,
ADDS.DPC, ADDS.INFOSOURCECODEID,
ADDS.ISPRIMARY,
ADDS.LASTVALIDATIONATTEMPTDATE,
ADDS.LOCALPRECINCTCODEID,
ADDS.LOT,
ADDS.OMITFROMVALIDATION,
ADDS.REGIONCODEID,
ADDS.STATEID,
ADDS.STATEHOUSEDISTRICTCODEID,
ADDS.STATESENATEDISTRICTCODEID,
ADDS.VALIDATIONMESSAGE,
ADDS.POSTCODE,
ADDS.SEQUENCE,
ADDS.UPDATEHOUSEHOLD,
ROW_NUMBER() over(order by ADDS.ISPRIMARY desc),
ADDS.INFOSOURCECOMMENTS,
ADDS.ID
from
ADDS;
if exists(select top 1 * from @ADDRESSTABLE)
begin
-- #253763 Check to see if there are duplicate addresses.
declare @DUPESTABLE table(
ADDRESSBLOCK nvarchar(150),
ADDRESSTYPECODEID uniqueidentifier,
CITY nvarchar(50),
COUNTRYID uniqueidentifier,
STATEID uniqueidentifier,
POSTCODE nvarchar(12),
RECORDLOCATION tinyint
)
insert into @DUPESTABLE
select ADDRESSBLOCK, ADDRESSTYPECODEID, CITY, COUNTRYID, STATEID, POSTCODE, min(RECORDLOCATION) as MINRECORDLOCATION
from(
-- RobertDi 2/27/2014 - Split this into two non-overlapping statements to avoid performance problems
-- caused by an "or" in the where clause. See Bug 356074.
select
NEW.ADDRESSBLOCK,
NEW.ADDRESSTYPECODEID,
NEW.CITY,
NEW.COUNTRYID,
NEW.STATEID,
NEW.POSTCODE,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
1 + (case when OLD.ID is not null then 2 else 0 end) as RECORDLOCATION
from (select * from @ADDRESSTABLE where HISTORICALENDDATE is null) as NEW
full outer join (
select ID
from dbo.ADDRESS
where HISTORICALENDDATE is null
) as OLD
on OLD.ID = NEW.ADDRESSID
where NEW.ID is not null
union all
select
OLD.ADDRESSBLOCK,
OLD.ADDRESSTYPECODEID,
OLD.CITY,
OLD.COUNTRYID,
OLD.STATEID,
OLD.POSTCODE,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
(case when OLD.ID is not null then 2 else 0 end) as RECORDLOCATION
from (select * from @ADDRESSTABLE where HISTORICALENDDATE is null) as NEW
full outer join (
select ID, ADDRESSBLOCK, ADDRESSTYPECODEID, CITY, COUNTRYID, STATEID, POSTCODE, CONSTITUENTID
from dbo.ADDRESS
where HISTORICALENDDATE is null
) as OLD
on OLD.ID = NEW.ADDRESSID
where OLD.CONSTITUENTID = @ID and NEW.ID is null
) adr
group by adr.ADDRESSBLOCK, adr.ADDRESSTYPECODEID, adr.CITY, adr.COUNTRYID, adr.STATEID, adr.POSTCODE
having COUNT(*) > 1 and
(min(RECORDLOCATION)<>2 or max(RECORDLOCATION)<>2) -- at least one of the records must be in the batch
if exists(select 'x' from @DUPESTABLE where RECORDLOCATION = 1)
raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1)
else if exists (select 'x'
from @DUPESTABLE d
inner join @ADDRESSTABLE at on
coalesce(at.ADDRESSBLOCK,'') = coalesce(d.ADDRESSBLOCK,'') and
coalesce(at.ADDRESSTYPECODEID,@NULLVALUE) = coalesce(d.ADDRESSTYPECODEID,@NULLVALUE) and
coalesce(at.CITY,'') = coalesce(d.CITY,'') and
coalesce(at.COUNTRYID,@NULLVALUE) = coalesce(d.COUNTRYID,@NULLVALUE) and
coalesce(at.STATEID,@NULLVALUE) = coalesce(d.STATEID,@NULLVALUE) and
coalesce(at.POSTCODE,'') = coalesce(d.POSTCODE,'')
inner join dbo.ADDRESS a on a.ID = at.ADDRESSID
where ( coalesce(at.ADDRESSBLOCK,'') <> coalesce(a.ADDRESSBLOCK,'')
or coalesce(at.ADDRESSTYPECODEID,@NULLVALUE) <> coalesce(a.ADDRESSTYPECODEID,@NULLVALUE)
or coalesce(at.CITY,'') <> coalesce(a.CITY,'')
or coalesce(at.COUNTRYID,@NULLVALUE) <> coalesce(a.COUNTRYID,@NULLVALUE)
or coalesce(at.STATEID,@NULLVALUE) <> coalesce(a.STATEID,@NULLVALUE)
or coalesce(at.POSTCODE,'') <> coalesce(a.POSTCODE,'')
or coalesce(at.HISTORICALENDDATE,@NULLDATE) <> coalesce(a.HISTORICALENDDATE,@NULLDATE)))
raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1)
-- Check to see if one of the addresses getting saved or one of the
-- existing addresses is primary. If not, throw an error.
declare @PRIMARYADDRESSEXISTS bit = 0;
select
@PRIMARYADDRESSEXISTS = 1
from
@ADDRESSTABLE TEMP
where
TEMP.ISPRIMARY = 1
if @PRIMARYADDRESSEXISTS = 0
select
@PRIMARYADDRESSEXISTS = 1
from
dbo.ADDRESS A
where
A.CONSTITUENTID = @ID
and A.ISPRIMARY = 1
and not exists
(
select 1
from @ADDRESSTABLE TEMP
where
A.ID = TEMP.ADDRESSID
)
if @PRIMARYADDRESSEXISTS = 0
raiserror('BBERR_ADDRESS_PRIMARYADDRESSREQUIRED', 13, 1);
-- 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
--Apply End date rules to @ADDRESSES
if @NEWADDRESSENDDATECODE = 0
begin
--CTE and update
--For ERB. Pull in rows that might need end dates.
--as well as handling addresses that have been added after CUB row was populated.
with INCOMING as (
select t.ADDRESSTYPECODEID
from @ADDRESSTABLE t
left outer join dbo.ADDRESS a on a.ID = t.ADDRESSID
where (t.ADDRESSID is null or
t.ADDRESSTYPECODEID <> isnull(a.ADDRESSTYPECODEID,@NULLVALUE))
and t.HISTORICALENDDATE is null
and t.DONOTMAIL = 0
)
insert into @ADDRESSTABLE (ADDRESSID,
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, ROWNUMBER, INFOSOURCECOMMENTS,ID)
select A.ID,
A.ADDRESSBLOCK, A.ENDDATE, A.STARTDATE,
dbo.UFN_DATE_GETEARLIESTTIME(A.HISTORICALSTARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(A.HISTORICALENDDATE),
A.ADDRESSTYPECODEID, A.CART, AVU.CERTIFICATIONDATA,
A.CITY, AVU.CONGRESSIONALDISTRICTCODEID, A.COUNTRYID,
AVU.COUNTYCODEID, A.DONOTMAIL, A.DONOTMAILREASONCODEID,
A.DPC, AVU.INFOSOURCECODEID, 0,
AVU.LASTVALIDATIONATTEMPTDATE,AVU.LOCALPRECINCTCODEID, A.LOT,
coalesce(AVU.OMITFROMVALIDATION,0),AVU.REGIONCODEID, A.STATEID,
AVU.STATEHOUSEDISTRICTCODEID, AVU.STATESENATEDISTRICTCODEID, coalesce(AVU.VALIDATIONMESSAGE, ''),
A.POSTCODE, A.SEQUENCE, NULL, ROW_NUMBER() over(order by A.ISPRIMARY desc),
AVU.INFOSOURCECOMMENTS,NEWID()
from dbo.ADDRESS a
inner join INCOMING i on i.ADDRESSTYPECODEID = a.ADDRESSTYPECODEID
left outer join dbo.ADDRESSVALIDATIONUPDATE AVU on AVU.ID = A.ID
where A.CONSTITUENTID = @ID
and (a.HISTORICALENDDATE is null or a.DONOTMAIL=0)
and NOT exists (select 1 from @ADDRESSTABLE AD where AD.ADDRESSID = A.ID)
and (a.ISPRIMARY = 0 or exists(select 1 from @ADDRESSTABLE where ISPRIMARY = 1));
-- addresses that may need end dates applied
with EXISTING as (
select t.ID, t.ADDRESSTYPECODEID
from @ADDRESSTABLE t
inner join dbo.ADDRESS a on a.ID = t.ADDRESSID
where t.ADDRESSTYPECODEID = a.ADDRESSTYPECODEID and (t.HISTORICALENDDATE is null or t.DONOTMAIL=0)
and t.ISPRIMARY = 0
),
-- addresses that may cause end dates to be put on EXISTING addresses
INCOMING as (
select t.ADDRESSTYPECODEID
from @ADDRESSTABLE t
left outer join dbo.ADDRESS a on a.ID = t.ADDRESSID
where (t.ADDRESSID is null or
t.ADDRESSTYPECODEID <> isnull(a.ADDRESSTYPECODEID,@NULLVALUE))
and t.HISTORICALENDDATE is null
and t.DONOTMAIL = 0
)
update t
set HISTORICALENDDATE = isnull(HISTORICALENDDATE,@CURRENTDATE),
DONOTMAIL = 1
from @ADDRESSTABLE t
inner join EXISTING e on e.ID = t.ID
inner join INCOMING i on i.ADDRESSTYPECODEID = e.ADDRESSTYPECODEID
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);
end
end --@ISADDRESSES
--Fill a table with all of the constituent's email addresses
if @ISEMAILADDRESSES = 1
begin
declare @EMAILADDRESSTABLE table
(
ROW int identity(1,1),
EMAILADDRESSTYPECODEID uniqueidentifier,
EMAILADDRESS dbo.UDT_EMAILADDRESS,
ISPRIMARY bit,
DONOTEMAIL bit,
SEQUENCE int,
UPDATEHOUSEHOLD bit,
STARTDATE datetime,
ENDDATE datetime,
INFOSOURCECODEID uniqueidentifier,
EMAILADDRESSID uniqueidentifier,
ID uniqueidentifier,
OLDEMAILADDRESS dbo.UDT_EMAILADDRESS null,
OLDEMAILADDRESSTYPECODEID uniqueidentifier null,
UPDATEDBYRULES bit default 0,
DONOTEMAILREASONCODEID uniqueidentifier,
INFOSOURCECOMMENTS nvarchar(256)
);
with EAB as (
select
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
SEQUENCE,
UPDATEHOUSEHOLD,
dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE) STARTDATE,
dbo.UFN_DATE_GETEARLIESTTIME(ENDDATE) ENDDATE,
INFOSOURCECODEID,
EMAILADDRESSID,
ID,
DONOTEMAILREASONCODEID,
INFOSOURCECOMMENTS
from
dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_2_FROMITEMLISTXML(@EMAILADDRESSES)
)
insert into @EMAILADDRESSTABLE
(
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
DONOTEMAIL,
SEQUENCE,
UPDATEHOUSEHOLD,
STARTDATE,
ENDDATE,
INFOSOURCECODEID,
EMAILADDRESSID,
ID,
OLDEMAILADDRESS,
OLDEMAILADDRESSTYPECODEID,
DONOTEMAILREASONCODEID,
INFOSOURCECOMMENTS
)
select
EAB.EMAILADDRESSTYPECODEID,
EAB.EMAILADDRESS,
EAB.ISPRIMARY,
case
when EAB.ENDDATE is not null then
1
else
EAB.DONOTEMAIL
end as DONOTEMAIL,
EAB.SEQUENCE,
EAB.UPDATEHOUSEHOLD,
dbo.UFN_DATE_GETEARLIESTTIME(EAB.STARTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(EAB.ENDDATE),
EAB.INFOSOURCECODEID,
case
when EAB.EMAILADDRESSID is not null then
EAB.EMAILADDRESSID
else
( select top 1 ID from dbo.EMAILADDRESS EA
where EA.CONSTITUENTID = @ID
and not exists(select 'x' from EAB where EMAILADDRESSID = EA.ID) and
coalesce(EA.EMAILADDRESS, '') = coalesce(EAB.EMAILADDRESS, '') and
coalesce(EA.EMAILADDRESSTYPECODEID, @NULLVALUE) = coalesce(EAB.EMAILADDRESSTYPECODEID, @NULLVALUE)
)
end EMAILADDRESSID,
EAB.ID,
EMAILADDRESS.EMAILADDRESS,
EMAILADDRESS.EMAILADDRESSTYPECODEID,
EAB.DONOTEMAILREASONCODEID,
ISNULL(EAB.INFOSOURCECOMMENTS,'')
from
EAB
left join dbo.EMAILADDRESS on EAB.EMAILADDRESSID = EMAILADDRESS.ID
if exists(select top 1 * from @EMAILADDRESSTABLE)
begin
-- #253763 Check to see if there are duplicate email addresses.
declare @EMAIL_DUPESTABLE table(
EMAILADDRESS dbo.UDT_EMAILADDRESS,
EMAILADDRESSTYPECODEID uniqueidentifier,
RECORDLOCATION tinyint
)
insert into @EMAIL_DUPESTABLE
select EMAILADDRESS, EMAILADDRESSTYPECODEID, min(RECORDLOCATION) as MINRECORDLOCATION
from(
-- RobertDi 2/27/2014 - Split this into two non-overlapping statements to avoid performance problems
-- caused by an "or" in the where clause. See Bug 356074.
select
NEW.EMAILADDRESS,
NEW.EMAILADDRESSTYPECODEID,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
1 + (case when OLD.ID IS NOT null then 2 else 0 end) as RECORDLOCATION
from (select * from @EMAILADDRESSTABLE where ENDDATE is null) as NEW
full outer join (
select ID
from dbo.EMAILADDRESS
where ENDDATE is null
) as OLD
on OLD.ID = NEW.EMAILADDRESSID
where NEW.ID is not null
union all
select
OLD.EMAILADDRESS,
OLD.EMAILADDRESSTYPECODEID,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
(case when OLD.ID is not null then 2 else 0 end) as RECORDLOCATION
from (select * from @EMAILADDRESSTABLE where ENDDATE is null) as NEW
full outer join (
select ID, EMAILADDRESS, EMAILADDRESSTYPECODEID, CONSTITUENTID
from dbo.EMAILADDRESS
where ENDDATE is null
) as OLD
on OLD.ID = NEW.EMAILADDRESSID
where OLD.CONSTITUENTID = @ID and NEW.ID is null
) adr
group by adr.EMAILADDRESS, adr.EMAILADDRESSTYPECODEID
having COUNT(*) > 1 and
(min(RECORDLOCATION)<>2 or max(RECORDLOCATION)<>2) -- at least one of the records must be in the batch
if exists(select 'x' from @EMAIL_DUPESTABLE where RECORDLOCATION = 1)
raiserror('BBERR_EMAILADDRESS_DUPLICATENOTALLOWED', 13, 1)
else if exists (select 'x'
from @EMAIL_DUPESTABLE d
inner join @EMAILADDRESSTABLE et on
coalesce(et.EMAILADDRESS,'') = coalesce(d.EMAILADDRESS,'') and
coalesce(et.EMAILADDRESSTYPECODEID,@NULLVALUE) = coalesce(d.EMAILADDRESSTYPECODEID,@NULLVALUE)
inner join dbo.EMAILADDRESS e on e.ID = et.EMAILADDRESSID
where ( coalesce(et.EMAILADDRESS,'') <> coalesce(e.EMAILADDRESS,'')
or coalesce(et.EMAILADDRESSTYPECODEID,@NULLVALUE) <> coalesce(e.EMAILADDRESSTYPECODEID,@NULLVALUE)
or coalesce(et.ENDDATE,@NULLDATE) <> coalesce(e.ENDDATE,@NULLDATE))
)
raiserror('BBERR_EMAILADDRESS_DUPLICATENOTALLOWED', 13, 1)
declare @PRIMARYEMAILADDRESSEXISTS bit
set @PRIMARYEMAILADDRESSEXISTS = 0
select
@PRIMARYEMAILADDRESSEXISTS = 1
from
@EMAILADDRESSTABLE TEMP
where
TEMP.ISPRIMARY = 1
if @PRIMARYEMAILADDRESSEXISTS = 0
begin
select
@PRIMARYEMAILADDRESSEXISTS = 1
from
dbo.EMAILADDRESS
where
EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1
and not exists
(
select
1
from
@EMAILADDRESSTABLE TEMP
where
EMAILADDRESS.ID = TEMP.EMAILADDRESSID
)
end
if @PRIMARYEMAILADDRESSEXISTS = 0 begin
raiserror('Constituent must have a primary email address.', 13, 1);
return 1;
end
--Apply End date rules to @EMAILADDRESSTABLE
if @NEWEMAILENDDATECODE = 0
begin
--CTE and update
--For ERB. Pull in rows that might need end dates.
--as well as handling emails that have been added after CUB row was populated.
with INCOMING as (
select t.EMAILADDRESSTYPECODEID
from @EMAILADDRESSTABLE t
left outer join dbo.EMAILADDRESS a on a.ID = t.EMAILADDRESSID
where (t.EMAILADDRESSID is null or
t.EMAILADDRESSTYPECODEID <> isnull(a.EMAILADDRESSTYPECODEID,@NULLVALUE))
and t.ENDDATE is null
and t.DONOTEMAIL = 0
)
insert into @EMAILADDRESSTABLE (EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY, DONOTEMAIL,
SEQUENCE, UPDATEHOUSEHOLD, STARTDATE, ENDDATE, INFOSOURCECODEID, EMAILADDRESSID, ID,
DONOTEMAILREASONCODEID,INFOSOURCECOMMENTS)
select E.EMAILADDRESSTYPECODEID, E.EMAILADDRESS, 0, E.DONOTEMAIL, E.SEQUENCE, null,
dbo.UFN_DATE_GETEARLIESTTIME(E.STARTDATE), dbo.UFN_DATE_GETEARLIESTTIME(E.ENDDATE), E.INFOSOURCECODEID, E.ID, NEWID(),
E.DONOTEMAILREASONCODEID, ISNULL(E.INFOSOURCECOMMENTS, '')
from dbo.EMAILADDRESS E
inner join INCOMING I on I.EMAILADDRESSTYPECODEID = E.EMAILADDRESSTYPECODEID
where E.CONSTITUENTID = @ID
and (E.ENDDATE is null or E.DONOTEMAIL = 0)
and NOT exists (select 1 from @EMAILADDRESSTABLE ET where ET.EMAILADDRESSID = E.ID)
and (E.ISPRIMARY = 0 or exists(select 1 from @EMAILADDRESSTABLE where ISPRIMARY = 1));
-- emails that may need end dates applied
with EXISTING as (
select t.ID, t.EMAILADDRESSTYPECODEID
from @EMAILADDRESSTABLE t
inner join dbo.EMAILADDRESS e on e.ID = t.EMAILADDRESSID
where t.EMAILADDRESSTYPECODEID = e.EMAILADDRESSTYPECODEID and (t.ENDDATE is null or t.DONOTEMAIL=0)
and t.ISPRIMARY = 0
),
-- emails that may cause end dates to be put on EXISTING emails
INCOMING as (
select t.EMAILADDRESSTYPECODEID
from @EMAILADDRESSTABLE t
left outer join dbo.EMAILADDRESS e on e.ID = t.EMAILADDRESSID
where (t.EMAILADDRESSID is null or
t.EMAILADDRESSTYPECODEID <> isnull(e.EMAILADDRESSTYPECODEID,@NULLVALUE))
and t.ENDDATE is null
and t.DONOTEMAIL = 0
)
update t
set ENDDATE = isnull(ENDDATE,@CURRENTDATE),
DONOTEMAIL = 1,
UPDATEDBYRULES = 1
from @EMAILADDRESSTABLE t
inner join EXISTING e on e.ID = t.ID
inner join INCOMING i on i.EMAILADDRESSTYPECODEID = e.EMAILADDRESSTYPECODEID
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);
end
end --@ISEMAILADDRESSES
--Fill a table with all of the constituent's phone numbers
declare @EXISTINGPRIMARYPHONEID uniqueidentifier;
declare @EXISTINGPRIMARYPHONEDONOTCALL bit;
if @ISPHONES = 1
begin
declare @PHONENUMBERTABLE table
(
ROW int identity(1,1),
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,
PHONEID uniqueidentifier,
ID uniqueidentifier,
OLDNUMBER nvarchar(100),
OLDPHONETYPECODEID uniqueidentifier,
UPDATEDBYRULES bit default 0,
DONOTTEXT bit,
INFOSOURCECOMMENTS nvarchar(256)
);
with PABS as (
select
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
DONOTCALL,
SEQUENCE,
UPDATEHOUSEHOLD,
COUNTRYID,
SEASONALSTARTDATE,
SEASONALENDDATE,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
INFOSOURCECODEID,
PHONEID,
ID,
DONOTTEXT,
INFOSOURCECOMMENTS
from
dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_FROMITEMLISTXML(@PHONES)
)
insert into @PHONENUMBERTABLE
(
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
DONOTCALL,
SEQUENCE,
UPDATEHOUSEHOLD,
COUNTRYID,
SEASONALSTARTDATE,
SEASONALENDDATE,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
INFOSOURCECODEID,
PHONEID,
ID,
OLDNUMBER,
OLDPHONETYPECODEID,
DONOTTEXT,
INFOSOURCECOMMENTS
)
select
PABS.PHONETYPECODEID,
NUMBER = dbo.UFN_PHONE_REMOVECOUNTRYCODE([PABS].[NUMBER], [PABS].[COUNTRYID]),
PABS.ISPRIMARY,
case
when PABS.ENDDATE is not null then
1
else
PABS.DONOTCALL
end as DONOTCALL,
PABS.SEQUENCE,
PABS.UPDATEHOUSEHOLD,
PABS.COUNTRYID,
PABS.SEASONALSTARTDATE,
PABS.SEASONALENDDATE,
PABS.STARTTIME,
PABS.ENDTIME,
dbo.UFN_DATE_GETEARLIESTTIME(PABS.STARTDATE) STARTDATE,
dbo.UFN_DATE_GETEARLIESTTIME(PABS.ENDDATE) ENDDATE,
PABS.INFOSOURCECODEID,
case
when PABS.PHONEID is not null then
PABS.PHONEID
else
( select top 1 ID from dbo.PHONE p
where p.CONSTITUENTID = @ID
and not exists(select 'x' from PABS where PHONEID = p.ID)
and coalesce(p.NUMBERNOFORMAT, '') = coalesce(dbo.UFN_PHONE_REMOVEFORMATTING(PABS.NUMBER), '')
and coalesce(p.PHONETYPECODEID, @NULLVALUE) = coalesce(PABS.PHONETYPECODEID, @NULLVALUE)
and coalesce(p.COUNTRYID,@NULLVALUE) = coalesce(PABS.COUNTRYID,@NULLVALUE)
)
end,
PABS.ID,
PHONE.NUMBER,
PHONE.PHONETYPECODEID,
ISNULL(PABS.DONOTTEXT, CAST(0 as BIT)),
ISNULL(PABS.INFOSOURCECOMMENTS,'')
from
PABS
left join dbo.PHONE on PABS.PHONEID = PHONE.ID
if exists(select top 1 * from @PHONENUMBERTABLE)
begin
-- #253763 Check to see if there are duplicate phone numbers.
declare @PHONE_DUPESTABLE table(
NUMBER nvarchar(100),
PHONETYPECODEID uniqueidentifier,
COUNTRYID uniqueidentifier,
RECORDLOCATION tinyint
)
insert into @PHONE_DUPESTABLE
select NUMBER, PHONETYPECODEID, COUNTRYID, min(RECORDLOCATION) as MINRECORDLOCATION
from(
-- RobertDi 2/27/2014 - Split this into two non-overlapping statements to avoid performance problems
-- caused by an "or" in the where clause. See Bug 356074.
select
dbo.UFN_PHONE_REMOVEFORMATTING(NEW.NUMBER) as NUMBER,
NEW.PHONETYPECODEID,
NEW.COUNTRYID,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
1 + (case when OLD.ID is not null then 2 else 0 end) as RECORDLOCATION
from (select * from @PHONENUMBERTABLE where ENDDATE is null) NEW
full outer join (
select ID
from dbo.PHONE
where ENDDATE is null
) as OLD
on OLD.ID = NEW.PHONEID
where NEW.ID is not null
union all
select
OLD.NUMBERNOFORMAT as NUMBER,
OLD.PHONETYPECODEID,
OLD.COUNTRYID,
-- 1=New record in batch, 2=Existing record not in batch, 3=Existing record in batch
(case when OLD.ID is not null then 2 else 0 end) as RECORDLOCATION
from (select * from @PHONENUMBERTABLE where ENDDATE is null) NEW
full outer join (
select ID, NUMBERNOFORMAT, PHONETYPECODEID, COUNTRYID, CONSTITUENTID
from dbo.PHONE
where ENDDATE is null
) as OLD
on OLD.ID = NEW.PHONEID
where OLD.CONSTITUENTID = @ID and NEW.ID is null
) phn
group by phn.NUMBER, phn.PHONETYPECODEID, phn.COUNTRYID
having COUNT(*) > 1 and
(min(RECORDLOCATION)<>2 or max(RECORDLOCATION)<>2) --at least one of the records must be in the batch
if exists(select 'x' from @PHONE_DUPESTABLE where RECORDLOCATION = 1)
raiserror('BBERR_PHONENUMBER_DUPLICATENOTALLOWED', 13, 1)
else if exists (select 'x'
from @PHONE_DUPESTABLE d
inner join @PHONENUMBERTABLE p on
coalesce(p.PHONETYPECODEID,@NULLVALUE) = coalesce(d.PHONETYPECODEID,@NULLVALUE) and
coalesce(p.COUNTRYID,@NULLVALUE) = coalesce(d.COUNTRYID,@NULLVALUE) and
coalesce(dbo.UFN_PHONE_REMOVEFORMATTING(p.NUMBER),'') = coalesce(d.NUMBER,'')
inner join dbo.PHONE ph on ph.ID = p.PHONEID
where ( coalesce(p.PHONETYPECODEID,@NULLVALUE) <> coalesce(ph.PHONETYPECODEID,@NULLVALUE)
or coalesce(p.COUNTRYID,@NULLVALUE) <> coalesce(ph.COUNTRYID,@NULLVALUE)
or coalesce(dbo.UFN_PHONE_REMOVEFORMATTING(p.NUMBER),'') <> coalesce(ph.NUMBERNOFORMAT,'')
or coalesce(p.ENDDATE,@NULLDATE) <> coalesce(ph.ENDDATE,@NULLDATE))
)
raiserror('BBERR_PHONENUMBER_DUPLICATENOTALLOWED', 13, 1)
declare @PRIMARYPHONEEXISTS bit
set @PRIMARYPHONEEXISTS = 0
--Get the existing primary phone number if it exists. At the end of the updates and inserts if there is no primary phone number for this constituent, update this record to be primary again.
select
@PRIMARYPHONEEXISTS = 1
from
@PHONENUMBERTABLE TEMP
where
TEMP.ISPRIMARY = 1
if @PRIMARYPHONEEXISTS = 0
begin
select
@PRIMARYPHONEEXISTS = 1
from
dbo.PHONE
where
PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1
and not exists
(
select
1
from
@PHONENUMBERTABLE TEMP
where
PHONE.ID = TEMP.PHONEID
)
end
--Make sure there is a primary phone number
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
--Apply End date rules to @PHONES
if @NEWPHONEENDDATECODE = 0
begin
--CTE and update
--For ERB. Pull in rows that might need end dates.
--as well as handling phones that have been added after CUB row was populated.
with INCOMING as (
select t.PHONETYPECODEID
from @PHONENUMBERTABLE t
left outer join dbo.PHONE a on a.ID = t.PHONEID
where (t.PHONEID is null or
t.PHONETYPECODEID <> isnull(a.PHONETYPECODEID,@NULLVALUE))
and t.ENDDATE is null
and t.DONOTCALL = 0
)
insert into @PHONENUMBERTABLE(PHONETYPECODEID, NUMBER, ISPRIMARY, DONOTCALL, SEQUENCE, UPDATEHOUSEHOLD, COUNTRYID, SEASONALSTARTDATE,
SEASONALENDDATE, STARTTIME, ENDTIME, STARTDATE, ENDDATE, INFOSOURCECODEID, PHONEID, ID, DONOTTEXT, INFOSOURCECOMMENTS)
select P.PHONETYPECODEID, P.NUMBER, 0, P.DONOTCALL, P.SEQUENCE, null, P.COUNTRYID, p.SEASONALSTARTDATE, P.SEASONALENDDATE, P.STARTTIME, P.ENDTIME,
dbo.UFN_DATE_GETEARLIESTTIME(P.STARTDATE), dbo.UFN_DATE_GETEARLIESTTIME(P.ENDDATE), P.INFOSOURCECODEID, P.ID, NEWID(), ISNULL(P.DONOTTEXT, CAST(0 as BIT)), ISNULL(P.INFOSOURCECOMMENTS,'')
from dbo.PHONE P
inner join INCOMING I on I.PHONETYPECODEID = P.PHONETYPECODEID
where P.CONSTITUENTID = @ID
and (P.ENDDATE is null or P.DONOTCALL = 0)
and NOT exists (select 1 from @PHONENUMBERTABLE PT where PT.PHONEID = P.ID)
and (P.ISPRIMARY = 0 or exists(select 1 from @PHONENUMBERTABLE where ISPRIMARY = 1));
-- phones that may need end dates applied
with EXISTING as (
select t.ID, t.PHONETYPECODEID
from @PHONENUMBERTABLE t
inner join dbo.PHONE p on p.ID = t.PHONEID
where t.PHONETYPECODEID = p.PHONETYPECODEID and (t.ENDDATE is null or t.DONOTCALL=0)
and t.ISPRIMARY = 0
),
-- phones that may cause end dates to be put on EXISTING phones
INCOMING as (
select t.PHONETYPECODEID
from @PHONENUMBERTABLE t
left outer join dbo.PHONE p on p.ID = t.PHONEID
where (t.PHONEID is null or
t.PHONETYPECODEID <> isnull(p.PHONETYPECODEID,@NULLVALUE))
and t.ENDDATE is null
and t.DONOTCALL = 0
)
update t
set ENDDATE = isnull(ENDDATE,@CURRENTDATE),
DONOTCALL = 1,
UPDATEDBYRULES = 1
from @PHONENUMBERTABLE t
inner join EXISTING e on e.ID = t.ID
inner join INCOMING i on i.PHONETYPECODEID = e.PHONETYPECODEID
end
-- Start date must be before end date
if exists (select 1 from @PHONENUMBERTABLE where STARTDATE > ENDDATE)
raiserror('BBERR_PHONE_VALIDDATERANGE', 13, 1);
-- Primary phone numbers 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);
end
end --@ISPHONES
--Fill a table with all of the constituent's social media accounts
if @ISSOCIALMEDIAACCOUNTS = 1
begin
--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, SOCIALMEDIAACCOUNTID uniqueidentifier
)
insert into @SOCIALMEDIAACCOUNTTABLE
(
SOCIALMEDIASERVICEID,
USERID,
URL,
SOCIALMEDIAACCOUNTTYPECODEID,
INFOSOURCECODEID,
DONOTCONTACT,
SEQUENCE,
SOCIALMEDIAACCOUNTID
)
select
ACCOUNTS.SOCIALMEDIASERVICEID,
ACCOUNTS.USERID,
ACCOUNTS.URL,
ACCOUNTS.SOCIALMEDIAACCOUNTTYPECODEID,
ACCOUNTS.INFOSOURCECODEID,
ACCOUNTS.DONOTCONTACT,
ACCOUNTS.SEQUENCE,
ACCOUNTS.SOCIALMEDIAACCOUNTID
from
dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_FORUPDATEBATCH_FROMITEMLISTXML(@SOCIALMEDIAACCOUNTS) ACCOUNTS
if exists(select 1 from @SOCIALMEDIAACCOUNTTABLE
group by SOCIALMEDIASERVICEID,URL
having count(*)>1)
raiserror('BBERR_SOCIALMEDIAACCOUNT_DUPLICATENOTALLOWED', 13, 1);
end
if @SOLICITCODES is not null
begin
begin try
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;
-- Mark associated mail preferences as Do not send when the ended solicit code is no longer active
update MAILPREFERENCE set
SENDMAIL = 0,
USESEASONALADDRESS = 0,
USEPRIMARYADDRESS = 0,
USEPRIMARYEMAIL = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @SOLICITCODESTABLE SOLICITCODES
inner join dbo.MAILPREFERENCE on SOLICITCODES.CONSTITUENTSOLICITCODEID = MAILPREFERENCE.CONSTITUENTSOLICITCODEID
where SOLICITCODES.ENDDATE < convert(date, @CURRENTDATE);
-- 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
CONSTITUENTSOLICITCODEID,
@ID,
SOLICITCODEID,
STARTDATE,
ENDDATE,
COMMENTS,
SEQUENCE,
CONSENTPREFERENCECODE,
SOURCECODEID,
SOURCEFILEPATH,
PRIVACYPOLICYFILEPATH,
SUPPORTINGINFORMATION,
CONSENTSTATEMENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SOLICITCODESTABLE;
end try
begin catch
exec.dbo.USP_RAISE_ERROR
return 1
end catch
end
--Fill a table with all of the constituent's relationships
if @ISRELATIONSHIPS = 1
begin
--We don't need to keep track of existing relationships for the constituent, thus no RELATIONSHIPID field is needed.
declare @RELATIONSHIPTABLE table
(
RECIPROCALCONSTITUENTID uniqueidentifier,
RELATIONSHIPTYPECODEID uniqueidentifier,
RECIPROCALTYPECODEID uniqueidentifier,
STARTDATE datetime,
RELATIONSHIPSETID uniqueidentifier
)
insert into @RELATIONSHIPTABLE
(
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
RELATIONSHIPSETID
)
select
RELATIONSHIPS.RECIPROCALCONSTITUENTID,
RELATIONSHIPS.RELATIONSHIPTYPECODEID,
RELATIONSHIPS.RECIPROCALTYPECODEID,
RELATIONSHIPS.STARTDATE,
newid()
from
dbo.UFN_CONSTITUENT_GETRELATIONSHIPS_FORUPDATEBATCH_FROMITEMLISTXML(@RELATIONSHIPS) RELATIONSHIPS
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
end
--Fill a table with all of the constituent's constituencies
--Created a table type so it can be passed to the SP that handles constituencies creation
declare @CONSTITUENCYTABLE as dbo.UDT_CONSTITUENTUPDATEBATCH_CONSTITUENCY
insert into @CONSTITUENCYTABLE
(
ID, CONSTITUENCYCODEID, DATEFROM, DATETO, ORIGINALCONSTITUENCYID
)
select
newid(), CONSTITUENCYCODEID, DATEFROM, DATETO, ORIGINALCONSTITUENCYID
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),
ORIGINALALTERNATELOOKUPID uniqueidentifier
)
insert into @ALTERNATELOOKUPIDTABLE
(
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID, ORIGINALALTERNATELOOKUPID
)
select
LOOKUPIDS.ALTERNATELOOKUPIDTYPECODEID,
LOOKUPIDS.ALTERNATELOOKUPID,
case when LOOKUPIDS.ORIGINALALTERNATELOOKUPID is null
then --if the ORIGINALALTERNATELOOKUPID field is null, look for an existing alternate lookup ID with the same type and lookup Id value on the constituent.
(
select top 1
ALTERNATELOOKUPID.ID
from
dbo.ALTERNATELOOKUPID
where
ALTERNATELOOKUPID.CONSTITUENTID = @ID
and ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID = LOOKUPIDS.ALTERNATELOOKUPIDTYPECODEID
and ALTERNATELOOKUPID.ALTERNATELOOKUPID = LOOKUPIDS.ALTERNATELOOKUPID
)
else
LOOKUPIDS.ORIGINALALTERNATELOOKUPID
end
from
dbo.UFN_CONSTITUENT_GETALTERNATELOOKUPIDS_FORUPDATEBATCH_FROMITEMLISTXML(@ALTERNATELOOKUPIDS) LOOKUPIDS
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
if exists ( select 1 from dbo.ALTERNATELOOKUPID
inner join @ALTERNATELOOKUPIDTABLE as NEWALTERNATELOOKUPID on
ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID = NEWALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID and
ALTERNATELOOKUPID.ALTERNATELOOKUPID = NEWALTERNATELOOKUPID.ALTERNATELOOKUPID
where ALTERNATELOOKUPID.CONSTITUENTID <> @ID)
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),
ORIGINALALTERNATELOOKUPID uniqueidentifier
)
insert into @SPOUSEALTERNATELOOKUPIDTABLE
(
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID, ORIGINALALTERNATELOOKUPID
)
select
ALTERNATELOOKUPIDTYPECODEID, ALTERNATELOOKUPID, ORIGINALALTERNATELOOKUPID
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
where ALTERNATELOOKUPID.CONSTITUENTID <> @SPOUSE_ID)
begin
raiserror('BBERR_ORIGINAL_ERROR:2627',1,11);
raiserror('UIX_ALTERNATELOOKUPID_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 @CONSTITUENT_SITES is null and dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
raiserror('CK_CONSTITUENTSITES_SITEREQUIRED', 13, 1);
--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);
if @VALIDATEONLY = 0
begin
if @CREATEHISTORICALNAMECODE = 1
-------------------------------------------------------------------
--Historical Names
-------------------------------------------------------------------
exec dbo.USP_CREATE_HISTORICALNAME @ID, @KEYNAME, @FIRSTNAME , @MIDDLENAME , @TITLECODEID, @SUFFIXCODEID, @CHANGEAGENTID;
begin try
--WI#249545 We need to include keyname prefix in case it is an organization. Blank if not.
declare @KEYNAMEPREFIX nvarchar(50) = ''
if @CONSTITUENTTYPECODE = 1 --Organization
exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAME output, @KEYNAMEPREFIX output;
-----------------------------------------------------------------
--Basic Constituent Info
--===============================================================
update
dbo.[CONSTITUENT]
set
[BIRTHDATE] = coalesce(nullif(@BIRTHDATE, '00000000'), BIRTHDATE),
[FIRSTNAME] = coalesce(nullif(@FIRSTNAME, ''), FIRSTNAME),
[GENDERCODE] = @GENDERCODE,
[GENDERCODEID]=@GENDERCODEID,
[GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
[KEYNAME] = coalesce(nullif(@KEYNAME, ''), KEYNAME),
[KEYNAMEPREFIX] = @KEYNAMEPREFIX,
--[CUSTOMIDENTIFIER] = @LOOKUPID, Will not update lookupid from batch due to match issues with alternate lookup ids
[MAIDENNAME] = coalesce(nullif(@MAIDENNAME, ''), MAIDENNAME),
[MIDDLENAME] = coalesce(nullif(@MIDDLENAME, ''), MIDDLENAME),
[NICKNAME] = coalesce(nullif(@NICKNAME, ''), NICKNAME),
[SUFFIXCODEID] = coalesce(nullif(@SUFFIXCODEID, '00000000-0000-0000-0000-000000000000'), SUFFIXCODEID),
[TITLECODEID] = coalesce(nullif(@TITLECODEID, '00000000-0000-0000-0000-000000000000'), TITLECODEID),
[WEBADDRESS] = coalesce(nullif(@WEBADDRESS, ''), WEBADDRESS),
[MARITALSTATUSCODEID] = coalesce(nullif(@MARITALSTATUSCODEID, '00000000-0000-0000-0000-000000000000'), MARITALSTATUSCODEID),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where
[CONSTITUENT].ID = @ID;
-----------------------------------------------------------------
--Name formats
--===============================================================
if @CONSTITUENTTYPECODE = 0 begin
exec dbo.USP_CONSTITUENTUPDATEBATCH_ADDUPDATENAMEFORMATS @ID, @NAMEFORMATS, @CHANGEAGENTID, @CURRENTDATE;
end
--Update/insert the constituentid into NETCOMMUNITYCLIENTUSER & set the NETCOMMUNITYMEMBER flag
--SET @BBNCTRANID= 25
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
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
END
IF ISNULL(@ProfileUpdateTransactionsID, 0) <> 0
EXEC spTransactions_MarkProfileUpdateAsProcessed @ProfileUpdateTransactionsID= @ProfileUpdateTransactionsID
--Kill or resurrect the constituent if deceased has been checked or unchecked
if @DECEASED = 1 and not exists(select ID from DECEASEDCONSTITUENT where ID = @ID)
begin
insert into dbo.DECEASEDCONSTITUENT
(
ID,
DECEASEDDATE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
isnull(@DECEASEDDATE, '00000000'),
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
exec dbo.USP_CONSTITUENT_DECEASEFROMRULES @ID, @CHANGEAGENTID;
end
-- If the constituent is already deceased, update the date.
else if @DECEASED = 1 and @DECEASEDDATE is not null
begin
update
dbo.DECEASEDCONSTITUENT
set
DECEASEDDATE = @DECEASEDDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
end
else if @DECEASED = 0
delete from dbo.DECEASEDCONSTITUENT
where ID = @ID
--Update the constituent's sites
-- Aaron Crawford WI#502525: Only delete constituent sites if data is coming from the batch user interface (as opposed to import)
if @ROWFROMBATCHUI = 1
begin
delete from dbo.CONSTITUENTSITE
where
CONSTITUENTSITE.CONSTITUENTID = @ID
and not exists (select 1 from @CONSTITUENTSITESTABLE NEWSITES where NEWSITES.SITEID = CONSTITUENTSITE.SITEID)
end
insert into dbo.CONSTITUENTSITE
(
ID,
CONSTITUENTID,
SITEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(), @ID, NEWSITES.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @CONSTITUENTSITESTABLE NEWSITES
where not exists (select 1 from dbo.CONSTITUENTSITE where CONSTITUENTSITE.SITEID = NEWSITES.SITEID and CONSTITUENTSITE.CONSTITUENTID = @ID)
-----------------------------------------------------------------
--Basic group info
--===============================================================
declare @PREVIOUSSTARTDATE date = (select STARTDATE from dbo.GROUPDATA where ID = @ID);
update dbo.GROUPDATA
set
GROUPTYPECODE = coalesce(case when @CONSTITUENTTYPECODE = 2 then 0 when @CONSTITUENTTYPECODE = 3 then 1 else 0 end, 0),
GROUPTYPEID = coalesce(nullif(@GROUPTYPEID, '00000000-0000-0000-0000-000000000000'), GROUPTYPEID),
DESCRIPTION = coalesce(nullif(@GROUPDESCRIPTION, ''), DESCRIPTION),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
STARTDATE = @GROUPSTARTDATE
where
ID = @ID
-- Update member's start date to be equal to the group's start date if the member's start date was
-- on or before the previous group start date
update
dbo.GROUPMEMBERDATERANGE
set
DATEFROM = @GROUPSTARTDATE,
DATETO = case when DATETO < @GROUPSTARTDATE then @GROUPSTARTDATE else DATETO end,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
GROUPMEMBERID in (select ID from dbo.GROUPMEMBER where GROUPID = @ID)
and ((DATEFROM is null and @PREVIOUSSTARTDATE is null)
or (DATEFROM = @PREVIOUSSTARTDATE)
or (DATEFROM < @GROUPSTARTDATE))
--Update the role dates in case the member dates were changed
exec dbo.USP_GROUPMEMBERROLE_UPDATEDATESFORGROUP @ID, @CHANGEAGENTID;
-----------------------------------------------------------------
--Basic organization info
--===============================================================
declare @OLDPARENTID uniqueidentifier = (select PARENTCORPID from dbo.ORGANIZATIONDATA where ORGANIZATIONDATA.ID = @ID);
update dbo.ORGANIZATIONDATA
set
INDUSTRYCODEID = coalesce(nullif(@ORG_INDUSTRYCODEID, '00000000-0000-0000-0000-000000000000'), INDUSTRYCODEID),
NUMEMPLOYEES = coalesce(nullif(@ORG_NUMEMPLOYEES, ''), NUMEMPLOYEES),
NUMSUBSIDIARIES = coalesce(nullif(@ORG_NUMSUBSIDIARIES, ''), NUMSUBSIDIARIES),
PARENTCORPID = coalesce(nullif(@ORG_PARENTCORPID, '00000000-0000-0000-0000-000000000000'), PARENTCORPID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
if @ORG_PARENTCORPID is not null and @ORG_PARENTCORPID <> '00000000-0000-0000-0000-000000000000'
begin
exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID, @ORG_PARENTCORPID,@CURRENTDATE,@CHANGEAGENTID,@CURRENTDATE,1,@OLDPARENTID;
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
begin try
update dbo.RELATIONSHIP
set
[ISSPOUSE] = 0,
[RELATIONSHIPTYPECODEID] = @SPOUSEREMOVEDRELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID] = @SPOUSEREMOVEDRELATIONSHIPTYPECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
[RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @ID
and [RELATIONSHIP].[ISSPOUSE] = 1;
end try
begin catch
exec dbo.USP_RAISE_ERROR 1;
return 1;
end catch
end
-- if this is a non BBIS transaction, use the batch relationship fields
if not isnull(@BBNCUSERID, 0) <> 0
begin
begin try
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 try
begin catch
exec dbo.USP_RAISE_ERROR 1;
return 1;
end catch
end
end
if (@SPOUSE_ID is not null or @SPOUSE_LASTNAME <> '') and @REMOVESPOUSE = 0 begin
--Get the previous spouse ID
declare @OLD_SPOUSE_ID uniqueidentifier
select @OLD_SPOUSE_ID = R.RECIPROCALCONSTITUENTID
from
dbo.RELATIONSHIP R
where
R.RELATIONSHIPCONSTITUENTID = @ID and ISSPOUSE = 1
--Update info for the current spouse if the current spouse is still the spouse
if @OLD_SPOUSE_ID is not null and @OLD_SPOUSE_ID = @SPOUSE_ID 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(nullif(@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.RELATIONSHIP SR
join dbo.CONSTITUENT SPOUSE on SR.RECIPROCALCONSTITUENTID = SPOUSE.ID
where
SR.RELATIONSHIPCONSTITUENTID = @ID and SR.ISSPOUSE = 1
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
begin try
update SR
set
SR.RELATIONSHIPTYPECODEID = coalesce(nullif(@SPOUSE_RELATIONSHIPTYPECODEID, '00000000-0000-0000-0000-000000000000'), SR.RELATIONSHIPTYPECODEID),
SR.RECIPROCALTYPECODEID = coalesce(nullif(@SPOUSE_RECIPROCALTYPECODEID, '00000000-0000-0000-0000-000000000000'), SR.RECIPROCALTYPECODEID),
SR.CHANGEDBYID = @CHANGEAGENTID,
SR.DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP SR
where
SR.RELATIONSHIPCONSTITUENTID = @ID and SR.ISSPOUSE = 1
end try
begin catch
exec dbo.USP_RAISE_ERROR 2;
return 1;
end catch
end
--Add a new spouse relationship if there was not a previous spouse
else if @OLD_SPOUSE_ID is null or @OLD_SPOUSE_ID <> @SPOUSE_ID begin
--Insert a new constituent for the spouse if an existing constituent was not chosen
if @SPOUSE_ID is null and @SPOUSE_LASTNAME is not null and @SPOUSE_LASTNAME <> '' begin
set @SPOUSE_ID = newid()
begin try
insert into dbo.CONSTITUENT
(
ID,
BIRTHDATE,
FIRSTNAME,
GENDERCODE,
GENDERCODEID,
KEYNAME,
CUSTOMIDENTIFIER,
MAIDENNAME,
MIDDLENAME,
NICKNAME,
SUFFIXCODEID,
TITLECODEID,
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,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
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 = @SPOUSE_ID
and CONSITTUENTALLNAMES.KEYNAME <> '';
end
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
raiserror(@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
--existing constituent is added as spouse to batch
else if @SPOUSE_ID is not null and @SPOUSE_LASTNAME is not null and @SPOUSE_LASTNAME <> '' begin
begin try
update dbo.CONSTITUENT
set
BIRTHDATE = coalesce(nullif(@SPOUSE_BIRTHDATE, '00000000'), @SPOUSE_BIRTHDATE),
FIRSTNAME = coalesce(nullif(@SPOUSE_FIRSTNAME, ''), @SPOUSE_FIRSTNAME),
GENDERCODE = coalesce(@SPOUSE_GENDERCODE, 0),
GENDERCODEID = @SPOUSE_GENDERCODEID,
KEYNAME = @SPOUSE_LASTNAME,
CUSTOMIDENTIFIER = coalesce(@SPOUSE_LOOKUPID, ''),
MAIDENNAME = coalesce(nullif(@SPOUSE_MAIDENNAME, ''), @SPOUSE_MAIDENNAME),
MIDDLENAME = coalesce(nullif(@SPOUSE_MIDDLENAME, ''), @SPOUSE_MIDDLENAME),
NICKNAME = coalesce(nullif(@SPOUSE_NICKNAME, ''), @SPOUSE_NICKNAME),
SUFFIXCODEID =coalesce(nullif(@SPOUSE_SUFFIXCODEID, '00000000-0000-0000-0000-000000000000'), @SPOUSE_SUFFIXCODEID),
TITLECODEID = coalesce(nullif(@SPOUSE_TITLECODEID, '00000000-0000-0000-0000-000000000000'), @SPOUSE_TITLECODEID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @SPOUSE_ID
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
raiserror(@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end
--Add a relationship for the new spouse
if not exists(
select ID from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @ID and RECIPROCALCONSTITUENTID = @SPOUSE_ID)
begin
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, ISSPOUSE, RELATIONSHIPSETID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID, @SPOUSE_ID,
@SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, 1, @SETID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end try
begin catch
exec dbo.USP_RAISE_ERROR 1;
return 1;
end catch
end
--Update the new spouse relationship
begin try
update SR
set
SR.RELATIONSHIPTYPECODEID = coalesce(nullif(@SPOUSE_RELATIONSHIPTYPECODEID, '00000000-0000-0000-0000-000000000000'), SR.RELATIONSHIPTYPECODEID),
SR.RECIPROCALTYPECODEID = coalesce(nullif(@SPOUSE_RECIPROCALTYPECODEID, '00000000-0000-0000-0000-000000000000'), SR.RECIPROCALTYPECODEID),
SR.ISSPOUSE = 1,
SR.CHANGEDBYID = @CHANGEAGENTID,
SR.DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP SR
where
SR.RELATIONSHIPCONSTITUENTID = @ID and SR.RECIPROCALCONSTITUENTID = @SPOUSE_ID
end try
begin catch
exec dbo.USP_RAISE_ERROR 2;
return 1;
end catch
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_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @SPOUSE_ID, @SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
--Add the spouse to the household
exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @ID, @SPOUSE_ID,
0, @CHANGEAGENTID, @CURRENTDATE, 0, @CURRENTAPPUSERID;
end
--Add the recognition credit defaults for the spouse
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @SPOUSE_ID, @ID, null, null,
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID, @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID, 0;
end
declare @HASHOUSEHOLD bit = 0;
--Get the constituents who are in the household
declare @MATCHINGCONSTITUENTS table(CONSTITID uniqueidentifier);
insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_CONSTITUENT_MATCHINGHOUSEHOLDRECORDS(@ID);
if @@rowcount > 0 begin
set @HASHOUSEHOLD = 1;
end
-----------------------------------------------------------------
--Aliases
--===============================================================
if @MAIDENNAMECHANGED = 1 and @MAIDENNAME is not null and @MAIDENNAME != ''
begin
if not exists (select ID from dbo.ALIAS
where ALIAS.CONSTITUENTID = @ID
and ALIAS.KEYNAME = @MAIDENNAME
and ALIAS.FIRSTNAME = @FIRSTNAME
and ALIAS.MIDDLENAME = @MIDDLENAME)
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@ID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
if @SPOUSE_MAIDENNAMECHANGED = 1 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
-----------------------------------------------------------------
--Alternate Lookup IDs
--===============================================================
--Update existing entries in the ALTERNATELOOKUPID table
update ALT
set
ALTERNATELOOKUPID = TEMP.ALTERNATELOOKUPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@ALTERNATELOOKUPIDTABLE TEMP
join dbo.ALTERNATELOOKUPID ALT on
TEMP.ORIGINALALTERNATELOOKUPID = ALT.ID and
ALT.CONSTITUENTID = @ID
--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
TEMP.ORIGINALALTERNATELOOKUPID is null or
not exists
(
select
1
from
dbo.ALTERNATELOOKUPID
where
ID = TEMP.ORIGINALALTERNATELOOKUPID and
ALTERNATELOOKUPID.CONSTITUENTID = @ID
)
--Update existing entries in the ALTERNATELOOKUPID table
update ALT
set
ALTERNATELOOKUPID = TEMP.ALTERNATELOOKUPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@SPOUSEALTERNATELOOKUPIDTABLE TEMP
join dbo.ALTERNATELOOKUPID ALT on
TEMP.ORIGINALALTERNATELOOKUPID = ALT.ID and
ALT.CONSTITUENTID = @SPOUSE_ID
--HANDLE SPOUSE ALTERNATE LOOKUPIDs
--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
TEMP.ORIGINALALTERNATELOOKUPID is null or
not exists
(
select
1
from
dbo.ALTERNATELOOKUPID
where
ID = TEMP.ORIGINALALTERNATELOOKUPID and
ALTERNATELOOKUPID.CONSTITUENTID = @SPOUSE_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
-----------------------------------------------------------------
--Addresses
--===============================================================
if exists(select top 1 * from @ADDRESSTABLE)
begin
-- 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
nullif(ADDRESSID, '00000000-0000-0000-0000-000000000000') is null
and ADDRESSTABLE.ISPRIMARY = 1
and ADDRESSTABLE.UPDATEHOUSEHOLD = 1
) and exists (
select
1
from
@ADDRESSTABLE ADDRESSTABLE
inner join dbo.ADDRESS CURRENTADDRESS on
ADDRESSTABLE.ADDRESSID = CURRENTADDRESS.ID
inner join dbo.ADDRESS on
ADDRESS.ADDRESSBLOCK = CURRENTADDRESS.ADDRESSBLOCK and
ADDRESS.CITY = CURRENTADDRESS.CITY and
(ADDRESS.COUNTRYID = CURRENTADDRESS.COUNTRYID or (ADDRESS.COUNTRYID is null and CURRENTADDRESS.COUNTRYID is null)) and
(ADDRESS.STATEID = CURRENTADDRESS.STATEID or (ADDRESS.STATEID is null and CURRENTADDRESS.STATEID is null)) and
ADDRESS.POSTCODE = CURRENTADDRESS.POSTCODE
and ( (ADDRESS.HISTORICALSTARTDATE = CURRENTADDRESS.HISTORICALSTARTDATE) or (ADDRESS.HISTORICALSTARTDATE is null and CURRENTADDRESS.HISTORICALSTARTDATE is null) )
and ( (ADDRESS.HISTORICALENDDATE = CURRENTADDRESS.HISTORICALENDDATE) or (ADDRESS.HISTORICALENDDATE is null and CURRENTADDRESS.HISTORICALENDDATE is null) )
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);
declare @PRIMARYROWNUM smallint
select TOP(1)
@PRIMARYROWNUM = ROWNUMBER
from
@ADDRESSTABLE
where
ISPRIMARY = 1
order by ROWNUMBER asc
if not @PRIMARYROWNUM is null begin
update @ADDRESSTABLE
set
ISPRIMARY = 0
where
ROWNUMBER <> @PRIMARYROWNUM and ISPRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @ID
end
declare @ADDRESSID uniqueidentifier
declare @ADDRESSTYPECODEID uniqueidentifier
declare @PRIMARY bit
declare @DONOTMAIL bit
declare @STARTDATE dbo.UDT_MONTHDAY
declare @ENDDATE dbo.UDT_MONTHDAY
declare @COUNTRYID uniqueidentifier
declare @STATEID uniqueidentifier
declare @ADDRESSBLOCK nvarchar(150)
declare @CITY nvarchar(50)
declare @POSTCODE nvarchar(12)
declare @CART nvarchar(10)
declare @DPC nvarchar(8)
declare @LOT nvarchar(5)
declare @OMITFROMVALIDATION bit
declare @COUNTYCODEID uniqueidentifier
declare @CONGRESSIONALDISTRICTCODEID uniqueidentifier
declare @STATEHOUSEDISTRICTCODEID uniqueidentifier
declare @STATESENATEDISTRICTCODEID uniqueidentifier
declare @LOCALPRECINCTCODEID uniqueidentifier
declare @INFOSOURCECODEID uniqueidentifier
declare @REGIONCODEID uniqueidentifier
declare @LASTVALIDATIONATTEMPTDATE datetime
declare @VALIDATIONMESSAGE nvarchar(200)
declare @CERTIFICATIONDATA integer
declare @DONOTMAILREASONCODEID uniqueidentifier
declare @UPDATEFROMREVENUEBATCH bit = 0;
declare @HISTORICALSTARTDATE date
declare @HISTORICALENDDATE date
declare @UPDATEHOUSEHOLD bit
declare @UPDATESPOUSE bit
declare @INFOSOURCECOMMENTS nvarchar(256)
declare ADDRESSCURSOR cursor local fast_forward for
select
ADDRESSID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID,
INFOSOURCECODEID,
REGIONCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
DONOTMAILREASONCODEID,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
case
when UPDATEHOUSEHOLD = 1 and
(@CURRENTCONSTITUENTTYPECODE = 2 or @HASHOUSEHOLD = 1) then
1
else
0
end,
case
when UPDATEHOUSEHOLD = 1 and isnull(@REMOVESPOUSE,0) = 0 and (@CURRENTSPOUSE is not null or @SPOUSE_ID is not null) then
1
else
0
end,
INFOSOURCECOMMENTS
from
@ADDRESSTABLE
order by ISPRIMARY desc, ROWNUMBER asc
open ADDRESSCURSOR;
fetch next from ADDRESSCURSOR into
@ADDRESSID,
@ADDRESSTYPECODEID,
@PRIMARY,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@OMITFROMVALIDATION,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID,
@INFOSOURCECODEID,
@REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE,
@HISTORICALENDDATE,
@UPDATEHOUSEHOLD,
@UPDATESPOUSE,
@INFOSOURCECOMMENTS;
while (@@FETCH_STATUS = 0)
begin
if not nullif(@ADDRESSID, '00000000-0000-0000-0000-000000000000') is null begin
exec dbo.USP_DATAFORMTEMPLATE_EDIT_ADDRESS_5
@ID = @ADDRESSID,
@CHANGEAGENTID = @CHANGEAGENTID,
@ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
@PRIMARY = @PRIMARY,
@DONOTMAIL = @DONOTMAIL,
@STARTDATE = @STARTDATE,
@ENDDATE = @ENDDATE,
@COUNTRYID = @COUNTRYID,
@STATEID = @STATEID,
@ADDRESSBLOCK = @ADDRESSBLOCK,
@CITY = @CITY,
@POSTCODE = @POSTCODE,
@CART = @CART,
@DPC = @DPC,
@LOT = @LOT,
@OMITFROMVALIDATION = @OMITFROMVALIDATION,
@COUNTYCODEID = @COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REGIONCODEID = @REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
@CERTIFICATIONDATA = @CERTIFICATIONDATA,
@UPDATECONTACTS = 0,
@DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
@HISTORICALENDDATE = @HISTORICALENDDATE,
@UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEHOUSEHOLD,
@INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS
end
else begin
exec dbo.USP_ADDRESS_ADD
@ID = @ADDRESSID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@CONSTITUENTID = @ID,
@ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
@PRIMARY = @PRIMARY,
@DONOTMAIL = @DONOTMAIL,
@STARTDATE = @STARTDATE,
@ENDDATE = @ENDDATE,
@COUNTRYID = @COUNTRYID,
@STATEID = @STATEID,
@ADDRESSBLOCK = @ADDRESSBLOCK,
@CITY = @CITY,
@POSTCODE = @POSTCODE,
@CART = @CART,
@DPC = @DPC,
@LOT = @LOT,
@OMITFROMVALIDATION = @OMITFROMVALIDATION,
@COUNTYCODEID = @COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REGIONCODEID = @REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
@CERTIFICATIONDATA = @CERTIFICATIONDATA,
@DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
@RECENTMOVE = 0,
@OLDADDRESSID = null,
@UPDATEMATCHINGSPOUSEADDRESSES = @UPDATESPOUSE,
@UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEHOUSEHOLD,
@INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
@HISTORICALENDDATE = @HISTORICALENDDATE
end
fetch next from ADDRESSCURSOR into
@ADDRESSID,
@ADDRESSTYPECODEID,
@PRIMARY,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@OMITFROMVALIDATION,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID,
@INFOSOURCECODEID,
@REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE,
@HISTORICALENDDATE,
@UPDATEHOUSEHOLD,
@UPDATESPOUSE,
@INFOSOURCECOMMENTS;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close ADDRESSCURSOR;
deallocate ADDRESSCURSOR;
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
--Update existing entries in the EMAILADDRESS table for records w/o emailaddressid
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.CHANGEDBYID = @CHANGEAGENTID,
EA.DATECHANGED = @CURRENTDATE,
EA.STARTDATE = EAT.STARTDATE,
EA.ENDDATE = EAT.ENDDATE,
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 and (EA.EMAILADDRESSTYPECODEID = EAT.EMAILADDRESSTYPECODEID or (EA.EMAILADDRESSTYPECODEID is null and EAT.EMAILADDRESSTYPECODEID is null))
where
(EA.CONSTITUENTID = @ID) and
EAT.EMAILADDRESSID is null
--Update existing entries in the EMAILADDRESS table for records with emailaddressid
update EA
set
EA.EMAILADDRESS = EAT.EMAILADDRESS,
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.CHANGEDBYID = @CHANGEAGENTID,
EA.DATECHANGED = @CURRENTDATE,
EA.STARTDATE = EAT.STARTDATE,
EA.ENDDATE = EAT.ENDDATE,
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.ID = EAT.EMAILADDRESSID
where
(EA.CONSTITUENTID = @ID)
-- update for households
-- Per 271319, do *not* update ISPRIMARY on the matching email addresses.
-- Per 372612, do not update the matching spouse DONOTEMAIL and ENDDATE if the constituent update end date rule (UPDATEDBYRULES) is enabled
update dbo.EMAILADDRESS
set
EMAILADDRESS.EMAILADDRESS = EAT.EMAILADDRESS,
EMAILADDRESS.EMAILADDRESSTYPECODEID = coalesce(nullif(EAT.EMAILADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), EMAILADDRESS.EMAILADDRESSTYPECODEID),
EMAILADDRESS.DONOTEMAIL = case when EAT.UPDATEDBYRULES = 0 then EAT.DONOTEMAIL else EMAILADDRESS.DONOTEMAIL end,
EMAILADDRESS.SEQUENCE = coalesce(nullif(EAT.SEQUENCE,''), EMAILADDRESS.SEQUENCE),
EMAILADDRESS.CHANGEDBYID = @CHANGEAGENTID,
EMAILADDRESS.DATECHANGED = @CURRENTDATE,
EMAILADDRESS.STARTDATE = EAT.STARTDATE,
EMAILADDRESS.ENDDATE = case when EAT.UPDATEDBYRULES = 0 then EAT.ENDDATE else EMAILADDRESS.ENDDATE end,
EMAILADDRESS.INFOSOURCECODEID = coalesce(nullif(EAT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), EMAILADDRESS.INFOSOURCECODEID),
EMAILADDRESS.DONOTEMAILREASONCODEID = coalesce(nullif(EAT.DONOTEMAILREASONCODEID, '00000000-0000-0000-0000-000000000000'), EMAILADDRESS.DONOTEMAILREASONCODEID),
EMAILADDRESS.INFOSOURCECOMMENTS = EAT.INFOSOURCECOMMENTS
from
dbo.EMAILADDRESS
inner join @EMAILADDRESSTABLE EAT on
EMAILADDRESS.EMAILADDRESS = EAT.OLDEMAILADDRESS
and (
EMAILADDRESS.EMAILADDRESSTYPECODEID = EAT.OLDEMAILADDRESSTYPECODEID or (EMAILADDRESS.EMAILADDRESSTYPECODEID is null and EAT.OLDEMAILADDRESSTYPECODEID is null)
)
where
((EMAILADDRESS.CONSTITUENTID in (select CONSTITID from @MATCHINGCONSTITUENTS)) and (EAT.UPDATEHOUSEHOLD = 1));
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY, DONOTEMAIL, SEQUENCE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE, INFOSOURCECODEID, DONOTEMAILREASONCODEID,
INFOSOURCECOMMENTS
)
select
@ID,
EAT.EMAILADDRESSTYPECODEID, EAT.EMAILADDRESS, EAT.ISPRIMARY, EAT.DONOTEMAIL, EAT.SEQUENCE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, EAT.STARTDATE, EAT.ENDDATE, EAT.INFOSOURCECODEID, EAT.DONOTEMAILREASONCODEID,
EAT.INFOSOURCECOMMENTS
from
@EMAILADDRESSTABLE EAT
where EAT.EMAILADDRESSID is null and not exists(
select ID
from dbo.EMAILADDRESS
where
CONSTITUENTID = @ID and
EMAILADDRESS.EMAILADDRESS = EAT.EMAILADDRESS and
(
EMAILADDRESS.EMAILADDRESSTYPECODEID = EAT.EMAILADDRESSTYPECODEID or
(
EMAILADDRESS.EMAILADDRESSTYPECODEID is null and EAT.EMAILADDRESSTYPECODEID is null
)
)
)
declare @EMAILADDRESSESTOINSERT table
(
ROW int,
ISPRIMARY bit,
MATCHINGCONSTITUENTID uniqueidentifier,
MATCHINGCONSTITUENTHASPRIMARY bit
)
--create a table of email addresses to add to the constituent's household and members of the household
insert into @EMAILADDRESSESTOINSERT
select
EAT.ROW, EAT.ISPRIMARY, MC.CONSTITID, case when EMAILADDRESS.ID is null then 0 else 1 end
from
@MATCHINGCONSTITUENTS MC
join @EMAILADDRESSTABLE EAT on EAT.UPDATEHOUSEHOLD = 1
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = MC.CONSTITID and EMAILADDRESS.ISPRIMARY = 1
where
EAT.EMAILADDRESSID is null
and not exists
(
select
CONSTITUENTID
from
dbo.EMAILADDRESS
where
CONSTITUENTID = MC.CONSTITID and
EMAILADDRESS.EMAILADDRESS = EAT.EMAILADDRESS and
(
EMAILADDRESS.EMAILADDRESSTYPECODEID = EAT.EMAILADDRESSTYPECODEID or
(
EMAILADDRESS.EMAILADDRESSTYPECODEID is null and
EAT.EMAILADDRESSTYPECODEID is null
)
)
)
--for the household or members who do not already have a primary email address, choose one of the email addresses to insert as the primary
update
@EMAILADDRESSESTOINSERT
set
ISPRIMARY = 1
from
@EMAILADDRESSESTOINSERT E
inner join
(
select
min(ROW) as ROW,
MATCHINGCONSTITUENTID
from
@EMAILADDRESSESTOINSERT
where
MATCHINGCONSTITUENTHASPRIMARY = 0
group by
MATCHINGCONSTITUENTID
having
sum(cast(ISPRIMARY as int)) = 0
) MATCHINGCONSTITUENTSWITHNOPRIMARYEMAIL
on E.ROW = MATCHINGCONSTITUENTSWITHNOPRIMARYEMAIL.ROW and E.MATCHINGCONSTITUENTID = MATCHINGCONSTITUENTSWITHNOPRIMARYEMAIL.MATCHINGCONSTITUENTID
--add the new email addresses to the household and members
insert into dbo.EMAILADDRESS
(
CONSTITUENTID,
EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY, DONOTEMAIL, SEQUENCE,
CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED, STARTDATE, ENDDATE, INFOSOURCECODEID, DONOTEMAILREASONCODEID, INFOSOURCECOMMENTS
)
select
EMAILADDRESSTOINSERT.MATCHINGCONSTITUENTID,
EAT.EMAILADDRESSTYPECODEID,
EAT.EMAILADDRESS,
case EMAILADDRESSTOINSERT.MATCHINGCONSTITUENTHASPRIMARY
when 1
then 0 --if the constituent already has a primary email address, add the new email address as non-primary
else
EMAILADDRESSTOINSERT.ISPRIMARY
end,
EAT.DONOTEMAIL,
EAT.SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
EAT.STARTDATE,
EAT.ENDDATE,
EAT.INFOSOURCECODEID,
EAT.DONOTEMAILREASONCODEID,
EAT.INFOSOURCECOMMENTS
from
@EMAILADDRESSESTOINSERT EMAILADDRESSTOINSERT
inner join @EMAILADDRESSTABLE EAT on EMAILADDRESSTOINSERT.ROW = EAT.ROW
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
--Update existing entries in the PHONE table where phoneid is null
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 = coalesce(nullif(PT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), P.INFOSOURCECODEID),
P.DONOTTEXT = PT.DONOTTEXT,
P.INFOSOURCECOMMENTS = PT.INFOSOURCECOMMENTS
from
dbo.PHONE P
inner join @PHONENUMBERTABLE PT on P.NUMBER = PT.NUMBER and ((P.PHONETYPECODEID is not null and PT.PHONETYPECODEID is not null and P.PHONETYPECODEID = PT.PHONETYPECODEID) or (P.PHONETYPECODEID is null and PT.PHONETYPECODEID is null))
where
(P.CONSTITUENTID = @ID) and PT.PHONEID is null
--Update existing entries in the PHONE table where phoneid is NOT null
update P
set
P.NUMBER = PT.NUMBER,
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 = coalesce(nullif(PT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), P.INFOSOURCECODEID),
P.DONOTTEXT = PT.DONOTTEXT,
P.INFOSOURCECOMMENTS = PT.INFOSOURCECOMMENTS
from
dbo.PHONE P
inner join @PHONENUMBERTABLE PT on P.ID = PT.PHONEID
where
(P.CONSTITUENTID = @ID)
-- Update matching phone numbers in the household.
-- Per 271319, do *not* update ISPRIMARY on the matching phone numbers.
-- Per 372612, do not update the matching spouse DONOTCALL and ENDDATE if the constituent update end date rule (UPDATEDBYRULES) is enabled
update dbo.PHONE
set
PHONE.NUMBER = PT.NUMBER,
PHONE.PHONETYPECODEID = coalesce(nullif(PT.PHONETYPECODEID, '00000000-0000-0000-0000-000000000000'), PHONE.PHONETYPECODEID),
PHONE.DONOTCALL = case when PT.UPDATEDBYRULES = 0 then PT.DONOTCALL else PHONE.DONOTCALL end,
PHONE.SEQUENCE = coalesce(nullif(PT.SEQUENCE,''), PHONE.SEQUENCE),
PHONE.COUNTRYID = PT.COUNTRYID,
PHONE.SEASONALSTARTDATE=PT.SEASONALSTARTDATE,
PHONE.SEASONALENDDATE=PT.SEASONALENDDATE,
PHONE.STARTTIME=PT.STARTTIME,
PHONE.ENDTIME=PT.ENDTIME,
PHONE.STARTDATE = PT.STARTDATE,
PHONE.ENDDATE = case when PT.UPDATEDBYRULES = 0 then PT.ENDDATE else PHONE.ENDDATE end,
PHONE.CHANGEDBYID = @CHANGEAGENTID,
PHONE.DATECHANGED = @CURRENTDATE,
PHONE.INFOSOURCECODEID = coalesce(nullif(PT.INFOSOURCECODEID, '00000000-0000-0000-0000-000000000000'), PHONE.INFOSOURCECODEID),
PHONE.DONOTTEXT = PT.DONOTTEXT,
PHONE.INFOSOURCECOMMENTS = PT.INFOSOURCECOMMENTS
from
dbo.PHONE
inner join @PHONENUMBERTABLE PT on PHONE.NUMBER = PT.OLDNUMBER
and (
(PHONE.PHONETYPECODEID is not null and PT.OLDPHONETYPECODEID is not null and PHONE.PHONETYPECODEID = PT.OLDPHONETYPECODEID) or (PHONE.PHONETYPECODEID is null and PT.OLDPHONETYPECODEID is null)
)
where
((PHONE.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 PT.PHONEID is null and not exists(
select ID
from dbo.PHONE
where
CONSTITUENTID = @ID and
NUMBER = PT.NUMBER and ((PHONETYPECODEID is not null and PT.PHONETYPECODEID is not null and PHONETYPECODEID = PT.PHONETYPECODEID) or (PHONETYPECODEID is null and PT.PHONETYPECODEID is null)))
declare @PHONENUMBERSTOINSERT table
(
ROW int,
ISPRIMARY bit,
MATCHINGCONSTITUENTID uniqueidentifier,
MATCHINGCONSTITUENTHASPRIMARY bit
)
--create a table of phones to add to the constituent's household and members of the household
insert into @PHONENUMBERSTOINSERT
select
PT.ROW, PT.ISPRIMARY, MC.CONSTITID, case when PHONE.ID is null then 0 else 1 end
from
@MATCHINGCONSTITUENTS MC
join @PHONENUMBERTABLE PT on PT.UPDATEHOUSEHOLD = 1
left join dbo.PHONE on PHONE.CONSTITUENTID = MC.CONSTITID and PHONE.ISPRIMARY = 1
where
PT.PHONEID is null
and not exists
(
select
PHONE.CONSTITUENTID
from
dbo.PHONE
where
PHONE.CONSTITUENTID = MC.CONSTITID and
PHONE.NUMBERNOFORMAT = dbo.UFN_PHONE_REMOVEFORMATTING(PT.NUMBER) and
(
(PHONE.PHONETYPECODEID is not null and PT.PHONETYPECODEID is not null and PHONE.PHONETYPECODEID = PT.PHONETYPECODEID) or
(PHONE.PHONETYPECODEID is null and PT.PHONETYPECODEID is null)
)
)
--for the household or members who do not already have a primary phone, choose one of the phone to insert as the primary
update
@PHONENUMBERSTOINSERT
set
ISPRIMARY = 1
from
@PHONENUMBERSTOINSERT P
inner join
(
select
min(ROW) as ROW,
MATCHINGCONSTITUENTID
from
@PHONENUMBERSTOINSERT
where
MATCHINGCONSTITUENTHASPRIMARY = 0
group by
MATCHINGCONSTITUENTID
having
sum(cast(ISPRIMARY as int)) = 0
) MATCHINGCONSTITUENTSWITHNOPRIMARYPHONE
on P.ROW = MATCHINGCONSTITUENTSWITHNOPRIMARYPHONE.ROW and P.MATCHINGCONSTITUENTID = MATCHINGCONSTITUENTSWITHNOPRIMARYPHONE.MATCHINGCONSTITUENTID
--add the new phones to the household and members
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
PHONETOINSERT.MATCHINGCONSTITUENTID,
PT.PHONETYPECODEID,
PT.NUMBER,
case PHONETOINSERT.MATCHINGCONSTITUENTHASPRIMARY
when 1
then 0 --if the constituent already has a primary phone, add the new phone as non-primary
else
PHONETOINSERT.ISPRIMARY
end,
PT.DONOTCALL,
PT.SEQUENCE,
PT.COUNTRYID,
PT.SEASONALSTARTDATE,
PT.SEASONALENDDATE,PT.STARTTIME,
PT.ENDTIME,
PT.STARTDATE,
PT.ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@INFOSOURCECODEID,
PT.DONOTTEXT,
PT.INFOSOURCECOMMENTS
from
@PHONENUMBERSTOINSERT PHONETOINSERT
inner join @PHONENUMBERTABLE PT on PHONETOINSERT.ROW = PT.ROW
--If no primary phone number exists at the end of the updates and inserts, set the primary phone to the existing primary phone before the changes were made.
if @EXISTINGPRIMARYPHONEID is not null and not exists (select 1 from dbo.PHONE where CONSTITUENTID = @ID and ISPRIMARY = 1)
begin
update
dbo.PHONE
set
ISPRIMARY = 1,
DONOTCALL = @EXISTINGPRIMARYPHONEDONOTCALL,
ENDDATE = NULL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @EXISTINGPRIMARYPHONEID;
end
end
-----------------------------------------------------------------
--Social media accounts
--===============================================================
if exists(select top 1 * from @SOCIALMEDIAACCOUNTTABLE)
begin
--Update existing entries in the SOCIALMEDIAACCOUNT table where SOCIALMEDIAACCOUNTID is null
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
and (SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID = ST.SOCIALMEDIAACCOUNTTYPECODEID or (SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID is null and ST.SOCIALMEDIAACCOUNTTYPECODEID is null))
where
SOCIALMEDIAACCOUNT.CONSTITUENTID = @ID and ST.SOCIALMEDIAACCOUNTID is null;
--Update existing entries in the SOCIALMEDIAACCOUNT table for records with socialmediaaccountid
update
dbo.SOCIALMEDIAACCOUNT
set
SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID = ST.SOCIALMEDIASERVICEID,
SOCIALMEDIAACCOUNT.USERID = ST.USERID,
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.ID = ST.SOCIALMEDIAACCOUNTID
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
ST.SOCIALMEDIAACCOUNTID is null
and not exists(
select
ID
from
dbo.SOCIALMEDIAACCOUNT
where
CONSTITUENTID = @ID
and SOCIALMEDIASERVICEID = ST.SOCIALMEDIASERVICEID
and USERID = ST.USERID
and (
SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID = ST.SOCIALMEDIAACCOUNTTYPECODEID or
(
SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID is null and ST.SOCIALMEDIAACCOUNTTYPECODEID is null
)
)
)
end
-----------------------------------------------------------------
--Relationships
--===============================================================
if exists(select top 1 * from @RELATIONSHIPTABLE)
begin
begin try
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 try
begin catch
exec dbo.USP_RAISE_ERROR 2;
return 1;
end catch
end
-----------------------------------------------------------------
--Constituencies
--===============================================================
-- moved logic in a SP so it can be re-used (ERB needs this functionality)
exec dbo.USP_CONSTITUENTUPDATEBATCH_ADDCONSTITUENCIES @CONSTITUENCYTABLE, @ID, @PROSPECTMANAGERFUNDRAISERID, @CHANGEAGENTID;
-----------------------------------------------------------------
--Primary business information
--===============================================================
--There's something in the business fields, so go forward with updating the business
if nullif(@BUSINESS_ID, '00000000-0000-0000-0000-000000000000') is not null or (@BUSINESS_NAME is not null and @BUSINESS_NAME <> '') or (@BUSINESS_LOOKUPID is not null and @BUSINESS_LOOKUPID <> '') begin
declare @BUSINESS_ISNEWRECORD bit = 0
--Update info for the current business if the current business is still the primary business
if (@OLD_BUSINESS_ID is not null and @BUSINESS_ID is not null and @OLD_BUSINESS_ID = @BUSINESS_ID) or (coalesce(@OLD_BUSINESS_LOOKUPID, '') <> '' and coalesce(@BUSINESS_LOOKUPID, '') <> '' and @OLD_BUSINESS_LOOKUPID = @BUSINESS_LOOKUPID) begin
if @BUSINESS_ID is null
set @BUSINESS_ID = @OLD_BUSINESS_ID
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
--Update the relationship
begin try
update BR
set
BR.RELATIONSHIPTYPECODEID = coalesce(nullif(@BUSINESS_RELATIONSHIPTYPECODEID, '00000000-0000-0000-0000-000000000000'), BR.RELATIONSHIPTYPECODEID),
BR.RECIPROCALTYPECODEID = coalesce(nullif(@BUSINESS_RECIPROCALTYPECODEID, '00000000-0000-0000-0000-000000000000'), BR.RECIPROCALTYPECODEID),
BR.CHANGEDBYID = @CHANGEAGENTID,
BR.DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP BR
where
BR.RELATIONSHIPCONSTITUENTID = @ID and BR.ISPRIMARYBUSINESS = 1
end try
begin catch
exec dbo.USP_RAISE_ERROR 3;
return 1;
end catch
end
--Add a new business relationship if one does not currently exist or if it is different from current
else if @OLD_BUSINESS_ID is null or (@OLD_BUSINESS_ID <> @BUSINESS_ID) or (@BUSINESS_NAME is not null and @BUSINESS_NAME <> '') begin
--If the new business is not the old business do not use any fields auto-loaded by the template that were not included in the batch.
--These are the old business's fields.
if @BATCHROWID is not null
select
@BUSINESS_ID = [BUSINESS_ID],
@BUSINESS_ADDRESSBLOCK = [BUSINESS_ADDRESSBLOCK],
@BUSINESS_ADDRESSTYPECODEID = [BUSINESS_ADDRESSTYPECODEID],
@BUSINESS_CART = [BUSINESS_CART],
@BUSINESS_CITY = [BUSINESS_CITY],
@BUSINESS_COUNTRYID = [BUSINESS_COUNTRYID],
@BUSINESS_DONOTMAIL = coalesce([BUSINESS_DONOTMAIL], '0'),
@BUSINESS_DONOTMAILREASONCODEID = [BUSINESS_DONOTMAILREASONCODEID],
@BUSINESS_DPC = [BUSINESS_DPC],
@BUSINESS_EMAILADDRESS = [BUSINESS_EMAILADDRESS],
@BUSINESS_EMAILADDRESSTYPECODEID = [BUSINESS_EMAILADDRESSTYPECODEID],
@BUSINESS_INDUSTRYCODEID = [BUSINESS_INDUSTRYCODEID],
@BUSINESS_LOOKUPID = [BUSINESS_LOOKUPID],
@BUSINESS_LOT = [BUSINESS_LOT],
@BUSINESS_NAME = [BUSINESS_NAME],
@BUSINESS_NUMEMPLOYEES = [BUSINESS_NUMEMPLOYEES],
@BUSINESS_NUMSUBSIDIARIES = [BUSINESS_NUMSUBSIDIARIES],
@BUSINESS_PARENTCORPID = [BUSINESS_PARENTCORPID],
@BUSINESS_PHONENUMBER = [BUSINESS_PHONENUMBER],
@BUSINESS_PHONETYPECODEID = [BUSINESS_PHONETYPECODEID],
@BUSINESS_RELATIONSHIPTYPECODEID = [BUSINESS_RELATIONSHIPTYPECODEID],
@BUSINESS_RECIPROCALTYPECODEID = [BUSINESS_RECIPROCALTYPECODEID],
@BUSINESS_STATEID = [BUSINESS_STATEID],
@BUSINESS_WEBADDRESS = [BUSINESS_WEBADDRESS],
@BUSINESS_POSTCODE = [BUSINESS_POSTCODE],
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = [BUSINESS_RELATIONSHIPTYPECODEID],
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = [BUSINESS_RECIPROCALTYPECODEID],
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = [BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS],
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = [BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR],
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = [BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS],
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = [BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR],
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID = [BUSINESS_PRIMARYRECOGNITIONTYPECODEID],
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = [BUSINESS_RECIPROCALRECOGNITIONTYPECODEID],
@BUSINESS_EMAILADDRESSSTARTDATE = [BUSINESS_EMAILADDRESSSTARTDATE]
from
dbo.BATCHCONSTITUENTUPDATE
where
BATCHCONSTITUENTUPDATE.ID = @BATCHROWID
--Insert a new business into the CONSTITUENT table if an existing one wasn't chosen
if @BUSINESS_ID is null and @BUSINESS_NAME is not null and @BUSINESS_NAME <> '' begin
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
)
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 = @BUSINESS_ID
and CONSITTUENTALLNAMES.KEYNAME <> '';
end
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 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
--Update the organization data for the business
update dbo.ORGANIZATIONDATA
set
INDUSTRYCODEID = coalesce(nullif(@BUSINESS_INDUSTRYCODEID, '00000000-0000-0000-0000-000000000000'), INDUSTRYCODEID),
NUMEMPLOYEES = coalesce(nullif(@BUSINESS_NUMEMPLOYEES, '0'), NUMEMPLOYEES),
NUMSUBSIDIARIES = coalesce(nullif(@BUSINESS_NUMSUBSIDIARIES, '0'), NUMSUBSIDIARIES),
PARENTCORPID = coalesce(nullif(@BUSINESS_PARENTCORPID, '00000000-0000-0000-0000-000000000000'), PARENTCORPID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @BUSINESS_ID
-- update the old business relationship to not be primary
if not @OLD_BUSINESS_ID is null begin
update
dbo.RELATIONSHIP
set
ISPRIMARYBUSINESS = 0
where
RELATIONSHIPCONSTITUENTID = @ID and
RECIPROCALCONSTITUENTID = @ID and
ISPRIMARYBUSINESS = 1;
end
--Add a new relationship for the business
if not exists(
select ID from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @ID and RECIPROCALCONSTITUENTID = @BUSINESS_ID)
begin
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
end
--Update the new business relationship
begin try
update BR
set
BR.RELATIONSHIPTYPECODEID = coalesce(nullif(@BUSINESS_RELATIONSHIPTYPECODEID, '00000000-0000-0000-0000-000000000000'), BR.RELATIONSHIPTYPECODEID),
BR.RECIPROCALTYPECODEID = coalesce(nullif(@BUSINESS_RECIPROCALTYPECODEID, '00000000-0000-0000-0000-000000000000'), BR.RECIPROCALTYPECODEID),
BR.ISPRIMARYBUSINESS = 1,
BR.CHANGEDBYID = @CHANGEAGENTID,
BR.DATECHANGED = @CURRENTDATE
from
dbo.RELATIONSHIP BR
where
BR.RELATIONSHIPCONSTITUENTID = @ID and BR.RECIPROCALCONSTITUENTID = @BUSINESS_ID
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
end
--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
if not exists(select 1 from dbo.CONSTITUENTSOLICITCODE SC where SC.SOLICITCODEID = @NOEMAILSOLICITCODEID and SC.CONSTITUENTID = @ID)
begin
exec dbo.USP_CONSTITUENTSOLICITCODE_ADD null, @NOEMAILSOLICITCODEID, @ID, null, null, '', @CHANGEAGENTID;
end
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
end
return 0;