Today I wanted to show you how to define triggers in XML Data Template. This is in continuation to my blog on generating XML Report from XML Data Template. Check the same here.
So here is my requirement:
- Populate a temporary table with the data based on the parameters passed to the Concurrent Program
- Fetch the data for the report from the Temporary table.
- Truncate the Table after the XML Report is generated
Temporary Table Name – XXRAJ_TEMP_TABLE
Here is how the XML Data Template will look like. I have also marked the changes in this new file.
Lets talk about how to define the trigger.
- Define a pl/sql Package xxraj_xml_triggers_pkg
- Define functions beforeReport and afterReport in the Package. This is very important. You have to define it as functions and not as procedures
- Define the parameters as variables in the Package Specification. In our case p_orgid and p_vendorid are the parameters. So you need to define these as variables in the Package Spec
- Create the package body and define the functions. The beforeReport function will insert the invoive data into the temp table and the afterReport function will truncate the data.
You may download the Package Spec ( xxraj_xml_triggers_pkg_ps ) and Package Body ( xxraj_xml_triggers_pkg_pb) for your reference.
Now looking at the changes in the XML Data Template, you will see that there are following changes:
Default Package
The package that you are using for the Trigger should be defined as the “default Package” in the XML Data Template.
SQL Statement
The SQL Statement has been changed to fetch the data from the temporary table rather than from the AP_INVOICES_ALL Table
Before Report Trigger
Just above the <dataStructure> element, I have defined a new trigger. Since the trigger is defined before the <dataStructure>, this will act as the before Report Trigger. You will notice that I have given the name and source of the trigger.
<dataTrigger name=”beforeReport” source=”xxraj_xml_triggers_pkg.beforeReport(:p_OrgId,:p_VendorId)”/>
name: This can be any value. However to make it easier to understand it will be a good practice to give the name as “beforeReport” to indicate that the trigger fires before the report.
source: Here you give the “package.function” that is to be called for execution. The parameters can be passed to this function.
After Report Trigger
Just below the <dataStructure> element, I have defined the after report Trigger. Since the trigger is defined after the <dataStructure>, this will act as the after Report Trigger.
<dataTrigger name=”afterReport” source=”xxraj_xml_triggers_pkg.afterReport(:p_OrgId)”/>
Upload the new XML file to the Data Definition. Everything else remains the same.
This was a very simple example to show how to define Triggers in XML Data Template. Hope this helps. Feel free to ask any questions you may have. As always your feedback is highly appreciated.
Questions? Comments? Suggestions? Let us know!! Like / Subscribe / Follow for more updates.