Date tracking in Oracle E-business is a mechanism
to store data based on dates. This helps storage of historical data along with
current data in the system.
The date tracked tables in Oracle end with ‘_F’ and
have columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. These columns along
with PERSON_ID form the composite key to identify a person’s record at a given
point of time.
For example, to get the person’s record as on current date –
SELECT *
FROM per_all_people_f
WHERE sysdate BETWEEN effective_start_date AND
effective_end_date;
Modes of Date Tracking:
- Purge: This
removes the entire record from the database
- End Date: This
updates EFFECTIVE_END_DATE of current active row to today’s date.
- Correction:This
mode will update the column. No record history will be retained.
- Update: This
will add a new row in the table with the EFFECTIVE_START_DATE as today’s
date and EFFECTIVE_END_DATE as EOT(31-DEC-4712 and update current active
record with EFFECTIVE_END_DATE as yesterday’s date
If Update mode selected, system will check if there are any future updates
entered for the record being updated. If it has been updated in future, system
will prompt for two more modes
- UPDATE_CHANGE_INSERT: The changes that the user makes remain in effect until the
effective end date of the current record. At that point the future
scheduled changes take effect.
- UPDATE_OVERRIDE:The user’s changes take effect from now until the end date of the
last record in the future. All future dated changes are deleted.
