USP_DATALIST_MKTLISTSUMMARY

A datalist of lists for summary report.

Parameters

Parameter	Parameter Type	Mode	Description
@LISTCATEGORYCODEID	uniqueidentifier	IN	Category
@VENDORID	uniqueidentifier	IN	Vendor
@STATUS	tinyint	IN	Status
@CURRENCYCODE	tinyint	IN	Currency
@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.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTLISTSUMMARY]
(
  @LISTCATEGORYCODEID uniqueidentifier = null,
  @VENDORID uniqueidentifier = null,
  @STATUS tinyint = null,
  @CURRENCYCODE tinyint = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @SELECTEDCURRENCYID uniqueidentifier;
  if isnull(@CURRENCYCODE, 0) = 1
  begin
    set @SELECTEDCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  end

  select
    [MKTLIST].[ID],
    [MKTLIST].[NAME],
    [MKTLIST].[DESCRIPTION],
    [MKTLISTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    [MKTLIST].[CODE],
    [MKTLIST].[VENDORID],
    [CONSTITUENT].[NAME] as [VENDOR],
    case @CURRENCYCODE
      when 1 then [MKTLIST].[ORGANIZATIONBASERENTALCOST]
      else [MKTLIST].[BASERENTALCOST] end [BASERENTALCOST],
    [MKTLIST].[BASERENTALCOSTBASISCODE],
    case @CURRENCYCODE
      when 1 then [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST]
      else [MKTLIST].[BASEEXCHANGECOST] end [BASEEXCHANGECOST],
    [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
    [MKTLIST].[ISINACTIVE],
    [CURRENCYPROPERTIES].[ISO4217] [ISOCURRENCYCODE],
    [CURRENCYPROPERTIES].[CURRENCYSYMBOL],
    [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE] [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    [CURRENCYPROPERTIES].[DECIMALDIGITS]
  from dbo.[MKTLIST]
  left join dbo.[MKTLISTCATEGORYCODE] on [MKTLISTCATEGORYCODE].[ID] = [MKTLIST].[LISTCATEGORYCODEID]
  left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTLIST].[VENDORID]
  outer apply dbo.[UFN_CURRENCY_GETPROPERTIES](isnull(@SELECTEDCURRENCYID, [MKTLIST].[BASECURRENCYID])) [CURRENCYPROPERTIES]
  where (@LISTCATEGORYCODEID is null or [MKTLIST].[LISTCATEGORYCODEID] = @LISTCATEGORYCODEID)
  and (@VENDORID is null or [MKTLIST].[VENDORID] = @VENDORID)
  and (@STATUS is null or @STATUS = 0 or (@STATUS = 1 and [MKTLIST].[ISINACTIVE] = 0) or (@STATUS = 2 and [MKTLIST].[ISINACTIVE] = 1))
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTLIST].[SITEID] or (SITEID is null and [MKTLIST].[SITEID] is null)))
  order by [MKTLIST].[NAME];

  return 0;