USP_MKTREVENUERECEIPT_CREATEORUPDATEVIEWS

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

  declare @SQL nvarchar(max);
  declare @SQLAUDIT nvarchar(max);
  declare @RECORDSOURCEID uniqueidentifier;
  declare @REVENUERECEIPTTABLENAME nvarchar(128);
  declare @NEEDUNION bit;
  declare @STATUSIDFIELD nvarchar(255);
  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_MKTREVENUERECEIPT')
    set @SQL = 'alter ';
  else
    set @SQL = 'create ';
  set @SQL = @SQL + 'view dbo.[V_MKTREVENUERECEIPT]' + char(13) + 'as' + char(13);

  if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'V_MKTREVENUERECEIPTAUDIT')
    set @SQLAUDIT = 'alter ';
  else
    set @SQLAUDIT = 'create ';
  set @SQLAUDIT = @SQLAUDIT + 'view dbo.[V_MKTREVENUERECEIPTAUDIT]' + 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_MKTREVENUERECEIPT_MAKETABLENAME]([ID])
    from dbo.[MKTGIFTRECORDSOURCE]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;

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

  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

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

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

    if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
      begin
        set @SQL = replace(@SQL, '[RR].[PROCESSDATE]', '[RR].[RECEIPTPROCESSDATE] as [PROCESSDATE]');
        set @SQL = replace(@SQL, @ALIAS, '[RRM]') + char(13) +
                   '  inner join dbo.[REVENUERECEIPTMARKETING] as [RRM] on [RRM].[ID] = [RR].[ID]';

        set @SQLAUDIT = replace(@SQLAUDIT, '[RRA].[PROCESSDATE]', '[RRA].[RECEIPTPROCESSDATE] as [PROCESSDATE]');
        set @SQLAUDIT = replace(@SQLAUDIT, @ALIAS, '[RRMA]') + char(13) +
                        '  inner join dbo.[REVENUERECEIPTMARKETINGAUDIT] as [RRMA] on [RRMA].[AUDITRECORDID] = [RRA].[AUDITRECORDID]';
      end
    else
      begin
        set @SQL = replace(@SQL, @ALIAS, '[RR]');
        set @SQLAUDIT = replace(@SQLAUDIT, @ALIAS, '[RRA]');
      end

    set @NEEDUNION = 1;

    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @REVENUERECEIPTTABLENAME;
  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 int) as [RECEIPTNUMBER], cast(null as datetime) as [PROCESSDATE], cast(null as datetime) as [RECEIPTDATE], cast(null as uniqueidentifier) as [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], cast(null as uniqueidentifier) as [MKTSEGMENTATIONID], cast(null as uniqueidentifier) as [MKTSEGMENTID], 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 int) as [RECEIPTNUMBER], cast(null as datetime) as [PROCESSDATE], cast(null as datetime) as [RECEIPTDATE], cast(null as uniqueidentifier) as [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID], cast(null as uniqueidentifier) as [MKTSEGMENTATIONID], cast(null as uniqueidentifier) as [MKTSEGMENTID], 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;