The goal here is to create a custom report containing a list of all the identities and the resource information. In this case, the resource is called MY_USERS_GTC.

Within this report, I want to present the following values:

• username
• first name
• lastname
• Resource Name
• E-mail
• resource status

• a .SQL file containing a modified store procedure
• XML file containing the reporting details
• Modification of the xlWebAdmin.properties file

In this example, I have decided to use the Oracle freeware product, SQL Developer. You need to create a new connection. Enter in your Oracle property values and test the connection by clicking connect.

As you may already know, the assignment of the tables, views, stored procedures etc are assigned to individual identities. In my case, I have assigned the OIM schema to the 'xelsysadm' user. Unless you have changed the user, the default is xelsysadm.

You need to expand out the user xelsysadm, then expand out the procedures.

To use as a template for creating new reports, locate the XL_SP_USERRESOURCEACCESS store procedure. Double click on this and a new window will display on the right. This is almost identical to the report that you will be creating, though we will need to make some alterations.

As you have the store procedure open, click on file and click save as.... Now find a suitable location to save this new file and call it 'Default_Store_Procedure_for_Reports.sql'. Once you've done that, click OK.

In order to create a report, you must first construct a SQL DDL Query, which will contain all the information from the database.

This is the SQL query that I will be using:

select distinct usr.usr_login AS "User_Login",
usr.usr_first_name AS "First_Name",
usr.usr_last_name AS "Last_Name",
obj.obj_name AS "Resource_Name",
UD_MY_USERS.UD_MY_USERS_EMAIL AS "E-mail"
FROM xelsysadm.usr,
xelsysadm.obi,
xelsysadm.obj,
xelsysadm.oiu,
xelsysadm.ost,
xelsysadm.orc,
xelsysadm.UD_MY_USERS
WHERE
obi.obj_key = obj.obj_key
AND obi.obi_key = oiu.obi_key
AND oiu.usr_key = usr.usr_key
AND ost.ost_key = oiu.ost_key
AND oiu.orc_key = orc.orc_key
AND orc.orc_key = xelsysadm.UD_MY_USERS.orc_key
AND obj.obj_name= 'MY_USERS_GTC'

You will probably need to change the object name and the values you are pulling

Now you have the default file that you saved and the SQL query, you will need to start manipulating the store procedure file by doing the following:

• 1. Change the procedure name (Step 6)
• 2. Removing/Adding parameters (Step 7 + 8)
• 3. Removing/Adding the IF statements (Step 9)
• 4. Include SQL query in SP (Step 10)
• 5. Alter the xlWebAdmin.properties (Step 11)

There are a few changes that you need to do there, you start off by changing the stored procedure name into something else.

On line 2, change the store procedure name...

PROCEDURE XL_SP_UserResourceAccess (

To...

PROCEDURE XL_SP_DR_NEWRESOURCE (

On line 327, change the store procedure name...

END XL_SP_UserResourceAccess;

To...

END XL_SP_DR_NEWRESOURCE (

The first 12 parameters within the store procedure (upto strudfcolumnvaluelist_in) are mandatory parameters.
For the meanwhile, remove all the values that you will not be needing:

struserlogin_in IN VARCHAR2,
strfirstname_in IN VARCHAR2,
strmiddlename_in IN VARCHAR2,
strlastname_in IN VARCHAR2,
struseremail_in IN VARCHAR2,
strorgname_in IN VARCHAR2,
strusergroup_in IN VARCHAR2,
strmgrfirstname_in IN VARCHAR2,
strmgrlastname_in IN VARCHAR2,
struserstatus_in IN VARCHAR2,
struseremptype_in IN VARCHAR2

As I mentioned before, I will be wanting to display the following values within the search query:

• username
• first name
• lastname
• resource status
• E-mail

Starting around the line 95 mark within the same file, you will need to remove any of the IF statements from step 5 and include any IF statements from step 6 Example, I am including an IF statement for 'strdatabase_in'
Review the form that is assigned to the resource object of interest. In this case, I am extracting information from the Oracle RO Resource. Form associated to that is called 'UD_DB_DR'
The field label value associated to this form that I wish to search for is called 'E-mail'. The name of this is called UD_DB_MY_USERS_Email.

Therefore, I will be using the structure of 'Form Designer.Column Property' to locate the value within the IF statement.

Example

IF strdatabase_in IS NOT NULL THEN
whereclause := whereclause
|| ' AND UPPER(UD_DB_DR.UD_MY_USERS_EMAIL) LIKE '
|| UPPER (''''||stremail_in||'''')
|| ' ';
END IF;

There are other steps required, which can be located within this blog,

'Creating custom reports part 2'.

About the author