USP_DATAFORMTEMPLATE_ADD_PROSPECTSEARCH_ADDRE_INDIVIDUAL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROSPECTSEARCH_ADDRE_INDIVIDUAL
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @RE7RECORDID int,
  @LASTNAME nvarchar(100),
  @FIRSTNAME nvarchar(50) = '',
  @MIDDLENAME nvarchar(50) = '',
  @MAIDENNAME nvarchar(100) = '',
  @NICKNAME nvarchar(50) = '',
  @TITLECODEID uniqueidentifier = null,
  @SUFFIXCODEID uniqueidentifier = null,
  @GENDERCODE tinyint = 0,
  @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
  @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
  @ADDRESS_COUNTRYID uniqueidentifier = null,
  @ADDRESS_STATEID uniqueidentifier = null,
  @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @ADDRESS_CITY nvarchar(50) = '',
  @ADDRESS_POSTCODE nvarchar(12) = '',
  @PHONE_PHONETYPECODEID uniqueidentifier = null,
  @PHONE_NUMBER nvarchar(100) = '',
  @SPOUSEID    uniqueidentifier = null,
  @EXISTINGSPOUSE bit = 0,
  @SPOUSE_LASTNAME nvarchar(100) = '',
  @SPOUSE_FIRSTNAME nvarchar(50) = '',
  @SPOUSE_MIDDLENAME nvarchar(50) = '',
  @SPOUSE_MAIDENNAME nvarchar(100) = '',
  @SPOUSE_NICKNAME nvarchar(50) = '',
  @SPOUSE_TITLECODEID uniqueidentifier = null,
  @SPOUSE_SUFFIXCODEID uniqueidentifier = null,
  @SPOUSE_GENDERCODE tinyint = 0,
  @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
  @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
  @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATIONID uniqueidentifier = null,
  @EXISTINGORGANIZATION bit = 0,
  @ORGANIZATION_NAME nvarchar(100) = '',
  @ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_COUNTRYID uniqueidentifier = null,
  @ORGANIZATION_STATEID uniqueidentifier = null,
  @ORGANIZATION_ADDRESSBLOCK nvarchar(150) = '',
  @ORGANIZATION_CITY nvarchar(50) = '',
  @ORGANIZATION_POSTCODE nvarchar(12) = '',
  @ORGANIZATION_DONOTMAIL bit = 0,
  @ORGANIZATION_PHONETYPECODEID uniqueidentifier = null,
  @ORGANIZATION_NUMBER nvarchar(100) = '',
  @ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_STARTDATE datetime = null,
  @ORGANIZATION_ENDDATE datetime = null,
  @POSITION nvarchar(50) = '',
  @ISMATCHINGGIFTRELATIONSHIP bit = 0,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_OMITFROMVALIDATION bit = 0,
  @ORGANIZATION_CART nvarchar(10) = '',
  @ORGANIZATION_DPC nvarchar(8) = '',
  @ORGANIZATION_LOT nvarchar(5) = '',
  @ORGANIZATION_COUNTYCODEID uniqueidentifier = null,
  @ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
  @ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null,
  @ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) = '',
  @ORGANIZATION_CERTIFICATIONDATA integer = 0,
  @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null,
  @ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
  @ORGANIZATION_INFOSOURCECODEID uniqueidentifier = null,
  @TITLE2CODEID uniqueidentifier = null,
  @SUFFIX2CODEID uniqueidentifier = null,
  @SPOUSE_TITLE2CODEID uniqueidentifier = null,
  @SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
  @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @RE7SPOUSERECORDID int = 0
)
as 
begin
  set nocount on;

  begin try
    exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
      @ID output,
      @CURRENTAPPUSERID,
      '68ff4985-10e8-49e8-b570-c5dd857d13c2',
      @CHANGEAGENTID,
      @LASTNAME,
      @FIRSTNAME,
      @MIDDLENAME,
      @MAIDENNAME,
      @NICKNAME,
      --10

      @TITLECODEID,
      @SUFFIXCODEID,
      @GENDERCODE,
      @BIRTHDATE,
      @ADDRESS_ADDRESSTYPECODEID,
      0, --@ADDRESS_DONOTMAIL,

      @ADDRESS_COUNTRYID,
      @ADDRESS_STATEID,
      @ADDRESS_ADDRESSBLOCK,
      @ADDRESS_CITY,
      --20

      @ADDRESS_POSTCODE,
      @PHONE_PHONETYPECODEID,
      @PHONE_NUMBER,
      null, --@EMAILADDRESS_EMAILADDRESSTYPECODEID,

      null, --@@EMAILADDRESS_EMAILADDRESS,

      null, --@@MARITALSTATUSCODEID,

      0, --@SKIP_ADDING_SECURITYGROUPS,

      @SPOUSEID,
      @EXISTINGSPOUSE,
      @SPOUSE_LASTNAME,
      --30

      @SPOUSE_FIRSTNAME,
      @SPOUSE_MIDDLENAME,
      @SPOUSE_MAIDENNAME,
      @SPOUSE_NICKNAME,
      @SPOUSE_TITLECODEID,
      @SPOUSE_SUFFIXCODEID,
      @SPOUSE_GENDERCODE,
      @SPOUSE_BIRTHDATE,
      @SPOUSE_RECIPROCALTYPECODEID,
      @SPOUSE_RELATIONSHIPTYPECODEID,
      --40

      1, --@COPYPRIMARYINFORMATION,

      null, --@SPOUSE_STARTDATE,

      @PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
      @PRIMARYSOFTCREDITMATCHFACTOR,
      @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
      @RECIPROCALSOFTCREDITMATCHFACTOR,
      @ORGANIZATIONID,
      @EXISTINGORGANIZATION,
      @ORGANIZATION_NAME,
      @ORGANIZATION_ADDRESSTYPECODEID,
      --50

      @ORGANIZATION_COUNTRYID,
      @ORGANIZATION_STATEID,
      @ORGANIZATION_ADDRESSBLOCK,
      @ORGANIZATION_CITY,
      @ORGANIZATION_POSTCODE,
      @ORGANIZATION_DONOTMAIL,
      @ORGANIZATION_PHONETYPECODEID,
      @ORGANIZATION_NUMBER,
      @ORGANIZATION_RECIPROCALTYPECODEID,
      @ORGANIZATION_RELATIONSHIPTYPECODEID,
      --60

      @ORGANIZATION_STARTDATE,
      @ORGANIZATION_ENDDATE,
      1, --@ISCONTACT,

      1, --@ISPRIMARYCONTACT,

      null, --@CONTACTTYPECODEID,

      @POSITION,
      @ISMATCHINGGIFTRELATIONSHIP,
      @RECIPROCALRECOGNITIONTYPECODEID,
      @PRIMARYRECOGNITIONTYPECODEID,
      0, --@ADDRESS_OMITFROMVALIDATION,

      --70

      '', --@ADDRESS_CART,

      '', --@ADDRESS_DPC,

      '', --@ADDRESS_LOT,

      null, --@ADDRESS_COUNTYCODEID,

      null, --@ADDRESS_CONGRESSIONALDISTRICTCODEID,

      null, --@ADDRESS_LASTVALIDATIONATTEMPTDATE,

      '', --@ADDRESS_VALIDATIONMESSAGE,

      '', --@ADDRESS_CERTIFICATIONDATA,

      0, --@ORGANIZATION_OMITFROMVALIDATION,

      '', --@ORGANIZATION_CART,

      --80

      @ORGANIZATION_DPC,
      @ORGANIZATION_LOT,
      @ORGANIZATION_COUNTYCODEID,
      @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
      @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
      @ORGANIZATION_VALIDATIONMESSAGE,
      @ORGANIZATION_CERTIFICATIONDATA,
      1, --@ISSPOUSERELATIONSHIP,

      1, --@HOUSEHOLDCOPYPRIMARYCONTACTINFO,

      null, --@ADDRESS_DONOTMAILREASONCODEID,

      --90

      @ORGANIZATION_DONOTMAILREASONCODEID,
      0, --@SKIP_ADDING_SITES,

      null, --@JOBCATEGORYCODEID,

      null, --@CAREERLEVELCODEID,

      @ADDRESS_INFOSOURCECODEID,
      @ORGANIZATION_INFOSOURCECODEID,
      @TITLE2CODEID,
      @SUFFIX2CODEID,
      @SPOUSE_TITLE2CODEID,
      @SPOUSE_SUFFIX2CODEID,
      --100

      1,
      @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
      @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR,
      @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
      @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR,
      @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID,
      @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID;

      --Link Record

      exec dbo.USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONCONSTITUENTMAP @ID, @CHANGEAGENTID, @ID, @RE7RECORDID;

      --Check for spouse

      if @EXISTINGSPOUSE = 0
      begin
        select @SPOUSEID= RECIPROCALCONSTITUENTID 
        from RELATIONSHIP
        where ISSPOUSE = 1 and RELATIONSHIPCONSTITUENTID = @ID;

        if @SPOUSEID is not null
          exec dbo.USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONCONSTITUENTMAP @SPOUSEID, @CHANGEAGENTID, @SPOUSEID, @RE7SPOUSERECORDID;
      end

  end try

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

return 0;
end;