Understanding the Date Track Feature

Date Track Feature is extensively used in many of the Oracle HRMS forms to view/modify the details within the form based on the snapshot of the information which is dependent on time. It adds dimension of time to an application's database. For example, querying an employee's annual salary with an effective date of 27-JUN-2006 might give a different value than a query with an effective date of 06-SEP-2006. Only a single record depending on the date specified in the effective Date field, will be retrieved within the form.

UPDATE/DELETE/INSERT in Date tracked Blocks:

UPDATE:

When a user first alters a field in a DateTracked block in the current Commit unit, he or she sees a choice of Update prompts as follows:

• UPDATE - Updated values are written to the database as a new row, effective from today until 31-DEC-4712. The old values remain effective up to and including yesterday.
• CORRECTION - The updated values override the old record values and inherit the same effective dates.

If the user selects UPDATE, DateTrack checks whether the record being updated starts today. If it does, a message warns that the previous values will be lost (because DateTrack can only store information on a day by day basis). DateTrack then changes the mode for that record to CORRECTION.
Next, if UPDATE was selected, DateTrack checks whether the record being updated has already had future updates entered. If it has been updated in the future, the user is further prompted for the type of update, as follows:

• UPDATE_CHANGE_INSERT (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 (Replace) - 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.

DELETE:

When deleting a record, the user is prompted for the type of delete. There are four options, as follows:

• DELETE (End Date) - This is the DateTracked delete. The record that the user is currently viewing has its effective end date set to today's date. The record disappears from the form although the user can requery it.
• ZAP (Purge) - This is the total delete. All records matching the key value, whatever their date stamps, are deleted.
• FUTURE CHANGE (All) - This choice causes any future dated changes to the current record, including a future DateTracked delete, to be removed. The current record has its effective end date set to 31-DEC-4712.

The record can again be displayed by requerying.

• DELETE NEXT CHANGE (Next Change) - This choice causes the next change to the current DateTracked record to be removed.

Where another future dated DateTracked row exists for this record, it is removed and the current row has its effective end date set to the effective end date of the deleted row.
Where no future DateTracked row exists, but the current row has an end date other than 31-DEC-4712, then this option causes the effective end date to be set to 31-DEC-4712. This means that a date effective end is considered to be a change.
Notice that this option again removes the current row from the form, though it can be displayed again by requerying.

INSERT:

The user is not prompted for any modes when inserting a record. The effective start date is always set to today (Effective Date). The effective end date is set as late as possible. Usually this is 31-DEC-4712, although it can be earlier especially when the record has a parent DateTracked record.

To know about the underlying table structure and details of implementing Date Track Feature, please refer:

How Date Tracking Works in Oracle