USP_DATAFORMTEMPLATE_EDIT_MKTMEMBERSHIPMAILINGTEMPLATERULE_MULTIPLE_5

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTMEMBERSHIPMAILINGTEMPLATERULE_MULTIPLE_5]
(
  @ID nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @ASKLADDER xml,
  @ASKLADDERLOADED bit,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGELOADED bit,
  @RESPONSERATE decimal(5,2),
  @RESPONSERATELOADED bit,
  @GIFTAMOUNT money,
  @GIFTAMOUNTLOADED bit,
  @PACKAGECODEVALUEID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @EXCLUDE bit,
  @EXCLUDELOADED bit,
  @CHANNELCODEVALUEID uniqueidentifier,
  @CHANNELCODE nvarchar(10)
)
as
  set nocount on;

  declare @RULESTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @RULEID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @DATEADDED datetime;
  declare @OLDEXCLUDE bit;

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

    set @CURRENTDATE = getdate();

    select 
      @PACKAGECODEVALUEID = case when @PACKAGECODEVALUEID is null then [PARTDEFINITIONVALUESID] else @PACKAGECODEVALUEID end,
      @PACKAGECODE = case when len(@PACKAGECODE) = 0 then [CODE] else @PACKAGECODE end,
      @CHANNELCODEVALUEID = case when @CHANNELCODEVALUEID is null then [CHANNELPARTDEFINITIONVALUESID] else @CHANNELCODEVALUEID end,
      @CHANNELCODE = case when len(@PACKAGECODE) = 0 then [CHANNELSOURCECODE] else @CHANNELCODE end
    from dbo.[MKTPACKAGE] 
    where [ID] = @PACKAGEID;

    if @EXCLUDELOADED = 1 and @EXCLUDE = 1
      begin
        set @ASKLADDERLOADED = 1;
        set @PACKAGELOADED = 1;
        set @RESPONSERATELOADED = 1;
        set @GIFTAMOUNTLOADED = 1;

        set @PACKAGEID = null;
        set @RESPONSERATE = 0;
        set @GIFTAMOUNT = 0;
        set @PACKAGECODEVALUEID = null;
        set @PACKAGECODE = '';
        set @CHANNELCODEVALUEID = null;
        set @CHANNELCODE = '';
      end

    if @PACKAGELOADED = 1 and @EXCLUDE = 0
      exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELCODE, @CHANNELCODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;

    if @GIFTAMOUNTLOADED = 1 and @EXCLUDE = 0
      set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    set @START = 0;
    select @POS = charindex(',', @ID, @START)

    while (@POS <> 0)
      begin
        insert into @RULESTABLE
          select substring(@ID, @START, @POS - @START);

        set @START = @POS + 1;
        select @POS = charindex(',', @ID, @START);
      end;

    if len(@ID) > 0
      insert into @RULESTABLE
        select substring(@ID, @START, 37);

    declare RULECURSOR cursor local fast_forward for
      select [ID] from @RULESTABLE;

    open RULECURSOR;
    fetch next from RULECURSOR into @RULEID;

    while (@@FETCH_STATUS = 0)
      begin
        if @GIFTAMOUNTLOADED = 1 and @EXCLUDE = 0
          begin
            select
              @ORGANIZATIONCURRENCYEXCHANGERATEID = [CURRENCYEXCHANGERATEID],
              @BASECURRENCYID = [BASECURRENCYID],
              @DATEADDED = [DATEADDED]
            from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE]
            where [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ID] = @RULEID;

            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
          end

        update dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] set
          [EXCLUDE] = (case when @EXCLUDELOADED = 1 then @EXCLUDE else [EXCLUDE] end),
          [ASKLADDERID] = (case when @ASKLADDERLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then (select T.c.value('(ASKLADDERID)[1]', 'uniqueidentifier') AS 'ASKLADDERID' from @ASKLADDER.nodes('/ASKLADDER/ITEM') T(c)) else null end else [ASKLADDERID] end),
          [PACKAGEID] = (case when @PACKAGELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @PACKAGEID else null end else [PACKAGEID] end),
          [RESPONSERATE] = (case when @RESPONSERATELOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @RESPONSERATE else 0 end else [RESPONSERATE] end),
          [GIFTAMOUNT] = (case when @GIFTAMOUNTLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @GIFTAMOUNT else 0 end else [GIFTAMOUNT] end),
          [ORGANIZATIONGIFTAMOUNT] = (case when @GIFTAMOUNTLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @ORGANIZATIONGIFTAMOUNT else 0 end else [ORGANIZATIONGIFTAMOUNT] end),
          [CURRENCYEXCHANGERATEID] = (case when @GIFTAMOUNTLOADED = 1 then case when (@EXCLUDELOADED = 1 and @EXCLUDE = 0) or (@EXCLUDELOADED = 0 and [EXCLUDE] = 0) then @ORGANIZATIONCURRENCYEXCHANGERATEID else null end else [CURRENCYEXCHANGERATEID] end),
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @RULEID;

        fetch next from RULECURSOR into @RULEID;
      end;

    close RULECURSOR;
    deallocate RULECURSOR;
  end try

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

  return 0;