USP_GLOBALCHANGE_DELETEREGISTRATIONOPTION

CREATE procedure dbo.USP_GLOBALCHANGE_DELETEREGISTRATIONOPTION
(
    @SELECTEDREGISTRATIONOPTIONSFORDELETE xml = null,
    @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
    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
         DECLARE @SelectedRegOptionsTable table (ID int identity(1,1), EventPriceID uniqueidentifier)
     DECLARE @DeleteTable table (ID int identity(1,1), EventPriceID uniqueidentifier)
     DECLARE @InactivateTable table (ID int identity(1,1), EventPriceID uniqueidentifier)
     DECLARE @NumberDeactivated int
     Set @NumberDeactivated = 0

          insert into @SelectedRegOptionsTable(EventPriceID)
          SELECT EVENTPRICEID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS EVENTPRICEID
            FROM @SELECTEDREGISTRATIONOPTIONSFORDELETE.nodes('/SELECTEDREGISTRATIONOPTIONSFORDELETE/ITEM') T(c)) XMLTABLE
      INNER JOIN EVENTPRICE EP ON XMLTABLE.EVENTPRICEID = EP.ID
      WHERE exists(
          select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EP.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)) )
          )      

      --insert all the records that already have at least one record in registrantregistration (i.e. they are in use)
      insert into @InactivateTable(EventPriceID)
      SELECT distinct ST.EVENTPRICEID
      FROM @SelectedRegOptionsTable ST
      INNER JOIN REGISTRANTREGISTRATION RR ON ST.EVENTPRICEID = RR.EVENTPRICEID
      LEFT JOIN FAFREGISTRATIONTYPE FR ON ST.EVENTPRICEID = FR.EVENTPRICEID
      WHERE FR.ISACTIVE=1

      insert into @DeleteTable
      SELECT ST.EVENTPRICEID
      FROM @SelectedRegOptionsTable ST
      LEFT JOIN REGISTRANTREGISTRATION RR ON ST.EVENTPRICEID = RR.EVENTPRICEID
      LEFT JOIN EVENTPRICE EP ON ST.EventPriceID = EP.ID
      WHERE RR.EventPriceID is NULL AND EP.ID is not NULL

      DELETE FROM EVENTPRICE
      WHERE ID in (SELECT EVENTPRICEID FROM @DeleteTable)

      UPDATE FAFREGISTRATIONTYPE
      SET ISACTIVE=0
      WHERE EVENTPRICEID in (SELECT EVENTPRICEID FROM @InactivateTable)

      SET @NUMBERDELETED = ((select COUNT(*) from @InactivateTable) + (select COUNT(*) from @DeleteTable))
      SELECT @NUMBERDELETED

    end try

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