USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2

The load procedure used by the edit dataform template "Recurring Gift Edit Form 2"

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.
@REVENUEID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@SPLITS	xml	INOUT	Designations
@FREQUENCYCODE	tinyint	INOUT	Frequency
@ENDDATE	datetime	INOUT	Ending on
@STARTDATE	datetime	INOUT	Starting on
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@GIVENANONYMOUSLY	bit	INOUT	Recurring gift is anonymous
@MAILINGID	uniqueidentifier	INOUT	Effort
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@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.
@SINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@FINDERNUMBERVALID	bit	INOUT	FINDERNUMBERVALID
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@SENDREMINDER	bit	INOUT	Send reminders
@NEXTINSTALLMENTID	uniqueidentifier	INOUT
@LASTACTIVITYDATE	date	INOUT
@EDITSTARTDATE	bit	INOUT
@ISSPONSORSHIP	bit	INOUT
@REVENUEDEVELOPMENTFUNCTIONCODEID	uniqueidentifier	INOUT	Revenue function
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@BASEDECIMALDIGITS	tinyint	INOUT	Decimal digits
@BASEROUNDINGTYPECODE	tinyint	INOUT	Rounding type
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	INOUT
@HASRECOGNITIONCREDIT	bit	INOUT
@UPDATERECOGNITIONOPTION	tinyint	INOUT
@SCHEDULESEEDDATE	datetime	INOUT	Next transaction
@ISMEMBERSHIPRECURRING	bit	INOUT
@PREVIOUSSCHEDULESEEDDATE	datetime	INOUT
@STATUSCODE	tinyint	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,    
    @REVENUEID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(700) = null output,
    @DATE datetime = null output,
    @AMOUNT money = null output,
    @SPLITS xml = null output,
    @FREQUENCYCODE tinyint = null output,
    @ENDDATE datetime = null output,
    @STARTDATE datetime = null output,
    @FINDERNUMBER bigint = null output,
    @SOURCECODE nvarchar(50) = null output,
    @APPEALID uniqueidentifier = null output,
    @GIVENANONYMOUSLY bit = null output,
    @MAILINGID uniqueidentifier = null output,
    @CHANNELCODEID uniqueidentifier = null output,
    @DONOTACKNOWLEDGE bit = null output,
    @MAXSOLICITORAMOUNT money = null output,
    @TSLONG bigint = 0 output,
    @SINGLEDESIGNATIONID uniqueidentifier = null output,
    @FINDERNUMBERVALID bit = null output,
    @REFERENCE nvarchar(255) = null output,
    @CATEGORYCODEID uniqueidentifier = null output,
    @SENDREMINDER bit = null output,
    @NEXTINSTALLMENTID uniqueidentifier = null output,
    @LASTACTIVITYDATE date = null output,
    @EDITSTARTDATE bit = null output,
    @ISSPONSORSHIP bit = null output,
    @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @BASEEXCHANGERATEID uniqueidentifier = null output,
    @EXCHANGERATE decimal(20,8) = null output,
    @HADSPOTRATE bit = null output,
    @RATECHANGED bit = null output,
    @BASEDECIMALDIGITS tinyint = null output,
    @BASEROUNDINGTYPECODE tinyint = null output,
    @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null output,
    @HASRECOGNITIONCREDIT bit = null output,
    @UPDATERECOGNITIONOPTION tinyint = null output,
    @SCHEDULESEEDDATE datetime = null output,
    @ISMEMBERSHIPRECURRING bit = null output,
    @PREVIOUSSCHEDULESEEDDATE datetime = null output,
    @STATUSCODE tinyint = null output
)

as

    set nocount on;

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

    select
        @DATALOADED = 1,
        @DATE = cast(REVENUE.DATE as datetime),
        @TSLONG = REVENUE.TSLONG,
        @CONSTITUENTNAME = NF.NAME,
        @CONSTITUENTID = REVENUE.CONSTITUENTID,
        @AMOUNT = REVENUE.TRANSACTIONAMOUNT,
        @FINDERNUMBER = REVENUE_EXT.FINDERNUMBER,
        @SOURCECODE = REVENUE_EXT.SOURCECODE,
        @APPEALID = REVENUE_EXT.APPEALID,
        @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
        @MAILINGID = REVENUE_EXT.MAILINGID,
        @CHANNELCODEID = REVENUE_EXT.CHANNELCODEID,
        @DONOTACKNOWLEDGE = REVENUE_EXT.DONOTACKNOWLEDGE,
        @MAXSOLICITORAMOUNT = coalesce((select max(AMOUNT) from dbo.REVENUESOLICITOR where REVENUESPLITID = REVENUE.ID), 0),
        @REFERENCE = REVENUE_EXT.REFERENCE,
        @CATEGORYCODEID = (select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID where REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1),
        @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID),
        @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
        @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE,
        @HADSPOTRATE = 
            case
                when CURRENCYEXCHANGERATE.TYPECODE = 2
                    then 1
                else 0
            end,
        @RATECHANGED = 0,
        @BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
        @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID)
    from 
        dbo.FINANCIALTRANSACTION REVENUE
        inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
        left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
        inner join dbo.CURRENCY on CURRENCY.ID = V.BASECURRENCYID
        left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
    where 
        REVENUE.ID = @ID and REVENUE.TYPECODE = 2 and REVENUE.DELETEDON is null;

    set @REVENUEID = @ID;

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

    set @EDITSTARTDATE = 1;

    if exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID)
    begin
        set @SCHEDULESEEDDATE = 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 in(0,1,5)
        begin
            select top 1
                @NEXTINSTALLMENTID = ID,
                @SCHEDULESEEDDATE = DATE
            from dbo.RECURRINGGIFTINSTALLMENT
            where REVENUEID = @ID
            and (DATE > @LASTACTIVITYDATE or @LASTACTIVITYDATE is null)
            order by DATE;

          -- if the last installment is partially paid or written-off, and it is today

          -- or in the future, there will be no next installment

          -- In that case, determine what the next installment date would be.

          if @SCHEDULESEEDDATE is null
            set @SCHEDULESEEDDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@ID,@LASTACTIVITYDATE);

          -- But don't go past the end date

          if @SCHEDULESEEDDATE > @ENDDATE
            set @SCHEDULESEEDDATE = null;
        end

        set @PREVIOUSSCHEDULESEEDDATE = @SCHEDULESEEDDATE
    end
    else
        select @LASTACTIVITYDATE = max(SCHEDULEDATE)
        from dbo.RECURRINGGIFTACTIVITY
        where SOURCEREVENUEID = @ID;

    if @SCHEDULESEEDDATE is null and @STATUSCODE not in (0,1,5)
        set @EDITSTARTDATE = 0;

    set @SPLITS = (
        select 
            SPLITS.TRANSACTIONAMOUNT AMOUNT, 
            SPLITS.APPLICATIONCODE, 
            SPLITS.CATEGORYCODEID, 
            SPLITS.DECLINESGIFTAID, 
            SPLITS.DESIGNATIONID, 
            SPLITS.ID, 
            SPLITS.TYPECODE,
            SPLITS.TRANSACTIONCURRENCYID
        from 
            dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
            left join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
        order by 
            DESIGNATION.VANITYNAME
        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
    );

    if exists(select REVENUESPLIT.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
            inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
            where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
            )
        begin
                set @ISMEMBERSHIPRECURRING = 1
        end
        else
        begin
                set @ISMEMBERSHIPRECURRING = 0
        end

    if @FINDERNUMBER = 0
        set @FINDERNUMBER = null;

    if (not @FINDERNUMBER is null)
        set @FINDERNUMBERVALID = 1;
    else
        set @FINDERNUMBERVALID = 0;

    if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM FTLI, dbo.REVENUESPLIT_EXT RSX where FTLI.FINANCIALTRANSACTIONID = @ID and FTLI.ID = RSX.ID AND RSX.TYPECODE = 9)
        set @ISSPONSORSHIP = 1;
    else
        set @ISSPONSORSHIP = 0;

    select @HASRECOGNITIONCREDIT = count(1)
    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
        inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
    set @UPDATERECOGNITIONOPTION = 0;

    return 0;