Record History is one of the excellent feature implemented by Oracle to track the data changes made by the users. Today in this article I will write how Oracle tracks the data changes made on the application.
Record history information is stored in the backend tables in the form of WHO columns.
Following are the WHO columns generally observed in almost all tables of Oracle Applications
- created_by – Keeps track of which user created the record(row)
- creation_date – Stores the date on which a row was created
- last_update_by – Keeps track of who last updated the row
- last_update_date – Stores the date on which the row was last updated
- last_update_login – Login Session ID of the user
| Column Name | How data is populated? |
| created_by | TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’)) |
| creation_date | SYSDATE |
| last_updated_by | TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’)) |
| last_update_date | SYSDATE |
| last_update_login | TO_NUMBER(FND_PROFILE.VALUE(‘LOGIN_ID’)) |
Some more information on last_update_login:
Let’s assume you have a user called JOHN (say User Id = 1234).
When user JOHN logs on to Oracle Applications a record is created in the FND_LOGINS table (say Login Id = 4321). While logged in, a change is made to a Sales Order Lines record (OE_OREDER_LINES_ALL table) – the Last Updated By will be set to 1234 and the Last Update Login will be set to 4321.
If JOHN logs off and then logs on again, another record is created in the FND_LOGINS table (say Login Id = 5432). While logged in this time, another change is made to the Sales Order Line record – the Last Updated By will still be 1234 and the Last Update Login will be updated to 5432.
The data in FND_LOGINS which will probably be purged in regular intervals and so you will only be able to find only the details of the most recent logins of the application.
How is this implemented in the application?
FND_STANDARD.SET_WHO Procedure is used to update the WHO columns in a Table when ever DML operations are performed from the application.
You represent each of the WHO columns as hidden fields in each block of your form (corresponding to the WHO columns in each underlying table). A call to FND_STANDARD.SET_WHO in PRE-UPDATE and PRE-INSERT to populate these fields.
Note: This API is a part of FDRCSID.pll