p_datetrack_update_mode in HRMS

DateTrack History is available in most windows where you can enter date tracked information. DateTrack History enables you to track changes made to records and fields, and by whom. 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. Forexample, querying an employee's annual salary with an effective date of 12-JUL-1992might 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.

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.

There are two values we can pass when we try to update or load an employee information using the p_datetrack_update_mode and both these have there own advantages and disadvantages.

1. Update: This will end date the already existing entry in the base table and then create a new entry with the start date as sysdate. 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.
Adv: This will keep track of your history data and at some point if you want to check your previous data it would be very helpfull. The updated values override the old record values and inherit the same effective dates.
Disadv: Creates as many number of records as we perform the updates on the record and this will increase the data in the base tables.

2. Correction: This will only update the relevant columns that is passed to the API.
Adv: Doesnot create any record while performing the updates on the record hence reducing the content of data in the base tables.
Disadv: Doesnot keep track of your history data.

Shameem Bauccha has explained quite well about the datetrack in his blog. Please refer to his blog at: http://oracledocs.wordpress.com/2009/06/22/hrms-date-track/