USP_DATALIST_MKTVENDORCREATIVE

Displays a list of all creatives for a vendor.

Parameters

Parameter	Parameter Type	Mode	Description
@VENDORID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@CREATIVETYPECODEID	uniqueidentifier	IN	Type
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected

Definition

Copy



CREATE procedure dbo.USP_DATALIST_MKTVENDORCREATIVE
(
  @VENDORID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
  @CREATIVETYPECODEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as
  set nocount on;

  select 
    [MKTCREATIVE].[ID],
    [MKTCREATIVE].[NAME],
    [MKTCREATIVETYPECODE].[DESCRIPTION],
    [MKTCREATIVE].[COST],
    [MKTCREATIVE].[COSTDISTRIBUTIONMETHOD],
    case when (len(isnull([MKTCREATIVE].[FILENAME], '')) = 0) then [MKTCREATIVE].[FILEURL] else [MKTCREATIVE].[FILENAME] end as [DOCUMENT],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTCREATIVE].[SITEID]) as [SITE],
    convert(bit, case when len(isnull([MKTCREATIVE].[FILEURL], '')) > 0 or len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then 1 else 0 end) as [HASDOCUMENT],
    convert(bit, case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then 1 else 0 end) as [ISATTACHMENT],
    [MKTCREATIVE].[BASECURRENCYID],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then convert(uniqueidentifier, '6e5ba4a0-8bdf-4124-86b6-bd5b78c2a04b') else null end as [VIEWDATAFORMID],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then 'FILE' else null end as [FILEFIELDID]
  from dbo.[MKTCREATIVE]
  left outer join dbo.[MKTCREATIVETYPECODE] on [MKTCREATIVETYPECODE].[ID] = [MKTCREATIVE].[CREATIVETYPECODEID]
  where [MKTCREATIVE].[VENDORID] = @VENDORID
  and (@CREATIVETYPECODEID is null or [MKTCREATIVE].[CREATIVETYPECODEID] = @CREATIVETYPECODEID)
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTCREATIVE].[SITEID] or (SITEID is null and [MKTCREATIVE].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTCREATIVE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTCREATIVE].[NAME];

  return 0;