USP_SPONSORSHIP_TRANSFER_EDITLOAD

CREATE procedure dbo.USP_SPONSORSHIP_TRANSFER_EDITLOAD (
  @ID uniqueidentifier,
  @PENDINGTRANSFER bit,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @FORMTITLE nvarchar(100) = null output,
  @ORIGINALOPPORTUNITYID uniqueidentifier = null output,
  @REVENUECONSTITUENTID uniqueidentifier = null output,
  @SPONSORSHIPCONSTITUENTID 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,
  @STARTDATE date = null output,
  @AMOUNT money = 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,
  @FREQUENCYCODE tinyint = null output,
  @REVENUESCHEDULEENDDATE datetime = null output,
  @REVENUESCHEDULESTARTDATE datetime = null output,
  @AUTOPAY bit = null output,
  @CREDITCARDTOKEN uniqueidentifier = null output,
  @GIFTRECIPIENT bit = null output,
  @PLANNEDENDDATE date=null output,
  @SENDREMINDER bit = null output,
  @NEXTINSTALLMENTID uniqueidentifier = 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,
  @EDITSTARTDATE bit = null output,
  @LASTACTIVITYDATE date = null output,
  @EXPIRATIONREASONID 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,
  @BASEAMOUNT money = null output,
  @UPDATERECOGNITIONOPTION tinyint = null output,
  @DONOTACKNOWLEDGE bit = null output,
  @SEPAMANDATEID uniqueidentifier = null output,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output
)
as
begin
    set nocount on;

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

    set @DATALOADED = 0
    set @TSLONG = 0

    set @FORMTITLE = 'Transfer a sponsorship'

    declare @REVENUEID uniqueidentifier

    declare @CURRENTDATEEARLIESTTIME date
    set @CURRENTDATEEARLIESTTIME = getdate()

    -- populate the fields that have default values that should be fetched from the database

    select
        @DATALOADED = 1,
        @TSLONG = SPONSORSHIP.TSLONG,
        @ORIGINALOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
        @SPONSORSHIPCONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
        @REVENUECONSTITUENTID = isnull(REVENUE.CONSTITUENTID,SPONSORSHIP.CONSTITUENTID),
        @STARTDATE = SPONSORSHIP.STARTDATE,
        @SPONSORSHIPPROGRAMID = SPONSORSHIP.SPONSORSHIPPROGRAMID,
        @SPONSORSHIPLOCATIONID = SPONSORSHIP.SPONSORSHIPLOCATIONID,
        @GENDERCODE = SPONSORSHIP.CHILDGENDERCODE,
        @SPROPPAGERANGEID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID,
        @ISHIVPOSITIVECODE = SPONSORSHIP.ISHIVPOSITIVECODE,
        @HASCONDITIONCODE = SPONSORSHIP.HASCONDITIONCODE,
        @ISORPHANEDCODE = SPONSORSHIP.ISORPHANEDCODE,
        @SPROPPPROJECTCATEGORYCODEID = SPONSORSHIP.SPROPPPROJECTCATEGORYCODEID,
        @ISSOLESPONSORSHIP = SPONSORSHIP.ISSOLESPONSORSHIP,
        @REVENUEID = REVENUESPLIT.REVENUEID,
        @PLANNEDENDDATE = SPONSORSHIP.PLANNEDENDDATE,
        @EXPIRATIONREASONID = SPONSORSHIP.EXPIRATIONREASONID,
    @BASEAMOUNT = REVENUE.AMOUNT
    from dbo.SPONSORSHIP
    left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left outer join dbo.SPONSORSHIP PENDING on PENDING.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and PENDING.STATUSCODE = 0
    left outer join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    where SPONSORSHIP.ID = @ID
    and SPONSORSHIP.STATUSCODE = 1
    and case when PENDING.ID is null then 0 else 1 end = @PENDINGTRANSFER

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

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2
        @REVENUEID,
        @AMOUNT = @AMOUNT output,
        @FREQUENCYCODE = @FREQUENCYCODE output,
        @ENDDATE = @REVENUESCHEDULEENDDATE output,
        @STARTDATE = @REVENUESCHEDULESTARTDATE 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,
        @NEXTINSTALLMENTID = @NEXTINSTALLMENTID output,
        @EDITSTARTDATE = @EDITSTARTDATE output,
        @LASTACTIVITYDATE = @LASTACTIVITYDATE output,
        @BASECURRENCYID = @BASECURRENCYID output,
        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID output,
        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID output,
        @EXCHANGERATE = @EXCHANGERATE output,
        @HADSPOTRATE = @HADSPOTRATE output,
        @RATECHANGED = @RATECHANGED output,
        @BASEDECIMALDIGITS = @BASEDECIMALDIGITS output,
        @BASEROUNDINGTYPECODE = @BASEROUNDINGTYPECODE output,
        @UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION output,
        @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE output

    if @SPONSORSHIPCONSTITUENTID = @REVENUECONSTITUENTID
        set @GIFTRECIPIENT = 0;
    else
        set @GIFTRECIPIENT = 1;

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFTPAYMENTDETAILS_3
        @ID = @REVENUEID,
        @PAYMENTMETHODCODE = @PAYMENTMETHODCODE output,
        @AUTOPAY = @AUTOPAY output,
        @CARDHOLDERNAME = @CARDHOLDERNAME output,
        @CREDITCARDNUMBER = @CREDITCARDNUMBER output,
        @CREDITTYPECODEID = @CREDITTYPECODEID output,
        @EXPIRESON = @EXPIRESON output,
        @REFERENCEDATE = @REFERENCEDATE output,
        @REFERENCENUMBER = @REFERENCENUMBER output,
        @ACCOUNTID = @CONSTITUENTACCOUNTID output,
        @CREDITCARDTOKEN = @CREDITCARDTOKEN output,
        @SEPAMANDATEID = @SEPAMANDATEID output,
        @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID output;

    if @PAYMENTMETHODCODE is null
        set @PAYMENTMETHODCODE = 2
    if @FREQUENCYCODE is null
        set @FREQUENCYCODE = 3

    return 0;
end