Change HRMS Security Profile At runtime For SQL Session- Options

If you wish to set your SQL Session in to restrict records as per HR Security, then you have couple of options

Option 1
Use this technique to restrict your SQL Access to a specific HR Security Profile.
Firstly find the security profile Id of your interest.
In this case, I will use an arbitrary id of 211. To know what 211 stands for, run the below query
select SECURITY_PROFILE_NAME from PER_SECURITY_PROFILES where SECURITY_PROFILE_ID =211 ;
begin
fnd_profile.put ( 'PER_SECURITY_PROFILE_ID' , '211' ) ;
hr_signon.initialize_hr_security;
end ;
/

Running above SQL will restrict data as per security profile id 211

Option 2
In case your SQL Session wants to display all the records regardless of security profile, then set this package variable to Y
This is particularly useful if you want to switch your context from a specific security profile to None [show all]
begin
hr_signon.g_hr_security_profile.view_all_flag := 'Y' ;
end ;
/

To create a default Date-Track Session Entry for your SQL*Plus session, simply run this SQL

set serveroutput on size 100000 ;
DECLARE
CURSOR c_session IS
SELECT *
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
p_session c_session%ROWTYPE;
BEGIN
dbms_output.enable(100000);
OPEN c_session;
FETCH c_session
INTO p_session;
IF p_session.session_id IS NULL
THEN
dbms_output.put_line(' FND_SESSIONS IS NOT POPULATED...');
INSERT INTO fnd_sessions
(session_id
,effective_date)
VALUES
(userenv('sessionid')
,SYSDATE);
dbms_output.put_line(' POPULATED FND_SESSIONS WITH SYSDATE...');
ELSE
dbms_output.put_line('Effective Date Already Exists. Its value is ====> ' ||
p_session.effective_date);
END IF;
FETCH c_session
INTO p_session;
IF c_session%FOUND
THEN
dbms_output.put_line('Oops multiple session entries ');
END IF;
CLOSE c_session;
COMMIT;

hr_signon.initialize_hr_security;
COMMIT;
END;
/

Please note that hr_signon.initialize_hr_security might reset hr_signon.g_hr_security_profile.view_all_flag to N

Set as favorite

Bookmark

Email This

Comments (7)

Subscribe to this comment's feed

Simply Superb...
written by Vijayakumar , June 19, 2008

Dear Anil,

Thank you very much & greatly appreciate all your efforts for providing valuable information .Simply Superb...

Regards,

Kasi, Vijayakumar

report abuse

vote down

vote up

Votes: +0

Userenv(sessionid) in SSHR
written by Sheena , November 06, 2008

Hi,
I have a value set on the Org DFF what has this where clause:
where p.business_group_id +0 = :$PROFILES$.PER_BUSINESS_GROUP_ID
and fs.effective_date between p.effective_start_Date and p.effective_end_date
and p.current_employee_flag = 'Y'
and fs.session_id = USERENV('SESSIONID')
and p.person_id = a.person_id
and fs.effective_date between a.effective_start_Date and a.effective_end_date
and a.assignment_type = 'E'
and a.primary_flag = 'Y'

Somehow the usernev part is giving a problem and when I try to use OA Framework to display the entire DFF, the segment that the Value set is associated to, does not get data and give an error saying that the person_id was not found in the value set.
if I take the fnd_session table and the userenv clause out, it works fine.
How can I make it work? Any help will be greatly appreciated!

Thanks
Sheena

report abuse

vote down

vote up

Votes: +0

...
written by Anil Passi- , November 06, 2008

There is a package named oracle.apps.per.common.CommonUtils

This has a class named CommonUtils
You need to invoke this as CommonUtils.setEffectiveDate

CommonUtils.setEffectiveDate(oadbtransaction, date);

report abuse

vote down

vote up

Votes: +1

Userenv(sessionid) in SSHR
written by Sheena , November 06, 2008

Anil , Thanks so much for your response. One more question, from your response and my investigation it seems like SS does not store the session id in the database table. Do you know why this is different from what forms does? Cause forms saves the info.
Is there never a requirement to get the session id from the system for SS?
Also, what is the best web site for all of Oracle java classes please? Sun Java has a good documentation, but I could not find anything similar for Oracle..

Thanks
Sheena

report abuse

vote down

vote up

Votes: +0

...
written by Anil Passi- , November 06, 2008

Forms uses pessimistic locking, hence a forms session clings to a Database Connection.
Hence locking can be immediate in Oracle Forms

OAF however follows optimistic locking, hence OAF requests a session as and when needed.
Please see the details in this forum thread
http://apps2fusion.com/forums/...1467#p1467

Thanks
Anil Passi

report abuse

vote down

vote up

Votes: +0

How Can I get the Assignment_id from the Self Service
written by Amr Sokkar , February 25, 2009

Hello Folks,
Am trying to get the Assignment_id from the self service.
Am customizing the payslip as am using the global legislation, so each employee should see his payslip, so am doing this by catching the assignment_id from the concurrent program definition , making the default value is profile and passing PER_ASSIGNMENT_ID , that one didn't work, I used the fnd_profile.value('PER_ASSIGNMENT_ID') as a sql default value but it did not work as well.
As a workaround I have used the USERNAME profile to get the assignment_id , however , this soultion worked in PUI and not in Self Service.
My Question is , did anyone try to populate the assignment_id in any DFF or any value set and succeed to do so ? Please help me Guys

Thanks,
Amr Sokkar

report abuse

vote down

vote up

Votes: +0

Cleanup of fnd_sessions table?
written by Brent Lowe , April 28, 2010

As far as creating a default Date-Track Session Entry, do you have to then delete your entry from fnd_sessions when you are done? What handles that cleanup?

Thanks

report abuse

vote down

vote up

Votes: +0

Write comment

Name

Title

Comment