USP_SOURCECODEITEM_GETDATALIST

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

  select 
    [MKTSOURCECODEITEM].[ID] as [MKTSOURCECODEITEMID],
    [MKTSOURCECODEPARTDEFINITION].[ID] as [PARTDEFINITIONID],
    case 
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @SEGMENTATIONID is not null then [MKTSEGMENTATION].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @ACKNOWLEDGEMENTMAILINGTEMPLATEID is not null then [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @MEMBERSHIPMAILINGTEMPLATEID is not null then [MKTMEMBERSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @SPONSORSHIPMAILINGTEMPLATEID is not null then [MKTSPONSORSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @WHITEMAILSEGMENTID is not null then case when [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] is not null then [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] else [MKTSEGMENTATION].[PARTDEFINITIONVALUESID] end
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1 and @WHITEMAILSEGMENTID is not null then [MKTSEGMENT].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] in (2, 3, 6, 7) and @WHITEMAILSEGMENTID is not null then [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5 then [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
    end as [PARTDEFINITIONVALUESID],
    [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] as [ITEMTYPECODE], 
    [MKTSOURCECODEPARTDEFINITION].[NAME] as [NAME], 
    [MKTSOURCECODEITEM].[SEQUENCE] as [SEQUENCE],
    case 
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @SEGMENTATIONID is not null then [MKTSEGMENTATION].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @ACKNOWLEDGEMENTMAILINGTEMPLATEID is not null then [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @MEMBERSHIPMAILINGTEMPLATEID is not null then [MKTMEMBERSHIPMAILINGTEMPLATE].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @SPONSORSHIPMAILINGTEMPLATEID is not null then [MKTSPONSORSHIPMAILINGTEMPLATE].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and @WHITEMAILSEGMENTID is not null then case when [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID] is not null then [MKTSOURCECODEPART].[CODE] else [MKTSEGMENTATION].[CODE] end
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1 and @WHITEMAILSEGMENTID is not null then [MKTSEGMENT].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] in (2, 3, 6, 7) and @WHITEMAILSEGMENTID is not null then [MKTSOURCECODEPART].[CODE]
      when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5 then [MKTSOURCECODEPART].[CODE]
    end as [CODE],
    [MKTSOURCECODEPART].[CHANNELCODE] as [CHANNELCODE], 
    [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] as [SEGMENTATIONSEGMENTID],
    [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] as [SEGMENTATIONTESTSEGMENTID],
    [MKTSOURCECODEPART].[WHITEMAILSEGMENTID] as [WHITEMAILSEGMENTID]
  from 
    dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    left join dbo.[MKTSEGMENTATION] on ([MKTSEGMENTATION].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and [MKTSEGMENTATION].[ID] = @SEGMENTATIONID)
    left join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] on ([MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID)
    left join dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] on ([MKTMEMBERSHIPMAILINGTEMPLATE].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] = @MEMBERSHIPMAILINGTEMPLATEID)
    left join dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on ([MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0 and [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = @SPONSORSHIPMAILINGTEMPLATEID)
    left join dbo.[MKTSEGMENTWHITEMAIL] on ([MKTSEGMENTWHITEMAIL].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1 and [MKTSEGMENTWHITEMAIL].[ID] = @WHITEMAILSEGMENTID)
    left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
    left join dbo.[MKTSOURCECODEPART] on ([MKTSOURCECODEPART].[SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
                                          and 
                                          ([MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID 
                                           or [MKTSOURCECODEPART].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID
                                           or [MKTSOURCECODEPART].[MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID
                                           or [MKTSOURCECODEPART].[SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID
                                           or [MKTSOURCECODEPART].[MARKETINGPLANITEMID] = @MARKETINGPLANITEMID
                                           or [MKTSOURCECODEPART].[WHITEMAILSEGMENTID] = @WHITEMAILSEGMENTID))
  where
    ([MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID) and
    (@SOURCECODEITEMID is null or [MKTSOURCECODEITEM].[ID] = @SOURCECODEITEMID) and
    (@ITEMTYPECODE is null or [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = @ITEMTYPECODE)
  order by
    [MKTSOURCECODEITEM].[SEQUENCE];

  return 0;