USP_VENDOR_ADD_ADDRESS

Wraps inserting initial Addresses to Constituents as used by Vendors

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@ADDRESSID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@COUNTRYID	uniqueidentifier	IN
@STATEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@POSTCODE	nvarchar(12)	IN
@DONOTMAIL	bit	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN
@OMITFROMVALIDATION	bit	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(200)	IN
@CERTIFICATIONDATA	int	IN

Definition

Copy



CREATE procedure dbo.USP_VENDOR_ADD_ADDRESS
(
  @ID uniqueidentifier
  ,@ADDRESSID uniqueidentifier = null output
  ,@CHANGEAGENTID uniqueidentifier
  ,@CURRENTDATE datetime

  ,@ADDRESSTYPECODEID uniqueidentifier = null
  ,@COUNTRYID uniqueidentifier = null
  ,@STATEID uniqueidentifier = null
  ,@ADDRESSBLOCK nvarchar(150) = ''
  ,@CITY nvarchar(50) = ''
  ,@POSTCODE nvarchar(12) = ''
  ,@DONOTMAIL bit = 0
  ,@DONOTMAILREASONCODEID uniqueidentifier = null

  ,@OMITFROMVALIDATION bit = 0
  ,@CART nvarchar(10) = ''
  ,@DPC nvarchar(8) = ''
  ,@LOT nvarchar(5) = ''
  ,@COUNTYCODEID uniqueidentifier = null
  ,@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null
  ,@LASTVALIDATIONATTEMPTDATE datetime = null
  ,@VALIDATIONMESSAGE nvarchar(200) = ''
  ,@CERTIFICATIONDATA integer = 0
)
as
begin

  -- Address Functionality

  if
  (
    (@STATEID is not null)
    or (coalesce(@ADDRESSBLOCK,'') <> '') 
    or (coalesce(@CITY,'') <> '') 
    or (coalesce(@POSTCODE,'') <> '') 
    or (@ADDRESSTYPECODEID is not null) 
    or (@COUNTRYID is not null)
    or (@DONOTMAIL = 1)
    or (@DONOTMAILREASONCODEID is not null)
  )
  begin
    set @ADDRESSID = newID();

    insert into dbo.ADDRESS
    (
      ID
      ,CONSTITUENTID
      ,ADDRESSTYPECODEID
      ,ISPRIMARY
      ,DONOTMAIL
      ,DONOTMAILREASONCODEID
      ,COUNTRYID
      ,STATEID
      ,ADDRESSBLOCK
      ,CITY
      ,POSTCODE
      ,CART
      ,DPC
      ,LOT
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
    )
    values
    (
      @ADDRESSID
      ,@ID
      ,@ADDRESSTYPECODEID
      ,1
      ,@DONOTMAIL
      ,@DONOTMAILREASONCODEID
      ,@COUNTRYID
      ,@STATEID
      ,@ADDRESSBLOCK
      ,@CITY
      ,@POSTCODE
      ,@CART
      ,@DPC
      ,@LOT
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
    );

    insert into dbo.ADDRESSVALIDATIONUPDATE
    (
      ID
      ,COUNTYCODEID
      ,CONGRESSIONALDISTRICTCODEID
      ,LASTVALIDATIONATTEMPTDATE
      ,VALIDATIONMESSAGE
      ,CERTIFICATIONDATA
      ,OMITFROMVALIDATION
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
    )
    values
    (
      @ADDRESSID
      ,@COUNTYCODEID
      ,@CONGRESSIONALDISTRICTCODEID
      ,@CURRENTDATE
      ,@VALIDATIONMESSAGE
      ,@CERTIFICATIONDATA
      ,@OMITFROMVALIDATION
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
    );
  end
  -- End Address

end