USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENT

The load procedure used by the view dataform template "Segment View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CODE	nvarchar(10)	INOUT	Code
@ISINUSE	bit	INOUT	Is in use
@RECORDCOUNT	int	INOUT	Quantity
@DATEREFRESHED	datetime	INOUT	Current as of
@SEGMENTREFRESHPROCESSID	uniqueidentifier	INOUT	Segment refresh process ID
@SEGMENTTYPECODE	tinyint	INOUT	Segment type code
@SEGMENTTYPE	nvarchar(100)	INOUT	Segment type
@PARENTLISTID	uniqueidentifier	INOUT	List
@PARENTLIST	nvarchar(50)	INOUT	List
@RENTALQUANTITY	int	INOUT	Rental quantity
@RENTALCOST	money	INOUT	Rental cost
@RENTALCOSTBASISCODE	tinyint	INOUT	Rental cost basis code
@RENTALCOSTBASIS	nvarchar(100)	INOUT	Rental cost basis
@EXCHANGEQUANTITY	int	INOUT	Exchange quantity
@EXCHANGECOST	money	INOUT	Exchange cost
@EXCHANGECOSTBASISCODE	tinyint	INOUT	Exchange cost basis code
@EXCHANGECOSTBASIS	nvarchar(100)	INOUT	Exchange cost basis
@RECEIVEDVIACODE	tinyint	INOUT	Received via code
@RECEIVEDVIA	nvarchar(100)	INOUT	Received via
@CATEGORY	nvarchar(100)	INOUT	Category
@TYPECODE	tinyint	INOUT	Type code
@STATUSCODE	tinyint	INOUT	Status code
@SEGMENTLISTID	uniqueidentifier	INOUT	List segment ID
@SEGMENTLISTIMPORTPROCESSID	uniqueidentifier	INOUT	List segment import process ID
@SEGMENTLISTDEDUPEPROCESSID	uniqueidentifier	INOUT	List segment dedupe process ID
@ISCONSOLIDATED	bit	INOUT	Is consolidated?
@SHOWINQUERYDESIGNER	bit	INOUT	Show in query designer?
@RECORDTYPENAME	nvarchar(50)	INOUT	Record type name
@QUERYVIEWCATALOGID	uniqueidentifier	INOUT	Query view ID
@PARENTSEGMENTID	uniqueidentifier	INOUT	Parent segment ID
@PARENTSEGMENTNAME	nvarchar(100)	INOUT	Parent segment name
@DEDUPLISTS	bit	INOUT	Process list duplicates
@GROUPS	nvarchar(255)	INOUT	Groups
@TOTALCOST	money	INOUT	Total cost
@ORDERDATE	datetime	INOUT	Order date
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@NUMBEROFCONTACTS	smallint	INOUT	# of contacts
@DUPLICATERECORDCOUNT	int	INOUT	Duplicates
@LISTCODE	nvarchar(255)	INOUT	List code
@LISTCODEVALUEID	uniqueidentifier	INOUT	List code part definition values ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@SITEID	uniqueidentifier	INOUT	Site ID
@SITE	nvarchar(100)	INOUT	Site
@CODEVALUEID	uniqueidentifier	INOUT
@ISHISTORICAL	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENT]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @CODE nvarchar(10) = null output,
  @ISINUSE bit = null output,
  @RECORDCOUNT int = null output,
  @DATEREFRESHED datetime = null output,
  @SEGMENTREFRESHPROCESSID uniqueidentifier = null output,
  @SEGMENTTYPECODE tinyint = null output,
  @SEGMENTTYPE nvarchar(100) = null output,
  @PARENTLISTID uniqueidentifier = null output,
  @PARENTLIST nvarchar(50) = null output,
  @RENTALQUANTITY integer = null output,
  @RENTALCOST money = null output,
  @RENTALCOSTBASISCODE tinyint = null output,
  @RENTALCOSTBASIS nvarchar(100) = null output,
  @EXCHANGEQUANTITY integer = null output,
  @EXCHANGECOST money = null output,
  @EXCHANGECOSTBASISCODE tinyint = null output,
  @EXCHANGECOSTBASIS nvarchar(100) = null output,
  @RECEIVEDVIACODE tinyint = null output,
  @RECEIVEDVIA nvarchar(100) = null output,
  @CATEGORY nvarchar(100) = null output,
  @TYPECODE tinyint = null output,
  @STATUSCODE tinyint = null output,
  @SEGMENTLISTID uniqueidentifier = null output,
  @SEGMENTLISTIMPORTPROCESSID uniqueidentifier = null output,
  @SEGMENTLISTDEDUPEPROCESSID uniqueidentifier = null output,
  @ISCONSOLIDATED bit = null output,
  @SHOWINQUERYDESIGNER bit = null output,
  @RECORDTYPENAME nvarchar(50) = null output,
  @QUERYVIEWCATALOGID uniqueidentifier = null output,
  @PARENTSEGMENTID uniqueidentifier = null output,
  @PARENTSEGMENTNAME nvarchar(100) = null output,
  @DEDUPLISTS bit = null output,
  @GROUPS nvarchar(255) = null output,
  @TOTALCOST money = null output,
  @ORDERDATE datetime = null output,
  @EXPIRATIONDATE datetime = null output,
  @NUMBEROFCONTACTS smallint = null output,
  @DUPLICATERECORDCOUNT int = null output,
  @LISTCODE nvarchar(255) = null output,
  @LISTCODEVALUEID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @SITEID uniqueidentifier = null output,
  @SITE nvarchar(100) = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @ISHISTORICAL bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAME = [MKTSEGMENT].[NAME],
    @DESCRIPTION = [MKTSEGMENT].[DESCRIPTION],
    @SITEID = [MKTSEGMENT].[SITEID],
    @SITE = [SITE].[NAME],
    @CODE = [MKTSEGMENT].[CODE],
    @ISINUSE = dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]),
    @RECORDCOUNT = 
        (case
            when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then [MKTSEGMENTLIST].[TOTALRECORDCOUNT] 
            when [MKTSEGMENT].[ISHISTORICAL] = 1 then [MKTSEGMENT].[HISTORICALQUANTITY]
            else [IDSETREGISTER].[NUMROWS] 
        end),
    @DATEREFRESHED = coalesce([MKTSEGMENT].[DATEREFRESHED], [IDSETREGISTER].[DATECHANGED], [MKTSEGMENTLIST].[DATECHANGED]),
    @SEGMENTREFRESHPROCESSID = [MKTSEGMENTREFRESHPROCESS].[ID],
    @SEGMENTTYPECODE =  [MKTSEGMENT].[SEGMENTTYPECODE],
    @SEGMENTTYPE = [MKTSEGMENT].[SEGMENTTYPE],
    @PARENTLISTID = [MKTSEGMENTLIST].[LISTID],
    @PARENTLIST = [MKTLIST].[NAME],
    @RENTALQUANTITY = [MKTSEGMENTLIST].[RENTALQUANTITY],
    @RENTALCOST = [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
    @RENTALCOSTBASISCODE = [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
    @RENTALCOSTBASIS = [MKTSEGMENTLIST].[RENTALCOSTBASIS],
    @EXCHANGEQUANTITY = [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
    @EXCHANGECOST = [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
    @EXCHANGECOSTBASISCODE = [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
    @EXCHANGECOSTBASIS = [MKTSEGMENTLIST].[EXCHANGECOSTBASIS],
    @RECEIVEDVIACODE = [MKTSEGMENTLIST].[RECEIVEDVIACODE],
    @RECEIVEDVIA = [MKTSEGMENTLIST].[RECEIVEDVIA],
    @CATEGORY = [MKTSEGMENTCATEGORYCODE].[DESCRIPTION],
    @TYPECODE = [MKTSEGMENTLIST].[TYPECODE],
    @STATUSCODE = [MKTSEGMENTLIST].[STATUSCODE],
    @SEGMENTLISTID = [MKTSEGMENTLIST].[ID],
    @SEGMENTLISTIMPORTPROCESSID = [MKTSEGMENTLISTIMPORTPROCESS].[ID],
    @SEGMENTLISTDEDUPEPROCESSID = [MKTSEGMENTLISTDEDUPEPROCESS].[ID],
    @ISCONSOLIDATED = (case when [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] is null then 0 else 1 end),
    @SHOWINQUERYDESIGNER = [QUERYVIEWCATALOG].[ROOTOBJECT],
    @RECORDTYPENAME = [RECORDTYPE].[NAME],
    @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
    @PARENTSEGMENTID = [PARENTSEGMENT].[ID],
    @PARENTSEGMENTNAME = [PARENTSEGMENT].[NAME],
    @DEDUPLISTS = cast((case when [MKTSEGMENTLISTDEDUPEPROCESS].[ID] is null then 0 else (case when dbo.[UFN_MKTSEGMENTLIST_DUPLICATEFIELDSEXIST]([MKTSEGMENTLIST].[ID]) = 1 then 1 else 0 end) end) as bit),
    @GROUPS = (select stuff((select ', ' + [MKTSEGMENTGROUP].[NAME]
                             from dbo.[MKTSEGMENTGROUP]
                             inner join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
                             where [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENT].[ID]
                             order by [MKTSEGMENTGROUP].[NAME]
                             for xml path(''), type
                             ).value('.', 'varchar(max)')
                             , 1, 2, '')),
    @TOTALCOST = 
      --Total base cost

      ((case when [MKTLIST].[BASERENTALCOSTBASISCODE] = 1 then  --Per thousand

          cast([MKTLIST].[BASERENTALCOST] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[BASERENTALCOST]
        end)
       +
       (case when [MKTLIST].[BASEEXCHANGECOSTBASISCODE] = 1 then  --Per thousand

          cast([MKTLIST].[BASEEXCHANGECOST] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[BASEEXCHANGECOST]
        end))
      +
      --Total cost adjustments

      ((case when [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] = 1 then  --Per thousand

          cast([MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT]
        end)
       +
       (case when [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] = 1 then  --Per thousand

          cast([MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT]
        end)),
    @ORDERDATE = [MKTSEGMENTLIST].[ORDERDATE],
    @EXPIRATIONDATE = [MKTSEGMENTLIST].[EXPIRATIONDATE],
    @NUMBEROFCONTACTS = [MKTSEGMENTLIST].[NUMBEROFCONTACTS],
    @DUPLICATERECORDCOUNT = [MKTSEGMENTLIST].[DUPLICATERECORDCOUNT],
    @LISTCODE = case when [MKTSEGMENTLIST].[ID] is null then [MKTRECORDSOURCE].[CODE] else [MKTLIST].[CODE] end,
    @LISTCODEVALUEID = case when [MKTSEGMENTLIST].[ID] is null then [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID] else [MKTLIST].[PARTDEFINITIONVALUESID] end,
    @BASECURRENCYID = [MKTSEGMENT].[BASECURRENCYID],
    @CODEVALUEID = [MKTSEGMENT].[PARTDEFINITIONVALUESID],
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL]
  from dbo.[MKTSEGMENT]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left outer join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left outer join dbo.[MKTSEGMENTLISTIMPORTPROCESS] on [MKTSEGMENTLISTIMPORTPROCESS].[SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left outer join dbo.[MKTSEGMENTLISTDEDUPEPROCESS] on [MKTSEGMENTLISTDEDUPEPROCESS].[SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left outer join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
  left outer join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IDSETREGISTER].[RECORDTYPEID] or [RECORDTYPE].[ID] = [MKTSEGMENTLIST].[IDSETRECORDTYPEID]
  left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
  left outer join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTSEGMENT] as [PARENTSEGMENT] on [PARENTSEGMENT].[ID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
  left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  left outer join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
  where [MKTSEGMENT].[ID] = @ID;

  return 0;