/* Formatted on 2010/07/01 14:37 (Formatter Plus v4.8.8) */
-- OBJECT TYPE:  PL/SQL
-- OBJECT NAME:  SZPGRGD.SQL
-- AUTHOR:       Davin Bostic    
-- DATE WRITTEN: 06/28/2010
-- INPUT:         
-- 
-- DESCRIPTION:  Students who are graduating (PN Status) with grades and sorted by the Date/Time stamp. 
--
--

--SET SCAN OFF;
SET serveroutput ON;
SET echo OFF;
SET verify OFF;
SET feedback OFF;
SET tab OFF;

DECLARE
   --Get PN cursor
   CURSOR pn_c1 (v_term VARCHAR2)
   IS
      SELECT DISTINCT shrdgmr_pidm, shrdgmr_levl_code, shrdgmr_term_code_grad,  shrdgmr_degs_code
          FROM shrdgmr, sfrstcr
         WHERE shrdgmr_term_code_grad = v_term
           AND shrdgmr_degs_code in ('AP','PN','AW')
           --sfrstcr
           AND shrdgmr_pidm = sfrstcr_pidm
           AND shrdgmr_term_code_grad = sfrstcr_term_code
           AND sfrstcr_rsts_code LIKE 'R%'
      ORDER BY shrdgmr_pidm;

   CURSOR course_c1 (v_pidm VARCHAR2, v_term VARCHAR2)
   IS
      SELECT *
        FROM shrtckn
       WHERE shrtckn_pidm = v_pidm AND shrtckn_term_code = v_term;
       
       
     --Sort Grades
   CURSOR all_grade_sort_c1 (v_pidm NUMBER, v_term VARCHAR2)
   IS
      SELECT *
          FROM ((SELECT 'shrtckn' AS v_source, shrtckn_crn, shrtckn_subj_code,
                        shrtckn_crse_numb, shrtckg_grde_code_final, shrtckg_gmod_code,
                        shrtckg_gchg_code, shrtckg_final_grde_chg_date, NULL rolled, shrtckn_ptrm_code
                   FROM shrtckg a, shrtckn x
                  WHERE shrtckg_gmod_code NOT in ('N','T')
                    AND shrtckg_grde_code_final != 'W'
                    AND x.shrtckn_pidm = a.shrtckg_pidm
                    AND x.shrtckn_seq_no = a.shrtckg_tckn_seq_no
                    AND x.shrtckn_term_code = a.shrtckg_term_code
                    AND a.shrtckg_pidm = v_pidm
                    AND a.shrtckg_term_code = v_term
                    AND a.shrtckg_seq_no =
                           (SELECT MAX (b.shrtckg_seq_no)
                              FROM shrtckg b
                             WHERE b.shrtckg_pidm = x.shrtckn_pidm
                               AND b.shrtckg_term_code = x.shrtckn_term_code
                               AND b.shrtckg_tckn_seq_no = x.shrtckn_seq_no))
                UNION
                (SELECT 'sfrstcr' AS v_source, sfrstcr_crn, NULL, NULL, sfrstcr_grde_code,
                        sfrstcr_gmod_code, NULL, NULL, DECODE(sfrstcr_grde_date,NULL,'N','Y'), sfrstcr_ptrm_code
                   FROM sfrstcr
                   WHERE sfrstcr_gmod_code NOT in ('N','T')
                    AND sfrstcr_pidm = v_pidm
                    AND sfrstcr_term_code = v_term
                  AND sfrstcr_rsts_code LIKE 'R%' -- check for registered for grade (could be audit only want registered)
                    AND NOT EXISTS (
                           SELECT 'x'
                             FROM shrtckn y
                            WHERE y.shrtckn_pidm = v_pidm
                              AND y.shrtckn_term_code = v_term
                              AND y.shrtckn_crn = sfrstcr_crn)))
                               
      ORDER BY shrtckg_final_grde_chg_date;


--Get course from ssbsect
   CURSOR ssbsect_c1 (v_crn VARCHAR2, v_term VARCHAR2)
   IS
      SELECT ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb
         FROM ssbsect
         WHERE ssbsect_crn = v_crn AND ssbsect_term_code = v_term;


    CURSOR sovlcur_c1(v_pidm NUMBER, v_term VARCHAR2, v_levl varchar2)
    IS
    SELECT sovlcur_coll_code, sovlcur_seqno
FROM sovlcur a
WHERE sovlcur_term_code = (select max(sovlcur_term_code)
                                            FROM sovlcur b
                                            WHERE a.sovlcur_pidm = b.sovlcur_pidm
                                            AND a.sovlcur_priority_no = b.sovlcur_priority_no
                                            AND a.sovlcur_levl_code = b.sovlcur_levl_code 
                                            AND a.sovlcur_lmod_code = b.sovlcur_lmod_code
                                            AND sovlcur_term_code <= v_term)
AND sovlcur_pidm = v_pidm  
AND sovlcur_lmod_code ='OUTCOME'   
AND sovlcur_levl_code = v_levl            
AND sovlcur_active_ind = 'Y'
AND sovlcur_current_ind = 'Y';


CURSOR sovlfos_c1(v_pidm NUMBER, v_cur_seqno NUMBER)
    IS         
SELECT sovlfos_majr_code FROM sovlfos
WHERE sovlfos_pidm = v_pidm
AND sovlfos_lcur_seqno = v_cur_seqno
AND sovlfos_lfst_code = 'MAJOR'
AND sovlfos_current_ind = 'Y'
AND sovlfos_active_ind = 'Y';




   --variable declaration
   v_term        VARCHAR2 (9);
   v_pidm        spriden.spriden_pidm%TYPE; 
   v_ssbsect     ssbsect%ROWTYPE;
   v_subj_code   VARCHAR2 (9);
   v_seq_numb    VARCHAR2 (9);
   v_cur_seqno   VARCHAR2 (9);
   v_crn         VARCHAR2 (9);
   v_crse_numb   VARCHAR2 (9);        
   v_grade       VARCHAR2 (9);
   v_course      VARCHAR2 (9);
   v_pn_status   VARCHAR2 (9);
   v_coll        VARCHAR2 (10);
   v_majr        VARCHAR2 (10);
   v_line_out    VARCHAR (2048);                        --output line for dbms
   v_count       NUMBER                      := 0;
   v_commit      BOOLEAN                     := TRUE;
   f1            VARCHAR2 (10)               := '"';
   f2            VARCHAR2 (10)               := '","';
   
   
 
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
   DBMS_OUTPUT.put_line (   'BEGINNING OF SZPGRGD'
                         || ': '
                         || TO_CHAR (SYSDATE, 'dd-MON-yyyy - hh:mi:ss')
                        );
   DBMS_OUTPUT.put_line ('-');
   --Get session parm from Banner

   v_term := gzpadmn.F_Get_Parm('&1','&2','&4','01');

   --******************************************************************
--write parm values to log file
   DBMS_OUTPUT.put_line ('Parm01: ' || v_term);
   DBMS_OUTPUT.put_line ('-');
   --write heading line to file
   v_line_out :=
         f1
      || 'TERM'
      || f2
      ||  'SUB-TERM'
      || f2
      || 'ACADEMIC OUTCOME'
      || f2
      || 'ID'
      || f2
      || 'FIRST NAME'
      || f2
      || 'MIDDLE INITIAL'
      || f2
      || 'LAST NAME'
      || f2
      || 'LEVEL'
      || f2
      || 'COLLEGE'
      || f2
      || 'MAJOR'
      || f2
      || 'COURSE ID'
      || f2
      || 'SECTION'
      || f2
      || 'CRN'
      || f2
      || 'ROLLED'
      || f2
      || 'FINAL GRADE'
      || f2
      || 'GRADE TYPE'
      || f2
      || 'DATE TIME'
      || f2
      || 'GRADE CHANGE CODE'
--      || f2
--      || 'STATUS'
      || f2
      || f1;
   -- Write heading line to file
   gzpadmn.p_spool_lis ('&1', '&2', '', 'H', v_line_out, v_commit);
   DBMS_OUTPUT.put_line (v_line_out);

   --Get people who are PN
   FOR pn_row IN pn_c1 (v_term)
  
   LOOP
   
      
    -- Get college
       for sovlcur_rec in sovlcur_c1(pn_row.shrdgmr_pidm, v_term, pn_row.shrdgmr_levl_code)

       loop
       
    --DBMS_OUTPUT.put_line ('college'||v_coll||'seq_num'||v_cur_seqno);
    
       -- Get major
      
     for sovlfos_rec in sovlfos_c1(pn_row.shrdgmr_pidm, sovlcur_rec.sovlcur_seqno)

       loop
       
       v_grade := 'N';
    
--            --Get grades and sort
      FOR v_rec in all_grade_sort_c1 (pn_row.shrdgmr_pidm,
                                   pn_row.shrdgmr_term_code_grad)
                         
      LOOP 
--            --Get course info
       v_grade := 'Y'; 
       v_seq_numb := NULL;
       v_crse_numb := NULL;
       v_subj_code := NULL; 
       OPEN ssbsect_c1 (v_rec.shrtckn_crn, v_term);
       FETCH ssbsect_c1 INTO v_crse_numb, v_subj_code, v_seq_numb;
       CLOSE ssbsect_c1;
       
      IF v_rec.v_source = 'sfrstcr'
      THEN
       
       
      
--       v_crse_numb := v_ssbsect.ssbsect_subj_code;
--       test2 := v_ssbsect.ssbsect_crse_numb;
       
       --Print out data line
         v_line_out :=
               f1
            || v_term
            || f2
            || v_rec.shrtckn_ptrm_code --sub term
            || f2     
            || pn_row.shrdgmr_degs_code
            || f2
            || szg8003.f_get_id (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_first (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_middle (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_last (pn_row.shrdgmr_pidm)
            || f2
            || pn_row.shrdgmr_levl_code
            || f2
            || sovlcur_rec.sovlcur_coll_code                        
            || f2
            || sovlfos_rec.sovlfos_majr_code
            || f2
            || v_crse_numb||v_subj_code 
            || f2
            || v_seq_numb
            || f2
            || v_rec.shrtckn_crn
            || f2
            || v_rec.rolled
            || f2
            || v_rec.shrtckg_grde_code_final         
            || f2
            || v_rec.shrtckg_gmod_code
            || f2
            || TO_CHAR (v_rec.shrtckg_final_grde_chg_date, 'mm/dd/yyyy hh24:mi:ss')
            || f2
            || v_rec.shrtckg_gchg_code
            || f2
            || f1;
            
                         
            ELSE 
            
             --Print out data line
         v_line_out :=
               f1
            || v_term
            || f2   
            || v_rec.shrtckn_ptrm_code --sub term
            || f2
            || pn_row.shrdgmr_degs_code
            || f2   
            || szg8003.f_get_id (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_first (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_middle (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_last (pn_row.shrdgmr_pidm)
            || f2
            || pn_row.shrdgmr_levl_code
            || f2
            || sovlcur_rec.sovlcur_coll_code                       
            || f2
            || sovlfos_rec.sovlfos_majr_code
            || f2
            || v_rec.shrtckn_subj_code||v_rec.shrtckn_crse_numb
            || f2
            || v_seq_numb
            || f2
            || v_rec.shrtckn_crn
            || f2
            || v_rec.rolled
            || f2
            || v_rec.shrtckg_grde_code_final
            || f2
            || v_rec.shrtckg_gmod_code
            || f2
            || TO_CHAR (v_rec.shrtckg_final_grde_chg_date, 'mm/dd/yyyy hh24:mi:ss')
            || f2
            || v_rec.shrtckg_gchg_code
            || f2
            || f1;
            
       END IF;

         gzpadmn.p_spool_lis ('&1', '&2', '', 'I', v_line_out, v_commit);
         --DBMS_OUTPUT.put_line (v_line_out);
         v_count := v_count + 1;
         
         END LOOP;
         
                  IF v_grade = 'N'
         
         THEN
                  v_line_out :=
               f1
            || v_term
            || f2 
            || '' --sub term
            || f2     
            || pn_row.shrdgmr_degs_code
            || f2  
            || szg8003.f_get_id (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_first (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_middle (pn_row.shrdgmr_pidm)
            || f2
            || szg8003.f_get_last (pn_row.shrdgmr_pidm)
            || f2
            || pn_row.shrdgmr_levl_code
            || f2
            || sovlcur_rec.sovlcur_coll_code                       
            || f2
            || sovlfos_rec.sovlfos_majr_code
            || f2
            || 'No grade data'
            || f2
            || 'No grade data'
            || f2
            || 'No grade data'
            || f2
            || 'No grade data'
            || f2
            || 'No grade data'
            || f2
            || 'No grade data'
            || f2
            || f1;
            
       gzpadmn.p_spool_lis ('&1', '&2', '', 'I', v_line_out, v_commit);
         
         end if;
         
         
   END LOOP;
  END LOOP;
  
   END LOOP;

   DBMS_OUTPUT.put_line ('Records processed: ' || v_count);
   DBMS_OUTPUT.put_line ('-');
   DBMS_OUTPUT.put_line ('SZPGRGD complete.');
   DBMS_OUTPUT.put_line ('-');
   DBMS_OUTPUT.put_line (   'END OF SZPGRGD '
                         || TO_CHAR (SYSDATE, 'dd-MON-yyyy - hh:mi:ss')
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      --verrmsg := SQLERRM;
      --verrcode := SQLCODE;
      DBMS_OUTPUT.put_line ('Error found.' || f1 || SQLERRM);
END;
/