Triggers in XML Data Template

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:

1.     Populate a temporary table with the data based on the parameters passed to the Concurrent Program

2.     Fetch the data for the report from the Temporary table.

3.     Truncate the Table after the XML Report is generated

Temporary T­able 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.

1.     Define a pl/sql Package xxraj_xml_triggers_pkg

2.     Define functions beforeReport and afterReport in the Package. This is very important. You have to define it as functions and not as procedures

3.     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

4.     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.

xxraj_xml_triggers_pkg_ps.sql

create or replace PACKAGE xxraj_xml_triggers_pkg

       AS p_orgid NUMBER;

            p_vendorid NUMBER;

   FUNCTION beforeReport(p_orgid IN NUMBER, p_vendorid IN NUMBER)

           return boolean;

    FUNCTION afterReport(p_orgid IN NUMBER)

          return boolean;

END xxraj_xml_triggers_pkg;

xxraj_xml_triggers_pkg_pb.sql

create or replace PACKAGE BODY xxraj_xml_triggers_pkg

 IS --Load the data into temp Table based on the entity type

FUNCTION beforeReport ( p_orgid IN NUMBER, p_vendorid IN NUMBER)

      RETURN BOOLEAN AS

BEGIN FOR invRec IN

   (SELECT invoice_num, invoice_currency_code, invoice_amount

    FROM ap_invoices_all

     WHERE org_id = p_orgId

AND vendor_id = p_vendorId)

LOOP

INSERT INTO xxraj_temp_table ( invoice_num, invoice_currency_code, invoice_amount )

                         VALUES ( invRec.invoice_num, invRec.invoice_currency_code, invRec.invoice_amount ); END LOOP;

COMMIT;

RETURN TRUE;

EXCEPTION WHEN OTHERS

        THEN fnd_file.put_line(fnd_file.log,'Error in beforeReport Proc.');                     

                   fnd_file.put_line(fnd_file.log,'Error : '||SQLERRM);

      RETURN FALSE;

END beforeReport;

--Truncate Data after the report is generated.

FUNCTION afterReport (p_orgid IN NUMBER)

RETURN BOOLEAN AS

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE xxdbppm_global_temp_tab';

RETURN TRUE;

EXCEPTION WHEN OTHERS

     THEN fnd_file.put_line(fnd_file.log,'Error in beforeReport Proc.');

      fnd_file.put_line(fnd_file.log,'Error : '||SQLERRM);

RETURN FALSE;

END afterReport;

END xxraj_xml_triggers_pkg;

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.