USP_DATAFORMTEMPLATE_EDITLOAD_STEWARDSHIPPLANSTEP

The load procedure used by the edit dataform template "Stewardship Plan Step Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@STEPOWNERNAME	nvarchar(154)	INOUT	Constituent name
@STEPOWNERISINDIVIDUAL	bit	INOUT	Step owner is an individual
@STEWARDSHIPPLANID	uniqueidentifier	INOUT	Plan ID
@CATEGORYCODEID	uniqueidentifier	INOUT	Category
@OBJECTIVE	nvarchar(100)	INOUT	Objective
@TARGETDATE	datetime	INOUT	Target date
@DATELOCKED	bit	INOUT	Locked
@ACTUALDATE	datetime	INOUT	Actual date
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@CONTACTMETHODCODEID	uniqueidentifier	INOUT	Contact method
@CONSTITUENTID	uniqueidentifier	INOUT	Assigned to
@ASSOCIATEDPLANS	xml	INOUT	Associated plans
@TEMPLATE	nvarchar(100)	INOUT	Template
@RECURSCODE	smallint	INOUT	Frequency
@STATUSCODE	smallint	INOUT	Status
@NEXTTARGETDATE	datetime	INOUT	Next target date
@LINKTYPECODE	smallint	INOUT	Link type
@BENEFITID	uniqueidentifier	INOUT	Benefit
@EVENTID	uniqueidentifier	INOUT	Event
@MAILINGID	uniqueidentifier	INOUT	Mailing
@TARGETSTARTTIME	UDT_HOURMINUTE	INOUT	Target start time
@TARGETENDTIME	UDT_HOURMINUTE	INOUT	Target end time
@TIMEZONEENTRYID	uniqueidentifier	INOUT	Time zone
@ISALLDAYEVENT	bit	INOUT	Is all day event
@ACTUALSTARTTIME	UDT_HOURMINUTE	INOUT	Actual start time
@ACTUALENDTIME	UDT_HOURMINUTE	INOUT	Actual end time
@STEPPARTICIPANTS	xml	INOUT	Plan participants
@STEPOWNERID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_STEWARDSHIPPLANSTEP
(
  @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 datetime = null output,
  @DATELOCKED bit = null output,
  @ACTUALDATE datetime = null output,
  @STARTDATE datetime = null output,
  @ENDDATE datetime = 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 datetime = 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_OWNER.NAME,
    @STEPOWNERISINDIVIDUAL = 
      case 
        when OWNER.ISGROUP = 1 or OWNER.ISORGANIZATION = 1 then 0
        else 1
      end,
    @CATEGORYCODEID = STEP.CATEGORYCODEID,
    @OBJECTIVE = STEP.OBJECTIVE,
    @TARGETDATE = STEP.TARGETDATE,
    @DATELOCKED = STEP.DATELOCKED,
    @ACTUALDATE = STEP.ACTUALDATE,
    @STARTDATE = STEP.STARTDATE,
    @ENDDATE = STEP.ENDDATE,
    @CONTACTMETHODCODEID = STEP.CONTACTMETHODCODEID,                        
    @CONSTITUENTID = STEP.CONSTITUENTID,
    @ASSOCIATEDPLANS = dbo.UFN_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_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_STEWARDSHIPPLANSTEP_PARTICIPANTS_TOITEMLISTXML(STEP.ID),
    @STEPOWNERID = OWNER.ID
  from 
    dbo.STEWARDSHIPPLANSTEP STEP
  inner join dbo.STEWARDSHIPPLAN [PLAN]
        on STEP.PLANID = [PLAN].ID
  inner join dbo.CONSTITUENT OWNER
    on [PLAN].CONSTITUENTID = OWNER.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(OWNER.ID) NF_OWNER
  where
    STEP.ID = @ID;

  return 0;

end