Specifying Effective Date Criteria

Effective-dated tables have record definitions that include the Effective Date (EFFDT) field. This field, used throughout the PeopleSoft applications, provides a historical perspective enabling you to see how the data has changed 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.

When you use a PeopleSoft application for day-to-day processing, you usually want the system to give you the currently effective rows of data—the rows where the effective date is less than or equal to today’s date. You do not want to see the history rows, which are no longer accurate, nor do you want to see future-dated rows, which are not yet in effect.

When you query an effective-dated table, however, you may want to see some rows that are not currently in effect. You might want to see all the rows, regardless of their effective dates. Or you might want to see the rows that were effective as of some date in the past.

To specify effective date criteria:

1. 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 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. Defaults are:

   • Expression 1: Record Alias.EFFDT

   • Condition Type: EffDt <=

   • Expression 2: Current Date

   • Effective Sequence: Last

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

   • Select Current Date to compare each row’s effective date against today’s date.

   • Select Constant to display the Define Constant box 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.

   • Select Expression to display the Define Expression page 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.

   • Select Field to display the Select Field box 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.

   • Select First Effective Date to return the row with the oldest effective date, usually the first row that is entered for an item.

   • Select Last Effective Date to return the row with the latest effective date, even if that date is still in the future.

   • Removing the Effective Date criterion is equivalent to selecting No Effective Date. The query returns all rows, regardless of their effective dates.

Note:

All options (except No Effective Date) return a single row for each item on the table. If you want a subset of the rows (for example, all future-dated rows or all history rows), enter a selection criterion in the Effective Date field. Use the standard comparison operators rather than the Effective Date comparison operators.

Remember that the effective date operators work differently than the standard comparison operators: they always return a single effective-dated row. For example, Eff Date <= returns the one row for which the EFFDT value is most recent, whereas not greater than would return the currently active row and all history rows.