DateTrack adds the dimension of time to an application's database. The value of a DateTracked record depends on the date from which you are viewing the data. For example, querying an employee's annual salary with an effective date of 12-JUL-1992 might give a different value than a query with an effective date of 01-DEC-1992. However, the application and the user see the employee's pay as a single record.
This section describes the behavior of forms that incorporate DateTracking.
When you begin to update or delete a record on a DateTracked form, you are prompted with a number of choices. This section describes the choices and their effect on the DateTracked table.
The term "today" refers to the effective date set by the user.
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.
In most forms, users are prompted for the update mode for each record they update. In some forms, they are asked for the update mode for only the first record they update. Any other rows updated take the same update mode. Users are not prompted again, until they have committed or cleared any outstanding changes.
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.
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.
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.
| EMPID | EMPNAME | SALARY | EFFECTIVE_START_DATE | EFFECTIVE_END_DATE |
|---|---|---|---|---|
| 3203 | SMITH | 17,000 | 12-MAR-1989 | 19-JUL-1989 |
| 3203 | SMITH | 18,200 | 20-JUL-1989 | 20-JUL-1989 |
| 3203 | SMITH | 18,400 | 21-JUL-1989 | 01-DEC-1989 |
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.
| EFFECTIVE DATE | EMPID | EMPNAME | SALARY |
|---|---|---|---|
| 11-MAR-1989 | ** no rows retrieved | ||
| 12-JUN-1989 | 3203 | SMITH | 17,000 |
| 21-JUL-1989 | 3203 | SMITH | 18,400 |
| 02-DEC-1989 | ** no rows retrieved |
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.
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>.
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.
To incorporate DateTrack on to an existing table called EMPLOYEES, follow these steps:
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;
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.
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'))
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
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.
The following global variables can be used at the DT_SELECT_MODE event. They are not available at any other CUSTOM library event.
| Global Variable Name | Read/Write | Description |
|---|---|---|
| g_dt_update | Read and write | Set to TRUE when the product would normally display the Update button on the mode selection window. Otherwise set to FALSE. |
| g_dt_correction | Read and write | Set to TRUE when the product would normally display the Correction button on the mode selection window. Otherwise set to FALSE. |
| g_dt_update_change_insert | Read and write | Set to TRUE when the product would normally display the Insert button on the mode selection window. Otherwise set to FALSE. |
| g_dt_update_override | Read and write | Set to TRUE when the product would normally display the Replace button on the mode selection window. Otherwise set to FALSE. |
| g_dt_zap | Read and write | Set to TRUE when the product would normally display the Purge button on the mode selection window. Otherwise set to FALSE. |
| g_dt_delete | Read and write | Set to TRUE when the product would normally display the End Date button on the mode selection window. Otherwise set to FALSE. |
| g_dt_future_change | Read and write | Set to TRUE when the product would normally display the All button on the mode selection window. Otherwise set to FALSE. |
| g_dt_delete_next_change | Read and write | Set to TRUE when the product would normally display the Next button on the mode selection window. Otherwise set to FALSE. |
Important: Custom code can change a TRUE value to FALSE. However, if it tries to change a FALSE value to TRUE, the system ignores this change.
To enable the DT_SELECT_MODE event, add the following code to the STYLE procedure in the CUSTOM package, CUSTOM library:
if event_name = 'DT_SELECT_MODE' then
return custom.after;
else
return custom.standard;
end if;
Suppose you wanted to stop the Delete mode button from being displayed on the Mode Selection window when DateTrack would normally make it available. You could add the following code to the EVENT procedure in the CUSTOM package, CUSTOM library:
if (event_name = 'DT_SELECT_MODE') then
if name_in('GLOBAL.G_DT_DELETE') = 'TRUE' then
copy('FALSE', 'GLOBAL.G_DT_DELETE');
end if;
end if;