UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2

Returns the total cost, in organization or base currency, of all inserts for a given package with the distribution method specified.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@PACKAGEID	uniqueidentifier	IN
@COSTDISTRIBUTIONMETHODCODE	tinyint	IN
@CURRENCYCODE	tinyint	IN
Definition

Copy


CREATE function dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]
(
  @PACKAGEID uniqueidentifier,
  @COSTDISTRIBUTIONMETHODCODE tinyint,
  @CURRENCYCODE tinyint = 0 -- 0 is base, 1 is organization

)
returns money
as
begin
  declare @COST money;
  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  if @CURRENCYCODE = 0
    set @COST = 
      isnull((select [MKTCREATIVE].[COST] from dbo.[MKTPACKAGE] inner join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID] where [MKTPACKAGE].[ID] = @PACKAGEID and [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE), 0) +
      (select isnull(sum([MKTCREATIVE].[COST]), 0) from dbo.[MKTPACKAGECREATIVE] inner join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGECREATIVE].[CREATIVEID] where [MKTPACKAGECREATIVE].[PACKAGEID] = @PACKAGEID and [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTDOCUMENT].[COST]), 0) from dbo.[MKTPACKAGEDOCUMENT] inner join dbo.[MKTDOCUMENT] on [MKTDOCUMENT].[ID] = [MKTPACKAGEDOCUMENT].[DOCUMENTID] where [MKTPACKAGEDOCUMENT].[PACKAGEID] = @PACKAGEID and [MKTDOCUMENT].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTMATERIAL].[COST]), 0) from dbo.[MKTPACKAGEMATERIAL] inner join dbo.[MKTMATERIAL] on [MKTMATERIAL].[ID] = [MKTPACKAGEMATERIAL].[MATERIALID] where [MKTPACKAGEMATERIAL].[PACKAGEID] = @PACKAGEID and [MKTMATERIAL].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTEXPENSE].[COST]), 0) from dbo.[MKTPACKAGEEXPENSE] inner join dbo.[MKTEXPENSE] on [MKTEXPENSE].[ID] = [MKTPACKAGEEXPENSE].[EXPENSEID] where [MKTPACKAGEEXPENSE].[PACKAGEID] = @PACKAGEID and [MKTEXPENSE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE)
  else
    set @COST = 
      isnull((select [MKTCREATIVE].[ORGANIZATIONCOST] from dbo.[MKTPACKAGE] inner join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID] where [MKTPACKAGE].[ID] = @PACKAGEID and [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE), 0) +
      (select isnull(sum([MKTCREATIVE].[ORGANIZATIONCOST]), 0) from dbo.[MKTPACKAGECREATIVE] inner join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGECREATIVE].[CREATIVEID] where [MKTPACKAGECREATIVE].[PACKAGEID] = @PACKAGEID and [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTDOCUMENT].[ORGANIZATIONCOST]), 0) from dbo.[MKTPACKAGEDOCUMENT] inner join dbo.[MKTDOCUMENT] on [MKTDOCUMENT].[ID] = [MKTPACKAGEDOCUMENT].[DOCUMENTID] where [MKTPACKAGEDOCUMENT].[PACKAGEID] = @PACKAGEID and [MKTDOCUMENT].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTMATERIAL].[ORGANIZATIONCOST]), 0) from dbo.[MKTPACKAGEMATERIAL] inner join dbo.[MKTMATERIAL] on [MKTMATERIAL].[ID] = [MKTPACKAGEMATERIAL].[MATERIALID] where [MKTPACKAGEMATERIAL].[PACKAGEID] = @PACKAGEID and [MKTMATERIAL].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE) +
      (select isnull(sum([MKTEXPENSE].[ORGANIZATIONCOST]), 0) from dbo.[MKTPACKAGEEXPENSE] inner join dbo.[MKTEXPENSE] on [MKTEXPENSE].[ID] = [MKTPACKAGEEXPENSE].[EXPENSEID] where [MKTPACKAGEEXPENSE].[PACKAGEID] = @PACKAGEID and [MKTEXPENSE].[COSTDISTRIBUTIONMETHODCODE] = @COSTDISTRIBUTIONMETHODCODE)

  --If per thousand cost, then divide cost by 1000 to get individual cost per piece.

  if @COSTDISTRIBUTIONMETHODCODE = 4
    set @COST = @COST/@PACKAGEPERTHOUSANDAMOUNT;

  return @COST;
end