USP_DATAFORMTEMPLATE_EDIT_GIVINGSUMMARYBATCHTEMPLATE

The save procedure used by the edit dataform template "Giving Summary Update Batch Template Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@LOOKUP_ID	nvarchar(100)	IN	Constituent/Lookup ID
@FIRSTGIFTAMOUNT	money	IN	First gift amount
@FIRSTGIFTDATE	datetime	IN	First gift date
@FIRSTGIFTDESIGNATION	nvarchar(100)	IN	First gift designation
@FIRSTGIFTTYPE	nvarchar(100)	IN	First gift type
@LARGESTGIFTAMOUNT	money	IN	Largest gift amount
@LARGESTGIFTDATE	datetime	IN	Largest gift date
@LARGESTGIFTDESIGNATION	nvarchar(100)	IN	Largest gift designation
@LARGESTGIFTTYPE	nvarchar(100)	IN	Largest gift type
@LATESTGIFTAMOUNT	money	IN	Latest gift amount
@LATESTGIFTDATE	datetime	IN	Latest gift date
@LATESTGIFTDESIGNATION	nvarchar(100)	IN	Latest gift designation
@LATESTGIFTTYPE	nvarchar(100)	IN	Latest gift type
@TOTALGIFTAMOUNT	money	IN	Total giving
@TOTALGIFTSGIVEN	int	IN	Total number of gifts

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GIVINGSUMMARYBATCHTEMPLATE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LOOKUP_ID nvarchar(100),
    @FIRSTGIFTAMOUNT money,
    @FIRSTGIFTDATE datetime,
    @FIRSTGIFTDESIGNATION nvarchar(100),
    @FIRSTGIFTTYPE nvarchar(100),
    @LARGESTGIFTAMOUNT money,
    @LARGESTGIFTDATE datetime,
    @LARGESTGIFTDESIGNATION nvarchar(100),
    @LARGESTGIFTTYPE nvarchar(100),
    @LATESTGIFTAMOUNT money,
    @LATESTGIFTDATE datetime,
    @LATESTGIFTDESIGNATION nvarchar(100),
    @LATESTGIFTTYPE nvarchar(100),
    @TOTALGIFTAMOUNT money,
    @TOTALGIFTSGIVEN int
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

        if exists(select top(1) ID from dbo.RE7INTEGRATIONGIVINGSUMMARY where ID = @ID)
            update dbo.RE7INTEGRATIONGIVINGSUMMARY set
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE,
                FIRSTGIFTAMOUNT = coalesce(nullif(@FIRSTGIFTAMOUNT, 0), FIRSTGIFTAMOUNT),
                FIRSTGIFTDATE = coalesce(@FIRSTGIFTDATE, FIRSTGIFTDATE),
                LATESTGIFTAMOUNT = coalesce(nullif(@LATESTGIFTAMOUNT, 0), LATESTGIFTAMOUNT),
                LATESTGIFTDATE = coalesce(@LATESTGIFTDATE, LATESTGIFTDATE),
                LARGESTGIFTAMOUNT = coalesce(nullif(@LARGESTGIFTAMOUNT, 0), LARGESTGIFTAMOUNT),
                LARGESTGIFTDATE = coalesce(@LARGESTGIFTDATE, LARGESTGIFTDATE),
                TOTALGIFTSGIVEN = coalesce(nullif(@TOTALGIFTSGIVEN, 0), TOTALGIFTSGIVEN),
                TOTALGIFTAMOUNT = coalesce(nullif(@TOTALGIFTAMOUNT, 0), TOTALGIFTAMOUNT),
                FIRSTGIFTTYPE = coalesce(nullif(@FIRSTGIFTTYPE, ''), FIRSTGIFTTYPE),
                FIRSTGIFTDESIGNATION = coalesce(nullif(@FIRSTGIFTDESIGNATION, ''), FIRSTGIFTDESIGNATION),
                LARGESTGIFTTYPE = coalesce(nullif(@LARGESTGIFTTYPE, ''), LARGESTGIFTTYPE),
                LARGESTGIFTDESIGNATION = coalesce(nullif(@LARGESTGIFTDESIGNATION, ''), LARGESTGIFTDESIGNATION),
                LATESTGIFTTYPE = coalesce(nullif(@LATESTGIFTTYPE, ''), LATESTGIFTTYPE),
                LATESTGIFTDESIGNATION = coalesce(nullif(@LATESTGIFTDESIGNATION, ''), LATESTGIFTDESIGNATION)
            where
                ID = @ID
        else
            insert into dbo.RE7INTEGRATIONGIVINGSUMMARY
            (
                ID,
                CHANGEDBYID, DATECHANGED, ADDEDBYID, DATEADDED,
                FIRSTGIFTAMOUNT, FIRSTGIFTDATE, LATESTGIFTAMOUNT, LATESTGIFTDATE, LARGESTGIFTAMOUNT,
                LARGESTGIFTDATE, TOTALGIFTSGIVEN, TOTALGIFTAMOUNT, FIRSTGIFTTYPE, 
                FIRSTGIFTDESIGNATION, LARGESTGIFTTYPE, LARGESTGIFTDESIGNATION, LATESTGIFTTYPE, LATESTGIFTDESIGNATION
            )
            values
            (
                @ID, 
                @CHANGEAGENTID, @CURRENTDATE, @CHANGEAGENTID, @CURRENTDATE,
                coalesce(@FIRSTGIFTAMOUNT, 0),
                @FIRSTGIFTDATE, 
                coalesce(@LATESTGIFTAMOUNT, 0),
                @LATESTGIFTDATE, 
                coalesce(@LARGESTGIFTAMOUNT, 0),
                @LARGESTGIFTDATE, 
                coalesce(@TOTALGIFTSGIVEN, 0), 
                coalesce(@TOTALGIFTAMOUNT, 0),
                coalesce(@FIRSTGIFTTYPE, ''),
                coalesce(@FIRSTGIFTDESIGNATION, ''), 
                coalesce(@LARGESTGIFTTYPE, ''), 
                coalesce(@LARGESTGIFTDESIGNATION, ''),
                coalesce(@LATESTGIFTTYPE, ''),
                coalesce(@LATESTGIFTDESIGNATION, '')
            )
    exec dbo.USP_WEALTHCAPACITY_UPDATE @ID, @CHANGEAGENTID;    
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;