USP_WEALTHPOINT_GETRESEARCHGROUPCLIENTDATAFORSEARCH2

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

with CONSTITUENT_CTE as (
  select
    C.ID as PROSPECTID,
    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(P.NUMBER,'') as CS_HOMEPHO,
    dbo.UFN_WEALTHPOINT_CONSTITUENTSEARCHEMAILADDRESSESXML(C.ID) as EMAILS,
    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,
    nullif(C.NICKNAME,'') as CS_NICKNAME,
    nullif(RE.RE7RECORDID,'') as RE7_ID,
    dbo.UFN_WEALTHPOINT_CONSTITUENTSEARCHADDRESSESXML(C.ID) as ADDRESSES

  from dbo.WPSEARCHHISTORY WP
  left outer join dbo.RESEARCHGROUP RG on WP.RESEARCHGROUPID = RG.ID
  left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
  left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
  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
  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
  left outer join dbo.RE7INTEGRATIONCONSTITUENTMAP RE on RE.ID = C.ID

  where
    WP.ID = @ID and
    C.ISORGANIZATION = 0 and
    DWU.ID is null
 ) ,  
SPOUSE_CTE as (  
select

    nullif(SPOUSE_T.DESCRIPTION,'') as SPOUSE_PREFIX,
    nullif(SPOUSE.FIRSTNAME,'') as SPOUSE_FNAME,
    nullif(SPOUSE.MIDDLENAME,'') as SPOUSE_MNAME,
    nullif(SPOUSE.MAIDENNAME,'') as SPOUSE_MAIDNAM,
    nullif(SPOUSE.KEYNAME,'') as SPOUSE_LNAME,
    nullif(SPOUSE_S.DESCRIPTION,'') as SPOUSE_NAMESUF,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,SPOUSE_A.ADDRESSBLOCK),'') as SPOUSE_ADDRLI1,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,SPOUSE_A.ADDRESSBLOCK),'') as SPOUSE_ADDRLI2,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,SPOUSE_A.ADDRESSBLOCK),'') as SPOUSE_ADDRLI3,
    nullif(SPOUSE_A.CITY,'') as SPOUSE_CITY,
    nullif(dbo.UFN_STATE_GETABBREVIATION(SPOUSE_A.STATEID),'') as SPOUSE_STATE,
    nullif(SPOUSE_A.POSTCODE,'') as SPOUSE_ZIPCODE,
    nullif(SPOUSE_BUSINESS.NAME,'') as SPOUSE_COMPANY,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(1,SPOUSE_ABUSINESS.ADDRESSBLOCK),'') as SPOUSE_ADDRBU1,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(2,SPOUSE_ABUSINESS.ADDRESSBLOCK),'') as SPOUSE_ADDRBU2,
    nullif(dbo.UFN_ADDRESS_GETADDRESSLINE(3,SPOUSE_ABUSINESS.ADDRESSBLOCK),'') as SPOUSE_ADDRBU3,
    nullif(SPOUSE_ABUSINESS.CITY,'') as SPOUSE_CITYBUS,
    nullif(dbo.UFN_STATE_GETABBREVIATION(SPOUSE_ABUSINESS.STATEID),'') as SPOUSE_STATEBU,
    nullif(SPOUSE_ABUSINESS.POSTCODE,'') as SPOUSE_ZIPCODB,
    nullif(SPOUSE_PBUSINESS.NUMBER,'') as SPOUSE_PHONEBU,
    nullif((substring(SPOUSE.BIRTHDATE,5,2) + substring(SPOUSE.BIRTHDATE,7,2) + substring(SPOUSE.BIRTHDATE,1,4)),'') as SPOUSE_DOB,
    nullif(SPOUSE.NICKNAME,'') as SPOUSE_NICKNAME,
    nullif(SPOUSE_P.NUMBER,'') as SPOUSE_HOMEPHO,
    C.ID as PROSPECTID

  from dbo.WPSEARCHHISTORY WP
  left outer join dbo.RESEARCHGROUP RG on WP.RESEARCHGROUPID = RG.ID
  left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
  left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
  left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = 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.TITLECODE SPOUSE_T on SPOUSE_T.ID = SPOUSE.TITLECODEID
  left outer join dbo.SUFFIXCODE SPOUSE_S on SPOUSE_S.ID = SPOUSE.SUFFIXCODEID
  left outer join dbo.PHONE SPOUSE_P on SPOUSE_P.CONSTITUENTID=SPOUSE.ID and SPOUSE_P.ISPRIMARY=1
  left outer join dbo.ADDRESS SPOUSE_A on SPOUSE_A.CONSTITUENTID=SPOUSE.ID and SPOUSE_A.ISPRIMARY=1
  left outer join dbo.RELATIONSHIP SPOUSE_RBUSINESS on SPOUSE.ID = SPOUSE_RBUSINESS.RELATIONSHIPCONSTITUENTID and SPOUSE_RBUSINESS.ISPRIMARYBUSINESS=1
  left outer join dbo.ADDRESS SPOUSE_ABUSINESS on SPOUSE_ABUSINESS.CONSTITUENTID=SPOUSE_RBUSINESS.RECIPROCALCONSTITUENTID and SPOUSE_ABUSINESS.ISPRIMARY=1
  left outer join dbo.PHONE SPOUSE_PBUSINESS on SPOUSE_PBUSINESS.CONSTITUENTID=SPOUSE_RBUSINESS.RECIPROCALCONSTITUENTID and SPOUSE_PBUSINESS.ISPRIMARY=1
  left outer join dbo.CONSTITUENT SPOUSE_BUSINESS on SPOUSE_BUSINESS.ID=SPOUSE_RBUSINESS.RECIPROCALCONSTITUENTID
  where
    WP.ID = @ID and
    C.ISORGANIZATION = 0 and
    DWU.ID is null and
    SPOUSE.ID is not null
)

  select

    CONSTITUENT_CTE.PROSPECTID,
    CONSTITUENT_CTE.CS_PREFIX,
    CONSTITUENT_CTE.CS_FNAME,
    CONSTITUENT_CTE.CS_MNAME,
    CONSTITUENT_CTE.CS_MAIDNAM,
    CONSTITUENT_CTE.CS_LNAME,
    CONSTITUENT_CTE.CS_NAMESUF,
    CONSTITUENT_CTE.CS_HOMEPHO,
    CONSTITUENT_CTE.EMAILS,
    CONSTITUENT_CTE.COMPANY,
    CONSTITUENT_CTE.CS_ADDRBU1,
    CONSTITUENT_CTE.CS_ADDRBU2,
    CONSTITUENT_CTE.CS_ADDRBU3,
    CONSTITUENT_CTE.CS_CITYBUS,
    CONSTITUENT_CTE.CS_STATEBU,
    CONSTITUENT_CTE.CS_ZIPCODB,
    CONSTITUENT_CTE.CS_PHONEBU,
    CONSTITUENT_CTE.DOB,
    CONSTITUENT_CTE.CS_NICKNAME,
    CONSTITUENT_CTE.RE7_ID,
    CONSTITUENT_CTE.ADDRESSES,

    SPOUSE_CTE.SPOUSE_PREFIX,
    SPOUSE_CTE.SPOUSE_FNAME,
    SPOUSE_CTE.SPOUSE_MNAME,
    SPOUSE_CTE.SPOUSE_MAIDNAM,
    SPOUSE_CTE.SPOUSE_LNAME,
    SPOUSE_CTE.SPOUSE_NAMESUF,
    SPOUSE_CTE.SPOUSE_ADDRLI1,
    SPOUSE_CTE.SPOUSE_ADDRLI2,
    SPOUSE_CTE.SPOUSE_ADDRLI3,
    SPOUSE_CTE.SPOUSE_CITY,
    SPOUSE_CTE.SPOUSE_STATE,
    SPOUSE_CTE.SPOUSE_ZIPCODE,
    SPOUSE_CTE.SPOUSE_COMPANY,
    SPOUSE_CTE.SPOUSE_ADDRBU1,
    SPOUSE_CTE.SPOUSE_ADDRBU2,
    SPOUSE_CTE.SPOUSE_ADDRBU3,
    SPOUSE_CTE.SPOUSE_CITYBUS,
    SPOUSE_CTE.SPOUSE_STATEBU,
    SPOUSE_CTE.SPOUSE_ZIPCODB,
    SPOUSE_CTE.SPOUSE_PHONEBU,
    SPOUSE_CTE.SPOUSE_DOB,
    SPOUSE_CTE.SPOUSE_NICKNAME,
    SPOUSE_CTE.SPOUSE_HOMEPHO
  from CONSTITUENT_CTE
  left outer join SPOUSE_CTE on CONSTITUENT_CTE.PROSPECTID = SPOUSE_CTE.PROSPECTID

  return 0;
end;