USP_EDITLOAD_RECURRINGGIFTDETAILS

The load procedure used by the edit dataform template "recurring Gift Details Edit Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@AMOUNT	money	INOUT	amount
@SPLITS	xml	INOUT	Designations
@FREQUENCYCODE	tinyint	INOUT	Frequency
@ENDDATE	datetime	INOUT	endDate
@STARTDATE	datetime	INOUT	startDate
@NEXTINSTALLMENTID	uniqueidentifier	INOUT	next installment id
@USEEXISTINGCURRENCY	bit	INOUT
@INSTALLMENTBEGINDATE	datetime	INOUT

Definition

Copy



            CREATE PROCEDURE USP_EDITLOAD_RECURRINGGIFTDETAILS 
              @ID uniqueidentifier,
              @DATALOADED bit = 0 output,
                    @TSLONG bigint = 0 output,
                        @AMOUNT money = null output,
                        @SPLITS xml = null output,
                        @FREQUENCYCODE tinyint = null output,
                        @ENDDATE datetime = null output,
                        @STARTDATE datetime = null output,
                        @NEXTINSTALLMENTID uniqueidentifier = null output,
              @USEEXISTINGCURRENCY bit = null output,
              @INSTALLMENTBEGINDATE datetime = null output
          AS
          BEGIN
           set nocount on;

                set @DATALOADED = 0;
                set @TSLONG = 0;                

                select
                    @DATALOADED = 1,
                    @TSLONG = REVENUE.TSLONG,
                    @AMOUNT = REVENUE.AMOUNT
                from dbo.REVENUE
                where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 2;

                declare @STATUSCODE tinyint;

                select top 1
                    @FREQUENCYCODE = FREQUENCYCODE,
                    @ENDDATE = ENDDATE,
                    @STARTDATE = NEXTTRANSACTIONDATE,
          @INSTALLMENTBEGINDATE = STARTDATE,
                    @STATUSCODE = STATUSCODE
                from dbo.REVENUESCHEDULE
                where ID = @ID;

                declare @LASTACTIVITYDATE date

                if exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID)
                begin
                    declare @SAVESTARTDATE date
                    set @SAVESTARTDATE = @STARTDATE
                    set @STARTDATE = null;

                    select @LASTACTIVITYDATE = max(ACTIVITYINSTALLMENT.DATE)
                    from dbo.RECURRINGGIFTINSTALLMENT ACTIVITYINSTALLMENT
                    left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = ACTIVITYINSTALLMENT.ID
                    left join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID = ACTIVITYINSTALLMENT.ID
                    where ACTIVITYINSTALLMENT.REVENUEID = @ID
                    and (RECURRINGGIFTINSTALLMENTPAYMENT.ID is not null or
                         RECURRINGGIFTINSTALLMENTWRITEOFF.ID is not null)

                    -- first installment w/ no activity, and after which there is no activity
                    if @STATUSCODE = 0
                        select top 1
                            @NEXTINSTALLMENTID = ID,
                            @STARTDATE = DATE
                        from dbo.RECURRINGGIFTINSTALLMENT
                        where REVENUEID = @ID
                        and (DATE > @LASTACTIVITYDATE or @LASTACTIVITYDATE is null)
                        order by DATE;

                    -- startdate could come out of this null, if the last installment is
                    -- partially paid or the RG is inactive;
                    -- in this case we should not allow edit of the next transaction date
                    if @STARTDATE is null
                    begin
                        set @STARTDATE = @SAVESTARTDATE    
                    end
                end

                set @SPLITS = dbo.[UFN_REVENUE_GETSPLITS_TOITEMLISTXML](@ID);


                return 0;
          END