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