USP_DATALIST_MKTVENDORMATERIALEXPENSE

Displays a list of all materials and expenses 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.
@MATERIALTYPECODEID	uniqueidentifier	IN	Material type
@EXPENSETYPECODEID	uniqueidentifier	IN	Expense 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_MKTVENDORMATERIALEXPENSE]
(
  @VENDORID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATERIALTYPECODEID uniqueidentifier = null,
  @EXPENSETYPECODEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 0
)
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,
    [TYPE] nvarchar(100) null,
    [COST] money not null,
    [COSTDISTRIBUTIONMETHOD] nvarchar(100) not null,
    [DESCRIPTION] nvarchar(255) null,
    [CURRENCYID] uniqueidentifier null)

  insert into @MATERIALEXPENSE
  select
    [MKTMATERIAL].[ID],
    1 as [MATERIALOREXPENSECODE],
    [MKTMATERIAL].[NAME],
    [MKTMATERIAL].[SITEID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTMATERIAL].[SITEID]) as [SITE],
    [MKTMATERIALTYPECODE].[DESCRIPTION] as [TYPE],
    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]
  from dbo.[MKTMATERIAL]
  left outer join dbo.[MKTMATERIALTYPECODE] on [MKTMATERIALTYPECODE].[ID] = [MKTMATERIAL].[MATERIALTYPECODEID]
  where (@MATERIALTYPECODEID is null or [MKTMATERIAL].[MATERIALTYPECODEID] = @MATERIALTYPECODEID)
    and [MKTMATERIAL].[VENDORID] = @VENDORID
  union all
  select
    [MKTEXPENSE].[ID],
    2 as [MATERIALOREXPENSECODE],
    [MKTEXPENSE].[NAME],
    [MKTEXPENSE].[SITEID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTEXPENSE].[SITEID]) as [SITE],
    [MKTEXPENSETYPECODE].[DESCRIPTION] as [TYPE],
    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]
  from dbo.[MKTEXPENSE]
  left outer join dbo.[MKTEXPENSETYPECODE] on [MKTEXPENSETYPECODE].[ID] = [MKTEXPENSE].[EXPENSETYPECODEID]
  where (@EXPENSETYPECODEID is null or [MKTEXPENSE].[EXPENSETYPECODEID] = @EXPENSETYPECODEID)
    and [MKTEXPENSE].[VENDORID] = @VENDORID

  select 
    [ID],
    [MATERIALOREXPENSECODE],
    [NAME],
    [SITE],
    [TYPE],
    [COST],
    [COSTDISTRIBUTIONMETHOD],
    [DESCRIPTION],
    [CURRENCYID]
  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;