Oracle Fusion BI Publisher supports PL/SQL Data Sets, allowing report developers to implement business logic directly within a report.
This approach is useful when report data needs to be dynamically filtered or transformed based on user input.
Requirement
In this example, users can select a Supplier Type parameter from the following values:
- SUPPLIER
- PUBLIC SECTOR COMPANIES
- INSURANCE
- INTERCOMPANY
Based on the selected value, the report retrieves matching supplier records from the Fusion Procurement supplier table (POZ_SUPPLIERS_V)
Click to view the full PL/SQL Data Set code
DECLARE
----------------------------------------------------------------------------
-- Define a REF CURSOR type.
-- BI Publisher uses a cursor to return the final result set back to
-- the report output.
----------------------------------------------------------------------------
TYPE refcursor IS REF CURSOR;
----------------------------------------------------------------------------
-- Cursor variable that will hold the query result.
-- BI Publisher expects the data to be returned through xdo_cursor.
----------------------------------------------------------------------------
xdo_cursor REFCURSOR;
----------------------------------------------------------------------------
-- Variable used to store the supplier type returned by the function.
----------------------------------------------------------------------------
l_supplier_filter VARCHAR2(100);
----------------------------------------------------------------------------
-- Local PL/SQL Function: Supplier_type
--
-- Purpose:
-- Accepts the report parameter P_VENDOR_TYPE and validates the value.
--
-- Allowed Values:
-- SUPPLIER
-- PUBLIC SECTOR COMPANIES
-- INSURANCE
-- INTERCOMPANY
--
-- Returns:
-- The corresponding supplier type value.
-- If no valid value is found, returns 'NA'.
----------------------------------------------------------------------------
FUNCTION Supplier_type (
p_in_vendor_type IN VARCHAR2
)
RETURN VARCHAR2
IS
----------------------------------------------------------------------------
-- Variable to hold the function result.
----------------------------------------------------------------------------
l_supplier_type_rtn VARCHAR2(100);
BEGIN
----------------------------------------------------------------------------
-- Check if the selected supplier type is SUPPLIER.
----------------------------------------------------------------------------
IF p_in_vendor_type = 'SUPPLIER' THEN
l_supplier_type_rtn := 'SUPPLIER';
----------------------------------------------------------------------------
-- Check if the selected supplier type is PUBLIC SECTOR COMPANIES.
----------------------------------------------------------------------------
ELSIF p_in_vendor_type = 'PUBLIC SECTOR COMPANIES' THEN
l_supplier_type_rtn := 'PUBLIC SECTOR COMPANIES';
----------------------------------------------------------------------------
-- Check if the selected supplier type is INSURANCE.
----------------------------------------------------------------------------
ELSIF p_in_vendor_type = 'INSURANCE' THEN
l_supplier_type_rtn := 'INSURANCE';
----------------------------------------------------------------------------
-- Check if the selected supplier type is INTERCOMPANY.
----------------------------------------------------------------------------
ELSIF p_in_vendor_type = 'INTERCOMPANY' THEN
l_supplier_type_rtn := 'INTERCOMPANY';
----------------------------------------------------------------------------
-- If none of the above values match, return NA.
----------------------------------------------------------------------------
ELSE
l_supplier_type_rtn := 'NA';
END IF;
----------------------------------------------------------------------------
-- Return the validated supplier type value.
----------------------------------------------------------------------------
RETURN l_supplier_type_rtn;
EXCEPTION
----------------------------------------------------------------------------
-- Handle any unexpected errors and return NA.
----------------------------------------------------------------------------
WHEN OTHERS THEN
RETURN 'NA';
END Supplier_type;
BEGIN
----------------------------------------------------------------------------
-- Pass the BI Publisher report parameter P_VENDOR_TYPE
-- into the Supplier_type function.
--
-- Example Input:
-- SUPPLIER
-- INSURANCE
-- PUBLIC SECTOR COMPANIES
-- INTERCOMPANY
--
-- Example Output:
-- SUPPLIER
-- INSURANCE
-- PUBLIC SECTOR COMPANIES
-- INTERCOMPANY
----------------------------------------------------------------------------
l_supplier_filter := Supplier_type(:P_VENDOR_TYPE);
----------------------------------------------------------------------------
-- Open the cursor and return the result set to BI Publisher.
----------------------------------------------------------------------------
OPEN :xdo_cursor FOR
----------------------------------------------------------------------------
-- Retrieve supplier information from Oracle Fusion Procurement
-- Supplier View (POZ_SUPPLIERS_V).
----------------------------------------------------------------------------
SELECT
----------------------------------------------------------------------------
-- Supplier Name
----------------------------------------------------------------------------
vendor_name,
----------------------------------------------------------------------------
-- Supplier Type
----------------------------------------------------------------------------
vendor_type_lookup_code,
----------------------------------------------------------------------------
-- Organization Type
----------------------------------------------------------------------------
organization_type_lookup_code,
----------------------------------------------------------------------------
-- Business Relationship
----------------------------------------------------------------------------
business_relationship,
----------------------------------------------------------------------------
-- Display the selected parameter value in the report output.
----------------------------------------------------------------------------
l_supplier_filter supplier_type
FROM poz_suppliers_v
----------------------------------------------------------------------------
-- Filter suppliers based on the selected Vendor Type parameter.
--
-- Example:
-- If P_VENDOR_TYPE = 'INSURANCE'
-- Then the query becomes:
--
-- WHERE vendor_type_lookup_code = 'INSURANCE'
----------------------------------------------------------------------------
WHERE vendor_type_lookup_code = l_supplier_filter;
END;
/
Create Data model in Fusion
As below
- Preview the data by passing parameters value.
- Save sample data & move to create a report
Conclusion
This PL/SQL-based BI Publisher Data Set demonstrates how Oracle Fusion report developers can combine report parameters, custom functions, and cursor-based queries to create dynamic reports.
By processing the user-selected supplier type through a PL/SQL function and applying it to the query filter, the report becomes more flexible, maintainable, and aligned with business requirements.
This technique is especially useful in Procurement reporting scenarios where supplier classifications drive reporting outcomes.
Top comments (0)