USP_DATAFORMTEMPLATE_VIEW_MKTVENDOR

The load procedure used by the view dataform template "Marketing Vendor 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
@DESCRIPTION	nvarchar(255)	INOUT	Description
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Web site address
@ISINACTIVE	bit	INOUT	Status
@PICTURETHUMBNAIL	varbinary	INOUT	Logo thumbnail
@ADDRESS	nvarchar(300)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	Phone
@PHONENUMBERTYPE	nvarchar(100)	INOUT	Phone number type
@EMAILADDRESSTYPE	nvarchar(100)	INOUT	Email address type
@PRIMARYCONTACTID	uniqueidentifier	INOUT	Primary contact ID
@PRIMARYCONTACT	nvarchar(700)	INOUT	Primary contact
@PRIMARYCONTACT_PHONENUMBER	nvarchar(100)	INOUT	Primary contact phone number
@PRIMARYCONTACT_PHONENUMBERTYPE	nvarchar(100)	INOUT	Primary contact phone number type
@PRIMARYCONTACT_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Primary contact email address
@PRIMARYCONTACT_EMAILADDRESSTYPE	nvarchar(100)	INOUT	Primary contact email address type
@SHOWCREATIVES	bit	INOUT	Show creatives tab
@SHOWLISTS	bit	INOUT	Show lists tab
@SHOWPUBLICMEDIA	bit	INOUT	Show public meda tab
@LOOKUPID	nvarchar(36)	INOUT	Lookup ID
@ISDECEASED	bit	INOUT	ISDECEASED
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@PRIMARYCONTACT_DONOTPHONE	bit	INOUT	PRIMARYCONTACT_DONOTPHONE
@PRIMARYCONTACT_PHONEISCONFIDENTIAL	bit	INOUT	PRIMARYCONTACT_PHONEISCONFIDENTIAL
@PRIMARYCONTACT_DONOTEMAIL	bit	INOUT	PRIMARYCONTACT_DONOTEMAIL

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTVENDOR]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(700) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
  @ISINACTIVE bit = null output,
  @PICTURETHUMBNAIL varbinary(max) = null output,
  @ADDRESS nvarchar(300) = null output,
  @PHONENUMBER nvarchar(100) = null output,
  @PHONENUMBERTYPE nvarchar(100) = null output,
  @EMAILADDRESSTYPE nvarchar(100) = null output,
  @PRIMARYCONTACTID uniqueidentifier = null output,
  @PRIMARYCONTACT nvarchar(700) = null output,
  @PRIMARYCONTACT_PHONENUMBER nvarchar(100) = null output,
  @PRIMARYCONTACT_PHONENUMBERTYPE nvarchar(100) = null output,
  @PRIMARYCONTACT_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @PRIMARYCONTACT_EMAILADDRESSTYPE nvarchar(100) = null output,
  @SHOWCREATIVES bit = null output,
  @SHOWLISTS bit = null output,
  @SHOWPUBLICMEDIA bit = null output,
  @LOOKUPID nvarchar(36) = null output,
  @ISDECEASED bit = null output,
  @DONOTMAIL bit = null output,
  @DONOTEMAIL bit = null output,
  @DONOTPHONE bit = null output,
  @PHONEISCONFIDENTIAL bit = null output,
  @ADDRESSISCONFIDENTIAL bit = null output,
  @ADDRESSID uniqueidentifier = null output,
  @PHONENUMBERID uniqueidentifier = null output,
  @EMAILADDRESSID uniqueidentifier = null output,
  @PRIMARYCONTACT_DONOTPHONE bit = null output,
  @PRIMARYCONTACT_PHONEISCONFIDENTIAL bit = null output,
  @PRIMARYCONTACT_DONOTEMAIL bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select 
    @DATALOADED = 1,
    @NAME = [NAMEFORMAT].[NAME],
    @DESCRIPTION = [VENDOR].[DESCRIPTION],
    @LOOKUPID = [CONSTITUENT].[LOOKUPID],
    @EMAILADDRESS = [EMAILADDRESS].[EMAILADDRESS],
    @WEBADDRESS = [CONSTITUENT].[WEBADDRESS],
    @ISINACTIVE = [CONSTITUENT].[ISINACTIVE],
    @PICTURETHUMBNAIL = [CONSTITUENT].[PICTURETHUMBNAIL],
    @ADDRESS = dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]),
    @DONOTMAIL = [ADDRESS].[DONOTMAIL],
    @ADDRESSISCONFIDENTIAL = [ADDRESS].[ISCONFIDENTIAL],
    @ADDRESSID = [ADDRESS].[ID],
    @PHONENUMBER = dbo.[UFN_PHONE_GETINTERNATIONALNUMBER]([PHONE].[COUNTRYID], [PHONE].[NUMBER]),
    @PHONENUMBERTYPE = dbo.[UFN_PHONETYPECODE_GETDESCRIPTION]([PHONE].[PHONETYPECODEID]),
    @DONOTPHONE = [PHONE].[DONOTCALL],
    @PHONEISCONFIDENTIAL = [PHONE].[ISCONFIDENTIAL],
    @PHONENUMBERID = [PHONE].[ID],
    @EMAILADDRESSTYPE = dbo.[UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION]([EMAILADDRESS].[EMAILADDRESSTYPECODEID]),
    @DONOTEMAIL = [EMAILADDRESS].[DONOTEMAIL],
    @EMAILADDRESSID = [EMAILADDRESS].[ID],
    @PRIMARYCONTACTID = [CONTACT].[ID],
    @PRIMARYCONTACT = [CONTACTNAMEFORMAT].[NAME],
    @PRIMARYCONTACT_PHONENUMBER = dbo.[UFN_PHONE_GETINTERNATIONALNUMBER]([CONTACTPHONE].[COUNTRYID], [CONTACTPHONE].[NUMBER]),
    @PRIMARYCONTACT_PHONENUMBERTYPE = dbo.[UFN_PHONETYPECODE_GETDESCRIPTION]([CONTACTPHONE].[PHONETYPECODEID]),
    @PRIMARYCONTACT_DONOTPHONE = [CONTACTPHONE].[DONOTCALL],
    @PRIMARYCONTACT_PHONEISCONFIDENTIAL = [CONTACTPHONE].[ISCONFIDENTIAL],
    @PRIMARYCONTACT_EMAILADDRESS = [CONTACTEMAILADDRESS].[EMAILADDRESS],
    @PRIMARYCONTACT_EMAILADDRESSTYPE = dbo.[UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION]([CONTACTEMAILADDRESS].[EMAILADDRESSTYPECODEID]),
    @PRIMARYCONTACT_DONOTEMAIL = [CONTACTEMAILADDRESS].[DONOTEMAIL],
    @SHOWCREATIVES = case when [SHOWCREATIVES].[ID] is null then 0 else 1 end,
    @SHOWLISTS = case when [SHOWLISTS].[ID] is null then 0 else 1 end,
    @SHOWPUBLICMEDIA = case when [SHOWPUBLICMEDIA].[ID] is null then 0 else 1 end    
  from dbo.[VENDOR]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [VENDOR].[ID]
  left outer join dbo.[ADDRESS] on [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1
  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
  left outer join dbo.[RELATIONSHIP] on [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = [VENDOR].[ID] and [RELATIONSHIP].[ISPRIMARYCONTACT] = 1
  left outer join dbo.[CONSTITUENT] as [CONTACT] on [CONTACT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  left outer join dbo.[PHONE] as [CONTACTPHONE] on [CONTACTPHONE].[CONSTITUENTID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID] and [CONTACTPHONE].[ISPRIMARY] = 1
  left outer join dbo.[EMAILADDRESS] as [CONTACTEMAILADDRESS] on [CONTACTEMAILADDRESS].[CONSTITUENTID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID] and [CONTACTEMAILADDRESS].[ISPRIMARY] = 1
  left outer join dbo.[MKTVENDORSERVICETYPE] as [SHOWCREATIVES] on [SHOWCREATIVES].[VENDORID] = [VENDOR].[ID] and [SHOWCREATIVES].[SERVICETYPECODE] = 1
  left outer join dbo.[MKTVENDORSERVICETYPE] as [SHOWLISTS] on [SHOWLISTS].[VENDORID] = [VENDOR].[ID] and [SHOWLISTS].[SERVICETYPECODE] = 2
  left outer join dbo.[MKTVENDORSERVICETYPE] as [SHOWPUBLICMEDIA] on [SHOWPUBLICMEDIA].[VENDORID] = [VENDOR].[ID] and [SHOWPUBLICMEDIA].[SERVICETYPECODE] = 3
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([CONSTITUENT].[ID]) as [NAMEFORMAT]
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([CONTACT].[ID]) as [CONTACTNAMEFORMAT]
  where [VENDOR].[ID] = @ID;

  return 0;