-- ============================================Diploma/Ceremony Vendor Report ================================= -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPGDIP.SQL -- AUTHOR: FRANK NEWLAND -- DATE WRITTEN: 06/20/2012 -- DESCRIPTION: Diploma application report to diploma vendor specifications -- PARMS: 01: Term Code -- 02: Graduation Date -- 03: Outcome Codes ------------------------------------------------------------------------------- -- TABLE NOTES: -- SZCDMA1 Undergraduate Degree information -- SZCDDE1 Graduate Degree information -- SZCGADD Diploma Applicants -- SZCGTA1 Diploma Signatories ------------------------------------------------------------------------------- -- REVISION HISTORY -- When Who What -- 06/20/2012 FCN Original report -- 04/10/2013 FCN StuDev#612 Added First,Middle,Last names end of report. -- 03/17/2016 RGC Removed Signature section of report -- 03/17/2016 RGC Added multiple Signatures at the begining of the document -- 03/17/2016 RGC Changed email to pull from email table instead of -- concatinating GID -- 06/15/2016 RGC Many changes made to process. Almost rewritten -- 07/05/2018 FCN StuDev#2331 Corrected:1. mid-record wordwrap by -- removing CR/LF from fields. -- 2. UG Degree title reporting. -- 3. Set CED column to NO (column AS) -- 11/26/2018 FCN StuDev#2475 Allow select programs to get AUM treatment. -- 02/07/2019 FCN StuDev#2525 Report correct Major2 and Major3 information -- Add new column to report AT_HOLD (Y/N) -- 11/12/2019 IMM REQ0014697/TASK0019538 -- 1. CURSOR c_szccert added to retrieve Certificates data. -- 2. sorlfos_cact_code not in ('REMOVED','INACTIVE') added. -- 3. cursor c_specific_stu_major was modified to include -- sorlcur_levl_code and check for priority_no. -- 4. 'CA' award status code added. -- 5. v_diploma_ind added for Diploma (DIP) or Certificate (CRT), and -- get_diploma_applicant was moved here to be included in the logic. -- 6. get_degree_wording_gr_cur was commented out. -- 7. c_student_majors cursor was modified and c_get_priority was created -- to be used instead of item_number. -- 8. Honors wording was modified. -- 9. MSN Joint diploma was specified in the logic. -- 06/04/2020 IMM INC0087140 - 'char' added to the variables declaration. Small enhancements performed. -- 09/28/2021 IMM REQ0024073/TASK0035662/BNRDEV21-337 -- Main driver cursor get_graduating_student modified to check later for szgregs.f_get_grad_courses_flag. -- 10/15/2021 RJM https://auburnu.atlassian.net/browse/BNRDEV21-416 Update field logic for SZPGDIP (Diploma Application) report. -- Mini Column is No when the degree code column is GCRT or UCRT / Ced Column is set to YES in all instances. -- ============================================================================= --SET SCAN OFF; SET LINESIZE 5000; SET SERVEROUTPUT ON SIZE UNLIMITED; SET ECHO OFF; SET VERIFY OFF; SET FEEDBACK OFF; SET tab OFF; DECLARE -- ============================================================================= -- 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 college/school. -- In such cases the student has two different CRNs, both for UNIV-4AA0. -- Handle dual degrees at the major level via 'get_degree_awarded' cursor. -- ============================================================================= CURSOR get_graduating_student(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') --and ssbsect_subj_code = 'UNIV' --and ssbsect_crse_numb in ('4AA0','9AA0') --IMM -- and sfrstcr_pidm = gb_common.f_get_pidm(903378032) group by sfrstcr_pidm order by gb_common.f_get_id(sfrstcr_pidm); -- ============================================================================= -- All students with record in SZCGADD for given term -- ============================================================================= CURSOR get_diploma_applicant(in_pidm varchar2, in_term VARCHAR2) IS SELECT * FROM szcgadd WHERE szcgadd_term_code = in_term AND szcgadd_pidm = in_pidm; v_diploma_applicant get_diploma_applicant%rowtype; --find out if the graduating student needs 2 signatures CURSOR get_two_sigs_cur(v_major_code varchar2) is select * from szcdsi1 where szcdsi1_majr_code = v_major_code; szcdsi1_record szcdsi1%rowtype; -------------------------------------------------------------------------------- CURSOR get_dean(in_coll_code VARCHAR2) IS SELECT * FROM szcgta1 WHERE szcgta1_coll_code = in_coll_code; v_dean_record get_dean%ROWTYPE; --------------------------------------------------------------------------------- --Undergraduate Degree information is in SZCDMA1 cursor get_degree_wording_cur(v_major varchar2) is select * from szcdma1 where szcdma1_majr_code = v_major; szcdma1_record szcdma1%rowtype; szcdma1_record1 szcdma1%rowtype; szcdma1_record2 szcdma1%rowtype; szcdma1_record3 szcdma1%rowtype; cursor c_get_ug_degree_wording(in_degree_code varchar2) is select szcdma1_degr_code, szcdma1_degr_title from szcdma1 where szcdma1_degr_code = in_degree_code group by szcdma1_degr_code, szcdma1_degr_title; v_ug_degree_wording_record c_get_ug_degree_wording%rowtype; -------------------------------------------------------------------------------- cursor c_get_ug_degree_info(in_degr_code varchar2, in_majr_code varchar2) is select szcdma1_degr_code, szcdma1_majr_code, szcdma1_degr_title, szcdma1_majr_text, szcdma1_optn_text from szcdma1 where szcdma1_degr_code = in_degr_code and szcdma1_majr_code = in_majr_code; v_ug_degree_record c_get_ug_degree_info%rowtype; ------------------------------------------------------------------------------- cursor get_degree_wording_gr_cur(v_degree varchar2) is select * from szcdde1 where szcdde1_degr_code = v_degree; szcdde1_record szcdde1%rowtype; -------------------------------------------------------------------------------- -- added by RGC0002 to get email from email table and not concatinate GID CURSOR get_email_address_cur(in_pidm varchar2) is select goremal_email_address from goremal where goremal_pidm = in_pidm and GOREMAL_EMAL_CODE = 'AU'; cursor get_official is select * from szcgta1 where upper(szcgta1_levl_code) = 'ZZ' and upper(szcgta1_coll_code) = 'ZZ' order by szcgta1_sort_order; -------------------------------------------------------------------------------- -- 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_primary_major(in_pidm varchar2) IS select sovlcur_pidm pidm, sovlcur_levl_code levl_code, sovlcur_coll_code coll_code, sovlcur_degc_code degc_code, sovlcur_program major, max_sovlcur from (select sovlcur_pidm, sovlcur_seqno, sovlcur_term_code, sovlcur_priority_no, sovlcur_levl_code, sovlcur_coll_code, sovlcur_degc_code, sovlcur_program, rank() over (partition by sovlcur_pidm order by sovlcur_term_code desc nulls last, sovlcur_priority_no) max_sovlcur from sovlcur where sovlcur_pidm = in_pidm and sovlcur_lmod_code = 'LEARNER' and sovlcur_cact_code = 'ACTIVE') where max_sovlcur = 1; -------------------------------------------------------------------------------- CURSOR get_college_info(in_coll_code varchar2) is select szcgta1_coll_desc college_desc from szcgta1 where szcgta1_coll_code = in_coll_code; v_college_info_record get_college_info%ROWTYPE; -------------------------------------------------------------------------------- CURSOR get_degree_awarded(in_degc_code VARCHAR2) IS SELECT stvdegc_desc FROM stvdegc WHERE stvdegc_code = in_degc_code; v_degree_awarded VARCHAR2 (100); v_degree_award_record get_degree_awarded%ROWTYPE; -------------------------------------------------------------------------------- CURSOR get_dept_honors(in_pidm VARCHAR2,in_seq_no NUMBER) IS SELECT shrdgdh_pidm dept_honors_pidm, shrdgdh_dgmr_seq_no dept_degree_seq_no, stvhond_desc dept_honors_desc FROM shrdgdh INNER JOIN stvhond ON shrdgdh_hond_code = stvhond_code WHERE shrdgdh_pidm = in_pidm AND shrdgdh_dgmr_seq_no = in_seq_no; v_dept_honors_record get_dept_honors%ROWTYPE; v_dept_honor_desc VARCHAR2 (50); -------------------------------------------------------------------------------- CURSOR get_inst_honors(in_pidm VARCHAR2, in_seq_no NUMBER) IS SELECT shrdgih_pidm inst_honors_pidm, shrdgih_dgmr_seq_no inst_degree_seq_no, stvhonr_desc inst_honors_desc FROM shrdgih INNER JOIN stvhonr ON shrdgih_honr_code = stvhonr_code WHERE shrdgih_pidm = in_pidm AND shrdgih_dgmr_seq_no = in_seq_no; v_inst_honors_record get_inst_honors%ROWTYPE; v_inst_honor_desc VARCHAR2 (50); -------------------------------------------------------------------------------- --changed to get majors from the degree tables instead of the backfill to shrdgmr cursor get_sorlcur_cur(v_pidm varchar2, v_term varchar2, v_seq_no varchar2 ) is select * from sorlcur where sorlcur_pidm = v_pidm and sorlcur_lmod_code = 'OUTCOME' and sorlcur_cact_code = 'ACTIVE' and sorlcur_key_seqno = v_seq_no and sorlcur_term_code = v_term and sorlcur_current_cde = 'Y'; ----------------------------------------------------------------------------------- cursor c_student_majors(in_pidm varchar2, in_seq number) is select count (distinct szcdma1_majr_code) as majcount -- [IMM TASK0019538 (7)] from( select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_seqno, sorlfos_lfst_code, sorlfos_term_code, sorlfos_priority_no, sorlfos_majr_code, sorlfos_dept_code, sorlfos_current_cde, szcdma1_degr_code, szcdma1_majr_code, szcdma1_majr_text, szcdma1_optn_text, count(*) over (partition by sorlfos_pidm, sorlfos_lcur_seqno) as major_count, row_number() over (partition by sorlfos_pidm order by sorlfos_seqno) as item_number from sorlfos left join szcdma1 on sorlfos_majr_code = szcdma1_majr_code where sorlfos_pidm = in_pidm and sorlfos_lfst_code='MAJOR' and sorlfos_lcur_seqno = in_seq --and (szcdma1_degr_code = in_degc or szcdma1_degr_code is null) and sorlfos_current_cde='Y' and sorlfos_cact_code not in ('REMOVED','INACTIVE')); --Added [IMM TASK0019538 (2)] v_stu_majr_rec c_student_majors%rowtype; ----------------------------------------------------------------------------------- cursor c_get_priority( in_pidm varchar2, -- [IMM TASK0019538 (7)] in_seq number) is select distinct sorlfos_priority_no as priority from( select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_seqno, sorlfos_lfst_code, sorlfos_term_code, sorlfos_priority_no, sorlfos_majr_code, sorlfos_dept_code, sorlfos_current_cde, szcdma1_degr_code, szcdma1_majr_code, szcdma1_majr_text, szcdma1_optn_text, count(*) over (partition by sorlfos_pidm, sorlfos_lcur_seqno) as major_count, row_number() over (partition by sorlfos_pidm order by sorlfos_seqno) as item_number from sorlfos left join szcdma1 on sorlfos_majr_code = szcdma1_majr_code where sorlfos_pidm = in_pidm and sorlfos_lfst_code='MAJOR' and sorlfos_lcur_seqno = in_seq --and (szcdma1_degr_code = in_degc or szcdma1_degr_code is null) and sorlfos_current_cde='Y' and sorlfos_cact_code not in ('REMOVED','INACTIVE')); ----Added [IMM TASK0019538 (2)] v_get_priority c_get_priority%rowtype; ------------------------------------------------------------------------------------------------- cursor c_specific_stu_major(in_pidm varchar2, in_lcur_seq number, in_major_number number) is select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_seqno, sorlfos_lfst_code, sorlfos_term_code, sorlfos_priority_no, sorlfos_majr_code, sorlfos_dept_code, sorlfos_current_cde, szcdma1_degr_code, szcdma1_majr_code, szcdma1_majr_text, szcdma1_optn_text, sorlcur_levl_code from ( select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_seqno, sorlfos_lfst_code, sorlfos_term_code, sorlfos_priority_no, sorlfos_majr_code, sorlfos_dept_code, sorlfos_current_cde, szcdma1_degr_code, szcdma1_majr_code, szcdma1_majr_text, szcdma1_optn_text, sorlcur_levl_code, count(*) over (partition by sorlfos_pidm, sorlfos_lcur_seqno) as major_count, row_number() over (partition by sorlfos_pidm order by sorlfos_seqno) as item_number from sorlfos left join szcdma1 on sorlfos_majr_code = szcdma1_majr_code left join sorlcur -- Added [IMM TASK0019538 (3)] on sorlfos_pidm = sorlcur_pidm and sorlfos_lcur_seqno = sorlcur_seqno where sorlfos_pidm = in_pidm and sorlfos_lfst_code='MAJOR' and sorlfos_lcur_seqno = in_lcur_seq and sorlfos_current_cde='Y' and sorlfos_cact_code not in ('REMOVED','INACTIVE')) --Added [IMM TASK0019538 (2)] where sorlfos_priority_no = in_major_number; -- where item_number = in_major_number; --Commented out [IMM TASK0019538 (3)] v_specific_major_rec c_specific_stu_major%rowtype; v_variable c_specific_stu_major%rowtype; -------------------------------------------------------------------------------------------------------- cursor get_major_cur(in_pidm varchar2, in_term varchar2, in_seq_no number, in_degc_code varchar2) is select sorlfos_pidm, sorlfos_lcur_seqno, sorlfos_seqno, sorlfos_lfst_code, sorlfos_priority_no, sorlfos_majr_code, szcdma1_degr_code, szcdma1_majr_code, szcdma1_degr_title, szcdma1_majr_text, szcdma1_optn_text from sorlfos left join szcdma1 on sorlfos_majr_code = szcdma1_majr_code where sorlfos_pidm = in_pidm and sorlfos_lcur_seqno = in_seq_no and sorlfos_lfst_code = 'MAJOR' and szcdma1_degr_code = in_degc_code and sorlfos_cact_code not in ('REMOVED','INACTIVE') --Added [IMM TASK0019538 (2)] -- and sorlfos_csts_code = 'AWARDED' /* and sorlfos_seqno = (select max(b.sorlfos_seqno) from sorlfos b where a.sorlfos_lcur_seqno = b.sorlfos_lcur_seqno and a.sorlfos_pidm = b.sorlfos_pidm)*/ -- and sorlfos_term_code = in_term and sorlfos_current_cde = 'Y' order by sorlfos_seqno; v_dma1_degr_title varchar2(500); v_dma1_degr_code varchar2(500); v_dma1_marj_text varchar2(500); ---------------------------------------------------------------------------------- cursor get_graduation_cur(v_pidm varchar2, v_term varchar2, in_codes varchar2) is select * from shrdgmr where shrdgmr_pidm = v_pidm and shrdgmr_term_code_grad = v_term and shrdgmr_degs_code in (SELECT REGEXP_SUBSTR(in_codes,'[^,]+',1,LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(in_codes,'[^,]+',1,LEVEL)IS NOT NULL); -------------------------------------------------------------------------------- CURSOR get_degree_major(in_pidm VARCHAR2, in_term VARCHAR2, in_codes VARCHAR2) IS select shrdgmr_pidm pidm, shrdgmr_degs_code degree_status, shrdgmr_seq_no degree_seq_no, shrdgmr_levl_code levl_code, shrdgmr_degs_code outcome_status_code, shrdgmr_degc_code degree_code, shrdgmr_coll_code_1 college_code, shrdgmr_majr_code_1 major1_code, shrdgmr_majr_code_1_2 major2_code, shrdgmr_majr_code_1_2, shrdgmr_majr_code_minr_1 minor1_code, shrdgmr_majr_code_minr_1_2 minor2_code, shrdgmr_majr_code_conc_1 concentration_code, s1.stvmajr_desc major1_desc, s2.stvmajr_desc major2_desc, shrdgmr_program program_code from shrdgmr left join stvmajr s1 on shrdgmr_majr_code_1 = s1.stvmajr_code left join stvmajr s2 on shrdgmr_majr_code_1_2 = s2.stvmajr_code where shrdgmr_pidm = in_pidm and shrdgmr_term_code_grad = in_term and shrdgmr_degs_code in (select regexp_substr(in_codes,'[^,]+',1,level) from dual connect by regexp_substr(in_codes,'[^,]+',1,level)is not null); v_major_1 VARCHAR2 (100); v_major_2 VARCHAR2 (100); v_major_3 VARCHAR2 (100); v_majcount varchar2(100); v_priority varchar2(100); v_maj1 varchar2(100); v_maj1_txt varchar2(100); v_maj2 varchar2(100); v_maj2_txt varchar2(100); v_maj3 varchar2(100); v_maj3_txt varchar2(100); v_seqn number; v_maj01_text varchar2(100); v_maj02_text varchar2(100); v_maj03_text varchar2(100); v_deg_seq_no NUMBER; v_degc_code shrdgmr.shrdgmr_degc_code%TYPE; v_levl_code shrdgmr.shrdgmr_levl_code%TYPE; -------------------------------------------------------------------------------- CURSOR get_szcgta1_info(in_coll_code VARCHAR2) IS SELECT * FROM szcgta1 WHERE szcgta1_coll_code = in_coll_code; -------------------------------------------------------------------------------- cursor get_spriden_cur(in_pidm varchar2) is select spriden_first_name, spriden_mi, spriden_last_name from spriden where spriden_pidm = in_pidm and spriden_change_ind is null; spriden_record get_spriden_cur%rowtype; -------------------------------------------------------------------------------- cursor get_am_values_cur is select * from szcgta1 where szcgta1_levl_code = 'GR' and szcgta1_coll_code = 'AM' order by szcgta1_sort_order; -------------------------------------------------------------------------------- cursor c_get_ug_degr_rec_2(in_majr_code varchar2) is select szcdma1_degr_code, szcdma1_majr_code szcdma1_degr_title, szcdma1_majr_text, szcdma1_optn_text from szcdma1 where szcdma1_majr_code = in_majr_code; v_ug_degr_rec_2 c_get_ug_degr_rec_2%rowtype; -------------------------------------------------------------------------------- cursor c_at_hold(in_pidm varchar2) is select 'Y' as has_hold, sprhold_pidm, sprhold_hldd_code from sprhold where sprhold_pidm = in_pidm and sprhold_hldd_code = 'AT' and sysdate between sprhold_from_date and sprhold_to_date; v_at_hold_record c_at_hold%rowtype; -------------------------------------------------------------------------------- -- Variables -------------------------------------------------------------------------------- v_major_count number := 0; v_report_name VARCHAR2 (7) := 'szpgdip'; v_pidm VARCHAR2 (60); v_term_code VARCHAR2 (6 char); v_sysbeg TIMESTAMP; v_sysend TIMESTAMP; v_duration INTERVAL DAY TO SECOND; v_output_line VARCHAR2 (5000); v_commit BOOLEAN := TRUE; v_count NUMBER := 0; v_lp_1_ct NUMBER := 0; v_lp_2_ct NUMBER := 0; v_lp_3_ct NUMBER := 0; v_lp_4_ct NUMBER := 0; v_degree_count NUMBER; v_gov_name VARCHAR2 (60 char); v_gov_title VARCHAR2 (60 char); v_pres_name VARCHAR2 (60 char); v_pres_title VARCHAR2 (60 char); v_prov_name VARCHAR2 (60 char); v_prov_title VARCHAR2 (60 char); v_aum_chance_name VARCHAR2 (60 char); v_aum_chance_title VARCHAR2 (60 char); v_aum_provost_name VARCHAR2 (60 char); v_aum_provost_title VARCHAR2 (60 char); v_graduation_date date; v_grad_date_text varchar2 (100); v_parm_term VARCHAR2 (10); v_outcome_status VARCHAR2 (7); v_diploma_ind VARCHAR2 (7); v_diploma_name VARCHAR2 (60 char); v_option VARCHAR2 (500 char); v_coll_code_1 VARCHAR2 (4); v_coll_code_1_desc varchar2 (200 char); v_dean_name_1 szcgta1.szcgta1_signatory_name%type; v_dean_title_1 szcgta1.szcgta1_signatory_title%type; v_coll_desc_1 szcgta1.szcgta1_coll_desc%type; v_coll_code_2 VARCHAR2 (4); v_coll_code_2_desc varchar2(200 char); v_dean_name_2 szcgta1.szcgta1_signatory_name%type; v_dean_title_2 szcgta1.szcgta1_signatory_title%type; v_coll_desc_2 szcgta1.szcgta1_coll_desc%type; v_mail_ad_1 VARCHAR2 (60 char); v_mail_ad_2 VARCHAR2 (60 char); v_mail_ad_3 VARCHAR2 (60 char); v_mail_city VARCHAR2 (60 char); v_mail_state VARCHAR2 (60 char); v_mail_zip VARCHAR2 (60 char); v_mail_country VARCHAR2 (60 char); v_stu_email VARCHAR2 (60 char); v_stu_id VARCHAR2 (60 char); v_stu_fname spriden.spriden_first_name%type; v_stu_mname spriden.spriden_mi%type; v_stu_lname spriden.spriden_last_name%type; v_report_2 VARCHAR2 (15) := 'noformat'; quote VARCHAR2 (10) := '"'; comma VARCHAR2 (10) := '","'; excel_begin_delimiter VARCHAR2 (10) := '"=""'; excel_end_delimiter VARCHAR2 (10) := '"""'; v_parm_award_status VARCHAR2 (30); v_award_status VARCHAR2 (20); v_records_reported NUMBER; v_header_3 VARCHAR2 (1000); v_phone varchar2(100) := null; v_posthumus varchar2(100) := null; v_has_at_hold varchar2(10); v_mini_code varchar2(5) := null; -- BNRDEV21-416 rjm0056 v_degree_text varchar2(1000 char); v_first_name varchar2(1000 char); v_middle_name varchar2(1000 char); v_last_name varchar2(1000 char); v_sorlcur_levl sorlcur.sorlcur_levl_code%type; v_sorlcur_program sorlcur.sorlcur_program%type; v_sorlcur_aum_flag varchar2(10); -------------------------------------------------------------------------------- -- FUNCTIONS -------------------------------------------------------------------------------- function f_get_ug_degree_title(in_degr_code varchar2, in_majr_code varchar2) return varchar2 is v_degree_title varchar2(300); v_return_val varchar2(300); v_variable varchar2(100); v_line_out varchar2(1000); cursor c_get_ug_degree_title is select szcdma1_degr_code, szcdma1_majr_code, szcdma1_degr_title from szcdma1 where szcdma1_degr_code = in_degr_code and szcdma1_majr_code = in_majr_code; v_ug_degree_title_record c_get_ug_degree_title%rowtype; begin v_degree_title :=null; open c_get_ug_degree_title; fetch c_get_ug_degree_title into v_ug_degree_title_record; if (c_get_ug_degree_title%found) then v_degree_title :=v_ug_degree_title_record.szcdma1_degr_title; else v_degree_title :=null; end if; if (c_get_ug_degree_title%isopen) then close c_get_ug_degree_title; end if; v_return_val := null; v_return_val := v_degree_title; return v_return_val; exception when others then dbms_output.put_line('Error f_get_ug_degree_title on DEGREE CODE:['||in_degr_code ||']'); 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_ug_degree_title; --============================================================================== -- END Declarations --============================================================================== --============================================================================== -- BEGIN MAIN --============================================================================== BEGIN v_sysbeg := systimestamp; v_records_reported := 0; DBMS_OUTPUT.ENABLE (null); DBMS_OUTPUT.PUT_LINE ('BEGINNING OF SZPGDIP:[' || TO_CHAR (SYSDATE, 'dd-MON-yyyy - hh:mi:ss') || ']'); DBMS_OUTPUT.PUT_LINE ('-'); --============================================================================== -- Get Parms --============================================================================== v_parm_term := UPPER(gzpadmn.f_get_parm ('&1','&2','&4','01')); v_parm_award_status :=UPPER (gzpadmn.f_get_parm ('&1','&2','&4','02')); -- v_parm_term := '201610'; -- v_parm_award_status := 'AW'; v_term_code := v_parm_term; IF (v_parm_award_status = '%') THEN v_award_status := 'AP,AW,PN,CA'; --[IMM TASK0019538 (4)] ELSE v_award_status := v_parm_award_status; END IF; DBMS_OUTPUT.put_line('Parm-01: Graduation Term:[' || v_term_code || ']'); DBMS_OUTPUT.put_line('Parm-02: Outcome Status :['|| v_parm_award_status || '] parses to:['|| v_award_status|| ']'); v_header_3 := quote || 'Sig1' || comma || 'SigTitle1' || comma || 'Sig2' || comma || 'SigTitle2' || comma || 'Sig3' || comma || 'SigTitle3' || comma || 'Sig4' || comma || 'SigTitle4' || comma || 'Sig5' || comma || 'SigTitle5' || comma || 'SCHOOL CODE' || comma || 'SCHOOL' || comma || 'DEAN1' || comma || 'DEAN_TITLE1' || comma || 'SCHOOL2 CODE' || comma || 'SCHOOL2' || comma || 'DEAN2' || comma || 'DEAN_TITLE2' || comma || 'STUDENT_NAME' || comma || 'DEGREE CODE' || comma || 'DEGREE' || comma || 'MAJOR CODE' || comma || 'MAJOR' || comma || 'MAJOR 2' || comma || 'MAJOR 2 WORDING' || comma || 'MAJOR 3' || comma || 'MAJOR 3 WORDING' || comma || 'OPTION' || comma || 'HONOR1' || comma || 'HONOR2' || comma || 'POSTHUMOUS WORDING' || comma || 'GRADUATION_DATE' || comma || 'ADDRESS1' || comma || 'ADDRESS2' || comma || 'ADDRESS3' || comma || 'CITY' || comma || 'STATE' || comma || 'ZIP' || comma || 'COUNTRY' || comma || 'STUDENT EMAIL' || comma || 'TELEPHONE' || comma || 'STUDENT ID' || comma || 'MINI' || comma || 'DIPLOMA' || comma || 'CED' || comma || 'OUTCOME_STATUS' || comma || 'FIRST_NAME' || comma || 'MIDDLE_NAME' || comma || 'LAST_NAME' || comma || 'DOCUMENT TYPE' || quote; -- || 'AT_HOLD' || quote; gzpadmn.p_spool_lis ('&1','&2',null,null,v_header_3,v_commit); -- DBMS_OUTPUT.put_line(v_header_3); FOR official_record IN get_official LOOP if official_record.szcgta1_sort_order = 1 then v_gov_name := replace(official_record.szcgta1_signatory_name,chr(13),''); v_gov_title := replace(official_record.szcgta1_signatory_title,chr(13),''); elsif official_record.szcgta1_sort_order = 2 then v_pres_name := replace(official_record.szcgta1_signatory_name,chr(13),''); v_pres_title := replace(official_record.szcgta1_signatory_title,chr(13),''); elsif official_record.szcgta1_sort_order = 3 then v_prov_name := replace(official_record.szcgta1_signatory_name,chr(13),''); v_prov_title := replace(official_record.szcgta1_signatory_title,chr(13),''); end if; END LOOP; ------------------------------------------------------------------------------- --#1: Outermost LOOP Generate a list of students in UNIV-4AA0 -- Get non-degree related information such as email, name, etc. ------------------------------------------------------------------------------- FOR student_record in get_graduating_student(v_term_code) LOOP IF szgregs.f_get_grad_courses_flag(student_record.pidm,v_term_code, 'ENR') = 'Y' then v_count := v_count + 1; v_pidm := student_record.pidm; v_stu_id := null; v_stu_id := gb_common.f_get_id(v_pidm); v_stu_email := null; open get_email_address_cur(v_pidm); fetch get_email_address_cur into v_stu_email; if (get_email_address_cur%isopen) then close get_email_address_cur; end if; -- Get information about those who've registered for diploma. v_diploma_name := null; v_mail_ad_1 := null; v_mail_ad_2 := null; v_mail_ad_3 := null; v_mail_city := null; v_mail_state := null; v_mail_zip := null; v_mail_country := null; -- ----------------------------------------------------------------------------- -- Loop through SHRDGMR and get relevant information about each degree. -- ----------------------------------------------------------------------------- v_coll_code_1 := null; v_coll_code_1_desc := null; v_coll_code_2 := null; v_coll_code_2_desc := null; v_major_1 := NULL; v_major_2 := NULL; v_major_3 := NULL; v_option := NULL; v_deg_seq_no := NULL; v_degree_count := 0; v_outcome_status := NULL; v_diploma_ind := null; -------------------------------------------------------------------------------- -- Loop #2: Student could have more than one degree per term. -- For each pidm, get all the relevant records in SHRDGMR. -- That is, get all degrees which student is earning this semester. -- Get sequence number here to lookup in SORLCUR (next loop) -------------------------------------------------------------------------------- v_lp_2_ct := 0; for get_graduation_rec in get_graduation_cur(v_pidm, v_term_code, v_award_status) loop v_lp_2_ct := v_lp_2_ct + 1; v_deg_seq_no := get_graduation_rec.shrdgmr_seq_no; --dbms_output.put_line('681 v_deg_seq_no :[' || v_deg_seq_no ||']'); v_graduation_date := get_graduation_rec.shrdgmr_grad_date; v_grad_date_text := null; v_grad_date_text := trim(to_char(v_graduation_date,'fm Month dd, YYYY' )); -------------------------------------------------------------------------------- -- Loop #3 : For each degree record in SHRDGMR -- get all the relevant records from SORLCUR. -- Get LEVEL and DEGREE information here. -------------------------------------------------------------------------------- v_lp_3_ct := 0; for get_sorlcur_rec in get_sorlcur_cur(v_pidm, v_term_code, v_deg_seq_no) loop --dbms_output.put_line('816 v_deg_seq_no :[' || v_deg_seq_no ||'] pidm :['||v_pidm||']'); v_lp_3_ct := v_lp_3_ct + 1; v_sorlcur_levl := null; v_sorlcur_levl :=get_sorlcur_rec.sorlcur_levl_code; v_degc_code := null; v_degc_code := get_graduation_rec.shrdgmr_degc_code; --[IMM TASK0019538 (5)] if v_degc_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_diploma_name := v_szccert_record.szccert_certificate_name; v_mail_ad_1 := v_szccert_record.szccert_address_line1; v_mail_ad_2 := v_szccert_record.szccert_address_line2; v_mail_ad_3 := v_szccert_record.szccert_address_line3; v_mail_city := v_szccert_record.szccert_city; v_mail_state := v_szccert_record.szccert_state_code; v_mail_zip := v_szccert_record.szccert_zip; v_mail_country := v_szccert_record.szccert_natn_name; end if; if (c_szccert%isopen) then close c_szccert; end if; else v_diploma_ind := 'DIP'; open get_diploma_applicant(v_pidm,v_term_code); fetch get_diploma_applicant into v_diploma_applicant; if (get_diploma_applicant%found) then v_diploma_name := v_diploma_applicant.szcgadd_diploma_name; v_mail_ad_1 := v_diploma_applicant.szcgadd_address_line1; v_mail_ad_2 := v_diploma_applicant.szcgadd_address_line2; v_mail_ad_3 := v_diploma_applicant.szcgadd_address_line3; v_mail_city := v_diploma_applicant.szcgadd_city; v_mail_state := v_diploma_applicant.szcgadd_state_code; v_mail_zip := v_diploma_applicant.szcgadd_zip; v_mail_country := v_diploma_applicant.szcgadd_natn_name; end if; if (get_diploma_applicant%isopen) then close get_diploma_applicant; end if; end if; ------------------------------------- szcdde1_record := null; v_major_count := 0; v_major_1 := null; v_major_2 := null; v_major_3 := null; v_levl_code := null; v_degree_text :=null; v_coll_code_1 := null; v_sorlcur_program := null; v_sorlcur_program := get_sorlcur_rec.sorlcur_program; ------------------------------------------------------------------- -- StuDev #2475 Logic for allowing select program codes to get -- AUM signatures ------------------------------------------------------------------- v_sorlcur_aum_flag :=null; if (v_sorlcur_program in ('LA_PHD_PUBP')) then v_sorlcur_aum_flag := 'AUM'; else v_sorlcur_aum_flag :=null; end if; ------------------------------------------------------------------- --Populate Graduate School Values ------------------------------------------------------------------- IF (v_sorlcur_levl = 'GR') then v_coll_code_1 := 'GR'; v_coll_code_1_desc := f_get_desc_fnc('STVLEVL','GR',30); v_levl_code := get_sorlcur_rec.sorlcur_levl_code; v_deg_seq_no := get_graduation_rec.shrdgmr_seq_no; -- get Graduate degree wording -- Commented out [IMM TASK0019538 (6)] /* open get_degree_wording_gr_cur(v_degc_code); fetch get_degree_wording_gr_cur into szcdde1_record; if (get_degree_wording_gr_cur%isopen) then close get_degree_wording_gr_cur; end if; v_degree_text :=null; v_degree_text := szcdde1_record.szcdde1_degr_title; v_degree_text := regexp_replace(v_degree_text, chr(13)|| '$',''); v_degree_text := regexp_replace(v_degree_text, chr(10)|| '$',''); */ ELSE ------------------------------------------------------------------- --Populate Undergraduate Values ------------------------------------------------------------------- v_levl_code := get_sorlcur_rec.sorlcur_levl_code; v_deg_seq_no := get_graduation_rec.shrdgmr_seq_no; v_coll_code_1 := null; v_coll_code_1 := get_sorlcur_rec.sorlcur_coll_code; v_levl_code := NULL; v_levl_code := get_sorlcur_rec.sorlcur_levl_code; v_deg_seq_no := null; v_deg_seq_no := get_graduation_rec.shrdgmr_seq_no; END IF; -------------------------------------------------------------------------------- -- Loop #4 : For each record in SORLCUR get all the relevant records from SORLFOS -- One degree could have multiple majors. -- Now that you know LEVEL and DEGREE information, look in SORLFOS for MAJOR,Concentration etc. -- for each degree. However, for Undergraduates, the wording for degree -- is MAJOR specific. So determine degree information once you get the MAJOR. -------------------------------------------------------------------------------- v_lp_4_ct :=0; v_major_1 := null; v_major_2 := null; v_major_3 := null; v_maj01_text := null; v_maj02_text := null; v_maj03_text := null; /* dbms_output.put_line('780 v_pidm:[' || v_pidm || ']'); dbms_output.put_line('781 v_term_code:[' || v_term_code || ']'); dbms_output.put_line('782 get_sorlcur_rec.sorlcur_seqno:[' || get_sorlcur_rec.sorlcur_seqno || ']'); dbms_output.put_line('783 v_degc_code:[' || v_degc_code || ']'); */ v_seqn := null; v_seqn := get_sorlcur_rec.sorlcur_seqno; /* dbms_output.put_line('871 v_pidm:[' || v_pidm || '] v_stu_id:[' || v_stu_id || '] v_sorlcur_levl[' || v_sorlcur_levl || '] v_degc_code[' || v_degc_code || ']'); */ v_maj1 := null; v_maj1_txt := null; v_maj2 := null; v_maj2_txt := null; v_maj3 := null; v_maj3_txt := null; ------------------------------------------------------------------------------- --BEGIN --STUDEV #2525 Correct to capture Double Majors and Triple Majors ------------------------------------------------------------------------------- /* IF ( upper(v_sorlcur_levl) not in ('GR','ED') AND ( upper(v_degc_code) like 'B%') ) THEN*/ v_majcount := 0; v_priority := null; open c_student_majors(v_pidm, v_seqn); fetch c_student_majors into v_stu_majr_rec; if (c_student_majors%found) then v_majcount := v_stu_majr_rec.majcount; --[IMM TASK0019538 (7)] end if; if (c_student_majors%isopen) then close c_student_majors; end if; <> BEGIN Case v_majcount when 1 then v_maj1 := null; v_maj1_txt := null; v_specific_major_rec := null; v_variable := null; open c_get_priority(v_pidm, v_seqn); fetch c_get_priority into v_get_priority; if (c_get_priority%found) then v_priority := v_get_priority.priority; --[IMM TASK0019538 (7)] end if; if (c_get_priority%isopen) then close c_get_priority; end if; for major_row in c_specific_stu_major(v_pidm, v_seqn, v_priority) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec := major_row; end if; if v_specific_major_rec.sorlfos_pidm is null then if (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj1 := v_specific_major_rec.sorlfos_majr_code; v_maj1_txt := v_specific_major_rec.szcdma1_majr_text; /* v_maj1 := v_stu_majr_rec.sorlfos_majr_code; v_maj1_txt := null; v_maj1_txt := v_stu_majr_rec.szcdma1_majr_text; dbms_output.put_line ('Case 1: '||v_pidm); /* dbms_output.put_line('883 CASE 1 v_pidm:[' || v_pidm || '] v_stu_id:[' || v_stu_id || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_degc_code :['|| v_degc_code || '] v_maj1 :[' || v_maj1 || '] v_maj1_txt :[' || v_maj1_txt || ']'); */ when 2 then v_maj1 := null; v_maj1_txt := null; v_specific_major_rec := null; v_variable := null; for major_row in c_specific_stu_major(v_pidm, v_seqn, 1) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec :=major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj1 := v_specific_major_rec.sorlfos_majr_code; v_maj1_txt := v_specific_major_rec.szcdma1_majr_text; /* open c_specific_stu_major(v_pidm, v_seqn, v_degc_code, 1); fetch c_specific_stu_major into v_specific_major_rec; if (c_specific_stu_major%found) then v_maj1 := v_specific_major_rec.sorlfos_majr_code; v_maj1_txt := v_specific_major_rec.szcdma1_majr_text; if (c_specific_stu_major%isopen) then close c_specific_stu_major; end if; end if; */ v_maj2 := null; v_maj2_txt := null; v_specific_major_rec := null; v_variable := null; for major_row in c_specific_stu_major(v_pidm, v_seqn, 2) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj2 := v_specific_major_rec.sorlfos_majr_code; v_maj2_txt := v_specific_major_rec.szcdma1_majr_text; /* open c_specific_stu_major(v_pidm, v_seqn, v_degc_code, 2); fetch c_specific_stu_major into v_specific_major_rec; if (c_specific_stu_major%found) then v_maj2 := v_specific_major_rec.sorlfos_majr_code; v_maj2_txt := v_specific_major_rec.szcdma1_majr_text; end if; if (c_specific_stu_major%isopen) then close c_specific_stu_major; end if; dbms_output.put_line ('Case 2: '||v_pidm); dbms_output.put_line('922 CASE 2 v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_maj1 :[' || v_maj1 || '] v_maj2 :[' || v_maj2 || '] v_maj2_txt :[' || v_maj2_txt || ']'); */ when 3 then v_maj1 := null; v_maj1_txt := null; v_specific_major_rec := null; v_variable := null; for major_row in c_specific_stu_major(v_pidm, v_seqn, 1) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj1 := v_specific_major_rec.sorlfos_majr_code; v_maj1_txt := v_specific_major_rec.szcdma1_majr_text; /* open c_specific_stu_major(v_pidm, v_seqn, v_degc_code, 1); fetch c_specific_stu_major into v_specific_major_rec; if (c_specific_stu_major%found) then v_maj1 := v_specific_major_rec.sorlfos_majr_code; v_maj1_txt := v_specific_major_rec.szcdma1_majr_text; if (c_specific_stu_major%isopen) then close c_specific_stu_major; end if; end if; */ v_maj2 := null; v_maj2_txt := null; v_specific_major_rec := null; v_variable := null; for major_row in c_specific_stu_major(v_pidm, v_seqn, 2) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj2 := v_specific_major_rec.sorlfos_majr_code; v_maj2_txt := v_specific_major_rec.szcdma1_majr_text; /* open c_specific_stu_major(v_pidm, v_seqn, v_degc_code, 2); fetch c_specific_stu_major into v_specific_major_rec; if (c_specific_stu_major%found) then v_maj2 := v_specific_major_rec.sorlfos_majr_code; v_maj2_txt := v_specific_major_rec.szcdma1_majr_text; end if; if (c_specific_stu_major%isopen) then close c_specific_stu_major; end if; */ v_maj3 := null; v_maj3_txt := null; v_specific_major_rec := null; v_variable := null; for major_row in c_specific_stu_major(v_pidm, v_seqn, 3) loop if (major_row.szcdma1_degr_code = v_degc_code) then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'UG' and major_row.szcdma1_degr_code like 'B%') then v_specific_major_rec := major_row; elsif (major_row.szcdma1_degr_code <> v_degc_code and major_row.sorlcur_levl_code = 'GR' and major_row.szcdma1_degr_code not like 'B%' and major_row.szcdma1_degr_code not in ('GCRT','UCRT')) then v_specific_major_rec := major_row; else v_variable := major_row; end if; end loop; if v_specific_major_rec.sorlfos_pidm is null then v_specific_major_rec := v_variable; end if; v_maj3 := v_specific_major_rec.sorlfos_majr_code; v_maj3_txt := v_specific_major_rec.szcdma1_majr_text; /* open c_specific_stu_major(v_pidm, v_seqn, v_degc_code, 3); fetch c_specific_stu_major into v_specific_major_rec; if (c_specific_stu_major%found) then v_maj3 := v_specific_major_rec.sorlfos_majr_code; v_maj3_txt := v_specific_major_rec.szcdma1_majr_text; end if; if (c_specific_stu_major%isopen) then close c_specific_stu_major; end if; dbms_output.put_line ('Case 3: '||v_pidm); dbms_output.put_line('988 CASE 3 v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_maj3 :[' || v_maj3 || '] v_maj3_txt :[' || v_maj3_txt || ']'); */ else dbms_output.put_line('1200 CASE ELSE v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || ']'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); End Case; END CASE_MAJOR_COUNT; -- Cases with wording included in degree /* if v_degc_code in ('MAC', 'MCHE', 'MCE', 'MISE', 'MME', 'MNR') then v_maj1_txt := null; v_maj2_txt := null; v_maj3_txt := null; end if; */ -- END IF; ------------------------------------------------------------------------------- --END STUDEV #2525 ------------------------------------------------------------------------------- for get_major_rec in get_major_cur(v_pidm, v_term_code, get_sorlcur_rec.sorlcur_seqno, v_degc_code) loop v_lp_4_ct := v_lp_4_ct + 1; v_major_count := v_major_count + 1; --dbms_output.put_line('785 get_sorlcur_rec.sorlcur_seqno:[' || -- get_sorlcur_rec.sorlcur_seqno || ']'); CASE WHEN (v_major_count = 1) THEN v_major_1 := get_major_rec.sorlfos_majr_code; --dbms_output.put_line('785 v_major_count:[' || -- v_major_count || ']'); /* if (v_major_1 is not null) then --Commented out [IMM TASK0019538] case v_sorlcur_levl when 'GR' then null; when 'ED' then null; else */ -------------------------------------------------------------------------------- -- UNDERGRADUATE -- 1. Populate DEGREE text. -- 2. Populate MAJOR information. -------------------------------------------------------------------------------- v_degree_text := null; open c_get_ug_degree_info(v_degc_code, v_major_1); fetch c_get_ug_degree_info into v_ug_degree_record; if (c_get_ug_degree_info%found) then v_degree_text := v_ug_degree_record.szcdma1_degr_title; end if; if (c_get_ug_degree_info%isopen) then close c_get_ug_degree_info; -- v_maj01_text := null; -- v_maj01_text := v_ug_degree_record.szcdma1_majr_text; end if; --end case; --end if; /* if (v_degree_text is null) then open get_degree_wording_gr_cur(v_degc_code); fetch get_degree_wording_gr_cur into szcdde1_record; if (get_degree_wording_gr_cur%found) then v_degree_text := szcdde1_record.szcdde1_degr_title; v_degree_text := regexp_replace(v_degree_text, chr(13)|| '$',''); v_degree_text := regexp_replace(v_degree_text, chr(10)|| '$',''); end if; if (get_degree_wording_gr_cur%isopen) then close get_degree_wording_gr_cur; end if; end if; */ when (v_major_count = 2 ) then v_major_2 := get_major_rec.sorlfos_majr_code; v_dma1_degr_title := get_major_rec.szcdma1_degr_title; -- dbms_output.put_line('819 v_degc_code:[' || v_degc_code || ']'); -------------------------------------------------------------------------------- -- 2nd Major Information -------------------------------------------------------------------------------- if (v_major_2 is not null) then case v_sorlcur_levl -- v_coll_code_1 when 'GR' Then v_maj02_text := null; when 'ED' Then v_maj02_text := null; else open c_get_ug_degree_info(v_degc_code, v_major_2); fetch c_get_ug_degree_info into v_ug_degree_record; if (c_get_ug_degree_info%isopen) then close c_get_ug_degree_info; v_maj02_text := null; v_maj02_text := v_ug_degree_record.szcdma1_majr_text; end if; end case; open c_get_ug_degree_info(v_degc_code, v_major_2); fetch c_get_ug_degree_info into v_ug_degree_record; if (c_get_ug_degree_info%isopen) then close c_get_ug_degree_info; end if; v_maj02_text := null; v_maj02_text := v_ug_degree_record.szcdma1_majr_text; v_maj02_text := null; open c_get_ug_degr_rec_2(v_major_2); fetch c_get_ug_degr_rec_2 into v_ug_degr_rec_2; close c_get_ug_degr_rec_2; v_maj02_text := v_ug_degr_rec_2.szcdma1_majr_text; end if; -------------------------------------------------------------------------------- -- 3rd Major Information -------------------------------------------------------------------------------- when (v_major_count = 3) then v_major_3 := get_major_rec.sorlfos_majr_code; v_dma1_degr_title := get_major_rec.szcdma1_degr_title; if (v_major_3 is not null) then case v_sorlcur_levl when 'GR' then v_maj03_text := null; when 'ED' then v_maj03_text := null; else open c_get_ug_degree_info(v_degc_code, v_major_3); fetch c_get_ug_degree_info into v_ug_degree_record; if (c_get_ug_degree_info%isopen) then close c_get_ug_degree_info; v_maj03_text := null; v_maj03_text := v_ug_degree_record.szcdma1_majr_text; end if; end case; v_maj03_text := null; open c_get_ug_degr_rec_2(v_major_3); fetch c_get_ug_degr_rec_2 into v_ug_degr_rec_2; close c_get_ug_degr_rec_2; v_maj03_text := v_ug_degr_rec_2.szcdma1_majr_text; end if; end case; v_degree_text := regexp_replace(v_degree_text, chr(13)|| '$',''); v_degree_text := regexp_replace(v_degree_text, chr(10)|| '$',''); end loop; --Loop #4 --GR degrees not in SZCDMAJ if (v_degree_text is null) then open get_degree_wording_gr_cur(v_degc_code); fetch get_degree_wording_gr_cur into szcdde1_record; if (get_degree_wording_gr_cur%found) then v_degree_text := szcdde1_record.szcdde1_degr_title; v_degree_text := regexp_replace(v_degree_text, chr(13)|| '$',''); v_degree_text := regexp_replace(v_degree_text, chr(10)|| '$',''); end if; if (get_degree_wording_gr_cur%isopen) then close get_degree_wording_gr_cur; end if; end if; v_outcome_status := get_graduation_rec.shrdgmr_degs_code; -- Get description of degree v_degree_awarded := NULL; OPEN get_degree_awarded (v_degc_code); FETCH get_degree_awarded INTO v_degree_award_record; IF (get_degree_awarded%FOUND) THEN v_degree_awarded := v_degree_award_record.stvdegc_desc; END IF; CLOSE get_degree_awarded; -- Get dean's name v_dean_name_1 := NULL; v_dean_title_1 := NULL; v_dean_name_2 := NULL; v_dean_title_2 := NULL; szcdsi1_record := null; -- don't even look for szcdsi1 information if they are graduate if (get_sorlcur_rec.sorlcur_levl_code != 'GR') then open get_two_sigs_cur( v_major_1); fetch get_two_sigs_cur into szcdsi1_record; close get_two_sigs_cur; if szcdsi1_record.szcdsi1_prog_code is null then open get_two_sigs_cur(v_major_2); fetch get_two_sigs_cur into szcdsi1_record; close get_two_sigs_cur; end if; if szcdsi1_record.szcdsi1_prog_code is null then open get_two_sigs_cur( v_major_3); fetch get_two_sigs_cur into szcdsi1_record; close get_two_sigs_cur; end if; end if; if szcdsi1_record.szcdsi1_prog_code is null then OPEN get_dean (v_coll_code_1); FETCH get_dean INTO v_dean_record; IF (get_dean%FOUND) THEN v_dean_name_1 := replace(v_dean_record.szcgta1_signatory_name,chr(13),''); v_dean_title_1 := replace(v_dean_record.szcgta1_signatory_title,chr(13),''); v_coll_code_1_desc := v_dean_record.szcgta1_coll_desc; END IF; CLOSE get_dean; else v_coll_code_1 := szcdsi1_record.szcdsi1_coll_code_1; OPEN get_dean (szcdsi1_record.szcdsi1_coll_code_1); FETCH get_dean INTO v_dean_record; IF (get_dean%FOUND) THEN v_dean_name_1 := v_dean_record.szcgta1_signatory_name; v_dean_title_1 := v_dean_record.szcgta1_signatory_title; v_coll_code_1_desc := v_dean_record.szcgta1_coll_desc; END IF; CLOSE get_dean; v_dean_record := null; v_coll_code_2 := szcdsi1_record.szcdsi1_coll_code_2; OPEN get_dean (szcdsi1_record.szcdsi1_coll_code_2); FETCH get_dean INTO v_dean_record; IF (get_dean%FOUND) THEN v_dean_name_2 := v_dean_record.szcgta1_signatory_name; v_dean_title_2 := v_dean_record.szcgta1_signatory_title; v_coll_code_2_desc := v_dean_record.szcgta1_coll_desc; END IF; CLOSE get_dean; end if; -- Get institutional honors student earned v_inst_honor_desc := NULL; OPEN get_inst_honors(v_pidm, v_deg_seq_no); FETCH get_inst_honors INTO v_inst_honors_record; IF (get_inst_honors%FOUND) THEN v_inst_honor_desc := --'(' || v_inst_honors_record.inst_honors_desc; --[IMM TASK0019538 (8)] --|| ')'; END IF; CLOSE get_inst_honors; -- Get departmental honors student earned v_dept_honor_desc := NULL; OPEN get_dept_honors (v_pidm, v_deg_seq_no); FETCH get_dept_honors INTO v_dept_honors_record; IF (get_dept_honors%FOUND) THEN v_dept_honor_desc := --'(' || v_dept_honors_record.dept_honors_desc; --[IMM TASK0019538 (8)] --|| ')'; END IF; CLOSE get_dept_honors; spriden_record := null; open get_spriden_cur(v_pidm); fetch get_spriden_cur into spriden_record; close get_spriden_cur; v_first_name := null; v_first_name := spriden_record.spriden_first_name; v_middle_name := null; v_middle_name := spriden_record.spriden_mi; v_last_name := null; v_last_name := spriden_record.spriden_last_name; szcdma1_record1 := null; -- get degree/major wording open get_degree_wording_cur(v_major_1); fetch get_degree_wording_cur into szcdma1_record1; close get_degree_wording_cur; szcdma1_record2 := null; -- get degree/major wording open get_degree_wording_cur(v_major_2); fetch get_degree_wording_cur into szcdma1_record2; close get_degree_wording_cur; szcdma1_record3 := null; -- get degree/major wording open get_degree_wording_cur(v_major_3); fetch get_degree_wording_cur into szcdma1_record3; close get_degree_wording_cur; v_option := szcdma1_record1.szcdma1_optn_text || ' ' || szcdma1_record2.szcdma1_optn_text || ' ' || szcdma1_record3.szcdma1_optn_text; v_option := ltrim(v_option); v_option := replace(v_option,chr(13),''); v_aum_chance_title := null; v_aum_chance_name := null; v_aum_provost_name := null; v_aum_provost_title := null; if ((v_degc_code = 'MSN' AND v_maj1 in ('NEDC','NINF','NHSL','NUPC')) OR v_sorlcur_aum_flag = 'AUM') --[IMM TASK0019538 (9)] then -- dbms_output.put_line('1043 v_pidm:[' || gb_common.f_get_id(v_pidm) || ']'); -- dbms_output.put_line('1043 v_sorlcur_program:[' || v_sorlcur_program || ']'); for get_am_values_rec in get_am_values_cur loop if get_am_values_rec.szcgta1_sort_order = 1 then v_aum_chance_name := get_am_values_rec.szcgta1_signatory_name; v_aum_chance_name := regexp_replace(v_aum_chance_name, chr(13),''); v_aum_chance_name := regexp_replace(v_aum_chance_name, chr(10),''); v_aum_chance_title := get_am_values_rec.szcgta1_signatory_title; v_aum_chance_title:= regexp_replace(v_aum_chance_title, chr(13),''); v_aum_chance_title:= regexp_replace(v_aum_chance_title, chr(10),''); elsif get_am_values_rec.szcgta1_sort_order = 2 then v_aum_provost_name := get_am_values_rec.szcgta1_signatory_name; v_aum_provost_name:= regexp_replace(v_aum_provost_name, chr(13),''); v_aum_provost_name:= regexp_replace(v_aum_provost_name, chr(10),''); v_aum_provost_title := get_am_values_rec.szcgta1_signatory_title; v_aum_provost_title:= regexp_replace(v_aum_provost_title, chr(13),''); v_aum_provost_title:= regexp_replace(v_aum_provost_title, chr(10),''); end if; end loop; v_coll_code_2 := 'AUM'; end if; if v_coll_code_1 = 'GR' then v_coll_code_1_desc := 'Graduate School'; end if; /* if v_coll_code_1 != 'GR' then v_coll_code_1_desc := F_GET_DESC_FNC('STVCOLL',v_coll_code_1,30); end if */ case v_coll_code_2 when 'AUM' then v_coll_code_2_desc := 'Auburn University Montgomery'; when 'GR' then v_coll_code_2_desc := 'Graduate School'; else null; end case; v_has_at_hold := null; open c_at_hold(v_pidm); fetch c_at_hold into v_at_hold_record; if (c_at_hold%found) then v_has_at_hold := v_at_hold_record.has_hold; else v_has_at_hold := 'N'; end if; if (c_at_hold%isopen) then close c_at_hold; end if; /* if ( v_major_1 <> v_maj1 ) then dbms_output.put_line('1242 v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_major_1 :[' || v_major_1 || '] v_maj1 :[' || v_maj1 || ']'); end if; if ( v_major_2 <> v_maj2 ) then dbms_output.put_line('1250 v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_major_2 :[' || v_major_2 || '] v_maj2 :[' || v_maj2 || ']'); end if; if ( v_major_3 <> v_maj3 ) then dbms_output.put_line('1250 v_pidm:[' || v_pidm || '] seqno :[' || v_seqn || '] v_majcount :[' || v_majcount || '] v_major_3 :[' || v_major_3 || '] v_maj3 :[' || v_maj3 || ']'); end if; */ --============================================================================== -- SZCDMA1 has two records where SZCDMA1_MAJR_CODE=NURS -- It only happens for SZCDMA1_MAJR_CODE=NURS. -- This causes the cursors that inner join SORLFOS with SZCDMA1 to return -- duplicate rows for those students having only one record in SORLFOS. -- This causes MAJOR2, and possibly MAJOR3, to populate for students with only -- one relevant record in SORLFOS. -- Solution: Just null the values for MAJ2 and MAJ3 for students whose MAJOR is NURS --============================================================================== if (v_maj1 = 'NURS' and v_maj2 = 'NURS') then v_maj2 := null; v_maj2_txt := null; end if; if (v_maj1 = 'NURS' and v_maj3 = 'NURS') then v_maj3 := null; v_maj3_txt := null; end if; --============================================================================= -- BNRDEV21-416 rjm0056 --============================================================================= v_mini_code := NULL; IF v_degc_code IN ('GCRT', 'UCRT') THEN v_mini_code := 'NO'; Else v_mini_code := 'YES'; END IF; --============================================================================== -- Pure csv file, contains no special formatting for leading zeros. --============================================================================== v_output_line := quote || v_gov_name || comma -- A || v_gov_title || comma -- B || v_prov_name || comma -- C || v_prov_title || comma -- D || v_pres_name || comma -- E || v_pres_title || comma -- F || v_aum_chance_name || comma -- G || v_aum_chance_title || comma -- H || v_aum_provost_name || comma -- I || v_aum_provost_title || comma -- J || v_coll_code_1 || comma -- K || v_coll_code_1_desc || comma -- L || v_dean_name_1 || comma -- M || v_dean_title_1 || comma -- N || v_coll_code_2 || comma -- O || v_coll_code_2_desc || comma -- P || v_dean_name_2 || comma -- Q || v_dean_title_2 || comma -- R || v_diploma_name || comma -- S || v_degc_code || comma -- T || v_degree_text || comma -- U --|| v_major_1 || comma --V -- || v_maj01_text || comma --W || v_maj1 || comma --V || v_maj1_txt || comma --W -- || v_major_2 || comma --X -- || v_maj02_text || comma --Y || v_maj2 || comma --X || v_maj2_txt || comma --Y -- || v_major_3 || comma --Z -- || v_maj03_text || comma --A || v_maj3 || comma --Z || v_maj3_txt || comma --A || v_option || comma -- AB || v_inst_honor_desc || comma -- AC || v_dept_honor_desc || comma -- AD || v_posthumus || comma -- AE Leave blank for Registrar || v_grad_date_text || comma --AF || v_mail_ad_1 || comma --AG || v_mail_ad_2 || comma --AH || v_mail_ad_3 || comma --AI || v_mail_city || comma --AJ || v_mail_state || comma --AK || v_mail_zip || comma --AL || v_mail_country || comma --AM || v_stu_email || comma --AN || v_phone || comma --AO Leave blank for Registrar || v_stu_id || comma --AP || v_mini_code || comma --AQ -- BNRDEV21-416 rjm0056 || 'YES' || comma --AR || 'YES' || comma --AS -- BNRDEV21-416 rjm0056 || v_outcome_status || comma --AT || v_first_name || comma --AU || v_middle_name || comma --AV || v_last_name || comma --AW || v_diploma_ind || quote; --AX -- || v_last_name || comma --AW -- || v_has_at_hold || quote; --AX v_output_line := regexp_replace(v_output_line,chr(13),''); v_output_line := regexp_replace(v_output_line,chr(10),''); gzpadmn.p_spool_lis ('&1','&2',null,null,v_output_line,v_commit); --DBMS_OUTPUT.put_line(v_output_line); v_records_reported := v_records_reported + 1; END LOOP; -- sorlcur record Loop #3 END LOOP; -- degree record Loop #2 ELSE continue; END IF; --UNIV Grad courses END LOOP; -- student record Loop #1 DBMS_OUTPUT.put_line ('Records processed:[' || v_count || ']'); DBMS_OUTPUT.put_line ('Records in report:[' || v_records_reported || ']'); DBMS_OUTPUT.PUT_LINE ('-'); DBMS_OUTPUT.PUT_LINE (v_report_name || ' complete.'); DBMS_OUTPUT.PUT_LINE ('-'); v_sysend := systimestamp; 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 || ']'); EXCEPTION WHEN OTHERS THEN --verrmsg := SQLERRM; --verrcode := SQLCODE; DBMS_OUTPUT.PUT_LINE ('Error found.' || quote || SQLERRM); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); END; / EXIT SUCCESS;