Fnd_sessions, Oracle HR Date Tracking

oracle EBS HRMS R12

Fnd_Session, Oracle HR Date Tracking

In this article I am going to explain Fnd_Session and cover the answers of following questions.

1)  How Date Track Works?

2) Per_people_v return null rows or select * from per_people_v return null rows?

3)  In Oracle Report Builder how to use FND_SESSIONS. How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE?

4) What is date tracking in Oracle HRMS?

5) Why there are multiple records in per_people_f against each employee.

6) What is fnd_session

Q: How Date Track Works ?

Q: In Oracle Report Builder how to use FND_SESSIONS. How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE?

Q: What is date tracking in Oracle HRMS?

When you try to update or delete a record in People screen or some other screens in HR Module. A Window appear with Two Options Update or Corrections.

Update: When you press Update button it create new row in database with new effecting date and value and old records remains there as history.

Correction: When you press Correction button it override the existing value.

In case if you update a record on 01-Jan-10 (effective date also 01-Jan-10) and you update record with effective date 15-dec-09 you will  further prompted for the type of update, as follows:

Update Insert:  By presseing intert button oracle will insert another record in database with effect from 15-Dec-09 till 01-Jan-10 and it will also change any previous record’s End effective date with 14-Dec-09.

Update Override (Replace):   Update effect from current effect date to end date of the last record.

Q: Per_people_v return null rows or select * from per_people_v return null rows

To query per_people_v you have to insert a session_ID and effect_date in fnd_session table. If you study the HRMS views it has a join with fnd_session’s effect date.  When you query a view it looks into the Fnd_Session’s effect date, either its between current record’s effect date or not. So before querying Oracle HRMS views like per_people_v use following insert command and you will start getting results based upon your current effective date.

Insert into Fnd_Sessions (Session_id,effective_date)   (select userenv(‘sessionid’),sysdate from dual) ;

Now if you query  select * from per_people_v , you will start getting results.

 

Q: In Oracle Report Builder how to use FND_SESSIONS. 

How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE

 

In Oracle Report Builder If you are using per_people_v in your query, you will get null rows to prevent this problem you can use “After Parameter Form” trigger.

and write following code.  

insert into fnd_sessions    (session_id, effective_date)
    values (userenv(‘sessionid’),:p_effective_date);

Q: What is fnd_sessions

FND_SESSIONS is table of APPLSYS schema which. when 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

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.  Now onward whenever you will update a record oracle will pic effective_date  from fnd_sessions against you sessionid. You can insert sessionid and effective_date  into fnd_session from SQL Plus or Toad as well and get required results.