USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENTLISTCOSTS

The load procedure used by the view dataform template "Marketing Effort List Segment Cost View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(73)	IN	The input ID parameter used to load the fields defined on the form.
@OVERRIDELISTCOSTS	bit	INOUT	Override list costs
@RENTALCOST	money	INOUT	Rental cost
@RENTALCOSTBASISCODE	tinyint	INOUT	Rental cost basis code
@EXCHANGECOST	money	INOUT	Exchange cost
@EXCHANGECOSTBASISCODE	tinyint	INOUT	Exchange cost basis code
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENTLISTCOSTS]
(
  @ID nvarchar(73),
  @OVERRIDELISTCOSTS bit = null output,
  @RENTALCOST money = null output,
  @RENTALCOSTBASISCODE tinyint = null output,
  @EXCHANGECOST money = null output,
  @EXCHANGECOSTBASISCODE tinyint = null output,
  @DATALOADED bit = 0 output
)
as
begin
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEGMENTID uniqueidentifier;
  declare @LISTID uniqueidentifier;

  set @SEGMENTATIONID = convert(uniqueidentifier, upper(substring(@ID, 1, charindex('|', @ID) - 1)));
  set @SEGMENTID = convert(uniqueidentifier, substring(@ID, charindex('|', @ID) + 1, len(@ID)));
  set @DATALOADED = 0;

  select
    @LISTID = [MKTSEGMENTLIST].[LISTID]
  from dbo.[MKTSEGMENT]
  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  where [MKTSEGMENT].[ID] = @SEGMENTID;

  select top 1
    @OVERRIDELISTCOSTS = [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS]
  from dbo.[MKTSEGMENTATIONSEGMENTLIST]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[ID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTLIST].[LISTID] = @LISTID;

  set @OVERRIDELISTCOSTS = isnull(@OVERRIDELISTCOSTS, 0);

  if @OVERRIDELISTCOSTS = 1 
    select top 1
      @RENTALCOST = [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST],
      @RENTALCOSTBASISCODE = [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOSTBASISCODE],
      @EXCHANGECOST = [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST],
      @EXCHANGECOSTBASISCODE = [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOSTBASISCODE],
      @DATALOADED = 1
    from dbo.[MKTSEGMENTATIONSEGMENTLIST]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[ID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and [MKTSEGMENTLIST].[LISTID] = @LISTID;
  else
    select
      @RENTALCOST = [MKTLIST].[BASERENTALCOST],
      @RENTALCOSTBASISCODE = [MKTLIST].[BASERENTALCOSTBASISCODE],
      @EXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
      @EXCHANGECOSTBASISCODE = [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
      @DATALOADED = 1
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;

  return 0;
end