USP_MKTSEGMENTATIONSEGMENTLIST_SAVE

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTLIST_SAVE]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEGMENTID uniqueidentifier,
  @USAGECODE tinyint,
  @CODE nvarchar(10),
  @TESTSEGMENTCODE nvarchar(10),
  @OVERRIDEQUANTITIESANDORSEGMENTCOSTS bit,
  @RENTALQUANTITY integer,
  @RENTALCOSTADJUSTMENT money,
  @RENTALCOSTADJUSTMENTBASISCODE tinyint,
  @EXCHANGEQUANTITY integer,
  @EXCHANGECOSTADJUSTMENT money,
  @EXCHANGECOSTADJUSTMENTBASISCODE tinyint,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @RESPONSERATE decimal(5,2),
  @GIFTAMOUNT money,
  @SAMPLESIZE int,
  @SAMPLESIZETYPECODE tinyint,
  @SAMPLESIZEMETHODCODE tinyint,
  @SAMPLESIZEEXCLUDEREMAINDER bit,
  @ASKLADDERID uniqueidentifier,
  @CODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @ITEMLIST xml = null,
  @CHANNELSOURCECODE nvarchar(10) = '',
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null,
  @LISTCODE nvarchar(10) = '',
  @LISTCODEVALUEID uniqueidentifier = null,
  @OVERRIDEBUSINESSUNITS bit = 0,
  @BUSINESSUNITS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONACTIVE bit;
  declare @QUANTITIESANDORVARIABLECOSTSWEREOVERRIDDEN bit;
  declare @CURRENTDATE datetime;
  declare @RECEIVEDVIACODE tinyint;
  declare @BASERENTALCOST money;
  declare @BASERENTALCOSTBASISCODE tinyint;
  declare @BASEEXCHANGECOST money;
  declare @BASEEXCHANGECOSTBASISCODE tinyint;

  begin try
    select
      @SEGMENTATIONACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @QUANTITIESANDORVARIABLECOSTSWEREOVERRIDDEN = isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    left outer join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    --Don't allow the user to set the sample size fields for a vendor managed segment...

    if dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST](@SEGMENTID) = 1
      begin
        set @SAMPLESIZE = 100;
        set @SAMPLESIZETYPECODE = 0;
        set @SAMPLESIZEMETHODCODE = 0;
        set @SAMPLESIZEEXCLUDEREMAINDER = 1;
      end

    exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_8]
      @ID,
      @CHANGEAGENTID,
      @SEGMENTID,
      @CODE,
      @TESTSEGMENTCODE,
      @PACKAGEID,
      @PACKAGECODE,
      @RESPONSERATE,
      @GIFTAMOUNT,
      @SAMPLESIZE,
      @SAMPLESIZETYPECODE,
      @SAMPLESIZEMETHODCODE,
      @SAMPLESIZEEXCLUDEREMAINDER,
      @ASKLADDERID,
      0,
      0,
      null,
      0,
      null,
      null,
      @CODEVALUEID,
      @PACKAGECODEVALUEID,
      @TESTSEGMENTCODEVALUEID,
      @ITEMLIST,
      @CHANNELSOURCECODE,
      @CHANNELSOURCECODEVALUEID,
      0,
      @OVERRIDEBUSINESSUNITS,
      @BUSINESSUNITS,
      @CURRENTAPPUSERID,
      0;

    /* Update the list with the selected code */
    declare @OLDLISTCODE nvarchar(10);
    declare @OLDLISTCODEVALUEID uniqueidentifier;
    declare @LISTID uniqueidentifier;

    /* Get the list ID for the segment */
    select
      @LISTID = [MKTSEGMENTLIST].[LISTID]
    from dbo.[MKTSEGMENT]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;

    select
      @OLDLISTCODE = isnull([CODE], ''),
      @OLDLISTCODEVALUEID = [PARTDEFINITIONVALUESID],
      @CODE = isnull(@LISTCODE, '')
    from dbo.[MKTLIST]
    where [ID] = @LISTID;

    /* Update the segment code if the old code value ID is nothing and the segment code value ID is supplied */
    if @LISTCODEVALUEID is not null and (@OLDLISTCODEVALUEID is null or @OLDLISTCODE <> @LISTCODE)
      update dbo.[MKTLIST] set
        [CODE] = @LISTCODE,
        [PARTDEFINITIONVALUESID] = @LISTCODEVALUEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @LISTID;

    /* save the usage code */
    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [USAGECODE] = @USAGECODE,
      [OVERRIDEBUSINESSUNITS] = @OVERRIDEBUSINESSUNITS,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    /* business units */
    exec dbo.USP_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_UPDATEFROMXML @ID, @BUSINESSUNITS, @CHANGEAGENTID;  

    /* an activated mailing is always in an overridden state - any segment that didn't have its values overridden by 
       the client before activation will have a row in MKTSEGMENTATIONSEGMENTLIST with OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 0
       which was created by the activation
       (it's done this way so that the ROLLBACK sp knows which rows in this table to delete if activation fails) */

    if @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1
      begin
        if @QUANTITIESANDORVARIABLECOSTSWEREOVERRIDDEN <> @OVERRIDEQUANTITIESANDORSEGMENTCOSTS
          begin
            -- override was set from false to true

            -- row might already exist because a list cost was overridden

            if not exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [ID] = @ID) 
              insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
                [ID],
                [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
                [BASERENTALCOST],
                [BASERENTALCOSTBASISCODE],
                [RENTALQUANTITY],
                [RENTALCOSTADJUSTMENT],
                [RENTALCOSTADJUSTMENTBASISCODE],
                [BASEEXCHANGECOST],
                [BASEEXCHANGECOSTBASISCODE],
                [EXCHANGEQUANTITY],
                [EXCHANGECOSTADJUSTMENT],
                [EXCHANGECOSTADJUSTMENTBASISCODE],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
              )
              select 
                @ID,
                1,
                [MKTLIST].[BASERENTALCOST],
                [MKTLIST].[BASERENTALCOSTBASISCODE],
                @RENTALQUANTITY,
                @RENTALCOSTADJUSTMENT,
                @RENTALCOSTADJUSTMENTBASISCODE,
                [MKTLIST].[BASEEXCHANGECOST],
                [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
                @EXCHANGEQUANTITY,
                @EXCHANGECOSTADJUSTMENT,
                @EXCHANGECOSTADJUSTMENTBASISCODE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              from dbo.[MKTSEGMENT]
              inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
              where [MKTSEGMENT].[ID] = @SEGMENTID;
            else
              update dbo.[MKTSEGMENTATIONSEGMENTLIST] set 
                [OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 1,
                [RENTALQUANTITY] = @RENTALQUANTITY,
                [RENTALCOSTADJUSTMENT] = @RENTALCOSTADJUSTMENT,
                [RENTALCOSTADJUSTMENTBASISCODE] = @RENTALCOSTADJUSTMENTBASISCODE,
                [EXCHANGEQUANTITY] = @EXCHANGEQUANTITY,
                [EXCHANGECOSTADJUSTMENT] = @EXCHANGECOSTADJUSTMENT,
                [EXCHANGECOSTADJUSTMENTBASISCODE] = @EXCHANGECOSTADJUSTMENTBASISCODE,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [ID] = @ID;
          end
        else
     -- override was already true

          update dbo.[MKTSEGMENTATIONSEGMENTLIST] set 
            [RENTALQUANTITY] = @RENTALQUANTITY,
            [RENTALCOSTADJUSTMENT] = @RENTALCOSTADJUSTMENT,
            [RENTALCOSTADJUSTMENTBASISCODE] = @RENTALCOSTADJUSTMENTBASISCODE,
            [EXCHANGEQUANTITY] = @EXCHANGEQUANTITY,
            [EXCHANGECOSTADJUSTMENT] = @EXCHANGECOSTADJUSTMENT,
            [EXCHANGECOSTADJUSTMENTBASISCODE] = @EXCHANGECOSTADJUSTMENTBASISCODE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
           where [ID] = @ID;
      end
    else -- @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 0

      -- restore values from segment

      update dbo.[MKTSEGMENTATIONSEGMENTLIST] set 
        [OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 0,
        [BASERENTALCOST] = (case when [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS] = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST] else [MKTLIST].[BASERENTALCOST] end),
        [BASERENTALCOSTBASISCODE] = (case when [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS] = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOSTBASISCODE] else [MKTLIST].[BASERENTALCOSTBASISCODE] end),
        [RENTALQUANTITY] = [MKTSEGMENTLIST].[RENTALQUANTITY],
        [RENTALCOSTADJUSTMENT] = [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
        [RENTALCOSTADJUSTMENTBASISCODE] = [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
        [BASEEXCHANGECOST] = (case when [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS] = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST] else [MKTLIST].[BASEEXCHANGECOST] end),
        [BASEEXCHANGECOSTBASISCODE] = (case when [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS] = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTLIST].[BASEEXCHANGECOSTBASISCODE] end),
        [EXCHANGEQUANTITY] = [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
        [EXCHANGECOSTADJUSTMENT] = [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
        [EXCHANGECOSTADJUSTMENTBASISCODE] = [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      from dbo.[MKTSEGMENTATIONSEGMENTLIST]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[ID]
      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 [MKTSEGMENTATIONSEGMENTLIST].[ID] = @ID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;