Page Comparison

...

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

...

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

   Code Block
   language typescript
   -- START SQL -- declare @awd int = 2024 declare @awd2 int = 20242025 SELECT CASE WHEN MAX(FAW.DEFAULT_STATUS_CODE) <> '' THEN MAX(FAW.DEFAULT_STATUS_CODE) ELSE 'V' END as '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' ,CAST(ISNULL(RTRIM(stb.ID_NUMBER),'') as int) as 'id_number' ,fun.fund_token as 'token' ,RTRIM(fap.AWARD_CODE) as '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 sched_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 XXXscanfilev5.[dbo].[FAPWRF] fap ON fap.fund_token = fun.fund_token INNER JOIN XXXscanfilev5.[dbo].[FACOMD] fac ON fac.PERIOD_OF_ENROLLMENT = vdps.poe_token INNER JOIN XXXscanfilev5.[dbo].[FAWCOD] FAW ON FAW.AWARD_CODE = fap.AWARD_CODE LEFT OUTER JOIN XXXscanfilev5.[dbo].[STBIOS] stb ON stb.SOCIAL_SEC_NUMBER = stu.student_ssn WHERE say.award_year_token IN (@awd-1,@awd2-1) AND stu.student_ssn != '' AND say.tracking_status IN ('AR', 'AW', 'DC', 'DM', 'DR', 'DS', 'HL', 'ID', 'PD', 'RD', 'RP') AND vdps.award_status IN ('A', 'P') GROUP BY fap.IS_DIRECT_LENDING_LOAN, stb.ID_NUMBER, fun.fund_token, fap.AWARD_CODE, fac.SEMESTER, stu.student_ssn, stu.first_name, stu.last_name --HAVING SUM(vdps.poe_award_amount) > 0 ORDER BY --stu.last_name,stu.first_name, stb.ID_NUMBER,fap.AWARD_CODE,fac.SEMESTER ORDER BY stu.last_name,stu.first_name,CAST(RIGHT('000000000' + RTRIM(stu.student_ssn),9) as int)

   TypeScript

4. Save the output as an Excel file (A comma separated value (.csv) format is not supported.)

...

1. Navigate to 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.

Other Notes:

Tables created by this Process:

• xFAWARDmmdd_time – A backup of the FAWARD Table prior to the upload

• AWDCMP – A table that contains only the current data from the financial aid spreadsheet. This table is used to remove records from FAWARD that no longer exist in Financial Aid.

The AWARD_AMOUNT will contain the total award amount.

The GROSS_AWARD_AMOUNT will contain the disbursed amount.

The AMT_PAID_TO_DATE will contain the disbursed amount less any fees and/or adjustments (the actual amount applied to a student account.

A billing batch record will be created for any disbursed amount in The Financial Aid spreadsheet that does not equal the amount in the GROSS_AWARD_AMOUNT field in the FAWARD table.  However, an ‘N’ in the process disbursement code in the Fund Token Table (FAPWRF) indicates an award that should not be disbursed through the upload process.  This is typically used in a situation where a loan is being dispersed manually in Campus Cafe for an amount other than what appears in the disbursed column.  Once the award is dispersed in Campus Café, the upload process will have no affect on the disbursed amount if this code has a value of N.   Any other value in this field allows awards disbursed in Financial Aid to be disbursed in Campus Café during the transfer.

EXAMPLE 1:

RECORD IS ADDED

BEFORE

FAWARD DOES NOT EXIST

ACTION -sched_amt = 1000

                 disb_amt = 0

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000 

FAWARD.GROSS_AWARD_AMT = 0

FAWARD.AMT_PAID_TO_DATE = 0

BIBBAT NOT CREATED

EXAMPLE 2:

RECORD IS UPDATED

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 0

FAWARD.AMT_PAID_TO_DATE = 0

ACTION -sched_amt = 1000.00

                 disb_amt = 1000.00

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 1000.00

FAWARD.AMT_PAID_TO_DATE = 970.00

BIBBAT CREATED FOR -970.00

EXAMPLE 3A: 

RECORD IS UPDATED-PARTIAL DISBURSEMENT

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 0

FAWARD.AMT_PAID_TO_DATE = 0

ACTION - sched_amt = 1000.00

                 disb_amt = 500.00

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 500.00

FAWARD.AMT_PAID_TO_DATE = 485.00

BIBBAT CREATED FOR -485.00

EXAMPLE 3B: 

RECORD IS UPDATED-SECOND HALF OF DISBURSEMENT

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 500.00

FAWARD.AMT_PAID_TO_DATE = 485.00

ACTION - sched_amt = 1000.00

                 disb_amt = 1000.00

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 1000.00

FAWARD.AMT_PAID_TO_DATE = 970.00

BIBBAT CREATED FOR -485.00

EXAMPLE 4: 

RECORD IS UPDATED- AMT_PAID_TO_DATE IS MANUALLY ADJUSTED

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 0.00

FAWARD.AMT_PAID_TO_DATE = 0.00

ACTION - sched_amt = 1000.00

                 disb_amt = 500.00

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 500.00

FAWARD.AMT_PAID_TO_DATE = 485.00

BIBBAT CREATED FOR -485.00

ACTION – Manual adjustment to AMT_PAID_TO_DATE (changed from 485.00 to 481.56)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 500.00

FAWARD.AMT_PAID_TO_DATE = 481.56

BIBBAT CHANGED TO -481.56

EXAMPLE 5: 

RECORD IS UPDATED- TWO DISBURSEMENTS

1st Upload from F/A -
Sheduled Amount = 1000.00
Disbursed Amount = 500.00
Fee = 3%.

FAWARD.AWARD_AMOUNT = 1000.00
FAWARD.GROSS_AWARD_AMT = 500.00
FAWARD.AMT_PAID_TO_DATE = 485.00
Billing Batch Record created for 485.00

User manually changes AMT_PAID_TO_DATE to 484.00.

2nd Upload from Financial Aid -
Scheduled Amount = 1000.00
Disbursed Amount = 1000.00
Fee = 3%

FAWARD.AWARD_AMOUNT = 1000.00
FAWARD.GROSS_AWARD_AMT = 1000.00 (Gross amount disbursed)
FAWARD.AMT_PAID_TO_DATE = 969.00 ( Previous AMT_PAID_TO_DATE (484.00) + new net AMT_PAID_TO_DATE (485.00) ).
Billing Batch Record created for 485.00.

EXAMPLE 6: 

RECORD IS UPDATED - DISBURSED AMOUNT IS SET TO ZERO

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 1000.00

FAWARD.AMT_PAID_TO_DATE = 970.00

ACTION - sched_amt = 1000.00

                 disb_amt = 0.00

                 fee_pct = 3.00  (this fee is calculated from the field FAPWRF.PROC_PERCENT)

AFTER

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 0.00

FAWARD.AMT_PAID_TO_DATE = 0.00

BIBBAT CREATED FOR 970.00

EXAMPLE 6B: 

RECORD IS DELETED

BEFORE

FAWARD EXISTS AS FOLLOWS:

FAWARD.AWARD_AMOUNT = 1000.00 

FAWARD.GROSS_AWARD_AMT = 1000.00

FAWARD.AMT_PAID_TO_DATE = 970.00

ACTION – No record in Financial Aid spreadsheet

AFTER

FAWARD RECORD DELETED 

BIBBAT CREATED FOR 970.00

A billing batch record should also be created for any awards that are backed out of the FAWARD table after comparing it to the data in the AWDCMP table. Only records for semesters that start with the current year and extend to the semester entered will be removed from the FAWARD table during this comparison.  The current year is derived from the Highest Award Semester simply by removing the semester portion (e.g. ‘202402’ becomes ‘2024’ .  Any record for a semester between 202400 and the semester entered (202402)on the Upload Panel that does NOT have a current record in the Financial Aid spreadsheet  will be removed from FAWARD and a corresponding billing batch record will be created for that student. )

The field TRANSFER_TO_AR in the table FAWCOD now controls whether or not a cash batch record is created for a given award/loan/grant during the upload process.  Populating this field with a ‘Y’ value will cause a cash batch record to be created for the amount disbursed.