USP_DATAFORM_EDITLOAD_STEWARDSHIPPLANPENDINGSTEPS_ORGWITHSUBSIDIARIES

The load procedure used by the edit dataform template "Organization Stewardship Plan With Subsidiaries 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.
@CURRENTPLANID	uniqueidentifier	INOUT	Current Plan ID
@NEWSTEPSTATUSCODE	smallint	INOUT
@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_ORGWITHSUBSIDIARIES
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,    
  @CURRENTPLANID uniqueidentifier = null 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;

  set @CURRENTPLANID = @ID;

  declare @PROSPECTID uniqueidentifier
  select @PROSPECTID = STEWARDSHIPPLAN.CONSTITUENTID
  from dbo.STEWARDSHIPPLAN
  where STEWARDSHIPPLAN.ID = @ID

  declare @SUBSIDIARIES table (ID uniqueidentifier);

  insert into @SUBSIDIARIES 
    (ID)            
  select ID
  from dbo.UFN_CORPORATION_GETSUBSIDIARIES(@ID, 0);

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

      case
        when STEP.PLANID <> @ID and (STEP.CONTACTPERSONID is null or STEP.CONTACTPERSONID <> @ID) then 1
        else 0
      end as ISSUBSIDIARYSTEP,

      case
        when STEP.PLANID <> @ID and STEP.CONTACTPERSONID = @ID then 1
        else 0
      end as STEPROLLEDDOWN,

      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
        )
      end as ASSOCIATEDPLANSTEXT,

      STEP.TEMPLATE,                        
      STEP.RECURSCODE,
      STEP.STATUSCODE,
      STEP.EVENTID,
      STEP.MAILINGID,
      STEP.BENEFITID,
      NF.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
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME([PLAN].CONSTITUENTID) NF
    where
      (PLANID = @ID
       or CONTACTPERSONID = @PROSPECTID
       or PLANID in (select SP.ID from dbo.UFN_CORPORATION_GETSUBSIDIARIES(@PROSPECTID, 0) CORP
                     inner join dbo.STEWARDSHIPPLAN SP ON SP.CONSTITUENTID = CORP.ID))
      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 TARGETDATE    
    for xml raw('ITEM'),type,elements,root('STEPS'),BINARY BASE64);

    select 
      @DATALOADED = 1,
      @TSLONG = CONSTITUENT.TSLONG,
      @NEWSTEPSTATUSCODE = 0,
      @CURRENTPLANOWNERNAME = NF.NAME,
      @CONSTITUENTID = [PLAN].CONSTITUENTID,
      @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;

  return 0;

end;