USP_RECORDOPERATION_MKTACKNOWLEDGEMENTMAILINGMARKLETTERSSENT

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

  declare @CURRENTDATE datetime;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @REVENUELETTERTABLENAME nvarchar(128);
  declare @REVENUERECEIPTTABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

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

    set @CURRENTDATE = getdate();


    --Loop through every record source and update the acknowledged/receipted date in the MKTREVENUELETTER and MKTREVENUERECEIPT tables...

    declare RECORDSOURCECURSOR cursor local fast_forward for
      select
        [ID],
        dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME]([ID]),
        dbo.[UFN_MKTREVENUERECEIPT_MAKETABLENAME]([ID])
      from dbo.[MKTGIFTRECORDSOURCE]
      where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;

    while (@@FETCH_STATUS = 0)
    begin
      --Update records in the REVENUELETTER table...

      set @SQL = 'update dbo.[' + @REVENUELETTERTABLENAME + '] set' + char(13) +
                 '  [ACKNOWLEDGEDATE] = @CURRENTDATE,' + char(13) +
                 '  [CHANGEDBYID] = @CHANGEAGENTID,' + char(13) +
                 '  [DATECHANGED] = @CURRENTDATE' + char(13);
      if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
        set @SQL = @SQL + 'from dbo.[' + @REVENUELETTERTABLENAME + '] as [RL]' + char(13) +
                          'inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]' + char(13) +
                          'where [RLM].[MKTSEGMENTATIONID] = @SEGMENTATIONID';
      else
        set @SQL = @SQL + 'where [MKTSEGMENTATIONID] = @SEGMENTATIONID';

      exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @SEGMENTATIONID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

      --Update records in the REVENUERECEIPT table...

      set @SQL = 'update dbo.[' + @REVENUERECEIPTTABLENAME + '] set' + char(13) +
                 '  [RECEIPTDATE] = @CURRENTDATE,' + char(13) +
                 '  [CHANGEDBYID] = @CHANGEAGENTID,' + char(13) +
                 '  [DATECHANGED] = @CURRENTDATE' + char(13);
      if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
        set @SQL = @SQL + 'from dbo.[' + @REVENUERECEIPTTABLENAME + '] as [RR]' + char(13) +
                          'inner join dbo.[REVENUERECEIPTMARKETING] as [RRM] on [RRM].[ID] = [RR].[ID]' + char(13) +
                          'where [RRM].[MKTSEGMENTATIONID] = @SEGMENTATIONID';
      else
        set @SQL = @SQL + 'where [MKTSEGMENTATIONID] = @SEGMENTATIONID';

      exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @SEGMENTATIONID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

      fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;
    end

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;


    --Update the acknowledged/receipted date for the status row...

    update dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] set
      [ACKNOWLEDGEDATE] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [SEGMENTATIONID] = @ID;
  end try

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

  return 0;