USP_DATALIST_MKTMATERIALEXPENSE

Displays a list of all marketing materials and expenses.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MATERIALTYPECODEID	uniqueidentifier	IN	Material type
@EXPENSETYPECODEID	uniqueidentifier	IN	Expense type
@VENDORID	uniqueidentifier	IN	Vendor
@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
@NAME	nvarchar(100)	IN

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTMATERIALEXPENSE]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATERIALTYPECODEID uniqueidentifier = null,
  @EXPENSETYPECODEID uniqueidentifier = null,
  @VENDORID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 0,
  @NAME nvarchar(100) = null
)
as
  set nocount on;

  declare @MATERIALEXPENSE table (
    [ID] uniqueidentifier not null,
    [MATERIALOREXPENSECODE] tinyint not null,
    [NAME] nvarchar(100) not null,
    [SITEID] uniqueidentifier null,
    [SITE] nvarchar(1024) null,
    [TYPECODEID] uniqueidentifier null,
    [TYPE] nvarchar(100) null,
    [VENDORID] uniqueidentifier null,
    [VENDOR] nvarchar(1024) null,
    [COST] money not null,
    [COSTDISTRIBUTIONMETHOD] nvarchar(100) not null,
    [DESCRIPTION] nvarchar(255) null,
    [CURRENCYID] uniqueidentifier null,
    [NUMPACKAGES] tinyint null)

  insert into @MATERIALEXPENSE
  select
    [MKTMATERIAL].[ID],
    1 as [MATERIALOREXPENSECODE],
    [MKTMATERIAL].[NAME],
    [MKTMATERIAL].[SITEID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTMATERIAL].[SITEID]) as [SITE],
    [MKTMATERIAL].[MATERIALTYPECODEID] as [TYPECODEID],
    [MKTMATERIALTYPECODE].[DESCRIPTION] as [TYPE],
    [MKTMATERIAL].[VENDORID],
    [CONSTITUENT].[NAME] as [VENDOR],
    case @CURRENCYCODE when 1 then [MKTMATERIAL].[ORGANIZATIONCOST] else [MKTMATERIAL].[COST] end [COST],
    [MKTMATERIAL].[COSTDISTRIBUTIONMETHOD],
    [MKTMATERIAL].[DESCRIPTION],
    case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]()
                       else [MKTMATERIAL].[BASECURRENCYID] end [CURRENCYID],
    (select count([MKTPACKAGEMATERIAL].[MATERIALID]) from dbo.[MKTPACKAGEMATERIAL] where [MKTPACKAGEMATERIAL].[MATERIALID] = [MKTMATERIAL].[ID]) [NUMPACKAGES]
  from dbo.[MKTMATERIAL]
  left outer join dbo.[MKTMATERIALTYPECODE] on [MKTMATERIALTYPECODE].[ID] = [MKTMATERIAL].[MATERIALTYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTMATERIAL].[VENDORID]
  where (@MATERIALTYPECODEID is null or [MKTMATERIAL].[MATERIALTYPECODEID] = @MATERIALTYPECODEID)
    and (@VENDORID is null or [MKTMATERIAL].[VENDORID] = @VENDORID)
    and (@NAME is null or [MKTMATERIAL].[NAME] = @NAME)
  union all
  select
    [MKTEXPENSE].[ID],
    2 as [MATERIALOREXPENSECODE],
    [MKTEXPENSE].[NAME],
    [MKTEXPENSE].[SITEID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTEXPENSE].[SITEID]) as [SITE],
    [MKTEXPENSE].[EXPENSETYPECODEID] as [TYPECODEID],
    [MKTEXPENSETYPECODE].[DESCRIPTION] as [TYPE],
    [MKTEXPENSE].[VENDORID],
    [CONSTITUENT].[NAME] as [VENDOR],
    case @CURRENCYCODE when 1 then [MKTEXPENSE].[ORGANIZATIONCOST] else [MKTEXPENSE].[COST] end [COST],
    [MKTEXPENSE].[COSTDISTRIBUTIONMETHOD],
    [MKTEXPENSE].[DESCRIPTION],
    case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]()
                       else [MKTEXPENSE].[BASECURRENCYID] end [CURRENCYID],
    (select count([MKTPACKAGEEXPENSE].[EXPENSEID]) from dbo.[MKTPACKAGEEXPENSE] where [MKTPACKAGEEXPENSE].[EXPENSEID] = [MKTEXPENSE].[ID]) [NUMPACKAGES]
  from dbo.[MKTEXPENSE]
  left outer join dbo.[MKTEXPENSETYPECODE] on [MKTEXPENSETYPECODE].[ID] = [MKTEXPENSE].[EXPENSETYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTEXPENSE].[VENDORID]
  where (@EXPENSETYPECODEID is null or [MKTEXPENSE].[EXPENSETYPECODEID] = @EXPENSETYPECODEID)
    and (@VENDORID is null or [MKTEXPENSE].[VENDORID] = @VENDORID)
    and (@NAME is null or [MKTEXPENSE].[NAME] = @NAME)

  select 
    [ID],
    [MATERIALOREXPENSECODE],
    [NAME],
    [SITE],
    [TYPE],
    [VENDOR],
    [COST],
    [COSTDISTRIBUTIONMETHOD],
    [DESCRIPTION],
    convert(nvarchar(36), [ID]) + '|' + convert(nvarchar(1), [MATERIALOREXPENSECODE]) as [EDITDATAFORMACTIONCONTEXT],
    [CURRENCYID],
    [NUMPACKAGES],
    [SITEID],
    [TYPECODEID],
    [VENDORID]
  from @MATERIALEXPENSE as [MATERIALEXPENSE]
  where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MATERIALEXPENSE].[SITEID] or (SITEID is null and [MATERIALEXPENSE].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MATERIALEXPENSE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MATERIALEXPENSE].[NAME];

  return 0;