USP_PROSPECTSEARCH_REVIEW_GETRPDATA

create procedure dbo.USP_PROSPECTSEARCH_REVIEW_GETRPDATA
(
    @ID int,
  @RPLOOKUPID nvarchar(100) = null output,
  @RPID uniqueidentifier = null output,
    @ESTIMATEDWEALTHID uniqueidentifier = null output,
    @ESTIMATEDWEALTH_MIN money = null output,
    @ESTIMATEDWEALTH_MAX money = null output
)
as
    set nocount on;

  select @RPLOOKUPID = CONSTITUENT.LOOKUPID,
    @RPID = CONSTITUENT.ID
  from dbo.CONSTITUENT
  inner join dbo.RE7INTEGRATIONCONSTITUENTMAP 
    on RE7INTEGRATIONCONSTITUENTMAP.ID = CONSTITUENT.ID
    and RE7INTEGRATIONCONSTITUENTMAP.RE7RECORDID = @ID;

  if (@RPID is not null)
  begin
    --CAPACITY

    select    
        @ESTIMATEDWEALTHID = WC.ESTIMATEDWEALTHID
    from
      WEALTHCAPACITY WC
    where WC.ID = @RPID
    /*
    Get major giving translation fields
    */
    if @ESTIMATEDWEALTHID is not null
    begin
      SELECT @ESTIMATEDWEALTH_MIN = MINIMUMAMOUNT from dbo.[ESTIMATEDWEALTH] where ID=@ESTIMATEDWEALTHID;

      SELECT top 1 @ESTIMATEDWEALTH_MAX = MINIMUMAMOUNT - 1 from dbo.[ESTIMATEDWEALTH]
      WHERE MINIMUMAMOUNT > @ESTIMATEDWEALTH_MIN order by MINIMUMAMOUNT ;
    end
  end

    return 0;