/*<TOAD_FILE_CHUNK>*/
-- OBJECT TYPE:	PL/SQL
-- OBJECT NAME:	SZP3009.SQL
-- AUTHOR: 		M. Dyies
-- DATE WRITTEN:	03/05/2007
--     
-- INPUT:			
--                  
--
-- DESCRIPTION:    When sgbstdn_exp_grad_date is null, Update sgbstdn_exp_grad_date
--                 to predetermined future date.
--
-- DATE REVISED: 12/08/2009 - Added (run in Audit mode) option. M. Dyies
--               01Mar2012  - Added rule for level = 'GR' and degree code ='41' per tkt 301 -- Sam Potts 
--				 11Sep2015  - Sam Potts - 1464 - Added rule level = 'GR', degree code ='99', degc_code_1 = 'XDGR'
--							--set expected Grad Date 2 years out.
--               06/16/2020 - IMM INC0087761
--                            1. Else added to conditional for error handling.
--                            2. Category 31 for DNP added.
--				 09/22/2020 - INC0099247  
--							  1. if the expected grad term prior than current term then make the expected grad term = current term
--							  2. Make the current term to be a parameter 
--							  3. Student population should be students who are currently taking class.

--SET SCAN OFF;
SET serveroutput ON;
SET echo OFF;
SET verify OFF;
SET feedback OFF;
SET tab OFF;

DECLARE
  --Pull Rows from SGBSTDN 
  CURSOR sgbstdn_c1 (vterm stvterm.stvterm_code%type)
  IS 
   SELECT  sgbstdn_pidm,
		sgbstdn_term_code_admit,
		sgbstdn_term_code_eff,
		spriden_first_name,
		spriden_id,  
		spriden_last_name,
		sgbstdn_levl_code,
		stvdegc_acat_code,
		sgbstdn_degc_code_1
  FROM   sgbstdn A  join ( select distinct sfrstcr_pidm
						   from sfrstcr
						   where  sfrstcr_term_code = vterm
								and sfrstcr_rsts_code IN 
									(select  STVRSTS_CODE from STVRSTS where STVRSTS_INCL_SECT_ENRL='Y' and STVRSTS_WITHDRAW_IND='N')
						  ) C on C.sfrstcr_pidm = A.sgbstdn_pidm	
         left join spriden on spriden.spriden_pidm = A.sgbstdn_pidm         
	     left join stvdegc on stvdegc_code = A.sgbstdn_degc_code_1
  WHERE  1=1  
	  and (A.sgbstdn_exp_grad_date IS NULL or A.SGBSTDN_TERM_CODE_GRAD < vterm)  
	  AND    spriden.spriden_change_ind IS NULL  
	  AND    sgbstdn_stst_code = 'AS'
	  AND    A.sgbstdn_term_code_eff >= A.SGBSTDN_TERM_CODE_ADMIT
	  AND 	 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 <= vterm
				  )
	  ;
	  
  
  
  /* old query LZ 9/23/2020
  SELECT *
  FROM   sgbstdn A,
         spriden,
	     stvdegc
  WHERE  A.sgbstdn_exp_grad_date IS NULL
  AND    spriden.spriden_pidm = A.sgbstdn_pidm
  AND    spriden.spriden_change_ind IS NULL
  AND    stvdegc_code = a.sgbstdn_degc_code_1
  AND    sgbstdn_stst_code = 'AS'
  AND    A.sgbstdn_term_code_eff >= A.SGBSTDN_TERM_CODE_ADMIT;
 */
     
 --Pull Rows from STVTERM 
  CURSOR stvterm_c1 (vtermf VARCHAR2)
  IS 
  SELECT *
  FROM   stvterm
  WHERE  stvterm_code = vtermf; 

  
  --variable declaration
  vpidm          NUMBER;
  vspridid       VARCHAR2 (9);
  vcnt1          NUMBER   (8):= 0;
  vcnt2          NUMBER   (8):= 0;
  vcrn           VARCHAR2 (5);
  vterm          VARCHAR2 (6);
  vterm2         VARCHAR2 (6);
  vterm3         VARCHAR2 (6);
  vtermf         VARCHAR2 (6);
  v_term_cur	 stvterm.stvterm_code%type;  --LZ 9/22/2020
  verrmsg        VARCHAR2 (132);
  verrcode       NUMBER;
  vlevel         VARCHAR2 (2);
  vuginterval    NUMBER := 6;
  vgrinterval    NUMBER := 4;
  vprinterval    NUMBER := 3;
  vstatdt        DATE   := SYSDATE;
  vtardt         DATE;
  vegdt          DATE;
  vacyrcd        VARCHAR2 (4);
  vdegcd         VARCHAR2 (2);
  vdcode         VARCHAR2 (6);
  vjobname       VARCHAR2 (30);
  vusername      VARCHAR2 (30);
  VFNAME         VARCHAR2 (60);
  VLNAME         VARCHAR2 (60);
  vlineout       VARCHAR  (2048);           --output line for dbms
  v_spool_commit BOOLEAN := FALSE;
  v_run          VARCHAR2 (1);


BEGIN 

   DBMS_OUTPUT.ENABLE(null); -- [IMM INC0087761 - Set to null]

--Get session parm from Banner
   v_run := gzpadmn.F_Get_Parm('&1','&2','&4','01');
   IF UPPER(v_run) = 'U' THEN
        v_spool_commit := TRUE;
        gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Commit Occurred. ' || SYSTIMESTAMP, v_spool_commit);
   ELSE
        v_spool_commit := FALSE;
        --v_spool_commit := TRUE;
        gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Audit Run Mode Rollback Occurred. ' || SYSTIMESTAMP, v_spool_commit);
   END IF;
   
   v_term_cur := gzpadmn.F_Get_Parm('&1','&2','&4','02');   --LZ 9/22/2020

      
   --write parm values to log file
   DBMS_OUTPUT.PUT_LINE('Parm1: ' || 'Run Mode = ' || v_run);
   DBMS_OUTPUT.PUT_LINE('Parm2: ' || 'Current Term = ' || v_term_cur);
   DBMS_OUTPUT.PUT_LINE('-');
   
   dbms_output.put_line ('PIDMS - ' || 'Expected grad term < current term, then make Expected grad term = current term ' );
   
   -- Pull records from sgbstdn_c1 
   FOR sgbstdn_row IN sgbstdn_c1(v_term_cur) LOOP
   	   vpidm    := sgbstdn_row.sgbstdn_pidm;
       vterm2   := sgbstdn_row.sgbstdn_term_code_admit;
       vterm3   := sgbstdn_row.sgbstdn_term_code_eff;
       vfname   := sgbstdn_ROW.spriden_first_name;
	   vspridid := sgbstdn_row.spriden_id;  
	   vlname   := sgbstdn_ROW.spriden_last_name;
	   vlevel   := sgbstdn_row.sgbstdn_levl_code;
	   vdegcd   := sgbstdn_row.stvdegc_acat_code;
	   vdcode   := sgbstdn_row.sgbstdn_degc_code_1;
		 
	vtermf := null;	 
    -------UG	                
	IF     vlevel = 'UG' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 600);
    -------GR
	ELSIF  vlevel = 'GR' 
	  and  vdegcd = '31' THEN	   
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 500); -- [IMM INC0087761 (2)]
    ELSIF  vlevel = 'GR' 
	  and  vdegcd = '41' THEN	   
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 300);   
	ELSIF  vlevel = 'GR' 
	  and (vdegcd = '42'
	   or  vdegcd = '43'
	   or  vdegcd = '56')
	  and  substr(vdcode,1,1) = 'M' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 400);
	ELSIF  vlevel = 'GR' 
	  and (vdegcd = '43'
	   or  vdegcd = '44')
	  and  substr(vdcode,1,1) = 'P' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 500);
    ELSIF  vlevel = 'GR' 
	  and (vdegcd = '43'
	   or  vdegcd = '44')
	  and  substr(vdcode,1,1) = 'E' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 500);
    ELSIF  vlevel = 'GR' 
	  and  vdegcd = '56'
	  and  substr(vdcode,1,4) = 'XDGR' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 200);  
    ELSIF  vlevel = 'GR' 			-----------------Sam 11Sep2015
	  and  vdegcd = '99'
	  and  substr(vdcode,1,4) = 'XDGR' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 200);  
    -------PR
	ELSIF  vlevel = 'PR' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 500);
	ELSIF  vlevel = 'U2' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 600);
	ELSIF  vlevel = 'U3' THEN
	       vtermf := TO_CHAR(TO_NUMBER(vterm2) + 600);
    ELSE   -- [IMM INC0087761 (1)]	   
	       dbms_output.put_line ('No Rule For: ' || vlevel || ' - ' || vdegcd || ' - ' || sgbstdn_row.sgbstdn_degc_code_1 || ' - ' || vpidm);
	END IF;
     	
		
	IF (vtermf is null or vtermf <= 600) THEN
       vlineout := 'No Rule For ' || vlevel || ' ' || vdegcd || ' ' || sgbstdn_row.sgbstdn_degc_code_1 || ' ' || vspridid || ' Admit term ' || vterm2 || ' Term Effective ' || vterm3;
       gzpadmn.p_spool_lis('&1', &2, '&3', 'errnotfnd',vlineout, v_spool_commit);
	
	else 
	    if SUBSTR(vtermf,1,4) > '2000' 
    	  and substr(vtermf,5,2) = '15' THEN
    	  vtermf := substr(vtermf,1,4) || '20';
        end if;
		
		----LZ 9/22/2020
		if vtermf < v_term_cur then
		   vtermf := v_term_cur;
		   vcnt2 := vcnt2 + 1; 
		   dbms_output.put_line (vpidm|| ' , ' );
		end if;   
		----LZ 9/22/2020

    	VTARDT := NULL;
    	VACYRCD := NULL;
        vjobname := 'SZP3009';
	
    	-- Pull records from stvterm_c1 
    	FOR stvterm_row IN stvterm_c1 (vtermf) LOOP
    	    vtardt := stvterm_row.stvterm_end_date;
    	    vacyrcd := stvterm_row.stvterm_acyr_code;	
        END LOOP;	   
      
--======================================================================================================
-- MAIN BEGIN
--======================================================================================================

        BEGIN
          -- Update sgbstdn expected grad Date to calulated date value 

          UPDATE sgbstdn
        	 SET sgbstdn_exp_grad_date  = vtardt,
                 sgbstdn_data_origin    = vjobname,
                 sgbstdn_user_id        = vusername,
        		 sgbstdn_activity_date  = vstatdt,
        		 sgbstdn_term_code_grad = vtermf,
        		 sgbstdn_acyr_code      = vacyrcd
           WHERE sgbstdn_term_code_eff  = vterm3
             --AND sgbstdn_exp_grad_date IS NULL   --commented out by LZ 9/23/2020
        	 AND sgbstdn_pidm           = vpidm;
            
		   vcnt1 := vcnt1 + 1;          
           gzpadmn.p_spool_lis('&1', &2, '&3', 'graddate',vpidm || ' ' || vspridid || ' ' || 'Expected Grad Date: '|| vtardt || ' ' || VFNAME || ' ' || VLNAME || ' ' || vtermf || ' ' || vlevel || ' ' || vacyrcd || ' ' || sgbstdn_row.sgbstdn_term_code_admit, v_spool_commit);

        EXCEPTION 
          WHEN OTHERS THEN 
             DBMS_OUTPUT.PUT_LINE('Line 1 ' || SUBSTR(SQLERRM, 1, 500) || vterm2 || ' ' || vtermf);
        	 DBMS_OUTPUT.PUT_LINE('Line 2 ' || 'INFO: ' || vpidm ||'Expected Grad Date: '|| vtardt || ' ' || VFNAME || ' ' || VLNAME || ' ' || vlevel || ' ' || vacyrcd);
        	 DBMS_OUTPUT.PUT_LINE('  ');
			 DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
             DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
        END;
    END IF;	   

   END LOOP;
   
   DBMS_OUTPUT.PUT_LINE('Total Update Count =  ' || vcnt1);
   DBMS_OUTPUT.PUT_LINE('Update Count Due to Expected grad term prior than current term  =  ' || vcnt2);
   
   IF vcnt1 = 0 THEN
      BEGIN
        gzpadmn.p_spool_lis('&1', &2, '&3', 'INFO','No Data Found for Update this Run', v_spool_commit);
      END;
   END IF;

   IF UPPER(v_run)  = 'U' THEN
        COMMIT;
     ELSE
        ROLLBACK;
     END IF; 
 
    
EXCEPTION
   WHEN OTHERS THEN 
   		verrmsg := SQLERRM;
        verrcode := SQLCODE;
        DBMS_OUTPUT.PUT_LINE('Line 1 ' || SUBSTR(SQLERRM, 1, 500) || vterm2 || ' ' || vtermf);
		DBMS_OUTPUT.PUT_LINE('Fatal Error ' || vfname || ' ' || vlname || ' ' || vspridid || ' ' || verrmsg || ' ' || verrcode);
		DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
        --gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Fatal Error  -- rollback will occur: '||vpidm ||' '||SQLERRM, v_spool_commit);
        rollback;

END;
/
/*<TOAD_FILE_CHUNK>*/
EXIT;