Upload F/A Awards PowerFaids Institution
  • 19 Jul 2021
  • 3 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Upload F/A Awards PowerFaids Institution

  • Dark
    Light
  • PDF

About Upload F/A Awards for PowerFaids Institution

For institutions using the Campus Cafe financial aid module this function is intended to upload institutional scholarships. It is not designed to upload federal financial aid. Awards uploaded through this method are not factored into cost of attendance. Uploaded awards are are limited to one disbursement per award per semester.

For institutions not using the Campus Cafe financial aid module, this function may, depending on the institution's business processes, facilitate the placement of financial aid awards contained in a spreadsheet on student records. 

Federal financial aid uploaded through this method will not generate COD documents, and will not be able to follow Campus Cafe's financial aid origination/disbursement processes.

For institutions using PowerFaids, this function allows the import of awards from PowerFaids (non-cloud version). This process is dependent on the institution having he ability to run a SQL statement against its PowerFaids database. [PowerFaids requires a separate contract with College Board.]

For institutions using a third-party financial aid servicer, the institution should consult with Campus Cafe support prior to using this function.

Prerequisites

  • Campus Cafe financial aid award codes must be configured and each code must be tried to a transaction code.
  • If using PowerFaids custom tables in the PowerFaids database must be created to hold translations between the PowerFaids Periods of Enrollment (POEs) and Campus Cafe semesters and the PowerFaids award tokens and the Campus Cafe award codes. Campus Cafe support can assist with the creation of these tables.

Configure Process (Using PowerFaids with Campus Cafe hosted in the cloud)

Set Controls

  1. Navigate to Admin > Custom Control Maintenance
  2. Locate ProgramId UPLOADPATH Sequence 1, Parameter 1
  3. Next to UPLOADPATH click the pencil
  4. In Parameter Value 1 enter C:\CampusCafe\resources\powerfaids.xlsx
  5. Click Save & Back
  6. Locate ProgramId FA_REMOVE Sequence 1, Parameter 1
  7. Next to FA_REMOVE click the pencil
  8. In Parameter Value 1 Campus Cafe recommends entering Y.
  9. Click Save & Back
  10. Locate ProgramId USEFADISB Sequence 1, Parameter 1. 
  11. Next to USEFADISB click the pencil
  12. In Parameter Value 1 enter Y to create a financial aid award and scheduled disbursement. (Only one disbursement will be created.) Enter N to create the award only.
  13. Click Save & Back
  14. Refresh the cache by navigating to Admin > Admin Servlet > reload data

Prepare the File (Source is PowerFaids)

Caution
The same student cannot have multiple awards of the same award type for the same semester. Only one award will be processed. Remaining awards will be skipped without notice.
  1. Open your PowerFaids database using an account with SQL select access
  2. Copy the below SQL and modify the two variables at the top
    awd int is the first financial aid award year to capture
    awd2 int is the second financial aid award year to capture; if only capturing one financial aid year, enter the same award year as entered for awd int
  3. Run the SQL
    TypeScript
    declare @awd int = 2020
    declare @awd2 int = 2021
    SELECT
    'V' 'award_status',
    SUM(vdps.poe_award_amount) as 'sched_amt', 
    CASE
    	WHEN SUM(vdps.poe_award_disbursed_amount) != 0 THEN SUM(vdps.poe_award_amount) 
    	ELSE 0 
    END as 'disb_amt',
    SUM(vdps.poe_award_disbursed_amount) as 'net_amount',
    CASE
    WHEN fap.IS_DIRECT_LENDING_LOAN != 'Y' THEN 'N'
    	ELSE 'Y'
    END as 'isDL',
    0 'id_number',
    fun.fund_token as 'token',
    fap.AWARD_CODE 'award_type',
    LEFT(RTRIM(fac.SEMESTER),4) as 'award_year',
    CAST(RTRIM(fac.SEMESTER) as int) as 'semester',
    CAST(RIGHT('000000000' + RTRIM(stu.student_ssn),9) as int) as 'SSN', 
    0 SCHEDULED_DATE,
    RTRIM(stu.first_name) AS FIRST_NAME,RTRIM(stu.last_name) as LAST_NAME,
    max(vdps.award_status) as pfaids_status
    FROM
    student stu
    INNER JOIN stu_award_year say ON stu.student_token = say.student_token 
    INNER JOIN v_disb_poe_summary vdps ON vdps.stu_award_year_token = say.stu_award_year_token 
    INNER JOIN funds fun ON fun.award_year_token = say.award_year_token AND fun.fund_token = vdps.fund_token 
    INNER JOIN [PFAIDS].[dbo].[FAPWRF] fap ON cast(fap.fund_token as int) = fun.fund_token 
    INNER JOIN [PFAIDS].[dbo].[FACOMD] fac ON fac.PERIOD_OF_ENROLLMENT = vdps.poe_token 
    INNER JOIN [PFAIDS].[dbo].[FAWCOD] FAW ON FAW.AWARD_CODE = fap.AWARD_CODE 
    WHERE say.tracking_status IN ('AR', 'AW', 'DC', 'DM', 'DR', 'DS', 'HL', 'ID', 'PD', 'RD', 'RP') 
    AND vdps.award_status IN ('A', 'P') 
    AND say.award_year_token IN (@awd-1,@awd2-1) 
    AND stu.student_ssn != ''
    GROUP BY fap.IS_DIRECT_LENDING_LOAN, stu.student_ssn, fun.fund_token, fap.AWARD_CODE, fac.SEMESTER, stu.first_name, stu.last_name 
    ORDER BY stu.student_ssn,fap.AWARD_CODE,fac.SEMESTER
  4. Save the output as an Excel file (A comma separated value (.csv) format is not supported.)

Upload the File

Permission #379 is required to access this function

Important
Post all outstanding bill batches prior to uploading a new file.
  1. Navigate to Student Financials > Upload F/A Awards
  2. In the drop down choose the maximum semester to process from the spreadsheet. Awards with this semester and below will be processed. Awards with semesters of higher numbers will be skipped.
  3. Click Browse
  4. Choose the file you saved earlier
  5. Click Upload
  6. A message will appear saying the number of rows to process
  7. Click Continue
  8. Wait until the Job still running turns to Job completed successfully! The process will continue to run if you navigate away from the page but the completed message will not appear so you will not be able to confirm if the process has completed successfully.
  9. Awards are added directly to student records; disbursements are sent to bill batch for posting; a link appears to download a file containing information on data that could not be processed because of an invalid SSN, semester code or transaction code.

Attachments

Was this article helpful?