USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLAN_PAGEDATA

The load procedure used by the view dataform template "Prospect Plan Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PROSPECTID	uniqueidentifier	INOUT	PROSPECTID
@PROSPECT	nvarchar(700)	INOUT	PROSPECT
@PROSPECTPLAN	nvarchar(100)	INOUT	PROSPECTPLAN
@PLANNAME	nvarchar(100)	INOUT	PLANNAME
@ISACTIVE	bit	INOUT	ISACTIVE
@HASSTEWARDSHIPPLAN	bit	INOUT	HASSTEWARDSHIPPLAN
@ISGROUP	bit	INOUT	ISGROUP
@ISHOUSEHOLD	bit	INOUT	ISHOUSEHOLD
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@HASPRIMARYMANAGER	bit	INOUT	HASPRIMARYMANAGER
@HASSECONDARYMANAGER	bit	INOUT	HASSECONDARYMANAGER
@HASPROSPECTMANAGER	bit	INOUT	HASPROSPECTMANAGER
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@HASOPPORTUNITYWITHASSOCIATEDREVENUE	bit	INOUT	HASOPPORTUNITYWITHASSOCIATEDREVENUE
@HASASSOCIATEDSTEWARDSHIPSTEPS	bit	INOUT	HASASSOCIATEDSTEWARDSHIPSTEPS
@OPPORTUNITYCOUNT	int	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLAN_PAGEDATA
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @PROSPECTID uniqueidentifier = null output,
  @PROSPECT nvarchar(700) = null output,
  @PROSPECTPLAN nvarchar(100) = null output,
  @PLANNAME nvarchar(100) = null output,
  @ISACTIVE bit = null output,
  @HASSTEWARDSHIPPLAN bit = null output,
  @ISGROUP bit = null output,
  @ISHOUSEHOLD bit = null output,
  @ATTRIBUTEDEFINED bit = null output,
  @HASPRIMARYMANAGER bit = null output,
  @HASSECONDARYMANAGER bit = null output, 
  @HASPROSPECTMANAGER bit = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @HASOPPORTUNITYWITHASSOCIATEDREVENUE bit = null output,
  @HASASSOCIATEDSTEWARDSHIPSTEPS bit = null output,
  @OPPORTUNITYCOUNT int = null output
)
as begin
  set nocount on;

  set @DATALOADED = 0;
  set @HASASSOCIATEDSTEWARDSHIPSTEPS = 0;

  declare @CURRENTDATEEARLIESTTIME datetime;
  set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  select 
    @DATALOADED = 1,
    @PROSPECTID = CONSTITUENT.ID,
    @PROSPECT = NF.NAME,
    @PROSPECTPLAN = PROSPECTPLANTYPECODE.DESCRIPTION,
    @PLANNAME = PROSPECTPLAN.NAME,
    @ISACTIVE = PROSPECTPLAN.ISACTIVE,
    @HASSTEWARDSHIPPLAN = dbo.UFN_CONSTITUENT_HASSTEWARDSHIPPLAN(CONSTITUENT.ID),
    @ISGROUP = CONSTITUENT.ISGROUP,
    @ISHOUSEHOLD = (case when GROUPDATA.GROUPTYPECODE = 0 then '1' else '0' end),
    @HASPRIMARYMANAGER = (case when PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is null or PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end), 
    @HASSECONDARYMANAGER = (case when PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is null or SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end),
    @HASPROSPECTMANAGER = (case when PROSPECT.PROSPECTMANAGERFUNDRAISERID is null or PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end)   
  from
    dbo.PROSPECTPLAN
  left outer join
    dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLAN.PROSPECTID
  left outer join
    dbo.PROSPECT on PROSPECT.ID = CONSTITUENT.ID
  left outer join
    dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID = PROSPECTPLAN.PROSPECTPLANTYPECODEID
  left outer join
    dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  where
    PROSPECTPLAN.ID = @ID;

  set @HASOPPORTUNITYWITHASSOCIATEDREVENUE = cast(0 as bit);

  if exists
  (
    select top(1) 
      REVENUEOPPORTUNITY.ID
    from
      dbo.OPPORTUNITY
    inner join
      dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
    where
      OPPORTUNITY.PROSPECTPLANID = @ID
  )
    set @HASOPPORTUNITYWITHASSOCIATEDREVENUE = cast(1 as bit);

  set @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('Prospect Plan',@CURRENTAPPUSERID);

  select top 1
    @HASASSOCIATEDSTEWARDSHIPSTEPS = 1
  from
    dbo.PROSPECTPLAN
  inner join
    dbo.STEWARDSHIPSTEPASSOCIATEDPLAN on STEWARDSHIPSTEPASSOCIATEDPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
  where
    PROSPECTPLAN.ID = @ID;

  select @OPPORTUNITYCOUNT = count(*) from dbo.OPPORTUNITY where OPPORTUNITY.PROSPECTPLANID = @ID

  return 0;

end