USP_MKTSEGMENTATIONPACKAGE_LOAD

CREATE procedure dbo.[USP_MKTSEGMENTATIONPACKAGE_LOAD]
(
  @ID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @ACTIVE bit;

  select
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE]
  from dbo.[MKTSEGMENTATIONPACKAGE]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  where [MKTSEGMENTATIONPACKAGE].[ID] = @ID;

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

      declare @R int;
      exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
      if @R <> 0
        return 1;
    end

  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  select 
    @ACTIVE as [ACTIVE],
    [MKTPACKAGE].[NAME],
    [MKTPACKAGE].[DESCRIPTION],
    [MKTPACKAGE].[SITEID],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[CODE] else [MKTSEGMENTATIONPACKAGE].[CODE] end) as [CODE],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[PARTDEFINITIONVALUESID] else [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] end) as [CODEVALUEID],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[CHANNELSOURCECODE] else [MKTSEGMENTATIONPACKAGE].[CHANNELSOURCECODE] end) as [CHANNELSOURCECODE],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] else [MKTSEGMENTATIONPACKAGE].[CHANNELPARTDEFINITIONVALUESID] end) as [CHANNELSOURCECODEVALUEID],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[UNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end) as [COST],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 then [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] else [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] end) as [COSTDISTRIBUTIONMETHODCODE],
    (case [MKTSEGMENTATION].[ACTIVE] when 0 
      then dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, 0) + 
           -- Add any other package costs using 'Per thousand'

           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, 0) 
      else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] end) as [INSERTCOSTPERPIECE],
    cast ((case [MKTSEGMENTATION].[ACTIVE] 
            when 0 then 
              (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
                 when 0 then [MKTPACKAGE].[UNITCOST] 
                 when 4 then [MKTPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT
                 else 0 end) + 
              dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, 0) +
              -- Add any other package costs using 'Per thousand'

              dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, 0)
            else 
              (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
                when 0 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] 
                when 4 then [MKTSEGMENTATIONPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT
                else 0 end) + 
              [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] end
    ) as decimal(30,8)) as [TOTALCOSTPERPIECE],
    @SEGMENTATIONID as [SEGMENTATIONID],
    [MKTSEGMENTATION].[SOURCECODEID],
    [MKTPACKAGE].[ID] as [PACKAGEID],
    [MKTPACKAGE].[CHANNELCODE],
    [MKTPACKAGE].[PACKAGECATEGORYCODEID] as [CATEGORYCODEID],
    [MKTPACKAGE].[LETTERCODEID],
    cast((case when [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ID] is not null and [MKTSEGMENTATION].[MAILINGTYPECODE] = 1 then 1 else 0 end) as bit) as [LETTERISINUSE],
    dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED]() as [NETCOMMUNITYLINKESTABLISHED],
    [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID],
    [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID],
    [MKTPACKAGE].[DOCUMENTFILENAME],
    [MKTPACKAGE].[CREATIVEID],
    case when [MKTPACKAGE].[CHANNELCODE] = 0 then isnull([LETTERCODE].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID])
         when [MKTPACKAGE].[CHANNELCODE] = 1 then isnull([EMAILEXPORTDEFINITION].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID])
         when [MKTPACKAGE].[CHANNELCODE] in (2, 3) then [MKTPACKAGE].[EXPORTDEFINITIONID]
    end as [EXPORTDEFINITIONID],
    convert(bit, case when [MKTPACKAGE].[CHANNELCODE] = 0 then case when [LETTERCODE].[EXPORTDEFINITIONID] is not null then 1 else 0 end
                      when [MKTPACKAGE].[CHANNELCODE] = 1 then case when [EMAILEXPORTDEFINITION].[EXPORTDEFINITIONID] is not null then 1 else 0 end
                      when [MKTPACKAGE].[CHANNELCODE] in (2, 3) then 0 end)
    as [EXPORTDEFINITIONDERIVEDFROMCONTENT],
    dbo.[UFN_MKTCOMMON_ACKNOWLEDGEMENTMAILINGSINSTALLED]() as [ACKNOWLEDGEMENTMAILINGSINSTALLED],
    dbo.[UFN_MKTCOMMON_MEMBERSHIPMAILINGSINSTALLED]() as [MEMBERSHIPMAILINGSINSTALLED],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    [MKTSEGMENTATIONPACKAGE].[TSLONG],
    [MKTSEGMENTATIONPACKAGE].[BASECURRENCYID],
    (select [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 2) as [PACKAGEPARTDEFINITIONID],
    (select [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 3) as [CHANNELPARTDEFINITIONID],
    dbo.[UFN_MKTCOMMON_SPONSORSHIPMAILINGSINSTALLED]() as [SPONSORSHIPMAILINGSINSTALLED]
  from dbo.[MKTSEGMENTATIONPACKAGE]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  left join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
  left outer join dbo.[MKTEXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on ([EMAILEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
  left outer join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID]
  where [MKTSEGMENTATIONPACKAGE].[ID] = @ID;

  return 0;