USP_WEALTHPOINT_GETCONSTITUENTCLIENTDATAFORSEARCH

CREATE procedure dbo.USP_WEALTHPOINT_GETCONSTITUENTCLIENTDATAFORSEARCH
(
  @ID uniqueidentifier
)
as
begin
  set nocount on;

  select
    C.ID as PROSPECTID,
    'A' as SPOUSEFLAG,
    null as SSNUMBER,
    nullif(T.DESCRIPTION,'') as CS_PREFIX,
    nullif(C.FIRSTNAME,'') as CS_FNAME,
    nullif(C.MIDDLENAME,'') as CS_MNAME,
    nullif(C.MAIDENNAME,'') as CS_MAIDNAM,
    nullif(C.KEYNAME,'') as CS_LNAME,
    nullif(S.DESCRIPTION,'') as CS_NAMESUF,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,A.ADDRESSBLOCK),'') as CS_ADDRLI1,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,A.ADDRESSBLOCK),'') as CS_ADDRLI2,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,A.ADDRESSBLOCK),'') as CS_ADDRLI3,
    nullif(A.CITY,'') as CS_CITY,
    nullif(dbo.UFN_STATE_GETABBREVIATION(A.STATEID),'') as CS_STATE,
    nullif(A.POSTCODE,'') as CS_ZIPCODE,
    nullif(P.NUMBER,'') as CS_HOMEPHO,
    nullif(CBUSINESS.NAME,'') as COMPANY,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU1,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU2,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU3,
    nullif(ABUSINESS.CITY,'') as CS_CITYBUS,
    nullif(dbo.UFN_STATE_GETABBREVIATION(ABUSINESS.STATEID),'') as CS_STATEBU,
    nullif(ABUSINESS.POSTCODE,'') as CS_ZIPCODB,
    nullif(PBUSINESS.NUMBER,'') as CS_PHONEBU,
    nullif((substring(C.BIRTHDATE,5,2) + substring(C.BIRTHDATE,7,2) + substring(C.BIRTHDATE,1,4)),'') as DOB,
    null as COMBO_DOB,
    null as PINKEY,
    null as EO_ID,
    null as FEC_ID,
    null as VICKERS_ID,
    null as WW_ID,
    null as DNB_ID,
    null as THOMSON_ID,
    null as EO_DT,
    null as FEC_DT,
    null as VICKERS_DT,
    null as WW_DT,
    null as DNB_DT,
    null as THOMSON_DT,
    null as CPAYSTAT,
    null as CS_EMAILHO,
    null as CS_EMAIL,
    null as CS_COLLEG1,
    null as CS_COLLEG2,
    null as CS_COLLEG3,
    null as CS_COLLEG4,
    null as CS_COLLEG5,
    nullif(C.NICKNAME,'') as CS_NICKNAME
  from  dbo.WPSEARCHHISTORY WP
  left outer join dbo.CONSTITUENT C on WP.CONSTITUENTID = C.ID
  left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
  left outer join dbo.TITLECODE T on T.ID = C.TITLECODEID
  left outer join dbo.SUFFIXCODE S on S.ID = C.SUFFIXCODEID
  outer apply [dbo].[UFN_WEALTHPOINT_CONSTITUENTSEARCHADDRESSES](C.ID) SEARCHADDRESS
  left outer join dbo.ADDRESS A on A.ID = SEARCHADDRESS.ID
  left outer join dbo.PHONE P on P.CONSTITUENTID=C.ID and P.ISPRIMARY=1
  left outer join dbo.RELATIONSHIP RBUSINESS on C.ID = RBUSINESS.RELATIONSHIPCONSTITUENTID and RBUSINESS.ISPRIMARYBUSINESS=1
  left outer join dbo.ADDRESS ABUSINESS on ABUSINESS.CONSTITUENTID=RBUSINESS.RECIPROCALCONSTITUENTID and ABUSINESS.ISPRIMARY=1
  left outer join dbo.PHONE PBUSINESS on PBUSINESS.CONSTITUENTID=RBUSINESS.RECIPROCALCONSTITUENTID and PBUSINESS.ISPRIMARY=1
  left outer join dbo.CONSTITUENT CBUSINESS on CBUSINESS.ID=RBUSINESS.RECIPROCALCONSTITUENTID
  where
    WP.ID = @ID and
    C.ISORGANIZATION = 0 and
    DWU.ID is null

  union all

  select
    C.ID as PROSPECTID,
    'B' as SPOUSEFLAG,
    null as SSNUMBER,
    nullif(T.DESCRIPTION,'') as CS_PREFIX,
    nullif(SPOUSE.FIRSTNAME,'') as CS_FNAME,
    nullif(SPOUSE.MIDDLENAME,'') as CS_MNAME,
    nullif(SPOUSE.MAIDENNAME,'') as CS_MAIDNAM,
    nullif(SPOUSE.KEYNAME,'') as CS_LNAME,
    nullif(S.DESCRIPTION,'') as CS_NAMESUF,
    case when A.ID is null then nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,APRIMARY.ADDRESSBLOCK),'') else nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,A.ADDRESSBLOCK),'') end as CS_ADDRLI1,
    case when A.ID is null then nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,APRIMARY.ADDRESSBLOCK),'') else nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,A.ADDRESSBLOCK),'') end as CS_ADDRLI2,
    case when A.ID is null then nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,APRIMARY.ADDRESSBLOCK),'') else nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,A.ADDRESSBLOCK),'') end as CS_ADDRLI3,
    case when A.ID is null then nullif(APRIMARY.CITY,'') else nullif(A.CITY,'') end as CS_CITY,
    case when A.ID is null then nullif(dbo.UFN_STATE_GETABBREVIATION(APRIMARY.STATEID),'') else nullif(dbo.UFN_STATE_GETABBREVIATION(A.STATEID),'') end as CS_STATE,
    case when A.ID is null then nullif(APRIMARY.POSTCODE,'') else nullif(A.POSTCODE,'') end as CS_ZIPCODE,
    nullif(P.NUMBER,'') as CS_HOMEPHO,
    nullif(CBUSINESS.NAME,'') as COMPANY,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU1,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU2,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,ABUSINESS.ADDRESSBLOCK),'') as CS_ADDRBU3,
    nullif(ABUSINESS.CITY,'') as CS_CITYBUS,
    nullif(dbo.UFN_STATE_GETABBREVIATION(ABUSINESS.STATEID),'') as CS_STATEBU,
    nullif(ABUSINESS.POSTCODE,'') as CS_ZIPCODB,
    nullif(PBUSINESS.NUMBER,'') as CS_PHONEBU,
    nullif((substring(SPOUSE.BIRTHDATE,5,2) + substring(SPOUSE.BIRTHDATE,7,2) + substring(SPOUSE.BIRTHDATE,1,4)),'') as DOB,
    null as COMBO_DOB,
    null as PINKEY,
    null as EO_ID,
    null as FEC_ID,
    null as VICKERS_ID,
    null as WW_ID,
    null as DNB_ID,
    null as THOMSON_ID,
    null as EO_DT,
    null as FEC_DT,
    null as VICKERS_DT,
    null as WW_DT,
    null as DNB_DT,
    null as THOMSON_DT,
    null as CPAYSTAT,
    null as CS_EMAILHO,
    null as CS_EMAIL,
    null as CS_COLLEG1,
    null as CS_COLLEG2,
    null as CS_COLLEG3,
    null as CS_COLLEG4,
    null as CS_COLLEG5,
    nullif(SPOUSE.NICKNAME,'') as CS_NICKNAME
  from dbo.WPSEARCHHISTORY WP
  left outer join dbo.CONSTITUENT C on WP.CONSTITUENTID = C.ID
  left outer join dbo.RELATIONSHIP RSPOUSE on C.ID = RSPOUSE.RELATIONSHIPCONSTITUENTID and RSPOUSE.ISSPOUSE=1
  left outer join dbo.CONSTITUENT SPOUSE on SPOUSE.ID = RSPOUSE.RECIPROCALCONSTITUENTID and SPOUSE.FIRSTNAME <> ''
  left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
  left outer join dbo.TITLECODE T on T.ID = SPOUSE.TITLECODEID
  left outer join dbo.SUFFIXCODE S on S.ID = SPOUSE.SUFFIXCODEID
  left outer join dbo.ADDRESS A on A.CONSTITUENTID=SPOUSE.ID and A.ISPRIMARY=1
  left outer join dbo.PHONE P on P.CONSTITUENTID=SPOUSE.ID and P.ISPRIMARY=1
  left outer join dbo.RELATIONSHIP RBUSINESS on SPOUSE.ID = RBUSINESS.RELATIONSHIPCONSTITUENTID and RBUSINESS.ISPRIMARYBUSINESS=1
  left outer join dbo.ADDRESS ABUSINESS on ABUSINESS.CONSTITUENTID=RBUSINESS.RECIPROCALCONSTITUENTID and ABUSINESS.ISPRIMARY=1
  left outer join dbo.PHONE PBUSINESS on PBUSINESS.CONSTITUENTID=RBUSINESS.RECIPROCALCONSTITUENTID and PBUSINESS.ISPRIMARY=1
  left outer join dbo.CONSTITUENT CBUSINESS on CBUSINESS.ID=RBUSINESS.RECIPROCALCONSTITUENTID
  left outer join dbo.ADDRESS APRIMARY on APRIMARY.CONSTITUENTID=C.ID and APRIMARY.ISPRIMARY = 1
  where
    WP.ID = @ID and
    C.ISORGANIZATION = 0 and
    SPOUSE.ID is not null and
    DWU.ID is null;

  return 0;
end;