USP_DATALIST_MKTSOURCECODEITEMS

CREATE procedure dbo.[USP_DATALIST_MKTSOURCECODEITEMS]
(
  @SOURCECODEITEMID uniqueidentifier = null,
  @SOURCECODEID uniqueidentifier = null,
  @ITEMTYPECODE tinyint = null,
  @SEGMENTATIONID uniqueidentifier = null,
  @ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier = null,
  @MEMBERSHIPMAILINGTEMPLATEID uniqueidentifier = null,
  @SEGMENTATIONSEGMENTID uniqueidentifier = null
)
as
  set nocount on;

  select 
    [MKTSOURCECODEITEM].[ID],
    [MKTSOURCECODEPARTDEFINITION].[ID],
    [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID],
    [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], 
    [MKTSOURCECODEPARTDEFINITION].[NAME], 
    [MKTSOURCECODEITEM].[SEQUENCE],
    [MKTSOURCECODEPART].[CODE],
    [MKTSOURCECODEPART].[CHANNELCODE]
  from 
    dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    left join dbo.[MKTSOURCECODEPART] on ([MKTSOURCECODEPART].[SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] 
       and ([MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID 
            or [MKTSOURCECODEPART].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID
            or [MKTSOURCECODEPART].[MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID))
  where
    (@SOURCECODEID is null or [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID) and
    (@SOURCECODEITEMID is null or [MKTSOURCECODEITEM].[ID] = @SOURCECODEITEMID) and
    (@ITEMTYPECODE is null or [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = @ITEMTYPECODE) and
    ((@SEGMENTATIONSEGMENTID is null and [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] is null) 
      or [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID 
      or [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0)
  order by
    [MKTSOURCECODEITEM].[SEQUENCE];

  return 0;