USP_MKTPACKAGE_EMAIL_LOAD

CREATE procedure dbo.[USP_MKTPACKAGE_EMAIL_LOAD]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  begin
    set nocount on;

    declare @CONTENTCANBECHANGED bit;
    declare @CONTENTMAILINGTYPECODEALLOWED tinyint;

    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 email message can be changed only if the package is only in use

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

        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 email message 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

    select
      [NAME],
      [DESCRIPTION],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [CHANNELCODE],
      [PACKAGECATEGORYCODEID] as [CATEGORYCODEID],
      [UNITCOST] as [COST],
      [COSTDISTRIBUTIONMETHODCODE],
      (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)) as [INSERTCOSTPERPIECE],
      [SITEID],
      dbo.UFN_SITEREQUIREDFORUSERONFEATURE(@CURRENTAPPUSERID, '759b0ddc-3ba6-44c3-94b7-21f9bfd37178', 1) as [SITEREQUIRED],
      convert(bit, case when
                      exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [PACKAGEID] = @ID) or
                      exists(select top 1 1 from dbo.[MKTPACKAGECREATIVE] where [PACKAGEID] = @ID)
                     then 0 else 1 end) as [SITECANBECHANGED],
      dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED]() as [NETCOMMUNITYLINKESTABLISHED],
      [NETCOMMUNITYTEMPLATEID],
      [NETCOMMUNITYDATASOURCEID],
      [EXPORTDEFINITIONID],
      @CONTENTCANBECHANGED as [CONTENTCANBECHANGED],
      @CONTENTMAILINGTYPECODEALLOWED as [CONTENTMAILINGTYPECODEALLOWED],
      dbo.[UFN_MKTCOMMON_ACKNOWLEDGEMENTMAILINGSINSTALLED]() as [ACKNOWLEDGEMENTMAILINGSINSTALLED],
      dbo.[UFN_MKTCOMMON_MEMBERSHIPMAILINGSINSTALLED]() as [MEMBERSHIPMAILINGSINSTALLED],
      [CHANNELSOURCECODE],
      [CHANNELPARTDEFINITIONVALUESID] as [CHANNELSOURCECODEVALUEID],
      [TSLONG],
      [BASECURRENCYID],
      (select top 1 [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 2) as [PACKAGEPARTDEFINITIONID],
      (select top 1 [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 3) as [CHANNELPARTDEFINITIONID],
      dbo.[UFN_MKTCOMMON_SPONSORSHIPMAILINGSINSTALLED]() as [SPONSORSHIPMAILINGSINSTALLED]
    from dbo.[MKTPACKAGE]
    where [ID] = @ID;

    return 0;
  end;