USP_DATALIST_MKTSOURCECODEPARTDEFINITIONVALUES

Displays a list of the source code part values associated with a source code part definition.

Parameters

Parameter	Parameter Type	Mode	Description
@PARTDEFINITIONID	uniqueidentifier	IN
@PARTDEFINITIONVALUESID	uniqueidentifier	IN
@VALIDVALUESONLY	bit	IN
@MAILINGTYPECODE	tinyint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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_MKTSOURCECODEPARTDEFINITIONVALUES]
(
  @PARTDEFINITIONID uniqueidentifier = null,
  @PARTDEFINITIONVALUESID uniqueidentifier = null,
  @VALIDVALUESONLY bit = 0,
  @MAILINGTYPECODE tinyint = 0,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

with [VALIDPARTSITES] as (
  select distinct 
    [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] as [PARTDEFINITIONVALUESID]
  from [MKTSOURCECODEITEM] 
  inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSOURCECODEITEM].[SOURCECODEID]
  inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
  where 
    [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = @PARTDEFINITIONID
    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSOURCECODE].[SITEID] or (SITEID is null and [MKTSOURCECODE].[SITEID] is null)))
)
  select
    [MKTSOURCECODEPARTDEFINITIONVALUES].[ID],
    [MKTSOURCECODEPARTDEFINITION].[NAME],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[REGEX],
    [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM],
    case when [MKTSOURCECODEPARTDEFINITION].[AUTOINCREMENTVALUE] = 1 then dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE]([MKTSOURCECODEPARTDEFINITIONVALUES].[ID], @MAILINGTYPECODE) else '' end as [NEXTCODE],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[SAMPLE],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH]
  from dbo.[MKTSOURCECODEPARTDEFINITION]
  inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on ([MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] and [MKTSOURCECODEPARTDEFINITION].[ID] = @PARTDEFINITIONID)
  left join [VALIDPARTSITES] on [VALIDPARTSITES].[PARTDEFINITIONVALUESID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]
  where 
    (@VALIDVALUESONLY = 0 and (@PARTDEFINITIONVALUESID is null or [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID))
    or
    (@VALIDVALUESONLY = 1 and exists(select 1 from [VALIDPARTSITES] where [VALIDPARTSITES].[PARTDEFINITIONVALUESID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]));

  return 0;