Getting the most current and active effective dated row

Let’s say we had the following rows in the PS_INSTITUTION_TBL:

PSUNV 01-01-1900 A
PSUNV 17-11-2010 I

If we had an Institution prompt based off of this table, we’d expect to only retrieve the most current active row. In this case it would be the first row in the table (i.e. the one with EFFDT 01-01-1900).

So let’s now take a look at the following 2 queries (both with Oracle resolved meta-SQL for %DateIn).

Incorrect Query:

Running this query would not return any rows because we’d be trying to find a row that has an effective date value of 17-11-2010 and that is active.

Correct Query:

This query will ensure that we have the most current effective dated and active date.

As a general rule, I always put the EFF_STATUS = ‘A’ condition within the effective date check. This unfortunately means that the %EffDtCheck meta-sql is essentially useless when you want to filter by EFF_STATUS (the majority of situations).


After doing some followup reading, I discovered the following link:

It seems that some would argue that no rows should be returned in my example above as the purpose of effective dating is to still maintain the history of the rows. Therefore, for a particular period (i.e until 17-11-2010), the institution was active, but right now it is currently inactive. I’ve always thought that EFF_STATUS was a soft delete toggle, but it looks as though PeopleTools does not see it that way either because when you use meta-SQL such as %EffDtCheck in my example above, no rows will be returned.

Leave a Reply

Your email address will not be published. Required fields are marked *