USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @SEGMENTID uniqueidentifier,
  @MARKETINGPLANBRIEFID uniqueidentifier = null,
  @USAGECODE tinyint = 1, -- test

  @CODE nvarchar(10) = '',
  @TESTSEGMENTCODE nvarchar(10) = '',
  @OVERRIDEQUANTITIESANDORSEGMENTCOSTS bit = 0,
  @RENTALQUANTITY integer = 0,
  @RENTALCOSTADJUSTMENT money = 0,
  @RENTALCOSTADJUSTMENTBASISCODE tinyint = 1, -- per thousand

  @EXCHANGEQUANTITY integer = 0,
  @EXCHANGECOSTADJUSTMENT money = 0,
  @EXCHANGECOSTADJUSTMENTBASISCODE tinyint = 1, -- per thousand

  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10) = '',
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money,
  @SEQUENCE int,
  @ASKLADDERID uniqueidentifier = null,
  @SAMPLESIZE int = 100,
  @SAMPLESIZETYPECODE tinyint = 0,
  @SAMPLESIZEMETHODCODE tinyint = 0,
  @SAMPLESIZEEXCLUDEREMAINDER bit = 1,
  @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
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;

  if charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) > 0
    set @SEGMENTATIONID = convert(uniqueidentifier, substring(@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) - 1));
  else
    set @SEGMENTATIONID = convert(uniqueidentifier, @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE)

  declare @CURRENTDATE datetime;
  declare @SEGMENTLISTID uniqueidentifier;
  declare @GETLISTCOSTSID nvarchar(73);
  declare @OVERRIDELISTCOSTS bit;
  declare @BASERENTALCOST money;
  declare @BASERENTALCOSTBASISCODE tinyint;
  declare @BASEEXCHANGECOST money;
  declare @BASEEXCHANGECOSTBASISCODE tinyint;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONBASERENTALCOST money;
  declare @ORGANIZATIONBASEEXCHANGECOST money;
  declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
  declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;

  begin try
    --Check if the mailing is currently being activated...

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

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

    set @CURRENTDATE = getdate();

    select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

    set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    --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

    --Save the segment...

    exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
      @ID output,
      @CHANGEAGENTID,
      @SEGMENTATIONID,
      @MARKETINGPLANBRIEFID,
      @SEGMENTID,
      @CODE,
      @TESTSEGMENTCODE,
      @PACKAGEID,
      @PACKAGECODE,
      @RESPONSERATE,
      @GIFTAMOUNT,
      @SAMPLESIZE,
      @SAMPLESIZETYPECODE,
      @SAMPLESIZEMETHODCODE,
      @SEQUENCE,
      @ASKLADDERID,
      @SAMPLESIZEEXCLUDEREMAINDER,
      default,
      default,
      default,
      default,
      default,
      default,
      @CODEVALUEID,
      @TESTSEGMENTCODEVALUEID,
      @PACKAGECODEVALUEID,
      @ITEMLIST,
      @CHANNELSOURCECODE,
      @CHANNELSOURCECODEVALUEID,
      default,
      @OVERRIDEBUSINESSUNITS,
      @BUSINESSUNITS,
      @CURRENTAPPUSERID;

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

    /* Get the segment list ID and list ID for the segment */
    select
      @SEGMENTLISTID = [MKTSEGMENTLIST].[ID],
      @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 list code if the old code value ID is nothing and the list code value ID is supplied */
    if @LISTCODEVALUEID is not null and (@OLDLISTCODEVALUEID is null or @OLDLISTCODE <> @LISTCODE)
      /* Update the base list code */
      update dbo.[MKTLIST] set
        [CODE] = @LISTCODE,
        [PARTDEFINITIONVALUESID] = @LISTCODEVALUEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @LISTID;

    --Save the usage code and override status...

    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [USAGECODE] = @USAGECODE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    set @GETLISTCOSTSID = convert(nvarchar(36), @SEGMENTATIONID) + '|' + convert(nvarchar(36), @SEGMENTID)

    exec dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENTLISTCOSTS]
      @GETLISTCOSTSID, 
      @OVERRIDELISTCOSTS output, 
      @BASERENTALCOST output, 
      @BASERENTALCOSTBASISCODE output, 
      @BASEEXCHANGECOST output, 
      @BASEEXCHANGECOSTBASISCODE output;

    if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
      begin
        set @ORGANIZATIONBASERENTALCOST = @BASERENTALCOST;
        set @ORGANIZATIONBASEEXCHANGECOST = @BASEEXCHANGECOST;
        set @ORGANIZATIONRENTALCOSTADJUSTMENT = @RENTALCOSTADJUSTMENT;
        set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = @EXCHANGECOSTADJUSTMENT;
      end
    else
      begin
        set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONBASERENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@BASERENTALCOST, @ORGANIZATIONCURRENCYEXCHANGERATEID);
        set @ORGANIZATIONBASEEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@BASEEXCHANGECOST, @ORGANIZATIONCURRENCYEXCHANGERATEID);
        set @ORGANIZATIONRENTALCOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@RENTALCOSTADJUSTMENT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
        set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@EXCHANGECOSTADJUSTMENT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
      end


    if @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1 or @OVERRIDELISTCOSTS = 1
      insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
        [ID],
        [SEGMENTLISTID],
        [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
        [OVERRIDELISTCOSTS],
        [BASERENTALCOST],
        [BASERENTALCOSTBASISCODE],
        [BASEEXCHANGECOST],
        [BASEEXCHANGECOSTBASISCODE],
        [RENTALQUANTITY],
        [RENTALCOSTADJUSTMENT],
        [RENTALCOSTADJUSTMENTBASISCODE],
        [EXCHANGEQUANTITY],
        [EXCHANGECOSTADJUSTMENT],
        [EXCHANGECOSTADJUSTMENTBASISCODE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED],
        [BASECURRENCYID],
        [ORGANIZATIONBASERENTALCOST],
        [ORGANIZATIONBASEEXCHANGECOST],
        [ORGANIZATIONRENTALCOSTADJUSTMENT],
        [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
        [ORGANIZATIONCURRENCYEXCHANGERATEID]
      ) values (
        @ID,
        @SEGMENTLISTID,
        @OVERRIDEQUANTITIESANDORSEGMENTCOSTS,
        @OVERRIDELISTCOSTS,
        @BASERENTALCOST,
        @BASERENTALCOSTBASISCODE,
        @BASEEXCHANGECOST,
        @BASEEXCHANGECOSTBASISCODE,
        @RENTALQUANTITY,
        @RENTALCOSTADJUSTMENT,
        @RENTALCOSTADJUSTMENTBASISCODE,
        @EXCHANGEQUANTITY,
        @EXCHANGECOSTADJUSTMENT,
        @EXCHANGECOSTADJUSTMENTBASISCODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        @BASECURRENCYID,
        @ORGANIZATIONBASERENTALCOST,
        @ORGANIZATIONBASEEXCHANGECOST,
        @ORGANIZATIONRENTALCOSTADJUSTMENT,
        @ORGANIZATIONEXCHANGECOSTADJUSTMENT,
        @ORGANIZATIONCURRENCYEXCHANGERATEID
      );
  end try

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

  return 0;