USP_DATAFORMTEMPLATE_EDITLOAD_SOLESPONSORSHIPREMOVE

The load procedure used by the edit dataform template "Remove Sole Sponsorship 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.
@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.
@REVENUEID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@CONSTITUENTNAME	nvarchar(255)	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	Next transaction
@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
@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
@UPDATERECOGNITIONOPTION	tinyint	INOUT
@SCHEDULESEEDDATE	datetime	INOUT
@ISMEMBERSHIPRECURRING	bit	INOUT
@PREVIOUSSCHEDULESEEDDATE	datetime	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SOLESPONSORSHIPREMOVE(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @REVENUEID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(255) = 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,
    @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,
    @UPDATERECOGNITIONOPTION tinyint = null output,
    @SCHEDULESEEDDATE datetime = null output,
    @ISMEMBERSHIPRECURRING bit = null output,
    @PREVIOUSSCHEDULESEEDDATE datetime = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows

    set @DATALOADED = 0
    set @TSLONG = 0

    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
    declare @HASRECOGNITIONCREDIT bit;

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2  
        @ID,
        @DATALOADED output,
        @REVENUEID output,
        @CONSTITUENTID output,
        @CONSTITUENTNAME output,
        @DATE output,
        @AMOUNT output,
        @SPLITS output,
        @FREQUENCYCODE output,
        @ENDDATE output,
        @STARTDATE output,
        @FINDERNUMBER output,
        @SOURCECODE output,
        @APPEALID output,
        @GIVENANONYMOUSLY output,
        @MAILINGID output,
        @CHANNELCODEID output,
        @DONOTACKNOWLEDGE output,
        @MAXSOLICITORAMOUNT output,
        @TSLONG output,
        @SINGLEDESIGNATIONID output,
        @FINDERNUMBERVALID output,
        @REFERENCE output,
        @CATEGORYCODEID output,
        @SENDREMINDER output,
        @NEXTINSTALLMENTID output,
        @LASTACTIVITYDATE output,
        @EDITSTARTDATE output,
        @ISSPONSORSHIP output,
        @REVENUEDEVELOPMENTFUNCTIONCODEID output,
        @BASECURRENCYID output,
        @TRANSACTIONCURRENCYID output,
        @BASEEXCHANGERATEID output,
        @EXCHANGERATE output,
        @HADSPOTRATE output,
        @RATECHANGED output,
        @BASEDECIMALDIGITS output,
        @BASEROUNDINGTYPECODE output,
        @TRANSACTIONCURRENCYDESCRIPTION output,
        @SPONSORSHIPOPPORTUNITYID  output,
        @HASRECOGNITIONCREDIT  output,
        @UPDATERECOGNITIONOPTION  output,
        @SCHEDULESEEDDATE   output,
        @ISMEMBERSHIPRECURRING  output,
        @PREVIOUSSCHEDULESEEDDATE output

-- See if anything has changed since we loaded the sponsorship page originally

    declare @SPONSORSHIPID uniqueidentifier;
    declare @ALLOWREMOVESOLESPONSORSHIP bit = 0

    set @SPONSORSHIPID = dbo.UFN_SPONSORSHIP_ID_FROM_REVENUEID(@ID);

    select @ALLOWREMOVESOLESPONSORSHIP = 1
    from dbo.SPONSORSHIP S
        inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
        inner join SPONSORSHIPOPPORTUNITYGROUP SG on SG.ID = SO.SPONSORSHIPOPPORTUNITYGROUPID
        inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = S.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = S.SPONSORSHIPCOMMITMENTID and S.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
    where 
        S.ID = @SPONSORSHIPID
        and  S.ISSOLESPONSORSHIP = 1
        -- not in pending transfer state

        and S.STATUSCODE = 1
        and LASTTRANSACTION.ACTIONCODE <> 6


    if @ALLOWREMOVESOLESPONSORSHIP = 0
      set @DATALOADED = 0        

    return 0;