USP_DATALIST_MKTPACKAGE

Displays a list of all packages.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANNELCODE	tinyint	IN	Channel
@CATEGORYCODEID	uniqueidentifier	IN	Category
@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.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTPACKAGE]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CHANNELCODE tinyint = null,
  @CATEGORYCODEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null  
)
as
  set nocount on;

  select
    [MKTPACKAGE].[ID],
    [MKTPACKAGE].[NAME],
    [MKTPACKAGE].[CODE],
    [MKTPACKAGE].[UNITCOST],
    [MKTPACKAGE].[DESCRIPTION],
    dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME([SITEID]) [SITE],
    [MKTPACKAGE].[CHANNEL],
    [MKTPACKAGE].[CHANNELCODE],
    [MKTPACKAGECATEGORYCODE].[DESCRIPTION] [CATEGORY]
  from
    dbo.[MKTPACKAGE]
  left outer join
    dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [MKTPACKAGE].[PACKAGECATEGORYCODEID]
  where 
    (@CHANNELCODE is null or @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE])
  and 
    (@CATEGORYCODEID is null or @CATEGORYCODEID = [MKTPACKAGE].[PACKAGECATEGORYCODEID])
    and (
              (
                  ( -- check site security

                      select count(*) 
            from
              (select [SITEID]
               from dbo.[MKTPACKAGE] as [PACKAGESITE]
               where [PACKAGESITE].[ID] = [MKTPACKAGE].[ID]) 
            as [PACKAGESITE]
                      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PACKAGESITE].[SITEID] or (SITEID is null and [PACKAGESITE].[SITEID] is null)))
                  ) > 0
              )
            and 
        ( -- apply site filter

                @SITEFILTERMODE = 0
                  or [MKTPACKAGE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
              )
        )    
  order by
    [NAME];

  return 0;