USP_DATAFORMTEMPLATE_VIEW_PAYMENTRECURRINGGIFT

The load procedure used by the view dataform template "Payment: Recurring Gift View 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.
@TRANSACTIONTYPE	nvarchar(20)	INOUT	Type
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent
@DESCRIPTION	nvarchar(700)	INOUT	Description
@GIFTDATE	datetime	INOUT	Date
@NEXTINSTALLMENTDATE	datetime	INOUT	Next Installment Due
@NEXTINSTALLMENTDUE	money	INOUT	Next Installment Amount
@AMOUNT	money	INOUT	Amount
@TOTALPAID	money	INOUT	Total paid
@FREQUENCY	nvarchar(100)	INOUT	Frequency
@DESIGNATIONS	nvarchar(3000)	INOUT	Designations
@ANONYMOUS	bit	INOUT	ANONYMOUS
@REVENUETYPE	nvarchar(20)	INOUT	Revenue type
@SPONSORNAME	nvarchar(500)	INOUT	Sponsor name
@OPPORTUNITYNAME	nvarchar(500)	INOUT	Opportunity
@PASTDUE	money	INOUT	Past due
@DESIGNATIONID	uniqueidentifier	INOUT	Designation
@ADDITIONALAMOUNT	money	INOUT	Additional amount
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@ACTIONABOVEAMOUNT	money	INOUT
@OVERPAYMENTCODE	tinyint	INOUT
@ACTIONBELOWAMOUNTS	xml	INOUT
@UNDERPAYMENTCODE	tinyint	INOUT
@SPLITS	xml	INOUT

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PAYMENTRECURRINGGIFT
        (
          @ID uniqueidentifier,    
          @DATALOADED bit = 0 output,
          @TRANSACTIONTYPE nvarchar(20) = null output,
          @CONSTITUENTNAME nvarchar(700) = null output,
          @DESCRIPTION nvarchar(700) = null output,
          @GIFTDATE datetime = null output,
          @NEXTINSTALLMENTDATE datetime = null output,
          @NEXTINSTALLMENTDUE money = null output,
          @AMOUNT money = null output,
          @TOTALPAID money = null output,
          @FREQUENCY nvarchar(100) = null output,
          @DESIGNATIONS nvarchar(3000) = null output,
          @ANONYMOUS bit = null output,
          @REVENUETYPE nvarchar(20) = null output,
          @SPONSORNAME nvarchar(500) = null output,
          @OPPORTUNITYNAME nvarchar(500) = null output,
          @PASTDUE money = null output,
          @DESIGNATIONID uniqueidentifier = null output,
          @ADDITIONALAMOUNT money = null output,
          @TRANSACTIONCURRENCYID uniqueidentifier = null output,
          @ACTIONABOVEAMOUNT money = null output,
          @OVERPAYMENTCODE tinyint = null output,
          @ACTIONBELOWAMOUNTS xml = null output,
          @UNDERPAYMENTCODE tinyint = null output,
          @SPLITS xml = null output
        )
        as
        set nocount on;

        declare @LASTPAYMENTID uniqueidentifier;

        select top 1 @LASTPAYMENTID = ID 
        from RECURRINGGIFTACTIVITY
        where SOURCEREVENUEID = @ID and TYPECODE = 0
        order by SCHEDULEDATE desc;

        set @DATALOADED = 0;

        select
          @DATALOADED = 1,
          @TRANSACTIONTYPE = REVENUE.TRANSACTIONTYPE,
          @CONSTITUENTNAME = NF.NAME,
          @GIFTDATE = REVENUE.DATE,
          @NEXTINSTALLMENTDATE = NEXTINSTALLMENT.DATE,
          @NEXTINSTALLMENTDUE = NEXTINSTALLMENT.BALANCE,
          @AMOUNT = REVENUE.TRANSACTIONAMOUNT,
          @FREQUENCY = REVENUESCHEDULE.FREQUENCY,
          @DESIGNATIONS = dbo.UFN_REVENUE_DESIGNATIONLIST(REVENUE.ID),
          @ANONYMOUS = REVENUE.GIVENANONYMOUSLY,
          @PASTDUE = dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_2(@ID, null, 1),
          @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
        from dbo.REVENUE
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null) NEXTINSTALLMENT
        where REVENUE.ID = @ID;

        set @SPLITS = (
        select 
            SPLITS.ID, 
            SPLITS.TRANSACTIONAMOUNT AMOUNT, 
            SPLITS.DESIGNATIONID, 
            DESIGNATION.USERID DESIGNATIONDESCRIPTION, 
            SPLITS.TRANSACTIONCURRENCYID
        from 
            dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
            left join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
        );

        select @TOTALPAID = coalesce(sum(RECURRINGGIFTACTIVITY.AMOUNT), 0.00)
        from REVENUE REVLAST
        inner join dbo.REVENUESPLIT REVSPLITLAST on REVSPLITLAST.REVENUEID = REVLAST.ID
        inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVSPLITLAST.ID
        where RECURRINGGIFTACTIVITY.SOURCEREVENUEID = @ID;

        set @DESCRIPTION = @CONSTITUENTNAME;


        -- information for handling donations and UI messages

        declare @APPLYTOPASTINSTALLMENTS bit;
        declare @PASTBALANCEUNDERPAYMENTCODE tinyint;

        select @ACTIONABOVEAMOUNT = ACTIONABOVEAMOUNT,
               @OVERPAYMENTCODE = OVERPAYMENTCODE,
               @ACTIONBELOWAMOUNTS = ACTIONBELOWAMOUNTS,
               @UNDERPAYMENTCODE = INSTALLMENTUNDERPAYMENTCODE,
   @APPLYTOPASTINSTALLMENTS = APPLYTOPASTINSTALLMENTS,
               @PASTBALANCEUNDERPAYMENTCODE = PASTBALANCEUNDERPAYMENTCODE
        from dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(@ID,@NEXTINSTALLMENTDUE,@PASTDUE,@NEXTINSTALLMENTDATE,getdate(),null);

        -- fix @PASTDUE - don't show if:

        if @PASTDUE = @NEXTINSTALLMENTDUE or       -- it's the same as the next installment amount

           (@APPLYTOPASTINSTALLMENTS = 0 and
            (@PASTBALANCEUNDERPAYMENTCODE = 0 or   -- applying that amount wouldn't result in paying off the past balance

             @OVERPAYMENTCODE = 2))                -- that amount would not all be applied to the RG

          set @PASTDUE = 0;

        -- sponsorship

        select 
          @REVENUETYPE = SP.TYPE,
          @SPONSORNAME = NF.NAME,
          @OPPORTUNITYNAME = coalesce((select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
                        (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)),
          @DESIGNATIONID = SP.DESIGNATIONID
        from REVENUESPLIT SP
        inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(S.CONSTITUENTID) NF
        inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
        where SP.REVENUEID = @ID
          and S.ISMOSTRECENTFORCOMMITMENT = 1;

        -- sponsorship recurring additional gift

        select 
          @REVENUETYPE = (select top 1 TYPECODE from dbo.REVENUESPLIT RS where RS.REVENUEID = SPRAG.REVENUEID),
          @OPPORTUNITYNAME = coalesce((select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
                        (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID))
        from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SPRAG
        inner join SPONSORSHIP S on S.ID = SPRAG.SPONSORSHIPID
        inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
        where SPRAG.REVENUEID = @ID
        and SPRAG.STATUSCODE in (0, 1, 5)
        and S.STATUSCODE = 1;

        return 0;