USP_BBNC_COMMITPROFILEUPDATEORGRELATIONSHIPS
Updates a constituent's organization relationship information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @RELATIONSHIPID | uniqueidentifier | IN | |
| @ORGNAME | nvarchar(100) | IN | |
| @ORGLINKID | uniqueidentifier | INOUT | |
| @RELATIONSHIPTYPEID | uniqueidentifier | IN | |
| @RECIPROCALRELATIONSHIPTYPEID | uniqueidentifier | IN | |
| @ISCONTACT | bit | IN | |
| @CONTACTTYPE | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @MATCHESGIFTS | bit | IN | |
| @COMMENTS | nvarchar(max) | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEORGRELATIONSHIPS
(
@CONSTITUENTID uniqueidentifier = null,
@RELATIONSHIPID uniqueidentifier = null,
@ORGNAME nvarchar(100) = '',
@ORGLINKID uniqueidentifier = null output,
@RELATIONSHIPTYPEID uniqueidentifier = null,
@RECIPROCALRELATIONSHIPTYPEID uniqueidentifier = null,
@ISCONTACT bit = 0,
@CONTACTTYPE uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@MATCHESGIFTS bit = 0,
@COMMENTS nvarchar(max) = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @RELATIONSHIPSETID uniqueidentifier;
declare @DEFAULTRELATIONSHIPTYPECODEID uniqueidentifier;
declare @DEFAULTRECIPROCALTYPECODEID uniqueidentifier;
declare @ORGID uniqueidentifier;
declare @BUSINESSADDRESSTYPECODEID uniqueidentifier;
declare @ORGANIZATIONPRIMARYADDRESSTYPECODEID 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;
set @INFOSOURCECODEID = null;
--ORGANIZATIONPRIMARYADDRESSTYPECODEID can be null
select top (1)
@BUSINESSADDRESSTYPECODEID = [BUSINESSADDRESSTYPECODEID],
@ORGANIZATIONPRIMARYADDRESSTYPECODEID = [ORGANIZATIONPRIMARYADDRESSTYPECODEID],
@INFOSOURCECODEID = [INFOSOURCECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @BUSINESSADDRESSTYPECODEID is null
begin
raiserror('You must define the default primary business address type code.',16,1);
return -2;
end
begin try
-- We will have a primary business
set @ORGID = @ORGLINKID;
if @ORGID is null
begin
-- We need a new primary business
set @ORGID = newid();
insert into dbo.CONSTITUENT
(
ID,
ISORGANIZATION,
KEYNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGID,
1,
@ORGNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
set @ORGLINKID = @ORGID; --JamesWill 09/27/2007 CR284214-092707
--add origin information
if @INFOSOURCECODEID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @ID = @ORGLINKID,
@CHANGEAGENTID=@CHANGEAGENTID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REVENUEID = null
end
end
if @RELATIONSHIPID is not null
begin
set @RELATIONSHIPSETID = (select RELATIONSHIPSETID from RELATIONSHIP where ID = @RELATIONSHIPID);
end
select top 1
@DEFAULTRELATIONSHIPTYPECODEID = [PRIMARYBUSINESSRELATIONSHIPTYPECODEID],
@DEFAULTRECIPROCALTYPECODEID = [PRIMARYBUSINESSRECIPROCALTYPECODEID]
from
dbo.NETCOMMUNITYDEFAULTCODEMAP;
if @RELATIONSHIPTYPEID is null
set @RELATIONSHIPTYPEID = @DEFAULTRELATIONSHIPTYPECODEID
if @RECIPROCALRELATIONSHIPTYPEID is null
set @RECIPROCALRELATIONSHIPTYPEID = @DEFAULTRECIPROCALTYPECODEID
if @RELATIONSHIPID is null and @ORGLINKID is not null
begin
select
@RELATIONSHIPID = ID
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RECIPROCALCONSTITUENTID = @ORGLINKID
and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPEID
and RECIPROCALTYPECODEID = @RECIPROCALRELATIONSHIPTYPEID
end
if @RELATIONSHIPID is null
begin
if @RELATIONSHIPTYPEID is null or @RECIPROCALRELATIONSHIPTYPEID is null
begin
raiserror('You must define the default relationship type codes for primary business relationships.',16,1);
return -2;
end
set @RELATIONSHIPSETID = newid();
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
set @RELATIONSHIPID = newid();
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPSETID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ENDDATE,
ISCONTACT,
CONTACTTYPECODEID,
ISMATCHINGGIFTRELATIONSHIP,
COMMENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@RELATIONSHIPSETID,
@CONSTITUENTID,
@ORGID,
@RELATIONSHIPTYPEID,
@RECIPROCALRELATIONSHIPTYPEID,
@STARTDATE,
@ENDDATE,
@ISCONTACT,
@CONTACTTYPE,
@MATCHESGIFTS,
@COMMENTS,
@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=@RELATIONSHIPTYPEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RECIPROCALRELATIONSHIPTYPEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @ORGID,
NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
end
else
begin
update
dbo.RELATIONSHIP
set
[RELATIONSHIPTYPECODEID] = @RELATIONSHIPTYPEID,
[RECIPROCALTYPECODEID] = @RECIPROCALRELATIONSHIPTYPEID,
[STARTDATE] = @STARTDATE,
[ENDDATE] = @ENDDATE,
[ISCONTACT] = @ISCONTACT,
[CONTACTTYPECODEID] = @CONTACTTYPE,
[ISMATCHINGGIFTRELATIONSHIP] = @MATCHESGIFTS,
[COMMENTS] = @COMMENTS,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @RELATIONSHIPID;
--Add relationshipsetid if missing
if @RELATIONSHIPSETID is null
exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @RELATIONSHIPID
end
--If it's a new business that didn't have an existing relationship before, and the constit is a contact, then add address
if @ISCONTACT = 1
and @RELATIONSHIPID is null
and exists (select 1 from dbo.ADDRESS where [CONSTITUENTID] = @ORGID)
begin
declare @CONSTITADDRESSID uniqueidentifier;
set @CONSTITADDRESSID = newid();
declare @STATEID uniqueidentifier;
declare @COUNTRYID uniqueidentifier;
declare @ADDRESSBLOCK nvarchar(300);
declare @CITY nvarchar(100);
declare @POSTCODE nvarchar(24);
select top (1)
@STATEID = STATEID,
@COUNTRYID = COUNTRYID,
@ADDRESSBLOCK = ADDRESSBLOCK,
@CITY = CITY,
@POSTCODE = POSTCODE
from dbo.ADDRESS
where [CONSTITUENTID] = @ORGID;
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@CONSTITADDRESSID,
@CONSTITUENTID,
@BUSINESSADDRESSTYPECODEID,
0,
coalesce(@COUNTRYID, dbo.UFN_COUNTRY_GETDEFAULT()),
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@CONSTITADDRESSID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;