Sometimes there are scenarios when standard SQL in Fusion BI report data set is not sufficient to get relevant information. There could be some custom validation or business logic to implement in the report extraction logic. This is easily possible in on-premise EBS R12 system using XML publisher reports where developers have direct access to database and one can write triggers on report, create database objects like functions, packages and PL SQL record and table type. But in Fusion SaaS ERP, there is no direct database access to create custom objects, so it becomes difficult to implement such custom logic in Fusion BI report.
Fusion BI can be used to run SQL using custom function. See below steps:
Create new Data Model in BI and then create a parameter to use in data set SQL
Create new Data Set with type as Non-Standard SQL and use below SQL code. Notice that new custom function named “supplier_type” is created and called from SQL. Based on value return from function, SQL column value will be populated. One can modify this function to add more PL SQL logic like deriving value by calling SQL within function.
WITH FUNCTION supplier_type (p_in_business_rltn IN varchar2)
RETURN varchar2 AS l_supplier_type_rtn varchar2(100) ;
BEGIN
IF p_in_business_rltn = 'SPEND_AUTHORIZED' THEN
l_supplier_type_rtn := 'Approved';
ELSE l_supplier_type_rtn := 'Restricted';
END IF;
RETURN l_supplier_type_rtn;
EXCEPTION
WHEN others THEN
RETURN 'NA';
END;
SELECT vendor_name,
enabled_flag,
organization_type_lookup_code ,
business_relationship,
supplier_type(business_relationship) AS "Supplier Type"
FROM poz_suppliers_v
WHERE enabled_flag = NVL(:P_ENABLED_FLAG, enabled_flag)
Save the Data Set and go to Data tab to see data output.
Note: In case of simple logic use standard SQL as it avoids unnecessary performance issue because of context switch between the PL/SQL engine and the SQL engine