UFN_MKTSOURCECODEPARTDEFINITION_GETITEMLIST

CREATE function dbo.[UFN_MKTSOURCECODEPARTDEFINITION_GETITEMLIST]
(
  @MKTSOURCECODEPARTDEFINITIONID uniqueidentifier
)
/*
Returns a table of all of the source code part definition items that have been assigned to the source code part.
*/
returns table
as
  return 
  (
    select 
      [VALUES].[ID], 
      [VALUES].[DESCRIPTION],
      [VALUES].[FORMAT],
      [VALUES].[VALUE],
      [VALUES].[SAMPLE],
      [VALUES].[DELIM],
      [VALUES].[REGEX],
      [VALUES].[LENGTH],
      cast((case
            when exists (select top 1 [MKTSEGMENTATION].[ID] from dbo.[MKTSEGMENTATION] where [MKTSEGMENTATION].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1 
            when exists (select top 1 [MKTSEGMENTATIONSEGMENT].[ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1 
            when exists (select top 1 [MKTSEGMENTATIONTESTSEGMENT].[ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [MKTSEGMENTATIONTESTSEGMENT].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1 
            when exists (select top 1 [MKTSOURCECODEPART].[ID] from dbo.[MKTSOURCECODEPART] where [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1 
            when exists (select top 1 [MKTSEGMENT].[ID] from dbo.[MKTSEGMENT] where [MKTSEGMENT].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTPACKAGE].[ID] from dbo.[MKTPACKAGE] where [MKTPACKAGE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTSEGMENTATIONPACKAGE].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTLIST].[ID] from dbo.[MKTLIST] where [MKTLIST].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTRECORDSOURCE].[ID] from dbo.[MKTRECORDSOURCE] where [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [MKTMEMBERSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ID] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [MKTSPONSORSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            when exists (select top 1 [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1
            else 0
            end) as bit) as [INUSE],
      cast((case when exists (select top 1 [MKTSOURCECODEVALIDPARTVALUES].[ID] from dbo.[MKTSOURCECODEVALIDPARTVALUES] where [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [VALUES].[ID]) then 1 
            else 0
            end) as bit) as [INUSEBYLAYOUT]
    from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [VALUES]
    where [VALUES].[MKTSOURCECODEPARTDEFINITIONID] = @MKTSOURCECODEPARTDEFINITIONID
  )