Effective Dating in Academic Advisement

By Brian Rockwood posted 11-11-2019 01:27 PM


Effective dated rows are a common element in many areas of Oracle Campus Solutions.  They allow universities to keep track of what we want to happen now, what we want to happen in the future, and when things changed in the past.  While this is an immensely powerful tool, it also adds a great deal of complexity to all aspects of CS.  Not only do we need to program and maintain the “what”, but also the “when.”  Because of this, the most common problems with advisement building are invariably related to effective dates.[i]

Effective dating permeates all elements of academic advisement; requirement groups, requirements, course lists, entity groups, and student groups are all effective dated.  In order to make the advisement report pull courses and requirements the way we want all of these elements need to be coordinated perfectly.  In this post, I hope to address just one element of this dance: How the advisement report chooses which effective date to use when faced with more than one to choose from.

In order to properly understand how this works, we are going to start with the simplest case.  A requirement group is built that is tagged to the student’s undergraduate career.  This requirement group has three effective dated rows[ii]:

  • Row #1 dated 5/1/2014
  • Row #2 dated 5/1/2016
  • Row #3 dated 5/1/2018

In order to determine which of these rows to use in the AAR, Campus Solutions first looks at the student’s requirement term on the Student Program/Plan page.  It then converts the term into a date by referencing the term table for that term[iii].

  • Sample student, “Smarty Pants” has an Undergraduate Career requirement term of Fall 2016 which was translated into 8/1/2016 via the term table.

At this point, we have one date that the AAR will use to determine which effective dated row to use.  To pick which one it will use….

**The Price is Right Rule[iv]**

The Price is Right Rule works as follows:

PiRR: Use the newest effective dated row that is earlier than or equal to the comparison date.  (Without going over!)

In this case, the AAR will take the 8/1/16 date from the term table and compare it to the effective dates for the requirement group. 

  • Since the 5/1/18 is after the 8/1/16 date it is eliminated from consideration.
  • The 5/1/16 row is earlier than 8/1/16, and is the newest such row so it is selected.
  • The 5/1/14 row is earlier than 8/1/16, but isn’t the newest row, so it too is eliminated.

That’s it!  This process is repeated for requirements and course lists, and there are some interesting interactions between these effective dates, but the core of effective dating is the PiRR[v].


[i] "If something isn't working the way you expected in Academic Advising, odds are something is haywire with the effective dates." -D. Wright, 2019.

[ii] Why does this RG have two effective dates?  Who knows!  My guess is that the faculty can’t make up their mind about which courses to require and we built it this way to make it easier to swap out requirements.

[iii] This is simpler than it sounds.  But I’d love to tell you all about it!  If you’d like more blog posts on this topic let us know.  Heck, maybe we can turn this into a whole series!

[iv] For the younger members of the audience, this refers to an old gameshow that you might remember from visiting your grandmother’s house.

[v] In a shameless self-plug, I’ll be going over these interactions (and more!) in a webinar on 11/14/19 at 1:00 EDT.  I hope to hear you all there!