Fee Remission Reporting
Current Report (FSU Fee Remissions Report (OFC v2)) is located on https://its-cog-dev.ucsd.edu (login w/ AD)
Team Content > Graduate Division > Financial Support Unit
Access to the report may be requested via
https://support.ucsd.edu/path?id=sc_cat_item&sys_id=1354cbfadbf01890dbd6f2b6af9619f7 (this link is on bah.ucsd.edu)
We will need to have three versions of this report to support the following:
Fee Remission Reports
a. GSR Fee Remission- Report currently looks for only GSR title codes GDGD-385 - Getting issue details... STATUS
- Background: Note that Graduate Students receive Fee Remissions as a result of 50% or higher employment. They will qualify for this if their employment appointment falls into a GSR (Graduate Student Researcher) or IA (Instructional Assistant). Many times students can have multiple appoints in either TA or GSR in order to meet this 50% threshold. However, for this report the intention is only to focus on the GSR component of the fee remission in terms of creating fee remission rates and entries, but the IA title codes need to be taken into account in order to see which students are meeting the threshold for fee remission
- Supporting documents saved in FSU shared Google Drive
Tasks | Assigned | Notes |
|---|---|---|
GFSU will indicate the IA Title Codes needed | Done IA title codes eligible for remission: 001506, 002310, 002850, 002860 | |
Add the title codes for Instructional Assistants to the report. Title Codes are sourced from UC Path | Done | |
Confirm rate amounts only appear for GSR positions as determined by the existing GSR title Codes. The report should already be doing this. The IA codes should a have a zero rate associated with them. Note that the addition of IA titles codes should be used to determine which students reached the 50% employment level in order to receive fee remission. However, this report will only need to determine the GSR portion of the fee remission rate. | I believe that that the IA title codes creating remission entries simply need to be setup at a zero rate for this requirement. They will show up as a line of data in the report. | |
GFSU will provide at least 3 test cases w/student PIDs with the following scenarios with expected outputs: Student has only GSR employment > 50% Student has only IA employment > 50% Student has a combination of IA + GSR employment > 50% |
| |
| GFSU will provide the new rates for 2022 | Done (provisional rates posted in Teams chat) | |
| Update the pooled rates so that the report is using rates for the current year 2022 | ||
| Incorporate Eligibility Field? | ||
| Set row limit on Excel export to "unlimited" | Currently Salary Detail and MCI pages are limited to 1000 rows per spreadsheet tab upon download. Bhavya is checking with Sara on if there's any way to preserve report performance and eliminate the export row limit. | |
| Freeze header row (field names) | Bhavya Guntupalli | |
| Report Validation | Meeting notes moved to:
Hannah Van Etten , separately, to provide list of requested enhancements to 1a MCI and SalaryDetails pages |
b. State/In-state and Campus Fees for Instructional Assistants
Needs to bring in AR Data - payments aggregated by student, per term:
Filter by Detail Codes: GMAH, GMAJ
| Tasks | Assigned | Notes |
|---|---|---|
| Implement a data module that will allow for linking of AR , LL Salary, and Student Demographics data | Bhavya Guntupalli Working with Jim Ybarra | After our last meeting with Judy White and BI team we understood that it would be possible to create a data module that would allow for the linking of the various packages needed |
Create a Calculated field -Term Code field based on the date range that LL Pay Period End Date | Terms: 10/1 - 12/31 (FA) 1/1 - 3/31 (WI) 4/1-6/30 (SP) | |
| Create a join between AR Data and LL Salary data using the Term Code (mentioned above) and PID | ||
| Add the TERM_CODE and APPLY_AMT field to the Report | The APPLY_AMT should be a SUM by Student and Term Code. The Mock data has been updated to show expected results. GMAH and GMAJ filters for code | |
Create an additional join to Student Demographics package using the student PID | Graduate Primary Major Department Current | |
| Using the Student Demographic data, add the student's Department to the report | ||
| Research Changes in Dept per Term | Per Jonathan Wai, Alfredo has been reporting on changes in Degree. May take some more research to see how to track this. | |
Implement Calculated Field - Proportional Split = =ROUND(M11*W11, 4) LL Job FTE * GSRTF Rate Factor rounded to four decimals | ||
| Implement Calculated Field - IA Rate = =M11/SUM($M$11,$M$13,$M$15)*AF11 (LL Job FTE/ (Sum of LL Job FTE where the LL Job Code is in (TA Title Codes 001506, 002310, 002850, 002860) for the given Term ) )* Proportional Split | CDF- This one is a little bit tricky but we must have done the same thing on the 1a report for GSRs. Basically I think the point of this is to add up the total amount of employment the student has performed as a TA for a given Term. I believe this was using the Job Title Codes as filter for the TA Code types. Then for the particular appointment on the individual report line, figure out how much remission should be charged based on what percentage of employment that amount represents. | |
Implement Calculated Field - IA Remission = =AE11*AG11 APPLY_AMT * IA Rate | ||
| Implement Calculated Field - |
| Field | Current | Updated |
|---|---|---|
| AR Salary Detail Query.Total Job FTE | [LLSalaryDetail Query].[Total Job FTE] | total ([LL Salary Total Pay Component Percent] for [LL Employee ID],[LL Salary Earnings Period End Date] ) |
| AR Salary Detail Query.Total Job FTE - Earnings Period Previously | [LLSalaryDetail Query].[Total Job FTE - Earnings Period Previously] | CASE WHEN [LLSalaryDetail Query].[LL Pay Period End Date] between ?P_Pay Calendar End Year From? and ?P_Pay Calendar End Year To? THEN 0 ELSE total (CASE WHEN [LLSalaryDetail Query].[LL Pay Period End Date] between ?P_Pay Calendar End Year From? and ?P_Pay Calendar End Year To? THEN 0 ELSE [LL Salary Total Pay Component Percent] END for [LL Employee ID],[LL Salary Earnings Period End Date] ) END |
| AR Salary Detail Query.GSRTF Rate Factor | [LLSalaryDetail Query].[GSRTF Rate Factor] | abs(CASE WHEN [Total Job FTE] >= 0 AND [Total Job FTE] < .25 THEN 0 WHEN [Total Job FTE] > -.25 AND [Total Job FTE] <= 0 THEN 0 ELSE 1/[Total Job FTE] END) |
| AR Salary Detail Query.GSRTF Rate Factor - Earnings Period Previously | [LLSalaryDetail Query].[GSRTF Rate Factor - Earnings Period Previously] | abs(CASE WHEN [Total Job FTE - Earnings Period Previously] >= 0 AND [Total Job FTE - Earnings Period Previously] < .25 THEN 0 WHEN [Total Job FTE - Earnings Period Previously] > -.25 AND [Total Job FTE - Earnings Period Previously]<= 0 THEN 0 ELSE 1/[Total Job FTE - Earnings Period Previously] END) |
c. Non-Resident Tuition for Instructional Assistants
This Report will follow the same spec as the 1b report with the following exceptions.
Filter by Detail Codes only by "GMAI"
Some different rates will be used in the calculation
| Tasks | Assigned | Notes |
|---|---|---|
| Implement filter to use "GMAI" for filtering Detail Codes | ||
| Define rates needed for calculations based on Project/Task? | CDF- Hannah you mention that we would maybe need to store some values for the calculation but I think in the meantime we will be hardcoding them into this report. | |