USP_BBNC_COMMITPROFILEUPDATEPHONE
Updates a constituent's phone information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @PHONETYPECODEID | uniqueidentifier | IN | |
| @NUMBER | nvarchar(100) | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEPHONE
(
@CONSTITUENTID uniqueidentifier = null,
@PHONETYPECODEID uniqueidentifier = null,
@NUMBER nvarchar(100) = '',
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @ID uniqueidentifier;
declare @SEQUENCE int;
declare @ISPRIMARY bit;
declare @DUPLICATEPHONEID 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();
if @PHONETYPECODEID is not null
select top 1
@ID = [ID],
@ISPRIMARY = [ISPRIMARY]
from
dbo.PHONE
where
[CONSTITUENTID] = @CONSTITUENTID
and [PHONETYPECODEID] = @PHONETYPECODEID
order by
ISPRIMARY desc, SEQUENCE;
declare @INFOSOURCECODEID uniqueidentifier;
select top (1)
@INFOSOURCECODEID = [INFOSOURCECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
begin try
if @ID is not null
begin
declare @DUPLICATEISPRIMARY as bit =0
select
@DUPLICATEPHONEID = [ID],
@DUPLICATEISPRIMARY = case when [ISPRIMARY] = 1 then 1 else 0 end
from
dbo.PHONE
where
[CONSTITUENTID] = @CONSTITUENTID
and [PHONETYPECODEID] = @PHONETYPECODEID
and [NUMBER] = @NUMBER;
-- The type and number for constituent phone must be unique.
if @DUPLICATEPHONEID is not null and @DUPLICATEPHONEID <> @ID
begin
exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
end
if len(@NUMBER) > 0
begin
update dbo.PHONE set
[ISPRIMARY] = 0,
[DONOTCALL] = 1,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[ENDDATE] = GETDATE()
where
[CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1
and
(
@DUPLICATEPHONEID is null
or
[ID] <> @DUPLICATEPHONEID
);
if @DUPLICATEPHONEID is not null and @DUPLICATEPHONEID = @ID
begin
--entry already exist , just make it primary contact
if(@DUPLICATEISPRIMARY <>1)
begin
update dbo.PHONE set
[ISPRIMARY] = 1,
[DONOTCALL] = 0,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[STARTDATE] = GETDATE(),
[ENDDATE] =null
where
[ID] = @DUPLICATEPHONEID
end
return 0
end
if @DUPLICATEISPRIMARY = 1
begin
set @ISPRIMARY =@DUPLICATEISPRIMARY
end
if len(@NUMBER) > 0 --JamesWill 01/29/2008 CR292112-012408 Don't try to insert blank phone numbers
begin
select @SEQUENCE = max([SEQUENCE])
from dbo.PHONE
where [CONSTITUENTID] = @CONSTITUENTID;
set @ISPRIMARY = 1;
if @SEQUENCE is null
begin
set @SEQUENCE = 0;
end
-- The type and number for constituent phone must be unique.
if not exists
(
select
1
from
dbo.PHONE
where
[CONSTITUENTID] = @CONSTITUENTID
and [PHONETYPECODEID] is null
and @PHONETYPECODEID is null
and [NUMBER] = @NUMBER
)
insert into dbo.PHONE
(
[ID],
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[SEQUENCE],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[STARTDATE]
)
values
(
newid(),
@CONSTITUENTID,
@PHONETYPECODEID,
@NUMBER,
@ISPRIMARY,
@SEQUENCE + 1,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@CHANGEDATE
);
end
end
else
begin
--JamesWill 01/29/2008 CR292112-012408 Delete phone entries when BBNC sends us an empty number
exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
end
end
else
begin
if len(@NUMBER) > 0 --JamesWill 01/29/2008 CR292112-012408 Don't try to insert blank phone numbers
begin
update dbo.PHONE set
[ISPRIMARY] = 0,
[DONOTCALL] = 1,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[ENDDATE] = GETDATE()
where
[CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1
and
(
@DUPLICATEPHONEID is null
or
[ID] <> @DUPLICATEPHONEID
);
select @SEQUENCE = max([SEQUENCE])
from dbo.PHONE
where [CONSTITUENTID] = @CONSTITUENTID;
set @ISPRIMARY = 1;
if @SEQUENCE is null
begin
set @SEQUENCE = 0;
end
-- The type and number for constituent phone must be unique.
if not exists
(
select
1
from
dbo.PHONE
where
[CONSTITUENTID] = @CONSTITUENTID
and [PHONETYPECODEID] is null
and @PHONETYPECODEID is null
and [NUMBER] = @NUMBER
)
insert into dbo.PHONE
(
[ID],
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[SEQUENCE],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[STARTDATE]
)
values
(
newid(),
@CONSTITUENTID,
@PHONETYPECODEID,
@NUMBER,
@ISPRIMARY,
@SEQUENCE + 1,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@CHANGEDATE
);
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;