USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFTSTATUS

The load procedure used by the edit dataform template "Recurring Gift Status Edit 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.
@STATUSCODE	tinyint	INOUT	Status
@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.
@REASONCODEID	uniqueidentifier	INOUT
@INSTALLMENTS	xml	INOUT
@RGFREQUENCYCODE	tinyint	INOUT
@RGENDDATE	date	INOUT
@SKIPPEDINSTALLMENTID	uniqueidentifier	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFTSTATUS
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @STATUSCODE tinyint = null output,
                    @TSLONG bigint = 0 output,
                    --@REASONCODEID is only needed on the save, but is required on editload due to platform constraints.

                    @REASONCODEID uniqueidentifier = null output,
                    @INSTALLMENTS xml = null output,
                    @RGFREQUENCYCODE tinyint = null output,
                    @RGENDDATE date = null output,
                    @SKIPPEDINSTALLMENTID uniqueidentifier = null output
                )

                as

                set nocount on;

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

                select top 1  --ONLY EXPECTING ONE HERE

                    @DATALOADED = 1,
                    @STATUSCODE = REVENUESCHEDULE.STATUSCODE,
                    @TSLONG = REVENUE.TSLONG,
                    @RGENDDATE = REVENUESCHEDULE.ENDDATE,
                    @RGFREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE
                from dbo.REVENUESCHEDULE
                inner join dbo.REVENUE
                    on REVENUESCHEDULE.ID = REVENUE.ID
                where REVENUE.ID = @ID;

                declare @CURRENTDATE date = getdate();

                if @STATUSCODE in(1,2,3)
                begin
                  -- Look for a skipped installment to determine whether:

                  -- a) Status can be set to Held if RG is Terminated or Canceled

                  -- b) Skipped installments will be undone if status is set to Active or Lapsed

                  select top 1 
                      @SKIPPEDINSTALLMENTID = I.ID
                  from dbo.RECURRINGGIFTINSTALLMENT I 
                  inner join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW on IW.RECURRINGGIFTINSTALLMENTID = I.ID
                  inner join dbo.RECURRINGGIFTWRITEOFF W on W.ID = IW.WRITEOFFID
                  where I.REVENUEID = @ID
                  and W.TYPECODE = 1
                  and I.DATE >= cast(@CURRENTDATE as date)
                  order by I.DATE;
                end

                if @STATUSCODE <> 1
                begin
                  -- Look for a skippable installment to determine whether status can be set to Held or not.

                  declare @FIRSTSKIPDATE date;

                  select @FIRSTSKIPDATE = min(i.DATE)
                  from dbo.UFN_QUERY_RECURRINGGIFTACTIVITY(@ID,0,1) i
                  where i.HIDESKIP = 0;

                  if @FIRSTSKIPDATE is not null
                    set @INSTALLMENTS =
                     (
                     select top 2 isnull(ID,'00000000-0000-0000-0000-000000000000') as ID, DATE, 0 WRITEOFFAMOUNT, BALANCE, INSTALLMENTNUMBER NUMBER, ACTIVITY
                     from dbo.UFN_QUERY_RECURRINGGIFTACTIVITY (@ID, 0, 1)
                     where DATE <= @FIRSTSKIPDATE
                     order by SEQ desc
                        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64);
                end;

                return 0;