USP_MKTSEGMENTATIONPASSIVESEGMENT_ADD

CREATE procedure dbo.[USP_MKTSEGMENTATIONPASSIVESEGMENT_ADD]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEGMENTATIONID uniqueidentifier,
  @SEGMENTID uniqueidentifier,
  @CODEVALUEID uniqueidentifier = null,
  @CODE nvarchar(10) = '',
  @PACKAGEID uniqueidentifier,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @CHANNELCODEVALUEID uniqueidentifier = null,
  @CHANNELCODE nvarchar(10) = '',
  @EXPOSURESTARTDATE date = null,
  @EXPOSUREENDDATE date = null,
  @RESPONSERATE decimal(5, 2) = 5,
  @GIFTAMOUNT money,
  @ITEMLIST xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @ACTIVE bit;
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @SEQUENCE int;

  begin try
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();

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

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

    if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
      set @ORGANIZATIONGIFTAMOUNT = @GIFTAMOUNT;
    else
      begin
        set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@GIFTAMOUNT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
      end

    select
      @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1
    from dbo.[MKTSEGMENTATIONSEGMENT]
    where [SEGMENTATIONID] = @SEGMENTATIONID;

    insert into dbo.[MKTSEGMENTATIONSEGMENT] (
      [ID],
      [SEGMENTATIONID],
      [SEGMENTID],
      [PARTDEFINITIONVALUESID],
      [CODE],
      [PACKAGEID],
      [EXPOSURESTARTDATE],
      [EXPOSUREENDDATE],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [BASECURRENCYID],
      [ORGANIZATIONGIFTAMOUNT],
      [CURRENCYEXCHANGERATEID],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @SEGMENTATIONID,
      @SEGMENTID,
      @CODEVALUEID,
      @CODE,
      @PACKAGEID,
      @EXPOSURESTARTDATE,
      @EXPOSUREENDDATE,
      @RESPONSERATE,
      @GIFTAMOUNT,
      @BASECURRENCYID,
      @ORGANIZATIONGIFTAMOUNT,
      @ORGANIZATIONCURRENCYEXCHANGERATEID,
      @SEQUENCE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    -- save and add the package to the mailing, only if it doesn't already exist

    if not exists(select [ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID;

    -- save the source code information

    exec dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST3_UPDATEFROMXML] @ID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

    -- update the segment with the selected code

    if @ACTIVE = 0
      begin
        exec dbo.[USP_MKTSEGMENT_UPDATECODE] @SEGMENTID, @CODE, @CODEVALUEID, @CHANGEAGENTID, 0, @CURRENTAPPUSERID;

        exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELCODE, @CHANNELCODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;
      end
    else
      exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGE] @SEGMENTATIONID, @PACKAGEID, @CHANGEAGENTID, @CURRENTDATE, @PACKAGECODEVALUEID, @PACKAGECODE, @CHANNELCODEVALUEID, @CHANNELCODE;
  end try

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

  return 0;