USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGACTIVATE

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGACTIVATE]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @MKTPACKAGEID uniqueidentifier,
  @IDSETREGISTERID uniqueidentifier,
  @LABELFILECHANGED bit,
  @LABELFILENAME nvarchar(255),
  @LABELFILE varbinary(max),
  @CREATEOUTPUTIDSET bit,
  @OUTPUTIDSETNAME nvarchar(100),
  @OVERWRITEOUTPUTIDSET bit,
  @MAILDATE datetime
)
as
  set nocount on;

  declare @APPEALMAILINGID uniqueidentifier;
  declare @HOUSEHOLDINGTYPECODE tinyint;
  declare @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
  declare @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
  declare @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
  declare @USEADDRESSPROCESSING bit;
  declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @CURRENTDATE datetime;
  declare @ISHYBRID bit;

  begin try
    --Validate that the mailing is not currently being activated before saving changes...

    if dbo.[UFN_MKTSEGMENTATION_ISACTIVATING](@ID) = 1
      raiserror('BBERR_APPEALMAILINGACTIVATE_ISACTIVATING', 13, 1);

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

    set @CURRENTDATE = getdate();

    --Grab some info so we know what to update...

    select
      @APPEALMAILINGID = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID],
      @HOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
      @USEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
      @ADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @ISHYBRID = (case when [APPEALMAILINGSETUP].[ID] is null then 0 else 1 end)
    from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
    left join dbo.[APPEALMAILINGSETUP] on [APPEALMAILINGSETUP].[ID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATIONACTIVATEPROCESS].[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] = @APPEALMAILINGID;

    --OutputIDSet information is now stored on the segmentation table, but keep both tables up-to-date for backwards compatibility...

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

    exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @APPEALMAILINGID;

    if @ISHYBRID = 0
      --Update the underlying segment information...

      exec dbo.[USP_APPEALMAILING_CREATEORUPDATESEGMENT]
    @APPEALMAILINGID = @APPEALMAILINGID,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @IDSETREGISTERID = @IDSETREGISTERID,
        @MKTPACKAGEID = @MKTPACKAGEID,
        @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
        @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
        @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
        @CHANGEAGENTID = @CHANGEAGENTID;
  end try

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

  return 0;