USP_SIMPLEDATALIST_MKTSOURCECODEPARTDEFINITIONVALUES

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTSOURCECODEPARTDEFINITIONVALUES]
(
  @PARTDEFINITIONID uniqueidentifier,
  @ONLYSPECIALCHARACTERFORMATS bit = 0,
  @VALIDVALUESONLY bit = 0,
  @SOURCECODEID uniqueidentifier = null
)
as
  set nocount on;

  with [VALIDVALUES] as (
    -- to get values associated with a particular source code layout, or, if not a particular source code layout, then all ACTIVE source code layouts

    select 
      [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] as [ID]
    from dbo.[MKTSOURCECODEVALIDPARTVALUES] 
    inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID]
    inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSOURCECODEITEM].[SOURCECODEID]
    where isnull(@SOURCECODEID, [MKTSOURCECODE].[ID]) = [MKTSOURCECODE].[ID]
    and case when @SOURCECODEID is null then [MKTSOURCECODE].[ISACTIVE] else cast(1 as bit) end = 1
  )
  select
    [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [VALUE],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [LABEL]
  from dbo.[MKTSOURCECODEPARTDEFINITION]
    inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] and [MKTSOURCECODEPARTDEFINITION].[ID] = @PARTDEFINITIONID
  where
    ((@VALIDVALUESONLY = 0 and @SOURCECODEID is null) or exists (select top 1 1 from [VALIDVALUES] where [VALIDVALUES].[ID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]))
    and (@ONLYSPECIALCHARACTERFORMATS = 0 or ([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT] like '%@%' or [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT] like '%#%' or [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT] like '%?%'))
  order by [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION];

  return 0;