USP_MKTREVENUELETTER_CREATEORUPDATEVIEWS

CREATE procedure dbo.[USP_MKTREVENUELETTER_CREATEORUPDATEVIEWS]
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SQLAUDIT nvarchar(max);
  declare @RECORDSOURCEID uniqueidentifier;
  declare @REVENUELETTERTABLENAME nvarchar(128);
  declare @NEEDUNION bit;
  declare @ALIAS nvarchar(11);

  set @NEEDUNION = 0;
  set @ALIAS = '###ALIAS###';


  if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'V_MKTREVENUELETTER')
    set @SQL = 'alter ';
  else
    set @SQL = 'create ';
  set @SQL = @SQL + 'view dbo.[V_MKTREVENUELETTER]' + char(13) + 'as' + char(13);

  if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'V_MKTREVENUELETTERAUDIT')
    set @SQLAUDIT = 'alter ';
  else
    set @SQLAUDIT = 'create ';
  set @SQLAUDIT = @SQLAUDIT + 'view dbo.[V_MKTREVENUELETTERAUDIT]' + char(13) + 'as' + char(13);


  --Loop through each record source and union the tables together for the views...

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

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

  while (@@FETCH_STATUS = 0)
  begin
    if @NEEDUNION = 1
      begin
        set @SQL = @SQL + char(13) + '  union all' + char(13);
        set @SQLAUDIT = @SQLAUDIT + char(13) + '  union all' + char(13);
      end

    -- Package id only exists on the REVENUELETTERMARKETING table, not the RE tables

    declare @PACKAGESELECT nvarchar(50) = case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1 then @ALIAS + '.[MKTPACKAGEID]' else 'cast(null as uniqueidentifier) as [MKTPACKAGEID]' end;

    set @SQL = @SQL + '  select cast(''' + cast(@RECORDSOURCEID as varchar(36)) + ''' as uniqueidentifier) as [RECORDSOURCEID], [RL].[ID], cast([RL].[REVENUEID] as varchar(36)) as [REVENUEID], [RL].[LETTERCODEID], [RL].[PROCESSDATE], [RL].[ACKNOWLEDGEDATE], ' + @ALIAS + '.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], ' + @ALIAS + '.[MKTSEGMENTATIONID], ' + @ALIAS + '.[MKTSEGMENTID], ' + @PACKAGESELECT + ', [RL].[ADDEDBYID], [RL].[CHANGEDBYID], [RL].[DATEADDED], [RL].[DATECHANGED], [RL].[TS], [RL].[TSLONG]' + char(13) +
                      '  from dbo.[' + @REVENUELETTERTABLENAME + '] as [RL]';

    set @SQLAUDIT = @SQLAUDIT + '  select cast(''' + cast(@RECORDSOURCEID as varchar(36)) + ''' as uniqueidentifier) as [RECORDSOURCEID], [RLA].[AUDITID], [RLA].[AUDITRECORDID], [RLA].[AUDITKEY], [RLA].[AUDITCHANGEAGENTID], [RLA].[AUDITDATE], [RLA].[AUDITTYPECODE], [RLA].[AUDITTYPE], cast([RLA].[REVENUEID] as varchar(36)) as [REVENUEID], [RLA].[LETTERCODEID], [RLA].[PROCESSDATE], [RLA].[ACKNOWLEDGEDATE], ' + @ALIAS + '.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], ' + @ALIAS + '.[MKTSEGMENTATIONID], ' + @ALIAS + '.[MKTSEGMENTID], ' + @PACKAGESELECT + ', [RLA].[ADDEDBYID], [RLA].[CHANGEDBYID], [RLA].[DATEADDED], [RLA].[DATECHANGED]' + char(13) +
                                '  from dbo.[' + @REVENUELETTERTABLENAME + 'AUDIT] as [RLA]';

    if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
      begin
        set @SQL = replace(@SQL, @ALIAS, '[RLM]') + char(13) +
                   '  inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]';

        set @SQLAUDIT = replace(@SQLAUDIT, @ALIAS, '[RLMA]') + char(13) +
                        '  inner join dbo.[REVENUELETTERMARKETINGAUDIT] as [RLMA] on [RLMA].[AUDITRECORDID] = [RLA].[AUDITRECORDID]';
      end
    else
      begin
        set @SQL = replace(@SQL, @ALIAS, '[RL]');
        set @SQLAUDIT = replace(@SQLAUDIT, @ALIAS, '[RLA]');
      end

    set @NEEDUNION = 1;

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

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  --If no record sources exist, create empty views with the same column names and datatypes...

  if @NEEDUNION = 0
    begin
      set @SQL = @SQL + '  select cast(null as uniqueidentifier) as [RECORDSOURCEID], cast(null as uniqueidentifier) as [ID], cast(null as varchar(36)) as [REVENUEID], cast(null as uniqueidentifier) as [LETTERCODEID], cast(null as datetime) as [PROCESSDATE], cast(null as datetime) as [ACKNOWLEDGEDATE], cast(null as uniqueidentifier) as [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], cast(null as uniqueidentifier) as [MKTSEGMENTATIONID], cast(null as uniqueidentifier) as [MKTSEGMENTID], cast(null as uniqueidentifier) as [MKTPACKAGEID], cast(null as uniqueidentifier) as [ADDEDBYID], cast(null as uniqueidentifier) as [CHANGEDBYID], cast(null as datetime) as [DATEADDED], cast(null as datetime) as [DATECHANGED], cast(null as timestamp) as [TS], cast(null as bigint) as [TSLONG]';
      set @SQLAUDIT = @SQLAUDIT + '  select cast(null as uniqueidentifier) as [RECORDSOURCEID], cast(null as uniqueidentifier) as [AUDITID], cast(null as uniqueidentifier) as [AUDITRECORDID], cast(null as uniqueidentifier) as [AUDITKEY], cast(null as uniqueidentifier) as [AUDITCHANGEAGENTID], cast(null as datetime) as [AUDITDATE], cast(null as tinyint) as [AUDITTYPECODE], cast(null as nvarchar(13)) as [AUDITTYPE], cast(null as varchar(36)) as [REVENUEID], cast(null as uniqueidentifier) as [LETTERCODEID], cast(null as datetime) as [PROCESSDATE], cast(null as datetime) as [ACKNOWLEDGEDATE], cast(null as uniqueidentifier) as [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], cast(null as uniqueidentifier) as [MKTSEGMENTATIONID], cast(null as uniqueidentifier) as [MKTSEGMENTID], cast(null as uniqueidentifier) as [MKTPACKAGEID], cast(null as uniqueidentifier) as [ADDEDBYID], cast(null as uniqueidentifier) as [CHANGEDBYID], cast(null as datetime) as [DATEADDED], cast(null as datetime) as [DATECHANGED]';
    end

  --Create/alter the two views...

  exec (@SQL);
  exec (@SQLAUDIT);

  return 0;