How to Apply Custom PL/SQL Based Security with Oracle Report Manager

Following example will give an insight to setup Custom PL/SQL based security with Oracle Report Manager to distribute reports based on "user to value" security function based on the user location(derived from hr locations based on person assignment). A PL/SQL package is required as a custom security to restrict the reporting information only to the user's(employee) location based on location segment in Accounting Combination.

Following is the package to derive individual person location, the parameters are :

 1. Location Code of individual
 2. Oracle Application user_id of individual


CREATE OR REPLACE PACKAGE BODY xx_custom_security AS
--Create  mandatory procedure for Report Manager Custom Security to individual person location

   PROCEDURE accessallowed (
      p_securedvalue    IN       VARCHAR2
     ,p_accessallowed   OUT      NUMBER
   ) IS

-- Define Local Variables
      l_query    VARCHAR2 (3000);
      l_dummy    NUMBER;
      l_cursor   NUMBER;
      l_count    NUMBER          DEFAULT 0;
      l_loc      VARCHAR2 (10)   DEFAULT '0';
      l_result   NUMBER;
   BEGIN
--Initialize variables
      l_loc := '0';
      l_query := '';
      l_cursor := '';
 
-- Dynamic select statement  to derive LOCATION location
      l_query :=
       'SELECT COUNT(hl.location_code)
      FROM per_assignments_x paaf
         , per_people_x papf
         , hr_all_organization_units hou
         , fnd_user fu
         , hr_locations hl
         , fnd_id_flex_segments ffs
         , fnd_flex_values_vl ffv
         , per_jobs pj
     WHERE paaf.person_id = papf.person_id
       AND paaf.organization_id = hou.organization_id
       AND paaf.assignment_type = ''E''
       AND paaf.primary_flag = ''Y''
       AND hou.TYPE = ''LOCATION''
       AND papf.person_id = fu.employee_id
       AND paaf.location_id = hl.location_id
       AND ffs.id_flex_code = ''GL#''
       AND hl.attribute2 = ''LOCATION''
       AND pj.job_id = paaf.job_id
       AND hl.location_code= ffv.flex_value
       AND ffs.application_column_name = ''SEGMENT2''
       AND ffv.enabled_flag = ''Y''
       AND ffs.enabled_flag = ''Y''
       AND ffv.summary_flag = ''N''
       AND ffv.flex_value_set_id = ffs.flex_value_set_id
       AND ffs.id_flex_num =101 ';
      l_query :=    l_query
                 || ' AND hl.location_code = :1 AND  fu.user_id = :2';
      l_cursor := DBMS_SQL.open_cursor;
-- Parse the query
      DBMS_SQL.parse (l_cursor,l_query,DBMS_SQL.native);
-- Pass 'Location' Parameter to the select stmt
      DBMS_SQL.bind_variable (l_cursor,':1',SUBSTR (p_securedvalue, -7));
-- Pass ‘Oracle FND User’ Parameter to the select stmt
      DBMS_SQL.bind_variable (l_cursor,':2',fnd_global.user_id);
      DBMS_SQL.define_column (l_cursor,1,l_count);
     
      l_dummy := DBMS_SQL.EXECUTE (l_cursor);
      l_dummy := DBMS_SQL.fetch_rows (l_cursor);
     
      DBMS_SQL.COLUMN_VALUE (l_cursor,1,l_count);
      DBMS_SQL.close_cursor (l_cursor);

--  If there is a User has Location then Count>1 then the result = 1 else --  result = 0
      IF (l_count = 0) THEN
         l_result := 0;
      ELSE
        l_result := 1;
      END IF;

      DBMS_OUTPUT.put_line ('Result '|| l_result);
      p_accessallowed := l_result;
   EXCEPTION
      WHEN OTHERS THEN
         l_result := 0;
   END accessallowed;
END xx_custom_security;
------------------------------------------------------------------------------------------------------------------
Once the above package is created, the package name i.e., XX_CUSTOM_SECURITY should be passed as a parameter value to "Custom Security" parameter in REPORT MANAGER/Financial Report Submission – Security (Section) as follows: