USP_WPBIOGRAPHICALDEMOGRAPHIC_ADD
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @FULLNAME | nvarchar(500) | IN | |
| @FIRSTNAME | nvarchar(100) | IN | |
| @MIDDLENAME | nvarchar(100) | IN | |
| @LASTNAME | nvarchar(100) | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | |
| @OCCUPATION | nvarchar(100) | IN | |
| @HOUSEHOLD_MOSAIC | nvarchar(100) | IN | |
| @HOUSEHOLDINCOMEESTIMATECODEID | uniqueidentifier | IN | |
| @DISCRETIONARY_SPEND | money | IN | |
| @CNOTES | nvarchar(1024) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @ORIGINALID | int | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @TITLECODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_WPBIOGRAPHICALDEMOGRAPHIC_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@FULLNAME nvarchar(500) = '',
@FIRSTNAME nvarchar(100) = '',
@MIDDLENAME nvarchar(100) = '',
@LASTNAME nvarchar(100) = '',
@SUFFIXCODEID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@MARITALSTATUSCODEID uniqueidentifier = null,
@OCCUPATION nvarchar(100) = '',
@HOUSEHOLD_MOSAIC nvarchar(100) = '',
@HOUSEHOLDINCOMEESTIMATECODEID uniqueidentifier = null,
@DISCRETIONARY_SPEND money = 0,
@CNOTES nvarchar(1024) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@ORIGINALID integer = 0,
@RECURSIVEMATCH bit = 0,
@TITLECODEID uniqueidentifier = null
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--Get any existing ID
select
@ID = ID
from
dbo.WPBIOGRAPHICALDEMOGRAPHIC
where
WEALTHID=@WEALTHID
declare @CONFIRMED bit;
declare @CONFIRMEDBYAPPUSERID uniqueidentifier;
declare @DATECONFIRMED datetime;
declare @REJECTED bit;
declare @REJECTEDBYAPPUSERID uniqueidentifier;
declare @DATEREJECTED datetime;
set @CONFIRMED = 0;
set @REJECTED = 0;
if @TITLECODEID = '00000000-0000-0000-0000-000000000000'
set @TITLECODEID = null;
if @SUFFIXCODEID = '00000000-0000-0000-0000-000000000000'
set @SUFFIXCODEID = null;
if @MARITALSTATUSCODEID = '00000000-0000-0000-0000-000000000000'
set @MARITALSTATUSCODEID = null;
if @HOUSEHOLDINCOMEESTIMATECODEID = '00000000-0000-0000-0000-000000000000'
set @HOUSEHOLDINCOMEESTIMATECODEID = null;
if @CURRENTAPPUSERID is not null
begin
declare @CONFIDENCE int
select
@CONFIDENCE = coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE)
from
dbo.MATCHCODE MC
left outer join
dbo.WEALTHSOURCE WS on @SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and CR.WEALTHSOURCEID = WS.ID
where
MC.MATCHCODE = @MC
select
@REJECTED = case @CONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case @CONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case @CONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case @CONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
where
@CONFIDENCE in (0,5);
end
-- Record exists
if @ID is not null
begin
if not exists(select 1 from dbo.WPBIOGRAPHICAL where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
update
dbo.WPBIOGRAPHICALDEMOGRAPHIC
set
SOURCE = coalesce(nullif(@SOURCE, ''), SOURCE),
FULLHASH = coalesce(@FULLHASH, ''),
PARTIALHASH = coalesce(@PARTIALHASH, ''),
MC = coalesce(@MC, ''),
FULLNAME = coalesce(@FULLNAME, ''),
FIRSTNAME = coalesce(@FIRSTNAME, ''),
MIDDLENAME = coalesce(@MIDDLENAME, ''),
LASTNAME = coalesce(@LASTNAME, ''),
SUFFIXCODEID = @SUFFIXCODEID,
TITLECODEID = @TITLECODEID,
BIRTHDATE = coalesce(@BIRTHDATE, '00000000'),
OCCUPATION = coalesce(@OCCUPATION, ''),
MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
HOUSEHOLD_MOSAIC = coalesce(@HOUSEHOLD_MOSAIC, ''),
HOUSEHOLD_INCOMECODEID = @HOUSEHOLDINCOMEESTIMATECODEID,
DISCRETIONARY_SPEND = coalesce(@DISCRETIONARY_SPEND, 0),
CNOTES = coalesce(nullif(@CNOTES, ''), CNOTES),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then 1 else CONFIRMED end,
CONFIRMEDBYAPPUSERID = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @CONFIRMEDBYAPPUSERID else CONFIRMEDBYAPPUSERID end,
DATECONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @DATECONFIRMED else DATECONFIRMED end,
VIEWED = 0
where
@ID = ID
end
end
else
begin
set @ID = newid();
insert into dbo.WPBIOGRAPHICALDEMOGRAPHIC (
ID,
WEALTHID,
SOURCE,
FULLHASH,
PARTIALHASH,
MC,
FULLNAME,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
SUFFIXCODEID,
BIRTHDATE,
OCCUPATION,
MARITALSTATUSCODEID,
HOUSEHOLD_MOSAIC,
HOUSEHOLD_INCOMECODEID,
DISCRETIONARY_SPEND,
CNOTES,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
TITLECODEID
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@FULLNAME, ''),
coalesce(@FIRSTNAME, ''),
coalesce(@MIDDLENAME, ''),
coalesce(@LASTNAME, ''),
@SUFFIXCODEID,
coalesce(@BIRTHDATE, '00000000'),
coalesce(@OCCUPATION, ''),
@MARITALSTATUSCODEID,
coalesce(@HOUSEHOLD_MOSAIC, ''),
@HOUSEHOLDINCOMEESTIMATECODEID,
coalesce(@DISCRETIONARY_SPEND, 0),
coalesce(@CNOTES, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@TITLECODEID
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;