USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPREASSIGN

The load procedure used by the edit dataform template "Reassign 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.
@FORMTITLE	nvarchar(100)	INOUT
@SPONSORSHIPOPPORTUNITYIDCHILD	uniqueidentifier	INOUT	Name
@SPONSORSHIPOPPORTUNITYIDPROJECT	uniqueidentifier	INOUT	Project
@SPONSORSHIPPROGRAMID	uniqueidentifier	INOUT	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
@ISSOLESPONSORSHIP	bit	INOUT	Sole sponsorship
@AMOUNT	money	INOUT	Amount
@FREQUENCYCODE	tinyint	INOUT	Frequency
@STARTDATE	date	INOUT	Start date
@REVENUESCHEDULESTARTDATE	datetime	INOUT	Starting on
@REVENUECONSTITUENTID	uniqueidentifier	INOUT	Financial sponsor
@SPONSORSHIPCONSTITUENTID	uniqueidentifier	INOUT	Corresponding sponsor
@SPONSORSHIPREASONID	uniqueidentifier	INOUT	Reason
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@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	Ending on
@AUTOPAY	bit	INOUT	Pay installments automatically by:
@CREDITCARDTOKEN	uniqueidentifier	INOUT
@GIFTRECIPIENT	bit	INOUT	Gift
@PLANNEDENDDATE	date	INOUT	Expiration date
@RESERVEDOPPORTUNITYIDCHILD	uniqueidentifier	INOUT	Child
@RESERVATIONKEY	uniqueidentifier	INOUT	Reservation key
@MATCHEDOPPORTUNITYID	uniqueidentifier	INOUT
@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
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@CARRYFORWARDPASTDUE	bit	INOUT	Carry forward
@CARRYFORWARDPASTDUEAMOUNT	money	INOUT
@EXPIRATIONREASONID	uniqueidentifier	INOUT	Reason
@BASECURRENCYID	uniqueidentifier	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@BASECURRENCYDECIMALDIGITS	tinyint	INOUT	Base currency decimal digits
@BASECURRENCYROUNDINGTYPECODE	tinyint	INOUT	Base currency rounding type code
@BASEAMOUNT	money	INOUT	Base amount
@ORIGINALTRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@DONOTACKNOWLEDGE	bit	INOUT
@SEPAMANDATEID	uniqueidentifier	INOUT
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPREASSIGN(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @FORMTITLE nvarchar(100) = null output,
    @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier = null output,
    @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier = 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,
    @ISSOLESPONSORSHIP bit = null output,
    @AMOUNT money = null output,
    @FREQUENCYCODE tinyint = null output,
    @STARTDATE date = null output,
    @REVENUESCHEDULESTARTDATE datetime = null output,
    @REVENUECONSTITUENTID uniqueidentifier = null output,
    @SPONSORSHIPCONSTITUENTID uniqueidentifier = null output,    
    @SPONSORSHIPREASONID uniqueidentifier = null output,
    @PAYMENTMETHODCODE tinyint = 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,
    @AUTOPAY bit = null output,
    @CREDITCARDTOKEN uniqueidentifier = null output,
    @GIFTRECIPIENT bit = null output,
    @PLANNEDENDDATE date = null output,
    @RESERVEDOPPORTUNITYIDCHILD uniqueidentifier = null output,
    @RESERVATIONKEY uniqueidentifier = null output,
    @MATCHEDOPPORTUNITYID 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,
    @CATEGORYCODEID uniqueidentifier = null output,
    @CARRYFORWARDPASTDUE bit = null output,
    @CARRYFORWARDPASTDUEAMOUNT money = null output,
    @EXPIRATIONREASONID uniqueidentifier = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @BASEEXCHANGERATEID uniqueidentifier = null output,
    @EXCHANGERATE decimal(20,8) = null output,
    @BASECURRENCYDECIMALDIGITS tinyint = null output,
    @BASECURRENCYROUNDINGTYPECODE tinyint = null output,
    @BASEAMOUNT money = null output,
    @ORIGINALTRANSACTIONCURRENCYID uniqueidentifier = null output,
    @DONOTACKNOWLEDGE bit = null output,
    @SEPAMANDATEID uniqueidentifier = null output,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output
)
as
    set nocount on;

    declare @CURRENTDATEEARLIESTTIME date;
    set @CURRENTDATEEARLIESTTIME = getdate();

    set @FORMTITLE = 'Reassign a sponsorship';
    set @CARRYFORWARDPASTDUE = 0;

    declare @REVENUEID uniqueidentifier;

    select @DATALOADED = 1,
         @TSLONG = SPONSORSHIP.TSLONG,
         @SPONSORSHIPOPPORTUNITYIDCHILD = case when SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = 1 then SPONSORSHIP.SPONSORSHIPOPPORTUNITYID end,
         @SPONSORSHIPOPPORTUNITYIDPROJECT = case when SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = 2 then SPONSORSHIP.SPONSORSHIPOPPORTUNITYID end,
         @SPONSORSHIPPROGRAMID = SPONSORSHIP.SPONSORSHIPPROGRAMID,
         @STARTDATE = SPONSORSHIP.STARTDATE,
         @SPONSORSHIPLOCATIONID = SPONSORSHIP.SPONSORSHIPLOCATIONID,
         @GENDERCODE = SPONSORSHIP.CHILDGENDERCODE,
         @SPROPPAGERANGEID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID,
         @ISHIVPOSITIVECODE = SPONSORSHIP.ISHIVPOSITIVECODE,
         @HASCONDITIONCODE = SPONSORSHIP.HASCONDITIONCODE,
         @ISORPHANEDCODE = SPONSORSHIP.ISORPHANEDCODE,
         @ISSOLESPONSORSHIP = SPONSORSHIP.ISSOLESPONSORSHIP,
         @SPONSORSHIPCONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
         @PLANNEDENDDATE = SPONSORSHIP.PLANNEDENDDATE,
         @EXPIRATIONREASONID = SPONSORSHIP.EXPIRATIONREASONID,
           @REVENUECONSTITUENTID = isnull(REVENUE.CONSTITUENTID,SPONSORSHIP.CONSTITUENTID),
         @REVENUEID = REVENUESPLIT.REVENUEID,
         @CARRYFORWARDPASTDUEAMOUNT = dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_2(REVENUE.ID,null,0),
         @BASEAMOUNT = REVENUE.AMOUNT
    from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
    left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left outer join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    where SPONSORSHIP.ID = @ID
    and SPONSORSHIP.STATUSCODE = 1
    and not exists(select 'x'
                 from dbo.SPONSORSHIP PENDING
                 where PENDING.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID
                 and PENDING.STATUSCODE = 0);

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2
        @REVENUEID,
        @AMOUNT = @AMOUNT output,
        @FREQUENCYCODE = @FREQUENCYCODE output,
        @FINDERNUMBER = @FINDERNUMBER output,
        @SOURCECODE = @SOURCECODE output,
        @APPEALID = @APPEALID output,
        @MAILINGID = @MAILINGID output,
        @CHANNELCODEID = @CHANNELCODEID output,
        @REFERENCE = @REFERENCE output,
        @CATEGORYCODEID = @CATEGORYCODEID output,
        @SENDREMINDER = @SENDREMINDER output,
        @BASECURRENCYID = @BASECURRENCYID output,
        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID output,
        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID output,
        @EXCHANGERATE = @EXCHANGERATE output,
        @BASEDECIMALDIGITS = @BASECURRENCYDECIMALDIGITS output,
        @BASEROUNDINGTYPECODE = @BASECURRENCYROUNDINGTYPECODE output,
        @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE output

  set @ORIGINALTRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFTPAYMENTDETAILS_3
        @ID = @REVENUEID,
        @PAYMENTMETHODCODE = @PAYMENTMETHODCODE output

    if @STARTDATE < @CURRENTDATEEARLIESTTIME
        set @STARTDATE = @CURRENTDATEEARLIESTTIME

    set @REVENUESCHEDULESTARTDATE = @STARTDATE

    if @FREQUENCYCODE is null
        set @FREQUENCYCODE = 3

    if @SPONSORSHIPCONSTITUENTID = isnull(@REVENUECONSTITUENTID,@SPONSORSHIPCONSTITUENTID)
    begin
        set @GIFTRECIPIENT = 0;
        set @REVENUECONSTITUENTID = null;
        set @FINDERNUMBER = null;
    end
    else
        set @GIFTRECIPIENT = 1;

    set @SPONSORSHIPCONSTITUENTID = null;

    if @PAYMENTMETHODCODE is null 
        set @PAYMENTMETHODCODE = 2


    return 0;