USP_DATAFORMTEMPLATE_EDITLOAD_BATCHSTEWARDSHIPPLANSTEPUPDATE

Returns stewardship plan step update batch row information.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@STEPOWNERNAME	nvarchar(154)	INOUT
@STEPOWNERISINDIVIDUAL	bit	INOUT
@STEWARDSHIPPLANID	uniqueidentifier	INOUT
@CATEGORYCODEID	uniqueidentifier	INOUT
@OBJECTIVE	nvarchar(100)	INOUT
@TARGETDATE	date	INOUT
@DATELOCKED	bit	INOUT
@ACTUALDATE	date	INOUT
@STARTDATE	date	INOUT
@ENDDATE	date	INOUT
@CONTACTPERSONID	uniqueidentifier	INOUT
@CONTACTMETHODCODEID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@ASSOCIATEDPLANS	xml	INOUT
@TEMPLATE	nvarchar(100)	INOUT
@RECURSCODE	smallint	INOUT
@STATUSCODE	smallint	INOUT
@NEXTTARGETDATE	date	INOUT
@LINKTYPECODE	smallint	INOUT
@BENEFITID	uniqueidentifier	INOUT
@EVENTID	uniqueidentifier	INOUT
@MAILINGID	uniqueidentifier	INOUT
@TARGETSTARTTIME	UDT_HOURMINUTE	INOUT
@TARGETENDTIME	UDT_HOURMINUTE	INOUT
@TIMEZONEENTRYID	uniqueidentifier	INOUT
@ISALLDAYEVENT	bit	INOUT
@ACTUALSTARTTIME	UDT_HOURMINUTE	INOUT
@ACTUALENDTIME	UDT_HOURMINUTE	INOUT
@STEPPARTICIPANTS	xml	INOUT
@STEPOWNERID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BATCHSTEWARDSHIPPLANSTEPUPDATE
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @STEPOWNERNAME nvarchar(154) = null output,
  @STEPOWNERISINDIVIDUAL bit = null output,
  @STEWARDSHIPPLANID uniqueidentifier = null output,
  @CATEGORYCODEID uniqueidentifier = null output,
  @OBJECTIVE nvarchar(100) = null output,
  @TARGETDATE date = null output,
  @DATELOCKED bit = null output,
  @ACTUALDATE date = null output,
  @STARTDATE date = null output,
  @ENDDATE date = null output,    
  @CONTACTPERSONID uniqueidentifier = null output,
  @CONTACTMETHODCODEID uniqueidentifier = null output,                        
  @CONSTITUENTID uniqueidentifier = null output,
  @ASSOCIATEDPLANS xml = null output,
  @TEMPLATE nvarchar(100) = null output,
  @RECURSCODE smallint = null output,
  @STATUSCODE smallint = null output,
  @NEXTTARGETDATE date = null output,
  @LINKTYPECODE smallint = null output,
  @BENEFITID uniqueidentifier = null output,
  @EVENTID uniqueidentifier = null output,
  @MAILINGID uniqueidentifier = null output,
  @TARGETSTARTTIME dbo.UDT_HOURMINUTE = null output,
  @TARGETENDTIME dbo.UDT_HOURMINUTE = null output,
  @TIMEZONEENTRYID uniqueidentifier = null output,
  @ISALLDAYEVENT bit = null output,
  @ACTUALSTARTTIME dbo.UDT_HOURMINUTE = null output,
  @ACTUALENDTIME dbo.UDT_HOURMINUTE = null output,
  @STEPPARTICIPANTS xml = null output,
  @STEPOWNERID uniqueidentifier = null output
) as begin
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @TSLONG = STEP.TSLONG,        
    @STEWARDSHIPPLANID = STEP.PLANID,
    @STEPOWNERNAME = NF.NAME,

    @STEPOWNERISINDIVIDUAL = 
      case
        when STEPOWNER.ISGROUP = 0 and STEPOWNER.ISORGANIZATION = 0 then 1
        else 0
      end,

    @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_BATCHSTEWARDSHIPPLANSTEPUPDATE_GETASSOCIATEDPLANS_TOITEMLISTXML(STEP.ID, @CURRENTAPPUSERID),
    @TEMPLATE = STEP.TEMPLATE,
    @RECURSCODE = STEP.RECURSCODE,
    @STATUSCODE = STEP.STATUSCODE,
    @LINKTYPECODE = 
      case
        when not STEP.EVENTID is null then 1
        when not STEP.MAILINGID is null then 2
        else 0
      end,
    @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_BATCHSTEWARDSHIPPLANSTEPUPDATE_GETSTEPPARTICIPANTS_TOITEMLISTXML(STEP.ID),
    @STEPOWNERID = STEPOWNER.ID
  from 
    dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BATCHSTEWARDSHIPPLANSTEPUPDATE 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 = @ID;

  return 0;

end