USP_DATALIST_MKTSOURCECODELAYOUT

CREATE procedure dbo.[USP_DATALIST_MKTSOURCECODELAYOUT]
(
  @ID uniqueidentifier = null
)
as
  set nocount on;

  select 
    [MKTSOURCECODEITEM].[ID] as [ID],
    null as [PARENTID],
    [MKTSOURCECODEPARTDEFINITION].[ID] as [SOURCECODEPARTDEFINITIONID],
    [MKTSOURCECODEPARTDEFINITION].[NAME],
    null as [CODE],
    null as [DELIM],
    [MKTSOURCECODEPARTDEFINITION].[DESCRIPTION],
      dbo.[UFN_MKTSOURCECODE_GETPOSITION]([MKTSOURCECODEITEM].[ID]) as [POSITION],
      [MKTSOURCECODEPARTDEFINITION].[ITEMTYPE],
    [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
    [MKTSOURCECODEITEM].[SEQUENCE],
    [MKTSOURCECODEITEM].[ID],
    (case when exists (select 1 from dbo.[UFN_MKTSOURCECODEPARTDEFINITION_GETITEMLIST]([MKTSOURCECODEPARTDEFINITION].[ID]) where [INUSE] = 1) then 1 else 0 end) as [PARTVALUEINUSE]
  from 
    dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
  where [MKTSOURCECODEITEM].[SOURCECODEID] = @ID

union

  select 
    [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [ID],
    [MKTSOURCECODEITEM].[ID] as [PARENTID],
    null as [SOURCECODEPARTDEFINITIONID],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[SAMPLE] as [CODE],
    [MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM] as [DELIM],
    null as [DESCRIPTION],
    null as [POSITION],
    null as [ITEMTYPE],
    null as [ITEMTYPECODE],
    0 as [SEQUENCE],
    null as [SOURCECODEITEMID],
    null as [PARTVALUEINUSE]
  from 
    dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
    inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
  where [MKTSOURCECODEITEM].[SOURCECODEID] = @ID
  order by [MKTSOURCECODEITEM].[SEQUENCE];

  return 0;