-- ================================================================================================ -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPGADD.SQL -- DESCRIPTION: Diploma and Graduation Extract -- Related Banner Forms: SHADGMQ, SHADEGR -- AUTHOR: FRANK NEWLAND -- DATE WRITTEN: 02/15/2012 -- INPUT: TERM CODE -- DATE REVISED: -- WHEN WHO WHAT -- 05/04/2015 FCN StuDev#1289 -- 1. Remove publish info checkbox(page,report) -- 2. Add minor3,minor4, permanent address info to report. -- 3. Change 'Dual Degree' to 'Concurrent Degree' -- 4. Add confidentiality flag -- 5. Add Concentration2 and 3 -- 01/15/2016 FCN StuDev#1555 exclude REMOVED from SORLFOS selection query. -- 01/06/2020 IMM REQ0014697/TASK0019538 -- 1. CURSOR c_szccert was added for Certificates information. -- 2. 'CA' shrdgmr_degs_code was included. -- 3. v_diploma_ind was added for Diploma(DIP) or Certificate(CRT). -- 4. c_szcgadd was moved to be included in new logic. -- 5. sorlcur_degc_code not in GCRT, -- sorlfos_csts_code not in ('CERTAWARDED') removed. -- 6. Loop variables re-initiated inside of it. -- 03/06/2020 IMM INC0079416 - Dual Degree modification. -- 09/28/2021 IMM REQ0024073/TASK0035662/BNRDEV21-340 -- Main driver cursor get_graduating_students modified to check later for szgregs.f_get_grad_courses_flag. -- RSTS_CODE hard coded values replaced by indicators. -- 02/03/2022 RJM REQ0030671/BNRDEV22-95 rjm0056 Add Advisor /Removed reporting term requirement. -- ================================================================================================ -- SET SCAN OFF; set linesize 500; SET SERVEROUTPUT ON; SET ECHO OFF; SET VERIFY OFF; SET FEEDBACK OFF; SET TAB OFF; DECLARE --================================================================================================= -- Cursors --================================================================================================= -- This is the driver cursor. Get list of all students for term enrolled in -- UNIV-4AA0. Group by pidm to remove duplicate pidms. A duplicate pidm -- occurs when a student gets more than one degree and the second degree is -- in another school. In such cases the student will have two different CRNs, -- both for UNIV-4AA0. Handle dual degrees at the major level via -- 'get_degree_awarded' cursor. --------------------------------------------------------------------------------------------------- CURSOR get_graduating_students(in_term varchar2) -- Driver IS select sfrstcr_pidm as pidm from sfrstcr inner join ssbsect on ssbsect_term_code = sfrstcr_term_code and ssbsect_crn = sfrstcr_crn where sfrstcr_term_code = in_term and sfrstcr_rsts_code in (select STVRSTS_code from STVRSTS where STVRSTS_INCL_SECT_ENRL = 'Y' and STVRSTS_WITHDRAW_IND = 'N' and STVRSTS_GRADABLE_IND = 'Y') --('RE','RW') / IMM Replaced --and ssbsect_subj_code = 'UNIV' --and ssbsect_crse_numb in ('4AA0', '9AA0') --IMM -- and sfrstcr_pidm =2490129 -- and sfrstcr_pidm = gb_common.f_get_pidm(903107638) group by sfrstcr_pidm order by szg8003.f_get_id(sfrstcr_pidm); -------------------------------------------------------------------------------- -- This cursor returns relevant information about a graduating student's -- on-line diploma application -------------------------------------------------------------------------------- CURSOR c_szcgadd(in_term varchar2, in_pidm varchar2) IS SELECT * FROM szcgadd WHERE szcgadd_term_code = in_term and szcgadd_pidm = in_pidm; v_szcgadd_record c_szcgadd%rowtype; -------------------------------------------------------------------------------- -- This cursor returns relevant information about a graduating student's -- certificate diploma --[IMM TASK0019538 (1)] -------------------------------------------------------------------------------- CURSOR c_szccert(in_term varchar2, in_pidm number) IS SELECT * FROM szccert WHERE szccert_term_code = in_term and szccert_pidm = in_pidm; v_szccert_record c_szccert%rowtype; -------------------------------------------------------------------------------- cursor get_degree_major(in_pidm varchar2, in_term varchar2) is select shrdgmr_pidm pidm, shrdgmr_seq_no key_seq, --[IMM INC0079416] shrdgmr_degs_code degree_status, shrdgmr_levl_code levl_code, shrdgmr_degc_code, substr(shrdgmr_program,1,instr(shrdgmr_program,'_')-1) college_code, substr(shrdgmr_program, instr(shrdgmr_program,'_')+1, instr(shrdgmr_program,'_',-1) - instr(shrdgmr_program,'_',1)-1) degree_code, shrdgmr_majr_code_1 major1_code, shrdgmr_majr_code_1_2 major2_code, shrdgmr_majr_code_minr_1 minor1_code, shrdgmr_majr_code_minr_1_2 minor2_code, shrdgmr_majr_code_conc_1 concentration_code, stvmajr_desc major_desc, shrdgmr_program program_code from shrdgmr inner join stvmajr on shrdgmr_majr_code_1 = stvmajr_code where shrdgmr_pidm = in_pidm and shrdgmr_term_code_grad = in_term and shrdgmr_degs_code in ('AP','PN','AW','CA'); --[IMM TASK0019538 (2)] -------------------------------------------------------------------------------- cursor c_get_major_count(in_pidm varchar2, in_seqno varchar2) is select sorlfos_pidm, sorlfos_lcur_seqno, count(*) as multi_major from sorlfos where sorlfos_pidm = in_pidm and sorlfos_lcur_seqno = in_seqno and sorlfos_lfst_code = 'MAJOR' and sorlfos_current_cde='Y' and sorlfos_csts_code not in ('CERTAWARDED') group by sorlfos_pidm, sorlfos_lcur_seqno; v_major_count_record c_get_major_count%rowtype; -------------------------------------------------------------------------------- cursor get_degree_count(in_pidm varchar2, in_term varchar2) is select shrdgmr_pidm, count(*) degree_count from shrdgmr inner join stvmajr on shrdgmr_majr_code_1 = stvmajr_code where shrdgmr_degs_code in ('AP','PN','AW','CA') and --[IMM TASK0019538 (2)] shrdgmr_term_code_grad = in_term and shrdgmr_pidm = in_pidm group by shrdgmr_pidm; v_degree_count_record get_degree_count%rowtype; v_degrees_per_pidm number; -------------------------------------------------------------------------------- cursor get_suffix(in_pidm varchar2) is select spbpers_name_suffix suffix from spbpers where spbpers_pidm = in_pidm; v_suffix_record get_suffix%rowtype; --------------------------------------------------------------------------------- --Advisor for TERM REQ0030671/BNRDEV22-95 rjm0056 --------------------------------------------------------------------------------- cursor get_advisor(in_pidm varchar2)--, in_term varchar2) is select spriden_last_name || ', ' || spriden_first_name || ' ' || SUBSTR(spriden_mi, 1,1) from sgradvr a, spriden b where b.spriden_pidm = sgradvr_advr_pidm and b.spriden_change_ind is null and a.sgradvr_prim_ind = 'Y' and a.sgradvr_term_code_eff = (select max(c.sgradvr_term_code_eff) from sgradvr c where c.sgradvr_pidm = a.sgradvr_pidm and c.sgradvr_advr_pidm is not NULL and c.sgradvr_prim_ind = 'Y') --and sgradvr_term_code_eff = in_term and a.sgradvr_pidm = in_pidm; --============================================================================== -- Variables --============================================================================== v_report_name varchar2(7) := 'SZPGADD'; v_pidm varchar2(60); v_stu_id varchar2(60); v_term_code varchar2(6); v_line_out varchar(5000); v_commit boolean := true; v_count number := 0; v_term_code_ctlg varchar2(6); v_registered varchar2(1); v_levl_code varchar2(5); v_college_code varchar2(10); v_diploma_name nvarchar2(100); v_suffix varchar2(60); v_email varchar2(60); v_address_1 szcgadd.szcgadd_address_line1%type; v_address_2 szcgadd.szcgadd_address_line2%type; v_address_3 szcgadd.szcgadd_address_line3%type; v_city szcgadd.szcgadd_city%type; v_state_code szcgadd.szcgadd_state_code%type; v_postal_code szcgadd.szcgadd_zip%type; v_nation_name szcgadd.szcgadd_natn_name%type; v_pr_city varchar2(100); v_pr_state varchar2(100); v_pr_st_desc stvstat.stvstat_desc%type; v_pr_natn varchar2(100); v_nation stvnatn.stvnatn_nation%type; v_hometown szcgadd.szcgadd_hometown%type; v_homestate szcgadd.szcgadd_homestate%type; v_homecountry szcgadd.szcgadd_homecountry%type; v_attend szcgadd.szcgadd_attend_ceremony%type; -- v_publish szcgadd.szcgadd_allow_publish_name%type; v_confidential spbpers.spbpers_confid_ind%type; v_create_user szcgadd.szcgadd_create_user%type; v_create_date szcgadd.szcgadd_create_date%type; v_update_user szcgadd.szcgadd_update_user%type; v_update_date szcgadd.szcgadd_update_date%type; v_shrdgmr_flag varchar2(1); v_total_degrees number; v_dual_degree varchar2(1); v_double_major varchar2(1); v_degree_status varchar2(10); v_diploma_ind varchar2(10); v_degree_code varchar2(20); v_major1_code varchar2(20); v_major2_code varchar2(20); v_major3_code varchar2(20); v_minor1_code varchar2(20); v_minor2_code varchar2(20); v_minor3_code varchar2(20); v_minor4_code varchar2(20); v_program_code varchar2(20); v_key_seq varchar2(2); v_concentration_code_1 varchar2(60); v_concentration_code_2 varchar2(60); v_concentration_code_3 varchar2(60); v_report_1 varchar2(10) := ''; v_header_1 varchar2(1000); f1 VARCHAR2(10) := '"'; f2 varchar2(10) := '","'; v_dual_count number; v_degree_count number; v_print_count number :=0; v_sysbeg timestamp; v_sysend timestamp; v_duration interval day to second; v_advisor varchar2(122) := NULL; --REQ0030671/BNRDEV22-95 rjm0056 --============================================================================= -- FUNCTIONS --============================================================================= function f_get_address(in_pidm varchar2, in_field varchar2, in_type varchar2) return varchar2 is v_return_val varchar2(200); v_st_line1 spraddr.spraddr_street_line1%type; v_st_line2 spraddr.spraddr_street_line2%type; v_st_line3 spraddr.spraddr_street_line3%type; v_city spraddr.spraddr_city%type; v_stat_code spraddr.spraddr_stat_code%type; v_postal spraddr.spraddr_zip%type; v_natn_code spraddr.spraddr_natn_code%type; cursor get_address is select spraddr_atyp_code, spraddr_seqno, spraddr_street_line1, spraddr_street_line2, spraddr_street_line3, spraddr_city, spraddr_stat_code, spraddr_zip, spraddr_natn_code from ( select spraddr_atyp_code, spraddr_seqno, spraddr_street_line1, spraddr_street_line2, spraddr_street_line3, spraddr_city, spraddr_stat_code, spraddr_zip, spraddr_natn_code, rank() over (partition by spraddr_pidm order by spraddr_seqno desc) as ranking from spraddr where spraddr_pidm = in_pidm and spraddr_atyp_code=in_type and spraddr_status_ind is null and spraddr_from_date is not null and trunc(spraddr_from_date) < trunc(sysdate) and (spraddr_to_date is null or spraddr_to_date > sysdate) ) where ranking=1; v_address_record get_address%rowtype; begin open get_address; fetch get_address into v_address_record; if (get_address%found) then v_st_line1 := null; v_st_line1 := v_address_record.spraddr_street_line1; v_st_line2 := null; v_st_line2 := v_address_record.spraddr_street_line2; v_st_line3 := null; v_st_line3 := v_address_record.spraddr_street_line3; v_city := null; v_city := v_address_record.spraddr_city; v_stat_code := null; v_stat_code := v_address_record.spraddr_stat_code; v_postal := null; v_postal := v_address_record.spraddr_zip; v_natn_code := null; v_natn_code := v_address_record.spraddr_natn_code; end if; close get_address; case in_field when 'LINE1' then v_return_val := v_st_line1; when 'LINE2' then v_return_val := v_st_line2; when 'LINE3' then v_return_val := v_st_line3; when 'CITY' then v_return_val := v_city; when 'STATE' then v_return_val := v_stat_code; when 'POSTAL' then v_return_val := v_postal; when 'NATN' then v_return_val := v_natn_code; else v_return_val := null; end case; return v_return_val; end f_get_address; --============================================================================= function f_get_confidential_flag(in_pidm varchar2) return varchar2 is v_return_val varchar2(1); v_confidence_flag varchar2(1); cursor get_confidential_flag is select nvl(spbpers_confid_ind ,'N') as confidential_flag from spbpers where spbpers_pidm = in_pidm; v_confidence_record get_confidential_flag%rowtype; begin v_confidence_flag := null; open get_confidential_flag ; fetch get_confidential_flag into v_confidence_record; if (get_confidential_flag%found) then v_confidence_flag := v_confidence_record.confidential_flag; else null; end if; close get_confidential_flag; v_return_val := v_confidence_flag; return v_return_val; end f_get_confidential_flag; --============================================================================== -- FUNCTION --============================================================================== function f_get_degc_code(in_pidm varchar2, in_term varchar2, in_priority varchar2) return varchar2 is v_return_val varchar2(10); v_degc_code varchar2(10); cursor c_get_degree_record is select sorlcur_pidm, sorlcur_seqno, sorlcur_term_code, sorlcur_priority_no, sorlcur_levl_code, sorlcur_coll_code, sorlcur_degc_code, sorlcur_current_cde, degree_ranking from ( select sorlcur_pidm, sorlcur_seqno, sorlcur_term_code, sorlcur_priority_no, sorlcur_levl_code, sorlcur_coll_code, sorlcur_degc_code, sorlcur_current_cde, rank() over (partition by sorlcur_pidm, sorlcur_priority_no order by sorlcur_term_code desc) as degree_ranking from sorlcur where sorlcur_pidm = in_pidm and sorlcur_lmod_code='LEARNER' and sorlcur_cact_code='ACTIVE' and sorlcur_term_code <= in_term order by sorlcur_term_code desc ) where degree_ranking = 1 and sorlcur_priority_no = in_priority; v_degree_record c_get_degree_record%rowtype; begin v_return_val := null; v_degc_code := null; open c_get_degree_record; fetch c_get_degree_record into v_degree_record; if (c_get_degree_record%found) then v_degc_code := v_degree_record.sorlcur_degc_code; dbms_output.put_line('606 v_degc_code :[' || v_degc_code || ']'); else v_degc_code := null; dbms_output.put_line('609 v_degc_code :[' || v_degc_code || ']'); end if; close c_get_degree_record; v_return_val := v_degc_code; return v_return_val; exception when others then dbms_output.put_line('Error in function f_get_degc_code on pidm :[' || in_pidm || ']'); end f_get_degc_code; --============================================================================== -- FUNCTION --============================================================================== /* --============================================================================= Notes on Reporting Majors, Minors and Concentrations. There are three scenarios to consider when reporting majors, minors, and conc. 1. Single degree with a single major A. Only one relevant record in SORLCUR. This record usually has SORLCUR_PRIORITY = 1 B. This SORLCUR record joins to only one SORLFOS record C. Join SORLCUR_SEQNO = SORLFOS_LCUR_SEQNO D. Result of Join returns 1 record E. Report findings F. It is possible that a single degree student has mulitple rows in SORLCUR with the same SORLCUR_PROGRAM. If this happens then get the one with greatest SORLCUR_SEQNO eg. TERM=201520, PIDM=2463337 has two AG_BS_AGEC in SORLCUR 2. DUAL MAJOR aka DOUBLE MAJOR A. Only one relevant record in SORLCUR. This record usually has SORLCUR_PRIORITY = 1 B. Multple relevant records in SORLFOS. C. Join SORLCUR_SEQNO = SORLFOS_LCUR_SEQNO D. Result of Join returns multiple records One SORLCUR maps to many SORLFOS records The SORLCUR record has SORLCUR_PRIORITY =1 The SORLFOS_PRIORITY =1 (First Major) The SORLFOS_PRIORITY =2 (Second Major) etc. 3. DUAL DEGREE aka CONCURRENT DEGREE. A. Has TWO relevant records in SORLCUR each having a unique SORLCUR_SEQNO B. First degree has SORLCUR_PRIORITY =1 C. Second degree has SORLCUR_PRIORITY =2 D. Join SORLCUR_SEQNO = SORLFOS_LCUR_SEQNO The first SORLCUR major (PRIORITY=1 results in SORLFOS_PRIORITY_N=1) SORLCUR_SEQNO = SORLFOS_LCUR_SEQNO = X The secod SORLCUR major (PRIORITY=2 results in SORLFOS_PRIORITY_N=1) SORLCUR_SEQNO = SORLFOS_LCUR_SEQNO = Y 4. The difference between DOUBLE MAJORS (DM) and DUAL DEGREES (DD) A. DM has SORLCUR_PRIORITY = 1 and each major has sequential SORLFOS_PRIORITY(1,2,3 etc) B. DD has multiple SORLCUR_PRIOROTY and each SORLFOS_PRIORITY =1 DD has multiple SORLCUR_PRIORITY=1 (First Major) and SORLFOS_PRIORITY=1 DD has multiple SORLCUR_PRIORITY=2 (Second Major) and SORLFOS_PRIORITY=1 5. This relationship exists for MAJORS, MINORS and CONCENTRATIONS. For major, minor, and concentration: the value for each is in SORLFOS_MAJR_CODE MAJORS have where SORLFOS_LFST_CODE = MAJOR the value to report is SOR MINORS have where SORLFOS_LFST_CODE = MINOR CONCENTRATIONS have where SORLFOS_LFST_CODE = CONCENTRATION 6. Certificates have SORLCUR_DEGC_CODE = GCRT. */ --============================================================================= function f_get_maj_min_con(in_term varchar2, in_pidm varchar2, in_priority varchar2, in_program varchar2, in_lfst varchar2, in_key_seq varchar2) return varchar2 is v_return_val varchar2(10); v_maj_aaa varchar2(10); v_line_out varchar2(1000); ----------------------------------------- cursor c_get_sorlcur_count is select sorlcur_pidm, distinct_programs, total_sorlcur_records from ( select sorlcur_pidm, count(distinct sorlcur_program) over (partition by sorlcur_pidm) as distinct_programs, count(*) over (partition by sorlcur_pidm) as total_sorlcur_records from sorlcur where sorlcur_pidm = in_pidm and sorlcur_term_code = in_term and sorlcur_lmod_code ='OUTCOME' and sorlcur_cact_code ='ACTIVE' and sorlcur_current_cde ='Y' --and sorlcur_degc_code not in ('GCRT') --[IMM TASK0019538 (5)] ) group by sorlcur_pidm, distinct_programs, total_sorlcur_records; v_sorlcur_count_record c_get_sorlcur_count%rowtype; v_total_sorlcur_records number; ----------------------------------------- cursor c_get_sorlcur_seqno is select sorlcur_pidm, sorlcur_seqno, sorlcur_key_seqno, sorlcur_program, most_recent_sorlcur_seqno from ( select sorlcur_pidm, sorlcur_seqno, sorlcur_key_seqno, sorlcur_program, rank() over (partition by sorlcur_pidm order by sorlcur_seqno desc) as most_recent_sorlcur_seqno from sorlcur where sorlcur_pidm = in_pidm and sorlcur_term_code = in_term and sorlcur_program = in_program and sorlcur_lmod_code ='OUTCOME' and sorlcur_cact_code ='ACTIVE' and sorlcur_current_cde ='Y' --and sorlcur_degc_code not in ('GCRT') --[IMM TASK0019538 (5)] ) where sorlcur_key_seqno = in_key_seq;--most_recent_sorlcur_seqno=1; [IMM INC0079416] v_sorlcur_seqno_record c_get_sorlcur_seqno%rowtype; v_sorlcur_seqno number; ----------------------------------------- cursor c_get_sorlfos_record_aaa(local_pidm varchar2, local_lfst varchar2, local_seqno number, local_priority varchar2) is select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_lfst_code, sorlfos_priority_no, sorlfos_majr_code, sorlfos_current_cde, sorlfos_term_code, sorlfos_csts_code, sorlfos_cact_code from sorlfos where sorlfos_pidm = local_pidm and sorlfos_priority_no = local_priority and sorlfos_lcur_seqno = local_seqno and sorlfos_lfst_code = local_lfst and sorlfos_current_cde='Y' --and sorlfos_csts_code not in ('CERTAWARDED') --[IMM TASK0019538 (5)] and sorlfos_cact_code not in ('REMOVED','INACTIVE'); v_sorlfos_record_aaa c_get_sorlfos_record_aaa%rowtype; v_distinct_programs number; begin -- dbms_output.put_line('556 BEGIN in_pidm:['|| in_pidm || -- '] in_stu_id :[' || gb_common.f_get_id(in_pidm)|| -- '] in_term:[' || in_term || -- '] in_priority:[' || in_priority || -- '] in_program:[' || in_program || -- '] in_lfst:[' || in_lfst || -- '] v_distinct_programs:[' || v_distinct_programs || -- ']'); -- v_distinct_programs := null; v_total_sorlcur_records := null; open c_get_sorlcur_count; fetch c_get_sorlcur_count into v_sorlcur_count_record; if (c_get_sorlcur_count%found) then v_distinct_programs := v_sorlcur_count_record.distinct_programs; v_total_sorlcur_records := v_sorlcur_count_record.total_sorlcur_records; else null; -- dbms_output.put_line('575 NO DISTINCT COUNT: in_pidm :[' || in_pidm || ']'); end if; close c_get_sorlcur_count; -- dbms_output.put_line('575 CAPTURE NEW VALS in_pidm :['|| in_pidm || -- '] in_stu_id :[' || gb_common.f_get_id(in_pidm)|| -- '] in_term:[' || in_term || -- '] in_priority:[' || in_priority || -- '] in_program:[' || in_program || -- '] in_lfst:[' || in_lfst || -- '] NEW 1 v_distinct_programs:[' || v_distinct_programs || -- '] NEW 1 v_total_sorlcur_records:[' || v_total_sorlcur_records|| -- ']'); -- v_sorlcur_seqno := null; open c_get_sorlcur_seqno; fetch c_get_sorlcur_seqno into v_sorlcur_seqno_record; if (c_get_sorlcur_seqno%found) then v_sorlcur_seqno := v_sorlcur_seqno_record.sorlcur_seqno; end if; close c_get_sorlcur_seqno; -- dbms_output.put_line('594 CAPTURE NEW 2 in_pidm :['|| in_pidm || -- '] in_stu_id :[' || gb_common.f_get_id(in_pidm)|| -- '] in_term:[' || in_term || -- '] in_priority:[' || in_priority || -- '] in_program:[' || in_program || -- '] in_lfst:[' || in_lfst || -- '] NEW 1 v_distinct_programs:[' || v_distinct_programs || -- '] NEW 1 v_total_sorlcur_records:[' || v_total_sorlcur_records|| -- '] NEW 2 v_sorlcur_seqno:[' || v_sorlcur_seqno|| -- ']'); v_double_major := null; open c_get_major_count(v_pidm,v_sorlcur_seqno); fetch c_get_major_count into v_major_count_record; if (c_get_major_count%found) then v_double_major := v_major_count_record.multi_major; else v_double_major := 0; end if; close c_get_major_count; -- dbms_output.put_line('594 CAPTURE NEW 2 in_pidm :['|| in_pidm || -- '] in_stu_id :[' || gb_common.f_get_id(in_pidm)|| -- '] in_term:[' || in_term || -- '] in_priority:[' || in_priority || -- '] in_program:[' || in_program || -- '] in_lfst:[' || in_lfst || -- '] NEW 1 v_distinct_programs:[' || v_distinct_programs || -- '] NEW 1 v_total_sorlcur_records:[' || v_total_sorlcur_records|| -- '] NEW 2 v_sorlcur_seqno:[' || v_sorlcur_seqno|| -- '] NEW 3 v_double_major:[' || v_double_major|| -- ']'); if (v_double_major > 1) then v_double_major := 'Y'; else v_double_major := 'N'; end if; -- dbms_output.put_line('633Post dblmajor compare: in_pidm :['|| in_pidm || -- '] in_stu_id :[' || gb_common.f_get_id(in_pidm)|| -- '] in_term:[' || in_term || -- '] in_priority:[' || in_priority || -- '] in_program:[' || in_program || -- '] in_lfst:[' || in_lfst || -- '] NEW 1 v_distinct_programs:[' || v_distinct_programs || -- '] NEW 1 v_total_sorlcur_records:[' || v_total_sorlcur_records|| -- '] NEW 2 v_sorlcur_seqno:[' || v_sorlcur_seqno|| -- '] NEW 3 v_double_major:[' || v_double_major|| -- ']'); CASE -- 1. Typical Single degree record in SORLCUR when (v_distinct_programs = 1 and v_total_sorlcur_records = 1) then open c_get_sorlcur_seqno; fetch c_get_sorlcur_seqno into v_sorlcur_seqno_record; if (c_get_sorlcur_seqno%found) then v_sorlcur_seqno := v_sorlcur_seqno_record.sorlcur_seqno; end if; close c_get_sorlcur_seqno; -- dbms_output.put_line('CASE #1 prg = rec :[' || v_sorlcur_seqno || ']'); when (v_distinct_programs = 2 and v_total_sorlcur_records = 2 ) then null; -- 2. Multiple SORLCUR records for same PROGRAM when (v_distinct_programs <> v_total_sorlcur_records ) then open c_get_sorlcur_seqno; fetch c_get_sorlcur_seqno into v_sorlcur_seqno_record; if (c_get_sorlcur_seqno%found) then v_sorlcur_seqno := v_sorlcur_seqno_record.sorlcur_seqno; end if; close c_get_sorlcur_seqno; -- dbms_output.put_line('CASE #2 prg <> records :[' || v_sorlcur_seqno || ']'); else null; -- dbms_output.put_line('676 CASE ELSE :[ ' || in_pidm || -- '] in_term:[' || in_term || -- '] in_lfst:[' || in_lfst || -- '] v_distinct_programs:[' || v_distinct_programs|| -- '] v_total_sorlcur_records:[' || v_total_sorlcur_records || -- ']'); END CASE; -------------------------------------------------------------------------------- -- Now that you have the desired SORLCUR_SEQNO you are ready to join to SORLFOS -- to get major, minor, concentration information. -------------------------------------------------------------------------------- v_maj_aaa := null; open c_get_sorlfos_record_aaa(in_pidm,in_lfst,v_sorlcur_seqno,in_priority); fetch c_get_sorlfos_record_aaa into v_sorlfos_record_aaa; if (c_get_sorlfos_record_aaa%found) then v_maj_aaa := v_sorlfos_record_aaa.sorlfos_majr_code; else null; end if; close c_get_sorlfos_record_aaa; -- dbms_output.put_line('688 v_maj_aaa:['|| v_maj_aaa || ']'); --============================================================================== v_return_val := null; v_return_val := v_maj_aaa; return v_return_val; exception when others then dbms_output.put_line('Error found in f_get_maj_min_con on PIDM:['||in_pidm ||']'); dbms_output.put_line('SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('SQLERRM:[' || SQLERRM || ']'); v_line_out := dbms_utility.format_error_backtrace; dbms_output.put_line('ERROR BACKTRACE:[' ||v_line_out || ']'); v_line_out := dbms_utility.format_call_stack; dbms_output.put_line('ERROR CALL STACK:[' ||v_line_out || ']'); end f_get_maj_min_con; --============================================================================== function f_get_maj_min_con_b(in_term varchar2, in_pidm varchar2, in_lfst varchar2, in_priority varchar2) return varchar2 is v_return_val varchar2(6); v_shrdgmr_majr varchar2(6); v_szvcurr_con varchar2(6); cursor c_get_szvcurr_maj_min_con is select academic_period, degree, student_level, college, major, second_major, first_minor, second_minor, first_concentration from szvcurr where person_uid = in_pidm and academic_period = in_term and current_curriculum = 'Y'; cursor get_major_minor is select sovlfos_pidm, sovlfos_term_code, sovlfos_lfst_code, sovlfos_majr_code, shrdgmr_seq_no, sovlfos_priority_no, sovlfos_csts_code, sovlfos_current_ind, sovlfos_active_ind, shrdgmr_majr_code_1 from ( select sovlfos_pidm, sovlfos_term_code, sovlfos_lfst_code, sovlfos_majr_code, sovlfos_priority_no, sovlfos_csts_code, sovlfos_current_ind, sovlfos_active_ind, shrdgmr_majr_code_1, shrdgmr_seq_no, rank() over (partition by sovlfos_pidm order by sovlfos_term_code desc, sovlfos_lcur_seqno desc -- sovlfos_seqno ) as term_ranking, rank() over (partition by sovlfos_pidm, sovlfos_lfst_code order by sovlfos_term_code desc, sovlfos_lcur_seqno desc ) as focus_ranking from sovlfos inner join shrdgmr on sovlfos_pidm = shrdgmr_pidm where sovlfos_pidm = in_pidm and sovlfos_term_code <= in_term and shrdgmr_term_code_ctlg_1 <= in_term and sovlfos_current_cde ='Y' and sovlfos_majr_code not in ('0000') and sovlfos_current_ind = 'Y' and sovlfos_cact_code = 'ACTIVE' and sovlfos_active_ind = 'Y' and ( sovlfos_dept_code not in ('0000') or sovlfos_dept_code is null ) and sovlfos_csts_code not in ('CERTAWARDED') and shrdgmr_degs_code not in ('CA') ) where term_ranking = 1 -- All records with max term code and focus_ranking = 1 -- All current MAJORS, MINORS,CONCENTRATIONS and sovlfos_lfst_code = in_lfst -- and sovlfos_priority_no = in_priority; v_major_minor_record get_major_minor%rowtype; v_major_minor varchar2(6); v_tcc varchar2(6); begin open get_major_minor; fetch get_major_minor into v_major_minor_record; if (get_major_minor%found) then v_major_minor := v_major_minor_record.shrdgmr_majr_code_1; --v_major_minor := v_major_minor_record.sovlfos_majr_code; v_shrdgmr_majr:= v_major_minor_record.shrdgmr_majr_code_1; end if; v_return_val := v_major_minor; if (v_major_minor <> v_shrdgmr_majr) then dbms_output.put_line('502 DIFFERENCE:[' || in_pidm || ']'); dbms_output.put_line('502 v_major_minor:[' || v_major_minor || ']'); dbms_output.put_line('502 v_shrgdmr_majr:['|| v_shrdgmr_majr|| ']'); dbms_output.put_line('502 in_lfst:[' || in_lfst || ']'); end if; return v_return_val; end f_get_maj_min_con_b; --============================================================================== function f_get_nation(in_natn varchar2) return varchar2 is v_return_val stvnatn.stvnatn_nation%type; v_nation stvnatn.stvnatn_nation%type; cursor c_get_nation is select stvnatn_nation from stvnatn where stvnatn_code=in_natn; v_nation_record c_get_nation%rowtype; begin v_return_val := null; v_nation := null; open c_get_nation; fetch c_get_nation into v_nation_record; if (c_get_nation%found) then v_nation := v_nation_record.stvnatn_nation; else v_nation := null; end if; close c_get_nation; v_return_val := v_nation; return v_return_val; end f_get_nation; --============================================================================= -- BEGIN MAIN --============================================================================= BEGIN v_sysbeg := sysdate; DBMS_OUTPUT.ENABLE(null); --Set to NULL --IMM DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZPGADD:' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE('-'); -- v_term_code :='201220'; v_term_code := UPPER(gzpadmn.f_get_parm('&1','&2','&4','01')); dbms_output.put_line('Parm01-Term Code:[' || v_term_code || ']'); v_header_1 := f1 ||'DOCUMENT TYPE' || f2 ||'CONCURRENT_DEGREE' || f2 ||'DEGREE_STATUS' || f2 ||'APPLIED' || f2 ||'TERM' || f2 ||'ADVISOR' --REQ0030671/BNRDEV22-95 rjm0056 || f2 ||'STUDENT_ID' || f2 ||'LAST_NAME' || f2 ||'FIRST_NAME' || f2 ||'MIDDLE_NAME' || f2 ||'SUFFIX' || f2 ||'LEVEL' || f2 ||'COLLEGE' || f2 ||'DEGREE' || f2 ||'MAJOR1' || f2 ||'MAJOR2' || f2 ||'MAJOR3' || f2 ||'MINOR1' || f2 ||'MINOR2' || f2 ||'MINOR3' || f2 ||'MINOR4' || f2 ||'CONCENTRATION_1' || f2 ||'CONCENTRATION_2' || f2 ||'CONCENTRATION_3' || f2 ||'EMAIL' || f2 ||'DIPLOMA_NAME' || f2 ||'ADDRESS_1' || f2 ||'ADDRESS_2' || f2 ||'ADDRESS_3' || f2 ||'CITY' || f2 ||'STATE' || f2 ||'POSTAL_CODE' || f2 ||'NATION_NAME' || f2 ||'HOMETOWN' || f2 ||'HOMESTATE' || f2 ||'HOMECOUNTRY' || f2 ||'CONFIDENTIAL' || f2 ||'ATTEND' || f2 ||'PR CITY' || f2 ||'PR STATE' || f2 ||'PR COUNTRY' || f2 ||'CREATE_USER' || f2 ||'CREATE_DATE' || f2 ||'UPDATE_USER' || f2 ||'UPDATE_DATE' || f2 ||f1; --============================================================================= -- Report: Students enrolled in UNIV-4AA0 and their diploma registration info --============================================================================= v_line_out := v_header_1; gzpadmn.p_spool_lis('&1','&2',v_report_1,'0',v_header_1,v_commit); v_count := 0 ; v_total_degrees := 0; v_dual_count := 0; FOR graduating_student in get_graduating_students(v_term_code) LOOP IF szgregs.f_get_grad_courses_flag(graduating_student.pidm,v_term_code, 'ENR') = 'Y' then v_count := v_count+1; v_pidm := null; v_pidm :=graduating_student.pidm; v_stu_id := gb_common.f_get_id(v_pidm); v_confidential := null; v_confidential := f_get_confidential_flag(v_pidm); v_email := null; v_email := lower(szg8003.f_get_gid(v_pidm) || '@auburn.edu'); ---------------------------------------------------------------------------- -- Student advisor for term REQ0030671/BNRDEV22-95 rjm0056 ---------------------------------------------------------------------------- v_advisor := null; open get_advisor(v_pidm); fetch get_advisor into v_advisor; close get_advisor; ---------------------------------------------------------------------------- -- Has student registered for diploma? If so, get relevant information ---------------------------------------------------------------------------- v_suffix := null; open get_suffix(v_pidm); fetch get_suffix into v_suffix_record; if (get_suffix%found) then v_suffix := v_suffix_record.suffix; end if; close get_suffix; case substr(upper(v_suffix),1,2) when 'JR' then v_suffix := 'Jr.'; when 'SR' then v_suffix := 'Sr.'; else null; end case; v_pr_city := null; v_pr_city := f_get_address(v_pidm,'CITY','PR'); v_pr_state := null; v_pr_state := f_get_address(v_pidm,'STATE','PR'); v_pr_st_desc := null; case upper(v_pr_state) when 'ZZ' then v_pr_st_desc := null; else v_pr_st_desc := szg8005.f_get_desc(v_pr_state,'STVSTAT'); end case; v_pr_natn := null; v_pr_natn := f_get_address(v_pidm,'NATN','PR'); v_nation := null; v_nation := f_get_nation(v_pr_natn); ---------------------------------------------------------------------------- --How many degrees did this student earn this semester? ---------------------------------------------------------------------------- v_degrees_per_pidm := 0; v_shrdgmr_flag :='N'; open get_degree_count(v_pidm, v_term_code); fetch get_degree_count into v_degree_count_record; if (get_degree_count%found) then v_shrdgmr_flag := 'Y'; v_degrees_per_pidm := v_degree_count_record.degree_count; v_total_degrees := v_total_degrees + v_degrees_per_pidm; end if; close get_degree_count; v_dual_degree := null; if (v_degrees_per_pidm > 1) then v_dual_degree := 'Y'; v_dual_count := v_dual_count + 1; else v_dual_degree := null; end if; ---------------------------------------------------------------------------- -- Check SHRDGMR to obtain student's degree and major. -- A student could be getting more than one degree at graduation. So loop -- through GET_DEGREE_MAJOR curor and report information through -- each iteration. This will create additional rows per pidm if necessary -- to report 2nd degree. ---------------------------------------------------------------------------- v_levl_code := null; v_college_code := null; v_degree_count := 0; v_degree_status := null; v_diploma_ind := null; v_degree_code := null; v_major1_code := null; v_major2_code := null; v_major3_code := null; v_minor1_code := null; v_minor2_code := null; v_concentration_code_1 :=''; v_concentration_code_2 :=''; v_concentration_code_3 :=''; --dbms_output.put_line('1099 v_shrdgmr_flag:[' || v_shrdgmr_flag || ']'); if (v_shrdgmr_flag = 'Y') then for degree_major_record in get_degree_major(v_pidm, v_term_code) loop --Moved to inside the loop --[IMM TASK0019538 (6)] v_registered := 'N'; v_diploma_name := null; v_address_1 := null; v_address_2 := null; v_address_3 := null; v_city := null; v_state_code := null; v_postal_code := null; v_nation_name := null; v_hometown := null; v_homestate := null; v_homecountry := null; v_attend := null; v_create_user := null; v_create_date := null; v_update_user := null; v_update_date := null; v_degree_count := v_degree_count + 1; v_levl_code := null; v_levl_code := degree_major_record.levl_code; v_degree_status := null; v_diploma_ind := null; v_degree_status := degree_major_record.degree_status; v_college_code := null; v_college_code := degree_major_record.college_code; v_degree_code := null; v_degree_code := degree_major_record.shrdgmr_degc_code; v_program_code := null; v_program_code := degree_major_record.program_code; v_key_seq := null;--[IMM INC0079416] v_key_seq := degree_major_record.key_seq; if v_degree_code is null then v_degree_code := f_get_degc_code(v_pidm,v_term_code,'1'); end if; --------------------------------------------------------------------------- -- v_diploma_ind added as an indicator for Diploma(DIP) or Certificate(CRT) -- If v_diploma_ind is a Certificate, then is pulling off data from SZCCERT -- [IMM TASK0019538 (3)] --------------------------------------------------------------------------- if v_degree_code in ('GCRT', 'UCRT') then v_diploma_ind := 'CRT'; open c_szccert(v_term_code, v_pidm); fetch c_szccert into v_szccert_record; if (c_szccert%found) then v_registered := 'Y'; v_diploma_name := v_szccert_record.szccert_certificate_name; v_address_1 := v_szccert_record.szccert_address_line1; v_address_2 := v_szccert_record.szccert_address_line2; v_address_3 := v_szccert_record.szccert_address_line3; v_city := v_szccert_record.szccert_city; v_state_code := v_szccert_record.szccert_state_code; v_postal_code := v_szccert_record.szccert_zip; v_nation_name := v_szccert_record.szccert_natn_name; v_hometown := v_szccert_record.szccert_hometown; v_homestate := v_szccert_record.szccert_homestate; v_homecountry := v_szccert_record.szccert_homecountry; v_attend := v_szccert_record.szccert_attend_ceremony; v_create_user := v_szccert_record.szccert_create_user; v_create_date := trunc(v_szccert_record.szccert_create_date); v_update_user := v_szccert_record.szccert_update_user; v_update_date := trunc(v_szccert_record.szccert_update_date); end if; if (c_szccert%isopen) then close c_szccert; end if; else v_diploma_ind := 'DIP'; --Moved here from 962, so it would retrieve the information depending on the type of document. --[IMM TASK0019538 (4)] open c_szcgadd(v_term_code, v_pidm); fetch c_szcgadd into v_szcgadd_record; if (c_szcgadd%found) then v_registered := 'Y'; v_diploma_name := v_szcgadd_record.szcgadd_diploma_name; v_address_1 := v_szcgadd_record.szcgadd_address_line1; v_address_2 := v_szcgadd_record.szcgadd_address_line2; v_address_3 := v_szcgadd_record.szcgadd_address_line3; v_city := v_szcgadd_record.szcgadd_city; v_state_code := v_szcgadd_record.szcgadd_state_code; v_postal_code := v_szcgadd_record.szcgadd_zip; v_nation_name := v_szcgadd_record.szcgadd_natn_name; v_hometown := v_szcgadd_record.szcgadd_hometown; v_homestate := v_szcgadd_record.szcgadd_homestate; v_homecountry := v_szcgadd_record.szcgadd_homecountry; v_attend := v_szcgadd_record.szcgadd_attend_ceremony; -- v_publish := v_szcgadd_record.szcgadd_allow_publish_name; v_create_user := v_szcgadd_record.szcgadd_create_user; v_create_date := trunc(v_szcgadd_record.szcgadd_create_date); v_update_user := v_szcgadd_record.szcgadd_update_user; v_update_date := trunc(v_szcgadd_record.szcgadd_update_date); end if; if (c_szcgadd%isopen) then close c_szcgadd; end if; end if; -------------------------------------------------------------------------- v_major1_code := null; v_major1_code :=f_get_maj_min_con(v_term_code,v_pidm,1,v_program_code,'MAJOR',v_key_seq);--[IMM INC0079416] -- dbms_output.put_line('1125 v_pidm:[' || v_pidm || -- '] v_shrdgmr_flag:[' || v_shrdgmr_flag || -- '] v_major1_code:[' || v_major1_code || -- '] v_major2_code:[' || v_major2_code || -- '] v_major3_code:[' || v_major3_code || -- ']'); v_major2_code := null; v_major2_code := f_get_maj_min_con(v_term_code,v_pidm,2,v_program_code,'MAJOR',v_key_seq); if (v_major1_code is null and v_major2_code is not null) then v_major1_code := v_major2_code; v_major2_code := null; end if; v_major3_code := f_get_maj_min_con(v_term_code,v_pidm,3,v_program_code,'MAJOR',v_key_seq); --============================================================================= -- GET MINOR INFORMATION --============================================================================= v_minor1_code := null; v_minor1_code := f_get_maj_min_con(v_term_code,v_pidm,1,v_program_code,'MINOR',v_key_seq); v_minor2_code := null; v_minor2_code := f_get_maj_min_con(v_term_code,v_pidm,2,v_program_code,'MINOR',v_key_seq); v_minor3_code := null; v_minor3_code := f_get_maj_min_con(v_term_code,v_pidm,3,v_program_code,'MINOR',v_key_seq); v_minor4_code := null; v_minor4_code := f_get_maj_min_con(v_term_code,v_pidm,4,v_program_code,'MINOR',v_key_seq); --============================================================================= -- GET CONCENTRATION INFORMATION --============================================================================= v_concentration_code_1 := null; v_concentration_code_1 := f_get_maj_min_con(v_term_code,v_pidm,1,v_program_code,'CONCENTRATION',v_key_seq); v_concentration_code_2 := null; v_concentration_code_2 := f_get_maj_min_con(v_term_code,v_pidm,2,v_program_code,'CONCENTRATION',v_key_seq); v_concentration_code_3 := null; v_concentration_code_3 := f_get_maj_min_con(v_term_code,v_pidm,3,v_program_code,'CONCENTRATION',v_key_seq); if (v_major1_code is null) then v_major1_code := f_get_maj_min_con_b(v_term_code,v_pidm,'MAJOR','1'); end if; if (v_major1_code is null and v_major2_code is null) then v_major2_code := f_get_maj_min_con(v_term_code,v_pidm,2,v_program_code,'MAJOR',v_key_seq); end if; /* --============================================================================= The data in BANNER may be poorly entered by users. For example, a student may have only one major. If entered in Banner correctly this will show in SHADEGR as PRIORITY=1. However, if a Banner user miskeys something then this could appear as PRIORITY=2 or 3. User wants to report first non-null major, regardless of where it appears in Banner as major1 , etc. This merely requires reassigning v_major2 to v_major1 for those cases when v_major1 is null and v_major2 is not null. Banner allows up to five majors. This routine addresses only the first three majors and minors. MAJOR Note: v_major_1 captures SHADEGR MAJOR_CODE where priority=1 and LFST_CODE=MAJOR v_major_2 captures SHADEGR MAJOR_CODE where priority=2 and LFST_CODE=MAJOR v_major_3 captures SHADEGR MAJOR_CODE where priority=3 and LFST_CODE=MAJOR MINOR Note: v_minor_1 captures SHADEGR MAJOR_CODE where priority=1 and LFST_CODE=MINOR v_minor_2 captures SHADEGR MAJOR_CODE where priority=2 and LFST_CODE=MINOR v_minor_3 captures SHADEGR MAJOR_CODE where priority=3 and LFST_CODE=MINOR --============================================================================= */ CASE -- 1 of 8 -- Highly unlikely for graduating students receiving a diploma. when (v_major1_code is null) and (v_major2_code is null) and (v_major3_code is null) then null; -- 2 of 8 when (v_major1_code is null) and (v_major2_code is null) and (v_major3_code is NOT null) then v_major1_code := v_major3_code; v_major3_code := null; -- 3 of 8 when (v_major1_code is null) and (v_major2_code is NOT null) and (v_major3_code is null) then v_major1_code := v_major2_code; v_major2_code := null; -- 4 of 8 when (v_major1_code is null) and (v_major2_code is NOT null) and (v_major3_code is NOT null) then v_major1_code := v_major2_code; v_major2_code := v_major3_code; v_major3_code := null; -- 5 of 8 when (v_major1_code is NOT null) and (v_major2_code is null) and (v_major3_code is null) then null; -- 6 of 8 -- Leave major1 alone. when (v_major1_code is NOT null) and (v_major2_code is null) and (v_major3_code is NOT null) then v_major2_code := v_major3_code; v_major3_code := null; -- 7 of 8 when (v_major1_code is NOT null) and (v_major2_code is NOT null) and (v_major3_code is null) then null; -- 8 of 8 -- Triple Major. WDE!! when (v_major1_code is NOT null) and (v_major2_code is NOT null) and (v_major3_code is NOT null) then null; else null; END CASE; /* --========================= -- Debug statements --========================= if (v_major1_code = v_major2_code) or (v_major2_code = v_major3_code) or (v_major1_code = v_major3_code) then dbms_output.put_line('1255 v_stu_id:[' || szg8003.f_get_id(v_pidm) || '] v_major1_code:[' || v_major1_code || '] v_major2_code:[' || v_major2_code || '] v_major3_code:[' || v_major3_code || ']'); end if; */ --============================================================================= -- Same logic as MAJORS except there are 4 MINORS to consider. --============================================================================= case -- 1 of 16 -- when (v_minor1_code is null) and (v_minor2_code is null) and (v_minor3_code is null) and (v_minor4_code is null) then null; -- 2 of 16 when (v_minor1_code is null) and (v_minor2_code is null) and (v_minor3_code is null) and (v_minor4_code is NOT null) then v_minor1_code := v_minor4_code; v_minor4_code := null; -- 3 of 16 when (v_minor1_code is null) and (v_minor2_code is null) and (v_minor3_code is NOT null) and (v_minor4_code is null) then v_minor1_code := v_minor3_code; v_minor3_code := null; -- 4 of 16 when (v_minor1_code is null) and (v_minor2_code is null) and (v_minor3_code is NOT null) and (v_minor4_code is NOT null) then v_minor1_code := v_minor3_code; v_minor2_code := v_minor4_code; v_minor3_code := null; v_minor4_code := null; -- 5 of 16 when (v_minor1_code is null) and (v_minor2_code is NOT null) and (v_minor3_code is null) and (v_minor4_code is null) then v_minor1_code := v_minor2_code; v_minor2_code := null; -- 6 of 16 when (v_minor1_code is null) and (v_minor2_code is NOT null) and (v_minor3_code is null) and (v_minor4_code is NOT null) then v_minor1_code := v_minor2_code; v_minor2_code := v_minor4_code; v_minor4_code := null; -- 7 of 16 when (v_minor1_code is null) and (v_minor2_code is NOT null) and (v_minor3_code is NOT null) and (v_minor4_code is null) then v_minor1_code := v_minor2_code; v_minor2_code := v_minor3_code; v_minor3_code := null; -- 8 of 16 when (v_minor1_code is null) and (v_minor2_code is NOT null) and (v_minor3_code is NOT null) and (v_minor4_code is NOT null) then v_minor1_code := v_minor2_code; v_minor2_code := v_minor3_code; v_minor3_code := v_minor4_code; v_minor4_code := null; -- 9 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is null) and (v_minor3_code is null) and (v_minor4_code is null) then null; -- 10 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is null) and (v_minor3_code is null) and (v_minor4_code is NOT null) then v_minor2_code := v_minor4_code; v_minor4_code := null; -- 11 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is null) and (v_minor3_code is NOT null) and (v_minor4_code is null) then v_minor2_code := v_minor3_code; v_minor3_code := null; -- 12 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is null) and (v_minor3_code is NOT null) and (v_minor4_code is NOT null) then v_minor2_code := v_minor3_code; v_minor3_code := v_minor4_code; v_minor4_code := null; -- 13 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is NOT null) and (v_minor3_code is null) and (v_minor4_code is null) then null; -- 14 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is NOT null) and (v_minor3_code is null) and (v_minor4_code is NOT null) then v_minor3_code := v_minor4_code; v_minor4_code := null; -- 15 of 16 when (v_minor1_code is NOT null) and (v_minor2_code is NOT null) and (v_minor3_code is NOT null) and (v_minor4_code is null) then null; -- 16 of 16 -- QUADRUPLE MINOR. WDE!! when (v_minor1_code is NOT null) and (v_minor2_code is NOT null) and (v_minor3_code is NOT null) and (v_minor4_code is NOT null) then null; end case; -------------------------------------------------------------------- -- Send information to report. -------------------------------------------------------------------- v_print_count := v_print_count + 1; -- dbms_output.put_line('v_print_count:[' || v_print_count || ']'); v_line_out := f1 || v_diploma_ind || f2 || v_dual_degree || f2 || v_degree_status || f2 || v_registered || f2 || v_term_code || f2 || v_advisor --REQ0030671/BNRDEV22-95 rjm0056 || f2 || szg8003.f_get_id(v_pidm) || f2 || szg8003.f_get_last(v_pidm) || f2 || szg8003.f_get_first(v_pidm) || f2 || szg8003.f_get_middle(v_pidm) || f2 || v_suffix || f2 || v_levl_code || f2 || v_college_code || f2 || v_degree_code || f2 || v_major1_code || f2 || v_major2_code || f2 || v_major3_code || f2 || v_minor1_code || f2 || v_minor2_code || f2 || v_minor3_code || f2 || v_minor4_code || f2 || v_concentration_code_1 || f2 || v_concentration_code_2 || f2 || v_concentration_code_3 || f2 || v_email || f2 || v_diploma_name || f2 || v_address_1 || f2 || v_address_2 || f2 || v_address_3 || f2 || v_city || f2 || v_state_code || f2 || v_postal_code || f2 || v_nation_name || f2 || v_hometown || f2 || v_homestate || f2 || v_homecountry --|| f2 || v_publish --StuDev#1289 || f2 || v_confidential || f2 || v_attend || f2 || v_pr_city --|| f2 || v_pr_state || f2 || v_pr_st_desc || f2 || v_nation || f2 || v_create_user || f2 || v_create_date || f2 || v_update_user || f2 || v_update_date || f2 || f1; gzpadmn.p_spool_lis('&1','&2',v_report_1,'0',v_line_out,v_commit); end loop; else null; end if; ELSE continue; END IF; END LOOP; dbms_output.put_line('UNIV-4AA0 Count : [' || v_count || ']'); dbms_output.put_line('Records printed..: [' || v_print_count || ']' ); dbms_output.put_line('Degrees Counted..: [' || v_total_degrees || ']'); dbms_output.put_line('Dual Degree Count: [' || v_dual_count || ']'); v_sysend := sysdate; v_duration := v_sysend - v_sysbeg; dbms_output.put_line('Begin:[' || v_report_name || '] at [' || v_sysbeg || ']'); dbms_output.put_line('End..:[' || v_report_name || '] at [' || v_sysend || ']'); dbms_output.put_line('Duration................:[' ||v_duration || ']'); dbms_output.put_line( v_report_name || ' complete.'); EXCEPTION WHEN OTHERS THEN --verrmsg := SQLERRM; --verrcode := SQLCODE; DBMS_OUTPUT.PUT_LINE('Error found in SZPGADD MAIN.'||f1||SQLERRM); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); DBMS_OUTPUT.put_line('ROLLING BACK CHANGES'); ROLLBACK; END; / EXIT;