USP_DATAFORM_EDITLOAD_STEWARDSHIPPLANPENDINGSTEPS

The load procedure used by the edit dataform template "Stewardship Plan Pending Steps 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.
@NEWSTEPSTATUSCODE	smallint	INOUT	New step status code
@CURRENTPLANOWNERNAME	nvarchar(154)	INOUT	Current plan owner name
@STEPS	xml	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@STARTDATE	datetime	INOUT	Start date

Definition

Copy


CREATE procedure dbo.USP_DATAFORM_EDITLOAD_STEWARDSHIPPLANPENDINGSTEPS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,    
  @NEWSTEPSTATUSCODE smallint = null output,
  @CURRENTPLANOWNERNAME nvarchar(154) = null output,
  @STEPS xml = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @STARTDATE datetime = null output

) as begin
  set nocount on;

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

  select
    @DATALOADED = 1,
    @TSLONG = CONSTITUENT.TSLONG,
    @NEWSTEPSTATUSCODE = 0,
    @CURRENTPLANOWNERNAME = NF.NAME,
    @CONSTITUENTID = CONSTITUENT.ID,
    @STARTDATE = [PLAN].STARTDATE
  from
    dbo.STEWARDSHIPPLAN [PLAN]
  inner join dbo.CONSTITUENT
    on [PLAN].CONSTITUENTID = CONSTITUENT.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  where
    [PLAN].ID = @ID;

  -- Unable to use UFN_STEWARDSHIPPLAN_FILTEREDSTEPS b/c ASSOCIATEDPLANSTEXT field...
  set @STEPS = 
    (select 
      STEP.ID,
      STEP.PLANID,

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

      STEP.CATEGORYCODEID,
      STEP.OBJECTIVE,
      STEP.TARGETDATE,
      STEP.DATELOCKED,
      STEP.ACTUALDATE,
      STEP.STARTDATE,
      STEP.ENDDATE,
      STEP.CONTACTPERSONID,
      STEP.CONTACTMETHODCODEID,
      STEP.CONSTITUENTID,            
      dbo.UFN_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_TOITEMLISTXML(STEP.ID, @CURRENTAPPUSERID),

      case when (select count(ID) from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID) > 1
        then convert(nvarchar(50), (select count(ID) from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID))
      else (
          select 
            PROSPECTPLAN.NAME
          from
            dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
          inner join dbo.PROSPECTPLAN
            on STEWARDSHIPSTEPASSOCIATEDPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
          where STEWARDSHIPSTEPASSOCIATEDPLAN.STEPID = STEP.ID
            and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
              where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, PROSPECTPLANSITE.SITEID) = 1
            ) > 0
        )
      end as ASSOCIATEDPLANSTEXT,

      STEP.TEMPLATE,                        
      STEP.RECURSCODE,
      STEP.STATUSCODE,
      STEP.EVENTID,
      STEP.MAILINGID,
      STEP.BENEFITID,
      NF_STEPOWNER.NAME as STEPOWNERNAME,
      STEP.TARGETSTARTTIME,
      STEP.TARGETENDTIME,
      STEP.TIMEZONEENTRYID,
      STEP.ISALLDAYEVENT,
      STEP.ACTUALSTARTTIME,
      STEP.ACTUALENDTIME,
      dbo.UFN_STEWARDSHIPPLANSTEP_PARTICIPANTS_TOITEMLISTXML(STEP.ID),
      case
        when not STEP.EVENTID is null then 1
        when not STEP.MAILINGID is null then 2
        else 0
      end as LINKTYPECODE
    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_STEPOWNER
    where
      (PLANID = @ID 
       or CONTACTPERSONID = @CONSTITUENTID)
      and COMPLETED = 0
      and (
        not exists(select ID from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID)
         or
        exists(
          select STEWARDSHIPSTEPASSOCIATEDPLAN.ID 
          from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
          inner join dbo.PROSPECTPLAN
            on PROSPECTPLAN.ID = STEWARDSHIPSTEPASSOCIATEDPLAN.PROSPECTPLANID
          where STEWARDSHIPSTEPASSOCIATEDPLAN.STEPID = STEP.ID 
            and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
              where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, PROSPECTPLANSITE.SITEID) = 1
            ) > 0
          )
        )

    order by STEP.TARGETDATE    
    for xml raw('ITEM'),type,elements,root('STEPS'),BINARY BASE64);

  return 0;

end;