USP_MKTSEGMENTATIONPASSIVE_ADD

CREATE procedure dbo.[USP_MKTSEGMENTATIONPASSIVE_ADD]
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @SITEID uniqueidentifier = null,
  @MAILDATE datetime = null,
  @APPEALINFORMATION xml,
  @SOURCECODEID uniqueidentifier,
  @CODEVALUEID uniqueidentifier = null,
  @CODE nvarchar(10) = '',
  @ITEMLIST xml = null,
  @ACTIVATIONKPIS xml = null,
  @USEKPISASDEFAULT bit = 0,
  @BASECURRENCYID uniqueidentifier = null output
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

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

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

    set @CURRENTDATE = getdate();
    if @BASECURRENCYID is null
      begin
        -- Get basecurrencyid from enterprise appeal, if no enterprise appeal use UFN_APPUSER_GETBASECURRENCY.

        select
          @BASECURRENCYID = [APPEAL].[BASECURRENCYID]
        from @APPEALINFORMATION.nodes('/APPEALINFORMATION/ITEM') T(c)
        inner join dbo.[APPEAL] on [APPEAL].[ID] = T.c.value('(APPEALSYSTEMID)[1]','uniqueidentifier')
        where (dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier')) = 1);

        if @BASECURRENCYID is null
          set @BASECURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
      end

    insert into dbo.[MKTSEGMENTATION] (
      [ID],
      [MAILINGTYPECODE],
      [NAME],
      [DESCRIPTION],
      [SITEID],
      [MAILDATE],
      [SOURCECODEID],
      [PARTDEFINITIONVALUESID],
      [CODE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID]
    ) values (
      @ID,
      4,
      @NAME,
      @DESCRIPTION,
      @SITEID,
      @MAILDATE,
      @SOURCECODEID,
      @CODEVALUEID,
      @CODE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID
    );

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

    if (@ORGANIZATIONCURRENCYID <> @BASECURRENCYID)
      set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

    -- create default budget info

    insert into dbo.[MKTSEGMENTATIONBUDGET] (
      [ID],
      [BUDGETAMOUNT],
      [FIXEDCOST],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID],
      [ORGANIZATIONBUDGETAMOUNT],
      [ORGANIZATIONFIXEDCOST],
      [ORGANIZATIONCURRENCYEXCHANGERATEID]
    ) values (
      @ID,
      0.0,
      0.0,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID,
      0.0,
      0.0,
      @ORGANIZATIONCURRENCYEXCHANGERATEID
    );

    -- save the source code information

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

    -- add the mailing to the ACTIVATIONPROCESS table

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @ID, @CHANGEAGENTID;

    -- save KPIs

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] @ID, @ACTIVATIONKPIS, @USEKPISASDEFAULT, @CHANGEAGENTID, @CURRENTAPPUSERID;

    -- save the appeal information

    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD_FROMXML] @ID, @APPEALINFORMATION, @CHANGEAGENTID;
  end try

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

  return 0;