This article provides historical information regarding the 2022 Data Governance project focused on tracking known ISIR Translate Value issues in the PeopleSoft Software.
Detailed Information
The following information has been found to have been incorrect for many years. Since the issue has been identified, it has worked through the Data Governance process. Oracle is aware of this issue and suggests using a work-around when querying these fields.
I. Description of Issue:
Oracle does not update ISIR Translate values in CS (Campus Solutions) as annual FAFSA changes occur (e.g., a question changes, a new response option is added, two answers are condensed into one, etc.). While the field values in Oracle delivered ISIR tables are correct, the corresponding translate values in those tables may be outdated. This means the derived translate values in Campus Solutions for field values that have changed in the ISIR Record could be incorrect when you query them (the field value without translate will be correct, however the long and short description will be outdated).
For this reason, an annual reminder is sent out to the Financial Aid group to review this page and update any translate values.
When querying values from ISIR data tables, such as:
as well as data from table views pulling in ISIR data, such as, UM_FIN_AID, a work around to get current translate values for certain fields is necessary.
This document highlights currently identified fields requiring a work around.
Use the EDE Technical Reference for the year being queried to verify translate values:
System Technical References | Library | Knowledge Center
Translate values may vary from year to year, so finding the correct Technical Reference for the corresponding FAFSA Year is important.
II. Example of Incorrect Translate Values in CS:
a. Current EDE Technical Reference Student Housing Plans field: (Table ISIR_STUDENT; field HOUSING_CODE_1)
ISIR Record Description/Data Dictionary (Continued)
b. Compared with current student housing code as seen on the ISIR information screen in Mainestreet:
Values in this screen match the current EDE Technical Reference, as this year is Aid Year specific.
Main Menu > Financial Aid > Federal Application Data > Correct 2019-2020 ISIR records
c. Student Housing Code 1 Translate values as defined within Query Manager:
Values in Query Manager are outdated.
Thus, in the example above the query results will return the correct field value of 3 , but the incorrect translate value of Parents.
III. Current Solution
If translated values are preferred in query output, the user would currently need to create the following expression (or one similar) to return the correct translate value for field HOUSING_CODE_1 in table ISIR_STUDENT:
CASE
WHEN X.HOUSING_CODE_1 = 1 THEN 'On campus'
WHEN X.HOUSING_CODE_1 = 2 THEN ‘With parent’
WHEN X.HOUSING_CODE_1 = 3 THEN 'Off campus'
ELSE ' '
END
Or, if “Decode” is preferred:
DECODE (X.HOUSING_CODE_1, '1', 'On campus' , '2', ‘With parent’, '3', 'Off campus')
*Please note the table below lists currently known table/field combinations that require a work-around and may not be comprehensive.
Currently Known ISIR Table/Fields needing Translate Work-Around:
|
Table(s)
|
Field Name
|
CS Translate Values
|
EDE Technical Ref. Values (2019-2020)
|
Expression Work Around Example
|
|
ISIR_STUDENT
UM_FIN_AID
FAN_ISIR_STD_VW
ISIR_00_1_EC
UM_ISIR_MAX_VW
UM_FIN_AID_DTVW
|
HOUSING_CODE_1
UPDATE 5/18: It does not appear that this PeopleSoft delivered field will be updated by Oracle. "Solution" is to code around. SQRs affected are being updated.
|
1 = On-Campus
2 = Off-Campus
3 = Parents
|
1 = On-Campus
2 = With Parents
3 = Off-Campus
|
CASE
WHEN X.HOUSING_CODE_1 = '1'THEN 'On Campus'
WHEN X.HOUSING_CODE_1 = '2' THEN 'With Parents'
WHEN X.HOUSING_CODE_1 = '3' THEN 'Off Campus'
END
|
|
ISIR_STUDENT
FAN_ISIR_STD_VW
INAS_SIM_STU
INST_STUDENT
ISIR_00_1_EC
UM_FIN_AID (field name MARITAL_STATUS)
UM_ISIR_MAX_VW
VERIFICATION
|
MARTIAL_STAT
(Students' Marital Status)
(Note values for Student and Parent marital status differ- For Parent values see corresponding row)
|
1 = Single
2 = Married
3 = Separated
4 = Divorced
5 = Widowed
|
1 = Single
2 = Married/Remarried
3 = Separated
4 = Divorced or widowed
|
CASE
WHEN X.MARITAL_STAT = '1' THEN 'Single'
WHEN X.MARITAL_STAT = '2' THEN 'Married/Remarried'
WHEN X.MARITAL_STAT = '3' THEN 'Separated'
WHEN X.MARITAL_STAT = '4' THEN 'Divorced or Widowed'
END
|
|
ISIR_PARENT
FAN_ISIR_PAR_VW
INAS_SIM_PAR
INST_PARENT
ISIR_00_1_EC (field name MARITAL_STAT_PAR)
UM_ISIR_MAX_VW (field name MARITAL_STAT_PAR)
|
MARTIAL_STAT
(Parents' Marital Status)
(Note values for Student and Parent marital status differ- For Student values see corresponding row)
|
1 = Single
2 = Married
3 = Separated
4 = Divorced
5 = Widowed
|
1 = Married/remarried
2 = Never married
3 = Divorced/separated
4 = Widowed
5 = Unmarried and both parents living together
Blank
|
CASE
WHEN X.MARITAL_STAT = '1' THEN 'Married/remarried'
WHEN X.MARITAL_STAT = '2' THEN 'Never married'
WHEN X.MARITAL_STAT = '3' THEN 'Divorced/separated'
WHEN X.MARITAL_STAT = '4' THEN 'Widowed'
WHEN X.MARITAL_STAT = '5' THEN 'Unmarried and both parents living together'
END
|
|
ISIR_STUDENT
INST_STUDENT
INAS_SIM_STU
FAN_ISIR_STD_VW
|
CURRENT_GRADE_LVL
|
01 = 1st Year, Never Attended
02 = 1st Year, Attended Before
03 = 2nd Yr/Sophomore
04 = 3rd Yr/Junior
05 = 4th Yr/Senior
06 = 5th Yr/Other UGRD
07 = 1st Yr Grad/Professional
08 = 2nd Yr Grad/Professional
09 = 3rd Yr Grad/Professional
10 = Beyond 3rd Yr Grad/Professional
|
0 = 1st Year, Never Attended
1 = 1st Year, Attended Previously
2 = 2nd Yr/Sophomore
3 = 3rd Yr/Junior
4 = 4th Yr/Senior
5 = 5th Yr/Other UGRD
6 = 1st Yr Grad/Professional
7 = Continuing Grad/Professional
Blank
|
CASE
WHEN X.CURRENT_GRADE_LVL = 0 THEN '1ST Yr. Never'
WHEN X.CURRENT_GRADE_LVL = 1 THEN '1ST Yr. Prev'
WHEN X.CURRENT_GRADE_LVL = 2 THEN '2nd Yr'
WHEN X.CURRENT_GRADE_LVL = 3 THEN '3rd Yr'
WHEN X.CURRENT_GRADE_LVL = 4 THEN '4th Yr'
WHEN X.CURRENT_GRADE_LVL = 5 THEN '5th Yr'
WHEN X.CURRENT_GRADE_LVL = 6 THEN '1st Yr Grad'
WHEN X.CURRENT_GRADE_LVL = 7 THEN 'Cont Grad'
END
|
|
ISIR_STUDENT
ISIR_PARENT
INAS_SIM_STU
INAS_SIM_PAR
FAN_ISIR_STD_VW
FAN_ISIR_PAR_VW
VERIFICATION (fields ELIG_FOR_1040A_EZ and ELIG_FOR_1040AEZ_P)
|
ELIG_FOR_1040A_EZ
(Used for both Parents’ Eligible to file 1040A or 1040EZ? and Student Eligible to file 1040A or 1040EZ?)
|
1 = Yes
2 = No/Don't Know
|
1 = Yes
2 = No
3 = Don't Know
Blank
|
CASE
WHEN X.ELIG_FOR_1040A_EZ = '1' THEN 'Yes'
WHEN X.ELIG_FOR_1040A_EZ = '2' THEN 'No'
WHEN X.ELIG_FOR_1040A_EZ = '3' THEN 'Don't Know'
END
|
|
ISIR_STUDENT
ISIR_PARENT
ISIR_00_1_EC
INAS_SIM_STU
INAS_SIM_PAR
FAN_ISIR_STD_VW
FAN_ISIR_PAR_VW
UM_ISIR_MAX_VW
|
TAX_FORM_FILED
(Used for both Parents’ Type of 20XX Tax Form Used? and Students’ Type of 20XX Tax Form Used?)
|
1 = 1040A/EZ
2 = 1040
3 = Foreign Tax Return
4 = US Territory Tax Return
5 = Will Not File
|
1 = IRS 1040
2 = IRS 1040A or 1040EZ
3 = Foreign Tax Return
4 = A tax return from Puerto Rico, a US Territory, or freely associated State
Blank
|
CASE
WHEN X.TAX_FORM_FILED = '1' THEN '1040'
WHEN X.TAX_FORM_FILED = '2' THEN '1040 A/EZ'
WHEN X.TAX_FORM_FILED = '3' THEN 'Foreign Tax'
WHEN X.TAX_FORM_FILED = '4' THEN 'US Terr Tax'
END
|
|
ISIR_STUDENT
INAS_SIM_STU
FAN_ISIR_STD_VW
|
APP_SIGNED_BY
|
A = Applicant
B = Applicant and Parent
|
A = Applicant Only
B = Applicant and Parent
P = Parent Only
Blank = No signatures
|
CASE
WHEN X.APP_SIGNED_BY = 'A' THEN 'Applicant'
WHEN X.APP_SIGNED_BY = 'B' THEN 'Both'
WHEN X.APP_SIGNED_BY = 'P' THEN 'Parent'
END
|
IV. Notes From External Sources
Having reviewed several posts on the forum, it is confirmed that this is a well-known issue extending back several years, with no real resolution yet in sight/announced by Oracle. Generally, forum contributors stated they would either resort to utilizing an expression, or that they would put in a request to have the translate tables updated by a developer.
Audience