USP_DATAFORMTEMPLATE_EDITLOAD_SOLESPONSORSHIPCHANGETO

The load procedure used by the edit dataform template "Change to 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
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	INOUT
@UPDATERECOGNITIONOPTION	tinyint	INOUT
@SCHEDULESEEDDATE	datetime	INOUT
@ISMEMBERSHIPRECURRING	bit	INOUT
@PREVIOUSSCHEDULESEEDDATE	datetime	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SOLESPONSORSHIPCHANGETO(
    @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,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = 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 @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 @ALLOWSOLESPONSORSHIP bit = 0;

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

  select @ALLOWSOLESPONSORSHIP = 1,
         @SPONSORSHIPOPPORTUNITYID = S.SPONSORSHIPOPPORTUNITYID
  from dbo.SPONSORSHIP S
    inner join dbo.SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
    inner join dbo.SPONSORSHIPOPPORTUNITYLOCK SOL on SOL.ID = SO.ID
    inner join dbo.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 dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(SG.ID,SO.SPONSORSHIPLOCATIONID) = 1
    and  S.ISSOLESPONSORSHIP = 0
    and not exists (select 'X' from sponsorship s2 where s2.sponsorshipopportunityid = SO.id and status in ('Active','Pending') and s2.ID != @SPONSORSHIPID)
    and SO.AVAILABILITYCODE = 0
    -- not in pending transfer state

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

  if @ALLOWSOLESPONSORSHIP = 0
    set @DATALOADED = 0


  return 0;