USP_MARKETINGMATERIALSEARCH

Search for Marketing Materials

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@NAME	nvarchar(100)	IN	Name
@MATERIALTYPECODEID	uniqueidentifier	IN	Type
@VENDOR	nvarchar(154)	IN	Vendor
@DESCRIPTION	nvarchar(255)	IN	Description
@SITEID	uniqueidentifier	IN	Site
@SITEIDISNULL	bit	IN	Site ID is null?
@EXACTMATCHONLY	bit	IN	Match all criteria exactly
@BASECURRENCYID	uniqueidentifier	IN	Base currency ID

Definition

Copy



CREATE procedure dbo.[USP_MARKETINGMATERIALSEARCH]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @MAXROWS smallint,
  @NAME nvarchar(100) = null,
  @MATERIALTYPECODEID uniqueidentifier = null,
  @VENDOR nvarchar(154) = null,
  @DESCRIPTION nvarchar(255) = null,
  @SITEID uniqueidentifier = null,
  @SITEIDISNULL bit = 0,
  @EXACTMATCHONLY bit = 0,
  @BASECURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  select top (@MAXROWS)
    [MKTMATERIAL].[ID],
    [MKTMATERIAL].[NAME],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTMATERIAL].[SITEID]) as [SITE],
    [MKTMATERIALTYPECODE].[DESCRIPTION] as [MATERIALTYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTMATERIAL].[COST],
    [MKTMATERIAL].[COSTDISTRIBUTIONMETHOD],
    [MKTMATERIAL].[DESCRIPTION],
    [MKTMATERIAL].[BASECURRENCYID]
  from dbo.[MKTMATERIAL]
  left outer join dbo.[MKTMATERIALTYPECODE] on [MKTMATERIALTYPECODE].[ID] = [MKTMATERIAL].[MATERIALTYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTMATERIAL].[VENDORID]
  where (@NAME is null or [MKTMATERIAL].[NAME] like @NAME + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and (@SITEID is null or [MKTMATERIAL].[SITEID] = @SITEID)
    and (@SITEIDISNULL = 0 or (@SITEIDISNULL = 1 and [MKTMATERIAL].[SITEID] is null))
    and (@MATERIALTYPECODEID is null or [MKTMATERIAL].[MATERIALTYPECODEID] = @MATERIALTYPECODEID)
    and (@VENDOR is null or [CONSTITUENT].[NAME] like @VENDOR + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTMATERIAL].[SITEID]) = 1
    and (@BASECURRENCYID is null or [MKTMATERIAL].[BASECURRENCYID] = @BASECURRENCYID)
  order by [MKTMATERIAL].[NAME];

  return 0;