USP_OLAP_COPYSMARTFIELDQUERYVIEWPERMISSIONS

create procedure dbo.[USP_OLAP_COPYSMARTFIELDQUERYVIEWPERMISSIONS] (@SYSTEMROLEID uniqueidentifier = null)
as
begin

declare @MARTKEY nvarchar(255);

declare @CHANGEAGENTID uniqueidentifier;
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

declare datamart_cursor cursor local fast_forward for
    select [MARTKEY] from dbo.[OLAPDATASOURCE];

open datamart_cursor

fetch next from datamart_cursor into @MARTKEY

while @@FETCH_STATUS = 0

begin

  merge [SYSTEMROLEPERM_QUERYVIEW] as t 

  using 

  (
      select
      isnull([DMPERM].[ID],newid()) as [ID],
      [OLTPPERM].[SYSTEMROLEID],
      [DMQV].[ID] as [QUERYVIEWCATALOGID],
      [OLTPPERM].[GRANTORDENY],
      @CHANGEAGENTID as [ADDEDBYID], 
      @CHANGEAGENTID as [CHANGEDBYID],
      @CURRENTDATE as [DATEADDED],
      @CURRENTDATE as [DATECHANGED]
      from dbo.[SYSTEMROLEPERM_QUERYVIEW] [OLTPPERM]
          inner join dbo.[QUERYVIEWCATALOG] [OLTPQV] on [OLTPQV].[ID] = [OLTPPERM].[QUERYVIEWCATALOGID]
          inner join dbo.[SMARTFIELD] on dbo.[UFN_SMARTFIELD_GETQUERYVIEWOBJECTNAME]([SMARTFIELD].[ID])=upper([OLTPQV].[OBJECTNAME])
      inner join dbo.[QUERYVIEWCATALOG] [DMQV] on [DMQV].[MARTKEY] = @MARTKEY and (replace('V_QUERY_'+ @MARTKEY + '_SMARTFIELD' + convert(nvarchar(36),[SMARTFIELD].[TABLECATALOGID]), '-',''))=upper([DMQV].[OBJECTNAME]) 
      left join dbo.[SYSTEMROLEPERM_QUERYVIEW] [DMPERM] on [DMPERM].[QUERYVIEWCATALOGID] = [DMQV].[ID] and [DMPERM].[SYSTEMROLEID] = [OLTPPERM].[SYSTEMROLEID]
    where ([OLTPPERM].[SYSTEMROLEID] = @SYSTEMROLEID or @SYSTEMROLEID is null)
    and ([DMPERM].[ID] is null or [DMPERM].[GRANTORDENY] <> [OLTPPERM].[GRANTORDENY]) 

  ) as s on t.[ID] = s.[ID]

  when not matched by target

    then insert ([ID], [SYSTEMROLEID], [QUERYVIEWCATALOGID], [GRANTORDENY], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])

    values 
  (s.[ID], s.[SYSTEMROLEID], s.[QUERYVIEWCATALOGID], s.[GRANTORDENY], s.[ADDEDBYID], s.[CHANGEDBYID], s.[DATEADDED], s.[DATECHANGED])

  when matched 

    then update 
    set

      t.[ID] = s.[ID],
        t.[SYSTEMROLEID] = s.[SYSTEMROLEID],
        t.[QUERYVIEWCATALOGID] = s.[QUERYVIEWCATALOGID],
        t.[GRANTORDENY] = s.[GRANTORDENY],    
        t.[ADDEDBYID] = s.[ADDEDBYID],
        t.[CHANGEDBYID] = s.[CHANGEDBYID],
        t.[DATEADDED] = s.[DATEADDED],
        t.[DATECHANGED] = s.[DATECHANGED];


  ---End merge statement, handle rows deleted from [SYSTEMROLEPERM_QUERYVIEW] for OLTP smart fields


  delete from dbo.[SYSTEMROLEPERM_QUERYVIEW]
  where [ID]
  in
  (
       select
      [DMPERM].[ID]
      from dbo.[SYSTEMROLEPERM_QUERYVIEW] [DMPERM]
    inner join dbo.[QUERYVIEWCATALOG] [DMQV] on [DMQV].[MARTKEY] = @MARTKEY and [DMPERM].[QUERYVIEWCATALOGID] = [DMQV].[ID]
    inner join dbo.[SMARTFIELD] on (replace('V_QUERY_'+ @MARTKEY + '_SMARTFIELD' + convert(nvarchar(36),[SMARTFIELD].[TABLECATALOGID]), '-',''))=upper([DMQV].[OBJECTNAME]) 
    left join dbo.[QUERYVIEWCATALOG] [OLTPQV] on dbo.[UFN_SMARTFIELD_GETQUERYVIEWOBJECTNAME]([SMARTFIELD].[ID])=upper([OLTPQV].[OBJECTNAME])
    left join dbo.[SYSTEMROLEPERM_QUERYVIEW] [OLTPPERM] on [OLTPPERM].[QUERYVIEWCATALOGID] = [OLTPQV].[ID] and [DMPERM].[SYSTEMROLEID] = [OLTPPERM].[SYSTEMROLEID]
    where [OLTPPERM].[ID] is null and ([DMPERM].[SYSTEMROLEID] = @SYSTEMROLEID or @SYSTEMROLEID is null)
  );

  fetch next from datamart_cursor into @MARTKEY

end

close datamart_cursor;
deallocate datamart_cursor;

end