USP_MKTPACKAGE_GETSUMMARYINFO

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

  select
    [MKTPACKAGE].[NAME],
    [MKTPACKAGE].[DESCRIPTION],
    [MKTPACKAGE].[CODE],
    [MKTPACKAGE].[CHANNELCODE],
    [MKTPACKAGE].[CHANNEL],
    [MKTPACKAGECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    [MKTPACKAGE].[UNITCOST] [COST],
    [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE],
    [MKTPACKAGE].[COSTDISTRIBUTIONMETHOD],
    (select count([MKTPACKAGECREATIVE].[ID]) from dbo.[MKTPACKAGECREATIVE] where [MKTPACKAGECREATIVE].[PACKAGEID] = [MKTPACKAGE].[ID]) as [NUMCREATIVES],
    [MKTPACKAGE].[SITEID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTPACKAGE].[SITEID]) as [SITE],
    [MKTPACKAGE].[LETTERCODEID],
    dbo.[UFN_LETTERCODE_GETNAME]([MKTPACKAGE].[LETTERCODEID]) as [LETTER],
    [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID],
    [MKTPACKAGE].[CREATIVEID],
    [MKTCREATIVE].[NAME] as [CREATIVE],
    [MKTPACKAGE].[DOCUMENTFILENAME],
    coalesce([EXPORTDEFINITIONMAIL].[NAME], [EXPORTDEFINITIONEMAIL].[NAME]) as [EXPORTDEFINITION],

    convert(money, case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
                   when 0 then [MKTPACKAGE].[UNITCOST]
                   -- Per Thousand

                   when 4 then [MKTPACKAGE].[UNITCOST]/1000
                   else 0 end
    ) as [COSTPERPIECE],

    convert(money, 
      dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, 0) +    -- Per Piece

      dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, 0)      -- Per Thousand

    ) as [INSERTCOSTPERPIECE],

    convert(money, 
      (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTPACKAGE].[UNITCOST] else 0 end) +
      dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, 0)
    ) as [ADDITIONALPEREFFORTCOST],

    [MKTPACKAGE].[PARTDEFINITIONVALUESID] as [SOURCECODEVALUEID],
    [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] as [CHANNELSOURCECODEVALUEID],
    [MKTPACKAGE].[CHANNELSOURCECODE] as [CHANNELSOURCECODE],
    [MKTPACKAGE].[BASECURRENCYID] [CURRENCYID],
    (select count([MKTSEGMENTATIONPACKAGE].[PACKAGEID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]) [NUMEFFORTS],
    (select count([MKTMARKETINGPLANBRIEF].[ID]) from dbo.[MKTMARKETINGPLANBRIEF] where [MKTMARKETINGPLANBRIEF].[PACKAGEID] = [MKTPACKAGE].[ID]) [NUMPLANS],
    (select count([MKTPACKAGEDOCUMENT].[PACKAGEID]) from dbo.[MKTPACKAGEDOCUMENT] where [MKTPACKAGEDOCUMENT].[PACKAGEID] = [MKTPACKAGE].[ID]) [NUMDOCUMENTS],
    (select count([MKTPACKAGEMATERIAL].[PACKAGEID]) from dbo.[MKTPACKAGEMATERIAL] where [MKTPACKAGEMATERIAL].[PACKAGEID] = [MKTPACKAGE].[ID]) [NUMMATERIAL],
    (select count([MKTPACKAGEEXPENSE].[PACKAGEID]) from dbo.[MKTPACKAGEEXPENSE] where [MKTPACKAGEEXPENSE].[PACKAGEID] = [MKTPACKAGE].[ID]) [NUMEXPENSES],
    dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTPACKAGE].[BASECURRENCYID]) [CURRENCY]

  from dbo.[MKTPACKAGE]
  left outer join dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [MKTPACKAGE].[PACKAGECATEGORYCODEID]
  left outer join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
  left outer join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID]
  left outer join dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONMAIL] on [EXPORTDEFINITIONMAIL].[ID] = isnull(case [MKTPACKAGE].[CHANNELCODE] when 0 then [LETTERCODE].[EXPORTDEFINITIONID] else null end, [MKTPACKAGE].[EXPORTDEFINITIONID])
  left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0 and [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] is not null)
  left outer join dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONEMAIL] on [EXPORTDEFINITIONEMAIL].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
  where [MKTPACKAGE].[ID] = @ID;

  return 0;
end