USP_DATALIST_ASKLADDER

Displays a list of all ask ladders.

Parameters

Parameter	Parameter Type	Mode	Description
@STATUS	tinyint	IN	Status
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_ASKLADDER]
(
  @STATUS tinyint = 1,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare  @TEMP_SYSOBJECTINFO table
  (
    [PARENTTYPE] nvarchar(max) null,
    [DISPLAYNAME] nvarchar(max) null,
    [OBJECTKEY] nvarchar(max) null
  )
  insert into @TEMP_SYSOBJECTINFO
  select   
    [QUERYVIEWCATALOG].[DISPLAYNAME] as [PARENTTYPE],  
    (select top 1 isnull(T.c.value('(@Caption)[1]', 'nvarchar(255)'), T.c.value('(@Name)[1]', 'nvarchar(255)')) from [QUERYVIEWCATALOG].OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) where T.c.value('(@Name)[1]', 'nvarchar(255)') = [SYSCOLUMNS].[NAME] and isnull(T.c.value('(@IsHidden)[1]', 'nvarchar(5)'), '0') in ('0', 'false')) as [DISPLAYNAME],  
    [SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME] as [OBJECTKEY] 
  from dbo.[QUERYVIEWCATALOG]   
    left join dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME] = [QUERYVIEWCATALOG].[OBJECTNAME]   
    left join dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID] = [SYSOBJECTS].[ID]  
    where ([SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME]) in (select [OBJECTKEY] from dbo.[MKTASKLADDER])

  select  
    [MKTASKLADDER].[ID],  
    [MKTASKLADDER].[NAME],  
    coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME], [SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME], [MKTASKLADDER].[OBJECTKEY]) as [BASEDON],  
    dbo.[UFN_MKTASKLADDER_ISINUSE]([MKTASKLADDER].[ID]) as [ISINUSE],  
    (select [DISPLAYNAME] from dbo.[QUERYVIEWCATALOG] where [ID] = [MKTASKLADDER].[RECORDSOURCEID]) as [RECORDSOURCE],  
    (case when [MKTASKLADDER].[ISACTIVE] = 1 then 'Active' else 'Inactive' end) as [STATUS],  
    (select [NAME] from dbo.[SITE] where [ID] = [MKTASKLADDER].[SITEID]) as [SITE]  
  from dbo.[MKTASKLADDER]   
  left join @TEMP_SYSOBJECTINFO [FIELDINFO] on [FIELDINFO].OBJECTKEY = [MKTASKLADDER].[OBJECTKEY] 
  left join (
    select 
      'Smart Field' as [PARENTTYPE],
      [SMARTFIELD].[NAME] as [DISPLAYNAME], 
      [SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME] as [OBJECTKEY]
    from dbo.[SMARTFIELD]
    inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID] = [SMARTFIELD].[TABLECATALOGID]
  ) as [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  where (@STATUS is null or @STATUS = [ISACTIVE])
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTASKLADDER].[SITEID] or (SITEID is null and [MKTASKLADDER].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTASKLADDER].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)));

  return 0;