Report Triggers in Fusion BIP Reports

Before and After Report trigger in Fusion BIP Reports.

Hi Friends, In this post I will explain you the Fusion BIP report triggers, How we can use these trigger in Fusion.

We all knows that in Oracle Report 10g , we use Report triggers to create dynamic where condition in our reports. We create bind variables in the report and set where conditions to these bind variables based on the input parameter values and then reference this variable in report query. This is really an very important feature.

But in Fusion BIP reports , Oracle has only given two report triggers that is :

2 Types of Report Triggers in Fusion BIP Reports

before Data
After Data

To implement this , first we will create a parameter p_where_clause to create dynamic where condition

Now we will create a before data Trigger. For that we need to create a DB Package first. All the parameters defined in the report must be defined In the package specification as global variable.

Complete Tax Setups in Oracle Fusion

The Logic I am going to use is , If P_VENDOR_NAME paramters is null then where condition will set  like P_WHERE_CLAUSE= 'AND 1=1'

CREATE OR REPLACE PACKAGE XX_FUSION_REPORT_TRIGGER
IS

P_VENDOR_NAME VARCHAR2 (100);
P_WHERE_CLAUSE VARCHAR2 (1000);

FUNCTION before_data
RETURN BOOLEAN;
END XX_FUSION_REPORT_TRIGGER;

CREATE OR REPLACE PACKAGE BODY XX_FUSION_REPORT_TRIGGER
IS
FUNCTION before_data
RETURN BOOLEAN
AS
BEGIN
IF (P_VENDOR_NAME IS NULL)
THEN
p_where_clause := ' AND 1=1';
ELSIF P_VENDOR_NAME IS NOT NULL
THEN
p_where_clause := ' AND upper(a2.vendor_name) like ''%'||UPPER(:P_VENDOR_NAME)||'%'' ';
ELSE
p_where_clause := NULL;
END IF;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
HEN
RETURN FALSE;
END;
END XX_FUSION_REPORT_TRIGGER;

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

Before Report Trigger.

Use the package name in the Oracle DB Default Package.

Move the Package name with procedure from Available function to Event Trigger as below.




Now set the Where Clause lexical parameter  to your Main report query.

If you want to see Other OAF Related Posts , Please visit Below URL.https://rpforacle.blogspot.in/2013/03/oaf_10.html



If you want to see  Oracle Fusion Related Posts , Please visit Below URL.

https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html

If you want to Learn Oracle Workflow Builder, Please visit Below URL.

https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html