/
F/A Data Export to PowerFaids

F/A Data Export to PowerFaids

Owned by Robert Lenk
Last updated: Oct 02, 2024
4 min read

Purpose

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.

Export F/A Data

Enter up to 5 semesters, up to 5 sites, a housing semester (also used for inquiry students), and an academic year start date to process and click on submit 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.

 

Example SQL query of what this process does behind the scenes (Sample WHERE CLAUSE):

WHERE (STBIOS.ID_NUMBER IN (SELECT HOHIST.ID_NUMBER FROM HOHIST WHERE HOHIST.CURRENT_SEMESTER = 202520 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 = 202520 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)

 

F/A Transfer Usage/Logic Notes  

 

  • 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.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.