-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 19-APR-2002
--
-- This function returns the most recent final grade for the passed student, crn and term.
-- If there is no grade in history, the function returns the final grade (if any)from
-- SFRSTCR.
--

create or replace function f_lmu_get_final_grade (pidm number, crn varchar2, term varchar2)
  return varchar2
as
  fgrd varchar2(3);
begin
  begin
    select a.shrtckg_grde_code_final
    into fgrd
    from shrtckn,
      shrtckg a
    where shrtckn_pidm=pidm
    and shrtckn_term_code=term
    and shrtckn_crn=crn
    and shrtckn_pidm=a.shrtckg_pidm
    and shrtckn_term_code=a.shrtckg_term_code
    and shrtckn_seq_no=a.shrtckg_tckn_seq_no
    and a.shrtckg_seq_no=
      (select max(b.shrtckg_seq_no) from shrtckg b
       where a.shrtckg_pidm=b.shrtckg_pidm
       and a.shrtckg_term_code=b.shrtckg_term_code
       and a.shrtckg_tckn_seq_no=b.shrtckg_tckn_seq_no);
  exception when others then
    fgrd:=null;
  end;

  if fgrd is null then
    begin
      select sfrstcr_grde_code
      into fgrd
      from sfrstcr
      where sfrstcr_pidm=pidm
      and sfrstcr_term_code=term
      and sfrstcr_crn=crn;
    exception when others then
      fgrd:=null;
    end;
  end if;

  return fgrd;
end;
/