Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

Introduction

For all of those who have worked with Oracle Reports they must be acquainted with using Before Parameter, After Parameter and other Report Triggers. Unfortunately for Oracle Fusion Cloud (until release 13) the only supported trigger is Schedule Trigger.(Worked out example on how to use a Schedule Trigger can be foundhere )

One very common usage of using Report Trigger (After Parameter Report Trigger in particular) was to validate the input values provided by end-user and one can display user friendly messages as per requirement) Typical examples being:

1.       Specify If an Incorrect Data Value is provided

2.       Notify if Mandatory parameter value is not provided

3.       Any specific action to be taken basis value of Report parameter

While Oracle BI (Cloud Deployment) does not allows us to create such triggers there could still be a smart way of achieving the above.

One would need to make use of a BI Report (in conjunction with a RTF Template).

In this article we would try to demonstrate the same.

We may break up the entire activity into the following sub-steps:

Create a SQL Based Data Model

  1. Ensure you use an Input Parameter

  2. Create a Global Level Function Using Add Element by Expression Feature to check whether the input provided is NULL or NOT NULL

  3. Create an Aggregate Function which would be used whether the BI Query returns data or not

  4. Create a RTF template

  5. Add specific logic to display messages when NO parameter value is passed

  6. Add specific logic to display messages when a parameter value is passed but yields no results.

Creating a Data Model

We would create a very simple SQL query which would fetch some of the data fields from application base table (per_all_people_f for this example). We would also use a Parameter (PersonNumber).

SQL Query

Select papf.person_number,

      papf.start_date,

      rownum recordno

From  per_all_people_f papf

Where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

And papf.person_number = :PersonNumber

Creating an Input Parameter

From the query we could see that we make use of an input variable PersonNumber in this case.

Details about the parameter is as follows:

Parameter Name

Data Type

Default Value

Parameter Type

Row Placement

Display Label

PersonNumber

String

 

Text

1

Person Number

Screen-shot:

Creating Global Level Function Using Add Element by Expression

We would need to create a Global Level Function (let’s say ISPERSONNUMBERNULL)

*Name

Display Name

Data Type

Expression

IsPersonNumberNULL

IsPersonNumberNULL

String

IF( param.PersonNumber!=null,'N','Y')

Screen-shot:

Creating Global Level Function Using Add Element by Expression

We would also need to create an Aggregate function (say CS_RECORDNO) which would be used to identify whether the SQL Query instance (for the specific input value passed) returns any value or not.

Details about the Aggregate Function are as follows:

*Column Name

*Alias

Display Name

Function

Data Type

Value if NULL

Round

G_1.RECORDNO

CS_RECORDNO

CS_RECORDNO

COUNT

Integer

 

3

Screen-shot:

Create a RTF Template

We would now need to create a RTF Template. We would need to map the Data Elements and the corresponding messages (based on specific conditions). The template used for this example would look as below:

Configuring RTF Template to Display Appropriate Message when Parameter Value Passed is Blank

We would need to specifically add logic in the RTF Template to display appropriate message when there is no value passed for the input parameter. In order to accomplish this we would make use of ISPERSONNUMBERNULL variable we have defined earlier.

Value of ISPERSONNUMBERNULL Variable

Meaning / Interpretation

Y

Value is NULL. No Input passed

N

Value is NOT NULL. An Input value is passed

We would make use of this variable and have the following logic added to the RTF Template.

<?if:ISPERSONNUMBERNULL = 'Y' ?>

Mandatory Parameter Missing Input

You have not provided an input for a Mandatory Parameter.

<?end if?>

From the code we can infer that this specific message will be displayed only when there is no input value passed.

Configuring RTF Template to Display Appropriate Message when an INVALID Parameter Value is Passed

We would need to specifically add logic in the RTF Template to display appropriate message when we do pass a input value but it does not returns any results. We would make use of both the expression variable ISPERSONNUMBERNULL and CS_RECORDNO

We would apply the following logic to the RTF Template

<?if: ISPERSONNUMBERNULL = ‘N’ and CS_RECORDNO = 0 ?>

Parameter Value Passed Returns No Results

There is No Data found for the provided input.

<?end if?>

The code indicates that when there is an Input Value passed (ISPERSONNUMBERNULL is ‘N’ ) but if the count of total records returned is 0 it means that there is no results retrieved using the SQL query.

And in the best case scenario when the input parameter value is NOT NULL and CS_RECORDNO returns a positive integer we display the actual data.

Verification

Till now we have explained how to configure the Data Model and the RTF Template, but the job isn’t complete unless we test the same.

As such, we would need to perform an exhaustive test and we would have three scenarios for the same:

1.       Scenario 1 : Not passing a input value

2.       Scenario 2: Pass an invalid input value

3.       Scenario 3: Pass a valid input value

Verification: Scenario 1: NULL Input

We would run the Report by NOT providing an Input Value for the parameter Person Number and the report should show an appropriate message.

Verification: Scenario 2: NOT NULL Invalid Input

In this scenario we would provide an INVALID input as the parameter value. Here too we expect to have a meaningful message displayed. We will pass ‘ABC’ as a person number (which is an invalid value) and check the report output.

Verification: Scenario 3: Valid Input

In this scenario we will provide a valid person number as parameter input and the report should display results. We will pass 10 (a valid value) and check for the report output.

Inference / Conclusion

So this is how we can configure out RTF Template to display different messages / results based on specific conditions. Hope this article did provide some basic guideline and starting point on which you all may extend further.

Do try it out and share your views / results with me. I hope this was a good write-up and would be useful to you guys. Thank You for your time, have a nice day ahead!



Ashish Harbhajanka

Comments   

0 #1 pharmacepticacom 2021-06-20 15:03
chloroquine phosphate cvs https://www.pharmaceptica.com/
Quote

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  May 2022  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner