Overview and Caveats
New Students should be identified primarily by the field PROG_REASON in the table CF_ACAD_PROG where the field ADMIT_TERM is equal to the field STRM in the table CF_STDNT_CAR_TERM. (When the ADMIT_TERM is a summer term, it is recoded to the following fall term. For example, '1136' --> '1139'). New Students can be classified by
Admission Type, Class Level, and Degree Status.
Admission Type: Whether a student should be classified as a first-time matriculant at an institution or a transfer from another institution is determined by the field PROG_REASON in the table CF_ACAD_PROG. For example, the program reason '2' indicates a first-time student and the program reason '3' indicates a transfer student.
Class Level: Whether a student should be classified as a new undergraduate student or a new graduate student is determined by the field ACAD_CAREER in the table CF_STDNT_CAR_TERM. For example, the academic career 'UGRD indicates an undergraduate student and the academic career 'GRAD' indicators a graduate student.
Degree Status: Whether a student should be classified as a new degree student or a new nondegree student is determined by the field ACAD_PROG_PRIMARY in the table CF_STDNT_CAR_TERM. For example, the academic program 'UGRD' indicates a degree student and the academic program 'NDEGU' indicates a nondegree student.
Missing or inconsistent data: In order to be classified as a new student--especially a first-time freshmen or transfer--the academic career and academic program must be consistent, and an appropriate program reason must be found in the program plan stack where the admit term is the same as the current term of enrollment. When the academic career and academic program are inconsistent, the academic career takes precedence over the academic program (see
Degree Status). When an appropriate program reason is missing, the field ADMIT_TYPE in the table CF_ADM_APPL_DATA may be used instead. However, because a student may have multiple admission events in the same institution and multiple admission terms may exist in the program plan stack, it is not always clear which one should be linked the admit type in the admissions record. Therefore, when ADMIT_TYPE is used instead of PROG_REASON or a match with the CAS or ASTA file is used instead of PROG_REASON, the current term must also be the first fall or spring term enrolled in courses as a degree-seeking student (see
First STRM as Degree).
Additional program reasons: When the admission module is implemented in CUNYfirst, information about the Class Level and
Degree Status will be embedded into the admit type and the admit type will be pushed into the program reason when the applicant matriculates and is moved into student records. For example, 'GDS' for first-time graduate matriculant. When reconciling inconsistencies, the academic career in the current term record will still take precedence over the primary academic program, and both will take precedence over any program reasons where the admission term is the same as current term of enrollment.
Also see
Admission Type and
Unclassified New Undergraduates or Graduates.
Definitions
First-time Freshman ('01'): A student in an undergraduate academic career and a degree-seeking academic program who has a program reason of '2' where the admit term equals both the current term and the first fall or spring term enrolled in courses as a degree-seeking student within the same institution and academic career. If no program reason can be found in the program plan stack, but the current term is the first fall or spring term enrolled in courses as a degree student, then the admit type in the admission record or a match with a CAS file can be used to classify a student as a first-time freshman. Summer admit terms are recoded to fall admit terms for the purpose of identifying new students.
- CF_STDNT_CAR_TERM.STRM in (Fall or Spring) and
- CF_STDNT_CAR_TERM.ACAD_CAREER in ('UGRD','UKCC','ULAG) and
- CF_STDNT_CAR_TERM.ACAD_PROG_PRIMARY in ('UGRD','CBUIS) and
- CF_ACAD_PROG.PROG_REASON = '2'
- or
- CF_ADM_APPL_DATA.ADMIT_TYPE = '2'
- or
- CBIL_CAS_FILE.FILETERM = CF_STDNT_CAR_TERM.STRM
- where
- CF_ACAD_PROG.ADMIT_TERM = CF_STDNT_CAR_TERM.STRM and
- CF_ACAD_PROG.INSTITUTION = CF_STDNT_CAR_TERM.INSTITUTION and
- CF_ACAD_PROG.ACAD_CAREER = CF_STDNT_CAR_TERM.ACAD_CAREER and
- CBIL_HISTORY_FACTS.IR_FIRST_STRM_AS_DEGREE = CF_STDNT_CAR_TERM.STRM
Sample Mapping from CUNYfirst
CF_STDNT_CAR_TERM.
STRM
|
CF_STDNT_CAR_TERM.
ACAD_CAREER
|
CF_STDNT_CAR_TERM
ACAD_PROG_PRIMARY
|
CF_ACAD_PROG.
ADMIT_TERM
|
CF_ACAD_PROG.
PROG_REASON
|
CF_ADM_APPL_DATA.
ADMIT_TYPE
|
CBIL_CAS_FILE.
FILETERM
|
CBIL_ASTA_FILE.
FILETERM
|
CBIL_HISTORY_FACTS.
IR_FIRST_TERM_AS_DEGREE
|
CBIL_HISTORY_FACTS.
IR_ADMISSION_TYPE_CODE
|
CBIL_HISTORY_FACTS.
IR_ADMISSION_TYPE_DESC
|
1136
|
UGRD
|
UGRD
|
1136
|
2
|
|
|
|
1139
|
00
|
Unknown
|
1139
|
UGRD
|
UGRD
|
1136
|
2
|
|
|
|
1139
|
01
|
First-time Freshman
|
1139
|
UKCC
|
UGRD
|
1139
|
2
|
|
|
|
1139
|
01
|
First-time Freshman
|
1139
|
ULAG
|
UGRD
|
1139
|
|
2
|
|
|
1139
|
01
|
First-time Freshman
|
1139
|
UGRD
|
CBUIS
|
|
|
|
201309
|
|
1139
|
01
|
First-time Freshman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SQL for Finding Program Reasons for New Students
create table IR.CBIL_CF_ADM6
tablespace CBIL_DATA
as
select
distinct SUBQ.CF_EMPLID,
SUBQ.CF_ACAD_CAREER,
SUBQ.CF_INSTITUTION,
SUBQ.CF_STRM,
SUBQ.IR_TERM_ENROLLED_DATE,
SUBQ.EFFDT,
SUBQ.EFFSEQ,
SUBQ.PROG_ACTION,
SUBQ.PROG_REASON
from (select
CART.CF_EMPLID,
CART.CF_ACAD_CAREER,
CART.CF_INSTITUTION,
CART.CF_STRM,
CART.IR_TERM_ENROLLED_DATE,
CART.CF_STDNT_CAR_NBR,
PROG.EFFDT,
PROG.EFFSEQ,
PROG.PROG_ACTION,
PROG.PROG_REASON,
PROG.PROG_STATUS,
rank() over (partition by CART.CF_EMPLID, CART.CF_ACAD_CAREER, CART.CF_INSTITUTION, CART.CF_STRM
order by PROG.EFFDT desc, PROG.EFFSEQ desc, PROG.PROG_ACTION asc) as MAX_RANK
from IR.CBIL_CF_CAR_TERM CART,
IR.CF_TERM_TBL TERM,
IR.CF_ACAD_PROG PROG
where TERM.INSTITUTION = CART.CF_INSTITUTION
and TERM.ACAD_CAREER = CART.CF_ACAD_CAREER
and TERM.STRM = CART.CF_STRM
and PROG.EMPLID = CART.CF_EMPLID
and PROG.ACAD_CAREER = CART.CF_ACAD_CAREER
and PROG.INSTITUTION = CART.CF_INSTITUTION
and substr(PROG.ADMIT_TERM,1,3)||decode(substr(PROG.ADMIT_TERM,4,1),'2','2','6','9','9','9') = CART.CF_STRM
and PROG.PROG_REASON in ('2','3')) SUBQ
where SUBQ.MAX_RANK = 1
and SUBQ.PROG_STATUS = 'AC';
.