USP_MKTSEGMENTATIONPACKAGE_SAVE

Saves information about a package in a marketing effort.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@CATEGORYCODEID	uniqueidentifier	IN
@COST	money	IN
@COSTDISTRIBUTIONMETHODCODE	tinyint	IN
@LETTERCODEID	uniqueidentifier	IN
@NETCOMMUNITYTEMPLATEID	int	IN
@NETCOMMUNITYDATASOURCEID	int	IN
@DOCUMENTFILENAME	nvarchar(255)	IN
@DOCUMENTFILENAMEFORDISPLAY	nvarchar(255)	IN
@DOCUMENTFILE	varbinary	IN
@DOCUMENTFILECHANGED	bit	IN
@CREATIVEID	uniqueidentifier	IN
@EXPORTDEFINITIONID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.[USP_MKTSEGMENTATIONPACKAGE_SAVE]
(
  @ID uniqueidentifier,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @CATEGORYCODEID uniqueidentifier,
  @COST money,
  @COSTDISTRIBUTIONMETHODCODE tinyint,
  @LETTERCODEID uniqueidentifier,
  @NETCOMMUNITYTEMPLATEID integer,
  @NETCOMMUNITYDATASOURCEID integer,
  @DOCUMENTFILENAME nvarchar(255),
  @DOCUMENTFILENAMEFORDISPLAY nvarchar(255),
  @DOCUMENTFILE varbinary(max),
  @DOCUMENTFILECHANGED bit,
  @CREATIVEID uniqueidentifier,
  @EXPORTDEFINITIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SITEID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @ACTIVE bit;
  declare @PACKAGEID uniqueidentifier;
  declare @CHANNELCODE tinyint;

  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONCOST 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
      @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @PACKAGEID = [MKTSEGMENTATIONPACKAGE].[PACKAGEID],
      @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
      @BASECURRENCYID = [MKTPACKAGE].[BASECURRENCYID],
      @CURRENCYEXCHANGERATEID = [MKTPACKAGE].[CURRENCYEXCHANGERATEID],
      @DATEADDED = [MKTPACKAGE].[DATEADDED]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
    where [MKTSEGMENTATIONPACKAGE].[ID] = @ID;

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

    if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
      set @ORGANIZATIONCOST = @COST;
    else
      begin
        if @CURRENCYEXCHANGERATEID is null
          set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

        set @ORGANIZATIONCOST = dbo.[UFN_CURRENCY_CONVERT](@COST, @CURRENCYEXCHANGERATEID);
      end

    if @ACTIVE = 0
      begin
        --Check if the mailing is currently being activated...

        exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

        if @CHANNELCODE = 0 -- mail

          begin
            if (select [EXPORTDEFINITIONID] from dbo.[LETTERCODE] where [ID] = @LETTERCODEID) is not null
              set @EXPORTDEFINITIONID = null;

            update dbo.[MKTPACKAGE] set 
              [NAME] = @NAME,
              [DESCRIPTION] = @DESCRIPTION,
              [SITEID] = @SITEID,
              [PACKAGECATEGORYCODEID] = @CATEGORYCODEID,
              [UNITCOST] = @COST,
              [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
              [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
              [LETTERCODEID] = @LETTERCODEID,
              [EXPORTDEFINITIONID] = @EXPORTDEFINITIONID,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @PACKAGEID
          end

        else if @CHANNELCODE = 1 -- email

          begin
            update dbo.[MKTPACKAGE] set 
              [NAME] = @NAME,
              [DESCRIPTION] = @DESCRIPTION,
              [SITEID] = @SITEID,
              [PACKAGECATEGORYCODEID] = @CATEGORYCODEID,
              [UNITCOST] = @COST,
              [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
              [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
              [NETCOMMUNITYTEMPLATEID] = @NETCOMMUNITYTEMPLATEID,
              [NETCOMMUNITYDATASOURCEID] = @NETCOMMUNITYDATASOURCEID,
              [EXPORTDEFINITIONID] = case when @NETCOMMUNITYDATASOURCEID > 0 then null else @EXPORTDEFINITIONID end,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @PACKAGEID
          end

        else if @CHANNELCODE = 2 -- phone

          begin
            if @DOCUMENTFILECHANGED = 1
              update dbo.[MKTPACKAGE] set
                [NAME] = @NAME,
                [DESCRIPTION] = @DESCRIPTION,
                [SITEID] = @SITEID,
                [PACKAGECATEGORYCODEID] = @CATEGORYCODEID,
                [UNITCOST] = @COST,
                [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
                [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
                [DOCUMENTFILENAME] = @DOCUMENTFILENAMEFORDISPLAY,
                [DOCUMENTFILE] = @DOCUMENTFILE,
                [EXPORTDEFINITIONID] = @EXPORTDEFINITIONID,
                [CHANGEDBYID] = @CHANGEAGENTID
              where [ID] = @PACKAGEID;
            else
              update dbo.[MKTPACKAGE] set
                [NAME] = @NAME,
                [DESCRIPTION] = @DESCRIPTION,
                [SITEID] = @SITEID,
                [PACKAGECATEGORYCODEID] = @CATEGORYCODEID,
                [UNITCOST] = @COST,
                [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
                [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
                [EXPORTDEFINITIONID] = @EXPORTDEFINITIONID,
                [CHANGEDBYID] = @CHANGEAGENTID
              where [ID] = @PACKAGEID;
          end

        else if @CHANNELCODE = 3 -- public media

          update dbo.[MKTPACKAGE] set
            [NAME] = @NAME,
            [DESCRIPTION] = @DESCRIPTION,
            [SITEID] = @SITEID,
            [PACKAGECATEGORYCODEID] = @CATEGORYCODEID,
            [UNITCOST] = @COST,
            [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
            [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
            [CREATIVEID] = @CREATIVEID,
            [CHANGEDBYID] = @CHANGEAGENTID
          where [ID] = @PACKAGEID;

      end
    else
      update dbo.[MKTSEGMENTATIONPACKAGE] set 
        [UNITCOST] = @COST,
        [ORGANIZATIONUNITCOST] = @ORGANIZATIONCOST,
        [COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @ID;
  end try

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

  return 0;