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:

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.

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.

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.

40 Responses

Bharathi says:

April 28, 2015 at 7:51 am

Hello

What do i need to fill in the afterreport function?

Please help

Reply
1. ArunRaj says:
  
  April 29, 2015 at 3:51 pm
  
  Hello..
  
  You can add any post report processing that you may want to do in the After Report Function.
  For example, you can call bursting program for report delivery.
  
  If you can ask me any specific questions that you may have, I will be glad to help.
  
  Thanks,
  Arun
  
  Reply
hayati89hayati says:

April 29, 2015 at 5:46 am

I am calling 2 functions from the package. Is it possible to do the twice in a row?

Reply
1. ArunRaj says:
  
  April 29, 2015 at 3:53 pm
  
  As far as I know, you can have one function for “Before Report Trigger” and one function for “After Report Trigger”.
  Any processing that you may want to do should be within those functions.
  
  Let me know if that answers your question. If you have questions about a specific functionality or requirement that you are trying to implement, please include those and I will try my level best to help you.
  
  Thanks,
  Arun
  
  Reply
  1. hayati89hayati says:
    
    May 5, 2015 at 8:59 am
    
    Hi Arun, thank you very much for your reply.
    
    Background :
    I have one BeforeReportTrigger function in my Employee Package which works just fine. Then i created another function in the same package. Once i put it in the XML, it straightaway crashed at certain point.
    
    What i did was similar to the below:
    
    Tried a few more ways to fix it, but to no avail. Appreciate your input on this.
    
    Reply
    1. ArunRaj says:
      
      May 13, 2015 at 8:49 am
      
      Do you think you can send me a sample file? Send it to arunraj@quest4apps.com
      Send me the XML Template file, the packages and the functions that you are trying to call from the XML template.
      If there are any sensitive data, you can delete it. I just need the bare minimum just to see how you are calling the functions.
      
      I will try to go through it and help you with the requirements. Sorry for the late reply.
      
      Thanks,
      Arun
      
      Reply
SATHISH says:

August 7, 2015 at 2:08 am

my before & after trigger not firing…plz help..i am pasting my data template & objects

————————————————————————————————-

CREATE OR REPLACE PACKAGE APPS.XXHLDATATEMREP_PKG
AS

p_org_id number;

function beforeReport (p_org_id number) return boolean;

function afterReport return boolean;

procedure XXHLDATATEMREP_PROC (p_org_id number);
end;
/
——————————————————————————-
CREATE OR REPLACE PACKAGE BODY APPS.XXHLDATATEMREP_PKG
AS

Function beforeReport (p_org_id number) return boolean
as
v_a number:=10;
BEGIN

v_a:=p_org_id;

INSERT INTO XXHLDATATEMREP_TAB
values(v_a);
COMMIT;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;

Function afterReport return boolean
as
v_a number:=10;
BEGIN

–v_a:=p_org_id;

INSERT INTO XXHLDATATEMREP_TAB
values(v_a);
COMMIT;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;

procedure XXHLDATATEMREP_PROC (p_org_id number)
is

begin

insert into XXHLDATATEMREP_TAB
values(p_org_id);

commit;
end;

end;
/

————————————————————————————————

CREATE TABLE XXHL.XXHLDATATEMREP_TAB
(
ORG_ID NUMBER
)

Reply
1. ArunRaj says:
  
  August 7, 2015 at 9:47 am
  
  Hi Satish,
  
  Can you send the Data template file (xml) to my email id @ arunraj@quest4apps.com? I will take a look at it.
  Also include the Functions and package specs.
  
  Thanks,
  Arun
  
  Reply
venkat says:

September 1, 2015 at 1:33 pm

Hi,
how to use the output parameter from function in query template,

Let say
.. p_deptno

….. select * from emp where deptno = &p_dept_no

here p_deptno is IN parameter to function and p_dept_no is OUT parameter from function. If I pass 10 to function, the output will be 20 (p_deptno + 10 ) . That 20 should be used in sql query.

How can I achieve it.

Reply
1. ArunRaj says:
  
  September 1, 2015 at 2:02 pm
  
  Hi,
  
  A very interesting question. Thank you for asking.
  As per Oracle’s documentation it is possible. Please take a look at the below link. If you still have questions let me know.
  Search for “Employee Listing Data Template” in the page.
  
  http://docs.oracle.com/cd/E12844_01/doc/bip.1013/e12187/T421739T434255.htm
  
  Hope this helps. If you like the blog it would be great if you can Like / Share the blog.
  
  Thanks,
  Arun
  
  Reply
Rahul says:

May 24, 2016 at 8:42 am

I have been wondering from a long time about how to take care of other 3 triggers which are available in rdf reports in xml reports (Before/After parameter form, Between pages?

Reply
1. ArunRaj says:
  
  May 24, 2016 at 9:16 am
  
  Hi Rahul,
  
  I have not personally tried it. However I believe you can have as many triggers as you want.
  You can have two triggers before the SQL and two after the SQL or two before SQL and 1 after the SQL.
  
  Check this document from Oracle
  
  Thanks,
  Arun
  
  Reply
  1. Rahul says:
    
    May 24, 2016 at 10:14 am
    
    Arun,
    
    I understand that we can have as many triggers as we can but when will those fire? Before report and after report fire at specific times, before query parsing and after generating xml, respectively. Now if I add another trigger, when will it fire?
    
    Rahul
    
    Reply
    1. ArunRaj says:
      
      May 27, 2016 at 8:22 am
      
      Rahul,
      
      Before SQL and After SQL in the sequence in which you define it your data model. Thats my understanding.
      
      Thanks,
      Arun
      
      Reply
Arif says:

January 21, 2017 at 12:20 am

Hi Sir,
Nice Information you provide. But I have a problem, I Create a function in package that accept date parameter ,I define parameter as varchar2 because I am using canonical date in my function. but when I run the report on apps it either show invalid character or format does not match . Will you please help me to resolve it.I am working on it since last 3 days.

Reply
1. ArunRaj says:
  
  January 22, 2017 at 9:47 pm
  
  Hi Arif,
  
  Can you send me the xml schema definition, the package, concurrent program parameter definition and also the log file for the process?
  Email it to me at arunraj@quest4apps.com
  
  Thank you for the encouraging words.
  
  Regards,
  Arun
  
  Reply
Jitender says:

April 20, 2017 at 8:32 am

Hi Arun,

I am trying to insert data in temp table in before report but before report trigger is not firing. Please help.

Thanks
Jitender

Reply
Jitender says:

April 20, 2017 at 8:33 am

Sending you the template and pkg on arunraj@quest4apps.com

Reply
Jitender Sharma says:

April 30, 2017 at 5:55 am

Hi Arun,

My before report trigger is not firing in XML publisher report. Please help.

Sending you the all the objects on your mentioned id : arunraj@quest4apps.com.

thanks,

Jitender

Reply
1. ArunRaj says:
  
  June 8, 2017 at 1:29 am
  
  Hi Jitender,
  
  I was on a sabbatical. So have not checked the emails for some time. Did u resolve this?
  
  Thanks,
  Arun
  
  Reply
  1. Jitender Sharma says:
    
    July 11, 2017 at 5:44 am
    
    Ji Arun,
    I am still facing the same issue.
    
    Reply
  2. Bharat says:
    
    April 24, 2023 at 6:16 am
    
    hello Arun,
    we have a data template, and calling afterreport trigger where calling a package.function with parameters, where it went in error… we are not using before report trigger.
    Regards
    Bharat
    
    Reply
lokesh says:

June 1, 2017 at 7:42 pm

Hi Arun,

I have created data template with Data triggers(Before Report and After Report), But i am getting the below error.
ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol “;” when expecting one of the following:

( – + case mod new not null

continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date
pipe

<an alternatively

Please look into it and let me know how to resolve this issue.

Here are my data template and package.

Package

create or replace PACKAGE XX_abc_PKG
as
–used to Intialize the org
FUNCTION beforeReport
RETURN BOOLEAN;

— used to call the bursting program
FUNCTION AfterReport
RETURN BOOLEAN;

end XX_abc_PKG;

create or replace PACKAGE BODY XX_abc_PKG
AS
FUNCTION beforeReport
RETURN BOOLEAN
IS
BEGIN
apps.mo_global.set_policy_context(‘S’,121);
RETURN TRUE;
EXCEPTION
when OTHERS then
–FND_FILE.put_line (fnd_file.LOG,’Error Occured in Beforreport : ‘|| SQLERRM);
RETURN FALSE;
END beforeReport;
—

—
FUNCTION AfterReport
RETURN BOOLEAN
is
v_req_id number;

v_conc_req_id number;
begin
v_conc_req_id:=fnd_global.conc_request_id;
v_req_id := fnd_request.submit_request(‘XDO’,
‘XDOBURSTREP’,
NULL,
NULL,
FALSE,
‘Y’,
v_conc_req_id,
‘Y’
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

if v_req_id is not null then
FND_FILE.put_line (fnd_file.LOG,’Bursting program Submitted successfully’);
return (TRUE);
else
FND_FILE.put_line (fnd_file.LOG,’Error at Bursting’);
return (false);
end if;

exception
when OTHERS then
FND_FILE.PUT_LINE (FND_FILE.log,’exception at Bursting: ‘||SQLERRM);
RETURN FALSE;

end AFTERREPORT;

end XX_abc_PKG;

Reply
1. ArunRaj says:
  
  June 8, 2017 at 1:26 am
  
  Hi Lokesh,
  
  Has this been resolved? I will take a look at it and let you know.
  
  Thanks,
  Arun
  
  Reply
Srinivasan says:

November 12, 2017 at 12:06 pm

can we define beforeparameterform trigger in data template?

Reply
1. Arun Raj says:
  
  March 9, 2018 at 4:32 pm
  
  I am not aware of that.
  You can have as many “Before Report” triggers you want.
  
  Thanks,
  Arun
  
  Reply
Law says:

February 21, 2018 at 1:47 pm

Is it possible to have 2 afterParameterFormTrigger in one xml file?

Reply
1. Arun Raj says:
  
  March 9, 2018 at 2:21 pm
  
  Hi,
  
  AS far as I know. yes it is possible to have multiple “before reprot” and “after report” triggers.
  Here is a link to a pretty good documentation
  
  Thanks,
  Arun
  
  Reply
Raghu says:

March 21, 2018 at 6:48 am

Hi Arun,
Can we use Before parameter and After Parameter trigger in data template reports? If Yes! what would be the appropriate event name to use here.

Reply
1. Arun Raj says:
  
  April 3, 2018 at 2:05 pm
  
  Hi Raghu,
  
  You can use both.
  
  Before Report will fire before the SQL is executed.
  After report will fire after the SQL is executed.
  
  If you want actions to be performed before SQL is executed, then use Before Report Trigger. For eg, populating data into a temporary table etc.
  If you want actions to be performed after SQL is executed, then use Before Report Trigger. For eg, sending email, bursting etc.
  
  Cheers,
  Arun
  
  Reply
deepali bafna says:

October 25, 2018 at 2:44 am

Hi,

My before report function is not getting called, Can you help?

Reply
1. Arun Raj says:
  
  November 8, 2018 at 7:14 am
  
  Hi,
  
  Did you get it working? If not please send me an email (arun.rajs@outlook.com) with your package definition and the xml data definition.
  
  Thanks,
  Arun
  
  Reply
Rahul Chauhan says:

April 22, 2019 at 10:46 pm

I am not using before report trigger to fetch data for my report. My report is generated using queries defined in data template. My requirement is to populate a custom table with values generated from the query. Is there a way we can pass the values generated by queries or tag values to default package?

Reply
ianadankarch says:

February 19, 2020 at 5:49 am

Hello Run,

I am launching a rural IEX lambt collection treatment with the customer rental number. this generates an XML file (a mail that needs to be sent to customers). Now I want to save in a DOCUBASE table the information of my treatment except that I do not have the right request_id so it is not back up. could you help me pleaze

thank you in advance

Reply
1. Arun Raj says:
  
  March 3, 2020 at 4:47 pm
  
  Are you generating this from Oracle ERP?
  
  Reply
doifode says:

May 11, 2020 at 8:17 pm

I want to pass on a P_CONC_REQUEST_ID into the sql query.How should i pass on that.
I hav already takn it in the before report .
now i want to pass it into the sql query.

FUNCTION beforeReport
RETURN BOOLEAN AS
P_CONC_REQUEST_ID NUMBER;
BEGIN

P_CONC_REQUEST_ID := fnd_global.conc_request_id;

FND_FILE.PUT_LINE(FND_FILE.log,’P_CONC_REQUEST_ID:-‘||P_CONC_REQUEST_ID);

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;

Reply
1. Arun Raj says:
  
  June 1, 2020 at 8:43 pm
  
  I believe we connected over email and you were able to get this working.
  
  Reply
Venu says:

June 10, 2020 at 1:19 am

Hi Arun,

My before report function is updating the global parameter but the update is not reflecting in the data template. And when I return false in after report function it is failing without XML generation.

Please help as soon as possible

Reply
Sateesh says:

October 3, 2020 at 11:15 am

Hi All,
How to pass select query column value to after report trigger.
Thanks
Sateesh

Reply
puja says:

October 4, 2021 at 4:58 pm

I am using after report trigger for calling bursting program.bursting program is running fine but parent prorgam is going in error

Reply

40 Responses

Leave a Reply Cancel reply