USP_WEALTHPOINT_UPDATESEARCHHISTORY_RESEARCHGROUP

Updates the WealthPoint Search History table for a research group 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
@REQUESTEDDATASOURCES	nvarchar(250)	IN
Definition

Copy


            CREATE procedure dbo.USP_WEALTHPOINT_UPDATESEARCHHISTORY_RESEARCHGROUP(
                @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,
        @REQUESTEDDATASOURCES nvarchar(250)=null
            ) 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),     
          REQUESTEDDATASOURCES = isnull(@REQUESTEDDATASOURCES,REQUESTEDDATASOURCES),
                    CHANGEDBYID =         @CHANGEAGENTID,
                    DATECHANGED =        @CURRENTDATE
                where
                    ID = @ID

                if @STATUSCODE in (3,4,5) 
                begin
                    update
                        dbo.WEALTH
                    set
                        PENDINGSEARCH =   0,
                        WEALTHPOINTDATE = case  
                                            when @STATUSCODE = 3 and CONSTITUENT.FIRSTNAME <> ''
                                                then @DATERETRIEVED
                                            when @STATUSCODE = 3 and CONSTITUENT.FIRSTNAME = ''
                                                then WEALTHPOINTDATE
                                            when @STATUSCODE = 4 
                                                then WEALTHPOINTDATE
                                            when @STATUSCODE = 5 
                                                then WEALTHPOINTDATE
                                          end,
                        CHANGEDBYID =      @CHANGEAGENTID,
                        DATECHANGED =     @CURRENTDATE
                    from
                        dbo.WEALTH
                    inner join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WEALTH.ID
                    inner join dbo.RESEARCHGROUP RG on RG.ID = RGM.RESEARCHGROUPID
                    inner join dbo.WPSEARCHHISTORY WP on WP.RESEARCHGROUPID = RG.ID
                    left join dbo.CONSTITUENT on CONSTITUENT.ID = RGM.CONSTITUENTID
                    where 
                        WP.ID = @ID         
                end

                return 0;

            end;