Self Service Procurement

Get Involved. Join the Conversation.


    Sumana Sarkar
    How to extract a Report on Purchase Requisition and...
    Topic posted September 6, 2019 by Sumana SarkarSilver Medal: 2,000+ Points, last edited September 6, 2019, tagged Approvals, Business Intelligence - BI, How-To 
    102 Views, 3 Comments
    How to extract a Report on Purchase Requisition and Purchasing Approval Rules
    The steps needed to produce a report of the approval rules that have been created in the system for Purchase Requisition and Purchasing related Approvals

    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.

    The attached 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:

    1. 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.
    2. Creating a Report based on the data model

    The document also talks about how an end user can be granted appropriate permissions to be able to run the report.


    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, therefore implementors must consider carefully.
    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 in the attached SQL query to reflect the task name DocumentApproval.



    • Ashok

      Support / Customers

      Do share your feedback on this article with Sumana. It would be good to know if you have used this and has met the requirements that we have heard from Customers and Support previously.

    • Ramesh Bomma

      Thanks for the document and steps to create Approval Rules report
      It helps lot of customers who can use this for Auditing their Approval Rules



    • Francisco Robinson

      Thanks for this document, Sumana!

      It's really useful for both, implementation team and customer.