USP_STEWARDSHIPPLAN_CLONERECURRINGCOMPLETEDSTEP

Create a pending step on a stewardship plan for recurring completed steps.
Parameters

Parameter	Parameter Type	Mode
@COMPLETEDSTEPID	uniqueidentifier	IN
@NEXTTARGETDATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


CREATE procedure dbo.USP_STEWARDSHIPPLAN_CLONERECURRINGCOMPLETEDSTEP
(
  @COMPLETEDSTEPID uniqueidentifier,
  @NEXTTARGETDATE datetime,
  @CHANGEAGENTID uniqueidentifier = null,
  @CHANGEDATE datetime = null
)
as
begin

  if not @NEXTTARGETDATE is null
  begin

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

    if @CHANGEDATE is null
      set @CHANGEDATE = getdate();

    declare @NEWSTEPID uniqueidentifier = newid();

    insert into dbo.STEWARDSHIPPLANSTEP
    (
      ID,
      PLANID,
      STATUSCODE,
      TARGETDATE,
      TARGETSTARTTIME,
      TARGETENDTIME,
      TIMEZONEENTRYID,
      CATEGORYCODEID,
      OBJECTIVE,
      CONTACTPERSONID,
      CONTACTMETHODCODEID,
      TEMPLATE,
      RECURSCODE,
      STARTDATE,
      ENDDATE,
      CONSTITUENTID,
      BENEFITID,
      EVENTID,
      MAILINGID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    select
      @NEWSTEPID,
      PLANID,
      0,
      @NEXTTARGETDATE,
      TARGETSTARTTIME,
      TARGETENDTIME,
      TIMEZONEENTRYID, 
      CATEGORYCODEID,
      OBJECTIVE,
      CONTACTPERSONID,
      CONTACTMETHODCODEID,
      TEMPLATE, 
      RECURSCODE,
      STARTDATE,
      ENDDATE, 
      CONSTITUENTID,
      BENEFITID,
      EVENTID,
      MAILINGID,
      @CHANGEAGENTID,
      @CHANGEAGENTID, 
      @CHANGEDATE,
      @CHANGEDATE
    from
      dbo.STEWARDSHIPPLANSTEP
    where
      ID = @COMPLETEDSTEPID;

    -- Copy Associated plans to new pending step

    insert into dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
    (
      ID,
      STEPID,
      PROSPECTPLANID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    select
      newid(),
      @NEWSTEPID,
      PROSPECTPLANID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE
    from
      dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
    where
      STEPID = @COMPLETEDSTEPID;

    -- Copy plan participants

    insert into dbo.STEWARDSHIPPLANSTEPPARTICIPANT
    (
      ID,
      STEWARDSHIPPLANSTEPID,
      CONSTITUENTID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    select
      newid(),
      @NEWSTEPID,
      CONSTITUENTID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE
    from
      dbo.STEWARDSHIPPLANSTEPPARTICIPANT
    where
      STEWARDSHIPPLANSTEPID = @COMPLETEDSTEPID;
  end

end