USP_SPONSORSHIP_OVERRIDETRANSFER

Override a pending transfer with new settings.

Parameters

Parameter	Parameter Type	Mode
@TOSPONSORSHIPID	uniqueidentifier	INOUT
@FROMSPONSORSHIPID	uniqueidentifier	IN
@DECLINEDSPONSORSHIPID	uniqueidentifier	IN
@SPONSORSHIPREASONID	uniqueidentifier	IN
@MATCHRULE	tinyint	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
@TOSPONSORSHIPOPPORTUNITYID	uniqueidentifier	IN
@ISSOLESPONSORSHIP	bit	IN
@UPDATEAVAILABILITY	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@PLANNEDENDDATE	date	IN
@EXPIRATIONREASONID	uniqueidentifier	IN
@UNLOCKTARGETOPPORTUNITY	bit	IN

Definition

Copy



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

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

    declare @TRANSFERDATE date;
    set @TRANSFERDATE = @CURRENTDATE;

    select @TRANSFERDATE = case when STARTDATE < @TRANSFERDATE then @TRANSFERDATE else STARTDATE end
    from dbo.SPONSORSHIP
    where ID = @FROMSPONSORSHIPID;

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

    begin try
        -- inactivate the old sponsorship

        exec dbo.USP_SPONSORSHIP_INACTIVATE
            @FROMSPONSORSHIPID,
            @TRANSFERDATE,
            @UPDATEAVAILABILITY,
            @CHANGEAGENTID

        -- inactivate the pending sponsorship

        exec dbo.USP_SPONSORSHIP_INACTIVATE
            @DECLINEDSPONSORSHIPID,
            null,
            @UPDATEAVAILABILITY,
            @CHANGEAGENTID

        declare @REVENUESPLITID uniqueidentifier

        -- create the new sponsorship

        exec dbo.USP_SPONSORSHIP_CREATETRANSFER
            @TOSPONSORSHIPID output,
            @FROMSPONSORSHIPID,
            7,
            @SPONSORSHIPREASONID,
            @TRANSFERDATE,
            @MATCHRULE,
            @TOSPONSORSHIPOPPORTUNITYID output,
            @SPONSORSHIPPROGRAMID,
            @SPONSORSHIPLOCATIONID,
            @GENDERCODE,
            @SPROPPAGERANGEID,
            @ISHIVPOSITIVECODE,
            @HASCONDITIONCODE,
            @ISORPHANEDCODE,
            @SPROPPPROJECTCATEGORYCODEID,
            @ISSOLESPONSORSHIP,
            @DECLINEDSPONSORSHIPID,
            @CHANGEAGENTID,
            @REVENUESPLITID output,
            @PLANNEDENDDATE,
            @EXPIRATIONREASONID,
      @UNLOCKTARGETOPPORTUNITY

        exec dbo.USP_SPONSORSHIP_UPDATEDESIGNATION
            @REVENUESPLITID,
            @TOSPONSORSHIPOPPORTUNITYID,
            @CHANGEAGENTID        
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end