Oracle PeopleSoft Query uses the effective-date (EFFDT) field to enable you to view data that changes over time.
This diagram shows how the system classifies the effective date into categories.Step 1
With effective-dated queries, rows of data are classified in one of three categories:
The highest effective date (and possibly sequence number, if used) is less than or equal to today’s date (system date on the server).
There can be only one current row per high-level key.
The effective date is less than the effective date of the current row.
The effective date is greater than today’s date (system date on the server).Step 2
When you choose the record that has EFFDT as a key field, Query Manager automatically creates default criteria and adds that criteria to the Criteria page.
This EFFDT criteria is used to specify which row of data PeopleSoft Query retrieves for each item in the table.
The default is the currently effective row.Step 3
Other defaults of the Effective-date field are:
• Expression 1: Record Alias.EFFDT
• Condition Type: Eff Dt <=
• Expression 2: Current Date
If you select one of the comparison options, choose to compare each row’s effective date against today's date or a date other than today.Step 4
Select the Current Date option to compare each row's effective date against today's date.Step 5
Select the Constant option to display the Define Constant section so that you can enter a date.
Select this option when you want to see the rows that were effective as of a past date or that will be effective on some future date.Step 6
Select the Expression option to display the Define Expression section so that you can enter a SQL expression that evaluates to a date.
Select this option if you want to prompt users for an effective date when they run the query.
You can add a prompt to the expression that you define in the Define Expression page.Step 7
Select the Field option to display the Choose Record and Field section so that you can select the record field that holds the date to which you want to compare effective dates.
Select this option when you want to see the rows that were effective at the same time as some other record.
For example, if you’re reviewing the list of products on a customer order, you will want to see the products that were effective on the date of the order.Step 8
You have successfully reviewed the Understanding Effective Date Criteria topic.
Table of Contents Start Topic