USP_DATAFORMTEMPLATE_VIEW_EXPORTDEFINITION_SOURCEVIEW

The load procedure used by the view dataform template "Export Definition Source View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SOURCEVIEWS_COMMON	xml	INOUT	SOURCEVIEWS_COMMON
@SOURCEVIEWS	xml	INOUT	Select a Query Type

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EXPORTDEFINITION_SOURCEVIEW
(
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SOURCEVIEWS_COMMON xml = null output,
  @SOURCEVIEWS xml = null output
)
as
  set nocount on;

  -- be sure to set this, in case the select returns no rows
  set @DATALOADED = 1;

  declare @QUERY_TYPES table
  (
      ID uniqueidentifier,
      OBJECTNAME nvarchar(128),     
      DISPLAYNAME nvarchar(255),
      RECORDTYPEID uniqueidentifier,
      RECORDTYPE nvarchar(50),
      OBJECTTYPE tinyint,
      [DESCRIPTION] nvarchar(max),
      USEINREPORTMODELGENERATOR bit
  )
  insert into @QUERY_TYPES
  exec dbo.[USP_QUERYVIEWCATALOG_GETROOTLIST] @CURRENTAPPUSERID

  declare @VISIBLE_QUERY_TYPES table
  (
      ID uniqueidentifier,    
      DISPLAYNAME nvarchar(255),
      RECORDTYPE nvarchar(50),
      [DESCRIPTION] nvarchar(max)
  )

  insert 
        into @VISIBLE_QUERY_TYPES
  select
      QUERY_TYPES.ID,
      QUERY_TYPES.DISPLAYNAME,
      QUERY_TYPES.RECORDTYPE,
      QUERY_TYPES.DESCRIPTION 
  from 
      @QUERY_TYPES QUERY_TYPES        
      inner join dbo.QUERYVIEWCATALOG on QUERY_TYPES.ID = QUERYVIEWCATALOG.ID
  where (
    dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(
        QUERYVIEWCATALOG.QUERYVIEWSPEC.query(
          'declare namespace common="bb_appfx_commontypes";
          /*/common:InstalledProductList'
        )
    ) = 1
  )
  -- hide marketing effort export record types
  and QUERY_TYPES.RECORDTYPEID not in (select [ID] from dbo.[RECORDTYPE] where [NAME] like '% Segment Member Export')

  select
      @SOURCEVIEWS = (
        select
          VISIBLE_QUERY_TYPES.ID,
          VISIBLE_QUERY_TYPES.DISPLAYNAME as NAME,
          VISIBLE_QUERY_TYPES.RECORDTYPE as GROUPTYPE,
          VISIBLE_QUERY_TYPES.DESCRIPTION     
        from 
          @VISIBLE_QUERY_TYPES VISIBLE_QUERY_TYPES
        order by 
          GROUPTYPE, NAME
        for xml raw('ITEM'),type,elements,root('SOURCEVIEWS'),binary base64
      ),

      @SOURCEVIEWS_COMMON = (
        select top(5)
          VISIBLE_QUERY_TYPES.ID,
          VISIBLE_QUERY_TYPES.DISPLAYNAME as NAME,
          VISIBLE_QUERY_TYPES.DESCRIPTION
        from 
          @VISIBLE_QUERY_TYPES VISIBLE_QUERY_TYPES
          inner join dbo.EXPORTDEFINITION on VISIBLE_QUERY_TYPES.ID = EXPORTDEFINITION.QUERYVIEWCATALOGID
        where
          EXPORTDEFINITION.ISSYSTEM = 0
        group by
          VISIBLE_QUERY_TYPES.ID,
          VISIBLE_QUERY_TYPES.DISPLAYNAME,
          VISIBLE_QUERY_TYPES.DESCRIPTION
        order by 
          count(EXPORTDEFINITION.ID) desc
        for xml raw('ITEM'),type,elements,root('SOURCEVIEWS_COMMON'),binary base64
      )

  return 0;