USP_MKTSEGMENTATIONSEGMENT_GETDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_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
    [MKTSEGMENTATIONSEGMENT].[ID],
    [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
    [MKTSEGMENT].[NAME],
    [SOURCECODE].[SOURCECODE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTPACKAGE].[CHANNEL],
    cast ((
            (case [MKTSEGMENTATION].[ACTIVE] 
               when 1 then 
                 case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
                   when 0 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end
                   when 4 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] / @PACKAGEPERTHOUSANDAMOUNT else [MKTSEGMENTATIONPACKAGE].[UNITCOST] / @PACKAGEPERTHOUSANDAMOUNT end else 0 end +
                   case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] end
               else 
                 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)
             end)
            + 
            (case [MKTSEGMENT].[SEGMENTTYPECODE] 
               when 2 then 
                 dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], @CURRENCYCODE) 
               else 0 
             end)
         ) as decimal(30,8)) as [COSTPERPIECE],
    (case [MKTSEGMENTATION].[ACTIVE] 
       when 1 then 
         case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 1 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end else 0 end +
         case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE] end
       else 
         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)
     end)
    as [COSTPERRESPONSE],
    [MKTSEGMENTATIONSEGMENT].[RESPONSERATE],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT] else [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] end as [GIFTAMOUNT],
    [MKTSEGMENTATIONSEGMENT].[MARKETINGPLANBRIEFID],
    [MKTSEGMENTATIONSEGMENT].[NEXTBRIEFSEQUENCE],
    [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
    [MKTSEGMENTATIONSEGMENT].[COLLAPSED],
    (case when [MKTSEGMENTATIONSEGMENT].[ASKLADDERID] is null then '' else dbo.[UFN_MKTASKLADDER_GETNAME]([MKTSEGMENTATIONSEGMENT].[ASKLADDERID]) end) as [ASKLADDER],
    [MKTSEGMENT].[SEGMENTTYPECODE],
cast((case when [MKTSEGMENTLIST].[TYPECODE] = 1 then 1 else 0 end) as bit) as [ISVENDORMANAGED],
    -- 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 when [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0 then
       (case when [MKTSEGMENTATION].[ACTIVE] = 1 then
          (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
            when 2 then (case @CURRENCYCODE when 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end)
            else 0
           end)
          +
          (case @CURRENCYCODE when 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT] end)
        else
          (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
            when 2 then (case @CURRENCYCODE when 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end)
            else 0
           end)
          +
          dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, @CURRENCYCODE)
        end)
       /
       (
         (select count([SS].[ID]) from dbo.[MKTSEGMENTATIONSEGMENT] as [SS] where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SS].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID])
         +
         isnull((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] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]), 0)
       )
     else
       0
     end) as [FIXEDCOSTFROMPACKAGE],
    [MKTSEGMENTATION].[BASECURRENCYID],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE],
    [MKTSEGMENTATIONSEGMENT].[TSLONG],
    [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
    (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end) as [HISTORICALQUANTITY]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  left outer join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left outer join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[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, 0) as [SOURCECODE] on [SOURCECODE].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4 -- so this can be used with reports that don't differentiate

  and [MKTSEGMENTATIONSEGMENT].[SEQUENCE] between @MINIMUMSEQUENCE and @MAXIMUMSEQUENCE
  order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE]
  option(recompile);

  return 0;