Different types of Triggers in Oracle Report

There are basically eight different type of trigger in Oracle reports.

(1) Before Parameter Form

It Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns.

(2) After Parameter Form

It Fires after the Runtime Parameter Form is displayed.

(3) Before Report

It Fires before the report is executed but after queries are parsed and data is fetched.

(4) Between Pages
It Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting.

(5) After Report

It Fires after you exit the Previewer, or after report output is sent to a specified destination, such
as a file, a printer,this trigger always fires, whether or not your report completed successfully.

(6) Validation Triggers

Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.

(7) Format Triggers

Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

(8) Action Triggers

Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer.This trigger can be used to dynamically call another report or execute any other PL/SQL.


Of all these trigger the most commonly used are before and after report triggers. We will try to see an example and understand how this trigger works.

We will go step by step in this and first create a simple report and then we will try to use trigger in the report.

Lets use ap_invoices_all table in EBS to create a simple report.

We will try to fetch information from this table based on the query

select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount from ap_invoices_all

Further to filter the search criteria we will use two input parameter org_id and vendor_id

So the filtered query will be something like this

select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount from ap_invoices_all WHERE org_id = :p_OrgId AND vendor_id = :p_VendorId

Here p_OrgId and p_VendorId are the parameters for the report.

Now the first things is to create a data template which can be used to create the xml document for our report.

As we know the data template consists of four sections

1>Define parameter
2>Define Triggers
3>Define Data Query
4>Definde Data Structure

We will create a XML basic data template for our requirement and this will be as below

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<dataTemplate name =”Invoice” description=”AP Invoice Data” Version=”1.0″>
<parameters>
<parameter name=”p_OrgId” dataType=”number” />
<parameter name=”p_VendorId” dataType=”number” />
</parameters>
<dataQuery>
<sqlStatement name=”invoiceQuery″>
<![CDATA[
select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount
from ap_invoices_all
WHERE org_id = :p_OrgId
AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name=”INV_GROUP” source=”invoiceQuery″>
<element name=”INVOICE_ID” value=”invoice_id” />
<element name=”INVOICE_NUMBER” value=”invoice_num” />
<element name=”INVOICE_CURRENCY_CODE” value=”invoice_currency_code” />
<element name=”ORGANISATION_ID” value=”org_ID”/>
<element name=”INVOICE_AMOUNT” value=”invoice_amount”/>
</group>
</dataStructure>
</dataTemplate>


Now first is to load the XML template in to your EBS.

Log in to Oracle apps and go to following navigation



Go to data definition tab and create a new data definition



And update the data template that we have created



Now we will create a concurrent program to call this template.

Go to System Administer responsibility-->Concurrent -->Program-->Define



Now create a program and make sure you are using Short name same as you have the code name for Data definition



Define the parameters for them



Go to security-->responsibility-->request



And add your program to it




Now actually to know the repsonsibility you can go to the following page

SEcurity-->Responsibility-->Define

Copy the Request group name and say CTRL+F11 and you will get the responsibility as shown



Now go to that responsibility and try to run the concurrent program.

Once you are able to validate the data output of the concurrent program we can go ahead and use the same template to add trigger in to it.

We will use before and after trigger in this case to do some logging.

Again it is always a good practice to add following component to your data template

<properties>
<property name="xml_tag_case" value="upper"/>
<property name="debug mode" value="on"/>
<property name="scalable_mode" value="on"/>
</properties>



Now to achieve some logging we will write a package and two fucntion for before and after trigger call. This will help us to log some error data in the concurrent program output.

This is not a great example but through this post we just want to cover how the tigger functionality can be use. You can come up with a much better logic for the trigger, something like before triggering the report you should commit the data or update some records or may be some other better logic. But in this exercise we will create a simple function to see the use case on how before and after trigger can be used.


SO My pacakge goes like this

create or replace
PACKAGE Custom_error AS
p_OrgId varchar2(20);
p_VendorId varchar2(20);
FUNCTION Initialize return BOOLEAN ;
FUNCTION Finalize return BOOLEAN ;
END Custom_error;


And my package body is

create or replace PACKAGE BODY Custom_error AS

FUNCTION Finalize RETURN BOOLEAN IS
BEGIN
fnd_file.put_line(fnd_file.LOG, 'After triggering every thing is fine');
return true;
END;

FUNCTION Initialize RETURN BOOLEAN IS
BEGIN
fnd_file.put_line(fnd_file.LOG, 'Before triggering every thing is fine');
return true;
END;

END Custom_error;


Compile this procedure and now we will make an entry in the data template to call these funciton in before and after trigger.

Now my revised Code will be

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name ="Invoice" defaultPackage="Custom_error" description="AP Invoice Data" Version="1.0">
<properties>
    <property name="xml_tag_case" value="upper" />
    <property name="debug mode" value="on" />
    <property name="scalable_mode" value="on" />
  </properties> 
<parameters>
<parameter name="p_OrgId" dataType="CHAR" />
<parameter name="p_VendorId" dataType="CHAR" />
</parameters>
<dataQuery>
<sqlStatement name="invoiceQuery">
<![CDATA[
select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount
from ap_invoices_all
WHERE org_id = :p_OrgId
AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="Custom_error.Initialize()" />
<dataStructure>
<group name="INV_GROUP" source="invoiceQuery">
<element name="INVOICE_ID" value="invoice_id" />
<element name="INVOICE_NUMBER" value="invoice_num" />
<element name="INVOICE_CURRENCY_CODE" value="invoice_currency_code" />
<element name="ORGANISATION_ID" value="org_ID"/>
<element name="INVOICE_AMOUNT" value="invoice_amount"/>
</group>
</dataStructure>
<dataTrigger name="afterReport" source="Custom_error.Finalize()"/>
</dataTemplate>


As you can see we have added two tags here

<dataTrigger name="beforeReport" source="Custom_error.Initialize()" />
<dataTrigger name="afterReport" source="Custom_error.Finalize()"/>

AS you can see these tags are actually calling the fucntions that we have defined.

Now once again go back to your concurrent program and try to run this

And this time you can find the following as output



So now i believe you might have got the idea on how to use the trigger in xml report.

You can also use procedure and pass the input parameters also to the call. Rest is for you all to experiment.