EFFECTIVE DATING
Effective-dated records/tables contain the field EFFDT.
The effective date field is used throughout PeopleSoft applications to give data a historical perspective and allows for the viewing of data changes over time.
- Whenever users add a row of data to the table, they specify the date on which that data becomes effective.
- Whenever users change a row of data, they specify a new effective date, and the system retains the previous version of the row as history.
Effective Date and Query
- As you create a query, if you are selecting a record for an effective-dated table, when you first access the Query page, a dialog box informs you that”an effective date criteria has been automatically added for this effective dated record.”
- The dialog box appears as follows.
- This message is stating that the database will automatically give you the very latest dated row for the criteria. It is less than or equal to the Current Date (today’s date).
- By Default, the query tool will retrieve CURRENT information.
- One way of thinking about this is that the effective date is in a subquery.
- If you have a clear understanding of the ramifications, you may remove or alter the criterion that has been automatically created. Otherwise, you should leave the criterion as is, and click the OK button.
Special Effective Date Operators/Condition Types
- If you choose an effective-date Condition (visible on the Edit Criteria Properties page in the Condition Type column), you return one effective-dated row of information per item. You can vary what you want the effective date compared against.
- Shown below in this drop-down list are the beginning entries of the special Effective Date operators:
Effective Date Conditions Described
This table describes the effective-date conditions on the Edit Criteria Properties page:
| EffDate<= | Returns the one row that is closest to or equal, without going over, to the expression 2 date value. If expression 2 is “Current Date”, then this will always retrieve the most current rows from the table. |
| EffDate< | Returns the one row that is closest to, without going over, the expression 2 date value. |
| Eff Date >= | Returns the one effective dated row that is closest to being equal to or greater than the expression 2 date. |
| Eff Date > | Returns the one effective dated row that is closest to being greater than the expression 2 date. |
| First Effective Date | Returns the row with the oldest effective date, usually the first row that is entered for an item. |
| Last Effective Date | Returns the row with the latest effective date, even if that date is still in the future. |
Effective Status field
- The EFF_STATUS field has two values: Active and Inactive.
- If you are working with effective-dated tables and looking for the current row of information, you may also want to add criteria in the EFF_STATUS field to specify only active rows.
- The table may also include the EFF_SEQ (effective sequence field). It is a sequencing number that is provided to further refine the effective date.
Three Categories
- With Effective dated queries, rows of data are classified in one of three categories:
1. Current – The highest effective date is less than or equal to today’s date (system date on server). There can only be one curent row
2. History – The effective date is less than the current effective date
3. Future – The effective date is greater than today’s date
- You can use effective date fields in criteria to:
- View all rows, regardless of their effective dates
- View rows that aren’t currently in effect
- View the rows that were effective as of a particular date
Demonstrations of using Effective Date in Query
These are illustrations of some methods for working with the Effective Date:
Effective Date – Date Range (Between Criterion)
Above is an illustration of how to build a query that finds information effective during a range of time using the Between Criteria.
Effective Date – Date Range (Between Criterion) with a Prompt
If you need to use a date range utilizing Between Criteria with a prompt, take a look at this example.
Point-in-Time (Historical) Query (updated 2017)
This is a demonstration of how to build a query to retrieve data for a date at some point in time in the past
Effective Dates with Multiple Tables
When doing table joins on effective dated information, you will rarely modify the criteria that PeopleSoft automatically creates. If you do need to change the criteria, be certain you’re changing the correct rows of criteria. This article shows how to use Effective Dates with multiple tables:
Reasons to Delete Effective Date
There will be a few instances where you need to delete the EFFDT criteria altogether. This is a demonstration of the following two examples:
- Part I – If we want all of the history from a particular EFFDT. Example: We want to see all the Program Actions for a set of students, not just the latest Program Action.
- Part II – We may delete the EFFDT criteria if we are basing our logic on some other date field within Peoplesoft. An example would be if we want to see all of the Program Actions that occurred before or after a given Action Date.