USP_DATAFORMTEMPLATE_ADD_MKTVENDORCONTACT

The save procedure used by the add dataform template "Marketing Vendor Contact Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@VENDORID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CONTACTID	uniqueidentifier	IN	Full name
@CONTACT_LASTNAME	nvarchar(100)	IN	Last name
@CONTACT_FIRSTNAME	nvarchar(50)	IN	First name
@CONTACT_MIDDLENAME	nvarchar(50)	IN	Middle name
@CONTACT_TITLECODEID	uniqueidentifier	IN	Title
@CONTACT_SUFFIXCODEID	uniqueidentifier	IN	Suffix
@CONTACT_NICKNAME	nvarchar(50)	IN	Nickname
@CONTACT_ADDRESS_TYPECODEID	uniqueidentifier	IN	Address type
@CONTACT_ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@CONTACT_ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@CONTACT_ADDRESS_CITY	nvarchar(50)	IN	City
@CONTACT_ADDRESS_STATEID	uniqueidentifier	IN	State
@CONTACT_ADDRESS_POSTCODE	nvarchar(12)	IN	Post code
@CONTACT_ADDRESS_DONOTMAIL	bit	IN	Do not send mail to this address
@CONTACT_ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@CONTACT_PHONE_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@CONTACT_PHONE_NUMBER	nvarchar(100)	IN	Phone number
@CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Email type
@CONTACT_EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@CONTACT_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@CONTACT_RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@CONTACTTYPECODEID	uniqueidentifier	IN	Contact type
@CONTACT_POSITION	nvarchar(100)	IN	Job title
@ISPRIMARYCONTACT	bit	IN	Set as primary contact
@CONTACT_TITLE2CODEID	uniqueidentifier	IN	Title 2
@CONTACT_SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTVENDORCONTACT]
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @VENDORID uniqueidentifier,
  @CONTACTID uniqueidentifier = null,
  @CONTACT_LASTNAME nvarchar(100) = '',
  @CONTACT_FIRSTNAME nvarchar(50) = '',
  @CONTACT_MIDDLENAME nvarchar(50) = '',
  @CONTACT_TITLECODEID uniqueidentifier = null,
  @CONTACT_SUFFIXCODEID uniqueidentifier = null,
  @CONTACT_NICKNAME nvarchar(50) = '',
  @CONTACT_ADDRESS_TYPECODEID uniqueidentifier = null,
  @CONTACT_ADDRESS_COUNTRYID uniqueidentifier = null,
  @CONTACT_ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @CONTACT_ADDRESS_CITY nvarchar(50) = '',
  @CONTACT_ADDRESS_STATEID uniqueidentifier = null,
  @CONTACT_ADDRESS_POSTCODE nvarchar(12) = '',
  @CONTACT_ADDRESS_DONOTMAIL bit = 0,
  @CONTACT_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
  @CONTACT_PHONE_PHONETYPECODEID uniqueidentifier = null,
  @CONTACT_PHONE_NUMBER nvarchar(100) = '',
  @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
  @CONTACT_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @CONTACT_RELATIONSHIPTYPECODEID uniqueidentifier = null,
  @CONTACT_RECIPROCALTYPECODEID uniqueidentifier = null,
  @CONTACTTYPECODEID uniqueidentifier = null,
  @CONTACT_POSITION nvarchar(100) = '',
  @ISPRIMARYCONTACT bit = 0,
  @CONTACT_TITLE2CODEID uniqueidentifier = null,
  @CONTACT_SUFFIX2CODEID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SITES xml;

  begin try
    if @ID is null set @ID = newid();

    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    if @CONTACTID is null
      begin
        set @CONTACTID = newid();

        -- insert the contact's base constituent information

        insert into dbo.[CONSTITUENT]
        (
          [ID],
          [ISORGANIZATION],
          [KEYNAME],
          [FIRSTNAME],
          [MIDDLENAME],
          [NICKNAME],
          [TITLECODEID],
          [TITLE2CODEID],
          [SUFFIXCODEID],
          [SUFFIX2CODEID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @CONTACTID,
          0,
          @CONTACT_LASTNAME,
          @CONTACT_FIRSTNAME,
          @CONTACT_MIDDLENAME,
          @CONTACT_NICKNAME,
          @CONTACT_TITLECODEID,
          @CONTACT_TITLE2CODEID,
          @CONTACT_SUFFIXCODEID,
          @CONTACT_SUFFIX2CODEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

        -- insert the contact's primary address (only if one was specified)

        if @CONTACT_ADDRESS_TYPECODEID is not null or 
           @CONTACT_ADDRESS_COUNTRYID is not null or
           @CONTACT_ADDRESS_ADDRESSBLOCK <> '' or
           @CONTACT_ADDRESS_CITY <> '' or
           @CONTACT_ADDRESS_STATEID is not null or
           @CONTACT_ADDRESS_POSTCODE <> '' or
           @CONTACT_ADDRESS_DONOTMAIL <> 0 or
           @CONTACT_ADDRESS_DONOTMAILREASONCODEID is not null
          insert into dbo.[ADDRESS] (
            [CONSTITUENTID], 
            [ISPRIMARY],
            [ADDRESSTYPECODEID],
            [COUNTRYID],
            [ADDRESSBLOCK],
            [CITY],
            [STATEID],
            [POSTCODE],
            [DONOTMAIL],
            [DONOTMAILREASONCODEID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            @CONTACTID,
            1,
            @CONTACT_ADDRESS_TYPECODEID,
            @CONTACT_ADDRESS_COUNTRYID,
            @CONTACT_ADDRESS_ADDRESSBLOCK,
            @CONTACT_ADDRESS_CITY,
            @CONTACT_ADDRESS_STATEID,
            @CONTACT_ADDRESS_POSTCODE,
            @CONTACT_ADDRESS_DONOTMAIL,
            @CONTACT_ADDRESS_DONOTMAILREASONCODEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

        -- insert the contact's primary phone (only if one was specified)

        if (@CONTACT_PHONE_PHONETYPECODEID is not null) or (@CONTACT_PHONE_NUMBER <> '')
          insert into dbo.[PHONE] (
            [CONSTITUENTID],
            [PHONETYPECODEID],
            [NUMBER],
            [ISPRIMARY],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            @CONTACTID,
            @CONTACT_PHONE_PHONETYPECODEID,
            @CONTACT_PHONE_NUMBER,
            1,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

        -- insert the primary email address (only if one was specified)

        if (@CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (@CONTACT_EMAILADDRESS_EMAILADDRESS <> '')
          insert into dbo.[EMAILADDRESS]
          (
            [CONSTITUENTID],
            [EMAILADDRESSTYPECODEID],
            [EMAILADDRESS],
            [ISPRIMARY],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          )
          values
          (
            @CONTACTID,
            @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID,
            @CONTACT_EMAILADDRESS_EMAILADDRESS,
            1,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

        -- start security groups

        exec dbo.[USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD]
            @APPUSERID = @CURRENTAPPUSERID,
            @DATAFORMTEMPLATEID = 'D6DCE820-315F-4eb3-A673-63103C0ECB63',
            @CONSTITUENTID = @CONTACTID,
            @DATEADDEDTOUSE = @CURRENTDATE,
            @CHANGEAGENTID = @CHANGEAGENTID;
        -- end security groups


        -- start sites

        -- copy sites from vendor to contact

        set @SITES = dbo.[UFN_CONSTITUENT_GETSITES_TOITEMLISTXML](@VENDORID);

        if @SITES is not null
          begin
            set @SITES.modify('delete /SITES/ITEM/ID');
            exec dbo.[USP_CONSTITUENT_GETSITES_ADDFROMXML] @CONTACTID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
          end
        -- end sites    

      end;

    if @ISPRIMARYCONTACT = 1
      update dbo.[RELATIONSHIP] set 
        [ISPRIMARYCONTACT] = 0,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [RELATIONSHIPCONSTITUENTID] = @VENDORID and [ISCONTACT] = 1;

    -- create the contact relationship


    declare @RELATIONSHIPSETID uniqueidentifier = newid();

    insert into dbo.[RELATIONSHIPSET]
    (
      [ID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @RELATIONSHIPSETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    insert into dbo.[RELATIONSHIP]
    (
      [ID],
      [RELATIONSHIPCONSTITUENTID],
      [RECIPROCALCONSTITUENTID],
      [RELATIONSHIPTYPECODEID],
      [RECIPROCALTYPECODEID],
      [ISCONTACT],
      [ISPRIMARYCONTACT],
      [CONTACTTYPECODEID],
      [RELATIONSHIPSETID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @VENDORID,
      @CONTACTID,
      @CONTACT_RELATIONSHIPTYPECODEID,
      @CONTACT_RECIPROCALTYPECODEID,
      1,
      @ISPRIMARYCONTACT,
      @CONTACTTYPECODEID,
      @RELATIONSHIPSETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    exec dbo.[USP_RELATIONSHIPCONFIGURATION_CONFIGURE] @VENDORID, @CONTACTID, @CONTACT_RELATIONSHIPTYPECODEID, @CONTACT_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE;

    if (len(@CONTACT_POSITION) > 0)
      insert into dbo.[RELATIONSHIPJOBINFO]
      (
        [RELATIONSHIPSETID],
        [JOBTITLE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) values (
        @RELATIONSHIPSETID,
        @CONTACT_POSITION,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;