This came up at end of term 201920. The shrtckg_incomplete_ext_date was incorrectly set to a future date (from SOATERM), which kept the process from running.

A ticket to the Ellucian Action Line suggested updating the shrtckg_incomplete_ext_date to SYSDATE, which didn't quite resolve the problem, as SYSDATE by default adds the current timestamp as well as the current date in the background. It needed to be SYSDATE with a 12:00am timestamp (00:00:00). The correct solution is below.


This issue was initially reported in ticket https://support.nwfsc.edu/helpdesk/tickets/22039


--1. back up SHRTCKG (just in case)

create table SHRTCKG_20190509_BAK as select * from SHRTCKG;


--2. set all students with an Incomplete ('I') to the current date WITH 00:00:00 timestamp

UPDATE SHRTCKG

SET SHRTCKG_INCOMPLETE_EXT_DATE = trunc(SYSDATE)        --need to set timestamp portion to 00:00:00

WHERE SHRTCKG_TERM_CODE=201910

AND SHRTCKG_GRDE_CODE_FINAL='I';

COMMIT;


UPDATE from 8/23/2019 from ticket 25606

This came up at the beginning of term 202010. This time all I and I. grades for terms 201920 and 201930 needed to be updated to a future date (12/16/2019).

Not sure why this was different.


--UPDATE Incomplete Extension Date for I grades (201920,201930)

--https://support.nwfsc.edu/helpdesk/tickets/25606

--target field to update: SHRTCKG_INCOMPLETE_EXT_DATE

--*NOTE: the erroneous SHRTCKG_INCOMPLETE_EXT_DATE matches the SHRTCKN_PTRM_END_DATE

SELECT SOBPTRM_TERM_CODE, SOBPTRM_PTRM_CODE, SOBPTRM_DESC, SOBPTRM_START_DATE, SOBPTRM_END_DATE, SOBPTRM_CENSUS_DATE, SOBPTRM_INCOMPLETE_EXT_DATE

FROM sobptrm

WHERE SOBPTRM_TERM_CODE IN (201920,201930)

ORDER BY 1;

SELECT

SHRTCKG_PIDM, SHRTCKG_TERM_CODE, SHRTCKG_TCKN_SEQ_NO, SHRTCKG_GRDE_CODE_FINAL, SHRTCKG_INCOMPLETE_EXT_DATE, SHRTCKG_TERM_CODE_GRADE

FROM SHRTCKG

WHERE SHRTCKG_TERM_CODE IN (201920,201930)

AND SHRTCKG_GRDE_CODE_FINAL='I'

ORDER BY 2;

--0. backup

CREATE TABLE SHRTCKG_20190823_BAK AS SELECT * FROM SHRTCKG;

--1. update SHRTCKG_INCOMPLETE_EXT_DATE

UPDATE SHRTCKG

SET SHRTCKG_INCOMPLETE_EXT_DATE='16-DEC-2019'

WHERE SHRTCKG_TERM_CODE IN (201920,201930)

AND SHRTCKG_GRDE_CODE_FINAL IN ('I','I.');

--396

COMMIT;