retrieve a student's current major

gistfile1.txt

CREATE OR REPLACE FUNCTION FNC_HG_MAJOR (PIDM_IN SPBPERS.SPBPERS_PIDM%TYPE,
                                         TERM_IN SFRSTCR.SFRSTCR_TERM_CODE%TYPE)
    RETURN VARCHAR2 AS

    HG_MAJOR_VALUE   SOVLFOS.SOVLFOS_MAJR_CODE%TYPE;

BEGIN
    HG_MAJOR_VALUE := NULL;
  
    BEGIN
       SELECT Y.SORLFOS_MAJR_CODE
         INTO HG_MAJOR_VALUE
         FROM SORLFOS Y
        WHERE Y.SORLFOS_PIDM = PIDM_IN
          AND Y.SORLFOS_LFST_CODE = 'MAJOR'
          AND Y.SORLFOS_CACT_CODE = 'ACTIVE'
          AND Y.SORLFOS_PRIORITY_NO =
             (SELECT MIN(Z.SORLFOS_PRIORITY_NO)
                FROM SORLFOS Z
               WHERE Z.SORLFOS_PIDM = PIDM_IN
                 AND Z.SORLFOS_LFST_CODE = 'MAJOR'
                 AND Z.SORLFOS_CACT_CODE = 'ACTIVE'
                 AND Z.SORLFOS_LCUR_SEQNO = Y.SORLFOS_LCUR_SEQNO)
          AND Y.SORLFOS_LCUR_SEQNO =
             (SELECT MAX(A.SORLCUR_SEQNO)
                FROM SORLCUR A
               WHERE A.SORLCUR_PIDM = PIDM_IN
                 AND A.SORLCUR_PRIORITY_NO =
                    (SELECT MIN(B.SORLCUR_PRIORITY_NO)
                       FROM SORLCUR B
                      WHERE B.SORLCUR_PIDM = PIDM_IN
                        AND B.SORLCUR_TERM_CODE = A.SORLCUR_TERM_CODE
                        AND B.SORLCUR_LMOD_CODE = 'LEARNER'
                        AND B.SORLCUR_CACT_CODE = 'ACTIVE')
                 AND A.SORLCUR_LMOD_CODE = 'LEARNER'    
                 AND A.SORLCUR_CACT_CODE = 'ACTIVE'
                 AND A.SORLCUR_TERM_CODE =
                    (SELECT MAX(C.SORLCUR_TERM_CODE)
                       FROM SORLCUR C
                      WHERE C.SORLCUR_PIDM = PIDM_IN
                        AND C.SORLCUR_TERM_CODE <= TERM_IN
                        AND C.SORLCUR_PRIORITY_NO = A.SORLCUR_PRIORITY_NO
                        AND C.SORLCUR_LMOD_CODE = 'LEARNER'
                        AND C.SORLCUR_CACT_CODE = 'ACTIVE'));
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          HG_MAJOR_VALUE := NULL;
       WHEN OTHERS THEN
          raise_application_error(-20001,'An error was encountered -
'||SQLCODE||
                                         ' -ERROR- '||SQLERRM||' -PIDM-
'||PIDM_IN);
    END;

    RETURN (NVL(HG_MAJOR_VALUE,' '));
END;

gistfile2.txt

DECLARE
  lv_curriculum_rec       sb_curriculum.curriculum_rec;
  lv_curriculum_cur       sb_curriculum.curriculum_ref;
  lv_fieldofstudy_rec     sb_fieldofstudy.fieldofstudy_rec;
  lv_fieldofstudy_cur     sb_fieldofstudy.fieldofstudy_ref;

....

BEGIN

....

-- retrieve the current curriculum in priority order

lv_curriculum_cur := sb_curriculum.f_query_current

        (p_pidm         => gpidm

        ,p_lmod_code    => sb_curriculum_str.f_learner

        ,p_keyseqno     => '99'

        ,p_eff_term     => gterm

        ,p_active_ind   => 'Y'

        );

LOOP

   FETCH lv_curriculum_cur INTO lv_curriculum_rec;

   EXIT WHEN lv_curriculum_cur%NOTFOUND;

...

-- loop thru the current fields of study in priority order

   lv_fieldofstudy_cur := sb_fieldofstudy.f_query_current

                (p_pidm         => gpidm

                ,p_lcur_seqno   => lv_curriculum_rec.r_seqno

                ,p_active_ind   => 'Y'

                );

   LOOP

     FETCH lv_fieldofstudy_cur INTO lv_fieldofstudy_rec;

     EXIT WHEN lv_fieldofstudy_cur%NOTFOUND;

...

   END LOOP;

   CLOSE lv_fieldofstudy_cur;

END LOOP;

CLOSE lv_curriculum_cur;