USP_MKTSEGMENTATION_GETTOTALPACKAGECOSTS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETTOTALPACKAGECOSTS]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @ACTIVE bit;

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

  declare @PACKAGECOSTS table (
    [ID] uniqueidentifier,
    [COSTPERPIECE] money,
    [COSTPERRESPONSE] money,
    [COSTPEREFFORT] money,
    [ORGANIZATIONCOSTPERPIECE] money,
    [ORGANIZATIONCOSTPERRESPONSE] money,
    [ORGANIZATIONCOSTPEREFFORT] money);

  if @ACTIVE = 1
    insert into @PACKAGECOSTS
    select
      [MKTSEGMENTATIONPACKAGE].[PACKAGEID],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] as [COSTPERPIECE],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE] as [COSTPERRESPONSE],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT] as [COSTPEREFFORT],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 0 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] as [ORGANIZATIONCOSTPERPIECE],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE] as [ORGANIZATIONCOSTPERRESPONSE],
      (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end) + [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT] as [ORGANIZATIONCOSTPEREFFORT]
    from dbo.[MKTSEGMENTATIONPACKAGE]
    where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID;
  else
    insert into @PACKAGECOSTS
    exec dbo.[USP_MKTSEGMENTATION_GETPACKAGECOSTS] @SEGMENTATIONID;

  select
    isnull(sum([COSTPERPIECE]), 0) as [TOTALCOSTPERPIECE],
    isnull(sum([COSTPERRESPONSE]), 0) as [TOTALCOSTPERRESPONSE],
    isnull(sum([COSTPEREFFORT]), 0) as [TOTALCOSTPEREFFORT],
    isnull(sum([ORGANIZATIONCOSTPERPIECE]), 0) as [ORGANIZATIONTOTALCOSTPERPIECE],
    isnull(sum([ORGANIZATIONCOSTPERRESPONSE]), 0) as [ORGANIZATIONTOTALCOSTPERRESPONSE],
    isnull(sum([ORGANIZATIONCOSTPEREFFORT]), 0) as [ORGANIZATIONTOTALCOSTPEREFFORT]    
  from @PACKAGECOSTS;

  return 0;