USP_SPONSORSHIPOPPORTUNITY_UNLOCK

Implements the unlock half of the sponsorship opportunity locking model.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@SPONSORCOUNT	smallint	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK (
  @ID uniqueidentifier,
  @SPONSORCOUNT smallint,  -- 0 = No change (just unlock), 1 = Sponsorship created/activated (increment sponsor count)

  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

  declare @SPONSORSPEROPPORTUNITY smallint;

    select @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(SPONSORSHIPOPPORTUNITYGROUPID,SPONSORSHIPLOCATIONID)
  from dbo.SPONSORSHIPOPPORTUNITY
  where ID = @ID;

  update dbo.SPONSORSHIPOPPORTUNITYLOCK
  set LOCKCOUNT = case LOCKCOUNT when 0 then 0 else LOCKCOUNT - 1 end,
      LOCKED = case LOCKCOUNT
                 when 0 then 0
                 when 1 then 0
                 -- if there are other shared locks, check whether we need to lock out the opportunity based on the new sponsor count

                 else case when SPONSORCOUNT + isnull(@SPONSORCOUNT,0) + LOCKCOUNT - 1 = @SPONSORSPEROPPORTUNITY then 1 else 0 end
               end,
      SPONSORCOUNT = SPONSORCOUNT + isnull(@SPONSORCOUNT,0),
        CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
    where ID = @ID;
end