USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYPROSPECTTILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYPROSPECTTILE
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ISPROSPECT bit = null output,
  @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null output,
  @ISFLAGGED bit = null output,
  @PROSPECTMANAGERNAME nvarchar(700) = null output,
  @PROSPECTSTATUS nvarchar(100) = null output,
  @PROSPECTMANAGERSTARTDATE datetime = null output
)
as
  set nocount on;

  set @DATALOADED = 1;
  set @ISPROSPECT = 0;

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

  set @ISPROSPECT = dbo.UFN_CONSTITUENT_ISPROSPECT(@ID);

  select
    @PROSPECTMANAGERFUNDRAISERID =
      case when PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null
      else PROSPECT.PROSPECTMANAGERFUNDRAISERID
    end,
    @PROSPECTMANAGERNAME =
      case when PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null
      else NF.NAME
    end,
    @PROSPECTMANAGERSTARTDATE =
      case when PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null
      else PROSPECT.PROSPECTMANAGERSTARTDATE
    end,
    @PROSPECTSTATUS = PROSPECTSTATUSCODE.DESCRIPTION
  from dbo.PROSPECT
    left join dbo.PROSPECTSTATUSCODE on PROSPECT.PROSPECTSTATUSCODEID = PROSPECTSTATUSCODE.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.PROSPECTMANAGERFUNDRAISERID) NF
  where
    PROSPECT.ID = @ID;

  if exists (select top 1 ID from dbo.FLAGGEDPROSPECT where PROSPECTID = @ID and APPUSERID = @CURRENTAPPUSERID)
    set @ISFLAGGED = 1;

  return 0;