Use future effective dating when showing information for future terms

[zyil1HXdVfyeN7gGCGqzvuNDvknVXnF55C3z61R]

This is the work requested in #184432990.

When viewing course information in a future term, we would like the information about the course to match what is correct for that term. Course information is effective dated; if we want to see the course information for a future term, we want to use the data which has an effective date as of the start of that term.

However, at the moment we are using as the effective date the current date. That means that if there is a change in the system to a course that takes effect on a future date (AKA: future effective dated), then our current code will ignore that change.

We are pulling this information from the TFMS table cached_courses, created here. cached_courses does not have any way to identify the term at the moment. Changing that specific table to include term as a primary key would have large downstream effects.

Our current proposed solution is to duplicate the cached_courses SQL inside this application as a new view. We then would then change that new view to have term as a primary key, and provide term appropriate results for each course.

Todo list:

• [ ] Create a cached_courses view inside of this repo which has no data - ex: select * from dual
• [ ] Change thecached_courses view to have the same data as TFMS. (Note: this will probably require access to asr_warehouse tables.)
• [ ] Change our existing code in this repo to use the new cached_courses view

That should complete the refactoring needed to get cached_courses ready for the change, without affecting the application behavior. This will allow us to go forward with changing the behavior, once it is isolated inside of the course fee repo.

[thoma127]

I spent some time digging into this today and I don't think we can rely on TFMS as a source of accurate course descriptions.

We are using tfms.cached_courses right now for a lot of info, but if I query the affected accounting courses from that table:

select
    *
from
    asr_tfms.cached_courses
where
    subject = 'ACCT'
and
    catalog_nbr like '205%'
;

I only get the updated course descriptions:

013103  1   UMNTC   ACCT    2051    Intr Financial Rptg Introduction to Financial Reporting
798385  1   UMNTC   ACCT    2051H   Honors:  Intr Financial Rptg    Honors: Introduction to Financial Reporting

So looking back at previous terms wouldn't give the accurate description for that term.

Add to that the fact, that we're actually getting the descriptions from asr_tfms.fee_categories:

https://github.umn.edu/asrweb/umn_course_fees/blob/39a0cee4e6f6d110029781534f1d1765c7342786/lib/data_views/course_fees.rb#L18

I don't think TFMS keeps updated course descriptions anywhere with historical data. The ASR Data Warehouse seems like a better data source for that if we can somehow link up courses, descriptions and strms within this API and use those in the courses_daily mview.

[isen0011]

I think this should be brought to the business owner, to find out if they want us to fix it. This is a large lift - it will take a good bit of work to make this happen. I don't think we should commit to that work without buy in, or maybe even prioritization.

[thoma127]

There is also a project request to rework how we get this data. It hasn't been prioritized, but this could be done in tandem.

[dlagerro]

I thought that work got prioritized and DE started it, but the subject matter experts (SR-IT?) weren't available due to other projects.

[thoma127]

I thought that work got prioritized and DE started it, but the subject matter experts (SR-IT?) weren't available due to other projects.

You are correct. That's a better way to put it.

[eeklund]

Will this repo continue to be maintained once DE completes their work with a subject matter expert? That would effect my opinion on whether or not this should be addressed.

[thoma127]

Will this repo continue to be maintained once DE completes their work with a subject matter expert? That would effect my opinion on whether or not this should be addressed.

For the foreseeable future, yes. This integration is used on the One Stop sites and there is no plan to decommission it.