USP_MKTPACKAGE_EDITLOAD

CREATE procedure dbo.[USP_MKTPACKAGE_EDITLOAD]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @CODE nvarchar(10) = null output,
  @COST money = null output,
  @COSTDISTRIBUTIONMETHODCODE tinyint = null output,
  @INSERTCOSTPERPIECE money = null output,
  @TOTALCOSTPERPIECE money = null output,
  @SITEID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @SITECANBECHANGED bit = null output,
  @CHANNELCODE tinyint = null output,
  @CATEGORYCODEID uniqueidentifier = null output,
  @EXPORTDEFINITIONID uniqueidentifier = null output,
  @CONTENTCANBECHANGED bit = null output,
  @CONTENTMAILINGTYPECODEALLOWED tinyint = null output,
  @ACKNOWLEDGEMENTMAILINGSINSTALLED bit = null output,
  @MEMBERSHIPMAILINGSINSTALLED bit = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @CHANNELSOURCECODE nvarchar(10) = null output,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEPARTDEFINITIONID uniqueidentifier = null output,
  @CHANNELPARTDEFINITIONID uniqueidentifier = null output,
  @SPONSORSHIPMAILINGSINSTALLED bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @NAME = [MKTPACKAGE].[NAME],
    @DESCRIPTION = [MKTPACKAGE].[DESCRIPTION],
    @CODE = [MKTPACKAGE].[CODE],
    @CODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
    @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
    @CATEGORYCODEID = [MKTPACKAGE].[PACKAGECATEGORYCODEID],
    @COST = [MKTPACKAGE].[UNITCOST],
    @COSTDISTRIBUTIONMETHODCODE = [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE],
    @INSERTCOSTPERPIECE = 
      (case isnull([MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE], 255) when 0 then [MKTCREATIVE].[COST] when 4 then [MKTCREATIVE].[COST] / 1000 else 0 end) +
      (select isnull(sum(case [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTCREATIVE].[COST] when 4 then [MKTCREATIVE].[COST] / 1000 end), 0) from dbo.[MKTCREATIVE] inner join dbo.[MKTPACKAGECREATIVE] on [MKTPACKAGECREATIVE].[CREATIVEID] = [MKTCREATIVE].[ID] where [MKTPACKAGECREATIVE].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] in (0, 4)) +
      (select isnull(sum(case [MKTDOCUMENT].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTDOCUMENT].[COST] when 4 then [MKTDOCUMENT].[COST] / 1000 end), 0) from dbo.[MKTDOCUMENT] inner join dbo.[MKTPACKAGEDOCUMENT] on [MKTPACKAGEDOCUMENT].[DOCUMENTID] = [MKTDOCUMENT].[ID] where [MKTPACKAGEDOCUMENT].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTDOCUMENT].[COSTDISTRIBUTIONMETHODCODE] in (0, 4)) +
      (select isnull(sum(case [MKTMATERIAL].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTMATERIAL].[COST] when 4 then [MKTMATERIAL].[COST] / 1000 end), 0) from dbo.[MKTMATERIAL] inner join dbo.[MKTPACKAGEMATERIAL] on [MKTPACKAGEMATERIAL].[MATERIALID] = [MKTMATERIAL].[ID] where [MKTPACKAGEMATERIAL].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTMATERIAL].[COSTDISTRIBUTIONMETHODCODE] in (0, 4)) +
      (select isnull(sum(case [MKTEXPENSE].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTEXPENSE].[COST] when 4 then [MKTEXPENSE].[COST] / 1000 end), 0) from dbo.[MKTEXPENSE] inner join dbo.[MKTPACKAGEEXPENSE] on [MKTPACKAGEEXPENSE].[EXPENSEID] = [MKTEXPENSE].[ID] where [MKTPACKAGEEXPENSE].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTEXPENSE].[COSTDISTRIBUTIONMETHODCODE] in (0, 4)),
    @SITEID = [MKTPACKAGE].[SITEID],
    @EXPORTDEFINITIONID = isnull([LETTERCODE].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID]),
    @CHANNELSOURCECODE = [MKTPACKAGE].[CHANNELSOURCECODE],
    @CHANNELSOURCECODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
    @TSLONG = [MKTPACKAGE].[TSLONG],
    @BASECURRENCYID = [MKTPACKAGE].[BASECURRENCYID]
  from dbo.[MKTPACKAGE]
  left outer join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID]
  left outer join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
  where [MKTPACKAGE].[ID] = @ID;

  if @DATALOADED = 1
    begin
      set @TOTALCOSTPERPIECE = (case @COSTDISTRIBUTIONMETHODCODE when 0 then @COST when 4 then @COST / 1000 else 0 end) + @INSERTCOSTPERPIECE;

      set @SITEREQUIRED = 0; -- obsolete (must be checked on a form-by-form basis)

      set @SITECANBECHANGED = 1; -- obsolete


      if exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [PACKAGEID] = @ID)
      or exists(select top 1 1 from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [PACKAGEID] = @ID)
      or exists(select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
      or exists(select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
      or exists(select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
        begin
          -- if the package is in use by any mailings, the letter can be changed only if the package is only in use

          -- by one type of mailing (appeal, acknowledgement, or membership)

          declare @MAILINGTYPECODES table ([MAILINGTYPECODE] tinyint);

          insert into @MAILINGTYPECODES
            select distinct 
              [MKTSEGMENTATION].[MAILINGTYPECODE]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
            where [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @ID;         

          insert into @MAILINGTYPECODES
            select distinct 
              [MKTSEGMENTATION].[MAILINGTYPECODE]
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
            inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
            where [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @ID;

          if exists(select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
            insert into @MAILINGTYPECODES values (1);

          if exists(select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
            insert into @MAILINGTYPECODES values (2);

          if exists(select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [PACKAGEID] = @ID)
            insert into @MAILINGTYPECODES values (3);

          if (select count(distinct [MAILINGTYPECODE]) from @MAILINGTYPECODES) = 1
            begin
              set @CONTENTCANBECHANGED = 1;
              set @CONTENTMAILINGTYPECODEALLOWED = (select top 1 [MAILINGTYPECODE] from @MAILINGTYPECODES);
            end
          else
            begin
              set @CONTENTCANBECHANGED = 0;
              set @CONTENTMAILINGTYPECODEALLOWED = 255;
            end

          -- if the package is in use on an acknowledgement mailing, its letter cannot be changed

          if exists(select top 1 1 
                    from dbo.[MKTSEGMENTATIONSEGMENT]
                    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
                    inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                    where [MKTSEGMENTATION].[MAILINGTYPECODE] = 1
                    and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @ID)
          or 
             exists(select top 1 1 
                    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
                    inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                    where [MKTSEGMENTATION].[MAILINGTYPECODE] = 1
                    and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @ID)
            set @CONTENTCANBECHANGED = 0;
        end
      else
        begin
          set @CONTENTCANBECHANGED = 1;
          set @CONTENTMAILINGTYPECODEALLOWED = 255;
        end
    end

  set @ACKNOWLEDGEMENTMAILINGSINSTALLED = dbo.[UFN_MKTCOMMON_ACKNOWLEDGEMENTMAILINGSINSTALLED]();
  set @MEMBERSHIPMAILINGSINSTALLED = dbo.[UFN_MKTCOMMON_MEMBERSHIPMAILINGSINSTALLED]();
  set @SPONSORSHIPMAILINGSINSTALLED = dbo.[UFN_MKTCOMMON_SPONSORSHIPMAILINGSINSTALLED]();

  select @PACKAGEPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 2;
  select @CHANNELPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 3;

  return 0;