How To Extract Approval Rules

Report on requisition and purchasing approval rules
Business Problem:
Customers often have a large number of complex approval rules configured on their system to enforce
purchasing approval policies. For audit purpose, a common business ask is to provide a report on the
approval policies prevailing across cost centers, track changes in approval policies over a period of time
etc.
Solution:
This document contains the steps to create a custom report based on SQL query using the Oracle BI
Publisher reporting solution. The report will extract all requisition approval rules configured in system,
the rule conditions and actions and the stages the rules are created in.
Creating a new report using the BI Publisher tool comprises of two parts:
A) Creating a Data Model : A data model is an object that contains a set of instructions for BI
Publisher to retrieve and structure data for a report. Data models reside as separate objects in
the catalog. In the example I have taken, the data model is based on custom SQL query.
B) Creating a Report based on the data model
Note:
1. Creating and editing data models requires that user is granted a role that includes the BI Author
role and the BI Publisher Data Model Developer role. Note that the ability to create Oracle BI
Publisher data models enables the user to write and execute SQL, and therefore you must
consider carefully to whom they grant the BI Publisher Data Model Developer role.
2. It is possible to leverage the same SQL query to create a report on Purchasing approval rules as
well, just by updating the bind variable value to reflect the task name DocumentApproval.
Creating a Data Model
1. Navigate to the BI Catalog.
To navigate to the catalog:

a. From Navigator, Click Tools > Reports and Analytics.
b. In the Reports and Analytics work area, click the Browse Catalog button.
2. Click Home to access the BI catalog home page.
a. Under the Create region, from Published Reporting, select More > Data Model to launch
the Data Model Editor.
b. Alternatively click on New > Data Model to launch the Data Model editor
3. In the Data Model Editor, the Data Sets page will be the default page displayed.
Create a new Data Set by clicking on + icon, and then SQL Query. The Create Data Set - SQL
dialog will open.
4. On the New Data Set – SQL Query dialog, the enter the following details:
a. Name of Data Source
b. Select a Data Source from the list e.g. ApplicationDB_FSCM
c. The SQL type defaults to Standard SQL - this is used for normal SELECT statements
interpreted to understand database schema.
d. Enter the attached SQL query.
e. After entering the query, click OK to save. For Standard SQL query, the data model
editor validates the query. The editor does not expect a query separator like semi-colon
to be entered.
.
5. Since the query includes a bind variable, you will be prompted to create the bind parameter
next. Click OK to create the bind parameter.
6. On the Data Model components pane, click Parameters and then click on + to create new
Parameter.
a. Enter a Name for the parameter. The name must match with the name of the parameter
in the data set.
b. Select the Data Type a String.
c. Enter a Default Value for the parameter. In this case, I entered the approval task name
which ReqApproval.
d. Select the Parameter Type as Text.
7. After you build your data model, it is required that you attach a small, but representative set of
sample data generated from your data model and view the output to ensure your results are as
expected.
a. In the Data Model editor, select the View Data tab. The click View to display the XML
that is returned by the data model.
8. After the data model has successfully run, click Save as Sample Data. The sample data is now
saved to the data model.
9. Click on the Save icon and save the data model under the Custom folder
.
Creating a report
10. Create a new BI Report by selecting the data model you saved.
Click on Next to open the Data Source pane.
11. The Data Source pane displays the structure of your data model and the data elements that are
available to insert into your layout. To insert a data element, select and drag it from the Data
Source pane to the component in the layout.
12. Click on Next and Save the Report under the Custom folder.
13. Navigate to the Custom Folder and find the report. Click on Open to run the report.
14. The below screen capture shows a sample report generated based on the data model in PDF
format
Update the bind variable and enter the approval task name as “DocumentApproval” to extract
the purchasing approval rules from the system.
How to Run the Report?
For an end user to access the report, his role must be granted appropriate permissions.
Permissions can be granted at the folder level and applied to all the objects and subfolders it
contains, or applied to individual objects. The below steps demonstrate how to grant a
functional role permissions to run the above report BIP we created.
To grant catalog permissions to a role:

1. Navigate to the Catalog. Locate the report > click More, and select Permissions.
2. On the Permissions dialog, click on add (+).
3. On the Add Roles dialog, search for the role name to which you want to give permission
to run the report. Use the shuttle bottom to move the role(s) from Available Members
list to Selected Members list.
Select Custom permission.
4. When finished, click OK to return to the Permissions dialog.

5. On the Permissions dialog, click on the pencil icon to configure the permissions you
want to assign to the role.
I have selected the permission to Read, Run and View Publisher Report. Click OK once
done.
With this configuration, a user having the Procurement Application Administrator Role
will be able to run the above BIP report we created to extract the approval rules.

How To Extract Approval Rules

Uploaded by

Copyright:

Available Formats

How To Extract Approval Rules

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

How To Extract Approval Rules

Uploaded by

Copyright:

Available Formats

Report on requisition and purchasing approval rules

B) Creating a Report based on the data model

Creating a Data Model

1. Navigate to the BI Catalog.

To navigate to the catalog:

2. Click Home to access the BI catalog home page.

How to Run the Report?

To grant catalog permissions to a role:

2. On the Permissions dialog, click on add (+).

Select Custom permission.

4. When finished, click OK to return to the Permissions dialog.

You might also like