Definition:
The first date for which a row in a Data Warehouse table is effective. This is a calculated field that only appears in the Data Warehouse: no such field exists in the HRMS application itself.
DW_FEFF_DT and DW_LEFF_DT (Data Warehouse First & Last Effective Date, respectively) are linked fields populated by the ETL process that transfers data from HRMS to the Data Warehouse.
For the last effective row, DW_LEFF_DT field is populated with a high value (12/31/9999). When a new row becomes effective, its effective date populates DW_FEFF_DT, the high value populates the new row's DW_LEFF_DT field; and the previously-last (now penultimate) row's DW_LEFF_DT field is assigned the new row's effective date minus one day.
The fields DW_FST_EFFDT and DW_LST_EFFDT are especially useful when doing point-in-time reporting. These fields describe periods of time that never overlap, except in cases where multiple rows exist for the same effective date (and in the exceptional cases where effective dates are prior to 07/01/2002, as described below).
To give an example, rows might include these fields,populated as shown:
Row EffDt DW_FST_EFFDT DW_LST_EFFDT
1 12/15/2002 12/15/2002 12/31/9999
2 11/01/2002 11/01/2002 12/14/2002
3 09/12/2002 09/12/2002 10/31/2002
4 06/15/2000 07/01/2002 09/11/2002
5 05/01/1986 07/01/2002 07/01/2002
EXCEPTIONAL CASES: Because HRMS went into production on July 1, 2002, no rows in the Data Warehouse have an earlier value of DW_FEFF_DT than 07/01/2002. In cases where multiple rows of data were converted from PPS (and imported into HRMS) on July 1, 2002, all but the latest effective-dated of those rows will have a value of 07/01/2002 in DW_LEFF_DT as well. That latest of the coversion-data's effective-dated rows will contain a high-value (12/31/9999) in DW_LEFF_DT if no rows have been added since HRMS went live; otherwise, that field will contain a date value one day earlier than the next row's effective-date.
Short definition...