HR Suite

In this article, I will explain how the date-tracking works in Oracle HRMS.

What is DateTracking in Oracle HRMS?
Let's take an example. An employee emails to their HRMS department that her Surname will change after 2weeks, as she gets married after 2weeks.
Today being 10 December, HRMS team will Date-Track to 24-Dec-2006 and query on her record.
Next her record will be changed to reflect the Surname applicable from 24-Dec-Onwards.

How does Date-Tracking actually happens?
As soon as you logon to a screen which is based on Date sensitive information,
Oracle will prompt you with two options
1. Change the Effective Date of the current logged on SESSION
2. Retain the SYSDATE as Current Effective date

In reality, a record gets created/updated in a table called fnd_sessions.



If you click on YES, then you will get an opportunity to change the Current Session Date


OK, the Date Selected by user for date-track is stored in fnd_sessions with their sessionid. But how about application data?
When you attempt to modify a date-track sensitive data such as Person Surname, Oracle HRMS prompts you for either a Correct Mode or Update Mode.

Lets say the record, before making the change the record was
Person First Name: Aish
Person First Name: Rai
effective_start_date : 10-JAN-2003    --when the person joined organization
effective_end_date : 31-12-4712      -- From Hr_Api.g_eot [end of time, well not literally]


If the record is modified in Correction mode, then this record will be modified as
Person First Name: Aish
Person First Name: Bachan
effective_start_date : 10-JAN-2003    --when the person joined organization
effective_end_date : 31-12-4712      -- From Hr_Api.g_eot [end of time, well not literally]


If the record is modified in UPDATE mode, then this record will be modified as
The existing record will be end dated
Person First Name: Aish
Person First Name: Rai
effective_start_date : 10-JAN-2003    --when the person joined organization
effective_end_date : 23-Dec-2006      -- a day prior to date-track date

A new record will be created
Person First Name: Aish
Person First Name: bachan
effective_start_date : 24-DEC2006    --when the person joined organization
effective_end_date : 31-12-4712      -- From Hr_Api.g_eot



Does this mean, when modifying in UPDATE mode existing record is end-dated and a new record is created?
Yes, indeed.


What are the other scenarios for the usage of Date-tracking?
1. Hiring an employee in future date.
2. Making salary changes which are effective after say 2months.


On a SQL*Plus session, I get nothing when I do select * from per_people_v?
Almost all the HRMS views on which screens are HRMS based, they have a join to fnd_sessions table.
When user changes their date-track value, it gets reflected in fnd_sessions table.
Use the SQL below to create a default record for your SQL*Plus session/
INSERT INTO fnd_sessions
  (session_id
  ,effective_date)
  (SELECT userenv('sessionid')
         ,SYSDATE
   FROM dual
   WHERE NOT EXISTS (SELECT 'c'
          FROM fnd_sessions s1
          WHERE userenv('sessionid') = s1.session_id));



What if I forget to change the date-tracked date when enteting the screen?
You can click on Calendar icon as below.

Anil Passi

Add comment