USP_DATAFORMTEMPLATE_EDIT_GIFTSPONSORSHIP

The save procedure used by the edit dataform template "Financial Sponsor 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.
@REVENUECONSTITUENTID	uniqueidentifier	IN	Financial sponsor
@SPONSORSHIPCONSTITUENTID	uniqueidentifier	IN	Corresponding sponsor
@GIFTRECIPIENT	bit	IN	Gift sponsorship
@RESETRECOGNITIONCREDITS	bit	IN	Recognition credit
@RESETSOLICITORS	bit	IN	Solicitors
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GIFTSPONSORSHIP (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @REVENUECONSTITUENTID uniqueidentifier,
    @SPONSORSHIPCONSTITUENTID uniqueidentifier,
    @GIFTRECIPIENT bit,
    @RESETRECOGNITIONCREDITS bit,
    @RESETSOLICITORS bit

)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        declare @ORIGINALREVENUECONSTITUENTID uniqueidentifier;
        declare @NEWREVENUECONSTITUENTID uniqueidentifier;
        declare @COMMITMENTID uniqueidentifier;
        declare @SPONSORSHIPID uniqueidentifier;
        declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;

        select @ORIGINALREVENUECONSTITUENTID = REVENUE.CONSTITUENTID,
               @COMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID,
               @SPONSORSHIPID = SPONSORSHIP.ID,
               @SPONSORSHIPOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        from dbo.REVENUE
        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
        inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
        where REVENUE.ID = @ID
        and SPONSORSHIP.STATUSCODE = 1;

        if @REVENUECONSTITUENTID <> @ORIGINALREVENUECONSTITUENTID
        begin
            -- reverify that financial sponsor is OK w/ this opportunity

            if dbo.UFN_SPONSORSHIP_VALIDOPPORTUNITYFORFINANCIALSPONSOR(@SPONSORSHIPOPPORTUNITYID,@REVENUECONSTITUENTID,@SPONSORSHIPID) = 0
            begin
                raiserror('BBERR_OPPORTUNITYINVALIDFORFINANCIALSPONSOR',13,1)
                return 1
            end

            -- update the recurring gift

            exec dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENT
                @ID,
                @CHANGEAGENTID,
                @REVENUECONSTITUENTID,
                @RESETRECOGNITIONCREDITS,
                @RESETSOLICITORS

            -- update the sponsor constituencies for the financial sponsor

            if @ORIGINALREVENUECONSTITUENTID = @SPONSORSHIPCONSTITUENTID
                -- change from not a gift to a gift

                -- check/remove sponsor constituency on old financial sponsor

                exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @ORIGINALREVENUECONSTITUENTID, @CURRENTDATE, 0, @CHANGEAGENTID, @CURRENTDATE
            else
                -- change from a gift to not a gift, or change of donor on a gift

                -- check/remove donor constituency on old financial sponsor 

                exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @ORIGINALREVENUECONSTITUENTID, @CURRENTDATE, 2, @CHANGEAGENTID, @CURRENTDATE

            if @REVENUECONSTITUENTID = @SPONSORSHIPCONSTITUENTID
            begin
                -- change from a gift to not a gift

                -- check/remove recipient constituency from sponsor, replacing w/ sponsor constituency

                exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @REVENUECONSTITUENTID, @CURRENTDATE, 1, @CHANGEAGENTID, @CURRENTDATE
                exec dbo.USP_SPONSOR_MAKEACTIVE @REVENUECONSTITUENTID, @CURRENTDATE, 0, @CHANGEAGENTID
            end
            else
            begin
                -- change from not a gift to a gift, or change of donor on a gift

                -- add appropriate constituencies to both sponsors

                exec dbo.USP_SPONSOR_MAKEACTIVE @REVENUECONSTITUENTID, @CURRENTDATE, 2, @CHANGEAGENTID
                exec dbo.USP_SPONSOR_MAKEACTIVE @SPONSORSHIPCONSTITUENTID, @CURRENTDATE, 1, @CHANGEAGENTID
            end

            -- add a transaction for the change

            insert into dbo.SPONSORSHIPTRANSACTION
            (
                ID,
                SPONSORSHIPCOMMITMENTID,
                TRANSACTIONSEQUENCE,
                ACTIONCODE,
--                SPONSORSHIPREASONID,

                CONTEXTSPONSORSHIPID,
                TARGETSPONSORSHIPID,
                TRANSACTIONDATE,
                GIFTFINANCIALSPONSORID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                newid(),
                @COMMITMENTID,
                (select max(TRANSACTIONSEQUENCE)+1 from dbo.SPONSORSHIPTRANSACTION where SPONSORSHIPCOMMITMENTID = @COMMITMENTID),
                9,
--                @REASONID,

                @SPONSORSHIPID,
                @SPONSORSHIPID,
                @CURRENTDATE,
                case when @REVENUECONSTITUENTID <> @SPONSORSHIPCONSTITUENTID then @REVENUECONSTITUENTID end,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;