USP_MARKETINGEXPENSESEARCH

Search for Marketing Expenses

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
@EXPENSETYPECODEID	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_MARKETINGEXPENSESEARCH]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @MAXROWS smallint,
  @NAME nvarchar(100) = null,
  @EXPENSETYPECODEID 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)
    [MKTEXPENSE].[ID],
    [MKTEXPENSE].[NAME],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTEXPENSE].[SITEID]) as [SITE],
    [MKTEXPENSETYPECODE].[DESCRIPTION] as [EXPENSETYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTEXPENSE].[COST],
    [MKTEXPENSE].[COSTDISTRIBUTIONMETHOD],
    [MKTEXPENSE].[DESCRIPTION],
    [MKTEXPENSE].[BASECURRENCYID]
  from dbo.[MKTEXPENSE]
  left outer join dbo.[MKTEXPENSETYPECODE] on [MKTEXPENSETYPECODE].[ID] = [MKTEXPENSE].[EXPENSETYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTEXPENSE].[VENDORID]
  where (@NAME is null or [MKTEXPENSE].[NAME] like @NAME + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and (@SITEID is null or [MKTEXPENSE].[SITEID] = @SITEID)
    and (@SITEIDISNULL = 0 or (@SITEIDISNULL = 1 and [MKTEXPENSE].[SITEID] is null))
    and (@EXPENSETYPECODEID is null or [MKTEXPENSE].[EXPENSETYPECODEID] = @EXPENSETYPECODEID)
    and (@VENDOR is null or [CONSTITUENT].[NAME] like @VENDOR + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTEXPENSE].[SITEID]) = 1
    and (@BASECURRENCYID is null or [MKTEXPENSE].[BASECURRENCYID] = @BASECURRENCYID)
  order by [MKTEXPENSE].[NAME];

  return 0;