--================================================================================ -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPGRAD -- AUTHOR: Sam Potts -- DATE WRITTEN: August 8, 2014 -- DESCRIPTION: Rpt of students with graduation codes in a term -- PARMS: 01 TERM -- 02 Graduation Code or % for all -- TABLES Used: SPRIDEN , SHRDGMR , STVDEGC , SGRVETN -- REVISION HISTORY -- WHEN WHO WHAT ----------------------------------------------------------------------------------- -- 08Aug2014 SP StuDev#1019 Conversion from ODS -- 08Aug2014 SP StuDev#1040 Remove PIDM -- 11Dec2014 SP StuDev#1029 1. changed source of Curr Acad Info to szpcurr -- 2. ADD College PARMS -- 18Apr2016 FCN StuDev#1627 Add column: email. Source=GOREMAL/AU=Type -- 18Oct2017 DTM StuDec#2068 - Added student type column. -- 28Aug2020 IMM REQ0019567/TASK0025779 - Cursor Call to check for Veteran w/SGRSATT attributes of ‘VA01’ and ‘VA02' added --================================================================================ set echo off; set verify off; set feedback off; set serveroutput ON SIZE UNLIMITED; whenever sqlerror exit failure; ------------------Declarations-------------------------------------------------- DECLARE ----Variables------------------------------------------------------------------- --User Parms v_term varchar2(6); -- User Parm 1 -- Term v_grad_code varchar2(2); -- User Parm 2 -- Graduation Code v_coll varchar2(2); -- User Parm 3 -- College Code -- Output Delimiters f1 varchar2(10) := '"'; f2 varchar2(10) := '","'; -- Output variables for log and output file v_line_out varchar2(4096):= null; v_count number := 0; v_end timestamp; v_begin timestamp; v_duration interval day to second(1); -- duration of program run, precision of 0.1 sec -- Variables v_vet varchar2(1):= null; v_vet2 varchar2(1):= null; v_ath varchar2(1):= null; v_pidm spriden.spriden_pidm%type; v_email goremal.goremal_email_address%type; v_rpt_name varchar2(10) := 'SZPGRAD'; v_type VARCHAR2(1) := NULL; ------------------------------------------------------------------------------- ----Cusors ------------------------------------------------------------------- -----Driver -- in term, find applied, or pending, or awarded, or all grad codes ------------------------------------------------------------------------------- cursor c_driver ( v_term varchar2 , v_grad_code varchar2 , v_coll varchar2 ) is select * from shrdgmr a where 1=1 and shrdgmr_degs_code LIKE v_grad_code and shrdgmr_term_code_grad = v_term and ( SHRDGMR_COLL_CODE_1 like v_coll or SHRDGMR_COLL_CODE_2 like v_coll ) and a.SHRDGMR_TERM_CODE_GRAD = (select max (b.SHRDGMR_TERM_CODE_GRAD ) from SHRDGMR b where 1=1 and b.SHRDGMR_PIDM = a.SHRDGMR_PIDM and b.SHRDGMR_SEQ_NO = a.SHRDGMR_SEQ_NO ) order by SHRDGMR_PIDM ; rec_driver c_driver%rowtype; ------------------------------------------------------------------------------- -- Cursor selects STUDENT's Name and info for supplied PIDM ------------------------------------------------------------------------------ cursor c_Stu_Name ( v_pidm number ) is select spriden_id as BAN_ID, spriden_last_name || ', ' || spriden_first_name ||' '|| spriden_mi as stu_name from spriden where spriden_pidm = v_pidm and spriden_change_ind is null ; rec_stu_name c_stu_name%rowtype; ------------------------------------------------------------------------------ -- cursor selects degree description ------------------------------------------------------------------------------ cursor c_deg_info ( v_deg_code varchar2 ) is select * ---STVDEGC_DESC --only desc att from stvdegc where 1=1 and STVDEGC_CODE = v_deg_code ; rec_deg_info c_deg_info%rowtype; ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- --- -- Cursor selects Major Description cursor c_maj_info ( v_maj_code varchar2 ) is select * ---STVMAJR_DESC --only desc att from STVMAJR where 1=1 and STVMAJR_CODE = v_maj_code ; rec_maj_info c_maj_info%rowtype; ------------------------------------------------------------------------------- -- Cursor selects to get students current academic information --change to using szvcurr for 2nd major, minor. perhaps get all from szvcurr ------------------------------------------------------------------------------- cursor c_curr_acad ( v_pidm number , v_term varchar2 ) is select * ---STVMAJR_DESC --only desc att from szvcurr where 1=1 and PERSON_UID = v_pidm and ACADEMIC_PERIOD = v_term ; rec_curr_acad c_curr_acad%rowtype; ------------------------------------------------------------------------------- --- --Cursor to check if student has a VET record in ANY term cursor c_vet ( v_pidm number ) is select unique 'Y' as vet from SGRVETN where 1=1 and SGRVETN_PIDM = v_pidm and SGRVETN_VETC_CODE is not null ; rec_vet c_vet%rowtype; ------------------------------------------------------------------------------- --- IMM TASK0025779 --Cursor to check if student has a VA01,VA02 cursor c_vet2 ( v_pidm number ) is select unique 'Y' as vet2 from SGRSATT where 1=1 and SGRSATT_PIDM = v_pidm and SGRSATT_ATTS_CODE IN ('VA01','VA02') ; rec_vet2 c_vet2%rowtype; ------------------------------------------------------------------------------- --Cursor to check if student is an ATHLETE in the term of interest cursor c_ath ( v_pidm Number, v_term varchar2 ) is select 'Y' from sgrsprt where 1=1 and SGRSPRT_PIDM = v_pidm and SGRSPRT_TERM_CODE = v_term and SGRSPRT_SPST_CODE = 'AC' --active code and SGRSPRT_ACTC_CODE is not null ; rec_ath c_ath%rowtype; ------------------------------------------------------------------------------- --Cursor to fetch student type CURSOR c_stu_type(v_pidm sgbstdn.sgbstdn_pidm%TYPE, v_term sgbstdn.sgbstdn_term_code_eff%TYPE) IS SELECT sgbstdn_styp_code FROM sgbstdn WHERE sgbstdn_pidm = v_pidm AND sgbstdn_term_code_eff <= v_term AND rownum = 1 ORDER BY sgbstdn_term_code_eff DESC; --============================================================================= function f_get_email(in_pidm varchar2) return varchar2 is v_return_val goremal.goremal_email_address%type; v_email_addr goremal.goremal_email_address%type; v_line_out varchar2(1000); cursor c_get_email_address is select goremal_email_address from goremal where goremal_pidm = in_pidm and goremal_emal_code='AU' and goremal_status_ind='A'; -- and goremal_preferred_ind='Y'; v_email_record c_get_email_address%rowtype; begin open c_get_email_address; fetch c_get_email_address into v_email_record; if (c_get_email_address%found) then v_email_addr :=v_email_record.goremal_email_address; else v_email_addr := szg8003.f_get_gid(in_pidm); v_email_addr := lower(v_email_addr) || '@auburn.edu'; end if; close c_get_email_address; v_return_val := null; v_return_val := v_email_addr; return v_return_val; exception when others then dbms_output.put_line('Error found in f_get_email 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_email; --***************************************************************************** BEGIN dbms_output.enable (null); --IMM TASK0025779 Set to null dbms_output.put_line('BEGIN' || v_rpt_name || TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); v_begin := systimestamp; DBMS_OUTPUT.PUT_LINE('---'); --------Get parms from Banner into variables v_term := gzpadmn.F_Get_Parm('&1','&2','&4','01'); v_grad_code := upper ( gzpadmn.F_Get_Parm('&1','&2','&4','02') ); v_coll := upper ( gzpadmn.F_Get_Parm('&1','&2','&4','03') ); --------Output Job parms, user, and user parm values to log file dbms_output.put_line('Job_Name: &1'||' '||'Job No.: &2'||' '||'DB_INSTANCE: &4'); dbms_output.put_line('Run by: &3'); -------------User Parms-------------------------- dbms_output.put_line( 'PARM Term Entered: [' || v_term || ']'); dbms_output.put_line( 'PARM Graduation Code:[' || v_grad_code || ']' ); dbms_output.put_line( 'PARM Graduation Coll:[' || v_coll || ']' ); DBMS_OUTPUT.PUT_LINE('---'); ------------------------------------------------------------------------------- --------Create the header line to file v_line_out := f1 --||'SHRDGMR_PIDM' ||f2 ----------dev and ts'ing only ||'Term' ||f2||'Deg Code Sts' ||f2||'BAN_ID' ||f2||'STU_Name' ||f2||'Level' ||f2||'College' ||f2||'Dept' ||f2||'Program' ||f2||'Degree' ||f2||'Degree_Desc' ||f2||'Maj' ||f2||'Maj_Desc' --||'College 2' ||f2 ||f2||'Second_Major' ||f2||'Minor' ||f2||'Second_Minor' ||f2||'Email_Address' ||f2||'Grad_Date' ||f2||'Vet Benefits' ----ANY Veteran Table entry for ANY term ||f2||'Athlete' ----Athlete in parm term ||f2||'Student_Type' ||f2||'Veteran' ||f2||f1; gzpadmn.p_spool_lis('&1', &2, null,null , v_line_out); ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ --------Open the driver and other cursors and drive the output line-by-line in the loop FOR rec_driver IN c_driver( v_term , v_grad_code , v_coll ) LOOP ---check that the code SO is not passed for user parm % (wildcard) IF rec_driver.shrdgmr_degs_code = 'SO' --skip SO (sought) then continue; end if; v_pidm := rec_driver.shrdgmr_pidm; --Cursor call to get Student Ban_ID and Name open c_Stu_Name ( rec_driver.SHRDGMR_PIDM ); fetch c_Stu_Name into rec_Stu_Name; close c_Stu_Name; --------------------- --Cursor call to get Degree Description open c_deg_info ( rec_driver.SHRDGMR_DEGC_CODE ); fetch c_deg_info into rec_deg_info; close c_deg_info; --------------------- --Cursor call to get Major Description open c_maj_info ( rec_driver.SHRDGMR_MAJR_CODE_1); fetch c_maj_info into rec_maj_info; close c_maj_info; --------------------- --Cursor call to get Current Academic Info --2nd maj, etc open c_curr_acad ( rec_driver.SHRDGMR_PIDM , v_term); fetch c_curr_acad into rec_curr_acad; close c_curr_acad; --------------------- --Cursor Call to check for Veteran record in ANY term rec_vet := null; v_vet := null; --Cursor call to get Degree Description open c_vet ( rec_driver.SHRDGMR_PIDM ); fetch c_vet into v_vet; close c_vet; --------------------- --Cursor Call to check for Veteran w/SGRSATT attributes of ‘VA01’ and ‘VA02 rec_vet2 := null; v_vet2 := null; --Cursor call to get Degree Description open c_vet2 ( rec_driver.SHRDGMR_PIDM ); fetch c_vet2 into v_vet2; close c_vet2; --------------------- --Cursor Call to check for Athlete in term rec_ath := null; v_ath := null; open c_ath ( rec_driver.SHRDGMR_PIDM, v_term ); fetch c_ath into v_ath; close c_ath; v_email := f_get_email(v_pidm); --Cursor Call to fetch student type v_type := NULL; OPEN c_stu_type(rec_driver.SHRDGMR_PIDM, v_term); FETCH c_stu_type INTO v_type; CLOSE c_stu_type; ---------------output if the person has one or more grad status codes v_line_out := f1 --|| rec_driver.SHRDGMR_PIDM ||f2 -----------dev and ts'ing only || v_term ||f2|| rec_driver.shrdgmr_degs_code ||f2|| rec_Stu_Name.BAN_ID ||f2|| rec_Stu_Name.Stu_Name ||f2|| rec_driver.SHRDGMR_LEVL_CODE ||f2|| rec_driver.SHRDGMR_COLL_CODE_1 ||f2|| rec_driver.SHRDGMR_DEPT_CODE ||f2|| rec_driver.SHRDGMR_PROGRAM ||f2|| rec_driver.SHRDGMR_DEGC_CODE ||f2|| rec_deg_info.STVDEGC_DESC ||f2|| rec_driver.SHRDGMR_MAJR_CODE_1 ||f2|| rec_maj_Info.STVMAJR_DESC --|| rec_driver.SHRDGMR_COLL_CODE_2 ||f2 ||f2|| rec_curr_acad.SECOND_MAJOR ||f2|| rec_driver.SHRDGMR_MAJR_CODE_MINR_1 ||f2|| rec_curr_acad.SECOND_MINOR ||f2|| v_email ||f2|| rec_driver.SHRDGMR_GRAD_DATE ||f2|| v_vet ||f2|| v_ath ||f2|| v_type ||f2|| v_vet2 ||f2||f1; gzpadmn.p_spool_lis('&1', &2, null,null , v_line_out); v_count := v_count + 1; --record counter is global end loop; ------------------------------------------------------------------------------ ------capture end of run time and compute duration v_end := systimestamp; v_duration := (v_end - v_begin) day to second; ----------------------------------------------------------------------------- -------Finished processing -- output end date/time stamp, and record count to log DBMS_OUTPUT.PUT_LINE (' '); DBMS_OUTPUT.PUT_LINE('Finished....SZPGRAD ... at ' || TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE('---'); -- insert line DBMS_OUTPUT.PUT_LINE('OUTPUT Records Count : ' || v_count); ---count for the records output DBMS_OUTPUT.PUT_LINE('Program Run Time : ' || v_duration); ---duration of program run DBMS_OUTPUT.PUT_LINE('---'); DBMS_OUTPUT.PUT_LINE ('---'); ------------------------------------------------------------------------------- ------Error handling EXCEPTION WHEN others THEN dbms_output.put_line('Unhandled Error: '); dbms_output.put_line(SQLERRM); -------------------------------------------------------------------------------- END; / EXIT SUCCESS COMMIT;