USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGREVENUECALCULATOR

The save procedure used by the edit dataform template "Appeal Mailing Revenue Calculator Edit Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@LETTERCHANNELINSTANCES	xml	IN
@BUDGETAMOUNT	money	IN	Budget
@ESTIMATEDRESPONSERATE	decimal(5, 2)	IN	Estimated response rate
@ESTIMATEDAVERAGEGIFTAMOUNT	money	IN	Estimated average gift amount
@ESTIMATEDNETREVENUE	money	IN	Estimated net revenue

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGREVENUECALCULATOR]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @LETTERCHANNELINSTANCES xml,
  @BUDGETAMOUNT money,
  @ESTIMATEDRESPONSERATE decimal(5,2),
  @ESTIMATEDAVERAGEGIFTAMOUNT money,
  @ESTIMATEDNETREVENUE money
)
as
  begin
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();

    update dbo.[MKTPACKAGE] set
      [MKTPACKAGE].[UNITCOST] = [LETTERCHANNELINSTANCES].[COSTPERPIECE],
      [MKTPACKAGE].[DATECHANGED] = @CURRENTDATE,
      [MKTPACKAGE].[CHANGEDBYID] = @CHANGEAGENTID
    from dbo.[UFN_APPEALMAILING_GETLETTERCHANNELINSTANCES_FROMITEMLISTXML](@LETTERCHANNELINSTANCES) as [LETTERCHANNELINSTANCES]
    where [MKTPACKAGE].[ID] = [LETTERCHANNELINSTANCES].[MKTPACKAGEID];

    update dbo.[MKTSEGMENTATIONPACKAGE] set
      [MKTSEGMENTATIONPACKAGE].[UNITCOST] = [LETTERCHANNELINSTANCES].[COSTPERPIECE],
      [MKTSEGMENTATIONPACKAGE].[CHANGEDBYID] = @CHANGEAGENTID,
      [MKTSEGMENTATIONPACKAGE].[DATECHANGED] = @CURRENTDATE
    from dbo.[UFN_APPEALMAILING_GETLETTERCHANNELINSTANCES_FROMITEMLISTXML](@LETTERCHANNELINSTANCES) as [LETTERCHANNELINSTANCES]
    where [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [LETTERCHANNELINSTANCES].[MKTPACKAGEID]
    and [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @ID;

    update dbo.[MKTSEGMENTATIONBUDGET] set
      [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT] = @BUDGETAMOUNT,
      [MKTSEGMENTATIONBUDGET].[DATECHANGED] = @CURRENTDATE,
      [MKTSEGMENTATIONBUDGET].[CHANGEDBYID] = @CHANGEAGENTID
    where [MKTSEGMENTATIONBUDGET].[ID] = @ID;

    update dbo.[APPEALMAILINGSETUP] set
      [APPEALMAILINGSETUP].[ESTIMATEDAVERAGEGIFTAMOUNT] = @ESTIMATEDAVERAGEGIFTAMOUNT,
      [APPEALMAILINGSETUP].[ESTIMATEDRESPONSERATE] = @ESTIMATEDRESPONSERATE,
      [APPEALMAILINGSETUP].[ESTIMATEDNETREVENUE] = @ESTIMATEDNETREVENUE,
      [APPEALMAILINGSETUP].[DATECHANGED] = @CURRENTDATE,
      [APPEALMAILINGSETUP].[CHANGEDBYID] = @CHANGEAGENTID
    where [APPEALMAILINGSETUP].[ID] = @ID;

    -- For convenience, appeal mailings save estimations at the APPEALMAILINGSETUP level.
    -- For compatibility, also maintain these estimations at the MKTSEGMENTATIONSEGMENT level, which
    -- is what DM mailings do.
    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [MKTSEGMENTATIONSEGMENT].[RESPONSERATE] = @ESTIMATEDRESPONSERATE,
      [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] = @ESTIMATEDAVERAGEGIFTAMOUNT,
      [MKTSEGMENTATIONSEGMENT].[DATECHANGED] = @CURRENTDATE,
      [MKTSEGMENTATIONSEGMENT].[CHANGEDBYID] = @CHANGEAGENTID
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID;

    declare @KPITABLE as table (
      [KPICATALOGID] uniqueidentifier,
      [SELECTED] bit,
      [NAME] nvarchar(255),
      [GOALTYPECODE] tinyint,
      [DEFAULT] bit,
      [LOCKED] bit,
      [TEMPLATETYPECODE] tinyint
    );

    insert into @KPITABLE
      exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS] null, 0, @CURRENTAPPUSERID;

    declare @ACTIVATIONKPIS xml;

    set @ACTIVATIONKPIS = (
      select 
        [KPICATALOGID],
        [SELECTED],
        [NAME],
        [GOALTYPECODE],
        [DEFAULT]
      from @KPITABLE 
      for xml raw('ITEM'), type, elements, root('ACTIVATIONKPIS'), binary base64);

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_CLEARFIELDS] @ID, 1, @CHANGEAGENTID;

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