USP_BBACONSTITUENTFILEIMPORT
This procedure is used by the Target Analytics Constituent File Import Record Operation for saving the information from a Target Analytics Constituent Import File to the database.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @LOOKUPID | nvarchar(100) | IN | |
| @SYSRECID | int | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @GENDERCODE | int | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @PHONETYPECODEID | uniqueidentifier | IN | |
| @PHONENUMBER | nvarchar(100) | IN | |
| @WINTERADDRESSTYPECODEID | uniqueidentifier | IN | |
| @WINTERCOUNTRYID | uniqueidentifier | IN | |
| @WINTERSTATEID | uniqueidentifier | IN | |
| @WINTERADDRESSBLOCK | nvarchar(150) | IN | |
| @WINTERCITY | nvarchar(50) | IN | |
| @WINTERPOSTCODE | nvarchar(12) | IN | |
| @WINTERPHONETYPECODEID | uniqueidentifier | IN | |
| @WINTERPHONENUMBER | nvarchar(100) | IN | |
| @BUSINESSNAME | nvarchar(100) | IN | |
| @EMPLOYEERELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @EMPLOYERRELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @BUSINESSADDRESSTYPECODEID | uniqueidentifier | IN | |
| @BUSINESSCOUNTRYID | uniqueidentifier | IN | |
| @BUSINESSSTATEID | uniqueidentifier | IN | |
| @BUSINESSADDRESSBLOCK | nvarchar(150) | IN | |
| @BUSINESSCITY | nvarchar(50) | IN | |
| @BUSINESSPOSTCODE | nvarchar(12) | IN | |
| @SPOUSELOOKUPID | nvarchar(100) | IN | |
| @SPOUSEKEYNAME | nvarchar(100) | IN | |
| @SPOUSEFIRSTNAME | nvarchar(50) | IN | |
| @SPOUSEMIDDLENAME | nvarchar(50) | IN | |
| @SPOUSETITLECODEID | uniqueidentifier | IN | |
| @SPOUSESUFFIXCODEID | uniqueidentifier | IN | |
| @SPOUSERELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @FIRSTGIFTAMOUNT | money | IN | |
| @FIRSTGIFTDATE | datetime | IN | |
| @LARGESTGIFTAMOUNT | money | IN | |
| @LARGESTGIFTDATE | datetime | IN | |
| @LATESTGIFTAMOUNT | money | IN | |
| @LATESTGIFTDATE | datetime | IN | |
| @TOTALGIFTSGIVEN | int | IN | |
| @TOTALGIFTAMOUNT | money | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @IMPORTID | nvarchar(100) | IN | |
| @SPOUSEIMPORTID | nvarchar(100) | IN | |
| @ALTERNATELOOKUPIDTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @ADDRESSDONOTMAIL | bit | IN | |
| @WINTERADDRESSDONOTMAIL | bit | IN | |
| @BUSINESSPHONETYPECODEID | uniqueidentifier | IN | |
| @BUSINESSPHONENUMBER | nvarchar(100) | IN | |
| @BUSINESSPOSITION | nvarchar(100) | IN | |
| @NICKNAME | nvarchar(50) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @SPOUSENICKNAME | nvarchar(50) | IN | |
| @SPOUSEMAIDENNAME | nvarchar(100) | IN | |
| @SPOUSEBIRTHDATE | UDT_FUZZYDATE | IN | |
| @FIRSTGIFTDESIGNATION | nvarchar(100) | IN | |
| @FIRSTGIFTTYPE | nvarchar(100) | IN | |
| @LARGESTGIFTDESIGNATION | nvarchar(100) | IN | |
| @LARGESTGIFTTYPE | nvarchar(100) | IN | |
| @LATESTGIFTDESIGNATION | nvarchar(100) | IN | |
| @LATESTGIFTTYPE | nvarchar(100) | IN | |
| @PROSPECTMANAGERKEYNAME | nvarchar(100) | IN | |
| @PROSPECTMANAGERFIRSTNAME | nvarchar(50) | IN |
Definition
Copy
CREATE procedure dbo.USP_BBACONSTITUENTFILEIMPORT(
@CONSTITUENTID uniqueidentifier output,
@LOOKUPID nvarchar(100),
@SYSRECID int,
@KEYNAME nvarchar(100),
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@GENDERCODE int,
@BIRTHDATE dbo.UDT_FUZZYDATE,
@TITLECODEID uniqueidentifier,
@SUFFIXCODEID uniqueidentifier,
@ADDRESSTYPECODEID uniqueidentifier,
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@POSTCODE nvarchar(12),
@PHONETYPECODEID uniqueidentifier,
@PHONENUMBER nvarchar(100),
@WINTERADDRESSTYPECODEID uniqueidentifier,
@WINTERCOUNTRYID uniqueidentifier,
@WINTERSTATEID uniqueidentifier,
@WINTERADDRESSBLOCK nvarchar(150),
@WINTERCITY nvarchar(50),
@WINTERPOSTCODE nvarchar(12),
@WINTERPHONETYPECODEID uniqueidentifier,
@WINTERPHONENUMBER nvarchar(100),
@BUSINESSNAME nvarchar(100),
@EMPLOYEERELATIONSHIPTYPECODEID uniqueidentifier,
@EMPLOYERRELATIONSHIPTYPECODEID uniqueidentifier,
@BUSINESSADDRESSTYPECODEID uniqueidentifier,
@BUSINESSCOUNTRYID uniqueidentifier,
@BUSINESSSTATEID uniqueidentifier,
@BUSINESSADDRESSBLOCK nvarchar(150),
@BUSINESSCITY nvarchar(50),
@BUSINESSPOSTCODE nvarchar(12),
@SPOUSELOOKUPID nvarchar(100),
@SPOUSEKEYNAME nvarchar(100),
@SPOUSEFIRSTNAME nvarchar(50),
@SPOUSEMIDDLENAME nvarchar(50),
@SPOUSETITLECODEID uniqueidentifier,
@SPOUSESUFFIXCODEID uniqueidentifier,
@SPOUSERELATIONSHIPTYPECODEID uniqueidentifier,
@FIRSTGIFTAMOUNT money,
@FIRSTGIFTDATE datetime,
@LARGESTGIFTAMOUNT money,
@LARGESTGIFTDATE datetime,
@LATESTGIFTAMOUNT money,
@LATESTGIFTDATE datetime,
@TOTALGIFTSGIVEN integer,
@TOTALGIFTAMOUNT money,
@CHANGEAGENTID uniqueidentifier,
@IMPORTID nvarchar(100) = '',
@SPOUSEIMPORTID nvarchar(100) = '',
@ALTERNATELOOKUPIDTYPECODEID uniqueidentifier = null,
@EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@ADDRESSDONOTMAIL bit = 0,
@WINTERADDRESSDONOTMAIL bit = 0,
@BUSINESSPHONETYPECODEID uniqueidentifier = null,
@BUSINESSPHONENUMBER nvarchar(100) = '',
@BUSINESSPOSITION nvarchar(100) = '',
@NICKNAME nvarchar(50) = '',
@MAIDENNAME nvarchar(100) = '',
@SPOUSENICKNAME nvarchar(50) = '',
@SPOUSEMAIDENNAME nvarchar(100) = '',
@SPOUSEBIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@FIRSTGIFTDESIGNATION nvarchar(100) = '',
@FIRSTGIFTTYPE nvarchar(100) = '',
@LARGESTGIFTDESIGNATION nvarchar(100) = '',
@LARGESTGIFTTYPE nvarchar(100) = '',
@LATESTGIFTDESIGNATION nvarchar(100) = '',
@LATESTGIFTTYPE nvarchar(100) = '',
@PROSPECTMANAGERKEYNAME nvarchar(100) = '',
@PROSPECTMANAGERFIRSTNAME nvarchar(50) = ''
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SETID uniqueidentifier;
begin try
declare @EXISTINGCONSTITUENTID uniqueidentifier;
select
@EXISTINGCONSTITUENTID = C.ID
from
dbo.CONSTITUENT C
left join
dbo.TAIMPORTIDMAP MAP on MAP.ID = C.ID
where
MAP.LOOKUPID = @IMPORTID;
declare @MATCHEDLOOKUPIDONLY bit = 0;
if @EXISTINGCONSTITUENTID is null and len(@LOOKUPID) > 0 begin
select
@EXISTINGCONSTITUENTID = ID,
@MATCHEDLOOKUPIDONLY = 1
from
dbo.CONSTITUENT
where
LOOKUPID = @LOOKUPID and
ISGROUP = 0 and ISORGANIZATION = 0;
if @EXISTINGCONSTITUENTID is null begin
if (select count(ID) from dbo.ALTERNATELOOKUPID where ALTERNATELOOKUPID = @LOOKUPID) > 1
raiserror('The lookup ID specified relates to more than one alternate lookup ID in the database so a match cannot be determined.', 13, 1);
select
@EXISTINGCONSTITUENTID = A.CONSTITUENTID,
@MATCHEDLOOKUPIDONLY = 1
from
dbo.ALTERNATELOOKUPID A
left join dbo.CONSTITUENT C on A.CONSTITUENTID = C.ID
where
A.ALTERNATELOOKUPID = @LOOKUPID and
C.ISGROUP = 0 and C.ISORGANIZATION = 0;
end
end
declare @USEALTERNATECONSTITUENTLOOKUPID bit;
if @EXISTINGCONSTITUENTID is null begin
set @CONSTITUENTID = newid();
if @LOOKUPID = '' or (select count(ID) from dbo.CONSTITUENT where LOOKUPID = @LOOKUPID) = 0 begin
set @USEALTERNATECONSTITUENTLOOKUPID = 0;
end
else begin
set @USEALTERNATECONSTITUENTLOOKUPID = 1;
end
insert into dbo.CONSTITUENT (
ID,
CUSTOMIDENTIFIER,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NICKNAME,
MAIDENNAME,
TITLECODEID,
SUFFIXCODEID,
GENDERCODE,
BIRTHDATE,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
case when @USEALTERNATECONSTITUENTLOOKUPID = 0 then @LOOKUPID else '' end,
@KEYNAME,
@FIRSTNAME,
@MIDDLENAME,
@NICKNAME,
@MAIDENNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @MAIDENNAME is not null and @MAIDENNAME != ''
begin
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@CONSTITUENTID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
insert into dbo.TAIMPORTIDMAP (
ID,
CUSTOMIDENTIFIER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@IMPORTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*Start name format defaults*/
insert into dbo.[NAMEFORMAT] (
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION])
select
@CONSTITUENTID,
NFD.NAMEFORMATTYPECODEID,
NFD.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
NFD.PRIMARYADDRESSEE,
NFD.PRIMARYSALUTATION
from dbo.NAMEFORMATDEFAULT as NFD
where NFD.APPLYTOCODE = 0
/*End name format defaults*/
end
else begin
set @CONSTITUENTID = @EXISTINGCONSTITUENTID;
declare @MAIDENNAMECHANGED bit = 0;
select @MAIDENNAMECHANGED = case when @MAIDENNAME = CONSTITUENT.MAIDENNAME then 0 else 1 end
from dbo.CONSTITUENT
where CONSTITUENT.ID = @EXISTINGCONSTITUENTID;
if @LOOKUPID = '' or (select count(ID) from dbo.CONSTITUENT where ID <> @EXISTINGCONSTITUENTID and LOOKUPID = @LOOKUPID) = 0 begin
set @USEALTERNATECONSTITUENTLOOKUPID = 0;
end
else begin
set @USEALTERNATECONSTITUENTLOOKUPID = 1;
end
update
dbo.CONSTITUENT
set
CUSTOMIDENTIFIER = case when @MATCHEDLOOKUPIDONLY = 0 and @USEALTERNATECONSTITUENTLOOKUPID = 0 then @LOOKUPID else CUSTOMIDENTIFIER end,
KEYNAME = coalesce(nullif(@KEYNAME, ''), KEYNAME, ''),
FIRSTNAME = coalesce(nullif(@FIRSTNAME, ''), FIRSTNAME, ''),
MIDDLENAME = coalesce(nullif(@MIDDLENAME, ''), MIDDLENAME, ''),
NICKNAME = coalesce(nullif(@NICKNAME, ''), NICKNAME, ''),
MAIDENNAME = coalesce(nullif(@MAIDENNAME, ''), MAIDENNAME, ''),
TITLECODEID = coalesce(@TITLECODEID, TITLECODEID),
SUFFIXCODEID = coalesce(@SUFFIXCODEID, SUFFIXCODEID),
GENDERCODE = coalesce(nullif(@GENDERCODE, 0), GENDERCODE, 0),
BIRTHDATE = coalesce(nullif(@BIRTHDATE,'00000000'),BIRTHDATE,'00000000'),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.CONSTITUENT
where
ID = @EXISTINGCONSTITUENTID;
if @MAIDENNAMECHANGED = 1 and @MAIDENNAME is not null and @MAIDENNAME != ''
begin
if (select ID from dbo.ALIAS where CONSTITUENTID = @EXISTINGCONSTITUENTID and KEYNAME = @MAIDENNAME and FIRSTNAME = @FIRSTNAME and MIDDLENAME = @MIDDLENAME) is null
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@EXISTINGCONSTITUENTID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
if @MATCHEDLOOKUPIDONLY = 1 begin
if (select top 1 1 from TAIMPORTIDMAP where ID = @CONSTITUENTID) <> 0 begin
update
TAIMPORTIDMAP
set
CUSTOMIDENTIFIER = @IMPORTID
where
ID = @CONSTITUENTID
end else begin
insert into dbo.TAIMPORTIDMAP (
ID,
CUSTOMIDENTIFIER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@IMPORTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
end
if @USEALTERNATECONSTITUENTLOOKUPID = 1 and @ALTERNATELOOKUPIDTYPECODEID is not null and @MATCHEDLOOKUPIDONLY = 0 begin
if not exists(select top 1 1 from dbo.ALTERNATELOOKUPID
where CONSTITUENTID = @CONSTITUENTID
and ALTERNATELOOKUPIDTYPECODEID = @ALTERNATELOOKUPIDTYPECODEID
and ALTERNATELOOKUPID = @LOOKUPID)
insert into dbo.[ALTERNATELOOKUPID] (
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID,
ALTERNATELOOKUPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@ALTERNATELOOKUPIDTYPECODEID,
@LOOKUPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @SYSRECID is not null and @SYSRECID > 0 begin
--remove any entries that are already using the given RE7INTEGRATIONCONSTITUENTMAP entry
delete
dbo.RE7INTEGRATIONCONSTITUENTMAP
where
RE7RECORDID = @SYSRECID and
ID <> @CONSTITUENTID;
if exists (select ID from dbo.RE7INTEGRATIONCONSTITUENTMAP where ID = @CONSTITUENTID) begin
update
dbo.RE7INTEGRATIONCONSTITUENTMAP
set
RE7RECORDID = @SYSRECID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @CONSTITUENTID;
end
else begin
insert into dbo.RE7INTEGRATIONCONSTITUENTMAP (
ID,
RE7RECORDID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@SYSRECID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Constituent address
if (@STATEID is not null) or len(@ADDRESSBLOCK) > 0 or len(@CITY) > 0 or len(@POSTCODE) > 0 begin
if not exists (select ID from dbo.ADDRESS where
CONSTITUENTID = @CONSTITUENTID and
COUNTRYID = @COUNTRYID and
STATEID = @STATEID and
ADDRESSBLOCK = @ADDRESSBLOCK and
CITY = @CITY and
POSTCODE = @POSTCODE) begin
update
dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @CONSTITUENTID;
insert into dbo.ADDRESS (
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
DONOTMAIL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@ADDRESSTYPECODEID,
1,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@ADDRESSDONOTMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Constituent phone
if len(@PHONENUMBER) > 0 begin
if not exists (select ID from dbo.PHONE where
CONSTITUENTID = @CONSTITUENTID and
NUMBER = @PHONENUMBER) begin
update
dbo.PHONE
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @CONSTITUENTID;
insert into dbo.PHONE (
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@PHONETYPECODEID,
@PHONENUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Constituent winter address
if (@WINTERSTATEID is not null) or len(@WINTERADDRESSBLOCK) > 0 or len(@WINTERCITY) > 0 or len(@WINTERPOSTCODE) > 0 begin
if not exists (select ID from dbo.ADDRESS where
CONSTITUENTID = @CONSTITUENTID and
COUNTRYID = @WINTERCOUNTRYID and
STATEID = @WINTERSTATEID and
ADDRESSBLOCK = @WINTERADDRESSBLOCK and
CITY = @WINTERCITY and
POSTCODE = @WINTERPOSTCODE) begin
insert into dbo.ADDRESS (
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
DONOTMAIL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@WINTERADDRESSTYPECODEID,
0,
@WINTERCOUNTRYID,
@WINTERSTATEID,
@WINTERADDRESSBLOCK,
@WINTERCITY,
@WINTERPOSTCODE,
@WINTERADDRESSDONOTMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Constituent winter phone
if len(@WINTERPHONENUMBER) > 0 begin
if not exists (select ID from dbo.PHONE where
CONSTITUENTID = @CONSTITUENTID and
NUMBER = @WINTERPHONENUMBER) begin
insert into dbo.PHONE (
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@WINTERPHONETYPECODEID,
@WINTERPHONENUMBER,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Constituent email address
if @EMAILADDRESSTYPECODEID is not null and len(@EMAILADDRESS) > 0 begin
if not exists (select ID from dbo.EMAILADDRESS where
CONSTITUENTID = @CONSTITUENTID and
EMAILADDRESS = @EMAILADDRESS) begin
declare @CURRENTPRIMARYEMAILID uniqueidentifier;
select
@CURRENTPRIMARYEMAILID = ID
from
dbo.EMAILADDRESS
where
ISPRIMARY = 1 and
CONSTITUENTID = @CONSTITUENTID;
update
dbo.EMAILADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @CONSTITUENTID;
BEGIN TRY
insert into dbo.EMAILADDRESS (
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@EMAILADDRESSTYPECODEID,
@EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
END TRY
BEGIN CATCH
--TA has requested that we do not fail the import if an email address is invalid.
--So we continue with import if the email is invalid, and rethrow for any other errors
if ERROR_MESSAGE() not like '%CK_EMAILADDRESS_EMAILADDRESS%'
BEGIN
exec dbo.USP_RAISE_ERROR;
END
--If we get here, then we are continuing with the import.
--Restore previous primary email in case it was changed.
update
dbo.EMAILADDRESS
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @CURRENTPRIMARYEMAILID
END CATCH
end
end
--Business
if len(@BUSINESSNAME) > 0 begin
declare @BUSINESSID uniqueidentifier;
select
@BUSINESSID = B.ID
from
dbo.CONSTITUENT B left join dbo.ADDRESS BA on BA.CONSTITUENTID = B.ID and BA.ISPRIMARY = 1
where
B.NAME = @BUSINESSNAME and
(BA.COUNTRYID = @BUSINESSCOUNTRYID or BA.COUNTRYID is null) and
(BA.STATEID = @BUSINESSSTATEID or (BA.STATEID is null and @BUSINESSSTATEID is null)) and
(BA.ADDRESSBLOCK = @BUSINESSADDRESSBLOCK or (BA.ADDRESSBLOCK is null and @BUSINESSADDRESSBLOCK is null) or (BA.ADDRESSBLOCK = '' and @BUSINESSADDRESSBLOCK is null) or (BA.ADDRESSBLOCK is null and @BUSINESSADDRESSBLOCK = '')) and
(BA.CITY = @BUSINESSCITY or (BA.CITY is null and @BUSINESSCITY is null) or (BA.CITY = '' and @BUSINESSCITY is null) or (BA.CITY is null and @BUSINESSCITY = '')) and
(BA.POSTCODE = @BUSINESSPOSTCODE or (BA.POSTCODE is null and @BUSINESSPOSTCODE is null) or (BA.POSTCODE = '' and @BUSINESSPOSTCODE is null) or (BA.POSTCODE is null and @BUSINESSPOSTCODE = '')) and
B.ISORGANIZATION = 1;
if @BUSINESSID is null begin
set @BUSINESSID = newid();
insert into dbo.CONSTITUENT (
ID,
KEYNAME,
ISORGANIZATION,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@BUSINESSID,
@BUSINESSNAME,
1,
1, --TA import should import business as constituents
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.ORGANIZATIONDATA (
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@BUSINESSID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--Business address
if (@BUSINESSSTATEID is not null) or len(@BUSINESSADDRESSBLOCK) > 0 or len(@BUSINESSCITY) > 0 or len(@BUSINESSPOSTCODE) > 0 begin
insert into dbo.ADDRESS (
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@BUSINESSID,
@BUSINESSADDRESSTYPECODEID,
1,
@BUSINESSCOUNTRYID,
@BUSINESSSTATEID,
@BUSINESSADDRESSBLOCK,
@BUSINESSCITY,
@BUSINESSPOSTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Business phone
if len(@BUSINESSPHONENUMBER ) > 0 begin
if not exists (select ID from dbo.PHONE where
CONSTITUENTID = @BUSINESSID and
NUMBER = @BUSINESSPHONENUMBER) begin
declare @BUSINESSPHONEISPRIMARY bit;
if (select ID from dbo.PHONE where CONSTITUENTID = @BUSINESSID and ISPRIMARY = 1) is null
set @BUSINESSPHONEISPRIMARY = 1;
else
set @BUSINESSPHONEISPRIMARY = 0;
insert into dbo.PHONE (
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@BUSINESSID,
@BUSINESSPHONETYPECODEID ,
@BUSINESSPHONENUMBER,
@BUSINESSPHONEISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Business relationship
declare @EXISTINGPRIMARYBUSINESSID uniqueidentifier;
select
@EXISTINGPRIMARYBUSINESSID = R.RECIPROCALCONSTITUENTID
from
dbo.RELATIONSHIP R
where
R.ISPRIMARYBUSINESS = 1 and
R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID;
if @EXISTINGPRIMARYBUSINESSID is null or @BUSINESSID <> @EXISTINGPRIMARYBUSINESSID begin
if not @EXISTINGPRIMARYBUSINESSID is null begin
-- update the existing relationship to not be the primary business one
update
dbo.RELATIONSHIP
set
ISPRIMARYBUSINESS = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ISPRIMARYBUSINESS = 1 and
RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
RECIPROCALCONSTITUENTID = @EXISTINGPRIMARYBUSINESSID;
end
-- add the primary business relationship
set @SETID = newid();
insert into dbo.RELATIONSHIPSET (
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RELATIONSHIP (
RELATIONSHIPSETID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
ISPRIMARYBUSINESS,
POSITION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SETID,
@CONSTITUENTID,
@BUSINESSID,
@EMPLOYEERELATIONSHIPTYPECODEID,
@EMPLOYERRELATIONSHIPTYPECODEID,
0,
1,
@BUSINESSPOSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if (len(@BUSINESSPOSITION) > 0)
insert into dbo.RELATIONSHIPJOBINFO (
RELATIONSHIPSETID,
JOBTITLE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SETID,
@BUSINESSPOSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Spouse
if (len(@SPOUSEIMPORTID) > 0 or len(@SPOUSEFIRSTNAME) > 0) and (@SPOUSEKEYNAME is null or @SPOUSEKEYNAME = '') begin
set @SPOUSEKEYNAME = @KEYNAME;
end
declare @SPOUSEID uniqueidentifier;
declare @SPOUSEMATCHEDLOOKUPIDONLY bit = 0;
if len(@SPOUSEIMPORTID) > 0 begin
select
@SPOUSEID = S.ID
from
dbo.CONSTITUENT S
left join
dbo.TAIMPORTIDMAP MAP on MAP.ID = S.ID
where
MAP.LOOKUPID = @SPOUSEIMPORTID;
if @SPOUSEID is null and len(@SPOUSELOOKUPID) > 0 begin
select
@SPOUSEID = ID,
@SPOUSEMATCHEDLOOKUPIDONLY = 1
from
dbo.CONSTITUENT
where
LOOKUPID = @SPOUSELOOKUPID and
ISGROUP = 0 and ISORGANIZATION = 0;
if @SPOUSEID is null begin
if (select count(ID) from dbo.ALTERNATELOOKUPID where ALTERNATELOOKUPID = @SPOUSELOOKUPID) > 1
raiserror('The spouse lookup ID specified relates to more than one alternate lookup ID in the database so a match cannot be determined.', 13, 1);
select
@SPOUSEID = A.CONSTITUENTID,
@SPOUSEMATCHEDLOOKUPIDONLY = 1
from
dbo.ALTERNATELOOKUPID A
left join dbo.CONSTITUENT C on A.CONSTITUENTID = C.ID
where
A.ALTERNATELOOKUPID = @SPOUSELOOKUPID and
ISGROUP = 0 and ISORGANIZATION = 0;
end
end
end
if (len(@SPOUSELOOKUPID) > 0 and @SPOUSEID is null and len(@SPOUSEKEYNAME) <= 0) begin
raiserror('A spouse lookup ID has been included, but no spouse can be added or linked. A last name, first name, matching lookup ID, or matching import ID is required.',13,1);
end
if not @SPOUSEID is null or len(@SPOUSEKEYNAME) > 0 begin
declare @USEALTERNATESPOUSELOOKUPID bit;
if @SPOUSEID is null begin
set @SPOUSEID = newid();
if isnull(@SPOUSELOOKUPID, '') = '' or (select count(ID) from dbo.CONSTITUENT where LOOKUPID = @SPOUSELOOKUPID) = 0 begin
set @USEALTERNATESPOUSELOOKUPID = 0;
end
else begin
set @USEALTERNATESPOUSELOOKUPID = 1;
end
insert into dbo.CONSTITUENT (
ID,
CUSTOMIDENTIFIER,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NICKNAME,
MAIDENNAME,
TITLECODEID,
SUFFIXCODEID,
BIRTHDATE,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
case when @USEALTERNATESPOUSELOOKUPID = 0 then @SPOUSELOOKUPID else '' end,
@SPOUSEKEYNAME,
@SPOUSEFIRSTNAME,
@SPOUSEMIDDLENAME,
@SPOUSENICKNAME,
@SPOUSEMAIDENNAME,
@SPOUSETITLECODEID,
@SPOUSESUFFIXCODEID,
@SPOUSEBIRTHDATE,
dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT(),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @SPOUSEMAIDENNAME is not null and @SPOUSEMAIDENNAME != ''
begin
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@SPOUSEID, @SPOUSEMAIDENNAME, @SPOUSEFIRSTNAME, @SPOUSEMIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
insert into dbo.TAIMPORTIDMAP (
ID,
CUSTOMIDENTIFIER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@SPOUSEIMPORTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*Start name format defaults*/
insert into dbo.[NAMEFORMAT]
([CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION])
select
@SPOUSEID,
NFD.NAMEFORMATTYPECODEID,
NFD.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
NFD.PRIMARYADDRESSEE,
NFD.PRIMARYSALUTATION
from dbo.NAMEFORMATDEFAULT as NFD
where NFD.APPLYTOCODE = 0
/*End name format defaults*/
end
else begin
if isnull(@SPOUSELOOKUPID, '') = '' or (select count(ID) from dbo.CONSTITUENT where LOOKUPID = @SPOUSELOOKUPID and ID <> @SPOUSEID) = 0 begin
set @USEALTERNATESPOUSELOOKUPID = 0;
end
else begin
set @USEALTERNATESPOUSELOOKUPID = 1;
end
declare @SPOUSEMAIDENNAMECHANGED bit = 0;
select @SPOUSEMAIDENNAMECHANGED = case when @SPOUSEMAIDENNAME = CONSTITUENT.MAIDENNAME then 0 else 1 end
from dbo.CONSTITUENT
where CONSTITUENT.ID = @SPOUSEID;
update
dbo.CONSTITUENT
set
CUSTOMIDENTIFIER = case when @SPOUSEMATCHEDLOOKUPIDONLY = 0 and @USEALTERNATESPOUSELOOKUPID = 0 then @SPOUSELOOKUPID else CUSTOMIDENTIFIER end,
KEYNAME = coalesce(nullif(@SPOUSEKEYNAME, ''), KEYNAME, ''),
FIRSTNAME = coalesce(nullif(@SPOUSEFIRSTNAME, ''), FIRSTNAME, ''),
MIDDLENAME = coalesce(nullif(@SPOUSEMIDDLENAME, ''), MIDDLENAME, ''),
NICKNAME = coalesce(nullif(@SPOUSENICKNAME, ''), NICKNAME, ''),
MAIDENNAME = coalesce(nullif(@SPOUSEMAIDENNAME, ''), MAIDENNAME, ''),
TITLECODEID = coalesce(@SPOUSETITLECODEID, TITLECODEID),
SUFFIXCODEID = coalesce(@SPOUSESUFFIXCODEID, SUFFIXCODEID),
BIRTHDATE = coalesce(nullif(@SPOUSEBIRTHDATE,'00000000'),BIRTHDATE,'00000000'),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.CONSTITUENT
where
ID = @SPOUSEID;
if @SPOUSEMAIDENNAMECHANGED = 1 and @SPOUSEMAIDENNAME is not null and @SPOUSEMAIDENNAME != ''
begin
if (select ID from dbo.ALIAS where CONSTITUENTID = @SPOUSEID and KEYNAME = @SPOUSEMAIDENNAME and FIRSTNAME = @SPOUSEFIRSTNAME and MIDDLENAME = @SPOUSEMIDDLENAME) is null
insert into dbo.ALIAS
(CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(@SPOUSEID, @SPOUSEMAIDENNAME, @SPOUSEFIRSTNAME, @SPOUSEMIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
end
if @SPOUSEMATCHEDLOOKUPIDONLY = 1 begin
if (select top 1 1 from TAIMPORTIDMAP where ID = @SPOUSEID) <> 0 begin
update
TAIMPORTIDMAP
set
CUSTOMIDENTIFIER = @SPOUSEIMPORTID
where
ID = @SPOUSEID
end else begin
insert into dbo.TAIMPORTIDMAP (
ID,
CUSTOMIDENTIFIER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@SPOUSEIMPORTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
end
if @USEALTERNATESPOUSELOOKUPID = 1 and @ALTERNATELOOKUPIDTYPECODEID is not null and @SPOUSEMATCHEDLOOKUPIDONLY = 0 begin
if not exists(select top 1 1 from dbo.ALTERNATELOOKUPID
where CONSTITUENTID = @SPOUSEID
and ALTERNATELOOKUPIDTYPECODEID = @ALTERNATELOOKUPIDTYPECODEID
and ALTERNATELOOKUPID = @SPOUSELOOKUPID)
insert into dbo.[ALTERNATELOOKUPID] (
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID,
ALTERNATELOOKUPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@ALTERNATELOOKUPIDTYPECODEID,
@SPOUSELOOKUPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
--Spouse address
if (@STATEID is not null) or len(@ADDRESSBLOCK) > 0 or len(@CITY) > 0 or len(@POSTCODE) > 0 begin
if not exists (select ID from dbo.ADDRESS where
CONSTITUENTID = @SPOUSEID and
COUNTRYID = @COUNTRYID and
STATEID = @STATEID and
ADDRESSBLOCK = @ADDRESSBLOCK and
CITY = @CITY and
POSTCODE = @POSTCODE) begin
declare @SPOUSEADDRESSISPRIMARY bit;
if (select ID from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1) is null
set @SPOUSEADDRESSISPRIMARY = 1;
else
set @SPOUSEADDRESSISPRIMARY = 0;
insert into dbo.ADDRESS (
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
DONOTMAIL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@ADDRESSTYPECODEID,
@SPOUSEADDRESSISPRIMARY,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@ADDRESSDONOTMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Spouse phone
if len(@PHONENUMBER) > 0 begin
if not exists (select ID from dbo.PHONE where
CONSTITUENTID = @SPOUSEID and
NUMBER = @PHONENUMBER) begin
declare @SPOUSEPHONEISPRIMARY bit;
if (select ID from dbo.PHONE where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1) is null
set @SPOUSEPHONEISPRIMARY = 1;
else
set @SPOUSEPHONEISPRIMARY = 0;
insert into dbo.PHONE (
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@PHONETYPECODEID,
@PHONENUMBER,
@SPOUSEPHONEISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Spouse winter address
if (@WINTERSTATEID is not null) or len(@WINTERADDRESSBLOCK) > 0 or len(@WINTERCITY) > 0 or len(@WINTERPOSTCODE) > 0 begin
if not exists (select ID from dbo.ADDRESS where
CONSTITUENTID = @SPOUSEID and
COUNTRYID = @WINTERCOUNTRYID and
STATEID = @WINTERSTATEID and
ADDRESSBLOCK = @WINTERADDRESSBLOCK and
CITY = @WINTERCITY and
POSTCODE = @WINTERPOSTCODE) begin
insert into dbo.ADDRESS (
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
DONOTMAIL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@WINTERADDRESSTYPECODEID,
0,
@WINTERCOUNTRYID,
@WINTERSTATEID,
@WINTERADDRESSBLOCK,
@WINTERCITY,
@WINTERPOSTCODE,
@WINTERADDRESSDONOTMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Spouse winter phone
if len(@WINTERPHONENUMBER) > 0 begin
if not exists (select ID from dbo.PHONE where
CONSTITUENTID = @SPOUSEID and
NUMBER = @WINTERPHONENUMBER) begin
insert into dbo.PHONE (
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@WINTERPHONETYPECODEID,
@WINTERPHONENUMBER,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
--Spouse email address
if @EMAILADDRESSTYPECODEID is not null and len(@EMAILADDRESS) > 0 begin
if not exists (select ID from dbo.EMAILADDRESS where
CONSTITUENTID = @SPOUSEID and
EMAILADDRESS = @EMAILADDRESS) begin
declare @SPOUSEEMAILISPRIMARY bit;
if (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1) is null
set @SPOUSEEMAILISPRIMARY = 1;
else
set @SPOUSEEMAILISPRIMARY = 0;
BEGIN TRY
insert into dbo.EMAILADDRESS (
CONSTITUENTID,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SPOUSEID,
@EMAILADDRESSTYPECODEID,
@EMAILADDRESS,
@SPOUSEEMAILISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
END TRY
BEGIN CATCH
--TA has requested that we do not fail the import if an email address is invalid.
--So we continue with import if the email is invalid, and rethrow for any other errors
if ERROR_MESSAGE() not like '%CK_EMAILADDRESS_EMAILADDRESS%'
BEGIN
exec dbo.USP_RAISE_ERROR;
END
END CATCH
end
end
--Spouse relationship
if exists(
select
ID
from
dbo.RELATIONSHIP
where
ISSPOUSE = 1 and
RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
RECIPROCALCONSTITUENTID <> @SPOUSEID) begin
raiserror('Constituent entered has an existing spouse that is different than the spouse entered in the row.',13,1);
end
if exists(
select
ID
from
dbo.RELATIONSHIP
where
ISSPOUSE = 1 and
RELATIONSHIPCONSTITUENTID = @SPOUSEID and
RECIPROCALCONSTITUENTID <> @CONSTITUENTID) begin
raiserror('Spouse entered has an existing spouse that is different than the constituent listed in the row.',13,1);
end
if exists(
select
GD.ID
from
dbo.GROUPMEMBER GM
inner join
dbo.GROUPDATA GD on GD.ID = GM.GROUPID and GD.GROUPTYPECODE = 0
where
GM.MEMBERID = @CONSTITUENTID and
not exists(
select
ID
from
dbo.GROUPMEMBER
where
GROUPID = GD.ID and
MEMBERID = @SPOUSEID)) 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 = @SPOUSEID and
not exists(
select
ID
from
dbo.GROUPMEMBER
where
GROUPID = GD.ID and
MEMBERID = @CONSTITUENTID)) begin
raiserror('Spouse entered is part of an existing household that does not include the listed constituent.',13,1);
end
if not exists (select ID from dbo.RELATIONSHIP where ISSPOUSE = 1 and (RELATIONSHIPCONSTITUENTID = @CONSTITUENTID or RELATIONSHIPCONSTITUENTID = @SPOUSEID)) begin
set @SETID = newid();
insert into dbo.RELATIONSHIPSET (
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RELATIONSHIP (
RELATIONSHIPSETID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
ISPRIMARYBUSINESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@SETID,
@CONSTITUENTID,
@SPOUSEID,
@SPOUSERELATIONSHIPTYPECODEID,
@SPOUSERELATIONSHIPTYPECODEID,
1,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
--Household for constituent and spouse
exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID, @SPOUSEID, 1, @CHANGEAGENTID, @CURRENTDATE,1,null;
end
if @FIRSTGIFTAMOUNT is not null and @LARGESTGIFTAMOUNT is not null and @LATESTGIFTAMOUNT is not null and @TOTALGIFTSGIVEN is not null and @TOTALGIFTAMOUNT is not null and
(@FIRSTGIFTAMOUNT > 0 or @LARGESTGIFTAMOUNT > 0 or @LATESTGIFTAMOUNT > 0 or @TOTALGIFTSGIVEN > 0 or @TOTALGIFTAMOUNT is not null) begin
if not exists (select ID from dbo.RE7INTEGRATIONGIVINGSUMMARY where ID = @CONSTITUENTID) begin
insert into dbo.RE7INTEGRATIONGIVINGSUMMARY (
ID,
FIRSTGIFTAMOUNT,
FIRSTGIFTDATE,
FIRSTGIFTDESIGNATION,
FIRSTGIFTTYPE,
LARGESTGIFTAMOUNT,
LARGESTGIFTDATE,
LARGESTGIFTDESIGNATION ,
LARGESTGIFTTYPE,
LATESTGIFTAMOUNT,
LATESTGIFTDATE,
LATESTGIFTDESIGNATION,
LATESTGIFTTYPE,
TOTALGIFTSGIVEN,
TOTALGIFTAMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@FIRSTGIFTAMOUNT,
@FIRSTGIFTDATE,
@FIRSTGIFTDESIGNATION,
@FIRSTGIFTTYPE,
@LARGESTGIFTAMOUNT,
@LARGESTGIFTDATE,
@LARGESTGIFTDESIGNATION ,
@LARGESTGIFTTYPE,
@LATESTGIFTAMOUNT,
@LATESTGIFTDATE,
@LATESTGIFTDESIGNATION,
@LATESTGIFTTYPE,
@TOTALGIFTSGIVEN,
@TOTALGIFTAMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else begin
update
dbo.RE7INTEGRATIONGIVINGSUMMARY
set
FIRSTGIFTAMOUNT = coalesce(nullif(@FIRSTGIFTAMOUNT, 0), FIRSTGIFTAMOUNT, 0),
FIRSTGIFTDATE = coalesce(@FIRSTGIFTDATE, FIRSTGIFTDATE),
FIRSTGIFTDESIGNATION = coalesce(nullif(@FIRSTGIFTDESIGNATION, ''), FIRSTGIFTDESIGNATION, ''),
FIRSTGIFTTYPE = coalesce(nullif(@FIRSTGIFTTYPE, ''), FIRSTGIFTTYPE, ''),
LARGESTGIFTAMOUNT = coalesce(nullif(@LARGESTGIFTAMOUNT, 0), LARGESTGIFTAMOUNT, 0),
LARGESTGIFTDATE = coalesce(@LARGESTGIFTDATE, LARGESTGIFTDATE),
LARGESTGIFTDESIGNATION = coalesce(nullif(@LARGESTGIFTDESIGNATION, ''), LARGESTGIFTDESIGNATION, ''),
LARGESTGIFTTYPE = coalesce(nullif(@LARGESTGIFTTYPE, ''), LARGESTGIFTTYPE, ''),
LATESTGIFTAMOUNT = coalesce(nullif(@LATESTGIFTAMOUNT, 0), LATESTGIFTAMOUNT, 0),
LATESTGIFTDATE = coalesce(@LATESTGIFTDATE, LATESTGIFTDATE),
LATESTGIFTDESIGNATION = coalesce(nullif(@LATESTGIFTDESIGNATION, ''), LATESTGIFTDESIGNATION, ''),
LATESTGIFTTYPE = coalesce(nullif(@LATESTGIFTTYPE, ''), LATESTGIFTTYPE, ''),
TOTALGIFTSGIVEN = coalesce(nullif(@TOTALGIFTSGIVEN, 0), TOTALGIFTSGIVEN, 0),
TOTALGIFTAMOUNT = coalesce(nullif(@TOTALGIFTAMOUNT, 0), TOTALGIFTAMOUNT, 0),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RE7INTEGRATIONGIVINGSUMMARY
where
ID = @CONSTITUENTID;
end
exec dbo.USP_WEALTHCAPACITY_UPDATE @CONSTITUENTID, @CHANGEAGENTID;
end
if @PROSPECTMANAGERKEYNAME is not null and len(@PROSPECTMANAGERKEYNAME) > 0 begin
declare @PROSPECTMANAGERID uniqueidentifier;
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
select
@PROSPECTMANAGERID = C.ID
from
dbo.CONSTITUENT C
inner join
dbo.FUNDRAISERDATERANGE FDR
on
FDR.CONSTITUENTID = C.ID
where
C.KEYNAME = @PROSPECTMANAGERKEYNAME and
C.FIRSTNAME = @PROSPECTMANAGERFIRSTNAME and
(FDR.DATEFROM is null or @CURRENTDATEEARLIESTTIME >= FDR.DATEFROM) and
(FDR.DATETO is null or @CURRENTDATEEARLIESTTIME <= FDR.DATETO);
if @PROSPECTMANAGERID is null begin
set @PROSPECTMANAGERID = newid();
insert into dbo.CONSTITUENT (
ID,
KEYNAME,
FIRSTNAME,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@PROSPECTMANAGERID,
@PROSPECTMANAGERKEYNAME,
@PROSPECTMANAGERFIRSTNAME,
1, --TA import we always want these to be constituents
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.FUNDRAISERDATERANGE (
CONSTITUENTID,
DATEFROM,
DATETO,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@PROSPECTMANAGERID,
@CURRENTDATEEARLIESTTIME,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if (select ID from dbo.PROSPECT where ID = @CONSTITUENTID) is not null begin
update
dbo.PROSPECT
set
PROSPECTMANAGERFUNDRAISERID = coalesce(@PROSPECTMANAGERID,PROSPECTMANAGERFUNDRAISERID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.PROSPECT
where
ID = @CONSTITUENTID;
end
else begin
insert into dbo.PROSPECT (
ID,
PROSPECTMANAGERFUNDRAISERID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@PROSPECTMANAGERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;