DARTS : Issues for UM_APPL_EXTRACT

• Date logic for getting acad_plan and etc.

• ACAD_PROG, ACAD_PLAN & ACAD_SUBPLAN (MIN/ MAX)
  

• External Org Data (discussion notes: Applicant Extract: External Org Fields)

• Ethnicity (discussion notes: Applicant Extract: Ethnicity Fields)

Date logic for getting ACAD_PLAN and etc.

The logic used to get the ACAD_PLAN data in UM_APPL_EXTRACT and in UM_STUD_EXTRACT is different. The logic affects ACAD_PLAN, ACAD_PLAN_DESCR, ACAD_PLAN_TYPE, ACAD_SUB_PLAN, ACAD_SUBPLAN_DESCR, and ACAD_SUBPLAN_TYPE. 

 In UM_APPL_EXTRACT, the effective date (ps.acad_plan.EFFDT) must to be less than the beginning date (ps_term_tbl.TERM_BEGIN_DT) of the following term.

 In UM_STUD_EXTRACT the effective date (ps.acad_plan.EFFDT) must to be less than the max of "Max Program Efftdt for Term" (ps_term_tbl.SSR_TRMAC_LAST_DT) of the current term.

For example with the attached screen shots below, if a student changed his acad_plan from "BIO-BA" to "NUR-BS" on 3/1/2019, UM_APPL_EXTRACT shows "NUR-BS" and UM_STUD_EXTRACT shows "BIO-BA" for the extract term.

The dates for "Max Program Effdt for Term" (SSR_TRMAC_LAST_DT) are very different across institutions. Here is the explanation for the field from HEUG forum (thanks to Linda) :

"It looks like it's used in any place where it needs to be determined if a student is active in a specific program for a specific term. If the student has an active program row with an effective date that is equal to or before the Max Program Effdt for Term, then they are considered active in the program for the term; otherwise, they won't be considered active in the term until next term. "

ACAD_PROG, ACAD_PLAN & ACAD_SUBPLAN (MIN/ MAX):

Current logic uses maximum acad_prog and the minimum and maximum acad_plan and acad_subplan values within the acad_prog.

External Org Data:

• Issue Summary: EXT_ORG_ID and UM_EXT_ORGID_DESCR are based on LAST_SCH_ATTEND field. If LAST_SCH_ATTEND is not supplied, then UM_EXT_ORG_ID1 and UM_EXT_ORGID_DSCR1 are used from PS_EXT_ACAD_DATA and PS_EXT_ORG_PRI_VW where the TO_DT is the most current date. If there is no single record with a most current TO_DT, then random record is used.

• Stakeholders: Admissions; Institutional Research, SPC

• Potential Solutions: Possibly rank based on LS_SCHOOL_TYPE table or set some rules
  

• meeting agenda: Applicant Extract: External Org Fields

Ethnicity: 

• Issue Summary:  If a student is not Hispanic and doesn't have PRIMARY_INDICATOR, the records for ETHNIC_GRP_CD, ETHNIC_CATEGORY, UM_ETHNIC_DESCR, ETHNIC_GROUP (?) , and UM_ETH_GRP_DESCR (? ) are returned randomly.

• Existing Policies or Documentation: 

  Scenario/ Fields in PS_UM_APPL_EXTRACT	ETHNIC_GRP_CD	ETHNIC_CATEGORY	UM_ETHNIC_DESCR	HISP_LATINO	UM_MULTIPLE_ETHNIC	ETHNIC_GROUP	UM_ETH_GRP_DESCR
  ETHNIC_GROUP = '3'  in PS_ETHNIC_GRP_TBL	from ETHNIC_GRP_CD in PS_DIVERS_ETHNIC when ETHNIC_GRP_CD <> 'NOTHIS'	from ETHNIC_CATEGORY in PS_ETHNIC_GRP_TBL	DESCR50 from PS_ETHNIC_GRP_TBL	Y	N	ETHNIC_GROUP = '3'  in PS_ETHNIC_GRP_TBL	XLATLONGNAME in PSXLATITEM where fieldname = 'ETHNIC_GROUP' and fieldvalue = 'ETHNIC_GROUP' value
  ETHNIC_GROUP <> '3'  in PS_ETHNIC_GRP_TBL and PRIMARY_INDICATOR = 'Y' in PS_DIVERS_ETHNIC	from ETHNIC_GRP_CD in PS_DIVERS_ETHNIC when ETHNIC_GRP_CD <> 'NOTHIS'	from ETHNIC_CATEGORY in PS_ETHNIC_GRP_TBL	DESCR50 from PS_ETHNIC_GRP_TBL	N	If sum of number of rows for ETHNIC_GROUP not in ( '3', '6')  in PS_ETHNIC_GRP_TBL per EMPLID is greater than 1, then 'Y else 'N'	from ETHNIC_GROUP  in PS_ETHNIC_GRP_TBL and PRIMARY_INDICATOR = 'Y' in PS_DIVERS_ETHNIC	XLATLONGNAME in PSXLATITEM where fieldname = 'ETHNIC_GROUP' and fieldvalue = 'ETHNIC_GROUP' value
  ETHNIC_GROUP <> '3'  in PS_ETHNIC_GRP_TBL and PRIMARY_INDICATOR <> 'Y' in PS_DIVERS_ETHNIC for all rows for the same EMPLID	Random from ETHNIC_GRP_CD in PS_DIVERS_ETHNIC when ETHNIC_GRP_CD <> 'NOTHIS'	Random ETHNIC_CATEGORY in from PS_ETHNIC_GRP_TBL	Random from DESCR50 in PS_ETHNIC_GRP_TBL	N	If sum of number of row for ETHNIC_GROUP not in ( '3', '6')  in PS_ETHNIC_GRP_TBL per EMPLID is greater than 1, then 'Y else 'N'	Random	XLATLONGNAME in PSXLATITEM where fieldname = 'ETHNIC_GROUP' and fieldvalue = 'ETHNIC_GROUP' value

• Stakeholders: Admissions, SPC, Student Records, IR

• Potential Solutions: Identify which ethnicity field(s) users need

• meeting agenda: Applicant Extract : Ethnicity Fields