USP_BBAMODELINGANDPROPENSITYFILEIMPORT
This procedure is used by the Target Analytics Modeling and Propensity File Import Record Operation for saving the information from a Target Analytics Modeling and Propensity Import File to the database.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @BBAID | nvarchar(100) | IN | |
| @ANNUALGIFTLIKELIHOOD | int | IN | |
| @ANNUITYLIKELIHOOD | int | IN | |
| @BEQUESTLIKELIHOOD | int | IN | |
| @CRTLIKELIHOOD | int | IN | |
| @MAJORGIVINGLIKELIHOOD | int | IN | |
| @MEMBERSHIPLIKELIHOOD | int | IN | |
| @ONLINEGIVINGLIKELIHOOD | int | IN | |
| @PATIENTRESPONSELIKELIHOOD | int | IN | |
| @PLANNEDGIFTLIKELIHOOD | int | IN | |
| @SUGGESTEDMEMBERSHIPLEVELSCORE | int | IN | |
| @NCOAMOVEDINDICATOR | bit | IN | |
| @TARGETGIFTRANGESCORE | int | IN | |
| @WPSCREENINGINDICATOR | nvarchar(1) | IN | |
| @WEALTHESTIMATORRATINGSCORE | int | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @RUNWEALTHPOINT | bit | IN | |
| @RESEARCHGROUPNAME | nvarchar(100) | IN | |
| @RESEARCHGROUPDESCRIPTION | nvarchar(255) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CREATEAUXILIARYRESEARCHGROUPS | bit | IN | |
| @AUXILIARYRESEARCHGROUPSIZE | int | IN | |
| @ANALYTICSPROJECTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_BBAMODELINGANDPROPENSITYFILEIMPORT (
@CONSTITUENTID uniqueidentifier output,
@BBAID nvarchar(100),
@ANNUALGIFTLIKELIHOOD int = 0,
@ANNUITYLIKELIHOOD int = 0,
@BEQUESTLIKELIHOOD int = 0,
@CRTLIKELIHOOD int = 0,
@MAJORGIVINGLIKELIHOOD int = 0,
@MEMBERSHIPLIKELIHOOD int = 0,
@ONLINEGIVINGLIKELIHOOD int = 0,
@PATIENTRESPONSELIKELIHOOD int = 0,
@PLANNEDGIFTLIKELIHOOD int = 0,
@SUGGESTEDMEMBERSHIPLEVELSCORE int = 0,
@NCOAMOVEDINDICATOR bit = 0,
@TARGETGIFTRANGESCORE int = 0,
@WPSCREENINGINDICATOR nvarchar(1) = '',
@WEALTHESTIMATORRATINGSCORE int = 0,
@CHANGEAGENTID uniqueidentifier,
@RUNWEALTHPOINT bit = 0,
@RESEARCHGROUPNAME nvarchar(100) = '',
@RESEARCHGROUPDESCRIPTION nvarchar(255) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@CREATEAUXILIARYRESEARCHGROUPS bit = 0,
@AUXILIARYRESEARCHGROUPSIZE int = 0,
@ANALYTICSPROJECTID uniqueidentifier = null
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
select
@CONSTITUENTID = C.ID
from
dbo.CONSTITUENT C
left join
dbo.TAIMPORTIDMAP
on
TAIMPORTIDMAP.ID = C.ID
where
TAIMPORTIDMAP.LOOKUPID = @BBAID;
if @CONSTITUENTID is not null begin
declare @SUGGESTEDMEMBERSHIPLEVELID uniqueidentifier;
select top 1 @SUGGESTEDMEMBERSHIPLEVELID = ID from dbo.SUGGESTEDMEMBERSHIPLEVEL where SCORE = @SUGGESTEDMEMBERSHIPLEVELSCORE;
if @SUGGESTEDMEMBERSHIPLEVELID is null and @SUGGESTEDMEMBERSHIPLEVELSCORE > 0 begin
raiserror('This suggested membership level score does not exist in the database.',11,1);
return 1;
end
declare @TARGETGIFTRANGEID uniqueidentifier;
select top 1 @TARGETGIFTRANGEID = ID from dbo.TARGETGIFTRANGE where SCORE = @TARGETGIFTRANGESCORE;
if @TARGETGIFTRANGEID is null and @TARGETGIFTRANGESCORE > 0 begin
raiserror('This target gift range score does not exist in the database.',11,1);
return 1;
end
declare @WEALTHESTIMATORRATINGID uniqueidentifier;
select top 1 @WEALTHESTIMATORRATINGID = ID from dbo.WEALTHESTIMATORRATING where SCORE = @WEALTHESTIMATORRATINGSCORE;
if @WEALTHESTIMATORRATINGID is null and @WEALTHESTIMATORRATINGSCORE > 0 begin
raiserror('This wealth estimator rating score does not exist in the database.',11,1);
return 1;
end
if exists (select ID from dbo.MODELINGANDPROPENSITY where ID = @CONSTITUENTID) begin
declare @EXISTINGANNUALGIFTLIKELIHOOD smallint;
declare @EXISTINGANNUITYLIKELIHOOD smallint;
declare @EXISTINGBEQUESTLIKELIHOOD smallint;
declare @EXISTINGCRTLIKELIHOOD smallint;
declare @EXISTINGMAJORGIVINGLIKELIHOOD smallint;
declare @EXISTINGMEMBERSHIPLIKELIHOOD smallint;
declare @EXISTINGONLINEGIVINGLIKELIHOOD smallint;
declare @EXISTINGPATIENTRESPONSELIKELIHOOD smallint;
declare @EXISTINGPLANNEDGIFTLIKELIHOOD smallint;
declare @EXISTINGSUGGESTEDMEMBERSHIPLEVELID uniqueidentifier;
declare @EXISTINGTARGETGIFTRANGEID uniqueidentifier;
select
@EXISTINGANNUALGIFTLIKELIHOOD = ANNUALGIFTLIKELIHOOD,
@EXISTINGANNUITYLIKELIHOOD = ANNUITYLIKELIHOOD,
@EXISTINGBEQUESTLIKELIHOOD = BEQUESTLIKELIHOOD,
@EXISTINGCRTLIKELIHOOD = CRTLIKELIHOOD,
@EXISTINGMAJORGIVINGLIKELIHOOD = MAJORGIVINGLIKELIHOOD,
@EXISTINGMEMBERSHIPLIKELIHOOD = MEMBERSHIPLIKELIHOOD,
@EXISTINGONLINEGIVINGLIKELIHOOD = ONLINEGIVINGLIKELIHOOD,
@EXISTINGPATIENTRESPONSELIKELIHOOD = PATIENTRESPONSELIKELIHOOD,
@EXISTINGPLANNEDGIFTLIKELIHOOD = PLANNEDGIFTLIKELIHOOD,
@EXISTINGSUGGESTEDMEMBERSHIPLEVELID = SUGGESTEDMEMBERSHIPLEVELID,
@EXISTINGTARGETGIFTRANGEID = TARGETGIFTRANGEID
from
dbo.MODELINGANDPROPENSITY
where
ID = @CONSTITUENTID;
if @ANALYTICSPROJECTID is null and (
@ANNUALGIFTLIKELIHOOD <> coalesce(@EXISTINGANNUALGIFTLIKELIHOOD, -1) and @ANNUALGIFTLIKELIHOOD <> -1
or @ANNUITYLIKELIHOOD <> coalesce(@EXISTINGANNUITYLIKELIHOOD, -1) and @ANNUITYLIKELIHOOD <> -1
or @BEQUESTLIKELIHOOD <> coalesce(@EXISTINGBEQUESTLIKELIHOOD, -1) and @BEQUESTLIKELIHOOD <> -1
or @CRTLIKELIHOOD <> coalesce(@EXISTINGCRTLIKELIHOOD, -1) and @CRTLIKELIHOOD <> -1
or @MAJORGIVINGLIKELIHOOD <> coalesce(@EXISTINGMAJORGIVINGLIKELIHOOD, -1) and @MAJORGIVINGLIKELIHOOD <> -1
or @MEMBERSHIPLIKELIHOOD <> coalesce(@EXISTINGMEMBERSHIPLIKELIHOOD, -1) and @MEMBERSHIPLIKELIHOOD <> -1
or @ONLINEGIVINGLIKELIHOOD <> coalesce(@EXISTINGONLINEGIVINGLIKELIHOOD, -1) and @ONLINEGIVINGLIKELIHOOD <> -1
or @PATIENTRESPONSELIKELIHOOD <> coalesce(@EXISTINGPATIENTRESPONSELIKELIHOOD, -1) and @PATIENTRESPONSELIKELIHOOD <> -1
or @PLANNEDGIFTLIKELIHOOD <> coalesce(@EXISTINGPLANNEDGIFTLIKELIHOOD, -1) and @PLANNEDGIFTLIKELIHOOD <> -1
or @SUGGESTEDMEMBERSHIPLEVELID <> @EXISTINGSUGGESTEDMEMBERSHIPLEVELID or @EXISTINGSUGGESTEDMEMBERSHIPLEVELID is null and @SUGGESTEDMEMBERSHIPLEVELID is not null
or @TARGETGIFTRANGEID <> @EXISTINGTARGETGIFTRANGEID or @EXISTINGTARGETGIFTRANGEID is null and @TARGETGIFTRANGEID is not null
)
raiserror('BBERR_ANALYTICSPROJECTMISSING',13,2);
update
dbo.MODELINGANDPROPENSITY
set
ANNUALGIFTLIKELIHOOD = coalesce(nullif(@ANNUALGIFTLIKELIHOOD, -1), ANNUALGIFTLIKELIHOOD, -1),
ANNUITYLIKELIHOOD = coalesce(nullif(@ANNUITYLIKELIHOOD, -1), ANNUITYLIKELIHOOD, -1),
BEQUESTLIKELIHOOD = coalesce(nullif(@BEQUESTLIKELIHOOD, -1), BEQUESTLIKELIHOOD, -1),
CRTLIKELIHOOD = coalesce(nullif(@CRTLIKELIHOOD, -1), CRTLIKELIHOOD, -1),
MAJORGIVINGLIKELIHOOD = coalesce(nullif(@MAJORGIVINGLIKELIHOOD, -1), MAJORGIVINGLIKELIHOOD, -1),
MEMBERSHIPLIKELIHOOD = coalesce(nullif(@MEMBERSHIPLIKELIHOOD, -1), MEMBERSHIPLIKELIHOOD, -1),
ONLINEGIVINGLIKELIHOOD = coalesce(nullif(@ONLINEGIVINGLIKELIHOOD, -1), ONLINEGIVINGLIKELIHOOD, -1),
PATIENTRESPONSELIKELIHOOD = coalesce(nullif(@PATIENTRESPONSELIKELIHOOD, -1), PATIENTRESPONSELIKELIHOOD, -1),
PLANNEDGIFTLIKELIHOOD = coalesce(nullif(@PLANNEDGIFTLIKELIHOOD, -1), PLANNEDGIFTLIKELIHOOD, -1),
SUGGESTEDMEMBERSHIPLEVELID = coalesce(@SUGGESTEDMEMBERSHIPLEVELID, SUGGESTEDMEMBERSHIPLEVELID),
NCOAMOVEDINDICATOR = coalesce(@NCOAMOVEDINDICATOR, NCOAMOVEDINDICATOR, 0),
TARGETGIFTRANGEID = coalesce(@TARGETGIFTRANGEID, TARGETGIFTRANGEID),
WPSCREENINGINDICATOR = coalesce(nullif(@WPSCREENINGINDICATOR, ''), WPSCREENINGINDICATOR, ''),
WEALTHESTIMATORRATINGID = coalesce(@WEALTHESTIMATORRATINGID, WEALTHESTIMATORRATINGID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.MODELINGANDPROPENSITY
where
ID = @CONSTITUENTID and
(@ANNUALGIFTLIKELIHOOD <> -1
or @ANNUITYLIKELIHOOD <> -1
or @BEQUESTLIKELIHOOD <> -1
or @CRTLIKELIHOOD <> -1
or @MAJORGIVINGLIKELIHOOD <> -1
or @MEMBERSHIPLIKELIHOOD <> -1
or @ONLINEGIVINGLIKELIHOOD <> -1
or @PATIENTRESPONSELIKELIHOOD <> -1
or @PLANNEDGIFTLIKELIHOOD <> -1
or @SUGGESTEDMEMBERSHIPLEVELID is not null
or @NCOAMOVEDINDICATOR is not null
or @TARGETGIFTRANGEID is not null
or ISNULL(@WPSCREENINGINDICATOR,'') <> ''
or @WEALTHESTIMATORRATINGID is not null);
end
else begin
if @ANALYTICSPROJECTID is null and (
@ANNUALGIFTLIKELIHOOD <> -1
or @ANNUITYLIKELIHOOD <> -1
or @BEQUESTLIKELIHOOD <> -1
or @CRTLIKELIHOOD <> -1
or @MAJORGIVINGLIKELIHOOD <> -1
or @MEMBERSHIPLIKELIHOOD <> -1
or @ONLINEGIVINGLIKELIHOOD <> -1
or @PATIENTRESPONSELIKELIHOOD <> -1
or @PLANNEDGIFTLIKELIHOOD <> -1
or @SUGGESTEDMEMBERSHIPLEVELID is not null
or @TARGETGIFTRANGEID is not null
)
raiserror('BBERR_ANALYTICSPROJECTMISSING',13,2);
insert into dbo.MODELINGANDPROPENSITY (
ID,
ANNUALGIFTLIKELIHOOD,
ANNUITYLIKELIHOOD,
BEQUESTLIKELIHOOD,
CRTLIKELIHOOD,
MAJORGIVINGLIKELIHOOD,
MEMBERSHIPLIKELIHOOD,
ONLINEGIVINGLIKELIHOOD,
PATIENTRESPONSELIKELIHOOD,
PLANNEDGIFTLIKELIHOOD,
SUGGESTEDMEMBERSHIPLEVELID,
NCOAMOVEDINDICATOR,
TARGETGIFTRANGEID,
WPSCREENINGINDICATOR,
WEALTHESTIMATORRATINGID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@ANNUALGIFTLIKELIHOOD,
@ANNUITYLIKELIHOOD,
@BEQUESTLIKELIHOOD,
@CRTLIKELIHOOD,
@MAJORGIVINGLIKELIHOOD,
@MEMBERSHIPLIKELIHOOD,
@ONLINEGIVINGLIKELIHOOD,
@PATIENTRESPONSELIKELIHOOD,
@PLANNEDGIFTLIKELIHOOD,
@SUGGESTEDMEMBERSHIPLEVELID,
coalesce(@NCOAMOVEDINDICATOR, 0),
@TARGETGIFTRANGEID,
@WPSCREENINGINDICATOR,
@WEALTHESTIMATORRATINGID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_MODELINGANDPROPENSITYDELIVERY_SYNCHRONIZE @CONSTITUENTID, @ANALYTICSPROJECTID,
@ANNUALGIFTLIKELIHOOD, @ANNUITYLIKELIHOOD,
@BEQUESTLIKELIHOOD, @CRTLIKELIHOOD,
@MAJORGIVINGLIKELIHOOD, @MEMBERSHIPLIKELIHOOD,
@ONLINEGIVINGLIKELIHOOD, @PATIENTRESPONSELIKELIHOOD,
@PLANNEDGIFTLIKELIHOOD, @SUGGESTEDMEMBERSHIPLEVELID,
@NCOAMOVEDINDICATOR, @TARGETGIFTRANGEID,
@WEALTHESTIMATORRATINGID;
--@WPSCREENINGINDICATOR is no longer used
if @RUNWEALTHPOINT = 1 begin
declare @RESEARCHGROUPID uniqueidentifier;
set @RESEARCHGROUPID = null;
set @CURRENTDATE = getdate();
select
@RESEARCHGROUPID = RESEARCHGROUP.ID
from
dbo.RESEARCHGROUP
where
RESEARCHGROUP.NAME = @RESEARCHGROUPNAME;
if @RESEARCHGROUPID is null begin
set @RESEARCHGROUPID = newid();
insert into dbo.RESEARCHGROUP(
ID,
NAME,
DESCRIPTION,
OWNERID,
OTHERSCANMODIFY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@RESEARCHGROUPID,
@RESEARCHGROUPNAME,
@RESEARCHGROUPDESCRIPTION,
@CURRENTAPPUSERID,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if not exists (select RESEARCHGROUPMEMBER.ID from dbo.RESEARCHGROUPMEMBER where RESEARCHGROUPMEMBER.RESEARCHGROUPID = @RESEARCHGROUPID and RESEARCHGROUPMEMBER.CONSTITUENTID = @CONSTITUENTID) begin
insert into dbo.RESEARCHGROUPMEMBER (
RESEARCHGROUPID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@RESEARCHGROUPID,
@CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_RESEARCHGROUP_INCREMENTNUMMEMBERS @RESEARCHGROUPID, 1, @CHANGEAGENTID;
end
if @CREATEAUXILIARYRESEARCHGROUPS = 1 and @AUXILIARYRESEARCHGROUPSIZE > 0 begin
declare @AUXILIARYRESEARCHGROUPID uniqueidentifier
select
@AUXILIARYRESEARCHGROUPID = RESEARCHGROUP.ID
from
dbo.RESEARCHGROUP
left join dbo.RESEARCHGROUPMEMBER on
RESEARCHGROUP.ID = RESEARCHGROUPMEMBER.RESEARCHGROUPID
where
NAME like @RESEARCHGROUPNAME+ '%' and
RESEARCHGROUP.ID <> @RESEARCHGROUPID
group by
RESEARCHGROUP.ID
having
COUNT(RESEARCHGROUPMEMBER.ID)< @AUXILIARYRESEARCHGROUPSIZE
if @AUXILIARYRESEARCHGROUPID is null begin
declare @AUXILIARYRESEARCHGROUPNAME nvarchar(100);
select
@AUXILIARYRESEARCHGROUPNAME = @RESEARCHGROUPNAME +' '+ cast(COUNT(ID)as nvarchar(10))
from
dbo.RESEARCHGROUP
where
NAME like @RESEARCHGROUPNAME+ '%'
set @AUXILIARYRESEARCHGROUPID = newid();
insert into dbo.RESEARCHGROUP(
ID,
NAME,
DESCRIPTION,
OWNERID,
OTHERSCANMODIFY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@AUXILIARYRESEARCHGROUPID,
@AUXILIARYRESEARCHGROUPNAME,
@RESEARCHGROUPDESCRIPTION,
@CURRENTAPPUSERID,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if not exists (select RESEARCHGROUPMEMBER.ID from dbo.RESEARCHGROUPMEMBER where RESEARCHGROUPMEMBER.RESEARCHGROUPID = @AUXILIARYRESEARCHGROUPID and RESEARCHGROUPMEMBER.CONSTITUENTID = @CONSTITUENTID) begin
insert into dbo.RESEARCHGROUPMEMBER (
RESEARCHGROUPID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@AUXILIARYRESEARCHGROUPID,
@CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_RESEARCHGROUP_INCREMENTNUMMEMBERS @AUXILIARYRESEARCHGROUPID, 1, @CHANGEAGENTID;
end
end
end
end
else begin
raiserror('The ImportID does not match a constituent in the database.',11,1);
return 1;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;