--=============================================================================
--     NAME: SZPSTIG
--  PURPOSE: Create Popsel of students who graduated (shrdgmr_degs_code = AW )
--           for parm term and have NOT registered in the NEXT TERM 
--	  TITLE: Graduated, NOT Registered in Next term
--	Ticket/User		1071 / Dan Hill
--  DATE WRITTEN:	16OCT2014
--    WRITTEN BY:   Sam Potts, F. Newland
--          PARM: 	01: Graduation Term 
--   TABLES USED:  shrdgmr, stvterm, sfrstcr 
--       HISTORY:         
-- WHEN         WHO   WHAT
-- 16-OCT-2014  SP    From registrar's office:  Ticket #1071.
--=============================================================================
	set echo off;
	set verify off;
	set feedback off;
	set serveroutput ON SIZE UNLIMITED;
	whenever sqlerror exit failure;
--=============================================================================
   DECLARE 
	-----------------User Parms
   v_term		varchar2(6);
   v_next_term	varchar2(6); 
   v_begin		timestamp;
   v_end		timestamp;
   v_duration	interval day to second(1); 
   f1 varchar2(10) := '"';   
   f2 varchar2(10) := '","';
   f3 varchar2(10) := ','; 
	
   v_reg_ind		varchar(1) := null ;
   v_reg_rpt		varchar(30) := 'Registered Rpt' ;
   v_not_reg_rpt	varchar(30) := 'NOT Registered Rpt' ;
	
   v_application varchar(30) := 'STUDENT';		
   v_creator_id	varchar(30) := 'GDH0002';
   v_user		varchar(30);   
   v_line_out	varchar2(2048):= null;  
   v_count      number := 0; 
   v_pidm       spriden.spriden_pidm%type;
   v_id         spriden.spriden_id%type;
   v_fname      varchar2(100);
   v_lname      varchar2(100);
   v_degs_code  shrdgmr.shrdgmr_degs_code%type;
   v_popsel     varchar2(1);
   v_flag_1     varchar2(1);
   v_flag_2     varchar2(1);
   v_si_count   number;
   v_no_count   number;

--=============================================================================
--- Cusors
--=============================================================================
--  Driver Cursor 
--=============================================================================
    cursor c_driver (v_term varchar2, v_next_term varchar2)
	is  
    select unique shrdgmr_term_code_grad as GRAD_TERM ,
                  shrdgmr_pidm as pidm , --only thing needed from the cursor
                  shrdgmr_degs_code as GRAD_CODE 
    from shrdgmr       
        where shrdgmr.shrdgmr_term_code_grad = v_term 
          and shrdgmr.shrdgmr_degs_code = 'AW' 
	order by szg8003.f_get_id(shrdgmr_pidm);

     rec_driver c_driver%rowtype;

--=============================================================================
    cursor c_next_term ( v_term varchar2 )
	is
	select min(STVTERM_CODE) as v_next_term
		from STVTERM
			where STVTERM_CODE > v_term;

    rec_next_term c_next_term%rowtype;
--=============================================================================
-- FUNCTIONS
--=============================================================================

  function f_is_in_next_term(in_pidm varchar2,
	                         in_next_term varchar2)
   return varchar2
   is 
   v_return_val varchar2(10);
   v_next_flag  varchar2(1);
 
   cursor c_is_student_next_term
   is
   select sfrstcr_pidm
   from   sfrstcr
   where  sfrstcr_pidm = in_pidm 
    and   sfrstcr_term_code = in_next_term
    and   sfrstcr_rsts_code in ( 'RE','RW' );


	v_student_next_term_record c_is_student_next_term%rowtype;
   begin 

	v_return_val := null;
	v_next_flag  := null;
	open   c_is_student_next_term;
	fetch  c_is_student_next_term into v_student_next_term_record;
	  if  (c_is_student_next_term%found) then
	   v_next_flag := 'Y';
      else 
	   v_next_flag := 'N';
	 end if;
	close c_is_student_next_term;

	v_return_val := v_next_flag;
	return v_return_val;
   end f_is_in_next_term;

--=============================================================================
-- *** BEGIN MAIN ***
--=============================================================================
   BEGIN
	v_begin := systimestamp;	
	
	dbms_output.enable (10000000);
	dbms_output.put_line('Start SZPSTIG:[' ||  v_begin || ']');
    v_term	:=  gzpadmn.F_Get_Parm('&1','&2','&4','01');
	DBMS_OUTPUT.PUT_LINE('Parm Term 01:[' || v_term || ']');
    dbms_output.put_line('Job_Name:[' ||  '&1' || ']');
    dbms_output.put_line('Job Number:['|| '&2' || ']');
    dbms_output.put_line('Databse Instance:['|| '&4' || ']');
    dbms_output.put_line('Run by:['|| '&3' || ']');
	v_user := UPPER('&3'); 
	
		open  c_next_term ( v_term );
		fetch c_next_term into v_next_term;
		close c_next_term;

	dbms_output.put_line( 'Next Term Code:[' || v_next_term || ']');	

    delete from GLBEXTR
	      where GLBEXTR_APPLICATION = 'STUDENT'  
			and GLBEXTR_SELECTION   = 'SZPSTIG' 
			and GLBEXTR_CREATOR_ID  = 'GDH0002'
			and GLBEXTR_SYS_IND = 'S';   		
	
   v_line_out := 'STUDENT_ID' || f3 ||
                 'LAST_NAME'  || f3 ||
				 'FIRST_NAME' || f3 ||
			     'DEGS_CODE'  || f3 ||	
                 'POPSEL';
	gzpadmn.p_spool_lis('&1', &2, null,null , v_line_out);

    v_si_count := 0;
    v_no_count := 0;
 
    FOR rec_driver IN c_driver(v_term , v_next_term) LOOP	
		v_pidm := null;
		v_pidm := rec_driver.pidm;

		v_id   :=  null;
		v_id   := gb_common.f_get_id(v_pidm);

		v_fname := null;
		v_fname := szg8003.f_get_first(v_pidm);
		
		v_lname := null;
		v_lname := szg8003.f_get_last(v_pidm);

        v_degs_code := null;
        v_degs_code := rec_driver.grad_code;

        v_flag_1 := null;
        v_flag_1 :=  f_is_in_next_term(v_pidm, v_next_term);
		 if (v_flag_1 = 'Y') then 
			v_popsel := 'N';
	        v_no_count := v_no_count +1;
	    else
			v_popsel := 'Y';
			v_si_count := v_si_count +1;
	   end if;

	  if (v_popsel = 'Y') then 
        insert into glbextr (
			        glbextr_application,   
					glbextr_selection,
					glbextr_creator_id,
					glbextr_user_id,	
					glbextr_key,
					glbextr_activity_date,
					glbextr_sys_ind,
					glbextr_slct_ind)
		   values ('STUDENT',
		           'SZPSTIG',
				   'GDH0002', 
			        v_user,
				    rec_driver.pidm,
				    sysdate,
				    'S',
				    null);
      end if;

		v_line_out := f1 || v_id        ||f2	
				         || v_lname     || f2
				         || v_fname     || f2
				         || v_degs_code || f2
				         || v_popsel    || f2
				         || f1;												
		gzpadmn.p_spool_lis('&1', &2, null,null , v_line_out);

		v_count := 	v_count + 1; 
	end loop;    

	v_end := systimestamp;
	v_duration := (v_end - v_begin) day to second;		

  DBMS_OUTPUT.PUT_LINE('OUTPUT Records:[' || v_count || ']');  
  DBMS_OUTPUT.PUT_LINE('YES Count:[' || v_si_count  || ']');
  DBMS_OUTPUT.PUT_LINE('NO Count:['  || v_no_count || ']');
  DBMS_OUTPUT.PUT_LINE('Finished SZPSTIG:[' || TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss') || ']');
  DBMS_OUTPUT.PUT_LINE('Program Run Time:[' || v_duration || ']');
	
	EXCEPTION WHEN others THEN
	  dbms_output.put_line('Unhandled Error: ');
	  dbms_output.put_line(SQLERRM);

   END;
/
   EXIT SUCCESS COMMIT;