-- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPMEDH.SQL -- AUTHOR: M Chen -- DATE WRITTEN: 02/29/2016 -- DATE REVISED: xx/xx/xxxx -- -- DESCRIPTION: -- Create MEDICAL Population Selction -- Note: -- TABLES: -- PARMS: #1: Student Type Selection - N for N type, T for L and T, O-Others: not (N, T, L) -- #2: Term Code for both admission and learner -- #3: Run Mode (A-Audit; U-Update) --set SCAN off set echo off set verify off set feedback off set serveroutput ON whenever sqlerror exit FAILURE DECLARE v_stu_population varchar2(1); v_term_code stvterm.stvterm_code%TYPE; v_term_code_admit stvterm.stvterm_code%TYPE; v_run_mode varchar2(1):= 'A'; v_commit BOOLEAN :=FALSE; v_pidm glbextr.glbextr_key%TYPE; v_cnt number := 0; v_selection glbextr.glbextr_selection%TYPE := 'MEDICAL_HOLDS';--have to use this name as it is in GLBSLCT v_application glbextr.glbextr_application%TYPE := 'STUDENT'; v_creator_id glbextr.glbextr_creator_id%TYPE := 'GDH0002'; v_user_id glbextr.glbextr_user_id%TYPE := 'GDH0002'; v_sys_ind glbextr.glbextr_sys_ind%TYPE := 'S'; v_sysbeg TIMESTAMP; v_sysend TIMESTAMP; v_duration INTERVAL DAY TO SECOND; v_status varchar2(200); vline_out VARCHAR(2048); --output line for dbms f1 VARCHAR2(10) := '"'; f2 VARCHAR2(10) := '","'; --pull selected students group using logic specified in the ticket instead of logics in popsels CURSOR students_c1 IS SELECT DISTINCT(A.sgbstdn_pidm), sgbstdn_styp_code, szg8003.f_get_last(A.sgbstdn_pidm), szg8003.f_get_first(A.sgbstdn_pidm) FROM sgbstdn A, sarappd WHERE A.sgbstdn_term_code_eff = (SELECT max(b.sgbstdn_term_code_eff) FROM sgbstdn b WHERE b.sgbstdn_pidm = A.sgbstdn_pidm AND b.sgbstdn_term_code_eff <= v_term_code) AND A.sgbstdn_stst_code = 'AS' AND NOT EXISTS( SELECT sprhold_pidm FROM sprhold WHERE A.sgbstdn_pidm = sprhold_pidm AND sprhold_hldd_code = 'MF') AND sarappd_term_code_entry = v_term_code_admit AND SARAPPD_APDC_CODE IN (SELECT stvapdc_code FROM stvapdc WHERE STVAPDC_STDN_ACC_IND = 'Y') AND A.sgbstdn_pidm = sarappd_pidm -- AND sgbstdn_pidm = gb_common.f_get_pidm('903364869') ORDER BY szg8003.f_get_last(A.sgbstdn_pidm) , szg8003.f_get_first(A.sgbstdn_pidm) ; /*logic from popsels SELECT DISTINCT(sarappd_pidm)--, szg8003.f_get_last(sarappd_pidm), szg8003.f_get_first(sarappd_pidm) -- it took longer with ORDER BY FROM sarappd WHERE sarappd_term_code_entry = v_term_code_admit AND SARAPPD_APDC_CODE IN (SELECT stvapdc_code FROM stvapdc WHERE STVAPDC_STDN_ACC_IND = 'Y') MINUS SELECT DISTINCT(sgbstdn_pidm)--, szg8003.f_get_last(sgbstdn_pidm), szg8003.f_get_first(sgbstdn_pidm) FROM sgbstdn WHERE sgbstdn_term_code_eff < v_term_code MINUS SELECT DISTINCT(sprhold_pidm)--, szg8003.f_get_last(sprhold_pidm), szg8003.f_get_first(sprhold_pidm) FROM sprhold WHERE sprhold_hldd_code = 'MF' --ORDER BY 2, 3 ; */ PROCEDURE p_insert_print (p_pidm varchar2, p_type varchar2, p_commit BOOLEAN) IS v_glbextr_rec glbextr%ROWTYPE; p_selection glbextr.glbextr_selection%TYPE := 'MEDICAL_HOLDS'; --have to use this name as it is in GLBSLCT p_application glbextr.glbextr_application%TYPE := 'STUDENT'; p_creator_id glbextr.glbextr_creator_id%TYPE := 'GDH0002'; p_user_id glbextr.glbextr_user_id%TYPE := 'GDH0002'; p_sys_ind glbextr.glbextr_sys_ind%TYPE := 'S'; pline_out VARCHAR(2048); --output line for dbms pf1 VARCHAR2(10) := '"'; pf2 VARCHAR2(10) := '","'; BEGIN pline_out := pf1 || szg8003.f_get_id(p_pidm) || pf2 || szg8003.f_get_last(p_pidm) || pf2 || szg8003.f_get_first(p_pidm) || pf2 || p_type || pf1; --dbms_output.put_line(vline_out); gzpadmn.p_spool_lis('&1','&2',NULL,NULL,pline_out, p_commit); v_glbextr_rec := NULL; v_glbextr_rec.glbextr_application := p_application; v_glbextr_rec.glbextr_selection := p_selection; v_glbextr_rec.glbextr_creator_id := p_creator_id; v_glbextr_rec.glbextr_user_id := p_user_id; v_glbextr_rec.glbextr_key := p_pidm; v_glbextr_rec.glbextr_activity_date := sysdate; v_glbextr_rec.glbextr_sys_ind := p_sys_ind; v_glbextr_rec.glbextr_slct_ind := NULL; BEGIN INSERT INTO glbextr VALUES v_glbextr_rec; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN vline_out := vline_out || 'DUPLICATE: record already added"'; gzpadmn.p_spool_lis('&1', &2, 'Error', NULL, pline_out, p_commit); WHEN OTHERS THEN DBMS_OUTPUT.put_line( 'An error occurred while inserting GLBEXTR - please send the following to someone in OIT Student Banner ID:' || gb_common.f_get_id(p_pidm) ); pline_out := pline_out || 'ERROR Loading"'; gzpadmn.p_spool_lis('&1', &2, 'Error', NULL, pline_out, p_commit); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); ROLLBACK; RETURN; END; END p_insert_print; BEGIN v_sysbeg := sysdate; dbms_output.enable(200000000); dbms_output.put_line('Starting.... SZPMEDH AT ' || systimestamp); --Output parm values to log file dbms_output.put_line('Job_Name: &1'||' '||'One_Up_No: &2'||' '||'DB_Name: &4'); dbms_output.put_line('Run BY: &3'); v_stu_population := upper( gzpadmn.F_Get_Parm('&1','&2','&4','01')); v_term_code := gzpadmn.F_Get_Parm('&1','&2','&4','02'); v_term_code_admit := v_term_code; v_run_mode := gzpadmn.f_get_parm ('&1','&2','&4','03'); -- v_stu_population := 'O'; --v_term_code := '201630'; --v_term_code_admit := '201630'; --Output parm values to log file dbms_output.put_line('PARM NUMBER: 01 Student Population Type Entered (N, T, or O) ' || v_stu_population); dbms_output.put_line('PARM NUMBER: 02 TERM CODE entered for both Admission and Learner records: ' || v_term_code ); dbms_output.put_line('PARM NUMBER: 03 Run Mode Entered: ' || v_run_mode); IF upper(v_run_mode) = 'A' THEN v_commit := TRUE; END IF; BEGIN DELETE FROM glbextr WHERE glbextr_application = v_application AND glbextr_selection = v_selection AND glbextr_creator_id = v_creator_id AND glbextr_user_id = v_user_id; EXCEPTION WHEN OTHERS THEN --verrmsg := SQLERRM; --verrcode := SQLCODE; DBMS_OUTPUT.PUT_LINE('SZPMEDH: Delete GLBEXTR Exception. Error found.'||f1||SQLERRM ); ROLLBACK; END; vline_out := 'Student ID,Student Last Name,Student First Name, Student Type'; -- dbms_output.put_line(vline_out); gzpadmn.p_spool_lis('&1','&2',NULL,NULL,vline_out, v_commit); --Read student records FOR student_row IN students_c1 LOOP v_pidm := student_row.sgbstdn_pidm; IF (v_stu_population = 'N' AND student_row.sgbstdn_styp_code = 'N') OR (v_stu_population = 'T' AND student_row.sgbstdn_styp_code IN ('L', 'T')) OR (v_stu_population = 'O' AND student_row.sgbstdn_styp_code NOT IN ('N', 'L', 'T')) THEN p_insert_print(v_pidm, student_row.sgbstdn_styp_code, v_commit); v_cnt := v_cnt + 1; END IF; END LOOP; v_sysend := sysdate; v_duration := v_sysend - v_sysbeg; dbms_output.put_line('SZPMEDH ENDED AT ' || systimestamp || ' with Run Mode ' || v_run_mode ); DBMS_OUTPUT.PUT_LINE('Duration:[' || v_duration || ']'); DBMS_OUTPUT.PUT_LINE('Number of Records Processed:[' || v_cnt || ']'); IF UPPER(v_run_mode) = 'U' THEN COMMIT; ELSE ROLLBACK; END IF; EXCEPTION WHEN OTHERS THEN --verrmsg := SQLERRM; --verrcode := SQLCODE; DBMS_OUTPUT.PUT_LINE('SZPMEDH EXCEPTION. ERROR FOUND.'||f1||SQLERRM || ', ' || gb_common.f_get_id(v_pidm)); ROLLBACK; END; / EXIT SUCCESS COMMIT;