Think of Date Track as being able to time travel without leaving your desk!
This was the first concept I was introduced to when I started with Oracle HRMS. There is just so much you learn about date tracking as and when you code more and more! I have tried to cover as much as I can about date-tracking in the next few paragraphs….. This hopefully will help you to understand the need of date tracking better!
Date tracking is a design / concept, which is used by Oracle E-Biz, in order to support the storage of historical data, along with the current ones. So basically, date tracking is a mechanism to store all the data based on dates.
I will explain this with the help of an example, but first let us get into the important terms which we need to know for date tracking.
Click the on the following button when you want to alter the effective date
How to deal with effective dates given as columns in various tables?
The effective date is used for date tracking, but not to determine when a change was made. To determine when a change was made you need to look at the date last updated.
The effective dates are used to tie records in different tables together. Some tables have just an effective date, some have an effective start and an effective end, while others have a FROM date and a TO date. All of these can be used together to determine the state of play as it was at a certain point in time. The current records can have their effective dates compared to SYSDATE like this:
Trunc(sysdate) between effective_start_date and effective_end_date
An interesting point to note down is that all those tables which have ‘_f’ at the end are date tracked tables! (Simple Na!) Few examples are:-