--============================================================================== -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPADVR.SQL -- AUTHOR: Rodney Clark -- DATE WRITTEN: 10/16/13 -- INPUT: -- GZAMIGR JOB DESC: ADVISOR REPORT -- DESCRIPTION: Process to set Advisors. -- REVISION HISTORY -- WHEN WHO WHAT -- 03/02/2015 MC Ticket #1177 Added % for College parm; -- Added Alt Pin and CATR, CATI, CATC columns; -- Excluded graduated students from the report -- 08/28/2015 FCN Ticket #1445 Add: 5 new Columns -- ADMIT_TYPE, ADMIT_TERM, EARN_HRS, TFER_EARN_HRS, CUM_EARN_HRS -- 05/26/2016 LZ Ticket #1676 Not taking out graduated students -- 03/03/2016 LZ Ticket #1872 Optimization to reduce running time in QUAL -- 03/26/2017 DTM Ticket #2095 Update such that: -- 1. Excludes students that have graduated and are no longer enrolled as an undergraduate -- 2. Give all column information even though they may lack an advisor assignment -- 03/26/2017 RGC Ticket 2095 - We have been asked to exclude graduated students again --============================================================================== --SET SCAN OFF; set serveroutput ON SIZE UNLIMITED; SET echo OFF; SET verify OFF; SET feedback OFF; SET tab OFF; DECLARE ------------------------------------------------------------------------------- -- CURSORS ------------------------------------------------------------------------------- CURSOR get_students_cur(v_term varchar2, v_college varchar2, v_levl varchar2) IS SELECT DISTINCT(person_uid) FROM szvcurr WHERE academic_period IN (v_term, szg8001.f_get_prior_term(v_term, 1), szg8001.f_get_prior_term(v_term, 2)) AND current_curriculum = 'Y' and ENROLLED_IND = 'Y' AND registered_ind = 'Y' -- and person_uid = gb_common.f_get_pidm(903349262) -- and person_uid = 3293632 AND student_level LIKE (v_levl) AND (v_college = '%' OR college = v_college) -- Changes for #2095 AND NOT (graduated_ind = 'Y' and enrolled_ind = 'N') -- End Changes for #2095 -- Not taking out graduated students by LZ on 5/26/2016 -- we have been asked to remove graduated students again RGC 6/12/2018 AND NOT EXISTS ( SELECT 'x' FROM shrdgmr A WHERE shrdgmr_pidm = person_uid AND shrdgmr_term_code_grad = (SELECT max(shrdgmr_term_code_grad) FROM shrdgmr b WHERE A.shrdgmr_pidm = b.shrdgmr_pidm AND A.shrdgmr_levl_code = b.shrdgmr_levl_code AND b.shrdgmr_term_code_grad < v_term) AND shrdgmr_degs_code = 'AW' AND shrdgmr_levl_code = student_level AND (v_college = '%' OR shrdgmr_coll_code_1 LIKE(v_college) OR shrdgmr_coll_code_2 IN (v_college)) ) -- Taking out the graduated students is not how SSB works. There is a possibility that an advior will be assigned a student, but they would not show up on the report. ; --============================================================================= CURSOR get_reg_students_cur(v_pidm varchar2, v_term varchar2, v_levl varchar2, v_college varchar2) IS SELECT * FROM szvcurr A WHERE person_uid = v_pidm AND current_curriculum = 'Y' AND academic_period = v_term and (v_college = '%' OR college = v_college) AND NOT EXISTS ( SELECT 'x' FROM shrdgmr WHERE shrdgmr_pidm = person_uid AND shrdgmr_term_code_grad = academic_period AND shrdgmr_degs_code = 'AW' AND shrdgmr_levl_code = student_level AND (v_college = '%' OR shrdgmr_coll_code_1 LIKE(v_college) OR shrdgmr_coll_code_2 IN (v_college))) ; szvcurr_record szvcurr%ROWTYPE; --============================================================================ CURSOR number_programs_cur(v_pidm varchar2, v_term varchar2, v_levl varchar2) IS SELECT count(*) v_count FROM szvcurr WHERE academic_period = v_term AND person_uid = v_pidm AND current_curriculum = 'Y' AND student_level LIKE (v_levl); --============================================================================ CURSOR take_out_none_college_cur( v_pidm varchar2, v_term varchar2, v_college varchar2, v_levl varchar2) IS SELECT * FROM szvcurr A WHERE A.person_uid = v_pidm AND academic_period = v_term AND current_curriculum = 'Y' -- AND registered_ind = 'Y' -- AND(college != 'LA' OR student_level NOT LIKE (v_levl)) AND(college != v_college OR student_level NOT LIKE (v_levl)) AND NOT EXISTS (SELECT 'x' FROM szvcurr c WHERE c.person_uid = v_pidm --LZ 3/3/2017 Optimization AND academic_period = A.academic_period AND student_level LIKE (v_levl) AND current_curriculum = 'Y' AND registered_ind = 'Y' AND (v_college = '%' OR college = v_college)); --============================================================================= CURSOR get_advisors_cur(v_pidm varchar2, v_term varchar2, v_pidm_a varchar2) IS SELECT * FROM sgradvr A WHERE sgradvr_pidm = v_pidm AND sgradvr_term_code_eff = (SELECT max(sgradvr_term_code_eff) FROM sgradvr b WHERE A.sgradvr_pidm = b.sgradvr_pidm AND b.sgradvr_term_code_eff <= v_term) AND sgradvr_advr_pidm LIKE (v_pidm_a); --============================================================================= CURSOR get_spriden_cur(v_pidm varchar2) IS SELECT * FROM spriden WHERE spriden_pidm = v_pidm AND spriden_change_ind IS NULL; spriden_record spriden%ROWTYPE; --============================================================================ CURSOR get_gpa_cur(v_pidm varchar2, v_levl varchar2) IS SELECT shrlgpa_gpa FROM shrlgpa WHERE shrlgpa_gpa_type_ind = 'I' AND shrlgpa_levl_code = v_levl AND shrlgpa_pidm = v_pidm; --============================================================================= CURSOR get_veteran_cur(v_pidm varchar2) IS SELECT * FROM sgrvetn A WHERE sgrvetn_vetc_code IS NOT NULL AND sgrvetn_vetc_code NOT IN ('X','Y') AND sgrvetn_pidm = v_pidm AND sgrvetn_term_code_va = (SELECT max(b.sgrvetn_term_code_va) FROM sgrvetn b WHERE A.sgrvetn_pidm = b.sgrvetn_pidm AND A.sgrvetn_vetc_code IS NOT NULL AND A.sgrvetn_vetc_code NOT IN ('X','Y')); --============================================================================ CURSOR get_sport_cur(v_pidm varchar2, v_term varchar2) IS SELECT * FROM sgrsprt WHERE sgrsprt_term_code = v_term AND sgrsprt_actc_code NOT IN ('XX', 'XA') AND sgrsprt_pidm = v_pidm; --============================================================================= CURSOR get_email_cur(v_pidm varchar2) IS SELECT goremal_email_address FROM goremal WHERE goremal_emal_code = 'AU' AND goremal_status_ind = 'A' AND goremal_pidm = v_pidm; --============================================================================= ------------------------------------------------------------------------------- -- REFERENCE CURSOR: -- Get employee dept information -------------------------------------------------------------------------------- CURSOR get_employee_dept_cur(v_pidm varchar2) IS SELECT peb.pebempl_pidm pidm, peb.pebempl_jbln_code jbln_code, ptr.ptrjbln_desc jbln_desc, ptr.ptrjbln_dicd_code dicd_code, gtv.gtvdicd_code gtv_dicd_code, gtv.gtvdicd_desc gtv_desc FROM pebempl peb INNER JOIN ptrjbln ptr ON peb.pebempl_jbln_code = ptr.ptrjbln_code INNER JOIN gtvdicd gtv ON ptr.ptrjbln_dicd_code = gtv.gtvdicd_code WHERE peb.pebempl_pidm= v_pidm; employee_record get_employee_dept_cur%ROWTYPE; --============================================================================ CURSOR get_cater_cur(v_pidm varchar2, v_term varchar2) IS SELECT * FROM sgrsact WHERE upper(sgrsact_actc_code) IN ('CATC', 'CATI', 'CATR' ) AND sgrsact_pidm = v_pidm AND sgrsact_term_code <= v_term ORDER BY sgrsact_term_code desc, sgrsact_actc_code ; --============================================================================= -- VARIABLES --============================================================================= v_text varchar2(2000); v_term stvterm.stvterm_code%TYPE; v_college varchar2(10); v_pidm_inp varchar2(9) := '%'; -- pidm from the person added from parameters v_id varchar2(9); v_level varchar2(4); v_gpa shrlgpa.shrlgpa_gpa%TYPE; v_veteran varchar2(1) := 'N'; v_num_programs number := 0; v_program_count number := 0; v_sport varchar2(1) := 'N'; v_email goremal.goremal_email_address%TYPE; v_count number := 0; v_has_advisor varchar2(1) := 'N'; v_non_college varchar2(1) := 'N'; v_pidm number; v_stu_pidm number; v_adv_pidm number; v_line_out varchar2(2000); f1 varchar2(1) := '"'; f2 varchar2(3) := '","'; f3 varchar2(3) := ','; v_spool_commit BOOLEAN :=FALSE; verrcode NUMBER; --holds SQLCODE in case of error v_ssb_flag varchar2(2); v_pin_message varchar2(200); v_catr_term sgrsact.sgrsact_term_code%TYPE; v_cati_term sgrsact.sgrsact_term_code%TYPE; v_catc_term sgrsact.sgrsact_term_code%TYPE; v_levl_code varchar2(10); v_admt_code varchar2(10); v_admt_term varchar2(10); v_aubn_earn_hrs varchar2(10); v_tfer_earn_hrs varchar2(10); v_cumu_earn_hrs varchar2(10); v_sysbeg TIMESTAMP; v_sysend TIMESTAMP; v_duration INTERVAL DAY TO SECOND; v_tally number; --============================================================================= -- PROCEDURES --============================================================================= PROCEDURE add_programs( v_pidm varchar2, v_term varchar2, v_levl varchar2, v_text IN OUT varchar2) IS CURSOR get_priority_cur IS SELECT * FROM szvcurr WHERE academic_period = v_term AND person_uid = v_pidm AND current_curriculum = 'Y' AND student_level LIKE (v_levl); v_curriculum_num number := 0; BEGIN FOR get_priority_rec IN get_priority_cur LOOP v_text := v_text || get_priority_rec.college || f2 || get_priority_rec.major || f2 || get_priority_rec.second_major || f2 || get_priority_rec.first_minor || f2 || get_priority_rec.second_minor || f2 || get_priority_rec.first_concentration || f2 || get_priority_rec.PROGRAM || f2; v_curriculum_num := v_curriculum_num + 1; END LOOP; IF v_curriculum_num <= 1 THEN v_text := v_text || f2 || f2 || f2 || f2 || f2 || f2 || f2; END IF; END add_programs; --============================================================================= -- FUNCTIONS --============================================================================= -- Example Usage: -- f_get_earned_hrs(pidm,'UG','I',parm_term,'XXX') -- Returns 'I'nsitutional hours attempted. -- f_get_earned_hrs(pidm,'UG','T',parm_term,'XXX') -- Returns 'T'ransfer hours attempted. -- f_get_earned_hrs(pidm,'UG','%',parm_term,'CUM') -- Returns Total Hours Earned (Institutional + Transfer) --============================================================================= function f_get_earned_hours(in_pidm varchar2, in_levl varchar2, in_type varchar2, in_term varchar2, in_code varchar2) return varchar2 is v_levl_cum_hrs number; v_earned_hours number; v_return_val varchar2(50); v_error_line varchar2(2000); cursor c_get_shrtgpa_hours is select shrtgpa_pidm, shrtgpa_term_code, shrtgpa_levl_code, shrtgpa_gpa_type_ind, shrtgpa_hours_attempted, shrtgpa_hours_earned, type_cum_earned_hrs, levl_cum_earned_hrs, row_no from ( select shrtgpa_pidm, shrtgpa_term_code, shrtgpa_levl_code, shrtgpa_gpa_type_ind, shrtgpa_hours_attempted, shrtgpa_hours_earned, sum(shrtgpa_hours_earned) over (partition by shrtgpa_pidm, shrtgpa_levl_code, shrtgpa_gpa_type_ind order by shrtgpa_term_code) as type_cum_earned_hrs, sum(shrtgpa_hours_earned) over (partition by shrtgpa_pidm, shrtgpa_levl_code) as levl_cum_earned_hrs, row_number() over (partition by shrtgpa_pidm, shrtgpa_levl_code, shrtgpa_gpa_type_ind order by shrtgpa_term_code desc ) as row_no from shrtgpa where shrtgpa_pidm = in_pidm and shrtgpa_term_code <= in_term ) where (shrtgpa_gpa_type_ind = in_type OR in_type = '%') and shrtgpa_levl_code = in_levl and row_no = 1; v_shrtgpa_record c_get_shrtgpa_hours%rowtype; begin v_return_val := null; v_earned_hours := null; v_levl_cum_hrs := null; open c_get_shrtgpa_hours; fetch c_get_shrtgpa_hours into v_shrtgpa_record; if (c_get_shrtgpa_hours%found) then v_earned_hours := v_shrtgpa_record.type_cum_earned_hrs; v_levl_cum_hrs := v_shrtgpa_record.levl_cum_earned_hrs; else v_earned_hours := 0; v_levl_cum_hrs := 0; end if; close c_get_shrtgpa_hours; case in_code when 'CUM' then v_return_val := to_char(v_levl_cum_hrs); else v_return_val := to_char(v_earned_hours); end case; return v_return_val; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('423: Error in f_get_earned_hours on pidm:[' || in_pidm || ']'); dbms_output.put_line('424:SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('425:SQLERRM:[' || SQLERRM || ']'); v_error_line := dbms_utility.format_error_backtrace; dbms_output.put_line('425:ERROR BACKTRACE:[' ||v_error_line || ']'); v_error_line := dbms_utility.format_call_stack; dbms_output.put_line('425:ERROR CALL STACK:[' ||v_error_line || ']'); RAISE; end f_get_earned_hours; --============================================================================= function f_get_max_sorlcur(in_pidm varchar2, in_code varchar2) return varchar2 is v_return_val varchar2(10); v_coll_code varchar2(10); v_levl_code varchar2(10); v_admt_code varchar2(10); v_admt_term varchar2(10); cursor get_max_sorlcur is select sorlcur_pidm, sorlcur_seqno, sorlcur_term_code_admit, sorlcur_priority_no, sorlcur_cact_code, sorlcur_levl_code, sorlcur_coll_code, sorlcur_admt_code from ( select sorlcur_pidm, sorlcur_seqno, sorlcur_term_code_admit, sorlcur_priority_no, sorlcur_cact_code, sorlcur_levl_code, sorlcur_coll_code, sorlcur_admt_code, rank() over (partition by sorlcur_pidm order by sorlcur_term_code desc, sorlcur_seqno desc) as ranking from sorlcur where sorlcur_pidm = in_pidm and sorlcur_current_cde='Y' and sorlcur_lmod_code ='LEARNER' and sorlcur_cact_code ='ACTIVE') where ranking=1; v_sorlcur_record get_max_sorlcur%rowtype; begin v_coll_code := null; v_levl_code := null; v_admt_code := null; v_admt_term := null; open get_max_sorlcur; fetch get_max_sorlcur into v_sorlcur_record; if (get_max_sorlcur%found) then v_coll_code := v_sorlcur_record.sorlcur_coll_code; v_levl_code := v_sorlcur_record.sorlcur_levl_code; v_admt_code := v_sorlcur_record.sorlcur_admt_code; v_admt_term := v_sorlcur_record.sorlcur_term_code_admit; else v_coll_code := null; v_levl_code := null; v_admt_code := null; v_admt_term := null; end if; close get_max_sorlcur; case in_code when 'COLL' then v_return_val := v_coll_code; when 'LEVL' then v_return_val := v_levl_code; when 'ADMT' then v_return_val := v_admt_code; when 'TERM' then v_return_val := v_admt_term; else v_return_val := null; end case; return v_return_val; end f_get_max_sorlcur; --============================================================================== --============================================================================== -- BEGIN MAIN --============================================================================== BEGIN v_sysbeg := systimestamp; v_term := UPPER(gzpadmn.f_get_parm('&1','&2','&4','01')); v_college := UPPER(gzpadmn.f_get_parm('&1','&2','&4','02')); v_level := UPPER(gzpadmn.f_get_parm('&1','&2','&4','03')); v_id := UPPER(gzpadmn.f_get_parm('&1','&2','&4','04')); DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZPADVR ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE('Input Parms: '); DBMS_OUTPUT.PUT_LINE('v_term: ' || v_term); DBMS_OUTPUT.PUT_LINE('v_college: ' || v_college); DBMS_OUTPUT.PUT_LINE('v_level: ' || v_level); DBMS_OUTPUT.PUT_LINE('v_id: ' || v_id); DBMS_OUTPUT.PUT_LINE(' '); v_text := 'ADVISOR ID' || f3 || 'ADVISOR NAME(FML)' || f3 || 'PRIMARY' || f3 || 'ADVISOR TYPE' || f3 || 'STUDENT ID' || f3 || 'STUDENT NAME (FML)' || f3 || 'LAST_NAME' || f3 || 'FIRST_NAME' || f3 || 'ADVISOR DEPARTMENT' || f3 || 'TERM' || f3 || 'LEVEL' || f3 || 'FIRST COLLEGE' || f3 || 'FIRST MAJOR 1' || f3 || 'FIRST MAJOR 2' || f3 || 'FIRST MINOR1' || f3 || 'FIRST MINOR 2' || f3 || 'FIRST CONCENTRATION' || f3 || 'FIRST PROGRAM CODE' || f3 || 'SECOND COLLEGE' || f3 || 'SECOND MAJOR 1' || f3 || 'SECOND MAJOR 2' || f3 || 'SECOND MINOR1' || f3 || 'SECOND MINOR 2' || f3 || 'SECOND CONCENTRATION' || f3 || 'SECOND PROGRAM CODE' || f3 || 'REGISTERED INDICATOR' || f3 || 'AU GPA' || f3 || 'VA (Y/N INDICATOR)' || f3 || 'ATHLETE (Y/N INDICATOR)' || f3 || 'STUDENT EMAIL' || f3 || 'PIN' || f3 || 'CATR' || f3 || 'CATI' || f3 || 'CATC' || f3 || 'ADMISSION_TYPE' || f3 || 'ADMISSION_TERM' || f3 || 'AUBURN_EARNED_HRS' || f3 || 'TRANSFER_EARNED_HRS' || f3 || 'CUMULATIVE_EARNED_HRS'; /* v_text := 'ADVISOR ID,ADVISOR NAME(FML),PRIMARY, ADVISOR TYPE,' || 'STUDENT ID,STUDENT NAME (FML),LAST_NAME,FIRST_NAME,ADVISOR DEPARTMENT,TERM,LEVEL,FIRST COLLEGE,FIRST MAJOR 1,FIRST MAJOR 2,' || 'FIRST MINOR1, FIRST MINOR 2,FIRST CONCENTRATION,FIRST PROGRAM CODE,' || 'SECOND COLLEGE,SECOND MAJOR 1,SECOND MAJOR 2,' || 'SECOND MINOR1,SECOND MINOR 2,SECOND CONCENTRATION,SECOND PROGRAM CODE,' || 'REGISTERED INDICATOR,AU GPA,VA (Y/N INDICATOR),ATHLETE (Y/N INDICATOR),STUDENT EMAIL,PIN,CATR,CATI,CATC'; */ -- DBMS_OUTPUT.PUT_LINE(v_text); gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_text); --used to get the advisors pidm IF length(v_id) = 9 THEN v_pidm_inp := gb_common.f_get_pidm(v_id); ELSE v_pidm_inp := '%'; END IF; v_tally := 0; FOR get_students_rec IN get_students_cur(v_term, v_college,v_level) LOOP v_tally := v_tally + 1; v_pidm := get_students_rec.person_uid; v_stu_pidm := get_students_rec.person_uid; v_program_count := 0; v_num_programs := 0; v_gpa := null; szvcurr_record := NULL; OPEN get_reg_students_cur(v_pidm, v_term,v_level,v_college); FETCH get_reg_students_cur INTO szvcurr_record; CLOSE get_reg_students_cur; v_levl_code := null; v_levl_code := szvcurr_record.student_level; FOR take_out_none_college_rec IN take_out_none_college_cur(v_pidm, v_term, v_college, v_level) LOOP v_non_college := 'Y'; END LOOP; -- v_num_programs is not used LZ 3/6/2017 -- OPEN number_programs_cur(v_pidm, v_term, v_level); -- FETCH number_programs_cur INTO v_num_programs; -- CLOSE number_programs_cur; IF v_non_college = 'N' THEN v_has_advisor := 'N'; -- <> FOR get_advisors_rec IN get_advisors_cur(v_pidm, v_term,v_pidm_inp) LOOP v_has_advisor := 'Y'; employee_record := NULL; OPEN get_employee_dept_cur(get_advisors_rec.sgradvr_advr_pidm); FETCH get_employee_dept_cur INTO employee_record; CLOSE get_employee_dept_cur; spriden_record := NULL; OPEN get_spriden_cur(get_advisors_rec.sgradvr_advr_pidm); FETCH get_spriden_cur INTO spriden_record; CLOSE get_spriden_cur; IF (get_advisors_rec.sgradvr_advr_pidm IS NOT NULL) THEN v_text := '"' || gb_common.f_get_id(get_advisors_rec.sgradvr_advr_pidm) || f2; ELSE v_text := '"' || f2; END IF; v_text := v_text || spriden_record.spriden_first_name || ' ' || spriden_record.spriden_mi || ' ' || spriden_record.spriden_last_name || f2 || get_advisors_rec.sgradvr_prim_ind || f2 || get_advisors_rec.sgradvr_advr_code || f2 || gb_common.f_get_id(v_pidm) || f2; OPEN get_spriden_cur(v_pidm); FETCH get_spriden_cur INTO spriden_record; CLOSE get_spriden_cur; v_text := v_text || spriden_record.spriden_first_name || ' ' || spriden_record.spriden_mi || ' ' || spriden_record.spriden_last_name || f2 || spriden_record.spriden_last_name || f2 || spriden_record.spriden_first_name || f2; IF (get_advisors_rec.sgradvr_advr_pidm IS NOT NULL) THEN v_text := v_text || employee_record.jbln_desc || ' ' || employee_record.gtv_desc || f2 || get_advisors_rec.sgradvr_term_code_eff || f2; ELSE v_text := v_text || f2 || f2; END IF; IF szvcurr_record.student_level IS NULL THEN GOTO next_record; /* v_text := v_text || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2 || f2; */ ELSE v_text := v_text || szvcurr_record.student_level || f2; add_programs(v_pidm, v_term, szvcurr_record.student_level , v_text); --add the programs END IF; v_text := v_text || szvcurr_record.registered_ind || f2; v_gpa := null; OPEN get_gpa_cur(szvcurr_record.person_uid, szvcurr_record.student_level); FETCH get_gpa_cur INTO v_gpa; CLOSE get_gpa_cur; v_text := v_text ||round(v_gpa,2) || f2; FOR get_veteran_rec IN get_veteran_cur(szvcurr_record.person_uid) LOOP v_veteran := 'Y'; END LOOP; v_text := v_text || v_veteran || f2; FOR get_sport_rec IN get_sport_cur(szvcurr_record.person_uid, v_term) LOOP v_sport := 'Y'; END LOOP; v_text := v_text || v_sport || f2; OPEN get_email_cur(szvcurr_record.person_uid); FETCH get_email_cur INTO v_email; CLOSE get_email_cur; v_text := v_text || v_email || f2; --add pin v_ssb_flag := NULL; v_ssb_flag := szgsdax.f_get_ssb_flag(szvcurr_record.person_uid, v_term); v_pin_message := szgsdax.f_get_adv_message(szvcurr_record.person_uid, v_term); v_text := v_text || v_ssb_flag || '-' || v_pin_message || f2; --add cater terms v_catr_term := NULL; v_cati_term := NULL; v_catc_term := NULL; FOR v_sgrsact_rec IN get_cater_cur(szvcurr_record.person_uid, v_term) LOOP IF v_sgrsact_rec.sgrsact_actc_code = 'CATC' THEN v_catc_term := v_sgrsact_rec.sgrsact_term_code; END IF; IF v_sgrsact_rec.sgrsact_actc_code = 'CATI' THEN v_cati_term := v_sgrsact_rec.sgrsact_term_code; END IF; IF v_sgrsact_rec.sgrsact_actc_code = 'CATR' THEN v_catr_term := v_sgrsact_rec.sgrsact_term_code; END IF; END LOOP; v_text := v_text || v_catr_term ||f2 || v_cati_term ||f2 || v_catc_term || f2; v_admt_code := null; v_admt_code := f_get_max_sorlcur(v_pidm,'ADMT'); v_text := v_text || v_admt_code || f2; v_admt_term := null; v_admt_term := f_get_max_sorlcur(v_pidm,'TERM'); v_text := v_text || v_admt_term || f2; v_aubn_earn_hrs := 0; v_aubn_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'I', v_term,'XXX'); v_text := v_text || v_aubn_earn_hrs || f2; v_tfer_earn_hrs := 0; v_tfer_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'T', v_term,'XXX'); v_text := v_text || v_tfer_earn_hrs || f2; v_cumu_earn_hrs := 0; v_cumu_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'%', v_term,'CUM'); v_text := v_text || v_cumu_earn_hrs || f1; -- set up to only print advisor if an advisor is entered IF v_pidm_inp = '%' THEN gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_text); ELSE IF get_advisors_rec.sgradvr_advr_pidm IS NOT NULL THEN gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_text); END IF; END IF; v_veteran := 'N'; v_sport := 'N'; END LOOP; --<> MATCHES <> -- if the student doesn't have an advisor print anyway IF (v_has_advisor = 'N') THEN OPEN get_spriden_cur(szvcurr_record.person_uid); FETCH get_spriden_cur INTO spriden_record; CLOSE get_spriden_cur; v_text := f1 || f2 || f2 || f2 || f2 || spriden_record.spriden_id || f2 || spriden_record.spriden_first_name || ' ' || spriden_record.spriden_mi || ' ' || spriden_record.spriden_last_name || f2 || spriden_record.spriden_last_name || f2 || spriden_record.spriden_first_name || f2 || f2 || f2 || szvcurr_record.student_level || f2; add_programs(v_pidm, v_term, v_level, v_text); --add the programs v_text := v_text || szvcurr_record.registered_ind || f2; v_gpa := null; OPEN get_gpa_cur(szvcurr_record.person_uid, szvcurr_record.student_level); FETCH get_gpa_cur INTO v_gpa; CLOSE get_gpa_cur; v_text := v_text || round(v_gpa,2) || f2; FOR get_veteran_rec IN get_veteran_cur(szvcurr_record.person_uid) LOOP v_veteran := 'Y'; END LOOP; v_text := v_text || v_veteran || f2; FOR get_sport_rec IN get_sport_cur(szvcurr_record.person_uid, v_term) LOOP v_sport := 'Y'; END LOOP; v_text := v_text || v_sport || f2; OPEN get_email_cur(szvcurr_record.person_uid); FETCH get_email_cur INTO v_email; CLOSE get_email_cur; -- Changes for #2095 -- v_text := v_text || v_email || f2 || f2 || f2 || f2 || f2; v_text := v_text || v_email || f2; --add pin v_ssb_flag := NULL; v_ssb_flag := szgsdax.f_get_ssb_flag(szvcurr_record.person_uid, v_term); v_pin_message := szgsdax.f_get_adv_message(szvcurr_record.person_uid, v_term); v_text := v_text || v_ssb_flag || '-' || v_pin_message || f2; --add cater terms v_catr_term := NULL; v_cati_term := NULL; v_catc_term := NULL; FOR v_sgrsact_rec IN get_cater_cur(szvcurr_record.person_uid, v_term) LOOP IF v_sgrsact_rec.sgrsact_actc_code = 'CATC' THEN v_catc_term := v_sgrsact_rec.sgrsact_term_code; END IF; IF v_sgrsact_rec.sgrsact_actc_code = 'CATI' THEN v_cati_term := v_sgrsact_rec.sgrsact_term_code; END IF; IF v_sgrsact_rec.sgrsact_actc_code = 'CATR' THEN v_catr_term := v_sgrsact_rec.sgrsact_term_code; END IF; END LOOP; v_text := v_text || v_catr_term ||f2 || v_cati_term ||f2 || v_catc_term || f2; -- End Changes for #2095 v_admt_code := null; v_admt_code := f_get_max_sorlcur(v_pidm,'ADMT'); v_text := v_text || v_admt_code || f2; v_admt_term := null; v_admt_term := f_get_max_sorlcur(v_pidm,'TERM'); v_text := v_text || v_admt_term || f2; v_aubn_earn_hrs := null; v_aubn_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'I', v_term,'XXX'); v_text := v_text || v_aubn_earn_hrs || f2; v_tfer_earn_hrs := null; v_tfer_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'T', v_term,'XXX'); v_text := v_text || v_tfer_earn_hrs || f2; v_cumu_earn_hrs := 0; v_cumu_earn_hrs := f_get_earned_hours(v_pidm , v_levl_code,'%', v_term,'CUM'); v_text := v_text || v_cumu_earn_hrs || f1; -- DBMS_OUTPUT.PUT_LINE(v_text); IF v_pidm_inp = '%' THEN gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_text); END IF; v_veteran := 'N'; v_sport := 'N'; ELSE -- GOES WITH (IF v_has_advisor = 'N') null; END IF; -- GOES WITH (IF v_has_advisor = 'N') END IF; v_non_college := 'N'; <> NULL; END LOOP; v_sysend := systimestamp; v_duration := v_sysend - v_sysbeg; --end comments DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('END OF SZPADVR ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS IN REPORT:[' || v_tally || ']'); DBMS_OUTPUT.PUT_LINE('Duration:[' || v_duration || ']'); --stop profiling EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error found in PIDM: '||v_pidm|| ' ' || SQLERRM); dbms_output.put_line('424:SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('425:SQLERRM:[' || SQLERRM || ']'); v_line_out := dbms_utility.format_error_backtrace; dbms_output.put_line('425:ERROR BACKTRACE:[' ||v_line_out || ']'); v_line_out := dbms_utility.format_call_stack; dbms_output.put_line('425:ERROR CALL STACK:[' ||v_line_out || ']'); END; /