/***********************************************************

*** CLEARINGHOUSE DATA CLEANUP 2020      ***

*** 7.17.2020 only steps 1 and 4 in use                 ***

***********************************************************/

------------------------------------------------------------

--1. DELETE EMPTY SFBETRM RECORDS (invalid to Clearinghouse)

------------------------------------------------------------

--spool /tmp/delete_sfbetrm.lst;

select count(*) from sfbetrm

where not exists

        (select * from sfrstcr

        where sfbetrm_pidm = sfrstcr_pidm

          and sfbetrm_term_code = sfrstcr_term_code);

delete from sfbetrm

where not exists

        (select * from sfrstcr

        where sfbetrm_pidm = sfrstcr_pidm

          and sfbetrm_term_code = sfrstcr_term_code);

--spool off;

commit;

--exit;


/*** DISCONTINUED as of 9/6/2019 - RUN UC4 SCRIPT INSTEAD (see step 4 below)

--2. --update the expected grad date if it precedes the end of term date 

--(Greg runs and adjusts the parameters)

-- grad date precedes term end ???

--NO: MATT ADJUSTED THE PARAMS TO...?

UPDATE saturn.sgbstdn MAIN

set sgbstdn_exp_grad_date = '08-AUG-2019'

where sgbstdn_exp_grad_date <= '08-AUG-2019'

and sgbstdn_term_code_eff=

(select max(sub.sgbstdn_term_code_eff)

       from sgbstdn sub

       where main.sgbstdn_pidm = sub.sgbstdn_pidm)

and exists

(select 'x'

  from saturn.sfrstcr

   where main.sgbstdn_pidm = sfrstcr_pidm

   AND sfrstcr_term_code = '201930');

COMMIT;

***/


/** NO LONGER IN USE

--3. --WHAT DOES THIS DO? UNKNOWN; discontinued as of 9/6/2019

--(Matt runs this one)

--nsc_no_nsc_equiv_for_00

UPDATE SATURN.SFBETRM

   SET SFBETRM_ESTS_CODE = 'WD'

WHERE SFBETRM_TERM_CODE = '201930'

       AND SFBETRM_ESTS_CODE IN ('EL','RS')

       --and SFBETRM_ESTS_CODE in ('EL','RS','AD')

       AND SFBETRM_TMST_CODE = '00';

COMMIT;

**/


---------------------------------------------------------------------------

--4. Populate Expected Grad Date (previously in UC4)

---------------------------------------------------------------------------

/**************************************************************** 

*** POPULATE THE EXPECTED GRADUATION DATE FOR SSCR PROCESSING  ***

*** Ken F., GT modified 1/27/2015 for UC4 weekly                                                      ****

*** corrected on 6/1/2016 >= in term_code_admit                                                       ****

*** added sysdate and user_id for audit trail 9/13/2016                                               ****

*** 2/12/2020 -additional section for expired grad dates                                              ****

***           -taken off weekly schedule; on demand only                                                ****

*** 7/17/2020 -section 4: currently registered students                                                ****

***************************************************************************************************/

--1. 'CC','VC','ATD' with null exp grad date 

UPDATE  sgbstdn t1

SET     sgbstdn_exp_grad_date = ( SELECT  t2.stvterm_end_date + 365

                        FROM    stvterm t2

                        WHERE   t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 IN ('CC','VC','ATD')

                        AND     sgbstdn_term_code_admit >= '201110'

                            ),

        sgbstdn_activity_date = sysdate,

        sgbstdn_user_id = 'UC4-DBA'

WHERE   EXISTS ( SELECT 1

                 FROM   stvterm t2

                 WHERE  t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 IN ('CC','VC','ATD')

                        AND     sgbstdn_term_code_admit >= '201110'

                );

COMMIT;


--2. 'AA','AAS','AS','BAS','BSE','BSN' with null exp grad date

UPDATE  sgbstdn t1

SET     sgbstdn_exp_grad_date = ( SELECT  t2.stvterm_end_date + 1095

                        FROM    stvterm t2

                        WHERE   t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 IN ('AA','AAS','AS','BAS','BSE','BSN')

                        AND     sgbstdn_term_code_admit >= '201110'

                            ),

        sgbstdn_activity_date = sysdate,

        sgbstdn_user_id = 'UC4-DBA'

WHERE   EXISTS ( SELECT 1

                 FROM   stvterm t2

                 WHERE  t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 IN ('AA','AAS','AS','BAS','BSE','BSN')

                        AND     sgbstdn_term_code_admit >= '201110'

                );

COMMIT;


--3. 'ND' with null exp grad date

--NEW SECTION SPLITTING OUT 'ND' RECORDS FOR 10 YEARS OUT 1/27

--UPDATE: change to 5 years out per Frazier/Durham 11/14/2016

UPDATE  sgbstdn t1

SET     sgbstdn_exp_grad_date = ( SELECT  t2.stvterm_end_date + 1825

                        FROM    stvterm t2

                        WHERE   t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 = 'ND'

                        AND     sgbstdn_term_code_admit >= '201110'

                            ),

        sgbstdn_activity_date = sysdate,

        sgbstdn_user_id = 'UC4-DBA'

WHERE   EXISTS ( SELECT 1

                 FROM   stvterm t2

                 WHERE  t1.sgbstdn_term_code_admit = t2.stvterm_code

                        AND     t1.sgbstdn_exp_grad_date IS NULL

                        AND     sgbstdn_degc_code_1 = 'ND'

                        AND     sgbstdn_term_code_admit >= '201110'

                );

COMMIT;


/* 7.17.2020 new section GT */

--4. currently registered students with expired exp_grad_date

UPDATE  sgbstdn t1

SET     sgbstdn_exp_grad_date = (SELECT  t2.stvterm_end_date

                                 FROM    stvterm t2

                                 WHERE   t2.stvterm_code=&&term

                                 ),

        sgbstdn_activity_date = SYSDATE,

        sgbstdn_user_id = 'UC4-DBA'

WHERE EXISTS (SELECT 1

              FROM sfrstcr t2

              WHERE t1.sgbstdn_pidm=t2.sfrstcr_pidm

              AND t2.sfrstcr_term_code=&term

              AND t1.sgbstdn_exp_grad_date is not null

              AND t1.sgbstdn_exp_grad_date <= SYSDATE

              AND t1.SGBSTDN_TERM_CODE_EFF = (SELECT MAX(t3.SGBSTDN_TERM_CODE_EFF)

                                              FROM sgbstdn t3

                                              WHERE t3.SGBSTDN_PIDM=t1.SGBSTDN_PIDM

                                              AND   t3.SGBSTDN_STST_CODE='AS'));

COMMIT;