Let me explain this issue with Q & A, followed by giving you a solution/the best practice resolution.
Note: You must test the solution suggested here thoroughly in your system. Customizations are not supported by Oracle. You will have to disable this customization and reproduce bugs in Oracle products when raising Tars.
What is a person type of "Applicant" in Oracle HRMS?
When someone applies for a job using Oracle iRecruitment, Oracle assigns a person type usage named "Applicant" to that person record.
What if an existing employee applies for an internal job using iRecruitment? Do they still get applicant attached to their person type usages record?
That's correct. Hence in this case the employee will have two concurrent person type usages
1. Employee -Their existing person type usage
2. Applicant -Created by iRecruitment
So what's the big deal, why does this cause a problem?
Usually, you wouldn't like your manager to know that you have applied for some other internal job. However, given the two person type usages, Oracle screens will display person type as "Employee.Applicant". Effectively, if your manager queries your person record[subject to security], they will know that you are unhappy working under them. This is not a problem [i.e. knowing the truth], but it may deter employees from seeking other jobs within the Organization. Interestingly this can also save the company paying recruitment fee to agents.
So is the solution merely to hide "Applicant" from appearing in person type field?
CORRECT, let’s discuss how this can be done.
Options you have are:-
BAD OPTION/SOLUTION
Modify all necessary D2K forms and reports by using forms personalization
CONS:-
How D2K forms will you personalize?
Identify and modify every report that lists person type.
Ditto for discoverer and D/W extracts
Ditto for XML publisher too
Ditto for workflow notifications
Same applied to "OA framework", how many View Objects[VO's] will you be extending
Obviously this approach sucks, and is not even worthy of discussion.
SOLUTION :- ROW LEVEL SECURITY ON PER_PERSON_TYPE_USAGES
Create a Row Level Security - RLS on per_person_type_usages.
What will this RLS do?
At core database level; this will hide the record that corresponds to person_type_Id of "Applicant". Hence all your forms, reports etc will work straight away.
Well well, but this will break the functionality of iRecruitment, as it relies upon Applicant entering being present?
Correct, you can design your RLS such that it hides applicant only from non-iRecruitment responsibility.
So what is the logic?
1. Create an RLS on per_person_type_usages table
2. Inside RLS, append a where clause to this table, such that, the record for Applicant is visible only from those responsibilities which have text IRC in their responsibility key.
Note :- This is one approach. You might want to use a profile option.
3. Effectively APPLICANT person_type_id record will be non-existent for non-iRecruitment responsibilities.
Hence the join between per_person_type_usages and per_person_types will fail, hence your manager will only see "Employee" and not "Employee.Applicant"
Question : Well, what if some HR Super Users want to convert an "Applicant" into an "Employee", they might need to see the Applicant usage from HRMS responsibility . How do we make this happen?
Answer: - Simply create a special HRMS responsibility that has text IRC in their responsibility key.
Note: If you decide to use profile option based approach, then this naming convention is not needed.
Will this not effect the performance?
The source code that I am providing uses session caching, hence minimizing performance impact.
What is the source code?
Apply these package procedures and run below script to enable the RLS.
Note: - I noticed that in Oracle 10g, RLS modifications can invalidate table dependent packages, hence the need to recompile (I am not 100% sure if this is a consistent behavior, hence you may need to recompile yourinvalid objects after trying this out).
CREATE OR REPLACE PACKAGE xx_restrict_irc_ppt_pkg IS
PROCEDURE add_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES');
PROCEDURE drop_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES');
PROCEDURE refresh_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES');
PROCEDURE enable_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES'
,p_enable VARCHAR2 DEFAULT 'Y');
FUNCTION restrict_access(p_owner VARCHAR2
,p_object_name VARCHAR2) RETURN VARCHAR2;
b_session_check_done BOOLEAN := FALSE;
g_session_resp_key VARCHAR2(500);
END xx_restrict_irc_ppt_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_restrict_irc_ppt_pkg IS
PROCEDURE add_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES') IS
BEGIN
dbms_rls.add_policy('APPS'
,p_object_name
,p_policy_name
,'APPS'
,'xx_restrict_irc_ppt_pkg.restrict_access'
,'SELECT');
END add_security_policy;
PROCEDURE drop_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES') IS
BEGIN
dbms_rls.drop_policy('APPS'
,p_object_name
,p_policy_name);
END drop_security_policy;
PROCEDURE refresh_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES') IS
BEGIN
dbms_rls.refresh_policy('APPS'
,p_object_name
,p_policy_name);
END refresh_security_policy;
PROCEDURE enable_security_policy(p_policy_name VARCHAR2 DEFAULT 'XX_RESTRICT_IRC_PPT'
,p_object_name VARCHAR2 DEFAULT 'PER_PERSON_TYPES'
,p_enable VARCHAR2 DEFAULT 'Y') IS
l_enable BOOLEAN DEFAULT TRUE;
BEGIN
IF p_enable = 'N'
THEN
l_enable := FALSE;
END IF;
dbms_rls.enable_policy('APPS'
,p_object_name
,p_policy_name
,l_enable);
END enable_security_policy;
FUNCTION restrict_access(p_owner VARCHAR2
,p_object_name VARCHAR2) RETURN VARCHAR2 IS
CURSOR c_get_resp IS
SELECT responsibility_key
FROM fnd_responsibility_vl
WHERE responsibility_id = fnd_global.resp_id
AND application_id = fnd_global.resp_appl_id
AND responsibility_key LIKE '%IRC%';
p_get_resp c_get_resp%ROWTYPE;
BEGIN
IF fnd_global.user_name IN
('GUEST', 'ANONYMOUS', 'CONCURRENT MANAGER', 'SYSADMIN')
THEN
RETURN '1=1';
END IF;
IF (NOT b_session_check_done)
THEN
b_session_check_done := TRUE;
OPEN c_get_resp;
FETCH c_get_resp
INTO g_session_resp_key;
CLOSE c_get_resp;
END IF;
IF g_session_resp_key IS NOT NULL
THEN
RETURN '1=1';
END IF;
RETURN 'NOT ( SYSTEM_PERSON_TYPE = ''APL'')';
END restrict_access;
END xx_restrict_irc_ppt_pkg;
/
Finally, add this colourful Row Level Security policy and enable it too
DECLARE
BEGIN
xx_restrict_irc_ppt_pkg.add_security_policy;
xx_restrict_irc_ppt_pkg.enable_security_policy;
END;
/
Comments
How to restrict external candidate having more than one account in iRecruitment External site visitor?Is there any solution for this other than jsp customization?
regards
radhik a
I would like to hide "Save Search" and "Views" button on "vacancies" page of Oracle irecruitment. Please let me know if any profile setup is available to do this or I need to customize the SSWA page.
Thanks & Regards,
Sreeni vasa
RSS feed for comments to this post