-- Object Type:  PL/SQL
-- Object Name:  szp3051.sql
-- Author     :  David Buchannon, Jr.
-- Description:  Sets Altpins in SPRAPIN, from SPAAPIN for a particular term SOS CWE
--
-- Create     :  2007/02/12   DB: Created
-- Change     :  2007/05/25   DB: Chg-01: Remove CWE Alt-Pin Search
-- 
--***************************************************************************************
-- Edited by Naomi Rigby 7/20/2010
-- Modified by Monica Chen 8/5/2010 
-- Modified to add logic to set alt pins on students enrolled in 1 of the new 'CA' Certificate Programs - GRAD level
-- where the GRAD student is enrolled in the 'GCRT' SOVLCUR_degree_code only - if they are enrolled in another 'normal' GR 
-- program - do NOT set alt pin 
-- 
--***************************************************************************************
-- Modified by Monica Chen 7/28/2011
-- Modified to 1.) Remove the selection of 'U2' and 'U3' leaving only 'UG' to be selected.  2.) Remove the selection for student_type = 'R', leaving 'L' and 'T'.
--
--***************************************************************************************
--
--***************************************************************************************
-- Modified by Rodney Clark 4/22/2014
-- Modified to 1.) Added fields to cursors that use v_rec  to comply with upgrade
--
--***************************************************************************************
-- sN- TASK0019223 Buchannon, David 'J' styp t o selection


set verify off;
set feedback off;
set echo off;
set tab off;
set serveroutput ON;



DECLARE

--***************************************************************************************
-- Modified:  Monica Chen 7/28/2011 
--***************************************************************************************
CURSOR c_altpins_sos ( v_sum_admt_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                       v_reg_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                       v_process sprapin.sprapin_process_name%TYPE) IS
                    SELECT v_reg_term AS reg_term, 
                           sgbstdn_pidm, 
                           v_process AS process_name,
                           SUBSTR(dbms_random.random,2,6) AS apin, 
                           SYSTIMESTAMP,
                           -- adding fields to comply with Banner upgrade
                           null as v_surrogate_id,
                           null as v_version,
                           user as v_user,
                           'szp3051' as v_process,
                           null as vpdi_code
                    FROM sgbstdn A
                    WHERE A.sgbstdn_term_code_eff = (SELECT MAX(b.sgbstdn_term_code_eff) FROM sgbstdn b
                                                            WHERE A.sgbstdn_pidm = b.sgbstdn_pidm
                                                            AND b.sgbstdn_term_code_eff <= v_reg_term)
                    AND A.sgbstdn_levl_code IN ('UG')
                    AND A.sgbstdn_styp_code IN ('L','T','J')
                    AND A.sgbstdn_term_code_admit = v_reg_term
                    AND NOT EXISTS (SELECT 'x' FROM sprapin 
                            WHERE sprapin_term_code = v_reg_term
                            AND sprapin_pidm = A.sgbstdn_pidm);


--***************************************************************************************
-- Added by Naomi Rigby 7/20/2010--cursor for GRAD - CERTIFICATE
-- Modified:  Monica Chen 8/5/2010 
--***************************************************************************************
CURSOR c_altpins_by_grad_cert (v_pidm SOVLCUR.SOVLCUR_PIDM%TYPE,                              
                               v_reg_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                               v_process sprapin.sprapin_process_name%TYPE) IS
                               

            SELECT v_reg_term AS reg_term, 
                   v_pidm,
                   v_process AS process_name,
                   SUBSTR(dbms_random.random,2,6) AS apin,
                   SYSTIMESTAMP AS,
                   -- adding fields to comply with Banner upgrade
                   null as v_surrogate_id,
                   null as v_version,
                   user as v_user,
                   'szp3051' as v_process,
                   null as vpdi_code 
            FROM DUAL
           WHERE NOT EXISTS (SELECT 'x' 
                               FROM sprapin
                              WHERE sprapin_term_code = v_reg_term
                                AND sprapin_pidm = v_pidm);
           
                             
--Chg-01
--cursor c_altpins_cwe ( v_sum_admt_term sgbstdn.sgbstdn_term_code_eff%type,
--                       v_reg_term sgbstdn.sgbstdn_term_code_eff%type,
--                       v_process sprapin.sprapin_process_name%type) is
--select v_reg_term as reg_term, sgbstdn_pidm, v_process as process_name,
--substr(dbms_random.random,2,6) as apin, systimestamp
--from sgbstdn a
--where a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff) from sgbstdn b
--                                where a.sgbstdn_pidm = b.sgbstdn_pidm
--                                and b.sgbstdn_term_code_eff <= v_reg_term)
--and a.sgbstdn_levl_code in ('UG','U2','U3')
--and a.sgbstdn_styp_code = 'N'
--AND a.sgbstdn_term_code_admit in (v_sum_admt_term, v_reg_term)
--and not exists (select 'x' from sprapin 
--                where sprapin_term_code = v_reg_term
--                and sprapin_pidm = a.sgbstdn_pidm);
				

CURSOR c_spriden (v_pidm spriden.spriden_pidm%TYPE) IS
                    SELECT * FROM spriden 
                    WHERE spriden_pidm = v_pidm
                    AND spriden_change_ind IS NULL;

CURSOR c_sprapin (v_pidm spriden.spriden_pidm%TYPE, v_term varchar2) IS
     SELECT sprapin_pidm FROM sprapin
     WHERE sprapin_pidm = v_pidm
     AND sprapin_term_code = v_term;


--***************************************************************************************
-- Edited by Naomi Rigby 7/20/2010--cursor for GRAD - CERTIFICATE
-- Modified:  Monica Chen 8/5/2010 
--***************************************************************************************
-- special GRAD - CERTIFICATE
/*
CURSOR c_grad(v_reg_term varchar2) IS
    SELECT COUNT(*) AS vcount, 
       SOVLCUR_PIDM 
    FROM SOVLCUR 
    WHERE SOVLCUR_TERM_CODE = v_reg_term 
        AND SOVLCUR_CACT_CODE = 'ACTIVE'
        AND SOVLCUR_LEVL_CODE = 'GR'
        AND SOVLCUR_LMOD_CODE = 'ADMISSIONS'
        AND sovlcur_degc_code = 'GCRT'   
    GROUP BY SOVLCUR_PIDM
    HAVING COUNT(*) <= 1
    ORDER BY SOVLCUR_PIDM;*/
CURSOR c_grad(v_reg_term varchar2) IS
  SELECT SOVLCUR_PIDM 
    FROM SOVLCUR 
    WHERE SOVLCUR_TERM_CODE = v_reg_term 
      AND SOVLCUR_TERM_CODE_ADMIT = v_reg_term
      AND SOVLCUR_LMOD_CODE = 'LEARNER'
      AND SOVLCUR_CACT_CODE = 'ACTIVE'
      AND SOVLCUR_LEVL_CODE = 'GR'
      AND SOVLCUR_DEGC_CODE = 'GCRT' 
      AND SOVLCUR_CURRENT_IND = 'Y'
      AND SOVLCUR_ACTIVE_IND = 'Y'
    ORDER BY SOVLCUR_PIDM;

--variable declarations
v_cnt number:=0;
v_total_cnt number:=0;
v_total_gradcnt number:=0;
v_multi_grad varchar2(1) := null;
v_sum_admt_term sgbstdn.sgbstdn_term_code_eff%TYPE;
v_reg_term sgbstdn.sgbstdn_term_code_eff%TYPE;
v_run_mode varchar2(1);
v_process sprapin.sprapin_process_name%TYPE:= 'TREG';
v_bad_pin sprapin.sprapin_pin%TYPE:= '111111';
v_failsafe_pin sprapin.sprapin_pin%TYPE:= '482351';
v_spool_commit BOOLEAN:=FALSE;
v_create_fall_ticket varchar2(1) := 'N';
v_fall_ticket_term_code varchar2(6);
rec_sprapin c_sprapin%ROWTYPE;

PROCEDURE p_insert_apin(v_rec c_altpins_sos%ROWTYPE)
IS


BEGIN
	 INSERT INTO sprapin VALUES v_rec;

END p_insert_apin;

PROCEDURE p_validate_pin(v_altpin IN OUT sprapin.sprapin_pin%TYPE) IS

v_bad_pin sprapin.sprapin_pin%TYPE := '111111';
v_cnt number := 0;
BEGIN
WHILE v_altpin = v_bad_pin LOOP
	v_cnt := v_cnt + 1;
	IF v_cnt > 10 THEN
	   v_altpin:= '751687';
	   exit;
	END IF;
    IF v_altpin = v_bad_pin THEN
	  	 v_altpin:= SUBSTR(dbms_random.random,2,6);
	END IF;
END LOOP;
END p_validate_pin;


PROCEDURE p_set_pins_sos (v_sum_admt_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                          v_reg_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                          v_process sprapin.sprapin_process_name%TYPE,
					      v_cnt OUT number) IS
BEGIN
	v_cnt :=0;
	FOR v_rec IN c_altpins_sos(v_sum_admt_term,v_reg_term,v_process) LOOP
        v_cnt:= v_cnt + 1;
		p_validate_pin(v_rec.apin);
		p_insert_apin(v_rec);
          IF v_create_fall_ticket = 'Y' THEN
               OPEN c_sprapin(v_rec.sgbstdn_pidm,v_fall_ticket_term_code);
               FETCH c_sprapin INTO rec_sprapin;               
               IF c_sprapin%NOTFOUND THEN
                    --set the term in the insertion record to the fall term
                    v_rec.reg_term:= v_fall_ticket_term_code;
                    p_insert_apin(v_rec);
               END IF;
               CLOSE c_sprapin;    
          END IF;
END LOOP;
END p_set_pins_sos;



--***************************************************************************************
-- Edited by Naomi Rigby 7/20/2010--cursor for GRAD - CERTIFICATE
-- Modified:  Monica Chen 8/5/2010 
--***************************************************************************************
PROCEDURE p_set_pins_by_grad (v_pidm sovlcur.sovlcur_pidm%TYPE,                             
                              v_reg_term sgbstdn.sgbstdn_term_code_eff%TYPE,
                              v_process sprapin.sprapin_process_name%TYPE,
							  v_cnt OUT number) IS

        
        CURSOR c_multi_grad(v_pidm SOVLCUR.SOVLCUR_PIDM%TYPE) IS
             
               SELECT 'X' 
                FROM SOVLCUR a 
               WHERE SOVLCUR_CACT_CODE = 'ACTIVE'
                 AND SOVLCUR_LEVL_CODE = 'GR'
                 AND SOVLCUR_LMOD_CODE = 'LEARNER'
                 AND SOVLCUR_CURRENT_IND = 'Y'
                 AND SOVLCUR_ACTIVE_IND = 'Y'
                 AND SOVLCUR_DEGC_CODE <> 'GCRT'
                 AND SOVLCUR_PIDM = v_pidm 
               GROUP BY SOVLCUR_PIDM
               HAVING COUNT(*) >= 1;
               
BEGIN
	--dbms_output.put_line(v_pidm);
   
    v_multi_grad := null;
    OPEN c_multi_grad(v_pidm);
    FETCH c_multi_grad into v_multi_grad;
    CLOSE c_multi_grad;
   
    v_cnt :=0;
    IF v_multi_grad is null THEN
       --dbms_output.put_line('Only GCRT program for pidm ' || v_pidm);
       FOR v_rec IN c_altpins_by_grad_cert(v_pidm,v_reg_term,v_process) LOOP
      
          v_cnt:= v_cnt + 1;
	      p_validate_pin(v_rec.apin);
	      p_insert_apin(v_rec);
          IF v_create_fall_ticket = 'Y' THEN
             OPEN c_sprapin(v_pidm,v_fall_ticket_term_code);
             FETCH c_sprapin INTO rec_sprapin;               
             IF c_sprapin%NOTFOUND THEN
                --set the term in the insertion record to the fall term
                v_rec.reg_term:= v_fall_ticket_term_code;
                p_insert_apin(v_rec);
             END IF;
             CLOSE c_sprapin;    
          END IF;
       END LOOP;
    END IF;
    
END p_set_pins_by_grad;

--Chg-01
--procedure p_set_pins_cwe  (v_sum_admt_term sgbstdn.sgbstdn_term_code_eff%type,
--                          v_reg_term sgbstdn.sgbstdn_term_code_eff%type,
--                          v_process sprapin.sprapin_process_name%type,
--					      v_cnt out number) is
--begin
--	v_cnt :=0;
--	for v_rec in c_altpins_cwe(v_sum_admt_term,v_reg_term,v_process) loop
--        v_cnt:= v_cnt + 1;
--		p_validate_pin(v_rec.apin);
--		p_insert_apin(v_rec);		
--end loop;
--end p_set_pins_cwe;

BEGIN
    dbms_output.enable(1000000);
    dbms_output.put_line('starting....' || SYSTIMESTAMP);

    --Get parms from Banner
    --v_sum_admt_term:= gzpadmn.f_get_parm ('&1','&2','&4','01'
    v_reg_term := gzpadmn.f_get_parm ('&1','&2','&4','01');
    v_create_fall_ticket := gzpadmn.f_get_parm ('&1', '&2', '&4', '02');
    v_fall_ticket_term_code := gzpadmn.f_get_parm ('&1', '&2', '&4', '03');
    v_run_mode := gzpadmn.f_get_parm ('&1','&2','&4','04');

    --debugging variables
    --v_sum_admt_term:= '200730';
    --v_reg_term:= '200810';
    --v_run_mode := 'A';
    
    dbms_output.put_line('Parm - Registration Term: ' || v_reg_term);
    DBMS_OUTPUT.put_line ('Parm - Create Fall Time Ticket: ' || v_create_fall_ticket);
    DBMS_OUTPUT.put_line ('Parm - Fall Term For Summer Students: ' || v_fall_ticket_term_code);
    dbms_output.put_line('Parm - Run Mode: ' || v_run_mode);

    IF v_run_mode = 'A' THEN
       v_spool_commit := TRUE;
    END IF;

    p_set_pins_sos(v_sum_admt_term,v_reg_term,v_process,v_cnt);
    gzpadmn.p_spool_lis('&1', &2, NULL, NULL,'SOS: ' || v_cnt,v_spool_commit);

    v_total_cnt:= v_total_cnt + v_cnt; 
    
    --Modified by Monica Chen 08/05/20100 
    FOR c_grad_rec IN c_grad(v_reg_term) LOOP                                
                            
        --dbms_output.put_line('first population: pidm ' || c_grad_rec.SOVLCUR_PIDM);        
        p_set_pins_by_grad(c_grad_rec.SOVLCUR_PIDM,v_reg_term,v_process,v_cnt);
        v_total_gradcnt:= v_total_gradcnt + v_cnt; 
    END LOOP;
  
    gzpadmn.p_spool_lis('&1', &2, NULL, NULL,'GRAD-GCRT' || ':  ' || v_total_gradcnt,v_spool_commit);
    v_total_cnt:= v_total_cnt + v_total_gradcnt; 
   

    gzpadmn.p_spool_lis('&1', &2, NULL, NULL,' ',v_spool_commit);
    gzpadmn.p_spool_lis('&1', &2, NULL, NULL,'Run assigned ' || v_total_cnt  || ' alternate pins',v_spool_commit);
    --dbms_output.put_line ('finished .... ' || systimestamp,v_spool_commit);

    IF v_run_mode = 'A' THEN
    ROLLBACK;
        dbms_output.put_line('Audit Mode Completed');
    ELSIF v_run_mode = 'U' THEN
        COMMIT;
        dbms_output.put_line('Update Mode Completed');
    ELSE
        ROLLBACK;
        dbms_output.put_line('Invalid Run Mode - Rollback Completed');
    END IF;
    dbms_output.put_line ('finished .... ' || SYSTIMESTAMP);

/*
    EXCEPTION 
    WHEN others THEN
    dbms_output.put_line('Unhandled Error');
    ROLLBACK;
   */
END;
/
Exit;