USP_DATALIST_MKTDOCUMENT

Displays a list of all marketing documents.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DOCUMENTTYPECODEID	uniqueidentifier	IN	Type
@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.
@CURRENCYCODE	tinyint	IN	Currency

Definition

Copy



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

  select
    [MKTDOCUMENT].[ID],
    [MKTDOCUMENT].[NAME],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTDOCUMENT].[SITEID]) as [SITE],
    case @CURRENCYCODE when 1 then [MKTDOCUMENT].[ORGANIZATIONCOST] else [MKTDOCUMENT].[COST] end [COST],
    [MKTDOCUMENT].[COSTDISTRIBUTIONMETHOD],
    [MKTDOCUMENTTYPECODE].[DESCRIPTION] as [DOCUMENTTYPE],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) = 0 then [MKTDOCUMENT].[FILEURL] else [MKTDOCUMENT].[FILENAME] end as [DOCUMENT],
    [MKTDOCUMENT].[DESCRIPTION],
    convert(bit, case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 or len(isnull([MKTDOCUMENT].[FILEURL], '')) > 0 then 1 else 0 end) as [HASDOCUMENT],
    convert(bit, case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then 1 else 0 end) as [ISATTACHMENT],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then convert(uniqueidentifier, 'EE0B7FDB-1466-4A0A-AB79-DCE605AC3F77') else null end as [VIEWDATAFORMID],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then 'FILE' else null end as [FILEFIELDID],
    case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]()
                       else [MKTDOCUMENT].[BASECURRENCYID] end [CURRENCYID],
    (select count([MKTPACKAGEDOCUMENT].[DOCUMENTID]) from dbo.[MKTPACKAGEDOCUMENT] where [MKTPACKAGEDOCUMENT].[DOCUMENTID] = [MKTDOCUMENT].[ID]) [NUMPACKAGES]
  from dbo.[MKTDOCUMENT]
  left outer join dbo.[MKTDOCUMENTTYPECODE] on [MKTDOCUMENTTYPECODE].[ID] = [MKTDOCUMENT].[DOCUMENTTYPECODEID]
  where (@DOCUMENTTYPECODEID is null or [MKTDOCUMENT].[DOCUMENTTYPECODEID] = @DOCUMENTTYPECODEID)
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTDOCUMENT].[SITEID] or (SITEID is null and [MKTDOCUMENT].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTDOCUMENT].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTDOCUMENT].[NAME];

  return 0;