USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPSTARTDATE

The save procedure used by the edit dataform template "Sponsorship Start Date 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.
@STARTDATE	date	IN	Start date

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPSTARTDATE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @STARTDATE date
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @REVENUEID uniqueidentifier;
    declare @REVENUESTARTDATE datetime;
    declare @SPONSORID uniqueidentifier;
    declare @GIFTFINANCIALSPONSORID uniqueidentifier;
    declare @ORIGINALSTARTDATE date;

    select 
        @REVENUEID = REVENUESPLIT.REVENUEID,
        @REVENUESTARTDATE = REVENUESCHEDULE.STARTDATE,
        @SPONSORID = SPONSORSHIP.CONSTITUENTID,
        @GIFTFINANCIALSPONSORID = ADDTRANSACTION.GIFTFINANCIALSPONSORID,
        @ORIGINALSTARTDATE = SPONSORSHIP.STARTDATE
    from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPTRANSACTION ADDTRANSACTION on ADDTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and ADDTRANSACTION.TRANSACTIONSEQUENCE = 1
    left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUESPLIT.REVENUEID
    where SPONSORSHIP.ID = @ID;

    if @STARTDATE <> @ORIGINALSTARTDATE
    begin
        if @STARTDATE <= @REVENUESTARTDATE or @REVENUEID is null
        begin
            update dbo.SPONSORSHIP set
                STARTDATE = @STARTDATE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID

            update dbo.SPONSORSHIPTRANSACTION
            set TRANSACTIONDATE = @STARTDATE
            where TARGETSPONSORSHIPID = @ID
            and ACTIONCODE = 0;

            if @REVENUEID is not null 
            begin
                begin try
                    update dbo.REVENUE set
                        DATE = @STARTDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @REVENUEID
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR
                    return 1
                end catch            
            end

            -- update constituencies relevant to the start of the commitment

            if @GIFTFINANCIALSPONSORID is null
                exec dbo.USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES @SPONSORID, 0, @CHANGEAGENTID
            else
            begin
                exec dbo.USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES @SPONSORID, 1, @CHANGEAGENTID
                exec dbo.USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES @GIFTFINANCIALSPONSORID, 2, @CHANGEAGENTID
            end
        end
        else
        begin
            raiserror('BBERR_DATETOLATE',13,1);
            return 1;
        end
    end

    return 0;