USP_MKTSEGMENTATION_GETPACKAGECOSTS

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

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

  select
    [MKTPACKAGE].[ID],
    cast ((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) as decimal(30,8)) -- WI 370811 cast as decimal to be explicit about the precision

     as [COSTPERPIECE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTPACKAGE].[UNITCOST] else 0 end) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, 0) as [COSTPERRESPONSE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTPACKAGE].[UNITCOST] else 0 end) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, 0) as [COSTPEREFFORT],
    cast ((case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
             when 0 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] 
             when 4 then [MKTPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT
             else 0 end) + 
           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, 1) + 
           -- Add any other package costs using 'Per thousand'

           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, 1) as decimal(30,8)) -- WI 370811 cast as decimal to be explicit about the precision

     as [ORGANIZATIONCOSTPERPIECE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else 0 end) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, 1) as [ORGANIZATIONCOSTPERRESPONSE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else 0 end) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, 1) as [ORGANIZATIONCOSTPEREFFORT]

  from dbo.[MKTSEGMENTATIONPACKAGE]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
  where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID;

  return 0;