USP_DATAFORMTEMPLATE_EDIT_PROSPECTRESEARCHREQUEST_2

The save procedure used by the edit dataform template "Prospect Research Request Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@STATUSCODE	tinyint	IN	Status
@PROSPECTRESEARCHREQUESTPRIORITYCODEID	uniqueidentifier	IN	Priority
@DUEDATE	date	IN	Due date
@PROSPECTRESEARCHREQUESTTYPECODEID	uniqueidentifier	IN	Research type
@PROSPECTRESEARCHREQUESTREASONCODEID	uniqueidentifier	IN	Request reason
@REQUESTEDBYID	uniqueidentifier	IN	Requested by
@SUBMITTEDBYID	uniqueidentifier	IN	Submitted by
@NOTES	nvarchar(max)	IN	Notes
@COPYTOLINKEDREQUESTS	bit	IN	Update research request information on open individual requests
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITES	xml	IN	Sites

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROSPECTRESEARCHREQUEST_2
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @STATUSCODE tinyint,
  @PROSPECTRESEARCHREQUESTPRIORITYCODEID uniqueidentifier,
  @DUEDATE date,
  @PROSPECTRESEARCHREQUESTTYPECODEID uniqueidentifier,
  @PROSPECTRESEARCHREQUESTREASONCODEID uniqueidentifier,
  @REQUESTEDBYID uniqueidentifier,
  @SUBMITTEDBYID uniqueidentifier,
  @NOTES nvarchar(max),
  @COPYTOLINKEDREQUESTS bit,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITES xml
)
as
  set nocount on;

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

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  begin try
    exec dbo.USP_PROSPECTRESEARCHREQUEST_VALIDATESITES @SITES, @CURRENTAPPUSERID, 'e15a6824-63b7-4498-b189-13937024391e', 1, 1, @REQUESTEDBYID, @SUBMITTEDBYID;

    -- save the existing requester and submittor

    declare
      @OLDREQUESTEDBYID uniqueidentifier,
      @OLDSUBMITTEDBYID uniqueidentifier;

    select
      @OLDREQUESTEDBYID = PRR.[REQUESTEDBYID],
      @OLDSUBMITTEDBYID = PRR.[SUBMITTEDBYID]
    from dbo.[PROSPECTRESEARCHREQUEST] PRR
    where PRR.[ID] = @ID;

    -- handle updating the data

    update dbo.PROSPECTRESEARCHREQUEST set
      STATUSCODE = @STATUSCODE,
      PROSPECTRESEARCHREQUESTPRIORITYCODEID = @PROSPECTRESEARCHREQUESTPRIORITYCODEID,
      DUEDATE = @DUEDATE,
      PROSPECTRESEARCHREQUESTTYPECODEID = @PROSPECTRESEARCHREQUESTTYPECODEID,
      PROSPECTRESEARCHREQUESTREASONCODEID = @PROSPECTRESEARCHREQUESTREASONCODEID,
      REQUESTEDBYID = @REQUESTEDBYID,
      SUBMITTEDBYID = @SUBMITTEDBYID,
      NOTES = @NOTES,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @ID;

    exec dbo.USP_PROSPECTRESEARCHREQUEST_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;

    update dbo.PROSPECTRESEARCHREQUESTCONSTITUENT set
      PROSPECTRESEARCHREQUESTPRIORITYCODEID = @PROSPECTRESEARCHREQUESTPRIORITYCODEID,
      DUEDATE = @DUEDATE,
      PROSPECTRESEARCHREQUESTTYPECODEID = @PROSPECTRESEARCHREQUESTTYPECODEID,
      PROSPECTRESEARCHREQUESTREASONCODEID = @PROSPECTRESEARCHREQUESTREASONCODEID,
      NOTES = case when NOTES = '' or NOTES is null then @NOTES else NOTES end,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      PROSPECTRESEARCHREQUESTID = @ID and 
      STATUSCODE not in(4, 6) and
      @COPYTOLINKEDREQUESTS = 1;

    if @STATUSCODE in (4, 5)
    begin
      update dbo.PROSPECTRESEARCHREQUEST set
        STATUSCODE = case when ASSIGNEDTOID is null then 1 else 2 end
      where ID = @ID;

      update dbo.PROSPECTRESEARCHREQUESTCONSTITUENT set
        STATUSCODE = case when ASSIGNEDTOID is null then 1 else 2 end,
        DATECOMPLETED = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        PROSPECTRESEARCHREQUESTID = @ID and
        STATUSCODE <> 6;
    end

    exec dbo.USP_PROSPECTRESEARCHREQUEST_EDITEDALERT_SEND @CURRENTAPPUSERID, @OLDREQUESTEDBYID, @OLDSUBMITTEDBYID, @ID;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

  return 0;