USP_APPEALMAILING_EDIT_SAVE_2

CREATE procedure dbo.[USP_APPEALMAILING_EDIT_SAVE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @MAILDATE datetime,
  @FIXEDCOST money,
  @MKTPACKAGEID uniqueidentifier,
  @IDSETREGISTERID uniqueidentifier,
  @HOUSEHOLDINGTYPECODE tinyint,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit,
  @EXCLUSIONDATETYPECODE tinyint,
  @EXCLUSIONASOFDATE datetime,
  @EXCLUDEDECEASED bit,
  @EXCLUDEINACTIVE bit,
  @EXCLUSIONS xml,
  @USEADDRESSPROCESSING bit,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime,
  @LABELFILECHANGED bit,
  @LABELFILENAME nvarchar(255),
  @LABELFILE varbinary(max),
  @CREATEOUTPUTIDSET bit,
  @OUTPUTIDSETNAME nvarchar(100),
  @OVERWRITEOUTPUTIDSET bit,
  @ACTIVATIONKPIS xml = null,
  @USEKPISASDEFAULT bit = null,
  @MAILINGBUDGET money,
  @DISPLAYEDFILENAME nvarchar(255),
  @RECORDCOUNTNEEDSUPDATED bit = null output
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @OLDNAME nvarchar(100);
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONBUDGETAMOUNT money;
  declare @ORGANIZATIONFIXEDCOST money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @DATEADDED datetime;

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

    set @CURRENTDATE = getdate();

    select
      @OLDNAME = [NAME]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @ID;

    --Update the appeal mailing...

    update dbo.[APPEALMAILING] set
      [LABELFILE] = (case when @LABELFILECHANGED = 1 then @LABELFILE else [LABELFILE] end),
      [LABELFILENAME] = (case when @LABELFILECHANGED = 1 then @LABELFILENAME else [LABELFILENAME] end),    
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    --Update the base mailing (do not set the householding field here, the SP below will take care of it)...

    update dbo.[MKTSEGMENTATION] set
      [NAME] = @NAME,
      [DESCRIPTION] = @DESCRIPTION,
      [MAILDATE] = @MAILDATE,
      [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    select 
      @BASECURRENCYID = [BASECURRENCYID], 
      @DATEADDED = [DATEADDED], 
      @ORGANIZATIONCURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID] 
    from dbo.[MKTSEGMENTATIONBUDGET]
    where [ID] = @ID;

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

    if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
      begin
        set @ORGANIZATIONBUDGETAMOUNT = @MAILINGBUDGET;
        set @ORGANIZATIONFIXEDCOST = @FIXEDCOST;
      end
    else
      begin
        if @ORGANIZATIONCURRENCYEXCHANGERATEID is null
          set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

        set @ORGANIZATIONBUDGETAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@MAILINGBUDGET, @ORGANIZATIONCURRENCYEXCHANGERATEID);
        set @ORGANIZATIONFIXEDCOST = dbo.[UFN_CURRENCY_CONVERT](@FIXEDCOST, @ORGANIZATIONCURRENCYEXCHANGERATEID);
      end

    --Update the budget information...

    update dbo.[MKTSEGMENTATIONBUDGET] set
      [BUDGETAMOUNT] = @MAILINGBUDGET,
      [FIXEDCOST] = @FIXEDCOST,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [ORGANIZATIONFIXEDCOST] = @ORGANIZATIONFIXEDCOST,
      [ORGANIZATIONBUDGETAMOUNT] = @ORGANIZATIONBUDGETAMOUNT,
      [ORGANIZATIONCURRENCYEXCHANGERATEID] = @ORGANIZATIONCURRENCYEXCHANGERATEID
    where [ID] = @ID;

    if @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
      exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @ID;

    exec dbo.[USP_APPEALMAILING_CREATEORUPDATESEGMENT]
      @APPEALMAILINGID = @ID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @NAME = @NAME,
      @IDSETREGISTERID = @IDSETREGISTERID,
      @MKTPACKAGEID = @MKTPACKAGEID,
      @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
      @EXCLUDEDECEASED = @EXCLUDEDECEASED,
      @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
      @EXCLUSIONS = @EXCLUSIONS,
      @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @RECORDCOUNTNEEDSUPDATED = @RECORDCOUNTNEEDSUPDATED output;

    --Update the name of any gift IDSets that are created for this mailing...

    update dbo.[IDSETREGISTER] set
      [NAME] = @NAME + substring([NAME], len(@OLDNAME) + 1, 300),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] in (
      select [NORMALGIFTIDSETREGISTERID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID and [NORMALGIFTIDSETREGISTERID] is not null
      union all
      select [UNRESOLVEDGIFTIDSETREGISTERID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID and [UNRESOLVEDGIFTIDSETREGISTERID] is not null
    )
    and [NAME] like (replace(replace(replace(@OLDNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' (%)') escape '\';

    --Save KPIs...

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

    --Update the name of any KPIs that are created for this mailing...

    update dbo.[KPIINSTANCE] set
      [NAME] = @NAME + substring([NAME], len(@OLDNAME) + 1, 255),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] in (
      select [KPIINSTANCE].[ID]
      from dbo.[MKTSEGMENTATIONACTIVATEKPI]
      inner join dbo.[KPIINSTANCE] on [KPIINSTANCE].[KPICATALOGID] = [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID]
      where [MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] = @ID
      and [KPIINSTANCE].[CONTEXTRECORDID] = cast([MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] as nvarchar(36))
      and [KPIINSTANCE].[NAME] like (replace(replace(replace(@OLDNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' - %') escape '\'
    );
  end try

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

  return 0;