USP_MKTSEGMENTATIONTESTSEGMENT_GETDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_GETDATALIST]
(
  @SEGMENTATIONID uniqueidentifier,
  @MINIMUMSEQUENCE int = null,
  @MAXIMUMSEQUENCE int = null,
  @CURRENCYCODE tinyint = 0 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  if @MINIMUMSEQUENCE is null
    set @MINIMUMSEQUENCE = 0;

  if @MAXIMUMSEQUENCE is null
    set @MAXIMUMSEQUENCE = 2147483647;

  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  select
    [MKTSEGMENTATIONTESTSEGMENT].[ID],
    [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE],
    dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) as [Name],
    [SOURCECODE].[SOURCECODE],
    [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZE],
    [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZETYPECODE],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTPACKAGE].[CHANNEL],
    (
      (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
         when 0 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end
         when 4 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else [MKTPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT end else 0 end) +
       dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, @CURRENCYCODE) +
       -- Add any other package costs using 'Per thousand'

       dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, @CURRENCYCODE)
      + 
      (case [MKTSEGMENT].[SEGMENTTYPECODE] 
         when 2 then 
           dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], @CURRENCYCODE) +
           -- Add any other package costs using 'Per thousand'

           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, @CURRENCYCODE)
         else 0 
       end)
    ) as [COSTPERPIECE],
    case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
      when 1 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
    dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, @CURRENCYCODE)
    as [COSTPERRESPONSE],
    [MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT] else [MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT] end as [GIFTAMOUNT],
    [MKTSEGMENTATIONSEGMENT].[MARKETINGPLANBRIEFID],
    [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID],
    (case when [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] is null then '' else dbo.[UFN_MKTASKLADDER_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID]) end) as [ASKLADDER],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    -- calculate this segment's share of the package's cost per effort, which is split evenly among all segments in the mailing

    -- using this package, and will be rolled up into the segment's fixed cost

    (
     (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
       when 2 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
      dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, @CURRENCYCODE))
     /
     (isnull((select count([SS].[ID]) from dbo.[MKTSEGMENTATIONSEGMENT] as [SS] where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SS].[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]), 0) +
     (select count([SS].[ID]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS] inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SS] on [SS].[ID] = [STS].[SEGMENTID] where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [STS].[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]))
    ) as [FIXEDCOSTFROMPACKAGE],
    [MKTSEGMENTATIONTESTSEGMENT].[FRACTION],
    [MKTSEGMENTATION].[BASECURRENCYID],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE],
    [MKTSEGMENTATIONTESTSEGMENT].[TSLONG]
  from dbo.[MKTSEGMENTATIONTESTSEGMENT]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
  left outer join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
  left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  left join dbo.UFN_MKTSOURCECODE_BUILDCODESFORSEGMENTATION(@SEGMENTATIONID, null, 1) SOURCECODE on SOURCECODE.SEGMENTATIONSEGMENTID = [MKTSEGMENTATIONTESTSEGMENT].ID
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTATIONSEGMENT].[SEQUENCE] between @MINIMUMSEQUENCE and @MAXIMUMSEQUENCE
  order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE], [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE]
  option (RECOMPILE);


  return 0;