Fee Remission Reporting

Owned by Carlo Del Frate
Last updated: May 12, 2022
5 min read

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

      1. 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
      2. Supporting documents saved in FSU shared Google Drive


Tasks

AssignedNotes

GFSU will indicate the IA Title Codes needed 


Done

IA title codes eligible for remission: 001506, 002310, 002850, 002860

(For more info, see Types of Academic Student Employees; Student Academic Title Pay Rates)

Add the title codes for Instructional Assistants to the report. Title Codes are sourced from UC Path

Bhavya Guntupalli

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.

Bhavya Guntupalli 
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%

Tiffany Swiderski Courtney Aguila Hannah Van Etten 

GFSU will provide the new rates for 2022Done (provisional rates posted in Teams chat)
Update the pooled rates so that the report is using rates for the current year 2022

Bhavya Guntupalli 


Incorporate Eligibility Field?

Set row limit on Excel export to "unlimited"

Bhavya Guntupalli 

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

Tiffany Swiderski Courtney Aguila Hannah Van Etten 

Meeting notes moved to:



Hannah Van Etten  to provide requirements for reconciliation report (newly identified)

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

      1. Needs to bring in AR Data - payments aggregated by student, per term:

      2. Filter by Detail Codes: GMAH, GMAJ

TasksAssigned

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  

Bhavya Guntupalli 

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
Bhavya Guntupalli 

Add the TERM_CODE and APPLY_AMT  field to the Report
Bhavya Guntupalli 

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


Bhavya Guntupalli 
Graduate Primary Major Department Current
Using the Student Demographic data, add the student's Department to the report 
Bhavya Guntupalli 

Research Changes in Dept per Term
Carlo Del Frate 
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

Bhavya Guntupalli 

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

Bhavya Guntupalli Tiffany Swiderski 

Hannah Van Etten Courtney Aguila 

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

Bhavya Guntupalli 

Implement Calculated Field - 

FieldCurrentUpdated
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


TasksAssigned

Notes


Implement filter to use "GMAI" for filtering Detail Codes 
Bhavya Guntupalli 

Define rates needed for calculations based on Project/Task?
Hannah Van Etten 
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.