USP_DATAFORMTEMPLATE_ADD_BATCHMODELINGANDPROPENSITYUPDATEBATCHROW

The save procedure used by the add dataform template "Modeling and Propensity Update Batch Row Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SEQUENCE	int	IN	Sequence
@PRIMARYRECORDID	uniqueidentifier	IN	Constituent
@LOOKUP_ID	nvarchar(100)	IN	Lookup ID
@KEYNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@ANNUALGIFTLIKELIHOOD	smallint	IN	Annual giving likelihood
@ANNUITYLIKELIHOOD	smallint	IN	Annuity likelihood
@BEQUESTLIKELIHOOD	smallint	IN	Bequest likelihood
@CRTLIKELIHOOD	smallint	IN	CRT likelihood
@MAJORGIVINGLIKELIHOOD	smallint	IN	Major giving likelihood
@MEMBERSHIPLIKELIHOOD	smallint	IN	Membership likelihood
@ONLINEGIVINGLIKELIHOOD	smallint	IN	Online giving likelihood
@PATIENTRESPONSELIKELIHOOD	smallint	IN	Patient response likelihood
@PLANNEDGIFTLIKELIHOOD	smallint	IN	Planned giving likelihood
@SUGGESTEDMEMBERSHIPLEVELID	uniqueidentifier	IN	Suggested membership level range
@SUGGESTEDMEMBERSHIPLEVELSCOREID	uniqueidentifier	IN	Suggested membership level score
@TARGETGIFTRANGEID	uniqueidentifier	IN	Target gift range
@TARGETGIFTRANGESCOREID	uniqueidentifier	IN	Target gift range score
@ANALYTICSPROJECTID	uniqueidentifier	IN

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHMODELINGANDPROPENSITYUPDATEBATCHROW(
    @ID uniqueidentifier = null output,
    @BATCHID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @SEQUENCE int,
    @PRIMARYRECORDID uniqueidentifier = null,
    @LOOKUP_ID nvarchar(100) = '',
    @KEYNAME nvarchar(100) = '',
    @FIRSTNAME nvarchar(50) = '',
    @ANNUALGIFTLIKELIHOOD smallint = -1,
    @ANNUITYLIKELIHOOD smallint = -1,
    @BEQUESTLIKELIHOOD smallint = -1,
    @CRTLIKELIHOOD smallint = -1,
    @MAJORGIVINGLIKELIHOOD smallint = -1,
    @MEMBERSHIPLIKELIHOOD smallint = -1,
    @ONLINEGIVINGLIKELIHOOD smallint = -1,
    @PATIENTRESPONSELIKELIHOOD smallint = -1,
    @PLANNEDGIFTLIKELIHOOD smallint = -1,
    @SUGGESTEDMEMBERSHIPLEVELID uniqueidentifier = null,
    @SUGGESTEDMEMBERSHIPLEVELSCOREID uniqueidentifier = null,
    @TARGETGIFTRANGEID uniqueidentifier = null,
    @TARGETGIFTRANGESCOREID uniqueidentifier = null,
    @ANALYTICSPROJECTID uniqueidentifier = null
) as
set nocount on;

declare @CURRENTDATE datetime;

if @ID is null
    set @ID = newid();

if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

set @CURRENTDATE = getdate();

if @SUGGESTEDMEMBERSHIPLEVELSCOREID is not null and @SUGGESTEDMEMBERSHIPLEVELID is null
    set @SUGGESTEDMEMBERSHIPLEVELID = @SUGGESTEDMEMBERSHIPLEVELSCOREID
else if @SUGGESTEDMEMBERSHIPLEVELSCOREID is null
    set @SUGGESTEDMEMBERSHIPLEVELSCOREID = @SUGGESTEDMEMBERSHIPLEVELID

if @TARGETGIFTRANGESCOREID is not null and @TARGETGIFTRANGEID is null
    set @TARGETGIFTRANGEID = @TARGETGIFTRANGESCOREID
else if @TARGETGIFTRANGESCOREID is null
    set @TARGETGIFTRANGESCOREID = @TARGETGIFTRANGEID


--Update Lookup ID on import
if @PRIMARYRECORDID is not null and nullif(@LOOKUP_ID, '') is null
  select 
    @LOOKUP_ID = LOOKUPID,
    @FIRSTNAME = FIRSTNAME,
    @KEYNAME = KEYNAME
  from dbo.CONSTITUENT
  where ID = @PRIMARYRECORDID



begin try
insert into dbo.BATCHMODELINGANDPROPENSITYUPDATE(
    [ID],
    [BATCHID], [SEQUENCE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED],
    [PRIMARYRECORDID],
    [LOOKUP_ID],
    [KEYNAME],
    [FIRSTNAME],
    [ANNUALGIFTLIKELIHOOD],
    [ANNUITYLIKELIHOOD],
    [BEQUESTLIKELIHOOD],
    [CRTLIKELIHOOD],
    [MAJORGIVINGLIKELIHOOD],
    [MEMBERSHIPLIKELIHOOD],
    [ONLINEGIVINGLIKELIHOOD],
    [PATIENTRESPONSELIKELIHOOD],
    [PLANNEDGIFTLIKELIHOOD],
    [SUGGESTEDMEMBERSHIPLEVELID],
    [SUGGESTEDMEMBERSHIPLEVELSCOREID],
    [TARGETGIFTRANGEID],
    [TARGETGIFTRANGESCOREID],
    [ANALYTICSPROJECTID]
) 
values 
(
    @ID,
    @BATCHID, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
    @PRIMARYRECORDID,
    coalesce(@LOOKUP_ID, ''),
    coalesce(@KEYNAME, ''),
    coalesce(@FIRSTNAME, ''),
    coalesce(nullif(@ANNUALGIFTLIKELIHOOD, -1), (select ANNUALGIFTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@ANNUITYLIKELIHOOD, -1), (select ANNUITYLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@BEQUESTLIKELIHOOD, -1), (select BEQUESTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@CRTLIKELIHOOD, -1), (select CRTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@MAJORGIVINGLIKELIHOOD, -1), (select MAJORGIVINGLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@MEMBERSHIPLIKELIHOOD, -1), (select MEMBERSHIPLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@ONLINEGIVINGLIKELIHOOD, -1), (select ONLINEGIVINGLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@PATIENTRESPONSELIKELIHOOD, -1), (select PATIENTRESPONSELIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(nullif(@PLANNEDGIFTLIKELIHOOD, -1), (select PLANNEDGIFTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID), -1),
    coalesce(@SUGGESTEDMEMBERSHIPLEVELID, (select SUGGESTEDMEMBERSHIPLEVELID from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID)),
    coalesce(@SUGGESTEDMEMBERSHIPLEVELSCOREID, (select SUGGESTEDMEMBERSHIPLEVELID from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID)),
    coalesce(@TARGETGIFTRANGEID, (select TARGETGIFTRANGEID from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID)),
    coalesce(@TARGETGIFTRANGESCOREID, (select TARGETGIFTRANGEID from dbo.MODELINGANDPROPENSITY where ID = @PRIMARYRECORDID)),
    @ANALYTICSPROJECTID
);
end try
begin catch
    exec.dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;