UFN_PROSPECTPLAN_CURRENTSTATUS

CREATE function dbo.UFN_PROSPECTPLAN_CURRENTSTATUS(@PROSPECTPLANID uniqueidentifier)
returns uniqueidentifier
with execute as caller
as begin
  declare @STATUS uniqueidentifier;

  -- Select the phase of the earliest incomplete step.


  select top 1 
    @STATUS=I.PROSPECTPLANSTATUSCODEID
  from PROSPECTPLAN PP
    inner join dbo.INTERACTION I on I.PROSPECTPLANID=PP.ID
    inner join dbo.PROSPECTPLANSTATUSCODE PPSC on I.PROSPECTPLANSTATUSCODEID = PPSC.ID
  where
    I.PROSPECTPLANID=@PROSPECTPLANID
    and I.STATUSCODE=1
  order by I.EXPECTEDDATE, PPSC.SEQUENCE desc;

  if @STATUS is null begin
    -- Select the phase of the most recent successful step.

    select top 1
      @STATUS=I.PROSPECTPLANSTATUSCODEID
    from
      dbo.INTERACTION I
    inner join
      dbo.PROSPECTPLANSTATUSCODE PPSC on I.PROSPECTPLANSTATUSCODEID = PPSC.ID
    where
      I.PROSPECTPLANID=@PROSPECTPLANID
      and I.STATUSCODE=2
    order by
      I.ACTUALDATE desc, PPSC.SEQUENCE desc;
  end;

  /* If we do not have pending or completed steps, the plan stage should be blank.
    if @STATUS is null begin
      -- Select the phase of the earliest step (it would have to be a failed step).
      select top 1
        @STATUS=PROSPECTPLANSTATUSCODEID
      from
        dbo.INTERACTION
      where
        PROSPECTPLANID=@PROSPECTPLANID
      order by
        ACTUALDATE;
    end;
  */
  return @STATUS;
end;