USP_TACONSTITUENT_ADD

CREATE procedure dbo.USP_TACONSTITUENT_ADD
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @LASTNAME nvarchar(100),
  @FIRSTNAME nvarchar(50) = '',
  @MIDDLENAME nvarchar(50) = '',
  @MAIDENNAME nvarchar(100) = '',
  @NICKNAME nvarchar(50) = '',
  --10

  @TITLECODEID uniqueidentifier = null,
  @SUFFIXCODEID uniqueidentifier = null,
  @GENDERCODE tinyint = 0,
  @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
  @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
  @ADDRESS_DONOTMAIL bit = 0,
  @ADDRESS_COUNTRYID uniqueidentifier = null,
  @ADDRESS_STATEID uniqueidentifier = null,
  @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @ADDRESS_CITY nvarchar(50) = '',
  --20

  @ADDRESS_POSTCODE nvarchar(12) = '',
  @PHONE_PHONETYPECODEID uniqueidentifier = null,
  @PHONE_NUMBER nvarchar(100) = '',
  @EMAILADDRESS_EMAILADDRESSTYPECODEID    uniqueidentifier = null,
  @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @MARITALSTATUSCODEID uniqueidentifier = null,
  @SKIP_ADDING_SECURITYGROUPS bit = 0,
  @SPOUSEID uniqueidentifier = null,
  @EXISTINGSPOUSE bit = 0,
  @SPOUSE_LASTNAME nvarchar(100) = '',
  --30

  @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,
  --40

  @COPYPRIMARYINFORMATION bit = 1,
  @SPOUSE_STARTDATE datetime = 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,
  --50

  @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,
  --60

  @ORGANIZATION_STARTDATE datetime = null,
  @ORGANIZATION_ENDDATE datetime = null,
  @ISCONTACT bit = 0,
  @ISPRIMARYCONTACT bit = 0,
  @CONTACTTYPECODEID uniqueidentifier = null,
  @POSITION nvarchar(100) = '',
  @ISMATCHINGGIFTRELATIONSHIP bit = 0,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @ADDRESS_OMITFROMVALIDATION bit = 0,
  --70

  @ADDRESS_CART nvarchar(10) = '',
  @ADDRESS_DPC nvarchar(8) = '',
  @ADDRESS_LOT nvarchar(5) = '',
  @ADDRESS_COUNTYCODEID uniqueidentifier = null,
  @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
  @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
  @ADDRESS_VALIDATIONMESSAGE nvarchar(200) = '',
  @ADDRESS_CERTIFICATIONDATA integer = 0,
  @ORGANIZATION_OMITFROMVALIDATION bit = 0,
  @ORGANIZATION_CART nvarchar(10) = '',
  --80

  @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,
  @ISSPOUSERELATIONSHIP bit = 1,
  @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,
  @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
  --90

  @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null,
  @SKIP_ADDING_SITES bit = 0,
  @JOBCATEGORYCODEID uniqueidentifier = null,
  @CAREERLEVELCODEID uniqueidentifier = null,
  @ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
  @ORGANIZATION_INFOSOURCECODEID uniqueidentifier = null,
  @TITLE2CODEID uniqueidentifier = null,
  @SUFFIX2CODEID uniqueidentifier = null,
  @SPOUSE_TITLE2CODEID uniqueidentifier = null,
  @SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
  --100

  @ISCONSTITUENT bit = 1,
  @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,
  @SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @SPOUSE_PHONE_NUMBER nvarchar(100) = '',
  @INFOSOURCECODEID uniqueidentifier = null
)
as
begin -- sproc body 

  set nocount on;

  begin try

    --Workaround to display error msg for spouse that already has a household on save

    --Ideally, will return later to reform this workflow

    if exists(select 1 from GROUPMEMBER
        left join GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
        where
            @SPOUSEID = MEMBERID
            and GROUPDATA.GROUPTYPECODE = 0
          )
    begin  
      declare @ERRMSG nvarchar(400);
      declare @SPOUSENAME nvarchar(100);
      declare @SPOUSEHOUSEHOLDNAME nvarchar(100);

      select @SPOUSENAME = NAME
      from CONSTITUENT
      where @SPOUSEID = CONSTITUENT.ID

      select @SPOUSEHOUSEHOLDNAME = CONSTITUENT.NAME 
      from GROUPMEMBER
        left join GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
        left join CONSTITUENT on CONSTITUENT.ID = GROUPMEMBER.GROUPID
      where
          @SPOUSEID = MEMBERID
          and GROUPDATA.GROUPTYPECODE = 0

      select @ERRMSG = @SPOUSENAME + ' is already a member of the "' + @SPOUSEHOUSEHOLDNAME + '" household. If this is inaccurate, please visit ' + @SPOUSENAME + '''s record to correct this information.'

      raiserror(@ERRMSG, 13, 1);
    end

    exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
      @ID output,
      @CURRENTAPPUSERID,
      '3C41E770-979F-4737-8283-747EED420150',
      @CHANGEAGENTID,
      @LASTNAME,
      @FIRSTNAME,
      @MIDDLENAME,
      @MAIDENNAME,
      @NICKNAME,
      --10

      @TITLECODEID,
      @SUFFIXCODEID,
      @GENDERCODE,
      @BIRTHDATE,
      @ADDRESS_ADDRESSTYPECODEID,
      @ADDRESS_DONOTMAIL,
      @ADDRESS_COUNTRYID,
      @ADDRESS_STATEID,
      @ADDRESS_ADDRESSBLOCK,
      @ADDRESS_CITY,
      --20

      @ADDRESS_POSTCODE,
      @PHONE_PHONETYPECODEID,
      @PHONE_NUMBER,
      @EMAILADDRESS_EMAILADDRESSTYPECODEID,
      @EMAILADDRESS_EMAILADDRESS,
      @MARITALSTATUSCODEID,
      @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

      @COPYPRIMARYINFORMATION,
      @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,
      @ISCONTACT,
      @ISPRIMARYCONTACT,
      @CONTACTTYPECODEID,
      @POSITION,
      @ISMATCHINGGIFTRELATIONSHIP,
      @RECIPROCALRECOGNITIONTYPECODEID,
      @PRIMARYRECOGNITIONTYPECODEID,
      @ADDRESS_OMITFROMVALIDATION,
      --70

      @ADDRESS_CART,
      @ADDRESS_DPC,
      @ADDRESS_LOT,
      @ADDRESS_COUNTYCODEID,
      @ADDRESS_CONGRESSIONALDISTRICTCODEID,
      @ADDRESS_LASTVALIDATIONATTEMPTDATE,
      @ADDRESS_VALIDATIONMESSAGE,
      @ADDRESS_CERTIFICATIONDATA,
      @ORGANIZATION_OMITFROMVALIDATION,
      @ORGANIZATION_CART,
      --80

      @ORGANIZATION_DPC,
      @ORGANIZATION_LOT,
      @ORGANIZATION_COUNTYCODEID,
      @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
      @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
      @ORGANIZATION_VALIDATIONMESSAGE,
      @ORGANIZATION_CERTIFICATIONDATA,
      @ISSPOUSERELATIONSHIP,
      @HOUSEHOLDCOPYPRIMARYCONTACTINFO,
      @ADDRESS_DONOTMAILREASONCODEID,
      --90

      @ORGANIZATION_DONOTMAILREASONCODEID,
      @SKIP_ADDING_SITES,
      @JOBCATEGORYCODEID,
      @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;

      exec dbo.USP_WEALTH_CREATE @ID, @CHANGEAGENTID;

      if @INFOSOURCECODEID is not null
      begin
          insert into CONSTITUENTORIGINATION
              (ID,INFOSOURCECODEID,CHANGEDBYID,ADDEDBYID)
          values
              (@ID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID)

        if (@SPOUSEID is null) and (coalesce(@SPOUSE_LASTNAME, '') <> '')
        begin
          select 
            @SPOUSEID = RECIPROCALCONSTITUENTID 
          from RELATIONSHIP 
          where RELATIONSHIPCONSTITUENTID = @ID and ISSPOUSE = 1

            insert into CONSTITUENTORIGINATION
                (ID,INFOSOURCECODEID,CHANGEDBYID,ADDEDBYID)
            values
                (@SPOUSEID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID)
        end

        if (@ORGANIZATIONID is null) and (coalesce(@ORGANIZATION_NAME,'') <> '')
        begin

          select 
            @ORGANIZATIONID = RECIPROCALCONSTITUENTID 
          from RELATIONSHIP 
          where RELATIONSHIPCONSTITUENTID = @ID and ISPRIMARYBUSINESS = 1

            insert into CONSTITUENTORIGINATION
                (ID,INFOSOURCECODEID,CHANGEDBYID,ADDEDBYID)
            values
                (@ORGANIZATIONID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID)
        end
      end

  end try

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


end