USP_WEALTHPOINT_UPDATESEARCHHISTORY_CONSTITUENT

Updates the WealthPoint Search History table for a single constituent search.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@JOBID	nvarchar(50)	IN
@STATUSCODE	int	IN
@STATUSDETAILS	nvarchar(500)	IN
@PARSING	bit	IN
@DATESTARTED	datetime	IN
@DATEREADY	datetime	IN
@DATERETRIEVED	datetime	IN
@FORCEOVERRIDE	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_WEALTHPOINT_UPDATESEARCHHISTORY_CONSTITUENT(
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @JOBID nvarchar(50) = null,
                @STATUSCODE int = 0,
                @STATUSDETAILS nvarchar(500)='',
                @PARSING bit = 0,
                @DATESTARTED datetime = null,
                @DATEREADY datetime =  null,
                @DATERETRIEVED datetime = null,
                @FORCEOVERRIDE bit = 1
            ) as begin
                set nocount on;

                declare @CURRENTDATE datetime;

                set @CURRENTDATE = getdate();

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

        -- There has been an issue surrounding historical real estate when we use the web server's date 

        -- instead of SQL server time values.

        if @DATEREADY is not null
          set @DATEREADY = @CURRENTDATE;

                if @FORCEOVERRIDE = 0  
                  begin
                    select @PARSING = PARSING from dbo.WPSEARCHHISTORY where ID = @ID

                    if @PARSING = 1
                      select @STATUSDETAILS = STATUSDETAILS from dbo.WPSEARCHHISTORY where ID = @ID
                  end

                update
                    dbo.WPSEARCHHISTORY
                set    
                    STATUSCODE =         isnull(@STATUSCODE,STATUSCODE),
                    STATUSDETAILS =      isnull(@STATUSDETAILS,STATUSDETAILS),
                    PARSING =            isnull(@PARSING,PARSING),
                    DATESTARTED =        isnull(@DATESTARTED,DATESTARTED),
                    DATEREADY =          isnull(@DATEREADY,DATEREADY),
                    DATERETRIEVED =         isnull(@DATERETRIEVED,DATERETRIEVED),
                    JOBID =              isnull(@JOBID,JOBID),          
                    CHANGEDBYID =           @CHANGEAGENTID,
                    DATECHANGED =        @CURRENTDATE
                where
                    ID = @ID

                if @STATUSCODE in(3,4,5)
                begin

                  declare @CONSTITUENTID uniqueidentifier

                  select 
                    @CONSTITUENTID = CONSTITUENTID
                  from
                    dbo.WPSEARCHHISTORY
                  where
                      ID = @ID

                    update
                        dbo.WEALTH
                    set
                        PENDINGSEARCH =   0,
                        WEALTHPOINTDATE = case @STATUSCODE 
                                            when 3 then @DATERETRIEVED
                                            when 4 then WEALTHPOINTDATE
                      when 5 then WEALTHPOINTDATE
                                          end,
                        CHANGEDBYID =      @CHANGEAGENTID,
                        DATECHANGED =   @CURRENTDATE
                    where
                        ID = @CONSTITUENTID     
                end

                return 0;

            end;