USP_GLOBALCHANGE_DELETESPONSORSHIPOPTION

CREATE procedure dbo.USP_GLOBALCHANGE_DELETESPONSORSHIPOPTION
(
    @SELECTEDSPONSORSHIPOPTIONSFORDELETE 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
         --do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate
         DECLARE @DeleteTable table (ID int identity(1,1), EventSponsorshipID uniqueidentifier)
         DECLARE @InactivateTable table (ID int identity(1,1), EventSponsorshipID uniqueidentifier)
         DECLARE @SelectedOptionsTable table (ID int identity(1,1), EventSponsorshipID uniqueidentifier)

         insert into @SelectedOptionsTable(EventSponsorshipID)
         SELECT EVENTSPONSORSHIPID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'EVENTSPONSORSHIPID'
            FROM @SELECTEDSPONSORSHIPOPTIONSFORDELETE.nodes('/SELECTEDSPONSORSHIPOPTIONSFORDELETE/ITEM') T(c)) AS XMLTABLE
      INNER JOIN EVENTSPONSORSHIPTYPE ES ON XMLTABLE.EVENTSPONSORSHIPID = ES.ID
      WHERE exists(
          select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(ES.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 into @InactivateTable(EventSponsorshipID)
         select distinct ST.EventSponsorshipID
         from @SelectedOptionsTable ST
         inner join EVENTSPONSORSHIPTYPE EST on EST.ID = ST.EventSponsorshipID
         inner join EVENTSPONSOR ES on ES.EVENTSPONSORSHIPTYPEID = EST.ID

         insert into @DeleteTable(EventSponsorshipID)
         select distinct DT.EventSponsorshipID
         from @SelectedOptionsTable DT
         inner join EVENTSPONSORSHIPTYPE EST on EST.ID = DT.EventSponsorshipID
         left join EVENTSPONSOR ES on ES.EVENTSPONSORSHIPTYPEID = EST.ID 
         WHERE ES.ID is null

         --update 
          update EVENTSPONSORSHIPTYPE 
          set ISACTIVE  = 0
          from EventSponsorshipType ES
          inner join @InactivateTable DT on DT.EventSponsorshipID = ES.ID 

          --delete 
      delete EVENTSPONSORSHIPTYPE where ID in (select EventSponsorshipID from @DeleteTable)

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

    end try

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