USP_GLOBALCHANGE_DELETEEVENTINCENTIVELEVEL

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

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

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

    begin try
        -- do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate
         DECLARE @SelectedIncentiveLevelsTable table (ID int identity(1,1), IncentiveLevelID uniqueidentifier)    

      insert into @SelectedIncentiveLevelsTable(IncentiveLevelID)
          SELECT IncentiveLevelID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS IncentiveLevelID
            FROM @SELECTEDINCENTIVELEVELSFORDELETE.nodes('/SELECTEDINCENTIVELEVELSFORDELETE/ITEM') T(c)) XMLTABLE
      INNER JOIN FAFINCENTIVELEVEL IL ON XMLTABLE.IncentiveLevelID = IL.ID
      WHERE exists(
          select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(IL.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)) )
          )  

    DELETE FROM FAFINCENTIVELEVEL
      WHERE ID in (SELECT IncentiveLevelID FROM @SelectedIncentiveLevelsTable)

    SET @NUMBERDELETED = (select COUNT(*) from @SelectedIncentiveLevelsTable)
    SELECT @NUMBERDELETED
    end try

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