USP_SPONSORSHIPLOCATION_INACTIVE_REASON

The save procedure used by the edit dataform template "Sponsorship Location Mark Inactive Reason Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SPONSORSHIPREASONID	uniqueidentifier	IN	Reason
@COMMENT	nvarchar(255)	IN	Comments

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPLOCATION_INACTIVE_REASON
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @SPONSORSHIPREASONID uniqueidentifier ,
        @COMMENT nvarchar(255)
)
as begin
        set nocount on;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()


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

    declare @HIERARCHYPATH hierarchyid

    select @HIERARCHYPATH = HIERARCHYPATH
    from dbo.SPONSORSHIPLOCATION
    where ID = @ID;

    -- cache any reservation keys used by opportunities in this location

    declare @KEYS table (ID uniqueidentifier);

    insert into @KEYS
    select distinct RESERVATIONKEYID
    from dbo.SPONSORSHIPOPPORTUNITY
    inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
    and RESERVATIONKEYID is not null;

    -- Update all opportunities in the area to unavailable.

    update SPONSORSHIPOPPORTUNITY
    set AVAILABILITYCODE = 2,
          RESERVATIONKEYID = null,
        CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
    from dbo.SPONSORSHIPOPPORTUNITY
  inner join dbo.SPONSORSHIPOPPORTUNITYLOCK on SPONSORSHIPOPPORTUNITYLOCK.ID = SPONSORSHIPOPPORTUNITY.ID
    inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
  and SPONSORSHIPOPPORTUNITYLOCK.LOCKCOUNT = 0
  and SPONSORSHIPOPPORTUNITYLOCK.LOCKED = 0;

  declare @REMAININGCOUNT int;
  select @REMAININGCOUNT = count(*)
  from dbo.SPONSORSHIPOPPORTUNITY
    inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
  and AVAILABILITYCODE <> 2;

    -- update status of reservation keys

    declare @RESERVATIONKEYID uniqueidentifier;
    declare KEYS_CUR cursor local fast_forward for
    select ID
    from @KEYS;

    open KEYS_CUR;
    fetch next from KEYS_CUR into @RESERVATIONKEYID;

    while (@@FETCH_STATUS = 0)
    begin
        exec dbo.USP_SPONSORSHIPOPPORTUNITYRESERVE_UPDATESTATUS @RESERVATIONKEYID, @CHANGEAGENTID
        fetch next from KEYS_CUR into @RESERVATIONKEYID;
    end
    close KEYS_CUR;
    deallocate KEYS_CUR;

    -- Mark the locations as inactive.

  if @REMAININGCOUNT = 0
      update dbo.SPONSORSHIPLOCATION
      set STATUSCODE = 1,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
          SPONSORSHIPREASONID = @SPONSORSHIPREASONID,
          COMMENT = case ID when @ID then @COMMENT else COMMENT end
      where ID = @ID
      or HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1;
  else
  begin
    raiserror('BBERR_SOMEOPPORTUNITIESLOCKED',13,1);
    return 1;
  end


    return 0;
end