USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGE

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGE]
(
  @SEGMENTATIONID uniqueidentifier,
  @PACKAGEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATE datetime = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = null,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null,
  @CHANNELSOURCECODE nvarchar(10) = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @ACTIVE bit;

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE],
    @ACTIVE = [ACTIVE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  begin try
    if @ACTIVE = 0 or @MAILINGTYPECODE = 4
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        if @CURRENTDATE is null set @CURRENTDATE = getdate();

        /* Update the mailing package with its current codes and costs */
        update [MKTSEGMENTATIONPACKAGE] set
          [UNITCOST] = [MKTPACKAGE].[UNITCOST],
          [COSTDISTRIBUTIONMETHODCODE] = [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE],
          [INSERTCOSTPERPIECE] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 0, 0) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 4, 0),
          [INSERTCOSTPERRESPONSE] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 1, 0),
          [INSERTCOSTPEREFFORT] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 2, 0),
          [CODE] = isnull(@PACKAGECODE, [MKTPACKAGE].[CODE]),
          [PARTDEFINITIONVALUESID] = isnull(@PACKAGECODEVALUEID, [MKTPACKAGE].[PARTDEFINITIONVALUESID]),
          [CHANNELSOURCECODE] = isnull(@CHANNELSOURCECODE, [MKTPACKAGE].[CHANNELSOURCECODE]),
          [CHANNELPARTDEFINITIONVALUESID] = isnull(@CHANNELSOURCECODEVALUEID, [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID]),
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE,
          [ORGANIZATIONUNITCOST] = [MKTPACKAGE].[ORGANIZATIONUNITCOST],
          [ORGANIZATIONINSERTCOSTPERPIECE] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 0, 1) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 4, 1),
          [ORGANIZATIONINSERTCOSTPERRESPONSE] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 1, 1),
          [ORGANIZATIONINSERTCOSTPEREFFORT] = dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 2, 1)
        from dbo.[MKTSEGMENTATIONPACKAGE]
        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [PACKAGEID] = @PACKAGEID;
      end
  end try

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

  return 0;