USP_RECORDOPERATION_TRIBUTEACKNOWLEDGEMENTPROCESS_MARKLETTERSACKNOWLEDGED

CREATE procedure dbo.[USP_RECORDOPERATION_TRIBUTEACKNOWLEDGEMENTPROCESS_MARKLETTERSACKNOWLEDGED]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @OUTPUTTABLE nvarchar(128);
  declare @CURRENTDATE datetime = getdate();

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    --Update the tribute letters with the "acknowledged" date...

    update dbo.[REVENUETRIBUTELETTER] set
      [ACKNOWLEDGEDATE] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID] = @ID;

    select
      @OUTPUTTABLE = [TABLENAME]
    from dbo.[BUSINESSPROCESSOUTPUT]
    where [BUSINESSPROCESSSTATUSID] = @ID
    and [TABLEKEY] = 'OUTPUT';

    if @OUTPUTTABLE is not null and exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @OUTPUTTABLE and [TABLE_TYPE] = 'BASE TABLE')
      begin
        --Update the "acknowledged" date column in the output table too.  We don't want a timestamp on the acknowledge date to show in the

        --output table that the user will download, so the @DATE param is defined just as a "date" so that the timestamp gets truncated.

        declare @SQL nvarchar(max);
        set @SQL = 'update dbo.[' + @OUTPUTTABLE + '] set [PROCESS_ACKNOWLEDGEDATE] = @DATE';
        exec sp_executesql @SQL, N'@DATE date', @DATE = @CURRENTDATE;

        --Store the "acknowledged" date for this business process status row.  For backwards compatibility we will continue to

        --also store the date as an extended property on the output table, but all new code will not rely on it.  Instead, all

        --new code will store and use the date on the BUSINESSPROCESSSTATUS_EXT table below.

        exec dbo.[USP_SCHEMA_TABLE_SETDATEEXTENDEDPROPERTY] @OUTPUTTABLE, @CURRENTDATE, 'BB_TRIBUTEACKNOWLEDGEMENTSSENTDATE';
      end

    --Store the "acknowledged" date for this business process status row.

    insert into dbo.[BUSINESSPROCESSSTATUS_EXT] (
      [ID],
      [DATESENT],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @CURRENTDATE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end try

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

  return 0;