UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2

CREATE function dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]
(
  @ID uniqueidentifier,
  @CURRENCYCODE tinyint -- 0 = base, 1 = organization

)
-- return a large number of decimal places, rather than a money data type, to

-- minimize rounding errors that accumulate when expenses are summed

returns numeric(30, 20)
as begin
  declare @SEGMENTATIONID uniqueidentifier;
  declare @LISTID uniqueidentifier;
  declare @OVERRIDEQUANTITIESANDORSEGMENTCOSTS bit;
  declare @OVERRIDELISTCOSTS bit;
  declare @BASERENTALCOST money;
  declare @BASERENTALCOSTBASISCODE tinyint; -- 1 = per thousand, 2 = flat

  declare @TOTALRENTALQUANTITY integer;
  declare @BASERENTALCOSTPERRECORD numeric(30, 20);
  declare @TOTALEXCHANGEQUANTITY integer;
  declare @BASEEXCHANGECOSTPERRECORD numeric(30, 20);
  declare @RENTALQUANTITY integer;
  declare @RENTALCOSTADJUSTMENT money;
  declare @RENTALCOSTADJUSTMENTBASISCODE tinyint; --1 = per thousand, 2 = flat

  declare @SEGMENTRENTALCOST numeric(30, 20);
  declare @BASEEXCHANGECOST money;
  declare @BASEEXCHANGECOSTBASISCODE tinyint; -- 1 = per thousand, 2 = flat

  declare @EXCHANGEQUANTITY integer;
  declare @EXCHANGECOSTADJUSTMENT money;
  declare @EXCHANGECOSTADJUSTMENTBASISCODE tinyint; -- 1 = per thousand, 2 = flat

  declare @SEGMENTEXCHANGECOST numeric(30, 20);
  declare @COSTPERRECORD numeric(30, 20);

  declare @ORGANIZATIONBASERENTALCOST money;
  declare @ORGANIZATIONBASERENTALCOSTPERRECORD numeric(30, 20);
  declare @ORGANIZATIONBASEEXCHANGECOSTPERRECORD numeric(30, 20);
  declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
  declare @ORGANIZATIONSEGMENTRENTALCOST numeric(30, 20);
  declare @ORGANIZATIONBASEEXCHANGECOST money;
  declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
  declare @ORGANIZATIONSEGMENTEXCHANGECOST numeric(30, 20);

  set @COSTPERRECORD = 0;

  select
    @LISTID = [MKTSEGMENTLIST].[LISTID],
    @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = (case when ([MKTSEGMENTATION].[ACTIVE] = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1) then 1 else 0 end),
    @OVERRIDELISTCOSTS = (case when ([MKTSEGMENTATION].[ACTIVE] = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1) then 1 else 0 end)
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

  if @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1
    select 
      @RENTALQUANTITY = [RENTALQUANTITY],
      @RENTALCOSTADJUSTMENT = [RENTALCOSTADJUSTMENT],
      @RENTALCOSTADJUSTMENTBASISCODE = [RENTALCOSTADJUSTMENTBASISCODE],
      @EXCHANGEQUANTITY = [EXCHANGEQUANTITY],
      @EXCHANGECOSTADJUSTMENT = [EXCHANGECOSTADJUSTMENT],
      @EXCHANGECOSTADJUSTMENTBASISCODE = [EXCHANGECOSTADJUSTMENTBASISCODE],
      @ORGANIZATIONRENTALCOSTADJUSTMENT = [ORGANIZATIONRENTALCOSTADJUSTMENT],
      @ORGANIZATIONEXCHANGECOSTADJUSTMENT = [ORGANIZATIONEXCHANGECOSTADJUSTMENT]
    from dbo.[MKTSEGMENTATIONSEGMENTLIST]
    where [ID] = @ID
  else
    select 
      @RENTALQUANTITY = [MKTSEGMENTLIST].[RENTALQUANTITY],
      @RENTALCOSTADJUSTMENT = [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
      @RENTALCOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
      @EXCHANGEQUANTITY = [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
      @EXCHANGECOSTADJUSTMENT = [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
      @EXCHANGECOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
      @ORGANIZATIONRENTALCOSTADJUSTMENT = [MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT],
      @ORGANIZATIONEXCHANGECOSTADJUSTMENT = [MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

  if (@RENTALQUANTITY + @EXCHANGEQUANTITY) > 0
    begin
      if @OVERRIDELISTCOSTS = 1
        select 
          @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
          @BASERENTALCOST = [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST],
          @BASERENTALCOSTBASISCODE = [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOSTBASISCODE],
          @BASEEXCHANGECOST = [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST],
          @BASEEXCHANGECOSTBASISCODE = [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOSTBASISCODE],
          @ORGANIZATIONBASERENTALCOST = [MKTSEGMENTATIONSEGMENTLIST].[ORGANIZATIONBASERENTALCOST],
          @ORGANIZATIONBASEEXCHANGECOST = [MKTSEGMENTATIONSEGMENTLIST].[ORGANIZATIONBASEEXCHANGECOST]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
        where [MKTSEGMENTATIONSEGMENT].[ID] = @ID
      else
        select 
          @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
          @BASERENTALCOST = [MKTLIST].[BASERENTALCOST],
          @BASERENTALCOSTBASISCODE = [MKTLIST].[BASERENTALCOSTBASISCODE],
          @BASEEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
          @BASEEXCHANGECOSTBASISCODE = [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
          @ORGANIZATIONBASERENTALCOST = [MKTLIST].[ORGANIZATIONBASERENTALCOST],
          @ORGANIZATIONBASEEXCHANGECOST = [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
        where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

      set @BASERENTALCOSTPERRECORD = 0;
      set @BASEEXCHANGECOSTPERRECORD = 0;
      set @SEGMENTRENTALCOST = 0;
      set @SEGMENTEXCHANGECOST = 0;
      set @ORGANIZATIONBASERENTALCOSTPERRECORD = 0;
      set @ORGANIZATIONBASEEXCHANGECOSTPERRECORD = 0;
      set @ORGANIZATIONSEGMENTRENTALCOST = 0;
      set @ORGANIZATIONSEGMENTEXCHANGECOST = 0;

      if @RENTALQUANTITY > 0
        begin
          if @BASERENTALCOSTBASISCODE = 1
            begin
              -- divide the cost by 1000

              set @BASERENTALCOSTPERRECORD = convert(numeric(30, 20), @BASERENTALCOST) / 1000;
              set @ORGANIZATIONBASERENTALCOSTPERRECORD = convert(numeric(30, 20), @ORGANIZATIONBASERENTALCOST) / 1000;
            end
          else -- 2 = flat

            begin
              -- divide the cost by the number of rental records for this list in the entire mailing

              select
                @TOTALRENTALQUANTITY = isnull(sum(case when @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else [MKTSEGMENTLIST].[RENTALQUANTITY] end), 0)
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
              inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              and [MKTSEGMENTLIST].[LISTID] = @LISTID;

              -- don't need to check for division by zero because total is going to include @RENTALQUANTITY, which is > 0

              set @BASERENTALCOSTPERRECORD = convert(numeric(30, 20), @BASERENTALCOST) / @TOTALRENTALQUANTITY;
              set @ORGANIZATIONBASERENTALCOSTPERRECORD = convert(numeric(30, 20), @ORGANIZATIONBASERENTALCOST) / @TOTALRENTALQUANTITY;
            end

          if @RENTALCOSTADJUSTMENTBASISCODE = 1 -- per thousand

            begin
              set @SEGMENTRENTALCOST = convert(numeric(30, 20), @RENTALCOSTADJUSTMENT) * (convert(numeric(30, 20), @RENTALQUANTITY) / 1000);
              set @ORGANIZATIONSEGMENTRENTALCOST = convert(numeric(30, 20), @ORGANIZATIONRENTALCOSTADJUSTMENT) * (convert(numeric(30, 20), @RENTALQUANTITY) / 1000);
            end
          else
            begin
              set @SEGMENTRENTALCOST = convert(numeric(30, 20), @RENTALCOSTADJUSTMENT);
              set @ORGANIZATIONSEGMENTRENTALCOST = convert(numeric(30, 20), @ORGANIZATIONRENTALCOSTADJUSTMENT);
            end
        end

      if @EXCHANGEQUANTITY > 0
        begin
          if @BASEEXCHANGECOSTBASISCODE = 1
            begin
              -- divide the cost by 1000

              set @BASEEXCHANGECOSTPERRECORD = @BASEEXCHANGECOST / 1000;
              set @ORGANIZATIONBASEEXCHANGECOSTPERRECORD = @ORGANIZATIONBASEEXCHANGECOST / 1000;
            end
          else -- 2 = flat

            -- divide the cost by the number of exchange records for this list in the entire mailing

            begin
              select
                @TOTALEXCHANGEQUANTITY = isnull(sum(case when @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else [MKTSEGMENTLIST].[EXCHANGEQUANTITY] end), 0)
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
              inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              and [MKTSEGMENTLIST].[LISTID] = @LISTID;

              -- don't need to check for division by zero because total is going to include @EXCHANGEQUANTITY, which is > 0

              set @BASEEXCHANGECOSTPERRECORD = @BASEEXCHANGECOST / @TOTALEXCHANGEQUANTITY;
              set @ORGANIZATIONBASEEXCHANGECOSTPERRECORD = @ORGANIZATIONBASEEXCHANGECOST / @TOTALEXCHANGEQUANTITY;
            end

          if @EXCHANGECOSTADJUSTMENTBASISCODE = 1 -- per thousand

            begin
              set @SEGMENTEXCHANGECOST = convert(numeric(30, 20), @EXCHANGECOSTADJUSTMENT) * (convert(numeric(30, 20), @EXCHANGEQUANTITY) / 1000);
              set @ORGANIZATIONSEGMENTEXCHANGECOST = convert(numeric(30, 20), @ORGANIZATIONEXCHANGECOSTADJUSTMENT) * (convert(numeric(30, 20), @EXCHANGEQUANTITY) / 1000);
            end
          else
            begin
              set @SEGMENTEXCHANGECOST = convert(numeric(30, 20), @EXCHANGECOSTADJUSTMENT);
              set @ORGANIZATIONSEGMENTEXCHANGECOST = convert(numeric(30, 20), @ORGANIZATIONEXCHANGECOSTADJUSTMENT);
            end
        end

      -- base cost + additional cost of the segment, averaged across the total number of records in the segment

      if @CURRENCYCODE = 1
        set @COSTPERRECORD = (((@ORGANIZATIONBASERENTALCOSTPERRECORD * @RENTALQUANTITY) + (@ORGANIZATIONBASEEXCHANGECOSTPERRECORD * @EXCHANGEQUANTITY) + @ORGANIZATIONSEGMENTRENTALCOST + @ORGANIZATIONSEGMENTEXCHANGECOST) / (@RENTALQUANTITY + @EXCHANGEQUANTITY));
      else
        set @COSTPERRECORD = (((@BASERENTALCOSTPERRECORD * @RENTALQUANTITY) + (@BASEEXCHANGECOSTPERRECORD * @EXCHANGEQUANTITY) + @SEGMENTRENTALCOST + @SEGMENTEXCHANGECOST) / (@RENTALQUANTITY + @EXCHANGEQUANTITY));

    end

  return @COSTPERRECORD;
end