USP_GLOBALCHANGE_EDITEVENTWAIVER

CREATE procedure dbo.USP_GLOBALCHANGE_EDITEVENTWAIVER
(
  @SELECTEDEVENTS xml,
  @WAIVEREVENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @CURRENTAPPUSERID uniqueidentifier = null  
)
as            
    set nocount off;

    declare @CURRENTDATE datetime
  declare @SOURCETEXT nvarchar(max)
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

  DECLARE @SELECTEDEVENTIDSTABLE table (ID int identity(1,1), EVENTID uniqueidentifier)
  DECLARE @TOTALEVENTS int
  DECLARE @i int = 1;  
  DECLARE @EVENTIDTOCHANGE uniqueidentifier;
  DECLARE @SEQUENCE int = 0;

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

  if  @WAIVEREVENTID is not null begin
    set @SOURCETEXT = (Select top 1 WAIVERTEXT FROM dbo.REGISTRATIONWAIVER where EVENTID=@WAIVEREVENTID AND ISCURRENT=1 ORDER BY DATEADDED DESC)

    INSERT INTO @SELECTEDEVENTIDSTABLE (EVENTID)
    SELECT EVENTID FROM (SELECT T.c.value('(EVENTID)[1]','uniqueidentifier') As 'EVENTID'
    FROM @SELECTEDEVENTS.nodes('/SELECTEDEVENTS/ITEM') T(c)) AS XMLTABLE
    WHERE exists(
        select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(XMLTABLE.EVENTID) EVENTSITE
        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
          or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20) 
          where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
        )

    SELECT @TOTALEVENTS = COUNT(*) FROM @SELECTEDEVENTIDSTABLE  

    BEGIN TRAN    
      begin try
        WHILE (@i <= @TOTALEVENTS) BEGIN
          SET @EVENTIDTOCHANGE = null

          SELECT @EVENTIDTOCHANGE = EVENTID from @SELECTEDEVENTIDSTABLE where id=@i

          --insert a new record with the copied waiver text

          exec dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRATION_WAIVER @ID=@EVENTIDTOCHANGE, @SEQUENCE=@SEQUENCE output

            insert into dbo.REGISTRATIONWAIVER
              (ID, EVENTID, WAIVERTEXT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISCURRENT, SEQUENCE)
          values
              (newid(), @EVENTIDTOCHANGE, @SOURCETEXT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, @SEQUENCE+1)

          --update the existing waiver to not be current

          UPDATE dbo.REGISTRATIONWAIVER
          SET ISCURRENT=0
          WHERE ID= (SELECT top 1 ID FROM dbo.REGISTRATIONWAIVER WHERE SEQUENCE=@SEQUENCE AND EVENTID=@EVENTIDTOCHANGE)

          SET @i = @i + 1;

        END
        set @NUMBEREDITED = @TOTALEVENTS;
        end try
      begin catch
        set @NUMBEREDITED = 0;
        exec dbo.USP_RAISE_ERROR;
        ROLLBACK TRAN
        return 1;
      end catch

    COMMIT TRAN 
  end