/* Formatted on 2007/03/21 13:12 (Formatter Plus v4.8.8) */
-- Object Type:  PL/SQL
-- Object Name:  szp3054.sql
-- Author     :  David Buchannon, Jr.
-- Description:  Set Time Tickets for New Students (excluding CWE)
--
-- Create     :  2007/02/20   DB: Created
-- Parms:      parm 01, the current effective term to use for pulling level,college, gpa....
--             parm 02, the term that the registration is for
--             parm 03, run mode, A= audit, U= update
-- ************************************************************************************************************
-- Edited by Rodney Clark on 9/19/2007 
-- Added the function to pull all U2 and U3 srudents
-- Edited to fulfill the spring 2008 registration
-- Edited by David Buchannon on 10/4/2007
-- added logic to exclude graduated students without a admit greated than the term of gradutaion
--                 and not exists --eclude anyone with a graduation term greated than the current sggbstdn admit term
--                 (select 'x' from shrdgmr where 
--                 shrdgmr_pidm = a.sgbstdn_pidm
--                 and shrdgmr_term_code_completed in ('200730','200810')
--                 and shrdgmr_grst_code = 'AW'
--                 and shrdgmr_term_code_completed >= a.sgbstdn_term_code_admit
--                 )
-- ************************************************************************************************************
-- Edited by David Buchannon 01/14/2008
-- Optimized cursor to eliminate full table scans against sgbstdn and shrlgpa
-- Changed the first parm to be the current term
-- the sgbstdn term is now equal to the registration term, as its been since 200810 registration
-- calculte the one and two prior terms based on the current term.
-- ************************************************************************************************************
-- Edited by David Buchannon 02/13/2008
-- Changed the selection for P1 - MSC1 from looking in the student attribute table to (sgrsatt) to the student
-- activity table (sgrsact)
-- Changed the select for P2 - MSC2, BAND, ROTC in the same manner as the P1 change
-- ************************************************************************************************************
-- cloned from szp3053 David Buchannon 2/22/2008
-- ************************************************************************************************************
-- Edited by Naomi Rigby 9/01/2009
-- Re-written to automate registration by using values from an external table.
-- Description:  Assigns tickets to everyone new exception new freshment - ie. re-admits, new students to the
-- university, new grad students
-- Usually ran in the spring for summer and fall registration
-- New - new freshmen are assigned a ticket manually for spring term - In the fall - for new new freshmen - a ticket is assigned
-- to them via a program Robert wrote for Camp War Eagle
-- ************************************************************************************************************
-- Edited by Monica Chen 08/24/2010 
-- Added the function to include students with Veteran codes in P1 priority 
-- Changed the sport codes selection froom IN to NOT IN in P2 priority and added 'XX' to P2 priority 
-- ************************************************************************************************************
-- Edited by Sam Potts 11 Aug 2011
-- Added HONI attribute to the (attribute) selection per ticket 149 
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited by Albert Homburger 1/11/2012
-- moved Veterans to a new group PRIORV and moved HON% attribute to a new group PRIORH
-- as requested by Dan Hill ticket 198
-- ************************************************************************************************************
-- ************************************************************************************************************
-- ************************************************************************************************************
-- Edited by Rodney Clark
-- Added The filter for student with type W
-- For ticket 2085
-- ************************************************************************************************************
-- ************************************************************************************************************
-- Edited by Irida Medina
-- sgrsatt_atts_code in ('VA01' , 'VA02') Added for veterans
-- REQ0018939/TASK0025780
-----------------
--SET scan off;
SET verify off;
SET feedback off;
SET echo off;
SET tab off;
SET serveroutput ON;

 

DECLARE


v_first_gradpro varchar2(1);
v_first_prior1 varchar2(1);
v_first_specdeg varchar2(1);
v_first_prior2 varchar2(1);
v_first_spec_ug varchar2(1);


-- ***CREATE CURSOR TO READ SZC3054
CURSOR c_szc3054
IS
  SELECT * 
    FROM szc3054;
    
-----------------------------------------------------------------------
   CURSOR c_stvterm (v_current_term varchar2)
   IS 
      SELECT stvterm_code
        FROM stvterm
        WHERE stvterm_code < v_current_term
        ORDER BY stvterm_code desc;

-----------------------------------------------------------------------
     CURSOR c_sfbrgrp (v_pidm varchar2, v_term_code varchar2)
     IS
     SELECT sfbrgrp_pidm 
     FROM  sfbrgrp
     WHERE sfbrgrp_pidm = v_pidm
     AND sfbrgrp_term_code = v_term_code;

-----------------------------------------------------------------------
   CURSOR c_sfbwctl (v_reg_term VARCHAR2, v_grp VARCHAR2)
   IS
      SELECT *
        FROM sfbwctl
       WHERE sfbwctl_term_code = v_reg_term AND sfbwctl_rgrp_code = v_grp;

-----------------------------------------------------------------------
   CURSOR c_voed_cnt (v_sgastdn_term VARCHAR2)
   IS
      SELECT COUNT (DISTINCT sgbstdn_pidm) voed_cnt
        FROM sgbstdn A
       WHERE A.sgbstdn_term_code_eff =
                (SELECT MAX (sgbstdn_term_code_eff)
                   FROM sgbstdn b
                  WHERE b.sgbstdn_term_code_eff <= v_sgastdn_term
                    AND b.sgbstdn_pidm = A.sgbstdn_pidm)
         AND A.sgbstdn_voed_code = 'Y';

-----------------------------------------------------------------------
   CURSOR c_all_levels_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (   
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
           FROM sgbstdn A
            WHERE A.sgbstdn_term_code_eff =
                              (SELECT MAX (b.sgbstdn_term_code_eff)
                                 FROM sgbstdn b
                                WHERE b.sgbstdn_pidm = A.sgbstdn_pidm                             
                                    AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
            AND A.sgbstdn_levl_code IN ('UG')
            AND A.sgbstdn_styp_code <> 'N'
            AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
            AND A.sgbstdn_term_code_admit = v_reg_term
            AND NOT EXISTS (
                              SELECT 'x'
                                FROM sfbrgrp
                               WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                                 AND sfbrgrp_term_code = v_reg_term)
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

-----------------------------------------------------------------------
   CURSOR c_ug_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
            FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('UG')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

-----------------------------------------------------------------------
   CURSOR c_gr_pr_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
            FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('GR','PR')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                   AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

----------------------------------------------------------------------
   --Added by Rodney Clark on 9/17/2007 for setting U2 and U3 students
   CURSOR c_u2_u3_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
            FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('U2','U3')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

-----------------------------------------------------------------------                                
  CURSOR c_all_levels_p1_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
                 FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('UG', 'U2', 'U3')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                  AND ((EXISTS (
                             SELECT 'x'
                               FROM sgrsatt w
                              WHERE w.sgrsatt_pidm = A.sgbstdn_pidm
                                AND w.sgrsatt_atts_code = 'COOP'
                                -- honors moved to PRIORH 
                                -- IN ( 'HONI', 'HONR', 'COOP' )
                                AND w.sgrsatt_term_code_eff =
                                       (SELECT   MAX (sgrsatt_term_code_eff)
                                            FROM sgrsatt x
                                           WHERE x.sgrsatt_pidm = w.sgrsatt_pidm
                                             --and w.sgrsatt_atts_code = x.sgrsatt_atts_code
                                             AND x.sgrsatt_term_code_eff <=  v_reg_term
                                        GROUP BY x.sgrsatt_pidm))
                        )
                       OR (EXISTS  --test for activities
                                 (
                             SELECT 'x'
                               FROM sgrsact
                              WHERE sgrsact_pidm = A.sgbstdn_pidm
                                AND sgrsact_actc_code = 'MSC1'
                                AND sgrsact_term_code= v_reg_term)
                          )
                       OR (EXISTS                    --test for sport activity
                                 (
                              SELECT 'x'
                                FROM sgrsprt
                               WHERE sgrsprt_actc_code IS NOT NULL
                                 AND sgrsprt_actc_code NOT IN ('GA', 'XA', 'XX')
                                 AND sgrsprt_term_code = v_reg_term
                                 AND sgrsprt_pidm = A.sgbstdn_pidm
                                 AND sgrsprt_spst_code = 'AC')
                            )
                          
                       OR (EXISTS
                                (
                             SELECT 'x'
                               FROM sgrdisa WHERE sgrdisa_spsr_code = 'P1'
                               AND (sgrdisa_term_code = v_reg_term)
                               AND sgrdisa_pidm = A.sgbstdn_pidm) 
                           )
                      )
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
                              --check for enrollment after 1 year
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

----------------------------------------------------------------------        
-- PRIORV  - Veterans Only group by hours - added ATH tic 189 --
CURSOR c_all_levels_vet_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
                 FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('UG', 'U2', 'U3')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                 -- must be a veteran --
                  AND (EXISTS       
                            (
                         SELECT 'x'
                           FROM sgrvetn WHERE sgrvetn_vetc_code in ('0', '1', '3', '5', '6', '7', 'A', 'B', 'C', 'D')
                           AND sgrvetn_pidm = A.sgbstdn_pidm) 
				  OR (EXISTS --IMM Added TASK0025780
                      (
                      SELECT 'x'
                      FROM sgrsatt WHERE sgrsatt_atts_code in ('VA01' , 'VA02')
                      AND sgrsatt_pidm = A.sgbstdn_pidm)
                      ))	   
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
                              --check for enrollment after 1 year
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;

 -- PRIORH - HONI/HONR  Only group by hours - added ATH tic 189 --
   CURSOR c_all_levels_hon_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
                 FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('UG', 'U2', 'U3')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                  -- must be honors
                  AND EXISTS (
                             SELECT 'x'
                               FROM sgrsatt w
                              WHERE w.sgrsatt_pidm = A.sgbstdn_pidm
                                AND w.sgrsatt_atts_code IN
                                                     ( 'HONI', 'HONR' )
                                AND w.sgrsatt_term_code_eff =
                                       (SELECT   MAX (sgrsatt_term_code_eff)
                                            FROM sgrsatt x
                                           WHERE x.sgrsatt_pidm = w.sgrsatt_pidm
                                             --and w.sgrsatt_atts_code = x.sgrsatt_atts_code
                                             AND x.sgrsatt_term_code_eff <=
                                                                    v_reg_term
                                        GROUP BY x.sgrsatt_pidm))
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
                              --check for enrollment after 1 year
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;


   CURSOR c_all_levels_p2_by_hours (
      v_ge_hrs         NUMBER,
      v_lthan_hrs      NUMBER,
      v_sgastdn_term   VARCHAR2,
      v_reg_term       VARCHAR2
   )
   IS
     SELECT * FROM 
     (
           SELECT DISTINCT A.sgbstdn_pidm, 
                  (NVL (
                         (SELECT shrlgpa_hours_earned 
                         FROM shrlgpa 
                         WHERE shrlgpa_gpa_type_ind = 'O' 
                         AND shrlgpa_pidm = A.sgbstdn_pidm
                         AND shrlgpa_levl_code = A.sgbstdn_levl_code)
                        , 0))
                   AS sum_hours
                 FROM sgbstdn A
                WHERE A.sgbstdn_term_code_eff =
                         (SELECT MAX (b.sgbstdn_term_code_eff)
                            FROM sgbstdn b
                           WHERE b.sgbstdn_pidm = A.sgbstdn_pidm
                             AND b.sgbstdn_term_code_eff <= v_sgastdn_term)
                  AND A.sgbstdn_levl_code IN ('UG', 'U2', 'U3')
                  AND A.sgbstdn_styp_code <> 'N'
                  AND A.sgbstdn_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
                  AND A.sgbstdn_term_code_admit = v_reg_term
                  AND ((EXISTS (   
                         -- ADDED BY NAOMI FEB 3 2010
                         -- tests for attributes for ROTC - where the first 3 letters are ROA, RON or ROF
                         SELECT 'x'
                           FROM sgrsatt w
                          WHERE w.sgrsatt_pidm = A.sgbstdn_pidm
                            AND (w.sgrsatt_atts_code LIKE 'ROA%'
                                OR w.sgrsatt_atts_code LIKE 'RON%'
                                OR w.sgrsatt_atts_code LIKE 'ROF%')
                            AND w.sgrsatt_term_code_eff =
                                   (SELECT   MAX (sgrsatt_term_code_eff)
                                        FROM sgrsatt x
                                       WHERE x.sgrsatt_pidm = w.sgrsatt_pidm
                                         --and w.sgrsatt_atts_code = x.sgrsatt_atts_code
                                         AND x.sgrsatt_term_code_eff <=
                                                                v_reg_term
                                    GROUP BY x.sgrsatt_pidm))
                       )
                       OR (EXISTS 
                                 (
                             SELECT 'x'
                               FROM sgrsact
                              WHERE 
                                sgrsact_pidm = A.sgbstdn_pidm
                                AND sgrsact_actc_code IN ('BAND', 'MSC2')
                                AND sgrsact_term_code = v_reg_term)
                          )
                       OR (EXISTS                    --test for sport activity
                                 (
                              SELECT 'x'
                                FROM sgrsprt
                               WHERE sgrsprt_actc_code IS NOT NULL
                                 AND sgrsprt_actc_code NOT IN ('OC', 'GA', 'XA', 'XX')
                                 AND sgrsprt_term_code = v_reg_term
                                 AND sgrsprt_pidm = A.sgbstdn_pidm
                                 AND sgrsprt_spst_code = 'AC')
                          )
                       OR (EXISTS
                                (
                             SELECT 'x'
                               FROM sgrdisa WHERE sgrdisa_spsr_code = 'P2'
                               AND (sgrdisa_term_code = v_reg_term)
                               AND sgrdisa_pidm = A.sgbstdn_pidm)
                         )
                      )
                  AND NOT EXISTS (
                         SELECT 'x'
                           FROM sfbrgrp
                          WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                            AND sfbrgrp_term_code = v_reg_term)
     )
     WHERE sum_hours >= v_ge_hrs
     AND   sum_hours < v_lthan_hrs;
                                

   v_sgbstdn_eff_term   VARCHAR2 (6);
   v_reg_term           VARCHAR2 (6);
   v_run_mode           VARCHAR2 (1);
   v_cnt                NUMBER                      := 0;
   v_voed_cnt           NUMBER                      := 0;
   v_assgn_cnt          NUMBER                      := 0;
   v_sumcnt             NUMBER                      := 0;
   v_disp_cnt           BOOLEAN                     := TRUE;
   v_glb_user           sfbrgrp.sfbrgrp_user%TYPE;
   v_create_fall_ticket varchar2(1) := 'N';
   v_fall_ticket_term_code varchar2(6);
   rec_sfbrgrp c_sfbrgrp%ROWTYPE;

   
FUNCTION f_verify_grp (v_reg_term VARCHAR2, v_grp VARCHAR2)
      RETURN BOOLEAN
   IS
      rec_sfbwctl   c_sfbwctl%ROWTYPE;
   BEGIN
      OPEN c_sfbwctl (v_reg_term, v_grp);

      FETCH c_sfbwctl
       INTO rec_sfbwctl;

      IF c_sfbwctl%FOUND
      THEN
         CLOSE c_sfbwctl;

         RETURN TRUE;
      ELSE
         CLOSE c_sfbwctl;
         RETURN FALSE;
--         RETURN TRUE;
      END IF;
   END f_verify_grp;

   PROCEDURE p_insert_ticket (
      v_reg_term   VARCHAR2,
      v_pidm       NUMBER,
      v_grp        VARCHAR2
   )
   IS
      A   VARCHAR2 (1);
   BEGIN
      A := '1';

      INSERT INTO sfbrgrp
                  (sfbrgrp_term_code, sfbrgrp_pidm, sfbrgrp_rgrp_code,
                   sfbrgrp_user, sfbrgrp_activity_date
                  )
           VALUES (v_reg_term, v_pidm, v_grp,
                   v_glb_user, SYSDATE
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SUBSTR (   'ERR- in P_INSERT Pidm:'
                                       || v_pidm
                                       || ' Group:'
                                       || v_grp
                                       || ' '
                                       || SQLERRM,
                                       1,
                                       200
                                      )
                              );
   END p_insert_ticket;

   PROCEDURE p_all_levels_p1_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_all_levels_p1_by_hours (v_ge_hrs,
                                                v_lthan_hrs,
                                                v_sgbstdn_eff_term,
                                                v_reg_term
                                               )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_all_levels_p1_by_hours;
   
      PROCEDURE p_all_levels_vet_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_all_levels_vet_by_hours (v_ge_hrs,
                                                v_lthan_hrs,
                                                v_sgbstdn_eff_term,
                                                v_reg_term
                                               )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_all_levels_vet_by_hours;
   
   
      PROCEDURE p_all_levels_hon_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_all_levels_hon_by_hours (v_ge_hrs,
                                                v_lthan_hrs,
                                                v_sgbstdn_eff_term,
                                                v_reg_term
                                               )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_all_levels_hon_by_hours;
   
   PROCEDURE p_u2_u3_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_u2_u3_by_hours (v_ge_hrs,
                                                v_lthan_hrs,
                                                v_sgbstdn_eff_term,
                                                v_reg_term
                                               )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_u2_u3_by_hours;

   PROCEDURE p_all_levels_p2_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_all_levels_p2_by_hours (v_ge_hrs,
                                                v_lthan_hrs,
                                                v_sgbstdn_eff_term,
                                                v_reg_term
                                               )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_all_levels_p2_by_hours;

   PROCEDURE p_all_levels_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_all_levels_by_hours (v_ge_hrs,
                                             v_lthan_hrs,
                                             v_sgbstdn_eff_term,
                                             v_reg_term
                                            )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_all_levels_by_hours;

   PROCEDURE p_ug_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_ug_by_hours (v_ge_hrs,
                                     v_lthan_hrs,
                                     v_sgbstdn_eff_term,
                                     v_reg_term
                                    )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_ug_by_hours;

   PROCEDURE p_gr_pr_by_hours (
      v_ge_hrs               NUMBER,
      v_lthan_hrs            NUMBER,
      v_grp                  VARCHAR2,
      v_sgastdn_term         VARCHAR2,
      v_reg_term             VARCHAR2,
      v_cnt            OUT   NUMBER,
      v_disp_cnt             BOOLEAN
   )
   IS
   BEGIN
      v_cnt := 0;

      IF f_verify_grp (v_reg_term, v_grp) = TRUE
      THEN
         FOR p_rec IN c_gr_pr_by_hours (v_ge_hrs,
                                        v_lthan_hrs,
                                        v_sgbstdn_eff_term,
                                        v_reg_term
                                       )
         LOOP
            v_cnt := v_cnt + 1;
            p_insert_ticket (v_reg_term, p_rec.sgbstdn_pidm, v_grp);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sfbrgrp(p_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sfbrgrp INTO rec_sfbrgrp;
               IF c_sfbrgrp%NOTFOUND THEN
                    p_insert_ticket (v_fall_ticket_term_code, p_rec.sgbstdn_pidm, v_grp);
               END IF;
               CLOSE c_sfbrgrp;    
          END IF;
         END LOOP;

         IF v_disp_cnt = TRUE
         THEN
            DBMS_OUTPUT.put_line (   'Group: '
                                  || v_grp
                                  || ' Greater than or equal to '
                                  || v_ge_hrs
                                  || ' hours, less than '
                                  || v_lthan_hrs
                                  || ' hours, Count '
                                  || v_cnt
                                 );
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (   'Invalid Group:'
                               || v_grp
                               || ' Term:'
                               || v_reg_term
                              );
      END IF;
   END p_gr_pr_by_hours;
   
BEGIN
   DBMS_OUTPUT.ENABLE (null); --IMM TASK0025780
   DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZP3054'||': '||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
 
   v_disp_cnt := TRUE;
--Get parms from Banner
  v_reg_term := gzpadmn.f_get_parm ('&1', '&2', '&4', '01');
  v_create_fall_ticket := gzpadmn.f_get_parm ('&1', '&2', '&4', '02');
  v_fall_ticket_term_code := gzpadmn.f_get_parm ('&1', '&2', '&4', '03');
  v_run_mode := UPPER(gzpadmn.f_get_parm ('&1', '&2', '&4', '04'));

  v_sgbstdn_eff_term := v_reg_term;

  v_glb_user := '&3';
  
  --*******************
  --  added theses variables for printing purposes for headers
  v_first_gradpro := 'y';
  v_first_prior1 := 'y';
  v_first_specdeg := 'y';
  v_first_prior2 := 'y';
  v_first_spec_ug := 'y';
  --********************
  
   
--debugging variables
--v_sgbstdn_eff_term := '200820';
--v_reg_term := '200820';
--v_run_mode := 'A';
--v_glb_user := 'BUCHADL';


   DBMS_OUTPUT.put_line ('User: ' || v_glb_user);
   DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
   DBMS_OUTPUT.put_line ('Parm - Registration Term: ' || v_reg_term);
   DBMS_OUTPUT.put_line ('Parm - Create Fall Time Ticket: ' || v_create_fall_ticket);
   DBMS_OUTPUT.put_line ('Parm - Fall Term for Summer Students: ' || v_fall_ticket_term_code);
   DBMS_OUTPUT.put_line ('Parm - Run Mode: ' || v_run_mode);
   DBMS_OUTPUT.put_line ('Term for SGASTDN Records: '  || v_sgbstdn_eff_term);
   DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
   




--PARM LIST
--Gretater Than Equal to Hours
--Less Than Hours
--Sgastdn Term
--Registration Term
--Count
--Display Count


    --^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    -- *** ADD FOR LOOP FOR TABLE CURSOR (szc3054)
    -- *** START FOR LOOP
    FOR c_szc3054_row IN c_szc3054  LOOP

        -- *** ACCORDING TO THE SZC3054_TYPE - PULL THE CORRECT PROCEDURE
        -- *** CREATE A CASE STATEMENT FOR WHAT TO DO FOR EACH TYPE
        -- *** START CASE

        BEGIN
            
                        CASE c_szc3054_row.SZC3054_TYPE
                                -- GRADUATE/PROFESSIONAL GROUP
                            WHEN 'GRADPRO'
                            THEN                       
                                IF v_first_gradpro = 'y'THEN
                                    --Graduate/Professional GROUP
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('Graduate Professional');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_gradpro:='n';
                                END IF;
                                                    
                                                    
                           p_gr_pr_by_hours(c_szc3054_row.szc3054_low_hrs,  --0,
                                            c_szc3054_row.szc3054_high_hrs, --999999,
                                            c_szc3054_row.szc3054_process,  -- 'GP1',
                                            v_sgbstdn_eff_term,
                                            v_reg_term,
                                            v_cnt,
                                            v_disp_cnt
                                            );
                            v_sumcnt := v_sumcnt + v_cnt;                                 
                                        
                            -- PRIORITY 1
                            WHEN 'PRIOR1'
                            THEN                        
                                IF v_first_prior1 = 'y'THEN
                                --P1 group
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('Priority Group 1');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_prior1:='n';
                                END IF;
                                                   
                               p_all_levels_p1_by_hours (c_szc3054_row.szc3054_low_hrs,     --145,
                                                         c_szc3054_row.szc3054_high_hrs,    --999999,
                                                         c_szc3054_row.szc3054_process,     --'PGA1',
                                                         v_sgbstdn_eff_term,
                                                         v_reg_term,
                                                         v_cnt,
                                                         v_disp_cnt
                                                        );
                               v_sumcnt := v_sumcnt + v_cnt;
                            -- veterans
                            WHEN 'PRIORV'
                            THEN                        
                                IF v_first_prior1 = 'y'THEN
                                --P1 group
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('Veterans');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_prior1:='n';
                                END IF;
                                                   
                               p_all_levels_vet_by_hours (c_szc3054_row.szc3054_low_hrs,
                                                         c_szc3054_row.szc3054_high_hrs,
                                                         c_szc3054_row.szc3054_process,
                                                         v_sgbstdn_eff_term,
                                                         v_reg_term,
                                                         v_cnt,
                                                         v_disp_cnt
                                                        );
                               v_sumcnt := v_sumcnt + v_cnt;

                            -- honors 
                            WHEN 'PRIORH'
                            THEN                        
                                IF v_first_prior1 = 'y'THEN
                                --P1 group
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('HONI/HONR ');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_prior1:='n';
                                END IF;
                                                   
                               p_all_levels_hon_by_hours (c_szc3054_row.szc3054_low_hrs,     --145,
                                                         c_szc3054_row.szc3054_high_hrs,    --999999,
                                                         c_szc3054_row.szc3054_process,     --'PGA1',
                                                         v_sgbstdn_eff_term,
                                                         v_reg_term,
                                                         v_cnt,
                                                         v_disp_cnt
                                                        );
                               v_sumcnt := v_sumcnt + v_cnt;                                  

                            -- SENIORS, 2ND DEGREE AND 3RD DEGREE
                            WHEN 'U2U3'
                            THEN
                                                                                                                            
                                IF c_szc3054_row.szc3054_process = 'U2U3'THEN
                                    --U2 and U3
                                   DBMS_OUTPUT.put_line ('U2, U3');
                                END IF;
                                                    
                               p_u2_u3_by_hours (c_szc3054_row.szc3054_low_hrs,
                                                 c_szc3054_row.szc3054_high_hrs, 
                                                 c_szc3054_row.szc3054_process,
                                                 v_sgbstdn_eff_term,
                                                 v_reg_term,
                                                 v_cnt,
                                                 v_disp_cnt
                                                );
                               v_sumcnt := v_sumcnt + v_cnt;
                            
                            -- SENIORS, 2ND DEGREE AND 3RD DEGREE
                            WHEN 'SPECDEG'
                            THEN
                                                                        
                                IF v_first_specdeg = 'y'THEN
                                    --seniors,second degree, and thrid degree
                                   DBMS_OUTPUT.put_line ('--------------------------------------');
                                   DBMS_OUTPUT.put_line ('Seniors,Second Degree, and Third Degree');
                                   DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_specdeg:='n';
                                END IF;
                                                    
                               p_ug_by_hours (c_szc3054_row.szc3054_low_hrs,
                                                 c_szc3054_row.szc3054_high_hrs, 
                                                 c_szc3054_row.szc3054_process,
                                                 v_sgbstdn_eff_term,
                                                 v_reg_term,
                                                 v_cnt,
                                                 v_disp_cnt
                                                );
                               v_sumcnt := v_sumcnt + v_cnt;
                                        
                            -- PRIORITY 2
                            WHEN 'PRIOR2'
                            THEN
                                            
                                IF v_first_prior2 = 'y'THEN
                                    --P2 group
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('Priority Group 2');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_prior2:='n';
                                END IF;
                                                    
                               p_all_levels_p2_by_hours (c_szc3054_row.szc3054_low_hrs,
                                                         c_szc3054_row.szc3054_high_hrs,
                                                         c_szc3054_row.szc3054_process,
                                                         v_sgbstdn_eff_term,
                                                         v_reg_term,
                                                         v_cnt,
                                                         v_disp_cnt
                                                        );
                               v_sumcnt := v_sumcnt + v_cnt;
                                                   
                            -- UNDERGRADUATES
                            WHEN 'SPEC_UG'
                            THEN
                                 IF v_first_spec_ug = 'y'THEN
                                    --Undergraduates
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    DBMS_OUTPUT.put_line ('Undergraduates');
                                    DBMS_OUTPUT.put_line ('--------------------------------------');
                                    v_first_spec_ug:='n';
                                END IF;
                                                    
                               p_ug_by_hours (c_szc3054_row.szc3054_low_hrs,
                                              c_szc3054_row.szc3054_high_hrs,
                                              c_szc3054_row.szc3054_process,
                                              v_sgbstdn_eff_term,
                                              v_reg_term,
                                              v_cnt,
                                              v_disp_cnt
                                             );
                               v_sumcnt := v_sumcnt + v_cnt;       
                        END CASE;    
            EXCEPTION
                  WHEN case_not_found THEN
                     DBMS_OUTPUT.put_line ('--------------------------------------');
                     dbms_output.put_line(SUBSTR('ERR- TYPE:  ' || c_szc3054_row.SZC3054_TYPE || ' Not Found '||SQLERRM, 1,200));
					 DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);    --IMM ADDED
					 DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); 
                     DBMS_OUTPUT.put_line ('--------------------------------------');
                
        END;
            
    END LOOP;
    -- *** END FOR LOOP
    --^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    
    

   DBMS_OUTPUT.put_line ('--------------------------------------');
   DBMS_OUTPUT.put_line ('Total Tickets Assigned: ' || v_sumcnt);
   DBMS_OUTPUT.put_line ('--------------------------------------');
 

--gzpadmn.p_spool_lis('&1', &2, null, null,' ',v_spool_commit);
--gzpadmn.p_spool_lis('&1', &2, null, null,'Run assigned ' || v_total_cnt  || ' alternate pins',v_spool_commit);
--Rollback
   IF v_run_mode = 'A'
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line ('Audit Mode Completed');
   ELSIF v_run_mode = 'U'
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line ('Update Mode Completed');
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line ('Invalid Run Mode - Rollback Completed');
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
   END IF;

   DBMS_OUTPUT.PUT_LINE('END OF SZP3054'||': '||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
   DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
END;
/

EXIT;