USP_GENERATESTEPUPDATEBATCH

CREATE procedure dbo.USP_GENERATESTEPUPDATEBATCH
(
  @STEPID uniqueidentifier,
  @BATCHID uniqueidentifier,
  @SEQUENCE int,      
  @CURRENTAPPUSERID uniqueidentifier
)
as
begin
  set nocount on;

  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @CHANGEAGENTID uniqueidentifier;

  set @ID = NewID();

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

  set @CURRENTDATE = GetDate();

  declare @STEPOWNERID uniqueidentifier;
  declare @STEWARDSHIPPLANID uniqueidentifier;
  declare @CATEGORYCODEID uniqueidentifier;
  declare @OBJECTIVE nvarchar(100);
  declare @TARGETDATE date;
  declare @DATELOCKED bit;
  declare @ACTUALDATE date;
  declare @STARTDATE date;
  declare @ENDDATE date;
  declare @CONTACTMETHODCODEID uniqueidentifier;
  declare @CONTACTPERSONID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;
  declare @RECURSCODE smallint;
  declare @STATUSCODE smallint;
  declare @EVENTID uniqueidentifier;
  declare @MAILINGID uniqueidentifier;
  declare @BENEFITID uniqueidentifier;
  declare @TIMEZONEENTRYID uniqueidentifier;
  declare @TARGETSTARTTIME UDT_HOURMINUTE;
  declare @TARGETENDTIME UDT_HOURMINUTE;
  declare @ACTUALSTARTTIME UDT_HOURMINUTE;
  declare @ACTUALENDTIME UDT_HOURMINUTE;
  declare @ISALLDAYEVENT bit;
  declare @ASSOCIATEDPLANS xml;
  declare @STEPPARTICIPANTS xml;

  select    
    @STEWARDSHIPPLANID = STEP.PLANID,
    @CATEGORYCODEID = STEP.CATEGORYCODEID,
    @OBJECTIVE = STEP.OBJECTIVE,
    @TARGETDATE = STEP.TARGETDATE,
    @DATELOCKED = STEP.DATELOCKED,
    @ACTUALDATE = STEP.ACTUALDATE,
    @STARTDATE = STEP.STARTDATE,
    @ENDDATE = STEP.ENDDATE,
    @CONTACTPERSONID = 
      case
        when STEPOWNER.ISGROUP = 0 and STEPOWNER.ISORGANIZATION = 0 then [PLAN].CONSTITUENTID
        else CONTACTPERSONID
      end,
    @CONTACTMETHODCODEID = STEP.CONTACTMETHODCODEID,                        
    @CONSTITUENTID = STEP.CONSTITUENTID,
    @ASSOCIATEDPLANS = dbo.UFN_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_TOITEMLISTXML(STEP.ID, @CURRENTAPPUSERID),
    @RECURSCODE = STEP.RECURSCODE,
    @STATUSCODE = STEP.STATUSCODE,
    @BENEFITID = STEP.BENEFITID,
    @EVENTID = STEP.EVENTID,
    @MAILINGID = STEP.MAILINGID,
    @TARGETSTARTTIME = STEP.TARGETSTARTTIME,
    @TARGETENDTIME = STEP.TARGETENDTIME,
    @ISALLDAYEVENT = STEP.ISALLDAYEVENT, 
    @TIMEZONEENTRYID = STEP.TIMEZONEENTRYID,
    @ACTUALSTARTTIME = STEP.ACTUALSTARTTIME,
    @ACTUALENDTIME = STEP.ACTUALENDTIME,
    @STEPPARTICIPANTS = dbo.UFN_STEWARDSHIPPLANSTEP_PARTICIPANTS_TOITEMLISTXML(STEP.ID),
    @STEPOWNERID = STEPOWNER.ID
  from 
    dbo.STEWARDSHIPPLANSTEP STEP
  inner join dbo.STEWARDSHIPPLAN [PLAN]
    on STEP.PLANID = [PLAN].ID
  inner join dbo.CONSTITUENT STEPOWNER
    on [PLAN].CONSTITUENTID = STEPOWNER.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEPOWNER.ID) NF
  where
    STEP.ID = @STEPID;

  begin try
    INSERT INTO dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
      (ID,BATCHID,SEQUENCE,PRIMARYRECORDID
      ,STEPOWNERID,STEWARDSHIPPLANID,CATEGORYCODEID,OBJECTIVE
      ,TARGETDATE,DATELOCKED,ACTUALDATE,STARTDATE,ENDDATE
      ,CONTACTMETHODCODEID,CONTACTPERSONID,CONSTITUENTID,RECURSCODE
      ,STATUSCODE,EVENTID,MAILINGID,BENEFITID
      ,TIMEZONEENTRYID,TARGETSTARTTIME,TARGETENDTIME,ACTUALSTARTTIME,ACTUALENDTIME,ISALLDAYEVENT
      ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
     VALUES
      (@ID,@BATCHID,@SEQUENCE,@STEPID
      ,@STEPOWNERID,@STEWARDSHIPPLANID,@CATEGORYCODEID,@OBJECTIVE
      ,@TARGETDATE,@DATELOCKED,@ACTUALDATE,@STARTDATE,@ENDDATE
      ,@CONTACTMETHODCODEID,@CONTACTPERSONID,@CONSTITUENTID,@RECURSCODE
      ,@STATUSCODE,@EVENTID,@MAILINGID,@BENEFITID
      ,@TIMEZONEENTRYID,@TARGETSTARTTIME,@TARGETENDTIME,@ACTUALSTARTTIME,@ACTUALENDTIME,@ISALLDAYEVENT
      ,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

    if @ASSOCIATEDPLANS is not null 
      SET @ASSOCIATEDPLANS.modify('delete /ASSOCIATEDPLANS/ITEM/ID') --reset IDs...don't want to reuse ones from the originating table


    exec dbo.USP_BATCHSTEWARDSHIPPLANSTEPUPDATE_GETASSOCIATEDPLANS_ADDFROMXML @ID, @CURRENTAPPUSERID, @ASSOCIATEDPLANS, @CHANGEAGENTID;

    if @STEPPARTICIPANTS is not null
      SET @STEPPARTICIPANTS.modify('delete /STEPPARTICIPANTS/ITEM/ID')

    exec dbo.USP_BATCHSTEWARDSHIPPLANSTEPUPDATE_GETSTEPPARTICIPANTS_ADDFROMXML @ID, @STEPPARTICIPANTS, @CHANGEAGENTID;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end