USP_RECORDOPERATION_BIOGRAPHICALDEMOGRAPHICUPDATEDEFAULTS

CREATE procedure dbo.USP_RECORDOPERATION_BIOGRAPHICALDEMOGRAPHICUPDATEDEFAULTS
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as begin

  declare @CONSTITUENTID uniqueidentifier;

  declare @KEYNAME nvarchar(200);
  declare @FIRSTNAME nvarchar(200);
  declare @MIDDLENAME nvarchar(200); 
  declare @TITLECODEID uniqueidentifier;
  declare @SUFFIXCODEID uniqueidentifier;
  declare @BIRTHDATE dbo.UDT_FUZZYDATE;
  declare @MARITALSTATUSCODEID uniqueidentifier;
  declare @DISCRETIONARYSPENDING money;
  declare @HHINCOMECODEID uniqueidentifier;
  declare @MOSAIC nvarchar(200);
  declare @ISSPOUEMATCHCODE bit;

  select
      @CONSTITUENTID = C.ID,
      @KEYNAME = case when C.KEYNAME <> '' then C.KEYNAME else D.LASTNAME end,
      @FIRSTNAME = case when C.FIRSTNAME <> '' then C.FIRSTNAME else left(D.FIRSTNAME, 50) end,
      @MIDDLENAME = case when C.MIDDLENAME <> '' then C.MIDDLENAME else left(D.MIDDLENAME, 50) end,
      @TITLECODEID = case when (C.TITLECODEID is not null or D.TITLECODEID = C.TITLE2CODEID) then C.TITLECODEID else D.TITLECODEID end,
      @SUFFIXCODEID = case when (C.SUFFIXCODEID is not null or D.SUFFIXCODEID = C.SUFFIX2CODEID) then C.SUFFIXCODEID else D.SUFFIXCODEID end,
      @MARITALSTATUSCODEID = case when C.MARITALSTATUSCODEID is not null then C.MARITALSTATUSCODEID else D.MARITALSTATUSCODEID end,
      @BIRTHDATE = case when C.BIRTHDATE <> '00000000' then C.BIRTHDATE else D.BIRTHDATE end,
      @DISCRETIONARYSPENDING = D.DISCRETIONARY_SPEND,
      @HHINCOMECODEID = D.HOUSEHOLD_INCOMECODEID,
      @MOSAIC = D.HOUSEHOLD_MOSAIC,
      @ISSPOUEMATCHCODE = case when D.MC like 'S%' then 1 else 0 end
  from 
      WPBIOGRAPHICALDEMOGRAPHIC D
  left join
      CONSTITUENT C on C.ID = D.WEALTHID
  where
    @ID = D.ID

  if @ISSPOUEMATCHCODE = 0
  begin
    update 
        CONSTITUENT
    set
        KEYNAME = @KEYNAME,
        FIRSTNAME = @FIRSTNAME,
        MIDDLENAME = @MIDDLENAME,
        TITLECODEID = @TITLECODEID,
        SUFFIXCODEID = @SUFFIXCODEID,
        MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
        BIRTHDATE = @BIRTHDATE
    where
        ID = @CONSTITUENTID
  end

    return 0;

end