The screen is used to upload information from Campus Cafe into Financial Aid Software. When you click the Export F/A button, data will be inserted into a table called external_data in the PowerFAIDS database. The data is then imported into the financial aid system using its import facility.
Enter a semester to process and click on transfer to create the transfer file.
The process will include any record with a progress code other than I or IW where the entering semester matches the semester specified or if they have an ID_NUMBER in the HOHIST table for specified semester.
It will exclude records where the STBIOS.ID_NUMBER = STBIOS.SOCIAL_SEC_NUMBER.
SQL Filter (Sample WHERE CLAUSE):
WHERE (STBIOS.ID_NUMBER IN (SELECT HOHIST.ID_NUMBER FROM HOHIST WHERE HOHIST.CURRENT_SEMESTER = 201620 AND HOHIST.BILLING_SITE IN (01)) OR
(STBIOS.ID_NUMBER IN (SELECT ID_NUMBER FROM STBIOS WHERE ADM_PROGRESS_CODE IN ('A','C','P','Y','AW','YW','PW','YR','PC') AND STBIOS.ADMISSIONS_ENT_SEM = 201620 AND STBIOS.ADMISSIONS_SITE IN (01)))) AND ADM_PROGRESS_CODE NOT IN ('X')
AND RGDEGR05.DEGREE_STATUS NOT IN ('X') AND STBIOS.ID_NUMBER <> SOCIAL_SEC_NUMBER
MSPARMS:
· FACONNECT (URL, User and Password to connect to the PowerFAIDS DB)
o Sample URL: jdbc:sqlserver://PFSQLSERVER1;Databasename=powerfaids;SelectMethod=cursor
· FA_FINAID (STBIOS.FIN_AID_CODES TO EXCLUDE FROM FA EXPORT)
· FA_EXCLNDS (EXCLUDE NON-DEGREE SEEKING STUDENTS FROM F/A EXPORT)
· FACATEGORY (PRIMARY & SECONDARY WORKFLOW CATEGORIES FOR INCLUDING OUTSIDE SCHOLARSHIPS)
· FA_EXGRSEM (Prevent the process from using the RGDEGR.EXPECTED_GRAD_SEM to populate the grad_date field if set to ‘N’)
· FA_EXCLPC (PROGRESS CODES TO EXCLUDE FROM FA EXPORT PROCESS )
· FA_EXCLRC (EXCLUDE RESIDENT/HOUSING CODE FROM FA EXPORT)
· FA_DEGSTAT (DEGREE STATUS(ES) TO EXCLUDE FROM POWERFAIDS DATA EXPORT)
· FA_HSGPA (ADINQS FIELD USED TO STORE HIGH SCHOOL GPA)
The following is a list of the columns that are extracted from the Campus Café Database and their location in the external_data table. Please see the usage notes that follow this list for fields that have translations.
Campus Café | PowerFAIDS (external_data) |
STBIOS | |
ID_NUMBER | alternate_id |
SSN | student_ssn |
LAST_NAME | last_name |
FIRST_NAME | first_name |
MIDDLE_NAME | middle_name |
HOME_ADDRESS_ONE | primary_street1 |
HOME_ADDRESS_TWO | primary_street2 |
HOME_CITY | primary_city |
HOME_STATE | primary_state |
HOME_ZIP | primary_zip |
HOME_ZIP_EXTENSION | primary_zip |
MAIL_ADDRESS_ONE | secondary_street1 |
MAIL_ADDRESS_TWO | secondary_street2 |
MAIL_CITY | secondary_city |
MAIL_STATE | secondary_state |
MAIL_ZIP | secondary_zip |
MAIL_ZIP_CODE_EXT | secondary_zip |
ADM_PROGRESS_CODE | admission_status |
BIRTH_DATE | birth_date |
HOME_PHONE_AREA | primary_telephone |
HOME_PHONE_EXCHANGE | primary_telephone |
HOME_PHONE_NUMBER | primary_telephone |
MAIL_PHONE_AREA | secondary_telephone |
MAIL_PHN_EXCHANGE | secondary_telephone |
MAIL_PHONE_NUMBER | secondary_telephone |
ADMISSIONS_SITE | academic_division |
TRANSFER_CODE | transfer_flag |
DATE_OF_WITHDRAWAL | date_witdrawn |
ADMISSIONS_RES_CODE | housing |
SEX | gender |
US_CITIZENSHIP | us_citizen |
CITIZENSHIP_COUNTRY | citizen_country |
ADINQS | |
PRIOR_SCHOOL_GPA | high_school_gpa |
PRIOR_COLLEGE_GPA | college_gpa |
ADMAST | |
APPLIED_FOR_MAJOR | major_code |
ACCEPT_MAJOR | major_code |
STMAIL | |
ADDRESS | email_address |
SBETNC | |
ETHNIC_CODES_ARRAY | race |
IS_HISPANIC | hispanic |
REG_DEGREE_MAJOR | |
DEGREE_MAJOR_CODE | major_code |
STUDENT_SITE | academic_division |
CIP_CODE | cip_code |
PROGRAM_LENGTH | published_pgm_length |
PROGRAM_LENGTH_TYPE | published_pgm_type |
SPECIAL_PROGRAMS | special_programs |
WEEKS_IN_ACADEMIC_YEAR | weeks_programs_academic_year |
PROGRAM_CREDENTIAL_LEVEL | program_credential_level |
ENTERING_SEM | date_enrolled |
STATUS_DATE | grad_date |
EXPECTED_GRAD_SEM* | grad_date |
RGSSUM | |
CUM_CREDITS_EARNED | poe1_units_ytd |
CUM_CREDITS_EARNED | poe2_units_ytd |
CUM_CRDITS_TAKEN | poe1_units_lifetime |
CUM_CRDITS_TAKEN | poe2_units_lifetime |
SEM_CREDITS_TAKEN | ay_units |
SEM_CREDITS_TAKEN | poe1_units |
SEM_CREDITS_TAKEN | poe2_units |
STADDR |
|
PHONE_AREA_CODE | cellular_telephone |
PHONE_EXCHANGE | cellular_telephone |
PHONE | cellular_telephone |
HOHIST |
|
RESIDENT_CODE | housing |
MISCELLANEOUS | |
1ST SEMESTER ENTERED ON PANEL | academic_year_begin_date |
User Defined Fields | |
ADMAST | |
ADMISSION_CODE (FA_RATING) | dateint2_value |
STBIOS | |
FIN_AID_CODE | string1_value |
Progress Code (admissions_status) Logic
If the admissions entering semester = the primary semester entered for the export the status is set to the COMMON_APP_CODE associated with the student’s progress code (STPARM – SBPRCC)
If the degree status is ‘L’ the status is set to ‘L’
If the degree status is ‘D’ the status is set to ‘U’
If the entering semester is less than the primary semester entered for the export AND the degree status is either ‘A’ or ‘E’, the admissions status is set to ‘U
If the entering semester is less than the primary semester entered for the export AND the degree status is ‘W’, the admissions status is set to ‘V’
If the entering semester is less than the primary semester entered for the export admissions status value is not yet set (BLANK), export the status is set to the COMMON_APP_CODE associated with the student’s progress code (STPARM – SBPRCC)
STMAIL.ADDRESS. Email address will be extracted for usage preferred flag only in STMAIL.
STBIOS.ADMISSIONS_RES_CODE will be translated to a 1 for value R, otherwise set to 3.
STBIOS.ADM_PROGRESS_CODE Progress code is mapped using the common application mapping from the STPARM file.
STBIOS.US_CITIZENSHIP. Translated to value 1 if Y in Campus Café, otherwise set to 2.
MAJOR CODE ( If the progress code is Y (Applied) the major is extracted from the ADMAST applied for major. If the progress code is A (Accept) the major is extracted from the ADMAST accept major. If the progress is P (Deposited), the major is extracted from the RGDEGR row.
STBIOS.TRANSFER_CODE. This will contain a Y if the value in Campus Café is T.
ADMAST.ADMAST.ADMISSION_CODE. This field is equivalent to financial aid rating in the financial aid system.
TRK_ACTIVITY_TRACKING.TEMPLATE_NAME (This field in the table will contain a value from a template in TRK_ACTIVITY_TRACKING with a category from the MSPARM FACATEGORY. This will allow specification of an Admissions scholarship and corresponding amount in TRK_ACTIVITY_TRACKING.PLEDGED_AMOUNT.
The grad_date will be populated with the STATUS_DATE if the DEGREE_STATUS value is either 'Y' or 'C'. Alternately, the grad_date be populated with the LAST_DAY_EXAMS associated with the current degree's EXPECTED_GRAD_SEM value.
The date_enrolled value is being populated with the SEM_START_DATE value from the semester dates table (FASCDT) associated with the current degree row's ENTERING_SEM and STUDENT_SITE.
The academic_year_begin_date is being populated with the SEM_START_DATE value from the semester dates table (FASCDT) associated with the semester entered in the first semester box on the panel.
Back
Click on Exit to return to the Student Financials Module Main Screen.