USP_BBNC_COMMITPROFILEUPDATEPRIMARYBUSINESS_1_1
Updates a constituent's primary business information from a NetCommunity profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @ORGNAME | nvarchar(100) | IN | |
| @ORGLINKID | uniqueidentifier | INOUT | |
| @POSITION | nvarchar(100) | IN | |
| @INDUSTRYCODEID | uniqueidentifier | IN | |
| @UPDATEINDUSTRYCODEID | bit | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @PROCESSADDRESS | bit | IN | |
| @ADDNEWADDRESS | bit | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEPRIMARYBUSINESS_1_1
(
@CONSTITUENTID uniqueidentifier = null,
@ORGNAME nvarchar(100) = '',
@ORGLINKID uniqueidentifier = null output,
@POSITION nvarchar(100) = '',
@INDUSTRYCODEID uniqueidentifier = null,
@UPDATEINDUSTRYCODEID bit = 0,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(12) = '',
@PROCESSADDRESS bit = 0,
@ADDNEWADDRESS bit = 0,
@ADDRESSTYPECODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @RELATIONSHIPID uniqueidentifier;
declare @RELATIONSHIPSETID uniqueidentifier;
declare @ORGID uniqueidentifier;
declare @RELATIONSHIPTYPECODEID uniqueidentifier;
declare @RECIPROCALTYPECODEID uniqueidentifier;
declare @BUSINESSADDRESSTYPECODEID uniqueidentifier;
declare @BUSINESSADDRESSID uniqueidentifier;
declare @SEQUENCE int;
declare @ORGANIZATIONPRIMARYADDRESSTYPECODEID uniqueidentifier;
declare @ISPRIMARY bit;
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
if @ORGLINKID is null
and (@ORGNAME is null or len(@ORGNAME) = 0)
begin
-- We no longer have a primary business, mark any existing relationship as not primary
update dbo.RELATIONSHIP set
ISPRIMARYBUSINESS = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.ISPRIMARYBUSINESS = 1;
end
else
begin
-- 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 = @ORGID,
@CHANGEAGENTID=@CHANGEAGENTID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REVENUEID = null
end
end
-- If there is an existing primary business relationship with a different org, mark it as not primary
update dbo.RELATIONSHIP set
ISPRIMARYBUSINESS = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.RECIPROCALCONSTITUENTID <> @ORGID
and RELATIONSHIP.ISPRIMARYBUSINESS = 1;
-- Use the existing relationship if the organization is the same
select
@RELATIONSHIPID = ID
from
dbo.RELATIONSHIP
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGID
and RELATIONSHIP.ISPRIMARYBUSINESS = 0;
update dbo.RELATIONSHIP set
ISPRIMARYBUSINESS = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
RELATIONSHIP.ID = @RELATIONSHIPID;
-- If we did not find an update an existing non-primary relationship for this org, look for an existing primary relationship
select
@RELATIONSHIPID = ID,
@RELATIONSHIPSETID = RELATIONSHIPSETID
from
dbo.RELATIONSHIP
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGID
and RELATIONSHIP.ISPRIMARYBUSINESS = 1;
if @RELATIONSHIPID is null
begin
-- We need a new primary business relationship
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 @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,
ISPRIMARYBUSINESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@RELATIONSHIPSETID,
@CONSTITUENTID,
@ORGID,
@RELATIONSHIPTYPECODEID,
@RECIPROCALTYPECODEID,
1,
@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;
end
else
begin
--Add relationshipsetid if missing
if @RELATIONSHIPSETID is null
exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @RELATIONSHIPID
end
--Update job information;
declare @LASTJOBINFOID uniqueidentifier;
select top 1
@LASTJOBINFOID = ID
from dbo.RELATIONSHIPJOBINFO
where RELATIONSHIPSETID = @RELATIONSHIPSETID
order by STARTDATE asc;
--An update is added if no position is passed then the current is removed.
if len(coalesce(@POSITION, '')) > 0
begin
--add if not present
if @LASTJOBINFOID is null
insert into dbo.RELATIONSHIPJOBINFO
(
RELATIONSHIPSETID,
JOBTITLE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPSETID,
@POSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
else
update
dbo.RELATIONSHIPJOBINFO
set
[JOBTITLE] = @POSITION,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @LASTJOBINFOID;
end
else
exec dbo.USP_RELATIONSHIPJOBINFO_DELETEBYID_WITHCHANGEAGENTID @LASTJOBINFOID, @CHANGEAGENTID;
-- Now ORGID points to the correct record and RELATIONSHIPID points
-- to the correct relationship but the data that hangs off of it
-- may need to be updated.
if @UPDATEINDUSTRYCODEID = 1
if exists (select 1 from dbo.ORGANIZATIONDATA where [ID] = @ORGID)
update dbo.ORGANIZATIONDATA set
INDUSTRYCODEID = @INDUSTRYCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID = @ORGID
and
(
(INDUSTRYCODEID is null and @INDUSTRYCODEID is not null)
or
(INDUSTRYCODEID is not null and @INDUSTRYCODEID is null)
or
INDUSTRYCODEID <> @INDUSTRYCODEID
);
else
if @INDUSTRYCODEID is not null
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGID,
@INDUSTRYCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
declare @CONSTITADDRESSID uniqueidentifier;
set @CONSTITADDRESSID = newid();
-- Create a default address for this organization based on entered data
-- TMV 05/01/2007 CR272710-041307 Don't add an address to the organization if we're not adding an address to the individual
if not exists (select 1 from dbo.ADDRESS where [CONSTITUENTID] = @ORGID)
and @PROCESSADDRESS = 1
begin
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@CONSTITADDRESSID,
@ORGID,
@ORGANIZATIONPRIMARYADDRESSTYPECODEID, --TMV 05/01/2007 CR268676-030107
1,
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
if @PROCESSADDRESS = 1
begin
select top (1)
@BUSINESSADDRESSID = [ID]
from
dbo.ADDRESS
where
[CONSTITUENTID] = @CONSTITUENTID
and [ADDRESSTYPECODEID] = @BUSINESSADDRESSTYPECODEID
order by
[ISPRIMARY] desc, [SEQUENCE];
if @ADDNEWADDRESS = 0 and @BUSINESSADDRESSID is not null
begin
update dbo.ADDRESS set
[COUNTRYID] = @COUNTRYID,
[STATEID] = @STATEID,
[ADDRESSBLOCK] = @ADDRESSBLOCK,
[CITY] = @CITY,
[POSTCODE] = @POSTCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @BUSINESSADDRESSID;
if exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @BUSINESSADDRESSID)
update dbo.ADDRESSVALIDATIONUPDATE set
INFOSOURCECODEID = @INFOSOURCECODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID = @BUSINESSADDRESSID
else
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BUSINESSADDRESSID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
)
end
else
begin
select
@SEQUENCE = coalesce(max(SEQUENCE),0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID = @CONSTITUENTID;
declare @OLDADDRESSTYPECODEID uniqueidentifier
select @OLDADDRESSTYPECODEID = [ADDRESSTYPECODEID] from dbo.ADDRESS where [ID] = @BUSINESSADDRESSID
update dbo.ADDRESS set
[ADDRESSTYPECODEID] = @ADDRESSTYPECODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @BUSINESSADDRESSID;
if @OLDADDRESSTYPECODEID = @ADDRESSTYPECODEID and (select [ISPRIMARY] from dbo.ADDRESS where [ID] = @BUSINESSADDRESSID) = 0
update dbo.ADDRESS set
[HISTORICALENDDATE] = convert(date,@CHANGEDATE),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @BUSINESSADDRESSID
set @BUSINESSADDRESSID = newid();
--When linked user don't have any address and an address is added by profile update batch then it'll consider as primary address by default.
set @ISPRIMARY= 0;
if not exists (select 1 from dbo.ADDRESS where [CONSTITUENTID] = @CONSTITUENTID)
begin
Set @ISPRIMARY =1;
end
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@BUSINESSADDRESSID,
@CONSTITUENTID,
@BUSINESSADDRESSTYPECODEID,
@ISPRIMARY,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BUSINESSADDRESSID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
)
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;