USP_SPONSORSHIP_CREATETRANSFER

CREATE procedure dbo.USP_SPONSORSHIP_CREATETRANSFER (
    @ID uniqueidentifier = null output,
    @FROMSPONSORSHIPID uniqueidentifier = null,
    @ACTIONCODE tinyint = 1,
    @SPONSORSHIPREASONID uniqueidentifier = null,
    @TRANSFERDATE date = null,
    @MATCHRULE tinyint = 0,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null output,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE int = 0,
    @SPROPPAGERANGEID uniqueidentifier = null,
    @ISHIVPOSITIVECODE int = 0,
    @HASCONDITIONCODE int = 0,
    @ISORPHANEDCODE int = 0,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
    @ISSOLESPONSORSHIP bit = 0,
    @DECLINEDSPONSORSHIPID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @REVENUESPLITID uniqueidentifier = null output,
    @PLANNEDENDDATE date = null,
    @EXPIRATIONREASONID uniqueidentifier = null,
  @UNLOCKTARGETOPPORTUNITY bit = 1
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    if @TRANSFERDATE is null
        set @TRANSFERDATE = @CURRENTDATE

    if @ID is null
        set @ID = newid();

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

    declare @CONSTITUENTID uniqueidentifier
    declare @FROMOPPORTUNITYID uniqueidentifier
    declare @ORIGINALLOCATIONID uniqueidentifier
    declare @REVENUECONSTITUENTID uniqueidentifier
    declare @OLDISAFFILIATE bit, @NEWISAFFILIATE bit

    select @CONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
         @REVENUESPLITID = SPONSORSHIP.REVENUESPLITID,
         @FROMOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
         @ORIGINALLOCATIONID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID,
         @REVENUECONSTITUENTID = REVENUE.CONSTITUENTID,
         @TRANSFERDATE = case when STARTDATE < @TRANSFERDATE then @TRANSFERDATE else STARTDATE end,
         @OLDISAFFILIATE = case when SPONSORSHIP.REVENUESPLITID is null then 1 else 0 end
    from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    where SPONSORSHIP.ID = @FROMSPONSORSHIPID

    set @NEWISAFFILIATE = 0;
    select @NEWISAFFILIATE = 1
    from dbo.SPONSORSHIPAFFILIATEPROGRAM
    where ID = @SPONSORSHIPPROGRAMID;

    if @OLDISAFFILIATE = 1 and @NEWISAFFILIATE = 0
    begin
        raiserror('BBERR_AFFILIATETONONAFFILIATE',13,1);
        return 1
    end
    else if @OLDISAFFILIATE = 0 and @NEWISAFFILIATE = 1
    begin
        raiserror('BBERR_NONAFFILIATETOAFFILIATE',13,1);
        return 1
    end

    begin try
        --------------------------------------------------------------

        -- Acquire opportunity

        --------------------------------------------------------------

    if @SPONSORSHIPOPPORTUNITYID is null
          exec dbo.USP_SPONSORSHIP_ACQUIREOPPORTUNITY
              @SPONSORSHIPOPPORTUNITYID output,
              @CHANGEAGENTID,
              @MATCHRULE,
              @CONSTITUENTID,
              @SPONSORSHIPPROGRAMID,
              @SPONSORSHIPLOCATIONID,
              @GENDERCODE,
              @SPROPPAGERANGEID,
              @ISHIVPOSITIVECODE,
              @HASCONDITIONCODE,
              @ISORPHANEDCODE,
              @SPROPPPROJECTCATEGORYCODEID,
              @ISSOLESPONSORSHIP,
              @FROMOPPORTUNITYID,
              @ORIGINALLOCATIONID,
              @REVENUECONSTITUENTID

      ---- Determine if opportunity is suitable for sole sponsorship (WI#195022)

      declare @SOLESPONSORSHIPEXCEPTION bit = 0;
      declare @OPPORTUNITYGROUPIDFORSOLE uniqueidentifier;
      declare @OPPORTUNITYLOCATIONIDFORSOLE uniqueidentifier;
      select @OPPORTUNITYGROUPIDFORSOLE = SPONSORSHIPOPPORTUNITYGROUPID,
             @OPPORTUNITYLOCATIONIDFORSOLE = SPONSORSHIPLOCATIONID
            from dbo.SPONSORSHIPOPPORTUNITY where ID = @SPONSORSHIPOPPORTUNITYID
      set @SOLESPONSORSHIPEXCEPTION = case when (@ISSOLESPONSORSHIP = 1 and dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(@OPPORTUNITYGROUPIDFORSOLE, @OPPORTUNITYLOCATIONIDFORSOLE) = 0)  then 1 else 0 end;

            if @SOLESPONSORSHIPEXCEPTION = 1
                begin
                    raiserror('BBERR_SOLESPONSORSHIPTONONSOLE',13,1)
                end    
        --------------------------------------------------------------

        -- SPONSORSHIP

        --------------------------------------------------------------

        declare @COMMITMENTID uniqueidentifier

        select @COMMITMENTID = SPONSORSHIPCOMMITMENTID
        from dbo.SPONSORSHIP
        where ID = @FROMSPONSORSHIPID;

        if @ACTIONCODE <> 6
            update dbo.SPONSORSHIP
            set ISMOSTRECENTFORCOMMITMENT = 0,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
            where ID = @FROMSPONSORSHIPID;

    insert into dbo.SPONSORSHIP
    (
      ID,
      SPONSORSHIPCOMMITMENTID,
      CONSTITUENTID,
      SPONSORSHIPPROGRAMID,
      SPONSORSHIPOPPORTUNITYID,
      STATUSCODE,
      STARTDATE,
      SPONSORSHIPLOCATIONID,
      CHILDGENDERCODE,
      SPONSORSHIPOPPORTUNITYAGERANGEID,
      ISHIVPOSITIVECODE,
      HASCONDITIONCODE,
      ISORPHANEDCODE,
      SPROPPPROJECTCATEGORYCODEID,
      ISSOLESPONSORSHIP,
      REVENUESPLITID,
      PLANNEDENDDATE,
      EXPIRATIONREASONID,
      ISMOSTRECENTFORCOMMITMENT,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @ID,
      @COMMITMENTID,
      @CONSTITUENTID,
      @SPONSORSHIPPROGRAMID,
      @SPONSORSHIPOPPORTUNITYID,
      case @ACTIONCODE when 6 then 0 else 1 end,
      case @ACTIONCODE when 6 then null else @TRANSFERDATE end,
      @SPONSORSHIPLOCATIONID,
      @GENDERCODE,
      @SPROPPAGERANGEID,
      @ISHIVPOSITIVECODE,
      @HASCONDITIONCODE,
      @ISORPHANEDCODE,
      @SPROPPPROJECTCATEGORYCODEID,
      @ISSOLESPONSORSHIP,
      @REVENUESPLITID,
      @PLANNEDENDDATE,
      @EXPIRATIONREASONID,
      case @ACTIONCODE when 6 then 0 else 1 end,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    )

        --------------------------------------------------------------

        -- SPONSORSHIPTRANSACTION

        --------------------------------------------------------------

        insert into dbo.SPONSORSHIPTRANSACTION
        (
            ID,
            SPONSORSHIPCOMMITMENTID,
            TRANSACTIONSEQUENCE,
            ACTIONCODE,
            SPONSORSHIPREASONID,
            CONTEXTSPONSORSHIPID,
            DECLINEDSPONSORSHIPID,
            TARGETSPONSORSHIPID,
            TRANSACTIONDATE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            newid(),
            @COMMITMENTID,
            (select max(TRANSACTIONSEQUENCE)+1 from dbo.SPONSORSHIPTRANSACTION where SPONSORSHIPCOMMITMENTID = @COMMITMENTID),
            @ACTIONCODE,
            @SPONSORSHIPREASONID,
            @FROMSPONSORSHIPID,
            @DECLINEDSPONSORSHIPID,
            @ID,
            @TRANSFERDATE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        --------------------------------------------------------------

        -- Update and unlock opportunity.

        --------------------------------------------------------------

    exec dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPADDED
      @SPONSORSHIPOPPORTUNITYID,
      @ISSOLESPONSORSHIP,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @UNLOCKTARGETOPPORTUNITY

    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end