USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONPASSIVESEGMENT

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

  declare @SEGMENTATIONID uniqueidentifier;
  declare @ACTIVE bit;
  declare @OLDCODE nvarchar(10);
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  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],
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @BASECURRENCYID = [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID],
      @ORGANIZATIONCURRENCYEXCHANGERATEID = [MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
      @DATEADDED = [MKTSEGMENTATIONSEGMENT].[DATEADDED]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

    if @ACTIVE = 0
      begin
        -- 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 
          @OLDPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
          @OLDSEGMENTATIONPACKAGEID = (select [MKTSEGMENTATIONPACKAGE].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID])
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
        where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

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

          if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
          begin
              set @ORGANIZATIONGIFTAMOUNT = @GIFTAMOUNT;
          end
          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_GETITEMLIST_UPDATEFROMXML] @ID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

      -- update the segment with the selected code

        select @OLDCODE = [CODE] from dbo.[MKTSEGMENT] where [ID] = @SEGMENTID;

        if @CODEVALUEID is not null
          exec dbo.[USP_MKTSEGMENT_UPDATECODE] @SEGMENTID, @CODE, @CODEVALUEID, @CHANGEAGENTID;

        select 
          @OLDCODE = [CODE],
          @OLDCHANNELCODE = [CHANNELSOURCECODE],
          @OLDCHANNELCODEVALUEID = [CHANNELPARTDEFINITIONVALUESID]
        from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID;

        -- update the package with the selected code

        if @OLDCODE <> @PACKAGECODE
          update dbo.[MKTPACKAGE] set
            [PARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
            [CODE] = @PACKAGECODE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @PACKAGEID;

        -- update the package with the selected channel source code

        if @OLDCHANNELCODE <> @CHANNELCODE or @OLDCHANNELCODEVALUEID <> @CHANNELCODEVALUEID
          update dbo.[MKTPACKAGE] set
            [CHANNELPARTDEFINITIONVALUESID] = @CHANNELCODEVALUEID,
            [CHANNELSOURCECODE] = @CHANNELCODE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @PACKAGEID;

        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)
              exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID;
          end
      end
    end try

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

  return 0;