USP_DATAFORMTEMPLATE_VIEW_MKTLISTSEGMENTATIONCONTEXT

The load procedure used by the view dataform template "List (Marketing Effort Context) View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(72)	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(43)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CODE	nvarchar(10)	INOUT	Code
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@CATEGORY	nvarchar(100)	INOUT	Category
@VENDOR	nvarchar(154)	INOUT	Vendor
@RECORDSOURCE	nvarchar(255)	INOUT	Record source
@BASERENTALCOST	money	INOUT	Base rental cost
@BASERENTALCOSTBASIS	nvarchar(50)	INOUT	Base rental cost basis
@BASEEXCHANGECOST	money	INOUT	Base exchange cost
@BASEEXCHANGECOSTBASIS	nvarchar(50)	INOUT	Base exchange cost basis
@ISINACTIVE	bit	INOUT	Status
@HASROOTQUERIES	bit	INOUT	Has root queries
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@CURRENCY	nvarchar(110)	INOUT	Currency
@ISINUSE	bit	INOUT	Is in use
@SITE	nvarchar(1024)	INOUT	Site

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTLISTSEGMENTATIONCONTEXT]
(
  @ID nvarchar(72),
  @DATALOADED bit = 0 output,
  @NAME nvarchar(43) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @CODE nvarchar(10) = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @CATEGORY nvarchar(100) = null output,
  @VENDOR nvarchar(154) = null output,
  @RECORDSOURCE nvarchar(255) = null output,
  @BASERENTALCOST money = null output,
  @BASERENTALCOSTBASIS nvarchar(50) = null output,
  @BASEEXCHANGECOST money = null output,
  @BASEEXCHANGECOSTBASIS nvarchar(50) = null output,
  @ISINACTIVE bit = null output,
  @HASROOTQUERIES bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CURRENCY nvarchar(110) = null output,
  @ISINUSE bit = null output,
  @SITE nvarchar(1024) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAME = [MKTLIST].[NAME],
    @DESCRIPTION = [MKTLIST].[DESCRIPTION],
    @CODE = [MKTLIST].[CODE],
    @CODEVALUEID = [MKTLIST].[PARTDEFINITIONVALUESID],
    @CATEGORY = [MKTLISTCATEGORYCODE].[DESCRIPTION],
    @VENDOR = [CONSTITUENT].[NAME],
    @RECORDSOURCE = [QUERYVIEWCATALOG].[DISPLAYNAME],
    @BASERENTALCOST = [MKTLIST].[BASERENTALCOST],
    @BASERENTALCOSTBASIS = [MKTLIST].[BASERENTALCOSTBASIS],
    @BASEEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
    @BASEEXCHANGECOSTBASIS = [MKTLIST].[BASEEXCHANGECOSTBASIS],
    @ISINACTIVE = [MKTLIST].[ISINACTIVE],
    @HASROOTQUERIES = (case when exists(select top 1 1
                                        from dbo.[MKTSEGMENTLIST]
                                        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
                                        where [MKTSEGMENTLIST].[LISTID] = [MKTLIST].[ID]
                                        and [MKTSEGMENTLIST].[TYPECODE] = 0
                                        and [MKTSEGMENTLIST].[STATUSCODE] = 3
                                        and [QUERYVIEWCATALOG].[ROOTOBJECT] = 1)
                       then 1 else 0 end),
    @BASECURRENCYID = [MKTLIST].[BASECURRENCYID],
    @CURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTLIST].[BASECURRENCYID]),
    @ISINUSE = case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTID] in (  select [SEGMENTID] from dbo.[MKTSEGMENTLIST] where [LISTID]= [MKTLIST].[ID] )) then 1 else 0 end,
    @SITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTLIST].[SITEID])
  from dbo.[MKTLIST]
  left join dbo.[MKTLISTCATEGORYCODE] on [MKTLISTCATEGORYCODE].[ID] = [MKTLIST].[LISTCATEGORYCODEID]
  left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTLIST].[VENDORID]
  left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTLIST].[RECORDSOURCEID]
  where [MKTLIST].[ID] = convert(uniqueidentifier, left(@ID, 36));

  return 0;