USP_DATAFORMTEMPLATE_VIEW_MKTVENDORCONTACT

The load procedure used by the view dataform template "Marketing Vendor Contact 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(700)	INOUT	Name
@RELATIONSHIP	nvarchar(100)	INOUT	Relationship
@POSITION	nvarchar(100)	INOUT	Job title
@ADDRESSTYPE	nvarchar(100)	INOUT	Address type
@ADDRESS	nvarchar(300)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	Phone
@PHONENUMBERTYPE	nvarchar(100)	INOUT	Phone number type
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@EMAILADDRESSTYPE	nvarchar(100)	INOUT	Email address type
@LOOKUPID	nvarchar(36)	INOUT	Lookup ID

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTVENDORCONTACT]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(700) = null output,
  @RELATIONSHIP nvarchar(100) = null output,
  @POSITION nvarchar(100) = null output,
  @ADDRESSTYPE nvarchar(100) = null output,
  @ADDRESS nvarchar(300) = null output,
  @PHONENUMBER nvarchar(100) = null output,
  @PHONENUMBERTYPE nvarchar(100) = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @EMAILADDRESSTYPE nvarchar(100) = null output,
  @LOOKUPID nvarchar(36) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select 
    @DATALOADED = 1,
    @NAME = [NAMEFORMAT].[NAME],
    @RELATIONSHIP = [RELATIONSHIPTYPECODE].[DESCRIPTION],
    @POSITION = dbo.UFN_RELATIONSHIPJOBINFO_GETCURRENTJOBTITLE([RELATIONSHIP].[ID]),
    @ADDRESSTYPE = [ADDRESSTYPECODE].[DESCRIPTION],
    @ADDRESS = dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]),
    @PHONENUMBER = [PHONE].[NUMBER],
    @PHONENUMBERTYPE = dbo.[UFN_PHONETYPECODE_GETDESCRIPTION]([PHONE].[PHONETYPECODEID]),
    @EMAILADDRESS = [EMAILADDRESS].[EMAILADDRESS],
    @EMAILADDRESSTYPE = dbo.[UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION]([EMAILADDRESS].[EMAILADDRESSTYPECODEID]),
    @LOOKUPID = [CONSTITUENT].[LOOKUPID]
  from dbo.[RELATIONSHIP]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  left outer join dbo.[ADDRESS] on [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1
  left outer join dbo.[ADDRESSTYPECODE] on [ADDRESSTYPECODE].[ID] = [ADDRESS].[ADDRESSTYPECODEID]
  inner join dbo.[RELATIONSHIPTYPECODE] on [RELATIONSHIPTYPECODE].[ID] = [RELATIONSHIP].[RECIPROCALTYPECODEID]
  left outer join dbo.[PHONE] on [PHONE].[CONSTITUENTID] = [CONSTITUENT].[ID] and [PHONE].[ISPRIMARY] = 1
  left outer join dbo.[EMAILADDRESS] on [EMAILADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [EMAILADDRESS].[ISPRIMARY] = 1
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME](CONSTITUENT.ID) as [NAMEFORMAT]
  where [RELATIONSHIP].[ID] = @ID;

  return 0;