USP_AMPROIMPORT_COMMITCONSTITUENT
Creates or updates a constituent with data from AuctionMaestro Pro.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @LINKID | uniqueidentifier | INOUT | |
| @BATCHROWID | uniqueidentifier | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @ISORG | bit | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @PHONE | nvarchar(100) | IN | |
| @EMAILADDRESS | nvarchar(100) | IN | |
| @UPDATECONTACTINFO | bit | IN | |
| @NEWSECURITYGROUPID | uniqueidentifier | IN | |
| @NEWSITEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_AMPROIMPORT_COMMITCONSTITUENT
(
@LINKID uniqueidentifier = null output,
@BATCHROWID uniqueidentifier = null,
@TITLECODEID uniqueidentifier = null,
@FIRSTNAME nvarchar(50) = '',
@KEYNAME nvarchar(100) = '',
@ISORG bit = 0,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@POSTCODE nvarchar(12) = '',
@COUNTRYID uniqueidentifier = null,
@STATEID uniqueidentifier = null,
@PHONE nvarchar(100) = '',
@EMAILADDRESS nvarchar(100) = '',
@UPDATECONTACTINFO bit = 0,
@NEWSECURITYGROUPID uniqueidentifier = null,
@NEWSITEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime
)
as
set nocount on;
declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000'
if @LINKID = @EMPTYGUID
set @LINKID = null;
if @BATCHROWID = @EMPTYGUID
set @BATCHROWID = null;
if @TITLECODEID = @EMPTYGUID
set @TITLECODEID = null;
if @COUNTRYID = @EMPTYGUID
set @COUNTRYID = null;
if @STATEID = @EMPTYGUID
set @STATEID = null;
if @NEWSECURITYGROUPID = @EMPTYGUID
set @NEWSECURITYGROUPID = null;
if @NEWSITEID = @EMPTYGUID
set @NEWSITEID = null;
declare @NAME nvarchar(100) = @KEYNAME;
declare @KEYNAMEPREFIX nvarchar(50) = '';
declare @CONSTITUENTID uniqueidentifier;
select top 1 @CONSTITUENTID = ID from dbo.CONSTITUENT where CONSTITUENT.ID = @LINKID;
if @CONSTITUENTID is null
begin
set @CONSTITUENTID = newid();
set @LINKID = @CONSTITUENTID;
if @ISORG = 1
begin
exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
insert into dbo.CONSTITUENT(ID, ISORGANIZATION, KEYNAME, KEYNAMEPREFIX, ISCONSTITUENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@CONSTITUENTID, 1, @KEYNAME, @KEYNAMEPREFIX, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
insert into dbo.ORGANIZATIONDATA(ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
else
begin
insert into dbo.CONSTITUENT(ID, ISORGANIZATION, KEYNAME, FIRSTNAME, TITLECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@CONSTITUENTID, 0, @KEYNAME, @FIRSTNAME, @TITLECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
if @NEWSECURITYGROUPID is not null
insert into dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT(ID, CONSTIT_SECURITY_ATTRIBUTEID, CONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @NEWSECURITYGROUPID, @CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if @NEWSITEID is not null
insert into dbo.CONSTITUENTSITE(CONSTITUENTID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@CONSTITUENTID, @NEWSITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
else
begin
if @ISORG = 1
begin
exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
update dbo.CONSTITUENT
set KEYNAME = @KEYNAME,
KEYNAMEPREFIX = @KEYNAMEPREFIX,
ISCONSTITUENT = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CONSTITUENTID;
end
else
begin
update dbo.CONSTITUENT
set KEYNAME = @KEYNAME,
FIRSTNAME = @FIRSTNAME,
TITLECODEID = @TITLECODEID,
ISCONSTITUENT = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CONSTITUENTID;
end
end
declare @HASADDRESS bit;
declare @OLDADDRESSID uniqueidentifier = null;
if len(@ADDRESSBLOCK) > 0 or len(@CITY) > 0 or len(@POSTCODE) > 0 or (@COUNTRYID is not null and @COUNTRYID <> @EMPTYGUID) or (@STATEID is not null and @STATEID <> @EMPTYGUID)
set @HASADDRESS = 1;
else
set @HASADDRESS = 0;
select @OLDADDRESSID = ADDRESS.ID from ADDRESS where ADDRESS.CONSTITUENTID = @CONSTITUENTID and ADDRESS.ISPRIMARY = 1;
if @HASADDRESS = 1 begin
if @UPDATECONTACTINFO = 0 or @OLDADDRESSID is null begin
update dbo.ADDRESS
set ADDRESS.ISPRIMARY = 0,
ADDRESS.HISTORICALENDDATE = @CHANGEDATE,
ADDRESS.DATECHANGED = @CHANGEDATE,
ADDRESS.CHANGEDBYID = @CHANGEAGENTID
where ADDRESS.ID = @OLDADDRESSID; --Won't do anything if @OLDADDRESSID is null.
declare @NEWADDRESSID uniqueidentifier;
set @NEWADDRESSID = newid();
exec dbo.USP_ADDRESS_CREATE @ID=@NEWADDRESSID output, @CHANGEAGENTID=@CHANGEAGENTID, @CONSTITUENTID=@CONSTITUENTID,
@PRIMARY=1, @HISTORICALSTARTDATE=@CHANGEDATE, @COUNTRYID=@COUNTRYID, @STATEID=@STATEID, @ADDRESSBLOCK=@ADDRESSBLOCK,
@CITY=@CITY, @POSTCODE=@POSTCODE;
end
else begin
update dbo.ADDRESS
set ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
ADDRESS.DATECHANGED = @CHANGEDATE,
ADDRESS.ISPRIMARY = 1,
ADDRESS.HISTORICALENDDATE = null,
ADDRESS.COUNTRYID = @COUNTRYID,
ADDRESS.STATEID = @STATEID,
ADDRESS.ADDRESSBLOCK = @ADDRESSBLOCK,
ADDRESS.CITY = @CITY,
ADDRESS.POSTCODE = @POSTCODE
where ADDRESS.ID = @OLDADDRESSID
end
end
declare @CURRENTPRIMARYPHONEID uniqueidentifier = null;
declare @CURRENTPRIMARYPHONENUMBER nvarchar(100) = null;
declare @CURRENTPRIMARYTYPECODEID uniqueidentifier = null;
select top 1
@CURRENTPRIMARYPHONEID = PHONE.ID,
@CURRENTPRIMARYPHONENUMBER = PHONE.NUMBER,
@CURRENTPRIMARYTYPECODEID = PHONE.PHONETYPECODEID
from dbo.PHONE
where PHONE.CONSTITUENTID = @CONSTITUENTID
and PHONE.ISPRIMARY = 1;
if @CURRENTPRIMARYPHONEID is not null and len(@PHONE) = 0
begin
--There was no phone number, so get rid of the primary number.
update dbo.PHONE
set ISPRIMARY = 0,
ENDDATE = @CHANGEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CURRENTPRIMARYPHONEID;
end
else if (@CURRENTPRIMARYPHONEID is not null and @CURRENTPRIMARYPHONENUMBER <> @PHONE)
begin
--There was a change in the phone number. So update the current or create a new one.
if @UPDATECONTACTINFO = 1--Update the current primary phone number
begin
--Trying to insert the same CONSTITUENT/PHONETYPECODEID/NUMBER into the PHONE table will result in an exception.
--So avoid doing that.
if exists(select ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID = @CURRENTPRIMARYTYPECODEID)
begin
--We can't use the primary, because that will cause an error. So act as if we moved away from the primary.
update dbo.PHONE
set ISPRIMARY = 0,
ENDDATE = @CHANGEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CURRENTPRIMARYPHONEID;
--Update the existing number to be primary
update dbo.PHONE
set ISPRIMARY = 1,
ENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = (select top 1 ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID = @CURRENTPRIMARYTYPECODEID);
end
else
begin
update dbo.PHONE
set NUMBER = @PHONE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CURRENTPRIMARYPHONEID;
end
end
else
begin --Insert a new phone number
--Get rid of the old primary phone
update dbo.PHONE
set ISPRIMARY = 0,
ENDDATE = @CHANGEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @CURRENTPRIMARYPHONEID;
--Trying to insert the same CONSTITUENT/PHONETYPECODEID/NUMBER into the PHONE table will result in an exception.
--So avoid doing that.
if exists(select ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID is null)
begin
update dbo.PHONE
set ISPRIMARY = 1,
ENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = (select top 1 ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID is null);
end
else
begin
insert into dbo.PHONE(ID, CONSTITUENTID, NUMBER, ISPRIMARY, STARTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @CONSTITUENTID, @PHONE, 1, @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
end
else if @CURRENTPRIMARYPHONEID is null and len(@PHONE) <> 0
begin
--Since there's no phone to update, we have to insert.
--Trying to insert the same CONSTITUENT/PHONETYPECODEID/NUMBER into the PHONE table will result in an exception.
--So avoid doing that.
if exists(select ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID is null)
begin
update dbo.PHONE
set ISPRIMARY = 1,
ENDDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = (select top 1 ID from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONE and PHONETYPECODEID is null);
end
else
begin
insert into dbo.PHONE(ID, CONSTITUENTID, NUMBER, ISPRIMARY, STARTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @CONSTITUENTID, @PHONE, 1, @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
declare @OLDEMAILADDRESSID uniqueidentifier = null;
select @OLDEMAILADDRESSID = EMAILADDRESS.ID from dbo.EMAILADDRESS where EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1;
if @EMAILADDRESS is not null and len(@EMAILADDRESS) > 0 begin
if @OLDEMAILADDRESSID is not null
begin
update dbo.EMAILADDRESS
set EMAILADDRESS.EMAILADDRESS = @EMAILADDRESS,
EMAILADDRESS.CHANGEDBYID = @CHANGEAGENTID,
EMAILADDRESS.DATECHANGED = @CHANGEDATE
where EMAILADDRESS.ID = @OLDEMAILADDRESSID;
end
else begin
insert into dbo.EMAILADDRESS
(ID, CONSTITUENTID, EMAILADDRESS, ISPRIMARY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @CONSTITUENTID, @EMAILADDRESS, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
else if @OLDEMAILADDRESSID is not null begin
delete from dbo.EMAILADDRESS where EMAILADDRESS.ID = @OLDEMAILADDRESSID;
end
--If this is a new constituent, update any batch rows that were pointing at it to point to the CONSTITUENT record instead
update dbo.BATCHAMPROIMPORTITEM
set AUCTIONITEM_CONSTITUENTID = @LINKID,
CHANGEDBYID= @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where AUCTIONITEM_CONSTITUENTID = @BATCHROWID;
update dbo.BATCHAMPROIMPORTREGISTRANT
set REGISTRANT_CONSTITUENTID = @LINKID,
CHANGEDBYID= @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where REGISTRANT_CONSTITUENTID = @BATCHROWID;
update dbo.BATCHAMPROIMPORTPURCHASE
set PURCHASE_CONSTITUENTID = @LINKID,
CHANGEDBYID= @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where PURCHASE_CONSTITUENTID = @BATCHROWID;