USP_MKTSEGMENTATIONPASSIVESEGMENT_LOAD

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

  select 
    [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    [MKTSEGMENTATION].[SITEID],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[SOURCECODEID],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
    [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID] as [CODEVALUEID],
    [MKTSEGMENTATIONSEGMENT].[CODE],
    [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] else [MKTPACKAGE].[PARTDEFINITIONVALUESID] end as [PACKAGECODEVALUEID],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end as [PACKAGECODE],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELPARTDEFINITIONVALUESID] else [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] end as [CHANNELCODEVALUEID],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELSOURCECODE] else [MKTPACKAGE].[CHANNELSOURCECODE] end as [CHANNELCODE],
    [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
    [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
    [MKTSEGMENTATIONSEGMENT].[RESPONSERATE],
    [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT],
    [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then (select isnull(sum([RESPONSES]), 0) from dbo.[MKTSEGMENTATIONSEGMENTACTIVE] where [SEGMENTID] = @ID) else 0 end as [RESPONSES],
    case when [MKTSEGMENTATION].[ACTIVE] = 1 then dbo.[UFN_MKTSEGMENTATIONPACKAGEACTIVE_GETRESPONSECOUNT]([MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID], [MKTSEGMENTATIONPACKAGE].[PACKAGEID]) else 0 end as [PACKAGERESPONSES],
    [MKTSEGMENTATIONSEGMENT].[TSLONG],
    [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID] as [LISTCODEVALUEID],
    isnull([MKTRECORDSOURCE].[CODE], '') as [LISTCODE],
    dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[ID]) as [ITEMLIST]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

  return 0;