USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPREASSIGN_4

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPREASSIGN_4 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @REVENUECONSTITUENTID uniqueidentifier,
    @SPONSORSHIPCONSTITUENTID uniqueidentifier,
    @SPONSORSHIPREASONID uniqueidentifier,
    @AMOUNT money,
    @PAYMENTMETHODCODE tinyint,
    @REFERENCEDATE dbo.UDT_FUZZYDATE,
    @REFERENCENUMBER nvarchar(20),
    @CARDHOLDERNAME nvarchar(255),
    @CREDITCARDNUMBER nvarchar(20),
    @CREDITTYPECODEID uniqueidentifier,
    @EXPIRESON dbo.UDT_FUZZYDATE,
    @CONSTITUENTACCOUNTID uniqueidentifier,
    @FREQUENCYCODE tinyint,
    @REVENUESCHEDULEENDDATE datetime,
    @REVENUESCHEDULESTARTDATE datetime,
    @AUTOPAY bit,
    @CREDITCARDTOKEN uniqueidentifier,
    @GIFTRECIPIENT bit,
    @PLANNEDENDDATE date,
    @SENDREMINDER bit,
    @FINDERNUMBER bigint,
    @SOURCECODE nvarchar(50),
    @APPEALID uniqueidentifier,
    @MAILINGID uniqueidentifier,
    @CHANNELCODEID uniqueidentifier,
    @REFERENCE nvarchar(255),
    @CATEGORYCODEID uniqueidentifier,
    @CARRYFORWARDPASTDUE bit,
    @CARRYFORWARDPASTDUEAMOUNT money,
    @EXPIRATIONREASONID uniqueidentifier,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @BASEEXCHANGERATEID uniqueidentifier,
    @EXCHANGERATE decimal(20,8),
    @CURRENTAPPUSERID uniqueidentifier,
    @DONOTACKNOWLEDGE bit,
    @SEPAMANDATEID uniqueidentifier,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier
)
as

set nocount on;

declare @NEWID uniqueidentifier
set @NEWID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime = getdate();

begin try
    declare @ORIGINALCONSTITUENTID uniqueidentifier
    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier
    declare @SPONSORSHIPPROGRAMID uniqueidentifier
    declare @SPONSORSHIPLOCATIONID uniqueidentifier
    declare @GENDERCODE tinyint
    declare @SPROPPAGERANGEID uniqueidentifier
    declare @ISHIVPOSITIVECODE tinyint
    declare @HASCONDITIONCODE tinyint
    declare @ISORPHANEDCODE tinyint
    declare @SPROPPPROJECTCATEGORYCODEID uniqueidentifier
    declare @ISSOLESPONSORSHIP bit
    declare @REASSIGNDATE date
    set @REASSIGNDATE = @CURRENTDATE;

    select @ORIGINALCONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
           @SPONSORSHIPOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
           @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,
           @REASSIGNDATE = case when STARTDATE < @REASSIGNDATE then @REASSIGNDATE else STARTDATE end
    from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    where SPONSORSHIP.ID = @ID

    if @SPONSORSHIPCONSTITUENTID = @ORIGINALCONSTITUENTID
          raiserror('BBERR_REASSIGNEDTOSAMECONSTITUENT',13,1)

    -- reverify that financial sponsor is OK w/ this opportunity

    if dbo.UFN_SPONSORSHIP_VALIDOPPORTUNITYFORFINANCIALSPONSOR(@SPONSORSHIPOPPORTUNITYID,@REVENUECONSTITUENTID,@ID) = 0
    begin
        raiserror('BBERR_OPPORTUNITYINVALIDFORFINANCIALSPONSOR',13,1)
        return 1
    end

    -- In the case of sole sponsorship, preemptively close the original sponsorship to prevent

    -- sole sponsorship errors.

    if @ISSOLESPONSORSHIP = 1
        exec dbo.USP_SPONSORSHIP_INACTIVATE
            @ID,
            @REASSIGNDATE,
            0,
            @CHANGEAGENTID
    if @FINDERNUMBER is null
       set @FINDERNUMBER = 0

    -- Create a new commitment for the new sponsor.

    exec dbo.USP_SPONSORSHIP_CREATECOMMITMENT
        @NEWID,
        @CHANGEAGENTID,
        @REVENUECONSTITUENTID,
        @SPONSORSHIPCONSTITUENTID,
        @ID,
        @SPONSORSHIPREASONID,
        @SPONSORSHIPPROGRAMID,
        @SPONSORSHIPLOCATIONID,
        @GENDERCODE,
        @SPROPPAGERANGEID,
        @ISHIVPOSITIVECODE,
        @HASCONDITIONCODE,
        @ISORPHANEDCODE,
        @SPROPPPROJECTCATEGORYCODEID,
        @SPONSORSHIPOPPORTUNITYID,
        @ISSOLESPONSORSHIP,
        @REASSIGNDATE,
        @AMOUNT,
        @PAYMENTMETHODCODE,
        @REFERENCEDATE,
        @REFERENCENUMBER,
        @CARDHOLDERNAME,
        @CREDITCARDNUMBER,
        @CREDITTYPECODEID,
        @EXPIRESON,
        @CONSTITUENTACCOUNTID,
        @FREQUENCYCODE,
        @REVENUESCHEDULEENDDATE,
        @REVENUESCHEDULESTARTDATE,
        @AUTOPAY,
        @CREDITCARDTOKEN,
        @PLANNEDENDDATE,
        @SENDREMINDER,
        @FINDERNUMBER,
        @SOURCECODE,
        @APPEALID,
        @MAILINGID,
        @CHANNELCODEID,
        @REFERENCE,
        @CATEGORYCODEID,
        @EXPIRATIONREASONID,
        @TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID,
        @EXCHANGERATE,
        @CURRENTAPPUSERID,
        default, --@BATCHNUMBER

        @DONOTACKNOWLEDGE,
        default, --@DATA xml

        default, --BASECURRENCYID

        @SEPAMANDATEID,
        @OTHERPAYMENTMETHODCODEID;

    if @CARRYFORWARDPASTDUE = 1
  begin
    declare @RGI_ID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @NEW_TRANSACTIONAMOUNT money;
    declare @NEW_BASEAMOUNT money;
    declare @NEW_ORGANIZATIONAMOUNT money;

    select @RGI_ID = RGI.ID,
           @NEW_TRANSACTIONAMOUNT = RGI.TRANSACTIONAMOUNT + @CARRYFORWARDPASTDUEAMOUNT,
           @BASECURRENCYID = RGI.BASECURRENCYID,
           @ORGANIZATIONEXCHANGERATEID = RGI.ORGANIZATIONEXCHANGERATEID
    from dbo.SPONSORSHIP
    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    inner join dbo.RECURRINGGIFTINSTALLMENT as RGI on RGI.REVENUEID = REVENUESPLIT.REVENUEID
    where SPONSORSHIP.ID = @NEWID
    and RGI.DATE = @REVENUESCHEDULESTARTDATE;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
      @NEW_TRANSACTIONAMOUNT,
      null,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @NEW_BASEAMOUNT output,
      null,
      @NEW_ORGANIZATIONAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID,
      0

    update dbo.RECURRINGGIFTINSTALLMENT
    set TRANSACTIONAMOUNT = @NEW_TRANSACTIONAMOUNT,
        AMOUNT = @NEW_BASEAMOUNT,
        ORGANIZATIONAMOUNT = @NEW_ORGANIZATIONAMOUNT,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where ID = @RGI_ID;
  end

    -- Close the original sponsor's commitment.

    exec dbo.USP_SPONSORSHIP_CLOSECOMMITMENT
        @ID,
        @REASSIGNDATE,
        5,
        @SPONSORSHIPREASONID,
        @NEWID,
        null,
        @CHANGEAGENTID

    -- on reassign, copy over link from the sponsorship recurring additional gift

    insert into dbo.SPONSORSHIPRECURRINGADDITIONALGIFT
        (
            ID,
            SPONSORSHIPID,
            REVENUEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select newid(),
        @NEWID,
        REVENUEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
        from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT
        where SPONSORSHIPID = @ID;
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0