USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPBATCH

The load procedure used by the edit dataform template "Sponsorship Batch 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.
@SEQUENCE	int	INOUT	Sequence
@CONSTITUENTID	uniqueidentifier	INOUT	Corresponding sponsor
@DONORID	uniqueidentifier	INOUT	Financial sponsor
@PLANNEDENDDATE	datetime	INOUT	Expiration date
@SPONSORSHIPPROGRAMID	uniqueidentifier	INOUT	Sponsorship program
@SPONSORSHIPLOCATIONID	uniqueidentifier	INOUT	Location
@GENDERCODE	int	INOUT	Gender
@SPROPPAGERANGEID	uniqueidentifier	INOUT	Age range
@ISHIVPOSITIVECODE	int	INOUT	HIV positive
@HASCONDITIONCODE	int	INOUT	Disability/Illness
@ISORPHANEDCODE	int	INOUT	Orphaned
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	INOUT	Category
@SPONSORSHIPOPPORTUNITYIDCHILD	uniqueidentifier	INOUT	Child
@SPONSORSHIPOPPORTUNITYIDPROJECT	uniqueidentifier	INOUT	Project
@SPONSORSHIPOPPORTUNITYTYPECODE	tinyint	INOUT	Sponsorship Opportunity Type
@ISSOLESPONSORSHIP	bit	INOUT	Sole sponsorship
@STARTDATE	datetime	INOUT	Start Date
@ENDDATE	datetime	INOUT	End Date
@AMOUNT	money	INOUT	Amount
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@FREQUENCYCODE	tinyint	INOUT	Frequency
@AUTOPAY	bit	INOUT	Pay installments automatically
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference no.
@CARDHOLDERNAME	nvarchar(255)	INOUT	Name on card
@CREDITCARDNUMBER	nvarchar(20)	INOUT	Card number
@CREDITTYPECODEID	uniqueidentifier	INOUT	Card type
@EXPIRESON	UDT_FUZZYDATE	INOUT	Expires on
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT	Account
@REVENUESCHEDULEENDDATE	datetime	INOUT	Payment end date
@REVENUESCHEDULESTARTDATE	datetime	INOUT	Payment start date
@CREDITCARDTOKEN	uniqueidentifier	INOUT	Credit card token
@ISTRANSIENTCARD	bit	INOUT	Is transient credit card
@RESERVATIONKEYID	uniqueidentifier	INOUT	Reservation key
@RESERVEDOPPORTUNITYID	uniqueidentifier	INOUT	Reserved child
@SENDREMINDER	bit	INOUT	Send reminders
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@MAILINGID	uniqueidentifier	INOUT	Effort
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@REFERENCE	nvarchar(255)	INOUT	Reference
@REVENUECATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@ISGIFTSPONSORSHIP	bit	INOUT	Is gift sponsorship
@EXPIRATIONREASONID	uniqueidentifier	INOUT	Expiration reason
@BID	uniqueidentifier	INOUT	Batch helper id
@FINANCIALSPONSORLOOKUPID	uniqueidentifier	INOUT	Financial sponsor ID
@CORRESPONDINGSPONSORLOOKUPID	uniqueidentifier	INOUT	Corresponding sponsor ID
@PARTIALCREDITCARDNUMBER	nvarchar(4)	INOUT	Partial card number
@NEWCONSTITUENT	xml	INOUT	New corresponding sponsor
@NEWDONORCONSTITUENT	xml	INOUT	New financial sponsor
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Base exchange rate
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@BASEAMOUNT	money	INOUT	Base amount
@COMMITMENTID	nvarchar(100)	INOUT	Commitment Id
@SEPAMANDATEID	uniqueidentifier	INOUT
@ADDSEPAMANDATE	bit	INOUT
@SEPAMANDATECUSTOMIDENTIFIER	nvarchar(35)	INOUT
@SEPAMANDATESIGNATUREDATE	date	INOUT
@SEPAMANDATETYPECODE	tinyint	INOUT
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT
@IMPORT	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPBATCH
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @SEQUENCE int = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @DONORID uniqueidentifier = null output,
  @PLANNEDENDDATE datetime=null output,
  @SPONSORSHIPPROGRAMID uniqueidentifier = null output,
  @SPONSORSHIPLOCATIONID uniqueidentifier = null output,
  @GENDERCODE int = null output,
  @SPROPPAGERANGEID uniqueidentifier = null output,
  @ISHIVPOSITIVECODE int = null output,
  @HASCONDITIONCODE int = null output,
  @ISORPHANEDCODE int = null output,
  @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null output,
  @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier = null output,
  @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier = null output,
  @SPONSORSHIPOPPORTUNITYTYPECODE tinyint = null output,
  @ISSOLESPONSORSHIP bit = null output,
  @STARTDATE datetime = null output,
  @ENDDATE datetime = null output,
  @AMOUNT money = null output,
  @PAYMENTMETHODCODE tinyint = null output,
  @FREQUENCYCODE tinyint = null output,
  @AUTOPAY bit = null output,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
  @REFERENCENUMBER nvarchar(20) = null output,
  @CARDHOLDERNAME nvarchar(255) = null output,
  @CREDITCARDNUMBER nvarchar(20) = null output,
  @CREDITTYPECODEID uniqueidentifier = null output,
  @EXPIRESON dbo.UDT_FUZZYDATE = null output,
  @CONSTITUENTACCOUNTID uniqueidentifier = null output,
  @REVENUESCHEDULEENDDATE datetime = null output,
  @REVENUESCHEDULESTARTDATE datetime = null output, 
  @CREDITCARDTOKEN uniqueidentifier = null output,
  @ISTRANSIENTCARD bit = null output, -- Isn't set since it's write-only

  @RESERVATIONKEYID uniqueidentifier = null output,
  @RESERVEDOPPORTUNITYID uniqueidentifier = null output,
  @SENDREMINDER bit = null output,
  @FINDERNUMBER bigint = null output,
  @SOURCECODE nvarchar(50) = null output,
  @APPEALID uniqueidentifier = null output,
  @MAILINGID uniqueidentifier = null output,
  @CHANNELCODEID uniqueidentifier = null output,
  @REFERENCE nvarchar(255) = null output,
  @REVENUECATEGORYCODEID uniqueidentifier = null output,
  @ISGIFTSPONSORSHIP bit = null output,
  @EXPIRATIONREASONID uniqueidentifier = null output,
  @BID uniqueidentifier = null output,
  @FINANCIALSPONSORLOOKUPID uniqueidentifier = null output,
  @CORRESPONDINGSPONSORLOOKUPID  uniqueidentifier = null output,
  @PARTIALCREDITCARDNUMBER nvarchar(4) = null output,
  @NEWCONSTITUENT xml = null output,
  @NEWDONORCONSTITUENT xml = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20,8) = null output,
  @BASEAMOUNT money = null output,
  @COMMITMENTID nvarchar(100) = null output,
  @SEPAMANDATEID uniqueidentifier = null output,
  @ADDSEPAMANDATE bit = null output,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = null output,
  @SEPAMANDATESIGNATUREDATE date = null output,
  @SEPAMANDATETYPECODE tinyint = null output,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
  @IMPORT bit = null output
)
as
  set nocount on;

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

  set @DATALOADED = 0
  set @TSLONG = 0

  -- populate the output parameters, which correspond to fields on the form.  Note that

  -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

  -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency

  -- can be considered.

  select
    @DATALOADED = 1,
    @TSLONG = BATCHSPONSORSHIP.TSLONG,
    @SEQUENCE = SEQUENCE,
    @CONSTITUENTID = CONSTITUENTID,
    @DONORID = DONORID,
    @PLANNEDENDDATE = PLANNEDENDDATE,
    @SPONSORSHIPPROGRAMID =  SPONSORSHIPPROGRAMID,
    @STARTDATE= STARTDATE,
    @ENDDATE = ENDDATE,
    @SPONSORSHIPLOCATIONID = SPONSORSHIPLOCATIONID,
    @SPROPPAGERANGEID = SPONSORSHIPOPPORTUNITYAGERANGEID,
    @GENDERCODE = CHILDGENDERCODE,
    @ISHIVPOSITIVECODE = ISHIVPOSITIVECODE,
    @ISORPHANEDCODE = ISORPHANEDCODE,
    @HASCONDITIONCODE = HASCONDITIONCODE,
    @SPROPPPROJECTCATEGORYCODEID = SPROPPPROJECTCATEGORYCODEID,
    @ISSOLESPONSORSHIP= ISSOLESPONSORSHIP,
    @AMOUNT = AMOUNT,
    @PAYMENTMETHODCODE= PAYMENTMETHODCODE,
    @FREQUENCYCODE = FREQUENCYCODE,
    @AUTOPAY=AUTOPAY,
    @SPONSORSHIPOPPORTUNITYTYPECODE= SPONSORSHIPOPPORTUNITYTYPECODE,
    @REFERENCEDATE = BATCHSPONSORSHIP.REFERENCEDATE,
    @REFERENCENUMBER = BATCHSPONSORSHIP.REFERENCENUMBER,
    @CARDHOLDERNAME = case when BATCHSPONSORSHIP.PAYMENTMETHODCODE = 98 then BATCHSPONSORSHIP.CARDHOLDERNAME else CREDITCARD.CARDHOLDERNAME end,
    @CREDITCARDNUMBER = CREDITCARD.CREDITCARDPARTIALNUMBER,
    @PARTIALCREDITCARDNUMBER = BATCHSPONSORSHIP.CREDITCARDPARTIALNUMBER,
    @CREDITCARDTOKEN = CREDITCARD.CREDITCARDTOKEN,
    @CREDITTYPECODEID = case when BATCHSPONSORSHIP.PAYMENTMETHODCODE = 98 then BATCHSPONSORSHIP.CREDITTYPECODEID else CREDITCARD.CREDITTYPECODEID end,
    @EXPIRESON = case when BATCHSPONSORSHIP.PAYMENTMETHODCODE = 98 then BATCHSPONSORSHIP.EXPIRESON else CREDITCARD.EXPIRESON end,
    @RESERVATIONKEYID = RESERVATIONKEYID,
    @RESERVEDOPPORTUNITYID = RESERVEDOPPORTUNITYID,
    @SENDREMINDER = SENDREMINDER,
    @FINDERNUMBER = FINDERNUMBER,
    @SOURCECODE = SOURCECODE,
    @APPEALID  = APPEALID,
    @MAILINGID = MAILINGID,
    @CHANNELCODEID = CHANNELCODEID,
    @REFERENCE = REFERENCE,
    @REVENUECATEGORYCODEID = REVENUECATEGORYID,
    @ISGIFTSPONSORSHIP = ISGIFTSPONSORSHIP,
    @EXPIRATIONREASONID = EXPIRATIONREASONID,
    @REVENUESCHEDULEENDDATE = REVENUESCHEDULEENDDATE,
    @REVENUESCHEDULESTARTDATE = REVENUESCHEDULESTARTDATE,
    @BID = BATCHSPONSORSHIP.ID,
    @FINANCIALSPONSORLOOKUPID = BATCHSPONSORSHIP.DONORID,
    @CORRESPONDINGSPONSORLOOKUPID = BATCHSPONSORSHIP.CONSTITUENTID,
    @TRANSACTIONCURRENCYID = BATCHSPONSORSHIP.TRANSACTIONCURRENCYID,
    @BASECURRENCYID = BATCHSPONSORSHIP.BASECURRENCYID,
    @BASEEXCHANGERATEID = BATCHSPONSORSHIP.BASEEXCHANGERATEID,
    @EXCHANGERATE = BATCHSPONSORSHIP.EXCHANGERATE,
    @COMMITMENTID = BATCHSPONSORSHIP.COMMITMENTID,
    @CONSTITUENTACCOUNTID = BATCHSPONSORSHIP.CONSTITUENTACCOUNTID,
    @SEPAMANDATEID = BATCHSPONSORSHIP.SEPAMANDATEID,
    @OTHERPAYMENTMETHODCODEID = BATCHSPONSORSHIP.OTHERPAYMENTMETHODCODEID,
    @IMPORT = 0
  from dbo.BATCHSPONSORSHIP
  left join dbo.CREDITCARD ON BATCHSPONSORSHIP.CREDITCARDID = CREDITCARD.ID
  where BATCHSPONSORSHIP.ID = @ID

  if @FINDERNUMBER = 0
    set @FINDERNUMBER = null;

  --Set default values for SEPA mandate fields only used in import

  set @ADDSEPAMANDATE = 0;
  set @SEPAMANDATECUSTOMIDENTIFIER = '';
  set @SEPAMANDATESIGNATUREDATE = null;
  set @SEPAMANDATETYPECODE = 0;

  if @SPONSORSHIPOPPORTUNITYTYPECODE= 1
  begin
    if @RESERVEDOPPORTUNITYID is null
      select @SPONSORSHIPOPPORTUNITYIDCHILD= SPONSORSHIPOPPORTUNITYID from dbo.BATCHSPONSORSHIP where @ID=ID
  end
  else
    select @SPONSORSHIPOPPORTUNITYIDPROJECT= SPONSORSHIPOPPORTUNITYID from dbo.BATCHSPONSORSHIP where @ID=ID

  return 0;