Thursday 1 February 2018

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

Report Triggers in Fusion BIP Reports




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.
Report Triggers in Fusion BIP Reports



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

Report Triggers in Fusion BIP Reports




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

18 comments:

Unknown said...

Good Article!!

Unknown said...

Does this apply to Fusion Cloud where I thought we could not create database packages?

rk said...
This comment has been removed by the author.
rk said...

How to create DB package in Fusion Cloud?

Unknown said...

good stuff but without answering the question raised by folks post is incomplete.

of-course i am also looking were do we crate database package in fusion. Please let us know it would be highly appreciated.

Rohit said...

Hi ,

Srry for Delay in response. Yes we can create the DB packages in Fusion but it depend upon which cloud services you are using. If you are using SAAS then it's not possible in other Mofels like PAAS we can create the DB packages in PLSQL because in PAAS we have the Data base access. I hope this have resolved you concerns

Anonymous said...

Wonderful blog I visit this blog it's incredibly awesome. Curiously, in this blog content formed doubtlessly and sensible. The substance of information is helpful.
Oracle Fusion HCM Online Training

shaik shah said...

Good blog, Thanks for Sharing informative article. It would be helpful to all.
Oracle Fusion HCM Online Training

Goutham Raj said...

Good Blog, well descrided, Thanks for sharing this information.
Oracle Fusion HCM Technical Online Training

Prakash Pagam said...

How can we call PAAS DB package in SaaS? Any guidance please.

Anonymous said...

Hi Prakash,

We can call any DB Package in SAAS using report trigger. Only thing, we cannot do is to create the DB package itself in SAAS Cloud.

Mayank said...

If we can't create DB package then how can we create lexical reference?

Mayank said...

If we can't create DB package then how can we create lexical reference?

Vikas Prajapati said...

Could you please let us know how to create DB package in the Fusion instance? In your blog it is not mentioned any where on the creation of DB package in fusion.

Vikas Prajapati said...
This comment has been removed by the author.
Ramaya said...

can we create ess job for dependent parameters ( ex:vendor_id,vendor_site_id)with lov report

Ramaya said...

Hi can we create ess job for dependent parameters ( ex:vendor_id,vendor_site_id)with lov reports ?

Anonymous said...

is it possible to edit any DB Package in SAAS using report trigger ?

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *