Oracle HRMS

To best explain this iRecruitment issue, for a minute, I want you to assume that you are working for an Organization that has implemented Oracle iRecruitment. Assume further that you hate your current job/role, and hence seek to apply for another vacancy internally within your Organization. You do not want your boss to know this until that opportunity materializes. Unfortunately, Oracle iRecruitment does not provide a functionality to keep your inspirations secret.
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;
/

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel