USP_DATAFORMTEMPLATE_ADD_MKTVENDOR

The save procedure used by the add dataform template "Marketing Vendor 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.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@WEBADDRESS	UDT_WEBADDRESS	IN	Web site address
@PICTURE	varbinary	IN	Logo
@PICTURETHUMBNAIL	varbinary	IN	Logo thumbnail
@ADDRESS_TYPECODEID	uniqueidentifier	IN	Address type
@ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ADDRESS_CITY	nvarchar(50)	IN	City
@ADDRESS_STATEID	uniqueidentifier	IN	State
@ADDRESS_POSTCODE	nvarchar(12)	IN	Post code
@ADDRESS_CART	nvarchar(10)	IN	CART
@ADDRESS_DPC	nvarchar(8)	IN	DPC
@ADDRESS_LOT	nvarchar(5)	IN	LOT
@PHONE_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@PHONE_NUMBER	nvarchar(100)	IN	Phone number
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@ADDRESS_DONOTMAIL	bit	IN	Do not send mail to this address
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@CONTACTID	uniqueidentifier	IN	Contact ID
@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
@VENDORSERVICETYPES	xml	IN	Service types provided
@VENDORSERVICES	xml	IN	Services provided
@SERVICETYPECODE	tinyint	IN	Input parameter indicating the context ID for the record being added.
@CONTACT_TITLE2CODEID	uniqueidentifier	IN	Title 2
@CONTACT_SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2
@SITES	xml	IN	Sites

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTVENDOR]
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,    
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @WEBADDRESS dbo.UDT_WEBADDRESS = '',
  @PICTURE varbinary(max) = null,
  @PICTURETHUMBNAIL varbinary(max) = null,
  @ADDRESS_TYPECODEID uniqueidentifier = null,
  @ADDRESS_COUNTRYID uniqueidentifier = null,
  @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @ADDRESS_CITY nvarchar(50) = '',
  @ADDRESS_STATEID uniqueidentifier = null,
  @ADDRESS_POSTCODE nvarchar(12) = '',
  @ADDRESS_CART nvarchar(10) = '',
  @ADDRESS_DPC nvarchar(8) = '',
  @ADDRESS_LOT nvarchar(5) = '',
  @PHONE_PHONETYPECODEID uniqueidentifier = null,
  @PHONE_NUMBER nvarchar(100) = '',
  @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
  @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @ADDRESS_DONOTMAIL bit = 0,
  @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
  @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) = '',
  @VENDORSERVICETYPES xml = null,
  @VENDORSERVICES xml = null,
  @SERVICETYPECODE tinyint,
  @CONTACT_TITLE2CODEID uniqueidentifier = null,
  @CONTACT_SUFFIX2CODEID uniqueidentifier = null,
  @SITES xml = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @KEYNAME nvarchar(100);
  declare @KEYNAMEPREFIX nvarchar(50);

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

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

    set @CURRENTDATE = getdate();

    ---------------------------------------------------------------------

    -- vendor

    ---------------------------------------------------------------------


    exec dbo.[USP_PARSE_ORGANIZATION_NAME] @NAME, @KEYNAME output, @KEYNAMEPREFIX output;

    -- insert the base constituent information

    insert into dbo.[CONSTITUENT] (
      [ID], 
      [ISORGANIZATION], 
      [KEYNAME], 
      [KEYNAMEPREFIX], 
      [WEBADDRESS], 
      [PICTURE], 
      [PICTURETHUMBNAIL], 
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      -1,
      @KEYNAME,
      @KEYNAMEPREFIX,
      @WEBADDRESS,
      @PICTURE,
      @PICTURETHUMBNAIL,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    -- insert the vendor information

    insert into dbo.[VENDOR] (
      [ID],
      [DESCRIPTION],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [ALLOWPAYMENTS],
      [ALLOWTOCREATEINVOICES]
    ) values (
      @ID,
      @DESCRIPTION,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      0, -- By default Marketing vendors cannot be paid or their invoices created in AP

      0  
    );

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

    if @ADDRESS_COUNTRYID is not null or
       @ADDRESS_ADDRESSBLOCK <> '' or
       @ADDRESS_CITY <> '' or
       @ADDRESS_STATEID is not null or
       @ADDRESS_POSTCODE <> '' or
       @ADDRESS_DONOTMAIL <> 0 or
       @ADDRESS_DONOTMAILREASONCODEID is not null or
       @ADDRESS_CART <> '' or
       @ADDRESS_DPC <> '' or
       @ADDRESS_LOT <> ''
      insert into dbo.[ADDRESS] (
        [CONSTITUENTID], 
        [ISPRIMARY],
        [ADDRESSTYPECODEID],
        [COUNTRYID],
        [ADDRESSBLOCK],
        [CITY],
        [STATEID],
        [POSTCODE],
        [CART],
        [DPC],
        [LOT],
        [DONOTMAIL],
        [DONOTMAILREASONCODEID],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) values (
        @ID,
        1,
        @ADDRESS_TYPECODEID,
        @ADDRESS_COUNTRYID,
        @ADDRESS_ADDRESSBLOCK,
        @ADDRESS_CITY,
        @ADDRESS_STATEID,
        @ADDRESS_POSTCODE,
        @ADDRESS_CART,
        @ADDRESS_DPC,
        @ADDRESS_LOT,
        @ADDRESS_DONOTMAIL,
        @ADDRESS_DONOTMAILREASONCODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

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

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

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

    if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (@EMAILADDRESS_EMAILADDRESS <> '')
      begin
        insert into dbo.[EMAILADDRESS]
        (
          [CONSTITUENTID],
          [EMAILADDRESSTYPECODEID],
          [EMAILADDRESS],
          [ISPRIMARY],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        )
        values
        (
          @ID,
          @EMAILADDRESS_EMAILADDRESSTYPECODEID,
          @EMAILADDRESS_EMAILADDRESS,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end

    -- start security groups

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


    -- start sites

    if @SITES is null and dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '05dc9052-4d2d-499b-a037-9726445c7842', 1) = 1
      begin
        raiserror('ERR_MKTVENDOR_SITEISREQUIRED', 13, 1);
        return 1;
      end

    exec dbo.[USP_CONSTITUENTSITE_VALIDATESITES] @SITES;                        

    exec dbo.[USP_CONSTITUENT_GETSITES_ADDFROMXML] @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
    -- end sites    


    ---------------------------------------------------------------------

    -- contact

    ---------------------------------------------------------------------


    if @CONTACTID is null
      if @CONTACT_LASTNAME <> ''
        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_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

          -- sites were validated above

          exec dbo.[USP_CONSTITUENT_GETSITES_ADDFROMXML] @CONTACTID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
          -- end sites    

        end;

    if @CONTACTID is not null
      begin
        -- create the primary 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
        (
          newid(),
          @ID,
          @CONTACTID,
          @CONTACT_RELATIONSHIPTYPECODEID,
          @CONTACT_RECIPROCALTYPECODEID,
          1,
          1,
          @CONTACTTYPECODEID,
          @RELATIONSHIPSETID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

        exec dbo.[USP_RELATIONSHIPCONFIGURATION_CONFIGURE] @ID, @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

    ---------------------------------------------------------------------

    -- services

    ---------------------------------------------------------------------


    exec dbo.[USP_MKTVENDOR_GETVENDORSERVICETYPES_ADDFROMXML] @ID, @VENDORSERVICETYPES, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.[USP_MKTVENDOR_GETVENDORSERVICES_ADDFROMXML] @ID, @VENDORSERVICES, @CHANGEAGENTID, @CURRENTDATE;

  end try

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

  return 0;