USP_BBNC_COMMITPROFILEUPDATEADDRESS
Updates a constituent's preferred address information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @ADDNEWADDRESS | bit | IN | |
| @MAKENEWPREFERRED | bit | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @PREVADDRESSTYPECODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEADDRESS
(
@CONSTITUENTID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(12) = '',
@ADDNEWADDRESS bit = 0,
@MAKENEWPREFERRED bit = 0,
@ADDRESSTYPECODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@PREVADDRESSTYPECODEID uniqueidentifier = null
) as
set nocount on;
declare @SEQUENCE int;
if @CONSTITUENTID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @MAKENEWPREFERRED is null
set @MAKENEWPREFERRED = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
--PRIMARYADDRESSTYPECODEID can be null
declare @PRIMARYADDRESSTYPECODEID uniqueidentifier;
declare @INFOSOURCECODEID uniqueidentifier;
select top (1)
@PRIMARYADDRESSTYPECODEID = [PRIMARYADDRESSTYPECODEID],
@INFOSOURCECODEID = [INFOSOURCECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
declare @CONSTITADDRESSID uniqueidentifier;
begin try
if @ADDNEWADDRESS = 0
begin
select @CONSTITADDRESSID = ID from dbo.ADDRESS where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1;
if @CONSTITADDRESSID is not null
update dbo.ADDRESS set
[COUNTRYID] = @COUNTRYID,
[ADDRESSBLOCK] = @ADDRESSBLOCK,
[CITY] = @CITY,
[STATEID] = @STATEID,
[POSTCODE] = @POSTCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[ADDRESSTYPECODEID]= coalesce(@ADDRESSTYPECODEID,[ADDRESSTYPECODEID])
where
ID = @CONSTITADDRESSID;
end
else
begin
select @SEQUENCE = max([SEQUENCE])
from dbo.ADDRESS
where [CONSTITUENTID] = @CONSTITUENTID;
if @SEQUENCE is null
set @SEQUENCE = 1;
if @MAKENEWPREFERRED = 1
begin
if @PREVADDRESSTYPECODEID is not null
update dbo.ADDRESS set
[HISTORICALENDDATE] = case when [ADDRESSTYPECODEID] = coalesce(@ADDRESSTYPECODEID, @PRIMARYADDRESSTYPECODEID) then convert(date,@CHANGEDATE)
else NULL
end,
[ISPRIMARY] = 0,
[ADDRESSTYPECODEID] = @PREVADDRESSTYPECODEID,
[DATECHANGED] = @CHANGEDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where
[CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1
else
update dbo.ADDRESS set
[ISPRIMARY] = 0,
[DATECHANGED] = @CHANGEDATE,
[CHANGEDBYID] = @CHANGEAGENTID,
[HISTORICALENDDATE] = case when [ADDRESSTYPECODEID] = coalesce(@ADDRESSTYPECODEID, @PRIMARYADDRESSTYPECODEID) then convert(date,@CHANGEDATE)
else NULL
end
where
[CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1;
end
-- TMV 09/26/2007 CR284107-092507 There should always be a primary address
if not exists(select 1 from dbo.ADDRESS where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1)
set @MAKENEWPREFERRED = 1;
declare @CONSTITADDRESSISPRIMARY bit = 0;
select top 1
@CONSTITADDRESSID = ID,
@CONSTITADDRESSISPRIMARY = ISPRIMARY
from
dbo.ADDRESS
where
@CONSTITUENTID = [CONSTITUENTID]
and
(@ADDRESSTYPECODEID = [ADDRESSTYPECODEID] or (@ADDRESSTYPECODEID is null and [ADDRESSTYPECODEID] is null) or @PRIMARYADDRESSTYPECODEID = [ADDRESSTYPECODEID] or (@PRIMARYADDRESSTYPECODEID is null and [ADDRESSTYPECODEID] is null))
and
@COUNTRYID = [COUNTRYID]
and
(@STATEID = [STATEID] or (@STATEID is null and [STATEID] is null))
and
@ADDRESSBLOCK = [ADDRESSBLOCK]
and
@CITY = [CITY]
and
@POSTCODE = [POSTCODE]
and
'' = [CART]
and
'' = [DPC]
and
'' = [LOT]
-- Bug 413289 check for non former addresses only
AND (HISTORICALENDDATE is null OR HISTORICALENDDATE > convert(date, GETDATE()));
--Bug 108929 Only insert the address if an identical address does not already exist
if @CONSTITADDRESSID is null
begin
set @CONSTITADDRESSID = newid();
insert into dbo.ADDRESS
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[STARTDATE],
[ENDDATE],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTITADDRESSID,
@CONSTITUENTID,
coalesce(@ADDRESSTYPECODEID, @PRIMARYADDRESSTYPECODEID), -- TMV 05/01/2007 CR268676-030107
@MAKENEWPREFERRED,
0,
'0000',
'0000',
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
'',
'',
'',
@SEQUENCE + 1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
else if @CONSTITADDRESSISPRIMARY = 0 and @MAKENEWPREFERRED = 1
update dbo.ADDRESS set
ISPRIMARY = 1
where
ID = @CONSTITADDRESSID;
end
if @CONSTITADDRESSID is not null
if not exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @CONSTITADDRESSID)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTITADDRESSID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
)
else
update dbo.ADDRESSVALIDATIONUPDATE set
[INFOSOURCECODEID] = @INFOSOURCECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where ID = @CONSTITADDRESSID
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;