By continuing on with the manipulation to the store procedure file, use your SQL query from step 4 and import this information into the file.

You need to change the following:

The SELECT into the strColumnList
The FROM into the strFromClause
The WHERE into the strWhereClause

strColumnList:= 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" ';


strFromClause := ' xelsysadm.usr',
|| 'xelsysadm.obi,'
|| 'xelsysadm.obj,'
|| 'xelsysadm.oiu,'
|| 'xelsysadm.ost,'
|| 'xelsysadm.orc,'
|| 'xelsysadm.UD_MY_USERS';

strWhereClause := ' 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''
|| whereclause||' ';

This is the store procedure file modified and complete so go ahead and save this file. Once saved, execute this whole file within SQL developer. Compile the SQL query then commit for the SQL query to take effect.

You need to ensure that the properties have been altered from within the xlWebAdmin.properties file

Go to the xlWebAdmin.properties (located within the /WEB-INF/classes folder)
and include the following lines....

# Inserted by DR 18th September 2012 # MY USERS GTC reports STARTS report.UsersWithMYUSERSGTCResource.label.userLogin=User Login report.UsersWithMYUSERSGTCResource.label.firstName=First Name report.UsersWithMYUSERSGTCResource.label.lastName=Last Name report.UsersWithMYUSERSGTCResource.label.resourceName=Resource Name report.UsersWithMYUSERSGTCResource.label.email=E-mail report.UsersWithMYUSERSGTCResource.label.resourceStatus=Resource Status # MY USERS GTC reports ENDS

Go ahead and save this file

Now we need to create the report and place this into the REP table. This is completed by constructing a SQL insert statement

The core component of the report is the XML within the SQL query, the top nodes are the input fields, the bottom are the output of the report. You need to ensure they match the store procedure values

Firstly, include the values you want to search for when executing the report. These values must also match to the values you included from within Step 7.

Next, ensure that the return values match the values associated with the SQL query in step 10

Finally, include the labelling of the values. These must match the values from within Step 12

Log in as xelsysadm
Go to users group --> Manage --> Search and select 'system administrators'
Within the drop-down list, select Allowed Reports
At the bottom left, click Assign Reports
Select the report you have imported in, and click OK