USP_MKTSEGMENTATIONPASSIVESEGMENT_SAVE

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

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @ACTIVE bit;
  declare @OLDSEGMENTID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDCODEVALUEID uniqueidentifier;
  declare @OLDCODE nvarchar(10);
  declare @OLDPACKAGECODEVALUEID uniqueidentifier;
  declare @OLDPACKAGECODE nvarchar(10);
  declare @OLDCHANNELCODEVALUEID uniqueidentifier;
  declare @OLDCHANNELCODE nvarchar(10);
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @DATEADDED datetime;

  begin try
    select
      @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
      @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @OLDSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
      @OLDPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @OLDSEGMENTATIONPACKAGEID = [MKTSEGMENTATIONPACKAGE].[ID],
      @OLDCODEVALUEID = [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID],
      @OLDCODE = [MKTSEGMENTATIONSEGMENT].[CODE],
      @OLDPACKAGECODEVALUEID = isnull(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] else [MKTPACKAGE].[PARTDEFINITIONVALUESID] end, '00000000-0000-0000-0000-0000000000000'),
      @OLDPACKAGECODE = case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end,
      @OLDCHANNELCODEVALUEID = isnull(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELPARTDEFINITIONVALUESID] else [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] end, '00000000-0000-0000-0000-0000000000000'),
      @OLDCHANNELCODE = case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CHANNELSOURCECODE] else [MKTPACKAGE].[CHANNELSOURCECODE] end,
      @BASECURRENCYID = [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID],
      @ORGANIZATIONCURRENCYEXCHANGERATEID = [MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
      @DATEADDED = [MKTSEGMENTATIONSEGMENT].[DATEADDED]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    left outer join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

    if @ACTIVE = 1
      if (select isnull(sum([RESPONSES]), 0) from dbo.[MKTSEGMENTATIONSEGMENTACTIVE] where [SEGMENTID] = @ID) > 0 and
         (@OLDSEGMENTID <> @SEGMENTID or @OLDPACKAGEID <> @PACKAGEID or
          (dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 1) = 1 and (@OLDCODEVALUEID <> @CODEVALUEID or @OLDCODE <> @CODE)) or
          (dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 2) = 1 and (@OLDPACKAGECODEVALUEID <> isnull(@PACKAGECODEVALUEID, '00000000-0000-0000-0000-000000000000') or @OLDPACKAGECODE <> @PACKAGECODE)) or
          (dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 3) = 1 and (@OLDCHANNELCODEVALUEID <> isnull(@CHANNELCODEVALUEID, '00000000-0000-0000-0000-000000000000') or @OLDCHANNELCODE <> @CHANNELCODE)))
        begin
          raiserror('ERR_MKTSEGMENTATIONSEGMENTPASSIVE_HASRESPONSES', 13, 1);
          return 1;
        end
      else if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 2) = 1 and 
              dbo.[UFN_MKTSEGMENTATIONPACKAGEACTIVE_GETRESPONSECOUNT](@SEGMENTATIONID, @PACKAGEID) > 0 and
              (@OLDPACKAGECODEVALUEID <> isnull(@PACKAGECODEVALUEID, '00000000-0000-0000-0000-000000000000') or @OLDPACKAGECODE <> @PACKAGECODE)
        begin
          raiserror('ERR_MKTSEGMENTATIONSEGMENTPASSIVE_PACKAGECODEHASRESPONSES', 13, 1);
          return 1;
        end
      else if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 3) = 1 and 
              dbo.[UFN_MKTSEGMENTATIONPACKAGEACTIVE_GETRESPONSECOUNT](@SEGMENTATIONID, @PACKAGEID) > 0 and
              (@OLDCHANNELCODEVALUEID <> isnull(@CHANNELCODEVALUEID, '00000000-0000-0000-0000-000000000000') or @OLDCHANNELCODE <> @CHANNELCODE)
        begin
          raiserror('ERR_MKTSEGMENTATIONSEGMENTPASSIVE_CHANNELCODEHASRESPONSES', 13, 1);
          return 1;
        end

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

    set @CURRENTDATE = getdate();

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

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

            set @ORGANIZATIONGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@GIFTAMOUNT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
        end

    update dbo.[MKTSEGMENTATIONSEGMENT] set 
      [SEGMENTID] = @SEGMENTID,
      [PACKAGEID] = @PACKAGEID,
      [PARTDEFINITIONVALUESID] = @CODEVALUEID,
      [CODE] = @CODE,
      [EXPOSURESTARTDATE] = @EXPOSURESTARTDATE,
      [EXPOSUREENDDATE] = @EXPOSUREENDDATE,
      [RESPONSERATE] = @RESPONSERATE,
      [GIFTAMOUNT] = @GIFTAMOUNT,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [ORGANIZATIONGIFTAMOUNT] = @ORGANIZATIONGIFTAMOUNT,
      [CURRENCYEXCHANGERATEID] = @ORGANIZATIONCURRENCYEXCHANGERATEID
    where [ID] = @ID;

    -- save the source code information

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

    if @ACTIVE = 0
      begin
        -- update the segment with the selected code

        exec dbo.[USP_MKTSEGMENT_UPDATECODE] @SEGMENTID, @CODE, @CODEVALUEID, @CHANGEAGENTID, 0, @CURRENTAPPUSERID;

        -- update the package with the selected code

        exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELCODE, @CHANNELCODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;
      end

    if @OLDPACKAGEID <> @PACKAGEID
      begin
        -- remove the old package if is not being used by any other segments

        if not exists(select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @OLDPACKAGEID)
          exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @OLDSEGMENTATIONPACKAGEID, @CHANGEAGENTID;

        -- add the new package if it doesn't exist already

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

            if @ACTIVE = 1
              exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGE] @SEGMENTATIONID, @PACKAGEID, @CHANGEAGENTID, @CURRENTDATE, @PACKAGECODEVALUEID, @PACKAGECODE, @CHANNELCODEVALUEID, @CHANNELCODE;
          end
      end
    else
      if @ACTIVE = 1
        update dbo.[MKTSEGMENTATIONPACKAGE] set
          [PARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
          [CODE] = @PACKAGECODE,
          [CHANNELPARTDEFINITIONVALUESID] = @CHANNELCODEVALUEID,
          [CHANNELSOURCECODE] = @CHANNELCODE
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [PACKAGEID] = @PACKAGEID;
    end try

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

  return 0;