USP_DATAFORMTEMPLATE_EDIT_MODELINGANDPROPENSITYUPDATEBATCHTEMPLATE_2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@LOOKUP_ID	nvarchar(100)	IN
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@ANNUALGIFTLIKELIHOOD	smallint	IN
@ANNUITYLIKELIHOOD	smallint	IN
@BEQUESTLIKELIHOOD	smallint	IN
@CRTLIKELIHOOD	smallint	IN
@MAJORGIVINGLIKELIHOOD	smallint	IN
@MEMBERSHIPLIKELIHOOD	smallint	IN
@ONLINEGIVINGLIKELIHOOD	smallint	IN
@PATIENTRESPONSELIKELIHOOD	smallint	IN
@PLANNEDGIFTLIKELIHOOD	smallint	IN
@SUGGESTEDMEMBERSHIPLEVELID	uniqueidentifier	IN
@SUGGESTEDMEMBERSHIPLEVELSCORE	uniqueidentifier	IN
@TARGETGIFTRANGEID	uniqueidentifier	IN
@TARGETGIFTRANGESCORE	uniqueidentifier	IN
@ANALYTICSPROJECTID	uniqueidentifier	IN
@VALIDATEONLY	bit	IN
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MODELINGANDPROPENSITYUPDATEBATCHTEMPLATE_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LOOKUP_ID nvarchar(100),
    @KEYNAME nvarchar(100),
    @FIRSTNAME nvarchar(50),
    @ANNUALGIFTLIKELIHOOD smallint,
    @ANNUITYLIKELIHOOD smallint,
    @BEQUESTLIKELIHOOD smallint,
    @CRTLIKELIHOOD smallint,
    @MAJORGIVINGLIKELIHOOD smallint,
    @MEMBERSHIPLIKELIHOOD smallint,
    @ONLINEGIVINGLIKELIHOOD smallint,
    @PATIENTRESPONSELIKELIHOOD smallint,
    @PLANNEDGIFTLIKELIHOOD smallint,
    @SUGGESTEDMEMBERSHIPLEVELID uniqueidentifier,
    @SUGGESTEDMEMBERSHIPLEVELSCORE uniqueidentifier,
    @TARGETGIFTRANGEID uniqueidentifier,
    @TARGETGIFTRANGESCORE uniqueidentifier,
    @ANALYTICSPROJECTID uniqueidentifier,
    @VALIDATEONLY bit
)
as

    set nocount on;

    if @VALIDATEONLY is null
        set @VALIDATEONLY = 0;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    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;

    begin try

        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 = @ID;

        if @VALIDATEONLY = 0
        begin
            if exists(select top(1) ID from dbo.MODELINGANDPROPENSITY where ID = @ID) begin
                update dbo.MODELINGANDPROPENSITY set
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE,
                    ANNUALGIFTLIKELIHOOD = coalesce(@ANNUALGIFTLIKELIHOOD, (select ANNUALGIFTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    ANNUITYLIKELIHOOD = coalesce(@ANNUITYLIKELIHOOD, (select ANNUITYLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    BEQUESTLIKELIHOOD = coalesce(@BEQUESTLIKELIHOOD, (select BEQUESTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    CRTLIKELIHOOD = coalesce(@CRTLIKELIHOOD, (select CRTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    MAJORGIVINGLIKELIHOOD = coalesce(@MAJORGIVINGLIKELIHOOD, (select MAJORGIVINGLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    MEMBERSHIPLIKELIHOOD = coalesce(@MEMBERSHIPLIKELIHOOD, (select MEMBERSHIPLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    ONLINEGIVINGLIKELIHOOD = coalesce(@ONLINEGIVINGLIKELIHOOD, (select ONLINEGIVINGLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    PATIENTRESPONSELIKELIHOOD = coalesce(@PATIENTRESPONSELIKELIHOOD, (select PATIENTRESPONSELIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    PLANNEDGIFTLIKELIHOOD = coalesce(@PLANNEDGIFTLIKELIHOOD, (select PLANNEDGIFTLIKELIHOOD from dbo.MODELINGANDPROPENSITY where ID = @ID), -1),
                    SUGGESTEDMEMBERSHIPLEVELID = coalesce(nullif(@SUGGESTEDMEMBERSHIPLEVELSCORE, SUGGESTEDMEMBERSHIPLEVELID), @SUGGESTEDMEMBERSHIPLEVELID, (select SUGGESTEDMEMBERSHIPLEVELID from dbo.MODELINGANDPROPENSITY where ID = @ID)),
                    TARGETGIFTRANGEID = coalesce(nullif(@TARGETGIFTRANGESCORE, TARGETGIFTRANGEID), @TARGETGIFTRANGEID, (select TARGETGIFTRANGEID from dbo.MODELINGANDPROPENSITY where ID = @ID))
                where
                    ID = @ID

            end
            else begin
                insert into dbo.MODELINGANDPROPENSITY
                (
                    ID,
                    CHANGEDBYID, DATECHANGED, ADDEDBYID, DATEADDED,
                    ANNUALGIFTLIKELIHOOD, ANNUITYLIKELIHOOD, BEQUESTLIKELIHOOD, CRTLIKELIHOOD, MAJORGIVINGLIKELIHOOD, MEMBERSHIPLIKELIHOOD,
                    ONLINEGIVINGLIKELIHOOD, PATIENTRESPONSELIKELIHOOD, PLANNEDGIFTLIKELIHOOD, SUGGESTEDMEMBERSHIPLEVELID, 
                    TARGETGIFTRANGEID
                )
                values
                (
                    @ID,
                    @CHANGEAGENTID, @CURRENTDATE, @CHANGEAGENTID, @CURRENTDATE,
                    coalesce(@ANNUALGIFTLIKELIHOOD, -1),
                    coalesce(@ANNUITYLIKELIHOOD, -1),
                    coalesce(@BEQUESTLIKELIHOOD, -1),
                    coalesce(@CRTLIKELIHOOD, -1),
                    coalesce(@MAJORGIVINGLIKELIHOOD, -1),
                    coalesce(@MEMBERSHIPLIKELIHOOD, -1),
                    coalesce(@ONLINEGIVINGLIKELIHOOD, -1),
                    coalesce(@PATIENTRESPONSELIKELIHOOD, -1),
                    coalesce(@PLANNEDGIFTLIKELIHOOD, -1),
                    case
                        when @SUGGESTEDMEMBERSHIPLEVELSCORE is not null then @SUGGESTEDMEMBERSHIPLEVELSCORE
                        else @SUGGESTEDMEMBERSHIPLEVELID
                    end, 
                    case 
                        when @TARGETGIFTRANGESCORE is not null then @TARGETGIFTRANGESCORE
                        else @TARGETGIFTRANGEID
                    end
                )
            end

            if @ANNUALGIFTLIKELIHOOD = @EXISTINGANNUALGIFTLIKELIHOOD
                set @ANNUALGIFTLIKELIHOOD = null;
            if @ANNUITYLIKELIHOOD = @EXISTINGANNUITYLIKELIHOOD
                set @ANNUITYLIKELIHOOD = null;
            if @ANNUITYLIKELIHOOD = @EXISTINGANNUITYLIKELIHOOD
                set @BEQUESTLIKELIHOOD = null;
            if @CRTLIKELIHOOD = @EXISTINGCRTLIKELIHOOD
                set @CRTLIKELIHOOD = null;
            if @MAJORGIVINGLIKELIHOOD = @EXISTINGMAJORGIVINGLIKELIHOOD
                set @MAJORGIVINGLIKELIHOOD = null;
            if @MEMBERSHIPLIKELIHOOD = @EXISTINGMEMBERSHIPLIKELIHOOD
                set @MEMBERSHIPLIKELIHOOD = null;
            if @ONLINEGIVINGLIKELIHOOD = @EXISTINGONLINEGIVINGLIKELIHOOD
                set @ONLINEGIVINGLIKELIHOOD = null;
            if @PATIENTRESPONSELIKELIHOOD = @EXISTINGPATIENTRESPONSELIKELIHOOD
                set @PATIENTRESPONSELIKELIHOOD = null;
            if @PLANNEDGIFTLIKELIHOOD = @EXISTINGPLANNEDGIFTLIKELIHOOD
                set @PLANNEDGIFTLIKELIHOOD = null;
            if @SUGGESTEDMEMBERSHIPLEVELID = @EXISTINGSUGGESTEDMEMBERSHIPLEVELID
                set @SUGGESTEDMEMBERSHIPLEVELID = null;
            if @TARGETGIFTRANGEID = @EXISTINGTARGETGIFTRANGEID
                set @TARGETGIFTRANGEID = null;

            exec dbo.USP_MODELINGANDPROPENSITYDELIVERY_SYNCHRONIZE @ID, @ANALYTICSPROJECTID, @ANNUALGIFTLIKELIHOOD, @ANNUITYLIKELIHOOD,
                @BEQUESTLIKELIHOOD, @CRTLIKELIHOOD, @MAJORGIVINGLIKELIHOOD, @MEMBERSHIPLIKELIHOOD, @ONLINEGIVINGLIKELIHOOD,
                @PATIENTRESPONSELIKELIHOOD, @PLANNEDGIFTLIKELIHOOD, @SUGGESTEDMEMBERSHIPLEVELID, @NCOAMOVEDINDICATOR=null,
                @TARGETGIFTRANGEID=@TARGETGIFTRANGEID, @WEALTHESTIMATORRATINGID=null;
        end

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;