CTM Data Updates Rules Specific to Admission Transactions

To be consistent with existing processes that update this data, the application data update rules are as follows for the following entities:

Entity	Rules
Academic Interests	If data exists on ADM_INTRST_HDR for an existing EMPLID and ACAD_CAREER and the max ADM_INTRST_HDR.EFFDT is not equal to %Date, a new row is inserted with EFFDT = %Date. Rows are inserted into ADM_INTERESTS for each academic interest populated on SCC_STG_ADMINT. PRIORITY is populated based on ADM_INTRST_HDR.USE_PRIORITY. If data exists on ADM_INTRST_HDR for an existing EMPLID and ACAD_CAREER and the max ADM_INTRST_HDR.EFFDT is equal to %Date, a row is not inserted into ADM_INTRST_HDR. Rows are inserted into ADM_INTERESTS only where SCC_STG_ADMINT.EXT_SUBJECT_AREA does not exist on ADM_INTERESTS for the current effective date. If ADM_INTRST_HDR.USE_PRIORITY is ‘Y’, the max ADM_INTEREST.PRIORITY is determined and incremented to the next number when new rows are inserted.
External Academic Data	A new Data Number (EXT_DATA_NBR) is inserted to EXT_ACAD_DATA if the EXT_ORG_ID on the staging record SCC_STG_ACADHST exists on the core record ACAD_HISTORY. The max EXT_DATA_NBR existing on EXT_ACAD_DATA is determined and incremented to the next number on insert. All child records are populated with the same EXT_DATA_NBR that is inserted on EXT_ACAD_DATA if the field exists on the child record. Exception: For the record EXT_DEGREE, if data exists on EXT_DEGREE where DESCR, DEGREE_DT, DEGREE_STATUS and LS_DATA_SOURCE = data from staging record (SCC_STG_EXTDEGR), a new row is not inserted.
General Materials	A new record is always inserted. The max GENL_MATL_NBR is determined and new incremented to the next number when new rows are inserted.
Residency Official and Residency Appeal	If the keys (INSTITUTION, ACAD_CAREER and EFFECTIVE_TERM) do not exist on RESIDENCY_OFF or RESIDENCY_APEAL, a new row is inserted. If the keys exist, a new row is not inserted and the data is not updated.
Residency Self (Self Reported Residency)	If Date Reported (SCC_STG_RES_SLF.SELF_REPORT_DT) does not exists on RESIDENCY_SELF, a new row is inserted. If Date Reported (SCC_STG_RES_SLF.SELF_REPORT_DT) exists, a new row is not inserted and the data is not updated.
Test Score	If SCC_STG_TEST.TEST_ID does not exist on STDNT_TEST for the EMPLID, a new row is inserted along with any child rows (STDNT_TEST_COMP). If the following elements on STDNT_TEST_COMP match the elements on the staging record SCC_STG_TESTCMP, update Score, Percentile and Date Loaded regardless of the previous score reported: EMPLID, TEST_ID, TEST_COMP, TEST_DT and DATA_SOURCE. If there is not an existing row on STDNT_TEST_COMP that matches on EMPLID, TEST_ID, TEST_COMP, TEST_DT, DATA_SOURCE, a new row is inserted.