USP_WEALTHPOINT_GETDATAREFRESHCLIENTDATAFORSEARCH

            CREATE procedure dbo.USP_WEALTHPOINT_GETDATAREFRESHCLIENTDATAFORSEARCH(
                @ID uniqueidentifier,
                @SECURITIES_REFRESHDATE datetime = null,
                @NPA_REFRESHDATE datetime = null,
                @PF_REFRESHDATE datetime = null,
                @BO_REFRESHDATE datetime = null,
                @BIO_REFRESHDATE datetime = null
            ) as begin
                set nocount on;

                declare @SECURITIES_LASTREFRESHDATE datetime
                declare @NPA_LASTREFRESHDATE datetime
                declare @PF_LASTREFRESHDATE datetime
                declare @BO_LASTREFRESHDATE datetime
                declare @BIO_LASTREFRESHDATE datetime

                select
                    @SECURITIES_LASTREFRESHDATE = SECURITIES_DATE,
                    @NPA_LASTREFRESHDATE = NPA_DATE,
                    @PF_LASTREFRESHDATE = PF_DATE,
                    @BO_LASTREFRESHDATE = BO_DATE,
                    @BIO_LASTREFRESHDATE = BIO_DATE
                from 
                    dbo.WEALTHSOURCEREFRESHDATE

                --Set the refresh dates at the time of the current refresh

                update dbo.WPSEARCHHISTORY set
                    SECURITIES_REFRESHDATE = @SECURITIES_REFRESHDATE,
                    NPA_REFRESHDATE = @NPA_REFRESHDATE,
                    PF_REFRESHDATE = @PF_REFRESHDATE,
                    BO_REFRESHDATE = @BO_REFRESHDATE,
                    BIO_REFRESHDATE = @BIO_REFRESHDATE
                where ID = @ID


                --insert all client data into a table

                declare @CLIENTDATA as table(
                    PROSPECTID uniqueidentifier,
                    WW_ID int,
                    DNB_ID nvarchar(10),
                    THOMSON_ID int,
                    WW_DT datetime,
                    DNB_DT datetime,
                    THOMSON_DT datetime,
                    GS_ID nvarchar(30),
                    GS_DT datetime,
                    GS_MATCHNAME nvarchar(150),
                    GS_MATCHCODE nvarchar(3),
                    DNB_MATCHNAME nvarchar(100),
                    DNB_MATCHCODE nvarchar(3)
                )

                insert into @CLIENTDATA
                select 
                    WEALTH.ID as PROSPECTID,
                    null as WW_ID,
                    null as DNB_ID,
                    WPSECURITIES.VIID as THOMSON_ID,
                    null as WW_DT,
                    null as DNB_DT,
                    MAX(WPSECURITIESHISTORY.DATEADDED) as THOMSON_DT,
                    null as GS_ID,
                    null as GS_DT,
                    null as GS_MATCHNAME,
                    null as GS_MATCHCODE,
                    null as DNB_MATCHNAME,
                    null as DNB_MATCHCODE
                from
                    dbo.WEALTH
                left join dbo.DISABLEDWEALTHUPDATES 
                    DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = WEALTH.ID
                inner join dbo.WPSECURITIES on
                    WPSECURITIES.WEALTHID = WEALTH.ID
                left join dbo.WPSECURITIESHISTORY on
                    WPSECURITIESHISTORY.WPSECURITIESID = WPSECURITIES.ID
                left join dbo.WEALTHSOURCE on
                    WEALTHSOURCE.SOURCE = WPSECURITIES.SOURCE
                where 
                    DISABLEDWEALTHUPDATES.ID is null and
                    WPSECURITIES.REJECTED = 0 and
                    WEALTHSOURCE.ISREFRESHABLE = 1 and
                    (@SECURITIES_REFRESHDATE is null or @SECURITIES_LASTREFRESHDATE is null or @SECURITIES_LASTREFRESHDATE < @SECURITIES_REFRESHDATE)
                group by
                    WEALTH.ID, WPSECURITIES.VIID

                union all

                select 
                    WEALTH.ID as PROSPECTID,
                    null as WW_ID,
                    null as DNB_ID,
                    null as THOMSON_ID,
                    null as WW_DT,
                    null as DNB_DT,
                    null as THOMSON_DT,
                    WPRELATIONSHIP_NPA.EIN as GS_ID,
                    MAX(WPNONPROFITAFFILIATION.DATEADDED) as GS_DT,
                    WPNONPROFITAFFILIATION.FULLNAME as GS_MATCHNAME,
                    WPNONPROFITAFFILIATION.MC as GS_MATCHCODE,
                    null as DNB_MATCHNAME,
                    null as DNB_MATCHCODE 
                from 
                    dbo.WEALTH
                left join dbo.DISABLEDWEALTHUPDATES 
                    DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = WEALTH.ID
                inner join dbo.WPNONPROFITAFFILIATION on
                    WPNONPROFITAFFILIATION.WEALTHID = WEALTH.ID
                left join dbo.WPRELATIONSHIP_NPA on 
                    WPRELATIONSHIP_NPA.ID = WPNONPROFITAFFILIATION.WPRELATIONSHIP_NPA_ID
                left join dbo.WPNONPROFITAFFILIATIONHISTORY on
                    WPNONPROFITAFFILIATIONHISTORY.WPNONPROFITAFFILIATIONID = WPNONPROFITAFFILIATION.ID
                left join dbo.WEALTHSOURCE on
                    WEALTHSOURCE.SOURCE = WPNONPROFITAFFILIATION.SOURCE
                where 
                    DISABLEDWEALTHUPDATES.ID is null and
                    WPNONPROFITAFFILIATION.REJECTED = 0 and
                    WEALTHSOURCE.ISREFRESHABLE = 1 and
                    (@NPA_REFRESHDATE is null or @NPA_LASTREFRESHDATE is null or @NPA_LASTREFRESHDATE < @NPA_REFRESHDATE)
                group by
                    WEALTH.ID, WPRELATIONSHIP_NPA.EIN, WPNONPROFITAFFILIATION.FULLNAME, WPNONPROFITAFFILIATION.MC

                union all

                select 
                    WEALTH.ID as PROSPECTID,
                    null as WW_ID,
                    null as DNB_ID,
                    null as THOMSON_ID,
                    null as WW_DT,
                    null as DNB_DT,
                    null as THOMSON_DT,
                    WPRELATIONSHIP_PF.EIN as GS_ID,
                    MAX(WPPRIVATEFOUNDATIONHISTORY.DATEADDED) as GS_DT,
                    WPPRIVATEFOUNDATION.FULLNAME as GS_MATCHNAME,
                    WPPRIVATEFOUNDATION.MC as GS_MATCHCODE,
                    null as DNB_MATCHNAME,
                    null as DNB_MATCHCODE
                from 
                    dbo.WEALTH
                left join dbo.DISABLEDWEALTHUPDATES 
                    DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = WEALTH.ID
                inner join dbo.WPPRIVATEFOUNDATION on
                    WPPRIVATEFOUNDATION.WEALTHID = WEALTH.ID
                left join dbo.WPRELATIONSHIP_PF on
                    WPRELATIONSHIP_PF.ID = WPPRIVATEFOUNDATION.WPRELATIONSHIP_PF_ID
                left join dbo.WPPRIVATEFOUNDATIONHISTORY on
                    WPPRIVATEFOUNDATIONHISTORY.WPPRIVATEFOUNDATIONID = WPPRIVATEFOUNDATION.ID
                left join dbo.WEALTHSOURCE on
                    WEALTHSOURCE.SOURCE = WPPRIVATEFOUNDATION.SOURCE
                where 
                    DISABLEDWEALTHUPDATES.ID is null and
                    WPPRIVATEFOUNDATION.REJECTED = 0 and
                    WEALTHSOURCE.ISREFRESHABLE = 1 and
                    (@PF_REFRESHDATE is null or @PF_LASTREFRESHDATE is null or @PF_LASTREFRESHDATE < @PF_REFRESHDATE)
                group by
                    WEALTH.ID, WPRELATIONSHIP_PF.EIN, WPPRIVATEFOUNDATION.FULLNAME, WPPRIVATEFOUNDATION.MC

                union all

                select 
                    WEALTH.ID as PROSPECTID,
                    null as WW_ID,
                    WPRELATIONSHIP_BO.DUNS as DNB_ID,
                    null as THOMSON_ID,
                    null as WW_DT,
                    MAX(WPBUSINESSOWNERSHIPHISTORY.DATEADDED) as DNB_DT,
                    null as THOMSON_DT,
                    null as GS_ID,
                    null as GS_DT,
                    null as GS_MATCHNAME,
                    null as GS_MATCHCODE,
                    WPBUSINESSOWNERSHIP.FULLNAME as DNB_MATCHNAME,
                    WPBUSINESSOWNERSHIP.MC as DNB_MATCHCODE
                from 
                    dbo.WEALTH
                left join dbo.DISABLEDWEALTHUPDATES 
                    DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = WEALTH.ID
                inner join dbo.WPBUSINESSOWNERSHIP on
                    WPBUSINESSOWNERSHIP.WEALTHID = WEALTH.ID
                left join dbo.WPRELATIONSHIP_BO on
                    WPRELATIONSHIP_BO.ID = WPBUSINESSOWNERSHIP.WPRELATIONSHIP_BO_ID
                left join dbo.WPBUSINESSOWNERSHIPHISTORY on
                    WPBUSINESSOWNERSHIPHISTORY.WPBUSINESSOWNERSHIPID = WPBUSINESSOWNERSHIP.ID
                left join dbo.WEALTHSOURCE on
                    WEALTHSOURCE.SOURCE = WPBUSINESSOWNERSHIP.SOURCE
                where 
                    DISABLEDWEALTHUPDATES.ID is null and
                    WPBUSINESSOWNERSHIP.REJECTED = 0 and
                    WEALTHSOURCE.ISREFRESHABLE = 1 and
                    (@BO_REFRESHDATE is null or @BO_LASTREFRESHDATE is null or @BO_LASTREFRESHDATE < @BO_REFRESHDATE)
                group by
                    WEALTH.ID, WPRELATIONSHIP_BO.DUNS, WPBUSINESSOWNERSHIP.FULLNAME, WPBUSINESSOWNERSHIP.MC

                union all

                select 
                    WEALTH.ID as PROSPECTID,
                    WPBIOGRAPHICAL.WWID as WW_ID,
                    null as DNB_ID,
                    null as THOMSON_ID,
                    MAX(WPBIOGRAPHICALHISTORY.DATEADDED) as WW_DT,
                    null as DNB_DT,
                    null as THOMSON_DT,
                    null as GS_ID,
                    null as GS_DT,
                    null as GS_MATCHNAME,
                    null as GS_MATCHCODE,
                    null as DNB_MATCHNAME,
                    null as DNB_MATCHCODE
                from 
                    dbo.WEALTH
                left join dbo.DISABLEDWEALTHUPDATES 
                    DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = WEALTH.ID
                inner join dbo.WPBIOGRAPHICAL on
                    WPBIOGRAPHICAL.WEALTHID = WEALTH.ID
                left join dbo.WPBIOGRAPHICALHISTORY on
                    WPBIOGRAPHICALHISTORY.WPBIOGRAPHICALID = WPBIOGRAPHICAL.ID
                left join dbo.WEALTHSOURCE on
                    WEALTHSOURCE.SOURCE = WPBIOGRAPHICAL.SOURCE
                where 
                    DISABLEDWEALTHUPDATES.ID is null and
                    WPBIOGRAPHICAL.REJECTED = 0 and
                    WEALTHSOURCE.ISREFRESHABLE = 1 and
                    (@BIO_REFRESHDATE is null or @BIO_LASTREFRESHDATE is null or @BIO_LASTREFRESHDATE < @BIO_REFRESHDATE)
                group by
                    WEALTH.ID, WPBIOGRAPHICAL.WWID


                --Set the record count

                update dbo.WPSEARCHHISTORY set
                    RECORDCOUNT = (select count(distinct PROSPECTID) from @CLIENTDATA)
                where ID = @ID


                --return the client data

                select 
                    PROSPECTID,
                    null as SPOUSEFLAG,
                    null as SSNUMBER,
                    null as CS_PREFIX,
                    null as CS_FNAME,
                    null as CS_MNAME,
                    null as CS_MAIDNAM,
                    null as CS_LNAME,
                    null as CS_NAMESUF,
                    null as CS_ADDRLI1,
                    null as CS_ADDRLI2,
                    null as CS_ADDRLI3,
                    null as CS_CITY,
                    null as CS_STATE,
                    null as CS_ZIPCODE,
                    null as CS_HOMEPHO,
                    null as COMPANY,
                    null as CS_ADDRBU1,
                    null as CS_ADDRBU2,
                    null as CS_ADDRBU3,
                    null as CS_CITYBUS,
                    null as CS_STATEBU,
                    null as CS_ZIPCODB,
                    null as CS_PHONEBU,
                    null as DOB,
                    null as COMBO_DOB,
                    null as PINKEY,
                    null as EO_ID,
                    null as FEC_ID,
                    null as VICKERS_ID,
                    WW_ID,
                    DNB_ID,
                    THOMSON_ID,
                    null as EO_DT,
                    null as FEC_DT,
                    null as VICKERS_DT,
                    WW_DT,
                    DNB_DT,
                    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,
                    null as CS_ADDRTYP,
                    null as CS_DEGRYR1,
                    GS_ID,
                    GS_DT,
                    GS_MATCHNAME,
                    GS_MATCHCODE,
                    DNB_MATCHNAME,
                    DNB_MATCHCODE
                from 
                    @CLIENTDATA

                return 0;

            end;