/* Formatted on 2007/03/21 13:12 (Formatter Plus v4.8.8) */
-- Object Type:  PL/SQL
-- Object Name:  szp3053sql
-- Author     :  David Buchannon, Jr.
-- Description:  Set Time Tickets
--
-- 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
-- ************************************************************************************************************
-- Edited by Naomi Rigby 1/16/2009
-- Edited to fulfill the spring 2009 registration
-- ************************************************************************************************************
-- Edited by Naomi Rigby 9/01/2009
-- Re-written to automate registration by using values from an external table.
-- Description:  Assigns tickets to currently enrolled students and two terms back.
-- ************************************************************************************************************
-- Edited by Monica Chen 08/24/2010 
-- Added the function to include students with Veteran codes in P1 priority 
-- Changed the sport codes selection from 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 from PGA to a new group PRIORV and moved HON% attribute to a new group PRIORH
-- as requested by Dan Hill ticket 198
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited bu Rodney Clark
-- Added TSUP to activity code for priority 2
-- For ticket 1871
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited by Rodney Clark
-- Added The filter for student with type W
-- For ticket 2085
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited by Rodney Clark   05/18/2018
--  removing the graduation exclusion for GR/PR in c_gr_pr_by_hours
-- For ticket 2085
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited by Lisa Zhang   07/27/2018
--  removing the graduation exclusion for GR/PR in c_gr_pr_by_hours
-- For ticket 2370 (a followup to the solved ticket #2303)
-- ************************************************************************************************************
-- ************************************************************************************************************
--Edited by Rodney Clark   02/15/2019  Ticket 2548
--  added Auburn Online Students 
-- For ticket 2548
-- ************************************************************************************************************
-- ************************************************************************************************************ 
--Edited by Irida Medina   09/30/2020  
-- 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_current_term_code   varchar2(6);
v_one_prior_term_code varchar2(6);
v_two_prior_term_code varchar2(6);

--used to print headers for each category
v_first_gradpro       varchar2(1);
v_first_prior1        varchar2(1);
v_first_priorv        varchar2(1);
v_first_priorh        varchar2(1);
v_first_specdeg       varchar2(1);
v_first_prior2        varchar2(1);
v_first_spec_ug       varchar2(1);
v_first_auo           varchar2(1);
v_include_online      varchar2(1);

-- ***CREATE CURSOR TO READ SZC3053
CURSOR c_szc3053
IS
  SELECT * 
    FROM szc3053;
    
-- gets term that are earlier than this year and sorts in descending order    
CURSOR c_stvterm (v_current_term varchar2)
IS 
  SELECT  stvterm_code
    FROM  stvterm
    WHERE stvterm_code < v_current_term
    ORDER BY stvterm_code desc;

-- gets group codes
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;

-- gets count
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';

-- gets all levels by hours
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
       FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code) )
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
        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_styp_code != 'W' -- added by rodney 12/18/2017  Ticket 2085
        AND A.sgbstdn_levl_code IN ('UG')
        AND NOT EXISTS (
                          SELECT 'x'
                            FROM sfbrgrp
                           WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                             AND sfbrgrp_term_code = v_reg_term)
        AND NOT EXISTS --eclude anyonw 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 (v_two_prior_term_code,v_one_prior_term_code)
                  AND shrdgmr_term_code_completed IN (v_two_prior_term_code,v_one_prior_term_code)
                  AND shrdgmr_grst_code = 'AW'
                  AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
                  )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;

-- gets ug by hour
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
        FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code) )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W' -- added by rodney 12/18/2017  Ticket 2085
              AND NOT EXISTS (
                     SELECT 'x'
                       FROM sfbrgrp
                      WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                        AND sfbrgrp_term_code = v_reg_term)
             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 (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;
 
  -- added by RGC0002 Ticket 2548 02/15/2019
     cursor c_get_online_cur(v_term                     varchar2,
                                                  v_reg_term            varchar2)
     is
     select DISTINCT A.sgbstdn_pidm
     from sgbstdn  a
         where sgbstdn_styp_code='W'
         and 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_term)
      AND NOT EXISTS (
                          SELECT 'x'
                            FROM sfbrgrp
                           WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                             AND sfbrgrp_term_code = v_reg_term);


-- gets group priority by hours
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
        FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code) )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W' -- added by rodney 12/18/2017  Ticket 2085
              AND NOT EXISTS (
                     SELECT 'x'
                       FROM sfbrgrp
                      WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                        AND sfbrgrp_term_code = v_reg_term)
      /*      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 (v_two_prior_term_code,v_one_prior_term_code)

             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             ) */    --Removed by LZ 7/27/2018
 )
 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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
        FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code)  )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W'  -- added by rodney 12/18/2017  Ticket 2085
              AND NOT EXISTS (
                     SELECT 'x'
                       FROM sfbrgrp
                      WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                        AND sfbrgrp_term_code = v_reg_term)
             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 (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;
                                
   
-- PGA
-- gets priority 1 by hours
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
             FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code)  )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W'  -- added by rodney 12/18/2017  Ticket 2085
              AND ((EXISTS (   -- tests for attributes
                         SELECT 'x'
                           FROM sgrsatt w
                          WHERE w.sgrsatt_pidm = A.sgbstdn_pidm
                            AND w.sgrsatt_atts_code = 'COOP' 
                            -- ATH 1/11/2012 Tic 189 move hon to it's own group 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    --test for disability 
                            (
                         SELECT 'x'
                           FROM sgrdisa WHERE sgrdisa_spsr_code = 'P1'
                           AND (sgrdisa_term_code = v_reg_term)
                           AND sgrdisa_pidm = A.sgbstdn_pidm) 
                       )
                    -- moved veterans to PRIORV group
                    --OR (EXISTS       --test for veteran codes 
                    --        (
                    --      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) 
                    --    )
                  )
              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
             AND NOT EXISTS --exclude 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 (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;

-- PRIORV  - Veterans Only group by hours
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
             FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code)  )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W'  -- added by rodney 12/18/2017  Ticket 2085
              -- 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)
                        ))
               -- not already asigned a group --
              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
             AND NOT EXISTS --exclude 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 (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
             FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code)  )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W'  -- added by rodney 12/18/2017  Ticket 2085
              -- must be honors --
              AND EXISTS (   -- tests for attributes
                         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 x.sgrsatt_term_code_eff <= v_reg_term
                                    GROUP BY x.sgrsatt_pidm))
              -- not already asigned a group --
              AND NOT EXISTS (
                     SELECT 'x'
                       FROM sfbrgrp
                      WHERE sfbrgrp_pidm = A.sgbstdn_pidm
                        AND sfbrgrp_term_code = v_reg_term)
             --exclude anyone with a graduation term greated than the current sggbstdn admit term --
             AND NOT EXISTS 
             (SELECT 'x' FROM shrdgmr WHERE 
             shrdgmr_pidm = A.sgbstdn_pidm
             AND shrdgmr_term_code_completed IN (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;


-- PGB
-- gets priority 2 by hours
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)
               + NVL(
                      (SELECT tot_crhrs_a3 
                       FROM sfvrcrs 
                       WHERE pidm_a3 = A.sgbstdn_pidm
                       AND term_code_a3 = v_current_term_code),0))
               AS sum_hours
             FROM sgbstdn A
             INNER JOIN
            ( SELECT DISTINCT enr_pidm FROM 
                            (SELECT DISTINCT shrtckn_pidm AS enr_pidm FROM shrtckn
                            WHERE shrtckn_term_code IN (v_two_prior_term_code,v_one_prior_term_code)  )                            
                            UNION
                            (SELECT DISTINCT sfrstcr_pidm AS enr_pidm FROM sfrstcr
                            WHERE sfrstcr_term_code = v_current_term_code)
            ) enr_check
            ON A.sgbstdn_pidm = enr_pidm
            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 != 'W'  -- added by rodney 12/18/2017  Ticket 2085
              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 -- EDITED BY NRL FEB 3 2010 - took out activity code ROTC  
                            sgrsact_actc_code IN ('BAND', 'MSC2','TSUP')  -- Edited by RGC 3/28/2017  Added TSUP
                            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)
             AND NOT EXISTS --exclude 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 (v_two_prior_term_code,v_one_prior_term_code)
             AND shrdgmr_grst_code = 'AW'
             AND shrdgmr_term_code_completed >= A.sgbstdn_term_code_admit
             )
 )
 WHERE sum_hours >= v_ge_hrs
 AND   sum_hours < v_lthan_hrs;
                                

-- defines variables
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;
   
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
                                      )
                              );
		DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);   --IMM ADDED
		DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); 	  
						
   END p_insert_ticket;
   
    --Added by RGC0002 02/15/2018        Ticket 2548
   procedure p_auburn_online(    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_get_online_cur (
                                                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);
         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_auburn_online;

   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);
         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);
         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);
         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);
         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);
         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);
         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);
         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);
         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 SZP3053'||': '||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
   
  v_include_online := 'N';  -- added by RGC0002  02/15/2019    Ticket  2548
  v_disp_cnt := TRUE;
--Get parms from Banner
  v_current_term_code := gzpadmn.f_get_parm ('&1', '&2', '&4', '01');
  v_reg_term := gzpadmn.f_get_parm ('&1', '&2', '&4', '02');
  v_sgbstdn_eff_term := v_reg_term;
  v_run_mode := UPPER(gzpadmn.f_get_parm ('&1', '&2', '&4', '03'));
  v_glb_user := '&3';
  v_include_online := UPPER(gzpadmn.f_get_parm ('&1', '&2', '&4', '04'));
  
    --*******************
  --  added theses variables for printing purposes for headers
  v_first_gradpro := 'y';
  v_first_prior1 := 'y';
  v_first_priorv := 'y';
  v_first_priorh := 'y';
  v_first_specdeg := 'y';
  v_first_prior2 := 'y';
  v_first_spec_ug := 'y';
  v_first_auo   := 'y';  -- added by RGC0002  02/15/2019    Ticket  2548

  --********************
   
--debugging variables
--v_sgbstdn_eff_term := '200820';
--v_reg_term := '200820';
--v_run_mode := 'A';
--v_glb_user := 'BUCHADL';


   

-- variables to contain one term and two terms prior to 
OPEN c_stvterm(v_current_term_code);
     FETCH c_stvterm INTO v_one_prior_term_code;
     FETCH c_stvterm INTO v_two_prior_term_code;
CLOSE c_stvterm;

    DBMS_OUTPUT.put_line ('User: ' || v_glb_user);
    DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
    DBMS_OUTPUT.put_line ('Parm - Current Term for SGASTDN Records: '  || v_current_term_code);
    DBMS_OUTPUT.put_line ('Parm - Registration Term: ' || v_reg_term);
    DBMS_OUTPUT.put_line ('Parm - Run Mode: ' || v_run_mode);
    DBMS_OUTPUT.put_line ('Parm - Auburn Online: ' || v_include_online);
    DBMS_OUTPUT.put_line ('Calculated One Term Prior: ' || v_one_prior_term_code);
    DBMS_OUTPUT.put_line ('Calculated Two Terms Prior: ' || v_two_prior_term_code);
    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 (szc3053)
    -- *** START FOR LOOP
    FOR c_szc3053_row IN c_szc3053  LOOP

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

        BEGIN
                            CASE c_szc3053_row.SZC3053_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_szc3053_row.szc3053_low_hrs,  --0,
                                                    c_szc3053_row.szc3053_high_hrs, --999999,
                                                    c_szc3053_row.szc3053_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_szc3053_row.szc3053_low_hrs,     --145,
                                                             c_szc3053_row.szc3053_high_hrs,    --999999,
                                                             c_szc3053_row.szc3053_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_priorv = 'y'THEN
                                    --P1 group
                                        DBMS_OUTPUT.put_line ('--------------------------------------');
                                        DBMS_OUTPUT.put_line ('Veterans');
                                        DBMS_OUTPUT.put_line ('--------------------------------------');
                                        v_first_priorv:='n';
                                    END IF;
                                   
                                   p_all_levels_vet_by_hours (c_szc3053_row.szc3053_low_hrs,
                                                             c_szc3053_row.szc3053_high_hrs,
                                                             c_szc3053_row.szc3053_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_priorh = 'y'THEN
                                    --P1 group
                                        DBMS_OUTPUT.put_line ('--------------------------------------');
                                        DBMS_OUTPUT.put_line ('HONI/HONR');
                                        DBMS_OUTPUT.put_line ('--------------------------------------');
                                        v_first_priorh:='n';
                                    END IF;
                                   
                                   p_all_levels_hon_by_hours (c_szc3053_row.szc3053_low_hrs,
                                                             c_szc3053_row.szc3053_high_hrs,
                                                             c_szc3053_row.szc3053_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 'U2U3'
                                THEN
                                                                                                        
--                                    IF c_szc305 3_row.szc3053_process = 'U2U3'THEN
--                                        --U2 and U3
--                                       DBMS_OUTPUT.put_line ('U2, U3');
--                                    END IF;
                                            
                                   p_u2_u3_by_hours (c_szc3053_row.szc3053_low_hrs,     --145,
                                                             c_szc3053_row.szc3053_high_hrs,    --999999,
                                                             c_szc3053_row.szc3053_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 '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_szc3053_row.szc3053_low_hrs,
                                                     c_szc3053_row.szc3053_high_hrs, 
                                                     c_szc3053_row.szc3053_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_szc3053_row.szc3053_low_hrs,
                                                             c_szc3053_row.szc3053_high_hrs,
                                                             c_szc3053_row.szc3053_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_szc3053_row.szc3053_low_hrs,
                                                  c_szc3053_row.szc3053_high_hrs,
                                                  c_szc3053_row.szc3053_process,
                                                  v_sgbstdn_eff_term,
                                                  v_reg_term,
                                                  v_cnt,
                                                  v_disp_cnt
                                                 );
                                   v_sumcnt := v_sumcnt + v_cnt;
                                   
                                     WHEN 'AUONLINE'  -- added by rgc0002 02/15/2019  Ticket 2548
                                     then
                                     if v_include_online = 'Y'  then--check to  see if its Y from form
                                            If v_first_auo = 'y' then                                        
                                                DBMS_OUTPUT.put_line ('--------------------------------------');
                                                DBMS_OUTPUT.put_line ('Auburn Online');
                                                DBMS_OUTPUT.put_line ('--------------------------------------');                                        
                                                v_first_auo := 'n';
                                            end if;
                                            
                                             p_auburn_online(c_szc3053_row.szc3053_low_hrs,
                                                                            c_szc3053_row.szc3053_high_hrs,
                                                                            c_szc3053_row.szc3053_process,
                                                                             v_sgbstdn_eff_term,
                                                                             v_reg_term,
                                                                             v_cnt ,
                                                                             v_disp_cnt);
                                                                             
                                            v_sumcnt := v_sumcnt + v_cnt;    
                                    end if;
                                   
                            END CASE;
            EXCEPTION
                  WHEN case_not_found THEN
                     DBMS_OUTPUT.put_line ('--------------------------------------');
                     dbms_output.put_line(SUBSTR('ERR- TYPE:  ' || c_szc3053_row.SZC3053_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 ('--------------------------------------');
 


-- Commit or 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 SZP3053'||': '||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
   DBMS_OUTPUT.put_line ('-----------------------------------------------------------------------------');
END;
/

EXIT;