create or replace
Function cbnf_single
/* Certs by Number Function Single
Purpose: 
  Determine whether or not a student meets the core requirements
  and has enough electives to qualify for a particular cert
Author:
  Frank Woodall
*/
(
  p_cert_id IN Varchar2,
  p_student_id IN Varchar2
)
Return Number As
  v_retVal                Number := 1;
  v_valid_start           CERTIFICATES.VALID_START%Type;
  v_required_class        REQUIREMENTS.COURSE_ID%Type;
  v_required_grade        REQUIREMENTS.GRADE%Type;
  v_mostRecentClassDate   GRADES.SEMESTER_ID%Type := 0;
  
  Cursor CertCursor Is
    SELECT valid_start vstart, valid_end vend
      FROM certificates
     WHERE cert_id = p_cert_id;
  c_CertCursor CertCursor%Rowtype;
  
  Cursor ReqCursor Is
    SELECT course_id cid, grade g
      FROM requirements
     WHERE cert_id = p_cert_id;
  c_ReqCursor ReqCursor%Rowtype;
  
  Cursor StuCursor (pi_student_id In Varchar2, pi_course_id In Varchar2) Is
    SELECT grade g, semester_id semid
      FROM grades
     WHERE student_id = pi_student_id
       AND course_id = pi_course_id;
  c_StuCursor StuCursor%Rowtype;
  
  Cursor ElecCursor(pi_cert_id In Varchar2) Is
    SELECT elec_type etype, num_of_classes numClass
      FROM Cert_elec_reqs
     WHERE cert_id = p_cert_id;
  c_ElecCursor ElecCursor%Rowtype;
   
Begin

  Open CertCursor;
    Fetch CertCursor
      Into c_CertCursor;
        dbms_output.put_line('Start Date: '||c_CertCursor.vstart);
        dbms_output.put_line('End Date: '||c_CertCursor.vend);
        
      
      Open ReqCursor;
        Loop
          Fetch ReqCursor
            Into c_ReqCursor;
          Exit When ReqCursor%NOTFOUND;                
          
          Open StuCursor(p_student_id, c_ReqCursor.cid);
            Fetch StuCursor
              Into c_StuCursor;
                If StuCursor%FOUND Then
                  dbms_output.put_line(initcap('Required Class: '   || c_ReqCursor.cid ||
                            ', Required Grade: ' || c_ReqCursor.g));
                  
                  If c_StuCursor.g >= c_ReqCursor.g Then
                    dbms_output.put_line(initcap('Students grade for class ' ||
                              c_ReqCursor.cid || ': ' ||
                              c_StuCursor.g));
                  Else
                    dbms_output.put_line(initcap('Grade not high enough'));
                    Return 0; 
                  End If; --End student grade > required grade if
                Else
                  dbms_output.put_line(initcap('Class Not Found'));
                  Return 0;
                End If;--end StuCursor%Found if
          Close StuCursor;            
        End Loop;--End ReqCursor loop
      Close ReqCursor;
      
      dbms_output.put_line('Checking Electives');
      
      Open ElecCursor(p_cert_id);
        Loop
          Fetch ElecCursor
            Into c_ElecCursor;
          Exit When ElecCursor%NOTFOUND;        
            v_retVal := she_date(p_student_id, c_ElecCursor.etype, c_ElecCursor.numClass, p_cert_id, c_CertCursor.vend);
        
            If v_retVal = 0 Then
              dbms_output.put_line('Elective not in right date range');
              Close ElecCursor;
              GoTo GoAgain;
            End If; 
        End Loop;
      Close ElecCursor;      
      <<GoAgain>>
  Close CertCursor;
  Return v_retVal;
End;