USP_WPRELATIONSHIP_BO_IND_ADD
Adds WealthPoint Business Ownership relationship information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @DUNS | nvarchar(10) | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @LASTNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @SUFFIX | nvarchar(50) | IN | |
| @TITLE | nvarchar(30) | IN | |
| @YEAROFBIRTH | UDT_YEAR | IN | |
| @GENDERCODE | tinyint | IN | |
| @ADDRESS1 | nvarchar(100) | IN | |
| @CITY1 | nvarchar(50) | IN | |
| @STATE1 | nvarchar(2) | IN | |
| @ZIP1 | nvarchar(10) | IN | |
| @ADDRESS2 | nvarchar(100) | IN | |
| @CITY2 | nvarchar(50) | IN | |
| @STATE2 | nvarchar(2) | IN | |
| @ZIP2 | nvarchar(10) | IN | |
| @LASTDATE | datetime | IN | |
| @SPOUSEFLAG | bit | IN | |
| @MATCHPROSPECT | bit | IN | |
| @MATCHPRIMARYBUSINESS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_WPRELATIONSHIP_BO_IND_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@DUNS nvarchar(10) = '',
@CONSTITUENTID uniqueidentifier = null,
@LASTNAME nvarchar(100) = '',
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@SUFFIX nvarchar(50) = '',
@TITLE nvarchar(30) = '',
@YEAROFBIRTH dbo.UDT_YEAR = 0,
@GENDERCODE tinyint = 0,
@ADDRESS1 nvarchar(100) = '',
@CITY1 nvarchar(50) = '',
@STATE1 nvarchar(2) = '',
@ZIP1 nvarchar(10) = '',
@ADDRESS2 nvarchar(100) = '',
@CITY2 nvarchar(50) = '',
@STATE2 nvarchar(2) = '',
@ZIP2 nvarchar(10) = '',
@LASTDATE datetime = null,
@SPOUSEFLAG bit = 0,
@MATCHPROSPECT bit = 0,
@MATCHPRIMARYBUSINESS bit = 0
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @NEWRECORD bit;
declare @UPDATEDRECORD bit;
declare @CURRENTDATE datetime;
set @NEWRECORD = 0;
set @UPDATEDRECORD = 0;
set @CURRENTDATE = getdate();
declare @WPRELATIONSHIP_BO_ID uniqueidentifier;
exec dbo.USP_WPRELATIONSHIP_BO_ADD @ID=@WPRELATIONSHIP_BO_ID output, @DUNS=@DUNS, @CHANGEAGENTID=@CHANGEAGENTID;
--Set constituentID to spouse ID if spouse flag set
if @SPOUSEFLAG = 1 and @CONSTITUENTID is not null begin
select
@CONSTITUENTID = RECIPROCALCONSTITUENTID
from
dbo.RELATIONSHIP
where
RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and ISSPOUSE = 1
end
--Get the primary business
declare @PRIMARYBUSINESSID uniqueidentifier;
select
@PRIMARYBUSINESSID = REL.RECIPROCALCONSTITUENTID
from
dbo.RELATIONSHIP REL
where
REL.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
REL.ISPRIMARYBUSINESS = 1
update
dbo.WPRELATIONSHIP_BO
set
CONSTITUENTID = @PRIMARYBUSINESSID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @WPRELATIONSHIP_BO_ID and
@MATCHPRIMARYBUSINESS = 1 and
CONSTITUENTID is null and
@PRIMARYBUSINESSID is not null;
--Get any existing individual ID
select
@ID = RBOI.ID
from
dbo.WPRELATIONSHIP_BO RBO
left join dbo.WPRELATIONSHIP_BO_IND RBOI
on RBO.ID = RBOI.WPRELATIONSHIP_BO_ID
where
RBO.ID = @WPRELATIONSHIP_BO_ID and
coalesce(RBOI.FIRSTNAME, '') = coalesce(@FIRSTNAME, '') and
coalesce(RBOI.LASTNAME, '') = coalesce(@LASTNAME, '') and
coalesce(RBOI.MIDDLENAME, '') = coalesce(@MIDDLENAME, '') and
coalesce(RBOI.SUFFIX, '') = coalesce(@SUFFIX, '');
-- Record exists
if @ID is not null begin
set @UPDATEDRECORD = 1;
update
dbo.WPRELATIONSHIP_BO_IND
set
CONSTITUENTID = case when @MATCHPROSPECT = 1 then coalesce(CONSTITUENTID, @CONSTITUENTID) else CONSTITUENTID end,
LASTNAME = @LASTNAME,
FIRSTNAME = @FIRSTNAME,
MIDDLENAME = @MIDDLENAME,
SUFFIX = @SUFFIX,
TITLE = @TITLE,
YEAROFBIRTH = @YEAROFBIRTH,
GENDERCODE = @GENDERCODE,
ADDRESS1 = @ADDRESS1,
CITY1 = @CITY1,
STATE1 = @STATE1,
ZIP1 = @ZIP1,
ADDRESS2 = @ADDRESS2,
CITY2 = @CITY2,
STATE2 = @STATE2,
ZIP2 = @ZIP2,
LASTDATE = @LASTDATE,
SPOUSEFLAG = @SPOUSEFLAG,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID and (
(@MATCHPROSPECT = 1 and
CONSTITUENTID is null and
@CONSTITUENTID is not null) or
(TITLE <> @TITLE and (TITLE is not null or @TITLE is not null)) or
(YEAROFBIRTH <> @YEAROFBIRTH and (YEAROFBIRTH is not null or @YEAROFBIRTH is not null)) or
(GENDERCODE <> @GENDERCODE and (GENDERCODE is not null or @GENDERCODE is not null)) or
(ADDRESS1 <> @ADDRESS1 and (ADDRESS1 is not null or @ADDRESS1 is not null)) or
(CITY1 <> @CITY1 and (CITY1 is not null or @CITY1 is not null)) or
(STATE1 <> @STATE1 and (STATE1 is not null or @STATE1 is not null)) or
(ZIP1 <> @ZIP1 and (ZIP1 is not null or @ZIP1 is not null)) or
(ADDRESS2 <> @ADDRESS2 and (ADDRESS2 is not null or @ADDRESS2 is not null)) or
(CITY2 <> @CITY2 and (CITY2 is not null or @CITY2 is not null)) or
(STATE2 <> @STATE2 and (STATE2 is not null or @STATE2 is not null)) or
(ZIP2 <> @ZIP2 and (ZIP2 is not null or @ZIP2 is not null)) or
(LASTDATE <> @LASTDATE and (LASTDATE is not null or @LASTDATE is not null)) or
(SPOUSEFLAG <> @SPOUSEFLAG and (SPOUSEFLAG is not null or @SPOUSEFLAG is not null))
);
end
else begin
--Record is new and needs to be Added
set @NEWRECORD = 1;
if @ID is null
set @ID = newid();
insert into dbo.WPRELATIONSHIP_BO_IND (
ID,
WPRELATIONSHIP_BO_ID,
CONSTITUENTID,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SUFFIX,
TITLE,
YEAROFBIRTH,
GENDERCODE,
ADDRESS1,
CITY1,
STATE1,
ZIP1,
ADDRESS2,
CITY2,
STATE2,
ZIP2,
LASTDATE,
SPOUSEFLAG,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@ID,
@WPRELATIONSHIP_BO_ID,
case when @MATCHPROSPECT = 1 then @CONSTITUENTID else null end,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@SUFFIX,
@TITLE,
@YEAROFBIRTH,
@GENDERCODE,
@ADDRESS1,
@CITY1,
@STATE1,
@ZIP1,
@ADDRESS2,
@CITY2,
@STATE2,
@ZIP2,
@LASTDATE,
@SPOUSEFLAG,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;