PL SQL block from Fusion BI Report using Procedure Call

Fusion BI obviously supports standard SQL to generate report. But for complex scenario where one needs to use PL SQL command, Procedural Call type Data Set can be used. BI Publisher supports executing PL/SQL anonymous blocks. When this SQL data type is used, no metadata is displayed on the data model structure tab, therefore modification of the data structure or data fields is not possible.

PL/SQL block must return a result set of type REF cursor and one must declare the out variable with name “xdo_cursor“

Create new Data Model in BI and then create a parameter to use in data set SQL

Create new Data Set with type as Procedure Call and use below code. Here based on input parameter custom function is called and then ref cursor is returned from the data set.

DECLARE
    TYPE refcursor IS ref CURSOR;
    xdo_cursor        REFCURSOR;
    l_supplier_filter VARCHAR2(50);
    FUNCTION Supplier_type (p_in_enabled_flag IN VARCHAR2)
    RETURN VARCHAR2
    IS
      l_supplier_type_rtn VARCHAR2(100);
    BEGIN
        IF p_in_enabled_flag = 'Y' THEN
          l_supplier_type_rtn := 'SPEND_AUTHORIZED';
        ELSE
          l_supplier_type_rtn := 'PROSPECTIVE';
        END IF;

        RETURN l_supplier_type_rtn;
    EXCEPTION
      WHEN OTHERS THEN
                 RETURN 'NA';
    END;
BEGIN
    l_supplier_filter := Supplier_type(:P_ENABLED_FLAG);

    OPEN :xdo_cursor FOR
      SELECT vendor_name,
             enabled_flag,
             organization_type_lookup_code,
             business_relationship,
             l_supplier_filter
      FROM   poz_suppliers_v
      WHERE  business_relationship = l_supplier_filter;
END;

Save the Data Set and go to Data tab to see data output.

Refer this to use custom function in BI Report.