USP_BBNC_COMMITPROFILEUPDATEPRIMARYBUSINESS
Updates a constituent's primary business information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @ADDNEW | bit | IN | |
| @MAKEPRIMARY | bit | IN | |
| @UPDATENAME | bit | IN | |
| @ORGNAME | nvarchar(100) | IN | |
| @POSITION | nvarchar(100) | IN | |
| @INDUSTRYCODEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEPRIMARYBUSINESS
(
@CONSTITUENTID uniqueidentifier = null,
@ADDNEW bit = 0,
@MAKEPRIMARY bit = 0,
@UPDATENAME bit = 0,
@ORGNAME nvarchar(100) = '',
@POSITION nvarchar(100) = '',
@INDUSTRYCODEID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(12) = '',
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @ID uniqueidentifier;
declare @ORGID uniqueidentifier;
declare @RELATIONSHIPID uniqueidentifier;
declare @RELATIONSHIPSETID uniqueidentifier;
declare @RELATIONSHIPTYPECODEID uniqueidentifier;
declare @RECIPROCALTYPECODEID uniqueidentifier;
declare @ADDRESSTYPECODEID uniqueidentifier;
declare @SEQUENCE int;
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();
select top 1 @ADDRESSTYPECODEID = [BUSINESSADDRESSTYPECODEID] from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @ADDRESSTYPECODEID is null
begin
raiserror('You must define the default primary business address type code.',16,1);
return -2;
end
-- CountryID is required
if @COUNTRYID is null
select @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();
begin try
if @ADDNEW = 1
begin
select top 1 @RELATIONSHIPTYPECODEID = [PRIMARYBUSINESSRELATIONSHIPTYPECODEID],
@RECIPROCALTYPECODEID = [PRIMARYBUSINESSRECIPROCALTYPECODEID]
from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @RELATIONSHIPTYPECODEID is null or @RECIPROCALTYPECODEID is null
begin
raiserror('You must define the default relationship type codes for primary business relationships.',16,1);
return -2;
end
set @ORGID = newid();
insert into dbo.CONSTITUENT
(
ID,
ISORGANIZATION,
KEYNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
( @ORGID,
-1,
@ORGNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
if @INDUSTRYCODEID is not null
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGID,
@INDUSTRYCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
-- Create a default address for this organization based on entered data
insert into dbo.ADDRESS
(
CONSTITUENTID,
ADDRESSTYPECODEID, -- CR268631-030107 Store the business address with the correct address type code
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGID,
@ADDRESSTYPECODEID,
1,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
set @RELATIONSHIPSETID = newid();
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
set @ID = newid();
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPSETID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISPRIMARYBUSINESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@RELATIONSHIPSETID,
@CONSTITUENTID,
@ORGID,
@RELATIONSHIPTYPECODEID,
@RECIPROCALTYPECODEID,
@MAKEPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0
declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null
declare @PRIMARYCONSTITUENTTYPE tinyint = 0
declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0
declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null
declare @RECIPROCALCONSTITUENTTYPE tinyint = 0
select @PRIMARYCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @CONSTITUENTID;
select @RECIPROCALCONSTITUENTTYPE = case
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household
when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group
when ISORGANIZATION = 1 then 1 --Organization
else 0 end --Individual
from dbo.CONSTITUENT where ID = @ORGID;
-- add default recognition credits if creating a new relationship
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@PRIMARYCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPECODEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RECIPROCALTYPECODEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @ORGID,
NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
if len(coalesce(@POSITION, '')) > 0
insert into dbo.RELATIONSHIPJOBINFO
(
RELATIONSHIPSETID,
JOBTITLE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPSETID,
@POSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
-- Add a new business address to the constituent as well
select
@SEQUENCE = coalesce(max(SEQUENCE),0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID = @CONSTITUENTID;
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@CONSTITUENTID,
@ADDRESSTYPECODEID,
0,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
else
begin
select
@ID = [ID],
@ORGID = [RECIPROCALCONSTITUENTID],
@RELATIONSHIPSETID = RELATIONSHIPSETID,
@RELATIONSHIPID = RELATIONSHIP.ID
from
dbo.RELATIONSHIP
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.ISPRIMARYBUSINESS = 1;
if @ID is null or @ORGID is null
begin
raiserror('Cannot locate a primary business relationship to update.',16,1);
return -2;
end
if @UPDATENAME = 1
update
dbo.CONSTITUENT
set
[KEYNAME] = @ORGNAME,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @ORGID;
declare @LASTJOBINFOID uniqueidentifier;
select top 1
@LASTJOBINFOID = ID
from dbo.RELATIONSHIPJOBINFO
where RELATIONSHIPSETID = @RELATIONSHIPSETID
order by STARTDATE asc;
if len(coalesce(@POSITION, '')) > 0
begin
if @LASTJOBINFOID is null
begin
if @RELATIONSHIPSETID is null
begin
exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @RELATIONSHIPID
end
insert into dbo.RELATIONSHIPJOBINFO
(
RELATIONSHIPSETID,
JOBTITLE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPSETID,
@POSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
)
end
else
update
dbo.RELATIONSHIPJOBINFO
set
[JOBTITLE] = @POSITION,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @LASTJOBINFOID;
end
else
exec dbo.USP_RELATIONSHIPJOBINFO_DELETEBYID_WITHCHANGEAGENTID @LASTJOBINFOID, @CHANGEAGENTID;
if @INDUSTRYCODEID is not null
begin
select
@ID = [ID]
from
dbo.ORGANIZATIONDATA
where
[ID] = @ORGID;
if @ID is null
insert into dbo.ORGANIZATIONDATA
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
update
dbo.ORGANIZATIONDATA
set
[INDUSTRYCODEID] = @INDUSTRYCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @ORGID;
select top 1
@ID = [ID]
from
dbo.ADDRESS
where
ADDRESS.[CONSTITUENTID] = @CONSTITUENTID
and ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID
order by
ADDRESS.ISPRIMARY desc, ADDRESS.SEQUENCE;
if @ID is not null
update
dbo.ADDRESS
set
[COUNTRYID] = @COUNTRYID,
[STATEID] = @STATEID,
[ADDRESSBLOCK] = @ADDRESSBLOCK,
[CITY] = @CITY,
[POSTCODE] = @POSTCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @ID
else
if @STATEID is not null or @ADDRESSBLOCK is not null or @CITY is not null or @POSTCODE is not null
begin
-- Add a new business address to the constituent
select
@SEQUENCE = coalesce(max(SEQUENCE),0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID = @CONSTITUENTID;
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@CONSTITUENTID,
@ADDRESSTYPECODEID,
0,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;