USP_DATALIST_DATABASECLEANUPGLOBALCHANGES

CREATE procedure dbo.[USP_DATALIST_DATABASECLEANUPGLOBALCHANGES]
(
  @GLOBALCHANGECATALOGID nvarchar(73),
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as begin
  set nocount on;

  declare @GLOBALCHANGECATALOGIDS table ([ID] uniqueidentifier);

  if charindex('|', @GLOBALCHANGECATALOGID) > 0 begin
    insert into @GLOBALCHANGECATALOGIDS values (convert(uniqueidentifier, left(@GLOBALCHANGECATALOGID, 36)));
    insert into @GLOBALCHANGECATALOGIDS values (convert(uniqueidentifier, right(@GLOBALCHANGECATALOGID, 36)));
  end else
    insert into @GLOBALCHANGECATALOGIDS values (convert(uniqueidentifier, @GLOBALCHANGECATALOGID));

  with xmlnamespaces ('bb_appfx_dataforms' as dfi)
  select
    [GLOBALCHANGE].[ID],
    [GLOBALCHANGE].[NAME],
    [GLOBALCHANGECATALOG].[DISPLAYNAME] as [TYPE],
    isnull([IDSETREGISTER].[NAME], '') as [SELECTION],
    [GLOBALCHANGE].[LASTRUNON],
    case when [GLOBALCHANGE].[LASTRUNON] is null then 0 else 1 end as [ENABLERESET]
  from dbo.[GLOBALCHANGE]
  inner join dbo.[GLOBALCHANGECATALOG] on [GLOBALCHANGECATALOG].[ID] = [GLOBALCHANGE].[GLOBALCHANGECATALOGID]
  left outer join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [GLOBALCHANGE].[GLOBALCHANGEDATAFORMITEM].value('data(/dfi:DataFormItem/dfi:Values/dfi:fv[@ID="SELECTIONID"]/dfi:Value)[1]', 'uniqueidentifier')
  where [GLOBALCHANGE].[GLOBALCHANGECATALOGID] in (select [ID] from @GLOBALCHANGECATALOGIDS)
  and dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [GLOBALCHANGE].[ID]) = 1
  and (select count(*) from dbo.[UFN_SITEID_MAPFROM_GLOBALCHANGEPROCESSID]([GLOBALCHANGE].[ID]) as [GLOBALCHANGEPROCESSSITE] where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[GLOBALCHANGEPROCESSSITE].[SITEID] or (SITEID is null and [GLOBALCHANGEPROCESSSITE].[SITEID] is null)))) > 0
  order by [GLOBALCHANGE].[NAME];
end