USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPTRANSFER_4

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPONSORSHIPREASONID	uniqueidentifier	IN
@SPONSORSHIPPROGRAMID	uniqueidentifier	IN
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN
@GENDERCODE	int	IN
@SPROPPAGERANGEID	uniqueidentifier	IN
@ISHIVPOSITIVECODE	int	IN
@HASCONDITIONCODE	int	IN
@ISORPHANEDCODE	int	IN
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	IN
@SPONSORSHIPOPPORTUNITYIDCHILD	uniqueidentifier	IN
@SPONSORSHIPOPPORTUNITYIDPROJECT	uniqueidentifier	IN
@ISSOLESPONSORSHIP	bit	IN
@AMOUNT	money	IN
@FREQUENCYCODE	tinyint	IN
@REVENUESCHEDULESTARTDATE	datetime	IN
@REVENUESCHEDULEENDDATE	datetime	IN
@PLANNEDENDDATE	date	IN
@RESERVEDOPPORTUNITYIDCHILD	uniqueidentifier	IN
@RESERVATIONKEY	uniqueidentifier	IN
@MATCHEDOPPORTUNITYID	uniqueidentifier	IN
@SENDREMINDER	bit	IN
@NEXTINSTALLMENTID	uniqueidentifier	IN
@FINDERNUMBER	bigint	IN
@SOURCECODE	nvarchar(50)	IN
@APPEALID	uniqueidentifier	IN
@MAILINGID	uniqueidentifier	IN
@CHANNELCODEID	uniqueidentifier	IN
@REFERENCE	nvarchar(255)	IN
@CATEGORYCODEID	uniqueidentifier	IN
@REVENUEDEVELOPMENTFUNCTIONCODEID	uniqueidentifier	IN
@EXPIRATIONREASONID	uniqueidentifier	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASEEXCHANGERATEID	uniqueidentifier	IN
@EXCHANGERATE	decimal(20, 8)	IN
@HADSPOTRATE	bit	IN
@RATECHANGED	bit	IN
@UPDATERECOGNITIONOPTION	tinyint	IN
@DONOTACKNOWLEDGE	bit	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPTRANSFER_4 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPREASONID uniqueidentifier,
    --@MATCHRULE tinyint = 0,

    @SPONSORSHIPPROGRAMID uniqueidentifier,
    @SPONSORSHIPLOCATIONID uniqueidentifier,
    @GENDERCODE int,
    @SPROPPAGERANGEID uniqueidentifier,
    @ISHIVPOSITIVECODE int,
    @HASCONDITIONCODE int,
    @ISORPHANEDCODE int,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier,
    @ISSOLESPONSORSHIP bit,
    @AMOUNT money,
    @FREQUENCYCODE tinyint,
    @REVENUESCHEDULESTARTDATE datetime,
    @REVENUESCHEDULEENDDATE datetime,
    @PLANNEDENDDATE date,
    @RESERVEDOPPORTUNITYIDCHILD uniqueidentifier,
    @RESERVATIONKEY uniqueidentifier,
    @MATCHEDOPPORTUNITYID uniqueidentifier,
    @SENDREMINDER bit,
    @NEXTINSTALLMENTID uniqueidentifier,
    @FINDERNUMBER bigint,
    @SOURCECODE nvarchar(50),
    @APPEALID uniqueidentifier,
    @MAILINGID uniqueidentifier,
    @CHANNELCODEID uniqueidentifier,
    @REFERENCE nvarchar(255),
    @CATEGORYCODEID uniqueidentifier,
    @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier,
    @EXPIRATIONREASONID uniqueidentifier,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @BASEEXCHANGERATEID uniqueidentifier,
    @EXCHANGERATE decimal(20,8),
    @HADSPOTRATE bit,
    @RATECHANGED bit,
    @UPDATERECOGNITIONOPTION tinyint,
    @DONOTACKNOWLEDGE bit
)
as

    set nocount on;

    declare @NEWID uniqueidentifier
    set @NEWID = newid()

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier
    set @SPONSORSHIPOPPORTUNITYID = isnull(@SPONSORSHIPOPPORTUNITYIDCHILD,@SPONSORSHIPOPPORTUNITYIDPROJECT)

    if @SPONSORSHIPOPPORTUNITYID is null and @RESERVEDOPPORTUNITYIDCHILD is not null
        set @SPONSORSHIPOPPORTUNITYID = @RESERVEDOPPORTUNITYIDCHILD

    if @SPONSORSHIPOPPORTUNITYID is not null
    begin
        set @SPONSORSHIPLOCATIONID = null;
        set @GENDERCODE = 0;
        set @SPROPPAGERANGEID = null;
        set @ISHIVPOSITIVECODE = 0;
        set @HASCONDITIONCODE = 0;
        set @ISORPHANEDCODE = 0;
        set @SPROPPPROJECTCATEGORYCODEID = null;
    end

    if @SPONSORSHIPOPPORTUNITYID is null and @MATCHEDOPPORTUNITYID is not null
        set @SPONSORSHIPOPPORTUNITYID = @MATCHEDOPPORTUNITYID

    if @SPONSORSHIPOPPORTUNITYID is null
    begin
        raiserror('BBERR_NOOPPORTUNITYSPECIFIED',13,1);
        return 1
    end

    if @GENDERCODE is null
        set @GENDERCODE = 0
    if @ISHIVPOSITIVECODE is null
        set @ISHIVPOSITIVECODE = 0
    if @HASCONDITIONCODE is null
        set @HASCONDITIONCODE = 0
    if @ISORPHANEDCODE is null
        set @ISORPHANEDCODE = 0
    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0

    begin try
        exec dbo.USP_SPONSORSHIP_FULLTRANSFER
          @NEWID output,
          @ID,
          @SPONSORSHIPREASONID,
          0,--@MATCHRULE,

          @SPONSORSHIPPROGRAMID,
          @SPONSORSHIPLOCATIONID,
          @GENDERCODE,
          @SPROPPAGERANGEID,
          @ISHIVPOSITIVECODE,
          @HASCONDITIONCODE,
          @ISORPHANEDCODE,
          @SPROPPPROJECTCATEGORYCODEID,
          @SPONSORSHIPOPPORTUNITYID,
          @ISSOLESPONSORSHIP,
          1,
          @CHANGEAGENTID,
          @PLANNEDENDDATE,
          @EXPIRATIONREASONID,
      1

        declare @REVENUEID uniqueidentifier;

        select @REVENUEID = REVENUESPLIT.REVENUEID
        from dbo.SPONSORSHIP
        left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
        left outer join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUESPLIT.REVENUEID
        where SPONSORSHIP.ID = @NEWID

        if @REVENUEID is not null
        begin
            declare @DATE datetime;
            declare @SPLITS xml;
            declare @GIVENANONYMOUSLY bit;
          declare @SCHEDULESEEDDATE datetime;

            exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2
                @ID = @REVENUEID,
                @DATE = @DATE output,
                @SPLITS = @SPLITS output,
                @GIVENANONYMOUSLY = @GIVENANONYMOUSLY output,
                @SCHEDULESEEDDATE = @SCHEDULESEEDDATE output;

            set @SPLITS.modify('replace value of (SPLITS/ITEM/AMOUNT[1]/text())[1] with sql:variable("@AMOUNT")')

            exec dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_7
                @REVENUEID,
                @CHANGEAGENTID,
                @DATE,
                @AMOUNT,
                @SPLITS,
                @FREQUENCYCODE,
                @REVENUESCHEDULEENDDATE,
                @REVENUESCHEDULESTARTDATE,
                @FINDERNUMBER,
                @SOURCECODE,
                @APPEALID,
                @GIVENANONYMOUSLY,
                @MAILINGID,
                @CHANNELCODEID,
                @DONOTACKNOWLEDGE,
                @REFERENCE,
                @CATEGORYCODEID,
                @SENDREMINDER,
                @NEXTINSTALLMENTID,
                @REVENUEDEVELOPMENTFUNCTIONCODEID,
               @TRANSACTIONCURRENCYID,
               @BASEEXCHANGERATEID,
               @EXCHANGERATE,
               @HADSPOTRATE,
               @RATECHANGED,
                @UPDATERECOGNITIONOPTION,
                null,
                @SCHEDULESEEDDATE,
                0;

        end

        -- on transfer, if there are recurring additional gifts, copy the link over.

        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;