How Date Tracking Works in Oracle

This article details the table structure required to implement the date track feature and other technical details. You may refer the article :

Understanding Date Track Feature , to know more about this feature.



A DateTracked (DT) record is what the application and the user see: a single DT record for each key value. However, this DT record may change over time, so it may correspond to one or more physical rows in the database. The history for the record is held by storing a row when the record is created, and an extra row every time the record changes. To control these rows, every DateTracked table must include these columns:

EFFECTIVE_START_DATE DATE NOT NULL
EFFECTIVE_END_DATE DATE NOT NULL



The effective start date indicates when the record was inserted. The effective end date indicates when the record was deleted or updated. A deleted record has the highest end date of all the rows with that key, but for an updated record there will be at least one row for this key with a higher effective end date.
As time support is not provided, the effective start date commences at 0000 hours and the effective end date finishes at 2359 hours. This means that a DT record can change at most once per day.


Example






The table above shows the physical table after the user has done the following:
Set the effective date to 12-MAR-1989. Inserted record for SMITH.
Set the effective date to 20-JUL-1989. Updated SMITH record with new salary.
Set the effective date to 21-JUL-1989. Again updated SMITH record with new salary.
Set the effective date to 1-DEC-1989. Deleted record for SMITH.


The table below shows what the user sees on querying the SMITH record at different effective dates.



Because the primary key column in the table is no longer unique, any indexes on the table that included the primary key column must now also include the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns.

List of DateTracked Tables

To get a list of the DateTracked tables used in Oracle Human Resources, select from the data dictionary where the table name is like Application Short Name%F. Substitute in the HRMS application short code you are interested in (such as PER or BEN). For each of the DateTracked tables there is a DateTracked view called 'TABLE NAME' and a synonym pointing to the full table called 'TABLE NAME_F'



Creating a DateTracked Table and View

The previous section described the table structure of a DateTracked table. This section describes the steps to go through to create a DateTracked table and view.
You must use the following nomenclature for DateTracked tables:
Base table: 'TABLE NAME_F '
DateTracked view: 'TABLE NAME '
In addition to the DateTracked view, there is another view that shows the rows in the table as of SYSDATE. The name of this view is derived by replacing the _F at the end of the table name by _X.


Example
To incorporate DateTrack on to an existing table called EMPLOYEES, follow these steps:
1. Create a new table called EMPLOYEES_F that is identical to EMPLOYEES but with the columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE added. Normally you would set the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns to the maximum range. CREATE TABLE EMPLOYEES_F AS SELECT EMPLOYEES.*, TO_DATE('01-01-0001','DD-MON-YYYY') EFFECTIVE_START_DATE, TO_DATE('31-12-4712','DD-MON-YYYY') EFFECTIVE_END_DATE FROM EMPLOYEES; ALTER TABLE EMPLOYEES_F MODIFY (EFFECTIVE_START_DATE NOT NULL, EFFECTIVE_END_DATE NOT NULL);
Remove the old table. DROP TABLE EMPLOYEES
If the old table already has the two new columns, just rename it. RENAME EMPLOYEES TO EMPLOYEES_F;

2. Create the New Unique Indexes of the DateTracked Table by dropping the old indexes, creating the new unique indexes as old unique index + EFFECTIVE_START_DATE + EFFECTIVE_END_DATE, and creating the new non-unique indexes the same as the old non-unique indexes.

3. Create a DateTracked view called EMPLOYEES. This view uses the entry in FND_SESSIONS for the current user effective id for the effective date. CREATE VIEW EMPLOYEES AS SELECT * FROM EMPLOYEES_F WHERE EFFECTIVE_START_DATE <= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID')) AND EFFECTIVE_END_DATE >= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID'))

4. To create the view EMPLOYEES_X based on the table EMPLOYEES_F, use the following SQL: CREATE VIEW EMPLOYEES_X AS SELECT * FROM EMPLOYEES_F WHERE EFFECTIVE_START_DATE <= SYSDATE AND EFFECTIVE_END_DATE >= SYSDATE

Restricting Datetrack Options Available to Forms Users

When a user edits or deletes a datetracked record, the system displays a window asking the user what type of update or deletion to perfom. Before it displays this window, the system calls a custom library event (called DT_SELECT_MODE). It passes in the list of buttons that DateTrack would normally display (such as Update and Correction).

Your custom code can restrict the buttons displayed. If necessary, it can require that the user is given no update or delete options, and receives an error message instead. However, it cannot display buttons that DateTrack would not normally display for the entity, effective date, and operation the user is performing.

If the user chooses Update and future changes exist, the custom library event point may be executed a second time so your custom code can determine whether the user is given the two update options: Insert and Replace.