USP_BBNC_COMMITPROFILEUPDATEADDITIONALADDRESS
Updates a constituent's additional 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 | |
| @ADDRESSID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @ADDNEWADDRESS | bit | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @PREVIOUSADDRESSTYPECODEID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @SEASONALSTARTDATE | UDT_MONTHDAY | IN | |
| @SEASONALENDDATE | UDT_MONTHDAY | IN | |
| @DONOTMAIL | bit | IN | |
| @ISPRIMARY | bit | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEADDITIONALADDRESS
(
@CONSTITUENTID uniqueidentifier = null,
@ADDRESSID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(12) = '',
@ADDNEWADDRESS bit = 0,
@ADDRESSTYPECODEID uniqueidentifier = null,
@PREVIOUSADDRESSTYPECODEID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@SEASONALSTARTDATE dbo.UDT_MONTHDAY = null,
@SEASONALENDDATE dbo.UDT_MONTHDAY = null,
@DONOTMAIL bit = null,
@ISPRIMARY bit = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @SEQUENCE int;
declare @PRIMARYADDRESSID uniqueidentifier;
if @CONSTITUENTID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @INFOSOURCECODEID uniqueidentifier;
select top (1)
@INFOSOURCECODEID = [INFOSOURCECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
declare @CONSTITADDRESSID uniqueidentifier;
begin try
if @ISPRIMARY = 1
begin
select
@PRIMARYADDRESSID = [ID]
from
dbo.ADDRESS
where
[CONSTITUENTID] = @CONSTITUENTID
and [ISPRIMARY] = 1;
if @PRIMARYADDRESSID is not null and (@ADDRESSID is null or @PRIMARYADDRESSID <> @ADDRESSID or (@PRIMARYADDRESSID = @ADDRESSID and @ADDNEWADDRESS = 1))
begin
update dbo.ADDRESS set
[ISPRIMARY] = 0
where
[ID] = @PRIMARYADDRESSID;
end
end
if @ADDNEWADDRESS = 0
begin
if @ADDRESSID is not null
update dbo.ADDRESS set
[ADDRESSTYPECODEID] = @ADDRESSTYPECODEID,
[ISPRIMARY] = @ISPRIMARY,
[COUNTRYID] = @COUNTRYID,
[ADDRESSBLOCK] = @ADDRESSBLOCK,
[CITY] = @CITY,
[STATEID] = @STATEID,
[POSTCODE] = @POSTCODE,
[HISTORICALSTARTDATE] = @STARTDATE,
[HISTORICALENDDATE] = @ENDDATE,
[STARTDATE] = @SEASONALSTARTDATE,
[ENDDATE] = @SEASONALENDDATE,
[DONOTMAIL] = @DONOTMAIL,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
ID = @ADDRESSID;
end
else
begin
select @SEQUENCE = max([SEQUENCE])
from dbo.ADDRESS
where [CONSTITUENTID] = @CONSTITUENTID;
if @SEQUENCE is null
set @SEQUENCE = 1;
if @PREVIOUSADDRESSTYPECODEID is not null
begin
if @ADDRESSID is not null
update dbo.ADDRESS set
[HISTORICALENDDATE] = case when [ADDRESSTYPECODEID] = @ADDRESSTYPECODEID and [ISPRIMARY] = 0 then convert(date,@CHANGEDATE)
else [HISTORICALENDDATE]
end,
[ADDRESSTYPECODEID] = @PREVIOUSADDRESSTYPECODEID,
[DATECHANGED] = @CHANGEDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where
[ID] = @ADDRESSID
end
else
if @ADDRESSID is not null
update dbo.ADDRESS set
[DATECHANGED] = @CHANGEDATE,
[CHANGEDBYID] = @CHANGEAGENTID,
[HISTORICALENDDATE] = case when [ADDRESSTYPECODEID] = @ADDRESSTYPECODEID and [ISPRIMARY] = 0 then convert(date,@CHANGEDATE)
else [HISTORICALENDDATE]
end
where
[ID] = @ADDRESSID
set @ADDRESSID = newid();
insert into dbo.ADDRESS
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[STARTDATE],
[ENDDATE],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[HISTORICALSTARTDATE],
[HISTORICALENDDATE],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@CONSTITUENTID,
@ADDRESSTYPECODEID,
@ISPRIMARY,
@DONOTMAIL,
@SEASONALSTARTDATE,
@SEASONALENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
'',
'',
'',
@STARTDATE,
@ENDDATE,
@SEQUENCE + 1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
if @ADDRESSID is not null
if not exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ADDRESSID)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
)
else
update dbo.ADDRESSVALIDATIONUPDATE set
[INFOSOURCECODEID] = @INFOSOURCECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where ID = @ADDRESSID
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;