-- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPADIE.SQL -- AUTHOR: Rodney Clark -- DATE WRITTEN: 02/20/14 -- INPUT: -- -- DESCRIPTION: Process to End Advisors for Graduation. -- -- DATE REVISED: -- --SET SCAN OFF; set serveroutput on size unlimited; SET echo OFF; SET verify OFF; SET feedback OFF; SET tab OFF; DECLARE -- cursor to get graduated students cursor get_graduated_stu_cur(v_term varchar2, v_coll varchar2) is select * from shrdgmr where shrdgmr_term_code_grad = v_term and shrdgmr_degs_code = 'AW' and (shrdgmr_coll_code_1 like(v_coll) or shrdgmr_coll_code_2 in (v_coll)) and exists (select 'x' from sgradvr a where sgradvr_pidm = shrdgmr_pidm and sgradvr_term_code_eff = (select max(b.sgradvr_term_code_eff) from sgradvr b where a.sgradvr_pidm = b.sgradvr_pidm and sgradvr_term_code_eff <= v_term) and sgradvr_advr_code like 'A%') ; cursor get_advisor_cur(v_pidm varchar2, v_term varchar2) is select * from sgradvr a where (sgradvr_advr_code not like ('A%') or sgradvr_advr_code is null) and sgradvr_term_code_eff = (select max(b.sgradvr_term_code_eff) from sgradvr b where a.sgradvr_pidm = b.sgradvr_pidm and sgradvr_term_code_eff <= v_term) and sgradvr_pidm = v_pidm; cursor get_student_info_cur(v_pidm varchar2) is select * from spriden where spriden_pidm = v_pidm and spriden_change_ind is null; v_text varchar2(2000); v_term stvterm.stvterm_code%type; spriden_record spriden%rowtype; v_audit varchar2(1); v_has_other varchar2(1); is_advisor varchar2(1) := 'N'; v_pidm number; v_tmp_cnt number := 0; v_coll varchar2(100); v_err_msg varchar2(2000); f1 varchar2(1) := '"'; f2 varchar2(3) := '","'; v_skip_advisoradd number := 0; v_skip_termination number := 0; v_termination number := 0; v_advisoradd number := 0; v_spool_commit boolean :=false; verrcode NUMBER; --holds SQLCODE in case of error function f_get_next_term_cur(v_term varchar2) return varchar2 is cursor get_next_term_cur is select stvterm_code from stvterm where stvterm_code > v_term order by stvterm_code asc; v_future_term stvterm.stvterm_code%type; begin open get_next_term_cur; fetch get_next_term_cur into v_future_term; close get_next_term_cur; return v_future_term; end f_get_next_term_cur; function f_advisor_end_scheduled(v_check_term in varchar2, v_student_pidm in number) return boolean is begin select nvl((select count(*) from sgradvr where sgradvr_pidm = v_student_pidm and sgradvr_term_code_eff = v_check_term), 0) into v_tmp_cnt from dual; if(v_tmp_cnt > 0) then return true; end if; return false; end f_advisor_end_scheduled; function f_advisor_term_scheduled(v_check_term in varchar2, v_student_pidm in number, v_advr_pidm in number, v_type in varchar2) return boolean is begin select nvl((select count(*) from sgradvr where sgradvr_pidm = v_student_pidm and sgradvr_term_code_eff = v_check_term and sgradvr_advr_pidm = v_advr_pidm and nvl(sgradvr_advr_code,'Z') = nvl(v_type,'Z')), 0) into v_tmp_cnt from dual; if(v_tmp_cnt > 0) then return true; end if; return false; end f_advisor_term_scheduled; BEGIN v_term := UPPER(gzpadmn.f_get_parm('&1','&2','&4','01')); v_coll := UPPER(gzpadmn.f_get_parm('&1','&2','&4','02')); v_audit := UPPER(gzpadmn.f_get_parm('&1','&2','&4','03')); DBMS_OUTPUT.ENABLE(null); DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZPADIG ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE('v_term = ' || v_term ); DBMS_OUTPUT.PUT_LINE('v_coll = ' || v_coll ); DBMS_OUTPUT.PUT_LINE('v_audit = ' || v_audit ); DBMS_OUTPUT.PUT_LINE(' '); v_text := 'Student ID,Student Name,Advisor ID,Advisor Code,Result'; --DBMS_OUTPUT.PUT_LINE(v_text); gzpadmn.p_spool_lis('&1','&2','grad',null,v_text); -- end graduated students advisors for get_graduated_stu_rec in get_graduated_stu_cur(v_term, v_coll) loop v_text := null; open get_student_info_cur(get_graduated_stu_rec.shrdgmr_pidm); fetch get_student_info_cur into spriden_record; close get_student_info_cur; -- check to see if there are none undergrad advisors v_has_other := 'N'; for get_advisor_rec in get_advisor_cur(get_graduated_stu_rec.shrdgmr_pidm, v_term) loop v_has_other := 'Y'; end loop; if v_has_other = 'Y' then v_tmp_cnt := 0; select nvl(( select count(*) from sgradvr a where (sgradvr_advr_code not like ('A%') or sgradvr_advr_code is null) and sgradvr_advr_pidm is null and sgradvr_term_code_eff = (select max(b.sgradvr_term_code_eff) from sgradvr b where a.sgradvr_pidm = b.sgradvr_pidm and sgradvr_term_code_eff <= v_term) and sgradvr_pidm = get_graduated_stu_rec.shrdgmr_pidm), 0) into v_tmp_cnt from dual; if(v_tmp_cnt > 0) then v_err_msg := v_err_msg || ' - Add nor End performed.'; v_text := f1 || spriden_record.spriden_id || f2 || spriden_record.spriden_last_name || ',' || spriden_record.spriden_first_name || f2 || '' || f2 || '' || f2 || 'Advisor Pidm Null' || f2 || v_err_msg || '"'; gzpadmn.p_spool_lis('&1','&2','bad',null,v_text); else for get_advisor_rec in get_advisor_cur(get_graduated_stu_rec.shrdgmr_pidm, v_term) loop v_text := null; if v_audit = 'U' then if(f_advisor_term_scheduled(f_get_next_term_cur(v_term ), get_graduated_stu_rec.shrdgmr_pidm, get_advisor_rec.sgradvr_advr_pidm, get_advisor_rec.sgradvr_advr_code)) then v_err_msg := v_err_msg || ' Advisor ' || FRKIDEN.f_get_spriden_id(get_advisor_rec.sgradvr_advr_pidm) || ' ' || gzpgene.f_get_full_name(get_advisor_rec.sgradvr_advr_pidm) || ' already processed for term ' || v_term || ' No change made.'; v_skip_advisoradd := nvl(v_skip_advisoradd, 0) + 1; else -- DBMS_OUTPUT.PUT_LINE('Would be adding an advisor (' || get_advisor_rec.sgradvr_advr_code || '): ' || FRKIDEN.f_get_spriden_id(get_advisor_rec.sgradvr_advr_pidm) || ' ' || gzpgene.f_get_full_name(get_advisor_rec.sgradvr_advr_pidm)); v_advisoradd := nvl(v_advisoradd, 0) + 1; /* */ szgadvr.p_add_advr( get_graduated_stu_rec.shrdgmr_pidm, f_get_next_term_cur(v_term ), get_advisor_rec.sgradvr_advr_pidm, get_advisor_rec.sgradvr_prim_ind, get_advisor_rec.sgradvr_advr_code, v_audit, v_err_msg); /* */ end if; end if; v_text := f1 || spriden_record.spriden_id || f2 || spriden_record.spriden_last_name || ',' || spriden_record.spriden_first_name || f2 || gb_common.f_get_id(get_advisor_rec.sgradvr_advr_pidm) || f2 || get_advisor_rec.sgradvr_advr_code || f2 || 'Advisor Copied' || f2 || v_err_msg || '"'; v_err_msg := ''; gzpadmn.p_spool_lis('&1','&2','grad',null,v_text); end loop; end if; else if v_audit = 'U' then if(f_advisor_end_scheduled(f_get_next_term_cur(v_term ), get_graduated_stu_rec.shrdgmr_pidm)) then v_err_msg := v_err_msg || ' Advisor Term Already Processed - No change made.'; v_skip_termination := nvl(v_skip_termination, 0) + 1; else --DBMS_OUTPUT.PUT_LINE('Would be terminating advising for ' || FRKIDEN.f_get_spriden_id(get_graduated_stu_rec.shrdgmr_pidm) || ' ' || gzpgene.f_get_full_name(get_graduated_stu_rec.shrdgmr_pidm)); v_termination := nvl(v_termination, 0) + 1; /* */ szgadvr.p_end_advr(get_graduated_stu_rec.shrdgmr_pidm, f_get_next_term_cur(v_term ), v_err_msg); /* */ end if; end if; v_text := f1 || spriden_record.spriden_id || f2 || spriden_record.spriden_last_name || ',' || spriden_record.spriden_first_name || f2 || f2 || f2 || 'Student Advisors Ended' || f2 || v_err_msg || '"'; v_err_msg := ''; -- DBMS_OUTPUT.PUT_LINE(v_text); gzpadmn.p_spool_lis('&1','&2','term',null,v_text); end if; end loop; DBMS_OUTPUT.PUT_LINE('Skip Advisor Add : ' || v_skip_advisoradd); DBMS_OUTPUT.PUT_LINE('Skip Termination Add: ' || v_skip_termination); DBMS_OUTPUT.PUT_LINE('Termination Add : ' || v_termination); DBMS_OUTPUT.PUT_LINE('Advisor Add : ' || v_advisoradd); --end comments DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('END OF SZPADIG ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error found in PIDM: '||v_pidm|| ' ' || SQLERRM || '; ' || DBMS_UTILITY.format_error_backtrace()); END; /