USP_GLOBALCHANGE_CHANGEACKNOWLEDGEMENTMESSAGE

CREATE procedure dbo.USP_GLOBALCHANGE_CHANGEACKNOWLEDGEMENTMESSAGE
(
  @SELECTEDEVENTS xml,
  @SOURCEEVENTID uniqueidentifier,
  @SOURCEACKNOWLEDGEMENTID int,
    @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)
  declare @MESSAGESUBJECT nvarchar(max)
  DECLARE @TOTALEMAILTEMPLATES int
  DECLARE @CONFIRMATIONTYPECODE int = 0
  DECLARE @TARGETMESSAGESTABLE table (ID int identity(1,1), EMAILTEMPLATEID int)

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

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

  if  @SOURCEACKNOWLEDGEMENTID is not null begin
    begin try
      SELECT @SOURCETEXT = CONTENTHTML, @CONFIRMATIONTYPECODE = EET.CONFIRMATIONTYPECODE, @MESSAGESUBJECT=ET.SUBJECT
      FROM dbo.EmailTemplate ET
      INNER JOIN dbo.EVENTEMAILTEMPLATE EET ON ET.ID = EET.EMAILTEMPLATEID
      WHERE ET.ID = @SOURCEACKNOWLEDGEMENTID

      --create a temp table of the email templates we need to update

      INSERT INTO @TARGETMESSAGESTABLE (EMAILTEMPLATEID)
      SELECT EMAILTEMPLATEID FROM EVENTEMAILTEMPLATE
      WHERE CONFIRMATIONTYPECODE=@CONFIRMATIONTYPECODE
      AND EVENTID IN (
        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)) )
            )            
      )

      --get the total #

      SELECT @TOTALEMAILTEMPLATES = COUNT(*) FROM @TARGETMESSAGESTABLE

      --update the subject and content fields for the given email templates

      update dbo.EmailTemplate
      SET SUBJECT=@MESSAGESUBJECT, 
      CONTENTHTML=@SOURCETEXT
            where ID in (SELECT EMAILTEMPLATEID FROM @TARGETMESSAGESTABLE)

      set @NUMBEREDITED = @TOTALEMAILTEMPLATES;      
    end try
      begin catch
          exec dbo.USP_RAISE_ERROR;
          return 1; 
      end catch
  end